1. 【強制】業(yè)務上具有唯一特性的字段等浊，即使是多個字段的組合，也必須建成唯一索引摹蘑。
   說明： 不要以為唯一索引影響了 insert 速度筹燕，這個速度損耗可以忽略，但提高查找速度是明顯的衅鹿； 另外撒踪，即使在應用層做了非常完善的校驗控制，只要沒有唯一索引大渤，根據(jù)墨菲定律制妄，必然有臟數(shù)據(jù)產(chǎn)生。

2. 【強制】超過三個表禁止 join兼犯。需要 join 的字段，數(shù)據(jù)類型必須絕對一致； 多表關聯(lián)查詢時切黔，保證被關聯(lián)的字段需要有索引砸脊。
   說明： 即使雙表 join 也要注意表索引、 SQL 性能纬霞。

3. 【強制】在 varchar 字段上建立索引時凌埂，必須指定索引長度，沒必要對全字段建立索引诗芜，根據(jù)實際文本區(qū)分度決定索引長度即可瞳抓。
   說明： 索引的長度與區(qū)分度是一對矛盾體，一般對字符串類型數(shù)據(jù)伏恐，長度為 20 的索引孩哑，區(qū)分度會高達 90%以上，可以使用 count(distinct left(列名, 索引長度))/count(*)的區(qū)分度來確定翠桦。

索引基數(shù)cardinality=count(distinct 列名),索引的選擇性=cardinality/count(*),這個數(shù)值通常在0~1之間横蜒，越接近1，查詢效率越高销凑，因為越接近1的時候丛晌，innodb引擎可以過濾的更多的行。而長度很長的varchar列會使索引變得很大且很慢斗幼，如果選擇該列的部分左前綴澎蛛，可以使索引選擇性接近1，可以僅選擇一定長度的左前綴進行索引蜕窿。

4. 【強制】頁面搜索嚴禁左模糊或者全模糊谋逻，如果需要請走搜索引擎來解決。
   說明： 索引文件具有 B-Tree 的最左前綴匹配特性渠羞，如果左邊的值未確定斤贰，那么無法使用此索引。

b+樹就是最左前綴匹配

5. 【推薦】如果有 order by 的場景次询，請注意利用索引的有序性荧恍。 order by 最后的字段是組合索引的一部分，并且放在索引組合順序的最后屯吊，避免出現(xiàn) file_sort 的情況送巡，影響查詢性能。
   正例： where a=? and b=? order by c; 索引： a_b_c
   反例： 索引中有范圍查找盒卸，那么索引有序性無法利用骗爆，如： WHERE a>10 ORDER BY b; 索引a_b 無法排序。

對于建一個表union_index_test（id,a,b,c）,在此表上建立一個聯(lián)合主鍵（a_b_c）

• 以a和b作為where條件蔽介，order by c看一下執(zhí)行計劃：

mysql> explain select * from union_index_test where a ='aa' and b='bb' order by c;
+----+-------------+------------------+------+---------------+-----------+---------+-------------+------+--------------------------+
| id | select_type | table            | type | possible_keys | key       | key_len | ref         | rows | Extra                    |
+----+-------------+------------------+------+---------------+-----------+---------+-------------+------+--------------------------+
|  1 | SIMPLE      | union_index_test | ref  | uni_index     | uni_index | 18      | const,const |    3 | Using where; Using index |
+----+-------------+------------------+------+---------------+-----------+---------+-------------+------+--------------------------+
1 row in set (0.01 sec)

• 以a作為where條件摘投，order by b 的執(zhí)行計劃：

mysql> explain select * from union_index_test where a='aa' order by b;
+----+-------------+------------------+------+---------------+-----------+---------+-------+------+--------------------------+
| id | select_type | table            | type | possible_keys | key       | key_len | ref   | rows | Extra                    |
+----+-------------+------------------+------+---------------+-----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | union_index_test | ref  | uni_index     | uni_index | 9       | const |    7 | Using where; Using index |
+----+-------------+------------------+------+---------------+-----------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

• 以a作為where條件煮寡，order by c 的執(zhí)行計劃：

