14、MySQL 教程 - MySQL 索引

索引原理

索引：存储引擎用于快速查找值的数据结构

1）本质：存储引擎在索引中找到值，根据索引返回数据行或直接返回索引；

2）索引是在存储引擎层实现，所以不同存储引擎的索引工作方式不同；

3）表可拥有索引数量不受限制，但索引需占用磁盘空间和维护成本；

//过多索引还会导致每次更新数据时的性能（维护成本过高）

索引优点：

1）提高数据检索效率（目录），降低数据库的IO成本（随机IO变顺序IO）；

2）减少查询中分组和排序的时间（降低CPU消耗）；

3）保证表中的数据唯一性（唯一索引）；

4）减少扫描数据时被锁定的数据；

5）加速表和表之间的连接；

//InnoDB访问聚簇索引使用写锁，访问二级索引使用读锁

索引缺点：

1）创建/维护索引需占用磁盘空间；

2）降低表的更新速度；

三星系统（Three-Star System）：优秀的索引系统

1）将查询语句相关的索引相邻（或足够靠近）；

2）索引列顺序和查询语句的排序顺序相同；

3）索引列中包含查询语句中所需的全部列；

索引基础

设计概念

MySQL中数据通过以下结构存储数据（简化版）

1）record_type：以不同数值表示该数据的类型

如：通过B+树检索数据流程

当查找数据20时，其检索路线如下：

1）确定目录项记录页为30（1 < 20 < 320）；

2）确定数据项记录页为9（12 < 20 < 209）；

3）遍历该页以检索到20；

B+树页面特性：

1）页内的数据按照主键排序成单向链表

2）各个数据页按照页内最小的主键排序成双向链表

3）目录页分为不同层次，相同层次的目录页根据最小的主键排序成双向链表

管理索引

创建索引分为：创建表时、后续添加

（1）创建表时创建索引

索引类型 INDEX [索引名]（列1， 列N[长度N]） [ASC | DESC]

1）索引类型分为以下（不指定则默认普通索引）：

2）INDEX关键词和KEY关键词同理；

3）若不指定索引名，则默认以指定列组合成索引名；

4）可指定列的长度（仅为字符类型的列可被指定长度）；

5）ASC和DESC指定以升序或降序的索引值存储；

//创建组合索引时不能指定索引类型（指定多个列即可）

（2）创建表后添加索引：

1）ALTER TABLE语句：

ALTER TABLE 表名
ADD 索引类型 INDEX [索引名]（列1， 列N[长度N]）[ASC|DESC]

2）CREATE INDEX语句

CREATE 索引类型 INDEX [索引名]（列1， 列N[长度N]）[ASC|DESC]

删除索引分为：ALTER语句、DROP语句

1）ALTER语句：

ALTER TABLE 表名
DROP INDEX 索引名

2）DROP语句：

DROP INDEX 索引名 ON 表名

隐藏索引：优化器不可调用该索引（使用FORCE INDEX也不可）

1）MySQL 8.X以上的版本才支持该功能；

2）实现方式：在创建/添加索引的最后处添加“INVISIBLE”关键词；

//该为“VISIBLE”关键词则为可见（默认为VISIBLE）

//索引被隐藏后仍会被维护

设计原则

适用场景

（1）具有唯一性限制的列

1）可明显提高检索速度（维护成本可忽略）；

（2）频繁作为WHERE条件的列

1）不仅是查询语句的WHERE条件（UPDATE和DELETE）；

（3）常用于GROUP BY和ORDER BY的列

1）仅索引列顺序和ORDER BY指定顺序且排序方向一致，才能使用索引排序

2）若查询关联多张表，仅ORDER BY指定列全为第一张表时能使用索引排序

3）其他排序需满足最左前缀从可使用索引排序

（4）常用DISTINCT去重的列

1）对去重的索引列创建普通索引即可提高检索效率；

（5）关联查询的WHERE条件和连接列

1）WHERE条件创建索引可提高对数据的过滤；

2）关联条件列需保持数据类型一致（否则导致索引失效）；

不适用场景

（1）数据量小时不建议使用索引

1）优化应遵循：小全扫、中索引、大分区

（2）WHERE条件中使用不到的列

1）非绝对性，对于特殊场景仍可建立索引；

（3）大量重复数据的列

1）重复率高于10%时，就无须创建索引；

