1、數據庫，究竟要怎樣呵護你？
一、基礎規範
（1）必須使用InnoDB存儲引擎
解讀：支持事務、行級鎖、併發性能更好、CPU及內存緩存頁優化使得資源利用率更高
（2）必須使用UTF8字符集
解讀：萬國碼，無需轉碼，無亂碼風險，節省空間
（3）數據表、數據字段必須加入中文註釋
解讀：N年後誰知道這個r1,r2,r3字段是幹嘛的
（4）禁止使用存儲過程、視圖、觸發器、Event
解讀：高併發大數據的互聯網業務，架構設計思路是“解放數據庫CPU，將計算轉移到服務層”，併發量大的情況下，這些功能很可能將數據庫拖死，業務邏輯放到服務層具備更好的擴展性，能夠輕易實現“增機器就加性能”。數據庫擅長存儲與索引，CPU計算還是上移吧
（5）禁止存儲大文件或者大照片
解讀：為何要讓數據庫做它不擅長的事情？大文件和照片存儲在文件系統，數據庫存URI多好
二、命名規範
（6）只允許使用內網域名，而不是ip連接數據庫
（7）線上環境、開發環境、測試環境數據庫內網域名遵循命名規範
業務名稱：xxx
線上環境：dj.xxx.db
開發環境：dj.xxx.rdb
測試環境：dj.xxx.tdb
從庫在名稱後加-s標識，備庫在名稱後加-ss標識
線上從庫：dj.xxx-s.db
線上備庫：dj.xxx-sss.db
（8）庫名、表名、字段名：小寫，下劃線風格，不超過32個字符，必須見名知意，禁止拼音英文混用
（9）表名t_xxx，非唯一索引名idx_xxx，唯一索引名uniq_xxx
三、表設計規範
（10）單實例表數目必須小於500
（11）單表列數目必須小於30
（12）表必須有主鍵，例如自增主鍵
解讀：
a）主鍵遞增，數據行寫入可以提高插入性能，可以避免page分裂，減少表碎片提升空間和內存的使用
b）主鍵要選擇較短的數據類型， Innodb引擎普通索引都會保存主鍵的值，較短的數據類型可以有效的減少索引的磁盤空間，提高索引的緩存效率
c） 無主鍵的表刪除，在row模式的主從架構，會導致備庫夯住
（13）禁止使用外鍵，如果有外鍵完整性約束，需要應用程序控制
解讀：外鍵會導致表與表之間耦合，update與delete操作都會涉及相關聯的表，十分影響sql 的性能，甚至會造成死鎖。高併發情況下容易造成數據庫性能，大數據高併發業務場景數據庫使用以性能優先
四、字段設計規範
（14）必須把字段定義為NOT NULL並且提供默認值
解讀：
a）null的列使索引/索引統計/值比較都更加複雜，對MySQL來說更難優化
b）null 這種類型MySQL內部需要進行特殊處理，增加數據庫處理記錄的複雜性；同等條件下，表中有較多空字段的時候，數據庫的處理性能會降低很多
c）null值需要更多的存儲空間，無論是表還是索引中每行中的null的列都需要額外的空間來標識
d）對null 的處理時候，只能採用is null或is not null，而不能採用=、in、、!=、not in這些操作符號。如：where name!=’xiaoming’，如果存在name為null值的記錄，查詢結果就不會包含name為null值的記錄
（15）禁止使用TEXT、BLOB類型
解讀：會浪費更多的磁盤和內存空間，非必要的大量的大字段查詢會淘汰掉熱數據，導致內存命中率急劇降低，影響數據庫性能
（16）禁止使用小數存儲貨幣
解讀：使用整數吧，小數容易導致錢對不上
（17）必須使用varchar(20)存儲手機號
解讀：
a）涉及到區號或者國家代號，可能出現+-()
b）手機號會去做數學運算麼？
c）varchar可以支持模糊查詢，例如：like“138%”
（18）禁止使用ENUM，可使用TINYINT代替
解讀：
a）增加新的ENUM值要做DDL操作
b）ENUM的內部實際存儲就是整數，你以為自己定義的是字符串？
五、索引設計規範
（19）單表索引建議控制在5個以內
（20）單索引字段數不允許超過5個
解讀：字段超過5個時，實際已經起不到有效過濾數據的作用了
（21）禁止在更新十分頻繁、區分度不高的屬性上建立索引
解讀：
a）更新會變更B+樹，更新頻繁的字段建立索引會大大降低數據庫性能
b）“性別”這種區分度不大的屬性，建立索引是沒有什麼意義的，不能有效過濾數據，性能與全表掃描類似
（22）建立組合索引，必須把區分度高的字段放在前面
解讀：能夠更加有效的過濾數據
六、SQL使用規範
（23）禁止使用SELECT *，只獲取必要的字段，需要顯示說明列屬性
解讀：
a）讀取不需要的列會增加CPU、IO、NET消耗
b）不能有效的利用覆蓋索引
c）使用SELECT *容易在增加或者刪除字段後出現程序BUG
（24）禁止使用INSERT INTO t_xxx VALUES(xxx)，必須顯示指定插入的列屬性
解讀：容易在增加或者刪除字段後出現程序BUG
（25）禁止使用屬性隱式轉換
解讀：SELECT uid FROM t_user WHERE phone=138******** 會導致全表掃描，而不能命中phone索引，猜猜為什麼？（這個線上問題不止出現過一次）
（26）禁止在WHERE條件的屬性上使用函數或者表達式
解讀：SELECT uid FROM t_user WHERE from_unixtime(day)>='2017-02-15' 會導致全表掃描
正確的寫法是：SELECT uid FROM t_user WHERE day>= unix_timestamp('2017-02-15 00:00:00')
（27）禁止負向查詢，以及%開頭的模糊查詢
解讀：
a）負向查詢條件：NOT、!=、<>、!、NOT IN、NOT LIKE等，會導致全表掃描
b）%開頭的模糊查詢，會導致全表掃描
（28）禁止大表使用JOIN查詢，禁止大表使用子查詢
解讀：會產生臨時表，消耗較多內存與CPU，極大影響數據庫性能
（29）禁止使用OR條件，必須改為IN查詢
解讀：舊版本Mysql的OR查詢是不能命中索引的，即使能命中索引，為何要讓數據庫耗費更多的CPU幫助實施查詢優化呢？
（30）應用程序必須捕獲SQL異常，並有相應處理
（31）優先級：整形 > date,time > enum,char,varchar > blob(基本禁止使用)
解讀：整形速度最快，因為B-tree數據庫的優點是範圍查詢
（32）儘量使用聯合索引，避免多個單列索引
解讀：因為多個單列索引只能有一個索引發揮作用，而聯合索引可以充分利用數據庫的左前綴規則。
以多列索引index(a,b,c)為例：
2、遠離全表掃描
（1）避免模糊查詢
解讀：如因業務需要一定要使用模糊查詢，則避免使用全模糊查詢。對於右模糊查詢，即like '...%'是可以使用索引的。而左模糊查詢like '%...'無法使用索引。
select id from t where name like '%abc%' 或者
select id from t where name like '%abc' 或者
若要提高效率，可以考慮全文檢索。
而select id from t where name like 'abc%' 才用到索引
（2）NULL
解讀：IS NOT NULL永遠不會使用到索引，最好的辦法依然是所有列都設置為NOT NULL；另外，應儘量避免在where 子句中對字段進行null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，
如：
select id from t where num is null
NULL對於大多數數據庫都需要特殊處理，MySQL也不例外，它需要更多的代碼，更多的檢查和特殊的索引邏輯，有些開發人員完全沒有意識到，創建表時NULL是默認值，但大多數時候應該使用NOT NULL，或者使用一個特殊的值，如0，-1作為默 認值。不能用null作索引，任何包含null值的列都將不會被包含在索引中。即使索引有多列這樣的情況下，只要這些列中有一列含有null，該列 就會從索引中排除。也就是說如果某列存在空值，即使對該列建索引也不會提高性能。 任何在where子句中使用is null或is not null的語句優化器是不允許使用索引的。
可以在num上設置默認值0，確保表中num列沒有null值，然後這樣查詢：
select id from t where num=0
（3）使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描
解讀：即使所比較的字段上有索引，當使用"!="操作符時，也會引起全表掃描。解決辦法是，可以把"!="操作符轉換成"or"，就可以使用到索引，避免全表掃描。
MySQL只有對以下操作符才使用索引：，>=，BETWEEN，IN，以及某些時候的LIKE。 可以在LIKE操作中使用索引的情形是指另一個操作數不是以通配符（%或者_）開頭的情形。例如，“SELECT id FROM t WHERE col LIKE 'Mich%';”這個查詢將使用索引，但“SELECT id FROM t WHERE col LIKE '%ike';”這個查詢不會使用索引。
（4）應儘量避免使用or 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描
如：
select id from t where num=10 or num=20
可以這樣查詢：
select id from t where num=10
union all
select id from t where num=20
（5）IN 和NOT IN 要慎用，否則會引起全表掃描
如：
select id from t where num IN(1,2,3)
對於連續的數值，能用BETWEEN就不要用IN了：
select id from t where num BETWEEN 1 and 3
（6）如果在where 子句中使用參數，也會導致全表掃描。
因為SQL只有在運行時才會解析局部變量，但優化程序不能將訪問計劃的選擇推 遲到運行時；它必須在編譯時進行選擇。然而，如果在編譯時建立訪問計劃，變量的值還是未知的，因而無法作為索引選擇的輸入項。如下面語句將進行全表掃描：
select id from t where num=@num
可以改為強制查詢使用索引：select id from t with(index(索引名)) where num=@num
（7）應儘量避免在where 子句中對字段進行表達式操作
這將導致引擎放棄使用索引而進行全表掃描。 如：
select id from t where num/2=100
應改為:
select id from t where num=100*2
（8）應儘量避免對字段進行函數操作，這將導致引擎放棄使用索引而進行全表掃描。
如：
select id from t where substring(name,1,3)='abc'--name
select id from t where datediff(day,createdate,'2005-11-30')=0--‘2005-11-30’生成的id 應改為:
select id from t where name like 'abc%'
select id from t where createdate>='2005-11-30' and createdate
（9）不要在where 子句中的“=”左邊進行函數、算術運算或其他表達式運算，否則系統將可能無法正確使用索引。
（10）在使用索引字段作為條件時，如果該索引是複合索引，那麼必須使用到該索引中的第一個字段作為條件時才能保證系統使用該索引，否則該索引將不會被使用，並且應儘可能的讓字段順序與索引順序相一致。
（11）很多時候用EXISTS代替IN是一個好的選擇：
select num from a where num IN (select num from b)
用下面的語句替換：
select num from a where EXISTS (select 1 from b where num=a.num)
（12）並不是所有索引對查詢都有效，SQL是根據表中數據來進行查詢優化的，當索引列有大量數據重複時，SQL查詢可能不會去利用索引，如一表中有字段sex，male、female幾乎各一半，那麼即使在sex上建了索引也對查詢效率起不了作用。
（13）索引並不是越多越好，索引固然可以提高相應的select 的效率，但同時也降低了insert 及update 的效率，因為insert 或update 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個表的索引數最好不要超過6個，若太多則應考慮一些不常使用到的列上建的索引是否有必要。
（14）應儘可能的避免更新clustered 索引數據列，因為clustered 索引數據列的順序就是表記錄的物理存儲順序，一旦該列值改變將導致整個表記錄的順序的調整，會耗費相當大的資源。若應用系統需要頻繁更新clustered 索引數據列，那麼需要考慮是否應將該索引建為clustered 索引。
（15）儘量使用數字型字段，若只含數值信息的字段儘量不要設計為字符型，這會降低查詢和連接的性能，並會增加存儲開銷。這是因為引擎在處理查詢和連接時會逐個比較字符串中每一個字符，而對於數字型而言只需要比較一次就夠了。
（16）儘可能的使用varchar/nvarchar 代替char/nchar ，因為首先變長字段存儲空間小，可以節省存儲空間，其次對於查詢來說，在一個相對較小的字段內搜索效率顯然要高些。
（17）任何地方都不要使用select * from t ，用具體的字段列表代替“*”，不要返回用不到的任何字段。
（18）儘量使用表變量來代替臨時表。如果表變量包含大量數據，請注意索引非常有限（只有主鍵索引）。
（19）避免頻繁創建和刪除臨時表，以減少系統表資源的消耗。臨時表並不是不可使用，適當地使用它們可以使某些工作更有效，例如，當需要重複引用大型表或常用表中的某個數據集時。但是，對於一次性事件，最好使用導出表。
（20）在新建臨時表時，如果一次性插入數據量很大，那麼可以使用select into 代替create table，避免造成大量log ，以提高速度；如果數據量不大，為了緩和系統表的資源，應先create table，然後insert。
（21）如果使用到了臨時表，在存儲過程的最後務必將所有的臨時表顯式刪除，先truncate table ，然後drop table ，這樣可以避免系統表的較長時間鎖定。
（22）儘量避免使用遊標，因為遊標的效率較差，如果遊標操作的數據超過1萬行，那麼就應該考慮改寫。使用基於遊標的方法或臨時表方法之前，應先尋找基於集的解決方案來解決問題，基於集的方法通常更有效。與臨時表一樣，遊標並不是不可使用。對小型數據集使用FAST_FORWARD 遊標通常要優於其他逐行處理方法，尤其是在必須引用幾個表才能獲得所需的數據時。在結果集中包括“合計”的例程通常要比使用遊標執行的速度快。如果開發時間允許，基於遊標的方法和基於集的方法都可以嘗試一下，看哪一種方法的效果更好。
（23）在所有的存儲過程和觸發器的開始處設置SET NOCOUNT ON ，在結束時設置SET NOCOUNT OFF 。無需在執行存儲過程和觸發器的每個語句後向客戶端發送DONE_IN_PROC 消息。
（24）儘量避免大事務操作，提高系統併發能力。
3、聯合索引實驗
#創建聯合索引
ALTER TABLE `analogy`.`indext` ADD INDEX `four_keys` (`a1`, `a2`, `a3`, `a4`);
#查看建表語句和索引
mysql> show create table indext;
indext | CREATE TABLE `indext` (
`id` int(10) unsigned NOT NULL DEFAULT '0',
`a1` char(10) NOT NULL DEFAULT '',
`a2` char(10) NOT NULL DEFAULT '',
`a3` char(10) NOT NULL DEFAULT '',
`a4` char(10) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `four_keys` (`a1`,`a2`,`a3`,`a4`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
#查看索引分佈
mysql> show index from indext;
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| indext | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| indext | 1 | four_keys | 1 | a1 | A | 0 | NULL | NULL | | BTREE | | |
| indext | 1 | four_keys | 2 | a2 | A | 0 | NULL | NULL | | BTREE | | |
| indext | 1 | four_keys | 3 | a3 | A | 0 | NULL | NULL | | BTREE | | |
| indext | 1 | four_keys | 4 | a4 | A | 0 | NULL | NULL | | BTREE | | |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)
閱讀更多 孤獨的根號三101010 的文章
