mysql優化整理(索引)

什么是索引？

索引是表記錄的單個或多個字段重新組織的一種方法，其目的是提高數據庫的查詢速度，本質上就是一種數據結構。索引的類型：primary（主鍵）、secondary（其他）

索引的數據結構

• Innodb primary key

  

  物理文件：

  

• Innodb secondary key

  

索引的優缺點

• 優點
  在大數據前提下，多數情況都會加快查詢的速度
• 缺點
  每次的數據的更新、刪除以及插入操作都需要對索引進行額外的維護，所以需要謹慎的創建索引，創建索引不是萬能的，后面會介紹一些簡單的技巧

小技巧

• 索引列都有default值
  這個的主要問題是，數據庫存儲的數值存在數據類型轉換的問題，如果有默認值，就可以達到快速區別的作用。
• 控制好索引長度
  一般情況下，索引的字段只會存儲255個字符長度，如果超出這部分之外的，其實作用不是很大，所以創建索引的字段長度最好是255以內的長度。
• 索引列的分組排序
  對于創建索引的字段進行分組處理，使用union進行合并
• like語句的優化。
  使用like的語句首先可以創建索引，其次盡量避免使用雙百分號(%)，盡量使用一個%完成，這樣可以提升速度。
• 不在索引列上進行運算
  不要對字段進行計算后查詢（避免將計算寫在等號左邊，可以換一種寫法放在等號右邊）。
• 不使用NOT IN和!=操作
  對于使用not in或者!=的查詢語句，盡量使用in寫法。
• 注意隱式轉換的問題
  和第一條差不多，我們存儲的字段類型最好和我們查詢語句的字段類型保持一致，這樣可以提升訪問速度。
• 創建組合索引可以將常用字段放在最左，這樣單個查詢條件也可以使用索引

再度整理

• 創建合理的索引或者組合索引
• 改寫語句，對于沒有創建索引的語句，可以使用其他形式轉換成具有索引的查詢語句
• 鏈表查詢時，最好使用inner形式或者是union合并；另外最好把條件限制到最低，這樣符合要求的語句就會更少，查詢速度會更快。
• 減少并發量
  有時候發現單個執行很快，多個并發執行會有這個問題。
• 縮小查詢范圍
  比如使用in的時候，in里面字段最多不超過20個，limit也需要減少個數。
• 使用主、從庫，進行讀寫分離
• 其他的可能需要檢查機器本身的問題了：內存、磁盤等等。

其他

查看是否使用索引方式：可以使用 explain + sql語句，查看所查詢的語句是否使用了創建的索引，進而優化自己的索引創建。

• 未創建索引的查詢

  

主要字段說明：select_type(簡單查詢)、table(表名)、type(索引類型)、prosible_key(可能的索引字段)、key(使用到的索引字段)、key_len(索引字段長度)、rows(查詢行數)、Extra(額外信息)

• 創建索引的查詢

  我這里創建了idx_appid_userid的組合索引，進行查詢之后：

  

我們可以發現之前的字段值發生了變化，可以看出來使用了我們創建的索引，額外信息中也提到使用到了索引條件。

使用上述方式進行優化后，之前整理過的 記一次pending請求問題查找過程 ，有很大的改善，不過這個優化需要一直跟進。

小結

總體的原則就是：盡最大的努力，結合業務情況，減少數據庫服務器的I/O，即可大幅度提升服務器的速度。

**本文主要參考@高級DBA李丹的分享 ，再加上一些實戰整理而成，由于是初步接觸數據庫相關的東西，有描述不準確的歡迎指正。另外，如果有其他好的方案也可以推薦給我，不勝感激~**