本文以MySQL InnoDB引擎为基础,讲解索引相关概念以及优化手段,很适合开发以及业务同学参考,避免工作中因为DB性能导致的一系列雪崩问题。
在InnoDB下,索引文件与数据文件是合并在一起的,想象一下,一颗树,非叶子节点都是索引文件,叶子节点都是数据文件,从而构成一张完整的表数据,因此还有一种专业说法叫索引组织表。该模式下索引文件即数据文件,数据文件即索引文件,但是为了提高查询速度,必然会在逻辑上分为索引与数据两部分内容,索引大多数时候都是存放在内存中,当一个查询在索引中定位数据后,会去磁盘中加载对应的数据内容,因此一定程度上磁盘IO的次数成为了提高索引性能的关键点。
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上建立索引,索引字段的选择一般参考以下几个因素:
如何编写走索引的SQL,该问题需要了解MySQL针对索引所定制的一些规则,比如最左前缀匹配规则,索引失效场景规则,以及一些常见查询索引解决方案。该部分情况比较复杂,因此不展开详细说,了解大概规则后,最佳做法是使用explan关键词进行分析,针对分析结果进行SQL或者索引的调整。
还有一些可以很好的从应用层解决的问题,比如一张用户黑名单表,使用 身份证 + 手机号作为唯一约束,那么索引该怎么设计才能提高查询效率?这种场景设计比较多,比如直接建立自增id主键,然后身份证+手机号联合索引,如果更新不频繁还可以通过md5方式,取一个hash后作为主键,然而这种场景下应用层完全可以使用布隆过滤器进行预先过滤,布隆过滤器中不存在的则一定不存在,大大会减少DB查询量,因此设计索引时需要关注具体业务场景,不能只看索引。
三星索引法是《数据库索引设计与优化》提出的一种建立合理索引的措施,具体如下:
select id, name, age from user where name='link' order by age desc
为例,索引(name, age, id) 是一个三星索引,而索引(name) 不是一个三星索引,然而在业务中往往索引(name)就能够满足该查询,从而减少内存消耗,因此三星索引可以作为一个索引好不好的参考,而具体业务场景下没必要过分追求三星索引。为什么 MySQL 使用 B+ 树 · Why’s THE Design?