【悄咪咪学MySql】10. 索引、性能分析

站长

2024年03月30日 14:47 · 阅读数 46

一. 简介

索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式应用（指向）数据，这样就可以在这些数据结构上实现高级查找算法买这种数据就是索引。

1. 模拟数据查询过程

无索引的情况下
- 数据库会全表扫描，根据条件语句寻找出符合条件语句的记录并返回
有索引的情况下
- 索引就是一种任意的数据结构，以二叉树为例，索引age字段，它可以帮助数据库在查找age过程中不需要全表扫描

2. 优缺点

优点
- 提高数据检索的效率，降低数据库的IO成本
- 通过索引对数据进行排序，降低数据排序的成本，降低CPU的消耗
缺点
- 索引列也占用空间
- 索引大大提高了查询效率，同时也降低了更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低

二. 结构

常见的索引结构：

索引结构	描述	支持的存储引擎
B+Tree索引	最常见的索引类型，大部分引擎都支持B+树索引	InnoDB、MyISAM、Memory
Hash索引	底层数据结构是用Hash表实现的，只有精确匹配索引列的查询才有效，不支持范围查询	Memory
R-Tree索引（空间索引）	空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常较少使用	MyISAM
Full-Text索引（全文索引）	是一种通过建立倒排索引，快速匹配文档的方式，类似于Lucene, Solr, ES	InnoDB(5.6版本以上)、MyISAM

1. 二叉树

缺点：

顺序插入时，会形成一个链表，查询性能大大降低；
大数据量的情况下，层级较深，检索速度慢；

解决缺点一时，可以考虑用红黑树这类自平衡二叉树

2. 红黑树

解决：顺序插入时，会形成一个链表，查询性能大大降低

缺点：

大数据量的情况下，层级较深，检索速度慢；

考虑解决这个缺点时，需要放弃二叉树，使得树有更多路，以此降低树的层级

3. B-树（多路平衡查找树）

多路:
- 解决：大数据量的情况下，层级较深，检索速度慢；
平衡：
- 解决：顺序插入时，会形成一个链表，查询性能大大降低；

3.1 B-树的要求

n阶B-树中：
- 非根非叶节点至少要有ceil(n/2)个分支，不满足时需要借位/合并
- 非根节点至少有floor(n/2)个关键字（key），不满足时需要借位/合并
- 每个关键字的左侧子树和右侧子树的高度差不能超过1

3.1 B-树的插入

以最大度数（max-degree）为5（5阶）的B-树为例：

每个节点最多存储4个key
每个节点最多存储5（即n+1）个指针

插入4个key时：

【悄咪咪学MySql】10. 索引、性能分析

插入第5个key，值为11时：

中间值升阶
左右断开，形成分支

1、2、6、7、11 中，中间值为6：

6升阶成根节点
1、2为根节点的左支
7、11为根节点的右支

【悄咪咪学MySql】10. 索引、性能分析

继续插入就会继续出现升阶的情况，这里放一张升阶到最终的图：

【悄咪咪学MySql】10. 索引、性能分析

3.2 B-树的删除

对于B-树关键字的删除，需要找到待删除的关键字，在结点中删除关键字的过程也有可能破坏B-树的特性，如旧关键字的删除可能使得结点中关键字的个数少于规定个数，这就需要

向其兄弟结点借关键字
和其孩子结点进行关键字的交换
进行结点的合并

【悄咪咪学MySql】10. 索引、性能分析

删除8、16：

关键字8在终端结点上，并且删除后其所在结点中关键字的个数不会少于2，因此可以直接删除
关键字16不在终端结点上，但是可以用17来覆盖16，然后将原来的17删除掉

【悄咪咪学MySql】10. 索引、性能分析

删除15：

15虽然也在终端结点上，但是不能直接删除，因为删除后当前结点中关键字的个数小于2
检查得知兄弟节点有多余最少key（即floor(5/2)）的节点，可以实行借位
- 17 覆盖删除需求 15
- 18 补位 17
- 删除 18

删除4：

不能直接删除，因为删除后该节点中关键字的个数小于2
检查得知兄弟节点均不能借位
先将4删除，并和父节点中的一个关键字和其关联的另一支看成一个整体

【悄咪咪学MySql】10. 索引、性能分析

由于非根非叶节点只有一个关键字(3或6)，不满足至少ceil(5/2)=3的要求：
- 查看兄弟节点，发现不能借位，那就只能合并
- 与父节点、兄弟节点合并

【悄咪咪学MySql】10. 索引、性能分析

4. B+树 B+Tree

B+树中

