MySQL 聯(lián)合索引與Where子句的優(yōu)化 提高數(shù)據(jù)庫運(yùn)行效率
更新時(shí)間:2012年01月30日 16:41:57 作者:
網(wǎng)站系統(tǒng)上線至今,數(shù)據(jù)量已經(jīng)不知不覺上到500M,近8W記錄了。涉及數(shù)據(jù)庫操作的基本都是變得很慢了,這篇文章主要是說明配置并不是數(shù)據(jù)庫操作慢的主要原因
網(wǎng)站系統(tǒng)上線至今,數(shù)據(jù)量已經(jīng)不知不覺上到500M,近8W記錄了。涉及數(shù)據(jù)庫操作的基本都是變得很慢了,用的人都會覺得躁火~~然后把這個(gè)情況在群里一貼,包括機(jī)器配置什么的一說,馬上就有群友發(fā)話了,而且?guī)臀掖_定了不是機(jī)器配置的問題,“深圳-槍手”熱心人他的機(jī)器512內(nèi)存過百W的數(shù)據(jù)里也跑得飛快,甚至跟那些幾W塊的機(jī)器一樣牛(吹過頭了),呵呵~~~
在群友的分析指點(diǎn)下,嘗試把排序、條件等一個(gè)一個(gè)去除來做測試,結(jié)果發(fā)現(xiàn)問題就出在排序部分,去除排序的時(shí)候,執(zhí)行時(shí)間由原來的48秒變成0.3x秒,這是個(gè)什么檔次的變化呀~~看著這個(gè)結(jié)果我激動ing.....
于是我把涉及排序的字段組成一個(gè)聯(lián)合索引alter table xx add index indexname(x1,x2,x3),經(jīng)過2分鐘創(chuàng)建新索引之后再執(zhí)行同一個(gè)SQL語句,哇塞0.28S。。。。爽
于是按照同樣的思路把其它幾個(gè)常用的SQL作了過些優(yōu)化,效果馬上見效
過了30分鐘再查slow sql記錄文件,不好了,發(fā)現(xiàn)原來一個(gè)好好的SQL變得灰常慢了,神馬情況?
幾經(jīng)分析和測試原來就是因?yàn)樘砑恿寺?lián)合索引的原因,而且這個(gè)SQL語句當(dāng)中有個(gè)or,當(dāng)把這個(gè)or改用union之后問題排除。
這回又得出一個(gè)心得:寫SQL的時(shí)候千萬別一時(shí)就手,隨便寫個(gè)就OK,那會為以為帶來很嚴(yán)重的后果。
再附上一段關(guān)于Where子句的執(zhí)行順序:
在用MySQL查詢數(shù)據(jù)庫的時(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)
這樣查詢需要20多秒,雖然在各個(gè)字段上都建立了索引。用分析Explain SQL一分析,發(fā)現(xià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條,接下來還要根據(jù)其它的條件來過濾,自然在速度上有了較大的提升。
經(jīng)過實(shí)踐發(fā)現(xiàn),不要以為WHERE中的字段順序無所謂,可以隨便放在哪,應(yīng)該盡可能地第一次就過濾掉大部分無用的數(shù)據(jù),只返回最小范圍的數(shù)據(jù)。
希望能幫到有同樣遭遇的朋友。
在群友的分析指點(diǎn)下,嘗試把排序、條件等一個(gè)一個(gè)去除來做測試,結(jié)果發(fā)現(xiàn)問題就出在排序部分,去除排序的時(shí)候,執(zhí)行時(shí)間由原來的48秒變成0.3x秒,這是個(gè)什么檔次的變化呀~~看著這個(gè)結(jié)果我激動ing.....
于是我把涉及排序的字段組成一個(gè)聯(lián)合索引alter table xx add index indexname(x1,x2,x3),經(jīng)過2分鐘創(chuàng)建新索引之后再執(zhí)行同一個(gè)SQL語句,哇塞0.28S。。。。爽
于是按照同樣的思路把其它幾個(gè)常用的SQL作了過些優(yōu)化,效果馬上見效
過了30分鐘再查slow sql記錄文件,不好了,發(fā)現(xiàn)原來一個(gè)好好的SQL變得灰常慢了,神馬情況?
幾經(jīng)分析和測試原來就是因?yàn)樘砑恿寺?lián)合索引的原因,而且這個(gè)SQL語句當(dāng)中有個(gè)or,當(dāng)把這個(gè)or改用union之后問題排除。
這回又得出一個(gè)心得:寫SQL的時(shí)候千萬別一時(shí)就手,隨便寫個(gè)就OK,那會為以為帶來很嚴(yán)重的后果。
再附上一段關(guān)于Where子句的執(zhí)行順序:
在用MySQL查詢數(shù)據(jù)庫的時(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)
這樣查詢需要20多秒,雖然在各個(gè)字段上都建立了索引。用分析Explain SQL一分析,發(fā)現(xià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條,接下來還要根據(jù)其它的條件來過濾,自然在速度上有了較大的提升。
經(jīng)過實(shí)踐發(fā)現(xiàn),不要以為WHERE中的字段順序無所謂,可以隨便放在哪,應(yīng)該盡可能地第一次就過濾掉大部分無用的數(shù)據(jù),只返回最小范圍的數(shù)據(jù)。
希望能幫到有同樣遭遇的朋友。
相關(guān)文章
從數(shù)據(jù)庫中取出最近三十天的數(shù)據(jù)并生成柱狀圖
從數(shù)據(jù)庫中取出最近三十天的數(shù)據(jù)并生成柱狀圖的代碼,需要的朋友可以參考下。2011-05-05mssql2008 自定義表類型實(shí)現(xiàn)(批量插入或者修改)
在做大型網(wǎng)站或者系統(tǒng)的時(shí)候,經(jīng)常會遇到個(gè)問題就是批量插入或者修改數(shù)據(jù)庫;今天這邊不講SqlBulkCopy,只簡單講sql自定義表類型,感興趣的朋友可以了解下哦,希望本文對你有所幫助2013-01-01mysqld-nt: Out of memory (Needed 1677720 bytes)解決方法
這篇文章主要介紹了mysqld-nt: Out of memory (Needed 1677720 bytes)解決方法,需要的朋友可以參考下2014-12-12mysql正確安全清空在線慢查詢?nèi)罩緎low log的流程分享
這篇文章主要介紹了正確安全清空在線慢查詢?nèi)罩緎low log的流程,需要的朋友可以參考下2014-02-02MYSQL必知必會讀書筆記第五章之排序檢索數(shù)據(jù)
本文給大家分享mysql必會必知讀書筆記第五章之排序檢索數(shù)據(jù),小編認(rèn)為非常具有參考價(jià)值,特此分享到腳本之家平臺供大家參考2016-05-05關(guān)于MySQL中savepoint語句使用時(shí)所出現(xiàn)的錯(cuò)誤
這篇文章主要介紹了關(guān)于MySQL中savepoint語句使用時(shí)所出現(xiàn)的錯(cuò)誤,字符串出現(xiàn)e時(shí)所產(chǎn)生的問題也被作為MySQL的bug進(jìn)行過提交,需要的朋友可以參考下2015-05-05隨機(jī)生成八位優(yōu)惠碼并保存至Mysql數(shù)據(jù)庫
這篇文章主要介紹了隨機(jī)生成八位優(yōu)惠碼并保存至Mysql數(shù)據(jù)庫的相關(guān)資料,非常不錯(cuò),具有參考借鑒價(jià)值,需要的朋友可以參考下2018-02-02