MySQL索引优化实战宝典

        MySQL索引是MySQL数据库用于快速查找和访问数据的一种数据结构，它就像书的目录一样，可以帮助数据库系统更快地定位到所需数据的位置，从而大大提高查询性能。

        下面来看一下索引分类

一、索引分类        

        本文介绍平时使用最多的基于 InnnoDB 存储引擎的索引，InnoDB 的索引是基于 B+ 树的。B+ 树是一种自平衡的树形数据结构，所有数据都存储在叶子节点上，并且叶子节点之间通过双向链表连接，形成有序序列，非常适合范围查询和全表扫描。

                

        索引的具体分类如下：

        聚集索引

        表中数据按照主键顺序，同时叶子节点存放的为整张表的行记录数据，也将叶子节点称为数据页，对于主键的排序和范围查找速度非常快。

        辅助索引

        叶子节点并不包含行记录的全部数据。每个叶子节点除了包含键值外，还包含一个书签，该书签用来告诉 Innodb 存储引擎哪里可以找到与索引对应的行数据。由于 Innodb 存储引擎表是索引组织表，因此 Innodb 存储引擎的辅助索引书签就是相应行记录的聚集索引键。当通过辅助索引来寻找数据时，Innodb 存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后在通过主键索引来找到一个完成的行记录（回表）。

        联合索引

        对多个列同时创建的索引，它基于这些列的值来组织数据，优化查询性能。联合索引主要应用于涉及多个列的查询条件，可以显著加快数据检索速度，减少磁盘 I/O。

        覆盖索引

        从辅助索引就能查到想要的记录，而不需要回表。

        前缀索引

        用字段的一部分做索性，例如有邮箱字段 email，建立索引时可以使用 email(6) 指定只使用前六个字符作为索引。使用前缀索引可以极大的节省空间，但是使用前缀索引后不能使用覆盖索引了，因为引擎不知道前缀索引是否包含整个字符串，所以需要额外的回表操作才能判断。

        索引应尽可能的小一些，因为这样占用的空间小，相同的数据页就能容纳更多的索引数据。

二、普通索引和唯一索引如何选择

        分别从查询过程和更新过程来说明一下如何选择普通索引和唯一索引。

        查询过程

        假设执行的语句为 select id from T where k=5。这个查询语句的查找过程，先是通过 B+ 树从树根开始，按层搜索到叶子节点，然后可以认为数据页内部通过二分法来定位记录。

• 对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。如果这条数据在当前数据页的最后，就需要读下一个页，但一个页可以放近千个 key，这种情况出现的概率很低；
• 对于唯一索引来说，由于唯一索引的唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

        这个不同带来的性能差距微乎其微。

        更新过程

        当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

        将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

        对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入 (4,400) 这个记录，就要先判断现在表中是否已经存在 k=4 的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。

        因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

        普通索引和唯一索引应该怎么选择。其实，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，建议尽量选择普通索引。

三、索引优劣判断

        通过 show index from table 可以观察表上的索引，其中有个属性相当重要——Cardinality，这个值非常关键。它是评估索引质量与效率的一个重要因素。在MySQL等数据库系统中，索引基数越高，意味着索引列的唯一性越强，索引筛选数据的能力也就越强。

        优化器会根据这个值来判断是否使用这个索引。但是这个值并不是实时更新的，因为代价太大。可以通过 analyze table 来更新索引。

        Cardinality/rows 应尽可能接近 1，如果非常小，那么就可以考虑有没有必要创建这个索引。

四、索引优化

        4.1 MRR优化        

        MySQL 5.6 版本开始支持 Multi-Range Read 优化。目的是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问。原理如下：

        范围扫描与回表： 当查询涉及二级索引，并且需要根据索引结果回表查询其他非索引列时，如果没有 MRR 优化，MySQL 会先扫描二级索引获取所有符合条件的记录的主键值，然后逐个通过主键值回表查询对应的行数据。这个过程中的回表操作往往会造成大量的随机 I/O。

        MRR 优化步骤：

