全文都是引用自：https://blog.csdn.net/Lxl1418/article/details/126717598
作者：String魄懂、code

SQL優(yōu)化 21 連擊
一润绎、查詢SQL盡量不要使用select *侧巨，而是具體字段

1什荣、反例

SELECT * FROM user
1
2何址、正例

SELECT id,username,tel FROM user
1
3聪廉、理由

節(jié)省資源乖订、減少網(wǎng)絡(luò)開銷斥难。
可能用到覆蓋索引，減少回表合住，提高查詢效率绰精。
注意：為節(jié)省時間，下面的樣例字段都用*代替了透葛。
1
二笨使、避免在where子句中使用 or 來連接條件
1、反例

SELECT * FROM user WHERE id=1 OR salary=5000
1
2僚害、正例

（1）使用union all

SELECT * FROM user WHERE id=1
UNION ALL
SELECT * FROM user WHERE salary=5000
1
2
3
（2）分開兩條sql寫

SELECT * FROM user WHERE id=1

SELECT * FROM user WHERE salary=5000
1
2
3
3硫椰、理由

使用or可能會使索引失效，從而全表掃描萨蚕；
對于or沒有索引的salary這種情況靶草，假設(shè)它走了id的索引，但是走到salary查詢條件時岳遥，它還得全表掃描奕翔；
也就是說整個過程需要三步：全表掃描+索引掃描+合并。如果它一開始就走全表掃描浩蓉，直接一遍掃描就搞定派继；
雖然mysql是有優(yōu)化器的，出于效率與成本考慮捻艳，遇到or條件驾窟，索引還是可能失效的；
三讯泣、盡量使用數(shù)值替代字符串類型
1纫普、正例

主鍵（id）：primary key優(yōu)先使用數(shù)值類型int，tinyint
性別（sex）：0代表女，1代表男昨稼；數(shù)據(jù)庫沒有布爾類型节视，mysql推薦使用tinyint
2、理由

因為引擎在處理查詢和連接時會逐個比較字符串中每一個字符假栓；
而對于數(shù)字型而言只需要比較一次就夠了寻行；
字符會降低查詢和連接的性能，并會增加存儲開銷匾荆；
四拌蜘、使用varchar代替char
1、反例

address char(100) DEFAULT NULL COMMENT '地址'
1
2牙丽、正例

address varchar(100) DEFAULT NULL COMMENT '地址'
1
3简卧、理由

varchar變長字段按數(shù)據(jù)內(nèi)容實際長度存儲，存儲空間小烤芦，可以節(jié)省存儲空間举娩；
char按聲明大小存儲，不足補空格构罗；
其次對于查詢來說铜涉，在一個相對較小的字段內(nèi)搜索，效率更高遂唧；
五芙代、技術(shù)延伸，char與varchar2的區(qū)別盖彭？
1纹烹、char的長度是固定的，而varchar2的長度是可以變化的召边。

比如滔韵，存儲字符串“101”，對于char(10)掌实，表示你存儲的字符將占10個字節(jié)（包括7個空字符），在數(shù)據(jù)庫中它是以空格占位的邦马，而同樣的varchar2(10)則只占用3個字節(jié)的長度贱鼻，10只是最大值，當(dāng)你存儲的字符小于10時滋将，按實際長度存儲邻悬。

2、char的效率比varchar2的效率稍高随闽。

3父丰、何時用char，何時用varchar2?

char和varchar2是一對矛盾的統(tǒng)一體，兩者是互補的關(guān)系蛾扇，varchar2比char節(jié)省空間攘烛，在效率上比char會稍微差一點，既想獲取效率镀首，就必須犧牲一點空間坟漱，這就是我們在數(shù)據(jù)庫設(shè)計上常說的“以空間換效率”。

