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

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

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

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

4. 【強(qiáng)制】頁面搜索嚴(yán)禁左模糊或者全模糊，如果需要請走搜索引擎來解決。
   說明： 索引文件具有 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進(jìn)行排序。

6. 【推薦】利用覆蓋索引來進(jìn)行查詢操作， 避免回表。
   說明： 如果一本書需要知道第 11 章是什么標(biāo)題，會翻開第 11 章對應(yīng)的那一頁嗎？目錄瀏覽一下就好，這個目錄就是起到覆蓋索引的作用。
   正例： 能夠建立索引的種類分為主鍵索引、唯一索引、普通索引三種，而覆蓋索引只是一種查詢的一種效果，用 explain 的結(jié)果， extra 列會出現(xiàn)： using index。

覆蓋索引指的是要查詢的數(shù)據(jù)列都包含在索引中。
還是對于表tt（id,a,b,c）,在此表上建立一個聯(lián)合主鍵（a_b）
當(dāng)要返回所有列的時候，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. 【推薦】利用延遲關(guān)聯(lián)或者子查詢優(yōu)化超多分頁場景。
   說明： MySQL 并不是跳過 offset 行，而是取 offset+N 行，然后返回放棄前 offset 行，返回N 行，那當(dāng) offset 特別大的時候，效率就非常的低下，要么控制返回的總頁數(shù)，要么對超過特定閾值的頁數(shù)進(jìn)行 SQL 改寫。
   正例： 先快速定位需要獲取的 id 段，然后再關(guān)聯(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)化的目標(biāo)：至少要達(dá)到 range 級別，要求是 ref 級別，如果可以是 consts最好。
   說明：
   1） consts 單表中最多只有一個匹配行（主鍵或者唯一索引），在優(yōu)化階段即可讀取到數(shù)據(jù)。
   2） ref 指的是使用普通的索引（normal index） 。
   3） range 對索引進(jìn)行范圍檢索。
   反例： explain 表的結(jié)果， 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.【推薦】 防止因字段類型不同造成的隱式轉(zhuǎn)換， 導(dǎo)致索引失效。
11.【參考】創(chuàng)建索引時避免有如下極端誤解：
1） 寧濫勿缺。 認(rèn)為一個查詢就需要建一個索引。
2） 寧缺勿濫。 認(rèn)為索引會消耗空間、嚴(yán)重拖慢更新和新增速度。
3） 抵制惟一索引。 認(rèn)為業(yè)務(wù)的惟一性一律需要在應(yīng)用層通過“先查后插”方式解決。

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

一、innodb引擎

1.1 InnoDB體系架構(gòu)

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
  事務(wù)被提交后，其所使用的undolog可能不再需要，因此需要Purge Thread來回收已經(jīng)已經(jīng)使用并分配的undo頁。在InnoDB1.1版本之前，purge操作只能在Master線程完成，從1.1版本開始，可以用單獨的線程處理。用戶可以在配置文件中配置：

[mysqld]
innodb_purge_threads=1

從1.2版本之后，可以設(shè)置多個purge線程，可以加快undo頁的回收。

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

內(nèi)存

