这篇注重实战，主要内容包括：

MySQL 慢查询分析相关资料
常见的慢查询分析思路
PROD 遇到的 CASE 积累

EXPLAIN 常见资料

官方文档系列
- Explain output
- EXPLAIN syntax
- EXPLAIN EXTENDED output
- 优化器相关
  - 指定索引
- 写好查询的一些建议
  - SELECT 语句优化
慢查询相关的配置
介绍索引
常见的工具
- pt-slowlog:比MySQL官方的慢查询信息要丰富，详情可以戳这里

几个关键点：

5.7版本开始，支持的 DML 语句类型更多了（比如 UPDATE 什么的）
EXPLAIN EXTENDED 只有 SELECT 才有，5.7 默认开启（即不用显示 EXPLAIN EXTENDED）

常见的慢查询分析思路

撸了一些文档+文章后，不总结一些自己的套路，总会存在记不住的感觉。这里总结了

问题 SQL 的定位

通常是根据业务场景，设置合理的 long_query_time & slow_query_log=ON 来发现慢查询。当然有一些工具来分析，上面有提及。通常在筛选慢查询的时候，考虑：

order by 慢查询次数，选取 top xx
order by 慢查询 avg time，选取 top xx
order by tp95 慢查询，选取 top xx

EXPLAIN 分析具体语句

问题到了这里通常是已经定位了具体的 SQL，然后需要分析原因了。常见的问题一般是：

没有用到预期的索引
- 没有用索引
- 优化器选择了错误索引
- 索引没有实现覆盖索引
扫描的行数过多
查询没什么问题，IO 有问题
返回无用列，且无用列有明显 IO 性能开销（比如TEXT or BLOB or JSON 之类的）

索引分析

使用了哪个索引，以及使用了索引的哪一部分：

通常看 possible_keys 以及 key 两个字段
possible_keys 的内容取决于 where 语句 + EXPLAIN TABLE 的结果，即 where 会用到哪些 EXPLAIN TABLE 中能看到的索引
TODO:补充下possible_keys选择index的条件
key 表示 MySQL 存储引擎实际选择的索引。这个索引可能不是 possible_keys 中列出来的
key_len 表示实际使用的索引的长度，通常能根据这个字段 + key 来判断用了 key 的哪几个字段

当前表的关联方式，也对查询性能影响较大。对应的在 EXPLAIN 结果中，有个 type 字段来参考关联方式。通常问题 SQL 中会出现 type=ALL 或者 type=index 的情况，即全表扫描。下面列举了下 type 的可能值，按照性能从好到差来排序。

system & const: 表示你条件可以被转换为常数列（即能通过条件确定唯一值），比如
- 只有一行记录的系统表：system
- where 条件是 pk or unique key 的唯一值，例如 a=1 & a is pk or uk
eq_ref & ref & ref_or_null
- eq_ref：previous table 的内容能在当前 table 唯一确认一列
- ref：用了索引，但是做不到 eq_ref 那么好，就是 ref
- ref_or_null：等价 ref，比 ref 多比较了一个 null 的情况
fulltext：使用了 FULLTEXT 索引
index_merge
- 采用了多个索引，此时 key 列会展示用到的具体索引
unique_subquery & index_subquery
- 针对 IN 类型的 subquery 优化
range
- 用了索引，判断采用范围读取的方式比较好
- 通常会出现在范围类型的 operator & 当前索引中用到的列，其区分度比较小的情况
- range 对 column 相关 operator 是有要求的，支持的有：=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, or IN()
index & all
- 都是全表扫描，区别是用非主键还是主键
- 要注意的是，不同索引的扫描，数据的返回顺序是不一样的

关于 eq_ref & ref 的理解，其实是 MySQL 在拿到 previous 表的数据，要处理本表的时候，想偷懒。那什么情况能偷懒呢？当本表的关联字段能用到 uk or pk 的时候，MySQL 知道比一次就 ok了。如果没法确认唯一值，则用的就是ref。ref_or_null 是 ref 的一个特殊情况，针对可空的列的比较。

IO 分析

通常可以通过 EXPLAIN 给出的行数预估来分析大概的 IO 开销，具体的字段有

rows
- MySQL 认为执行当前查询需要遍历的行数
- 在 innodb 中，这是个估计值；TODO 补充下 why
filter：表示实际有效的rows / MySQL scan 的 rows，这个值在 1-100 之间，一般越大越好
- stackoverflow:filter means
- 严格意义上 filter 代表的内容实际已经在 IO 发生之后，但是 filter 参数可以体现你的查询中无效 IO 的占比

