首页  思维导图  详情

MySQL索引原理与优化

2024-12-30 18:12:04   12  举报





AI智能生成

索引优化

读书笔记

索引

数据库

查询优化

作者其他创作

大纲/内容

索引定义

存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作。

存储

索引是物理数据页存储，在数据文件中(InnoDB，ibd文件)，利用数据页(page)存储

树和Hash结构
测试链接：https://www.cs.usfca.edu/~galles/visualization/BST.html

二叉查找树

数据结构

缺点

可能会形成一个单向链表,对于读取某个指定节点时效率会很低.

含义

:左子树的键值小于根的键值，右子树的键值
大于根的键值。每个节点分别保存字段数据和一个指向对应数据记录物理地址的指针.

平衡二叉树 (AVL Tree)

含义

在符合二叉查找树的条件下，还满足任何节点的两个子树的高度最大差为1

数据结构

结构图

如何维持

AVL树的旋转方式

旋转流程

优点

叶子节点的层级减少

形态上能够保持平衡

查询效率提升,大量的顺序插入也不会导致查询性能的降低.

缺点

一个节点最多分裂出两个子节点, 树的高度太高,导致IO次数过多

节点里面只保存着一个关键字,每次操作获取的目标数据太少

B-Tree

思想设计

每个节点尽可能多的存储一些数据,每次磁盘IO就能多加载一些数据到内存,减少磁盘IO

含义

B-Tree是一种平衡的多路查找树,B树允许一个节点存放多个数据

B-Tree的阶=B-Tree中所有节点的子树个数的最大值【B-Tree的每个节点的元素可以视为一次I/O读取，树的高度表示最多的I/O次数】

满足条件

每个节点最多拥有m-1个关键字(根节点除外),也就是m个子树

根节点至少有两个子树(可以没有子树,有就必须是两个)

分支节点至少有(m/2)颗子树 (除去根节点和叶子节点其他都是分支节点)

所有叶子节点都在同一层,并且以升序排序

图

结构存储索引的特点

key为记录的键值，对应表中的主键值(聚簇索引)

data为一行记录中除主键外的数据

模型图

查找步骤

首先要查找节点,因为B-Tree通常是在磁盘上存储的,所以这步需要进行磁盘IO操作

第二步查找关键字,当找到某个节点后将该节点读入内存中, 然后通过顺序或者折半查找来查找关键
字,如果命中就结束查找。

若没有找到关键字，则需要判断大小来找到合适的分支继续查找,如果已经找到了叶子节点,就结束
查询。

模型图

总结

优点: B树可以在内部节点存储键值和相关记录数据，因此把频繁访问的数据放在靠近根节点的位
置将大大提高热点数据的查询效率。

缺点: B树中每个节点不仅包含数据的key值,还有data数据. 所以当data数据较大时,会导致每个节点
存储的key值减少,并且导致B树的层数变高.增加查询时的IO次数.

使用场景: B树主要应用于文件系统以及部分数据库索引，如MongoDB，大部分关系型数据库索引
则是使用B+树实现

B+Tree

含义

B+Tree是在B-Tree基础上的一种优化，使其更适合实现存储索引结构，InnoDB存储引擎就是用B+Tree 实现其索引结构。

特征

非叶子节点只存储键值信息.

所有叶子节点之间都有一个链指针.

数据记录都存放在叶子节点中.

图形模式

优势

降低树的高度，增大节点存储数据量

扫库和扫表能力更强【如果我们要根据索引去进行数据表的扫描，对B Tree进行扫描，需要把整棵树遍历一遍，而B+Tree只需要遍历他的所有叶子节点即可(叶子节点之间有引用)】

磁盘读写能力更强【加载到磁盘的关键字信息更多】

排序能力更强，如上面的图中可以看出，B+Tree天然具有排序功能。【他的根节点和支节点不保存数据区，所有根节点和支节点同样大小的情况下，保存的关键字要比B Tree要多】

查询效率更加稳定，每次查询数据，查询IO次数一定是稳定的

一颗B+Tree可以存放多少数据【4292 8704(3阶)】

Hash索引

优点

等值比较查询，而不包含排序或范围查询的需求，都适合使用哈希索引

