Categories
程式開發

MySQL-技術專題-性能優化—索引篇


前言

今天我們來講講如何優化MySQL的性能,主要從索引方面優化。下期文章講講MySQL慢查詢日誌,我們是依據慢查詢日誌來判斷哪條SQL語句有問題,然後在進行優化,敬請期待MySQL慢查詢日誌篇

建表

MySQL-技術專題-性能優化—索引篇 1

優化一:全部用到索引

介紹

建立的複合索引包含了幾個字段,查詢的時候最好能全部用到,而且嚴格按照索引順序,這樣查詢效率是最高的。 (最理想情況,具體情況具體分析)

SQL 案例

MySQL-技術專題-性能優化—索引篇 2

優化二:最左前綴法則

介紹

如果建立的是複合索引,索引的順序要按照建立時的順序,即從左到右,如:a->b->c(和B+樹的數據結構有關)

無效索引舉例

a->c:a 有效,c 無效b->c:b、c 都無效c:c 無效

SQL 案例

MySQL-技術專題-性能優化—索引篇 3

優化三:不要對索引做下處理

以下用法會導致索引失效

計算,如:+、-、*、/、!=、、is null、is not null、or函數,如:sum()、round()等等手動/自動類型轉換,如:id = “1” ,本來是數字,給寫成字符串了

SQL 案例

MySQL-技術專題-性能優化—索引篇 4

優化四:索引不要放在範圍查詢右邊

舉例

比如復合索引:a->b->c,當where a=”” and b>10 and 3=””,這時候只能用到a 和b,c 用不到索引,因為在範圍之后索引都失效(和B+樹結構有關)

SQL 案例

MySQL-技術專題-性能優化—索引篇 5

優化五:減少select * 的使用

使用覆蓋索引

即:select 查詢字段和where 中使用的索引字段一致。

SQL 案例

MySQL-技術專題-性能優化—索引篇 6

優化六:like 模糊搜索

失效情況

like “%張三%”like “%張三”

解決方案

使用複合索引,即like 字段是select 的查詢字段,如:select name from table where name like “%張三%”使用like “張三%”

SQL 案例

優化七:order by 優化

當查詢語句中使用order by 進行排序時,如果沒有使用索引進行排序,會出現filesort 文件內排序,這種情況在數據量大或者並發高的時候,會有性能問題,需要優化。

filesort 出現的情況舉例

order by 字段不是索引字段order by 字段是索引字段,但是select 中沒有使用覆蓋索引,如:select * from staffs order by age asc;order by 中同時存在ASC 升序排序和DESC 降序排序,如:select a, b from staffs order by a desc, b asc;order by 多個字段排序時,不是按照索引順序進行order by,即不是按照最左前綴法則,如:select a, b from staffs order by b asc, a asc ;

索引層面解決方法

使用主鍵索引排序按照最左前綴法則,並且使用覆蓋索引排序,多個字段排序時,保持排序方向一致在SQL 語句中強制指定使用某索引,force index(索引名字)不在數據庫中排序,在代碼層面排序

order by 排序算法

雙路排序Mysql4.1 之前是使用雙路排序,字面的意思就是兩次掃描磁盤,最終得到數據,讀取行指針和ORDER BY 列,對他們進行排序,然後掃描已經排好序的列表,按照列表中的值重新從列表中讀取對數據輸出。也就是從磁盤讀取排序字段,在buffer 進行排序,再從磁盤讀取其他字段。

文件的磁盤IO 非常耗時的,所以在Mysql4.1 之後,出現了第二種算法,就是單路排序。

單路排序從磁盤讀取查詢需要的所有列,按照orderby 列在buffer 對它們進行排序,然後掃描排序後的列表進行輸出, 它的效率更快一些,避免了第二次讀取數據,並且把隨機IO 變成順序IO,但是它會使用更多的空間, 因為它把每一行都保存在內存中了。

當我們無可避免要使用排序時,索引層面沒法在優化的時候又該怎麼辦呢?盡可能讓MySQL 選擇使用第二種單路算法來進行排序。這樣可以減少大量的隨機IO 操作,很大幅度地提高排序工作的效率。下面看看單路排序優化需要注意的點

單路排序優化點

增大max_length_for_sort_data去掉不必要的返回字段,避免select *增大sort_buffer_size 參數設置

但是sort_buffer_size 不是越大越好:

Sort_Buffer_Size 是一個connection 級參數,在每個connection 第一次需要使用這個buffer 的時候,一次性分配設置的內存。 Sort_Buffer_Size 並不是越大越好,由於是connection 級的參數,過大的設置和高並發可能會耗盡系統內存資源。據說Sort_Buffer_Size 超過2M 的時候,就會使用mmap() 而不是malloc() 來進行內存分配,導致效率降低。

優化八:group by

其原理也是先排序後分組,其優化方式可參考order by。 where高於having,能寫在where限定的條件就不要去having限定了。