pt-slow-query 一些参数

除了通过 EXPLAIN 来分析 MySQL 执行语句的行为之外，还可以通过一些额外的参数来分析。percona 在代码中进行了埋点，可以对慢查询进行监测。常见的指标解释如下：

Query_time：查询时间
Lock_time：锁时间 TODO 持有锁吧？
Rows_sent：实际返回的 row 数量
Rows_examined：实际读取的 row 数量
Rows_affected：查询影响的行数量（UPDATE 类似的）
Bytes_sent：Rows_sent * query row size
Tmp_tables：查询中，在内存中创建的临时表数量
Tmp_disk_tables：查询中，在 disk 上创建的临时表数量
Tmp_table_sizes：查询用到的临时表（mem+disk）总大小
QC_Hit：缓存命中
Full_scan：是否全表扫描
Full_join：是否 join without index
Tmp_table：是否用到临时表；yes or no
Tmp_table_on_disk：查询是否创建 disk 临时表；yes or no
Filesort：查询是否用到的 filesort；yes or no
Filesort_on_disk：查询是否用到 disk filesort ；yes or no
Merge_passes：TODO 暂时不知道
InnoDB_IO_r_ops：当前查询 read page 操作的次数（一个估计值）
InnoDB_IO_r_bytes：read page * page size
InnoDB_IO_r_wait：查询花费在 io 设备上等待上的时间
InnoDB_rec_lock_wait：查询花费在等待 row lock 的时间； TODO table lock 呢？
InnoDB_queue_wait：当前查询等待被调度的时间；TODO 看看是不是跟 cpu 有关呢
InnoDB_pages_distinct：统计本次查询 read page 去重后的数量
- 这个是依赖于一个 hash_array 去计算的，比 InnoDB_IO_r_ops 还不准
- btw，数越大越不准

通常分析的时候，会考虑：

看看有没有问题项，比如：Full_scan，Full_join，Tmp_table，Tmp_table_on_disk
看看查询的 IO 开销，如：InnoDB_IO_r_wait，InnoDB_IO_r_ops，
看看是不是锁导致的：InnoDB_IO_lock_wait
看看是不是 cpu 负载高导致的：InnoDB_queue_wait

官方文档在这里。

需要注意的是，log_slow_verbosity 这个参数对具体输出的内容影响还是比较大的。这个参数介绍如下：

能指定 slow log 中到底输出什么玩意儿
可配置的内容有：
- microtime：是否用微秒计算查询
- query_plan：是否输出 EXPLAIN 的内容
- innodb：是否输出 innodb 的一些静态参数
- minimal：等价于 microtime
- standard：等价于 microtime,innodb
- full：等价于 microtime, query_plan, innodb
- profiling：开启性能剖析
- profiling_use_getrusage：开启高级性能分析

通常我们的配置是 full

另外一些相关的参数有：

log_slow_filter：慢查询采样的方式，query 级别或者 session 级别；
log_slow_rate_limit：采样的比率，1-100，默认是1，记录全量；跟上面的参数结合使用
log_slow_sp_statements：是不是记录 stored procedures 的慢日志
slow_query_log_use_global_control：慢查询的参数是用全局的还是local的啊
slow_query_log_always_write_time：定义了日志在查询结束后多久开始写

通常是记录 query 级别的慢查询，并且记录全量的。

MySQL INFORMATION_SCHEMA

TODO：待补充

优化成本与收益的评估

CASE 分析

这里整理了一些典型的 CASE 进行分析

大查询

