MySQL查詢最佳化，MySQL最佳化

MySQL查詢最佳化，MySQL最佳化
資料庫的查詢最佳化有很多事情可以做。下面總結了一些：
基本原則：

1. 減少資料庫查詢時發生的磁碟I/O數
   • 合理利用索引
   • 避免全表遍曆、掃描
2. 減少網路傳輸資料量
   • 只查詢需要的欄位

1. 如果同時有Group By和Join，盡量先Group By後再Join
2. 避免在where語句中使用MySQL函數
3. 使用locate(substr, str)代替 like '%substr%'
4. 使用複合式索引（對超長欄位使用sub_part())
5. 避免使用select *，應該直接指定要select的欄位
   • select *會導致資料庫先把*翻譯成實際的欄位，多了一步操作
   • *中不是每個欄位都是我們需要的，浪費了磁碟I/O和網路傳輸
   • 如果我們需要select的欄位（比如user_id）已經建立了索引，select user_id將直接從索引中返回結果，而select * 需要從資料表中尋找結果，至少多了一次磁碟I/O

最佳化工具：連續運行若干次，看總耗時：SELECT BENCHMARK(100000000, LOCATE('foo','foobar'));SELECT BENCHMARK(100000000, 'foobar' LIKE '%foo%');
查看是否用到了索引：
Explain <SQL語句>
可以被建立索引的欄位：

• 哪些欄位需要建立索引：用於JOIN、WHERE、ORDER BY、GROUP BY、MAX()、MIN()等欄位上
• 哪些欄位不適合建索引：含有大量重複值的欄位，如布爾型

索引生效條件：

• 哪些情況索引會生效：
  • 使用>, >=, =, <, <=, IF NULL和BETWEEN
  • 使用MAX()和 MIN()函數的情況
  • 被ORDER BY、GROUP BY使用的情況
  • LIKE使用萬用字元在最後的時候：where a like 'b%'
  • 對於複合式索引（multiple-column index 或 composite index），如（c1,c2）：where c1=1生效，where c1=1 and c2=2生效
    
• 哪些情況索引不會生效：
  • 使用NOT IN、<>的情況
  • LIKE使用萬用字元開始的時候：where a like '%b'
  • 如果WHERE中的列已經建立了索引，ORDER BY中另外一個列的索引不會生效
  • 對於複合式索引，如（c1,c2）：where c2=2不生效
  • 對列進行運算的情況下，如：WHERE YEAR(iDate)<2015（可改為WHERE iDate<'2015-01-01'）

MySQL索引類型：

• NORMAL：普通索引類型，沒有唯一性限制
• UNIQUE：要求被索引欄位的值都是唯一的
• FULLTEXT：可以在varchar或text類型上建立，只能用於MyISAM類型的表

索引欄位的資料類型選擇：

• 被索引的欄位盡量使用小和單一資料型別（比如整形），避免使用複雜類型（比如字串類型）
• 被索引的欄位盡量限制為NOT NULL，可以使用0、-1、空串代替NULL

（原創文章，轉載請註明