三、索引选择实践与Explain详解

玉衡子
161 阅读15分钟

三、索引选择实践与Explain详解

在上一篇文章中，我们介绍了 InnoDB 索引的数据结构模型，今天我们再继续聊聊跟 MySQL 索引有关的概念。

覆盖索引

覆盖索引指的是，索引包含了满足查询需要的所有数据，而不需要再去回表查询。 还是以上一章介绍的图来介绍：

二级索引例子.png

例如select id from t where name = xxx这个语句。 在这个二级索引中，我们可以直接通过name就可以获取到 id 这个字段的值放入结果集中。也就是说，在这个查询里面，索引 name 已经“覆盖了”我们的查询需求，我们称为覆盖索引。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

联合索引

基于上面覆盖索引的说明，我们来讨论一个问题：在一个市民信息表上，是否有必要将身份证号和名字建立联合索引？

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

我们知道，身份证号是市民的唯一标识。也就是说，如果有根据身份证号查询市民信息的需求，我们只要在身份证号字段上建立索引就够了。

而再建立一个（身份证号、姓名）的联合索引，是不是浪费空间？

如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。

最左前缀原则

但是如果无脑为每一种查询都设计一个索引，会导致索引太多，更新和插入的成本会增加。但如果我现在要按照市民的身份证号去查他的家庭地址呢？虽然这个查询需求在业务中出现的概率不高，但总不能让它走全表扫描吧？

这里B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

为了直观地说明这个概念，我们用（name,age,position）这个联合索引来分析下面这个索引结构：

最左前缀原则示例.png

（注：图中紫色部分为主键id，这里以日期字段作为示例）

可以看到，索引项是按照索引定义里面出现的字段顺序排序的。

当你的逻辑需求是查到所有名字是“Bill”的人时，可以快速定位到叶子节点中的第一个“Bill”，然后向后遍历得到所有需要的结果。

如果你要查的是所有名字第一个字是“L”的人，你的 SQL 语句的条件是"where name like ‘L%’"。这时，你也能够用上这个索引，查找到第一个符合条件的记录是2011-08-07这条，然后向后遍历，直到不满足条件为止。

可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

由于这个特性，我们设计了（name,age,position）这个索引后就不需要再额外设计（name,age）或name单列索引了。

索引下推

上一段我们说到满足最左前缀原则的时候，最左前缀可以用于在索引中定位记录。这时，你可能要问，那些不符合最左前缀的部分，会怎么样呢？ 还是以（name,age,position）这个索引为例，假设我们有这样一个场景sql：

select * from t where name='Bill' and age=30 and position='dev';

在MySQL 5.6之前，会先找到联合索引树中第一个满足条件name='Bill'的记录，然后使用主键ID回表查询整行记录再判断是否满足其他条件（age=30 and position='dev'）。如上图这种情况就需要回表三次。

而MySQL 5.6引入的索引下推优化（index condition pushdown），可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。在上面例子中，InnoDB在（name,age,position）索引内部就判断了age是否等于30且position是否等于'dev'，对于不等于的记录，直接判断并跳过。如上图这种情况就只需要回表一次。

索引选择实践

对于普通索引和唯一索引该如何选择的问题，我们需要先了解一下查询和更新在B+树的大致过程：

场景 以如下场景举例：id是主键，k是普通索引或唯一索引 select id from t where k = 10

查询过程

  • 对于普通索引，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足k=10条件的记录才结束。
  • 对于唯一索引，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

这里看似普通索引查找次数可能要多一些，但是我们知道InnoDB的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在InnoDB中，每个数据页的大小默认是16KB。

因为引擎是按页读写的，所以说，当找到k=10的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。这种很小内存操作带来的影响可以忽略不记的。

当然要是k=10这条记录恰好是在页尾，那么就需要读取下一个数据页，涉及到磁盘IO操作。不过对于整型字段，一个数据页可以放近千个key，因此出现这种情况的概率千分之一会很低。

所以，这里我们计算平均性能差异时，仍可以认为这个操作成本对于现在的CPU来说可以忽略不计。

更新过程

对于更新对于普通索引和唯一索引的影响分析就不得不先介绍一下change buffer。

change buffer是用来存放那些不影响数据一致性的更新操作的缓冲区。

有了change buffer就不需要从磁盘中读入这个数据页再更新了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作即可。通过这种方式就能保证这个数据逻辑的正确性。

将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。除了访问这个数据页会触发merge 外，系统有后台线程会定期merge。在数据库正常关闭（shutdown）的过程中，也会执行merge操作。

显然，使用change buffer的可以减少更新时的随机磁盘读IO次数，并且可以减少更新时将数据页读入buffer pool带来的内存占用，提高buffer pool内存利用率。

虽然change buffer听起来好像很好，但是使用不当会造成反效果。

