mysql索引优化实践

一 数据准备

员工表，id自增主键，name，age，position构成联合辅助索引

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='员工记录表';

INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

二 优化策略

2.0 列的离散程度

离散程度的计算公式：count(distinct column_name)：count(*)，就是用去重后的列值个数比个数。值在 (0,1] 范围内。离散程度越高，选择性越好
如下表中各个字段，明显能看出Id的选择性比gender更高

mysql> select * from user;
+----+--------------+------+--------+
| id | name         | age  | gender |
+----+--------------+------+--------+
| 20 | 君莫笑       |   15 |      1 |
| 40 | 苏沐橙       |   12 |      0 |
| 50 | 张楚岚       |   25 |      1 |
| 60 | 诸葛青       |   27 |      1 |
| 61 | 若有人兮     |   38 |      0 |
| 64 | 冯宝宝       |   18 |      0 |
+----+--------------+------+--------+

为什么说离散型越高，选择型越好？
因为离散度越高，通过索引最终确定的范围越小，最终扫描的行数也就越少。

2.1 全值匹配

如果用到联合索引，最好where用到联合索引的所有列，并且按照索引列的顺序，where条件后面排列
（注意：即使你按照错误的顺序where后面排列，结果可能依然会用索引，因为mysql有优化器，会自动帮你去处理，排好正确的顺序，但是很明显我们自己养好一个习惯）
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' and age=22;

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' and age=22;

2.2 最左前缀原则

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

（1）根据联合索引的B+树数据结构，总是从索引的第一个列开始匹配，跳过索引的第一列直接无法匹配

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

（2）从索引的第一列开始匹配，但是跳过第二列，直接进入第三列，导致断节了，现匹配找到name为lilei的所有记录，然后再去这所有的记录里面去寻找position = 'manager'的记录，等于全表扫描了

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

（3）使用索引的非最左前缀列

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

2.3 不在索引列上做任何操作（计算、函数、（自动或者手动）类型转换），会导致索引失效，从而转向全表扫描 *****

原因：你对索引的值进行操作，索引的值都发生了改变，都不完整了，取了索引的中间一部分等，无法跟B+树上的索引key进行比较，从而走向全表扫描

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

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

2.4 对索引的部分列采用范围查找，导致存储引擎不能使用索引中范围条件右边的列

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

下面案例对age进行范围查询

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

从索引长度78可以看到用了name和age字段的索引，但是没用到position字段的索引，导致按照B+树结构，查找了name是lile，并且年龄是22的所有记录，然后用position=‘manager’再从结果中筛选，从而索引并未使用到position列，并不是精确的根据3个条件一次性查找到一条记录

2.5 尽量使用覆盖索引，避免使用select * 语句

如果查询的列，通过索引项的信息可直接返回，则该索引称之为查询SQL的覆盖索引。覆盖索引可以提高查询的效率

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

如上图，如果通过name进行数据检索：
select * from users where name = ?
需要需要在name索引中找到name对应的Id，然后通过获取的Id在主键索引中查到对应的行。整个过程需要扫描两次索引，一次name，一次id。

如果我们查询只想查询id的值，就可以改写SQL为：
select id from users where name = ?
因为只需要id的值，通过name查询的时候，扫描完name索引，我们就能够获得id的值了，所以就不需要再去扫面id索引，就会直接返回。

当然，如果你同时需要获取age的值：
select id，age from users where name = ?
这样就无法使用到覆盖索引了，当然我们可以采取创建name和age的联合索引来避免再获取到id去回表查询age
https://www.jianshu.com/p/8991cbca3854

知道了覆盖索引，就知道了为什么sql中要求尽量不要使用select *，要写明具体要查询的字段。其中一个原因就是在使用到覆盖索引的情况下，不需要进入到数据区，数据就能直接返回，提升了查询效率。在用不到覆盖索引的情况下，也尽可能的不要使用select *，如果行数据量特别多的情况下，可以减少数据的网络传输量。当然，这都视具体情况而定，通过select返回所有的字段，通用性会更强，一切有利必有弊。

2.6 使用不等于（!= 或者<>）的时候无法使用索引

原因：使用等于才能精确一条记录，使用大于或者小于可以走索引，但是使用不等于，根据B+树结构，无法使用方法走B+索引树，等于全表扫描

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

2.7 使用is null，is not null的时候无法使用索引

同2.6，无法使用方法走B+索引树检索结果

2.8 like模糊查询，以like通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作

原因：放在前面的话，前面都不知道是什么（通配符匹配一位或者多位置），B+树逐字匹配，无法匹配，但是如果like的通配符放在后面是可以走索引的，因为前面是确定的

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

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

优化：
问题：解决like'%字符串%'索引不被使用的方法？
如果必须要使用%在前面的模糊查询，考虑采用覆盖索引，让在索引的列的范围查询

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

注意：当覆盖索引指向的字段是varchar(380)及380以上的字段时，覆盖索引会失效！

2.9 字符串不加单引号索引会失效

原因：结合B+树索引结构，mysql底层执行了类型转换，将其转换为字符串，类似于第三条，对索引字段使用了函数

EXPLAIN SELECT * FROM employees WHERE name = '1000';
EXPLAIN SELECT * FROM employees WHERE name = 1000;

2.10 少用or，用or连接时会导致索引失效

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

二 优化总结