varchar2雖然比char節(jié)省空間更哄，但是假如一個varchar2列經(jīng)常被修改芋齿，而且每次被修改的數(shù)據(jù)的長度不同，這會引起“行遷移”現(xiàn)象成翩，而這造成多余的I/O觅捆，是數(shù)據(jù)庫設(shè)計中要盡力避免的，這種情況下用char代替varchar2會更好一些麻敌。char中還會自動補齊空格栅炒，因為你insert到一個char字段自動補充了空格的,但是select后空格沒有刪除，因此char類型查詢的時候一定要記得使用trim庸论，這是寫本文章的原因职辅。

如果開發(fā)人員細(xì)化使用rpad()技巧將綁定變量轉(zhuǎn)換為某種能與char字段相比較的類型（當(dāng)然，與截斷trim數(shù)據(jù)庫列相比聂示，填充綁定變量的做法更好一些域携，因為對列應(yīng)用函數(shù)trim很容易導(dǎo)致無法使用該列上現(xiàn)有的索引），可能必須考慮到經(jīng)過一段時間后列長度的變化鱼喉。如果字段的大小有變化秀鞭，應(yīng)用就會受到影響，因為它必須修改字段寬度扛禽。

正是因為以上原因锋边，定寬的存儲空間可能導(dǎo)致表和相關(guān)索引比平常大出許多，還伴隨著綁定變量問題编曼，所以無論什么場合都要避免使用char類型豆巨。

六、where中使用默認(rèn)值代替null
1掐场、反例

SELECT * FROM user WHERE age IS NOT NULL
1
2往扔、正例

SELECT * FROM user WHERE age>0
1
3、理由

并不是說使用了is null或者 is not null就會不走索引了熊户，這個跟mysql版本以及查詢成本都有關(guān)萍膛；
如果mysql優(yōu)化器發(fā)現(xiàn)，走索引比不走索引成本還要高嚷堡，就會放棄索引蝗罗，這些條件 !=，<>，is null串塑，is not null經(jīng)常被認(rèn)為讓索引失效沼琉；
其實是因為一般情況下，查詢的成本高拟赊，優(yōu)化器自動放棄索引的刺桃；
如果把null值，換成默認(rèn)值吸祟，很多時候讓走索引成為可能瑟慈，同時，表達(dá)意思也相對清晰一點屋匕；
七葛碧、避免在where子句中使用!=或<>操作符
1、反例

SELECT * FROM user WHERE salary!=5000

SELECT * FROM user WHERE salary<>5000
1
2
3
2过吻、理由

使用!=和<>很可能會讓索引失效
應(yīng)盡量避免在where子句中使用!=或<>操作符进泼，否則引擎將放棄使用索引而進(jìn)行全表掃描
實現(xiàn)業(yè)務(wù)優(yōu)先，實在沒辦法纤虽，就只能使用乳绕，并不是不能使用
八、inner join 逼纸、left join洋措、right join，優(yōu)先使用inner join
三種連接如果結(jié)果相同杰刽，優(yōu)先使用inner join菠发，如果使用left join左邊表盡量小。

inner join 內(nèi)連接贺嫂，只保留兩張表中完全匹配的結(jié)果集滓鸠；
left join會返回左表所有的行，即使在右表中沒有匹配的記錄第喳；
right join會返回右表所有的行糜俗，即使在左表中沒有匹配的記錄；
為什么曲饱？

如果inner join是等值連接吩跋，返回的行數(shù)比較少，所以性能相對會好一點渔工；
使用了左連接，左邊表數(shù)據(jù)結(jié)果盡量小桥温，條件盡量放到左邊處理引矩，意味著返回的行數(shù)可能比較少；
這是mysql優(yōu)化原則，就是小表驅(qū)動大表旺韭，小的數(shù)據(jù)集驅(qū)動大的數(shù)據(jù)集氛谜，從而讓性能更優(yōu)；
九区端、提高group by語句的效率
1值漫、反例

先分組，再過濾

select job, avg（salary） from employee
group by job
having job ='develop' or job = 'test';
1
2
3
2织盼、正例

