一、 表設計
- 庫名、表名、字段名必須使用小寫字母，“_”分割。
- 庫名、表名、字段名必須不超過12個字符。
- 庫名、表名、字段名見名知意,建議使用名詞而不是動詞。
- 建議使用InnoDB存儲引擎。
- 存儲精確浮點數必須使用DECIMAL替代FLOAT和DOUBLE。
- 建議使用UNSIGNED存儲非負數值。
- 建議使用INT UNSIGNED存儲IPV4。
- 整形定義中不添加長度，比如使用INT，而不是INT(4)。
- 使用短數據類型，比如取值范圍為0-80時，使用TINYINT UNSIGNED。
- 不建議使用ENUM類型，使用TINYINT來代替。
- 盡可能不使用TEXT、BLOB類型。
- VARCHAR(N)，N表示的是字符數不是字節數，比如VARCHAR(255)，可以最大可存儲255個漢字，需要根據實際的寬度來選擇N。
- VARCHAR(N)，N盡可能小，因為MySQL一個表中所有的VARCHAR字段最大長度是65535個字節，進行排序和創建臨時表一類的內存操作時，會使用N的長度申請內存。
- 表字符集選擇UTF8。
- 使用VARBINARY存儲變長字符串。
- 存儲年使用YEAR類型。
- 存儲日期使用DATE類型。
- 存儲時間（精確到秒）建議使用TIMESTAMP類型，因為TIMESTAMP使用4字節，DATETIME使用8個字節。
- 建議字段定義為NOT NULL。
- 將過大字段拆分到其他表中。
- 禁止在數據庫中使用VARBINARY、BLOB存儲圖片、文件等。
- 表結構變更需要通知DBA審核。
二、 索引
- 非唯一索引必須按照“idx_字段名稱_字段名稱[_字段名]”進行命名。
- 唯一索引必須按照“uniq_字段名稱_字段名稱[_字段名]”進行命名。
- 索引名稱必須使用小寫。
- 索引中的字段數建議不超過5個。
- 單張表的索引數量控制在5個以內。
- 唯一鍵由3個以下字段組成，并且字段都是整形時，使用唯一鍵作為主鍵。
- 沒有唯一鍵或者唯一鍵不符合5中的條件時，使用自增（或者通過發號器獲取）id作為主鍵。
- 唯一鍵不和主鍵重復。
- 索引字段的順序需要考慮字段值去重之后的個數，個數多的放在前面。
- ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面。
- 使用EXPLAIN判斷SQL語句是否合理使用索引，盡量避免extra列出現：Using File Sort，UsingTemporary。
- UPDATE、DELETE語句需要根據WHERE條件添加索引。
- 不建議使用%前綴模糊查詢，例如LIKE “%weibo”。
- 對長度過長的VARCHAR字段建立索引時，添加crc32或者MD5 Hash字段，對Hash字段建立索引。
- 合理創建聯合索引（避免冗余），(a,b,c) 相當于 (a) 、(a,b) 、(a,b,c)。
- 合理利用覆蓋索引。
- SQL變更需要確認索引是否需要變更并通知DBA。
三、 SQL語句
- 使用prepared statement，可以提供性能并且避免SQL注入。
- SQL語句中IN包含的值不應過多。
- UPDATE、DELETE語句不使用LIMIT。
- WHERE條件中必須使用合適的類型，避免MySQL進行隱式類型轉化。
- SELECT語句只獲取需要的字段。
- SELECT、INSERT語句必須顯式的指明字段名稱，不使用SELECT *，不使用INSERTINTO table()。
- 使 用SELECT column_name1, column_name2 FROM table WHERE[condition]而不是SELECT column_name1 FROM table WHERE[condition]和SELECT column_name2 FROM table WHERE [condition]。
- WHERE條件中的非等值條件（IN、BETWEEN、<、<=、>、>=）會導致后面的條件使用不了索引。
- 避免在SQL語句進行數學運算或者函數運算，容易將業務邏輯和DB耦合在一起。
- INSERT語句使用batch提交（INSERT INTO tableVALUES(),(),()……），values的個數不應過多。
- 避免使用存儲過程、觸發器、函數等，容易將業務邏輯和DB耦合在一起，并且MySQL的存儲過程、觸發器、函數中存在一定的bug。
- 避免使用JOIN。
- 使用合理的SQL語句減少與數據庫的交互次數。
- 不使用ORDER BY RAND()，使用其他方法替換。
- 建議使用合理的分頁方式以提高分頁的效率。
- 統計表中記錄數時使用COUNT(*)，而不是COUNT(primary_key)和COUNT(1)。
- 禁止在從庫上執行后臺管理和統計類型功能的QUERY。
四、 散表
- 每張表數據量建議控制在5000w以下。
- 可以結合使用hash、range、lookup table進行散表。
- 散表如果使用md5（或者類似的hash算法）進行散表，表名后綴使用16進制，比如user_ff。
- 推薦使用CRC32求余（或者類似的算術算法）進行散表，表名后綴使用數字，數字必須從0開始并等寬，比如散100張表，后綴從00-99。
- 使用時間散表，表名后綴必須使用特定格式，比如按日散表user_20110209、按月散表user_201102。
五、 其他
- 批量導入、導出數據需要DBA進行審查，并在執行過程中觀察服務。
- 批量更新數據，如update,delete 操作，需要DBA進行審查，并在執行過程中觀察服務。
- 產品出現非數據庫平臺運維導致的問題和故障時，如前端被抓站，請及時通知DBA，便于維護服務穩定。
- 業務部門程序出現bug等影響數據庫服務的問題,請及時通知DBA，便于維護服務穩定。
- 業務部門推廣活動，請提前通知DBA進行服務和訪問評估。
- 如果出現業務部門人為誤操作導致數據丟失，需要恢復數據，請在第一時間通知DBA，并提供準確時間，誤操作語句等重要線索。
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
FAQ
1-1.庫名、表名、字段名必須使用小寫字母，“_”分割。
a)MySQL有配置參數lower_case_table_names，不可動態更改，linux系統默認為0，即庫表名以實際情況存儲，大小寫敏感。如果是1，以小寫存儲，大小寫不敏感。如果是2，以實際情況存儲，但以小寫比較。
b) 如果大小寫混合用，可能存在abc,Abc,ABC等多個表共存，容易導致混亂。
c) 字段名顯示區分大小寫，但實際使用不區分，即不可以建立兩個名字一樣但大小寫不一樣的字段。
d) 為了統一規范， 庫名、表名、字段名使用小寫字母。
1-2.庫名、表名、字段名必須不超過12個字符。
庫名、表名、字段名支持最多64個字符，但為了統一規范、易于辨識以及減少傳輸量，必須不超過12字符。
1-3.庫名、表名、字段名見名知意,建議使用名詞而不是動詞。
a) 用戶評論可用表名usercomment或者comment。
b) 庫表是一種客觀存在的事物，一種對象，所以建議使用名詞。
1-4.建議使用InnoDB存儲引擎。
a) 5.5以后的默認引擘，支持事務，行級鎖，更好的恢復性，高并發下性能更好，對多核，大內存，ssd等硬件支持更好。
b) 具體比較可見附件的官方白皮書。
1-5.存儲精確浮點數必須使用DECIMAL替代FLOAT和DOUBLE。
a) mysql中的數值類型（不包括整型）：
IEEE754浮點數：float （單精度） ， double 或real （雙精度）
定點數： decimal或 numeric
單精度浮點數的有效數字二進制是24位，按十進制來說，是8位；雙精度浮點數的有效數字二進制是53位，按十進制來說，是16 位
一個實數的有效數字超過8位，用單精度浮點數來表示的話，就會產生誤差！同樣，如果一個實數的有效數字超過16位，用雙精度浮點數來表示，也會產生誤差
b)IEEE754標準的計算機浮點數，在內部是用二進制表示的，但在將一個十進制數轉換為二進制浮點數時，也會造成誤差，原因是不是所有的數都能轉換成有限長度的二進制數。
即一個二進制可以準確轉換成十進制，但一個帶小數的十進制不一定能夠準確地用二進制來表示。
實例：
drop table if exists t;
create table t(value float(10,2));
insert into t values(131072.67),(131072.68);
select value from t;
+-----------+
|value |
+-----------+
| 131072.67 |
| 131072.69 |
+-----------+
1-6.建議使用UNSIGNED存儲非負數值。
同樣的字節數，存儲的數值范圍更大。如tinyint 有符號為 -128-127，無符號為0-255
1-7. 如何使用INT UNSIGNED存儲ip？
使用INTUNSIGNED而不是char(15)來存儲ipv4地址，通過MySQL函數inet_ntoa和inet_aton來進行轉化。Ipv6地址目前沒有轉化函數，需要使用DECIMAL或者兩個bigINT來存儲。例如：
SELECT INET_ATON('209.207.224.40');
3520061480
SELECT INET_NTOA(3520061480);
209.207.224.40
1-8. INT[M]，M值代表什么含義？
注意數值類型括號后面的數字只是表示寬度而跟存儲范圍沒有關系，比如INT(3)默認顯示3位，空格補齊，超出時正常顯示，Python、java客戶端等不具備這個功能。
1-10.不建議使用ENUM、SET類型，使用TINYINT來代替。
a)ENUM，有三個問題：添加新的值要做DDL，默認值問題(將一個非法值插入ENUM(也就是說，允許的值列之外的字符串)，將插入空字符串以作為特殊錯誤值)，索引值問題（插入數字實際是插入索引對應的值）
實例：
drop table if exists t;
create table t(sex enum('0','1'));
insert into t values(1);
insert into t values('3');
select * from t;
+------+
| sex |
+------+
| 0 |
| |
+------+
2 rows in set (0.00 sec)
1-11.盡可能不使用TEXT、BLOB類型。
a) 索引排序問題，只能使用max_sort_length的長度或者手工指定ORDER BY SUBSTRING(column,length)的長度來排序
b) Memory引擘不支持text,blog類型，會在磁盤上生成臨時表
c) 可能浪費更多的空間
d) 可能無法使用adaptive hash index
e) 導致使用where沒有索引的語句變慢
1-13. VARCHAR中會產生額外存儲嗎？
VARCHAR(M)，如果M<256時會使用一個字節來存儲長度，如果M>=256則使用兩個字節來存儲長度。
1-14.表字符集選擇UTF8。
a) 使用utf8字符集，如果是漢字，占3個字節，但ASCII碼字符還是1個字節。
b) 統一，不會有轉換產生亂碼風險
c) 其他地區的用戶（美國、印度、臺灣）無需安裝簡體中文支持，就能正常看您的文字，并且不會出現亂碼
d)ISO-8859-1編碼(latin1)使用了單字節內的所有空間，在支持ISO-8859-1的系統中傳輸和存儲其他任何編碼的字節流都不會被拋棄。即把其他任何編碼的字節流當作ISO-8859-1編碼看待都沒有問題，保存的是原封不動的字節流。
1-15.使用VARBINARY存儲變長字符串。
二進制字節流，不存在編碼問題
1-18. 為什么建議使用TIMESTAMP來存儲時間而不是DATETIME？
DATETIME和TIMESTAMP都是精確到秒，優先選擇TIMESTAMP，因為TIMESTAMP只有4個字節，而DATETIME8個字節。同時TIMESTAMP具有自動賦值以及自動更新的特性。
如何使用TIMESTAMP的自動賦值屬性？
a) 將當前時間作為ts的默認值：ts TIMESTAMP DEFAULTCURRENT_TIMESTAMP。
b) 當行更新時，更新ts的值：ts TIMESTAMP DEFAULT 0 ONUPDATE CURRENT_TIMESTAMP。
c) 可以將1和2結合起來：ts TIMESTAMP DEFAULTCURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。
1-19.建議字段定義為NOT NULL。
a）如果null字段被索引，需要額外的1字節
b）使索引，索引統計，值的比較變得更復雜
c）可用0，''代替
d）如果是索引字段，一定要定義為not null
1-21.禁止在數據庫中使用VARBINARY、BLOB存儲圖片、文件等。
采用分布式文件系統更高效
2. 為什么MySQL的性能依賴于索引？
MySQL的查詢速度依賴良好的索引設計，因此索引對于高性能至關重要。合理的索引會加快查詢速度（包括UPDATE和DELETE的速度，MySQL會將包含該行的page加載到內存中，然后進行UPDATE或者DELETE操作），不合理的索引會降低速度。
MySQL索引查找類似于新華字典的拼音和部首查找，當拼音和部首索引不存在時，只能通過一頁一頁的翻頁來查找。當MySQL查詢不能使用索引時，MySQL會進行全表掃描，會消耗大量的IO。
2-5. 為什么一張表中不能存在過多的索引？
InnoDB的secondaryindex使用b+tree來存儲，因此在UPDATE、DELETE、INSERT的時候需要對b+tree進行調整，過多的索引會減慢更新的速度。
2-11. EXPLAIN語句
EXPLAIN語句（在MySQL客戶端中執行）可以獲得MySQL如何執行SELECT語句的信息。通過對SELECT語句執行EXPLAIN，可以知曉MySQL執行該SELECT語句時是否使用了索引、全表掃描、臨時表、排序等信息。盡量避免MySQL進行全表掃描、使用臨時表、排序等。詳見官方文檔。
2-13.不建議使用%前綴模糊查詢，例如LIKE “%weibo”。
會導致全表掃描
2-14. 如何對長度大于50的VARCHAR字段建立索引？
下面的表增加一列url_crc32，然后對url_crc32建立索引，減少索引字段的長度，提高效率。
- CREATE TABLE url(
……
url VARCHAR(255) NOT NULL DEFAULT 0,
url_crc32INT UNSIGNED NOT NULL DEFAULT 0,
……
index idx_url(url_crc32)
)
2-16. 什么是覆蓋索引？
InnoDB 存儲引擎中，secondaryindex（非主鍵索引）中沒有直接存儲行地址，存儲主鍵值。如果用戶需要查詢secondaryindex中所不包含的數據列時，需要先通過secondaryindex查找到主鍵值，然后再通過主鍵查詢到其他數據列，因此需要查詢兩次。
覆蓋索引的概念就是查詢可以通過在一個索引中完成，覆蓋索引效率會比較高，主鍵查詢是天然的覆蓋索引。
合理的創建索引以及合理的使用查詢語句，當使用到覆蓋索引時可以獲得性能提升。
比如SELECT email,uid FROM user_email WHEREuid=xx，如果uid不是主鍵，適當時候可以將索引添加為index(uid,email)，以獲得性能提升。
3-3.UPDATE、DELETE語句不使用LIMIT。
a) 可能導致主從數據不一致
b) 會記錄到錯誤日志，導致日志占用大量空間
3-4. 為什么需要避免MySQL進行隱式類型轉化？
因為MySQL進行隱式類型轉化之后，可能會將索引字段類型轉化成=號右邊值的類型，導致使用不到索引，原因和避免在索引字段中使用函數是類似的。
3-6. 為什么不建議使用SELECT *?
增加很多不必要的消耗（cpu、io、內存、網絡帶寬）；增加了使用覆蓋索引的可能性；當表結構發生改變時，前段也需要更新。
3-13. 如何減少與數據庫的交互次數？
使用下面的語句來減少和db的交互次數：
INSERT ... ON DUPLICATE KEY UPDATE
REPLACE
INSERT IGNORE
INSERT INTO values(),()如何結合使用多個緯度進行散表散庫？
例如微博message，先按照crc32(message_id)將message散到16個庫中，然后針對每個庫中的表，一天生成一張新表。
3-14. 為什么不能使用ORDER BY rand()？
因為ORDER BYrand()會將數據從磁盤中讀取，進行排序，會消耗大量的IO和CPU，可以在程序中獲取一個rand值，然后通過在從數據庫中獲取對應的值。
3-15. MySQL中如何進行分頁？
假如有類似下面分頁語句：
SELECT * FROM table ORDER BY TIME DESC LIMIT 10000,10;
這種分頁方式會導致大量的io，因為MySQL使用的是提前讀取策略。
推薦分頁方式：
SELECT * FROM table WHERE TIME<last_TIME ORDER BYTIME DESC LIMIT 10.
SELECT * FROM table inner JOIN(SELECT id FROM table ORDER BYTIME LIMIT 10000,10) as t USING(id)
3-17.為什么避免使用復雜的SQL？
拒絕使用復雜的SQL，將大的SQL拆分成多條簡單SQL分步執行。原因：簡單的SQL容易使用到MySQL的querycache；減少鎖表時間特別是MyISAM；可以使用多核cpu。
2. InnoDB存儲引擎為什么避免使用COUNT(*)?
InnoDB表避免使用COUNT(*)操作，計數統計實時要求較強可以使用memcache或者redis，非實時統計可以使用單獨統計表，定時更新。
 |
不含病毒。www.avast.com |
留言列表
