一、查詢SQL盡量不要使用select *，而是具體字段 1、反例
SELECT * FROM user 2、正例
SELECT id,username,tel FROM user 3、理由
節(jié)省資源、減少網(wǎng)絡(luò)開銷。 注意：為節(jié)省時間，下面的樣例字段都用*代替了。
二、避免在where子句中使用 or 來連接條件 1、反例
SELECT * FROM user WHERE id=1 OR salary=5000 2、正例
（1）使用union all
SELECT * FROM user WHERE id=1 UNION ALLSELECT * FROM user WHERE salary=5000 （2）分開兩條sql寫
SELECT * FROM user WHERE id=1SELECT * FROM user WHERE salary=5000 3、理由
對于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、理由
因?yàn)橐嬖谔幚聿樵兒瓦B接時會逐個比較字符串中每一個字符； 四、使用varchar代替char 1、反例
`address` char(100) DEFAULT NULL COMMENT '地址' 2、正例
`address` varchar(100) DEFAULT NULL COMMENT '地址' 3、理由
varchar變長字段按數(shù)據(jù)內(nèi)容實(shí)際長度存儲，存儲空間小，可以節(jié)省存儲空間； 其次對于查詢來說，在一個相對較小的字段內(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時，按實(shí)際長度存儲。
2、char的效率比varchar2的效率稍高。
3、何時用char，何時用varchar2?
char和varchar2是一對矛盾的統(tǒng)一體，兩者是互補(bǔ)的關(guān)系，varchar2比char節(jié)省空間，在效率上比char會稍微差一點(diǎn)，既想獲取效率，就必須犧牲一點(diǎn)空間，這就是我們在數(shù)據(jù)庫設(shè)計(jì)上常說的“以空間換效率”。
varchar2雖然比char節(jié)省空間，但是假如一個varchar2列經(jīng)常被修改，而且每次被修改的數(shù)據(jù)的長度不同，這會引起“行遷移”現(xiàn)象，而這造成多余的I/O，是數(shù)據(jù)庫設(shè)計(jì)中要盡力避免的，這種情況下用char代替varchar2會更好一些。char中還會自動補(bǔ)齊空格，因?yàn)槟?code style="margin: 0px 2px; padding: 2px 4px; outline: 0px; user-select: initial !important; max-width: 100%; box-sizing: border-box !important; overflow-wrap: break-word !important; border-radius: 4px; background-color: rgba(27, 31, 35, 0.05); font-family: "Operator Mono", Consolas, Monaco, Menlo, monospace; word-break: break-all; color: rgb(40, 202, 113);">insert到一個char字段自動補(bǔ)充了空格的,但是select后空格沒有刪除，因此char類型查詢的時候一定要記得使用trim，這是寫本文章的原因。
如果開發(fā)人員細(xì)化使用rpad()技巧將綁定變量轉(zhuǎn)換為某種能與char字段相比較的類型（當(dāng)然，與截斷trim數(shù)據(jù)庫列相比，填充綁定變量的做法更好一些，因?yàn)閷α袘?yīng)用函數(shù)trim很容易導(dǎo)致無法使用該列上現(xiàn)有的索引），可能必須考慮到經(jīng)過一段時間后列長度的變化。如果字段的大小有變化，應(yīng)用就會受到影響，因?yàn)樗仨毿薷淖侄螌挾取?/p>
正是因?yàn)橐陨显颍▽挼拇鎯臻g可能導(dǎo)致表和相關(guān)索引比平常大出許多，還伴隨著綁定變量問題，所以無論什么場合都要避免使用char類型。
六、where中使用默認(rèn)值代替null 1、反例
SELECT * FROM user WHERE age IS NOT NULL 2、正例
SELECT * FROM user WHERE age>0 3、理由
并不是說使用了is null或者 is not null就會不走索引了，這個跟mysql版本以及查詢成本都有關(guān)； 如果mysql優(yōu)化器發(fā)現(xiàn)，走索引比不走索引成本還要高，就會放棄索引，這些條件 !=，<>，is null，is not null經(jīng)常被認(rèn)為讓索引失效； 其實(shí)是因?yàn)橐话闱闆r下，查詢的成本高，優(yōu)化器自動放棄索引的； 如果把null值，換成默認(rèn)值，很多時候讓走索引成為可能，同時，表達(dá)意思也相對清晰一點(diǎn)； 七、避免在where子句中使用!=或<>操作符 1、反例
SELECT * FROM user WHERE salary!=5000SELECT * FROM user WHERE salary<>5000 2、理由
應(yīng)盡量避免在where子句中使用!=或<>操作符，否則引擎將放棄使用索引而進(jìn)行全表掃描 實(shí)現(xiàn)業(yè)務(wù)優(yōu)先，實(shí)在沒辦法，就只能使用，并不是不能使用 八、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ù)比較少，所以性能相對會好一點(diǎn)；
使用了左連接，左邊表數(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 jobhaving job ='develop' or job = 'test'; 2、正例
先過濾，后分組
select job，avg（salary） from employee where job ='develop' or job = 'test' group by job; 3、理由
可以在執(zhí)行到該語句前，把不需要的記錄過濾掉
十、清空表時優(yōu)先使用truncate truncate table在功能上與不帶 where子句的 delete語句相同：二者均刪除表中的全部行。但 truncate table比 delete
delete語句每次刪除一行，并在事務(wù)日志中為所刪除的每行記錄一項(xiàng)。truncate table通過釋放存儲表數(shù)據(jù)所用的數(shù)據(jù)頁來刪除數(shù)據(jù)，并且只在事務(wù)日志中記錄頁的釋放。
truncate table刪除表中的所有行，但表結(jié)構(gòu)及其列、約束、索引等保持不變。新行標(biāo)識所用的計(jì)數(shù)值重置為該列的種子。如果想保留標(biāo)識計(jì)數(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， 沒有limit的話，還會繼續(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ù)，可能造成鎖表，會有lock wait timeout exceed的錯誤，所以建議分批操作。
十二、UNION操作符 UNION在進(jìn)行表鏈接后會篩選掉重復(fù)的記錄，所以在表鏈接后會對所產(chǎn)生的結(jié)果集進(jìn)行排序運(yùn)算，刪除重復(fù)的記錄再返回結(jié)果。實(shí)際大部分應(yīng)用中是不會產(chǎn)生重復(fù)的記錄，最常見的是過程表與歷史表UNION。如：
select username,tel from userunionselect departmentname from department 這個SQL在運(yùn)行時先取出兩個表的結(jié)果，再用排序空間進(jìn)行排序刪除重復(fù)的記錄，最后返回結(jié)果集，如果表數(shù)據(jù)量大的話可能會導(dǎo)致用磁盤進(jìn)行排序。推薦方案：采用UNION ALL操作符替代UNION，因?yàn)?code style="margin: 0px 2px; padding: 2px 4px; outline: 0px; user-select: initial !important; max-width: 100%; box-sizing: border-box !important; overflow-wrap: break-word !important; border-radius: 4px; background-color: rgba(27, 31, 35, 0.05); font-family: "Operator Mono", Consolas, Monaco, Menlo, monospace; word-break: break-all; color: rgb(40, 202, 113);">UNION ALL操作只是簡單的將兩個結(jié)果合并后就返回。
十三、批量插入性能提升 1、多條提交
INSERT INTO user (id,username) VALUES(1,'哪吒編程');INSERT INTO user (id,username) VALUES(2,'妲己'); 2、批量提交
INSERT INTO user (id,username) VALUES(1,'哪吒編程'),(2,'妲己'); 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è)計(jì)了 阿里規(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(); 2、正例
SELECT * FROM user WHERE birthday >= DATE_ADD(NOW(),INTERVAL 7 DAY); 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; 實(shí)際上只是查詢出符合 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) 2、滿足復(fù)合索引的最左特性，哪怕只是部分，復(fù)合索引生效
SELECT * FROM employee WHERE NAME='哪吒編程' 3、沒有出現(xiàn)左邊的字段，則不滿足最左特性，索引失效
SELECT * FROM employee WHERE salary=5000 4、復(fù)合索引全使用，按左側(cè)順序出現(xiàn) name,salary，索引生效
SELECT * FROM employee WHERE NAME='哪吒編程' AND salary=5000 5、雖然違背了最左特性，但MySQL執(zhí)行SQL時會進(jìn)行優(yōu)化，底層進(jìn)行顛倒優(yōu)化
SELECT * FROM employee WHERE salary=5000 AND NAME='哪吒編程' 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 '%大連%' (不使用索引) 2、正例
select * from citys where name like '大連%' (使用索引) 。 3、理由
首先盡量避免模糊查詢，如果必須使用，不采用全模糊查詢，也應(yīng)盡量采用右模糊查詢， 即like ‘…%’，是會使用索引的； 左模糊like ‘%...’無法直接使用索引，但可以利用reverse + function index的形式，變化成 like ‘…%’； 全模糊查詢是無法優(yōu)化的，一定要使用的話建議使用搜索引擎。 十九、使用explain分析你SQL執(zhí)行計(jì)劃 1、type
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快，因?yàn)樗饕募ǔ１葦?shù)據(jù)文件小；性能排名：system > const > eq_ref > ref > range > index > all。實(shí)際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è)計(jì)表的時候，所有表和字段都添加相應(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） 因?yàn)椴患訂我枙r，是字符串跟數(shù)字的比較，它們類型不匹配；
（2）MySQL會做隱式的類型轉(zhuǎn)換，把它們轉(zhuǎn)換為數(shù)值類型再做比較；
6、盡量把所有列定義為NOT NULL
NOT NULL列更節(jié)省空間，NULL列需要一個額外字節(jié)作為判斷是否為 NULL的標(biāo)志位。NULL列需要注意空指針問題，NULL列在計(jì)算和比較的時候，需要注意空指針問題。
7、偽刪除設(shè)計(jì)
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)
因?yàn)橛螛?biāo)的效率較差，如果游標(biāo)操作的數(shù)據(jù)超過1萬行，那么就應(yīng)該考慮改寫。
400 186 1886
