簡介篇
存儲引擎
MyISAM是MySQL5.1及之前的版本的默認存儲引擎。MyISAM提供了大量的特性，包括全文索引、壓縮、空間函數（GIS）等，但是MyISAM不支持事務和行級鎖，而且有一個毫無疑問的缺陷就是崩潰後無法安全回復。
MyISAM會將表存儲在兩個文件中：數據文件和索引文件，分別以.MYD和.MYI為擴展名。
InnoDB表是基於聚簇索引建立的。
推薦InnoDB存儲引擎
數據類型
MySQL支持的數據類型非常多,總體上分為：數字、字符、日期、JSON。選擇正確的數據類型對於獲得高性能至關重要。不管存儲那種類型的數據，下面幾個簡單的原則都有助於作出更好的選擇。
1）更小的通常更好
一般情況下，應該盡量使用可以正確存儲數據的最小數據類型。更小的數據類型通常更快，因為他們占用更少的磁盤、內存和CPU緩存，並且處理時需要的CPU周期也更少。
2）簡單就好
簡單的數據類型的操作通常需要更少的CPU周期。例如，整型比字符操作代價更低，因為字符集和校對規則（排序規則）使字符比較比整型比較更復雜。
3）盡量避免NULL
很多表都包含可為NULL的列，即使應用程序並不需要保存NULL也是如此，這是因為可為NULL是列的默認屬性。通常情況下最好指定列為NOT NULL，除非真的需要存儲NULL值。
用NULL會浪費存儲空間，因為InnoDB需要一個額外的字節存儲。
NULL字段的復合索引無效。
如果查詢中包含可為NULL的列，對MySQL來說更難優化，因為可為NULL的列使得索引、索引統計和值比較都更負責。當可為NULL的列被索引時，每個索引記錄需要一個額外的字節，在MyISAM裡甚至還可能導致固定大小的索引（例如只有一個整數列的索引）變成可變大小的索引。
日期類型
DATE類型表示日期的年月日部分，范圍在1000-01-01到9999-12-31之間。
DATETIME類型表示日期的年月日時分秒部分，范圍在1000-01-01 00:00:00到9999-12-31 23:59:59之間。
TIMESTAMP類型表示日期的年月日時分秒部分，范圍在1970-01-01 00:00:01UTC到2038-01-19 03:14:07UTC之間。
可以讓DATETIME和TIMESTAMP自動初始化並且隨其他字段的修改而自動修改。
例如：
CREATE TABLE aaa (
id bigint(11) NOT NULL AUTO_INCREMENT,
ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
dt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
name varchar(20) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
主鍵
整數通常是主鍵的最好選擇，因為他們很快並且可以使用AUTO_INCREMENT。
如果可能，應該避免使用字符串類型作為主鍵，因為他們很消耗空間，並且通常比數字類型慢。尤其在MyISAM表裡使用字符串作為主鍵要特別小心。MyISAM默認對字符串使用壓縮索引，這會導致查詢慢很多。
對於完全“隨機”的字符串也許多加注意，例如MD5()、SHA1()、或者UUID()產生的字符串。這些函數生成的新值會任意分布在很大的空間內，這回導致INSERT以及一些SELECT語句變得很慢：
因為插入值會隨機地寫到索引的不通位置，所以是的INSERT語句更慢。這回導致頁分裂、磁盤隨機訪問，以及對於聚簇存儲引擎產生聚簇索引碎片。
SELECT語句會變得更慢，因為邏輯上相鄰的行會分布在磁盤和內存的不同地方。
隨機值導致緩存對所有類型的查詢語句效果都很差，因為會使得緩存賴以工作的訪問局部性原理失效。如果整個數據集都一樣的“熱”，那麼緩存任何一部分特定數據到內存都沒有好處；如果工作集比內存大，緩存將會有很多刷新和不命中。
如果存儲UUID值，則應該移除“-”符號；或者更好的做法是，用UNHEX()函數轉換UUID值為16字節的數字，並且存儲在一個BINARY(16)列中。檢索時可以通過HEX()函數來格式化為十六進制格式。
UUID()生成的值與加密散列函數例如SHA1()生成的值有不同的特征：UUID值雖然分布也不均勻，但還是有一定順序的。盡管如此，還是不如遞增的整數好用。
索引
B-Tree索引
B-Tree索引能夠加快訪問數據的速度，因為存儲引擎不再需要進行全表掃描來獲取需要的數據，取而代之的是從索引的根節點開始進行搜索。
B+樹索引並不能找到一個給定鍵值的具體行。B+樹索引能找到的只是被查找數據行所在的頁。然後數據庫通過把頁讀入內存，再在內存中進行查找，最終得到要查找的數據。
限制：
如果不是按照索引的最左列開始查找，則無法使用索引。
不能跳過索引中的列。
如果查詢中有某個列的范圍查詢，則其右邊所有列都無法使用索引優化查找。
Hash索引
高性能索引策略：
分區表
分區功能並不是在存儲引擎層完成的。分區有水平分區和垂直分區兩種，但是MySQL不支持垂直分區。
大多數DBA會有這樣一個誤區：只要啟用了分區，數據庫就會變得更快。這個結論是存在很多問題的。其實，分區對於某些SQL語句性能可能會帶來提高，但是分區主要用語高可用性，利於數據庫的管理。在OLTP應用中，對於分區的使用應該非常小心。如果只是一味使用分區，而不理解分區是如何工作的，也不清楚你的應用如是使用分區，那麼分許極有可能只會對性能產生負面的影響。
MySQL數據庫支持以下幾種分區類型：
RANGE分區：
LIST分區：
HASH分區：
KEY分區：
不論創建哪種類型的分區，如果表中存在主鍵或者是唯一索引時，分區列必須是唯一索引的一個組成部分。
唯一索引可以是允許NULL值的，並且分區列只要是唯一索引的一個組成部分，不需要整個唯一索引列都是分區列。
當建表時沒有指定主鍵和唯一索引時，可以指定任何一列為分區列。
規范篇
規范存在意義
保證線上數據庫schema規范
減少出問題概率
方便自動化管理
規范需要長期堅持，對開發和DBA是一個雙贏的事情
核心規范
不在數據庫做運算：cpu計算務必移至業務層
控制單表數據量：單表記錄控制在1000w
控制列數量：字段數控制在20以內
平衡范式與冗余：為提高效率犧牲范式設計，冗余數據
拒絕3B：拒絕大sql，大事物，大批量
基本命名和約束規范
表字符集選擇UTF8 ，如果需要存儲emoj表情，需要使用UTF8mb4(MySQL 5.5.3以後支持)
存儲引擎使用InnoDB
變長字符串盡量使用varchar varbinary
不在數據庫中存儲圖片、文件等
單表數據量控制在1000w以下
庫名、表名、字段名不使用保留字
庫名、表名、字段名、索引名使用小寫字母，以下劃線分割 ，需要見名知意
庫表名不要設計過長，盡可能用最少的字符表達出表的用途
索引規范
單個索引字段數不超過5，單表索引數量不超過5，索引設計遵循B+ Tree索引最左前綴匹配原則
選擇區分度高的列作為索引
建立的索引能覆蓋80%主要的查詢，不求全，解決問題的主要矛盾
DML和order by和group by字段要建立合適的索引
不在索引做列運算
避免索引的隱式轉換
避免冗余索引
不要用外鍵
關於索引規范，一定要記住索引這個東西是一把雙刃劍，在加速讀的同時也引入了很多額外的寫入和鎖，降低寫入能力，這也是為什麼要控制索引數原因。之前看到過不少人給表裡每個字段都建了索引，其實對查詢可能起不到什麼作用。
冗余索引例子
idx_abc(a,b,c)
idx_a(a) 冗余
idx_ab(a,b) 冗余
隱式轉換例子
字段:remark varchar(50) NOT Null
MySQL>SELECT id, gift_code FROM gift WHERE deal_id = 640 AND remark=115127; 1 row in set (0.14 sec)
MySQL>SELECT id, gift_code FROM pool_gift WHEREdeal_id = 640 AND remark=‘115127’; 1 row in set (0.005 sec)
字段定義為varchar，但傳入的值是個int，就會導致全表掃描，要求程序端要做好類型檢查
字段規范
所有字段均定義為NOT NULL ，除非你真的想存Null
字段類型在滿足需求條件下越小越好，使用UNSIGNED存儲非負整數 ，實際使用時候存儲負數場景不多
使用datetime存儲時間
使用varchar存儲變長字符串 ，當然要注意varchar(M)裡的M指的是字符數不是字節數；使用UNSIGNED INT存儲IPv4 地址而不是CHAR(15) ，這種方式只能存儲IPv4，存儲不了IPv6
使用DECIMAL存儲精確浮點數，用float有的時候會有問題
少用blob text
SQL規范
盡量不使用存儲過程、觸發器、函數等
避免使用大表的JOIN，MySQL優化器對join優化策略過於簡單
避免在數據庫中進行數學運算和其他大量計算任務
SQL合並，主要是指的DML時候多個value合並，減少和數據庫交互
UPDATE、DELETE語句不使用LIMIT，容易造成主從不一致
不用select *
慎用count(*)
避免負向%
請使用同類型比較
sql語句盡可能簡單
一條sql只能在一個cpu運算
大語句拆小語句以減少鎖時間
一條大sql可以堵死整個庫
簡單的事務
事務時間盡可能短
bad case：
上傳圖片事務
OR改寫為IN()
or的效率是n級別
in的消息時log(n)級別
in的個數建議控制在200以內
select id from t where phone=’159′ or phone=’136′;
=>
select id from t where phone in (’159′, ’136′);
OR改寫為UNION
mysql的索引合並很弱智
select id from t where phone = ’159′ or name = ‘john’;
=>
select id from t where phone=’159′
union
select id from t where name=’jonh’
limit高效分頁
limit越大，效率越低
select id from t limit 10000, 10;
=>
select id from t where id > 10000 limit 10;
使用union all替代union
union有去重開銷
使用load data導數據
load data比insert快約20倍；
