性能调优3. Mysql索引优化实战一

1. 前言准备

1.1. 示例表

	#创建表
‌‌‌　　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=1 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());

	#插入一些示例数据

‌‌‌　　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('zhuge',i),i,'dev');
‌‌‌　　set i=i+1;
‌‌‌　　end while;
‌‌‌　　end;;
‌‌‌　　delimiter ;
‌‌‌　　call insert_emp();

2. 索引设计原则

2.1. 代码先行，索引后上

‌‌‌　　建完表不要马上建议索引，一般应该等到主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立索引。

2.2. 联合索引尽量覆盖条件

‌‌‌　　比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含SQL语句里的where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足SQL查询的最左前缀原则。

‌‌‌　　MySQL大多数情况下只会选择一个索引。

‌‌‌　　如果想规避字段数据重复，可以给字段建唯一索引。

2.3. 不要在小基数(DISTINCT字段后结果集大小)字段上建立索引

‌‌‌　　索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段，其值不是男就是女，那么该字段的基数就是2。

‌‌‌　　如果对这种小基数字段建立索引的话，还不如全表扫描了，因为你的索引树里就包含男和女两种值，根本没法进行快速的二分查找，那用索引就没有太大的意义了。

‌‌‌　　一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查找的优势来。

2.4. 长字符串可以采用前缀索引

‌‌‌　　尽量对字段类型较小的列设计索引。

‌‌‌　　比如tinyint之类的，字段类型较小的话，占用磁盘空间也会比较小，搜索性能也会比较好一点。

‌‌‌　　只针对字段类型小一点的列做索引，也不是绝对的，很多时候就是要针对varchar(255)这种字段建立索引，哪怕多占用一些磁盘空间也是有必要的。

‌‌‌　　对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个字符建立索引，对这个字段里的每个值的前20个字符放在索引树里，如 KEYindex(name(20),age,position)，能过滤90%情况下的查询。

‌‌‌　　如在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来完整的name字段值进行比对。

‌‌‌　　但是order by name，group by name。采用前缀索引排序是没法触发索引的。

2.5. where与order by冲突时优先where

‌‌‌　　where和order by出现索引设计冲突时，往往都是让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序。

‌‌‌　　因为大多数情况基于索引进行where筛选可以最快速度筛选出需要的少部分数据，然后做排序的成本可能会小很多。

2.6. 基于慢SQL查询做优化

‌‌‌　　根据监控后台的一些慢SQL，存储到文件或者数据库。针对这些慢SQL查询做特定的索引优化。建议从库去开启，监听查询语句。

3. 索引优化技巧

3.1. 前言

‌‌‌　　使用到表还是开头的数据表。

‌‌‌　　下面索引都是根据B+Tree数据结构构建。

3.2. 联合索引的优化

3.2.1. 遵守最左前缀法则

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

‌‌‌　　下面三条SQL语句只有第一条，走了索引，key_len=74。

‌‌‌　　EXPLAIN SELECT * FROM employees WHERE name = 'Bill' and age = 31;
‌‌‌　　EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev';
‌‌‌　　EXPLAIN SELECT * FROM employees WHERE position = 'manager';

3.2.2. 尽量全值匹配

‌‌‌　　如果用联合索引，那么查询条件完全匹配联合索引的要比只匹配一部分的，查询效率来的高。识别度高，扫描行数就越少。

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

‌‌‌　　表字段如下，索引长度：24*3字节+2字节。

‌‌‌　　全值匹配下

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

‌‌‌　　索引长度：24*3字节+2字节+4字节+20*3字节+2字节。

‌‌‌　　注意

‌‌‌　　联合索引全匹配下，SQL语句查询条件的顺序，不影响最左前缀原理。MySQL内部会优化排好序(建议还是查询时候顺序写好)。

3.2.3. 联合索引第一个字段不要使用范围查询

‌‌‌　　MySQL内部可能觉得第一个字段就用范围，结果集很大，回表效率不高，不如全表扫描。

‌‌‌　　EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' 

‌‌‌　　解决方式

‌‌‌　　满足覆盖索引下，可以使用覆盖索引优化

3.3. 强制走索引优化

‌‌‌　　使用force index(索引字段)，强制查询走指定索引。

‌‌‌　　EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 AND position ='manager'

‌‌‌　　虽然使用了强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，但是最终查找效率不一定比全表扫描高，因为还需要回表，效率不高。

‌‌‌　　例子

‌‌‌　　比对强制走索引和不走索引效率。

‌‌‌　　set global query_cache_size=0; 用于将全局查询缓存大小设置为 0，表示关闭查询缓存。

‌‌‌　　set global query_cache_type=0; 用于将查询缓存类型设置为 0，即禁用查询缓存。

‌‌‌　　Mysql8.0去除了缓存功能。

   ‐‐ 关闭查询缓存
‌‌‌　　set global query_cache_size=0;
‌‌‌　　set global query_cache_type=0;

‌‌‌　　‐‐ 执行时间0.333s
‌‌‌　　SELECT * FROM employees WHERE name > 'LiLei';

‌‌‌　　‐‐ 执行时间0.444s
‌‌‌　　SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei';

3.3. 覆盖索引优化

‌‌‌　　查询尽量使用覆盖索引，即查询列包含在索引里头（不需要回表查找等操作，提高效率），减少 select * 语句。

‌‌‌　　用覆盖索引下，原先联合索引的范围查询不走索引也可能走索引，MySQL有自己的成本计算。

‌‌‌　　select * 查询不走索引。

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

‌‌‌　　使用覆盖索引走索引。

‌‌‌　　EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager'

‌‌‌　　如果查找字段很多，又不能覆盖索引，可以考虑搜索引擎。

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

‌‌‌　　这些操作后查匹配索引变得无序，还不如走主键索引全表扫描取数据。

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

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

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

‌‌‌　　避免字段类型的转换，还有就是建议字符串都用单引号不要双引号。

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

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

3.5. 少用or或in

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

‌‌‌　　in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描（当然也不一定）。

‌‌‌　　数据量大情况下

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

3.6. MySQL在使用不等于（！=或者<>），not in，not exists的时候无法使用索引会导致全表扫描。<小于、>大于、<=、>=这些，MySQL内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

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

3.7. is null,is not null 一般情况下也无法使用索引

‌‌‌　　EXPLAIN SELECT * FROM employees WHERE name is null;