select id,data_category_ids,data_label_ids,dp_recommend_count from wm_sku_data_77 where id in (453338068,470040215,462609978,443988226,449445332,444039920,441522846,448273400,446023844,451334931,446036497,448221618,453308830,453324980,451146363,451253253,752467558,444092352,444059744,446115229,443991718,451479971,451336327,457061195,448209283,451488817,457306327,453311209,453307698,472568521,453306264,451349146,451396846,465518943,462602036,465518530,453732809,457370608,457260589,446408606,457087110,465516595,465523250,752463867,465521583,470040345,470046660,396914098,396932006,396925153,396932594,396865450,397788893,397770968,342933317,342907935,550628938,349016246,426859626,733075648,426838494,733072381,370243127,375444814,370247367,375445246,385490281,396853993,385001785,385100302,389366696,384173302,384171927,577517305,385001578,385089883,369935009,393683221,383992294,384175978,388449371,396857432,342943506,367673119,346872752,557216905,344594896,344647818,349542729,353208870,353295610,346862831,344655564,345805418,350833408,344531106,367757317,370123141,370118543,370118764,370225469,370147186,384182382,370243117,370123008,371166489,357023164,557540192,565730412,371166358,372833669,371166668,442817126,433833918,444010093,446400682,446075050,441339532,441623285,444044685,444614371,446033485,441624289,446138871,444129095,444000781,442822030,441632171,426839258,396913606,446348274,448269241,453335432,453311435,448223235,449448239,449786170,451339224,451245089,444027520,446038886,446104020,448225326,448276467,448278364,442810294,448274491,446004703,444008583,451330241,453298435,443995163,442812510,451239721,448267718,448204597,441527000,444053700,457389132,457227319,449779633,453420889,444078482,441553493,730765296,465521654,472473929,346858805,305474344,306348494,316366040,306035164,305477009,315838581,496124310,304768303,304151357,310495933,496451096,317906177,496113183,320687938,306128322,317871537,320685881,315734848,317550768,315655506,318010473,315641833,315731800,306072762,315351409,316919614,315617709,317871114,318485289,304796226,305476626,316897891,321490300,316915753,316882157,316885257,323513050,314485345,306346235,315739699,315825599,316449162,304757142,308094235,322730753,509606233,315345926,316900825,335728618,303613078,306189144,317586304,305752089,305586050,495748004,315602827,496117654,318253082,318492808,318489088,316913427,315735084,316909605,496126096,306077279,312596756,320689788,305579322,305476063,320581651,318484787,315284773,317480018,304104345,316464073,315618156,314433486,315522324,495003159,315603736,315617936,317552666,316885124,316330244,315839883,315656239,316395000,509118327,316410043,316902100,316922400,305656921,321683873,495820915,306338224,496101268,496123768,396931562,315842658,305572693,306029033,308214256,314431381,495639160,308515874,303613079,497074967,318491008,316906180,320687504,316900546,496165447,316403419,305237941,304147503,317495181,318280131,315627437,305472972,305474672,318253634,322735938,316910518,320440119,315655723,315601317,322715696,316920873,308099207,316396056,316883517,317432506,305571862,310293313,315720564,315735264,318209356,496110809,316883036,306336112,496108598,496262859,315745297,506891239,317902089,325845477,309266662,317636609,314484872,316385360,314600953,314624013,315862379,316447591,320633359,318223407,320662910,335741440,314615862,320512079,315861595,317666120,317614202,314615975,317662465,316411175,315644426,472501604,306431965,321506417,306489315,321682486,320686843,315624607,315852063,321679143,318246641,318253691,329726337,323456047,320687591,316884945,323451360,315862002,316852349,317617612,322910313,318280512,321688295,325847053,320687149,305129243,369919438,314431327,497082768,317627490,315722031,496128160,317679888,318281281,304792453,305766753,320652646,306337957,321457101,507009685,321501455,308163813,315284774,305622945,304693120,315619344,305478070,318220166,315586393,306108020,316901286,496224759,496127098,320440073,506889526,317909450,321505823,314433410,305474265,305286089,306169694,305308093,304159409,306184983,306185433,316858890,314517531,496110967,316901782,305657209,316903738,317590101,495650696,304074403,317633881,315647584,316902255,306080210,306433360,496468946,496120833,320658101,314600467,506888893,494963209,320648237,317549666,315624083,317709617,317705566,318496456,304091871,304194421,316879750,496453998,317622036,304702775,305624715,531006970,323510778,349519173,444615762,541286469,536563313,541291275,541283179,541291403,534201763,535164302,541287477,538615670,541286964,546801524,370125311,548272873,551131423,548323411,752464849,557533671,557195943,557097458,451141542,451221342,546891777,541289283,557657982,555241878,304795278,336907486,337271893,521472620,326910675,521598826,337277929,524426384,548251770,555208884,555158179,555190868,551132246,548548866,451251305,551139314,448205192,557183524,444105755,548355116,544933660,544928946,557152571,546884333,741550977,531192460,448204912,369942401,534192230,541287031,541289223,317446111,317997411,495815105,442871951,600188380,496120788,396932584,396917972)

explain

