mysql數據庫索引調優

一、mysql索引

1、磁盤文件結構

　　innodb引擎：frm格式文件存儲表結構，ibd格式文件存儲索引和數據。

　　MyISAM引擎：frm格式文件存儲表結構，MYI格式文件存儲索引，MYD格式文件存儲數據

2、mysql數據庫數據范問原理（innodb、BTREE）

　　（1）ibd文件中主鍵構建b+tree，主鍵樹的葉子結點包含了所有的主鍵值，存儲主鍵值和對應的表數據

　　（2）其他索引構建b+tree，樹的葉子結點包含了所有該索引字段的值，存儲索引值和主鍵值。

　　原理圖如下圖所示：

3、索引類型

　　普通索引：無任何限制的索引
　　唯一索引：列值唯一的索引，可以為空值
　　主鍵索引：表的主鍵，特殊的唯一性索引，不能為空值
　　組合索引：多個字段上的索引，遵循左前綴集合
　　全文索引：來查找文本中的關鍵字

4、使用explain+sql語句進行調優

    （1）explain包含的信息包含：

    　　 主要從id、type、key、rows、Extra分析。

     （2）id

　　　表示執行的先后順序，id值大的先執行，小的后執行，id值相同的從上到下執行。

     （3）type

　　　訪問類型，結果值從好到壞依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。

　　　建議盡量達到range級別，常見類型介紹如下：

　　　const：通過索引一次找到，通常用于主鍵或唯一性索引。

　　　eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見于主鍵 或 唯一索引掃描。

　　　ref：非唯一性索引掃描，返回匹配某個單獨值的所有行。

　　　range：只檢索給定范圍的行，使用一個索引來選擇行。key列顯示使用了那個索引。一般就是在where語句中出現了bettween、<、>、in等的查詢。

　　　index：Index與ALL雖然都是讀全表，但index是從索引中讀取，而ALL是從硬盤讀取。

　　　all：Full Table Scan，遍歷全表以找到匹配的行 。

　　（4）key

　　　　實際使用的索引，如果為NULL，則沒有使用索引。查詢中如果使用了覆蓋索引，則該索引僅出現在key列表中 。

　　（5）rows

　　　　根據表統計信息及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數。

　　（6）Extra

　　　　Using index： 表示相應的select操作中使用了覆蓋索引（Covering Index），避免了訪問表的數據行，效率高 。Using where，表明索引被用來執行索引鍵值的查找，如果沒用同時出現Using where，表明索引用來讀取數據而非執行查找動作。

　　　　Using  where：表示使用了where條件過濾。

　　　　Convering Index：覆蓋索引表示直接從索引中讀取數據，sql中查詢字段、where條件等涉及的字段都在覆蓋索引包含的字段里面。

　　　　Using Index Condition：優化器在索引存在情況下通過符合range范圍的條數和總數比例來確定進行索引還是全表遍歷。

　　　　Using filesort：無法利用索引完成的排序操作。

　　　　Using temporary：使用臨時表保存中建結果，如order by和group by，出現臨時表需要優化sql。

5、組合索引

　　（1）使用組合索引時需要遵循索引最左匹配原則，使用了最左原則type可以為eq_ref （組合索引為唯一索引）、ref、index，可以使用算法來

查詢索引值。如果組合索引為（a,b），使用where a ='value'或者where a ='value' and b='value1'時type為ref。

　　（2）使用組合索引時需要遵循索引最左匹配原則，沒有使用了最左原則type可以為index，直接掃描索引全表查找索引值。如果組合索引為（a,b），使用where b ='value1'或者where a ='value' or b ='value1' 時type為index。

　　（3）組合索引和多個單列索引

　　　　在（a,b,c）3列上建立組合索引和3個單列索引時，where條件為a、b、c3列時，組合索引性能更優，3個單列索引只會走一個最優的單列索引。

6、mysql不走索引的原因                                

　　1) 沒有查詢條件，或者查詢條件沒有建立索引。

　　2) 在查詢條件上沒有使用引導列。

　　3) 查詢的數量是大表的大部分，應該是30％以上。

　　4) 索引本身失效。

　　5) 查詢條件使用函數在索引列上，或者對索引列進行運算，運算包括(+，-，*，/，! 等)

　　6) 對小表查詢。

　　10)隱式轉換導致索引失效.這一點應當引起重視.也是開發中經常會犯的錯誤。

　　11) 注意使用的特殊符號

　　12)like "%_" 百分號在前.

　　13) not in ,not exist。

　　15)當變量采用的是times變量，而表的字段采用的是date變量時.或相反情況。

　　16)B-tree索引is null不會走,is not null會走。

　　17)聯合索引 is not null 只要在建立的索引列（不分先后）都會走

　　　　in null時 必須要和建立索引第一列一起使用,當建立索引第一位置條件是is null 時,其他建立索引的列可以是is null（但必須在所有列 都滿足is null的時候）,或者=一個值；當建立索引的第一位置是=一個值時,其他索引列可以是任何情況（包括is null =一個值）,以上兩種情況索引都會走。其他情況不會走。