開發中 MySQL 規范

一、建表規范

　
1、數據庫名、表名、字段名必須使用小寫字母或數字，并且禁止以數字開頭

示例：goods_category、agent_operate_201812_log

　
?
2、數據庫名、表名、字段名要做到見名識意

示例：goods_category，不能 gc

　
?
3、配置表建議以 xx_config 形式命名

示例：shop_payment_config

　
?
4、日志表建議以 xx_log 形式命名

示例：system_log

　
?
5、臨時表建議以 temp_xx 形式命名

示例：temp_order_info_export

　
?
6、創建時間使用 create_time，更新時間使用 update_time

類型使用 int(11) unsigned

　
?
7、字段類型為字符串時需要注意的

如果存儲的字符串長度幾乎相等，則應該使用 char 定長字符串類型。

如果長度超過5000個字符，則應該將字段類型定義為 text，并獨立出來一張表，用主鍵對應，避免影響其他字段的索引效率。

　
?
8、字段允許適當冗余，以提高查詢性能，但必須考慮數據一致性

　
?
9、單表行數超過 500 萬行或者單表容量超過 2GB 時，才推薦進行分庫分表

　
?
10、當存儲的字段為小數時，數據類型設置為 decimal，禁止使用 float 和 double

在存儲的時候，float 和 double 存在精度損失的問題，很可能在比較值的時候，得到不正確的結果。

如果存儲的數據范圍超過 decimal 的范圍，建議將數據拆成整數和小數分開存儲。

存儲商品價格時，統一轉為分，存儲類型應為整型 int。

　
?
11、沒有特殊要求的情況下，所有的數據表必須使用 Innodb 存儲引擎

Innodb 支持事務，支持行級鎖，擁有更好的并發性能和恢復性。

　
?
12、數據庫和數據表的字符集統一使用 utf8，需要存儲 emoji 表情的使用 utf8mb4

　
?
13、所有數據表和字段必須寫 comment 注釋說明

有條件盡量建立數據字典。

　
?
14、盡量做到冷熱數據分離，減小表的寬度

表越寬，把表裝進內存緩沖池時所占用的內存也就越大，也會消耗更多的 IO。

　
?
15、禁止在數據表中建立預留字段

預留字段的命名很難做到見名識意，并且無法選擇合適的類型，而且對預留字段修改時，會對整張表進行鎖定。

　
?
16、禁止在數據庫中存儲圖片、文件等大的二進制數據

文件很大時，IO 將會很耗時，也會占用很多帶寬，影響響應速度。

建議圖片、視頻、大文件統一存儲在文件存儲空間，比如阿里云、騰訊云的對象存儲空間和文件存儲空間，數據庫中只記錄文件地址。

　
?
17、設置合適的字符存儲長度

對象 ?	年齡區間	類型 ?	字節	表示范圍
人 ?	150歲以內	unsigned tinyint	1	無符號值 0~255
烏龜 ?	數百歲	unsigned smallint	2	無符號值 0~65535
恐龍化石	數千萬年	unsigned int	4	無符號值 0~約42.9億
太陽 ?	約50億年	unsigned bigint	8	無符號值 0~約10^19

18、條件允許，就將字符串轉換成數字類型存儲

比如存儲ip時，使用 ip2long 和 long2ip

　
?
19、避免使用 enum 類型存儲字段

enum 類型的 orderby 操作效率低。

　
?
20、建議把所有列定義為 NOT NULL

索引 null 列需要額外的空間來保存，要占用更多空間。進行比較時和計算時要對 null 值進行特別處理。

　
?
21、禁止在開發環境、測試環境直接連接生產環境數據庫

　
?二、索引規范

　
1、業務上具有唯一特性的字段，即使是多個字段的組合，也必須建成唯一索引

唯一索引影響 insert 的速度可以忽略不計，但會明顯提高查詢速度。

另外，即使在應用層做了非常完善的校驗控制，只要沒有唯一索引，根據墨菲定律，必然會有臟數據產生。

　
?
2、禁止3個表以上join。需要join的字段，數據類型必須一致，當多表關聯時，保證被關聯的字段有索引

　?
3、限制每張表上的索引數量，盡量不超過5個

索引增加查詢效率的同時，也會降低插入和更新的效率，甚至有時會降低查詢效率。

mysql優化器在選擇如何優化查詢時，會根據統一信息，對每一個可以用到的索引進行評估，以生成一個最佳的執行計劃。

如果同時有很多個索引都可以用于查詢，就會增加mysql優化器生成執行計劃的時間，進而降低查詢性能。

　
?
4、在 varchar 字段上建立索引時，必須指定索引長度

沒必要對全字段建立索引，根據實際文本區分度決定索引長度即可。一般對字符串數據，長度為20的索引，區分度就會高達 90%。

可以使用下列語句來確定區分度。

count(distinct left (列名,索引長度)) / count(*)

　
5、頁面搜索嚴禁左模糊或全模糊，如果需要，請使用搜索引擎解決

索引文件具有最左匹配特性，如果左邊的值未確定，則無法使用此索引。

　
?
6、如果有order by 的場景，請注意利用索引的有序性

正例：where a=5 and b=10 order by c; #索引 a_b_c 生效反例：where a>10 order by b #索引中有范圍查找，索引 a_b 不生效

　
?
7、使用延遲關聯或者子查詢優化超多分頁場景

MySQL 并不是跳過 offset 行，而是取 offset + n 行。

當 offset 特別大時，效率將會非常低，要么控制返回的總頁數，要么對超過特定閥值的頁數進行 SQL 改寫。

正例：先快速定位需要獲取的 id 段，然后再關聯。

