学习笔记 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文件 ⑦：变更完成