學(xué)習(xí)筆記 12 數(shù)據(jù)庫(kù)索引和鎖

索引是為了提高查詢的效率。
常見的索引模型有hash索引，有序數(shù)組，二叉樹索引。
hash索引：hash表是一種以鍵值對(duì)存儲(chǔ)的數(shù)據(jù)結(jié)構(gòu)，適用于等值查詢場(chǎng)景；由于數(shù)據(jù)不是有序存儲(chǔ)的，所以范圍查詢時(shí)效率比較差。
有序數(shù)組：適用于靜態(tài)存儲(chǔ)引擎，數(shù)據(jù)不會(huì)發(fā)生改變的場(chǎng)景，等值查詢和范圍查詢的效率都很好。
N叉數(shù)索引：為了讓查詢盡量少地讀磁盤，不適用二叉樹，使用N叉樹。

INNODB使用B+索引樹模型，表按照主鍵的順序以索引的形式存放，即索引組織表。索引分為主鍵索引和非主鍵索引(二級(jí)索引)。
非主鍵索引查詢數(shù)據(jù)需要回表，覆蓋索引由于要查詢的值已經(jīng)存在于索引中，因此不需要回表，減少樹的搜索次數(shù)，提升性能。
B+樹索引結(jié)構(gòu)，可以使用最左前綴原則來(lái)定位數(shù)據(jù)。
建立聯(lián)合索引時(shí)，注意安排字段的順序，盡量使得需要維護(hù)的索引的數(shù)目最少。
MySQL 5.6引入索引下推優(yōu)化，在索引遍歷的過程中，對(duì)索引包含的字段先做判斷，過濾不滿足條件的記錄，減少回表次數(shù)。

MySQL鎖的類型：
全局鎖，表鎖，行鎖。
全局鎖對(duì)數(shù)據(jù)庫(kù)整個(gè)實(shí)例加鎖，flush table with read lock,在邏輯備份時(shí)使用。
表級(jí)鎖：表鎖和元數(shù)據(jù)鎖
表鎖，lock tables *** read/write
元數(shù)據(jù)鎖

MySQL在線對(duì)表DDL操作時(shí)，如果有未提交的事務(wù)，也會(huì)造成數(shù)據(jù)庫(kù)堵塞。
在線添加索引：
alter table test add index index_id(column1) algrithm=inplace;
如下online ddl總結(jié)來(lái)源于博客：
https://www.cnblogs.com/beef/p/7376035.html
https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html
online ddl主要包括3個(gè)階段，prepare階段，ddl執(zhí)行階段，commit階段，rebuild方式比no-rebuild方式實(shí)質(zhì)多了一個(gè)ddl執(zhí)行階段，prepare階段和commit階段類似。下面將主要介紹ddl執(zhí)行過程中三個(gè)階段的流程。

3.1、Prepare階段:

①：創(chuàng)建新的臨時(shí)frm文件(與InnoDB無(wú)關(guān)) ②：持有EXCLUSIVE-MDL鎖，禁止讀寫 ③：根據(jù)alter類型，確定執(zhí)行方式(copy,online-rebuild,online-norebuild)

假如是Add Index，則選擇online-norebuild即INPLACE方式             

④：更新數(shù)據(jù)字典的內(nèi)存對(duì)象

⑤：分配row_log對(duì)象記錄增量(僅rebuild類型需要) ⑥：生成新的臨時(shí)ibd文件(僅rebuild類型需要)

3.2、ddl執(zhí)行階段:

①：降級(jí)EXCLUSIVE-MDL鎖，允許讀寫

②：掃描old_table的聚集索引每一條記錄rec ③：遍歷新表的聚集索引和二級(jí)索引，逐一處理

④：根據(jù)rec構(gòu)造對(duì)應(yīng)的索引項(xiàng)

⑤：將構(gòu)造索引項(xiàng)插入sort_buffer塊排序 ⑥：將sort_buffer塊更新到新的索引上 ⑦：記錄ddl執(zhí)行過程中產(chǎn)生的增量(僅rebuild類型需要) ⑧：重放row_log中的操作到新索引上(no-rebuild數(shù)據(jù)是在原表上更新的) ⑨：重放row_log間產(chǎn)生dml操作append到row_log最后一個(gè)Block

3.3、commit階段:

①：當(dāng)前Block為row_log最后一個(gè)時(shí)，禁止讀寫，升級(jí)到EXCLUSIVE-MDL鎖 ②：重做row_log中最后一部分增量 ③：更新innodb的數(shù)據(jù)字典表 ④：提交事務(wù)(刷事務(wù)的redo日志) ⑤：修改統(tǒng)計(jì)信息 ⑥：rename臨時(shí)idb文件，frm文件 ⑦：變更完成