SQL 语句分析
当我们进行业务开发的过程中，一定会和数据库做数据上的交互。但是有的时候我们开发完一个功能之后，发现这个功能执行的时间特别长。那我们就要考虑是不是我的 SQL 出现了一些问题，如果运维给你抛过来一句有问题 SQL，我们要怎么分析这条 SQL 语句呢？
接下来我们就来看下 MySQL 的执行计划。
Explain 分析 SQL 语句
大家都知道我们身体不舒服的时候，会去医院检查一下身体，医生会根据你的描述给你做各种检查，根据检查的结果，推测出你的问题。
那我们在面对有可能出现问题的 SQL，能不能也能像医生一样，给 SQL 语句来一个体检单。这个就可以针对性的分析 SQL 语句。
答案是可以的，MySQL 为我们提供了 Explain 来分析 SQL 语句。接下来会给大家介绍：Explain 是什么、能干嘛？怎么玩？
Explain 是什么
使用 Explain 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。
Explain 可以获取 MySQL 中 SQL 语句的执行计划，比如语句是否使用了关联查询、是否使用了索引、扫描行数等。可以帮我们选择更好地索引和写出更优的 SQL 。
使用 Explain 也非常简单，在查询语句前面加上 Explain 运行就可以了。
Explain 能干嘛
- 表的读取顺序
- SQL 的查询类型
- 那些索引可以使用
- 那些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
大家可能看到这些，一脸蒙蔽，大家看完下面的案例，大家就可以理解了。
Explain 案例
建表：
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`other_column` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
insert into `t1`(`id`,`other_column`) values (1,'测试'),(2,'Juran ');
CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
insert into `t2`(`id`) values (1),(2);
CREATE TABLE `t3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`other_column` varchar(20) DEFAULT NULL,
`col1` varchar(20) DEFAULT NULL,
`col2` varchar(20) DEFAULT NULL,
`col3` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_col1_col2` (`col1`,`col2`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
insert into `t3`(`id`,`other_column`,`col1`,`col2`,`col3`) values (1,'',NULL,NULL,NULL);
我们先来写一条 SQL，给大家看下效果：
explain select * from t1;
Explain 执行计划包含字段信息如下：分别是 id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra12 个字段。
我用的 MySQL 版本是 5.7，如果大家的 MySQL 版本是 5.5 是看不到 partitions 和 filtered 这两列的。
id
id：表的读取顺序，select 查询的顺序号，包含一组数字，表示查询中执行 select 子句或操作表的顺序。
id 字段有三种情况：
- id 相同，执行顺序由上至下。
- id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行。
- id 相同不同，相同 id 划分为一组，同组的从上往下顺序执行，不同组 id 值越大，优先级越高，越先执行。
我们先来看第一种情况，id 相同：
explain select t2.* from t1,t2,t3 where t1.id = t2.id and t1.id = t3.id and t1.other_column='';
第二种情况，id 不同：
explain select t2.* from t2 where id=(select id from t1 where id=(select t3.id from t3 where t3.other_column=''));
第三种情况，比较少见，大家如果遇到可以看下我们上面的解释。
select_type
数据读取操作的操作类型，表示 select 查询的类型，主要是用于区分各种复杂的查询，例如：普通查询、联合查询、子查询等。
|
值 |
描述 |
|
SIMPLE |
简单的 SELECT 语句（不包括 UNION 操作或子查询操作） |
|
PRIMARY |
查询中最外层的 SELECT（如两表做 UNION 或者存在子查询的外层的表操作为 PRIMARY，内层的操作为 UNION） |
|
UNION |
UNION 操作中，查询中处于内层的 SELECT，即被 union 的 SELECT |
|
SUBQUERY |
子查询中的 SELECT |
|
DERIVED |
表示包含在 From 子句中的 Select 查询 |
|
UNION RESULT |
union 的结果，此时 id 为 NULL |
我们来给说几个比较常见的类型。
SIMPLE 类型
explain select * from t1;
PRIMARY 和 SUBQUERY 类型
explain select t2.* from t2 where id = (select id from t1 where id=(select t3.id from t3 where t3.other_column=''));
UNION 类型
explain select * from t2 union select * from t4; # t4 表和 t2 表结构相同
table
这个比较简单，显示这一行的数据时关于那张表的。
partitions
查询访问的分区，如果没有分区显示 NULL，如果表有分区，会显示查询的数据对应的分区
type
type：字段访问类型，它在 SQL 优化中是一个非常重要的指标，一共有 ALL、index、range、ref、eq_ref、const、system、NULL 这几种。
从好到坏依次是：
system > const > eq_ref > ref > range > index > ALL
一般来说得保证查询至少达到 range 级别，最好能达到 ref。当然我们也不可能要求所有的 SQL 语句都要达到 ref 这个级别，就像春运时候的火车票，有个坐就不错了.....
system，表只有一行记录（等于系统表），这是 const 类型的特例，平时不会出现,这个也可以忽略不计。
explain select * from mysql.db;
const 表示查询时命中 primary key 主键或者 unique 唯一索引，因为只匹配一行数据，所以很快。或者被连接的部分是一个常量（const）值。
explain select * from t1 where id = 1;
基本这种 SQL 在我们的业务场景中，出现的几率也比较少。
eq_ref，唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
explain select * from t1,t2 where t1.id = t2.id;
ref，非唯一性索引扫描，返回匹配某个单独值得所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行。
create index idx_col1_col2 on t3(col1,col2);
explain select * from t3 where col1 = 'ac';
range，只检索给定范围的行，使用一个索引来选择行。
一般就是在你的 where 语句中出现了 between、<、>、in 等查询
这种范围扫描比全表扫描要好，因为只需要开始于索引的某一点，结束另一点，不用扫描全部索引。
explain select * from t1 where id between 10 and 20;
explain select * from t1 where id in (1,3,6);
index，Full Index Scan，index 与 ALL 区别为 index 类型只遍历索引树。 这通常比 ALL 快，因为索引文件通常比数据文件小。Index 与 ALL 其实都是读全表，区别在于 index 是遍历索引树读取，而 ALL 是从硬盘中读取。
explain select id from t1;
ALL，将遍历全表找到匹配的行。
explain select * from t1 where other_column='';
possible_keys
显示可能应用在这张表中的索引，一个或多个。但不一定被查询实际使用。
key
实际使用的索引。如果为 null，则没有使用索引。
possible_keys 和 key 这两个大家可以理解为我们军训的时候，班级应到 30 人，实到 28 人。
explain select col1,col2 from t3;
key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。
索引长度计算：
varchr(24)变长字段且允许 NULL
24*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
varchr(10)变长字段且不允许 NULL
10*(Character Set：utf8=3,gbk=2,latin1=1)+2(变长字段)
char(10)固定字段且允许 NULL
10*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)
char(10)固定字段且不允许 NULL
10*(Character Set：utf8=3,gbk=2,latin1=1)
ref
显示索引那一列被使用到了，如果可能的话，是一个常数。那些列或常量被用于查找索引列上的值。
rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。这是评估 SQL 性能的一个比较重要的数据，mysql 需要扫描的行数，很直观的显示 SQL 性能的好坏，一般情况下 rows 值越小越好。
Extra
包含不适合在其他列中显示但十分重要的额外信息：
- Using filesort，说明 MySQL 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，MySQL 中无法利用索引完成的排序操作称为“文件排序”。
- Using temporary，使用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
- Using index，使用了索引，避免了全表扫描，效率不错。
- Using where，使用了 where 过滤。
- Using join buffer，使用了连接缓存。
- impossible where，不可能的条件，where 子句的值总是 false。
其中我们需要重点关注的是， Using filesort 和 Using temporary，如果 SQL 语句中出现了这两个一定要是去优化的。
我们先来看 Using filesort：
创建索引 idx_col1_col2_col3 在字段 col1,col2,col3
explain select * from t3 where col1 = 'ac' order by col3;
出现了文件内排序，我们建的索引 SQL 并没有用我们建的索引来进行排序，那要优化的话，也很简单。
修改索引，让 order by 按照索引的顺序来进行排序：
创建索引 idx_col1_col2 在字段 col1,col2
explain select * from t3 where col1 = 'ac' order by col3;
在来看出现了 Using temporary：
explain select * from t3 where col1 in ('ac','ab') group by col2;
使用临时表的话，在查询的时候，新建了一个临时表，把数据放到临时表中在查询，查询结果之后再把临时表删除。
更详细的信息，大家可以看 MySQL 的官方文档：
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#jointype_index_merge
参考文章链接：
Explain 执行计划详解
Show profile 进行 SQL 分析
我们聊完了 explain，通过 explain 我们可以知道，我们自己写的 SQL 到底有没有用到索引，以及字段的访问类型。那我们接下来聊的 Show profile 是用来帮助我们做什么的呢？
Show profile 是 MySQL 提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于 SQL 的调优的测量。
可能从概念上我们不好理解这个 Show profile，给大家举个例子。假如我们去超时购物买了 300 块钱的商品，那这 300 块钱我买了那些东西，我们可以通过消费的小票看到，我们的钱到底花在了哪里，这个 Show profile 大家可以想象成这个消费的小票。
有时需要确定 SQL 到底慢在哪个环节，此时 explain 可能不好确定。在 MySQL 数据库中，通过 Show profile，能够更清楚地了解 SQL 执行过程的资源使用情况，能让我们知道到底慢在哪个环节，是不是跟超时的消费票据有点像。
分析步骤
我们知道了 Show profile 是什么，那我们如何用 Show profile 来进行分析呢？
- 是否支持，看看当前 MySQL 版本是否支持
- 开启功能，默认是关闭,使用前需要开启
我们先来看 MySQL 是否支持 Show profile：
select @@have_profiling;
从上面结果中可以看出是 YES，表示支持 Show profile 的。
开启 Show profile 功能
默认是关闭的。
show variables like 'profiling';
开启参数：
set profiling = on;
show variables like 'profiling';
Show profile 示例
建表 SQL：
create table emp(
id int primary key auto_increment,
empno mediumint not null, -- 编号
ename varchar(20) not null, -- 名字
job varchar(9) not null, -- 工作
mgr mediumint not null, -- 上级编号
hiredate DATE not null, -- 入职时间
sal decimal(7,2) not null, -- 薪水
comm decimal(7,2) not null, -- 红利
deptno mediumint not null -- 部门编号
)engine=innodb default charset=gbk;
执行 SQL 语句：
select deptno from emp group by deptno limit 3;
select * from emp order by deptno limit 3;
select * from emp group by id%10 limit 150000;
查看 SQL 的 Query_ID：
show profiles;
我们可以先通过 explain 来查看 SQL：
explain select deptno from emp group by deptno limit 3;
根据 explain 分析，创建了临时表以及出现了文件内排序。
根据 Query_ID 查看 SQL 执行详情：
show profile cpu,block io for query 1;
大家看到上面的表格中 Creating tmp table，这就表示创建了临时表。通过这个表格我们可以清晰的看到我们的 SQL 语句到底在那一步执行花费的时间比较长。
show profile 后面除了可以查看 cpu、block io 信息，还可以查看
all 显示所有的开销信息
block io 显示块 IO 相关开销
cpu 显示 CPU 相关开销信息
ipc 显示发送和接收相关开销信息
memory 显示内存相关开销信息
page faults 显示页面错误相关开销信息
trace 分析 SQL 优化器
从前面学到了 explain 可以查看 SQL 执行计划，但是无法知道它为什么做这个决策，如果想确定多种索引方案之间是如何选择的或者排序时选择的是哪种排序模式，有什么好的办法吗？
从 MySQL 5.6 开始，可以使用 trace 查看优化器如何选择执行计划。
建表 SQL：
CREATE TABLE `t1` ( /* 创建表 t1 */
`id` int(11) NOT NULL auto_increment,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
PRIMARY KEY (`id`),
KEY `idx_a` (`a`),
KEY `idx_b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
存储过程插入测试数据：
delimiter ;;
create procedure insert_t1() /* 创建存储过程 insert_t1 */
begin
declare i int; /* 声明变量 i */
set i=1; /* 设置 i 的初始值为 1 */
while(i<=1000)do /* 对满足 i<=1000 的值进行 while 循环 */
insert into t1(a,b) values(i, i); /* 写入表 t1 中 a、b 两个字段，值都为 i 当前的值 */
set i=i+1; /* 将 i 加 1 */
end while;
end;;
delimiter ; /* 创建批量写入 1000 条数据到表 t1 的存储过程 insert_t1 */
call insert_t1(); /* 运行存储过程 insert_t1 */
我们知道了 trace 是什么，那我们如何用 trace 来进行分析呢？
- 需要开启 trace，设置格式为 JSON
- 执行需要分析的 SQL，查看 trace 分析结果
explain 分析创建的表 t1 做实验：
explain select * from t1 where a >900 and b > 910 order by a;
通过 explain 分析，可能用到索引 idx_a 和 idx_b，但是实际只用到了 idx_b，a 和 b 字段都是有索引的，为什么选择了 b 字段创建的索引而没有选择 a 字段创建的呢？
这时我们可以用 trace 来分析，开启 trace 功能，并设置格式为 JSON：
set session optimizer_trace="enabled=on",end_markers_in_json=on;
执行 SQL 语句：
select * from t1 where a >900 and b > 910 order by a;
查看 trace 分析结果：
select * from information_schema.OPTIMIZER_TRACE\G
\G 以表格的形式来显示结果，这个结果特别多，所以我们用 \G 来显示：
QUERY: select * from t1 where a >900 and b > 910 order by a --SQL 语句
TRACE: {
"steps": [
{
"join_preparation": { --SQL 准备阶段
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `t1`.`id` AS `id`,`t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`create_time` AS `create_time`,`t1`.`update_time` AS `update_time` from `t1` where ((`t1`.`a` > 900) and (`t1`.`b` > 910)) order by `t1`.`a`"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": { --SQL 优化阶段
"select#": 1,
"steps": [
{
"condition_processing": { --条件处理
"condition": "WHERE",
"original_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))", --原始条件
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))" --等值传递转换
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))" --常量传递转换
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))" --去除没有的条件后的结构
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */ --替换虚拟生成列
},
{
"table_dependencies": [ --表依赖详情
{
"table": "`t1`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [ --预估表的访问成本
{
"table": "`t1`",
"range_analysis": {
"table_scan": {
"rows": 1000, --扫描行数
"cost": 207.1 --成本
} /* table_scan */,
"potential_range_indexes": [ --分析可能使用的索引
{
"index": "PRIMARY",
"usable": false, --为 false，说明主键索引不可用
"cause": "not_applicable"
},
{
"index": "idx_a", --可能使用索引 idx_a
"usable": true,
"key_parts": [
"a",
"id"
] /* key_parts */
},
{
"index": "idx_b", --可能使用索引 idx_b
"usable": true,
"key_parts": [
"b",
"id"
] /* key_parts */
}
] /* potential_range_indexes */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"analyzing_range_alternatives": { --分析各索引的成本
"range_scan_alternatives": [
{
"index": "idx_a", --使用索引 idx_a 的成本
"ranges": [
"900 < a" --使用索引 idx_a 的范围
] /* ranges */,
"index_dives_for_eq_ranges": true, --是否使用 index dive（详细描述请看下方的知识扩展）
"rowid_ordered": false, --使用该索引获取的记录是否按照主键排序
"using_mrr": false, --是否使用 mrr
"index_only": false, --是否使用覆盖索引
"rows": 100, --使用该索引获取的记录数
"cost": 121.01, --使用该索引的成本
"chosen": true --可能选择该索引
},
{
"index": "idx_b", --使用索引 idx_b 的成本
"ranges": [
"910 < b"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 90,
"cost": 109.01,
"chosen": true --也可能选择该索引
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": { --分析使用索引合并的成本
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": { --确认最优方法
"range_access_plan": {
"type": "range_scan",
"index": "idx_b",
"rows": 90,
"ranges": [
"910 < b"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 90,
"cost_for_plan": 109.01,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [ --考虑的执行计划
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`t1`",
"best_access_path": { --最优的访问路径
"considered_access_paths": [ --决定的访问路径
{
"rows_to_scan": 90, --扫描的行数
"access_type": "range", --访问类型：为 range
"range_details": {
"used_index": "idx_b" --使用的索引为：idx_b
} /* range_details */,
"resulting_rows": 90, --结果行数
"cost": 127.01, --成本
"chosen": true, --确定选择
"use_tmp_table": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 90,
"cost_for_plan": 127.01,
"sort_cost": 90,
"new_cost_for_plan": 217.01,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": { --尝试添加一些其他的查询条件
"original_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`t1`",
"attached": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`t1`.`a`",
"items": [
{
"item": "`t1`.`a`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`t1`.`a`"
} /* clause_processing */
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"index_order_summary": {
"table": "`t1`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "idx_b",
"plan_changed": false
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
},
{
"refine_plan": [ --改进的执行计划
{
"table": "`t1`",
"pushed_index_condition": "(`t1`.`b` > 910)",
"table_condition_attached": "(`t1`.`a` > 900)"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": { --SQL 执行阶段
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`t1`",
"field": "a"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false, --未使用优先队列优化排序
"cause": "not applicable (no LIMIT)" --未使用优先队列排序的原因是没有 limit
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": { --排序详情
"rows": 90,
"examined_rows": 90, --参与排序的行数
"number_of_tmp_files": 0, --排序过程中使用的临时文件数
"sort_buffer_size": 115056,
"sort_mode": "<sort_key, additional_fields>" --排序模式（详解请看下方知识扩展）
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 --该字段表示分析过程丢弃的文本字节大小，本例为 0，说明没丢弃任何文本
INSUFFICIENT_PRIVILEGES: 0 --查看 trace 的权限是否不足，0 表示有权限查看 trace 详情
1 row in set (0.00 sec)
虽然结果比较多，但是总体可以分为三个阶段：
- 准备阶段，对应文本中的 join_preparation
- 优化阶段，对应文本中的 join_optimization
- 执行阶段，对应文本中的 join_execution
从结果我们可以看到，使用索引 idx_a 的成本为 121.01，使用索引 idx_b 的成本为 109.01，显然使用索引 idx_b 的成本要低些，因此优化器选择了 idx_b 索引。
参考文章链接：
trace 分析 SQL 优化器
慢查询日志
前面我们学习了分析 SQL 语句的方式，及 SQL 优化器如何选择执行计划。那我们要怎么在测试的服务器中获取到慢 SQL 呢？
就是 MySQL 的慢查询日志，MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阙值的语句，具体指运行时间超过 long_query_time 值得 SQL，则会被记录到慢查询日志中。
如何在服务器中分析 SQL
- 观察，至少跑一天，看看生产的慢 SQL 情况
- 开启慢查询日志，设置阙值，比如超过 5 秒钟的就是慢 SQL，并抓取出来
- explain + 慢 SQL 分析
- show profile
- 进行 SQL 数据库服务器的参数调优
慢查询日志使用
默认情况下，MySQL 数据库没有开启慢查询日志，需要我们手动来设置这个参数。
当然如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。
查看是否开启及如何开启
show variables like '%slow_query_log%';
开启慢查询日志
set global slow_query_log = 1;
show variables like '%slow_query_log%';
通过命令行的方式是临时修改，如果想要永久修改需要修改 MySQL 的配置文件。
开启了慢查询日志后，什么样的 SQL 才会记录到慢查询日志里面呢？
这个是由 long_query_time 控制，默认情况下 long_query_time 的值为 10 秒，主要这个是大于，没有等于：
show variables like 'long_query_time%';
设置慢的阙值时间
set global long_query_time = 3;
需要重新连接或新开一个会话才能看到
select sleep(4);
查询当前系统有多少条慢查询记录
show global status like '%slow_queries%';
慢查询日志工具
在生产环境中，如果要手工分析日志，查找，分析 SQL，显然是个体力活，MySQL 提供了日志分析工具 mysqldumpslow。
perl mysqldumpslow.pl --help
在 Windows 中 mysqldumpslow 不是一个 exe，是一个 pl 程序，所以要用 perl 来运行，查看帮助文档。
参数示例：
- s：表示按照何种方式排序
- c：访问次数
- l：锁定时间
- r：返回记录
- t：查询时间
- al：平均锁定时间
- ar：平均返回记录数
- t：即为返回前面多少条的数据
得到返回记录集最多的 10 个 SQL：
perl mysqldumpslow.pl -s r -t 10 慢查询日志记录位置
得到访问次数最多的 10 个 SQL：
perl mysqldumpslow.pl -s c -t 10 慢查询日志记录位置
SQL 优化
索引优化
我这里有一个索引优化的口诀，可以帮助大家来理解索引优化，但是大家面试的时候千万不要去跟面试官说口诀，不然让面试官以为你来面试不是开发，而是说相声的……
全值匹配我最爱，最左前缀要遵守带头大哥不能挂，中间兄弟不能断索引列上少计算，范围之后全失效like 百分写最右，覆盖索引不写星不等空值还有 or，索引失效要少用varchar 引号不可丢，SQL 高级也不难
我们在讲解这些口诀什么意思之前，我们需要先建一张测试表：
create table staffs(
id int primary key auto_increment,
name varchar(24) not null default "",
age int not null default 0,
pos varchar(20) not null default "",
add_time timestamp not null default CURRENT_TIMESTAMP
)charset utf8;
插入测试数据：
insert into staffs(`name`,`age`,`pos`,`add_time`) values('z3',22,'manager',now());
insert into staffs(`name`,`age`,`pos`,`add_time`) values('July',23,'dev',now());
insert into staffs(`name`,`age`,`pos`,`add_time`) values('2000',23,'dev',now());
建立复合索引：
create index idx_staffs_nameAgePos on staffs(name,age,pos);
全值匹配我最爱
explain select * from staffs where name = 'july';
explain select * from staffs where name = 'july' and age = 25;
explain select * from staffs where name = 'july' and age = 25 and pos = 'dev';
大家可以看到这三条 SQL，全都用到了索引，第三条 SQL 我们的查询条件把索引全部都涵盖了，这样的 SQL 是不是很爽。
最佳左前缀原则
查询从索引的最左前列开始并且不跳过索引中的列：
explain select * from staffs where age = 23 and pos = 'dev';
大家可以看到，这条 SQL 并没有使用到索引，因为我们建立索引的顺序是 name、age、pos，但是使用的时候并没有从 name 开始，大家可以把我们创建的索引想象成楼层，name 对应一楼，age 对应二楼，pos 对应三楼，我们没有通过一楼想去二楼，肯定不行。
我们在来看这条 SQL：
explain select * from staffs where name = 'july' and pos = 'dev';
大家可以看到用到了索引但是并没有全都用到，只用到了 name。因为二楼不在了，想去三楼肯定也是不行的，这就是我们口诀中的带头大哥不能挂，中间兄弟不能断。
索引列上少计算
不在索引列上做任何操作，会导致索引失效而转向全表扫描，这个操作包括使用函数，在索引列上做计算。
这条 SQL，是可以用到索引的，如果我在 name 字段上做了操作就会导致索引失效。
explain select * from staffs where name = 'july';
explain select * from staffs where lower(name) = 'july';
单独创建一个索引：
create index idx_age on staffs(age);
在索引列上做计算：
explain select * from staffs where age-1=22;
explain select * from staffs where age=22+1; # 这个并不是在索引列上做计算
范围之后全失效
存储引擎不能使用索引中范围条件右边的列：
explain select * from staffs where name = 'july' and age = 25 and pos = 'dev';
explain select * from staffs where name = 'july' and age > 13 and pos = 'dev';
下面的 SQL，用到了索引，但是只用到了 name、age 索引。
like 百分写最右
like 以通配符开头，MySQL 索引失效会变成全表扫描的操作：
explain select * from staffs where name like '%july%';
explain select * from staffs where name like 'july%';
explain select * from staffs where name like '%july';
大家可以看到只有%写到右边的时候才能用到索引。那有的小伙伴可能会问，那我要进行模糊搜索岂不是用不到索引了，其实现在已经有其他的工具可以替代比如 Elasticsearch。
覆盖索引不写星
尽量使用覆盖索引，减少 select *，用什么取什么会比写小菊花好。
explain select * from staffs where name = 'july' and age = 25 and pos = 'dev';
explain select name,age,pos from staffs where name = 'july' and age = 25 and pos = 'dev';
explain select * from staffs where name = 'july' and age > 25 and pos = 'dev';
explain select name,age,pos from staffs where name = 'july' and age > 25 and pos = 'dev';
不等空值还有 or，索引失效要少用
explain select * from staffs where name != 'july';
explain select * from staffs where name <> 'july';
varchar 引号不可丢
字符串不加单引号索引失效，这个在开发中是重罪。
这条 SQL 大家都知道 name 字段是字符串类型：
select * from staffs where name = '2000';
如果我把上面的 SQL 换成 name=2000，能否查到数据呢？
select * from staffs where name = 2000;
大家可以看到，是可以查询到数据的，但是会导致索引失效。而且这种 SQL 是很难发现的。
explain select * from staffs where name = '2000';
explain select * from staffs where name = 2000;
join 语句优化
先建表，表比较简单：
商品类别
create table class(
id int unsigned not null primary key auto_increment,
card int unsigned not null
);
图书表
create table book(
bookid int unsigned not null auto_increment primary key,
card int unsigned not null
);
执行 20 次，插入测试记录：
insert into class(card) values(floor((rand()*20)));
执行 20 次，插入测试记录：
insert into book(card) values(floor((rand()*20)));
执行 SQL 语句：
explain select * from class left join book on class.card = book.card;
准备开始优化，在两个表中我们思考要加索引字段，那现在有一个问题就是我的索引字段加在那张表呢？我们用最笨的方法，一个个去试。
创建索引，在 book 表中：
create index idx_book_card on book(card);
explain select * from class left join book on class.card = book.card;
然后删除索引，在 class 表中创建索引：
create index idx_book_card on class(card);
explain select * from class left join book on class.card = book.card;
我们通过 explain 分析， 左连接往右表加索引 ，那右连接就应该往左表加索引。这是由左连接特性决定的，lift join 条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定需要建立索引。
关联查询的算法
- Nested-Loop Join 算法
- Block Nested-Loop Join 算法
Nested-Loop Join 算法
一个简单的 Nested-Loop Join(NLJ) 算法一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。
Block Nested-Loop Join 算法
Block Nested-Loop Join（BNL）算法的思想是：把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比，如果满足 join 条件，则返回结果给客户端。
小表做驱动表
我们来做一个测试，为什么要用小表做驱动表。
建表：
CREATE TABLE `t1` (
`id` int(11) NOT NULL auto_increment,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
COMMENT '记录更新时间',
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
create table t2 like t1; /* 创建表 t2，表结构与 t1 一致 */
delimiter ;;
create procedure insert_t1() /* 创建存储过程 insert_t1 */
begin
declare i int; /* 声明变量 i */
set i=1; /* 设置 i 的初始值为 1 */
while(i<=10000)do /* 对满足 i<=10000 的值进行 while 循环 */
insert into t1(a,b) values(i, i); /* 写入表 t1 中 a、b 两个字段，值都为 i 当前的值 */
set i=i+1; /* 将 i 加 1 */
end while;
end;;
delimiter ; /* 创建批量写入 10000 条数据到表 t1 的存储过程 insert_t1 */
call insert_t1(); /* 运行存储过程 insert_t1 */
insert into t2 select * from t1 limit 100; /* 将表 t1 的前 100 行数据导入到 t2 */
执行 SQL：
select * from t2 straight_join t1 on t2.a = t1.a;
这里使用 straight_join 可以固定连接方式，让前面的表为驱动表。
执行 SQL：
select * from t1 straight_join t2 on t1.a = t2.a;
明显前者扫描的行数少（注意关注 explain 结果的 rows 列），所以建议小表驱动大表。
order by 语句优化
order by 子句，尽量使用 index 方式排序，避免使用 filesort 方式排序。
先来建表：
create table tbla(
age int,
birth timestamp not null
);
插入数据：
insert into tbla(age,birth) values(22,now());
insert into tbla(age,birth) values(23,now());
insert into tbla(age,birth) values(24,now());
创建索引：
create index idx_tbla_agebrith on tbla(age,birth);
分析：会不会产生 filesort
explain select * from tbla where age > 30 order by age;
explain select * from tbla where age > 30 order by age,birth;
explain select * from tbla where age > 30 order by birth;
explain select * from tbla where age > 30 order by birth,age;
explain select * from tbla order by birth;
explain select * from tbla order by age asc,birth desc;
MySQL 支持两种方式的排序——filesort 和 index，index 效率高，MySQL 扫描索引本身完成排序。filesort 方式效率较低。
从上面的 explain 分析中，我们可以看到，order by 满足两种情况下，会使用 index 方式排序：
- order by 语句使用索引最左前列
- 使用 where 子句与 order by 子句条件组合满足索引最左前列
Filesort 是在内存中还是在磁盘中完成排序的？
MySQL 中的 Filesort 并不一定是在磁盘文件中进行排序的，也有可能在内存中排序，内存排序还是磁盘排序取决于排序的数据大小和 sort_buffer_size 配置的大小。
- 如果“排序的数据大小” < sort_buffer_size： 内存排序
- 如果“排序的数据大小” > sort_buffer_size：磁盘排序
我们也可以通过前面学的 trace 来进行分析，来看其中的 number_of_tmp_files，如果等于 0，则表示排序过程没使用临时文件，在内存中就能完成排序；如果大于 0，则表示排序过程中使用了临时文件。
如果不在索引列上，filesort 有两种算法，MySQL 就要启动双路排序和单路排序。
双路排序，MySQL 4.1 之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和 order by 列，对他们进行排序，然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出
众所周知，I\O 是很耗时的，所以在 MySQL 4.1 之后，出现了第二种算法，就是单路排序。
单路排序，从磁盘读取查询需要的所有列，按照 order by 列在 buffer 对他们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据，并且把随机 IO 变成了顺序 IO，但是它会使用更多的空间。
什么情况下会导致单路排序失效呢？
如果超过 sort_buffer_size，会导致多排序几次，效率还不如双路排序
在 sort_buffer 中，单路排序要比双路排序占很多空间，因为单路排序把所有的字段都取出，所以有可能取出的数据的总大小超出了 sort_buffer 的容量，导致每次只能读取 sort_buffer 容量大小的数据，进行排序（创建 tmp 文件，多路合并），排完再取 sort_buffer 容量大小，再次排序……从而多次 I/O。
优化策略调整 MySQL 参数：
- 增加 sort_buffer_size 参数设置
- 增大 max_lenght_for_sort_data 参数的设置
order by 时 select * 是一个大忌，只写需要的字段。
当查询的字段大小总和小于 max_length_for_sort_data 而且排序字段不是 text|blob 类型时，会用改进后的算法，单路排序
两种算法的数据都有可能超出 sort_buffer 的容量，超出之后，会创建 tmp 文件进行合并排序，导致多次 I/O。