制作网站的公司做网站去哪里找,旅游网站设计完整代码,山东振国网站建设,合合肥网站建设前言
本章详细讲下排序#xff0c;排序在我们业务开发非常常见#xff0c;有对时间进行排序#xff0c;又对城市进行排序的。不合适的排序#xff0c;将对系统是灾难性的#xff0c;这个不是危言耸听。可能有些人会想#xff0c;对于排序mysql 是怎么实现的#xff0c;…前言
本章详细讲下排序排序在我们业务开发非常常见有对时间进行排序又对城市进行排序的。不合适的排序将对系统是灾难性的这个不是危言耸听。可能有些人会想对于排序mysql 是怎么实现的它的底层原理是怎么样的如果我加上分页排序是不是就会快一些。关于这些问题本章详细讲解。
有人经常问我mysql 优化的规则总是不假思索的说ESRE 是 equal S是sort 。可见排序有多么重要为了讲解方便我先画个思维导图。 上图标的12 是mysql 配置文件可以配置的。可以通过 show variables like max_length_for_sort_data; 可以具体的配置。从图上我们可以看到mysql 排序分为全字段排序和 rowid 。这是两大类里面又分为内存排序文件排序我将从这2大类4小类讲解。
全字段排序 由上图可以看出 Extra Using filesort 就表示了排序但此时还不能判断是文件排序还是内存排序
可以根据下面介绍的方法来确定一个排序语句是否使用了临时文件
/* 打开optimizer_trace只对本线程有效 */
SET optimizer_traceenabledon;
/* a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into a from performance_schema.session_status where variable_name Innodb_rows_read;
/* 执行语句 */
select city, name,age from t where city杭州 order by name limit 1000;
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
/* b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into b from performance_schema.session_status where variable_name Innodb_rows_read;
/* 计算Innodb_rows_read差值 */
select b-a;
### Number_of_tmp_files0 就表示文件排序没有就表示是内存排序。sort_buffer_size 越小那么 Number_of_tmp_files 就会越大文件排序用的是归并排序也就是把数据分给多个文件每个文件排序后最终合并一个文件。
上面sort_mode 可以看到这是一个全字段排序什么是全字段排序就拿上面这个sql 语句来说city ,name,age 都在文件里对name 进行排序
这个排序的内部是这么实现的 初始化 sort_buffer确定放入 name、city、age 这三个字段 从索引 city 找到第一个满足 city杭州’ 条件的主键 id 到主键 id 索引取出整行取 name、city、age 三个字段的值存入 sort_buffer 中 从索引 city 取下一个满足 city杭州’ 的主键 id 重复步骤 3、4 直到 city 的值不满足查询条件为止 对 sort_buffer 中的数据按照字段 name 做快速排序 按照排序结果取前 1000 行返回给客户端。
由此我们发现排序会对表的所有的记录进行排序然后在取出1000条
rowid
如果 排序数据的长度超过了 max_length_for_sort_data 就是 rowid排序。排序数据的长度就是指拿上面这个例子说 name、city、age 这三个字段大于 max_length_for_sort_data 就是rowid 排序。为什么会这样的呢mysql 会尽量用内存排序字段越长占用空间越大未了提高排序效率就会用rowid 排序。
rowid排序的步骤是这样的 初始化 sort_buffer确定放入两个字段即 name 和 id 从索引 city 找到第一个满足 city杭州’条件的主键 id 到主键 id 索引取出整行取 name、id 这两个字段存入 sort_buffer 中 从索引 city 取下一个记录的主键 id 重复步骤 3、4 直到不满足 city杭州’条件为止 对 sort_buffer 中的数据按照字段 name 进行排序 遍历排序结果取前 1000 行并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。
我们可以看到 rowid 会多访问一次表在mysql 看来排序的复杂度高于回表的复杂度这也是一种取舍。
综上可以看出不管是内存排序还是文件排序都是很繁琐的那么有没有对于这个问题有没有优化点了在前面我们已经讲过了索引一定是有序的如果我们对cityname 建一个联合索引就不用mysql 重新排序因为索引本身就是有序的。
就是如下所示
alter table t add index city_user(city, name);
但是上面虽然不用mysql 用文件排序但是还是要回表的那还有没有进一步的优化呢我们可以考虑用覆盖索引
如下所示
alter table t add index city_user_age(city, name, age);
这样就不用回表了用explain 来看 Extra using index
大家要综合考虑吧索引越多索引越大会影响插入的速度的。
