对于任何一个后端的开发人员来说，索引优化是必备的技能，接下来会通过两篇文章来介绍如何优化索引以及如何设计好的索引。
一、数据准备
创建一个员工表，除了主键索引外，还额外创建了联合索引idx_name_age_position
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
age int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
position varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
hire_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (id),
KEY idx_name_age_position (name,age,position) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
添加一些基本数据
INSERT INTO employees(name,age,position,hire_time) VALUES('lizhi',25,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('zhuyuzhu', 26,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('linan',25,'dev',NOW());
再通过存储引擎插入100000条数据，索引只有在数据量大时才能正真体现出它的优势。
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(`name`,age,position) VALUES(CONCAT('zhuyuzhu',i),i,'dev');
SET i=i+1;
END WHILE;
END $
CALL insert_emp();
注：这里创建的数据，在后面的文章都还会使用
二、联合索引优化
2.1 范围查询导致全表扫描
联合索引第一个字段使用范围查询，导致全表扫描
可以在where关键字前面指定强制走索引：force index(idx_name_age_position)
强制走索引之后，可以看到执行rows字段减少了，表示扫描的记录数减少了，但这并不意味着查询的时间就少了，强制走索引之后，需要大量进行回表，就会导致SQL执行比全表扫面时间还长，可以看这两个SQL的具体执行耗时：
关闭查询缓存(默认关闭)，这样可以更直观比较结果
set global query_cache_size=0;
set global query_cache_type=0;
不走缓存的执行时间：
强制走缓存的执行时间：
2.2 通过覆盖索引优化
上面的(Select *)之所以不能走索引，是因为需要进行回表操作，但如果需要查询的字段在二级索引就可以拿到，那么就不需要回表操作，这样走索引就会比全表扫描的效率要高很多。
2.3 IN和OR的优化
拷贝前面的employees表，创建一个employees_copy表，然后copy表只存储少量数据，比较IN和OR在大小数据量下走索引的情况
DROP TABLE IF EXISTS employees_copy;
CREATE TABLE employees_copy (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
age int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
position varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
hire_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (id),
KEY idx_name_age_position (name,age,position) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
INSERT INTO employees_copy(name,age,position,hire_time) VALUES('lizhi',25,'manager',NOW());
INSERT INTO employees_copy(name,age,position,hire_time) VALUES('zhuyuzhu', 26,'dev',NOW());
INSERT INTO employees_copy(name,age,position,hire_time) VALUES('linan',25,'dev',NOW());
2.3.1 数据量大时走索引
employees表的数据有100000多条，此时使用IN和OR都会使用索引
2.3.2 数据量小时不走索引
employees_copy表的数据比较少，此时使用IN和OR就不会使用索引
原因也很好分析：
在数据量大时，IN和OR走辅助索引查询的数据量相对就小很多，即使需要回表，也比全表扫描的效率高很多；而在数据量小时，走索引还要回表，还不如直接全表扫描的效率高
三、索引下推
在《索引使用准则》这篇文章提到过了，在联合索引中，对于like KK%的情况，即使后面条件中的联合索引字段并不是有序的，也会走索引，这正是因为索引下推的缘故。
什么是索引下推？
索引下推(Index Condition PushdownICP)是MySQL5.6实现的功能。在MySQL5.6之前，对于二级联合索引，按照正常的最左匹配原则
SELECT * FROM employees WHERE name like 'li%' AND age = 25 AND position ='manager';
上面这个SQL只会走name字段的索引，因为name字段过滤完后，后面的age和position字段是无序的，就导致无法走索引。每次根据name筛选出来的数据，再根据主键进行回表，判断age和position是否符合。
引入索引下推之后，在二级辅助索引中，对于满足name条件的记录，会继续判断记录中的age和position是否满足条件，这样筛选出来的主键就会少很多，可以大大降低回表的的次数。
索引下推的目的是为了减少回表次数，所以只能用于二级索引，对于主键索引，并不需要回表，所以也就没法减少查询行。
模糊查询也是一个范围，那为什么普通的>、<这样的范围查询不能走索引呢？
对于like KK%可以走索引，而对于age > 20后面的条件就没法走索引，这个大概的原因在于前者的查询范围要比后者这种范围擦汗寻所扫描的范围要小得多。
like KK%在绝大多数情况来看，过滤后的结果集比较小，所以这里Mysql选择给 like KK% 用了索引下推优化，当然这也不是绝对的，有时like KK% 也不一定就会走索引下推。
四、Trace工具使用
Trace工具可以从细节上分析MySQL是如何选择索引，从下面两个SQL，看MySQL对索引的选择：
两个基本一样的SQL语句，一个走了索引，一个没有走索引，更加说明MySQL走不走索引并不是固定的，而是根据具体情况具体分析的。
对于上面这两种name>'a' 和 name>'zzz' 的执行结果，mysql最终是否选择走索引或者一张表涉及多个索引，mysql最终如何选择索引，可以用trace工具来一查究竟，开启trace工具会影响mysql性能，所以只能临时分析sql使用，用完之后立即关闭。
4.1 Trace使用
开启trace
set session optimizer_trace="enabled=on",end_markers_in_json=on;
select * from employees where name > 'a' order by position;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
两个查询语句要一起执行才行
关闭trace:
set session optimizer_trace="enabled=off";
4.2 Trace报文分析
Trace报文:
{
"steps": [
{
"join_preparation": { // 第一阶段：SQL准备阶段，格式化SQL
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'a') order by `employees`.`position`"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": { // 第二阶段：SQL优化阶段
"select#": 1,
"steps": [
{
"condition_processing": { // 条件处理
"condition": "WHERE",
"original_condition": "(`employees`.`name` > 'a')",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`employees`.`name` > 'a')"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`employees`.`name` > 'a')"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`employees`.`name` > 'a')"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [ // 查询依赖的表详情
{
"table": "`employees`",
"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": "`employees`",
"range_analysis": {
"table_scan": { // 表扫描
"rows": 94182, // 扫描行数
"cost": 19192 // 查询成本,这就是一个数值,没有单位
} /* table_scan */,
"potential_range_indexes": [ // 查询可能使用的索引
{
"index": "PRIMARY", // 主键索引
"usable": false, // 不能用
"cause": "not_applicable"
},
{
"index": "idx_name_age_position", // 辅助联合索引
"usable": true, // 可以使用
"key_parts": [ //索引叶子节点存储的列
"name",
"age",
"position",
"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_name_age_position",
"ranges": [
"a < name"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false, // 是否使用覆盖索引(不需要回表)
"rows": 47091, // 索引扫描行数
"cost": 56510, // 索引使用成本
"chosen": false, // 是否选择该索引
"cause": "cost"
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`employees`",
"best_access_path": { // 最有访问路径
"considered_access_paths": [ // 最终选择的访问路径
{
"rows_to_scan": 94182, // 需要扫描的行
"access_type": "scan", // 访问类型：scan(全表扫描)
"resulting_rows": 94182,
"cost": 19189,
"chosen": true, // 确认选择该方式
"use_tmp_table": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 94182,
"cost_for_plan": 19189,
"sort_cost": 94182,
"new_cost_for_plan": 113371,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`employees`.`name` > 'a')",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`employees`",
"attached": "(`employees`.`name` > 'a')"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`employees`.`position`",
"items": [
{
"item": "`employees`.`position`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`employees`.`position`"
} /* clause_processing */
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"index_order_summary": {
"table": "`employees`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "unknown",
"plan_changed": false
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
},
{
"refine_plan": [
{
"table": "`employees`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"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": 100003,
"examined_rows": 100003,
"number_of_tmp_files": 31,
"sort_buffer_size": 262056,
"sort_mode": "<sort_key, packed_additional_fields>"
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
] /* steps */
}
结论：全表扫描的使用成本为19192，而索引扫描的使用成本为56510，所以选择全表扫描而不使用索引。MySQL选择是否使用索引或者选择使用哪一个索引都是要通过计算查询成本，然后选择成本最小的方案来执行SQL。
EXPLAIN select * from employees where name > 'zzz' order by position;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
可以自行看一下上面这个SQL的成本分析，结合上面Trace报文中的注释，就能看明白为什么选择使用索引了。使用完，一定要关闭trace工具。
五、Order By和Group By
Order By和Group By依然使用最左匹配原则。
5.1 查询条件缺少中间字段，后面字段无法走索引
查询字段name使用了索引，因为缺失age查询条件，position字段不一定是有序的，所以没有走索引。
因为在name相同时，age具有有序性，所以按照age排序可以走索引，直接使用索引进行排序
5.2 查询或排序条件缺少中间索引，排序无法走索引
因为缺少age字段，所以position字段在满足条件的记录中并不一定是有序的的。所以按照position进行排序时，使用filesort文件排序
5.3 查询条件和排序条件符合联合索引顺序，可以走索引
在name确定时，age和position是有序的，所以通过age，position进行排序可以使用索引
5.4 排序条件不符合联合索引顺序，排序无法走索引
联合索引中，是按照name、age、position的顺序排序的，所以当顺序打乱后就不再是有序的了，所以对position,age进行排序没法走索引
5.5 某个排序条件为常量，可以走索引
虽然它的排序字段也不符合联合索引的顺序，但age字段是一个常量，只需要按照position进行排序据可以了，所以可以使用索引进行排序
5.6 排序的索引字段采用不同的排序方式，无法走索引
联合索引的多个字段参与排序时，只要有一个字段的排序方式与索引排序方式不同，就不能使用索引排序，只能使用文件排序
5.7 IN、OR查询排序无法走索引
在排序的情况下，查询条件如果使用了IN或OR，排序就不能使用索引。原因很简单，这两种方式筛选出来的记录，age和position并一定是有序的。
5.8 范围查询排序无法走索引
范围查询中，age和position并一定是有序的，所以没法通过索引进行排序
六、文件排序(filesort)
在排序中，除了索引排序就是文件排序，filesort又分为单路排序和双路排序(回表排序)两种方式。
6.1 单路排序
一次性取出满足条件的行的所有字段，然后在sort buffer中进行排序；sort buffer是MySQL的一段内存区域，在内存中进行排序。
用trace命令可以看到sort_mode信息里面显示<sort_key,additional_fields>或<sort_key,packed_additional_fields>
6.2 双路排序
双路排序又称为回表排序，是因为双路排序是先根据相应的where子句的判断条件，取出排序字段和可以直接定位行数据的行ID(主键),然后在sort buffer中进行排序，排序完成后再回表取出其他需要的字段。
用trace工具可以看到sort_mode信息里显示<sort_key,rowid>。
如果需要排序的数据量很大，sort buffer无法一次性放下，会分成临时文件，通过磁盘文件进行排序
6.3 MySQL如何选择文件排序
MySQL通过比较系统变量max_length_for_sort_data(默认1024字节)的大小和需要查询的字段总大小(一行记录中字段的总大小)来判断使用那种方式：
1、如果字段的总长度小于max_length_for_sort_data，那么使用单路排序模式;
2、如果字段的总长度大于max_length_for_sort_data，那么使用双路排序模式；
6.4 Trace查看排序方式
通过Explain关键字可以简单看到Useing filesort，就表示当前SQL的排序方式使用的是文件排序，但具体使用的是文件排序的哪一种方式，还需要根据trace工具来具体分析。
6.4.1 使用默认大小的max_length_for_sort_data
文件排序:
set session optimizer_trace="enabled=on",end_markers_in_json=on;
select * from employees where name > 'lizhi' order by position;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
只查看trace中SQL执行阶段的内容：
{
"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": 100001, // 预计扫描行数
"examined_rows": 100003, //参与排序的行
"number_of_tmp_files": 31, // 使用临时文件的个数,这个指如果为0代表全部使用的sort buffer内存排序,否则使用的磁盘文件排序
"sort_buffer_size": 262056, // 排序缓存(sort buffer)的大小，单位byte
"sort_mode": "<sort_key, packed_additional_fields>" //排序方式，这里使用的是单路排序
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
6.4.2 将max_length_for_sot_data设置为10(employees所有字段总大小是大于10的)
设置max_length_for_sort_data，重新查看排序方式
set max_length_for_sort_data = 10;
select * from employees where name > 'lizhi' order by position;
SELECT * FROM information_schema.OPTIMIZER_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": 100001,
"examined_rows": 100003,
"number_of_tmp_files": 20,
"sort_buffer_size": 262136,
"sort_mode": "<sort_key, rowid>" // 排序方式，这是使用的是双路排序
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
关闭trace
set session optimizer_trace="enabled=off"; --关闭trace
6.5 排序过程
以下面的SQL为例：
select * from employees where name = 'lizhi' order by position;
6.5.1 单路排序
1、从索引name中找到第一个满足条件的主键ID
2、根据主键ID取出所有整行所有字段的指，存入到sort buffer中
3、从索引name找到下一个满足name='lizhi'条件的主键ID
4、重复步骤2、3直到不满足name='lizhi'
5、对sort_buffer中的数据按照字段position进行排序
6、将排序后的数据返回给客户端
6.5.2 双路排序
1、从索引name中找到第一个满足条件的主键ID
2、根据主键ID取出这整行，然后将排序字段postion和主键ID这两个字段存入到sort buffer中
3、从索引name找到下一个满足name='lizhi'条件的主键ID
4、重复步骤2、3直到不满足name='lizhi'
5、对sort_buffer中的数据按照字段position和主键ID按照position进行排序
6、遍历排序好的ID和字段position，按照ID的值回表取出所有字段的值返回给客户端
6.6 总结
1、对比两个排序模式，单路排序会把所有需要查询的字段都放到sort buffer中，而双路排序只会把主键和需要排序的字段放到sort buffer中进行排序，然后再通过主键回到原表查询需要的字段。
2、如果 MySQL 排序内存sort_buffer配置的比较小并且没有条件继续增加了，可以适当把 max_length_for_sort_data 配置小点，让优化器选择使用双路排序算法，可以在sort_buffer中一次排序更多的行，只是需要再根据主键回到原表取数据。
3、如果 MySQL 排序内存有条件可以配置比较大，可以适当增大max_length_for_sort_data的值，让优化器优先选择全字段排序(单路排序)，把需要的字段放到sort_buffer中，这样排序后就会直接从内存里返回查询结果了。
4、MySQL通过 max_length_for_sort_data这个参数来控制排序，在不同场景使用不同的排序模式，从而提升排序效率。
5、如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大max_length_for_sort_data(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整。
6、单路排序只需要一次回表，而双路排序需要两次回表，所以尽量让排序字段在查询条件对应的联合索引中，这样双路排序应该就不需要两次回表了
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/112098.html
