mysql索引的使用和優化

索引是一種特殊的檔案(InnoDB 資料表上的索引是表空間的一個組成部分)，它們包含著對資料表裡的所有記錄的引用指標。更通俗的說，資料庫索引就好比一本書的目錄，能夠加快資料庫的查詢速度。
首先感謝網上的那些前輩和大神們的無私分享
最近在學習mysql的優化問題,在查詢中正確使用索引,對查詢效率的提升有非常大的幫助,使用不當會使索引失效,起不到索引該有的作用。把這兩天學到的知識記錄一下。

使用索引的優勢

• 提高資料的檢索速度，降低資料庫IO成本：使用索引的意義就是通過縮小表中需要查詢的記錄的數目從而加快搜尋的速度。
• 降低資料排序的成本，降低CPU消耗：索引之所以查的快，是因為先將資料排好序，若該欄位正好需要排序，則真好降低了排序的成本。

使用索引帶來的問題

• 佔用儲存空間：索引實際上也是一張表，記錄了主鍵與索引欄位，一般以索引檔案的形式儲存在磁碟上。
• 降低更新表的速度：表的資料發生了變化，對應的索引也需要一起變更，從而減低的更新速度。否則索引指向的物理資料可能不對，這也是索引失效的原因之一。

索引的型別

1.normal：

表示普通索引,它沒有任何限制，MyISAM 中預設的 B-tree 型別的索引

2.unique：

表示唯一的，不允許重複的索引，但是允許有空值。如果該欄位資訊保證不會重複例如身份證號用作索引時，可設定為unique。

3.full textl:

表示全文搜尋的索引。 FULLTEXT 用於搜尋很長一篇文章的時候，效果最好。注意僅 MyISAM 引擎支援

4.組合索引（最左字首）

平時用的SQL查詢語句一般都有比較多的限制條件，所以為了進一步榨取MySQL的效率，就要考慮建立組合索引。使用組合索引時注意最左匹配原則。

比如新建索引ALTER TABLE testADD INDEX `id_name_age` (`id`,`name`,`age`)。

在查詢的時SELECT * FROM user WHERE id =1 AND name=`bruce`索引起作用。
但是查詢時SELECT * FROM user WHERE name=`bruce` AND age = 18這時候索引不起作用。
至於原因,因為輔助索引是B+樹實現的，雖然可以指定多個列，但是每個列的比較優先順序不一樣，寫在前面的優先比較。一旦出現遺漏，在B+樹上就無法繼續搜尋了（通過補齊等措施解決的除外），因此是按照最左連續匹配來的。既然是在B+樹上搜尋，對於條件的比較自然是要求精確匹配（即”=”和”IN”）。不過順序倒是可以顛倒，因為查詢優化器重排序一下就好了。

索引的優化

• 應儘量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如：

select id from t where num is null,可以在num上設定預設值0，確保表中num列沒有null值，然後這樣查詢：
select id from t where num=0

• 應儘量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描。優化器將無法通過索引來確定將要命中的行數,因此需要搜尋該表的所有行。
• 應儘量避免在 where 子句中使用 or 來連線條件，否則將導致引擎放棄使用索引而進行全表掃描，如：

select id from t where num=10 or num=20

• 儘量避免在索引過的字元資料中，使用非打頭字母搜尋。這也使得引擎無法利用索引。

見如下例子：

SELECT * FROM T1 WHERE NAME LIKE ‘%L%’
SELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=’L’
SELECT * FROM T1 WHERE NAME LIKE ‘L%’

• 應儘量避免在 where 子句中對欄位進行表示式操作，這將導致引擎放棄使用索引而進行全表掃描
• 應儘量避免在where子句中對欄位進行函式操作，這將導致引擎放棄使用索引而進行全表掃描
• 不要在 where 子句中的“=”左邊進行函式、算術運算或其他表示式運算，否則系統將可能無法正確使用索引

總結

哪些情況需要建索引：

1 主鍵，唯一索引
2 經常用作查詢條件的欄位需要建立索引
3 經常需要排序、分組和統計的欄位需要建立索引
4 查詢中與其他表關聯的欄位，外來鍵關係建立索引

哪些情況不要建索引：

1 表的記錄太少，百萬級以下的資料不需要建立索引,資料量很少的時候,索引帶來的提升不夠明顯
2 經常增刪改的表不需要建立索引(在修改表的內容時，索引必須進行更新，有時可能需要重構，因此，索引越多，所花的時間越長。)
3 資料重複且分佈平均的欄位不需要建立索引，如 true,false 之類。(體現不出索引帶來的價值)
4 頻發更新的欄位不適合建立索引(和2同理)
5 where條件裡用不到的欄位不需要建立索引(索引起不到作用)

使用注意:

• 效能優化過程中，選擇在哪個列上建立索引是最重要的步驟之一。可以考慮使用索引的主要有兩種型別的列：在where子句中出現的列，在join子句中出現的列。
• 考慮列中值的分佈，索引的列的基數越大，索引的效果越好。
• 使用短索引，如果對字串列進行索引，應該指定一個字首長度，可節省大量索引空間，提升查詢速度。
• 不要過度索引，只保持所需的索引。每個額外的索引都要佔用額外的磁碟空間，並降低寫操作的效能。
• 在修改表的內容時，索引必須進行更新，有時可能需要重構，因此，索引越多，所花的時間越長。
• MySQL只對一下操作符才使用索引：<,<=,=,>,>=,between,in, 以及某些時候的like(不以萬用字元%或_開頭的情形)。

再次感謝前輩們的無私分享

我也是在學習中,文中如有錯誤的地方,歡迎在評論區指出,方便共同學習