學習筆記 12 數據庫索引和鎖

索引是為了提高查詢的效率。
常見的索引模型有hash索引，有序數組，二叉樹索引。
hash索引：hash表是一種以鍵值對存儲的數據結構，適用于等值查詢場景；由于數據不是有序存儲的，所以范圍查詢時效率比較差。
有序數組：適用于靜態存儲引擎，數據不會發生改變的場景，等值查詢和范圍查詢的效率都很好。
N叉數索引：為了讓查詢盡量少地讀磁盤，不適用二叉樹，使用N叉樹。

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

MySQL鎖的類型：
全局鎖，表鎖，行鎖。
全局鎖對數據庫整個實例加鎖，flush table with read lock,在邏輯備份時使用。
表級鎖：表鎖和元數據鎖
表鎖，lock tables *** read/write
元數據鎖

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

3.1、Prepare階段:

①：創建新的臨時frm文件(與InnoDB無關) ②：持有EXCLUSIVE-MDL鎖，禁止讀寫 ③：根據alter類型，確定執行方式(copy,online-rebuild,online-norebuild)

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

④：更新數據字典的內存對象

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

3.2、ddl執行階段:

①：降級EXCLUSIVE-MDL鎖，允許讀寫

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

④：根據rec構造對應的索引項

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

3.3、commit階段:

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