实用指南：MySQL进阶-索引（全面讲解）
目录
1.索引概述
索引(index)是帮助MySQ高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据， 这样就行在这些数据结构上达成高级查找算法，这种数据结构就是索引。
2.索引结构
| 索引类型 | 描述 | 特点 | 适用场景 | 支持的引擎 |
|---|---|---|---|---|
| B+树索引 | 基于B+树的数据结构，支持高效的范围查询和顺序访问。 | - 支持范围查询和排序操作。- 支持前缀索引和复合索引。- 拥护唯一性和非唯一性索引。 | - 大部分关系型数据库应用。- 需要高效范围查询和排序的场景。 | 大部分存储引擎，如 InnoDB、MyISAM。 |
| Hash索引 | 基于哈希表实现，通过哈希函数将键值映射到哈希表中。 | - 只协助精确匹配查询，不帮助范围查询。- 查询速度快，适合等值查询。 | - 需迅速等值查询的场景。- 例如，缓存系统中的键值对查询。 | - Memory 存储引擎。- 一些 NoSQL 数据库（如 Redis）。 |
| R-tree索引 | 一种空间索引，用于地理空间数据，如多边形、矩形等。 | - 专门用于空间数据的索引。- 帮助空间范围查询和空间关系查询。 | - 地理信息系统（GIS）应用。- 需要处理空间数据的场景。 | - MyISAM 存储引擎。- 一些专门的GIS数据库。 |
| Full-text索引 | 用于全文搜索的索引，依据建立倒排索引实现快速文本匹配。 | - 通过倒排索引搭建快速文本匹配。- 支持全文搜索和模糊查询。 | - 需要全文搜索的场景。- 例如，搜索引擎、文档管理系统。 | - InnoDB 存储引擎。- 一些专门的全文搜索引擎（如 Lucene、Solr、Elasticsearch）。 |
B-Tree
素材演变过程为向上分裂，n阶树中每个节点最多存储n-1个key，n个指针，拥有n个子节点。
B+Tree
MySQL中的优化B+Tree
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。
Hash索引
| 特点 | 描述 |
|---|---|
| 对等比较支持 | Hash 索引只能用于对等比较（如 =、IN），不支持范围查询（如 BETWEEN、>、< 等）。 |
| 不支持范围查询 | 由于 Hash 索引基于哈希表实现，无法帮助范围查询。 |
| 排序操作 | 无法利用 Hash 索引完毕排序管理。 |
| 查询效率 | 查询效率高，通常只需要一次检索即可完成，效率通常高于 B+Tree 索引。 |
| 存储引擎支持 | 在 MySQL 中，支持 Hash 索引的存储引擎主要是 Memory 引擎。 |
| 自适应 Hash 作用 | InnoDB 存储引擎具有自适应 Hash 功能，Hash 索引是存储引擎根据 B+Tree 索引在指定条件下自动构建的。 |
3.为什么INNODB使用B+Tree![]()
4.索引分类
特性 聚集索引 二级索引 定义 表中数据的存储顺序与索引的顺序一致 基于表中的非主键列创建的索引 数据存储 素材行按照索引顺序存储 叶子节点存储主键值，利用主键值回表查询素材行 每个表的数量 每个表只能有一个聚集索引 一个表能够有多个二级索引 查询效率 高效，直接定位到资料行 较低，需要回表查询完整的数据行 适用场景 主键查询、范围查询、排序和分组 非主键查询、多列查询、过滤和排序
B+Tree只有叶子节点存储数据，内部节点只存储指针和键。
一页代表一个节点，16K。 由于一行数据大小为1K，则一页存16行数据。
一层树只有1页*16行数据。
2层树由于一层存指针和键值，16*1024字节=n*8+(n+1)*6 =>n=1170个
则一个节点（一页）存1170个键，1171个指针。
说明一层有1170个键,1171个指针，指向第二层1171个页
2层树有1171*1171*16行数据.=18736行内容
3层树有1171*1171*16行数据=21939856行素材
5.索引语法
6.性能分析
查看执行频率
判断当前数据库在增删改查中，哪个操作频繁，针对其进行优化。
慢查询日志
show profiles
查看每条SQL语句的耗时情况，以及具体在哪一块的耗时。
explain执行计划![]()
![]()
7.索引使用规则
最左前缀法则![]()
![]()
联合索引满足最左前缀法则的原因主要与 B+Tree 的结构特性有关：
查询优化：B+Tree 的查询是从最左边的列开始的，逐步向右匹配。只有最左边的列（或多个列）可以用于优化查询。
索引覆盖：如果查询的列都在复合索引中，那么这个查询可以被索引覆盖，从而避免回表查询，进一步提高查询性能。
由于联合索引的B+Tree构造顺序的优先级是从左至右。所以在左列未知的情况下，右边的内容多是无序（例如该树中电话列）。
idx_user_pro_age_sta 以该联合索引为例演示：
索引长度为54，满足最左前缀法则
索引长度为49，满足最左前缀法则。 则status字段的索引长度为5
索引长度为47，满足最左前缀法则。 则age字段的索引长度为2
由于跳过profession字段，不满足最左前缀法则，不走索引，type=ALL为全表扫描。
索引长度为47，满足最左前缀法则，由于未包含age字段，后续字段索引失效。
索引失效
范围查询![]()
![]()
索引长度为49，由于age字段使用了范围查询，所以右侧列索引失效。
将范围查询替换为 (>=,<=) 索引长度为54，右侧列生效。
在实际业务中尽量包含等于的范围查询可以提高索引的查询效率，防止索引失效。
索引列运算
字符串不加引号
同一查询语句，由于status字段的字符串未加引号，索引长度从54->49，索引失效。
模糊查询
只要头部加了%模糊匹配，则失效。
or语句连接
原表中只有包含age的联合索引，单独查询age时不生效，无单列索引。
在or条件中，哪怕id有primary主键索引，但age无索引，于是整个查询索引失效。
同理。
在单独给age添加索引后，整个查询的索引生效。
数据分布影响
MySQL会评估采用索引和全表扫描的效率来决定是否使用索引。
要是查询内容为表中大部分数据满足则走全表，反之查询内容为小部分数据则走索引。
表中成员手机号为0-23
当查询为>0，则表中所有数据，评估为全表扫描效率高。
当查询>10，则为表中大部分数据，仍然评估为全表扫描效率高。
当>13时，则为小部分数据，评估为索引查询。
建议，具体由MySQL自己判断。就是ues
禁止采用该索引。就是ignore
force为必须采用该索引。
覆盖索引&回表查询
查询借助辅助索引找到了name和id列，但是未找到gender列
再通过id值，回表通过聚集索引到对应行过滤得到gender列
同样返回所有字段，通过聚集索引id可以直接得到。而凭借辅助索引得到id还需通过回表查询，性能低。
假如单独建立username的单列索引的话，由于查询列囊括password，单次查询无法得到该字段。
再次通过id进行回表查询得到对应行再过滤得到password。
所以最佳方案为：建立username和password的联合索引。
前缀索引![]()
根据选择性和索引字段长度，权衡合适的前缀长度。
查询过程
存在则根据其主键值回表查询对应完整行资料就是1.经过前缀索引找到对应行，要
2.根据查询到的完整行数据与where语句中条件比对，如果不同数据库会（凭借叶子节点中的链表）继续查找下一个匹配的前缀索引条目，直到找到完全匹配的行或所有匹配的前缀索引条目都已检查完毕。
单列索引&联合索引![]()
![]()
一次查询中MySQL会选一种索引完成本次查询，由于该查询中只用了phone字段的索引，name字段没走索引，该表返回列包含name字段需进行回表查询 Extra=NULL
8.索引设计原则![]()
9.索引分类总结
数据结构
| 索引类型 | 结构 | 查询效率 | 适用场景 |
|---|---|---|---|
| B+树索引 | 多路平衡树 | 范围查询和顺序访问高效 | 关系型数据库，需要高效范围查询和排序的应用 |
| 哈希索引 | 哈希表 | 精确匹配查询高效 | 缓存系统，需要快速等值查询的场景 |
| 倒排索引 | 倒排表 | 全文搜索和模糊查询高效 | 全文搜索引擎，需要全文搜索和模糊查询的应用 |
| R 树索引 | 空间索引 | 空间范围查询和空间关系查询高效 | 地理信息系统，需要处理空间数据的应用 |
B+树索引：适用于关系型数据库，支持高效范围查询和排序。
哈希索引：适用于需要飞快等值查询的场景，如缓存体系。
倒排索引：适用于全文搜索引擎，支持全文搜索和模糊查询。
R 树索引：适用于地理信息系统，支持空间范围查询和空间关系查询。
InnoDB构建
| 特性 | 聚簇索引 | 非聚簇索引 |
|---|---|---|
| 定义 | 必须存在，默认为主键索引，若没有主键则选取任意唯一索引作为聚簇索引 | 基于表中的非主键列创建的索引 |
| 数据存储 | 完整行数据 | 叶子节点存储主键值，依据主键值回表查询素材行 |
| 每个表的数量 | 每个表只能有一个聚簇索引 | 一个表可以有多个非聚簇索引 |
| 查询效率 | 高效，直接定位到数据行 | 较低，需要回表查询完整的数据行 |
| 适用场景 | 主键查询、范围查询、排序和分组 | 非主键查询、多列查询、过滤和排序 |
索引性质
| 索引类型 | 定义 | 特点 | 适用场景 |
|---|---|---|---|
| 普通索引 | 基于一个或多个列创建的索引 | 通过不唯一，能够包含重复值 | 得飞快查找但不要求唯一性的列 |
| 主键索引 | 用于唯一标识表中的每一行 | 唯一，非空 | 需要唯一标识每一行的列 |
| 唯一索引 | 用于确保索引列的值是唯一的 | 唯一，可以包含 NULL 值 | 需要确保列值唯一但允许 NULL 值的列 |
| 联合索引 | 基于多个列创建的索引 | 多列，遵循最左前缀法则 | 需要在多个列上进行查询的场景 |
| 全文索引 | 用于全文搜索 | 支持复杂的文本匹配和模糊查询 | 需要全文搜索的列 |
| 空间索引 | 用于处理地理空间数据 | 支持空间范围查询和空间关系查询 | 地理信息系统和需要处理空间数据的应用 |
浙公网安备 33010602011771号