• 緩沖池(Buffer Pool)
  緩沖池是主存中的一個區(qū)域，從中可以獲取InnoDB緩存表和索引數(shù)據(jù)。緩沖池是的那些頻繁使用的數(shù)據(jù)可以直接從內(nèi)存中獲取訪問
  InnoDB存儲引擎給予磁盤存儲，將記錄按照頁的方式進(jìn)行管理。由于CPU速度和磁盤速度不同，采取了緩沖池的技術(shù)來提高性能。為了提高高容量讀取操作的效率，緩沖池被劃分為頁（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ù)結(jié)構(gòu)，當(dāng)要修改的頁不在buffer pool中時，用來緩存對輔助索引頁的修改。緩存起來的改變，可能是insert、update和delete操作，等到相關(guān)的頁被其他的讀進(jìn)程讀入buffer pool中后，才會進(jìn)行merge操作。ChangeBuffer位于共享表空間，即ibdata文件，是物理頁的組成部分。
  不同于聚集索引，二級索引通常都是非唯一的，向二級索引的插入操作相對比較隨機(jī)。同樣的，刪除和更新操作經(jīng)會影響在二級索引里面不相鄰的頁。等到受影響的頁被其他操作讀到緩沖池之后，再對緩存在changebuffer中的內(nèi)容進(jìn)行merge操作，可以避免大量的磁盤隨機(jī)IO訪問。周期性地，當(dāng)系統(tǒng)處于空閑狀態(tài)時運行的清除操作，或在緩慢關(guān)機(jī)期間，將更新后的索引頁寫入磁盤。
  在系統(tǒng)空閑或緩慢關(guān)閉過程中，會執(zhí)行purge操作，將更新過的索引頁寫入磁盤。purge操作一次寫多個索引值會比每次修改后就立即寫入磁盤的效率高。
  如果有大量的二級索引要更新和大量受影響的行，那么change buffer的merge可能需要好幾個小時。在merge過程中，磁盤的I/O會增加，可能會引起其他查詢的性能的降低。
  merge操作也可能發(fā)生在事務(wù)提交后。事實上，即使在實例重啟后，還會可能發(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 自適應(yīng)hash索引
  根據(jù)對查詢模式的觀測，如果某些索引頁頻繁被訪問，MySQL基于索引鍵的前綴構(gòu)建hash索引。
• Doublewrite Buffer二次寫緩沖
  MySQL數(shù)據(jù)庫的頁默認(rèn)是16K，但是linux文件系統(tǒng)的頁默認(rèn)是4k，在頁向磁盤寫的時候，可能會發(fā)生丟失。MySQL用兩次寫機(jī)制保證了數(shù)據(jù)的可靠性。
  doublewrite buffer是位于系統(tǒng)表空間的存儲區(qū)域，在innodb將頁寫到磁盤正確位置之前，會將頁緩沖到這個緩沖區(qū)。只有將頁寫到這個緩沖區(qū)之后，系統(tǒng)才會將頁寫到磁盤。如果在寫入磁盤的過程中，發(fā)生了系統(tǒng)崩潰，后來恢復(fù)的時候也可以從系統(tǒng)表空間中恢復(fù)這部分頁。雖然寫了兩次，但是磁盤IO的開銷并不會增大一倍，因為Doublewrite buffer是一個連續(xù)的塊，只需要對操作系統(tǒng)進(jìn)行一次fsync()調(diào)用。

二、聚集索引和非聚集索引的結(jié)構(gòu)

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

• DB_TRX_ID :插入或者更新這個行的事務(wù)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用來標(biāo)志該行是否被刪除。

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

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

InnoDB存儲結(jié)構(gòu)

rollback segment里面的回滾（undo）日志分為delete日志和update日志。插入回滾日志只有事務(wù)回滾的時候才會被用到，而且可以在事務(wù)提交時丟棄。更新回滾日志可以在一致性讀中使用，它們只有在所有事務(wù)都執(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)常性的提交事務(wù)，包括哪些只有重復(fù)讀的事件。否則，innodb就不能及時丟棄update重做日志，rollback segment就會占用太多表空間。
在innodb多版本模式下，行刪除并不是執(zhí)行sql語句后立即物理性地從數(shù)據(jù)庫移除，innodb會在它丟棄了update undo log之后，物理性移動對應(yīng)的行和他的索引記錄。這個移除操作稱為purge,他十分迅速，一般和sql 聲明執(zhí)行刪除同時發(fā)生。

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

ICP

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

• explain顯示的執(zhí)行計劃中type值（join 類型）為range、 ref、 eq_ref或者ref_or_null。且查詢需要訪問表的整行數(shù)據(jù)，即不能直接通過二級索引的元組數(shù)據(jù)獲得查詢結(jié)果(索引覆蓋)。
• ICP可以用于innodb和MyISAM表，包括分區(qū)表。
• 在InnoDB表結(jié)構(gòu)中，ICP只用于二級索引，ICP的目標(biāo)是減少全表讀取的數(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ù)庫目錄文件