（4）频繁更新和存储无序值的列

1）维护成本过高；

（5）尽量避免重复/冗余索引（会导致DML速度降慢）

1）冗余索引：其他索引可替代的索引

2）重复索引：在相同列上按照相同顺序创建的相同类型的索引

3）MySQL唯一限制（UNIQUE）和主键限制（PRIMARY）均通过索引实现

4）查表看各索引使用频率：INFORMATION_SCHEMA.INDEX_STATISTICS

//若该表无数据，可将系统变量userstate置为1

索引类型

B-Tree

B-Tree：基于B+树存储索引

1）每次检索均从根节点开始，且每个叶子页到根的距离相同

2）每个索引都需建立个B+树，B+树的每个节点都是数据页（16KB）

3）树的深度和表的大小相关，B-Tree一般不会超过4层（足够记录数据）

//根页面位置不变，页至少存储2条数据

B-Tree适用查询：

1）全值匹配：检索数据的列均为索引列（且为精确匹配）

2）匹配范围/顺序值：B-Tree所有数据都是按顺序存储的

3）精确匹配和范围匹配混用：先精确匹配后范围匹配

4）匹配列前缀：仅使用索引列数据的前部分

5）匹配最左前缀：仅使用索引的前几列

6）仅访问索引：检索的数据同时为索引

//索引对多个值排序是依据定义表时索引列的顺序

B-Tree限制：

1）必须从最左列开始，否则索引失效；

2）不能略过索引直接调用其后续索引；

3）使用范围查询的索引列，后续列无法使用索引；

//限制均可通过更改索引列的顺序解决（定义索引时需严格考虑其顺序）

哈希

哈希：基于哈希表存储索引

1）通过所有索引列计算哈希码，将哈希码存储在索引中（索引结构十分紧凑）

2）哈希索引仅支持精确匹配（使用所有索引列的查询才有效）

3）哈希表中存储哈希码和指针的映射（指向每个数据行）

哈希索引查找流程：

1）计算数据哈希值，根据哈希值在哈希表中查找指针；

2）根据指针查找到数据行；

3）核验；

哈希限制：

1）哈希索引只包含哈希值；

2）哈希索引是无须存储的（无法用于排序）；

3）当发生哈希碰撞时，存储引擎会遍历链表中所有指针；

4）哈希索引仅支持等值比较查询（不支持模糊或范围查询）；

5）哈希索引不支持部分索引列匹配查找（哈希值通过全部索引列计算）；

哈希碰撞：数据通过哈希函数后映射到相同位置

1）数据库通过链接法解决碰撞（将碰撞的元素放在同一链表中）

2）尽量避免哈希碰撞（选择较多的列或更复杂的哈希函数）

InnoDB不支持哈希索引，仅支持自适应哈希（Adaptive Hash Index）

1）本质：当索引值使用频繁时，在内存中基于B-Tree索引创建个哈希索引；

2）需在查询时指定哈希函数（检索时通过哈希值进行查找）；

3）该功能仅由InnoDB自动实现（不可手动调用）；

//当B+树比较深时，自适应哈希可明显提高效率

//建议使用自定义哈希函数（返回值为正整数以节省时间和空间）

索引策略

索引策略：通过不同标准建立适用于不同场景的索引以提高性能

聚簇索引

聚簇索引：特殊的索引数据存储方式（索引即数据）

1）表中仅能有一个聚簇索引（数据行无法存储于两个不同的位置）；

2）聚簇数据能明显提高IO密集型应用的性能；

3）创建主键时会同时为其创建聚簇索引；

InnoDB要求定义的聚簇索引的表必须有主键

1）若没显示指定，MySQL会自动选择个非空且唯一标识符列为主键

2）若不存在该种列，MySQL为表生成个隐式字段作为主键（6个字节长整型）

//若无主键，建议创建代理主键（和应用无关，限制常为AUTO_INCREMENT）

//主键尽量为正整数，否则导致大量随机IO和频繁页分裂

聚簇索引的B-Tree页特性：

1）叶子节点存储数据行的全部数据（目录页只存储索引列）；

2）叶子节点同时存储事物ID和MVCC的回滚指针；

优点：

1）数据访问快：索引和数据保存在同一个B+树中（索引即数据）

2）排序/范围查找快：索引默认被排序（代表数据也被排序）

3）覆盖索引扫描可直接使用页节点中的主键值；