+----+-------------+----------------+------------+-------+---------------+---------+---------+-----+------+----------+-------------+
| id | select_type | table          | partitions | type  | possible_keys | key     | key_len | ref | rows | filtered | Extra       |
+----+-------------+----------------+------------+-------+---------------+---------+---------+-----+------+----------+-------------+
| 1  | SIMPLE      | wm_sku_data_77 |            | range | PRIMARY       | PRIMARY | 8       |     | 500  | 100.00   | Using where |
+----+-------------+----------------+------------+-------+---------------+---------+---------+-----+------+----------+-------------+

explain 没有什么问题。继续看慢查询日志，能发现：

#查询时间
Query_time_pct_95：0.1413688224141467
Query_time_median：0.11715152384010005
#问题原因
Rows_sent_pct_95：479.43383964521115	
Rows_sent_max：1568
Rows_sent_median：462.34785693624747	
InnoDB_IO_r_ops_pct_95：268.3401180350262
InnoDB_IO_r_ops_median：217.77432919967092
InnoDB_IO_r_bytes_pct_95：4396756.842355549
InnoDB_IO_r_bytes_median：3575052.773446439
InnoDB_IO_r_wait_pct_95：0.12995980728473044
InnoDB_IO_r_wait_median：0.10632934818258488
Bytes_pct_95：4960.0556819400745
Bytes_median：4764.844027032568
InnoDB_pages_distinct_pct_95: 440.89948084152536
InnoDB_pages_distinct_median: 361.3429844024798

问题应该比较明显，花在 IO 上的时间太多；IO 花费高的原因从 InnoDB_pages_distinct 能看出，

update demo

update wm_sku_data_57 set wm_poi_id=1092557,name='???? 1?/???500g',price=35.9,picture='http://p0.meituan.net/xianfu/6a7fef040ab5c7ae4d54d2f8b8351d7a158838.jpg',valid=1,status=1,sell_status=0,wm_food_spu_id=498507922,poi_id=82096266,limit_stock=1,stock=0,ori_price=0.0,min_order_count=1,min_order_count_unit='?',box_num=0.0,box_price=0.0,tag_id='57926199',tag_name='???????',label_ids='',shipping_time_x='-',food_id=0,source_food_code='42223',upc_code='',spec='',description='????????????',sequence=1,locator_code='',spu_sequence=1,weight=0,scn=null,hash_code='1100940DC4957E0272C9D307FE351FF8',utime=unix_timestamp() where id=544647196

看下 EXPLAIN：

+----+-------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table          | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1  | SIMPLE      | wm_sku_data_57 |            | const | PRIMARY       | PRIMARY | 8       | const | 1    | 100.00   |       |
+----+-------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

慢查询的信息：

1 2	Query_time_pct_95: 0.19402465571876754 Query_time_median: 0.13474452702397088

能看到的慢查询中可疑部分就是：

Bytes_pct_95：742.7619171652906	
Bytes_median：655.9474754716242
``` 
其他指标都比较正常。看了下慢查询的分布时间，基本与 DB 的写高峰相同。只能猜测是这个原因，同时引入两个问题：
- 写高峰导致某个正常 SQL 变慢，应该从哪个指标能看出来呢？为什么 `InnoDB_IO_r_wait` 指标没有明显的增加？
## 查询返回行太多
```sql
select id,wm_poi_id,wm_food_spu_id,data_category_ids from wm_sku_data_1 where wm_poi_id = 3677501 and valid = 1

explain

+----+-------------+---------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table         | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| 1  | SIMPLE      | wm_sku_data_1 |            | ref  | idx_wmpoiid   | idx_wmpoiid | 8       | const | 3561 | 10.00    | Using where |
+----+-------------+---------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+

在 EXPLAIN 中就能看到，对应问题是：

rows 过多，且 filter 比较小

找其他参数验证下：

Rows_sent_pct_95: 1133.9412835303774
Rows_sent_median: 706.0977712590644
Rows_examined_pct_95: 1184.5020402948908
Rows_examined_median: 723.9766940664738
InnoDB_IO_r_ops_pct_95: 347.23657275260763
InnoDB_IO_r_bytes_pct_95: 5690091.345744681
InnoDB_IO_r_wait_pct_95: 0.14782536392437018
Query_time_pct_95：0.17730173072282304
InnoDB_pages_distinct_pct_95: 592.4017782008394

主要开销确定是由于读取的行数过多导致的，建议增加limit采用分页的方式来完成该查询。

an delete demo

1	explain delete from wm_sku_data_0 where id = 95661103;

