MySQL中怎么實現(xiàn)索引優(yōu)化

MySQL中怎么實現(xiàn)索引優(yōu)化，相信很多沒有經驗的人對此束手無策，為此本文總結了問題出現(xiàn)的原因和解決方法，通過這篇文章希望你能解決這個問題。

建表

// 建表  CREATE TABLE IF NOT EXISTS staffs(      id INT PRIMARY KEY AUTO_INCREMENT,      name VARCHAR(24) NOT NULL DEFAULT "" COMMENT'姓名',      age INT NOT NULL DEFAULT 0 COMMENT'年齡',      pos VARCHAR(20) NOT NULL DEFAULT "" COMMENT'職位',      add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入職事件'  ) CHARSET utf8 COMMENT'員工記錄表';  // 插入數(shù)據(jù)  INSERT INTO `test`.`staffs` (`name`, `age`, `pos`, `add_time`) VALUES ('z3', 22, 'manager', now());  INSERT INTO `test`.`staffs` (`name`, `age`, `pos`, `add_time`) VALUES ('July', 23, 'dev', now());  INSERT INTO `test`.`staffs` (`name`, `age`, `pos`, `add_time`) VALUES ('2000', 23, 'dev', now());  // 建立復合索引（即一個索引包含多個字段）  ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);

優(yōu)化一：全部用到索引

介紹

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

SQL 案例

優(yōu)化二：最左前綴法則

介紹

如果建立的是復合索引，索引的順序要按照建立時的順序，即從左到右，如：a->b->c（和 B+樹的數(shù)據(jù)結構有關）

無效索引舉例

•  a->c：a 有效，c 無效

•  b->c：b、c 都無效

•  c：c 無效

SQL 案例

優(yōu)化三：不要對索引做以下處理

以下用法會導致索引失效

•  計算，如：+、-、*、/、!=、<>、is null、is not null、or

•  函數(shù)，如：sum()、round()等等

•  手動/自動類型轉換，如：id = "1"，本來是數(shù)字，給寫成字符串了

SQL 案例

優(yōu)化四：索引不要放在范圍查詢右邊

舉例

比如復合索引：a->b->c，當 where a="" and b>10 and 3=""，這時候只能用到 a 和 b，c 用不到索引，因為在范圍之后索引都失效（和 B+樹結構有關）

SQL 案例

優(yōu)化五：減少 select * 的使用

使用覆蓋索引

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

SQL 案例

優(yōu)化六：like 模糊搜索

失效情況

•  like "%張三%"

•  like "%張三"

解決方案

•  使用復合索引，即 like 字段是 select 的查詢字段，如：select name from table where name like "%張三%"

•  使用 like "張三%"

SQL 案例

優(yōu)化七：order by 優(yōu)化

當查詢語句中使用 order by 進行排序時，如果沒有使用索引進行排序，會出現(xiàn) filesort 文件內排序，這種情況在數(shù)據(jù)量大或者并發(fā)高的時候，會有性能問題，需要優(yōu)化。

filesort 出現(xiàn)的情況舉例

•  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(索引名字)

•  不在數(shù)據(jù)庫中排序，在代碼層面排序

order by 排序算法

•  雙路排序

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

文件的磁盤 IO 非常耗時的，所以在 Mysql4.1 之后，出現(xiàn)了第二種算法，就是單路排序。

•  單路排序

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

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

單路排序優(yōu)化點

•     增大 max_length_for_sort_data

在 MySQL 中,決定使用"雙路排序"算法還是"單路排序"算法是通過參數(shù) max_length_for_ sort_data 來決定的。當所有返回字段的最大長度小于這個參數(shù)值時,MySQL 就會選擇"單路排序"算法,反之,則選擇"多路排序"算法。所以,如果有充足的內存讓 MySQL 存放須要返回的非排序字段,就可以加大這個參數(shù)的值來讓 MySQL 選擇使用"單路排序"算法。

•     去掉不必要的返回字段，避免select *

當內存不是很充裕時,不能簡單地通過強行加大上面的參數(shù)來強迫 MySQL 去使用"單路排序"算法,否則可能會造成 MySQL 不得不將數(shù)據(jù)分成很多段,然后進行排序,這樣可能會得不償失。此時就須要去掉不必要的返回字段,讓返回結果長度適應 max_length_for_sort_data 參數(shù)的限制。

•  增大 sort_buffer_size 參數(shù)設置

這個值如果過小的話,再加上你一次返回的條數(shù)過多,那么很可能就會分很多次進行排序,然后最后將每次的排序結果再串聯(lián)起來,這樣就會更慢,增大 sort_buffer_size 并不是為了讓 MySQL 選擇"單路排序"算法,而是為了讓 MySQL 盡量減少在排序過程中對須要排序的數(shù)據(jù)進行分段,因為分段會造成 MySQL 不得不使用臨時表來進行交換排序。

但是sort_buffer_size 不是越大越好：

•  Sort_Buffer_Size 是一個 connection 級參數(shù),在每個 connection 第一次需要使用這個 buffer 的時候,一次性分配設置的內存。

•  Sort_Buffer_Size 并不是越大越好,由于是 connection 級的參數(shù),過大的設置和高并發(fā)可能會耗盡系統(tǒng)內存資源。

•  據(jù)說 Sort_Buffer_Size 超過 2M 的時候,就會使用 mmap() 而不是 malloc() 來進行內存分配,導致效率降低。

優(yōu)化八：group by

其原理也是先排序后分組，其優(yōu)化方式可參考order by。where高于having,能寫在where限定的條件就不要去having限定了。

看完上述內容，你們掌握MySQL中怎么實現(xiàn)索引優(yōu)化的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注億速云行業(yè)資訊頻道，感謝各位的閱讀！