MySQL ORDER BY 性能优化详解Using filesort、联合索引、ASC/DESC 与覆盖索引MySQL ORDER BY 性能优化详解Using filesort、联合索引、ASC/DESC 与覆盖索引前言一、先说结论ORDER BY 应该怎么优化二、MySQL ORDER BY 为什么会慢三、什么是 Using filesortUsing filesort 是不是一定会使用磁盘四、如何避免 Using filesort五、案例没有索引时的 ORDER BY六、建立 age phone 联合索引七、为什么 ORDER BY age 可以使用 age, phone 联合索引八、多字段 ORDER BY 也要注意联合索引顺序九、为什么 ORDER BY phone, age 不能直接使用 (age, phone) 排序十、ORDER BY 与联合索引的最左原则十一、两个字段全部 DESC还能使用 ASC 索引吗十二、为什么 ASC DESC 混合排序可能出现 filesort十三、如何优化 ASC DESC 混合排序十四、四种 ORDER BY 与索引的关系十五、一个非常重要的技术辨析Using index ≠ ORDER BY 一定走索引排序1. Using index 的准确含义2. 判断 ORDER BY 是否额外排序看什么没有 Using filesort出现 Using filesort十六、为什么 ORDER BY 优化建议尽量使用覆盖索引十七、ORDER BY 索引优化思维图十八、如果 Using filesort 无法避免怎么办十九、sort_buffer_size 是什么二十、sort_buffer_size 是不是越大越好二十一、完整案例总结场景 1没有索引场景 2建立联合索引场景 3全部降序场景 4调换字段顺序场景 5一升一降场景 6建立混合方向索引二十二、ORDER BY 性能优化的正确排查步骤第一步使用 EXPLAIN第二步检查排序字段第三步检查联合索引字段顺序第四步检查 ASC / DESC第五步检查能否使用覆盖索引第六步最后再考虑 sort_buffer_size二十三、ORDER BY 优化的 4 条核心原则1. 根据排序字段建立合适索引2. 多字段排序注意索引字段顺序3. 尽量使用覆盖索引4. ASC / DESC 混合时注意索引方向二十四、FAQMySQL ORDER BY 常见问题1. MySQL ORDER BY 如何优化2. Using filesort 是什么意思3. Using filesort 一定很慢吗4. Using index 是什么意思5. Using index; Using filesort 能同时出现吗6. ORDER BY age DESC, phone DESC 能走 (age, phone) 索引吗7. 为什么 ORDER BY age ASC, phone DESC 可能不能使用普通联合索引排序8. ORDER BY phone, age 能使用 (age, phone) 索引吗9. sort_buffer_size 越大越好吗二十五、一张表总结 MySQL ORDER BY 优化二十六、30 秒记住 ORDER BY 优化二十七、总结MySQL ORDER BY 性能优化详解Using filesort、联合索引、ASC/DESC 与覆盖索引前言在 MySQL 中ORDER BY是非常常见的排序操作。例如SELECTid,age,phoneFROMtb_userORDERBYage,phone;SQL 本身很简单但当数据量越来越大时排序可能成为查询性能瓶颈。MySQL 对ORDER BY的处理大体可以分为两类利用索引本身的有序性返回结果无法利用索引顺序需要额外执行 filesort 排序因此优化ORDER BY的核心目标可以概括为一句话尽可能让 MySQL 利用索引本身的顺序完成排序减少额外的 filesort。本文围绕 MySQLORDER BY优化展开重点讲清楚Using filesort到底是什么意思Using index又是什么意思为什么联合索引可以优化排序为什么ORDER BY age, phone可以走索引而ORDER BY phone, age可能不行为什么两个字段同时倒序仍然可以利用索引为什么一升一降可能再次出现Using filesort如何创建ASC / DESC混合索引覆盖索引为什么有利于排序性能无法避免 filesort 时还能怎么优化一、先说结论ORDER BY 应该怎么优化如果只记本文最重要的内容可以记住下面 4 条MySQL ORDER BY 优化原则根据排序字段建立合适的索引多字段排序时注意联合索引的字段顺序ASC、DESC混合排序时让索引方向与查询排序方向匹配无法避免filesort时再考虑sort_buffer_size等排序参数。另外查询字段能够被索引覆盖时通常还可以减少回表因此实际优化中也应尽量考虑覆盖索引。二、MySQL ORDER BY 为什么会慢假设执行SELECTid,age,phoneFROMtb_userORDERBYage,phone;如果age、phone没有合适的索引MySQL 无法直接按照索引顺序获得最终结果。此时就需要读取符合条件的数据 ↓ 放入排序过程 ↓ 按照 age、phone 排序 ↓ 返回最终结果这会产生额外的排序成本。在EXPLAIN执行计划中通常可以通过Using filesort判断 MySQL 是否额外执行了排序。三、什么是 Using filesort例如执行EXPLAINSELECTid,age,phoneFROMtb_userORDERBYage,phone;如果当前没有适合排序的索引Extra中可能出现Using filesortUsing filesort表示 MySQL不能直接利用索引顺序得到最终排序结果需要额外执行一次排序阶段。MySQL 官方文档同样将filesort描述为无法通过索引满足ORDER BY时执行的额外排序过程。 (MySQL开发者专区)Using filesort 是不是一定会使用磁盘不是。这是一个很容易产生误解的地方。虽然名字叫filesort但它并不意味着“只要出现 Using filesort就一定会进行磁盘文件排序。”MySQL 会使用排序缓冲区处理排序当排序数据无法完全放入内存时才可能使用临时磁盘文件。 (MySQL开发者专区)因此更准确的理解是filesort 代表额外排序算法而不是“必然磁盘排序”。四、如何避免 Using filesort关键在于利用 BTree 索引本身已经有序的特点。例如我们经常按照ORDERBYage,phone进行排序那么可以考虑建立联合索引CREATEINDEXidx_user_age_phoneONtb_user(age,phone);索引本身会按照age ↓ 相同 age 下继续按照 phone维护有序结构。于是查询SELECTid,age,phoneFROMtb_userORDERBYage,phone;就有机会直接按照索引顺序扫描而不需要重新对结果进行额外排序。五、案例没有索引时的 ORDER BY首先假设age phone都没有适合当前排序的索引。执行EXPLAINSELECTid,age,phoneFROMtb_userORDERBYage;或者EXPLAINSELECTid,age,phoneFROMtb_userORDERBYage,phone;此时通常可能看到Using filesort因为 MySQL 无法借助适合的索引顺序直接返回结果。六、建立 age phone 联合索引创建联合索引CREATEINDEXidx_user_age_phone_aaONtb_user(age,phone);没有显式指定方向时可以理解为ageASC,phoneASC即(age ASC, phone ASC)然后再次执行EXPLAINSELECTid,age,phoneFROMtb_userORDERBYage,phone;此时 MySQL 就具备了利用索引顺序完成排序的条件。七、为什么 ORDER BY age 可以使用 age, phone 联合索引假设索引为(age, phone)那么 BTree 首先按照age排序在相同age下再按照phone排序。可以抽象成age │ ├── 18 │ ├── phone A │ ├── phone B │ └── phone C │ ├── 20 │ ├── phone A │ ├── phone B │ └── phone C │ └── 25 ├── phone A ├── phone B └── phone C因此ORDERBYage可以利用索引前半部分的顺序。同样ORDERBYage,phone也与索引整体顺序匹配。八、多字段 ORDER BY 也要注意联合索引顺序现在索引是(age, phone)查询SELECTid,age,phoneFROMtb_userORDERBYage,phone;排序字段顺序与索引一致索引 age → phone ORDER BY age → phone这种情况可以很好地利用索引。但是如果改成SELECTid,age,phoneFROMtb_userORDERBYphone,age;排序顺序变成phone → age而索引是age → phone两者的全局顺序并不一致。因此很可能再次出现Using filesort九、为什么 ORDER BY phone, age 不能直接使用 (age, phone) 排序这是理解联合索引排序优化的关键。对于INDEX(age, phone)索引真正保证的是先按照 age 排序 age 相同时再按照 phone 排序它并不能保证所有 phone 在整个索引范围内都是全局有序的举个例子age 18, phone 135... age 18, phone 139... age 20, phone 131... age 20, phone 138...按照整个索引看它首先满足age 有序而不是phone 有序因此ORDERBYphone,age无法简单通过(age, phone)这个索引获得全局正确的排序结果。十、ORDER BY 与联合索引的最左原则对于联合索引(age, phone)以下查询比较容易利用索引顺序ORDERBYage;以及ORDERBYage,phone;而ORDERBYphone;或者ORDERBYphone,age;通常无法直接使用该联合索引满足完整的排序顺序。因此可以总结为多字段 ORDER BY 优化时应让排序字段顺序尽可能与联合索引字段顺序匹配。十一、两个字段全部 DESC还能使用 ASC 索引吗这是本节非常重要的一点。假设索引CREATEINDEXidx_user_age_phone_aaONtb_user(age,phone);其方向可以理解为age ASC phone ASC现在执行EXPLAINSELECTid,age,phoneFROMtb_userORDERBYageDESC,phoneDESC;这种情况下MySQL 可以通过反向扫描索引获得age DESC phone DESC的结果。MySQL 8.0 官方文档也明确说明同方向多列排序时可以通过对应索引或反向扫描索引满足ORDER BY。 (MySQL开发者专区)执行计划中可能看到类似Backward index scan表示正在反向扫描索引。十二、为什么 ASC DESC 混合排序可能出现 filesort现在索引依然是(age ASC, phone ASC)执行EXPLAINSELECTid,age,phoneFROMtb_userORDERBYageASC,phoneDESC;排序要求变成age ASC phone DESC而原来的索引方向是age ASC phone ASC如果直接正向扫描age ASC √ phone ASC ×如果整体反向扫描age DESC × phone DESC √无论正向还是反向扫描都无法同时满足age ASC phone DESC因此就可能产生Using filesort十三、如何优化 ASC DESC 混合排序如果业务中大量出现ORDERBYageASC,phoneDESC可以建立与查询排序方向一致的联合索引CREATEINDEXidx_user_age_phone_adONtb_user(ageASC,phoneDESC);此时索引的组织顺序就是age ASC phone DESC再次执行EXPLAINSELECTid,age,phoneFROMtb_userORDERBYageASC,phoneDESC;MySQL 就具备通过索引顺序直接完成排序的条件。MySQL 8.0 已经支持真正的降序索引因此混合ASC/DESC的多列排序可以通过匹配方向的联合索引优化。 (MySQL开发者专区)十四、四种 ORDER BY 与索引的关系假设存在索引(age ASC, phone ASC)可以快速总结成下面这张表ORDER BY是否容易利用该索引完成排序原因age ASC, phone ASC是与索引方向一致age DESC, phone DESC是可以反向扫描age ASC, phone DESC否两列方向不一致age DESC, phone ASC否两列方向不一致如果建立(age ASC, phone DESC)那么ORDERBYageASC,phoneDESC就可以利用匹配的索引顺序。十五、一个非常重要的技术辨析Using index ≠ ORDER BY 一定走索引排序很多教程会把Using index和Using filesort简单理解成Using index 性能好 Using filesort 性能差作为入门记忆方法没有问题但严格来说还需要进一步区分。1. Using index 的准确含义EXPLAIN Extra中的Using index主要表示查询需要的列可以直接从索引中获得不需要再读取完整的数据行。也就是我们常说的覆盖索引Covering Index2. 判断 ORDER BY 是否额外排序看什么真正判断ORDER BY是否进行了额外排序更重要的是观察Using filesort没有 Using filesort意味着没有额外执行 filesort 排序出现 Using filesort意味着执行了额外排序阶段MySQL 官方EXPLAIN文档也分别定义了Using index和Using filesort前者指利用索引树取得查询所需列后者则表示需要额外排序。 (MySQL开发者专区)因此可能出现Using index; Using filesort这并不矛盾。它表示Using index ↓ 查询列可以由覆盖索引取得 Using filesort ↓ 但是 ORDER BY 仍然需要额外排序这个区别在分析真实生产 SQL 时非常重要。十六、为什么 ORDER BY 优化建议尽量使用覆盖索引例如查询SELECTid,age,phoneFROMtb_userORDERBYage,phone;如果索引能够覆盖查询需要的数据就有机会扫描索引 ↓ 得到排序结果 ↓ 直接取得需要的列从而减少额外的数据页访问。反之如果查询SELECT*FROMtb_userORDERBYage,phone;即使排序字段有索引也可能因为需要获取大量不在索引中的字段增加回表访问成本。因此ORDER BY 优化不仅需要考虑排序字段是否建立索引还应该考虑查询字段能否合理地被索引覆盖。但也不要为了覆盖所有查询而无限扩展索引因为索引越多、越宽会增加存储空间INSERT 成本UPDATE 成本DELETE 成本索引维护成本。MySQL 官方同样强调应在查询性能与索引维护成本之间取得平衡。 (MySQL开发者专区)十七、ORDER BY 索引优化思维图可以把整个判断流程记成ORDER BY │ ↓ 排序字段有没有合适索引 │ │ 否 是 │ │ ↓ ↓ Using filesort 字段顺序匹配吗 │ ┌───────┴───────┐ │ │ 否 是 │ │ ↓ ↓ Using filesort 排序方向匹配吗 │ ┌───────┴───────┐ │ │ 否 是 │ │ ↓ ↓ 创建匹配 ASC/DESC 利用索引顺序 的联合索引因此ORDER BY 索引设计实际上主要需要看三个维度字段 字段顺序 ASC / DESC 方向十八、如果 Using filesort 无法避免怎么办并不是所有排序查询都值得建立专门索引。例如排序条件组合很多 临时统计查询很多 低频查询 排序字段变化非常频繁如果为了所有ORDER BY都创建一个联合索引会产生大量冗余索引。因此某些场景下Using filesort是可以接受的。当 filesort 无法通过 SQL 和索引设计消除并且排序数据量比较大时可以进一步关注sort_buffer_size十九、sort_buffer_size 是什么sort_buffer_size是 MySQL 用于排序操作的缓冲区相关参数。可以查看SHOWVARIABLESLIKEsort_buffer_size;MySQL 8.0 官方文档给出的默认值为262144 bytes也就是256 KB如果大量排序无法通过索引优化并出现较多排序合并操作可以评估是否适当增大该参数。 ([MySQL开发者专区][6])二十、sort_buffer_size 是不是越大越好不是。不要简单地执行排序慢 ↓ 把 sort_buffer_size 调到很大因为该缓冲区与执行排序的会话相关。如果全局设置过大同时存在大量并发排序单连接排序内存 × 并发连接数量就可能产生比较大的内存压力。MySQL 官方文档也建议不要盲目把该值全局设置得很大更适合针对实际负载测试后调整。 ([MySQL开发者专区][6])因此正确优化顺序应该是① 优化 SQL ↓ ② 优化索引 ↓ ③ 减少排序数据量 ↓ ④ 最后再考虑排序参数而不是一看到 Using filesort ↓ 直接修改 sort_buffer_size二十一、完整案例总结假设tb_user包含id age phone场景 1没有索引EXPLAINSELECTid,age,phoneFROMtb_userORDERBYage,phone;可能出现Using filesort场景 2建立联合索引CREATEINDEXidx_user_age_phone_aaONtb_user(age,phone);执行EXPLAINSELECTid,age,phoneFROMtb_userORDERBYage,phone;此时具备利用索引顺序避免额外排序的条件。场景 3全部降序EXPLAINSELECTid,age,phoneFROMtb_userORDERBYageDESC,phoneDESC;可以通过反向扫描Backward index scan利用相同的联合索引顺序。场景 4调换字段顺序EXPLAINSELECTid,age,phoneFROMtb_userORDERBYphone,age;由于索引 age → phone 查询 phone → age顺序不匹配因此可能Using filesort场景 5一升一降EXPLAINSELECTid,age,phoneFROMtb_userORDERBYageASC,phoneDESC;现有索引(age ASC, phone ASC)无法完全满足排序方向因此可能Using filesort场景 6建立混合方向索引CREATEINDEXidx_user_age_phone_adONtb_user(ageASC,phoneDESC);然后EXPLAINSELECTid,age,phoneFROMtb_userORDERBYageASC,phoneDESC;即可让索引方向与查询排序方向匹配。二十二、ORDER BY 性能优化的正确排查步骤线上遇到慢排序 SQL 时可以按照以下顺序排查。第一步使用 EXPLAINEXPLAINSELECT...FROM...ORDERBY...;重点观察key rows Extra尤其是Using filesort第二步检查排序字段例如ORDERBYage,phone确认是否存在(age, phone)这样的合适索引。第三步检查联合索引字段顺序索引(age, phone)不等于(phone, age)因此不能看到字段都在索引中就认为一定能优化排序。第四步检查 ASC / DESC查询ORDERBYageASC,phoneDESC需要重点检查索引方向是否与排序要求匹配。第五步检查能否使用覆盖索引尽量避免不必要的SELECT*只查询业务真正需要的列。第六步最后再考虑 sort_buffer_size如果索引已经无法进一步优化而且确实存在大量排序再结合Sort_merge_passes 并发连接数 单次排序数据量 服务器内存评估排序缓冲区参数。二十三、ORDER BY 优化的 4 条核心原则最终可以总结成下面四条。1. 根据排序字段建立合适索引例如ORDERBYage,phone可以考虑INDEX(age,phone)2. 多字段排序注意索引字段顺序索引(age, phone)更适合ORDERBYage,phone而不是ORDERBYphone,age3. 尽量使用覆盖索引在满足业务需求的前提下只查询真正需要的字段尽量减少不必要的回表。4. ASC / DESC 混合时注意索引方向如果长期存在ORDERBYageASC,phoneDESC可以考虑INDEX(ageASC,phoneDESC)二十四、FAQMySQL ORDER BY 常见问题1. MySQL ORDER BY 如何优化最主要的方法是根据 ORDER BY 的字段、字段顺序和排序方向建立合适的联合索引让 MySQL 尽可能利用索引顺序返回数据从而避免额外的 filesort。同时可以配合覆盖索引减少回表。2. Using filesort 是什么意思Using filesort表示 MySQL 无法完全通过索引顺序满足ORDER BY需要执行额外的排序过程。它不代表一定进行了磁盘排序。3. Using filesort 一定很慢吗不一定。性能取决于排序数据量是否需要磁盘临时文件LIMIT大小内存数据分布查询并发量。但是对于高频、大数据量查询如果可以利用合适的索引消除 filesort通常值得优化。4. Using index 是什么意思Using index主要表示查询所需要的数据可以从索引树中直接获得也就是覆盖索引。它并不等价于ORDER BY 一定没有额外排序判断排序是否进行了 filesort应该重点查看Using filesort5. Using index; Using filesort 能同时出现吗可以。例如Using index; Using filesort表示查询列能够通过覆盖索引获得但是 ORDER BY 仍然需要额外排序。6. ORDER BY age DESC, phone DESC 能走 (age, phone) 索引吗可以。当索引为(age ASC, phone ASC)时可以通过反向扫描得到(age DESC, phone DESC)7. 为什么 ORDER BY age ASC, phone DESC 可能不能使用普通联合索引排序因为普通(age ASC, phone ASC)索引无法通过简单的正向或反向扫描同时满足age ASC phone DESC这种混合排序方向。可以考虑建立INDEX(ageASC,phoneDESC)8. ORDER BY phone, age 能使用 (age, phone) 索引吗通常不能直接使用该索引完成完整排序。因为(age, phone)保证的是先 age 再 phone而不是先 phone 再 age9. sort_buffer_size 越大越好吗不是。应该优先优化SQL ↓ 索引 ↓ 查询数据量确实无法避免大量 filesort 后再针对实际负载评估sort_buffer_size。二十五、一张表总结 MySQL ORDER BY 优化问题推荐方案排序字段没有索引为高频排序建立合适索引多字段排序建立联合索引字段顺序不匹配调整联合索引字段顺序全部 ASC正向扫描索引全部 DESC可反向扫描索引ASC DESC 混合建立对应方向的混合索引查询字段较少考虑覆盖索引出现Using filesort检查是否能通过索引消除filesort 无法避免再评估sort_buffer_size不确定是否优化成功使用EXPLAIN验证二十六、30 秒记住 ORDER BY 优化最后把整节内容压缩成一段MySQL ORDER BY 优化的核心是利用索引本身的有序性。 如果 ORDER BY 无法利用索引顺序 EXPLAIN 中通常会看到 Using filesort。 对于多字段排序 字段顺序要与联合索引匹配 全部 ASC 或全部 DESC 可以利用同一索引正向/反向扫描 ASC、DESC 混合时需要关注联合索引的排序方向。 同时尽量使用覆盖索引减少回表。 如果 filesort 无法通过 SQL 和索引设计避免 再考虑 sort_buffer_size 等排序相关参数。二十七、总结MySQLORDER BY优化并不是简单地给排序字段加一个索引真正需要同时考虑排序字段 联合索引顺序 ASC / DESC 方向 查询字段是否可以覆盖最关键的判断方法还是EXPLAIN重点关注Using filesort如果能够利用合适的 BTree 索引直接获得有序结果就可以避免额外排序。最终记住ORDER BY 优化的本质就是尽量把“运行时重新排序”转变为“直接读取已经有序的索引”。这也是处理 MySQL 排序性能问题时最重要的优化思路。若有转载请标明出处https://blog.csdn.net/CharlesYuangc/article/details/164186003