对应 EXPLAIN

1 2	id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 DELETE wm_sku_data_0 None range PRIMARY PRIMARY 8 const 1 100.0 Using where

很有意思，跟之前 INSERT 的 range = ALL 有区别；并且这个 SQL 出现的时间点，跟 DB 的写高峰压力基本差不多，且 InnoDB_IO_r_wait 参数也没有明显的问题。

TODO：待补充原因

EXPLAIN 中的错误信息

1	select id,data_type,sub_data_type,object_type,object_id,content,error_des,hash_code,num,utime,ctime from error_data where hash_code='E811E20343C0150E83873462B8BDC6F7' order by utime limit 1

+----+-------------+------------+------------+-------+---------------+-----------+---------+-----+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key       | key_len | ref | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+-----------+---------+-----+------+----------+-------------+
| 1  | SIMPLE      | error_data |            | index |               | idx_utime | 8       |     | 1    | 10.00    | Using where |
+----+-------------+------------+------------+-------+---------------+-----------+---------+-----+------+----------+-------------+

table-sql

CREATE TABLE `error_data` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键自增长',
  `data_type` varchar(50) NOT NULL COMMENT '数据类型，需加索引，如m2s-sku',
  `sub_data_type` varchar(50) DEFAULT NULL COMMENT '数据子类型，data_type=m2s时为具体表名，如wm_sku_data_0',
  `object_type` varchar(50) DEFAULT NULL COMMENT '对象类型',
  `object_id` varchar(50) DEFAULT NULL COMMENT '对象id',
  `content` text NOT NULL COMMENT '数据内容，data_type=m2s时为wm_sku_data表的主键集合，json格式，如[1,2]',
  `error_des` varchar(255) DEFAULT NULL COMMENT '异常描述,如e.getMessage',
  `hash_code` varchar(50) DEFAULT NULL COMMENT '异常唯一标识码,data_type、sub_data_type、object_type、object_id、content 的MD5值',
  `num` int(11) DEFAULT '1' COMMENT '异常唯一标识码编码一致，num自增1',
  `ctime` bigint(20) NOT NULL COMMENT '创建时间，时间戳',
  `utime` bigint(20) NOT NULL COMMENT '最后修改时间',
  PRIMARY KEY (`id`),
  KEY `idx_data_type_object_type` (`data_type`,`sub_data_type`,`object_type`,`object_id`),
  KEY `idx_utime` (`utime`)
) ENGINE=InnoDB AUTO_INCREMENT=44680 DEFAULT CHARSET=utf8mb4 COMMENT='错误数据信息'

查询的慢日志：

/*id:c119a791*/select id,data_type,sub_data_type,object_type,object_id,content,error_des,hash_code,num,utime,ctime from error_data where hash_code='E4E1146C92231B13800CA614EB9ED00B' order by utime limit 1;
# Time: 2018-02-01T08:48:22.156612+08:00
# User@Host: cdatacen_efa9_r[cdatacen_efa9_r] @  [10.5.204.197]  Id: 34557804
# Schema: cdatacenter  Last_errno: 0  Killed: 0
# Query_time: 0.129067  Lock_time: 0.000097  Rows_sent: 0  Rows_examined: 34894  Rows_affected: 0
# Bytes_sent: 807  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
# QC_Hit: No  Full_scan: Yes  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
# Filesort: No  Filesort_on_disk: No  Merge_passes: 0
#   InnoDB_IO_r_ops: 0  InnoDB_IO_r_bytes: 0  InnoDB_IO_r_wait: 0.000000
#   InnoDB_rec_lock_wait: 0.000000  InnoDB_queue_wait: 0.000000
#   InnoDB_pages_distinct: 2678

看慢日志可知，基本是一个全表遍历，最后因为没有 match，而没有实际发送数据。更可怕的是InnoDB_pages_distinct 到了 2678！虽然这不是个准确值但是表示读取的 pages 也很多了。按照这个 SQL 去分析，执行计划其实没错，只不过根据 limit 错误的估计了 rows 的数量。一定要通过 HASH 去比较的话，就要增加针对 hashcode 字段的索引。

扩展阅读

MySQL 支持 INSERT / UPDATE / DELETE 的 EXPLAIN 查看
- 更详细的内容可以在 git log 中搜索 WL#4897
- 其中主 commit 为：326cf3e3c455664aa06ca65ffcdfc5d1ed3dff33
MariaDB内核开发Sergey Petrunia’s blog