创建示例表，并通过存储过程给这张表添加100万数据。
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`age` int(5) NULL DEFAULT NULL,
`sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`height` int(10) NULL DEFAULT NULL,
`weight` int(10) NULL DEFAULT NULL,
`job` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`salary` int(10) NULL DEFAULT NULL,
`create_time` datetime(0) NULL DEFAULT NULL,
`create_by` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`update_time` datetime(0) NULL DEFAULT NULL,
`update_by` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_name_salary`(`name`, `salary`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1000114 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
存储过程
drop procedure inserData;
create procedure inserData()
begin
declare i int;
set i=0;
while i<1000000 do
INSERT INTO person(`name`, age, sex, height, weight, job, salary, create_time, create_by, update_time, update_by, remark) VALUES (CONCAT('赵铁柱',i), 18, '1', 180, 180, '搬砖的', 1000+i, '2021-03-13 16:49:37', 'system', NULL, NULL, NULL);
set i=i+1;
end while;
end;
call inserData();
当然执行需要相当一段长的时间，不如让它先执行，接着往下看
现在我的这张表中是有100万数据的
1. MySql如何选择合适的索引？
下面的这条语句中，可能很多人都会这样写
select * from person where name > 'a';
让我们用explain工具分析一下
EXPLAIN select * from person where name > 'a';
如上图所示的那样，使用name索引需要遍历name字段联合索引树，然后还需要根据遍历出来的主键值去主键索引树里再去查出最终数据，成本比全表扫描还高。
对于这样的SQL，我们能做的就是使用覆盖索引，这样只需要遍历name字段的联合索引树就能拿到所有的结果
EXPLAIN select name,salary from person where name > 'a';
但是像 select * from person where name > 'a';这样的查询SQL并不一定都不走索引，MySql内部优化器根据检索比例、表大小等多个因素整体评估是否使用索引。
比如再新建一张表person2,并往其中添加几条记录。再用 EXPLAIN select * from person2 where name > 'a';，你会发现MySql又走索引了
假如你想知道MySql到底是如何判断选择索引，可以使用trace工具，用来分析sql。
但是trace工具的使用会影响mysql的性能，所以使用后务必记得关闭。
1.1 trace工具
开启trace工具
set session optimizer_trace="enabled=on",end_markers_in_json=on;
select * from person where name > 'a' order by salary;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
执行查询之后
当中的TRACE就是我们要的东西，它会说MySql为什么要这么做。赋值出来之后可以看到相当长的一串
1.1.1 trace信息查看
这里我使用chrome的console窗口，就不用一次性看太多的信息量，导致眼花缭乱的。
1.1.2 steps节点
展开steps节点，可以看到有三个节点，分别对应了SQL执行的 0-准备阶段、1-SQL优化阶段、2-SQL执行阶段
1.1.3 SQL优化阶段
准备阶段我们可以跳过，点开SQL优化阶段，这里为了好展示，我单独将第二阶段的json复制出来。
当中需要注意的地方都用红字标注出来了。
很明显，为什么在 EXPLAIN select * from person where name > 'a';中没有使用索引，而是使用全表扫描，是因为MySql判定索引产生的cost更大，为21万，而全表扫描的cost为10万。
关闭trance
set session optimizer_trace="enabled=off";
2. SQL深入优化
2.1 order by与group by优化
为了复现这个例子，我们需要删除之前的索引，并创建新的索引
ALTER TABLE `person` add INDEX `idx_name_salary`;
ALTER TABLE `person` ADD INDEX `idx_name_age_salary` (`name`,`age`,`salary`) USING BTREE ;
案例一
EXPLAIN select * from person where name='赵铁柱100' and salary=2000 order by age;
分析：
利用最左前缀法则：中间字段不能断，因此查询用到了name索引，从ken_len=768也能看出，age索引列用在排序过程中，因为extra字段里没有using filesort
案例二
EXPLAIN select * from person where name='赵铁柱100' order by salary;
分析：从explain的执行结果看：ken_len=768,查询使用了name索引，由于用了salary进行排序，跳过了age，出现了using firesort
案例三
EXPLAIN select * from person where name='赵铁柱100' order by age,salary;
分析：查询只用到索引name，age和salary用于排序，无Using firesort
案例四
EXPLAIN select * from person where name='赵铁柱100' order by salary,age;
分析：出现了Using firesort，因为索引的创建顺序为name，age，salary，但是排序的时候age和salary颠倒位置了
案例五
EXPLAIN select * from person where name='赵铁柱100' and age =18 order by salary,age;
与案例四对比，在Extra中并未出现Using firesort，因为age为常量，在排序中被优化，所以索引未颠倒，不会出现Using firesort
案例六
EXPLAIN select * from person where name='赵铁柱100' order by age asc, salary DESC;
分析：虽然排序的字段列和索引顺序一样，且order by默认升序，这里的salary desc变成了降序，导致与索引排序方式不同，产生了Using filesort。
案例七
EXPLAIN select * from person where name in ('赵铁柱100','赵铁柱101') order by age , salary ;
分析：
对于排序来说，多个相等条件也是范围查询。通过范围查询获取的结果并不一样是有序的，所以这里通过name列走了索引，但是在文件排序的时候无法通过索引进行排序。
通过trace工具，也可以看到mysql没有选择通过索引进行排序，这里我截取trace工具打印的信息。
案例八
EXPLAIN select * from person where name > 'a' order by name;
分析：
和案例七一样，因为范围查询获取的结果并不一样是有序的，导致产生Using filesort。
可以通过覆盖索引进行优化
EXPLAIN select name,age,salary from person where name > 'a' order by name;
3. 优化总结：
1.MySql支持两种方式的排序filesort和index，Using index是指MySql扫描索引本身完成排序。index效率高，filesort效率低。
2.order by满足两种情况会使用Using index
- order by语句使用索引最左前列
- 使用where字句与order by字句条件列组合满足索引最左前列
3.尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则
4.如果order by的条件不在索引列上，就会产生Using filesort
5.能使用覆盖索引尽量用覆盖索引
6.group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了
4. Using filesort文件原理详解
4.1 filesort文件排序方式
- 单路排序：是一次性取出满足条件行的所有字段，然后再sort buff中进行排序;用trace工具可以看到sort_mode信息里显示**<sort_key,additional_fields>** 或者**<sort_key,packed_additional_fields>**
- 双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行ID，然后再sort buff中进行排序，排序完后需要再次取回其他需要的字段；用trace工具可以看到**<fixed_sort_key, rowid>**
MySql通过比较系统变量max_length_for_sort_data（默认1024字节）的大小和需要查询的字段总大小来判断使用那种排序方式。
- 如果max_length_for_sort_data比查询字段的总长度大，那么使用单路排序
- 如果max_length_for_sort_data比查询字段的总长度小，那么使用单路排序
4.1.1 单路排序的详细过程
- 从索引name找到第一个满足 name = ‘赵铁柱’ 条件的主键 id
- 根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer 中
- 从索引name找到下一个满足 name = ‘赵铁柱’ 条件的主键 id
- 重复步骤 2、3 直到不满足 name = ‘赵铁柱’
- 对 sort_buffer 中的数据按照字段 salary进行排序
- 返回结果给客户端
过程如下图所示：
4.1.2 双路排序的详细过程
- 从索引 name 找到第一个满足 name = ‘赵铁柱’ 的主键id
- 根据主键 id 取出整行，把排序字段 salary和主键 id 这两个字段放到 sort buffer 中
- 从索引 name 取下一个满足 name = ‘赵铁柱’ 记录的主键 id
- 重复 3、4 直到不满足 name = ‘赵铁柱’
- 对 sort_buffer 中的字段 salary和主键 id 按照字段 salary进行排序
- 遍历排序好的 id 和字段salary，按照 id 的值回到原表中取出 所有字段的值返回给客户端
过程如下图所示：
其实对比两个排序模式，单路排序会把所有需要查询的字段都放到 sort buffer 中，而双路排序只会把主键
和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段。
如果 MySQL 排序内存配置的比较小并且没有条件继续增加了，可以适当把 max_length_for_sort_data 配
置小点，让优化器选择使用双路排序算法，可以在sort_buffer 中一次排序更多的行，只是需要再根据主键
回到原表取数据。
如果 MySQL 排序内存有条件可以配置比较大，可以适当增大 max_length_for_sort_data 的值，让优化器
优先选择全字段排序(单路排序)，把需要的字段放到 sort_buffer 中，这样排序后就会直接从内存里返回查
询结果了。
所以，MySQL通过 max_length_for_sort_data 这个参数来控制排序，在不同场景使用不同的排序模式，
从而提升排序效率。
注意，如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增
大sort_buffer(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整。