• 收集排序: MRR 优化在扫描二级索引的过程中，会收集所有需要回表查询的主键值，并对这些主键值进行排序。
• 批量回表: 排序后的主键值集合按照顺序进行批量回表查询，即一次性请求一批相邻主键值对应的行数据，而不是一次一个随机访问。
• 减少随机 I/O: 通过批量读取和顺序访问，MRR 优化有效地减少了在硬盘上寻找数据的随机I/O 次数，转而采用更高效的顺序 I/O，这在大量数据处理时尤其重要。

        4.2 索引下推优化

        Index Condition Pushdown 同样是 MySQL 5.6 开始支持的一种根据索引进行查询的优化方式。当进行索引查询时首先根据索引来查找记录，然后在根据 where 条件来过滤记录。在支持索引下推后，MySQL 数据会在去除索引的同时，判断是否可以进行 where 条件过滤，也就是将where 的部分过滤操作放在了存储引擎层，减少了回表操作，从而提高了性能。

• 在不使用 ICP 的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给 MySQL 服务器，服务器然后判断数据是否符合条件 。
• 在使用 ICP 的情况下，如果存在某些被索引的列的判断条件时，MySQL 服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合 MySQL 服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给 MySQL 服务器 。
• 索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少 MySQL 服务器从存储引擎接收数据的次数。

        注意：MySQL 的索引下推（Index Condition Pushdown, ICP）优化特性只在查询条件包含索引列，并且这部分条件能够利用索引进行过滤的情况下生效。

        4.3 最左匹配原则

        如果我们使用的是复合索引，应该尽量遵循最左前缀匹配原则。MySQL 会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。

select * from t where a = 1 and b = 2 and c > 3 and d = 4;

        如上 sql，那我们建立的复合索引应该是index(a,b,d,c)而不是index(a,b,c,d)，因为 c 是范围查询，当 MySQL 遇到范围查询就停止索引匹配。

        还要注意一点：最左前缀原则不但是复合索引的最左N个字段；也可以是单列（字符串类型）索引的最左M个字符。

        例如我们常说的like关键字，尽量不要使用全模糊查询，因为这样用不到索引，所以建议使用右模糊查询。

        4.3 覆盖索引

        很多时候还可以用覆盖索引来优化 sql。

        情况一：sql只查询主键作为返回值。

        主键索引（聚簇索引）的叶子节点是整行数据，而普通索引（二级索引）的叶子节点是主键的值。所以当我们的sql值查询主键值，可以直接获取对应叶子节点的内容，而避免了回表。

        情况二：sql查询字段就在索引里

        复合索引，假如我们有一个复合索引 index(name,age)，sql 如下

select name, age from t where name like '苏%';

        由于字段 name 是右模糊查询所以可以走复合索引，然后匹配到 name 时，不需要回表，因为 sql 只查询 name 和 age，索引直接就返回索引值就 ok 了。

        4.4 order by 索引优化

        order by 后面的字段尽量是带索引的，这样能避免 sort_buffer 进行排序。假如一条 sql，根据生日查询所有学生的信息：

select * from student order by birthday desc;

        那么为了提升查询性能，应该在birthday字段上建立索引。

        select 后面不要带上不必要的字段，因为如果当行长度太长会导致查询数据太多，MySQL 会利用 rowid 排序来代替全字段排序，这样会导致回表操作。查什么字段就跟上什么字段。

        如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer中， 这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

        4.5 or 索引优化

        在 InnoDB 引擎下or关键字无法使用复合索引。

select id, product_name from orders where mobile = '12345678' or user_id = 6;

        一般我们为了提升上面 sql 的效率，会想着为字段 mobile 和 user_id 建立一个复合索引index(mobile, user_id); 可是我们看执行计划的话提示并没有使用复合索引，所以 or 关键字无法命中复合索引。        

往期经典推荐

深入JVM内核揭示Java多态背后的神秘机制-CSDN博客

MySQL数据更新流程原来这么复杂-CSDN博客

MySQL自增主键有什么作用？来自大厂的使用经验_数据库主键自增-CSDN博客

MySQL计数优化探秘：COUNT(*)、COUNT(主键)与索引字段，谁是性能王者？-CSDN博客

TiDB内核解密：揭秘其底层KV存储引擎如何玩转键值对_tidb 的key value是如何做到的-CSDN博客