建站
咨詢
售后
建站咨詢
新聞中心
本篇文章為大家展示了MySQL中排序速度慢如何解決，內(nèi)容簡(jiǎn)明扼要并且容易理解，絕對(duì)能使你眼前一亮，通過這篇文章的詳細(xì)介紹希望你能有所收獲。
創(chuàng)新互聯(lián)主營(yíng)新邱網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營(yíng)網(wǎng)站建設(shè)方案,成都App制作,新邱h5小程序制作搭建,新邱網(wǎng)站營(yíng)銷推廣歡迎新邱等地區(qū)企業(yè)咨詢
一、具體現(xiàn)象
有一個(gè)功能，按照算法得出的權(quán)重值，分頁展示一批列表數(shù)據(jù)，權(quán)重值越大越靠前。研發(fā)同學(xué)反饋查詢速度慢且排序不穩(wěn)定。
排序不穩(wěn)定的具體現(xiàn)象，有不少記錄存在相同權(quán)重值，某條記錄（假設(shè)id=100）第一頁出現(xiàn)了，翻到第二頁可能還有它（采用的limit控制哪一頁）。
第1頁數(shù)據(jù)
第2頁數(shù)據(jù)
一個(gè)主表A，左連接兩個(gè)表B、C，根據(jù)C的權(quán)重字段排序。具體SQL如下
二、問題分析
查看SQL語句的執(zhí)行計(jì)劃（EXPLAIN），發(fā)現(xiàn)有Using filesort的字樣。
趕緊搜索一下MySQL說明文檔，第一條是排序優(yōu)化
文檔中有這么一句話“如果索引不能滿足ORDERBY子句，MySQL將執(zhí)行文件排序（filesort）操作，讀取數(shù)據(jù)行并對(duì)其進(jìn)行排序。文件排序構(gòu)成查詢執(zhí)行中的額外排序階段。”
顯然，利用索引實(shí)現(xiàn)有序，比采用filesort更高效。filesort并不一定都通過磁盤排序，數(shù)據(jù)量不大的時(shí)候是在內(nèi)存里完成。速度不夠快的原因找到了。
filesort的時(shí)候可能在內(nèi)存中出現(xiàn)堆排序列或快速排序兩種方式，具體使用哪一種排序方式是優(yōu)化器決定的，基本原則如下
快速排序算法：大量排序
堆排序算法：排序量不大
快速排序和堆排序是不穩(wěn)定的排序算法，對(duì)于重復(fù)值不能保證順序。Order by排序不穩(wěn)定的原因也定位到了
了解一下filesort的原理
（1）根據(jù)表的索引或者全表掃描，讀取所有滿足條件的記錄。
（2）對(duì)于每一行，存儲(chǔ)一對(duì)值到緩沖區(qū)（排序列，行記錄指針），一個(gè)是排序的索引列的值，即order by用到的列值，和指向該行數(shù)據(jù)的行指針，緩沖區(qū)的大小為sort_buffer_size大小。
（3）當(dāng)緩沖區(qū)滿后，運(yùn)行一個(gè)快速排序（qsort）來將緩沖區(qū)中數(shù)據(jù)排序，并將排序完的數(shù)據(jù)存儲(chǔ)到一個(gè)臨時(shí)文件，并保存一個(gè)存儲(chǔ)塊的指針，當(dāng)然如果緩沖區(qū)不滿，則不會(huì)重建臨時(shí)文件了。
（4）重復(fù)以上步驟，直到將所有行讀完，并建立相應(yīng)的有序的臨時(shí)文件。
（5）對(duì)塊級(jí)進(jìn)行排序，這個(gè)類似歸并排序算法，只通過兩個(gè)臨時(shí)文件的指針來不斷交換數(shù)據(jù)，最終達(dá)到兩個(gè)文件，都是有序的。
（6）重復(fù)5直到所有的數(shù)據(jù)都排序完畢。
（7）采取順序讀的方式，將每行數(shù)據(jù)讀入內(nèi)存（這里讀取數(shù)據(jù)時(shí)并不是一行一行讀），并取出數(shù)據(jù)傳到客戶端，讀取緩存大小由read_rnd_buffer_size來指定。
三、怎么優(yōu)化
1、利用索引達(dá)到排序目的（針對(duì)例子的優(yōu)化）
針對(duì)文章開始的例子，優(yōu)化原則是Use of Indexes to Satisfy ORDER BY（讓ORDER BY用上索引），即提升查詢效率，又保證穩(wěn)定性（索引B+樹葉子結(jié)點(diǎn)的順序是唯一且一定的）
MySQL的文檔列出若干具體的case，把最主要整理出來如下。
MySQL文檔中有這么一句話 “該查詢連接了許多表，并且ORDER BY中的列并非全部來自用于檢索行的第一個(gè)非恒定表。”，滿足這類型的SQL也不能利用索引排序。這就是文章開頭的例子。另外，使用別名，如果跟表的列名沖突可能導(dǎo)致索引排序失效。
看到有些文章寫到下面這條語句ORDER BY不能利用索引
這個(gè)說法顯然與MySQL官方文檔不一致。我覺得，這個(gè)語句能不能使用索引，跟數(shù)據(jù)庫引擎根據(jù)開銷決定是否檢索的階段使用索引有關(guān)。
2、優(yōu)化filesort
如果確實(shí)沒辦法利用索引，可以想辦法優(yōu)化filesort排序。
如果結(jié)果集太大內(nèi)存裝不下，filesort將根據(jù)需要使用臨時(shí)磁盤文件。磁盤io速度你懂的！MySQL官方建議可以調(diào)大排序緩存參數(shù)sort_buffer_size，MySQL 8.0還對(duì)緩存利用率做了優(yōu)化，調(diào)大一點(diǎn)也不浪費(fèi)。以前版本的MySQL可以求助DBA。
可以這樣優(yōu)化的典型SQL 語句如下
上述內(nèi)容就是MySQL中排序速度慢如何解決，你們學(xué)到知識(shí)或技能了嗎？如果還想學(xué)到更多技能或者豐富自己的知識(shí)儲(chǔ)備，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。
網(wǎng)站名稱：MySQL中排序速度慢如何解決
文章起源：http://fisionsoft.com.cn/article/psosos.html
