初识mysql索引

发表于 | 分类于 | | 阅读次数:

什么是索引，为什么要索引，这是熟悉数据库必不可少的知识。简单的来说，索引就是利用合理的数据结构来提高查询数据的效率。让我们通过这种数据结构而快速的找到我们想要的数据。

常见的索引模型

从数据结构来说有B+树索引、hash索引、FULLTEXT索引、R-Tree索引，此处介绍hash索引和b+树索引

  • hash索引

    hash索引顾名思义就是利用key-value存储数据的一种结构，只需要我们输入key，就能找到匹配的value，hash索引常用来匹配等值查询的场景，在mysql中MEMORY默认使用哈希索引。MEMORY用到的很少，因为它是把数据存到内存中，如果内存出现异常就会影响数据。如果重启或者关机，所有数据都会消失，不过在真实业务中key-value的查询常用nosql数据库来进行存储，此处不过多介绍。

  • B+树索引

    b+树索引是innodb中b—tree索引的实现，为什么用B+树作为innodb索引的实现呢？因为磁盘的存储原理决定了b+树是最适合它的存储模型。

在innodb中磁盘以页为单位，一页等于16K，我们访问磁盘的时候访问的是逻辑分区，机器扫描计算机对应的物理分区。随机io的时候扫描不同的磁盘需要磁头不停的移动，我们需要的数据可能分布在不同的页中，在没有任何索引的数据库中寻找数据，此时数据库相当于一个文件系统，需要cpu进行全表扫描。而b+树的一棵树对应innodb的一页，我们每次访问数据库只需要加载对应的页，从树的节点一直找下去，在固定的次数总会在叶子节点找到响应的数据，b+这种数据结构完美的契合了innodb这种以页为单位的存储引擎。下图为无索引情况下全图扫描

为什么不用b树作为innodb索引的数据结构？

先看一下b树的数据结构模型

在b树这种数据结构中，数据存储在节点之内，在查找数据的时候运气好可以快速在前面的节点中找到，但是，当数据是范围查询的时候，数据分配在不同的节点，cpu会进行多次io来遍历节点来进行查找数据，此时性能会极速下降。b树存在不稳定的查询性能。

重点，innodb的b-tree实现，b+树，先看图

在b+树这种数据结构中，数据都存储在叶子中，非叶子结构存储着数据的指针，数据的左节点都比自己小，右节点都大于等于自己。底层的叶子节点数据头尾用链表串联起来。这种数据每次查询的次数稳定，范围查询时在底层用链表顺序查询，大大提高的查询效率。

b+树与b树比较

  • 单一节点存储的元素更多，使得查询的IO次数更少，所以也就使得它更适合做为数据库MySQL的底层数据结构了。
  • 所有的查询都要查找到叶子节点，查询性能是稳定的，而B树，每个节点都可以查找到数据，所以不稳定。
  • 所有的叶子节点形成了一个有序链表，更加便于查找。

从索引类型上来看索引又分为聚簇索引和非聚簇索引

聚簇索引简单的来说就是所有的辅助索引都需要通过查找主键索引，从而查询真实数据的一个过程。而非聚簇索引中主键索引和辅助索引在叶子节点中都存储着数据的指针。需要查询真实的物理指针才能找到真实的数据。聚簇索引的代表是innodb，非聚簇索引的代表是myisam。

在聚簇索引中主键索引叶子节点存储的是数据，既是索引又是文件。innodb牺牲了空间换取了时间。而非聚簇索引牺牲时间换空间。

索引的选择

只要我们建立了索引，数据库优化的时候肯定会利用到索引吗？并不是，在mysql server层优化器进行选择的时候，会对索引进行一个采样，若cardinality太小,优化器不会利用到索引，会进行全表扫描。优化器索引规则查询sql如下

1
show index from <table>

索引利用到两种采样算法

index dive: 速度慢,但能得到精确的值（MySQL的实现是数索引对应的索引项个数，所以精确）

index statistics: 速度快,但得到的值未必精确
索引的优化

  1. 覆盖索引

在普通的索引查询中当中，当辅助索引查询到主键时，还需要从主键索引中查询数据，这个过程叫做回表。比如在一个表T中，表的初始化语句是

