MySQL大表優(yōu)化

索引(Index)相關(guān)及索引原理(B樹诊沪，B+樹）

一是目、優(yōu)化細則

1. 在 where 子句中使用 != 或 <> 操作符，會導(dǎo)致引擎放棄使用索引而進行全表掃描。SQL 中竞膳，不等于操作符會限制索引蹄殃，造成全表掃描，即使比較的字段上有索引袋倔。MySQL 只有對以下操作符才使用索引：<雕蔽，<=，=宾娜，>批狐，>=，BETWEEN前塔，IN嚣艇，以及某些方式的 LIKE ('a%')。

模糊查詢效率很低原因：like 本身效率就比較低华弓，應(yīng)該盡量避免查詢條件使用 like食零。對于like ‘%...%’(全模糊)這樣的條件，是無法使用索引的寂屏，全表掃描自然效率很低贰谣；另外，由于匹配算法的關(guān)系迁霎，模糊查詢的字段長度越大吱抚，模糊查詢效率越低。
解決辦法：首先盡量避免模糊查詢考廉，如果因為業(yè)務(wù)需要一定要使用模糊查詢频伤，則至少保證不要使用全模糊查詢，對于【右模糊查詢like ‘…%’是會使用索引的芝此；左模糊like ‘%...’無法直接使用索引】憋肖，但可以利用 reverse + function index 的形式，變化成 like ‘…%’婚苹。全模糊是無法優(yōu)化的岸更，一定要的話考慮用搜索引擎。出于降低數(shù)據(jù)庫服務(wù)器負載的考慮膊升，盡可能的減少數(shù)據(jù)庫模糊查詢怎炊。

2. 注意范圍查詢語句
   對于聯(lián)合索引來說，如果存在范圍查詢，比如between评肆、>债查、<等條件時，會造成后面的索引字段失效瓜挽。

3. 對查詢進行優(yōu)化盹廷，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引久橙。如果排序字段沒有用到索引俄占，就盡量少排序。根據(jù)實際情況進行調(diào)整淆衷，因為有時索引太多也會降低性能缸榄。

• order by的列盡量被索引。order by的列如果被索引祝拯，性能也會更好
• 不使用order by rand() limit ~MySQL的隨機抽取實現(xiàn)方法

select id from table order by rand() limit 1000;

上面的SQL語句甚带，可優(yōu)化為：

select id from table  t1 
join  (select rand() * (select max(id) from table ) as nid) t2 on t1.id > t2.nid
limit 1000;

4. 在 where 子句中對字段進行 null 值判斷，會導(dǎo)致引擎放棄使用索引而進行全表掃描佳头，如：

理由：并不是說使用了 is null 或者 is not null 就會不走索引了鹰贵，這個跟 MySQL 版本以及查詢成本都有關(guān)。如果 MySQL 優(yōu)化器發(fā)現(xiàn)畜晰，走索引比不走索引成本還要高砾莱，肯定會放棄索引，這些條件 !=凄鼻，>腊瑟，is null，is not null 經(jīng)常被認為讓索引失效块蚌，其實是因為一般情況下闰非，查詢的成本高，優(yōu)化器自動放棄索引的峭范。如果把 null 值财松，換成默認值，很多時候讓走索引成為可能纱控，同時辆毡，表達意思會相對清晰一點√鸷Γ【見第21點】

5. 在 where 子句中使用參數(shù)舶掖，也會導(dǎo)致全表掃描。因為SQL只有在運行時才會解析局部變量尔店，但優(yōu)化程序不能將訪問計劃的選擇推遲到運行時眨攘；它必須在編譯時進行選擇主慰。然而，如果在編譯時建立訪問計劃鲫售，變量的值還是未知的共螺，因而無法作為索引選擇的輸入項。如下面語句將進行全表掃描：

6. 在 where 子句中對字段進行函數(shù)情竹、算術(shù)運算或其他表達式運算藐不，會導(dǎo)致引擎放棄使用索引而進行全表掃描。如：

算術(shù)運算

函數(shù)操作

7. 為列選擇合適的數(shù)據(jù)類型鲤妥，而且要避免隱式類型轉(zhuǎn)換

• where子句中出現(xiàn)column字段的類型和傳入的參數(shù)類型不一致的時候會發(fā)生類型轉(zhuǎn)換哩掺，建議先確定where中的參數(shù)類型王财。
• 能用TINYINT就不用SMALLINT，能用SMALLINE就不用INT酿雪，磁盤和內(nèi)存消耗越小越好铸抑。

【如果字段類型是字符串贡耽，where 時一定用引號括起來，否則索引失效】
反例：

select * from user where userid = 123; 

正例：

select * from user where userid = '123'; 

為什么第一條語句未加單引號就不走索引了鹊汛？這是因為不加單引號時蒲赂，是字符串跟數(shù)字的比較，它們類型不匹配刁憋，MySQL 會做隱式的類型轉(zhuǎn)換滥嘴，把它們轉(zhuǎn)換為浮點數(shù)再做比較。

8. 結(jié)果集允許重復(fù)的話至耻，盡量用union all代替union
   union 和 union all 的差異若皱，主要是前者不管檢索結(jié)果有沒有重復(fù)，都會嘗試進行合并尘颓，然后在輸出最終結(jié)果前進行排序走触、過濾操作，增加大量的CPU運算疤苹，加大資源消耗及延遲互广。而UNINON ALL不去重，效率高于UNION卧土。當然惫皱，union all 的前提條件是兩個結(jié)果集沒有重復(fù)數(shù)據(jù)∮容海或者如果結(jié)果集允許重復(fù)的話旅敷，盡量使用union all 代替 union 缝裁。

9. 如果限制條件中其他字段沒有索引扫皱，盡量少用 or足绅。or兩邊的字段中，如果有一個不是索引字段韩脑，而其他條件也不是索引字段氢妈，會造成該查詢不走索引的情況。應(yīng)盡量避免在 where 子句中使用 or 來連接條件段多，否則將導(dǎo)致引擎放棄使用索引而進行全表掃描首量。很多時候使用 union all 或者是 union（必要的時候）的方式來代替“or” 會得到更好的效果。新建一個 user 表进苍，它有一個普通索引 userId加缘，表結(jié)構(gòu)如下：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

假設(shè)現(xiàn)在需要查詢 userId 為 1 或者年齡為 18 歲的用戶，很容易有以下 SQL觉啊。
反例：

select * from user where userid=1 or age =18

正例：

//使用union all 
select * from user where userid=1 
union all 
select * from user where age = 18

//或者分開兩條sql寫：
select * from user where userid=1
select * from user where age = 18
理由：使用 or 可能會使索引失效拣宏，從而全表掃描。

對于 or+沒有索引的 age 這種情況杠人，假設(shè)它走了 userId 的索引勋乾，但是走到 age 查詢條件時，它還得全表掃描嗡善，也就是需要三步過程：全表掃描+索引掃描+合并辑莫，如果它一開始就走全表掃描，直接一遍掃描就完事罩引。MySQL 是有優(yōu)化器的各吨，處于效率與成本考慮，遇到 or 條件袁铐，索引可能失效揭蜒，看起來也合情合理。

10. 將大的 delete昭躺，update 或者 insert 查詢變成多個小查詢
    

    為了更優(yōu)的性能以及更好的數(shù)據(jù)控制忌锯，應(yīng)該將大的 sql 寫成多個小查詢。避免同時修改或刪除過多數(shù)據(jù)领炫，因為會造成 CPU 利用率過高偶垮，從而影響別人對數(shù)據(jù)庫的訪問。

反例：

//一次刪除10萬或者100萬+帝洪？
delete from user where id <100000;
//或者采用單一循環(huán)操作似舵，效率低，時間漫長
for（User user：list）{
   delete from user葱峡； 
}

正例：

//分批進行刪除砚哗，如每次500
delete user where id<500
delete product where id>=500 and id<1000;

理由：一次性刪除太多數(shù)據(jù)，可能會有 lock wait timeout exceed 的錯誤砰奕，所以建議分批操作蛛芥。

【關(guān)于 delete 優(yōu)化】一個 delete 語句執(zhí)行了2個多小時了提鸟，還沒執(zhí)行完，能不能把這個 SQL 回滾仅淑？答案是不能称勋，如果直接回滾，結(jié)果會更慘涯竟。一個 SQL 能不能回滾赡鲜，或者說進行到了什么程度？不建議暴力重啟庐船，先執(zhí)行一個“show engine innodb status\G”語句银酬，這樣能看到執(zhí)行時間。如果沒有運行狀態(tài)筐钟，就可以 Kill 掉揩瞪。

11. 如果插入數(shù)據(jù)過多，考慮批量插入

反例：

for(User u :list){
 insert into user(name,age) values(#name#,#age#)   
}

正例：

//一次500批量插入盗棵，分批進行
insert into user(name,age) values
<foreach collection="list" item="item" index="index" separator=",">
    (#{item.name},#{item.age})
</foreach>

理由：批量插入性能好壮韭，更加省時間北发。
打個比喻：假如需要搬一萬塊磚到樓頂纹因。有一個電梯，電梯一次可以放適量的磚(最多放 500)琳拨，可以選擇一次運送一塊磚瞭恰，也可以一次運送 500 塊磚，哪個時間消耗大?

12. select語句務(wù)必指明字段名稱狱庇，盡量避免使用"select * "惊畏。因為它會進行全表掃描，不能有效利用索引密任，增加很多不必要的消耗（CPU颜启、IO、內(nèi)存浪讳、網(wǎng)絡(luò)帶寬）缰盏，增大了數(shù)據(jù)庫服務(wù)器的負擔，以及它與應(yīng)用程序客戶端之間的網(wǎng)絡(luò)IO開銷淹遵。當表結(jié)構(gòu)發(fā)生改變時口猜，前端也需要更新。所以要求直接在select后面接上字段名透揣。

13. Inner join 济炎、left join、right join辐真，優(yōu)先使用 Inner join须尚。如果是 left join崖堤，左邊表結(jié)果盡量小。Inner join 內(nèi)連接耐床，在兩張表進行連接查詢時倘感，只保留兩張表中完全匹配的結(jié)果集。left join 在兩張表進行連接查詢時咙咽，會返回左表所有的行老玛，即使在右表中沒有匹配的記錄。right join 在兩張表進行連接查詢時钧敞，會返回右表所有的行蜡豹，即使在左表中沒有匹配的記錄。都滿足 SQL 需求的前提下溉苛，推薦優(yōu)先使用 Inner join(內(nèi)連接)镜廉，如果要使用 left join，左邊表數(shù)據(jù)結(jié)果盡量小愚战，如果有條件的盡量放到左邊處理娇唯。

• MySQL中沒有full join，可以用以下方式來解決：

select * from A 
left join B on B.name = A.name where B.name is null
union all 
select * from B;

• 盡量使用inner join寂玲，避免left join：
  參與聯(lián)合查詢的表至少為2張表塔插，一般都存在大小之分。如果連接方式是inner join拓哟，在沒有其他過濾條件的情況下MySQL會自動選擇小表作為驅(qū)動表想许；但是left join在驅(qū)動表的選擇上遵循的是左邊驅(qū)動右邊的原則，即left join左邊的表名為驅(qū)動表断序。
• 合理利用索引：
  被驅(qū)動表的索引字段作為on的限制字段流纹。
• 利用小表去驅(qū)動大表
  從原理圖能夠直觀的看出如果能夠減少驅(qū)動表的話，減少嵌套循環(huán)中的循環(huán)次數(shù)违诗，以減少 IO總量及CPU運算的次數(shù)漱凝。
• 巧用STRAIGHT_JOIN：
  inner join是由MySQL選擇驅(qū)動表，但是有些特殊情況需要選擇另個表作為驅(qū)動表诸迟，比如有g(shù)roup by茸炒、order by等「Using filesort」、「Using temporary」時亮蒋。STRAIGHT_JOIN來強制連接順序扣典，在STRAIGHT_JOIN左邊的表名就是驅(qū)動表，右邊則是被驅(qū)動表慎玖。在使用STRAIGHT_JOIN有個前提條件是該查詢是內(nèi)連接贮尖，也就是inner join。其他鏈接不推薦使用STRAIGHT_JOIN趁怔，否則可能造成查詢結(jié)果不準確湿硝。
  這個方式有時能減少3倍的時間薪前。

反例：

select * from tab a left join tab1 b on a.size = b.size where a.id>2; 

正例：

select * from (select * from tab where id >2) a left join tab1 b on a.size = b.size; 

理由如下：
如果 inner join 是等值連接，或許返回的行數(shù)比較少关斜，所以性能相對會好一點示括。
同理，使用了左連接痢畜，左邊表數(shù)據(jù)結(jié)果盡量小垛膝，條件盡量放到左邊處理，意味著返回的行數(shù)可能比較少丁稀。

【不要有超過 5 個以上的表連接】連表越多吼拥，編譯的時間和開銷也就越大。把連接表拆開成較小的幾個執(zhí)行线衫，可讀性更高凿可。如果一定需要連接很多表才能得到數(shù)據(jù)，那么意味著設(shè)計非常糟糕授账。

14. SQL語句中 in 和 not in 也要慎用枯跑，否則會導(dǎo)致全表掃描。MySQL對于in做了相應(yīng)的優(yōu)化白热，即將in中的常量全部存儲在一個數(shù)組里面敛助，而且這個數(shù)組是排好序的。但是如果數(shù)值較多棘捣，產(chǎn)生的消耗也是比較大的辜腺。對于連續(xù)的數(shù)值，能用between就不要用in乍恐，再或者使用連接來替換。

15. 區(qū)分 in 和 exists测砂、not in 和not exists

區(qū)分 in 和 exists 主要是造成了驅(qū)動順序的改變（這是性能變化的關(guān)鍵）茵烈。如果是exists，那么以外層表為驅(qū)動表砌些，先被訪問呜投；如果是IN，那么先執(zhí)行子查詢存璃。所以IN適合于外表大而內(nèi)表小的情況仑荐；exists適合于外表小而內(nèi)表大的情況。

關(guān)于 not in 和 not exists纵东，推薦使用not exists粘招，不僅僅是效率問題，not in 可能存在邏輯問題偎球。如何高效的寫出一個替代 not exists 的SQL語句洒扎？

原SQL語句：

select colname … from tableA 
where a.id not in (select b.id from tableB)

高效的SQL語句：

select colname … from tableA 
left join tableB on a.id = b.id 
where b.id is null

取出的結(jié)果集為A表不在B表中的數(shù)據(jù)辑甜。

【exist&in 的合理利用】

假設(shè)表 A 表示某企業(yè)的員工表，表B表示部門表袍冷，查詢所有部門的所有員工磷醋，很容易有以下 SQL：

select * from A where deptId in (select deptId from B);

這樣寫等價于：

先查詢部門表B
select deptId from B
再由部門deptId，查詢A的員工
select * from A where A.deptId = B.deptId

可以抽象成這樣的一個循環(huán)：

List<> resultSet ;
    for(int i=0;i<B.length;i++) {
          for(int j=0;j<A.length;j++) {
          if(A[i].id==B[j].id) {
             resultSet.add(A[i]);
             break;
          }
       }
    }

顯然胡诗，除了使用 in邓线，也可以用 exists 實現(xiàn)一樣的查詢功能，如下：

select * from A where exists (select 1 from B where A.deptId = B.deptId);

因為 exists 查詢的理解就是煌恢，先執(zhí)行主查詢褂痰，獲得數(shù)據(jù)后，再放到子查詢中做條件驗證症虑，根據(jù)驗證結(jié)果(true 或者 false)缩歪，來決定主查詢的數(shù)據(jù)結(jié)果是否得意保留。那么谍憔，這樣寫就等價于：

select * from A 先從A表做循環(huán)
select * from B where A.deptId = B.deptId 再從B表做循環(huán)匪蝙。

同理，可以抽象成這樣一個循環(huán)：

List<> resultSet ;
    for(int i=0;i<A.length;i++) {
          for(int j=0;j<B.length;j++) {
          if(A[i].deptId==B[j].deptId) {
             resultSet.add(A[i]);
             break;
          }
       }
    }

數(shù)據(jù)庫最費勁的就是跟程序鏈接釋放习贫。假設(shè)鏈接了兩次逛球，每次做上百萬次的數(shù)據(jù)集查詢，查完就走苫昌，這樣就只做了兩颤绕。相反建立了上百萬次鏈接，申請鏈接釋放反復(fù)重復(fù)祟身，這樣系統(tǒng)就受不了了奥务。

即 MySQL 優(yōu)化原則，就是小表驅(qū)動大表袜硫，小的數(shù)據(jù)集驅(qū)動大的數(shù)據(jù)集氯葬，從而讓性能更優(yōu)。因此婉陷，我們要選擇最外層循環(huán)小的帚称，也就是，如果 B 的數(shù)據(jù)量小于 A秽澳，適合使用 in闯睹，如果 B 的數(shù)據(jù)量大于 A，即適合選擇 exist担神。

16. 【推薦】in操作能避免則避免楼吃，若實在避免不了，需要仔細評估in后邊的集合元素數(shù)量，控制在1000個之內(nèi)所刀。

17. 給整型字段設(shè)置長度并無意義衙荐，int(1)與int(11)通常情況下沒有任何區(qū)別。

18. 必要時可以使用force index來強制查詢走某個索引
    有的時候MySQL優(yōu)化器采取它認為合適的索引來檢索SQL語句浮创，但是可能它所采用的索引并不是我們想要的忧吟。這時就可以采用force index來強制優(yōu)化器使用我們制定的索引。

19. 【強制】不要使用count(列名)或count(常量)來替代count(*)斩披，count(*)是SQL92定義的標準統(tǒng)計行數(shù)的語法溜族，跟數(shù)據(jù)庫無關(guān)，跟NULL和非NULL無關(guān)垦沉。

說明：count(*)會統(tǒng)計值為NULL的行煌抒，而count(列名)不會統(tǒng)計此列為NULL值的行。

20. 不要寫一些沒有意義的查詢厕倍，如需要生成一個空表結(jié)構(gòu)：
    select col1,col2 into #t from t where 1=0
    這類代碼不會返回任何結(jié)果集寡壮，但是會消耗系統(tǒng)資源的，應(yīng)改成這樣：
    create table #t(...)

21. 并不是所有索引對查詢都有效讹弯，SQL是根據(jù)表中數(shù)據(jù)來進行查詢優(yōu)化的况既，當索引列有大量數(shù)據(jù)重復(fù)時，SQL查詢可能不會去利用索引组民。如一表中有字段sex棒仍，male、female幾乎各一半臭胜，那么即使在sex上建了索引也對查詢效率起不了作用莫其。

22. 索引并不是越多越好

• 索引并不是越多越好，索引雖然提高了查詢的效率耸三，但是也降低了插入和更新的效率乱陡。
• insert 或 update 時有可能會重建索引，所以建索引需要慎重考慮吕晌，視具體情況來定蛋褥。
• 一個表的索引數(shù)最好不要超過 6 個，若太多需要考慮一些索引是否沒有存在的必要睛驳。

23. 應(yīng)盡可能的避免更新 clustered 索引數(shù)據(jù)列，因為 clustered 索引數(shù)據(jù)列的順序就是表記錄的物理存儲順序膜廊，一旦該列值改變將導(dǎo)致整個表記錄的順序的調(diào)整乏沸，會耗費相當大的資源。若應(yīng)用系統(tǒng)需要頻繁更新 clustered 索引數(shù)據(jù)列爪瓜，那么需要考慮是否應(yīng)將該索引建為 clustered 索引蹬跃。

24. 盡量使用數(shù)字型字段，若只含數(shù)值信息的字段盡量不要設(shè)計為字符型，否則會降低查詢和連接的性能蝶缀，并會增加存儲開銷丹喻。這是因為引擎在處理查詢和連接時會逐個比較字符串中每一個字符，而對于數(shù)字型而言只需要比較一次就夠了翁都。

反例：

king_id` varchar(20) NOT NULL COMMENT '守護者Id'

正例：

`king_id` int(11) NOT NULL COMMENT '守護者Id'`

25. 盡可能的使用 varchar/nvarchar 代替 char/nchar 碍论。因為首先變長字段存儲空間小，可以節(jié)省存儲空間柄慰，其次對于查詢來說鳍悠，在一個相對較小的字段內(nèi)搜索效率顯然要高些。

反例：

`deptName` char(100) DEFAULT NULL COMMENT '部門名稱' 

正例：

`deptName` varchar(100) DEFAULT NULL COMMENT '部門名稱' 

26. 盡量使用表變量來代替臨時表坐搔。如果表變量包含大量數(shù)據(jù)藏研，請注意索引非常有限（只有主鍵索引）。

27. 避免頻繁創(chuàng)建和刪除臨時表概行，以減少系統(tǒng)表資源的消耗蠢挡。

28. 臨時表并不是不可使用，適當?shù)厥褂盟鼈兛梢允鼓承├谈行У拭Γ缫堤ぃ斝枰貜?fù)引用大型表或常用表中的某個數(shù)據(jù)集時。但是消略，對于一次性事件堡称，最好使用導(dǎo)出表。

29. 在新建臨時表時艺演，如果一次性插入數(shù)據(jù)量很大却紧，那么可以使用 select into 代替 create table，避免造成大量 log 胎撤，以提高速度晓殊；如果數(shù)據(jù)量不大，為了緩和系統(tǒng)表的資源伤提，應(yīng)先create table巫俺，然后insert。

30. 如果使用到了臨時表肿男，在存儲過程的最后務(wù)必將所有的臨時表顯式刪除介汹，先 truncate table ，然后 drop table 舶沛，這樣可以避免系統(tǒng)表的較長時間鎖定嘹承。

31. 盡量避免使用游標，因為游標的效率較差如庭，如果游標操作的數(shù)據(jù)超過1萬行叹卷，那么就應(yīng)該考慮改寫。

32. 使用基于游標的方法或臨時表方法之前，應(yīng)先尋找基于集的解決方案來解決問題骤竹，基于集的方法通常更有效帝牡。

33. 與臨時表一樣，游標并不是不可使用蒙揣。對小型數(shù)據(jù)集使用 FAST_FORWARD 游標通常要優(yōu)于其他逐行處理方法靶溜，尤其是在必須引用幾個表才能獲得所需的數(shù)據(jù)時。在結(jié)果集中包括“合計”的例程通常要比使用游標執(zhí)行的速度快鸣奔。如果開發(fā)時間允許墨技，基于游標的方法和基于集的方法都可以嘗試一下，看哪一種方法的效果更好挎狸。

34. 在所有的存儲過程和觸發(fā)器的開始處設(shè)置 SET NOCOUNT ON 扣汪，在結(jié)束時設(shè)置 SET NOCOUNT OFF 。無需在執(zhí)行存儲過程和觸發(fā)器的每個語句后向客戶端發(fā)送 DONE_IN_PROC 消息锨匆。

35. 盡量避免向客戶端返回大數(shù)據(jù)量崭别，若數(shù)據(jù)量過大，應(yīng)該考慮相應(yīng)需求是否合理恐锣。

36. 盡量避免大事務(wù)操作茅主，提高系統(tǒng)并發(fā)能力。

37. 使用EXPLAIN分析 SQL 計劃土榴。EXPLAIN可以檢查索引使用情況以及掃描的行诀姚。日常開發(fā)寫 SQL 的時候，盡量養(yǎng)成用 explain 分析 SQL 的習慣玷禽，尤其是走不走索引這一塊赫段。

38. 慎用 distinct 關(guān)鍵字

distinct 關(guān)鍵字一般用來過濾重復(fù)記錄，以返回不重復(fù)的記錄矢赁。在查詢一個字段或者很少字段的情況下使用時糯笙，給查詢帶來優(yōu)化效果。但是在字段很多的時候使用撩银，卻會大大降低查詢效率给涕。

反例：

select distinct * from user; 

正例：

select distinct name from user; 

理由：帶 distinct 的語句 CPU 時間和占用時間都高于不帶 distinct 的語句。因為當查詢很多字段時额获，如果使用 distinct够庙，數(shù)據(jù)庫引擎就會對數(shù)據(jù)進行比較，過濾掉重復(fù)數(shù)據(jù)抄邀，然而這個比較首启、過濾的過程會占用系統(tǒng)資源，CPU 時間撤摸。

【強制】count(distinct col) 計算該列除NULL之外的不重復(fù)行數(shù)，注意 count(distinct col1, col2) 如果其中一列全為NULL，那么即使另一列有不同的值准夷，也返回為0钥飞。

39. 【強制】當某一列的值全是NULL時，count(col)的返回結(jié)果為0衫嵌，但sum(col)的返回結(jié)果為NULL读宙，因此使用sum()時需注意NPE問題。

正例：可以使用如下方式來避免sum的NPE問題：SELECT IF(ISNULL(SUM(g)),0,SUM(g)) FROM table;

40. 【強制】使用ISNULL()來判斷是否為NULL值楔绞。

說明：NULL與任何值的直接比較都為NULL结闸。

1） NULL<>NULL的返回結(jié)果是NULL，而不是false酒朵。

2） NULL=NULL的返回結(jié)果是NULL桦锄，而不是true。

3） NULL<>1的返回結(jié)果是NULL蔫耽，而不是true结耀。

41. 【強制】不得使用外鍵與級聯(lián)，一切外鍵概念必須在應(yīng)用層解決匙铡。

說明：以學(xué)生和成績的關(guān)系為例图甜，學(xué)生表中的student_id是主鍵，那么成績表中的student_id則為外鍵鳖眼。如果更新學(xué)生表中的student_id黑毅，同時觸發(fā)成績表中的student_id更新，即為級聯(lián)更新钦讳。外鍵與級聯(lián)更新適用于單機低并發(fā)矿瘦，不適合分布式、高并發(fā)集群蜂厅；級聯(lián)更新是強阻塞匪凡，存在數(shù)據(jù)庫更新風暴的風險；外鍵影響數(shù)據(jù)庫的插入速度掘猿。

42. 【強制】禁止使用存儲過程病游，存儲過程難以調(diào)試和擴展，更沒有移植性稠通。

43. 【強制】數(shù)據(jù)訂正（特別是刪除衬衬、修改記錄操作）時，要先select改橘，避免出現(xiàn)誤刪除滋尉，確認無誤才能執(zhí)行更新語句。

44. 【參考】如果有國際化需要飞主，所有的字符存儲與表示狮惜，均以utf-8編碼高诺，注意字符統(tǒng)計函數(shù)的區(qū)別。

說明：
SELECT LENGTH("輕松工作")碾篡； 返回為12
SELECT CHARACTER_LENGTH("輕松工作")虱而； 返回為4

如果需要存儲表情，那么選擇utf8mb4來進行存儲开泽，注意它與utf-8編碼的區(qū)別牡拇。

45. 【參考】 TRUNCATE TABLE 比 DELETE 速度快，且使用的系統(tǒng)和事務(wù)日志資源少穆律，但TRUNCATE無事務(wù)且不觸發(fā)trigger惠呼，有可能造成事故，故不建議在開發(fā)代碼中使用此語句峦耘。 說明：TRUNCATE TABLE 在功能上與不帶 WHERE 子句的 DELETE 語句相同剔蹋。

46. 為獲得相同結(jié)果集的多次執(zhí)行，請保持SQL語句前后一致贡歧。這樣做的目的是為了充分利用查詢緩沖滩租。比如根據(jù)地域和產(chǎn)品ID查詢產(chǎn)品價格，第一次使用了：
    select price from order where id='123' and region='BEIJING'
    那么第二次同樣的查詢利朵，請保持以上語句的一致性律想，比如不要將where語句里面的id和region位置調(diào)換順序。

47. 為了提高 group by 語句的效率绍弟，可以在執(zhí)行到該語句前技即，把不需要的記錄過濾掉。

反例：

select job,avg(salary) from employee
group by job 
having job ='president' or job = 'managent'

正例：

select job,avg(salary) from employee
where job ='president' or job = 'managent' 
group by job

48. 索引不會包含有 NULL 值的列
    只要列中包含有 NULL 值都將不會被包含在索引中樟遣，復(fù)合索引中只要有一列含有 NULL 值而叼，那么這一列對于此復(fù)合索引就是無效的。所以在數(shù)據(jù)庫設(shè)計時不能讓字段的默認值為 NULL豹悬。

49. 索引列排序
    MySQL查詢只使用一個索引葵陵，因此如果where子句中已經(jīng)使用了索引的話，那么order by中的列是不會使用索引的瞻佛。因此數(shù)據(jù)庫默認排序可以符合要求的情況下不要使用排序操作脱篙；盡量不要包含多個列的排序，如果需要最好給這些列創(chuàng)建復(fù)合索引

50. 字符字段只建前綴索引

51. 字符字段最好不要做主鍵

52. 盡量不用UNIQUE伤柄，由程序保證約束

53. 明知只有一條查詢結(jié)果绊困，那請使用 “LIMIT 1”

“LIMIT 1”可以避免全表掃描，找到對應(yīng)結(jié)果就不會再繼續(xù)掃描了适刀。

54. varchar應(yīng)給設(shè)置一個合適的長度而不是給一個很大的長度秤朗，因為MySQL建立索引時一般會默認使用的該字段的長度，占用存儲空間且降低性能笔喉，另外取视，所有列總長度被限制為65,535字節(jié)硝皂。

55. text、blob字段應(yīng)該從頻繁查詢的表中分離出去贫途，因為text吧彪、blob之類的長列會完全被MySQL保存在一個單獨的數(shù)據(jù)頁里面里，查詢效率低丢早。

56. 不要輕易使用查詢緩存，特別是寫密集型應(yīng)用秧倾，緩存失效會占用大量的系統(tǒng)資源怨酝。

二、Oracle中的in參數(shù)的個數(shù)限制：

Oracle 中 in 后括號中的參數(shù)個數(shù)有限制那先，Oracle 9i 中個數(shù)不能超過256农猬，Oracle 10g個數(shù)不能超過1000。
當in的個數(shù)大于1000時售淡，有以下兩個解決辦法：

1. 對參數(shù)進行處理斤葱，分成多個in，其中每個in列表中參數(shù)都小于1000揖闸。如 params in(1,2,3.........1000) or params in(1001,1002...2000)揍堕。
   不過這種方法性能和維護性方面不好
2. 將in后面的字符串改成子查詢，將in里面的數(shù)據(jù)保存到臨時表中汤纸，params in（select ....from dual)

