MySQL优化总结之分析SQL执行过程(explain、show profile、trace)
一、定位慢查询
在实际生产环境中，可能因为开发写了不正确的SQL语句，索引优化的不好，或其他查询操作而导致数据库整体性能下降。我们可以开启mysq慢查询，一旦有sql执行时间超过了设置的慢查询时间，就会被记录到慢查询日志中。这样我们就可以从慢查询日志中定位慢查询sql，然后分析慢查询日志就会知道问题出在哪了。
1.使用show status查询数据库的运行状况
//显示数据库运行状态 SHOW STATUS //显示数据库运行总时间 SHOW STATUS LIKE 'uptime' //显示连接的次数 SHOW STATUS LIKE 'connections' //显示执行CRUD的次数 SHOW STATUS LIKE 'com_select' SHOW STATUS LIKE 'com_insert' SHOW STATUS LIKE 'com_update' SHOW STATUS LIKE 'com_delete'
2.定位慢查询SQL
查看慢查询相关信息
//显示慢查询次数 SHOW STATUS LIKE 'slow_queries' //显示慢查询时间，默认为10s SHOW VARIABLES LIKE 'long_query_time'
mysql的慢查询默认是关闭的，慢查询日志开启后对Mysql的整体性能或多或少都会造成负面影响，如果没有调优的需求，不建议开启慢查询日志记录。
可以通过操作终端开启session级别慢查询，也可以在配置文件中修改永久生效。
【session级别】 #开启慢查询 SET slow_query_log='ON'; #设置慢查询日志存放位置 SET slow_query_log_file='/usr/local/mysql/data/slow.log'; #设置慢查询时间 SET long_query_time=10 【global级别】 SET global slow_query_log='ON'; SET global slow_query_log_file='/usr/local/mysql/data/slow.log'; SET global long_query_time=3
如果是修改配置文件，修改后需要重启mysql才会生效。Linux下修改my.cnf，Windows下修改my.ini。
#开启慢查询
slow-query-log=1
#慢查询的文件路径
slow_query_log_file="D:/Program Files/MySQL/Log/mysql-slow.log"
#慢查询时间。默认为10秒
long_query_time=10
3.分析慢查询
//查看是否启用慢日志记录和状态 show variables like "%slow%"
如果慢查询日志中内容较多，可以使用Mysql自带的慢查询日志分析工具mysqldumpslow来对慢查询日志进行分类汇总。mysqldumpslow会自动将文本完全一致但变量不同的SQL语句视为同一个语句进行统计，变量值用N来代替。
mysqldumpslow -s r -t 10 /data/dbdata/frem-slow.log
二、使用explain查看SQL执行计划
官方文档：Optimizing Queries with EXPLAIN
mysql会将慢查询记录到慢查询日志中，这时我们就可以针对这些慢查询的sql进行分析和优化，需要用到explain命令。
explain [要分析的sql]
分析结果中有如下几列：
+----+-------------+---------+------+---------------+------+---------+------+------+-------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+-------+
下面介绍各列的含义。可以参考官方文档说明：Explain Output Colums
id
表示select查询序列号。id值越大，越优先执行。如果id相同，执行顺序由上至下；
select_type
表示查询操作的类型。主要用于区分普通查询、子查询、联合查询等几种查询情况。有这些取值：simple，primary，subquery，derived，union，union result
①simple：表示简单查询，只有一个select操作，即不使用连接和union。
#只有一个select操作，所以都是简单查询 select id from emp; select id from emp join dept on emp.dept_id=dept.id;
②primary：表示主查询。子查询语句中的最外层select，或union操作的第一个select。
#子查询形式：第一个select操作为primary select * from app_school where id = (select id from app_school where id=100); #union形式：第一个select操作为primary select * from app_school where id=100 union select * from app_school where id=101;
③subquery：表示子查询。子查询语句中的内层select。
#第二个select操作为subquery select * from app_school where id = (select id from app_school where id=100);
④derived：表示FROM后跟着的select查询，会被标记为derived(导出表/衍生表)。
#第二个select操作为derived select * from (select id from app_school) t;
⑤union：表示UNION操作后面的select查询。
#第二个select操作为union select * from app_school where id=100 union select * from app_school where id=101;
⑥union result：表示获取UNION最后结果的查询。
#第一个select操作为primary #第二个select操作为union #获取最终结果的操作为union result select * from app_school where id=100 union select * from app_school where id=101;
table
表示查询用到的表。
type
表示找到匹配行用到的访问类型。最为常见的类型有system，const，eq_ref，ref，range，index，All，按照性能从高到低顺序如下：NULL-->system-->const-->eq-ref-->ref-->range-->index-->All 。一般来说，要让查询至少达到range级别，最好能达到ref级别。
①NULL：不用访问表或索引，就可直接得出结果。
②system：该表仅有一行(等同于系统表)，这是const类型的一个特例。系统表中的数据通常已经加载到了内存中，所以不需要磁盘IO。
例子1：查询系统表
例子2：内层嵌套(const)返回了一个临时表，外层嵌套从临时表中查询，其扫描类型也是system，也不需要磁盘IO。
③const：最多只有一个匹配行，在查询开始时读取。因为只有一行，所以该行中列的值可以被优化器当作常量来处理。const表非常快，因为只会读取一次。
当使用primary key或unique index进行查询时就会用到const。简单地说const就是直接按主键或唯一键取值。
例如，上例的第二个例子，payment表的访问类型就是const，其通过主键来取值。此时payment表可以当做const表使用。
④eq_ref：使用唯一索引，对于每个索引键值，表中只有一条记录匹配。简单说，就是多表连接中使用primary索引或unique索引(NOT NULL)作为关联条件。这是除了system和const外最好的连接类型。
eq_ref被用于使用=操作符比较的索引列，比较值可以是常量或表达式，该表达式会使用表中的列，该列会在此表之前读取。
注意const和eq_ref的区别：简单地说const是直接按主键或唯一键读取，eq_ref用于联表查询的情况，按联表的主键或唯一键联合查询。
⑤ref：使用非唯一索引，或唯一索引的前缀扫描，返回匹配某个单独值的所有行(可能匹配多个行)。ref 可用于使用 = 或 <=> 运算符比较的索引列。
ref还经常出现在join操作中
⑥ref_or_null：与ref类似，区别在MySQL会额外搜索含有NULL值的行。这种连接类型优化在解析子查询时最常使用。
SELECT * FROM t WHERE key_column=expr OR key_column IS NULL;
⑦index_merge：索引合并优化。explain输出中key列显示使用的索引列表，key_len列包含所用到的索引的最长关键元素列表。
⑧unique_subquery：取代eq_ref，用于IN后面是一个查询主键字段的子查询。 unique_subquery只是一个索引查找函数，完全取代子查询，以提高工作效率。
value IN (SELECT primary_key FROM single_table WHERE some_expr)
⑨index_subquery：与unique subquery类似，取代了IN子查询，用于IN的后面是查询非唯一索引字段的子查询。
value IN (SELECT key_column FROM single_table WHERE some_expr)
⑩range：只检索指定范围的行，使用索引来选择行。key列显示使用了哪个索引，key_len列包含所使用索引的最长关键元素。用于key列与常量的比较，可以是=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE或者IN()操作。
SELECT * FROM tbl_name WHERE key_column = 10; SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20; SELECT * FROM tbl_name WHERE key_column IN (10,20,30); SELECT * FROM tbl_name WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
实例：
11.index：索引树全扫描。遍历整个索引树来查询匹配的行。
12.ALL：全表扫描，性能最差。
possible_keys和key
possible_keys表示查询时可能使用到的索引，而key表示实际选择使用的索引
key_len
表示使用到的索引列的长度
ref
表示该表的索引字段关联了哪张表的哪个列
rows
表示预估扫描行的数量。注意是预估扫描的行数，并不是实际扫描的行数，因为这是执行计划，只是执行前的一个评估决策。
Extra
表示执行情况的说明和描述，包含不适合在其它列中显示但对执行计划非常重要的额外信息。可以参考：MySQL中explain执行计划中额外信息字段(Extra)详解
记录几个重要的。更多内容参考官方文档：EXPLAIN Extra Information
- Using index：使用了覆盖索引，直接从索引中就能获取到结果。查询列表和查询条件只包含了某个索引中的列，直接通过索引就能获取到结果，不需要进行回表。
- Using where：在查找使用索引的情况下，索引并不能覆盖到需要查询的所有列，需要回表去查询所需的数据。(where条件中除了索引包含的列外，还有索引未包含的列)
- Using index condition：使用了索引条件下推(ICP)。MySQL的架构中分成了server层和引擎层。索引下推(ICP)其实就是实现了index filter技术，将原来的在server层的table filter中可以进行的过滤操作，移到引擎层面使用index filter进行处理，不再需要回表进行table filter。
- Using filesort：使用了文件排序。当查询语句包含ORDER BY时，如果无法使用索引来完成排序，则需要进行额外的排序操作。
- Using temporary：使用临时表来保存中间结果，用于完成排序、去重等操作。比如我们在执行许多包含 DISTINCT 、 GROUP BY 、ORDER BY、 UNION 等子句的查询过程中，如果不能有效利用索引来完成查询， 就需要建立内部的临时表来执行查询。
- Using index for group-by：待补充！待补充！待补充！
三、使用trace分析优化器如何选择执行计划
官方文档：Tracing the Optimizer和tracing-example
Mysql5.6提供了对sql的跟踪，能够进一步了解为什么优化器选择A执行计划而不选择B执行计划，帮助我们更好地理解优化器的行为。下面是典型的使用方式：
# 开启trace(默认关闭)，设置json格式 SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on; //设置内存，避免解析过程中因为默认内存过小而不能够完整显示。 SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000; # 执行sql SELECT ...; # your query here # 检查INFORMATION_SCHEMA.OPTIMIZER_TRACE查看跟踪信息 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE \G; # 关闭trace SET optimizer_trace="enabled=off";
也可以将上面的跟踪信息输出到文件中，方便我们阅读。
SELECT TRACE INTO DUMPFILE <filename> FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
可以直接参考官方给出了案例：tracing-example
四、对SQL性能分析
除了通过explain分析SQL的执行计划外，还可以对SQL进行性能分析。MySQL提供了show profiles和show profile命令，可以用来获取SQL执行过程中的资源使用和耗时情况，对于分析SQL的性能瓶颈有非常大的帮助。但是这个功能在MySQL5.7中已经被废弃了，取而代之的是使用Performance Schema(系统表信息)来提供同样的功能。不过令人诟病的是，performance schema表众多，这些表和 information_schema 中的部分表也缠夹不清，让大家用得很不习惯。
使用show profile(被Performance Schema取代)
官方文档：SHOW PROFILE Statement和SHOW PROFILES Statement
show profile的使用步骤如下：
#0.查看是否支持profile SELECT @@have_profiling #1.开启session级别的profile(默认关闭) set profiling=1; #2.执行select语句 select ……
#3.查看该sql语句的Query ID show profiles
#4.查看执行中线程的每个状态和消耗的时间 show profile for query [上面的Query ID]
Performance Schema
官方文档：Performance Schema和Query Profiling Using Performance Schema
1.开启Performance Schema功能
该功能默认是开启的，对应的配置文件如下
[mysqld] performance_schema=ON
2.配置
配置表setup_actors
默认情况下，performance_schema功能打开后，将会收集所有用户的SQL执行历史事件，因为收集的信息太多，对数据库整体性能有一定影响，而且也不利于排查指定SQL的性能问题，因此需要修改setup_actors表的配置，只收集特定用户的历史事件信息。setup_actors表配置如下：
SELECT * FROM performance_schema.setup_actors; +------+------+------+---------+---------+ | HOST | USER | ROLE | ENABLED | HISTORY | +------+------+------+---------+---------+ | % | % | % | YES | YES | +------+------+------+---------+---------+ UPDATE performance_schema.setup_actors SET ENABLED = 'NO', HISTORY = 'NO' WHERE HOST = '%' AND USER = '%'; INSERT INTO performance_schema.setup_actors (HOST,USER,ROLE,ENABLED,HISTORY) VALUES('localhost','test_user','%','YES','YES'); SELECT * FROM performance_schema.setup_actors; +-----------+-----------+------+---------+---------+ | HOST | USER | ROLE | ENABLED | HISTORY | +-----------+-----------+------+---------+---------+ | % | % | % | NO | NO | | localhost | test_user | % | YES | YES | +-----------+-----------+------+---------+---------+
配置表setup_instruments
启用statement和stage监视器。
UPDATE performance_schema.setup_actors SET ENABLED = 'NO', HISTORY = 'NO' WHERE HOST = '%' AND USER = '%'; INSERT INTO performance_schema.setup_actors (HOST,USER,ROLE,ENABLED,HISTORY) VALUES('localhost','test_user','%','YES','YES');
配置表setup_consumers
启用events_statements_*，events_stages_* 开头的事件类型消费。
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_statements_%'; UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_stages_%';
3.收集具体SQL的性能分析
执行SQL语句
SELECT * FROM employees.employees WHERE emp_no = 10001; +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | +--------+------------+------------+-----------+--------+------------+
获取SQL的事件ID
SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%10001%'; +----------+----------+--------------------------------------------------------+ | event_id | duration | sql_text | +----------+----------+--------------------------------------------------------+ | 31 | 0.028310 | SELECT * FROM employees.employees WHERE emp_no = 10001 | +----------+----------+--------------------------------------------------------+
获取各阶段执行耗时
SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=31; +--------------------------------+----------+ | Stage | Duration | +--------------------------------+----------+ | stage/sql/starting | 0.000080 | | stage/sql/checking permissions | 0.000005 | | stage/sql/Opening tables | 0.027759 | | stage/sql/init | 0.000052 | | stage/sql/System lock | 0.000009 | | stage/sql/optimizing | 0.000006 | | stage/sql/statistics | 0.000082 | | stage/sql/preparing | 0.000008 | | stage/sql/executing | 0.000000 | | stage/sql/Sending data | 0.000017 | | stage/sql/end | 0.000001 | | stage/sql/query end | 0.000004 | | stage/sql/closing tables | 0.000006 | | stage/sql/freeing items | 0.000272 | | stage/sql/cleaning up | 0.000001 | +--------------------------------+----------+
五、show processlist
官方文档：SHOW PROCESSLIST Statement
show processlist / show full processlist 也是一个常用命令，可以用来查看当前mysql 是否有压力，都在跑什么语句，当前语句耗时多久了，有没有什么慢 SQL 正在执行之类的。
可以看到总共有多少链接数，用户正在运行的线程，哪些线程有问题。其显示的信息都是来自系统库information_schema中的processlist 表。
show full processlist
需要注意，除了 root 用外，用户只能看到自己正在运行的线程，除非单独为这个用户赋予PROCESS 权限。
