MySQL-索引解析

MySQL-索引解析

起男 1,176 2021-01-20

MySQL-索引解析

索引基础

索引对查询的速度有着至关重要的影响，理解索引也是进行数据库性能调优的起点，索引就是为了提高数据查询的效率。索引可以包含一个或多个列的值，如果索引包含多个列的值，则列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列

索引类型

用于提高读写效率的数据结构有很多，这里先介绍常见的3种，分别是：

  • 哈希表
  • 有序数组
  • 搜索树（重点）

哈希索引

哈希表是一种以k-v的方式存储数据的结构，我们只要输入待查找的值（即key），就可以找到其对应的值（即value）。

哈希的思路很简单，把值放到数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的位置，即idx=Hash(key)。如果出现哈希冲突，就采用拉链法解决

因为哈希表中存放的数据不是有序的，因此不适合做区间查询，适用于只有等值查询的场景

有序数组

有序数组在等值查询和范围查询场景中的性能都非常优秀。用二分法就可以快速找到（时间复杂度为O(logN)）。但是如果要往中间插入一条数据，则必须挪动后面的所有记录，成本较高。因此，有序数组只适用于静态存储引擎，即数据表一旦建立后不再会修改

B+树（inndb）

首先，得先好好理解什么是B+树，简单的说，是因为使用B+树存储数据可以让一个查询尽量少的读磁盘，从而减少查询磁盘i/o的时间

在innodb中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，innodb使用了b+树索引模型，所以数据都是存储在b+树中的。每一个索引在innodb里面对应一块b+树

根据叶子节点的内容，索引类型分为主键索引和非主键索引

  • 主键索引的叶子节点存的是整条记录，主键索引也被称为聚簇索引
  • 非注解索引的叶子节点存的是主键的值，非主键索引也被称为二级索引/普通索引/辅助索引

那么，基于主键索引和非主键索引的查询有什么区别

  • 如果语句是select * from t where id=500，即主键查询，则只需要搜索id这棵树
  • 如果语句是select * from t where k=5，即非主键索引查询，则需要先搜索k索引树，得到id的值为500，再到id索引树搜索一次。从非主键索引回到主键索引的过程称为回表

也就是说，基于非主键索引的查询需要多扫描一颗索引树，因此，我们在应用中应该尽量使用主键查询。而从存储空间的角度讲，因为非主键索引树的节点存放的是主键的值，那么，应该考虑让主键的字段尽量短，这样非主键索引的叶子节点就越小。一般情况下，建议创建一个自增主键，这样非主键索引占用的空间最小

联合索引

联合索引是指对表上的多个列进行索引

联合索引(a,b)是根据a，b进行排序（先根据a排序，如果a相同则根据b排序）。因此，下列语句可以直接使用联合索引得到结果（事实上用到了最左前缀原则）：

select ... from xxx where a=xxx;
select ... from xxx where a=xxx order by b;

而下列语句则不能使用联合查询：

select ... from xxx where b=xxx;

对于联合索引(a,b,c)，下列语句同样可以直接通过联合索引得到结果：

select ... from xxx where a=xxx order by b;
select ... from xxx where a=xxx and b=xxx order by c;

而下列语句则不行，需要执行一次filesort排序操作：

select ... from xxx where a=xxx order by c;

最左前缀原则

对于有很多字段的一张表，查询的方式是多样的，难道要为了每一种可能的查询都定义索引吗？这样岂不是很浪费空间，毕竟建索引也是需要一些空间的。事实上，b+树这种索引结构，可以利用索引的“最左前缀”原则来定位记录，避免重复定义索引

假设建立了一个联合索引(name,age)，可以看到，索引项是按照索引定义里面出现的字段顺序排序的，先根据name排序，name相同就根据age排序

当你的逻辑需求是查到所有名字是“张三”的人时，可以快速等位到id，然后向后遍历得到所有需要的结果

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

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

因此，基于最左前缀原则，我们在定义联合索引的时候，考虑如何安排索引内的字段顺序就至关重要了！评估的标准就是索引的复用能力，比如，当已经有了(a,b)字段的索引，一般就不需要再单独在a上建立索引了

索引覆盖

如果执行语句：

select * from t where k between 3 and 5;

则这条sql语句的执行流程如下：

  1. 在k索引树上找到k=3的记录，取得id
  2. 再到id索引树查到id对应的值
  3. 在k索引树取下一个值k=5，取得id
  4. 再回到id索引树查到id对应的值
  5. 在k索引树取下一个值，不满足条件，循环结束

在这个过程中，回到主键索引树的过程，称为回表。在这个例子中，由于查询的结果是所有字段，所需要的数据只有主键上才有，所以不得不回表。大如果执行的语句是下面这样的

select id from t where k between 3 and 5;

由于查询的值是id，而id的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里，索引k已经“覆盖了”我们的查询需求，故称为覆盖索引

除了上面这种情况，针对某些统计问题时，覆盖索引也能发挥用处。还是上面的例子，执行如下语句来统计表的纪录总数（在此我们假设这张表数据量特别大，需要多次磁盘io）：

select count(*) from t;

如果没有对字段k设置索引，那么只能通过聚簇索引来计算；如果对字段k设置了索引，那么，由于聚簇索引的叶节点存放的是整行记录的所有信息，而辅助索引的节点只存放主键，两者对比，对于一页内存，显然辅助索引能够存放的节点更多，意味着辅助索引可以减少io次数，从而更快的计算出count(*)的值

可见，如果建立了辅助索引，在有些场景下，优化器会自动使用辅助索引从而提升查询效率。

总结

覆盖索引就是从辅助索引中直接得到查询结果，而不需要回表到聚簇索引中进行再次查询，所以可以减少搜索次数（不需要从辅助索引树回表到聚簇索引树），或者说减少io操作（通过辅助索引树可以一次性从磁盘载入更多节点），从而提升性能

索引下推

什么是索引下推呢？假设有这么个需求，查询表中名字第一个字是张，性别男，年龄为10的所有记录。那么查询语句是这么写的：

select * from t where name like '张' and age=10 and ismale=1;

根据前面说的“最左前缀原则”，该语句在搜索索引树的时候，只能匹配第一个字是“张”的记录，接下来是怎么处理呢？当然就是从id开始，逐个回表，到主键索引上找到相应的记录，再对比age和ismale这两个字段的值是否符合

但是，MySQL5.6引入了索引下推优化，可以在索引遍历过程中，对索引包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数

总结

如果没有索引下推优化（或称icp优化），当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；在支持icp优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤，也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能

原文：https://www.cnblogs.com/kkbill/p/11354685.html#1-%E7%B4%A2%E5%BC%95%E5%9F%BA%E7%A1%80

mysql