MySQL优化之分析SQL执行过程(explain、show profile、trace)
官方文档:Understanding the Query Execution Plan
SQL优化的一般步骤:先查询mysql数据库运行状况,然后定位慢查询,再分析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
我们可以通过mysql来记录慢查询,一旦有sql执行时间超过了设置的慢查询时间,就会被记录到慢查询日志中。这样我们就可以从慢查询日志中定位慢查询sql,然后进行分析优化。
查看慢查询相关信息
//显示慢查询次数 SHOW STATUS LIKE 'slow_queries' //显示慢查询时间,默认为10s SHOW VARIABLES LIKE 'long_query_time'
mysql的慢查询默认是关闭的,可以通过修改配置文件或通过命令来开启。
①修改配置文件方式
Linux下修改my.cnf,Windows下修改my.ini。修改后需要重启mysql才会生效。
#开启慢查询 slow-query-log=1 #慢查询的文件路径 slow_query_log_file="D:/Program Files/MySQL/Log/mysql-slow.log" #慢查询时间。默认为10秒 long_query_time=10
②命令方式
也可以使用命令来修改。
【session级别】 #开启慢查询 SET slow_query_log='ON'; #设置慢查询日志存放位置 SET slow_query_log_file='/usr/local/mysql/data/slow.log'; #设置慢查询时间 SET long_query_time=3 【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
3.分析慢查询
在实际生产环境中,可能因为开发写了不正确的SQL语句,索引优化的不好,或其他查询操作而导致数据库整体性能下降。我们只需要分析一下慢查询日志就会知道问题出在哪。
//查看是否启用慢日志记录和状态 show variables like "%slow%"
如果慢查询日志中记录内容较多,则可以使用Mysql自带的慢查询日志分析工具mysqldumpslow工具来对慢查询日志进行分类汇总。该工具位于/mysql/bin目录下。mysqldumpslow将会自动将文本完全一致但变量不同的SQL语句视为同一个语句进行统计,变量值用N来代替。
mysqldumpslow -s r -t 10 /data/dbdata/frem-slow.log
另外,show processlist也是一个常用命令。
官方文档:13.7.5.29 SHOW PROCESSLIST Statement
show full processlist
show processlist 是显示用户正在运行的线程,需要注意的是,除了 root 用户能看到所有正在运行的线程外,其他用户都只能看到自己正在运行的线程,看不到其它用户正在运行的线程。除非单独个这个用户赋予了PROCESS 权限。
可以参考:show processlist 详解
二、使用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:最多只有一个匹配行,所以该行中的其它列的值可以当作常量来处理。例如,根据主键primary key或唯一索引unique index进行查询。简单地说const就是直接按主键或唯一键取值。例如在②中介绍system时的举例中user表的访问类型就是const,其通过主键来取值。
④eq_ref:使用唯一索引,对于每个索引键值,表中只有一条记录匹配。简单说,就是多表连接中使用primary key或unique index作为关联条件。
注意const和eq_ref的区别:简单地说是const
是直接按主键或唯一键读取,eq_ref
用于联表查询的情况,按联表的主键或唯一键联合查询。
⑤ref:使用非唯一索引,或唯一索引的前缀扫描,返回匹配某个单独值的所有行(可能匹配多个行)。
ref还经常出现在join操作中
⑥ref_or_null:与ref类似,区别在于条件中包含对NULL的查询。
⑦index_merge:索引合并优化。
⑧unique_subquery:in的后面是一个查询主键字段的子查询。
⑨index_subquery:与unique subquery类似,区别在于in的后面是查询非唯一索引字段的子查询。
⑩range:只检索指定范围的行,使用一个索引来选择行。常见于<,<=,>,>=,between或者IN操作符。
key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。
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 、 UNION 等子句的查询过程中,如果不能有效利用索引来完成查询, 就需要建立内部的临时表来执行查询。
三、使用show profile分析sql(已过时)
官方文档:SHOW PROFILE Statement和SHOW PROFILES Statement
有时仅通过explain分析执行计划并不能很快地定位SQL的问题,这时就可以选择profile联合分析。Mysql从5.0.37开始增加了对show profiles和show profile语句的支持。
默认profile是关闭的,可以通过set开启session级别的profiling。
//查看是否支持profile SELECT @@have_profiling //开启profile(session级别) set profiling=1;
①执行select语句
②show profiles,查询该sql语句的Query ID.
③通过show profile for query语句能看到执行中线程的每个状态和消耗的时间。
show profile for query [上面的query id]
在MySQL5.7中,show profile 命令已经开始不推荐使用,可以使用performance_schema 中系统表的信息来替代。
官方文档:24.19.1 Query Profiling Using Performance Schema
四、通过trace分析优化器如何选择执行计划
官方文档:Chapter 8 Tracing the Optimizer
Mysql5.6提供了对sql的跟踪trace,通过trace文件能够进一步了解为什么优化器选择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就知道mysql是如何执行sql的了 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; # 完成后,关闭trace SET optimizer_trace="enabled=off";
最后会输出一个跟踪文件。