全網(wǎng)最全的 MySQL 索引優(yōu)化方案

goldbomb 2022-03-30

展開全文

一、分頁查詢優(yōu)化

很多時候我們業(yè)務系統(tǒng)實現(xiàn)分頁功能可能會用如下sql實現(xiàn)：

select * from employees limit 10000,10;

表示從表 employees 中取出從 10001 行開始的 10 行記錄,?？此浦徊樵兞?10 條記錄，實際這條 SQL 是先讀取 10010條記錄,，然后拋棄前 10000 條記錄,，然后讀到后面 10 條想要的數(shù)據(jù)。因此要查詢一張大表比較靠后的數(shù)據(jù),，執(zhí)行效率是非常低的,。這是典型的深度分頁問題。

分頁場景優(yōu)化技巧

1,、根據(jù)自增且連續(xù)的主鍵排序的分頁查詢

首先來看一個根據(jù)自增且連續(xù)主鍵排序的分頁查詢的例子：

select * from employees limit 90000,5;

該 SQL 表示查詢從第 90001開始的五行數(shù)據(jù),，沒添加單獨 order by，表示通過主鍵排序,。我們再看表 employees ,，因為主鍵是自增并且連續(xù)的，所以可以改寫成按照主鍵去查詢從第 90001開始的五行數(shù)據(jù),，如下：

select * from employees where id > 90000 limit 5;

查詢的結果是一致的,。我們再對比一下執(zhí)行計劃：

EXPLAIN select * from employees limit 90000,5;

EXPLAIN select * from employees where id > 90000 limit 5;

顯然改寫后的 SQL 走了索引，而且掃描的行數(shù)大大減少,，執(zhí)行效率更高,。

但是，這條改寫的SQL 在很多場景并不實用,，因為表中可能某些記錄被刪后,，主鍵空缺，導致結果不一致,，如下圖試驗所示（先刪除一條前面的記錄,，然后再測試原 SQL 和優(yōu)化后的 SQL）：

兩條 SQL 的結果并不一樣，因此,，如果主鍵不連續(xù),，不能使用上面描述的優(yōu)化方法。

另外如果原 SQL 是 order by 非主鍵的字段,，按照上面說的方法改寫會導致兩條 SQL 的結果不一致。所以這種改寫得滿足以下兩個條件：

主鍵自增且連續(xù)
結果是按照主鍵排序的

2,、根據(jù)非主鍵字段排序的分頁查詢

再看一個根據(jù)非主鍵字段排序的分頁查詢,，SQL 如下：

select * from employees ORDER BY name limit 90000,5;

EXPLAIN select * from employees ORDER BY name limit 90000,5;

發(fā)現(xiàn)并沒有使用 name 字段的索引（key 字段對應的值為 null）,，具體原因是：掃描整個索引并查找到?jīng)]索引的行(可能要遍歷多個索引樹)的成本比掃描全表的成本更高，所以優(yōu)化器放棄使用索引,。

知道不走索引的原因，那么怎么優(yōu)化呢,？

其實關鍵是讓排序時返回的字段盡可能少,，即考慮使用覆蓋索引進行優(yōu)化，所以可以讓排序和分頁操作先查出主鍵,，然后根據(jù)主鍵查到對應的記錄,，SQL改寫如下:

select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;

需要的結果與原 SQL 一致，執(zhí)行時間減少了一半以上,，此時查詢和排序都是在覆蓋索引樹上進行的，所以效率較高,。我們再對比優(yōu)化前后sql的執(zhí)行計劃：

原 SQL 使用的是 file sort 排序,，而優(yōu)化后的 SQL 使用的是索引排序。

根據(jù)id判斷,，會先執(zhí)行id = 2的sql,，此時使用了覆蓋索引,，排序和查詢都是在索引樹上完成的。
然后執(zhí)行id=1的sql,，這里使用了eq_ref,，即主鍵索引。
最后執(zhí)行join關聯(lián)的那張表,，因為此時的table是derived, 是前面兩張表的關聯(lián)表,，總共有5條記錄，所以即使全表掃描,，也是比較快的,。

基于 Spring Boot + MyBatis Plus + Vue & Element 實現(xiàn)的后臺管理系統(tǒng) + 用戶小程序，支持 RBAC 動態(tài)權限,、多租戶,、數(shù)據(jù)權限、工作流,、三方登錄,、支付、短信,、商城等功能,。
項目地址：https://github.com/YunaiV/ruoyi-vue-pro

二、Join關聯(lián)查詢優(yōu)化

示例表：

