1. 索引的優(yōu)化

1.1 支持多種過濾條件

• 考慮表上所有的選項
  • 通用的原則: 盡量在選擇性高的列上做索引.
  • 例外: 對于經常被作為條件的列, 如性別, 最好還是對它創(chuàng)建索引.
  • 設計索引時, 不要?只從現在的查詢考慮, 還要考慮對查詢進行優(yōu)化.
  • 如果為某些查詢創(chuàng)建的索引, 會導致另一些查詢的銷量降低. 應該考慮優(yōu)化查詢.
• 盡可能重用索引而不是建立大量的組合索引
  • 例如: 查詢中可能需要(sex, country, age) 和(sex, country, region, age) 這兩種索引.
  • 限制: Mysql 只能匹配索引的最左前綴.
  • 做法: 只建立(country, region,sex, age) 一個索引.
    • 在需要使用到(country, region, age) 索引的查詢中, 在where 條件中加入 And sex in ('m','f');
  • 局限: 每額外增加一個IN() 條件, 優(yōu)化器需要做的組合就以指數級增加.
    • 當列過多時, 很可能會大大地降低了查詢性能.
    • 老版本的MySql 在IN()組合條件過多時會有很多問題, 如查詢優(yōu)化耗時且浪費內存.
    • 而新版本在組合數超過一定數量后, 就不再進行執(zhí)行計劃評估了, 從而導致Mysql 不能很好地利用索引.
• 盡可能將需要做范圍查詢的列放到索引的后面
  • 例如: age 這類經常被用來作范圍條件(18~25) 的列.
  • 限制: 查詢只能使用索引的最左前綴, 直到遇到第一個范圍條件列.
  • 做法: 可以使用IN() 來替代范圍查詢, 但是并不是所有的范圍查詢都適用.

1.2 避免多個范圍條件

• 列表和范圍查詢
  • 列表查詢: IN(1,3,5) ; 范圍查詢: id>45.
  • Mysql 區(qū)分不來這兩種類型, explain 的結果中type 字段都為range.
  • 但是, Mysql 不能再使用范圍列后面的其他索引, 而對于列表查詢沒有這個限制.
  • 在遇到有連續(xù)的范圍查詢時, 設法把除最后一列外的范圍列查詢轉化為列表查詢.
  • 未來: 如果Mysql 實現了松散索引掃描, 那么就能在一個索引上使用多個范圍條件. 也就不需要該技巧.

2. 維護索引和表

2.1 找到并修復順壞的表

• 損壞的索引會導致查詢返回錯誤的結果或者莫名的主鍵沖突等問題.
  • 當遇到古怪的問題時, 使用Check Table 來檢查是否有表損壞.
  • 如果有表損壞, 使用Repair Table 來修復表.
  • 如果引擎不支持修復命令, 通過一個不做任何操作(no-op) 的Alter 操作來重建表, 也可以達到效果.
  • 如果遇到數據損壞, 重要的是要找出是什么原因導致了損壞, 而不是簡單地修復. 否則損壞很可能再次發(fā)生.

2.2 更新索引統(tǒng)計信息

• Mysql 優(yōu)化器通過兩個API 來了解存儲引擎的索引值分布情況, 來決定如何使用索引.
  • records_in_range() 返回一個范圍內的記錄數.
  • info() 返回各種類型的數據, 包括索引的基數(鍵值的記錄數).
• 如果存儲引擎提供的掃描行數信息是不準確的. 優(yōu)化器會使用索引統(tǒng)計信息來估算掃描行數
  • 優(yōu)化器基于成本模型, 成本的核心指標是查詢需要掃描的行數.
  • 如果沒有統(tǒng)計信息,或者信息不準確, 優(yōu)化器很可能做出錯誤的決定.
  • 通過Analyze Table來重新生成統(tǒng)計信息.
• 每種存儲引擎的索引統(tǒng)計信息實現方式都不同, 所以需要重新生成的頻率和成本也不同.
  • MyISAM 將統(tǒng)計信息存儲在磁盤中, 重新生成需要進行一次全索引掃描來計算索引基數, 過程中會鎖表.
  • InnoDB 不在磁盤存儲索引統(tǒng)計信息, 而是通過隨機的索引訪問進行評估并存儲在內存中.
  • Show Index From 來查看索引的基數.
  • 索引的統(tǒng)計信息會在一些情況下自動更新, 如果數據量較大時,可能會造成性能問題.
    • 關閉自動更新后, 需要周期性地運行Analyze Table 來手動更新. 否則會出現糟糕的執(zhí)行計劃.

2.3 減少索引和數據的碎片

• B-Tree 索引可能會碎片化, 這會降低查詢的效率.
  • 碎片化的索引會議很差或無序的方式存儲在硬盤上.
• 表的數據存儲也可能碎片化.
  • 行碎片. 數據行被存儲在多個片段中.
  • 行間碎片. 邏輯上順序的頁或行, 在硬盤上不是順序存儲的.
  • 剩余空間碎片.頁中的剩余空間會造成浪費.
• 通過Optimize Table 或導出再倒入的方式重新整理數據.
  • 對于不支持上述命令的存儲引擎, 通過一個不做任何操作的Alter 操作來重建表.

3. MICS

• 三個原則
  • 單行訪問是很慢的. 讀取的塊應該包含盡可能多所需要的行.
  • 按順序訪問范圍數據是很快的.
  • 索引覆蓋查詢是很快的.
• 像 NOW() 和 RAND() 或是其它的諸如此類的SQL函數都不會開啟查詢緩存泽论，因為這些函數的返回值時不定的易變的.
• 當只要一行數據時使用limit 1.
  • 優(yōu)化: 數據庫引擎會在找到第一條數據后立即停止搜索.
• 為經常用來做搜索的字段建立索引.
• 表join 時兩邊的列應該擁有相同的類型,并且是被建立過索引的.
• 永遠為每張表設置一個ID.
  • 即使該表已經有唯一的字段. int 自增的ID 性能更好.
  • 關聯(lián)表的外鍵是個例外.
• 把IP 地址存儲unsigned int.