一、什么是索引

一般来说,数据库中所存放的数据量级动辄几十万上百万,在大型项目中甚至达到数千万,当我们需要查询某些符合我们要求的数据时,把一条一条数据进行检索对比,速度会非常慢。就像小时候我们拿着一本新华字典,碰到不认识的字时并不会一页一页每个字都看一下是不是要查的字,而是像老师教我们的那样,先从目录上查起。

MySQL的索引的作用就像是字典的目录一样,解决的主要问题就是提升查询速度。字典的目录会消耗一些纸张来印刷,同样的,数据库也会消耗一些存储来保存这些索引。

在与MySQL打交道的过程中经常会遇到查询速度慢的场景,我们通常会考虑是不是要给字段加个索引,具体添加唯一索引还是普通索引,加单字段索引还是复合索引,这都要根据具体场景而决定,一波操作猛如虎的添加显得格外莽撞,搞清楚索引的原理才能有效地解决问题。

二、索引模型

实现索引快速定位数据的方式有多种,我们称之为索引模型。常见的索引模型有三种,它们对应了三种简单的数据结构,分别是哈希表、有序数组、搜索树。

哈希表是以 key-value 键值形式存储的数据结构,只需要将索引字段进行哈希计算得到 key,数据存放到 value 中即可。由于不同的值换算的哈希结果可能一样,所以 value 中存放的是一个链表。如下图所示,当房屋表 HOUSE 以房源编号建立索引时,key 和 value 的存储形式如下,不同的房屋可能会对应同一个 key 。哈希表是无序的,这将导致一个问题,它不支持范围查询,必须进行全表扫描。

图片1hash.png

有序数组结构比较简单,如图所示,以房源编号递增顺序,无论是等值查询还是范围查询都非常简单快速。但这种结构只适用于静态数据存储,如果有更新数据操作,会引起此记录后面的所有记录都向后挪动,成本太大。一张存储历史数据的表用这种索引将非常适合。

图片2数组.png

二叉搜索树效率非常高,但实际上大多数数据库并不使用这种结构,MySQL的InnoDB采用的B+ 树,这是因为考虑到要存储到磁盘上,存储相同的数据量,二叉树的高度远高于N叉树,访问磁盘的次数也会增多,进而降低了效率。下图是B+树的结构。

图片3  树.png

三、InnoDB 索引分类

MySQL支持多种存储引擎,包括MyISAM、Memory、InnoDB、ARCHIVE等,不同存储引擎各有优劣,适用于不同的场景,例如MyISAM引擎是不支持事务的,但相比 InnoDB 引擎占用的存储空间要小很多。

我们常用的MySQL存储引擎是InnoDB,其索引分为两类。

聚集索引,也称为主键索引,如果一个表没有设置主键索引,则会自动生成一个rowid作为主键。InnoDB 存储引擎表是索引组织表,即表中数据按照主键顺序存放,聚集索引是按照每张表的主键构建一棵 B+ 树,并且叶子节点中存放着整张表的行记录数据,聚集索引的特性决定了索引组织表中数据也是索引的一部分。

很多情况下,查询优化器非常倾向于采用聚集索引,因为聚集索引能够在叶子节点上直接获取到数据,并且由于定义了数据的逻辑顺序,针对范围查询也能做到快速查找。

辅助索引,也称为二级索引,叶子节点存储的是主键的值,根据主键再去查找具体的行记录,这个过程称为回表。辅助索引又分为唯一索引、普通索引、联合索引等分类。下图是辅助索引和聚集索引的关系。

图片4  聚集辅助索引.png

四、B+ 树索引

InnoDB的数据和索引都是以页为单位进行存储、读取的,每页大小是16k。根据索引定位到的并不是行数据,而是行数据所在的页。

为了提高读取的性能,InnoDB 存储引擎还采用了预读技术,通过一次 IO 请求将多个页预读到缓冲池中,认为预读取的多个页大概率会被马上访问。

B+ 树的每一个节点实际对应的是一页,树的高度一般是2~3层,查找某一页的行记录,最多只需要2到3次 IO,一般磁盘每秒至少可以做100次IO,2~3次的IO 意味着查询时间只需0.02~0.03秒。如果恰巧数据页在内存中,那么消耗的时间将更少。

五、有索引一定快吗

建了索引后,查询一定会快吗?这是不一定的,我们分类讨论。

1、数据库中刷新脏页

对列建立了索引,用解释执行计划也能看到查询是用了索引,但是会碰到偶尔查询慢的情况,这种场景一般是数据库中刷新脏页。当向数据库中插入或者更新数据时,并不会立即写入磁盘,而是先将内存中的数据更新,并把更新的记录写到redo log 中,等空闲的时候再同步到磁盘上。redo log 的容量上有限的,遇到数据更新频繁,redo log 写满时,只能暂停其他操作全力将最新数据同步到磁盘上。

2、字段类型不匹配

上线之前按照清单对数据库表已经建立了索引,上线后慢查询攀升,不多久整个应用都挂了,这个实际案例中的慢查询原因就是因为字段类型不匹配。例如下面这个表,对列a 建立了索引,当使用第一种查询方式时将用不到索引。

CREATE TABLE `test` (  
  `id` bigint(10) NOT NULL,  
  `a` varchar(128) DEFAULT NULL,  
  `b` int(11) DEFAULT NULL,  
  PRIMARY KEY (`id`),  
  KEY `idx_a` (`a`) USING BTREE,  
  KEY `idx_b` (`b`) USING BTREE)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
EXPLAIN SELECT * FROM test WHERE a = 99;

图片5  字段类型不匹配.png

EXPLAIN SELECT * FROM test WHERE a = '99';

图片6  字段类型不匹配.png

3、对字段进行了运算或函数操作

在运算符的左边对列进行运算或者函数操作,都将使用不到索引,要避免这种写法,对自己的SQL也要善于用解释执行计划进行分析。

EXPLAIN SELECT * FROM test WHERE b > 100 + 1;

图片7  函数运算.png

EXPLAIN SELECT * FROM test WHERE b - 1 > 100;

图片8  函数运算.png

EXPLAIN SELECT * FROM test WHERE POW(b,2) > 100;

图片9  函数运算.png

4、查询优化器选错了索引

MySQL 的查询优化器会比较使用索引和全表扫描的代价,如果是全表扫描则扫描的行数就是表的总记录数,如果使用的是辅助索引,需要有回表操作,当索引的区分度不高时,使用索引的扫描次数有可能高于全表扫描。这就是索引的选择性(Selectivity),选择性 = 基数 / 记录数,其中基数代表了索引中不重复的值,选择性越高代表索引价值越大。

举例说明,向表 test 中插入一些数据如下,当使用列 a 进行查询时,发现并没有使用索引,原因就是列 a 的区分度很低,回表的代价超过了全表扫描。这也告诉我们,建立索引时需要考虑列的区分度是否真正值得建立索引。

图片10  区分度.png

EXPLAIN SELECT * FROM test WHERE a = '99';

图片11  区分度.png

六、总结

本文中我们讨论一些关于MySQL 索引相关的内容,包括索引相关的原理及实战经验,希望对你能有所帮助。