欢迎大家关注我的微信公众号:
目录
示例表:
1 CREATE TABLE `employees` (
2 `id` int(11) NOT NULL AUTO_INCREMENT,
3 `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
4 `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
5 `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
6 `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
7 PRIMARY KEY (`id`),
8 KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
9 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
10
11 INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
12 INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
13 INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());
14
15 ‐‐ 插入一些示例数据
16 drop procedure if exists insert_emp;
17 delimiter ;;
18 create procedure insert_emp()
19 begin
20 declare i int;
21 set i=1;
22 while(i<=100000)do
23 insert into employees(name,age,position) values(CONCAT('zhuge',i),i,'dev');
24 set i=i+1;
25 end while;
26 end;;
27 delimiter ;
28 call insert_emp();
‘employees’表除了主键索引id外,建立了联合索引idx_name_age_position('name','age','position')
举一个大家不容易理解的综合例子:
EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';
2、强制走索引
EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 AND position ='manager';
虽然使用了强制走索引让联合索引第一个字段范围查找也走索引,扫描的行rows看上去也少了点,但是最终查找效率不一定比全表扫描高,因为回表效率不高
做了一个小实验:
‐‐ 关闭查询缓存
2 set global query_cache_size=0;
3 set global query_cache_type=0;
4 ‐‐ 执行时间0.333s
5 SELECT * FROM employees WHERE name > 'LiLei';
6 ‐‐ 执行时间0.444s
7 SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei';
EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';
4、in和or在表数据量比较大的情况会走索引,在表记录不多的情况下会选择全表扫描
EXPLAIN SELECT * FROM employees WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position ='manager';
做一个小实验,将employees 表复制一张employees_copy的表,里面保留两三条记录
EXPLAIN SELECT * FROM employees_copy WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position ='manager';
5、like KK% 一般情况都会走索引
EXPLAIN SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager';
这里给大家补充一个概念,索引下推(Index Condition Pushdown,ICP), like KK%其实就是用到了索引下推优化。
什么是索引下推?
为什么范围查找Mysql没有用索引下推优化?
Mysql如何选择合适的索引?
mysql> EXPLAIN select * from employees where name > 'a';
mysql> EXPLAIN select * from employees where name > 'zzz' ;
mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on; ‐‐开启trace
mysql> select * from employees where name > 'a' order by position;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
{
"rows_estimation": [ ‐‐预估表的访问成本
{
"table": "`employees`",
"range_analysis": {
"table_scan": { ‐‐全表扫描情况
"rows": 10123, ‐‐扫描行数
"cost": 2054.7 ‐‐查询成本
} /* 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": 5061, ‐‐索引扫描行数
"cost": 6074.2, ‐‐索引使用成本
"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": 10123,
"access_type": "scan", ‐‐访问类型:为scan,全表扫描
"resulting_rows": 10123,
"cost": 2052.6,
"chosen": true, ‐‐确定选择
"use_tmp_table": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 10123,
"cost_for_plan": 2052.6,
"sort_cost": 10123,
"new_cost_for_plan": 12176,
"chosen": true
}
] /* considered_execution_plans */
}
mysql> select * from employees where name > 'zzz' order by position;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
mysql> set session optimizer_trace="enabled=off"; ‐‐关闭trace
name>'zzz'时索引扫描的成本低于全表扫描,所以mysql最终选择索引扫描。
常见sql深入优化
Order by与Group by优化
Case1:
分析:
利用最左前缀法则:中间字段不能断,因此查询用到了name索引,从key_len=74也能看出,age索引列用在排序过程中,因为Extra字段里没有using filesort
Case 2:
分析:
从explain的执行结果来看:key_len=74,查询使用了name索引,由于用了position进行排序,跳过了age,出现了Using filesort。
Case 3:
分析:
查找只用到索引name,age和position用于排序,无Using filesort。
Case 4:
分析:
和Case 3中explain的执行结果一样,但是出现了Using filesort,因为索引的创建顺序为name,age,position,但是排序的时候age和position颠倒位置了。
Case 5:
分析:
与Case 4对比,在Extra中并未出现Using filesort,因为age为常量,在排序中被优化,所以索引未颠倒,不会出现Using filesort。
Case 6:
分析:
虽然排序的字段列与索引顺序一样,且order by默认升序,这里position desc变成了降序,导致与索引的排序方式不同,从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。
Case 7:
分析:
对于排序来说,多个相等条件也是范围查询
Case 8:
可以用覆盖索引优化
- MySQL支持两种方式的排序filesort和index,Using index是指MySQL扫描索引本身完成排序。index效率高,filesort效率低。
- order by满足两种情况会使用Using index。
1) order by语句使用索引最左前列。
2) 使用where子句与order by子句条件列组合满足索引最左前列。
- 尽量在索引列上完成排序,遵循索引建立(索引创建的顺序)时的最左前缀法则。
- 如果order by的条件不在索引列上,就会产生Using filesort。
- 能用覆盖索引尽量用覆盖索引
- group by与order by很类似,其实质是先排序后分组,遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意,where高于having,能写在where中的限定条件就不要去having限定了。
Using filesort文件排序原理详解
- 单路排序:是一次性取出满足条件行的所有字段,然后在sort buffer中进行排序;用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >
- 双路排序(又叫回表排序模式):是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行ID,然后在 sort buffer 中进行排序,排序完后需要再次取回其它需要的字段;用trace工具可以看到sort_mode信息里显示< sort_key, rowid >
- 如果字段的总长度小于max_length_for_sort_data ,那么使用单路排序模式;
- 如果字段的总长度大于max_length_for_sort_data ,那么使用双路排序模式。
示例验证下各种排序方式:
查看以下sql对应trace结果如下(只展示排序部分):
mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on; ‐‐开启trace
mysql> select * from employees where name = 'zhuge' order by position;
mysql> 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 */
mysql> set max_length_for_sort_data = 10; ‐‐employees表所有字段长度总和肯定大于10字节
mysql> select * from employees where name = 'zhuge' order by position;
mysql> select * from information_schema.OPTIMIZER_TRACE;
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
- 从索引name找到第一个满足 name = ‘zhuge’ 条件的主键 id
- 根据主键 id 取出整行,取出所有字段的值,存入 sort_buffer 中
- 从索引name找到下一个满足 name = ‘zhuge’ 条件的主键 id
- 重复步骤 2、3 直到不满足 name = ‘zhuge’
- 对 sort_buffer 中的数据按照字段 position 进行排序
- 返回结果给客户端
- 从索引 name 找到第一个满足 name = ‘zhuge’ 的主键id
- 根据主键 id 取出整行,把排序字段 position 和主键 id 这两个字段放到 sort buffer 中
- 从索引 name 取下一个满足 name = ‘zhuge’ 记录的主键 id
- 重复 2、3 直到不满足 name = ‘zhuge’
- 对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序
- 遍历排序好的 id 和字段 position,按照 id 的值回到原表中取出所有字段的值返回给客户端
索引设计原则