‐‐ 示例表：
 CREATE TABLE `t1` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `a` int(11) DEFAULT NULL,
 `b` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_a` (`a`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 create table t2 like t1;

 ‐‐ 插入一些示例數(shù)據(jù)
 ‐‐ 往t1表插入1萬行記錄
 drop procedure if exists insert_t1;
 delimiter ;;
 create procedure insert_t1()
 begin
 declare i int;
 set i=1;
 while(i<=10000)do
 insert into t1(a,b) values(i,i);
 set i=i+1;
 end while;
 end;;
 delimiter ;
 call insert_t1();
 
 ‐‐ 往t2表插入100行記錄
 drop procedure if exists insert_t2;
 delimiter ;;
 create procedure insert_t2()
 begin
 declare i int;
 set i=1;
 while(i<=100)do
 insert into t2(a,b) values(i,i);
 set i=i+1;
 end while;
 end;;
 delimiter ;
 call insert_t2();

mysql的表關聯(lián)常見有兩種算法

Nested-Loop Join 算法
Block Nested-Loop Join 算法

1,、嵌套循環(huán)連接 Nested-Loop Join(NLJ) 算法

一次一行循環(huán)地從第一張表（稱為驅動表）中讀取行,，在這行數(shù)據(jù)中取到關聯(lián)字段，根據(jù)關聯(lián)字段在另一張表（被驅動表）里取出滿足條件的行,，然后取出兩張表的結果合集,。

EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;  -- // a字段有索引

從執(zhí)行計劃中可以看到這些信息：

驅動表是 t2，被驅動表是 t1,。先執(zhí)行的就是驅動表(執(zhí)行計劃結果的id如果一樣則按從上到下順序執(zhí)行sql),；優(yōu)化器一般會優(yōu)先選擇小表做驅動表。所以使用 inner join 時,，排在前面的表并不一定就是驅動表,。
當使用left join時，左表是驅動表,，右表是被驅動表,，當使用right join時，右表是驅動表,，左表是被驅動表,，當使用join時，mysql會選擇數(shù)據(jù)量比較小的表作為驅動表,，大表作為被驅動表,。
使用了 NLJ算法,。一般 join 語句中，如果執(zhí)行計劃 Extra 中未出現(xiàn) Using join buffer 則表示使用的 join 算法是 NLJ,。

上面sql的大致流程如下：

從表 t2 中讀取一行數(shù)據(jù)（如果t2表有查詢過濾條件的,，會從過濾結果里取出一行數(shù)據(jù)）；
從第 1 步的數(shù)據(jù)中,，取出關聯(lián)字段 a,，到表 t1 中查找；
取出表 t1 中滿足條件的行,，跟 t2 中獲取到的結果合并,，作為結果返回給客戶端；
重復上面 3 步,。

整個過程會讀取 t2 表的所有數(shù)據(jù)(掃描100行),，然后遍歷這每行數(shù)據(jù)中字段 a 的值，根據(jù) t2 表中 a 的值索引掃描 t1 表中的對應行(掃描100次 t1 表的索引,，1次掃描可以認為最終只掃描 t1 表一行完整數(shù)據(jù),，也就是總共 t1 表也掃描了100行)。因此整個過程掃描了 200 行,。

2,、基于塊的嵌套循環(huán)連接 Block Nested-Loop Join(BNL)算法

把驅動表的數(shù)據(jù)讀入到 join_buffer 中，然后掃描被驅動表,，把被驅動表每一行取出來跟 join_buffer 中的數(shù)據(jù)做對比。

EXPLAIN select * from t1 inner join t2 on t1.b= t2.b; -- // b字段沒有索引

Extra 中的Using join buffer (Block Nested Loop)說明該關聯(lián)查詢使用的是 BNL 算法,。

上面sql的大致流程如下：

把 t2 的所有數(shù)據(jù)放入到 join_buffer 中
把表 t1 中每一行取出來,，跟 join_buffer 中的數(shù)據(jù)做對比
返回滿足 join 條件的數(shù)據(jù)

整個過程對表 t1 和 t2 都做了一次全表掃描，因此掃描的總行數(shù)為10000(表 t1 的數(shù)據(jù)總量) + 100(表 t2 的數(shù)據(jù)總量) =10100,。并且 join_buffer 里的數(shù)據(jù)是無序的,，因此對表 t1 中的每一行，都要做 100 次判斷,，所以內(nèi)存中的判斷次數(shù)是100 * 10000= 100 萬次,。

這個例子里表 t2 才 100 行，要是表 t2 是一個大表,，join_buffer 放不下怎么辦呢,？

join_buffer 的大小是由參數(shù) join_buffer_size 設定的，默認值是 256k,。如果放不下表 t2 的所有數(shù)據(jù)話,，策略很簡單，就是分段放,。

比如 t2 表有1000行記錄,， join_buffer 一次只能放800行數(shù)據(jù),，那么執(zhí)行過程就是先往 join_buffer 里放800行記錄，然后從 t1 表里取數(shù)據(jù)跟 join_buffer 中數(shù)據(jù)對比得到部分結果,，然后清空 join_buffer ,，再放入 t2 表剩余200行記錄，再次從 t1 表里取數(shù)據(jù)跟 join_buffer 中數(shù)據(jù)對比,。所以就多掃了一次 t1 表,。

被驅動表的關聯(lián)字段沒索引為什么要選擇使用 BNL 算法而不使用 Nested-Loop Join 呢？

如果上面第二條sql使用 Nested-Loop Join,，那么掃描行數(shù)為 100 * 10000 = 100萬次,，這個是磁盤掃描。

很顯然,，用BNL磁盤掃描次數(shù)少很多,，相比于磁盤掃描，BNL的內(nèi)存計算會快得多,。因此MySQL對于被驅動表的關聯(lián)字段沒索引的關聯(lián)查詢,，一般都會使用 BNL 算法。如果有索引一般選擇 NLJ 算法,，有索引的情況下 NLJ 算法比 BNL算法性能更高.

對于關聯(lián)sql的優(yōu)化

關聯(lián)字段加索引,，讓mysql做join操作時盡量選擇NLJ算法
小表驅動大表，寫多表連接sql時如果明確知道哪張表是小表可以用straight_join寫法固定連接驅動方式,，省去mysql優(yōu)化器自己判斷的時間

straight_join解釋：straight_join功能同join類似,，但能讓左邊的表來驅動右邊的表，能改表優(yōu)化器對于聯(lián)表查詢的執(zhí)行順序,。

比如：select * from t2 straight_join t1 on t2.a = t1.a; 代表指定mysql選著 t2 表作為驅動表,。

straight_join只適用于inner join，并不適用于left join,，right join,。（因為left join，right join已經(jīng)代表指定了表的執(zhí)行順序）
盡可能讓優(yōu)化器去判斷,，因為大部分情況下mysql優(yōu)化器是比人要聰明的,。使用straight_join一定要慎重，因為部分情況下人為指定的執(zhí)行順序并不一定會比優(yōu)化引擎要靠譜,。

對于小表定義的明確：

在決定哪個表做驅動表的時候,，應該是兩個表按照各自的條件過濾,，過濾完成之后,，計算參與 join 的各個字段的總數(shù)據(jù)量,，數(shù)據(jù)量小的那個表，就是“小表”,，應該作為驅動表,。

基于微服務的思想,，構建在 B2C 電商場景下的項目實戰(zhàn)。核心技術棧,，是 Spring Boot + Dubbo ,。未來，會重構成 Spring Cloud Alibaba ,。
項目地址：https://github.com/YunaiV/onemall

三,、in和exsits優(yōu)化

原則：小表驅動大表，即小的數(shù)據(jù)集驅動大的數(shù)據(jù)集,。

In是In后的表先執(zhí)行（適用于B表小于A表）：

select * from A where id in ( select id from B)

Exists是Exists前面的表先執(zhí)行（適用于A表小于B表）:

select * from A where id in ( select id from B)

in：當B表的數(shù)據(jù)集小于A表的數(shù)據(jù)集時,，in優(yōu)于exists

select * from A where id in (select id from B)

// #等價于：
 for(select id from B){
 select * from A where A.id = B.id
 }

exists：當A表的數(shù)據(jù)集小于B表的數(shù)據(jù)集時，exists優(yōu)于in

將主查詢A的數(shù)據(jù),，放到子查詢B中做條件驗證,，根據(jù)驗證結果（true或false）來決定主查詢的數(shù)據(jù)是否保留.

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

 // # 等價于:
 for(select * from A){
 select * from B where B.id = A.id
 }

 // # A表與B表的ID字段應建立索引

總結：

1、EXISTS (subquery)只返回TRUE或FALSE,因此子查詢中的SELECT * 也可以用SELECT 1替換,官方說法是實際執(zhí)行時會忽略SELECT清單,因此沒有區(qū)別

2,、EXISTS子查詢的實際執(zhí)行過程可能經(jīng)過了優(yōu)化而不是我們理解上的逐條對比

3,、EXISTS子查詢往往也可以用JOIN來代替，何種最優(yōu)需要具體問題具體分析

四,、`count(*)`查詢優(yōu)化

 -- 臨時關閉mysql查詢緩存,，為了查看sql多次執(zhí)行的真實時間
 set global query_cache_size=0;
 set global query_cache_type=0;

 EXPLAIN select count(1) from employees;
 EXPLAIN select count(id) from employees;
 EXPLAIN select count(name) from employees;
 EXPLAIN select count(*) from employees;
 -- 注意：以上4條sql只有根據(jù)某個字段count不會統(tǒng)計字段

經(jīng)過測試發(fā)現(xiàn)：四個sql的執(zhí)行計劃一樣，說明這四個sql執(zhí)行效率應該差不多

1,、字段有索引： count(*)≈count(1)>count(字段)>count(主鍵 id)

字段有索引,，count(字段)統(tǒng)計走二級索引，二級索引存儲數(shù)據(jù)比主鍵索引少,，所以count(字段)>count(主鍵 id)

2,、字段無索引: count(*)≈count(1)>count(主鍵 id)>count(字段)

字段沒有索引count(字段)統(tǒng)計走不了索引，count(主鍵 id)還可以走主鍵索引,，所以count(主鍵 id)>count(字段)

count(1) 跟 count(字段) 執(zhí)行過程類似，不過count(1)不需要取出字段統(tǒng)計,，就用常量1做統(tǒng)計,，count(字段)還需要取出字段，所以理論上count(1)比count(字段)會快一點,。

count(*) 是例外,，mysql并不會把全部字段取出來，而是專門做了優(yōu)化(5.7版本),，不取值,，按行累加，效率很高,，所以不需要用count(列名)或count(常量)來替代 count(*),。

為什么對于count(id),，mysql最終選擇輔助索引而不是主鍵聚集索引？因為二級索引相對主鍵索引存儲數(shù)據(jù)更少,，檢索性能應該更高,，mysql內(nèi)部做了點優(yōu)化(應該是在5.7版本才優(yōu)化)。

常見優(yōu)化方法

當表中數(shù)據(jù)量非常大的時候,，count這種通過計算統(tǒng)計的都會很慢,，所以需要一些優(yōu)化手段。

1,、查詢mysql自己維護的總行數(shù)

對于myisam存儲引擎的表做不帶where條件的count查詢性能是很高的,，因為myisam存儲引擎的表的總行數(shù)會被mysql存儲在磁盤上，查詢不需要計算.

對于innodb存儲引擎的表mysql不會存儲表的總記錄行數(shù)(因為有MVCC機制,，后面會講),，查詢count需要實時計算.

2、show table status

如果只需要知道表總行數(shù)的估計值可以用如下sql查詢,，性能很高

3,、將總數(shù)維護到Redis里

插入或刪除表數(shù)據(jù)行的時候同時維護redis里的表總行數(shù)key的計數(shù)值(用incr或decr命令)，但是這種方式可能不準,，很難保證表操作和redis操作的事務一致性.

4,、增加數(shù)據(jù)庫計數(shù)表

插入或刪除表數(shù)據(jù)行的時候同時維護計數(shù)表，讓他們在同一個事務里操作

五,、阿里MySQL規(guī)范解讀

(一) 建表規(guī)約

【強制】表達是與否概念的字段,，必須使用is_xxx的方式命名，數(shù)據(jù)類型是unsigned tinyint（1表示是,，0表示否）,。說明：任何字段如果為非負數(shù)，必須是unsigned,。注意：POJO類中的任何布爾類型的變量,，都不要加is前綴，所以,，需要在設置從is_xxx到Xxx的映射關系,。數(shù)據(jù)庫表示是與否的值，使用tinyint類型,，堅持is_xxx的命名方式是為了明確其取值含義與取值范圍,。正例：表達邏輯刪除的字段名is_deleted，1表示刪除,，0表示未刪除,。
【強制】表名、字段名必須使用小寫字母或數(shù)字，禁止出現(xiàn)數(shù)字開頭,，禁止兩個下劃線中間只出現(xiàn)數(shù)字,。數(shù)據(jù)庫字段名的修改代價很大，因為無法進行預發(fā)布,，所以字段名稱需要慎重考慮,。說明：MySQL在Windows下不區(qū)分大小寫，但在Linux下默認是區(qū)分大小寫,。因此,，數(shù)據(jù)庫名、表名,、字段名,，都不允許出現(xiàn)任何大寫字母，避免節(jié)外生枝,。正例：aliyun_admin,，rdc_config，level3_name 反例：AliyunAdmin,，rdcConfig,，level_3_name
【強制】表名不使用復數(shù)名詞。說明：表名應該僅僅表示表里面的實體內(nèi)容,，不應該表示實體數(shù)量,，對應于DO類名也是單數(shù)形式，符合表達習慣,。
【強制】禁用保留字,，如desc、range,、match,、delayed等，請參考MySQL官方保留字,。
【強制】主鍵索引名為pk_字段名,；唯一索引名為uk_字段名；普通索引名則為idx_字段名,。說明：pk_ 即primary key,；uk_ 即 unique key；idx_ 即index的簡稱,。
【強制】小數(shù)類型為decimal，禁止使用float和double,。說明：在存儲的時候,，float 和 double 都存在精度損失的問題，很可能在比較值的時候,，得到不正確的結果,。如果存儲的數(shù)據(jù)范圍超過 decimal 的范圍,，建議將數(shù)據(jù)拆成整數(shù)和小數(shù)并分開存儲。
【強制】如果存儲的字符串長度幾乎相等,，使用char定長字符串類型,。
【強制】varchar是可變長字符串，不預先分配存儲空間,，長度不要超過5000,，如果存儲長度大于此值，定義字段類型為text,，獨立出來一張表,，用主鍵來對應，避免影響其它字段索引效率,。
【強制】表必備三字段：id, gmt_create, gmt_modified,。說明：其中id必為主鍵，類型為bigint unsigned,、單表時自增,、步長為1。gmt_create, gmt_modified的類型均為datetime類型,，前者現(xiàn)在時表示主動式創(chuàng)建,，后者過去分詞表示被動式更新。
【推薦】表的命名最好是遵循“業(yè)務名稱_表的作用”,。正例：alipay_task / force_project / trade_config
【推薦】庫名與應用名稱盡量一致,。
【推薦】如果修改字段含義或對字段表示的狀態(tài)追加時，需要及時更新字段注釋,。
【推薦】字段允許適當冗余,，以提高查詢性能，但必須考慮數(shù)據(jù)一致,。冗余字段應遵循：1）不是頻繁修改的字段,。2）不是唯一索引的字段。3）不是varchar超長字段,，更不能是text字段,。正例：各業(yè)務線經(jīng)常冗余存儲商品名稱，避免查詢時需要調(diào)用IC服務獲取,。
【推薦】單表行數(shù)超過500萬行或者單表容量超過2GB,，才推薦進行分庫分表。說明：如果預計三年后的數(shù)據(jù)量根本達不到這個級別,，請不要在創(chuàng)建表時就分庫分表,。
【參考】合適的字符存儲長度，不但節(jié)約數(shù)據(jù)庫表空間、節(jié)約索引存儲,，更重要的是提升檢索速度,。正例：無符號值可以避免誤存負數(shù)，且擴大了表示范圍,。

(二) 索引規(guī)約

【強制】業(yè)務上具有唯一特性的字段,，即使是組合字段，也必須建成唯一索引,。說明：不要以為唯一索引影響了insert速度,，這個速度損耗可以忽略，但提高查找速度是明顯的,；另外,，即使在應用層做了非常完善的校驗控制，只要沒有唯一索引,，根據(jù)墨菲定律,，必然有臟數(shù)據(jù)產(chǎn)生。
【強制】超過三個表禁止join,。需要join的字段,，數(shù)據(jù)類型保持絕對一致；多表關聯(lián)查詢時,，保證被關聯(lián)的字段需要有索引,。說明：即使雙表join也要注意表索引、SQL性能,。
【強制】在varchar字段上建立索引時,，必須指定索引長度，沒必要對全字段建立索引,，根據(jù)實際文本區(qū)分度決定索引長度,。說明：索引的長度與區(qū)分度是一對矛盾體，一般對字符串類型數(shù)據(jù),，長度為20的索引,，區(qū)分度會高達90%以上，可以使用count(distinct left(列名, 索引長度))/count(*)的區(qū)分度來確定,。
【強制】頁面搜索嚴禁左模糊或者全模糊,，如果需要請走搜索引擎來解決。說明：索引文件具有B-Tree的最左前綴匹配特性,，如果左邊的值未確定,，那么無法使用此索引。
【推薦】如果有order by的場景,，請注意利用索引的有序性,。order by 最后的字段是組合索引的一部分,，并且放在索引組合順序的最后，避免出現(xiàn)file_sort的情況,，影響查詢性能。正例：where a=? and b=? order by c; 索引：a_b_c 反例：索引如果存在范圍查詢,，那么索引有序性無法利用,，如：WHERE a>10 ORDER BY b; 索引a_b無法排序。
【推薦】利用覆蓋索引來進行查詢操作,，避免回表,。說明：如果一本書需要知道第11章是什么標題，會翻開第11章對應的那一頁嗎,？目錄瀏覽一下就好,，這個目錄就是起到覆蓋索引的作用。正例：能夠建立索引的種類分為主鍵索引,、唯一索引,、普通索引三種，而覆蓋索引只是一種查詢的一種效果,，用explain的結果,，extra列會出現(xiàn)：using index。
【推薦】利用延遲關聯(lián)或者子查詢優(yōu)化超多分頁場景,。說明：MySQL并不是跳過offset行,，而是取offset+N行，然后返回放棄前offset行,，返回N行,，那當offset特別大的時候，效率就非常的低下,，要么控制返回的總頁數(shù),，要么對超過特定閾值的頁數(shù)進行SQL改寫。正例：先快速定位需要獲取的id段,，然后再關聯(lián)：SELECT a.* FROM 表1 a, (select id from 表1 where 條件 LIMIT 100000,20 ) b where a.id=b.id
【推薦】SQL性能優(yōu)化的目標：至少要達到 range 級別,，要求是ref級別，如果可以是consts最好,。說明：1） consts 單表中最多只有一個匹配行（主鍵或者唯一索引）,，在優(yōu)化階段即可讀取到數(shù)據(jù)。2） ref 指的是使用普通的索引（normal index）,。3） range 對索引進行范圍檢索,。反例：explain表的結果，type=index,，索引物理文件全掃描,，速度非常慢,，這個index級別比較range還低，與全表掃描是小巫見大巫,。
【推薦】建組合索引的時候,，區(qū)分度最高的在最左邊。正例：如果where a=? and b=?,，a列的幾乎接近于唯一值,，那么只需要單建idx_a索引即可。說明：存在非等號和等號混合判斷條件時,，在建索引時,，請把等號條件的列前置。如：where c>? and d=? 那么即使c的區(qū)分度更高,，也必須把d放在索引的最前列,，即建立組合索引idx_d_c。
【推薦】防止因字段類型不同造成的隱式轉換,，導致索引失效,。
【參考】創(chuàng)建索引時避免有如下極端誤解：1）索引寧濫勿缺。認為一個查詢就需要建一個索引,。2）吝嗇索引的創(chuàng)建,。認為索引會消耗空間、嚴重拖慢記錄的更新以及行的新增速度,。3）抵制惟一索引,。認為惟一索引一律需要在應用層通過“先查后插”方式解決。

(三) SQL語句

【強制】不要使用count(列名)或count(常量)來替代count(),，count()是SQL92定義的標準統(tǒng)計行數(shù)的語法,，跟數(shù)據(jù)庫無關，跟NULL和非NULL無關,。說明：count(*)會統(tǒng)計值為NULL的行,，而count(列名)不會統(tǒng)計此列為NULL值的行。
【強制】count(distinct col) 計算該列除NULL之外的不重復行數(shù),，注意 count(distinct col1, col2) 如果其中一列全為NULL,，那么即使另一列有不同的值，也返回為0,。
【強制】當某一列的值全是NULL時,，count(col)的返回結果為0，但sum(col)的返回結果為NULL,，因此使用sum()時需注意NPE問題,。正例：可以使用如下方式來避免sum的NPE問題：SELECT IFNULL(SUM(column), 0) FROM table;
【強制】使用ISNULL()來判斷是否為NULL值。說明：NULL與任何值的直接比較都為NULL,。1） NULL<>NULL的返回結果是NULL,，而不是false,。2） NULL=NULL的返回結果是NULL，而不是true,。3） NULL<>1的返回結果是NULL,，而不是true。反例：在SQL語句中,，如果在null前換行,，影響可讀性。select * from table where column1 is null and column3 is not null; 而ISNULL(column)是一個整體,，簡潔易懂。從性能數(shù)據(jù)上分析,，ISNULL(column)執(zhí)行效率更快一些,。
【強制】代碼中寫分頁查詢邏輯時，若count為0應直接返回,，避免執(zhí)行后面的分頁語句,。
【強制】不得使用外鍵與級聯(lián)，一切外鍵概念必須在應用層解決,。說明：（概念解釋）學生表中的student_id是主鍵,，那么成績表中的student_id則為外鍵。如果更新學生表中的student_id,，同時觸發(fā)成績表中的student_id更新,，即為級聯(lián)更新。外鍵與級聯(lián)更新適用于單機低并發(fā),，不適合分布式,、高并發(fā)集群；級聯(lián)更新是強阻塞,，存在數(shù)據(jù)庫更新風暴的風險,；外鍵影響數(shù)據(jù)庫的插入速度。
【強制】禁止使用存儲過程,，存儲過程難以調(diào)試和擴展,，更沒有移植性。
【強制】數(shù)據(jù)訂正（特別是刪除或修改記錄操作）時,，要先select,，避免出現(xiàn)誤刪除，確認無誤才能執(zhí)行更新語句,。
【強制】對于數(shù)據(jù)庫中表記錄的查詢和變更,，只要涉及多個表，都需要在列名前加表的別名（或表名）進行限定,。說明：對多表進行查詢記錄,、更新記錄,、刪除記錄時，如果對操作列沒有限定表的別名（或表名）,，并且操作列在多個表中存在時,，就會拋異常。正例：select t1.name from table_first as t1 , table_second as t2 where t1.id=t2.id; 反例：在某業(yè)務中,，由于多表關聯(lián)查詢語句沒有加表的別名（或表名）的限制,，正常運行兩年后，最近在某個表中增加一個同名字段,，在預發(fā)布環(huán)境做數(shù)據(jù)庫變更后,，線上查詢語句出現(xiàn)出1052異常：Column 'name’ in field list is ambiguous。
【推薦】SQL語句中表的別名前加as,，并且以t1,、t2、t3,、…的順序依次命名,。說明：1）別名可以是表的簡稱，或者是根據(jù)表出現(xiàn)的順序,，以t1,、t2、t3的方式命名,。2）別名前加as使別名更容易識別,。正例：select t1.name from table_first as t1, table_second as t2 where t1.id=t2.id;
【推薦】in操作能避免則避免，若實在避免不了,，需要仔細評估in后邊的集合元素數(shù)量,，控制在1000個之內(nèi)。
【參考】因國際化需要,，所有的字符存儲與表示,，均采用utf8字符集，那么字符計數(shù)方法需要注意,。說明：SELECT LENGTH(“輕松工作”),； 返回為12 SELECT CHARACTER_LENGTH(“輕松工作”)； 返回為4 如果需要存儲表情,，那么選擇utf8mb4來進行存儲,，注意它與utf8編碼的區(qū)別。
【參考】TRUNCATE TABLE 比 DELETE 速度快,，且使用的系統(tǒng)和事務日志資源少,，但TRUNCATE無事務且不觸發(fā)trigger，有可能造成事故,，故不建議在開發(fā)代碼中使用此語句,。說明：TRUNCATE TABLE 在功能上與不帶 WHERE 子句的 DELETE 語句相同,。

(四) ORM映射

【強制】在表查詢中，一律不要使用 * 作為查詢的字段列表,，需要哪些字段必須明確寫明,。說明：1）增加查詢分析器解析成本。2）增減字段容易與resultMap配置不一致,。3）無用字段增加網(wǎng)絡消耗,，尤其是text類型的字段。
【強制】POJO類的布爾屬性不能加is,，而數(shù)據(jù)庫字段必須加is_,，要求在resultMap中進行字段與屬性之間的映射。說明：參見定義POJO類以及數(shù)據(jù)庫字段定義規(guī)定,，在sql.xml增加映射,，是必須的。
【強制】不要用resultClass當返回參數(shù),，即使所有類屬性名與數(shù)據(jù)庫字段一一對應，也需要定義,；反過來,，每一個表也必然有一個與之對應。說明：配置映射關系,，使字段與DO類解耦,，方便維護。
【強制】sql.xml配置參數(shù)使用：#{},，#param# 不要使用${} 此種方式容易出現(xiàn)SQL注入,。
【強制】iBATIS自帶的queryForList(String statementName,int start,int size)不推薦使用。說明：其實現(xiàn)方式是在數(shù)據(jù)庫取到statementName對應的SQL語句的所有記錄,，再通過subList取start,size的子集合,。

正例：

Map<String, Object> map = new HashMap<>();
map.put(“start”, start);
map.put(“size”, size);

【強制】不允許直接拿HashMap與Hashtable作為查詢結果集的輸出。反例：某同學為避免寫一個,，直接使用HashTable來接收數(shù)據(jù)庫返回結果,，結果出現(xiàn)日常是把bigint轉成Long值，而線上由于數(shù)據(jù)庫版本不一樣,，解析成BigInteger,，導致線上問題。
【強制】更新數(shù)據(jù)表記錄時,，必須同時更新記錄對應的gmt_modified字段值為當前時間,。
【推薦】不要寫一個大而全的數(shù)據(jù)更新接口。傳入為POJO類,，不管是不是自己的目標更新字段,，都進行update table set c1=value1,c2=value2,c3=value3; 這是不對的,。執(zhí)行SQL時，不要更新無改動的字段,，一是易出錯,；二是效率低；三是增加binlog存儲,。
【參考】@Transactional事務不要濫用,。事務會影響數(shù)據(jù)庫的QPS，另外使用事務的地方需要考慮各方面的回滾方案,，包括緩存回滾,、搜索引擎回滾、消息補償,、統(tǒng)計修正等,。
【參考】中的compareValue是與屬性值對比的常量，一般是數(shù)字,，表示相等時帶上此條件,；表示不為空且不為null時執(zhí)行；表示不為null值時執(zhí)行,。

六,、MySQL數(shù)據(jù)類型選擇

在MySQL中，選擇正確的數(shù)據(jù)類型,，對于性能至關重要,。一般應該遵循下面兩步：

確定合適的大類型：數(shù)字、字符串,、時間,、二進制；
確定具體的類型：有無符號,、取值范圍,、變長定長等。

在MySQL數(shù)據(jù)類型設置方面,，盡量用更小的數(shù)據(jù)類型,，因為它們通常有更好的性能，花費更少的硬件資源,。并且,，盡量把字段定義為NOT NULL，避免使用NULL.

1,、數(shù)值類型

優(yōu)化建議：

如果整形數(shù)據(jù)沒有負數(shù),，如ID號，建議指定為UNSIGNED無符號類型，容量可以擴大一倍,。
建議使用TINYINT代替ENUM,、BITENUM、SET,。
避免使用整數(shù)的顯示寬度(參看文檔最后),，也就是說，不要用INT(10)類似的方法指定字段顯示寬度,，直接用INT,。
DECIMAL最適合保存準確度要求高，而且用于計算的數(shù)據(jù),，比如價格,。但是在使用DECIMAL類型的時候，注意長度設置,。
建議使用整形類型來運算和存儲實數(shù),，方法是，實數(shù)乘以相應的倍數(shù)后再操作,。
整數(shù)通常是最佳的數(shù)據(jù)類型,，因為它速度快，并且能使用AUTO_INCREMENT,。

2,、日期和時間

優(yōu)化建議：

MySQL能存儲的最小時間粒度為秒。
建議用DATE數(shù)據(jù)類型來保存日期,。MySQL中默認的日期格式是yyyy-mm-dd,。
用MySQL的內(nèi)建類型DATE,、TIME,、DATETIME來存儲時間，而不是使用字符串,。
當數(shù)據(jù)格式為TIMESTAMP和DATETIME時,，可以用CURRENT_TIMESTAMP作為默認（MySQL5.6以后），MySQL會自動返回記錄插入的確切時間,。
TIMESTAMP是UTC時間戳,，與時區(qū)相關。
DATETIME的存儲格式是一個YYYYMMDD HH:MM:SS的整數(shù),，與時區(qū)無關,，你存了什么，讀出來就是什么,。
除非有特殊需求,，一般的公司建議使用TIMESTAMP，它比DATETIME更節(jié)約空間，但是像阿里這樣的公司一會用DATETIME,，因為不用考慮TIMESTAMP將來的時間上限問題,。
有時人們把Unix的時間戳保存為整數(shù)值，但是這通常沒有任何好處,，這種格式處理起來不太方便,，我們并不推薦它。

3,、字符串

優(yōu)化建議:

字符串的長度相差較大用VARCHAR,；字符串短，且所有值都接近一個長度用CHAR,。
CHAR和VARCHAR適用于包括人名,、郵政編碼、電話號碼和不超過255個字符長度的任意字母數(shù)字組合,。那些要用來計算的數(shù)字不要用VARCHAR類型保存,，因為可能會導致一些與計算相關的問題。換句話說,，可能影響到計算的準確性和完整性,。
盡量少用BLOB和TEXT，如果實在要用可以考慮將BLOB和TEXT字段單獨存一張表,，用id關聯(lián),。
BLOB系列存儲二進制字符串，與字符集無關,。TEXT系列存儲非二進制字符串,，與字符集相關。
BLOB和TEXT都不能有默認值,。

PS：INT顯示寬度

我們經(jīng)常會使用命令來創(chuàng)建數(shù)據(jù)表,，而且同時會指定一個長度，如下,。但是,，這里的長度并非是TINYINT類型存儲的最大長度，而是顯示的最大長度,。

CREATE TABLE `user`(
 `id` TINYINT(2) UNSIGNED
);

這里表示user表的id字段的類型是TINYINT,，可以存儲的最大數(shù)值是255。所以,，在存儲數(shù)據(jù)時,，如果存入值小于等于255，如200,，雖然超過2位,，但是沒有超出TINYINT類型長度，所以可以正常保存；如果存入值大于255,，如500,，那么MySQL會自動保存為TINYINT類型的最大值255。

在查詢數(shù)據(jù)時,，不管查詢結果為何值,，都按實際輸出。這里TINYINT(2)中2的作用就是,，當需要在查詢結果前填充0時,，命令中加上ZEROFILL就可以實現(xiàn)，如：