所有非叶节点均只为索引，数据存储在叶节点，即索引节点中的数据仍然会出现在叶节点中
叶节点最终会形成一个单向链表
叶节点的作用域为左开右闭，即其父节点中的关键字会保留在右子树的最左侧

【悄咪咪学MySql】10. 索引、性能分析

4.1 插入和删除

B+树的插入和删除与B-树基本相当：

插入并发生向上升阶时，升阶关键字还会保留在分裂的右树的最左侧
删除并发现关键字也为索引时，需要同时删除索引并按规定检查触发借位/合并

4.2 MySQL中的B+树索引

MySQL的B+树索索引是为提高区间访问性能，而对经典B+树的优化：

叶节点增加一个指向前一个链表单元的指针，形成环形双链表

【悄咪咪学MySql】10. 索引、性能分析

5. Hash

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中

5.1 Hash冲突及解决办法

在大量键值通过hash算法时，可能会有多个键值最终落在同一槽位上，这时就发生了hash冲突（hash碰撞），是通过槽位中的链表解决的，将多个键值hash存储在同一槽位的链表中，虽然不能一一对应，但也大幅度减少了搜索遍历量

5.2 特点

Hash索引只能用于对等比较(=, in)，不支持范围查询(between, >, <, ...)
无法利用索引完成排序
查询效率高，通常只需要一次检索，效率通常高于B+Tree索引

5.3 存储引擎支持

在MySQL中，只有Memory支持Hash索引，而InnoDB中具有自适应Hash功能，Hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的，即已优化在InnoDB中。

三. 为什么InnoDB选择B+Tree

相比于二叉树：
- 层级少，搜索效率高
- 自平衡，不会形成链表树，尽量降低搜索复杂度
相比于B-Tree：
- 因为MySQL数据都存储在页中，相同数据量下，受页容量限制的B+树的非叶节点能储存更多的索引关键字，降低树的层数，搜索效率更高

四. 索引分类

分类	含义	特点	关键字
主键索引	针对表中主键创建的索引	默认自动创建，只能一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

根据索引的储存形式分类：

分类	含义	特点
聚集索引	将数据存储与索引放到一块，索引结构的叶子节点保存了行数据	必须有，且只有一个
二级索引	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以有多个

聚集索引选取规则：

如果存在主键，主键索引就是聚集索引
如果不存在主键，将第一个UNIQUE索引作为聚集索引
如果没有主键，也没有合适的UNIQUE索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

五. SQL性能分析

1. SQL执行频率

-- 模糊匹配 7 个下划线
SHOW [SESSION|GLOBAL] STATUS LIKE 'Com_______';

用这个语句获取不同的SQL执行频率，可以为SQL优化提供方向引导。

2. 慢查询日志

慢查询日志记录了所有执行时间超过指定参数 LONG_QUERY_TIME（默认10s）的所有SQL语句的日志

查询慢查询日志开关

SHOW VARIABLES LIKE 'slow_query_log';

MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf)中配置：

# 开启MySQL慢查询日志开关
slow_query_log=1

# 设置慢日志的判定阈值为2s
long_query_time=2

3. profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作

SELECT @@HAVE_PROFILING;

profiling默认是关闭的，查询profiling参数查看是否开启

SELECT @@PROFILING;

开启profiling

SET PROFILING=1;

查看profiles报告

-- 查看每一条SQL的耗时基本情况
SHOW PROFILES;

-- 查看指定查询ID（query_id）的SQL语句各个阶段的耗时情况
SHOW PROFILE FOR QUERY 查询ID;

-- 查看指定查询ID（query_id）的SQL语句CPU的使用情况
SHOW PROFILE CPU FOR QUERY 查询ID;

4. explain执行计划

4.1 语法

-- 在任意 SELECT 语句前加上关键字 explain / desc
EXPLAIN SELECT 字段李彪 FROM 表名 [WHERE 条件];

4.2 报告结果含义

字段	含义
id	表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）
select_type	表示SELECT的类型，常见的右SIMPLE（简单表，即不适用表连接或子查询）、PRIMARY（主查询，即外层查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含的子查询）等
table	操作的表
partitions	-
type	表示连接类型，性能由好到差的连接类型为：NULL、system、const、eq_ref、ref、range、index、all
possible_keys	展示可能应用在这张表上的索引，一个或多个
key	实际用到的索引，如果为NULL，则没有使用索引
key_len	表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
ref
rows	MySQL认为必须要执行查询的行数，在InnoDB引擎的表中，是一个估算值，可能并不总是准确的
filtered	表示返回结果的行数占需读取行数的百分比，即遍历命中率，filtered的值越大越好
Extra	额外信息

六. 使用

1. 语法

1.1 创建索引

