一 sql的排查

1.1 性能下降原因

1.索引失效

2.sql写的太烂

3.关联查询太多join(设计缺陷或不得已的需求)

4.服务器调优以及各个参数配置(缓冲、线程数)

1.2 mysql的瓶颈

1.cpu在饱和的时候，一般发生在数据装入内存或从磁盘上读取数据时候。

2.磁盘I/O瓶颈发生在装入数据远远大于内存容量的时候。

3.服务器硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能。

1.3 排查步骤

1.通过top命令 排查mysql进程使用cpu的占有率

2.通过show full processlist； 抓取慢sql。

通过mysql -uxxx -pxxx 进入mysql命令行，输入：show full processlist；

3.通过explain 查看sql执行计划

二  mysql的explain执行计划详解

2.1 explain/desc概述

2.2 id列

1.规则1：id相同，则从上到下顺序执行

2.规则2：id不同，id的数字越大，优先级越高，越先执行。

3.规则3：

有相同也有不同

2.3 selectType

2.3.1 simple

2.3.2 primary

2.3.3 derived

desc select * from ( select * from user where name='Java' union select * from user where role_id=1 ) a;

第一个执行的select_type为简单查询，第二个及以后的查询为UNION，最后合并时的select_type为UNION RESULT。

2.3.4 subquery

desc select * from user where role_id=(select id from role where name='开发');

2.3.5  dependent subquery

在SELECT或WHERE列表中包含了子查询,子查询基于外层

desc select * from user where role_id = ( select id from role where id=user.id );

2.3.6  uncacheable subquery

2.3.7  union

desc select * from user where name='Java' union select * from user where role_id=1;

desc select * from user where name='Java' union select * from user where role_id=1;

2.3.9  uncacheable union

2.3.10  materialized

2.4 table列

查询涉及的表或衍生表

2.5 partions分区

partitions查询涉及到的分区

1.创建分区表

2.查看

 desc select * from user_partitions where id>200;

查询id大于200（200>100，p1分区）的记录，查看执行计划，partitions是p1，符合我们的分区规则

2.6


Type列

type 提供了


判断查询是否高效的重要依据依据。通过

type

字段, 我们判断此次查询是全表扫描还是索引扫描等。

可以使用desc，或者 explain 执行，查看执行计划：

类型从好到坏的排序:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >ALL

常见的有：


system>const>eq_ref>ref>range>index>ALL

一般来说，得保证

查

询至少达到range级别，最好能达到ref。

2.6.1 System


system

: 表中只有一条数据，相当于系统表； 这个类型是特殊的

const

类型；


system

：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。

2.6.2 Const


const

:


主键或者唯一索引的常量查询


，表格最多只有1行记录符合查询，通常const使用到主键或者唯一索引进行定值查询。表示通过索引一次就找到了，const用于比


较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中


，MySQL就能将该查询转换为一个常量

2.6.3 eq_ref



eq_ref

: 除了system和const类型之外,效率最高的连接类型；


唯一索引扫描，对于每个索引键，表中只有一条记录与之对应；


常用于主键或唯一索引扫描。（

联表唯一，和上面的区别在于索引数量不同


）

根据上面的知识；可知id相同，由上至下依次执行，分析结果可知：

先查询t表就是teacher表中name字段为a的记录，由于name字段没有索引，所以全表扫描（type:ALL），一共有3条记录，扫描了3行（rows:3）,1条符合条件（filtered:33.33 1/3）；

再查询tc即teacher_card表使用主键和之前的t.tc_id关联；由

于是关联查询，并且是通过唯一索引（主键）进行查询，仅能返回1或0条记录

，所以type为eq_ref。

总结：

equ_ref用于是关联多表查询时的唯一索引查询，对每个索引键，表中只有一条或零条记录与之匹配

2.6.4 ref

ref：此类型通常出现


在多表的 join 查询


,


针对于非唯一或非主键索引, 或者是使用了

最左前缀

规则索引的查询（


换句话说，返回的可能是多行数据）

2.6.5 range

range：表示使


用索引范围查询,


