实用指南:MySQL进阶-索引(全面讲解)

目录

1.索引概述

2.索引结构

B-Tree

B+Tree

MySQL中的优化B+Tree

Hash索引

3.为什么INNODB启用B+Tree​

4.索引分类

5.索引语法

6.性能分析

查看执行频率

慢查询日志

show profiles

explain执行计划​

7.索引使用规则

最左前缀法则​

索引失效

范围查询​

索引列运算

字符串不加引号

模糊查询

or语句连接

数据分布影响

覆盖索引&回表查询

前缀索引​

查询过程

单列索引&联合索引​

8.索引设计原则​

9.索引分类总结

数据结构

InnoDB实现

索引性质


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 值的列
联合索引基于多个列创建的索引多列,遵循最左前缀法则需要在多个列上进行查询的场景
全文索引用于全文搜索支持复杂的文本匹配和模糊查询需要全文搜索的列
空间索引用于处理地理空间数据支持空间范围查询和空间关系查询地理信息系统和需要处理空间数据的应用
posted @ 2025-08-23 18:23  yfceshi  阅读(26)  评论(0)    收藏  举报