上一篇 <<<MySQL性能優(yōu)化之表設(shè)計優(yōu)化
下一篇 >>>MySQL性能優(yōu)化之索引調(diào)優(yōu)實戰(zhàn)
SQL性能優(yōu)化的目標：至少要達到range級別，要求是ref級別，consts最高蝶桶。【阿里巴巴JAVA開發(fā)手冊】
說明：
1)、consts單表中最多只有一個匹配行（主鍵/唯一索引）味抖，在優(yōu)化階段即可讀到數(shù)據(jù)躬贡。
2)劫谅、ref指的是使用普通的索引(normal index)氢妈。
3)粹污、range 對索引進行范圍檢索
反例：explain表的結(jié)果段多，type=index首量，索引物理文件全掃描新啼，速度非常慢磕蒲，這個index級別比較range還低，與全表掃描是小巫見大巫霹娄。
最優(yōu)到最差順序：system > const > eq_ref > ref > range > index > ALL
語句優(yōu)化
1.簡單查詢語句優(yōu)化
1.1 select 字段部分
- a觉啊、返回具體的字段以代替*
1)拣宏、用不到的字段會消耗CPU和IO、消耗網(wǎng)絡(luò)帶寬
2)杠人、若可以使用索引的情況下勋乾，使用*會導(dǎo)致回表查詢，效率更低
3)嗡善、數(shù)據(jù)暴露更不安全辑莫。
- b、count(*)會統(tǒng)計null值的行罩引，count(列名)不會統(tǒng)計null值的行
select count(user_id) from dw_user where `order` is null limit 10; 結(jié)果：5875
select count(0) from dw_user where `order` is null limit 10; 結(jié)果：5875
select count(`order`) from dw_user where `order` is null limit 10; 結(jié)果：0
5.6版本：默認會選擇輔助索引技術(shù)各吨，沒有的話才會走主鍵聚集索引技術(shù)
1.select count(1) from employees; 使用輔助聯(lián)合索引計數(shù)
2.select count(id) from employees; 使用輔助索引技術(shù)
3.select count(name) from employees; 使用輔助索引技術(shù)
4.select count() from employees; 使用輔助索引技術(shù)
5.select count(id) from employees force index(PRIMARY) 強制使用主鍵索引
從效率角度分析：count(1) ==count()>count(name)>count(id)
因為主鍵索引的id對應(yīng)的葉子節(jié)點中存放data數(shù)據(jù)，每個節(jié)點只能放16k的頁數(shù)據(jù)袁铐，所以掃描范圍不是非常廣揭蜒，而且比較占內(nèi)存的耗時。
count(name)統(tǒng)計的時候剔桨，葉子節(jié)點中存放主鍵id屉更，每次掃描范圍更廣，效率更高洒缀。
推薦使用count(*)
在MySQL 5.7 COUNT(*)默認會選擇聚集索引技術(shù)瑰谜，進行一次內(nèi)部handler函數(shù)調(diào)用，即可快速獲得該表總數(shù)帝洪。
如果聚集索引較大（或者說表數(shù)據(jù)量較大）似舵，沒有完全加載到buffer pool中的話，MySQL 5.7的查詢方式有可能反而會更慢葱峡，還不如用原先的方式（MySQL 5.6）
聚集索引：就是主鍵id索引
非聚集索引：就是自定義的其他字段索引
- c砚哗、函數(shù)統(tǒng)計，盡量放到內(nèi)存中執(zhí)行
select sum(a)+sum(b) as c
- d砰奕、tinyint在使用時容易變?yōu)椴紶栃椭虢妫a中記得轉(zhuǎn)換提鸟，用case時等于更優(yōu)
case status when 0 then do1 when 1 do2 end
===》
case when status=0 then do1 when status=1 then do2 end
1.2 條件部分
- a、數(shù)據(jù)類型必須一致仅淑，否則索引失效称勋，還會增加轉(zhuǎn)換的開銷
SELECT * FROM t WHERE id = '19';
----->
SELECT * FROM t WHERE id = 19;
- b、少用≥涯竟，直接使用＞赡鲜，可提升查詢效率
select * from dw_user where user_id>=101; ----—多一次等于的判斷
select * from dw_user where user_id>100;
1.3 分組和排序
- 禁止使用order by rand()
SELECT * FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 4;
---->
SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4;
- 分組查詢時，默認分組后庐船，還會排序银酬，可能會降低速度
在group by 后面增加 order by null 就可以防止排序.
explain select * from emp group by deptno order by null;
排序細節(jié)可參考order by中的單路和雙路排序算法原理
1.4 索引不起效果的寫法
- a、使用group by揩瞪、not in、not like不使用索引
2.查詢語句-臨時表場景
2.1 必須創(chuàng)建臨時表的情況
- 如果一次性插入數(shù)據(jù)量很大篓冲，那么可以使用 select into 代替 create table嗤攻，避免造成大量 log 恶耽，以提高速度缰盏；
- 如果數(shù)據(jù)量不大川抡，為了緩和系統(tǒng)表的資源侍咱，應(yīng)先 create table，然后 insert麸粮。
2.2 臨時表的顯示刪除
先 truncate table ，然后 drop table 寂玲，這樣可以避免系統(tǒng)表的較長時間鎖定流纹。
3.插入語句
//批量插入使用/*+append*/
insert into p_nbsc_ho1 select * from p_nbsc_ho （消耗時間73分鐘）
insert /*+append*/ into p_nbsc_ho2 select * from p_nbsc_ho （消耗時間7分鐘）
4.更新語句
如果只更改 1茸炒、2 個字段绅项，不要 Update 全部字段示括，否則頻繁調(diào)用會引起明顯的性能消耗，同時帶來大量日志吼拥。
5.開發(fā)代碼中少用truncate table
truncate table和delete功能相同，但速度更快纳击，使用的系統(tǒng)和事務(wù)日志資源較少，它無事務(wù)且不觸發(fā)trigger，容易造成事故啥寇。【阿里巴巴JAVA開發(fā)手冊】
推薦閱讀：
<<<MySQL執(zhí)行計劃示例解讀
<<<MySQL性能優(yōu)化之慢查詢定位
<<<MySQL性能優(yōu)化之表設(shè)計優(yōu)化
<<<MySQL性能優(yōu)化之索引調(diào)優(yōu)實戰(zhàn)
<<<MySQL性能優(yōu)化之分頁查詢優(yōu)化
<<<MySQL性能優(yōu)化之關(guān)聯(lián)查詢優(yōu)化
<<<MySQL性能優(yōu)化之in你雌、exists優(yōu)化
<<<order by中的單路和雙路排序算法原理
<<<MySQL如何性能優(yōu)化面試題完美解答