先過濾杨何，后分組

select job，avg（salary） from employee
where job ='develop' or job = 'test'
group by job;
1
2
3
3沥邻、理由

可以在執(zhí)行到該語句前危虱，把不需要的記錄過濾掉

十、清空表時優(yōu)先使用truncate
truncate table在功能上與不帶 where子句的 delete語句相同：二者均刪除表中的全部行唐全。但 truncate table比 delete速度快埃跷，且使用的系統(tǒng)和事務(wù)日志資源少。

delete語句每次刪除一行邮利，并在事務(wù)日志中為所刪除的每行記錄一項弥雹。truncate table通過釋放存儲表數(shù)據(jù)所用的數(shù)據(jù)頁來刪除數(shù)據(jù)，并且只在事務(wù)日志中記錄頁的釋放延届。

truncate table刪除表中的所有行剪勿，但表結(jié)構(gòu)及其列、約束祷愉、索引等保持不變窗宦。新行標(biāo)識所用的計數(shù)值重置為該列的種子。如果想保留標(biāo)識計數(shù)值二鳄，請改用 DELETE赴涵。如果要刪除表定義及其數(shù)據(jù)，請使用 drop table語句订讼。

對于由 foreign key約束引用的表髓窜，不能使用 truncate table，而應(yīng)使用不帶 where子句的 DELETE 語句欺殿。由于 truncate table不記錄在日志中寄纵，所以它不能激活觸發(fā)器。

truncate table不能用于參與了索引視圖的表脖苏。

十一程拭、操作delete或者update語句，加個limit或者循環(huán)分批次刪除
1棍潘、降低寫錯SQL的代價

清空表數(shù)據(jù)可不是小事情恃鞋，一個手抖全沒了崖媚，刪庫跑路？如果加limit恤浪，刪錯也只是丟失部分?jǐn)?shù)據(jù)畅哑，可以通過binlog日志快速恢復(fù)的。

2水由、SQL效率很可能更高

SQL中加了limit 1荠呐，如果第一條就命中目標(biāo)return， 沒有l(wèi)imit的話砂客，還會繼續(xù)執(zhí)行掃描表泥张。

3、避免長事務(wù)

delete執(zhí)行時,如果age加了索引鞭盟，MySQL會將所有相關(guān)的行加寫鎖和間隙鎖圾结，所有執(zhí)行相關(guān)行會被鎖住，如果刪除數(shù)量大齿诉，會直接影響相關(guān)業(yè)務(wù)無法使用筝野。

4、數(shù)據(jù)量大的話粤剧，容易把CPU打滿

如果你刪除數(shù)據(jù)量很大時歇竟，不加 limit限制一下記錄數(shù)，容易把cpu打滿抵恋，導(dǎo)致越刪越慢焕议。

5、鎖表

一次性刪除太多數(shù)據(jù)弧关，可能造成鎖表盅安，會有l(wèi)ock wait timeout exceed的錯誤，所以建議分批操作世囊。

十二别瞭、UNION操作符
UNION在進(jìn)行表鏈接后會篩選掉重復(fù)的記錄，所以在表鏈接后會對所產(chǎn)生的結(jié)果集進(jìn)行排序運算株憾，刪除重復(fù)的記錄再返回結(jié)果蝙寨。實際大部分應(yīng)用中是不會產(chǎn)生重復(fù)的記錄，最常見的是過程表與歷史表UNION嗤瞎。如：

select username,tel from user
union
select departmentname from department
1
2
3
這個SQL在運行時先取出兩個表的結(jié)果墙歪，再用排序空間進(jìn)行排序刪除重復(fù)的記錄，最后返回結(jié)果集贝奇，如果表數(shù)據(jù)量大的話可能會導(dǎo)致用磁盤進(jìn)行排序虹菲。推薦方案：采用UNION ALL操作符替代UNION，因為UNION ALL操作只是簡單的將兩個結(jié)果合并后就返回掉瞳。