mysql> explain select * from union_index_test where a ='aa' order by c;
+----+-------------+------------------+------+---------------+-----------+---------+-------+------+------------------------------------------+
| id | select_type | table            | type | possible_keys | key       | key_len | ref   | rows | Extra                                    |
+----+-------------+------------------+------+---------------+-----------+---------+-------+------+------------------------------------------+
|  1 | SIMPLE      | union_index_test | ref  | uni_index     | uni_index | 9       | const |    7 | Using where; Using index; Using filesort |
+----+-------------+------------------+------+---------------+-----------+---------+-------+------+------------------------------------------+

可以看到extra里面額外利用了filesort進行排序。

6. 【推薦】利用覆蓋索引來進行查詢操作犀呼， 避免回表幸撕。
   說明： 如果一本書需要知道第 11 章是什么標題，會翻開第 11 章對應的那一頁嗎外臂？目錄瀏覽一下就好坐儿，這個目錄就是起到覆蓋索引的作用。
   正例： 能夠建立索引的種類分為主鍵索引宋光、唯一索引貌矿、普通索引三種，而覆蓋索引只是一種查詢的一種效果罪佳，用 explain 的結果逛漫， extra 列會出現(xiàn)： using index。

覆蓋索引指的是要查詢的數(shù)據(jù)列都包含在索引中菇民。
還是對于表tt（id,a,b,c）,在此表上建立一個聯(lián)合主鍵（a_b）
當要返回所有列的時候尽楔，extra列沒有using index。

mysql> explain select * from tt where a='aa' and b='bb';
+----+-------------+-------+------+---------------+--------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref         | rows | Extra                 |
+----+-------------+-------+------+---------------+--------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | tt    | ref  | tt_a_b        | tt_a_b | 14      | const,const |    1 | Using index condition |
+----+-------------+-------+------+---------------+--------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)

如果只返回索引里面的列第练， extra 列出現(xiàn)： using index阔馋。如果返回索引里的列和主鍵，也會返回using index娇掏，這是因為二級索引的葉子節(jié)點是有主鍵列的呕寝。

mysql> explain select a,b  from tt where a='aa' and b='bb';
+----+-------------+-------+------+---------------+--------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref         | rows | Extra                    |
+----+-------------+-------+------+---------------+--------+---------+-------------+------+--------------------------+
|  1 | SIMPLE      | tt    | ref  | tt_a_b        | tt_a_b | 14      | const,const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------------+------+--------------------------+
1 row in set (0.00 sec)

7. 【推薦】利用延遲關聯(lián)或者子查詢優(yōu)化超多分頁場景。
   說明： MySQL 并不是跳過 offset 行婴梧，而是取 offset+N 行下梢，然后返回放棄前 offset 行，返回N 行塞蹭，那當 offset 特別大的時候孽江，效率就非常的低下，要么控制返回的總頁數(shù)番电，要么對超過特定閾值的頁數(shù)進行 SQL 改寫岗屏。
   正例： 先快速定位需要獲取的 id 段，然后再關聯(lián)：SELECT a.* FROM 表 1 a, (select id from 表 1 where 條件 LIMIT 100000,20 ) b where a.id=b.id

8. 【推薦】 SQL 性能優(yōu)化的目標：至少要達到 range 級別漱办，要求是 ref 級別这刷，如果可以是 consts最好。
   說明：
   1） consts 單表中最多只有一個匹配行（主鍵或者唯一索引）娩井，在優(yōu)化階段即可讀取到數(shù)據(jù)暇屋。
   2） ref 指的是使用普通的索引（normal index） 。
   3） range 對索引進行范圍檢索洞辣。
   反例： explain 表的結果咐刨， type=index昙衅，索引物理文件全掃描，速度非常慢定鸟，這個 index 級別比較 range 還低绒尊，與全表掃描是小巫見大巫。

用explain來解釋執(zhí)行計劃仔粥，type列指的是MySQL在表中找到所需行的方式。常見類型如下：

從左到右蟹但，查找性能由差到好躯泰。

• ALL是全表掃描，MySQL要遍歷全表來找到匹配的行
• index是索引全掃描华糖，MySQL遍歷整個索引來找到匹配的行
• range索引范圍掃描麦向，一般用于<、>客叉、<=诵竭、>=、between操作
• ref使用非唯一索引掃描或者唯一索引的前綴掃描兼搏，返回匹配單獨值的記錄行
• eq_ref,唯一索引卵慰，對于每個鍵值，表中只有一行記錄與之匹配
• const/system,單表只有一個匹配行佛呻，例如根據(jù)主鍵或者唯一索引記性的查詢

