目录
4、in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描
一、数据准备
初始化10w条数据
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(20) 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=100003 DEFAULT CHARSET=utf8 COMMENT='员工表';
-- 插入一些示例数据
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('zhangsan',i),i,'开发');
set i=i+1;
end while;
end;;
delimiter ;
call insert_emp();
CREATE TABLE `employees_copy` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(20) 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('zhangsan',22,'开发',NOW());
INSERT INTO employees_copy(name,age,position,hire_time) VALUES('lisi', 23,'测试',NOW());
INSERT INTO employees_copy(name,age,position,hire_time) VALUES('wangwu',23,'产品',NOW());二、实战说明
1、联合索引第一个字段用范围不会走索引
-- 联合索引3列都生效
EXPLAIN SELECT * from employees where name='zhangsan' and age=21 and position='开发';
EXPLAIN SELECT * from employees where name='zhangsan' and age=21 and position>'开发';
-- 为啥name范围查询，索引就全部失效，而position范围查询，索引就全部生效
-- 因为第一个name就用范围，MySQL会认为结果集比较大，而且还需要回表，而回表效率不高，还不如就全表扫描
EXPLAIN SELECT * from employees where name>'zhangsan' and age=21 and position='开发';
Q：为啥name范围查询，索引就全部失效，而position范围查询，索引就全部生效；
分析：第一个name就用范围，MySQL会认为结果集比较大，而且还需要回表，而回表效率不高，还不如就全表扫描
2、强制走索引
-- 强制走索引 force index(idx_name_age_position)
EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'zhangsan' AND age = 22 AND position ='开发';
分析：第一个索引列就用范围查询，使索引失效，依然可以强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，但是最终查找效率不一定比全表扫描高，因为回表效率不高
做如下测试：
-- 关闭查询缓存
set global query_cache_size=0; set global query_cache_type=0;
-- 不走索引
--全表扫描，扫描行数多一些
explain SELECT * FROM employees WHERE name > 'zhangsan';
-- 强制走索引--扫描的行数少一些，但实际耗时却比不走索引多
explain SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'zhangsan';分析：虽然不走索引扫描的行数较多，强制走索引扫描行数少一些，但是这只是影响查询快慢因素之一，还需要考虑回表的数据等，总而言之，不用太刻意去更改MySQL自动优化部分，但不是绝对。
一般这种情况比较建议的是用覆盖索引。
3、覆盖索引优化
使用覆盖索引就最少都是 Using index
EXPLAIN SELECT name,age,position FROM employees WHERE name > 'zhangsan' AND age = 22 AND position ='开发';
4、in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描
有10w数据量的表employees：
1）in查询
EXPLAIN SELECT * FROM employees WHERE name in ('zhangsan','lisi','wangwu') AND age = 22 AND position ='开发';
2）or查询
-- 有10w数据的表的or查询：索引生效
EXPLAIN SELECT * FROM employees WHERE (name='zhangsan' or name='lisi') AND age = 22 AND position ='开发';
将employees 表复制一张employees_copy的表，里面保留了三条记录,这样 in 和 or 就都是全表扫描了
-- 有3条数据的表的in查询：不走索引，全表扫描
EXPLAIN SELECT * FROM employees_copy WHERE name in ('zhangsan','lisi','wangwu') AND age = 22 AND position ='开发';
-- 有3条数据的表的or查询：不走索引，全表扫描
EXPLAIN SELECT * FROM employees_copy WHERE (name='zhangsan' or name='lisi') AND age = 22 AND position ='开发';
5、like xx% 一般情况都会走索引
EXPLAIN SELECT * FROM employees WHERE name like 'zhangsan%' AND age = 22 AND position ='开发';
分析：为啥like 'zhangsan%' 索引都生效，详见索引下推
三、索引下推
索引下推（Index Condition Pushdown，ICP）, like zhangsan%其实就是用到了索引下推优化
对于辅助的联合索引(name,age,position)，正常情况按照最左前缀原则，
SELECT * FROM employees WHERE name like 'zhangsan%' AND age = 22 AND position ='开发' ;
这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引树里的age和position是有可能是无序的，也就无法利用索引。
在MySQL5.6之前的版本，这个查询sql只能在联合索引里匹配到name是 'zhangsan' 开头的索引，然后拿这些索引对应的主键逐个回表，到聚集索引（主键索引）上找出相应的记录，再过滤出age和position这两个字段符合的数据。也就是，先过滤出name like 'zhangsan%' ，拿到主键回表，再过滤出age = 22 AND position ='开发' ；
MySQL 5.6引入了索引下推优化，可以在联合索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 'zhangsan' 开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查数据。也就是直接过滤出name like 'zhangsan%' ，然后比较 and age = 22 AND position ='开发' ，符合再拿主键再回表；
索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引（非聚集索引），innodb的主键索引（聚集索引）树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。
为什么范围查找Mysql没有用索引下推优化？
估计应该是Mysql认为范围查找过滤的结果集过大，like xx% 在绝大多数情况来看，过滤后的结果集比较小，所以这里Mysql选择给 like xx% 用了索引下推优化，当然这也不是绝对的，有时like xx% 也不一定就会走索引下推。
Mysql如何选择合适的索引
-- 全表扫描
EXPLAIN SELECT * from employees where name > 'a';
如果用name索引需要遍历name字段联合索引树，然后还需要根据遍历出来的主键值去回表，从主键索引树里再去查出最终数据，成本比全表扫描还高，可以用覆盖索引优化，这样只需要遍历name字段的联合索引树就能拿到所有结果，如下：
EXPLAIN select name,age,position from employees where name > 'a' ;
EXPLAIN select * from employees where name > 'zz' ;
对于上面这两种 name>'a' 和 name>'zz' 的执行结果，mysql最终是否选择走索引或者一张表涉及多个索引，mysql最终如何选择索引，我们可以用trace工具来一查究竟，开启trace工具会影响mysql性能，所以只能临时分析sql使用，用完之后立即关闭
trace工具用法
set session optimizer_trace="enabled=on",end_markers_in_json=on; --开启trace
explain select * from employees where name > 'a' order by position;
SELECT * FROM information_schema.OPTIMIZER_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`"
}
]
}
},
{
"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')"
}
]
}
},
{
"substitute_generated_columns": {}
},
{
"table_dependencies": [ --表依赖详情
{
"table": "`employees`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": []
}
]
},
{
"ref_optimizer_key_uses": []
},
{
"rows_estimation": [ -- 预估表的访问成本
{
"table": "`employees`",
"range_analysis": {
"table_scan": { -- 全表扫描
"rows": 100061, -- 扫描行数
"cost": 20367 -- 预估成本（相对值）
},
"potential_range_indexes": [ -- 查询可能使用的索引
{
"index": "PRIMARY", -- 主键索引
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_name_age_position",-- 辅助索引
"usable": true,
"key_parts": [
"name",
"age",
"position",
"id"
]
}
],
"setup_range_conditions": [],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"analyzing_range_alternatives": { -- 分析各个索引使用成本
"range_scan_alternatives": [
{
"index": "idx_name_age_position",
"ranges": [
"a < name" -- 索引使用范围
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false, -- 使用该索引获取的记录是否按照主键排序
"using_mrr": false,
"index_only": false,-- 是否使用覆盖索引
"rows": 50030,-- 索引扫描行数
"cost": 60037,--索引使用成本
"chosen": false,--是否选择该索引
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [],
"table": "`employees`",
"best_access_path": { --最优访问路径
"considered_access_paths": [ -- 最终选择的访问路径
{
"rows_to_scan": 100061,
"access_type": "scan",-- 访问类型：scan全表扫描
"resulting_rows": 100061,
"cost": 20365,
"chosen": true,-- 确定选择
"use_tmp_table": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 100061,
"cost_for_plan": 20365,
"sort_cost": 100061,
"new_cost_for_plan": 120426,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`employees`.`name` > 'a')",
"attached_conditions_computation": [],
"attached_conditions_summary": [
{
"table": "`employees`",
"attached": "(`employees`.`name` > 'a')"
}
]
}
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`employees`.`position`",
"items": [
{
"item": "`employees`.`position`"
}
],
"resulting_clause_is_simple": true,
"resulting_clause": "`employees`.`position`"
}
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"steps": [],
"index_order_summary": {
"table": "`employees`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "unknown",
"plan_changed": false
}
}
},
{
"refine_plan": [
{
"table": "`employees`"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`employees`",
"field": "position"
}
],
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
},
"filesort_execution": [],
"filesort_summary": {
"rows": 100002,
"examined_rows": 100002,
"number_of_tmp_files": 30,
"sort_buffer_size": 262016,
"sort_mode": "<sort_key, packed_additional_fields>"
}
}
]
}
}
]
}
结论：全表扫描的成本低于索引扫描，所以mysql最终选择全表扫描
mysql> select * from employees where name > 'zz' order by position;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
查看trace字段可知索引扫描的成本低于全表扫描，所以mysql最终选择索引扫描
mysql> set session optimizer_trace="enabled=off"; --关闭trace四、order by 与 group by优化
场景1
分析：利用最左前缀法则，中间字不能断，因此查询用到name索引，key_len可以看出，age索引列用在排序过程中，故Extra字段里不是Using filesort；
结论：order by 与 group by 使用到的索引字段不会计算到key_len
无论表数据量大小，联合索引第一个字段用来排序，那么该字段索引不生效，可以使用覆盖索引优化
场景2
分析：从key_len看出，name索引生效，跳过了age列，故position是无序，所以Extra里有using filesort；
场景3
分析：索引用到name，age和position用于排序，无Using filesort
场景4
分析：联合索引的顺序是(name,age,position)，name索引生效了，但排序是按position,age，故有Using filesort
场景5
分析：和case4不同在于，并未出现using filesort，因为age是常量22，在排序中被优化
场景6
分析：排序字段和索引顺序一致，但是索引是从左到右是递增，而order by后面position是降序，从而产生using filesort
场景7
对于name=zhangsan、lisi，结果集里面的age并一定是升序
分析：对于排序来说，多个相等的条件也是范围查询，因为只有name是常量，age和position才是有序
场景8
使用覆盖索引优化
优化总结
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文件排序方式
1、单路排序
一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用race工具可以看到sort mode信息里显示< sort_key, additional_fields >或者< sort_key,oacked_additional_fields >
2、双路排序
又叫回表排序模式，
是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段，用trace工具可以看到sort mode信息里显示< sort_key, rowid >；直白说就是数据太大，sort buffer一次性放不下，会先放到临时文件file里（可能是多个，依数据量而定），再加载到内存去排序
MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节)的大小和需要查的字段总大小来判断使用哪种排序模式。
如果字段的总长度小于max_length_for_sort_data，那么使用单路排序模式
如果字段的总长度大于max_length_for_sort_data，那么使用双路排序模式
总之就是，需要检查的字段sort buffer一次性放不下，就会选择双路排序，放得下就选择单路排序
示例验证排序方式
EXPLAIN SELECT * from employees where name='zhangsan' ORDER BY position;
-- trace工具
set session optimizer_trace="enabled=on",end_markers_in_json=on; -- 开启trace
EXPLAIN SELECT * from employees where name='zhangsan' ORDER BY position;
SELECT * FROM information_schema.OPTIMIZER_TRACE;单路排序的详细过程：
- 从索引name找到第一个满足 name = ‘xxx’ 条件的主键 id
- 根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer 中
- 从索引name找到下一个满足 name = ‘xxx’ 条件的主键 id
- 重复步骤 2、3 直到不满足 name = ‘xxx’
- 对 sort_buffer 中的数据按照字段 position 进行排序
- 返回结果给客户端
双路排序的详细过程：
- 从索引 name 找到第一个满足 name = ‘xxx’ 的主键id
- 根据主键 id 取出整行，把排序字段 position 和主键 id 这两个字段放到 sort buffer 中
- 从索引 name 取下一个满足 name = ‘xxx’ 记录的主键 id
- 重复 3、4 直到不满足 name = ‘xxx’
- 对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序
- 遍历排序好的 id 和字段 position，按照 id 的值回到原表中取出 所有字段的值返回给客户端
六、索引设计原则
1、代码先行，索引后上
一般应该等到主体业务功能开发完，把涉及到该表相关sql全部拿出来分析之后，才能更准确建立索引。
2、联合索引尽量覆盖条件列
注意：联合索引并不是越多越好，索引越多，增删改就越慢，因为要维护索引树
可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。
3、不要在小基数字段上建立索引
索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段，其值不是男就是女，那么该字段的基数就是2。
如果对这种小基数字段建立索引的话，还不如全表扫描了，因为你的索引树里就包含男和女两种值，根本没法进行快速的二分查找，那用索引就没有太大的意义了。
一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查找的优势来。
4、长字符串我们可以采用前缀索引
使用前20个字符匹配基本可以满足90%的场景
尽量对字段类型较小的列设计索引，比如说什么tinyint之类的，因为字段类型较小的话，占用磁盘空间也会比较小，此时你在搜索的时候性能也会比较好一点。
当然，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这种字段建立索引，哪怕多占用一些磁盘空间也是有必要的。
对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 KEY index(name(20),age,position)。
此时你在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来完整的name字段值进行比对。
但是假如你要是order by name，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这个排序是没法用上索引的， group by也是同理。所以这里大家要对前缀索引有一个了解。
5、where与order by冲突时优先where
在where和order by出现索引设计冲突时，到底是针对where去设计索引，还是针对order by设计索引？到底是让where去用上索引，还是让order by用上索引?
一般这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序。
因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可能会小很多。
6、基于慢sql查询做优化
可以根据监控后台的一些慢sql，针对这些慢sql查询做特定的索引优化。
注意：开启慢sql捕捉会影响性能，一调优时在从库开启
273
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
