MySQL 索引

1|0MySQL 索引

1|1概念

1|0聚簇索引

将数据存储和索引放到了一块，找到了索引也就找到了数据。

ps： MySQL的InnoDB引擎中，索引数据结构是B+树，主键索引叶子节点的值存储的就是MySQL的数据行，普通索引的叶子节点的值存储的是主键值。

1|0规则

• 定义了主键，则该主键就是聚簇索引
• 未定义主键，第一个not NULL unique列是聚集索引
• 所有字段均可为NULL， InnoDB会创建一个隐藏的row-id作为聚集索引

1|0非聚簇索引

以主键以外的列值作为键值构建的 B+ 树索引，我们称之为非聚集索引。

在InnoDB引擎中，一般一个表只能有一个聚簇索引，但可以有多个非聚簇索引，每一个非聚簇索引的叶子节点都存储了主键的值。

1|0唯一索引

索引列的值必须唯一，但允许有空值，且空值只能有一个

1|0全文索引

MySQL5.7版本之前只在MYISAM引擎上支持，全文索引可以在Char Varchar、Text类型的列上创建。

1|0联合索引

多列联合组成索引。

1|0最左前缀优先原则

最左前缀原则（假设索引为a, b, c）

则有效索引为:

• a
• a,b
• a,b,c

1|0覆盖索引

查询字段中，索引 已经“覆盖了”我们的查询需求，我们称为覆盖索引。

举例： select ID from T where k between 3 and 5，
这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表

1|0回表

在InnoDB中，非聚簇索引的叶子节点上存储的值为主键的值，并不包含完整行的数据，

所以需要拿着主键的值，到主键索引树上去查询整行的数据，这个过程称为回表。

1|0索引下推（Index Condition Pushdown）

1|0启用版本

Mysql5.6的版本上推出，用于优化查询。

可通过下述配置关闭

set optimizer_switch='index_condition_pushdown=off';

1|0优点

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

1|0流程变化

1|0不使用ICP

1. MySQL Server发送查询请求到存储引擎（查询条件里包含索引条件）
2. 存储引擎根据索引检索数据，将检索出的数据返回给MySQL Server
3. MySQL Server再根据条件筛选数据

1|0使用ICP

1. MySQL Server发送查询请求到存储引擎（查询条件里包含索引条件），并将条件传递给存储引擎
2. 存储引擎根据索引检索数据，将符合条件的数据返回给MySQL Server
3. MySQL Server不需要根据索引字段再筛选了

1|0适用场景

1|0当需要整表扫描

1|0适用InnoDB引擎和MyISAM引擎查询

5.6版本不适用分区查询。

5.7版本可以用于分区表查询

1|0InnoDB引擎仅仅适用二级索引(即非聚簇索引)

原因： InnoDB聚簇索引将整行数据读到InnoDB缓冲区

1|0不能使用索引下推的情况

1. 子查询条件不能下推
2. 调用存储过程条件不能下推
3. 触发条件不能下推（这个不太明白）

1|2索引失效

1. 使用 like 关键字， 且 % 在前面
2. 联合索引不符合最左前缀原则
3. or 语句前后没有同时使用索引。当 or 左右查询字段只有一个是索引，该索引失效
4. 数据类型出现隐式转化，如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描
5. 在索引字段上使用not，<>，!=， 只会产生全表扫描
6. 对索引字段进行计算操作、字段上使用函数
7. 当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效

1|3优缺点

1|0优点

1. 索引大大减小了服务器需要扫描的数据量，从而大大加快数据的检索速度
2. 索引可以帮助服务器避免排序和创建临时表
3. 索引可以将随机IO变成顺序IO
4. 索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组，提高了表访问并发性
5. 关于InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）

1|0缺点

1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
2. 对表进行增删改时，数据库本身会增加一个对索引维护的步骤，所以效率会受到影响

1|4数据结构

1|0Hash

1|0特点

在InnoDB引擎中，

• 采用除法散列函数

• 解决冲突机制采用链表法

1|0自适应哈希索引

在InnoDB引擎中，用户无法手动创建哈希索引。

InnoDB会自调优(self-tuning)，如果判定建立自适应哈希索引(Adaptive Hash Index, AHI)，能够提升查询效率，InnoDB自己会建立相关哈希索引。

key是索引键值（或者键值前缀）。

value是索引记录页面位置。

1|0优点

检索效率非常高，索引的检索可以一次定位

1|0缺点

1. Hash索引仅仅能满足=、in()、!=，不支持范围查询
2. Hash索引无法被用来避免数据的排序操作
3. Hash索引不能利用部分索引键查询通过组合索引的前面一个或几个索引键进行查询的时候，Hash索引也无法被利用
4. Hash索引在任何时候都不能避免表扫描，由于不同索引键存在相同Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。
5. Hash索引遇到大量Hash值相等的情况后性能并不一定就会比BTree索引高

1|0B树

1|0特点

• 每个节点包含了索引值和表记录的信息
• 叶节点具有相同的深度
• 叶子节点的指针为空
• 节点中的数据key从左到右递增排列

1|0优点

BTree的结构可以弥补红黑树的缺点，解决数据量过大时整棵树高度过大的问题，
相同的数据量只需要更少的层，相同的深度可以存储更多的数据，查找效率更高。

1|0缺点

在查询单条数据是很快的，但是如果范围查询的话，BTree结构每次都需要从根节点查询一遍，会影响效率;

读取索引数据时，会连带着将整行数据加载进内存，占用更多的内存

1|0B+ 树

1|0特点

1. 非叶子节点不存储数据，只存储key（索引值），会有冗余
2. 叶子节点存储数据，包含行所有字段
3. 叶子节点用指针连接（双向）-便于范围查找，提高区间访问的性能

1|0优点

1. 顺序访问指针，提高区间访问的性能
2. 层高不会太高，查询速度快

__EOF__

本文作者：前方丶路
本文链接：https://www.cnblogs.com/strive-for-life/p/16813278.html
关于博主：评论和私信会在第一时间回复。或者直接私信我。
版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
声援博主：如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力！