SQL优化之慢查询日志和explain

优化概述

数据库性能取决于数据库级别的多个因素，例如表、查询和配置设置。这些软件构造会导致硬件级别的 CPU 和 I/O 操作，您必须将其最小化并尽可能高效。

典型用户的目标是从其现有软件和硬件配置中获得最佳数据库性能。高级用户寻找机会改进MySQL软件本身，或开发自己的存储引擎和硬件设备来扩展MySQL生态系统。
（1）在数据库级别进行优。
（2）在硬件级别进行优化。
（3）平衡便携性和性能。

优化器成本

MySQL 优化器主要针对 IO 和 CPU 会计算语句的成本；可能不会按照分析的原理来执行语句。

成本分析步骤：
（1）找出所有可能需要使用到的索引。
（2）计算全表扫描的代价。
（3）计算不同索引执行查询的代价。
（4）对比找出代价最小的执行方案。

EXPLAIN

用来查看 SQL 语句的具体执行过程。
原理：模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理 SQL 语句的。

执行计划

示例：

DROP TABLE IF EXISTS `covering_index_t`;
CREATE TABLE `covering_index_t` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255) DEFAULT NULL,
	`cid` INT(11) DEFAULT NULL,
	`age` SMALLINT DEFAULT 0,
	`score` SMALLINT DEFAULT 0,
	PRIMARY KEY (`id`),
	KEY `name_cid_idx` (`name`, `cid`)
)ENGINE = INNODB AUTO_INCREMENT=0 DEFAULT CHARSET = utf8;


INSERT INTO `covering_index_t` (`name`, `cid`, `age`, `score`)
VALUES
	('FLY', 10001, 12, 99),
	('fly', 10002, 13, 98),
	('cc', 10003, 14, 97),
	('qq', 10004, 15, 100);

EXPLAIN SELECT * FROM `covering_index_t` WHERE `name` = 'mark';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

id

select 查询的序列号，包含一组数字，表示查询中执行select 子句或者操作表的顺序。
id 号分为三种情况：

1. id 相同，那么执行顺序从上到下。
2. id 不同，id 越大越先执行。
3. id 有相同的也有不同的，id 相同的按 1 执行，id 不同的按 2 执行。

select_type

主要用来分辨查询的类型，是普通查询还是联合查询还是子查询。

table

对应行正在访问哪一个表，表名或者别名，可能是临时表或者union 合并结果集。

1. 具体表名或者表的别名，从具体的物理表中获取数据。
2. 表明为 derivedN 的形式，表示 id 为 N 的查询产生的衍生表。
3. 当有 union result 的时候，表名是 union n1,n2 等的形式，n1,n2 表示参与 union的 id。

type

type 显示访问类型；采用怎么样的方式来访问数据；效率从好到坏依次为：

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

possible_keys

查询涉及到字段的索引，则这些索引都会列举出来，但是不一定采纳。

key

实际使用的索引，如果为 NULL，则没有使用索引。

key_len

表示索引中使用的字节数；查询中使用的索引长度；在不损失精度的情况下长度越短越好。

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。

rows

大致估算出找出所需记录需要读取的行数，反映了sql找了多少条数据，该值越小越好。

extra

额外信息。

优化器选择过程

优化器根据解析树可能会生成多个执行计划，然后选择最优的的执行计划。

SHOW VARIABLES LIKE 'optimizer_trace';
-- 启用优化器的追踪
SET optimizer_trace='enabled=on';
-- 执行一条查询语句
SELECT * FROM information_schema.optimizer_trace;
-- 用完关闭
SET optimizer_trace="enabled=off";
SHOW VARIABLES LIKE 'optimizer_trace';
1
2
3
4
5
6
7
8

慢日志查询

慢查询日志由执行时间超过 long_query_time 秒且至少需要检查 min_examined_row_limit 行的 SQL 语句组成。慢查询日志可用于查找需要很长时间才能执行的查询，因此是优化的候选项。但是，检查较长的慢查询日志可能是一项耗时的任务。为了简化此操作，您可以使用 mysqldumpslow 命令来处理慢查询日志文件并汇总其内容。

慢查询日志参数

long_query_time的最小值和默认值分别为 0 和 10。可以将该值指定为微秒的分辨率。

缺省情况下，不记录管理语句，也不记录不使用索引进行查找的查询。可以使用log_slow_admin_statements和log_queries_not_using_indexes更改此行为，默认情况下，慢查询日志处于禁用状态。

开启

（1）查看：

SHOW GLOBAL VARIABLES LIKE 'slow_query%';
SHOW GLOBAL VARIABLES LIKE 'long_query%';
1
2

（1）设置：

SET GLOBAL slow_query_log = ON;  -- on 开启 off，关闭
SET GLOBAL long_query_time = 4;   -- 单位秒；默认 10s；此时设置为4s
1
2

或者修改配置：

slow_query_log = ON
long_query_time = 4
slow_query_log_file = D:/mysql/mysql57-slow.log
1
2
3

mysqldumpslow

查找最近10条慢查询日志：

mysqldumpslow -s t -t 10 -g 'select' D:/mysql/mysql57-slow.log
1

总结

当出现SQL比较慢时，需要进行如下步骤进行优化：
（1）找到SQL语句。通过show processlist和开启慢查询日志。
（2）分析SQL语句。
a. 查看where、group by、order by里面的字段是否创建了索引。
b. in 优化成联合查询，减少联合查询等。