DBA操作規範

1、涉及業務上的修改/刪除資料，在得到業務方、CTO的郵件批准後方可執行，執行前提前做好備份，必要時可逆。

2、所有上線需求必須走工單系統，口頭通知視為無效。

3、在對大表做表結構變更時，如修改欄位屬性會造成鎖表，並會造成從庫延遲，從而影響線上業務，必須在凌晨0:00 後業務低峰期執行，另統一用工具 pt-online-schema-change 避免鎖表且降低延遲執行時間。

使用範例：

#pt-online-schema-change --alter="add index IX_id_no(id_no)"

--no-check-replication-filters --recursion-method=none --user=dba

--password=123456 D=test,t=t1 --execute

對於MongoDB建立索引要在後臺建立，避免鎖表。

使用範例：

db.t1.createIndex({idCardNum:1},{background:1})

4、所有線上業務庫均必須搭建MHA高可用架構，避免單點問題。

5、給業務方開許可權時，密碼要用MD5加密，至少16位。許可權如沒有特殊要求，均為select查詢許可權，並做庫表級限制。

6、刪除預設空密碼賬號。

delete from mysql.user where user='' and password='';

flush privileges;

7、彙總庫開啟Audit審計日誌功能，出現問題時方可追溯。

行為規範

8、禁止一個MySQL例項存放多個業務資料庫，會造成業務耦合性過高，一旦出現問題會殃及池魚，增加了定位故障問題的難度。通常採用多例項解決，一個例項一個業務庫，互不干擾。

9、禁止在主庫上執行後臺管理和統計類的功能查詢，這種複雜類的SQL會造成CPU的升高，進而會影響業務。

10、批次清洗資料，需要開發和DBA共同進行審查，應避開業務高峰期時段執行，並在執行過程中觀察服務狀態。

11、促銷活動等應提前與DBA當面溝通，進行流量評估，比如提前一週增加機器記憶體或擴充套件架構，防止DB出現效能瓶頸。

12、禁止線上上做資料庫壓力測試。

基本規範

13、禁止在資料庫中儲存明文密碼。

14、使用InnoDB儲存引擎。

支援事務，行級鎖，更好的恢復性，高併發下效能更好。

InnoDB表避免使用COUNT(*)操作，因內部沒有計數器，需要一行一行累加計算，計數統計實時要求較強可以使用memcache或者Redis。

15、表字符集統一使用UTF8。

不會產生亂碼風險。

16、所有表和欄位都需要新增中文註釋。

方便他人、方便自己。

17、不在資料庫中儲存圖片、檔案等大資料。

圖片、檔案更適合於GFS分散式檔案系統，資料庫裡存放超連結即可。

18、避免使用儲存過程、檢視、觸發器、事件。

MySQL是OLTP應用，最擅長簡單的增、刪、改、查操作，但對邏輯計算分析類的應用，並不適合，所以這部分的需求最好透過程式上實現。

19、避免使用外來鍵，外來鍵用來保護參照完整性，可在業務端實現。

外來鍵會導致父表和子表之間耦合，十分影響SQL效能，出現過多的鎖等待，甚至會造成死鎖。

20、對事務一致性要求不高的業務，如日誌表等，優先選擇存入MongoDB。

其自身支援的sharding分片功能，增強了橫向擴充套件的能力，開發不用過多調整業務程式碼。

庫表設計規範

21、表必須有主鍵，例如自增主鍵。

這樣可以保證資料行是按照順序寫入，對於SAS傳統機械式硬碟寫入效能更好，根據主鍵做關聯查詢的效能也會更好，並且還方便了資料倉儲抽取資料。從效能的角度來說，使用UUID作為主鍵是個最不好的方法，它會使插入變得隨機。

22、禁止使用分割槽表。

