索引:
聚集索引
聚集索引是按照每张表的主键构造一颗B+树,同时叶子节点中存放的就是整张表的行记录数据,也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分
- 主键被定义了,那么这个主键作为聚集索引
- 主键没有被定义,那么该表的第一个唯一非空索引被作为聚集索引
- 主键没有定义,同时也没有合适的唯一索引,那么innodb内部会生成一个隐藏的主键作为聚集索引,这个隐藏的主键是一个6个字节的列,该列的值会随着数据的插入自增。
非聚集索引
在聚集索引之上创建的索引称之为非聚集索引,非聚集索引访问数据总是需要二次查找。叶子节点存储的不是行的物理位置,而是主键值。通过非聚集索引首先找到的是主键值,再通过主键值找到数据行的数据页,再通过数据页中的Page Directory找到数据行。
叶子节点并不包含行记录的全部数据,叶子节点除了包含键值外,还包含了相应行数据的聚簇索引键。
不影响数据在聚簇索引中的组织,所以一张表可以有多个非聚集索引。
回表
由于非聚集索引存储的都是主键值,所以如果我们通过费聚集索引查询的话,整个查询流程为:首先通过普通索引(非聚集索引)定位到主键值,再通过主键值定位到该行记录,这就是回表。回表查询的性能较低。
覆盖索引
如果一个索引包含(或者说覆盖)所有需要查询的字段的值,我们就称之为覆盖索引,我们知道 InnoDB 存储引擎中,如果不是主键索引,叶子节点存储的是主键+列值。最终还是要回表,也就是要通过主键再查找一次,这样就会比较慢。覆盖索引就是把要查询出的列和索引是对应的,不做回表操作。
如何实现索引覆盖?
常见的方法是:将被查询的字段,建立到联合索引里去(或者说 查询的字段都已经建立了索引)。
以一个user表为例,假设有三个字段,id,name,sex。其中id为主键索引,name字段上建立了普通索引,sex字段无索引。
第一个sql语句:
SELECT id, name FROM user WHERE name='shenjian';
能够命中 name 索引,索引叶子节点存储了主键 id,通过 name 的索引树即可获取 id 和 name,无需回表,符合索引覆盖,效率较高。
第二个SQL语句:
SELECT id, name, sex FROM user WHERE name='shenjian';
能够命中 name 索引,索引叶子节点存储了主键 id,但 sex 字段必须回表查询才能获取到,不符合索引覆盖,需要再次通过 id 值扫码聚集索引获取 sex 字段,效率会降低。
如果把 (name) 单列索引升级为联合索引 (name, sex) 就不同了:
SELECT id, name, sex FROM user WHERE name='shenjian';
能够命中联合索引,索引叶子节点存储了主键 id,通过 联合索引的索引树即可获取 name 和 sex,无需回表,符合索引覆盖,效率较高。
联合索引
在平时开发中,我们最常见的是聚集索引,但在我们需要多条件查询的时候,就不得不建立联合索引,来提高我们的查询效率
联合索引:也称复合索引,就是建立在多个字段上的索引。联合索引的数据结构依然是 B+ Tree
一颗 B+ Tree 只能根据一个值来构建,所以联合索引使用最左的字段来构建 B+ Tree
联合索引的查询流程
InnoDB 会使用主键索引 B+ Tree 维护索引和数据文件,同样联合索引 (A,B) 也会生成一个 B+ Tree ,只不过联合索引 B+ Tree的 data 部分存储的是联合索引所在行的主键值
拿到联合索引所在行的主键值后,在通过主键索引 B+ Tree 就可以直接拿到具体的行数据了。
最左前缀匹配原则
最左优先,以最左边的为起点任何连续的索引都能匹配上,如果中间某列没有条件,或使用like会导致后面的列不能使用索引。但遇到范围查询 (>、<、between、like) 就会停止匹配。
例题
假设某个表有一个联合索引(c1,c2,c3,c4)以下选项哪些字段使用了该索引:
-
A where c1=x and c2=x and c4>x and c3=x
-
B where c1=x and c2=x and c4=x order by c3
-
C where c1=x and c4= x group by c3,c2
-
D where c1=? and c5=? order by c2,c3
-
E where c1=? and c2=? and c5=? order by c2,c3
-
A:四个字段均使用了该索引,mysql在查询时会优化,调换顺序,将c3换到c4前面
-
B:c1,c2字段使用了该索引
-
C:c1字段使用该索引
-
D:c1字段使用该索引
-
E:c1,c2字段使用了该索引