三衩茸、SQL語句中過濾條件where和having的區(qū)別

1. where 是一個約束聲明，使用 where 約束來自數(shù)據(jù)庫的數(shù)據(jù)贮泞，where 是在結(jié)果返回之前起作用楞慈，where 中不能使用聚合函數(shù)。
2. Having 是一個過濾聲明啃擦，是在查詢返回結(jié)果集以后對查詢結(jié)果進行的過濾操作囊蓝，在 Having 中可以使用聚合函數(shù)。
3. 在查詢過程中令蛉，where 子句執(zhí)行優(yōu)先級高于聚合語句聚霜。聚合語句(sum，min言询，max俯萎，avg，count)優(yōu)先級高于 having 子句运杭。

四夫啊、一個表建有多個索引，Oracle如何選擇

一個表最多可有255 個字段和32個索引辆憔。

1. 表的主鍵撇眯、外鍵必須有索引报嵌；
2. 數(shù)據(jù)量超過300的表應(yīng)該有索引；
3. 經(jīng)常與其他表進行連接的表熊榛，在連接字段上應(yīng)該建立索引锚国；
4. 經(jīng)常出現(xiàn)在Where子句中的字段，特別是大表的字段玄坦，應(yīng)該建立索引血筑；
5. 索引應(yīng)該建在選擇性高的字段上；
6. 索引應(yīng)該建在小字段上煎楣，對于大的文本字段甚至超長字段豺总，不要建索引；
7. 復(fù)合索引的建立需要進行仔細分析择懂；盡量考慮用單字段索引代替：
   ①正確選擇復(fù)合索引中的主列字段蛀蜜，一般是選擇性較好的字段犹菇。
   ②復(fù)合索引的幾個字段是否經(jīng)常同時以 AND 方式出現(xiàn)在 Where 子句中？單字段查詢是否極少甚至沒有？如果是帝美，則可以建立復(fù)合索引囱修；否則考慮單字段索引卦方。
   ③復(fù)合索引中包含的字段經(jīng)常單獨出現(xiàn)在 where 子句中纪吮，則分解為多個單字段索引。
   ④復(fù)合索引所包含的字段超過3個盈魁，要仔細考慮必要性翔怎，考慮減少復(fù)合的字段。
   ⑤既有單字段索引杨耙，又有這幾個字段上的復(fù)合索引赤套，可以刪除復(fù)合索引。