分割槽表的好處是對於開發來說，不用修改程式碼，透過後端DB的設定，比如對於時間欄位做拆分，就可以輕鬆實現表的拆分。但這裡面涉及一個問題，查詢的欄位必須是分割槽鍵，否則會遍歷所有的分割槽表，並不會帶來效能上的提升。此外，分割槽表在物理結構上仍舊是一張表，此時我們更改表結構，一樣不會帶來效能上的提升。所以應採用切表的形式做拆分，如程式上需要對歷史資料做查詢，可透過union all的方式關聯查詢。另外隨著時間的推移，歷史資料表不再需要，只需在從庫上dump出來，即便捷地遷移至備份機上。

欄位設計規範

23、用DECIMAL代替FLOAT和DOUBLE儲存精確浮點數。

浮點數的缺點是會引起精度問題，請看下面一個例子：

mysql> CREATE TABLE t3 (c1 float(10,2),c2 decimal(10,2));

Query OK, 0 rows affected (0.05 sec)

>mysql> insert into t3 values (999998.02, 999998.02);

Query OK, 1 row affected (0.01 sec)

>mysql> select * from t3;

+-----------+-----------+

| c1 | c2 |

+-----------+-----------+

| 999998.00 | 999998.02 |

+-----------+-----------+

1 row in set (0.00 sec)

可以看到c1列的值由999998.02變成了999998.00，這就是float浮點數型別的不精確性造成的。因此對貨幣等對精度敏感的資料，應該用定點數表示或儲存。

24、使用TINYINT來代替ENUM型別。

採用enum列舉型別，會存在擴充套件的問題，例如使用者線上狀態，如果此時增加了：5表示請勿打擾、6表示開會中、7表示隱身對好友可見，那麼增加新的ENUM值要做DDL修改表結構操作了。

25、欄位長度儘量按實際需要進行分配，不要隨意分配一個很大的容量。

選擇欄位的一般原則是保小不保大，能用佔用位元組少的欄位就不用大欄位。比如主鍵，強烈建議用int整型，不用uuid，為什麼？省空間啊。空間是什麼？空間就是效率！按4個位元組和按32個位元組定位一條記錄，誰快誰慢太明顯了。涉及幾個表做join時，效果就更明顯了。更小的欄位型別佔用的記憶體就更少，佔用的磁碟空間和磁碟I/O也會更少，而且還會佔用更少的頻寬。

有不少開發人員在設計表欄位時，只要是針對數值型別的全部用int，但這不一定合適，就比如使用者的年齡，一般來說，年齡大都在1~100歲之間，長度只有3，那麼用int就不適合了，可以用tinyint代替。又比如使用者線上狀態，0表示離線、1表示線上、2表示離開、3表示忙碌、4表示隱身等，其實類似這樣的情況，用int都是沒有必要的，浪費空間，採用tinyint完全可以滿足需要，int佔用的是4位元組，而tinyint才佔用1個位元組。

int整型有符號（signed）最大值是2147483647，而無符號（unsigned）最大值是4294967295，如果你的需求沒有儲存負數，那麼建議改成有符號（unsigned），可以增加int儲存範圍。

int(10)和int(1)沒有什麼區別，10和1僅是寬度而已，在設定了zerofill擴充套件屬性的時候有用，例：

root@localhost(test)10:39>create table test(id int(10) zerofill,id2 int(1));

Query OK, 0 rows affected (0.13 sec)

root@localhost(test)10:39>insert into test values(1,1);

Query OK, 1 row affected (0.04 sec)

root@localhost(test)10:56>insert into test values(1000000000,1000000000);

Query OK, 1 row affected (0.05 sec)

root@localhost(test)10:56>select * from test;

+------------+------------+

| id | id2 |

+------------+------------+

| 0000000001 | 1 |

| 1000000000 | 1000000000 |

+------------+------------+

2 rows in set (0.01 sec)

26、欄位定義為NOT NULL要提供預設值。

從應用層角度來看，可以減少程式判斷程式碼，比如你要查詢一條記錄，如果沒預設值，你是不是得先判斷該欄位對應變數是否被設定，如果沒有，你得透過java把該變數置為''或者0，如果設了預設值，判斷條件可直接略過。

