网站建设域名什么意思,玉山网站制作,珠海做网站哪间好,wordpress搭建实时聊天网站书接上回
上次发了几张图#xff0c;给了几个MySQL Explain的场景#xff0c;链接在这儿#xff1a;你是不是MySQL老司机#xff1f;来看看这些explain结果你能解释吗#xff1f;MySQL 夺命6连问
我们依次来分析下这6个问题。
在分析之前#xff0c;我们先来了解一下M…书接上回
上次发了几张图给了几个MySQL Explain的场景链接在这儿你是不是MySQL老司机来看看这些explain结果你能解释吗MySQL 夺命6连问
我们依次来分析下这6个问题。
在分析之前我们先来了解一下MySQL中的排序。 MySQL 排序主要有以下几种方式
文件排序Filesort索引排序(Index Order)
文件排序(Filesort)
文件排序还是比较有歧义的。字面意思就是使用文件排序但是实际又不是只使用文件根据具体情况而定也就是文件排序不一定涉及磁盘文件的读写。
在使用文件排序算法时MySQL会先尝试是不是可以在排序缓冲区Sort Buffer中排序如果内存放不下要排序的数据集MySQL才会选择使用磁盘临时文件。
这里有两个关键点需要注意
先尝试排序缓冲区Sort Buffer排序缓冲区大小不够才使用文件使用文件是使用临时文件
我们只是MySQL有一个配置是Sort_buffer_size用来配置一个连接的排序缓冲区MySQL在排序时使用这个缓冲区。 另外一点就是临时文件我们要知道临时文件不一定涉及文件读写因为临时文件可能是在内存中也可能是在硬盘上。这个需要你根据实际情况来决定使用硬盘还是内存。如果使用基于内存的临时文件系统那么在Linux临时文件系统中向临时文件写数据会先写入内存如果内存空间不足那么才会将内存中的数据交换到磁盘。 看看kernel.org对临时文件系统的解释 如果你使用硬盘的文件系统比如xfs,ext4等速度虽然不如tmpfs但是更稳定。
怎么查询MySQL 临时文件配置
show variables like tmpdir
COPY 我们看到值是/tmp然后在使用df -h /tmp来查看是什么文件系统: 总结思考
以上两点让我们可以做一些优化
如果涉及排序可以把sort_buffer_size设置大点如果使用Tmpfs就把MySQL物理内存配置的大点如果使用xfs/ext4就用高速SSD硬盘
文件排序流程
当然以下是结构化的过程描述 MySQL根据Where条件匹配行 然后在Sort Buffer中存一下排序所需要的列值排序键值行指针以及查询所需的列 Sort Buffer满的时候使用快速排序算法进行排序然后将排序好的数据写入临时文件中同时还得记录一下这个文件一般是记录文件描述符就是一个int值在Linux系统中 对上面的步骤循环直到所有扫描完所有Where条件匹配到的行 到这儿的时候MySQL可能已经得到了非常多的临时文件MySQL中交Chunk 然后使用归并排序并和到一个结果文件 然后读取这个文件返回结果集
那么在使用Filesort进行排序是MySQL使用快速排序对Sort buffer中的数据进行排序然后使用归并排序对临时文件进行排序。 快排 归并排 索引排序(Index Order)
索引排序就比较简单了就是如果查询可以使用索引那么MySQL就使用扫描索引进行排序。如果是正序Explain的Extra列会显示空如果是倒叙那么Explain就显示backword index scan。
索引的组织结构就是B树天然有序。
Backward index scan是MySQL 8.0提供的优化特性。
几个例子
我们有一张trade_user表表结构数据行数如下 表结构 数据行 使用文件排序
我们现在来使用name字段进行排序
explain select * from trade_user order by name asc limit 10;
COPY
Type列为ALLExtra列为Using filesort。 这表示对trade_order表进行全表扫描排序使用文件排序算法。 explain结果如下 使用索引排序
现在我们修改一下表结构对name字段增加索引
alter table trade_user add index idx_name (name);
show indexes from trade_user;
COPY
我们可以看到idx_name索引是visible的这是MySQL 8.0的新特性索引是否对优化器可见。 我们来执行刚才的SQL:
explain select * from trade_user order by name asc limit 10;
COPY
这次我们执行会看到: Type列为Index, Extra列为NULL。 这个就是使用了索引进行排序。
现在我们修改一下SQL把asc修改为desc。
explain select * from trade_user order by name desc limit 10;
COPY
我们看到输出和刚才的差别是Extra列显示Backward index scan这就是使用了MySQL 8.0的反向索引扫描 文件排序和索引排序的差别
文件排序
我们先把idx_name设置为不可见来分析下执行过程:
alter table trade_user alter index idx_name invisible;-- 这里我们添加一个analyze
explain analyze select * from trade_user order by name desc limit 10;
COPY
输出如下 我们可以在输出中看到
Table scan on trade_user (cost52799 rows521335) (actual time0.0305..236 rows524991 loops1)
COPY
这个SQL需要进行全表扫描开销很大数据行越多开销越大。
索引排序
我们先把idx_name设置为可见来分析下执行过程:
alter table trade_user alter index idx_name visible;-- 这里我们添加一个analyze
explain analyze select * from trade_user order by name desc limit 10;
COPY
输出如下 我们可以在输出中看到
Index scan on trade_user using idx_name (reverse) (cost0.0128 rows10) (actual time1.76..1.79 rows10 loops1)
COPY
索引排序的开销就很小。
analyze怎么看
倒序看我们看下我们刚才的两个输出怎么看:
-- 文件排序
3 - Limit: 10 row(s) (cost52799 rows10) (actual time281..281 rows10 loops1)
2 - Sort row IDs: trade_user.name DESC, limit input to 10 row(s) per chunk (cost52799 rows521335) (actual time281..281 rows10 loops1)
1 - Table scan on trade_user (cost52799 rows521335) (actual time0.0382..221 rows524991 loops1)-- 索引排序1 - Limit: 10 row(s) (cost0.0128 rows10) (actual time1.76..1.79 rows10 loops1)
2 - Index scan on trade_user using idx_name (reverse) (cost0.0128 rows10) (actual time1.76..1
