几乎所有的业务项目都会涉及数据存储,虽然当前各种NoSQL和文件系统大行其道,但MySQL等关系型数据库因为满足ACID、可靠性高、对开发友好等特点,仍然最常被用于存储重要数据。在关系型数据库中,索引是优化查询性能的重要手段。
为此,我经常看到一些同学一遇到查询性能问题,就盲目要求运维或DBA给数据表相关字段创建大量索引。显然,这种想法是错误的。今天,我们就以MySQL为例来深入理解下索引的原理,以及相关误区。
InnoDB是如何存储数据的?
MySQL把数据存储和查询操作抽象成了存储引擎,不同的存储引擎,对数据的存储和读取方式各不相同。MySQL支持多种存储引擎,并且可以以表为粒度设置存储引擎。因为支持事务,我们最常使用的是InnoDB。为方便理解下面的内容,我先和你简单说说InnoDB是如何存储数据的。
虽然数据保存在磁盘中,但其处理是在内存中进行的。为了减少磁盘随机读取次数,InnoDB采用页而不是行的粒度来保存数据,即数据被分成若干页,以页为单位保存在磁盘中。InnoDB的页大小,一般是16KB。
各个数据页组成一个双向链表,每个数据页中的记录按照主键顺序组成单向链表;每一个数据页中有一个页目录,方便按照主键查询记录。数据页的结构如下:
页目录通过槽把记录分成不同的小组,每个小组有若干条记录。如图所示,记录中最前面的小方块中的数字,代表的是当前分组的记录条数,最小和最大的槽指向2个特殊的伪记录。有了槽之后,我们按照主键搜索页中记录时,就可以采用二分法快速搜索,无需从最小记录开始遍历整个页中的记录链表。
举一个例子,如果要搜索主键(PK)=15的记录:
- 先二分得出槽中间位是(0+6)/2=3,看到其指向的记录是12<15,所以需要从#3槽后继续搜索记录;
- 再使用二分搜索出#3槽和#6槽的中间位是(3+6)/2=4.5取整4,#4槽对应的记录是16>15,所以记录一定在#4槽中;
- 再从#3槽指向的12号记录开始向下搜索3次,定位到15号记录。
理解了InnoDB存储数据的原理后,我们就可以继续学习MySQL索引相关的原理和坑了。
聚簇索引和二级索引
说到索引,页目录就是最简单的索引,是通过对记录进行一级分组来降低搜索的时间复杂度。但,这样能够降低的时间复杂度数量级,非常有限。当有无数个数据页来存储表数据的时候,我们就需要考虑如何建立合适的索引,才能方便定位记录所在的页。
为了解决这个问题,InnoDB引入了B+树。如下图所示,B+树是一棵倒过来的树:
B+树的特点包括:
- 最底层的节点叫作叶子节点,用来存放数据;
- 其他上层节点叫作非叶子节点,仅用来存放目录项,作为索引;
- 非叶子节点分为不同层次,通过分层来降低每一层的搜索量;
- 所有节点按照索引键大小排序,构成一个双向链表,加速范围查找。
因此,InnoDB使用B+树,既可以保存实际数据,也可以加速数据搜索,这就是聚簇索引。如果把上图叶子节点下面方块中的省略号看作实际数据的话,那么它就是聚簇索引的示意图。由于数据在物理上只会保存一份,所以包含实际数据的聚簇索引只能有一个。
InnoDB会自动使用主键(唯一定义一条记录的单个或多个字段)作为聚簇索引的索引键(如果没有主键,就选择第一个不包含NULL值的唯一列)。上图方框中的数字代表了索引键的值,对聚簇索引而言一般就是主键。
我们再看看B+树如何实现快速查找主键。比如,我们要搜索PK=4的数据,通过根节点中的索引可以知道数据在第一个记录指向的2号页中,通过2号页的索引又可以知道数据在5号页,5号页就是实际的数据页,然后再通过二分法查找页目录马上可以找到记录的指针。
为了实现非主键字段的快速搜索,就引出了二级索引,也叫作非聚簇索引、辅助索引。二级索引,也是利用的B+树的数据结构,如下图所示:
这次二级索引的叶子节点中保存的不是实际数据,而是主键,获得主键值后去聚簇索引中获得数据行。这个过程就叫作回表。
举个例子,有个索引是针对用户名字段创建的,索引记录上面方块中的字母是用户名,按照顺序形成链表。如果我们要搜索用户名为b的数据,经过两次定位可以得出在#5数据页中,查出所有的主键为7和6,再拿着这两个主键继续使用聚簇索引进行两次回表得到完整数据。
考虑额外创建二级索引的代价
创建二级索引的代价,主要表现在维护代价、空间代价和回表代价三个方面。接下来,我就与你仔细分析下吧。
首先是维护代价。创建N个二级索引,就需要再创建N棵B+树,新增数据时不仅要修改聚簇索引,还需要修改这N个二级索引。
我们通过实验测试一下创建索引的代价。假设有一个person表,有主键ID,以及name、score、create_time三个字段:
CREATE TABLE `person` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`score` int(11) NOT NULL,
`create_time` timestamp NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
通过下面的存储过程循环创建10万条测试数据,我的机器的耗时是140秒(本文的例子均在MySQL 5.7.26中执行):
CREATE DEFINER=`root`@`%` PROCEDURE `insert_person`()
begin
declare c_id integer default 1;
while c_id45678
原因也很简单,在联合索引的情况下,数据是按照索引第一列排序,第一列数据相同时才会按照第二列排序。也就是说,如果我们想使用联合索引中尽可能多的列,查询条件中的各个列必须是联合索引中从最左边开始连续的列。如果我们仅仅按照第二列搜索,肯定无法走索引。尝试把搜索条件加入name列,可以看到走了name_score索引:
EXPLAIN SELECT * FROM person WHERE SCORE>45678 AND NAME LIKE 'NAME45%'
需要注意的是,因为有查询优化器,所以name作为WHERE子句的第几个条件并不是很重要。
现在回到最开始的两个问题。
- 是不是建了索引一定可以用上?并不是,只有当查询能符合索引存储的实际结构时,才能用上。这里,我只给出了三个肯定用不上索引的反例。其实,有的时候即使可以走索引,MySQL也不一定会选择使用索引。我会在下一小节展开这一点。
- 怎么选择建联合索引还是多个独立索引?如果你的搜索条件经常会使用多个字段进行搜索,那么可以考虑针对这几个字段建联合索引;同时,针对多字段建立联合索引,使用索引覆盖的可能更大。如果只会查询单个字段,可以考虑建单独的索引,毕竟联合索引保存了不必要字段也有成本。
数据库基于成本决定是否走索引
通过前面的案例,我们可以看到,查询数据可以直接在聚簇索引上进行全表扫描,也可以走二级索引扫描后到聚簇索引回表。看到这里,你不禁要问了,MySQL到底是怎么确定走哪种方案的呢。
其实,MySQL在查询数据之前,会先对可能的方案做执行计划,然后依据成本决定走哪个执行计划。
这里的成本,包括IO成本和CPU成本:
- IO成本,是从磁盘把数据加载到内存的成本。默认情况下,读取数据页的IO成本常数是1(也就是读取1个页成本是1)。
- CPU成本,是检测数据是否满足条件和排序等CPU操作的成本。默认情况下,检测记录的成本是0.2。
基于此,我们分析下全表扫描的成本。
全表扫描,就是把聚簇索引中的记录依次和给定的搜索条件做比较,把符合搜索条件的记录加入结果集的过程。那么,要计算全表扫描的代价需要两个信息:
- 聚簇索引占用的页面数,用来计算读取数据的IO成本;
- 表中的记录数,用来计算搜索的CPU成本。
那么,MySQL是实时统计这些信息的吗?其实并不是,MySQL维护了表的统计信息,可以使用下面的命令查看:
SHOW TABLE STATUS LIKE 'person'
输出如下:
可以看到:
- 总行数是100086行(之前EXPLAIN时,也看到rows为100086)。你可能说,person表不是有10万行记录吗,为什么这里多了86行?其实,MySQL的统计信息是一个估算,其统计方式比较复杂我就不再展开了。但不妨碍我们根据这个值估算CPU成本,是100086*0.2=20017左右。
- 数据长度是4734976字节。对于InnoDB来说,这就是聚簇索引占用的空间,等于聚簇索引的页面数量*每个页面的大小。InnoDB每个页面的大小是16KB,大概计算出页面数量是289,因此IO成本是289左右。
所以,全表扫描的总成本是20306左右。
接下来,我还是用person表这个例子,和你分析下MySQL如何基于成本来制定执行计划。现在,我要用下面的SQL查询name>‘name84059’ AND create_time>‘2020-01-24 05:00:00’
EXPLAIN SELECT * FROM person WHERE NAME >'name84059' AND create_time>'2020-01-24 05:00:00'
其执行计划是全表扫描:
只要把create_time条件中的5点改为6点就变为走索引了,并且走的是create_time索引而不是name_score联合索引:
我们可以得到两个结论:
- MySQL选择索引,并不是按照WHERE条件中列的顺序进行的;
- 即便列有索引,甚至有多个可能的索引方案,MySQL也可能不走索引。
其原因就是,MySQL并不是猜拳决定是否走索引的,而是根据成本来判断的。虽然表的统计信息不完全准确,但足够用于策略的判断了。
不过,有时会因为统计信息的不准确或成本估算的问题,实际开销会和MySQL统计出来的差距较大,导致MySQL选择错误的索引或是直接选择走全表扫描,这个时候就需要人工干预,使用强制索引了。比如,像这样强制走name_score索引:
EXPLAIN SELECT * FROM person FORCE INDEX(name_score) WHERE NAME >'name84059' AND create_time>'2020-01-24 05:00:00'
我们介绍了MySQL会根据成本选择执行计划,也通过EXPLAIN知道了优化器最终会选择怎样的执行计划,但MySQL如何制定执行计划始终是一个黑盒。那么,有没有什么办法可以了解各种执行计划的成本,以及MySQL做出选择的依据呢?
在MySQL 5.6及之后的版本中,我们可以使用optimizer trace功能查看优化器生成执行计划的整个过程。有了这个功能,我们不仅可以了解优化器的选择过程,更可以了解每一个执行环节的成本,然后依靠这些信息进一步优化查询。
如下代码所示,打开optimizer_trace后,再执行SQL就可以查询
information_schema.OPTIMIZER_TRACE表查看执行计划了,最后可以关闭optimizer_trace功能:
SET optimizer_trace="enabled=on";
SELECT * FROM person WHERE NAME >'name84059' AND create_time>'2020-01-24 05:00:00';
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";
对于按照create_time>'2020-01-24 05:00:00’条件走全表扫描的SQL,我从OPTIMIZER_TRACE的执行结果中,摘出了几个重要片段来重点分析:
-
使用name_score对name84059