2_mysql（索引、儲存引擎）

課程大綱：https://www.jianshu.com/p/edd2e20aeaee

一、索引及執行計劃

1、在功能上的分類
① 聚簇索引：建表時，指定了主鍵列，Mysql Innodb 會將主鍵作為聚簇索引列；如果沒有主鍵，會選擇唯一鍵作為聚集索引。
② 輔助索引
(1) 單列輔助索引
(2) 聯合索引：多個列作為索引條件，生成索引樹，理論上設計的好的，可以減少大量的回表查詢
注：聯合索引注意最左原則，inx(a,b,c)：查詢條件中，必須包含最左例。
(3) 字首索引： 使用欄位的前部分資料作為建立索引的關鍵字
③ 唯一索引：索引列的值都是唯一的。
PRI: 主鍵索引
UNI: 唯一索引
MUL: 輔助索引(單列,聯和,字首)
2、B+tree 索引樹高度影響因素
索引列值過長 , 解決方法：字首索引
資料量級， 解決方法：分表、分庫、分散式
資料型別：比如，變長長度字串，使用了char，解決方案：變長字串使用varchar
3、建立索引

alter table city add index idx_name(name);
聯合索引：
alter table city add index idx_co_po(countrycode,population);
字首索引： 
alter table city add index idx_di(district(5));
唯一索引：  
alter table city add unique index idx_uni1(name);

4、執行計劃獲取及分析
① 檢視執行計劃：explain + 語句
② 執行計劃顯示結果的分析
table：查詢操作的表，可精確到問題表
type
possible_keys：可能會走的索引
key：真正走的索引
key_len：聯合索引覆蓋長度
rows
Extra
(1) type ：索引型別
all：全表掃描，不用任何索引
index：全索引掃描 —> 把整個索引樹掃描一遍
range：索引範圍掃描
—> 輔助索引> < >= <= LIKE IN OR
—> 特殊情況：主鍵 <> NOT IN
ref：非唯一性索引，輔助索引等值查詢
eq_ref：針對多表連線中，非驅動表連線條件是主鍵或唯一鍵
const( system )：聚簇索引等值查詢
(2) extra
filesort ,檔案排序
—> 當我們看到執行計劃extra位置出現filesort,說明由檔案排序出現
—> 觀察需要排序(ORDER BY,GROUP BY ,DISTINCT )的條件,有沒有索引
—> 根據子句的執行順序,去建立聯合索引
5、索引應用規範
5.1 建表時一定要有主鍵,一般是個無關列
5.2 選擇唯一性索引
5.3 為經常需要where 、ORDER BY、GROUP BY,join on等操作的欄位，建立索引，優化查詢
5.4 儘量使用字首來索引
5.5 限制索引的數目
5.6 刪除不再使用或者很少使用的索引
5.7 大表加索引,要在業務不繁忙期間操作
5.8 儘量少在經常更新值的列上建索引
5.9 建索引原則
(1) 必須要有主鍵,如果沒有可以做為主鍵條件的列,建立無關列
(2) 經常做為where條件列 order by group by join on, distinct 的條件(業務:產品功能+使用者行為)
(3) 最好使用唯一值多的列作為索引,如果索引列重複值較多,可以考慮使用聯合索引
(4) 列值長度較長的索引列,我們建議使用字首索引.
(5) 降低索引條目,一方面不要建立沒用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引維護要避開業務繁忙期
6、不走索引的情況（開發規範）
6.1 沒有查詢條件，或者查詢條件沒有建立索引
6.2 查詢結果集是原表中的大部分資料，應該是25％以上。
6.3 索引本身失效，統計資料不真實
索引有自我維護的能力。對於表內容變化比較頻繁的情況下，有可能會出現索引失效，一般是刪除重建。
6.4 查詢條件使用函式在索引列上，或者對索引列進行運算
6.5 隱式轉換導致索引失效
6.6 <> ，not in 不走索引（輔助索引）
單獨的>,<,in 有可能走，也有可能不走，和結果集有關，儘量結合業務新增limit
or或in 儘量改成union
6.7 like “%_” 百分號在最前面不走
7、優化器對索引的演算法
7.1 MySQL索引的自優化-AHI
MySQL的InnoDB引擎，能夠建立只有Btree。AHI作用： 自動評估"熱"的記憶體索引page,生成HASH索引表。幫助InnoDB快速讀取索引頁。加快索引讀取的所讀。相當與索引的索引。
7.2 MySQL索引的自優化-Change buffer
比如insert，update，delete 資料。對於聚簇索引會立即更新。對於輔助索引，不是實時更新的。
在InnoDB 記憶體結構中，加入了insert buffer（會話），現在版本叫change buffer。Change buffer 功能是臨時緩衝輔助索引需要的資料更新。當我們需要查詢新insert 的資料，會在記憶體中進行merge(合併)操作，此時輔助索引就是最新的。
7.3 ICP 索引下推
作用： 解決了聯合索引只能部分應用情況。為了使減少沒必要的資料頁被掃描。將不走索引的條件，在engine層取資料之前先做c二次過濾。一些無關資料就會被提前過濾掉。
例子：index(a,b,c)select * from t1 where a= and c=
7.4 MRR muti range read
mysql> set global optimizer_switch=‘mrr=on,mrr_cost_based=off’;
輔助索引 —回表----》 聚簇索引轉換為輔助索引 —>sort id --回表–> 聚簇索引
7.5 SNLJ
7.6 BNLJ
在 A和B關聯條件匹配時，不再一次一次進行迴圈。而是採用一次性將驅動表的關聯值和非驅動表匹配.一次性返回結果主要優化了， CPU消耗，減少了IO次數。
7.7 BKA
主要作用，使用來優化非驅動表的關聯列有輔助索引。BNL+ MRR的功能。

