MySQL優(yōu)化嵌套和分頁查詢流程的實(shí)例介紹

下文主要給大家?guī)?a title="MySQL" target="_blank" href="http://www.kemok4.com/mysql/">MySQL優(yōu)化嵌套和分頁查詢流程的實(shí)例介紹，希望這些內(nèi)容能夠帶給大家實(shí)際用處，這也是我編輯MySQL優(yōu)化嵌套和分頁查詢流程的實(shí)例介紹這篇文章的主要目的。好了，廢話不多說，大家直接看下文吧。

優(yōu)化嵌套查詢

嵌套查詢（子查詢）可以使用SELECT語句來創(chuàng)建一個(gè)單列的查詢結(jié)果，然后把這個(gè)結(jié)果作為過濾條件用在另一個(gè)查詢中。嵌套查詢寫起來簡單，也容易理解。但是，有時(shí)候可以被更有效率的連接（JOIN)替代。

現(xiàn)在假如要找出從來沒有在網(wǎng)站中消費(fèi)的客戶，也就是查詢在客戶customer表中但是不在支付payment表中的客戶信息。

嵌套查詢：

explain select * from customer where customer_id not in (select customer_id from payment);

連接改寫：

explain select * from customer a left join payment b on a.customer_id = b.customer_id where b.customer_id is null;

畫外音：連接查詢效率更高的原因，是因?yàn)镸ySQL不需要在內(nèi)存中創(chuàng)建臨時(shí)表來完成這個(gè)邏輯上需要兩個(gè)步驟的查詢工作；并且Not exists表示MYSQL優(yōu)化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標(biāo)準(zhǔn)的行， 就不再搜索了。

優(yōu)化分頁查詢

在MySQL中做分頁查詢，MySQL 并不是跳過 offset 行，而是取 offset+N 行，然后返回放棄前 offset 行，返回 N 行，那當(dāng) offset 特別大的時(shí)候，效率就非常的低下。例如“l(fā)imit 1000,20”，此時(shí)MySQL排序出前1020條數(shù)據(jù)后僅僅需要第1001到1020條記錄，前1000條數(shù)據(jù)都會被拋棄，查詢和排序的代價(jià)非常高。由此可見MySQL的分頁處理并不是十分完美，需要我們在分頁SQL上做一些優(yōu)化，要么控制返回的總頁數(shù)，要么對超過特定閾值的頁數(shù)進(jìn)行 SQL 改寫。

畫外音：控制返回的總頁數(shù)并不是那么靠譜，畢竟每頁的數(shù)據(jù)量也不能過大，數(shù)據(jù)多起來之后，控制返回的總頁數(shù)就變的不現(xiàn)實(shí)了。所以還是要對超過特定閾值的頁數(shù)進(jìn)行 SQL 改寫。歡迎大家加入659270626，一起討論技術(shù)問題。

現(xiàn)在假設(shè)要對電影表film排序后取某一頁數(shù)據(jù)

explain select * from film order by title limit 50,5;

可以看到優(yōu)化器實(shí)際上做了全表掃描，處理效率不高。

第一種優(yōu)化思路

在索引上完成排序分頁的操作，最后根據(jù)主鍵關(guān)聯(lián)回表查詢所需要的其他列內(nèi)容。

畫外音：此處涉及到了SQL優(yōu)化的兩個(gè)重要概念，索引覆蓋和回表，我在前面的文章中詳細(xì)介紹過這兩個(gè)概念。通過索引覆蓋在索引上完成掃描和排序（索引有序），最后通過主鍵（InnoDB引擎索引會通過主鍵回表）回表查詢，最大限度減少回表查詢的I/O次數(shù)。

explain select * from film a inner join (select film_id from film order by title limit 50,5)b on a.film_id = b.film_id;

第二種優(yōu)化思路

把LIMIT查詢轉(zhuǎn)換成某個(gè)位置的查詢，減少分頁翻頁的壓力。

假設(shè)現(xiàn)在每頁10條數(shù)據(jù)，要取第42頁的數(shù)據(jù)。

explain select * from film order by title limit 410,10;

現(xiàn)在需要多傳一個(gè)參數(shù)，就是上一頁（第41頁）的最后一條數(shù)據(jù)的主題title，

SQL可以改寫為：

explain select * from film where title>'HOLES BRANNIGAN' order by title limit 10;

這樣就把LIMIT m,n 轉(zhuǎn)換成了LIMIT n的查詢，但是這種方案只適合在不會出現(xiàn)重復(fù)值的特定環(huán)境，否則分頁結(jié)果可能會丟失數(shù)據(jù)。

對于以上關(guān)于MySQL優(yōu)化嵌套和分頁查詢流程的實(shí)例介紹，大家是不是覺得非常有幫助。如果需要了解更多內(nèi)容，請繼續(xù)關(guān)注我們的行業(yè)資訊，相信你會喜歡上這些內(nèi)容的。