mysql深度优化1-----mysql索引最佳实践

示例表：
先创建一张员工表，在表内插入一些数据
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());

字段及索引如下


可以看到有一个联合索引，包含了name age position 字段
现在我准备了三条sql语句，我们可以来看看这三条sql语句底层的执行效果

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 AND position = ‘manager’ ;

我们通过执行这三条sql语句可以发现，根据联合索引的“左前缀法则”，可以看到这三条sql语句都走了索引，但是用到联合索引的字段是有所区别的 ，见下图，结果分别用结果1结果2结果3来表示



通过以上结果可以看出，他们用到的索引字段是有区别的，也就是三条结果的key_len（索引长度）长度存在差异

结果1———-索引长度74字节
结果2———-索引长度78字节
结果3———-索引长度140字节

根据索引长度的计算法则，存储varchar类型的字段时，假设字段长度为n,那么索引长度为3n + 2 是个字节长度，由于name 为varchar(24)，所以字段1的索引长度为74字节长
（(Character Set：utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列长度 + 1(允许null) + 2(变长列)）
在utf8-mb4字符类型下，索引长度为4n+2,如果列允许为空，索引长度+1，本文使用utf-8字符类型

由于int类型占用4个字节，所以结果2的索引长度为一个name的长度加上age的长度，为74 + 4字节，结果2的索引长度为78字节；

结果3同上计算方式，如果字段允许为空，mysql对于这种允许为空的字段需要进行特殊处理，需要花费一个字节记录这个字段是否为空，所以结果3的索引长度为140字节 ；

针对这个表我们可以做出一些相关的优化，优化原则如下：

1.全值匹配
针对以上表的联合索引来说，说白了就是尽量的将要查询的字段都走索引，对于最后的这条sql语句，它的执行效率也是三条sql语句中最高的，因为它将联合索引中所有的字段都用上了，所以在表中数据越多的时候，尽量的将联合索引中包括的字段都用上，以提升查询效率

2.最左前缀法则
如果索引了多列，如name ,age , position ，那么mysql会从最左前方开始查找索引，如果中间跳过的索引，比如
EXPLAIN SELECT FROM employees WHERE name = ‘lilei’ AND age = 22 AND position = ‘manager’ ;
变为
EXPLAIN SELECT FROM employees WHERE name = ‘lilei’ AND position = ‘manager’ AND age = 22;

这种情况也是会走索引的，因为mysql在sql语句执行之前会对我们的sql进行优化，会在底层将我们的查询条件与索引做相关匹配，但如果以上的sql语句变为
EXPLAIN SELECT FROM employees WHERE position = ‘manager’ AND age = 22;
开始少了个name作为查询条件，那么联合索引将失效，这会对我们的查询效率造成极大的影响

3.不要在索引列上做运算操作
下如果在索引列上做计算，排序求和，索引会失效继而转向全表扫描，下面是联合索引的结构
由下图可以看到，索引是有序排列的，如果在索引列上做计算或排序，如果做了排序或者跳过了某些字段，我们拿着这个索引的一部分去走这个索引，那么实际上这些字段是无序的，无序的数据无法在索引树上找到，所以会转为全表扫描

下面两条SQL语句对比
EXPLAIN SELECT FROM employees WHERE name = ‘lilei’ ;
EXPLAIN SELECT FROM employees WHERE LEFT(name,3) = ‘lilei’ ;


还有一种情况，转换时间格式是否会走索引？
现在我们在入职时间这一列加上索引
ALTER TABLE employees ADD INDEX idx_hire_time (hire_time) USING BTREE;

EXPLAIN SELECT FROM employees WHERE date(hire_time)=’2020-05-05’ ;

因为date（）算出来的值在索引树上找不到，所以无法走索引
如果第二个字段进行排序或范围查找，那么第三个字段也无法走索引

4.尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select * 语句
如下两个sql语句结果对比：

EXPLAIN SELECT name,age FROM employees WHERE name= ‘lilei’ AND age = 22 AND position = ‘manager’;

EXPLAIN SELECT FROM employees WHERE name= ‘lilei’ AND age = 22 AND position = ‘manager’;

5.mysql在使用不等于或者（！=或者<>）的时候无法使用索引会导致全表扫描
EXPLAIN SELECT FROM employees WHERE name != ‘lilei’ ;

6.is null ，is not null 一般情况下也不会走索引
如：
EXPLAIN SELECT * FROM employees WHERE name != null ;

7.like %(开头)索引会失效，直接变为全表扫描，但是like xxx%会走索引
如：
EXPLAIN SELECT FROM employees WHERE name LIKE ‘%li’ ;

EXPLAIN SELECT FROM employees WHERE name LIKE ‘li%’ ;

如何针对%开头的sql语句进行优化？
使用覆盖索引，查询字段必须是建立覆盖索引字段，也就是说索引字段包括所有查询字段
如下sql语句
EXPLAIN SELECT name,age,position FROM employees WHERE name LIKE ‘%li%’ ;

执行结果为index,性能有较为明显的提升
like KK%相当于常量，相当于sql进行了一次等值查询，%KK和%KK%相当于范围查询

8.字符串不加单引号索引失效
如下SQL
EXPLAIN SELECT FROM employees WHERE name = 1000 ;

EXPLAIN SELECT FROM employees WHERE name = ‘1000’ ;

在执行name = ?的时候，后台会帮你完成转型，如果类型不匹配，mysql后台会帮你转换，导致索引失效