缺点：

1）插入速度依赖于插入顺序：按主键顺序插入最快（否则可能出现页分裂）

2）二级索引访问需进行两次索引查找：查主键和根据主键查行数据

3）全表扫面变慢：行比较稀疏或页分裂导致数据存储不连续

4）更新索引列的代价高：更新会导致新的行移动

二级索引

二级索引（非聚簇索引）：非主键类型的索引（普通索引）

1）二级索引的叶子节点存储的行的主键值（而非指向行的指针）

2）二级索引通常存在回表影响其性能；

//回表：根据索引查找到主键后，返回原表根据主键查找行数据

覆盖索引

覆盖索引：索引包含查询语句所需的所有列（不仅WHERE语句）

1）覆盖索引必须存储索引列的值（仅B-Tree实现）；

2）覆盖索引通常会导致索引冗余

//优化器会在执行前判断索引是否全包含依次判断是否使用覆盖索引

覆盖索引的优点：

1）减少磁盘IO：只需访问内存中的缓存即可完成查询；

2）随机IO便顺序IO：利于排序/范围查询

3）避免回表：二级索引可直接返回所需数据

覆盖索引的限制：

1）索引不能过多（否则性能不如全表扫描）；

2）索引中不能执行LIKE模糊查询；

多列索引

多列索引（组合索引）：使用多个单列组合成索引

1）本质：同时使用多个单列索引进行检索，再将结果合并；

2）多列索引的排序顺序是根据其索引定义的顺序；

//先按照第一个索引进行排序/查找，再按照第二个索引进行排序/查找

多列索引适用场景：

1）多个单列索引联合时（常为多个OR条件）；

2）多个单列索引相交时（常为多个AND条件）；

//多个单列索引进行OR/AND操作时，会消耗大量系统资源

//当多个列都需创建索引时，多列索引优于单列索引

多列索引的顺序应遵循以下建议：

1）常用的列放在靠前位置（不考虑排序和分组）；

2）根据运行频率高的查询语句调整索引列顺序；

前缀索引

前缀索引：仅使用数据的部分字符串作为索引

1）BLOB、TEXT和很长的VARCHAR类型的列必须使用前缀索引；

2）前缀索引需同时保证高索引选择性和较短的前缀；

索引选择性：不重复的前缀值（基数） / 数据表的记录总数（#T）

1）索引选择性越高，其查询效率也越高；

2）唯一索引的选择性是1（最好的索引选择性，性能也是最好的）

3）合适的前缀长度：用最常见的值的列和最常见的前缀列表进行比较

//索引的选择性需同时考虑最坏情况（尽量测试后再选择）

前缀索引的限制：

1）无法进行ORDER BY和GROUP BY；

2）无法做覆盖索引扫描；

索引优化

索引下推

索引下推（Index Condition Pushdown，ICP）：存储引擎层优化索引方式

1）本质：存在索引列的判断条件时，同时传输该判断条件至存储引擎层；

2）ICP可显著减少存储引擎访问表和服务器从存储引擎接收数据的次数；

//在存储引擎层就对数据进行过滤（而非原本的返回数据后再过滤）

索引下推的限制：

1）ICP仅能用于二级索引；

2）覆盖索引时，ICP无法使用；

3）子查询、存储过程和触发的条件均无法下推；

索引失效

（1）未满足最左前缀（底层B+树特性）

1）指定索引的左值未确定时，索引无法使用；

（2）错误的索引使用方法

1）索引用于表达式和当作函数参数均会导致索引失效；

2）类型转换的本质也是在底层调用函数（索引当成函数参数）；

（3）范围条件右侧无法使用索引

1）范围条件应尽量在最后指定（保证尽可能使用多的索引）；

2）可将范围查询等价变为多个等值查询（但不可过多）；

（4）不等值查询导致索引失效

1）索引默认仅支持等值查询；

（5）IS NOT NULL无法使用索引

1）可将IS NOT NULL转换为等价的IS NULL（可使用索引）；

（6）左模糊或全模糊查询无法使用索引

1）左模糊或全模糊时无法确定匹配项（只能使用右模糊）；

（7）OR前后存在非索引列无法使用索引

1）其可能仍需扫描全表，所以会直接扫描全表以获取数据；

（8）数据库和表的字符集未统一

1）导致数据会进行隐式转换无法使用索引；