8. 頻繁進行數(shù)據(jù)操作的表珊膜，不要建立太多的索引容握；
9. 刪除無用的索引，避免對執(zhí)行計劃造成負面影響车柠；
   以上是一些普遍的建立索引時的判斷依據(jù)剔氏。一言以蔽之，索引的建立必須慎重竹祷，對每個索引的必要性都應(yīng)該經(jīng)過仔細分析谈跛，要有建立的依據(jù)令花。因為太多的索引與不充分、不正確的索引對性能都毫無益處：在表上建立的每個索引都會增加存儲開銷，索引對于插入、刪除漫蛔、更新操作也會增加處理上的開銷毯盈。另外脑奠，過多的復(fù)合索引齿诞，在有單字段索引的情況下陡厘，一般都是沒有存在價值的云茸；相反，還會降低數(shù)據(jù)增加刪除時的性能谤饭，特別是對頻繁更新的表來說标捺，負面影響更大。

五揉抵、商品表亡容，商品有類目屬性，找出某個類目下最新創(chuàng)建的100個商品冤今，表索引怎么設(shè)計?

1. 類目加索引闺兢，需要遍歷類目下所有商品，當類目商品數(shù)量多時戏罢，執(zhí)行速度慢屋谭，不同類目的執(zhí)行效率不一樣。
2. 創(chuàng)建時間加索引龟糕，執(zhí)行過程會命中創(chuàng)建時間索引桐磁，按照時間倒排篩選類目，如果當前類目很長時間沒有新品創(chuàng)建讲岁，有可能遍歷整個表我擂。
3. 類目+創(chuàng)建時間聯(lián)合索引，先命中類目索引缓艳，然后按照創(chuàng)建時間后續(xù)遍歷就可以拿到最新的100條校摩。

六、一個6億的表A郎任，一個3億的表B秧耗，通過外鍵tid關(guān)聯(lián)，如何最快查詢出有關(guān)聯(lián)關(guān)系的第50000到第50200中的這200條數(shù)據(jù)記錄

1舶治、如果 A 表 TID 是自增長分井，并且是連續(xù)的，B 表的 ID 為索引
select * from a,b where a.tid = b.id and a.tid>500000 limit 200;
2霉猛、如果 A 表的 TID 不是連續(xù)的尺锚，那么就需要使用覆蓋索引。TID 要么是主鍵惜浅，要么是覆蓋索引瘫辩，B 表 ID 也需要有索引。
select * from b, (select tid from a limit 50000,200) a where b.id = a.tid;

總結(jié)：SQL調(diào)優(yōu)方法很多，同樣的查詢結(jié)果可以有很多種不同的查詢方式伐厌。其實最好的方法就是在開發(fā)環(huán)境中用最貼近真實的數(shù)據(jù)集和硬件環(huán)境進行測試承绸，然后再發(fā)布到生產(chǎn)環(huán)境中。