資料庫的查詢優化有很多事情可以做。下面總結瞭一些:
基本原則:
減少資料庫查詢時發生的磁盤I/O數
合理利用索引
避免全表遍歷、掃描
減少網絡傳輸數據量
隻查詢需要的字段
如果同時有Group By和Join,盡量先Group By後再Join
避免在where語句中使用MySQL函數
使用locate(substr, str)代替 like '%substr%'
使用組合索引(對超長字段使用sub_part())
避免使用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
查看是否用到瞭索引:
Explain <SQL語句>