SELECT a.* FROM 表1 a, (select id from 表1 where 條件 LIMIT 100000,20 ) b where a.id=b.id

實例對比：

select a.* from agent_admin a, 
(select agent_admin_id from agent_admin where admin_id = 11400 limit 1000,5
) b 
where a.agent_admin_id=b.agent_admin_id;
0.017sSELECT * from agent_admin where admin_id = 11400 limit 1000,5;
0.023S

　
?
8、建立組合索引時，區分度最高的放在最左邊

　
?
9、哪些字段最好建索引

（1）經常出現在 where 從句的字段

（2）包含在 order by，group by、distinct 中的字段

　
?
10、避免建立重復索引和冗余索引

建立冗余索引，ui增加查詢優化器生成執行計劃的時間

// 重復索引示例
primary key(id)
index(id)
unique key(id)

// 冗余索引示例
index(a,b,c)
index(a,b)
index(a)

　
?
11、創建索引時盡量避免如下誤解

（1）寧濫勿缺：認為一個查詢就需要建立一個索引

（2）寧缺毋濫：任務索引會消耗空間、嚴重拖慢更新和新增速度

（3）抵制唯一索引：認為業務的唯一性一律需要在應用層通過“先查后插”的方式解決

　
三、SQL 開發規范

　
?
1、不要使用 count(列名) 或 count(常量) 來替代 count(\*）

count(\*) 是 SQL92 定義的標準統計行數的語法，count(\*) 會統計值為NULL的行，而count(列名) 不會統計此列值為 null 的行。

　
2、在代碼中寫分頁查詢邏輯時，如果 count 為 0 ，應直接返回結果，避免繼續執行后面的程序再返回結果

　
?
3、禁止使用存儲過程

存儲過程難以調試和擴展，新人接手麻煩，可移植性差。

　
?
4、禁止使用外鍵與級聯，一切外鍵概念必須在應用層解決

以學生和成績的關系為例，學生表中的student_id是主鍵，那么成績表中的student_id則為外鍵。如果更新學生表中的 student_id,同時觸發成績表中的student_id更新，即為級聯更新。

外鍵與級聯更新適用于單機低并發，不適合分布式、高并發集群；

級聯更新是強阻塞，存在數據庫更新風暴的風險；外鍵影響數據庫的插入速度。

　
?
5、建議使用預編譯語句進行數據庫操作

盡量使用框架自帶的查詢構造器，其底層均封裝了預編譯處理。如果特殊情況使用不了框架的查詢構造器，也要手動預編譯查詢。

預編譯語句可以重復使用優化查詢器生成的執行計劃，減少 SQL編譯所需要的時間，還可以解決動態 SQL 所帶來的的 SQL 注入問題。

　
?
6、避免數據類型的隱式轉換

select user_name,age from admin where admin_id = '11140';

　
?
7、禁止使用 select \*，必須指定要查詢的具體字段

（1）無法使用覆蓋索引

注：覆蓋索引的含義是 select 的數據列只從索引中就能夠取得，不必讀取數據行，換句話說查詢列已經被所建的索引覆蓋。

（2）消耗更多的 cpu 和 IO 以及網絡帶寬資源。

　
?
8、避免使用子查詢，可以把子查詢優化成join查詢

子查詢的結果集無法使用索引，通常子查詢的結果集會被存儲到臨時表中，不論是內存臨時表還是磁盤臨時表都不會存在索引，所以查詢性能會受到一定的影響。特別是對于返回結果集比較大的子查詢，其對查詢性能的影響也就越大。

注：通常子查詢在in子句中，并且子查詢中為簡單SQL(不包含union、group by、order by、limit從句)時,才可以把子查詢轉化為關聯查詢進行優化。

　
?
9、避免使用JOIN關聯太多的表

對于Mysql來說，是存在關聯緩存的，緩存的大小可以由join_buffer_size參數進行設置。

在 Mysql 中，對于同一個 SQL 多關聯一個表，就會多分配一個關聯緩存，在一個SQL 中，關聯的表越多，所占用的內存就越大。

如果程序中大量的使用了多表關聯的操作，同時 join_buffer_size 設置的也不合理的情況下，就容易造成服務器內存溢出的情況，進而影響到服務器數據庫性能的穩定性。

MySQL 最多允許關聯61個表，建議不超過5個

　
?
10、減少和數據庫的交互操作

合并多個相同的操作到一起，可以提高處理效率。比如批量更新時，將語句處理合并后，在提交到 MySQL 中進行處理，這樣效率會更高。

一定要避免在循環中執行 SQL。

　
?
11、對同一列進行 or 判斷時，使用 in 代替 or

舉例：

select user_name,age from admin where city in (1024, 1028);

in 操作可以更有效的利用索引，or 大多數情況下很少能利用到索引。但需要注意的是，in 的值不要超過500個。

　
?
12、禁止在 SQL 語句中進行函數轉換和計算

將數據取出來再在程序中進行處理，比如格式化時間和轉換ip時。

13、在明顯不會有重復值時使用 UNINON ALL，而不是 UNION

UNION 會把兩個結果集的所有數據放到臨時表，再進行去重操作

UNINON ALL不會再對結果集進行去重操作

　
?
14、拆分復雜的大 SQL 為多個小 SQL

SQL 拆分后可以通過并行執行來提高處理效率。

　
?
15、大批量操作分批執行

大批量修改數據，會造成表中大量數據行被鎖定，從而造成大量的阻塞。

長時間的阻塞會占滿數據庫所有的可用連接，使生產環境中的其他應用無法連接到數據庫。

因此一定要注意大批量寫操作一定要分批執行。

原文鏈接：https://www.haveyb.com/article/149