对于唯一索引来说，每次更新操作都需要确保记录是否违反唯一性约束，因此需要先判断表中是否存在要变更的记录，所以其本身就需要将数据读入内存。

因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

假设现在要执行这样一条sql语句：

insert into t (id,k) values (8, 5);

第一种情况：要更新的页在内存

  • 对于普通索引来说，找到7和9之间的位置，插入这个值，语句执行结束。
  • 对于唯一索引，找到7和9之间的位置，判断是否有冲突，插入这个值，语句执行结束。 二者性能几乎无差别

第二种情况：要更新的页不在内存

  • 对于普通索引来说，不需要将数据页读入内存，直接记录这个变更到change buffer后结束。
  • 对于唯一索引，需要将数据页读入内存，找到7和9之间的位置，判断是否有冲突，插入这个值，语句执行结束。

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

因此写多读少使用changebuffer可以加快执行速度(减少数据页磁盘io); 但如果业务模型是：写后立马会做查询, 则会触发changebuff立即merge, 这样的场景磁盘io次数不会减少,反而会增加changebuffer的维护代价。

因此需要综合业务场景考虑选择合适索引。

Explain详解

参考文档：

explain中的常见列信息如下：

示例表：
DROP TABLE IF EXISTS `actor`; 
CREATE TABLE `actor` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017-12-22 15:27:18'), (2,'b','2017-12-22 15:27:18'), (3,'c','2017-12-22 15:27:18');

DROP TABLE IF EXISTS `film`;
CREATE TABLE `film` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');

DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor` (
  `id` int(11) NOT NULL,
  `film_id` int(11) NOT NULL,
  `actor_id` int(11) NOT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

id列

id是select的执行序号，越大越先执行，如果相同则从上往下顺序执行。

type列

explain中的select_type列.png

select的类型

  • primary：复杂查询中最外层的 select
  • subquery：包含在 select 中的子查询（不在 from 子句中）
  • derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）

table列

这一列表示 explain 的一行正在访问哪个表。 explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;

explain中的table列.png

当 from 子句中有子查询时，table列是  格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。 当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。

type列

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。 依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL

一般来说，得保证查询达到range级别，最好达到ref

const和system：

mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是const的特例，表里只有一条元组匹配时为system

explain extended select * from (select * from film where id = 1) tmp;

explain中的type列_const.png

show warnings;

explain中的type列_const_show_warnings.png

eq_ref: primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

如explain select * from film_actor left join film on film_actor.film_id = film.id;

ref: 相比 eq_ref，不使用唯一索引，而是使用普通索引或者联合索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

  1. 简单 select 查询，name是普通索引（非唯一索引） explain select * from film where name = 'film1';
  2. 关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分。 explain select film_id from film left join film_actor on film.id = film_actor.film_id;

range: 范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。 explain select * from actor where id > 1;

index: 扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些。

ALL: 即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。

possible_keys列

这一列显示查询可能使用哪些索引来查找。explain时可能出现possible_keys有列，而key显示NULL的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。 

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查where子句看是否可以创造一个适当的索引来提高查询性能，然后用explain查看效果。

keys列

这一列显示mysql实际采用哪个索引来优化对该表的访问。如果没有使用索引，则该列是NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

key_len列

这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

key_len计算规则如下：

字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节:

  • char(n)：如果存汉字长度就是 3n 字节
  • varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串

数值类型

  • tinyint：1字节
  • smallint：2字节
  • int：4字节
  • bigint：8字节　　

时间类型　

  • date：3字节
  • timestamp：4字节
  • datetime：8字节

如果字段允许为 NULL，需要1字节记录是否为 NULL

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

ref列 这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）

rows列 这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。只是一个估计值。

extra列 这一列展示的是额外信息。常见的重要值如下：

  • Using index：使用覆盖索引

  • Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖

  • Using index condition：查询的列不完全被索引覆盖，where条件中是一个联合索引前缀的范围,如explain select * from film_actor where film_id > 1;

  • Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。 actor.name没有索引，此时创建了张临时表来distinct explain select distinct name from actor; film.name建立了idx_name索引，此时查询时extra是using index,没有用临时表

  • Using filesort:将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。 explain select * from actor order by name; actor.name未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录。 film.name建立了idx_name索引,此时查询时extra是using index。

总结

本章介绍了什么是覆盖索引、联合索引、最左前缀原则、索引下推以及普通索引和唯一索引该如何选择等相关知识，并在最后还补充了explain中列的含义。

从普通索引和唯一索引的选择开始，和你分享了数据的查询和更新过程，然后说明了 change buffer 的机制以及应用场景，最后讲到了索引选择的实践。由于唯一索引用不上 change buffer 的优化机制，因此如果业务可以接受，从性能角度出发我建议你优先考虑非唯一索引。

avatar
文章
阅读
粉丝