十三届惋、批量插入性能提升
1髓帽、多條提交

INSERT INTO user (id,username) VALUES(1,'哪吒編程');

INSERT INTO user (id,username) VALUES(2,'妲己');
1
2
3
2、批量提交

INSERT INTO user (id,username) VALUES(1,'哪吒編程'),(2,'妲己');
1
3脑豹、理由

默認(rèn)新增SQL有事務(wù)控制，導(dǎo)致每條都需要事務(wù)開啟和事務(wù)提交衡查，而批量處理是一次事務(wù)開啟和提交瘩欺，效率提升明顯，達(dá)到一定量級拌牲，效果顯著俱饿，平時看不出來。

十四塌忽、表連接不宜太多拍埠，索引不宜太多，一般5個以內(nèi)
1土居、表連接不宜太多枣购，一般5個以內(nèi)

關(guān)聯(lián)的表個數(shù)越多沃呢，編譯的時間和開銷也就越大
每次關(guān)聯(lián)內(nèi)存中都生成一個臨時表
應(yīng)該把連接表拆開成較小的幾個執(zhí)行邮丰，可讀性更高
如果一定需要連接很多表才能得到數(shù)據(jù)，那么意味著這是個糟糕的設(shè)計了
阿里規(guī)范中胚膊，建議多表聯(lián)查三張表以下
2眷蜓、索引不宜太多分瘾，一般5個以內(nèi)

索引并不是越多越好，雖其提高了查詢的效率吁系，但卻會降低插入和更新的效率德召；
索引可以理解為一個就是一張表，其可以存儲數(shù)據(jù)汽纤，其數(shù)據(jù)就要占空間上岗；
索引表的數(shù)據(jù)是排序的，排序也是要花時間的冒版；
insert或update時有可能會重建索引液茎，如果數(shù)據(jù)量巨大，重建將進(jìn)行記錄的重新排序辞嗡，所以建索引需要慎重考慮捆等，視具體情況來定；
一個表的索引數(shù)最好不要超過5個续室，若太多需要考慮一些索引是否有存在的必要栋烤；
十五、避免在索引列上使用內(nèi)置函數(shù)
1挺狰、反例

SELECT * FROM user WHERE DATE_ADD(birthday,INTERVAL 7 DAY) >=NOW();
1
2明郭、正例

SELECT * FROM user WHERE birthday >= DATE_ADD(NOW(),INTERVAL 7 DAY);
1
3买窟、理由

使用索引列上內(nèi)置函數(shù)，索引失效薯定。

十六始绍、組合索引
排序時應(yīng)按照組合索引中各列的順序進(jìn)行排序，即使索引中只有一個列是要排序的话侄，否則排序性能會比較差亏推。

create index IDX_USERNAME_TEL on user(deptid,position,createtime);
select username,tel from user where deptid= 1 and position = 'java開發(fā)' order by deptid,position,createtime desc;
1
2
實際上只是查詢出符合 deptid= 1 and position = 'java開發(fā)'條件的記錄并按createtime降序排序，但寫成order by createtime desc性能較差年堆。

十七吞杭、復(fù)合索引最左特性
1、創(chuàng)建復(fù)合索引

ALTER TABLE employee ADD INDEX idx_name_salary (name,salary)
1
2变丧、滿足復(fù)合索引的最左特性芽狗，哪怕只是部分，復(fù)合索引生效

SELECT * FROM employee WHERE NAME='哪吒編程'
1
3痒蓬、沒有出現(xiàn)左邊的字段童擎，則不滿足最左特性，索引失效

SELECT * FROM employee WHERE salary=5000
1
4谊却、復(fù)合索引全使用柔昼，按左側(cè)順序出現(xiàn) name,salary，索引生效

