熟读《阿里巴巴java开发手册》（五、 MySQL 数据库）
目錄
(一) 建表規約
(二) 索引規約
(三) SQL 語句
(四) ORM 映射
(一) 建表規約
1. 【強制】 表達是與否概念的字段，必須使用 is_xxx 的方式命名，數據類型是 unsigned tinyint（ 1 表示是， 0 表示否）。
說明： 任何字段如果為非負數，必須是 unsigned。
注意： POJO 類中的任何布爾類型的變量，都不要加 is 前綴，所以，需要在<resultMap>設置從 is_xxx到 Xxx 的映射關系。數據庫表示是與否的值，使用 tinyint 類型，堅持 is_xxx 的命名方式是為了明確其取值含義與取值范圍。
正例： 表達邏輯刪除的字段名 is_deleted， 1 表示刪除， 0 表示未刪除。
2. 【強制】 表名、字段名必須使用小寫字母或數字， 禁止出現數字開頭，禁止兩個下劃線中間只出現數字。數據庫字段名的修改代價很大，因為無法進行預發布，所以字段名稱需要慎重考慮。
說明： MySQL 在 Windows 下不區分大小寫，但在 Linux 下默認是區分大小寫。因此，數據庫名、表名、字段名，都不允許出現任何大寫字母，避免節外生枝。
正例： aliyun_admin， rdc_config， level3_name
反例： AliyunAdmin， rdcConfig， level_3_name
3. 【強制】 表名不使用復數名詞。
說明： 表名應該僅僅表示表里面的實體內容，不應該表示實體數量，對應于 DO 類名也是單數形式，符合表達習慣。
4. 【強制】 禁用保留字，如 desc、 range、 match、 delayed 等， 請參考 MySQL 官方保留字。
5. 【強制】 主鍵索引名為 pk_字段名；唯一索引名為 uk_字段名； 普通索引名則為 idx_字段名。
說明： pk_ 即 primary key； uk_ 即 unique key； idx_ 即 index 的簡稱。
6. 【強制】 小數類型為 decimal，禁止使用 float 和 double。
說明： 在存儲的時候， float 和 double 都存在精度損失的問題，很可能在比較值的時候，得到不正確的結果。如果存儲的數據范圍超過 decimal 的范圍，建議將數據拆成整數和小數并分開存儲。
7. 【強制】 如果存儲的字符串長度幾乎相等，使用 char 定長字符串類型。
8. 【強制】 varchar 是可變長字符串，不預先分配存儲空間，長度不要超過 5000，如果存儲長度大于此值，定義字段類型為 text，獨立出來一張表，用主鍵來對應，避免影響其它字段索引效率。
9. 【強制】 表必備三字段： id, create_time, update_time。
說明： 其中 id 必為主鍵，類型為 bigint unsigned、單表時自增、步長為 1。 create_time, update_time的類型均為 datetime 類型。
10.【推薦】 表的命名最好是遵循“業務名稱_表的作用” 。
正例： alipay_task / force_project / trade_config
11.【推薦】 庫名與應用名稱盡量一致。
12.【推薦】 如果修改字段含義或對字段表示的狀態追加時，需要及時更新字段注釋。
13.【推薦】 字段允許適當冗余，以提高查詢性能，但必須考慮數據一致。冗余字段應遵循：
1） 不是頻繁修改的字段。
2） 不是 varchar 超長字段，更不能是 text 字段。
3） 不是唯一索引的字段。
正例： 商品類目名稱使用頻率高，字段長度短，名稱基本一不變，可在相關聯的表中冗余存儲類目名稱，避免關聯查詢。
14.【推薦】 單表行數超過 500 萬行或者單表容量超過 2GB，才推薦進行分庫分表。
說明： 如果預計三年后的數據量根本達不到這個級別，請不要在創建表時就分庫分表。
15.【參考】 合適的字符存儲長度，不但節約數據庫表空間、節約索引存儲，更重要的是提升檢索速度。
正例： 如下表，其中無符號值可以避免誤存負數， 且擴大了表示范圍。
(二) 索引規約
1. 【強制】 業務上具有唯一特性的字段，即使是多個字段的組合，也必須建成唯一索引。
說明： 不要以為唯一索引影響了 insert 速度，這個速度損耗可以忽略，但提高查找速度是明顯的； 另外，即使在應用層做了非常完善的校驗控制，只要沒有唯一索引，根據墨菲定律，必然有臟數據產生。
2. 【強制】 超過三個表禁止 join。需要 join 的字段，數據類型必須絕對一致； 多表關聯查詢時，保證被關聯的字段需要有索引。
說明： 即使雙表 join 也要注意表索引、 SQL 性能。
3. 【強制】 在 varchar 字段上建立索引時，必須指定索引長度，沒必要對全字段建立索引，根據實際文本區分度決定索引長度即可。
說明： 索引的長度與區分度是一對矛盾體，一般對字符串類型數據，長度為 20 的索引，區分度會高達90%以上，可以使用 count(distinct left(列名, 索引長度))/count(*)的區分度來確定。
4. 【強制】 頁面搜索嚴禁左模糊或者全模糊，如果需要請走搜索引擎來解決。
說明： 索引文件具有 B-Tree 的最左前綴匹配特性，如果左邊的值未確定，那么無法使用此索引。
5. 【推薦】 如果有 order by 的場景，請注意利用索引的有序性。 order by 最后的字段是組合索引的一部分，并且放在索引組合順序的最后，避免出現 file_sort 的情況，影響查詢性能。
正例： where a=? and b=? order by c; 索引： a_b_c
反例： 索引如果存在范圍查詢， 那么索引有序性無法利用，如： WHERE a>10 ORDER BY b; 索引 a_b 無法排序。
6. 【推薦】 利用覆蓋索引來進行查詢操作， 避免回表。
說明： 如果一本書需要知道第 11 章是什么標題，會翻開第 11 章對應的那一頁嗎？目錄瀏覽一下就好，這個目錄就是起到覆蓋索引的作用。
正例： 能夠建立索引的種類分為主鍵索引、唯一索引、普通索引三種，而覆蓋索引只是一種查詢的一種效果，用 explain 的結果， extra 列會出現： using index。
7. 【推薦】 利用延遲關聯或者子查詢優化超多分頁場景。
說明： MySQL 并不是跳過 offset 行，而是取 offset+N 行，然后返回放棄前 offset 行，返回 N 行，那當offset 特別大的時候，效率就非常的低下，要么控制返回的總頁數，要么對超過特定閾值的頁數進行 SQL改寫。
正例： 先快速定位需要獲取的 id 段，然后再關聯：SELECT a.* FROM 表 1 a, (select id from 表 1 where 條件 LIMIT 100000,20 ) b where a.id=b.id
8. 【推薦】 SQL 性能優化的目標：至少要達到 range 級別， 要求是 ref 級別， 如果可以是consts 最好。
說明：
1） consts 單表中最多只有一個匹配行（主鍵或者唯一索引），在優化階段即可讀取到數據。
2） ref 指的是使用普通的索引（ normal index） 。
3） range 對索引進行范圍檢索。
反例： explain 表的結果， type=index，索引物理文件全掃描，速度非常慢，這個 index 級別比較 range還低，與全表掃描是小巫見大巫。
9. 【推薦】 建組合索引的時候，區分度最高的在最左邊。
正例： 如果 where a=? and b=? ， 如果 a 列的幾乎接近于唯一值，那么只需要單建 idx_a 索引即可。
說明： 存在非等號和等號混合時，在建索引時，請把等號條件的列前置。如： where c>? and d=? 那么即使 c 的區分度更高，也必須把 d 放在索引的最前列， 即索引 idx_d_c。
10.【推薦】 防止因字段類型不同造成的隱式轉換， 導致索引失效。
11.【參考】 創建索引時避免有如下極端誤解：
1） 寧濫勿缺。 認為一個查詢就需要建一個索引。
2） 寧缺勿濫。 認為索引會消耗空間、 嚴重拖慢記錄的更新以及行的新增速度。
3） 抵制惟一索引。 認為業務的惟一性一律需要在應用層通過“先查后插” 方式解決。
?
(三) SQL 語句
1. 【強制】 不要使用 count(列名)或 count(常量)來替代 count(*)， count(*)是 SQL92 定義的標準統計行數的語法，跟數據庫無關，跟 NULL 和非 NULL 無關。
說明： count(*)會統計值為 NULL 的行，而 count(列名)不會統計此列為 NULL 值的行。
2. 【強制】 count(distinct col) 計算該列除 NULL 之外的不重復行數， 注意 count(distinct col1, col2) 如果其中一列全為 NULL，那么即使另一列有不同的值，也返回為 0。
3. 【強制】 當某一列的值全是 NULL 時， count(col)的返回結果為 0，但 sum(col)的返回結果為 NULL，因此使用 sum()時需注意 NPE 問題。
正例： 使用如下方式來避免 sum 的 NPE 問題： SELECT IFNULL(SUM(column), 0) FROM table;
4. 【強制】 使用 ISNULL()來判斷是否為 NULL 值。
說明： NULL 與任何值的直接比較都為 NULL。
1） NULL<>NULL 的返回結果是 NULL， 而不是 false。
2） NULL=NULL 的返回結果是 NULL， 而不是 true。
3） NULL<>1 的返回結果是 NULL，而不是 true。
5. 【強制】 代碼中寫分頁查詢邏輯時，若 count 為 0 應直接返回，避免執行后面的分頁語句。
6. 【強制】 不得使用外鍵與級聯，一切外鍵概念必須在應用層解決。
說明： 以學生和成績的關系為例， 學生表中的 student_id 是主鍵，那么成績表中的 student_id 則為外鍵。如果更新學生表中的 student_id，同時觸發成績表中的 student_id 更新， 即為級聯更新。外鍵與級聯更新適用于單機低并發，不適合分布式、高并發集群；級聯更新是強阻塞，存在數據庫更新風暴的風險；外鍵影響數據庫的插入速度。
7. 【強制】 禁止使用存儲過程，存儲過程難以調試和擴展，更沒有移植性。
8. 【強制】 數據訂正（特別是刪除、 修改記錄操作） 時，要先 select，避免出現誤刪除，確認無誤才能執行更新語句。
9. 【推薦】 in 操作能避免則避免，若實在避免不了，需要仔細評估 in 后邊的集合元素數量，控制在 1000 個之內。
10.【參考】 如果有國際化需要，所有的字符存儲與表示，均以 utf-8 編碼，注意字符統計函數的區別。
說明：
SELECT LENGTH("輕松工作")； 返回為 12
SELECT CHARACTER_LENGTH("輕松工作")； 返回為 4
如果需要存儲表情，那么選擇 utf8mb4 來進行存儲，注意它與 utf-8 編碼的區別。
11.【參考】 TRUNCATE TABLE 比 DELETE 速度快，且使用的系統和事務日志資源少，但TRUNCATE 無事務且不觸發 trigger，有可能造成事故，故不建議在開發代碼中使用此語句。
說明： TRUNCATE TABLE 在功能上與不帶 WHERE 子句的 DELETE 語句相同。
(四) ORM 映射
1. 【強制】 在表查詢中，一律不要使用 * 作為查詢的字段列表，需要哪些字段必須明確寫明。
說明： 1）增加查詢分析器解析成本。 2）增減字段容易與 resultMap 配置不一致。 3）無用字段增加網絡消耗，尤其是 text 類型的字段。
2. 【強制】 POJO 類的布爾屬性不能加 is，而數據庫字段必須加 is_，要求在 resultMap 中進行字段與屬性之間的映射。
說明： 參見定義 POJO 類以及數據庫字段定義規定，在<resultMap>中增加映射，是必須的。 在MyBatis Generator 生成的代碼中，需要進行對應的修改。
3. 【強制】 不要用 resultClass 當返回參數，即使所有類屬性名與數據庫字段一一對應，也需要定義；反過來，每一個表也必然有一個 POJO 類與之對應。
說明： 配置映射關系，使字段與 DO 類解耦，方便維護。
4. 【強制】 sql.xml 配置參數使用： #{}， #param# 不要使用${} 此種方式容易出現 SQL 注入。
5. 【強制】 iBATIS 自帶的 queryForList(String statementName,int start,int size)不推薦使用。
說明： 其實現方式是在數據庫取到 statementName 對應的 SQL 語句的所有記錄，再通過 subList 取start,size 的子集合。
正例：
6. 【強制】 不允許直接拿 HashMap 與 Hashtable 作為查詢結果集的輸出。
說明： resultClass=”Hashtable” ， 會置入字段名和屬性值，但是值的類型不可控。
7. 【強制】 更新數據表記錄時，必須同時更新記錄對應的 gmt_modified 字段值為當前時間。
8. 【推薦】 不要寫一個大而全的數據更新接口。 傳入為 POJO 類，不管是不是自己的目標更新字段，都進行 update table set c1=value1,c2=value2,c3=value3; 這是不對的。執行 SQL時， 不要更新無改動的字段，一是易出錯；二是效率低；三是增加 binlog 存儲。
9. 【參考】 @Transactional 事務不要濫用。事務會影響數據庫的 QPS，另外使用事務的地方需要考慮各方面的回滾方案，包括緩存回滾、搜索引擎回滾、消息補償、統計修正等。
10.【參考】 <isEqual>中的 compareValue 是與屬性值對比的常量，一般是數字，表示相等時帶上此條件； <isNotEmpty>表示不為空且不為 null 時執行； <isNotNull>表示不為 null 值時執行。
?
?
總結
以上是生活随笔為你收集整理的熟读《阿里巴巴java开发手册》（五、 MySQL 数据库）的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 熟读《阿里巴巴java开发手册》（三、单
- 下一篇： 熟读《阿里巴巴java开发手册》（六、工