MySql 索引详解（3）

最新推荐文章于 2024-09-11 10:18:51 发布

Asatake

最新推荐文章于 2024-09-11 10:18:51 发布

阅读量37

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/Asatake/article/details/133653115

版权

MySql 索引详解（3）

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为 覆盖索引。 我们知道在 InnoDB 存储引擎中，如果不是主键索引，叶子界定啊存储的是主键+列值。最终还是要 “回表”，也就是要通过主键再查找一次，这样就会比较慢。而覆盖索引就是把要查询出的列和索引是对饮的，不做回表操作。

联合索引

使用表中的多个字段创建索引，就是 联合索引，也叫 组合索引 或 复合索引。

最左前缀匹配原则

最左前缀哦i配原则指的是，在使用联合索引时，MySql 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果拆线呢条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一段数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询才会停止匹配。对于 <= >= BETWEEN LIKE 前缀的范围查询，并不会停止匹配，所以，我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。

正确使用索引的一些建议

选择合适的字段创建索引

不为 Null 的字段： 索引字段的数据应该尽量不为 NULL ，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0、1、true、false 这样语义较为清晰的短值或者短字符作为代替。
被频繁查询的字段： 我们创建索引的字段应该是查询操作非常频繁的字段
被作为条件查询的字段： 被作为 WHERE 条件查询的字段，应该考虑建立索引
频繁需要排序的字段： 索引已经排序，这样查询可以利用索引的排序，加快排序的查询时间。
被频繁用于连接的字段： 经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列设计到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

被频繁更新的字段应该慎重建立索引

索然索引能够带来效率上的提升，但是维护索引的成本也是不小的。如果一个字段不被经常访问，反而被进厂修改，那么就更不应该在这种字段上建立索引了。

限制每张表上的索引数量

索引并不是越多越好，建议单张表的索引不超过 5 个！索引可以提高效率同样可以降低效率。

索引可以增加查询效率，但童谣也会降低插入和更新的频率，甚至有些情况下会降低查询效率。

因为 MySql 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于拆线呢，就会增加 MySql 优化器生成执行计划的时间，同样会降低查询性能。

尽可能的考虑建立联合索引而不是单列索引

因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

注意避免冗余索引

冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

字符串类型的字段使用前缀索引代替普通索引

前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。

# 避免索引失效

索引失效也是慢查询的主要原因之一，常见的导致索引失效的情况有下面这些：

使用 SELECT * 进行查询; SELECT * 不会直接导致索引失效（如果不走索引大概率是因为 where 查询范围过大导致的），但它可能会带来一些其他的性能问题比如造成网络传输和数据处理的浪费、无法使用索引覆盖;
创建了组合索引，但查询条件未遵守最左匹配原则;
在索引列上进行计算、函数、类型转换等操作;
以 % 开头的 LIKE 查询比如 like '%abc';
查询条件中使用 or，且 or 的前后条件中有一个列没有索引，涉及的索引都不会被使用到;
发生隐式转换；