9. 【推薦】建組合索引的時候裳朋，區(qū)分度最高的在最左邊。
   正例： 如果 where a=? and b=? 吓著， a 列的幾乎接近于唯一值鲤嫡，那么只需要單建 idx_a 索引即
   可。
   說明： 存在非等號和等號混合判斷條件時绑莺，在建索引時暖眼，請把等號條件的列前置。如： where a>?and b=? 那么即使 a 的區(qū)分度更高纺裁，也必須把 b 放在索引的最前列诫肠。

建立組合索引時（a，b）時对扶，索引會先按照a排序区赵，再按照b排序

10.【推薦】 防止因字段類型不同造成的隱式轉換， 導致索引失效浪南。
11.【參考】創(chuàng)建索引時避免有如下極端誤解：
1） 寧濫勿缺笼才。 認為一個查詢就需要建一個索引。
2） 寧缺勿濫络凿。 認為索引會消耗空間骡送、嚴重拖慢更新和新增速度昂羡。
3） 抵制惟一索引。 認為業(yè)務的惟一性一律需要在應用層通過“先查后插”方式解決摔踱。

一些基本知識（都是基于innodb存儲引擎）

一虐先、innodb引擎

1.1 InnoDB體系架構

image.png

后臺線程

• Master Thread
  將緩存池中的數(shù)據(jù)異步刷新到磁盤
• IO Thread
  show engine innodb status \G;
  
  image.png
  也可以用show variables like 'innodb_%io_threads' \G;命令查看
  
  image.png
  InnoDB一共有4種IO Thread：insert buffer、log 派敷、write和read IO Thread
• Purge Thread
  事務被提交后蛹批，其所使用的undolog可能不再需要，因此需要Purge Thread來回收已經(jīng)已經(jīng)使用并分配的undo頁篮愉。在InnoDB1.1版本之前腐芍，purge操作只能在Master線程完成，從1.1版本開始试躏，可以用單獨的線程處理猪勇。用戶可以在配置文件中配置：

[mysqld]
innodb_purge_threads=1

從1.2版本之后，可以設置多個purge線程颠蕴，可以加快undo頁的回收泣刹。

• Page Cleaner Thread
  版本1.2.X中加入的，將之前版本中臟頁的刷新獨立到單獨的線程中犀被。

內(nèi)存

• 緩沖池(Buffer Pool)
  緩沖池是主存中的一個區(qū)域椅您，從中可以獲取InnoDB緩存表和索引數(shù)據(jù)。緩沖池是的那些頻繁使用的數(shù)據(jù)可以直接從內(nèi)存中獲取訪問
  InnoDB存儲引擎給予磁盤存儲寡键，將記錄按照頁的方式進行管理襟沮。由于CPU速度和磁盤速度不同，采取了緩沖池的技術來提高性能昌腰。為了提高高容量讀取操作的效率开伏，緩沖池被劃分為頁（page）的鏈表。

查看緩存池實例show variables like 'innodb_buffer_pool_instances' \G;

image.png

innodb_buffer_pool_stats表

LRU List遭商、FreeList 和FlushList

緩沖池的使用情況

緩存池命中率：
（1-innodb_buffer_pool_reads/innodb_buffer_pool_read_request）*100%

