文章目录
前言
一、准备测试数据
#测试表
create table employees
(
id int not null
primary key,
name varchar(24) not null,
age int null,
position varchar(20) null,
hire_time timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP
);
create index idx_name_age_position
on employees (name, age, position);
#插入一些示例数据
drop procedure if exists insert_emp;
delimiter ;;
create procedure insert_emp()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into employees(id,name,age,position) values(i,CONCAT('ljc',i),i,'dev');
set i=i+1;
end while;
end;;
delimiter ;
call insert_emp();
二、常见sql深入优化
2.1、Order by与Group by优化
在开始一点先补充一点
Extra列里面如果出现了using filesort，则表示没有用到索引排序，用的是文件排序。
接下来先来几个案例看下各种场景下的区别
CASE1
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND position ='dev' order by age;
结论
利用最左前缀法则：中间字段不能断，因此查询用到了name索引，从key_len=26也能看出，Extra里没有出现using filesort(表示age走了索引)，因为name走了索引，索引这里age自然而然可以走索引。
这里可能会有人有疑问，为啥key_len没有加上age在字节（这是因为order by和group by场景不会在这里面）
CASE2
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' order by position;
结论
从explain的执行结果来看：key_len=26，查询使用了name索引，由于用了position进行排序，跳过了age，出现了Using filesort。
CASE3
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' order by age,position;
结论
查找只用到索引name，age和position用于排序，无Using filesort。
CASE4
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' order by position,age;
结论
和Case 3中explain的执行结果一样，但是出现了Using filesort，因为索引的创建顺序为name,age,position，但是排序的时候age和position颠倒位置了。
CASE5
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' and age=19 order by position,age;
结论
与Case 4对比，在Extra中并未出现Using filesort，因为age为常量，在排序中被优化，所以索引未颠倒，不会出现Using filesort。
CASE6
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' order by age asc,position desc
结论
虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。
CASE7
EXPLAIN SELECT * FROM employees WHERE name in('LiLei','ljc') order by age,position
结论
对于排序来说，多个相等(in多个条件)条件也是范围查询
CASE8
EXPLAIN SELECT * FROM employees WHERE name > 'a' order by name;
可以用覆盖索引
EXPLAIN SELECT name FROM employees WHERE name > 'a' order by name;
2.2、order by和group优化总结
1、MySQL支持两种方式的排序filesort（外部排序、又称文件排序）和index（索引排序），Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。
2、order by满足两种情况会使用Using index。
1) order by语句使用索引最左前列。
2) 使用where子句与order by子句条件列组合满足索引最左前列。
3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
4、如果order by的条件不在索引列上，就会产生Using filesort。
5、能用覆盖索引尽量用覆盖索引
6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。
三、Using filesort文件排序原理
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' order by position
3.1、什么是单路排序？
先通过一个单路排序的详细过程：
1、从索引name找到第一个满足 name = ‘’ 条件的主键 id；
2、根据主键 id 取出整行数据，取出所有字段的值，存入 sort buffer（排序缓冲区） 中；
3、从索引name找到下一个满足 name = ‘LiLei’ 条件的主键 id；
4、重复步骤 2、3 直到不满足 name = ‘LiLei’ ；
5、对 sort_buffer 中的数据按照字段 position 进行排序;
6、返回结果给客户端；
结论
比如上面那条SQL，单路排序他会把order by之前，一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；
通过trace工具可以看见"sort_mode": “<sort_key, packed_additional_fields>” --排序方式，这里用的单路排序
单路排序案例验证，这里采用trace工具
set session optimizer_trace="enabled=on",end_markers_in_json=on; --开启trace
select * from employees where name = 'ljc' order by position;
select * from information_schema.OPTIMIZER_TRACE;
trace排序部分结果
"join_execution": { --Sql执行阶段
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`employees`",
"field": "position"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": { --文件排序信息
"rows": 10000, --预计扫描行数
"examined_rows": 10000, --参与排序的行
"number_of_tmp_files": 3, --使用临时文件的个数，这个值如果为0代表全部使用的sort_buffer内存排序，否则使用的磁盘文件排序
"sort_buffer_size": 262056, --排序缓存的大小，单位Byte
"sort_mode": "<sort_key, packed_additional_fields>" --排序方式，这里用的单路排序
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
3.2、什么是双路排序？
先通过一个双路排序的详细过程：
1、从索引 name 找到第一个满足 name = ‘LiLei’ 的主键id;
2、根据主键 id 取出整行，把排序字段 position 和主键 id 这两个字段放到 sort buffer 中；
3、从索引 name 取下一个满足 name = ‘LiLei’ 记录的主键 id；
4、重复 3、4 直到不满足 name = ‘LiLei’ ；
5、对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序；
6、遍历排序好的 id 和字段 position，按照 id 的值回到原表中取出 所有字段的值返回给客户端；
结论
双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；
trace工具里可以看见"sort_mode": “<sort_key, rowid>” 这个代表就是双路排序；
双路排序trace排序部分结果
"join_execution": {
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`employees`",
"field": "position"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": {
"rows": 10000,
"examined_rows": 10000,
"number_of_tmp_files": 2,
"sort_buffer_size": 262136,
"sort_mode": "<sort_key, rowid>" --排序方式，这里用的双路排序
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
mysql> set session optimizer_trace="enabled=off"; --关闭trace
3.3、文件排序内存不足怎么处理的？
通过上面的trace可以看见有一个参数
#使用临时文件的个数，这个值如果为0代表全部使用的sort_buffer内存排序，否则使用的磁盘文件排序
"number_of_tmp_files": 3,
结论
如果为0，则使用排序缓存区；
不为0，则使用磁盘文件排序；
上面参数一般不为0,一般都是因为分配的排序缓冲区容量不足以放下这么多数据；
所以他会搞一个临时磁盘文件，然后把数据放进去，就不会加载到之前那个排序缓冲区了，而是加载到内存上，进行排序；
3.3、MySql具体使用哪种排序？
MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。
如果 字段的总长度小于max_length_for_sort_data ，那么使用 单路排序模式；
如果 字段的总长度大于max_length_for_sort_data ，那么使用 双路排序模式。
MySQL通过 max_length_for_sort_data 这个参数来控制排序，在不同场景使用不同的排序模式，从而提升排序效率。
PS：正常情况无需修改，除非你是专业DBA。
四、using index和using filesort区别
using index是用到了二级索引树，按照索引树的顺序去排序的；
是先从根据条件查询出的结果集，从磁盘idb文件的数据加载到内存里面去进行排序的；
using filesort是没有用到二级索引树的，而是用到了主键索引，他是把整张表的数据加载到内存，去进行一个排序；
PS：idb文件里面包含**主键索引（整张表的索引数据）**和二级索引二块区域。
结论
因为using filesort是拿的主键索引（整张表的数据），而using index拿的是二级索引比主键索引相对小太多，所以using index效率会快很多。
402
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