没有哈希冲突的情况下,等值查询访问哈希索引的数据非常快

缺点

哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。

哈希索引只支持等值比较查询。不支持任何范围查询和部分索引列匹配查找。

哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。

模型图

索引

聚簇索引

模型图

注意问题

当使用主键为聚簇索引时，主键最好不要使用uuid【uuid的值太过离散，不适合排序且可能出线新增加记录的uuid,导致索引树调整复杂度变大，消耗更多的时间和资源】

建议使用int类型的自增【方便排序并且默认会在索引树的末尾增加主键值，对索引树的结构影响最小。而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到IO操作读取到的数据量。】

含义

将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据.【主键索引】

非聚簇索引

模型图

含义

将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置【非主键/二级索引】

注意问题

回表问题

使用覆盖索引来避免回表【索引包含了所有需要查询的字段的值 (不需要回表)，这个索引就是覆盖索引】

Explain性能分析

MySQL查询过程

字段说明

id值越大优先级越高，越先被执行

select_type

simple

简单的select查询，查询中不包含子查询或者UNION

primary

查询中若包含任何复杂的子部分，最外层查询被标记

subquery

在select或where列表中包含了子查询

union

union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是union

derived

在from列表中包含的子查询被标记为derived(派生表)，MySQL会递归执行这些子查询，把结果放到临时表中

union result

UNION 的结果

type

system > const > eq_ref > ref > range > index > ALL【最佳类型到最坏类型进行排序】

system

表中就仅有一行数据的时候. 这是const连接类型的一个特例,很少出现。

const

const表示命中主键索引(primary key) 或者唯一索引(unique),表示通过索引一次就找到数
据记录.

eq_ref

对于前一个表中的每个一行，后表只有一行被扫描【当联接使用索引的部分都是主键或惟一非空索引】

ref

非唯一性索引扫描(使用了普通索引), 对于前表的每一行(row)，后表可能有多于一行的数据被扫描,它返回所有匹配某个单独值的行.

range

索引上的范围查询,检索给定范围的行,between，in函数，> 都是典型的范围(range)查询,

index

出现index 是 SQL 使用了索引, 但是没有通过索引进行过滤,
需要扫描索引上的全部数据 (查找所有索引树,比ALL快一些,因为索引文件要比数据文件小 ),
一般是使用了索引进行排序分组.

ALL

没有使用到任何索引, 连接查询时对于前表的每一行，后表都要被全表扫描。

possible_keys

显示可能应用到这张表上的索引

key

实际使用的索引【查询中若使用了覆盖索引，则该索引仅出现在key列表中。】

key_len

表示索引中使用的字节数, 可以通过该列计算查询中使用索引的长度.【ken_len 越长, 说明索引使用的越充分】

ref

索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

rows

估算的找到所需的记录所需要读取的行数

filtered

它指返回结果的行占需要读到的行(rows列的值)的百分比

extra

Using filesort

得到所需结果集，需要对所有记录进行文件排序【这类SQL语句性能极差，需要进行优化】

Using temporary

需要使用临时表来存储结果集，常见于排序和分组查询

Using where

全表扫描或者在查找使用索引的情况下,但是还有查询条件不在索引字段当中

Using index

表示直接访问索引就能够获取到所需要的数据(覆盖索引) , 不需要通过索引回表.

Using join buffer

使用了连接缓存, 会显示join连接查询时,MySQL选择的查询算法 .

Using index condition

查找使用了索引 (但是只使用了一部分,一般是指联合索引)，但是需要回表查询数.

同时出现

using index :使用覆盖索引的时候就会出现

using where :在查找使用索引的情况下，需要回表去查询所需的数据

using index condition :查找使用了索引，但是需要回表查询数据

using index & using where :查找使用了索引，但是需要的数据都在索引列中能找到，所以
不需要回表查询数据

优化原则详解

最佳左前缀法则

如果创建的是联合索引,就要遵循该法则. 使用索引时，where后面的条件需要从索引的最左前列开始使用,并且不能跳过索引中的列使用。

例子

ALTER TABLE users ADD INDEX idx_nal (user_name,user_age,user_level) USING
BTREE;

