索引结构

B+树

本文主要介绍innodb存储引擎的索引

innodb采用的是b+树的索引结构，通过索引页+记录页的方式，记录页为双向链表相连。

MySQL索引_聚簇索引

查找的时候先从根节点确认页的位置，不断向下查询，定位到具体的记录页，再通过页中的页目录，来查询到想要的数据。

PS:页是 InnoDB 管理存储空间的基本单位，一个页的大小一般是 16kb。

record_type代表页类型 0：表示普通记录 1：表示 B+ 树非叶节点的目录项记录

为什么采用B+树？

对比hash：hash不支持范围查询+

对比二叉树：二叉树有可能退化成单向链表，b+树能自平衡

对比b树：b树非叶子节点也能存放数据，会导致每条记录的查询复杂度不一致，且非叶子节点放了数据，就会导致相同高度的层级，存放的总数据少了，造成更好的树深度。

索引的种类

innodb的索引主要分为聚簇索引（clustered+inex+)和辅助索引（sencondary+index）。也称为主键索引和二级索引。

聚簇索引

聚簇索引叶子节点存放的是具体的记录行。

MySQL索引_二级索引_02

辅助索引

辅助索引叶子节点存放的是聚簇索引的索引键。空间占用比聚簇索引小

MySQL索引_聚簇索引_03

联合索引

两个字段一起组成联合索引，这样我们会先去匹配第一个索引列，在第一个索引列相同的情况下去匹配第二个索引列。

MySQL索引_联合索引_04

特性

由于innodb的索引结构，带来了一系列耳熟能详的索引特性，接下来我们就一起看看有哪些。

回表查询

-- 索引：（c1）
select * from T where c1=5;

像这样一条语句，二级索引c1只存了聚簇索引的索引键的值，如果我们是根据二级索引查到的数据，还需要拿着聚簇索引的索引键到聚簇索引中去查到对应的真实数据，称为回表查询。

覆盖索引

回表是有代价的，为了避免回表的代价。我们需要指定我们想要查询的列，就有机会达到覆盖索引。

select id,c1 from T where c1 = 5;

像这样一条查询语句，我们想要的主键id和二级索引c1在二级索引中都覆盖到了，就不需要去回表了，就称为覆盖索引，有效的解决了回表的问题。

索引查询原理

-- 主键索引：id
-- 二级索引：c1,c2
-- 普通字段commmon
-- 插入语句
insert into t(id,c1,c2,common) values (1,1,1,'小红');
insert into t(id,c1,c2,common) values (2,1,2,'小明');
insert into t(id,c1,c2,common) values (3,2,1,'张三');
insert into t(id,c1,c2,common) values (4,2,2,'李四');
-- 执行语句
select * from t where c1=1 and c2=1;

假设执行上面的语句，会出现什么样的结果？

c1和c2都是索引列的情况下，优化器只会选中其中一个作为查询条件，比如在二级索引通过c1=1过滤出两条数据的主键1和2，然后再回表查出这这两行记录，最后在server层中过滤掉c2=1的记录。也就是，这个过程涉及到了回表，同时还涉及到了内存过滤。有没有可能这些都在二级索引中就解决呢？当然有。

在大部分情况下，mysql只会使用一个索引列，除了某些场景，优化器认为同时使用两个索引列的代价更低，就把c1=1和c2=1的数据全部查出来，然后在内存中做交集的运算。

最左匹配原则

要善于运用组合索引，组合索引更容易达到索引覆盖的效果，并且在合适的时候更容易命中索引。比如上面提到的查询。

-- 主键索引：id
-- 联合索引：(c1,c2)
-- 普通字段commmon
-- 插入语句
insert into t(id,c1,c2,common) values (1,1,1,'小红');
insert into t(id,c1,c2,common) values (2,1,2,'小明');
insert into t(id,c1,c2,common) values (3,2,1,'张三');
insert into t(id,c1,c2,common) values (4,2,2,'李四');
-- 执行语句
select * from t where c1=1 and c2=1;

由于组合索引中同时有c1也有c2，就可以在二级索引中完全过滤出想要的数据了，避免了内存的过滤。使用联合索引要注意一个最左匹配原则

-- 联合索引：(c1,c2)
select * from t where c1=1 and c2=1;-- 走索引
select * from t where c1=1 and c2>1;-- 走索引
select * from t where c1>1 and c2>1;-- 不走索引
select * from t where c1>1;-- 走索引
select * from t where c2>1;-- 不走索引
-- 模糊匹配
select * from t where c1 like 'ab%';-- 走索引
select * from t where c1 like '%ab%';-- 不走索引
-- 排序
select * from t order by c1,c2;-- 走索引
select * from t order by c2,c1;-- 不走索引

注意以上的例子，大概就能明白最左前缀原则了，实际上明白了b+树的查询原理，也能很好的明白最左前缀原则。

索引下推

索引下推（ICP index condition pushdown）是MySQL5.6及以上版本上推出的，用于对查询进行优化。

索引下推是把本应该在server层进行筛选的条件，下推到存储引擎层来进行筛选判断，这样能有效减少回表。

-- 联合索引（c1,c2）
select * from T where c1>1 and c2=2;
-- 先在非聚簇索引中找到c1>1的记录，然后再判断c2=2；然后再回表

失效场景

使用索引可以快速地定位表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。

如果查询时没有使用索引，查询语句就会扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。

  1. 非最左法则

索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

最左匹配原则指的是，以最左边的为起点字段查询可以使用联合索引，否则将不能使用联合索引。 我们本文的联合索引的字段顺序是 sn + name + age。sn+name+age可以触发索引，name+age不能触发索引

  1. 计算、函数、类型转换(自动或手动)导致索引失效
explain select * from t where id=1;-- 走索引
explain select * from t where id+1=2;-- 不走索引
explain select * from t where ifNull(id,0)=1;-- 不走索引
explain select * from t where c1='cn12';-- 走索引
explain select * from t where c1=123;-- 不走索引
  1. 范围条件右边的列索引失效
-- 创建一个联合索引， 注意字段的顺序
create index idx_age_classid_name on student(age,classid,name);
-- 执行计划
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age = 30 AND student.classId > 20 AND  = 'abc' ;

MySQL索引_聚簇索引_05

-- 再创建一个联合索引，与上面的索引对比字段顺序变了
create index idx_age_name_classid on student(age,name,classid);
-- 再执行一模一样的执行计划
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age = 30 AND student.classId > 20 AND  = 'abc' ;

MySQL索引_聚簇索引_06

看到两个执行计划虽然都用到了索引，但是：

第一个没用全，只用到了联合索引“idx_age_classid_name” 的age和classid。

第二个把联合索引“idx_age_name_classid”的age，name和classid都用上了。

  1. 不等于(!= 或者**<>)导致索引失效
  2. is null可以使用索引，is not null无法使用索引
  3. like以通配符%开头导致索引失效
  4. OR 前后只要存在非索引的列，都会导致索引失效