MySQL 聯(lián)合索引與Where子句的優(yōu)化 提高數(shù)據(jù)庫(kù)運(yùn)行效率
更新時(shí)間：2012年01月30日 16:41:57 作者：
網(wǎng)站系統(tǒng)上線至今，數(shù)據(jù)量已經(jīng)不知不覺(jué)上到500M，近8W記錄了。涉及數(shù)據(jù)庫(kù)操作的基本都是變得很慢了，這篇文章主要是說(shuō)明配置并不是數(shù)據(jù)庫(kù)操作慢的主要原因
網(wǎng)站系統(tǒng)上線至今，數(shù)據(jù)量已經(jīng)不知不覺(jué)上到500M，近8W記錄了。涉及數(shù)據(jù)庫(kù)操作的基本都是變得很慢了，用的人都會(huì)覺(jué)得躁火~~然后把這個(gè)情況在群里一貼，包括機(jī)器配置什么的一說(shuō)，馬上就有群友發(fā)話(huà)了，而且?guī)臀掖_定了不是機(jī)器配置的問(wèn)題，“深圳-槍手”熱心人他的機(jī)器512內(nèi)存過(guò)百W的數(shù)據(jù)里也跑得飛快，甚至跟那些幾W塊的機(jī)器一樣牛(吹過(guò)頭了)，呵呵~~~
在群友的分析指點(diǎn)下，嘗試把排序、條件等一個(gè)一個(gè)去除來(lái)做測(cè)試，結(jié)果發(fā)現(xiàn)問(wèn)題就出在排序部分，去除排序的時(shí)候，執(zhí)行時(shí)間由原來(lái)的48秒變成0.3x秒，這是個(gè)什么檔次的變化呀~~看著這個(gè)結(jié)果我激動(dòng)ing.....
于是我把涉及排序的字段組成一個(gè)聯(lián)合索引alter table xx add index indexname(x1,x2,x3)，經(jīng)過(guò)2分鐘創(chuàng)建新索引之后再執(zhí)行同一個(gè)SQL語(yǔ)句，哇塞0.28S。。。。爽
于是按照同樣的思路把其它幾個(gè)常用的SQL作了過(guò)些優(yōu)化，效果馬上見(jiàn)效
過(guò)了30分鐘再查slow sql記錄文件，不好了，發(fā)現(xiàn)原來(lái)一個(gè)好好的SQL變得灰常慢了，神馬情況?
幾經(jīng)分析和測(cè)試原來(lái)就是因?yàn)樘砑恿寺?lián)合索引的原因，而且這個(gè)SQL語(yǔ)句當(dāng)中有個(gè)or，當(dāng)把這個(gè)or改用union之后問(wèn)題排除。
這回又得出一個(gè)心得：寫(xiě)SQL的時(shí)候千萬(wàn)別一時(shí)就手，隨便寫(xiě)個(gè)就OK，那會(huì)為以為帶來(lái)很?chē)?yán)重的后果。
再附上一段關(guān)于Where子句的執(zhí)行順序：
在用MySQL查詢(xún)數(shù)據(jù)庫(kù)的時(shí)候，連接了很多個(gè)用，發(fā)現(xiàn)非常慢。例如：
SELECT ... WHERE p.languages_id = 1 AND m.languages_id = 1 AND c.languages_id = 1 AND t.languages_id = 1 AND p.products_id IN (472,474)
這樣查詢(xún)需要20多秒，雖然在各個(gè)字段上都建立了索引。用分析Explain SQL一分析，發(fā)現(xiàn)在第一次分析過(guò)程中就返回了幾萬(wàn)條數(shù)據(jù)：
WHERE p.languages_id = 1 ，然后再依次根據(jù)條件，縮小范圍。
而我改變一下WHERE 字段的位置之后，速度就有了明顯地提高：
WHERE p.products_id IN (472,474) AND p.languages_id = 1 AND m.languages_id = 1 AND c.languages_id = 1 AND t.languages_id = 1
這樣，第一次的條件是p.products_id IN (472,474)，它返回的結(jié)果只有不到10條，接下來(lái)還要根據(jù)其它的條件來(lái)過(guò)濾，自然在速度上有了較大的提升。
經(jīng)過(guò)實(shí)踐發(fā)現(xiàn)，不要以為WHERE中的字段順序無(wú)所謂，可以隨便放在哪，應(yīng)該盡可能地第一次就過(guò)濾掉大部分無(wú)用的數(shù)據(jù)，只返回最小范圍的數(shù)據(jù)。
希望能幫到有同樣遭遇的朋友。
在群友的分析指點(diǎn)下，嘗試把排序、條件等一個(gè)一個(gè)去除來(lái)做測(cè)試，結(jié)果發(fā)現(xiàn)問(wèn)題就出在排序部分，去除排序的時(shí)候，執(zhí)行時(shí)間由原來(lái)的48秒變成0.3x秒，這是個(gè)什么檔次的變化呀~~看著這個(gè)結(jié)果我激動(dòng)ing.....
于是我把涉及排序的字段組成一個(gè)聯(lián)合索引alter table xx add index indexname(x1,x2,x3)，經(jīng)過(guò)2分鐘創(chuàng)建新索引之后再執(zhí)行同一個(gè)SQL語(yǔ)句，哇塞0.28S。。。。爽
于是按照同樣的思路把其它幾個(gè)常用的SQL作了過(guò)些優(yōu)化，效果馬上見(jiàn)效
過(guò)了30分鐘再查slow sql記錄文件，不好了，發(fā)現(xiàn)原來(lái)一個(gè)好好的SQL變得灰常慢了，神馬情況?
幾經(jīng)分析和測(cè)試原來(lái)就是因?yàn)樘砑恿寺?lián)合索引的原因，而且這個(gè)SQL語(yǔ)句當(dāng)中有個(gè)or，當(dāng)把這個(gè)or改用union之后問(wèn)題排除。
這回又得出一個(gè)心得：寫(xiě)SQL的時(shí)候千萬(wàn)別一時(shí)就手，隨便寫(xiě)個(gè)就OK，那會(huì)為以為帶來(lái)很?chē)?yán)重的后果。
再附上一段關(guān)于Where子句的執(zhí)行順序：
在用MySQL查詢(xún)數(shù)據(jù)庫(kù)的時(shí)候，連接了很多個(gè)用，發(fā)現(xiàn)非常慢。例如：
SELECT ... WHERE p.languages_id = 1 AND m.languages_id = 1 AND c.languages_id = 1 AND t.languages_id = 1 AND p.products_id IN (472,474)
這樣查詢(xún)需要20多秒，雖然在各個(gè)字段上都建立了索引。用分析Explain SQL一分析，發(fā)現(xiàn)在第一次分析過(guò)程中就返回了幾萬(wàn)條數(shù)據(jù)：
WHERE p.languages_id = 1 ，然后再依次根據(jù)條件，縮小范圍。
而我改變一下WHERE 字段的位置之后，速度就有了明顯地提高：
WHERE p.products_id IN (472,474) AND p.languages_id = 1 AND m.languages_id = 1 AND c.languages_id = 1 AND t.languages_id = 1
這樣，第一次的條件是p.products_id IN (472,474)，它返回的結(jié)果只有不到10條，接下來(lái)還要根據(jù)其它的條件來(lái)過(guò)濾，自然在速度上有了較大的提升。
經(jīng)過(guò)實(shí)踐發(fā)現(xiàn)，不要以為WHERE中的字段順序無(wú)所謂，可以隨便放在哪，應(yīng)該盡可能地第一次就過(guò)濾掉大部分無(wú)用的數(shù)據(jù)，只返回最小范圍的數(shù)據(jù)。
希望能幫到有同樣遭遇的朋友。
相關(guān)文章
記一次因線上mysql優(yōu)化器誤判引起慢查詢(xún)事件
這篇文章主要介紹了記一次因線上mysql優(yōu)化器誤判引起慢查詢(xún)事件的相關(guān)資料以及最終的解決方案，分享給大家，希望能夠給大家一點(diǎn)啟發(fā)。2017-02-02
索引在我們使用MySQL數(shù)據(jù)庫(kù)時(shí)可以極大的提高查詢(xún)效率，然而，有時(shí)候因?yàn)槭褂蒙系囊恍╄Υ镁蜁?huì)導(dǎo)致索引的失效，無(wú)法達(dá)到我們使用索引的預(yù)期效果，今天介紹一種MySQL中幾種常見(jiàn)的索引失效的原因，可以在以后的工作中盡可能避免因索引失效帶來(lái)的坑。2021-06-06
這篇文章主要介紹了MySQL建表設(shè)置默認(rèn)值的取值范圍,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-10-10
從云數(shù)據(jù)遷移服務(wù)看MySQL大表抽取模式的原理解析
這篇文章主要介紹了從云數(shù)據(jù)遷移服務(wù)看MySQL大表抽取模式的原理解析,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-02-02
Mysql存儲(chǔ)過(guò)程、觸發(fā)器、事件調(diào)度器使用入門(mén)指南
存儲(chǔ)過(guò)程（Stored Procedure）是一種在數(shù)據(jù)庫(kù)中存儲(chǔ)復(fù)雜程序的數(shù)據(jù)庫(kù)對(duì)象。為了完成特定功能的SQL語(yǔ)句集，經(jīng)過(guò)編譯創(chuàng)建并保存在數(shù)據(jù)庫(kù)中，本文給大家介紹Mysql存儲(chǔ)過(guò)程、觸發(fā)器、事件調(diào)度器使用入門(mén)指南，感興趣的朋友一起看看吧2022-01-01
vscode安裝mysql相關(guān)插件的詳細(xì)步驟
這篇文章主要給大家介紹了關(guān)于vscode安裝mysql相關(guān)插件的詳細(xì)步驟,這將幫助你更高效地管理數(shù)據(jù)庫(kù)操作,文中通過(guò)圖文介紹的非常詳細(xì),需要的朋友可以參考下2024-07-07
MySQL無(wú)服務(wù)及服務(wù)無(wú)法啟動(dòng)的終極解決方案分享
又是MySQL的問(wèn)題,之前已經(jīng)遇見(jiàn)過(guò)一次本地MySQL服務(wù)無(wú)法啟動(dòng)的情況,現(xiàn)在又出現(xiàn)了,下面這篇文章主要給大家介紹了關(guān)于MySQL無(wú)服務(wù)及服務(wù)無(wú)法啟動(dòng)的終極解決方案,需要的朋友可以參考下2022-06-06
這篇文章主要介紹了Mysql如何查看表及字段信息，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-01-01
備份對(duì)于數(shù)據(jù)庫(kù)來(lái)說(shuō)是相當(dāng)重要的工作。如果數(shù)據(jù)庫(kù)在使用過(guò)程中出現(xiàn)了問(wèn)題，比如系統(tǒng)崩潰、硬件故障或錯(cuò)誤的刪除了數(shù)據(jù)。這時(shí)，如果我們進(jìn)行了數(shù)據(jù)備份，就能比較方便的使數(shù)據(jù)庫(kù)恢復(fù)工作，并使我們的數(shù)據(jù)損失到最小。下面我們來(lái)詳細(xì)探討下數(shù)據(jù)庫(kù)的備份與恢復(fù)。2016-11-11
MySQL Server 8.0.13.0 安裝教程圖文詳解
本文通過(guò)圖文并茂的形式給大家介紹了MySQL Server 8.0.13.0 安裝教程 ,非常不錯(cuò)，具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2019-04-04