定位慢sql
1、查看慢查询日志确定已经执行完的慢查询
mysql> set global slow_query_log = on;
mysql> set global long_query_time = 1;
mysql> show global variables like "datadir";
mysql> show global variables like "slow_query_log_file";
[root@mysqltest ~]# tail -n5 /data/mysql/data/3306/mysql-slow.log
Time: 2019-05-21T09:15:06.255554+08:00
User@Host: root[root] @ localhost [] Id: 8591152
Query_time: 10.000260 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1558401306;
select sleep(10);可以看到查询时间、表锁时间、扫描行数Rows_examined。
在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟rows_examined并不是完全相同的。
2、show processlist 查看正在执行的慢查询
mysql> show processlist\G`
`*************************** 10. row ***************************`
`Id: 7651833`
`User: one`
`Host: 192.168.1.251:52154`
`db: ops`
`Command: Query`
`Time: 3`
`State: User sleep`
`Info: select sleep(10)`
`......`
`10 rows in set (0.00 sec)`可以看到执行时间和SQL语句
分析慢查询
1、使用 explain 分析慢查询
获取 MySQL 中 SQL 语句的执行计划.需要重点关注以下：
select_type ：查询类型，是简单还是复杂查询
type ：查询的表连接类型
key ：实际选择的索引
rows： 预计需要扫描的行数，对 InnoDB 来说，这个值是估值
Extra ：附加信息
| select_type | 含义 |
|---|---|
| SIMPLE | 简单查询，不使用关联查询或子查询 |
| PRIMARY | 如果包含关联查询或者子查询，则最外层的查询部分标记为primary |
| UNION | 联合查询中第二个及后面的查询 |
| DEPENDENT UNION | 满足依赖外部的关联查询中第二个及以后的查询 |
| UNION RESULT | 联合查询的结果 |
| SUBQUERY | 子查询中的第一个查询 |
| DEPENDENT SUBQUERY | 子查询中的第一个查询，并且依赖外部查询 |
| DERIVED | 用到派生表的查询 |
| MATERIALIZED | 被物化的子查询 |
| UNCACHEABLE SUBQUERY | 一个子查询的结果不能被缓存，必须重新评估外层查询的每一行 |
| UNCACHEABLE UNION | 关联查询第二个或后面的语句属于不可缓存的子查询 |
| type | 含义 |
|---|---|
| system | 查询对象表只有一行数据 |
| const | 基于主键或唯一索引查询，最多返回一条结果 |
| eq_ref | 最多只返回一条符合条件的记录。 |
| ref | 基于普通索引的等值查询，或者表间等值连接 |
| fulltext | 全文检索 |
| ref_or_null | 表连接类型是 |
| index_merge | 利用多个索引 |
| unique_subquery | 子查询中使用唯一索引 |
| index_subquery | 子查询中使用普通索引 |
| range | 利用索引进行范围查询 |
| index | 全索引扫描 |
| ALL | 全表扫描 |
| Extra | 解释 |
|---|---|
| Using filesort | 将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序 |
| Using temporary | 需要创建一个临时表来存储结构，通常发生对没有索引的列进行 GROUP BY 时 |
| Using index | 使用覆盖索引 |
| Using where | 使用 where 语句来处理结果 |
| Impossible WHERE | 对 where 子句判断的结果总是 false 而不能选择任何数据 |
| Using join buffer（BlockNested Loop） | 关联查询中，被驱动表的关联字段没索引 |
| Using index condition | 先条件过滤索引，再查数据 |
| Select tables optimized away | 使用某些聚合函数（比如 max、min）来访问存在索引的某个字段 |
2、show profile
了解 SQL 执行过程的资源使用情况，能让我们知道到底慢在哪个环节
mysql> select @@have_profiling;
mysql> select @@profiling;
mysql> set profiling=1;
mysql> select * from t1 where b=1000;
mysql> show profiles; /*获取query id*/
mysql> show profile for query 1;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000115 |
| checking permissions | 0.000013 |
| Opening tables | 0.000027 |
| init | 0.000035 |
| System lock | 0.000017 |
| optimizing | 0.000016 |
| statistics | 0.000025 |
| preparing | 0.000020 |
| executing | 0.000006 |
| Sending data | 0.000294 |
| end | 0.000009 |
| query end | 0.000012 |
| closing tables | 0.000011 |
| freeing items | 0.000024 |
| cleaning up | 0.000016 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)3、trace
使用 trace 查看优化器如何选择执行计划
mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on;
/* optimizer_trace="enabled=on" 表示开启 trace；end_markers_in_json=on 表示 JSON 输出开启结束标记 */
mysql> select * from t1 where a >900 and b > 910 order by a;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE\G;
TRACE包括准备阶段、优化阶段、执行阶段。trace中可以看到使用每个索引的成本。
也可以看number_of_tmp_files中判断是否使用了临时文件。
