MySQL索引相关知识总结

前言

索引相关知识笔记

索引

什么是索引？按照MySQL的官方定义，索引是存储引擎用于快速定位记录的一种数据结构。可能初看到这句话都是懵逼的。举个例子，给你一本《21天学会Java》这本书，现在想让你快速找到多线程相关知识在哪页，你会怎么做？当然是先找到目录，确定一个多线程相关知识在哪个章节，然后看一下页码，最后翻到对应的页就可以了。哪如果这本书没有目录呢？我们就只能一页页翻过去确认。所以MySQL中的索引其实就是一个目录，这个目录记录着数据库中数据的位置，这样我们在删改查的时候，就可以快速定位到相关记录行，然后进行修改。

索引的优缺点

如上所述，索引的存在可以加速数据库定位到相关记录，但索引也是一种数据结构，我们都知道，我们使用数据结构对数据进行增删改查时，都需要对数据结构进行维护，常见的如List、Map等，相信看过相关源码的同学都知道，因此索引也需要数据库进行维护，这也会增加数据库的工作量。索引常见的优缺点如下：

优点
- 减少查询需要扫描的数据量，加快查询速度；
- 减少服务器的排序操作和创建临时表的操作，这是因为索引是有序排列的；
- 将服务器的随机IO变为顺序IO（后面会聊到）；
缺点
- 索引占用磁盘和内存空间；
- 增删改查时，数据库要维护索引，降低了这些操作的速度；

索引分类

对于MySQL来说，索引是由数据库底层的存储引擎实现的，由于MySQL支持不同的存储引擎，如Myisam、Memory、InnoDB等，因此索引也有很多种。根据划分维度不同，下面分成两个部分来介绍，一是实现索引的底层数据结构，二是以应用层常用的索引类型进行分类。

以数据结构进行分类

底层实现也不同。这里以底层数据结构进行分类，介绍几种常见的索引：

B-/B+树索引；
Hash索引；
空间数据索引；
全文索引；

这里我们主要介绍Hash索引和B-树和B+树索引。

Hash索引

Hash索引是基于Hash表实现的，熟悉数据结构的同学应该知道Hash是基于K-V键值对来存储数据的。对于每一行数据，存储引擎都会对所有的索引列使用Hash函数进行计算得出相应的HashCode，Hash索引将所有的HashCode都存储在索引中，同时在Hash表中保存指向每行数据的指针。对于HashCode相同的索引，则采用追加链表的方式来解决冲突。例如，维护一个用户身份证信息和姓名的表，需要根据身份证信息找到用户姓名，此时的Hash索引就如下所示：

Hash索引的优点是增加数据时很方面，只需要计算一下HashCode然后进行插入或者追加链表即可，查询时，若是等值查询，即select语句中where子句使用=、in(...)或者是<>这些符号时，查询速度非常快。但是，Hash索引的缺点也是显而易见，那就是范围查询时，这个时候就需要遍历整个Hash索引来确定。而范围查询在实际开发过程中使用频率非常高，若是数据库中数据量非常大，这么搞的话要么数据库崩了，要么用户心态崩了。

总结一下Hash索引的优缺点：

优点
- 新增数据时，操作简便，只需要计算HashCode再向Hash表中插入或者追加链表即可；
- 等值查询时，速度非常快，只需要计算HashCode后找到对应链表遍历即可；
缺点：
- 只支持等值查询，范围查询性能较差；
- Hash索引中的数据并不是按序排列的，这样无法用于数据排序操作；
- Hash冲突，若是数据量非常大时，则产生Hash冲突几率也会增大，链表也会越来越长，这样在遍历链表时花费的时间也会越长，查询性能较差；

B-树索引

上面说到了，索引的存在可以加速数据库定位相关记录的速度，那么除了Hash表之外，还有什么数据结构可以用户存储和查找呢？
答案很显然，就是树。树的种类有很多种，最常见的就是AVL平衡二叉树了。既然数据库需要查找索引，为什么没有使用AVL树而是B-树呢？虽然AVL的时间复杂度为O(logN)，查询性能已经很高了，但需要注意到的是，数据库里的索引和数据最终都是要落地的，即保存到硬盘介质上的。当数据量非常大的时候，索引也会非常大，内存不能够一次性将这些索引全部加载完毕，只能一次加载一个磁盘页。若是使用AVL树，那么当索引非常大时，这棵树的高度也就会非常高。而当AVL树的高度很高时，就会占用很多页，而且这些页只是在逻辑上连续，在物理上并不一定连续。这就会导致数据库在查询索引时要很频繁的进行磁盘IO。在最坏的情况下，磁盘IO的次数就等于树的高度。而且，我们都知道，一般情况下，内存访问一定量的数据可远比磁盘访问一定量数据要快得多，前者越是后者的五分之一时间，因此，此时IO成为阻碍提高数据库性能的瓶颈。