CREATE [UNIQUE|FULLTEXT] INDEX 索引名 ON 表名 (字段1[(前缀索引长度)] [ASC|DESC], ..., 字段n[(前缀索引长度)] [ASC|DESC]);

1.2 查看索引

SHOW INDEX FROM 表名;

1.3 删除索引

DROP INDEX 索引名 ON 表名;

2. 使用原则

2.1 最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。指的是查询从索引的最左列开始，并且不跳过索引中的列。

如果跳过，则其与其后边的索引列全部失效；
如果使用了范围查询（>, <），则其自身生效，但其后面的索引列失效；
- 但使用 >=, <= 时，能规避以上问题，后面的索引仍然生效；

所以：

在构建联合索引时，要按搜索条件的必要性从高到低、由前到后排列；

业务允许的情况下尽量使用 >=, <=；

2.2 覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），即减少回表查询。

减少 SELECT *
可以考虑为业务固定返回的字段添加联合索引

3. SQL提示

SQL提示，是优化数据库的一个重要手段，就是在SQL语句中加入一些人为的提示来达到优化操作的目的

当字段A存在与字段B、字段C的联合索引，同时字段A也有自己的单列索引时，MySQL自身优化器可能在只有字段A作为条件时也使用联合索引。这个时候就可以使用SQL提示来强制使用其单列索引，以此达到优化的目的。

3.1 语法

-- 建议使用某索引
EXPLAIN SELECT 字段列表 FROM 表名 USE INDEX(索引名) WHERE 条件;

-- 忽略某索引
EXPLAIN SELECT 字段列表 FROM 表名 IGNORE INDEX(索引名) WHERE 条件;

-- 强制使用某索引
EXPLAIN SELECT 字段列表 FROM 表名 FORCE INDEX(索引名) WHERE 条件;

4. 前缀索引

当字段A中存储了大量字符，如果要对字段A生成索引，那么将会十分耗费搜索时索引的IO，这个时候可以使用前缀索引。

前缀索引是将该字段A中的字符抽取一定长度来建立索引，抽取长度越长，唯一性越高、但搜索时IO占用越大；抽取长度越短，搜索时IO占用越小，但唯一性越低，影响索引的搜索效果。

4.1 通过SQL判断合适的截取长度

以该SQL不停尝试截取长度，当结果为1时，仍然是100%的唯一性的，这是一个权衡的过程，不一定要追求唯一性为100%

SELECT COUNT(DISTINCT SUBSTRING(字段名, 1, 尝试截取长度)) / COUNT(1) FROM 表名;

5. 注意事项

以下情况将使得索引失效:

索引列上计算，包括substring之类的函数计算；
'123' 这类字符串类型数字，在不加''查询时也能查询出来，但此时索引失效；
如果出现头部模糊查询 like '%abc'，此时索引失效
- ==但如果是尾部模糊查询 like 'abc%' 索引生效==
OR 拼接查询条件时，如果存在没有索引的列，则所有索引失效
- 针对 OR 拼接的没索引的字段添加索引能解决
MySQL评估使用索引比全表扫描更慢，则不使用索引；比如结果数量是全表的一半以上甚至是全部，则没有使用索引的必要了

七. 设计原则

针对数据量较大，且查询比较频繁的表建立索引
针对常作为查询条件、排序、分组操作的字段建立索引
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
如果是字符串类型的字段，字段的长度较长，可以针对字段的特点，建立前缀索引
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省储存空间，避免回表查询，提高查询效率
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价越大，会影响增删改的效率
如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束，当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询

转载自:https://juejin.cn/post/7351300892613771327

【悄咪咪学MySql】10. 索引、性能分析

一. 简介

1. 模拟数据查询过程

2. 优缺点

二. 结构

1. 二叉树

2. 红黑树

3. B-树 （多路平衡查找树）

3.1 B-树的要求

3.1 B-树的插入

3.2 B-树的删除

4. B+树 B+Tree

4.1 插入和删除

4.2 MySQL中的B+树索引

5. Hash

5.1 Hash冲突及解决办法

5.2 特点

5.3 存储引擎支持

三. 为什么InnoDB选择B+Tree

四. 索引分类

五. SQL性能分析

1. SQL执行频率

2. 慢查询日志

3. profile详情

4. explain执行计划

4.1 语法

4.2 报告结果含义

六. 使用

1. 语法

1.1 创建索引

1.2 查看索引

1.3 删除索引

2. 使用原则

2.1 最左前缀法则

2.2 覆盖索引

3. SQL提示

3.1 语法

4. 前缀索引

4.1 通过SQL判断合适的截取长度

5. 注意事项

七. 设计原则

3. B-树（多路平衡查找树）