楚雄做网站,四年级的简短新闻播报,wordpress黑糖主题破解,四川企业网站建设平台文章目录 MySQL45讲 第十六讲 “order by”是怎么工作的#xff1f;一、引言二、全字段排序#xff08;一#xff09;索引创建与执行情况分析#xff08;二#xff09;执行流程#xff08;三#xff09;查看是否使用临时文件 三、rowid 排序#xff08;一#xff09;参… 文章目录 MySQL45讲 第十六讲 “order by”是怎么工作的一、引言二、全字段排序一索引创建与执行情况分析二执行流程三查看是否使用临时文件 三、rowid 排序一参数控制与算法改变二执行流程三全字段排序和rowid 排序性能对比 四、利用联合索引避免排序一创建联合索引二执行流程简化三覆盖索引优化 五、总结与思考 MySQL45讲 第十六讲 “order by”是怎么工作的
一、引言
在应用开发中经常需要根据指定字段排序显示结果。本文以查询城市为 “杭州” 的市民信息并按姓名排序为例深入探讨 MySQL 中 “order by” 语句的执行流程、不同算法以及相关优化策略避免在开发中出现性能问题。
例子假设你要查询城市是“杭州”的所有人名字并且按照姓名排序返回 前1000个人的姓名、年龄。
假设这个表的部分定义是这样的
CREATE TABLE t (
id int(11) NOT NULL,
city varchar(16) NOT NULL,
name varchar(16) NOT NULL,
age int(11) NOT NULL,
addr varchar(128) DEFAULT NULL,
PRIMARY KEY (id),
KEY city (city)
) ENGINEInnoDB;这时你的SQL语句可以这么写
select city,name,age from t where city杭州 order by name limit 1000;二、全字段排序
一索引创建与执行情况分析 MySQL会给每个线程分配一块内存用于排序这块内存称为sort_buffer。为避免全表扫描需在 city 字段创建索引。 使用 explain 命令查看执行情况Extra 字段中的 Using filesort 表示需要排序MySQL 会为每个线程分配 sort_buffer 内存用于排序。
二执行流程
初始化 sort_buffer确定放入 name、city、age 三个字段。从 city 索引找到满足条件的第一个主键 id。到主键 id 索引取出整行取相关字段值存入 sort_buffer。从 city 索引取下一个记录的主键 id重复 3、4 步直到不满足条件。对 sort_buffer 中的数据按 name 字段做快速排序可能在内存或使用外部排序取决于 sort_buffer_size 参数和排序数据量。取前 1000 行返回给客户端。 三查看是否使用临时文件 通过设置 optimizer_trace 为 enabledon计算执行语句前后 performance_schema.session_status 中 Innodb_rows_read 的差值并查看 OPTIMIZER_TRACE 结果中的 number_of_tmp_files可确定是否使用临时文件。若该值大于 0表示使用了外部排序MySQL 将数据分成多份排序后合并若为0表示可在内存中完成排序。 下图中的number_of_tmp_files12代表MySQL将需要排序的数据分成12份每一份单独排序后存在这些临时文件中。然后把这12个有序文件再合并成一个有序的大文件。 三、rowid 排序
在全字段排序算法过程里面只对原表的数据读了一遍剩下的操作都是在sort_buffer和临时文件中执行的。如果查询要返回的字段很多的话那么sort_buffer里面要放的字段数太多即行长度过长这样内存里能够同时放下的行数很少要分成很多个临时文件排序的性能会很差。这时候就需要使用rowid排序。
一参数控制与算法改变 SET max_length_for_sort_data 16; 当 max_length_for_sort_data 参数设置为较小值如 16且单行长度超过该值时MySQL 采用 rowid排序算法。此算法放入 sort_buffer 的字段只有要排序的列如 “name”和主键 id。
二执行流程 初始化 sort_buffer确定放入 name 和 id 字段。 从 city 索引找到满足条件的第一个主键 id。 到主键 id 索引取出整行取 name 和 id 字段存入 sort_buffer。 从 city 索引取下一个记录的主键 id重复 3、4 步直到不满足条件。 对 sort_buffer中的数据按 name 排序。 遍历排序结果取前 1000 行按 id 值回原表取出 city、name 和 age 字段返回给客户端。此算法多了一次回表操作但在单行数据较大时可在排序过程中一次排序更多行。 三全字段排序和rowid 排序性能对比
全字段排序在内存足够时优先选择可直接从内存返回结果减少磁盘访问rowid 排序在内存较小时使用虽排序时能处理更多行但需回表取数据增加磁盘读操作。 四、利用联合索引避免排序
一创建联合索引 创建 city 和 name 的联合索引如 city_user (city, name)可确保从该索引取出行时按 name 递增排序无需再进行排序操作。 无需继续创建临时表和排序使用explain指令查看Extra字段已经没有Using filesort,证明
二执行流程简化 从联合索引找到满足条件的第一个主键 id。 到主键 id 索引取整行相关字段值直接返回。 从联合索引取下一个记录主键 id重复 2 步直到满足条件结束。
三覆盖索引优化 进一步创建 city、name 和 age 的联合索引如 city_user_age (city, name, age)可利用覆盖索引直接从索引获取数据返回无需回表从主键索引取数据性能更快但需权衡索引维护代价。覆盖索引是指索引上的信息足够满足查询请求不需要回到主键索引上去取数据。 这样整个查询语句的执行流程就变成了 从索引(city,name,age)找到第一个满足city杭州’条件的记录取出其中的city、name和age 这三个字段的值作为结果集的一部分直接返回从索引(city,name,age)取下一个记录同样取出这三个字段的值作为结果集的一部分直接返回重复执行步骤2直到查到第1000条记录或者是不满足city杭州’条件时循环结束。 五、总结与思考
MySQL 中 order by 语句有多种执行算法开发人员应清楚其排序逻辑和系统资源消耗根据实际情况选择合适方案。
全字段排序可能需要使用临时表进行排序在字段过多的情况下性能可能会很差。为了减少字段过长导致的排序性能下降rowid排序算法放入 sort_buffer 的字段只有要排序的列如 “name”和主键 id。如果需要进一步提高性能可以采取联合索引乃至覆盖索引字段已排序这样就可以避免排序但是需要消耗空间存储索引和维护索引为代价。