SELECT * FROM employee WHERE NAME='哪吒編程' AND salary=5000
1
5炎辨、雖然違背了最左特性捕透，但MySQL執(zhí)行SQL時會進(jìn)行優(yōu)化，底層進(jìn)行顛倒優(yōu)化

SELECT * FROM employee WHERE salary=5000 AND NAME='哪吒編程'
1
6碴萧、理由

復(fù)合索引也稱為聯(lián)合索引乙嘀，當(dāng)我們創(chuàng)建一個聯(lián)合索引的時候，如(k1,k2,k3)破喻，相當(dāng)于創(chuàng)建了（k1）虎谢、(k1,k2)和(k1,k2,k3)三個索引，這就是最左匹配原則曹质。

聯(lián)合索引不滿足最左原則婴噩，索引一般會失效。

十八羽德、優(yōu)化like語句
模糊查詢几莽，程序員最喜歡的就是使用like，但是like很可能讓你的索引失效宅静。

1章蚣、反例

select * from citys where name like '%大連' (不使用索引)
select * from citys where name like '%大連%' (不使用索引)
1
2
2、正例

select * from citys where name like '大連%' (使用索引) 姨夹。
1
3纤垂、理由

首先盡量避免模糊查詢矾策，如果必須使用，不采用全模糊查詢峭沦，也應(yīng)盡量采用右模糊查詢贾虽， 即like ‘…%’，是會使用索引的吼鱼；
左模糊like ‘%...’無法直接使用索引榄鉴，但可以利用reverse + function index的形式，變化成 like ‘…%’蛉抓；
全模糊查詢是無法優(yōu)化的，一定要使用的話建議使用搜索引擎剃诅。
十九巷送、使用explain分析你SQL執(zhí)行計劃
1、type

system：表僅有一行矛辕，基本用不到笑跛；
const：表最多一行數(shù)據(jù)配合，主鍵查詢時觸發(fā)較多聊品；
eq_ref：對于每個來自于前面的表的行組合飞蹂，從該表中讀取一行。這可能是最好的聯(lián)接類型翻屈，除了const類型陈哑；
ref：對于每個來自于前面的表的行組合，所有有匹配索引值的行將從這張表中讀壬炜簟惊窖；
range：只檢索給定范圍的行，使用一個索引來選擇行厘贼。當(dāng)使用=界酒、<>、>嘴秸、>=毁欣、<、<=岳掐、IS NULL凭疮、<=>、BETWEEN或者IN操作符岩四，用常量比較關(guān)鍵字列時哭尝，可以使用range；
index：該聯(lián)接類型與ALL相同剖煌，除了只有索引樹被掃描材鹦。這通常比ALL快逝淹，因為索引文件通常比數(shù)據(jù)文件小桶唐；
all：全表掃描栅葡；
性能排名：system > const > eq_ref > ref > range > index > all。
實際sql優(yōu)化中尤泽，最后達(dá)到ref或range級別欣簇。
2、Extra常用關(guān)鍵字

Using index：只從索引樹中獲取信息坯约，而不需要回表查詢熊咽；
Using where：WHERE子句用于限制哪一個行匹配下一個表或發(fā)送到客戶。除非你專門從表中索取或檢查所有行闹丐，如果Extra值不為Using where并且表聯(lián)接類型為ALL或index横殴，查詢可能會有一些錯誤。需要回表查詢卿拴。
Using temporary：mysql常建一個臨時表來容納結(jié)果衫仑，典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時；
二十堕花、一些其它優(yōu)化方式
1文狱、設(shè)計表的時候，所有表和字段都添加相應(yīng)的注釋缘挽。

2瞄崇、SQL書寫格式，關(guān)鍵字大小保持一致到踏，使用縮進(jìn)杠袱。

3、修改或刪除重要數(shù)據(jù)前窝稿，要先備份楣富。

4、很多時候用 exists 代替 in 是一個好的選擇

5伴榔、where后面的字段纹蝴，留意其數(shù)據(jù)類型的隱式轉(zhuǎn)換。

