MySQL 索引创建和优化实践

[[434021]]

本文转载自微信公众号「运维开发故事」，索实践作者老郑。引创优化转载本文请联系运维开发故事公众号。建和

本文以 employees 表为例子，索实践结合具体的引创优化索引运用实践案例，通过分析 EXPLAIN 关键字获取执行计划，建和来验证我们这些索引实践。索实践如果是引创优化执行计划相关的详细信息，大家可以参考 mysql 官网 explain 介绍。建和

mysql 版本: 5.7.23

使用的索实践表

复制CREATETABLE employees (   id int(11) NOTNULL AUTO_INCREMENT,   namevarchar(24) NOTNULLDEFAULT COMMENT 姓名,   age int(11) NOTNULLDEFAULT0 COMMENT 年龄,   position varchar(20) NOTNULLDEFAULT COMMENT 职位,   hire_time timestampNOTNULLDEFAULTCURRENT_TIMESTAMP COMMENT 入职时间,   PRIMARYKEY (id),   KEY idx_name_age_position USING BTREE (name, age, position)  ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARSET = utf8 COMMENT 员工记录表;  INSERTINTO employees (name, age, position, hire_time) VALUES (LiLei, 22, manager, NOW());  INSERTINTO employees (name, age, position, hire_time) VALUES (WaKen, 23, dev, NOW());  INSERTINTO employees (name, age, position, hire_time) VALUES (Lucy, 23, dev, NOW());  1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.

复合索引数据结构

下面是一个复合索引的是示意图

索引最佳实践

1. 全值匹配

对于全值匹配，就是引创优化查询条件能够命中索引的全部列，或者复合索引需要命中左边的建和数据

复制EXPLAIN SELECT * FROM employees WHEREname= LiLei;  1.

复制EXPLAIN SELECT * FROM employees WHEREname= LiLeiAND age = 22;  1.

复制EXPLAIN SELECT * FROM employees WHEREname= LiLeiAND 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 WHEREname = LiLei;  1.

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

使用全值匹配能够正常使用索引

复制EXPLAIN SELECT * FROM employees WHEREname = LiLei;  1.

使用 left 或者其他的数据库函数导致索引未命中。会走一个全表扫描。

复制EXPLAIN SELECT * FROM employees WHEREleft(name,3) = LiLei;  1.

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

全值匹配如下。

复制EXPLAIN SELECT * FROM employees WHEREname= LiLeiAND age = 22 AND position =manager;  1.

如果我们中间增加一个范围查找，会导致右边的查询条件无法使用索引。我们对比一下，上下两条 sql 的差别。

复制EXPLAIN SELECT * FROM employees WHEREname= LiLeiAND age > 22 AND position =manager;  1.

5.尽量使用覆盖索引(只访问索引的查询(索引列包含查询列))，减少select *语句

对于 select 指定字段查询，如果查询的所有列都是索引上的数据，那么可以减少 “回表”。查询的效率高于 select

*复制explain selectname,age from employees wherename = Lileiand age = 23 and position= manage;  1.

复制EXPLAIN SELECT * FROM employees WHEREname= LiLeiAND age = 23 AND position =manager;  1.

6.mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描

复制EXPLAIN SELECT * FROM employees WHEREname != LiLei;  1.

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

因为在 mysql 变长的云服务器提供商数据类型中，如 varchar 。null 不会存储，它需要一个额外的标志位来存储。所以如果我们使用 is null , is not null 是无法使用索引的。所以我们在 DDL 语句中尽量让每个字段都有默认值。

复制EXPLAIN SELECT * FROM employees WHEREnameisnull;  1.

8.like以通配符开头($abc...)mysql索引失效会变成全表扫描操作

复制EXPLAIN SELECT * FROM employees WHEREnamelike%Lei;  1.

复制EXPLAIN SELECT * FROM employees WHEREnamelikeLei%;  1.

问题：解决like%字符串%索引不被使用的方法?a)使用覆盖索引，查询字段必须是建立覆盖索引字段

复制EXPLAIN SELECTname,age,position FROM employees WHEREnamelike%Lei%;  1.

b)当覆盖索引指向的字段是varchar(380)及380以上的字段时，覆盖索引会失效!

9. 如果查询条件导致类型转换会导致索引失效

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

加上单引号，无需数据转换 name 存储数据也是 varchar 类型。

复制EXPLAIN SELECT * FROM employees WHEREname = 1000;  1.

如果不加单引号，发生数据类型转换导致本次查询索引失效。

复制EXPLAIN SELECT * FROM employees WHEREname = 1000;  1.

10.少用or,用它连接时很多情况下索引会失效

复制EXPLAIN SELECT * FROM employees WHEREname = LiLeiorname = HanMeimei;  1.

小总结

like KK%相当于=常量，%KK和%KK% 相当于范围 下面是常见的几种索引查询失效或者，可用的企商汇判定

总结 1

总结 2

参考文档

https://blog.csdn.net/qq_38138069/article/details/82998658

https://www.processon.com/u/5e26625de4b00fbcc45e576d

https://zhuanlan.zhihu.com/p/94190700

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

本文地址：http://www.bzuk.cn/news/26c8899885.html