Mysql分頁&關(guān)聯(lián)查詢優(yōu)化
優(yōu)化關(guān)聯(lián)查詢
這個話題基本上整本書都在討論，這里需要特別提到的是：
- 確保ON或者USING子句中的列上有索引。在創(chuàng)建索引的時候就要考慮到關(guān)聯(lián)的順序。
- 當(dāng)表A和表B用列c關(guān)聯(lián)的時候，如果優(yōu)化器的關(guān)聯(lián)順序是B、A，那么就不需要在B表的對應(yīng)列上建上索引。沒有用到的索引只會帶來額外的負(fù)擔(dān)。一般來說，除非有其他理由，否則只需要在關(guān)聯(lián)順序中的第二個表的相應(yīng)列上創(chuàng)建索引。
- 確保任何的GROUP BY和ORDER BY中的表達(dá)式只涉及到一個表中的列，這樣MySQL才有可能使用索引來優(yōu)化這個過程。
當(dāng)升級MySQL的時候需要注意：關(guān)聯(lián)語法、運(yùn)算符優(yōu)先級等其他可能會發(fā)生變化的地方。因為以前是普通關(guān)聯(lián)的地方可能會變成笛卡兒積，不同類型的關(guān)聯(lián)可能會生成不同的結(jié)果
優(yōu)化LIMIT分頁
在系統(tǒng)中需要進(jìn)行分頁操作的時候，我們通常會使用LIMIT加上偏移量的辦法實現(xiàn)，同時加上合適的ORDER BY子句。如果有對應(yīng)的索引，通常效率會不錯，否則，MySQL需要做大量的文件排序操作。
一個非常常見又令人頭疼的問題就是，在偏移量非常大的時候注”，例如可能是LIMIT1000,20這樣的查詢，這時MySQL需要查詢1 0 020條記錄然后只返回***20條，前面10 000條記錄都將被拋棄，這樣的代價非常高。如果所有的頁面被訪問的頻率都相同，那么這樣的查詢平均需要訪問半個表的數(shù)據(jù)。要優(yōu)化這種查詢，要么是在頁面中限制分
頁的數(shù)量，要么是優(yōu)化大偏移量的性能。
優(yōu)化此類分頁查詢的一個最簡單的辦法就是盡可能地使用索引覆蓋掃描，而不是查詢所有的列。然后根據(jù)需要做一次關(guān)聯(lián)操作再返回所需的列。對于偏移量很大的時候，這樣做的效率會提升非常大?？紤]下面的查詢：
- mysql> SELECT film_id,description FROM sakila.film ORDER BY title LIHIT 50,5;
如果這個表非常大，那么這個查詢***改寫成下面的樣子：
- mysql> SELECT film.film_id,Film.description
- -> FROM sakila.film
- ->INNER JOIN(
- -> SELECT film.film_id FROM sakila.film
- -> ORDER BY title LIMIT 50,5
- ->) AS lim USING(film_id);
這里的“延遲關(guān)聯(lián)”將大大提升查詢效率，它讓MySQL掃描盡可能少的頁面，獲取需要訪問的記錄后再根據(jù)關(guān)聯(lián)列回原表查詢需要的所有列。這個技術(shù)也可以用于優(yōu)化關(guān)聯(lián)查詢中的LIMIT子句。
有時候也可以將LIMIT查詢轉(zhuǎn)換為已知位置的查詢，讓MySQL通過范圍掃描獲得到對應(yīng)的結(jié)果。例如，如果在一個位置列上有索引，并且預(yù)先計算出了邊界值，上面的查詢就可以改寫為：
- mysql> SELECT film_id, description FROM sakila.Film
- -> WHERE position BETWEEN so AND 54 0RDER BY position;
對數(shù)據(jù)進(jìn)行排名的問題也與此類似，但往往還會同時和GROUP BY混合使用。在這種情況下通常都需要預(yù)先計算并存儲排名信息。
LIMIT和OFFST的問題，其實是OFFSET的問題.它會導(dǎo)致MySQL掃描大量不需要的行然后再拋棄掉。如果可以使用書簽記錄上次取數(shù)據(jù)的位置，那么下次就可以直接從該書簽記錄的位置開始掃描，這樣就可以避免使用OFFSET。例如，若需要按照租借記錄做翻頁，那么可以根據(jù)***一條租借記錄向后追溯，這種做法可行是因為租借記錄的主鍵是單調(diào)增長的。首先使用下面的查詢獲得***組結(jié)果：
- mysql> SELECT * FROM sakila.rental
- -> ORDER BY rental id DESC LIMIT 20;
假設(shè)上面的查詢返回的是主鍵為1 6 049到1 6 03 0的租借記錄，那么下一頁查詢就可以從1 6 030這個點開始：
- mysql> SELECT * FROM sakila*rental
- -> WHERE rental id < 16030,
- -> ORDER BY rental id DESC LIMIT 20;
該技術(shù)的好處是無論翻頁到多么后面，其性能都會很好。
其他優(yōu)化辦法還包括使用預(yù)先計算的匯總表，或者關(guān)聯(lián)到一個冗余表，冗余表只包含主鍵列和需要做排序的數(shù)據(jù)列。還可以使用Sphinx優(yōu)化一些搜索操作，參考附錄F可以獲得更多相關(guān)信息。