那么如何减少IO次数呢？AVL树中，除叶子节点外，其他节点最多只有两个子节点，这就导致数据量非常大时，树的高度会逐渐增大。那么假如现有有一种数据结构，除了叶子节点外，每个节点的子节点个数比AVL多一些呢，这样每层所能容纳的数据量是不是就会变大？树的高度是不是就会变小？磁盘IO速度是不是就会降低？基于此，B-树就应运而生了。B-树是一种多路平衡查找树，每一个节点最多包含K个孩子，K被称为B-树的阶。B-树的概念以及如何实现，这里不是重点，所以就不重点展开了。下面是相同量的数据，AVL树与B-树对比的一个示意图:

image (1).png

从上图可以明显看出，相同数据量下，B-树的确减少树的高度。

对于MySQL中的B-树索引来说，每个节点存储两个东西，一个是索引，一个是插入到数据库的数据。

B+树索引

说完了B-树，那B+树就很简单了。对于MySQL中的B+树索引，除叶子节点外，其余节点均只存储索引，叶子节点即存储着索引，也存储着具体的行数据；B+树的叶子节点之间用指针连接形成链表，这样可以通过对链表的遍历，便可以获取叶子节点上的索引数据。如下所示：

image (2).png

上面关于B-和B+树的介绍很简单，这是因为其底层实现太过复杂，这里不是一两句话能说清的，有兴趣的可以自行搜索资料，这里特别推荐一个小册，对MySQL的InnoDB引擎做了很详尽的解释，绝对物超所值。MySQL 是怎样运行的：从根儿上理解 MySQL

小结

Hash索引与B+树索引的区别：

hash索引进行等值查询更快(一般情况下),无法进行范围查询，也不支持使用索引进行排序。这是因为在hash索引中经过hash函数建立索引之后,索引的顺序与原顺序无法保持一致,不能支持范围查询.而B+树的的所有节点皆遵循(左节点小于父节点,右节点大于父节点,多叉树也类似),天然支持范围；
hash索引不支持模糊查询以及多列索引的最左前缀匹配.原理也是因为hash函数的不可预测.AAAA和AAAAB的索引没有相关性；
hash索引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚簇索引,覆盖索引等，后面会说到)的时候可以只通过索引完成查询；
hash索引虽然在等值查询上较快,但是不稳定.性能不可预测,当某个键值存在大量重复的时候,发生hash碰撞,此时效率可能极差.而B+树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树的高度较低；

B-树与B+树区别：

B+树存储更多索引。相比于B-树，B+树的非叶子节点只保存索引，这样使得B+树每个非叶子节点能保存的索引数量大大增加，一次磁盘IO时就能读取到更多的索引，加快索引查询速度；
B+树支持范围查询。B+树叶子节点保存着具体的行记录，每个叶子节点的键值从小到大链接形成链表，这样就可以通过遍历链表获取叶子节点上的索引和数据；
B+树的查询时间复杂度稳定。使用B+存储数据时，若想要查询具体的行记录则必须要到叶子节点才能获取到，所以每次查询都是根节——>非叶子节点——>叶子节点，查询性能较为稳定，而B-树就不稳定，靠近根节点就快点，远离根节点就慢点；

二者的对比图如下所示：

image (3).png

image (4).png

以应用进行分类

InnoDB引擎因其优良的性能已经成为MySQL的默认引擎了，所以下面介绍的索引类型都是基于InnoDB引擎生成实现的。

聚簇索引

什么叫聚簇索引呢？先建个表来进行说明。

