数据库索引并不是万能的

InnoDB 是如何存储数据的?

MySQL 把数据存储和查询操作抽象成了存储引擎,不同的存储引擎,对数据的存储和读取方式各不相同。MySQL 支持多种存储引擎,并且可以以表为粒度设置存储引擎。因为支持事务,我们最常使用的是 InnoDB

虽然数据保存在磁盘中,但其处理是在内存中进行的。为了减少磁盘随机读取次数,InnoDB 采用而不是行的粒度来保存数据,即数据被分成若干页,以页为单位保存在磁盘中。InnoDB 的页大小,一般是 16KB。各个数据页组成一个双向链表,每个数据页中的记录按照主键顺序组成单向链表;每一个数据页中有一个页目录,方便按照主键查询记录。

数据库索引并不是万能的_第1张图片

如何建立合适的索引来方便定位记录所在的页?

页目录通过槽把记录分成不同的小组,每个小组有若干条记录。如图所示,记录中最前面的小方块中的数字,代表的是当前分组的记录条数,最小和最大的槽指向 2 个特殊的伪记录。有了槽之后,我们按照主键搜索页中记录时,就可以采用二分法快速搜索,无需从最小记录开始遍历整个页中的记录链表。

数据库索引并不是万能的_第2张图片

InnoDB 使用 B+ 树,既可以保存实际数据,也可以加速数据搜索,这就是聚簇索引。如果把上图叶子节点下面方块中的省略号看作实际数据的话,那么它就是聚簇索引的示意图。由于数据在物理上只会保存一份,所以包含实际数据的聚簇索引只能有一个。

InnoDB 会自动使用主键(唯一定义一条记录的单个或多个字段)作为聚簇索引的索引键,如果没有主键,就选择第一个不包含 NULL 值的唯一列,如果主键,唯一键都没有就会自动给你维护一个自增主键。上图方框中的数字代表了索引键的值,对聚簇索引而言一般就是主键。

B+ 树如何实现快速查找主键?

比如,我们要搜索上图中 PK=4 的数据,通过根节点中的索引可以知道数据在第一个记录指向的 2 号页中,通过 2 号页的索引又可以知道数据在 5 号页,5 号页就是实际的数据页,然后再通过二分法查找页目录马上可以找到记录的指针。

如何实现快速查找非主键字段?

二级索引,也叫作非聚簇索引、辅助索引。二级索引,也是利用的 B+ 树的数据结构,如下图所示:

数据库索引并不是万能的_第3张图片

这次二级索引的叶子节点中保存的不是实际数据,而是主键,获得主键值后去聚簇索引中获得数据行。这个过程就叫作回表

额外创建二级索引的代价

创建二级索引的代价,主要表现在维护代价空间代价回表代价三个方面。

1. 维护代价

创建 N 个二级索引,就需要再创建 N 棵 B+ 树,新增数据时不仅要修改聚簇索引,还需要修改这 N 个二级索引。

页中的记录都是按照索引值从小到大的顺序存放的,新增记录就需要往页中插入数据,现有的页满了就需要新创建一个页,把现有页的部分数据移过去,这就是页分裂

如果删除了许多数据使得页比较空闲,还需要进行页合并

页分裂和合并,都会有 IO 代价,并且可能在操作过程中产生死锁。

2. 空间代价

虽然二级索引不保存原始数据,但要保存索引列的数据,所以会占用更多的空间。

3. 回表代价

二级索引不保存原始数据,通过索引找到主键后需要再查询聚簇索引,才能得到我们要的数据。

索引开销的最佳实践

第一,无需一开始就建立索引,可以等到业务场景明确后,或者是数据量超过 1 万、查询变慢后,再针对需要查询、排序或分组的字段创建索引。创建索引后可以使用 EXPLAIN 命令,确认查询是否可以使用索引。

第二,尽量索引轻量级的字段,比如能索引 int 字段就不要索引 varchar 字段。索引字段也可以是部分前缀,在创建的时候指定字段索引长度。针对长文本的搜索,可以考虑使用 Elasticsearch 等专门用于文本搜索的索引数据库。

第三,尽量不要在 SQL 语句中 SELECT *,而是 SELECT 必要的字段,甚至可以考虑使用联合索引来包含我们要搜索的字段,既能实现索引加速,又可以避免回表的开销。

索引失效

  1. 索引只匹配列前缀,因为索引B+树中行数据按照索引值排序,只能根据前缀进行比较
  2. 条件涉及函数操作无法走索引,因为索引保存的是索引的原始值,而不是经过函数计算后的值。
  3. 联合索引只匹配左边的列,因为联合索引的数据是按照第一列排序,第一列数据相同时才会按照第二列排序。

数据库基于成本决定是否走索引

MySQL在查询数据之前,会先对可能的方案做执行计划,然后依据成本决定走哪个执行计划。

这里成本包括IO成本和CPU成本

  • IO成本,是从磁盘把数据加载到内存的成本。默认情况下,读取数据页的IO成本常数是1(读一个页的成本是1)
  • CPU成本,是检测数据是否满足条件和排序等CPU操作的成本。默认情况下,检测记录的成本是0.2

基于此,全表扫描的成本:聚簇索引中的记录依次和给定的搜索条件做比较,把符合搜索条件的记录加入结果集的过程。

成本=IO成本(页面数)+CPU成本(记录数)* 0.2

你可能感兴趣的