Mysql面试知识点_数据库
2024-12-10 21:27:04 0 举报AI智能生成
Mysql面试知识点_数据库 面试必备
作者其他创作
大纲/内容
mysql事务
ACID
原子性
一致性
隔离性
持久性
并发事务处理带来的问题
更新丢失(Lost Update)或脏写
脏读
不可重读
幻读
事务隔离级别
默认的事务隔离级别是可重复读
操作
常看当前数据库的事务隔离级别
show variables like 'tx_isolation';
设置事务隔离级别
set tx_isolation='read-uncommitted';
set tx_isolation='read-committed';
set tx_isolation='repeatable-read';
set tx_isolation='serializable';
隔离级别
读未提交
脏读
不可重复读
幻读
读已提交
不可重复读
幻读
可重复读
幻读
可串行化
数据库锁
分类
从性能上分为乐观锁(用版本对比来实现)和悲观锁
从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)
读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响
写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁
从对数据操作的粒度分，分为表锁和行锁
表锁
特点
每次操作锁住整张表
开销小，加锁快
这里的开销指的是加锁的开销
不会出现死锁
当不同事务都修改一张表数据时不会出现死锁
当不同事务互相持有对方表锁时出现死锁
session1
begin;
update account set balance = 1 where name = 'lilei';
update user set age = 2 where name = 'aa';
session2
begin;
update user set age = 1 where name = 'bb';
update account set balance = 1 where name = 'hanmei';
锁定粒度大，发生锁冲突的概率最高，并发度最低
一般用在整表数据迁移的场景
操作
加锁：
lock table 表名称 read(write),表名称2 read(write);
lock table 表名称 read(write),表名称2 read(write);
查看表上加过的锁：
show open tables;
show open tables;
删除表锁：
unlock tables;
unlock tables;
行锁
特点
每次操作锁住一行数据
开销大，加锁慢
会出现死锁
锁定粒度最小，发生锁冲突的概率最低，并发度最高
引擎支持性
InnoDB
在执行查询语句SELECT时(非串行隔离级别)，不会加锁
update、insert、delete操作会加行锁
MyISAM
不支持行锁
执行查询语句SELECT前自动给涉及的所有表加读锁
执行update、insert、delete操作会自动给涉及的表加写锁
间隙锁
间隙锁，锁的就是两个值之间的空隙
间隙锁是在可重复读隔离级别下才会生效
间隙有 id 为 (3,10)，(10,20)，(20,正无穷) 这三个区间
update account set name = 'zhuge' where id > 8 and id <18
id在 (3,20]区间都无法修改数据
临键锁
Next-Key Locks是行锁与间隙锁的组合
上面例子里的(3,20]的整个区间可以叫做临键锁
无索引行锁会升级为表锁
锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁
以该表为例
session1
begin
update account set balance = balance-5 where name = 'lilei';
session2
begin
在session1的update之后：
update account set balance = balance-5 where name = 'hanmei';
update account set balance = balance-5 where name = 'hanmei';
在任意session
show open tables;
此时将看到出现表锁
注意：如果只是单纯在session1操作，不会出现表锁，只有当其他session更新时才会出现
行锁分析
show status like 'innodb_row_lock%';
Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg: 每次等待所花平均时间
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg: 每次等待所花平均时间
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间
查看事务：select * from INFORMATION_SCHEMA.INNODB_TRX;
查看锁：select * from INFORMATION_SCHEMA.INNODB_LOCKS;
查看锁等待：select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
查看锁等待详细信息：show engine innodb status\G;
释放锁：kill trx_mysql_thread_id
锁优化建议
尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能减少检索条件范围，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
尽可能低级别事务隔离
MVCC多版本控制
undo日志版本链
指一行数据被多个事务依次修改后，MySQL会保留修改前的数据undo回滚日志，
并且用两个隐藏字段trx_id(事务id)和roll_pointer(指向上一条日志的指针)把这些undo日志串联起来形成一个历史记录版本链
并且用两个隐藏字段trx_id(事务id)和roll_pointer(指向上一条日志的指针)把这些undo日志串联起来形成一个历史记录版本链
图
隐藏标识：删除标识(头信息中)
和事务是否提交无关
只有一份，被所有事务共享
read-view
当事务开启，执行任何查询sql时会生成当前事务的一致性视图read-view
可重复级别直到事务结束也不会变化
读已提交隔离级别在每次执行查询sql时都会重新生成
生成时机
只在查询时生成
begin开始事务时不生成
begin后立即修改数据也不生成
事务id
查询时生成临时事务id
修改数据时生成实际的事务id
组成
未提交事务id数组+最大已创建事务id
三个区间
已提交事务
小于最小未提交事务id
未提交事务和已提交事务
大于等于最小未提交事务， 小于最大已创建事务id
未开始事务(在当前生成视图时刻)
大于最大已创建事务id
查询机制
使用read-view比对undo日志版本链
查看undo日志中的记录是否可见
1.是否在已提交事务中
是，可见
2.是否在未提交和已提交事务的区间中
是，判断该记录的事务id是否在未提交事务id数组中
在,判断该记录的事务id是否是本事务的事务id
是，可见
否，不可见
不在，可见
否，不可见
3.在未开始的事务中
是，不可见
不符合要求则使用roll_point继续比对下一行记录
如果是删除的记录，则不返回数据
存储引擎
执行步骤
update account set name = 'zhangsan' where id = 1
图
为什么MySQL不能直接更新磁盘上的数据而且设置这么一套复杂的机制来执行SQL？
对磁盘文件是进行随机读写
BufferPool是在内存读写
redo日志和undo日志是顺序读写
底层原理
记录存储结构
数据划分为若干页
作为磁盘和内存之间
交互的基本单位
交互的基本单位
大小一般为 16 KB
行
平时是以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方
式也被称为行格式或者记录格式
式也被称为行格式或者记录格式
4 种不同类型
Compact
图
变长字段长度列表
可变长数据类型，存储数据占用字节数不固定
变长的数据类型
VARCHAR(M)
TEXT
BLOB
记录方式
1个字节能表示最大的数：127
超过 255 字节并且真实存储的字节数超过 127 字节，使用 2 个字节
否则使用 1 个字节
数据格式
逆序存放每个字段所占长度
为什么逆序？
这样在读取数据时，可以使得前面列的数据和实际长度可以在同一个cacheline中
name-varchar,phone-varchar,age-int
占用字节数，逆序
NULL值列表
某些列可能存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据
中存储会很占地方，所以可以把这些值为 NULL 的列统一管理起来
中存储会很占地方，所以可以把这些值为 NULL 的列统一管理起来
记录方式
每个允许存储 NULL 的列对应一个二进制位
二进制位的值为1时，代表该列的值为NULL
二进制位的值为0时，代表该列的值不为NULL
数据格式
逆序存放每个字段是否为null
为什么逆序？同上变长字段长度列表
name-varchar,phone-varchar,age-int
数据格式
记录头信息
5个字节(40个二进制位)
预留位 1
预留位 2
delete_mask
标记该记录是否被删除
min_rec_mask
B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned
表示当前记录拥有的记录数
heap_no
表示当前记录在页的位置信息
record_type
表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录
next_record
下一条记录的位置
隐藏列
DB_ROW_ID(row_id)
非必须，6 字节，表示行 ID，唯一标识一条记录
出现时机
优先使用用户自定义主键作为主键
如果没有定义主键，则选取一个Unique 键作为主键
也没有unique, 添加一个名为 row_id 的隐藏列作为主键
DB_TRX_ID
必须，6 字节，表示事务 ID
DB_ROLL_PTR
必须，7 字节，表示回滚指针
处理数据溢出
对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的该列的前768 个字节的数据，
然后把剩余的数据分散存储在几个其他的页中，记录的真实数据处用 20 个字节存储指向这些页的地
址。这个过程也叫做行溢出，存储超出 768 字节的那些页面也被称为溢出页。
然后把剩余的数据分散存储在几个其他的页中，记录的真实数据处用 20 个字节存储指向这些页的地
址。这个过程也叫做行溢出，存储超出 768 字节的那些页面也被称为溢出页。
Redundant
Dynamic
MySQL5.7 的默认行格式
和 Compressed 行格式大致相同，不同点在于在处理行溢出数据时的方式
处理数据溢出
不会在记录的真实数据处存储字段真实数据的前 768 个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据
处存储其他页面的地址。
处存储其他页面的地址。
Compressed
索引页格式
大小一般是 16KB
索引页结构
体系结构
图
文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html
表空间
系统表空间
ibdata1
独立表空间
表名.ibd
任何类型的页都有专门的地方保存页属于哪个表空间，同时表空间中的每一
个页都对应着一个页号，这个页号由 4 个字节组成，也就是 32 个比特位，所以
一个表空间最多可以拥有 2^32 个页，如果按照页的默认大小 16KB 来算，一个表
空间最多支持 64TB 的数据。
个页都对应着一个页号，这个页号由 4 个字节组成，也就是 32 个比特位，所以
一个表空间最多可以拥有 2^32 个页，如果按照页的默认大小 16KB 来算，一个表
空间最多支持 64TB 的数据。
如何管理表空间
图
区
连续的64 个页就是一个区
一个区默认占用 1MB
引入目的
一个区就是在物理位置上连续的 64 个页
范围查询只需要定位到最左边的记录和最右边的记录，然后沿着双向链表一直扫描就可以了
顺序 I/O
组
每 256个区被划分成一个组
第一个组最开始的 3 个页面的类型是固定的
用来登记整个表空间的一些整体属性以及本组所有的区被称为 FSP_HDR，
也就是 extent 0 ~ extent 255 这 256个区，整个表空间只有一个 FSP_HDR
也就是 extent 0 ~ extent 255 这 256个区，整个表空间只有一个 FSP_HDR
其余各组最开始的 2 个页面的类型是固定的
一个 XDES 类型，用来登记本组
256 个区的属性，FSP_HDR 类型的页面其实和 XDES 类型的页面的作用类似，只
不过 FSP_HDR 类型的页面还会额外存储一些表空间的属性
256 个区的属性，FSP_HDR 类型的页面其实和 XDES 类型的页面的作用类似，只
不过 FSP_HDR 类型的页面还会额外存储一些表空间的属性
段
不对应表空间中某一个连续的物理区域，而是一个逻辑上的概念
区分叶子节点和非叶子节点
双写缓冲区/双写机制
双写缓冲区
系统表空间的 extent 1 和 extent 两个区
双写
第一遍是写到doublewrite buffer
第二遍是写到真正的数据文件中
一种特殊文件 flush 技术
保证InnoDB 存储引擎数据页的可靠性
过程
在把页写到数据文件之前
先把它们写到一个叫 doublewrite
buffer（双写缓冲区）的连续区域内
buffer（双写缓冲区）的连续区域内
在写 doublewrite buffer 完成后，InnoDB 才
会把页写到数据文件的适当的位置
会把页写到数据文件的适当的位置
为什么需要他？
如果直接往页里写数据
每次只能写4kb，而一页数据有16kb
中间过程如果发送意外，就会导致页损坏
引入双写缓冲区
如果在写页的过程中发生意外崩溃，InnoDB在稍后的恢复过程中在 doublewrite buffer 中找到完好的 page 副本用于恢复
降低了大概 5-10%左右性能
在 slave上可以关闭
BufferPool
大小
默认128M
show variables like 'innodb_buffer_pool_size';
最小值为 5M
优化：设置为机器内存的 60%左右
参考文档：https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html
更大的缓冲池只需更少的磁盘 I/O 来多次访问相同的表数据
依据缓存命中率设置大小
show engine innodb status\G
对于读取多的情况,如果没达到 98%以上，都说明 buffer 不够，可以扩，如果
给命中都能达到 98%~100%了，而且还有大量的 free page 那说明够用了
给命中都能达到 98%~100%了，而且还有大量的 free page 那说明够用了
内部组成
默认的缓存页大小和在磁盘上默认的页大小是一样的，16kb
控制块
为了更好的管理缓存页
包括该页所属的表空间编号、页号、缓存
页在 Buffer Pool 中的地址、链表节点信息、一些锁信息以及 LSN 信息
页在 Buffer Pool 中的地址、链表节点信息、一些锁信息以及 LSN 信息
每个缓存页对应的控制信息占用的内存大小是相同的
控制块和缓存页是一一对应的
控制块被存放到 Buffer Pool 的前边，缓存页被存放到 Buffer Pool 后边
每个控制块大约占用缓存页大小的 5%
free 链表
记录一下 Buffer Pool 中哪些缓存页是可用的
把所有空闲的缓存页对应的控制块作为一个节点放到一个链表中
刚刚完成初始化的 Buffer Pool 中所有的缓存页都是空闲的，所以每一
个缓存页对应的控制块都会被加入到 free 链表中
个缓存页对应的控制块都会被加入到 free 链表中
图
每当需要从磁盘中加载一个页到 Buffer Pool 中时，就从 free 链表中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填
上（就是该页所在的表空间、页号之类的信息），然后把该缓存页对应的 free链表节点从链表中移除，表示该缓存页已经被使用了
上（就是该页所在的表空间、页号之类的信息），然后把该缓存页对应的 free链表节点从链表中移除，表示该缓存页已经被使用了
缓存页的哈希处理
要访问某个页中的数据，快速定位该页是否在bufferpool
key:表空间号 + 页号
value:缓存页
用表空间号 + 页号作为 key，缓存页作为 value 创建一个哈希表，
在需要访问某个页的数据时，先从哈希表中根据表空间号 + 页号看看有没有对
应的缓存页，如果有，直接使用该缓存页就好，如果没有，那就从 free 链表中
选一个空闲的缓存页，然后把磁盘中对应的页加载到该缓存页的位置
在需要访问某个页的数据时，先从哈希表中根据表空间号 + 页号看看有没有对
应的缓存页，如果有，直接使用该缓存页就好，如果没有，那就从 free 链表中
选一个空闲的缓存页，然后把磁盘中对应的页加载到该缓存页的位置
flush 链表
用于判断哪些页是脏页
脏页：当我们修改了 Buffer Pool 中某个缓存页的数据，那它就和磁盘上的页不一
致了，这样的页称为脏页
致了，这样的页称为脏页
结构和free链表相似
使用原因
修改了BufferPool数据如果立即同步到磁盘的页上，会严重的影响程序的性能
所以应是在未来的某个时间点进行同步
这就需要知道 BufferPool 中哪些页是脏页
LRU 链表
使用原因
Buffer Pool 对应的内存大小是有限的
free链表中已经没有多余的空闲缓存页时，需要把某些旧的缓存页从 Buffer Pool 中移除
为什么使用LRU,而不是LFU
从业务角度考虑，业务总是访问最近的数据，而很少访问历史的数据
简单LRU的问题
原理
访问某个页时
如果该页不在 Buffer Pool 中，在把该页从磁盘加载到 Buffer Pool 中的缓存页
时，就把该缓存页对应的控制块作为节点塞到 LRU 链表的头部
时，就把该缓存页对应的控制块作为节点塞到 LRU 链表的头部
如果该页已经缓存在 Buffer Pool 中，则直接把该页对应的控制块移动到 LRU
链表的头部
链表的头部
只要我们使用到某个缓存页，就把该缓存页调整到 LRU 链表的头
部，这样 LRU 链表尾部就是最近最少使用的缓存页
部，这样 LRU 链表尾部就是最近最少使用的缓存页
当 Buffer Pool 中的空
闲缓存页使用完时，到 LRU 链表的尾部找些缓存页淘汰就行了
闲缓存页使用完时，到 LRU 链表的尾部找些缓存页淘汰就行了
问题
InnoDB 提供了预读
所谓预读，就是 InnoDB认为执行当前的请求可能之后会读取某些页面，
就预先把它们加载到 Buffer Pool中。
就预先把它们加载到 Buffer Pool中。
线性预读
如果顺序访问了某个区（extent）的页面数超过56，就会触发一次异步读取下一个
区中全部的页面到 Buffer Pool 的请求
区中全部的页面到 Buffer Pool 的请求
56是变量innodb_read_ahead_threshold的默认值
随机预读
如果 Buffer Pool 中已经缓存了某个区的 13 个连续的页面，不论这些页面是不
是顺序读取的，都会触发一次异步读取本区中所有其他的页面到 Buffer Pool 的请
求
是顺序读取的，都会触发一次异步读取本区中所有其他的页面到 Buffer Pool 的请
求
变量innodb_random_read_ahead控制是否开启，默认off
不小心进行了全部扫描
MySQL中的LRU链路
划分为两部分
存储使用频率非常高的缓存页（young区）
热数据
分存储使用频率不是很高的缓存页（old区）
冷数据
划分比例：37，系统变量：innodb_old_blocks_pc
young区
被访问的缓存页位于 young 区域的 1/4 的后边，才会被移动到 LRU 链表头部
可以降低调整 LRU 链表的频率，从而提升性能
old区
当磁盘上的某个页面在初次加载到 Buffer Pool 中的某个缓存页时，该缓存页对应的控制块会被放到 old 区域的头部
针对预读到 Buffer Pool却不进行后续访问的页面就会被逐渐从 old 区域逐出，而不会影响 young 区域中
被使用比较频繁的缓存页。
被使用比较频繁的缓存页。
针对全表扫描
问题
虽然首次被加载到 Buffer Pool 的页被放到了 old 区域的头
部，但是后续会被马上访问到，每次进行访问的时候又会把该页放到 young 区域
的头部，这样仍然会把那些使用频率比较高的页面给顶下去
部，但是后续会被马上访问到，每次进行访问的时候又会把该页放到 young 区域
的头部，这样仍然会把那些使用频率比较高的页面给顶下去
解决
在执行全表扫描的过程中，即使某个页面中有很多条记录，去多次访问这个页面所花费的时间也是非常少的
在对某个处在 old 区域的缓存页进行第一次访问时就在它对应的控制块
中记录下来这个访问时间，如果后续的访问时间与第一次访问的时间在1s间隔内，那么该页面就不会被从 old 区域移动到 young 区域的头部
中记录下来这个访问时间，如果后续的访问时间与第一次访问的时间在1s间隔内，那么该页面就不会被从 old 区域移动到 young 区域的头部
1s是变量innodb_old_blocks_time默认值
并发冲突
在多线程环境下，访问 Buffer Pool 中的各种链表都需要加锁处理，在 Buffer
Pool 特别大而且多线程并发访问特别高的情况下，单一的 Buffer Pool 可能会影响
请求的处理速度
Pool 特别大而且多线程并发访问特别高的情况下，单一的 Buffer Pool 可能会影响
请求的处理速度
多个 Buffer Pool 实例降低冲突
BufferPool内存小于1G时无法配置多个实例
查看BufferPool信息：SHOW ENGINE INNODB STATUS\G
刷盘时机
从 LRU 链表的冷数据中刷新一部分页面到磁盘
从 flush 链表中刷新一部分页面到磁盘
BUF_FLUSH_LIST
BUF_FLUSH_SINGLE_PAGE
三大特性
Buffer Pool
自适应 Hash 索引
双写缓冲区/双写机制
WAL
Write-ahead logging，预写式日志
MySQL中事务的具体实现机制
所有的修改都先被写入到日志中，然后再被应用到系统中
包含 redo 和 undo 两部分信息
redo日志
每当有操作时，在数据变更之前将操作写入 redo log
undo日志
当一些变更执行到一半无法完成时，可以根据撤销日
志恢复到变更之间的状态
志恢复到变更之间的状态
redo日志
保证事务的持久性
redo 日志会把事务在执行过程中对数据库所做的所有修改都记录下来，
在之后系统崩溃重启后可以把事务所做的任何修改都恢复出来
在之后系统崩溃重启后可以把事务所做的任何修改都恢复出来
日志格式
type:该条 redo 日志的类型，redo 日志设计大约有 53 种不同的类型日志
space ID：表空间 ID
page number：页号
data：该条 redo 日志的具体内容
日志文件组
ib_logfile0
ib_logfile1
写入过程
redo log block
512 字节
日志缓冲区
被划分成若干个连续的redo log block
通过参数：innodb_log_buffer_size指定，默认16M
顺序写入，先写满一个block，再写下一个
redo 日志刷盘时机
占满了 log buffer 总容量的大约一半左右
事务提交时
后台线程每秒刷新
恢复
redo日志以页面为单位恢复，hash表，key为页码，value为修改的记录
Log Sequence Number
日志序列号
随着redo日志量不断递增
优点
占用的空间非常小
顺序写入磁盘
为什么先写入redo日志而不是直接写入磁盘？
刷新一个完整的数据页太浪费了
随机 IO 刷起来比较慢
undo日志
保证事务的原子性
事务执行过程中遇到错误
程序进行rollback
记录对数据的改动
插入一条记录时，至少要把这条记录的主键值记下来
删除了一条记录，至少要把这条记录中的内容都记下来
修改了一条记录，至少要把修改这条记录前的旧值都记录下来
存储中表空间中
系统表空间
undo tablespace
数据结构
数据结构
二叉搜索树
若索引元素时顺序的，如1234，则将退化为链表
在数据量达到千万级时，树的层度过高，查询效率低
红黑树
数据结构
平衡树，比二叉搜索树好一些，不会退化成链表
同样是颗二叉树，在数据量达到千万级时，树的层度过高，查询效率低
Hash
查询效率O(1), 比B+树更高效
仅能满足=、in，不支持范围查询
hash冲突问题
B树
数据结构
数据结构
一个结点存储更多的元素
非叶子结点同样存储数据
结点由于存储了数据，所以存储的索引会更少
叶子结点无指向其他结点的指针
对范围查询不友好
B+树
数据结构
数据结构
非叶子结点不存储数据，只存储索引，相比于B树，在相同深度下存放更多的索引，MySQL中千万级数据树高只需3层
叶子结点用指针相连，提高区间访问的性能
存储引擎
InnoDB
聚集索引
使用两个文件存储表
.frm 表结构文件
.idb 索引数据文件
比非聚集索引效率更高(减少了一次回表)
叶子结点包含了完整的数据记录
二级索引的叶子结点存储的数据为主键id
普通二级索引
联合索引
MyISAM
非聚集索引（索引和数据分离）
使用三个文件存储表
.frm 表结构文件
.MYD 数据文件
.MYI 索引文件
叶子结点存储的是数据所在磁盘的地址
算法
查找算法
1.将根结点加载到内存
2.通过二分查找定位到下一个结点在磁盘上的地址
3.将该结点加载到内存，继续定位，直到找到目标数据
4.若是二级索引，则使用主键回表查找主键索引
执行计划详解
文章：https://zijiancode.cn/archives/mysql-explain
案例
表结构
联合索引
explain select * from employees t1 join (select id from employees order by `name` limit 10000, 10) t2 ON t1.id = t2.id;
explain列
参考文档：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
id列
id列的编号是 select 的序列号，有几个 select 就有几个id
id的顺序是按 select 出现的顺序增长的
id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行
select_type列
simple：简单查询。查询不包含子查询和union
primary：复杂查询中最外层的 select
subquery：包含在 select 中的子查询（不在 from 子句中）
derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）
union：在 union 中的第二个和随后的 select
table列
表示 explain 的一行正在访问哪个表
当 from 子句中有子查询时，table列是 <derivenN>格式，表示当前查询依赖 id=N 的查询，于是先执行id=N 的查询
当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id
type列
表示关联类型或访问类型，即MySQL决定如何查找表中的行
从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
一般来说，得保证查询达到range级别，最好达到ref
NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表。
const, system：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是const的特例，表里只有一条元组匹配时为system
eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在const 之外最好的联接类型了，简单的 select 查询不会出现这种 type
ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行
range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行
index：扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些
ALL：即全表扫描，扫描你的聚簇索引的所有叶子节点
possible_keys列
显示查询可能使用哪些索引来查找
explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询
key列
显示mysql实际采用哪个索引来优化对该表的访问
如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。
key_len列
显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列
计算规则
各类型所占字节
char(n)：n字节长度
varchar(n)：如果是utf-8，则长度 3n + 2 字节，加的2字节用来存储字符串长度
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节
date：3字节
timestamp：4字节
datetime：8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL
explain select * from employees where name = 'zhangsan2'
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
24*3 + 2 = 74
图片
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引
ref列
显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：order.id）
rows列
mysql估计要读取并检测的行数，注意这个只是估计，不是结果集里的行数
大概行数计算方式：rows列的值 * filtered列的值 / 100：rows*filtered/100
Extra列
Using index：使用覆盖索引
explain select name from employees;
Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖
explain select * from employees where name > 'zhangsan10';
Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围
explain select name,hire_time from employees where name > 'zhangsan10';
Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化
Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的
Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段时
索引实践
最左匹配原则
explain select * from employees where name > 'zhangsan10'
可能走name索引
explain select * from employees where name > 'zhangsan99999'
走name索引
explain select * from employees where name > 'zhangsan99999' and age = 10 and position = 'dev'
走name索引
explain select * from employees where age = 10 and name > 'zhangsan99999' and position = 'dev'
走name索引
explain select * from employees where name like 'zhangsan99999%' and age = 10 and position = 'dev'
走name age position索引
全值匹配
不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
存储引擎不能使用索引中范围条件右边的列
尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句
mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
is null,is not null 一般情况下也无法使用索引
like以通配符开头（'%abc...'）mysql索引失效会变成全表扫描操作
字符串不加单引号索引失效
少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，详见范围查询优化
范围查询优化
mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。有时候由于单次数据量查询过大导致优化器最终选择不走索引
优化方法：可以将大的范围拆分成多个小范围
mysql执行流程图
Server层
连接器
建立连接
获取权限
维持管理连接
默认8小时空闲后断开
5.7版本使用`mysql_reset_connection`重置连接
查询缓存
8.0已删除
解析器
词法分析
语法分析
构建语法树
预处理器
检查 SQL 查询语句中的表或者字段是否存在
将 select * 中的 * 符号，扩展为表上的所有列
优化器
负责将 SQL 查询语句的执行方案确定下来
执行器
主键索引查询
全表扫描
索引下推
负责建立连接、分析和执行 SQL
存储引擎层
innodb
myisam
其他
索引
最左前缀法则
索引的匹配从最左边的字段开始，匹配成功才能往右继续匹配下一个字段
索引下推
在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数
覆盖索引优化
覆盖索引：查询的字段被索引完全覆盖
主键索引(innodb引擎)会存储完整的记录，而二级索引只存储主键。
二级索引一个叶子结点能够存放的记录会多的多，扫描二级索引比扫描主键索引的IO次数会少很多
优化案例
explain select * from employees order by name limit 10000,10;
explain select * from employees t1 join (select id from employees order by `name` limit 10000, 10) t2 on t1.id = t2.id;
先使用覆盖索引方式查出10条数据，再使用这10条数据连接查询
排序方式
使用文件排序 use filesort(效率低)
order by的条件不在索引列上
单路排序
一次性取出满足条件行的所有字段，然后在sort buffer中进行排序
双路排序(回表排序)
首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，
然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；
然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；
扫描索引本身完成排序 user index(效率高)
无where条件，order by语句使用索引最左前列
使用where子句与order by子句条件列组合满足索引最左前列
分页查询优化
根据自增且连续的主键排序的分页查询
select * from employees limit 90000,5;
select * from employees where id > 90000 limit 5;
根据非主键字段排序的分页查询
select * from employees ORDER BY name limit 90000,5;
select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;
Join关联查询优化
优化方法
关联字段加索引
小表驱动大表
常见算法
嵌套循环连接 Nested-Loop Join(NLJ) 算法
基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法
in和exsits优化
小表驱动大表
当B表的数据集小于A表的数据集时，in优于exists
select * from A where id in (select id from B)
当A表的数据集小于B表的数据集时，exists优于in
select * from A where exists (select 1 from B where B.id = A.id)
count(*)查询优化
字段有索引
count(*)≈count(1)>count(字段)≈count(主键 id)
字段无索引
count(*)≈count(1)>count(主键 id)>count(字段)
in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描
trace工具
set session optimizer_trace="enabled=on",end_markers_in_json=on;
select * from employees where name > 'a' order by position;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
0 条评论
下一页
