共计 2554 个字符，预计需要花费 7 分钟才能阅读完成。
这篇文章给大家介绍 MySQL 索引有哪些法则，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。
一、最佳左前缀法则
1. 定义
在创建了多列索引的情况下，查询从索引的最左前列开始且不能跳过索引中的列。
最佳左前缀法则就是说如果创建了多个索引，在使用索引时要按照创建索引的顺序来使用，不能缺少或跳过，当然如果只使用最左边的索引列，也就是第一个索引是可以的。
2. 环境准备
DROP TABLE IF EXISTS `tb_emp`; CREATE TABLE `tb_emp` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `age` int(11) NOT NULL, gender varchar(10) NOT NULL, email varchar(20), PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO `tb_emp` (name,age,gender,email) VALUES (Tom , 22 , male , [email protected] INSERT INTO `tb_emp` (name,age,gender,email) VALUES (Mary , 21 , female , [email protected] INSERT INTO `tb_emp` (name,age,gender,email) VALUES (Jack , 27 , male , [email protected] INSERT INTO `tb_emp` (name,age,gender,email) VALUES (Rose , 23 , female , [email protected]3. 创建组合索引
create index idx_all on tb_emp(name,age,gender); show index from tb_emp;这里用火车头代表 name, 车厢代表 age, 车尾代表 gender。
4. 只有火车头
说明：
索引的创建顺序为 name，age，gender;
直接使用 name(火车头)作为条件，可以看到 type=ref，key_len=82，ref=const，效果还行。
5. 只有车厢
说明：没使用火车头(name)，直接用车厢，导致走全表扫描(type=ALL)
6. 火车头加车厢、火车头加车尾
说明：
火车头加车厢、火车头加车尾, 虽然都是 type=ref，但是观察 key_len 和 ref 两项，并对比只有火车头中的结果，可得出在使用火车头 (name) 和车尾 (gender) 时，只使用了部分索引也就是火车头 (name) 的索引。
通俗理解：火车头单独跑没问题，火车头与直接相连的车厢一起跑也没问题，但是火车头与车尾，如果中间没有车厢，只能火车头自己跑。
7. 火车头加车厢加车尾
说明：火车头加车厢加车尾，三者串联，就变成了奔跑的小火车。type=ref，key_len=128，ref=const，const，const。
二、索引列不做计算
在索引列上做任何操作 (计算、函数、(自动 or 手动) 类型转换)，会导致索引失效从而转向全表扫描。
1. 函数计算
说明：这里使用了函数计算，type=ALL，导致索引失效。
2. 隐式类型转换
说明：这里 123 是字符串，而 123 是数字，发生了隐式类型转换，导致全表扫描(type=ALL)
三、范围右边索引列全失效
存储引擎不能使用索引中范围右边的列，也就是说范围右边的索引列会失效。
对以上 4 个 SQL 进行分析：
条件单独使用 name 时，type=ref，key_len=82，ref=const。
条件加上 age 时(使用常量等值)，type=ref，key_len=86，ref=const，const。
当全值匹配时，type=ref，key_len=128，ref=const，const，const。说明索引全部用上，从 key_len 与 ref 可以看出。
当使用范围时(age 27)，type=range，key_len=86，ref=Null，可以看到只使用了部分索引，但 gender 索引没用上。
结论：范围右边的索引列失效。
四、尽量使用覆盖索引
1. 覆盖索引定义
如果一个索引包含 (或覆盖) 所有需要查询的字段的值，称为 lsquo; 覆盖索引 rsquo;。即只需扫描索引而无须回表。
只扫描索引而无需回表的优点：
索引条目通常远小于数据行大小，只需要读取索引，则 mysql 会极大地减少数据访问量。
因为索引是按照列值顺序存储的，所以对于 IO 密集的范围查找会比随机从磁盘读取每一行数据的 IO 少很多。
一些存储引擎如 myisam 在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用
innodb 的聚簇索引，覆盖索引对 innodb 表特别有用。(innodb 的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询)
覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引不存储索引列的值，所以 mysql 只能用 B -tree 索引做覆盖索引。
当发起一个索引覆盖查询时，在 explain 的 extra 列可以看到 using index 的信息
2. 对比是否使用覆盖索引好处
尽量使用覆盖索引(查询列和索引列尽量一致，通俗说就是对 A、B 列创建了索引，然后查询中也使用 A、B 列)，减少 select * 的使用。
mysql explain select * from tb_emp where name= Jack and age=27 and gender= male mysql explain select name,age,gender from tb_emp where name= Jack and age=27 and gender= male说明：对比两个 sql，第一个使用 select *，第二个使用覆盖索引(查询列与条件列对应)，可看到 Extra 从 Null 变成了 Using index，提高检索效率。
关于 MySQL 索引有哪些法则就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
