怎样让你的SQL使用最佳索引！_sql执行器 最优索引是什么

---

一.示例表

示例表：

 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=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表'; 
1
2
3
4
5
6
7
8
9
10
11
12

可以看到这里我们建立了以name,age,position顺序的联合索引

二、索引使用情况

1.全值匹配

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';
1

走了联合索引中的name索引这里key_len的算法再给大家说一遍 name的长度为24 字符类型为UTF-8所以长度为：3n+2=3*24+2=74

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;
1
2

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';
1

2.最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

EXPLAIN SELECT * FROM employees WHERE age = 22 AND position ='manager';
1

EXPLAIN SELECT * FROM employees WHERE position = 'manager'; 
1

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei
1

这里可以看到我们的联合索引有个最左前缀法则，如果我们不按顺序或者跳过索引中的列是不会走索引的！！！

3.不在索引列上做任何操作

（计算、函数、（自动or手动）类型转换），会导致索引失效而转 向全表扫描

EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';
1

例如：
给hire_time增加一个普通索引：

ALTER TABLE `employees` 
ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE ;
1
2

用函数不走索引示例

EXPLAIN select * from employees where date(hire_time) ='2018-09-30';
1

不用函数走索引示例

EXPLAIN select * from employees where hire_time >='2018-09-30 00:00:00' and hire_time <='2018-09-30 23:59:59';
1

4.存储引擎不能使用索引中范围条件右边的列

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';
1

可以看出来这里是没有走我们联合索引position字段

5.尽量使用覆盖索引，减少select *语句

EXPLAIN SELECT name,age FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';
1

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';
1

6.！=或者<> 无法使用索引会导致全表扫描

EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';
1

7.is null,is not null 也无法使用索引

EXPLAIN SELECT * FROM employees WHERE name is null
1

8.尽量避免like以通配符开头

EXPLAIN SELECT * FROM employees WHERE name like '%Lei'
1

通配符放在后边则会走索引

EXPLAIN SELECT * FROM employees WHERE name like 'Lei%'
1
2

解决like’%字符串%'索引不被使用的方法？
a）使用覆盖索引，查询字段必须是建立覆盖索引字段

EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%';
1

b）如果不能使用覆盖索引则可能需要借助搜索引擎

9.字符串不加单引号索引失效

EXPLAIN SELECT * FROM employees WHERE name = '1000'; //会走索引
EXPLAIN SELECT * FROM employees WHERE name = 1000;//不会走 底层回去做转义封装
1
2

10.少用or或in

用or或in查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、 表大小等多个因素整体评估是否使用索引，详见范围查询优化

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei';
1

结果不唯一！跟数据量和MySQL底层对SQL语句的优化有关系

11.范围查询优化

给年龄添加单值索引

ALTER TABLE `employees` 
ADD INDEX `idx_age` (`age`) USING BTREE ;
1
2

explain select * from employees where age >=1 and age <=2000;
1

没走索引原因： mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索 引。比如这个例子，可能是由于单次数据量查询过大导致优化器最终选择不走索引

优化方法： 可以讲大的范围拆分成多个小范围

explain select * from employees where age >=1 and age <=1000;
explain select * from employees where age >=1001 and age <=2000;
1
2

三、Mysql如何选择合适的索引

 EXPLAIN select * from employees where name > 'a';
1

如果用name索引需要遍历name字段联合索引树，然后还需要根据遍历出来的主键值去主键索引树里再去查出最终数据，成本比全表扫描 还高，可以用覆盖索引优化，这样只需要遍历name字段的联合索引树就能拿到所有结果，如下：

EXPLAIN select name,age,position from employees where name > 'a' ;
1

mysql> EXPLAIN select * from employees where name > 'zzz' ;
1

对于上面这两种 name>‘a’ 和 name>‘zzz’ 的执行结果，mysql最终是否选择走索引或者一张表涉及多个索引，mysql最 终如何选择索引，我们可以用trace工具来一查究竟，开启trace工具会影响mysql性能，所以只能临时分析sql使用，建议分析完就关闭

1.trace工具用法：

set session optimizer_trace="enabled=on",end_markers_in_json=on;  ‐‐开启trace  
select * from employees where name > 'a' order by position; 
SELECT * FROM information_schema.OPTIMIZER_TRACE;
1
2
3

查看trace字段//只摘抄了几个重要信息
"join_preparation": { ‐‐第一阶段：SQL准备阶段
"join_optimization": { ‐‐第二阶段：SQL优化阶段
"rows_estimation": [ ‐‐预估表的访问成本 
 {
  "table": "`employees`", 
  "range_analysis": { 
  "table_scan": { ‐‐全表扫描情况 
  "rows": 10123, ‐‐扫描行数 
  "cost": 2054.7 ‐‐查询成本 
     } /* table_scan */,
"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 
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

结论：全表扫描的成本低于索引扫描，所以mysql最终选择全表扫描
我们在执行SQL语句的时候MySQL会给我们去预估一下怎样执行会最优，会给我们实现优化对比。所以大部分情况下理论知识是行得通的，有些情况是需要我们用trace工具去分析为什么去选择这样的引擎

四、总结：