1
2
3
4
5
6
create table T (
ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

数据库插入数据

1
insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

数据库执行查询语句

1
select * from T where k between 3 and 5
数据库索引的执行过程为

  • 在k索引树上找到k=3的记录，取得 ID = 300；

  • 再到ID索引树查到ID=300对应的R3；

  • 在k索引树取下一个值k=5，取得ID=500；

  • 再回到ID索引树查到ID=500对应的R4；

  • 在k索引树取下一个值k=6，不满足条件，循环结束。

现在我们查询的是全部的数据，只有在主键索引上才又全部的数据，所以不得不回表，但是如果我们只需要获取主键id，那我们是不是就可以不需要再次回到主键索引去查询了呢？

比如

1
select id from T where id between 3 and 5

这次我们的执行顺序为

  • 在k索引树上找到k=3的记录，取得 ID = 300；

  • 在k索引树取下一个值k=5，取得ID=500；

  • 在k索引树取下一个值k=6，不满足条件，循环结束

在此次数据的查询中，我们每次都在索引上获取了查询结果，不需要再次回到主键索引才能获取我们需要的结果，这个过程就叫做覆盖索引，在我们的sql中需要有意的用到覆盖索引。避免不必要的查询。我们也可以通过执行计划看出来我们是否使用了覆盖索引。

1
explain select id from T where id between 3 and 5

在最后extra列中显示 using index表示此次sql的执行用到的覆盖索引

那是不是查询全表的sql就利用不到覆盖索引了呢？再举一个常见的例子

我们在大数据量中的数据查询中常使用到分页查询，在下面的例子中，全表数据量在226w左右

我们执行数据库查询语句

1
SELECT * from `phone_insurance_order_backup_20200318` LIMIT 2000000,10

执行耗时4.27秒

一个最简单的分页查询，数据库会进行扫描前2000010条数据，然后去除不要的前200w条数据。那这个sql有什么问题呢？

前200w条数据我们都是不需要的，但是却全部进行了查询，然后在丢弃，这都是无意义的查询，我们只需要后十条数据的结果。
下面看优化后的sql语句

1
SELECT * from `phone_insurance_order_backup_20200318` p JOIN (SELECT id from `phone_insurance_order_backup_20200318` limit 2000000,10) t1 on p.id=t1.id;

一共耗时395毫秒，性能提高十倍左右。为什么这么一个简单的查询性能提高这么多呢，答案就是利用到了覆盖索引。

在子查询中我们先查询出需要的数据主键id，避免不必要的全表查询全部数据，然后在用子查询查询到的id和表中的主键连接，从而找出来所需的数据。

1
(SELECT id from `phone_insurance_order_backup_20200318` limit 2000000,10) t1

可以看出，覆盖索引可以减少数据库的回表次数，显著的增强了查询性能。覆盖索引是常用的一种优化手段。

  1. 最左前缀匹配

在数据库中，我们总会对表建立索引，那是不是可以对每一个需要查询的字段都建立索引呢？肯定不是，那我们应该思考一下怎么合适的建立索引。那就需要认识一下b+树的最左前缀匹配原则。

最左前缀匹配的意思是索引查找最左N个字段，也可以是字符串索引的最左M个字符，不管该索引是普通索引还是联合索引。都可以利用最左前缀匹配来查询数据。举例子：

表结构为

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE `template_message` (
  `gmt_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录修改时间',
  `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
  `record_status` tinyint(2) NOT NULL DEFAULT '0' COMMENT '数据记录逻辑删除标识 0 正常 1 已删除',
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `out_user_id` varchar(64) NOT NULL DEFAULT '' COMMENT '支付宝uid',
  `form_id` varchar(64) NOT NULL DEFAULT '' COMMENT 'form_id或者trade_no',
  `pay_status` varchar(16) NOT NULL DEFAULT '' COMMENT '支付状态 已支付:already_pay 未支付:no_pay',
  `send_times` tinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT '发送次数',
  `send_date` timestamp NOT NULL DEFAULT '1970-01-01 08:00:01' COMMENT '发送时间',
  `form_type` varchar(16) NOT NULL DEFAULT '' COMMENT 'form_id类型 表单类:form 支付类:trade',
  `sub_type` varchar(16) NOT NULL DEFAULT ' ' COMMENT '子类型:业务自定义',
  `retry_times` tinyint(2) unsigned DEFAULT '0' COMMENT '重试次数',
  PRIMARY KEY (`id`),
  UNIQUE KEY `out_user_id` (`out_user_id`),
  KEY `idx_form_id` (`form_id`,`retry_times`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='消息模板';

随便插入几条测试数据

1
2
3
4
5
6
7
8
INSERT INTO `template_message` (`gmt_modified`, `gmt_create`, `record_status`, `id`, `out_user_id`, `form_id`, `pay_status`, `send_times`, `send_date`, `form_type`, `sub_type`, `retry_times`)
VALUES
	('2020-05-12 14:21:00', '1970-01-01 08:00:01', 0, 1, '1', '0232', '2', 2, '1970-01-01 08:00:01', '', ' ', 1),
	('2020-05-12 14:21:06', '2020-03-16 15:44:09', 0, 4, '4', '4121', '', 0, '1970-01-01 08:00:01', '', ' ', 0),
	('2020-05-12 14:21:12', '2020-03-16 15:44:14', 0, 5, '5', '3121', '', 0, '1970-01-01 08:00:01', '', ' ', 5),
	('2020-05-12 14:21:20', '1970-01-01 08:00:01', 0, 6, '6', '0232', '2', 2, '1970-01-01 08:00:01', '', ' ', 4),
	('2020-05-12 14:21:26', '2020-03-16 15:44:09', 0, 7, '7', '4121', '', 0, '1970-01-01 08:00:01', '', ' ', 0),
	('2020-05-12 14:21:31', '2020-03-16 15:44:14', 0, 8, '8', '3121', '', 0, '1970-01-01 08:00:01', '', ' ', 2);

查询sql

1
EXPLAIN SELECT * from `template_message` where `form_id`  like '312%'

可以看到，当我们对form_id进行模糊查询的时候，也是可以用到索引的，但是如果我们把模糊查询的%放到前面进行查询，还可以用到索引吗？试一下

显示执行计划中type为all，mysql没有用到索引，进行了全表扫描。

大家可以试一下查询retry_times是否可以利用到索引。

这还是针对联合索引单列进行模糊匹配的结果，实际上在此次的(form_id,retry_times)联合索引中，你可以认为数据库对form_id和 （formid,retry_times）都建立了索引，这样理解起来比较方便。

b+树这种数据结构就是一颗有序的多叉树。查询从左边进行查询，一一匹配，比如(form_id,retry_times)这种联合索引先对form_id进行排序，再对retry_times进行排序，查询时先查询
form_id，再查询retry_times。最左匹配原则就是基于这个道理。所以在建立表索引时，有一个基本原则：

如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的

  1. 索引下推

如果在上面那个(form_id,retry_times)联合索引中，执行了下面这条语，数据库的执行情况会是怎么样的。

1
EXPLAIN SELECT * from `template_message` where `form_id`  like '312%' and `retry_times`=2

先看一下这条sql在5.6版本以前的执行情况：

mysql在数据库中查找满足form_id like ‘312%’的值，主键为5和8的都满足，所以这两个都需要回表查询，主键为8的这一行不满足，放弃。这个查询一共回表两次。
但是在mysql5.6之后，mysql会对查询的索引段继续扫描，排除不满足的索引行。执行情况如下

在联合索引中，retry_times为5的索引不满足条件，mysql执行器不会进行回表，此次查询共回表一次。

索引下推在mysql5.6中自动开启，可以通过设置optimizer_switch([global|session],dynamic)变量开启或者关闭index_condition_push优化功能

1
set optimizer_switch=’index_condition_pushdown=on|off’


开启时extra为Using index condition意味着用到了索引下推，关闭则没有
Using index condition，没有进行索引下推优化

  1. 唯一索引和普通索引选择

在我们建表的时候，唯一索引常用情况就是防止数据库脏数据的产生，但是唯一索引是否一定优于普通索引呢？
在我们读取innodb数据页的时候，并不是一条一条的读取的，innodb会读取一页约16K的数据加载进内存中，然后对数据进行筛选。如果选择普通索引还是唯一索引，不得不提到的一个数据结构叫做change buffer。

下面我们用一条sql语句来讲解change buffer的执行过程：
表中有唯一索引限制

1
2
INSERT INTO `template_message` (`gmt_modified`, `gmt_create`, `record_status`, `id`, `out_user_id`, `form_id`, `pay_status`, `send_times`, `send_date`, `form_type`, `sub_type`, `retry_times`)
VALUE ('2020-05-12 14:21:31', '2020-03-16 15:44:14', 0, 9, '9', '3123', '', 0, '1970-01-01 08:00:01', '', ' ', 2);
  1. 数据经过mysql server层到达innodb引擎执行
  2. 数据页在innodb buffer pool中直接更新，两阶段提交
  3. 否则从表中加载数据到innodb buffer pool判断是否唯一性冲突
  4. 无冲突写入innodb buffer pool和两阶段提交

如果是普通索引上述又会怎么执行？

  1. 数据经过mysql server层到达innodb引擎执行
  2. 数据页在innodb buffer pool中直接更新，两阶段提交
  3. 否则写入change buffer和两阶段提交

可以看出来唯一索引新增的情况会多从磁盘中扫描一次进行io，那什么时候change buffer会持久化呢？
我们再次执行一次普通索引情况的sql过程：

1
select * from `template_message` where `form_id`='0232'
  1. 数据经过mysql server层到达innodb引擎执行
  2. innodb buffer pool中没有命中数据行
  3. 加载数据到innodb buffer pool中并marge change buffer 得到最新的数据页
  4. 返回匹配数据

大致流程如上，change buffer了访问这个数据页会触发merge外，系统后台有parge线程会定期merge。

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

业务上首先要正常，change buffer在满足业务的时候才会考虑使用，想要靠程序来满足唯一，根据墨菲定律，必有脏数据产生（阿里巴巴开发手册）。
对于写多读少的业务适合change buffer，如果写入马上读则体现不出change buffer的优点，还要维护change buffer，造成不必要的性能浪费。

5.其他索引规则

  1. 官方文档显示,字段为null时,如果该字段有索引,查询该字段的时候是走索引的,两个字段组成的复合索引,都为null时,才不走索引
  2. 索引在mysql server层中的优化器中进行索引的选择
  3. 有时候mysql会选错索引，必要的时候可以加 force index强制让mysql选择对应的索引

参考资料: https://time.geekbang.org/column/intro/100020801