MySQL– 索引指南

  • 2020 年 2 月 10 日
  • 筆記

本文以MySQL InnoDB引擎为基础，讲解索引相关概念以及优化手段，很适合开发以及业务同学参考，避免工作中因为DB性能导致的一系列雪崩问题。

索引的数据结构

在InnoDB下，索引文件与数据文件是合并在一起的，想象一下，一颗树，非叶子节点都是索引文件，叶子节点都是数据文件，从而构成一张完整的表数据，因此还有一种专业说法叫索引组织表。该模式下索引文件即数据文件，数据文件即索引文件，但是为了提高查询速度，必然会在逻辑上分为索引与数据两部分内容，索引大多数时候都是存放在内存中，当一个查询在索引中定位数据后，会去磁盘中加载对应的数据内容，因此一定程度上磁盘IO的次数成为了提高索引性能的关键点。

B+树结构

B+树为InnoDB的数据结构，与B树最大的不同有两点。1：其叶子节点只存放数据，非叶子节点存放索引。2：叶子节点由于数据都是有序的，因此叶子节点之间使用双向链表连接。换一种描述，为增加树的高度，从而减少查询的磁盘IO次数。接下来分别阐述为什么这么做。

1. 为什么非叶子节点不能存放数据？ 该问题本质上是为什么不能使用B树的结构。这里借用 为什么 MySQL 使用 B+ 树 · Why’s THE Design? 中的描述图。 B树的非叶子节点中会存放数据，因此每一次查询都必须从根节点开始，因为每一个节点上都可能会有数据，因此对于范围类型查询就会很吃力，磁盘IO次数会增加不少。其次由于节点中包含数据，一般情况下数据比索引会大很多，因此也不适合全部存放到内存，索引本身就会消耗掉一些磁盘IO，而B+树则改进了这些缺点，更加优化了索引定位后，再获取数据这一策略。

2. 叶子节点为什么使用双向链表？ 对于B+树，其叶子节点使用双向链表关联起来，假设查询条件where age > 5 and age < 9，其中age存在索引，那么根据age查询到9这个节点后，其可以利用索引的有序性，直接通过双向链表进行范围查询，而不需要再次从根节点出发。

索引的类型

聚集索引

在InnoDB中，聚集索引指的是主键所对应的B+树，之所以叫聚集，因为其是根据主键索引进行存储，索引即数据，数据即索引。 一张表只能有一个主键，一张表只能有一个聚集索引，因此聚集索引的选择就比较真贵，对无意义的id来管理聚集索引的方式应该有所排斥。

非聚集索引

在InnoDB中，主键以外的辅助索引为非聚集索引，也有说法叫次级索引，其特点是叶子节点会指向对应的主键索引，而不是直接指向数据，索引本身就是索引。因此当一个查询使用到了辅助索引，其本质上会查询两颗树，一是该辅助索引对应的B+树，找到主键后，再去主键索引对应的B+树去获取对应的数据。

索引的优化

阐述上述结构的最终目地是为了优化，而优化更多的是从开发者角度建立合适的表结构以及索引，写出走合理索引的SQL。

选择合适的列建立索引

索引的选择与具体的业务息息相关，比如用户表一般都是根据手机号以及邮箱查询，那么就不适合在name上建立索引，索引字段的选择一般参考以下几个因素：

  • 区分度要高，例如sex与phone两列相比，sex的区分度太低，此时及时条件走了索引，仍然会命中大量数据，然后进一步筛选。
  • 有序，索引具有有序性，每次写入或者删除数据时，会涉及B+树的调整，因此当表的写入量很大时，需要考虑这一步的消耗
  • 长度合适，索引可以认为是全部放到内存中的，因此合适的大小能够减少大量的内存消耗，针对超长字符串可以选择指定索引前缀长度等方式进行优化。

编写走索引的SQL

如何编写走索引的SQL，该问题需要了解MySQL针对索引所定制的一些规则，比如最左前缀匹配规则，索引失效场景规则，以及一些常见查询索引解决方案。该部分情况比较复杂，因此不展开详细说，了解大概规则后，最佳做法是使用explan关键词进行分析，针对分析结果进行SQL或者索引的调整。

还有一些可以很好的从应用层解决的问题，比如一张用户黑名单表,使用 身份证 + 手机号作为唯一约束,那么索引该怎么设计才能提高查询效率？这种场景设计比较多，比如直接建立自增id主键，然后身份证+手机号联合索引，如果更新不频繁还可以通过md5方式，取一个hash后作为主键，然而这种场景下应用层完全可以使用布隆过滤器进行预先过滤，布隆过滤器中不存在的则一定不存在，大大会减少DB查询量，因此设计索引时需要关注具体业务场景，不能只看索引。

三星索引法

三星索引法是《数据库索引设计与优化》提出的一种建立合理索引的措施，具体如下：

  • 一星索引：取出所有等值谓词的列，作为索引的开头，目的是减少扫描数据数量
  • 二星索引：将order by中的列加入到索引当中，目的是避免文件排序增加磁盘IO
  • 三星索引：将查询语句剩余的列加入到索引当中，目的是走覆盖索引，避免磁盘IO 以select id, name, age from user where name='link' order by age desc为例，索引(name, age, id) 是一个三星索引，而索引(name) 不是一个三星索引，然而在业务中往往索引(name)就能够满足该查询，从而减少内存消耗，因此三星索引可以作为一个索引好不好的参考，而具体业务场景下没必要过分追求三星索引。

参考

为什么 MySQL 使用 B+ 树 · Why’s THE Design? MySQL 索引设计概要

VirMach 便宜 VPS

QNews

QNews