核心思路 合理創建索引 使用索引避免全表掃描 使用explain sql語句來獲取mysql執行計劃,分析性能瓶頸 只有當where條件子句中用到索引字段才會觸發使用索引 建立索引的標準: 1.表記錄不足5000條,不建立索引[經驗之談] 2.頻繁作為where子句的字段[實際需求] 3.唯一性比例要比較高 distinct(字段)/字段 >=80% 一.查詢優化 1.避免全表掃描,首先應考慮在where和orderby字段上建立索引 2.避免對where子句中的字段使用null值判斷,這樣會導致引擎放棄索引而進行全表掃描,因此建立字段時,默認填充最好設置not null 約束并按需求判斷是否指定默認值[一般字符類型默認空字符串,數值類型默認0,時間默認當前時間]. not null 和 default是兩種不同情況的約束, not null 約束了在insert和update時,sql語句必須指定字段的值,而如果加了default,則會取消 not null 對insert的字段必填約束,轉而使用默認值填充. 3.盡量避免where子句中字段判斷使用 != 或 <> 操作符,這樣會導致引擎放棄索引而進行全表掃描 4,應避免在where子句中使用 or 來鏈接條件,因為其中只要有一個字段沒有使用索引,將導致引擎放棄索引而進行全表掃描. 如 select id from t where num=10 or Name = 'admin' 可以改為 select id from t where num = 10 union all select id from t where Name = 'admin' "union"操作符是合并兩個多多個select的查詢結果集.注意連接的兩個或多個查詢語句 查詢字段的名稱和順序要一致,數據類型要類似. 其中 union all 是列出所有查詢結果且不排重 select1 = 1 2 3 union all select2 = 3 4 5 結果為 1 2 3 3 4 5 其中 union 是列出所有查詢結果且排重 select1 = 1 2 3 union all select2 = 3 4 5 結果為 1 2 3 4 5 只會列出所有符合的結果且首次出現的結果,后續不再出現.同表數據可以用此特性合并去重,但異表數據此特性會導致首次出現的數據外的其他表相同字段數據丟失 5.in 和 not in 要慎用,否則導致全表掃描 對于連續的范圍,能用between就不要用in 多表查詢取字段交集時,用exists替代in 例子: select num from a where num in(select num from b) 策略: select num from a where exists(select num from b where num=a.num) 6.模糊查詢匹配的值起始部分使用了模糊%導致全表掃描 例子: select id from t where name like ‘%abc%’ 策略: 若要提高效率，可以考慮全文檢索 7.避免對where子句的字段進行表達式操作,這樣會導致引擎放棄索引而進行全表掃描 例子: select id from t where num/2 = 1000 策略: select id from t where num = 100*2 例子: select id from t where substring(name,1,3) = ’abc’ -–name以abc開頭的id 策略: select id from t where name like 'abc%' 8.使用到符合索引,必須保證該復合索引的第一個字段作為條件才能保證索引被正常使用,否則不會使用,并應盡可能的讓字段條件順序與組合索引順序一致.任意N個字段的組合索引,可以看似為N個索引組合的集合,每個組合分別為完全順序的索引字段,依次從尾部去掉一個索引字段,因為所有的組合都保留了第一順位的字段,也就是最左邊的字段,也叫最左原則,使用到索引組合中,索引字段數越多越有效率. 例子: 符合索引 name age city ,相當于建立了 name age city name age name 這三個索引集合,只要條件字段的順序與其一致,就可以用到,明顯無論那種條件順序都是第一個字段在最前面. 9.索引可以增加select效率,但是會降低insert和update的效率,建議一個表索引不要超過6個,索引的建立最好是查詢頻繁的字段上.索引除了可以增加查詢效率還可以減少排序成本,因為索引本身是有序值相當于字典的頁碼,插入數據時本身就會根據索引字段的排序特效對數據排序,當排序字段剛好是查詢的索引字段,就不用再排了. 10.連表查詢中,mysql優化程序會用小表驅動大表,此時小表是驅動表,大表是被驅動表.如果有排序和條件字段,優先使用驅動表的字段作為條件,可以減少耗時.