NULL值很難進行查詢最佳化，它會使索引統計更加複雜，還需要MySQL內部進行特殊處理。

27、儘可能不使用TEXT、BLOB型別。

增加儲存空間的佔用，讀取速度慢。

索引規範

28、索引不是越多越好，按實際需要進行建立。

索引是一把雙刃劍，它可以提高查詢效率但也會降低插入和更新的速度並佔用磁碟空間。適當的索引對應用的效能至關重要，而且在MySQL中使用索引它的速度是極快的。遺憾的是，索引也有相關的開銷。每次向表中寫入時（如INSERT、UPDATEH或DELETE），如果帶有一個或多個索引，那麼MySQL也要更新各個索引，這樣索引就增加了對各個表的寫入操作的開銷。只有當某列被用於WHERE子句時，才能享受到索引的效能提升的好處。如果不使用索引，它就沒有價值，而且會帶來維護上的開銷。

29、查詢的欄位必須建立索引。

如：1、SELECT、UPDATE、DELETE語句的WHERE條件列；2、多表JOIN的欄位。

30、不在索引列進行數學運算和函式運算。

無法使用索引，導致全表掃描。

例：SELECT * FROM t WHERE YEAR(d) >= 2016;

由於MySQL不像Oracle那樣支援函式索引，即使d欄位有索引，也會直接全表掃描。

應改為----->

SELECT * FROM t WHERE d >= '2016-01-01';

31、不在低基數列上建立索引，例如‘性別’。

有時候，進行全表瀏覽要比必須讀取索引和資料表更快，尤其是當索引包含的是平均分佈的資料集是更是如此。對此典型的例子是性別，它有兩個均勻分佈的值（男和女）。透過性別需要讀取大概一半的行。在種情況下進行全表掃描瀏覽要更快。

32、不使用%前導的查詢，如like ‘%xxx’。

無法使用索引，導致全表掃描。

低效查詢

SELECT * FROM t WHERE name LIKE '%de%';

----->

高效查詢

SELECT * FROM t WHERE name LIKE 'de%';

33、不使用反向查詢，如 not in / not like。

無法使用索引，導致全表掃描。

34、避免冗餘或重複索引。

聯合索引IX_a_b_c(a,b,c) 相當於 (a) 、(a,b) 、(a,b,c)，那麼索引 (a) 、(a,b) 就是多餘的。

SQL設計規範

*35、不使用SELECT ，只獲取必要的欄位。**

消耗CPU和IO、消耗網路頻寬；

無法使用覆蓋索引。

36、用IN來替換OR。

低效查詢

SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;

----->

高效查詢

SELECT * FROM t WHERE LOC_IN IN (10,20,30);

37、避免資料型別不一致。

SELECT * FROM t WHERE id = '19';

----->

SELECT * FROM t WHERE id = 19;

38、減少與資料庫的互動次數。

INSERT INTO t (id, name) VALUES(1,'Bea');

INSERT INTO t (id, name) VALUES(2,'Belle');

INSERT INTO t (id, name) VALUES(3,'Bernice');

----->

INSERT INTO t (id, name) VALUES(1,'Bea'), (2,'Belle'),(3,'Bernice');

Update … where id in (1,2,3,4);

Alter table tbl_name add column col1, add column col2;

39、拒絕大SQL，拆分成小SQL。

低效查詢

SELECT * FROM tag

JOIN tag_post ON tag_post.tag_id = tag.id

JOIN post ON tag_post.post_id = post.id

WHERE tag.tag = 'mysql';

可以分解成下面這些查詢來代替

----->

高效查詢

SELECT * FROM tag WHERE tag = 'mysql'

SELECT * FROM tag_post WHERE tag_id = 1234

SELECT * FROM post WHERE post_id in (123, 456, 567, 9098, 8904);

40、禁止使用order by rand()

SELECT * FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 4;

---->

SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4;

每一份讚賞源於懂得

DBA的40條軍規

相關文章