场景

底层原理

分析

首先会对联合索引最左边的字段进行排序

在第一个字段的基础之上再对第二个字段进行排序

最左字段肯定是有序的, 第二个字段则是无序的【和B+树的结构有关系】

联合索引的排序方式是: 先按照第一个字段进行排序,如果第一个字段相等再根据第二个字段排序

所以如果直接使用第二个字段 user_age 通常是使用不到索引的.

分析图

不要在索引列上做任何计算

在索引列上做任何操作，比如计算、使用函数、自动或手动进行类型转换,会导致索引失效，从而使
查询转向全表扫描。

场景

使用系统函数 left()函数,对user_name进行操作

字符串不加单引号 (隐式类型转换)

范围之后全失效

where条件中如果有范围条件, 并且范围条件之后还有其他过滤条件,
那么范围条件之后的列就都将会索引失效.

例子

场景

避免使用 is null 、 is not null、!= 、or

like以%开头会使索引失效

like查询为范围查询，%出现在左边，则索引失效。%出现在右边索引未失效.

例子

场景

解决%出现在左边索引失效的方法

解决方案

like 失效的原理

%号在右: 由于B+树的索引顺序，是按照首字母的大小进行排序，%号在右的匹配又是匹配首字母。所以可以在B+树上进行有序的查找，查找首字母符合要求的数据。所以有些时候可以用到索引.

%号在左: 是匹配字符串尾部的数据，我们上面说了排序规则，尾部的字母是没有顺序的，所以不能按照索引顺序查询，就用不到索引.

两个%%号: 这个是查询任意位置的字母满足条件即可，只有首字母是进行索引排序的，其他位置的字母都是相对无序的，所以查找任意位置的字母是用不上索引的.

慢查询日志分析

慢查询设置与测试

慢查询参数

slow_query_log【默认OFF】

是否开启慢查询日志， ON(1) 表示开启,
OFF(0) 表示关闭。

slow-query-log-file

新版(5.6及以上版本)MySQL数据库慢查询日志存储路径。

long_query_time

慢查询阈值，当查询时间多于设定的阈值时，记录日志。

慢查询配置方式

开启了慢查询日志只对当前数据库生效

永久生效

修改配置文件my.cnf

-- 编辑配置
vim /etc/my.cnf
-- 添加如下内容
slow_query_log =1 slow_query_log_file=/var/lib/mysql/ruyuan-slow.log
-- 重启MySQL
service mysqld restart
mysql> show variables like '%slow_query%';

重启失效

set global slow_query_log=1

什么样的SQL才会记录到慢查询日志里面呢

set global long_query_time=1;【重新连接或新开一个会话才能看到修改值】

指定日志的存储方式

log_output='FILE' 表示将日志存入文件，默认值是'FILE'

。log_output='TABLE' 表示将日志存入数据库，这样日志信息就会被写入到 mysql.slow_log

未使用索引的查询也被记录到慢查询日志

set global log_queries_not_using_indexes=1;

日志分析

# Time: 2022-02-23T13:50:45.005959Z
# User@Host: root[root] @ localhost [] Id: 3
# Query_time: 3.724273 Lock_time: 0.000371 Rows_sent: 5 Rows_examined: 5000000 SET timestamp=1645624245;
select * from test_index where hobby = '20009951' or hobby = '10009931' or hobby = '30009931' or dname = 'name4000' or dname = 'name6600';

Time: 执行时间
User: 用户信息 ,Id信息 Query_time: 查询时长 Lock_time: 等待锁的时长 Rows_sent:查询结果的行数 Rows_examined: 查询扫描的行数 SET timestamp: 时间戳 SQL的具体信息

慢查询SQL优化

原因

等待时间长

锁表导致查询一直处于等待状态

执行时间长

1.查询语句写的烂
2.索引失效 3.关联查询太多join 4.服务器调优及各个参数的设置

优化思路

1. 优先选择优化高并发执行的SQL,因为高并发的SQL发生问题带来后果更严重.

2 定位优化对象的性能瓶颈(在优化之前了解性能瓶颈在哪)

3. 明确优化目标

4. 从explain执行计划入手