3.1 什么是性能优化：
3.3 剖析mysql查询
3.4 诊断间歇性问题
mysqladmin -uroot -p ext -i1|awk '
#把上次的值存到qp里，然后本次值减去qp的，就是一秒内的query数量
awk '/^# Time:/{print $3, $4, c;c=0}/^# User/{c++}' mysql_slow.log
|sort -k 3 -nr
结果类似这样：
140106 7:00:01 278
140106 10:20:01 269
140106 7:40:01 266
140106 12:20:01 264
140106 6:40:01 261
140106 7:10:01 254
3.4.2捕获诊断数据
3.5其它剖析工具
4.1选择优化的数据类型
4.1.6
4.2常见的schema设计陷阱
4.3范式和反范式
4.4缓存表和汇总表
update hit_counter set cnt=cnt+1 where slot = rand() * 100;
当要获取新的序号时，这样获取：
select sum(cnt) from hit_counter;
这样就把写竞争拆分成100个小锁，大大增加了并发性。但100个定时器的sum还是顺序地往上走，却不会锁（因为只是select）。
还可以再新加一个date列，一天100个计数器，如下表结构：
create table daily_hit_counter (
day 版的增加计数器是这样：
insert into
daily_hit_counter(day,slot,cnt)
如果希望减少表中的行数，可以把所有计数器都加到0号slot上，然后把其余的都删掉：
update daily_hit_counter as c
delete from daily_hit_counter where slot <> 0 and cnt=
0;
结果演示：
mysql> select * from daily_hit_counter;
+------------+------+-----+
| day
+------------+------+-----+
| 2014-01-06 |
5 |
| 2014-01-06 |
| 2014-01-06 |
| 2014-01-06 |
| 2014-01-06 |
| 2014-01-06 | 12 |
| 2014-01-06 | 13 |
8 |
| 2014-01-06 | 14 |
| 2014-01-06 | 15 |
| 2014-01-06 | 16 |
9 |
| 2014-01-07 |
3 |
| 2014-01-07 |
| 2014-01-07 |
7 |
| 2014-01-07 |
8 |
update之后就成为
+------------+------+------+
| day
+------------+------+------+
| 2014-01-06 |
| 2014-01-06 |
| 2014-01-06 |
| 2014-01-06 |
| 2014-01-07 |
| 2014-01-07 |
| 2014-01-07 |
| 2014-01-07 |
| 2014-01-07 |
| 2014-01-07 |
| 2014-01-07 |
删掉后就只剩下两行：
| day
+------------+------+------+
| 2014-01-06 |
| 2014-01-07 |
以下是自动插入day 版的存储过程：
delimiter //
create procedure a()
delimiter ;
4.5加快alter table的速度
5.3.5 聚簇索引
聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，它是连索引带数据一起的一种数据存储方式，不光是索引。在oracle里它叫索引组织表，其实就是一种表，数据呀，索引呀啥的，都有了，啥也没剩下了。
innodb和myisam的索引区别有两点：
5.3.6 索引覆盖
如果一个索引包含所有要查询的字段的值，就成为“索引覆盖”。在explain结果的extra列里有“using
index”就表示用到了索引覆盖。
索引覆盖要求select的列和where列出的列都要在索引中列出。这个条件有点高，有时where列出的列都包含在索引里，但select就不一定，这种情况可以通过“deferred
join”的技巧来部分使用索引覆盖。
思路是把索引包含的列组成一个子查询，得出部分结果(比如要查询的行的主键)，然后再根据这个部分结果(主键)去获取全部结果。
例子：
5.3.7 是用索引扫描来做排序
mysql有两种方式可以生成有序的结果：通过排序操作或者按索引顺序扫描。如果explain结果的type列值是"index"，则说明mysql使用了索引扫描来排序。
只有当索引的列顺序和order
by子句的顺序完全一致，并且所有列的排序方向都一样时，才能使用索引对结果进行排序。如果查询需要关联多张表，则只有当order
by子句引用的字段全部为第一个表时，才能使用索引排序。order
by子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则只会执行排序操作而不会使用索引排序。
有一种情况下order by子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。如果where
子句或join子句中对这些列制指定了常量，就可以弥补索引的不足。
索引列(a,b,c)
则
... where a = 'xxx' order by b,c 是可以使用索引排序的
则...
5.3.9冗余和重复索引
一般来讲，重复的索引是不需要的，这也包括左前缀索引是重复的。但有一些例外情况：
第一：比如select count(*) from a where colx = xx; 在colx上建有index,
对下面的语句：
myisam , q1
114.96
25.4
myisam, 9.97
16.34
16.37
innodb, 108.55
100.33
innodb, 12.12
28.06
第二：因为Inoodb的二级索引的叶节点是指向主键的，索引对于一个二级索引(colx)实际上是(colx,pkey),
所以(colx）和(colx,pkey)同时存在，就是重复的了。
5.3.11 索引和锁
这是一个很重要的内容，但书上写得不多，仅仅举了一个例子，是间隙锁的一个例子。
5.4.1 支持多种过滤条件
对于一些使用得非常频繁的列，虽然这些列的选择性不高，但由于使用很频繁，所以如果能把它们都用到索引，还是会快一些。比如sex,
country。那么我们建一个(sex,country,lname,compny,...)的索引，希望能尽量多地适用于不同的sql。
对于那些不使用这两列的查询也可以用一种办法用到这个索引。方法简称IN方法，具体是：建立一个复合索引，然后sql里的选择加上
5.4.2避免多个范围条件
所谓多范围查询，比如： and col1 > 100 and col2 between 'ab' and 'yz';
mysql无法同时使用(col1)和(col2)索引，只能使用其中之一。松散索引扫描就可以实现一个索引上使用多个范围条件，也就不需要上面的IN方法了。
5.4.3 优化排序
对于翻页逻辑会很慢，比如
select from profiles where sex='M' order by rating limit
100000,10
一种优化方法是deferred join,改写如下：
select from profiles inner join (
5.5.2 更新索引统计信息
innodb会在表首次打开，或者执行naalye table，抑或表的大小发生非常大的变化的时候计算索引的统计信息。
inndb在打开某些informaction_schema表，或者使用show table staus 和hsow
index，或在mysql客户端开启自动补全功能的时候都会触发索引统计信息的更新。如果服务器上有大量数据，这可能就是个很严重的问题。可以关闭innodb_stats_on_metadata参数来避免上面提到的问题。percona里通过innodb_stats_auto_update参数来禁止通过自动采集的方式更新索引统计信息，这是需要手动执行analyze
table命令来更新统计信息。
另外，mysql还能使用系统表来持久化这些索引统计信息。percona
5.1中控制参数是innodb_use_sys_stats_table,
mysql5.6是innodb_analyze_is_persistent
6.2慢查询基础
6.2.1 确定只向mysql申请需要的数据
1. 如果只取有限行，用limit限制
2. 如果只需要某些列，不要用select *
6.2.2 为得到上面预申请的数据，mysql需要扫描的行数
有三个指标：响应时间，扫描的行数和返回的行数。后两者比例越接近1:1就越好，但通常达不到。关键就是where条件的利用情况：
最好的情况： 由存储引擎层完成，在索引中使用where条件国旅不匹配的记录。这个应该是ICP（Index
Condition Pushdown）
较好的情况： 索引覆盖(extra: Using index)返回记录，也是依赖索引就可以滤过不要的并返回结果，不过它
是在mysql 服务层实现的(与上面的情况相区别)，也不需要回表查询。
次些的情况： 从数据表中返回数据，然后过滤不满足条件的(extra: Using where).
如果尝试去提高这个比例，可以
1. 尽量按上面的三种情况使用where的列索引
2. 改变库表结构，使用汇总表
3. 把复杂sql改写成简单的，比如巨型delete改成一次delete一点，join拆成单表查询，不过这都要具体分析
6.4 查询执行的基础
查询执行路径
6.4.1 mysql server/client通信协议
这个协议是半双工的。任何一个时刻要么服务器向客服端发数据，要么是反过来。
多数连接mysql的库函数都可以获得全部结果集并缓存在内存里，也可以不缓存，一行一行获得。通常是缓存的，服务区在数据都到达客户端的缓存后才能断开连接。注意这个时候数据仅仅是在客户端驱动的缓存，还没到真正的客户代码。这样有助于少占用服务端时间，尽早释放服务端资源。但问题是如果返回一个很大的结果集，库函数会花很多时间和内存去处理这些数据，这种情况就宁可不要缓存，直接从服务器拿数据处理。
对于php来讲，mysql_query是缓存版的，还有一个mysql_unbuffered_query不缓存版。
perl的不缓存是在连接的时候指定mysql_use_result=1
6.4.2查询缓存
6.4.3 查询优化处理
一. 语法解析器和预处理
二.查询优化器
三. mysql如何执行关联查询
四.执行计划
五.关联查询优化：主要就是驱动表的选择
六.排序优化：两个版本的order by
算法。当查询需要所有列的长度不超过参数max_length_for_sort_data
时，mysql使用优化后的排序方法。
七.查询执行引擎
八.返回结果给客户端
6.5 mysql查询优化器的局限性
6.5.1 关联子查询：where
id in (select ...) 这类的语句
一般来讲mysql实现子查询的效率不高，一般是把它换成join来做。但也不是绝对，具体还是要具体分析。
6.5.2UNION的限制：在每个select后面加上limit
6.5.3索引和并优化
6.5.4等值传递:mysql会把一个表上面的in列表数据传给其它表。如果这个列表很大，就会导致优化和执行都很慢。不过这个问题很罕见。
6.5.5 无并行执行
6.5.6 无哈希关联
6.5.7 关于松散索引扫描(loose index scan): 在mysql
5.6里面已经支持
如 select
min(actor_id) from actor where first_name = 'xxxx';
first_name
无索引。
下面的查询希望把两个表中相似行（type相等）的数量记录到字段cnt中
update
tbl
inner join (
select type,count(*) as cnt
from tbl
group by type
) as der
USING(type)
set
tbl.cnt=der.cnt;
mysql>select sum(if(color='bule',1,0)) as
blue, sum(if(color='red',1,0)) as red from items;
@curr_cnt:=cnt as
cnt,
@rank := if(@prev_cnt <>
@curr_cnt,@rank + 1,@rank) as rank,
@prev_cnt := @curr_cnt as
dummy
from
(
select count(*) as cnt
,actor_id
from film_actor
group by actor_id
order by cnt desc
limit 10
) as der;
c1=values(c1) + (0*(@x:=@x+1));
6.5.9 在同一个表上查询和更新
6.7 优化特定类型的查询
6.7.1 优化count()查询
count(*)用于统计行数
count(colname 或列的表达式)用于统计列植的数量，此种情况是把NULL排除的。
下面是一个计算满足某种条件行数的示例：通过一个查询返回各种不同颜色商品的数量，此时不能用or语句(select
count(color='blue' or color='red') from
items),因为这样无法区分不同颜色的商品数量；也不能使用and，比如color = 'blue' and color 'red'
,两个and条件相互排斥。可以这样：
也可以用count
mysql>select count(color='blue' or NULL) as blue,
count(color='red' or NULL) as red from items;
更进一步的优化count()是一个很难的问题，因为这意味这要访问大量数据。一般来讲，mysql层面能做的就是尽量使用索引覆盖扫描。如果这个不行，就要考虑修改应用的架构，增加汇总表或使用memcached这样的外部缓存机制。“快速，精准和实现简单”很难都瞒住，必须舍掉其一。
6.7.2优化关联操作
两大信条：
a.
确保ON或USING子句中的列上有索引。在创建索引的时候只需要在关联顺序中的第二个表的相应列上创建索引。如果优化器的关联顺序是B,A,那么就只需要在A表上的对应列上建索引就够了。
b. 确保任何的group by 和order by中的表达式只涉及到一个表中的列，这样mysql才有可能使用索引来优化。
6.7.3优化子查询
一般来讲是使用关联查询代替,当然最详尽的方案是具体分析。不过在mysql5.6或mariaDB，就不需要用关联查询代替了。
6.7.5 优化limit 分页
1. 使用delayed
join，改写成临时表的子查询。内查询使用索引覆盖，得到主键，再和外查询关联，得到索要找的列数据。
2. 获取上次分页的最后记录的主键id0，下次使用这个id0：
where id > id0 limit offset.
（假设从小到大的顺序获取记录）
6.7.9 使用用户自定义变量
一例： 打印出某个group分组总量的排序
表：create table film_actor(actor_id int ,film_id int);
数据：insert into film_actor values
(1,100),(2,109),(3,102),(4,190),(4,123),(4,132),(4,129),(5,194),(5,190),(6,193),(6,187),(7,167),(7,105),(7,119),(7,121),(7,153),(7,149),(7,148),(7,147),(7,142),(7,141),(7,159),(7,158),(7,152);
解决方案：
set
@curr_cnt:=0,@prev_cnt:=0,@rank:=0;
select
actor_id,
结果：
| actor_id | cnt | rank |
dummy |
+----------+-----+------+-------+
|
7 |
13 | 1 |
13 |
|
4 |
4 | 2 |
4 |
|
5 |
2 | 3 |
2 |
|
6 |
2 | 3 |
2 |
|
1 |
1 | 4 |
1 |
|
2 |
1 | 4 |
1 |
|
3 |
1 | 4 |
1 |
+----------+-----+------+-------+
分析：
子查询得到前两列的内容
select colxxx
字段列表的排列是有前后顺序的，放在后面的最后执行。
set
是初始化三个局部变量全零，
select的三个计算： 第一个是得到该行的cnt，
第二个计算rank, 第三个把本次的cnt保留供下一行比较。这三个计算是按顺序的，不能调换。
统计insert on duplicate key update的update数量
insert into t(c1,c2) values (4,4),(2,2),(3,1)
on duplicate key update
表达式0*(@x:=@x+1)总是0，所以不会改变整个sql的结果，但每update一次@x变量就增加一次。最后,mysql会返回@x的值。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/653783/viewspace-1115819/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
没有了~
下一篇：
没有了~
请登录后发表评论
登录
全部评论