二、儲存引擎

1、儲存引擎種類
InnoDB、MyISAM 等
TokuDB 、RocksDB、MyRocks，三種儲存引擎的共同點:壓縮比較高,資料插入效能極高。現在很多的NewSQL,使用比較多的功能特性.
2、InnoDB核心特性

問題一：請你列舉MySQL InnoDB儲存優點？
問題二：請你列舉 InooDB和MyIsam的區別？
Clustered index
Change buffer
自適應hash索引：AHI
MVCC：多版本併發控制
多緩衝區池
事務
行級鎖粒度；MyISAMs是表鎖級
外來鍵
更多複製特性
支援熱備份
自動故障恢復
alter table x engine = innodb; 進行innodb表的碎片化整理
3、MySql的儲存引擎體系結構
3.1 巨集觀機構
① MyISAM
myt.frm：存放資料字典資訊（列的定義和屬性）
myt.MYD：存放資料行資訊
myt.MYI：存放索引資訊
② InnoDB
myt.frm：存放資料字典資訊（列的定義和屬性）
myt.ibd：獨立表空間檔案，存放資料行和索引資訊
ibdata1：共享表空間檔案，存放數字字典資訊，undo logs、double write、change buffer磁碟區域
③ 說明：不同版本ibdata1中儲存的資料不一樣
5.5：ibdata1 中還會儲存臨時表資料 + 使用者資料(資料行+索引)
5.6：ibdata1 中還會儲存臨時表資料
8.0：ibdata1 取消儲存資料字典資訊，將 undo logs 獨立出去
ibdata1 在慢慢瘦身，將比較關鍵的資料獨立出來了
ib_logfile0 ~ ib_logfile1：InnoDB 事務重做日誌（redo logs）
ibtmp1：臨時表空間檔案（排序、分組、多表連線、子查詢、邏輯備份等）
ib_buffer_pool：正常關庫時，儲存緩衝區的熱資料
結論：僅僅拷貝ibd、frm檔案到新的資料庫是無法正常使用的

3.2 InnoDB微觀結構
① 磁碟

表空間
(1) 什麼是表空間？
表空間的概念是引入與Oracle資料庫
起初是為了解決儲存空間擴充套件的問題，MySQL5.5引入了共享表空間模式

(2) MySQL表空間型別
	共享表空間：在5.5版本引入了共享表空間(ibdata1)作為預設儲存方式，用於儲存系統資料、日誌、undo、臨時表、使用者資料和索引
	獨立表空間：5.6版本預設獨立表空間模式，單表單表空間
	普通表空間：完全和Oracle一致的表空間管理模式，更加自定義化
	undo表空間：儲存undo logs(回滾日誌)
	臨時表空間：儲存臨時表，5.7預設獨立

(3) 表空間管理
	使用者資料預設的儲存方式：獨立表空間模式(5.6後)，獨立表空間和共享表空間是可以相互切換的
	-- 檢視預設表空間模式
	-- 1代表獨立表空間模式；0代表共享表空間模式
	select @@innodb_file_per_table;
	
	-- 切換表空間模式
	-- 說明：修改完成後隻影響新建立的表
	set global innodb_file_per_table=0;
	
	-- 檢視共享表空間大小和個數
	mysql> select @@innodb_data_file_path;
	+-------------------------+
	| @@innodb_data_file_path |
	+-------------------------+
	| ibdata1:12M:autoextend  |
	+-------------------------+
	1 row in set (0.00 sec)
	
	-- 擴充套件共享表空間大小和個數
	-- 說明：通常是在初始化資料時就設定號引數
	-- 方法1：初始化之前，在my.cnf加入以下配置
	innodb_data_file_path=idata1:1G;idata2:1G:autoextend
	-- 方法2：在已執行中的資料庫上擴充套件多個ibdata檔案
	-- 在設定innodb_data_file_path引數時，已有的ibdata1大小應該和磁碟上真實大小一致，再進行新增
	innodb_data_file_path=idata1:xx;idata2:1G:idata3:1G:autoextend

事務日誌：
redo logs：重做日誌
(1) 檔案位置
	/data/3306/ib_logfile0~ib_logfileN
(2)	控制引數
	-- 設定檔案大小
	innodb_log_file_size=50331648
	-- 設定檔案個數
	innodb_log_files_in_group=2
	-- 設定儲存位置
	innodb_log_group_home_dir=./
(3) 功能：
	用來儲存MySQL在做修改類(DML)操作時資料頁變化過程及版本號(LSN),屬於物理日誌。預設兩個檔案儲存redo，是迴圈覆蓋使用的

undo logs：回滾日誌
(1) 檔案位置
	/data/3306/ibdata1
	/data/3306/ibtmp1
(2) 控制引數
	-- 回滾段的個數
	innodb_rollback_segments=128
(3) 功能:
	用來儲存回滾日誌，相當於快照，提供InnoDB多版本併發讀寫，屬於邏輯日誌。通過記錄的每次操作的反操作，提供回滾功能

② 記憶體