快捷導(dǎo)航

MySQL是如何處理排序的(最新推薦)

更新時(shí)間：2024年05月16日 09:35:37 作者：華為云開(kāi)發(fā)者聯(lián)盟

在MySQL的查詢(xún)中常常會(huì)用到order by和group by這兩個(gè)關(guān)鍵字,它們的相同點(diǎn)是都會(huì)對(duì)字段進(jìn)行排序,那查詢(xún)語(yǔ)句中的排序是如何實(shí)現(xiàn)的呢,下面跟隨小編一起看看吧

前言

在MySQL的查詢(xún)中常常會(huì)用到order by和group by這兩個(gè)關(guān)鍵字

它們的相同點(diǎn)是都會(huì)對(duì)字段進(jìn)行排序，那查詢(xún)語(yǔ)句中的排序是如何實(shí)現(xiàn)的呢？

當(dāng)使用的查詢(xún)語(yǔ)句需要進(jìn)行排序時(shí)有兩種處理情況：

當(dāng)前記錄本來(lái)就是有序的，不需要進(jìn)行排序
當(dāng)前記錄未保持順序，需要排序

使用索引保證有序

對(duì)于第一種情況，常常是使用二級(jí)索引中索引列的有序來(lái)保證結(jié)果集有序，從而不需要進(jìn)行排序

對(duì)于表a，為a2建立二級(jí)索引，那么在二級(jí)索引上a2就是有序的

CREATE TABLE `a` (
   `a1` int(11) NOT NULL AUTO_INCREMENT,
   `a2` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
   `a3` varchar(255) DEFAULT NULL,
   PRIMARY KEY (`a1`),
   KEY `idx_a2` (`a2`)
 ) ENGINE=InnoDB AUTO_INCREMENT=76 DEFAULT CHARSET=utf8;

select * from a order by a.a2 limit 10

當(dāng)優(yōu)化器選擇使用a2索引時(shí)，a2列的記錄本身就是有序的，因此不需要再使用其他開(kāi)銷(xiāo)進(jìn)行排序

當(dāng)然，優(yōu)化器也有可能不使用a2索引（當(dāng)優(yōu)化器認(rèn)為使用a2回表開(kāi)銷(xiāo)太大時(shí)會(huì)使用全表掃描）

當(dāng)優(yōu)化器使用的索引上a2無(wú)序時(shí)，則會(huì)通過(guò)其他手段對(duì)結(jié)果進(jìn)行排序

filesort

當(dāng)執(zhí)行計(jì)劃的Extra附加信息中出現(xiàn)Using filesort時(shí)，會(huì)使用sort_buffer對(duì)結(jié)果進(jìn)行排序

sort_buffer是一塊用于排序的內(nèi)存，sort_buffer可能存放查詢(xún)需要的所有字段，也可能只存放需要排序的字段和主鍵

show variables like 'max_length_for_sort_data'

當(dāng)查詢(xún)需要的字段長(zhǎng)度小于max_length_for_sort_data時(shí)，則會(huì)將查詢(xún)需要的所有字段放入sort_buffer中，然后對(duì)需要排序的列進(jìn)行排序，最后返回結(jié)果

當(dāng)查詢(xún)需要的字段長(zhǎng)度大于max_length_for_sort_data時(shí)，只會(huì)將需要排序的字段和主鍵值放入sort_buffer中，等到排序后再去查詢(xún)聚簇索引獲取需要查詢(xún)的列（相當(dāng)于又多了一次回表）

在sort_buffer中進(jìn)行排序時(shí)，如果內(nèi)存足夠則會(huì)在內(nèi)存中進(jìn)行排序，如果內(nèi)存不夠則會(huì)使用磁盤(pán)的臨時(shí)文件來(lái)輔助排序

開(kāi)啟optimizer_trace可以查看是否使用臨時(shí)文件輔助排序

#開(kāi)啟優(yōu)化器追蹤
 SET optimizer_trace='enabled=on'; 
 ?
 #sql語(yǔ)句
 select * from student order by student_name limit 10000;
 ?
 #查看優(yōu)化器追蹤的信息
 SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G;

排序使用的算法是歸并算法，先分割成多個(gè)小文件排序再進(jìn)行合并

其中number_of_tmp_files為使用到的臨時(shí)文件數(shù)量，sort_buffer_size為sort_buffer大小