通过索引字段范围获取表中部分数据记录. 这个类型通常出现在


=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。


在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

2.6.6 index

如果索引是复合索引


，并且复合索引列满足select所需的所有数据，则仅扫描索引树。在这种情况下，



Extra

为

Using index



。仅索引扫描，通常比ALL索引小于表数据，查询更快 。

index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小（



也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的



）。

1.select 查询的列为tc_id,满足覆盖索引规则，此时扫描索引树，extra为using index

2.索引列不满足select所需的所有数据（如索引是tc_id，查询是name）


，此时需要回表扫描；按索引顺序查找数据行。



Uses index

没有出现在

Extra

列中。

2.6.7 all

all：

ALL

: 全表扫描,没有任何索引可以使用

时。这是最差的情况，应该避免。

由于name字段不存在索引，type:ALL全表扫描；可通过对name字段设置索引进行优化。

2.6


possibleKeys

possible_keys（理论上要多少索引）



显示可能应用在这张表中的索引，一个或多个。


查询涉及到的字段若存在索引，则

该索引将被列出，但不一定被查询实际使用

。

2.7


keys

key（实际用到的索引）实际使用的索引


。如果为NULL，则没有使用索引，查询中


若使用了

覆盖索引，则该索引仅出现在key列表中。


所谓的覆盖索引：查询的字段正好和建立索引的字段和顺序一致。

2.8


keys_len

表示索引中使用的


字节数，


可通过该列计算查询中使用的索引的长度。



在不损失精确性的情况下，长度越短越好。



只计算利用

索引作为index key的索引长度，不包括用于group by/order by的索引长度


key_len显示的值


为索引字段的

最大可能长度，并非实际使用长度



，即key_len是根据表定义计算而得，不是通过表内检索出的。

1. 
   一般地，key_len 等于索引列类型字节长度，例如
   
   int类型为4 bytes，bigint为8 bytes
   
   ；
   
2. 
   如果是字符串类型，还需要同时考虑字符集因素，例如u
   
   tf8字符集1个字符占3个字节，gbk字符集1个字符占2个字节
   
   
3. 
   若该列类型定义
   
   时允许NULL，其key_len还需要再加 1 bytes
   
   
4. 
   若该列类型为变长类型，例如 VARCHAR（TEXT\BLOB不允许整列创建索引，如果创建部分索引也被视为动态列类型），
   
   其key_len还需要再加 2 bytes
   
   

字符集会影响索引长度、数据的存储空间，为列选择合适的字符集；变长字段需要额外的2个字节，固定长度字段不需要额外的字节。而null都需要1个字节的额外空间，所以以前有个说法：

索引字段最好不要为NULL，因为NULL让统计更加复杂，并且需要额外一个字节的存储空间。

假设这些字段为索引字段

id：长度为4

age：长度为 4+1=5

empno： 4+1=5；

name:  utf-8 字符集  20*3+2  ； gbk ：20*2+2；

一些其他例子

2.9 ref

ref：（显示使用到的条件查询，如果是常量就为const）

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

显示该表的索引字段关联了哪张表的哪个字段

由key_len可知t1表的索引idx_col1_col2被充分使用，t1表的col1匹配t2表的col1，col2匹配了一个常量，即 ‘ac’。

2.10 rows

根据表统计信息及索引选用情况，


大致估算出找到所需的记录所需要读取的行数。（越小越好）

案例：

可以看到t2表没有创建索引

创建复合索引，进行查看

2.11 filtered

返回结果的行数占读取行数的百分比，


值越大越好

先查询t表就是teacher表中name字段为a的记录，由于name字段没有索引，所以全表扫描（type:ALL），一共有3条记录，扫描了3行（rows:3）,1条符合条件（filtered:33.33 1/3）；

再查询tc即teacher_card表使用主键和之前的t.tc_id关联；扫描索引

（type:ref）

，返回1条记录，最终返回1条记录，

（filtered:100 1/1）

。

2.12 Extras

2.12.1



Using filesort

查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。

2.12.2



Using temporary

2.12.3

Using index