MySQL資料庫數據庫在使用時的註意事項

1.什麼情況下應該使用組合索引而非單獨索引呢?

假設有條件語句A=a AND B=b,如果A和B是兩個單獨的索引,在AND條件下隻有一個索引起作用,對於B則要逐個判斷,而如果使用組合索引(A, B),隻要遍歷一棵樹就可以瞭,大大增加瞭效率。

但是對於A=a OR B=b,由於是或的關系,因而組合索引是不起作用的,因而可以使用單獨索引,這個時候,兩個索引可以同時起作用。

2.如果有組合索引,還需要單列索引嗎?

如果組合索引是(A, B),則對於條件A=a,是可以用上這個組合索引的,因為組合索引是先按照第一列進行排序的,所以沒必要對於A單獨建立一個索引,但是對於B=b就用不上瞭,因為隻有在第一列相同的情況下,才比較第二列,因而第二列相同的,可以分佈在不同的節點上,沒辦法快速定位。

3.為什麼索引要有區分度,組合索引中應該講有區分度的放在前面?

如果沒有區分度,例如用性別,相當於把整個大表分成兩部分,查找數據還是需要遍歷半個表才能找到,使得索引失去瞭意義。?

4.索引是越多越好嗎?

當然不是,隻有在必要的地方添加索引,索引不但會使得插入和修改的效率降低,而且在查詢的時候,有一個查詢優化器,太多的索引會讓優化器困惑,可能沒有辦法找到正確的查詢路徑,從而選擇瞭慢的索引。

5.為什麼不要在更新頻繁的字段上建立索引

更新一個字段意味著相應的索引也要更新,更新往往意味著刪除然後再插入,索引本來是一種事先在寫的階段形成一定的數據結構,從而使得在讀的階段效率較高的方式,但是如果一個字段是寫多讀少,則不建議使用索引。

6.為什麼不要使用NOT等負向查詢條件

你可以想象一下,對於一棵B+樹,跟節點是40,如果你的條件是等於20,就去左面查,你的條件等於50,就去右面查,但是你的條件是不等於66,索引應該咋辦?還不是遍歷一遍才知道。

7.為什麼模糊查詢不要以通配符開頭

對於一棵B+樹來講,如果根是字符def,如果通配符在後面,例如abc%,則應該搜尋左面,例如efg%,則應該搜尋右面,如果通配符在前面%abc,則不知道應該走哪一面,還是都掃描一遍吧。

8.為什麼OR要改成IN,或者使用Union

OR查詢條件的優化往往比較難找到最佳的路徑,尤其是OR的條件比較多的時候,尤其如此,對於同一個字段,使用IN就好一些,資料庫會對IN裡面的條件進行排序,並統一通過二分搜尋的方法處理。對於不同的字段,使用Union,則可以讓每一個子查詢都使用索引。

9.為什麼數據類型應該盡量小,常用整型來代替字符型,長字符類型可以考慮使用前綴索引?

因為資料庫是按照頁存放的,每一頁的大小是一樣的,如果數據類型比較大,則頁數會比較多,每一頁放的數據會比較少,樹的高度會比較高,因而搜尋數據要讀取的I/O數目會比較多,插入的時候節點也容易分裂,效率會降低。使用整型來代替字符型多是這個考慮,整型對於索引有更高的效率,例如IP地址等。如果有長字符類型需要使用索引進行查詢,為瞭不要使得索引太大,可以考慮將字段的前綴進行索引,而非整個字段。

10.SQL優化思路

一、如何收集有問題的SQL語句

1)MySQL 資料庫提供瞭慢SQL日志功能,通過參數slow_query_log,獲取執行時間超過一定閾值的SQL語錄列表。

2)沒有使用索引的SQL語句,可以通過long_queries_not_using_indexes參數開啟。

3)參數min_examined_row_limit,掃描記錄數大於該值的SQL語句才會被記入慢SQL日志。

二、找到有問題的語句,接下來就是通過explainSQL,獲取SQL的執行計劃,是否通過索引掃描記錄,可以通過創建索引來優化執行效率。是否掃描記錄數過多。是否持鎖時間過長,是否存在鎖沖突。返回的記錄數是否較多。

三、定制化的優化。

1)沒有被索引覆蓋的過濾條件涉及的字段,在區分度較大的字段上創建索引,如果涉及多個字段,盡量創建聯合索引。

2)掃描記錄數非常多,返回記錄數不多,區分度較差,重新評估SQL語句涉及的字段,選擇區分度高的多個字段創建索引

3)掃描記錄數非常多,返回記錄數也非常多,過濾條件不強,增加SQL過濾條件

4)schema_redundant_indexes查看有哪些冗餘索引。?

如果多個索引涉及字段順序一致,則可以組成一個聯合索引

5)schema_unused_indexes查看哪些索引從沒有被使用。

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *