MySQL開發規范與使用技巧總結

命名規范

1.庫名、表名、欄位名必須使用小寫字母，并采用下劃線分割，

a)MySQL有配置引數lower_case_table_names，不可動態更改，Linux系統默認為 0，即庫表名以實際情況存盤，大小寫敏感，如果是1，以小寫存盤，大小寫不敏感，如果是2，以實際情況存盤，但以小寫比較，

b)如果大小寫混合使用，可能存在abc，Abc，ABC等多個表共存，容易導致混亂，

c)欄位名顯示區分大小寫，但實際使?用不區分，即不可以建立兩個名字一樣但大小寫不一樣的欄位，

d)為了統一規范， 庫名、表名、欄位名使用小寫字母，

2.庫名、表名、欄位名禁止超過32個字符，

庫名、表名、欄位名支持最多64個字符，但為了統一規范、易于辨識以及減少傳輸量，禁止超過32個字符，

3.使用INNODB存盤引擎，

INNODB引擎是MySQL5.5版本以后的默認引擘，支持事務、行級鎖，有更好的資料恢復能力、更好的并發性能，同時對多核、大記憶體、SSD等硬體支持更好，支持資料熱備份等，因此INNODB相比MyISAM有明顯優勢，

4.庫名、表名、欄位名禁止使用MySQL保留字，

當庫名、表名、欄位名等屬性含有保留字時，SQL陳述句必須用反引號參考屬性名稱，這將使得SQL陳述句書寫、SHELL腳本中變數的轉義等變得?非常復雜，

5.禁止使用磁區表，

磁區表對磁區鍵有嚴格要求；磁區表在表變大后，執?行DDL、SHARDING、單表恢復等都變得更加困難，因此禁止使用磁區表，并建議業務端手動SHARDING，

6.建議使用UNSIGNED存盤非負數值，

同樣的位元組數，非負存盤的數值范圍更大，如TINYINT有符號為 -128-127，無符號為0-255，

7.建議使用INT UNSIGNED存盤IPV4，

用UNSINGED INT存盤IP地址占用4位元組，CHAR(15)則占用15位元組，另外，計算機處理整數型別比字串型別快，使用INT UNSIGNED而不是CHAR(15)來存盤IPV4地址，通過MySQL函式inet_ntoa和inet_aton來進行轉化，IPv6地址目前沒有轉化函式，需要使用DECIMAL或兩個BIGINT來存盤，例如：

SELECT INET_ATON('209.207.224.40'); 3520061480SELECT INET_NTOA(3520061480);

209.207.224.40

8.強烈建議使用TINYINT來代替ENUM型別，ENUM型別在需要修改或增加列舉值時，需要在線DDL，成本較高；ENUM列值如果含有數字型別，可能會引起默認值混淆，

9.使用VARBINARY存盤大小寫敏感的變長字串或二進制內容，

VARBINARY默認區分大小寫，沒有字符集概念，速度快，

http://10.INT型別固定占用4位元組存盤

例如INT(4)僅代表顯示字符寬度為4位，不代表存盤長度，數值型別括號后面的數字只是表示寬度而跟存盤范圍沒有關系，比如INT(3)默認顯示3位，空格補齊，超出時正常顯示，Python、Java客戶端等不具備這個功能，

11.區分使用DATETIME和TIMESTAMP，

存盤年使用YEAR型別，存盤日期使用DATE型別，存盤時間(精確到秒)建議使用TIMESTAMP型別，

DATETIME和TIMESTAMP都是精確到秒，優先選擇TIMESTAMP，因為TIMESTAMP只有4個位元組，而DATETIME8個位元組，同時TIMESTAMP具有自動賦值以及?自動更新的特性，注意：在5.5和之前的版本中，如果一個表中有多個timestamp列，那么最多只能有一列能具有自動更新功能，如何使用TIMESTAMP的自動賦值屬性?

a)自動初始化，而且自動更新：

column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP

b)只是自動初始化：

column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP

c)自動更新，初始化的值為0：

column1 TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP

d)初始化的值為0：

1 column1 TIMESTAMP DEFAULT 0

12.索引欄位均定義為NOT NULL，

a)對表的每一行，每個為NULL的列都需要額外的空間來標識，

b)B樹索引時不會存盤NULL值，所以如果索引欄位可以為NULL，索引效率會下降，

c)建議用0、特殊值或空串代替NULL值，

MySQL使用技巧

1.將大欄位、訪問頻率低的欄位拆分到單獨的表中存盤，分離冷熱資料，

有利于有效利用快取，防?止讀入無用的冷資料，較少磁盤IO，同時保證熱資料常駐記憶體提?高快取命中率，

2.禁止在資料庫中存盤明文密碼，

采用加密字串存盤密碼，并保證密碼不可解密，同時采用隨機字串加鹽保證密碼安全，

3.表必須有主鍵，推薦使用UNSIGNED自增列作為主鍵，

表沒有主鍵，INNODB會默認設定隱藏的主鍵列；沒有主鍵的表在定位資料行的時候非常困難，也會降低基于行復制的效率，

4.禁止冗余索引，

索引是雙刃劍，會增加維護負擔，增?大IO壓力，(a,b,c)、(a,b)，后者為冗余索引，可以利用前綴索引來達到加速目的，減輕維護負擔

，5.禁止重復索引，

primary key a;uniq index a;重復索引增加維護負擔、占用磁盤空間，同時沒有任何益處，

6.不在低基數列上建立索引，例如“性別”，

大部分場景下，低基數列上建立索引的精確查找，相對于不建立索引的全表掃描沒有任何優勢，而且增大了IO負擔，

7.合理使用覆寫索引減少IO，避免排序，

覆寫索引能從索引中獲取需要的索引欄位，從?而避免回表進行二次查找，節省IO，

INNODB存盤引擎中，secondary index(非主鍵索引，又稱為輔助索引、二級索引)沒有直接存盤行地址，而是存盤主鍵值，

如果用戶需要查詢secondary index中所不包含的資料列，則需要先通過secondary index查找到主鍵值，然后再通過主鍵查詢到其他資料列，因此需要查詢兩次，覆寫索引則可以在?一個索引中獲取所有需要的資料，因此效率較高，

例如SELECT email，uid FROM user_email WHERE uid=xx，如果uid不是主鍵，適當時候可以將索引添加為index(uid，email)，以獲得性能提升，

8.用IN代替OR，SQL陳述句中IN包含的值不應過多，應少于1000個，

IN是范圍查找，MySQL內部會對IN的串列值進行排序后查找，比OR效率更高，

9.表字符集使用UTF8，必要時可申請使用UTF8MB4字符集，

a)UTF8字符集存盤漢字占用3個位元組，存盤英文字符占用一個位元組，

b)UTF8統一而且通用，不會出現轉碼出現亂碼風險，

c)如果遇到EMOJ等表情符號的存盤需求，可申請使用UTF8MB4字符集，

10.用UNION ALL代替UNION，

UNION ALL不需要對結果集再進行排序，

11.禁止使用order by rand()，

order by rand()會為表增加一個偽列，然后用rand()函式為每一行資料計算出rand()值，然后基于該行排序，這通常都會生成磁盤上的臨時表，因此效率非常低，建議先使用rand()函式獲得隨機的主鍵值，然后通過主鍵

獲取資料，

12.建議使用合理的分頁方式以提高分頁效率，

假如有類似下面分頁陳述句:

SELECT * FROM table ORDER BY TIME DESC LIMIT 10000，10;

這種分頁方式會導致大量的io，因為MySQL使用的是提前讀取策略，

推薦分頁方式:

SELECT * FROM table WHERE TIME<last_TIME ORDER BY TIME DESC

LIMIT 10.SELECT * FROM table inner JOIN (SELECT id FROM table

ORDER BY TIME LIMIT 10000，10) as tUSING(id)

13.SELECT只獲取必要的欄位，禁?止使用SELECT *，減少網路帶寬消耗；

能有效利用覆寫索引；

表結構變更對程式基本無影響，

14.SQL中避免出現now()、rand()、sysdate()、current_user()等不確定結果的函式，

陳述句級復制場景下，引起主從資料不一致；不確定值的函式，產?生的SQL陳述句無法利用QUERY CACHE，

15.采用合適的分庫分表策略，例如千庫十表、十庫百表等，

采用合適的分庫分表策略，有利于業務發展后期快速對資料庫進行水平拆分，同時分庫可以有效利?用MySQL

的多執行緒復制特性，

16.減少與資料庫互動次數，盡量采用批量SQL陳述句，

使用下面的陳述句來減少和db的互動次數:

a)INSERT ... ON DUPLICATE KEY UPDATE
b)REPLACE INTO
c)INSERT IGNORE 
d)INSERT INTO VALUES()

17.拆分復雜SQL為多個小SQL，避免大事務，

簡單的SQL容易使?用到MySQL的QUERY CACHE；減少鎖表時間特別是MyISAM；可以使用多核 CPU，18.對同一個表的多次alter操作必須合并為一次操作，

mysql對表的修改絕大部分操作都需要鎖表并重建表，而鎖表則會對線上業務造成影響，為減少這種影響，必須把對表的多次alter操作合并為一次操作，例如，要給表t增加一個欄位b，同時給已有的欄位aa建立索引，

通常的做法分為兩步：

alter table t add column b varchar(10);
然后增加索引：
alter table t add index idx_aa(aa);
正確的做法是：
alter table t add column b varchar(10),add index idx_aa(aa);

19.避免使用存盤程序、觸發器、視圖、自定義函式等，

這些高級特性有性能問題，以及未知BUG較多，業務邏輯放到資料庫會造成資料庫的DDL、SCALE OUT、

SHARDING等變得更加困難，

20.禁止有super權限的應用程式賬號存在，

安全第一，super權限會導致read only失效，導致較多詭異問題而且很難追蹤，

21.不要在MySQL資料庫中存放業務邏輯，

資料庫是有狀態的服務，變更復雜而且速度慢，如果把業務邏輯放到資料庫中，將會限制業務的快速發展，建議把業務邏輯提前，放到前端或中間邏輯層，而把資料庫作為存盤層，實作邏輯與存盤的分離，

標籤：MySQL

上一篇：mysql的鎖機制詳解

下一篇：mysql備份