聚集索引（Clustered Index）

​ B+树索引并不能找到一个给定键值的具体行。B+树索引能找到的只是被查找数据行所在的页。然后数据库通过页读入到内存，再在内存中进行查找，最后得到要查找的数据。

由于聚集索引是主键索引，叶子节点存储了整行数据，并且通过双向链表连接，因此数据存储在逻辑上是连续，另外一点是每一页上的多行记录也是通过双向链表进行维护的。

聚集索引另外一个好处：对主键的排序和范围查找速度很快，因为叶子节点存储了全部数据。

如用户需要查找一张注册用户的表，查询最后注册的10位用户，由于B+树是通过双向链表维护的，所以可以快速拿到最后一个数据页，并取出10条记录。

01

02

非聚集索引（Secondary Index）

非聚集索引又称辅助索引，叶子节点并不存储整行记录的全部数据，叶子节点存储的是

（主键1，主键2...，索引1，索引2...）

索引管理

Fast Index Creation(FIC)

起因

​ MySQL5.5版本之前，索引的添加/删除这类DDL操作，MySQL的操作过程为：

  • 创建一张新的临时表，表结构通过命令ALTER TABLE 新定义的结构

  • 把原表数据导入到临时表

  • 删除原表

  • 将临时表重命名为原表名

    当表数据量很大的时候，索引的添加将会需要耗费极长时间，索引的维护变得非常困难。

解决

​ Innodb存储引擎从InnoDB 1.0.x版本开始支持FIC(Fast Index Creation，快速索引创建）。

辅助索引的创建/删除

​ 对于辅助索引的创建，Innodb存储引擎会对创建索引的表加上一个S锁。

​ 对于辅助索引的删除，Innodb存储引擎只需要更新内部视图，并将辅助索引的空间标记为不可用，同时删除MySQL数据库内部视图上对该表的索引定义即可。

主键索引的创建/删除

​ 需要重建一张表，FIC只支持辅助索引。

Online Scheme Change(在线架构改变， OSC)

P207

Online DDL

​ MySQL5.6版本开始支持Online DDL操作。

​ 其允许辅助索引创建时，还允许其他诸如INSERT/UPDATE/DELETE这类DML操作。

​ 并且还支持在线执行以下DDL操作：

  • 辅助索引的创建/删除
  • 改变自增长值
  • 添加/删除外键约束
  • 列的重命名

Cardlinality

Cardlinality值通常作为添加索引的参考，一般值应该尽可能接近1才会添加索引。

联合索引

联合索引无非就是多个列构成的索引，MySQL5.0+/InnoDB1.0+开始支持。

联合索引在叶子节点的存储结构导致了联合索引的最左匹配规则。

覆盖索引（Covering Index）

覆盖索引也就是从辅助索引的叶子节点能拿到要查询的记录，无需“回表”。

辅助索引叶子节点：（primary key1, primary key2,...key1,key2...）

辅助索引用于统计时

SELECT COUNT(*) FROM tb_xxx

​ 若表上有主键索引和辅助索引，那么SQL优化器会优先选择辅助索引，因为辅助索引远小于主键索引，选择辅助索引可以减少IO操作。

​ 此外，通常情况下，单独使用非联合索引中的最左列作为查询条件时，一般是不能使用联合索引的，但是如果是统计操作，并且是覆盖索引的，则优化器会进行选择，如一下SQL语句：

EXPLAIN SELECT COUNT(*) FROM Profile WHERE age > 1 AND age < 20

03

优化器选择不使用索引的情况

  1. 某些范围查询

范围查询优化文档地址：https://dev.mysql.com/doc/refman/5.5/en/range-optimization.html#range-access-multi-part.

表数据如下：

04

执行计划如下：

05

将17改为16，执行计划如下

06

范围缩小后，优化器选择idx_02索引，Extra是Using Index Condition，也就是索引下推。

由上可见范围查询时，范围超过某个极限（这个极限跟该索引的记录数有关，当该索引的记录数<=8时，默认都会使用ICP）时就会using where全表扫描，其实原理很简单，当范围很大时，如果使用索引检索，检索之后还需要拿主键去查询整行记录，这样IO次数就变得很多了，这时候SQL优化器就会选择一个最优方案，直接走全表扫描。

索引提示（Index Hint）

显式指定优化器使用的索引，USING INDEX(索引名)/FORCE INDEX(索引名)/IGNORE INDEX（索引名）

USING INDEX：告诉优化器可参考使用此索引，优化器有权不使用此索引

FORCE INDEX：告诉优化器必须要使用此索引，优化器无权不使用此索引

以下两种情况可能会用到索引提示

  • 优化器选错了索引导致SQL执行很慢，但是优化器选错索引似乎很不常见。
  • 某SQL语句possible_keys有很多可选索引，这时优化器选择执行计划所耗费的时间很长。

语法如下：https://dev.mysql.com/doc/refman/5.7/en/index-hints.html

代码 
tbl_name [[AS] alias] [index_hint_list]
  
index_hint_list:
    index_hint [index_hint] ...
  
index_hint:
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | {IGNORE|FORCE} {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
  
index_list:
    index_name [, index_name] ...

Multi-Range Read（MRR）优化

MRR优化文档：https://dev.mysql.com/doc/refman/5.7/en/mrr-optimization.html

适用范围：range，ref，eq_ref类型，需要获取整行记录的查询，MySQL5.6+。

​ MRR优化是针对辅助索引的，MRR优化的好处有以下三点：

  • 使得磁盘访问变得较为顺序
  • 减少缓冲池中页被替换的次数
  • 批量处理对键值的查询操作

未使用MRR时，使用辅助索引查询的流程如下：

  • 通过辅助索引获取主键列表
  • 使用主键列表通过聚集索引获取记录
    • 此时的主键列表是无序的
    • 由于硬盘的特点，随机读取硬盘效率很低

使用MRR后，使用辅助索引查询的流程如下：

  • 通过辅助索引获取主键列表
  • 对主键列表进行排序
  • 使用有序的主键列表通过聚集索引获取记录
    • 此时的主键列表是有序的
    • 所以获取记录过程是顺序读取硬盘

支持范围查询和JOIN查询操作

Index Condition Pushdown（ICP）优化

ICP优化文档：https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html

适用范围：range，ref，eq_ref，ref_or_null类型，WHERE子句有联合索引条件。

​ ICP可以利用联合索引在存储引擎取出索引时把符合条件的数据取出，没有ICP之前需要到主键索引取出整行记录后过滤数据。

开关ICP

代码 
SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';

举个栗子，存在一个表，定义了一个联合索引 idx (zipcode, lastname, firstname)

代码 
-- 此时我们只知道某人的zipcode，但是他的名字记不太清，需要模糊查询，
-- 支持ICP之前，由于最左匹配原则，%开头不能使用索引，所以返回很多无效数据，数据库执行了很多无效IO
-- 支持ICP之后，存储引擎在取出zipcode的索引后直接将lastname和address过滤
SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';
ABOUT ME
Gitee Github
LINKS
  • 看雪