• ChangeBufer
  change buffer是一種特殊的數(shù)據(jù)結構固灵，當要修改的頁不在buffer pool中時，用來緩存對輔助索引頁的修改劫流。緩存起來的改變巫玻，可能是insert、update和delete操作祠汇，等到相關的頁被其他的讀進程讀入buffer pool中后仍秤，才會進行merge操作。ChangeBuffer位于共享表空間可很，即ibdata文件诗力，是物理頁的組成部分。
  不同于聚集索引我抠，二級索引通常都是非唯一的苇本，向二級索引的插入操作相對比較隨機袜茧。同樣的，刪除和更新操作經(jīng)會影響在二級索引里面不相鄰的頁瓣窄。等到受影響的頁被其他操作讀到緩沖池之后笛厦，再對緩存在changebuffer中的內(nèi)容進行merge操作，可以避免大量的磁盤隨機IO訪問俺夕。周期性地裳凸，當系統(tǒng)處于空閑狀態(tài)時運行的清除操作，或在緩慢關機期間劝贸，將更新后的索引頁寫入磁盤登舞。
  在系統(tǒng)空閑或緩慢關閉過程中，會執(zhí)行purge操作悬荣，將更新過的索引頁寫入磁盤。purge操作一次寫多個索引值會比每次修改后就立即寫入磁盤的效率高疙剑。
  如果有大量的二級索引要更新和大量受影響的行氯迂，那么change buffer的merge可能需要好幾個小時。在merge過程中言缤，磁盤的I/O會增加嚼蚀，可能會引起其他查詢的性能的降低。
  merge操作也可能發(fā)生在事務提交后管挟。事實上轿曙，即使在實例重啟后，還會可能發(fā)生merge操作僻孝。
  在內(nèi)存中导帝，change buffer會占用buffer pool的空間；在物理磁盤上穿铆，change buffer是系統(tǒng)表空間的一部分您单，所以對索引的修改在數(shù)據(jù)庫重啟后仍然存在change buffer中。
  change buffer包含的特性也叫作change buffering荞雏，包含insert buffering虐秦、delete buffering、purge buffering凤优。
• Adaptive Hash Index 自適應hash索引
  根據(jù)對查詢模式的觀測悦陋，如果某些索引頁頻繁被訪問，MySQL基于索引鍵的前綴構建hash索引筑辨。
• Doublewrite Buffer二次寫緩沖
  MySQL數(shù)據(jù)庫的頁默認是16K俺驶，但是linux文件系統(tǒng)的頁默認是4k，在頁向磁盤寫的時候棍辕，可能會發(fā)生丟失痒钝。MySQL用兩次寫機制保證了數(shù)據(jù)的可靠性秉颗。
  doublewrite buffer是位于系統(tǒng)表空間的存儲區(qū)域，在innodb將頁寫到磁盤正確位置之前送矩，會將頁緩沖到這個緩沖區(qū)蚕甥。只有將頁寫到這個緩沖區(qū)之后，系統(tǒng)才會將頁寫到磁盤栋荸。如果在寫入磁盤的過程中菇怀，發(fā)生了系統(tǒng)崩潰，后來恢復的時候也可以從系統(tǒng)表空間中恢復這部分頁晌块。雖然寫了兩次爱沟，但是磁盤IO的開銷并不會增大一倍，因為Doublewrite buffer是一個連續(xù)的塊匆背，只需要對操作系統(tǒng)進行一次fsync()調(diào)用呼伸。

二、聚集索引和非聚集索引的結構

在innodb钝尸，表結構稱之為索引組織表括享。innodb的索引的底層結構都是B+樹，B+樹包括葉子節(jié)點和非葉子節(jié)點珍促。索引包括聚集索引和二級索引（secondary index）铃辖，每個表只有一個聚集索引，是根據(jù)主鍵順序存放的猪叙，聚集索引的葉子節(jié)點保存了行記錄娇斩。mysql行由以下部分組成：

• DB_TRX_ID :插入或者更新這個行的事務id，6字節(jié)
• DB_ROLL_PTR ：回滾指針穴翩，指向回滾日志記錄rollback_segment（Tablespace包括leafnode segment犬第、non-leaf node segment和rollback segment）中記錄的undo 日志記錄。undo日志記錄包含了在rebuild數(shù)據(jù)更新前的這行記錄芒帕。7字節(jié)瓶殃。
• B_ROW_ID: 如果innodb引擎的表沒有顯示的創(chuàng)建主鍵，那么本列就是自動根據(jù)行插入增長的row_id副签，如果顯示創(chuàng)建了主鍵遥椿，那么這列就不存在索引里面了。
• ps:行里面有一個單獨的bit用來標志該行是否被刪除淆储。

二級索引的葉子節(jié)點保存了所有的索引字段以及主鍵冠场，如果where條件是二級索引，那么先要通過二級索引定位到主鍵本砰，再去聚集索引里面獲取其他字段碴裙。

三吞歼、多版本并發(fā)控制和二級索引 InnoDB multiversion concurrency control(MVCC)

InnoDB存儲結構