CREATE TABLE `tb` (
  `id` int(11) NOT NULL,
  `k` int(11) NOT NULL,
  `name` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_k` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

当向表中分别插入(100,"1","a")，(200,"2","b")，(300,"3","c")，(500,"5","e")和(600,"6","f")，InnoDB会为主键id和索引k分别生成一个B+树，如下所示：

我们先来看id这个索引，可以看到，每条记录都完整地保存在叶子节点上，且每个叶子节点由指针相连形成链表，这种由主键索引组成，且每个叶子节点保存一行记录的索引就叫做主键索引，也称之为聚簇索引。由于聚簇索引上的叶子节点都保存有完整的行记录，当查询索引时，只要定位到相关叶子节点上，然后遍历链表，就可以轻松获取想要查询的行记录。例如，select * from tb where id=200，先通过（300）这个索引通过索引定位到（200），再通过（200）定位到（200,2，b）这个索引上，又因为这个索引上存储着完整的行记录，索引可以读取到数据。这种索引结构的查询效率是最高的，因为索引即数据，数据即索引。

我们再来看k形成的索引，每个叶子节点上只保存了k和id的值，每个叶子节点由指针相连，形成链表。由于这不是主键构成的，统称之为非聚簇索引。非聚簇索引是如何查询的呢？以k为例，假如现在有这样一个查询语句，select * from tb where k=2，那么就会先通过（3）定位到（2）这个节点，再定位到（2,100）这个叶子节点上，然后再根据100这个主键值去聚簇索引中去查询具体的行记录，最后将结果返回，这种由非聚簇索引记录去聚簇索引查询的操作就叫做回表。如下所示：

所以，相对于聚簇索引，非聚簇索引的查询方式需要多扫描一颗B+树，这样就会多消耗一定的时间和资源。因此，在实际应用过程中，我们都会建议使用非业务字段建立一个主键索引加快查询速度，例如用户个人信息表，除了姓名，性别，身份证号等业务字段之外，我们都会再分配一个id给它，使用这个非业务字段id来建立聚簇索引。最后一个建议就是，当设计数据库时，如果准备使用id做主键建立索引时，最好将id设为自动增长，即 AUTO_INCREMNET，因为若是主键自增，则id将会一直变大，这样在建立索引时，行记录可以一直向后插入，此时建立B+树索引时耗费的时间和资源最小，否则会造成索引页分裂，耗费性能。最后需要注意的是，InnoDB引擎关于聚簇索引有这么一条规则：如果表设置了主键，则主键就是聚簇索引；如果表没有主键，则会默认第一个NOT NULL，且唯一（UNIQUE）的列作为聚簇索引；以上都没有，则会默认创建一个隐藏的row_id作为聚簇索引。

二级索引

除了聚簇索引之外的，其他的都是二级索引，分别有这么几种：

普通索引：即使用一个列建立的索引，就像上面的 key idx_k(k)；
唯一索引：创建时使用unique关键字进行修饰的，假如将k设为唯一索引，可以这样写 unique key uni_k(k)；
联合索引：使用多个列建立的索引，假如使用k和name建立一个联合索引，就是这样 key idx_k_name(k,name)；
前缀索引：当使用一个较长的字符串建立索引时，此时索引较大，占据的空间较大，此时可以仅这个字符串的一部分字符建立索引，这样就可以大大节约索引空间，从而提高索引检索效率，假如对name建立前缀索引，可以这样，key idx_sub(name(3))，name(3)的3就代表对name的前3个字符建立索引；

MySQL底层对于索引的优化

上面我们说到了，非聚簇索引在查询时，还需要进行回表操作，因为相比于聚簇索引，其效率较低。那非聚簇索引可不可以减少回表次数甚至规避回表操作呢？答案当然是可以的。我们先建立一个用户信息表，根据这个表来进行分析。

CREATE TABLE `t_user` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `id_card` bigint(11) NOT NULL COMMENT '身份证号',
  `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
  `age` int(11) NOT NULL COMMENT '年龄',
  `sex` tinyint(1) NOT NULL COMMENT '性别，0 男；1 女',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_card` (`id_card`),
  KEY `idx_name_age` (`name`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `t_user`(`id_card`,`name`,`age`,`sex`,`create_time`,`update_time`) VALUES (12345678910,'jack',20,0,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP),(12345678911,'rose',18,1,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);

索引下推

我们前面说过，B+树上，每个节点都是按照创建索引时的顺序就行排列的，以idx_name_age为例，先按照名字进行排序，若名字相同时，按照年龄来排序，年龄相同时按主键id进行排序。当有这么一个查询语句时，select * from t_user where name like 'j%‘ and age>18，在MySQL5.6之前，sql语句在执行时，先找到所有满足name like 'j%'的索引节点，然后执行回表操作去聚簇索引中找到满足name like 'j%'的第一行记录，然后开始遍历链表，再找出满足age>18的记录。但从MySQL5.6之后开始引入索引下推优化（index condition pushdown），可以在遍历索引时，先对索引中包含的字段进行判断，过滤掉不满足条件的记录，从而减少回表的次数。

索引覆盖

我们知道聚簇索引中，所有的叶子节点除了保存索引项之外还包含完整的行记录，而二级索引中，每个叶子节点上的索引除了含有索引项之外还含有主键项。所以，当执行select id,name,age from t_user where name ='jack'时，由于在k索引的B+树上，每个节点既含有k的值，也含有主键id的值，所以这条sql语句的执行流程是这样的：先找到（jack,20,1），由于此时id和age的值也在这个节点上，所以不需要再去聚簇索引中查找id和age的值，不需要进行回表操作。也就是说，在这个查询中，索引idx_k已经“覆盖”了我们的查询需求。当一个索引包含或者说是覆盖需要查询的索引字段时，我们称之为索引覆盖。

索引失效的几种情况

现在创建一个表，分析索引失效的几种情况。

CREATE TABLE `t_invalid` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a_b_c` (`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

最左匹配原则

上面说到了，非聚簇上的索引的排序规则是按照创建索引时各列的顺序进行的。所以索引idx_a_b_c的排序规则是，先按照a排序，a相同的话按照b排序，b相同的话按照c排序，c相同的话按照主键id排序。

假如现在有这样一个查询语句，select * from t_invalid where b='b'，如果直接使用b='b'这个条件进行搜索的话，MySQL不知道b='b'在哪个索引节点上，所以只能进行全表扫描，此时索引idx_a_b_c就失效了。同理，select * from t_invalid where a like '%a'这种查询语句也会导致索引失效，因为%a的排序规则不确定，所以MySQL也只能走全表扫描。为什么会出现这种情况呢？这也就引出了MySQL中一个很重要的原则，最左匹配原则。所谓的最左匹配原则即当MySQL匹配索引时会按照联合索引的最左N个字段，或者是字符串索引的最左M个字符进行匹配查找，直到遇上范围查询时（>、<、between、like）就会停止匹配。

全值匹配

select * from t_invalid where a='a' and b='b' and c='c'这个查询语句，查询条件与索引顺序一致，MySQL会按照索引顺序从左到右一直匹配下去进行查询。这里需要注意一点的是，当查询条件包含所有索引，且均是等值匹配时，条件中的索引顺序可以不一致，就是说a='a'、b='b'、c='c'的顺序可以任意，因为MySQL的查询优化器会自动优化查询顺序。

匹配左边列

下面的这三个查询语句，都是从最左边开始匹配，都用到了idx_a_b_c这个索引：

select * from t_invalid  where a = '1' 
select * from t_invalid  where a = '1' and b = '2'  
select * from t_invalid  where a = '1' and b = '2' and c = '3'

而这几种都没有从最左边开始匹配，因为索引失效：

select * from table_name where  b = '2' 
select * from table_name where  c = '3'
select * from table_name where  b = '1' and c = '3'

匹配前缀列

如果列是字符型的话它的比较规则是先比较字符串的第一个字符，第一个字符小的哪个字符串就比较小，如果两个字符串第一个字符相同，那就再比较第二个字符，第二个字符比较小的那个字符串就比较小，依次类推，比较字符串。

例如，select * from table_name where a like 'as%';这个查询语句，前面的as是有序的，此时可以使用到idx_a_b_c进行匹配，而 select * from table_name where a like '%as'以及select * from table_name where a like '%as%'这两种，由于前面的字符顺序不确定，因为无法使用到索引，只能全表扫描。

范围匹配

按照索引顺序，从左向右开始，范围匹配之后的搜索条件将无法使用到索引。例如，select * from t_invalid where a='a' and b>'b' and c='c'这个查询语句，当a='a'这个条件满足时，b就是有序的，因而b>'b'这个条件也可以使用索引进行匹配，但是对于c='c'这个条件来说，因为b>'b'这个范围内，c的排序又是不确定的，所以就无法使用到idx_a_b_c这个索引条件进行匹配，只能逐条过滤出满足c='c'这个条件的数据。

排序

我们经常使用order by子句对返回结果进行排序，若是order by子句使用到了索引，则可以加速排序速度，如下所示：

select * from t_invalid order by a,b,c;

因为索引B+树本来就是按照a、b、c的规则进行排序的，索引可以直接从索引中取出数据，再回表取回完整数据即可。

select * from t_invalid order by a;
select * from t_invalid order by a,b;

上面这两种查询语句用到了部分索引，查询效率也很高。

select * from t_invalid order by b;
select * from t_invalid oder b,c;
select * from t_invalid order by b,c,a；

上述几种查询语句，由于最左匹配原则，不能使用到索引，所以查询效率很低。

列参与运算

select * from t_invalid where UPPER(a) > 'a';

上面这个查询语句，由于对a使用了UPPER()函数，索引参与到了运算当中，则索引就会失效。其他的，如：

select * from table_name where b/2>4;

这种也会导致索引失效，所以在实际应用中，除非必要情况，否则尽量让索引列不参与到运算当中。

MySQL分析器优化

当一条查询语句执行时，MySQL内部的分析器会对该查询语句进行分析，若是MySQL分析出该查询语句使用索引进行查询比全表扫描还要慢时，比如数据量较少时，使用二级索引查询还有可能涉及到回表操作，这比直接全表扫描要慢，此时MySQL就会放弃索引直接进行全表扫描。

总结

对于索引失效的几个场景需要在实际开发工作中注意，因为之前的确遇到了一些索引失效的情况，导致踩了一些坑。

posted @ 2020-03-27 00:40 Reecelin 阅读(24) 评论(0) 编辑收藏举报

刷新页面返回顶部

凡尘多遗梦