MYSQL数据库底层基础专栏

什么时候需要重建索引？

当索引建立后在实际业务使用中频繁发生update delete操作
如何判断索引是否应该被重建？
1.索引倾斜度，看是否浪费了空间，对索引进行分析
analyze index #index_name validate structrue;
2.在session中查询index_stats表
select height,DEL_LF_ROWS/LF_ROWS from index_stats;

当height的结果，即索引深度，从根节点到叶节点的高度，或者DEL_LF_ROWS/LF_ROWS>0.2的情况下，需要考虑重建索引。

索引的重建

drop index #index_name
1

create index #index_name on #table_name(index_column);
1

但是将索引删除再重建的情况是比较耗时的
所以一般建议使用索引的重构语句

alter index #index_name rebuild;
1

alter index #index_name rebuild online;
1

rebuild是重建索引的有效方法，因为重建索引是基于现有的数据重建的，如果再重建过程中，有其他的用户再操作表数据，可以利用online，避免索引重建可能出现的锁问题。
即rebuild 会阻塞DML操作，但是rebuild online 不会

Hash索引和B树索引

hash索引底层是哈希表，当查找数据时，会根据数据进行哈希得到相应的键值，然后根据回表得到最终的数据。
B+树是多路平衡查找树，每次查找从根节点出发，经过一个节点即一次IO操作，B+树每个叶子节点都有数据连接，查找叶子节点可以获取所查键值，然后查询判断是否需要回表查询数据。

hash在等值查找时更快，但是hash是无序的，所以不支持范围查找，而B+树的叶子节点都遵循左低右高的准则。所以支持范围查找。
除了范围，hash索引也不能支持索引进行排序。
同时，hash索引也不支持模糊查询，以及多列索引的最左前缀匹配，其原因也是因为hash函数是散列的。
hash索引在任何场景下都无法避免回表，而B+树在聚簇索引，覆盖索引时可以通过索引完成查询。避免回表。

事务

事务的四个特性：原子性，一致性，持久性，隔离性。
原子性实现原理：当事务回滚时撤回所有已执行成功的语句。InnoDB回滚是因为有undo.log，当事务对数据库进行修改操作时，InnoDB会生成对应的undo.log。当事务失败时，调用rollback方法，导致事务回滚，则利用undo.log回滚到之前的数据。
持久性的实现原理：如果数据的每次读写都需要操作磁盘，会降低数据库的效率。所以InnoDB中提供了缓存，（Buffer Pool），其中包含了磁盘数据的映射，作为访问数据库的缓存。当数据库读取数据时，从BufferPool中先读取数据，如果数据库没有，再从数据库读取，读取后放入Buffer Pool，当写场景时，关于写操作的数据先写入Buffer Pool，Buffer Pool中的数据再定期刷入磁盘。即为刷脏。其中这个Buffer Pool的作用和redis其实蛮像的，所以也带来了许多和redis作为缓存一样的宕机问题。所以InnoDB中会有redo.log来解决当Buffer Pool宕机时，数据未存入磁盘的问题。当事务提交时，调用fsync接口对redo.log进行刷盘，如果Mysql宕机，重启时可以读取redo.log中的数据恢复数据库。

为什么Buffer Pool的日志比redo.log将日志写入磁盘慢？

刷脏（BufferPool）是随机IO，每次修改的数据都是随机的。但是redo.log的追加的操作，是顺序IO。
刷脏里面包含的数据是很多的，以页为单位，redo.log只包含需要写入的部分，无效IO减少。

隔离性实现原理：隔离性是通过锁机制实现的，事务修改数据之前必须获取到锁才可以修改数据。按照锁粒度，分为表锁，行锁，以及位于表锁，行锁之间的锁，表锁是操作数据时，锁住整张表，只有一个事务可以提交成功。
行锁则是锁住操作的那一行数据，并发性好，但是加锁本身就是一种耗费资源的操作，因此锁表也是可以节省资源的。
MyISAM存储引擎只支持表锁，InnoDB支持行锁。
同时，InnoDB也通过MVCC保证隔离性，MVCC是Mutli-Version Concurrency Control，即多版本并发控制协议，优点是读不加锁。

数据库的锁类型，InnoDB存储引擎有两种行级锁，分为共享锁和排它锁。
共享锁：允许事务读一行数据。
排它锁：允许事务删除或者修改一行数据。

共享锁主要是实现锁的兼容，事务A获得a行数据的共享锁，同时事务B也可以获取a行数据的共享锁，但当事务C想获取a行数据的排它锁，必须等事务AB都释放a行的共享锁才可以获取到排它锁。

关于死锁

死锁是指当两个及两个以上的事务执行过程同时争夺统一资源造成互相等待的场景，无外力作用无法继续推进。
解决死锁的直接方法是超时，当两个事务互相等待时，当一个事务的等待时间设置超过某一个阈值，其中一个事务回滚，另一个等待的事务继续运行。

数据库也会采用wait-for graph（等待图）检测死锁，相比锁超时，这是一种主动发现死锁的方式，wait-for graph的数据库会保存两种信息：锁的信息链表，事务等待链表
通过链表可以构造出一张图，如果图存在回路，代表存在死锁，资源互相等待，在每个事务请求之前都会进行判断，如果存在死锁InnoDB会回滚undo量最小的事务。