rollback segment里面的回滾（undo）日志分為delete日志和update日志务傲。插入回滾日志只有事務回滾的時候才會被用到坷随，而且可以在事務提交時丟棄饱岸。更新回滾日志可以在一致性讀中使用，它們只有在所有事務都執(zhí)行完畢后才可以刪除载慈，因為InnoDB在一致性讀中分配了一個快照惭等，需要根據(jù)update undo log中的信息重建一個更早版本的數(shù)據(jù)庫的行。（Update undo logs are used also in consistent reads, but they can be discarded only after there is no transaction present for which InnoDB has assigned a snapshot that in a consistent read could need the information in the update undo log to build an earlier version of a database row.這個長句給我看哭了~~~）
所以要經(jīng)常性的提交事務办铡，包括哪些只有重復讀的事件辞做。否則，innodb就不能及時丟棄update重做日志寡具，rollback segment就會占用太多表空間秤茅。
在innodb多版本模式下，行刪除并不是執(zhí)行sql語句后立即物理性地從數(shù)據(jù)庫移除童叠，innodb會在它丟棄了update undo log之后框喳，物理性移動對應的行和他的索引記錄。這個移除操作稱為purge,他十分迅速厦坛，一般和sql 聲明執(zhí)行刪除同時發(fā)生五垮。

聚集索引的更新操作是在原始位置（in-place）操作的，他們指向舊版本記錄的指針列可以重構粪般。二級索引則不是這樣，二級索引不含有隱藏的系統(tǒng)列更新操作也不是in-place的污桦。
當一個二級索引列更新時亩歹，舊的二級索引記錄會被標記為deleted，新的記錄會被插入凡橱，然后刪除的記錄會產(chǎn)生purge操作小作。當二級索引被標記為刪除或者二級索引頁被新的的事務更新時，InnoDB在聚集索引中查找數(shù)據(jù)庫記錄稼钩。在聚集索引中顾稀，檢查該記錄的DB_TRX_ID（事務版本號），如果記錄在這個讀事務開始之后被修改了坝撑，可以從回滾日志中回復正確的記錄版本静秆。如果一個二級索引的記錄被標志為已經(jīng)刪除，或者二級索引頁被一個新的事務更新了巡李，那么覆蓋索引技術接沒有用了抚笔，只能從聚集索引中獲取數(shù)據(jù)而不能直接從二級索引結構中直接返回。

ICP

index condition pushdown（ICP）是MySQL5.6啟用的新特性侨拦，是一種在存儲引擎層使用索引過濾數(shù)據(jù)的一種優(yōu)化方式殊橙。沒有啟用ICP，存儲引擎遍歷整個索引定位到基表中的行，然后將所有行返回至MySQL server層膨蛮，server層根據(jù)where條件來定位行叠纹。當ICP啟用時，如果經(jīng)評估可以使用部分存在于索引列的where條件來查找敞葛，server層將這部分條件查詢列下發(fā)到存儲引擎層進行查找誉察。然后，存儲引擎通過使用索引項來檢索pushed索引條件制肮，并且只有行記錄的值滿足where條件時才將其返回至server層冒窍。ICP能減少引擎層訪問基表的次數(shù)和MySQL Server 訪問存儲引擎的次數(shù)。
ICP使用條件：

• explain顯示的執(zhí)行計劃中type值（join 類型）為range豺鼻、 ref综液、 eq_ref或者ref_or_null。且查詢需要訪問表的整行數(shù)據(jù)儒飒，即不能直接通過二級索引的元組數(shù)據(jù)獲得查詢結果(索引覆蓋)谬莹。
• ICP可以用于innodb和MyISAM表，包括分區(qū)表桩了。
• 在InnoDB表結構中附帽，ICP只用于二級索引，ICP的目標是減少全表讀取的數(shù)目以降低IO操作井誉，對于聚集索引蕉扮，完整的記錄直接讀到了InnoDB緩存中，所以不會減少I/O操作颗圣。

四喳钟、分區(qū)

創(chuàng)建一個數(shù)據(jù)庫

建庫

空的數(shù)據(jù)庫

create table partition_test01(
id int auto_increment,
birth_day datetime not null,
sex char(1),
primary key(id,birth_day))
engine=innodb default charset=utf8 
partition by range(TO_DAYS(birth_day))(
partition p20180417 values less than (TO_DAYS('2018-04-18')),
partition p20180418 values less than (TO_DAYS('2018-04-19'))
);

包含分區(qū)表的數(shù)據(jù)庫目錄文件