未使用索引

SELECT * FROM user WHERE NAME=110
1
（1） 因為不加單引號時踪少，是字符串跟數(shù)字的比較塘安，它們類型不匹配；

（2）MySQL會做隱式的類型轉(zhuǎn)換援奢，把它們轉(zhuǎn)換為數(shù)值類型再做比較兼犯；

6、盡量把所有列定義為NOT NULL

NOT NULL列更節(jié)省空間，NULL列需要一個額外字節(jié)作為判斷是否為 NULL的標(biāo)志位切黔。NULL列需要注意空指針問題砸脊，NULL列在計算和比較的時候，需要注意空指針問題纬霞。

7凌埂、偽刪除設(shè)計

8、數(shù)據(jù)庫和表的字符集盡量統(tǒng)一使用UTF8

（1）可以避免亂碼問題诗芜；

（2）可以避免瞳抓，不同字符集比較轉(zhuǎn)換，導(dǎo)致的索引失效問題伏恐；

9孩哑、select count(*) from table；

這樣不帶任何條件的count會引起全表掃描翠桦，并且沒有任何業(yè)務(wù)意義臭笆，是一定要杜絕的。

10秤掌、避免在where中對字段進(jìn)行表達(dá)式操作

（1）SQL解析時，如果字段相關(guān)的是表達(dá)式就進(jìn)行全表掃描 鹰霍；

（2）字段干凈無表達(dá)式闻鉴，索引生效；

11茂洒、關(guān)于臨時表

（1）避免頻繁創(chuàng)建和刪除臨時表孟岛，以減少系統(tǒng)表資源的消耗；

（2）在新建臨時表時督勺，如果一次性插入數(shù)據(jù)量很大渠羞，那么可以使用 select into 代替 create table，避免造成大量 log智哀；

（3）如果數(shù)據(jù)量不大次询，為了緩和系統(tǒng)表的資源，應(yīng)先create table瓷叫，然后insert屯吊；

（4）如果使用到了臨時表，在存儲過程的最后務(wù)必將所有的臨時表顯式刪除摹菠。先 truncate table 盒卸，然后 drop table ，這樣可以避免系統(tǒng)表的較長時間鎖定次氨；

12蔽介、索引不適合建在有大量重復(fù)數(shù)據(jù)的字段上，比如性別，排序字段應(yīng)創(chuàng)建索引

13虹蓄、去重distinct過濾字段要少

帶distinct的語句占用cpu時間高于不帶distinct的語句
當(dāng)查詢很多字段時犀呼，如果使用distinct，數(shù)據(jù)庫引擎就會對數(shù)據(jù)進(jìn)行比較武花，過濾掉重復(fù)數(shù)據(jù)
然而這個比較圆凰、過濾的過程會占用系統(tǒng)資源，如cpu時間
14体箕、盡量避免大事務(wù)操作专钉，提高系統(tǒng)并發(fā)能力

15、所有表必須使用Innodb存儲引擎

Innodb「支持事務(wù)累铅，支持行級鎖跃须，更好的恢復(fù)性」，高并發(fā)下性能更好娃兽，所以呢菇民，沒有特殊要求（即Innodb無法滿足的功能如：列存儲，存儲空間數(shù)據(jù)等）的情況下投储，所有表必須使用Innodb存儲引擎第练。

16、盡量避免使用游標(biāo)

因為游標(biāo)的效率較差玛荞，如果游標(biāo)操作的數(shù)據(jù)超過1萬行娇掏，那么就應(yīng)該考慮改寫。
————————————————
版權(quán)聲明：本文為CSDN博主「String勋眯、code」的原創(chuàng)文章婴梧，遵循CC 4.0 BY-SA版權(quán)協(xié)議，轉(zhuǎn)載請附上原文出處鏈接及本聲明客蹋。
原文鏈接：https://blog.csdn.net/Lxl1418/article/details/126717598