MYSQL篇-03-索引&锁&事务

四、MySQL索引类型级结构

1、从应用层次划分

索引分类
主键索引	一种特殊的唯一索引，一个表只能有一个主键
普通索引	最基本的索引，它没有任何限制，用于加速查询。	ALTER TABLE table ADD INDEX index_name (name);
唯一索引	索引列的值必须唯一，但允许有空值。	ALTER TABLE table ADD UNIQUE INDEX index_name (name);
组合索引	多个字段上创建的索引	ALTER TABLE table ADD INDEX index__name (id,name);
全文索引	用来查找文本中的关键字，而不是直接与索引中的值相比较。	ALTER TABLE table ADD FULLTEXT INDEX index_contents(contents);
覆盖索引	select的内容都在索引中
聚集索引	指InnoDB 数据都是存储在主键索引的叶子结点中，而MyISAM叶子结点式存储的数据的内存地址

2、从存储结构划分

存储结构划分：
BTree索引（B-Tree或B+Tree索引）
Hash索引
full-index全文索引
R-Tree索引

3、MySQL数据库索引采用的B+Tree优势

二叉树	如果递增，不能体现出优势
红黑树	树的高度还是很大
HASH表	不能适用于范围查找
B-Tree	每个节点存储多个索引+数据的信息，对红黑树的优化，但是每个节点存储的数据有限（16K），高度还是很高。
B+Tree	每个节点只存储索引，数据都是存储在叶子结点，但是会有节点冗余，树的高度大大的减小。其中每个叶子结点还有一个指针指向的，对于范围查找有一定的优势

4、什么是索引下推

这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和 position是无序的，无法很好的利用索引。

 SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager' 

MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 ‘LiLei’ 开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。

索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引，innodb的主键索引(聚簇索引)树叶子节点上保存的是全 行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。

为什么范围查找Mysql没有用索引下推优化?

估计应该是Mysql认为范围查找过滤的结果集过大，like KK% 在绝大多数情况来看，过滤后的结果集比较小，所以Mysql选择给 like KK% 用索引下推优化。

--- 关闭索引下推
set optimizer_switch='index_condition_pushdown=off';

五、MySQL锁与事务的隔离级别

1、MySQL锁分类

①、锁定义

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

锁分类
性能	对数据库操作	InnoDB中有几种行级锁类型	对数据库操作的粒度
乐观锁（版本比较）	读锁（属于悲观锁，又称共享锁）	Record Lock：在索引记录上加锁	表锁：锁住整张表。开销小，加锁快；锁定粒度大，发生锁冲突的概率最高，并发度最低；
悲观锁	写锁（属于悲观锁，又称排它锁）	Gap Lock：间隙锁	行锁：每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。
		Next-key Lock：Record Lock+Gap Lock	页锁：BDB支持页级锁

共享锁就是允许多个线程同时获取一个锁，一个锁可以同时被多个线程拥有。

排它锁，也称作独占锁，一个锁在某一时刻只能被一个线程占有，其它线程必须等待锁被释放之后才可能获取到锁。

悲观锁的优点和不足： 悲观锁实际上是采取了“先取锁在访问”的策略，为数据的处理安全提供了保证，但是在效率方面，由于额外的加锁机制产生了额外的开销，并且增加了死锁的机会。并且降低了并发性；当一个事物所以一行数据的时候，其他事物必须等待该事务提交之后，才能操作这行数据。

乐观锁的优点和不足：

乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。但如果直接简单这么做，还是有可能会遇到不可预期的结果，例如两个事务都读取了数据库的某一行，经过修改以后写回数据库，这时就遇到了问题。

注意：1、MyISAM存储引擎不支持行级别的锁。所以读锁会阻塞写，但是不会阻塞读；而写锁则会把读和写都阻塞。

2、InnoDB的行锁是针对索引加的锁，不是针对记录加的锁， 如果对非索引字段更新，行锁可能会变表锁。（场景：当 Where 查询条件中的字段没有索引时，更新操作会锁住全表！）

②、InnoDB锁的特性

锁的特性
1	在不通过索引条件查询的时候，InnoDB使用的确实是表锁！
2	由于 MySQL 的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行 的记录,但是如果是使用相同的索引键,是会出现锁冲突的。
3	当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论 是使用主键索引、唯一索引或普通索引,InnoDB 都会使用行锁来对数据加锁。
4	即便在条件中使用了索引字段,但是否使用索引来检索数据是由 MySQL 通过判断不同 执行计划的代价来决定的,如果 MySQL 认为全表扫 效率更高,比如对一些很小的表,它 就不会使用索引,这种情况下 InnoDB 将使用表锁,而不是行锁。因此,在分析锁冲突时, 别忘了检查 SQL 的执行计划（explain查看）,以确认是否真正使用了索引。

③、意向锁

意向共享锁、意向排它锁

2、InnoDB行锁问题失效死锁等

①、行锁升级为表锁的情况：

1、更新操作：条件没有使用索引；

2、更新操作：条件使用索引，但是索引失效；

锁定行操作：

锁定某一行还可以用lock in share mode(共享锁) 和for update(排它锁)，例如：select * from test_innodb_lock where a = 2 for update;这样其他session只能读这行数据，修改则会被阻塞，直到锁定行的session提交

②、INSERT死锁问题(show engine innodb status)

https://www.cnblogs.com/deliver/p/5730616.html

https://blog.csdn.net/hj7jay/article/details/56274056

– insert造成的死锁：

– 情况一：插入造成的

– 事务1插入但是事务还没有提交，获取排它锁；

– 事务2和事务3插入的时候发现，有冲突，准备获取共享锁，但是事务1加了排它锁，在等待队列中；

– 此时事务1 rollback了，事务2和事务3想插入必须获取排它锁，

– 但是他们都已经获取了共享锁，所以无法获取排它锁，于是发生了死锁

– 情况二：删除造成：删除了，但是rollback了

解决insert死锁可以指定使用排它锁：

sql和insert加锁的不同的是，如果检测到键冲突，它直接申请加排它锁，而不是共享锁。

INSERT ... ON DUPLICATE KEY UPDATE

③、行锁分析

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

show status like'innodb_row_lock%';
show status like 'table%'；

对各个状态量的说明如下
Innodb_row_lock_current_waits	当前正在等待锁定的数量
Innodb_row_lock_time	从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg	每次等待所花平均时间
Innodb_row_lock_time_max	从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits	系统启动后到现在总共等待的次数

行锁更新操作更新优化建议
1	尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
2	合理设计索引，尽量缩小锁的范围
3	尽可能减少检索条件范围，避免间隙锁
4	尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql
5	尽量放在事务最后执行
6	尽可能低级别事务隔离

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁;

3、MySQL事务

事务的四大特性
原子性(Atomicity)	事务是一个原子操作单元，其对数据的修改,要么全都执行，要么全都不执行。
一致性(Consistent)	在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构(如B树索引或双向链表)也都必须是正确的。
隔离性(Isolation)	数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
持久性(Durabel)	事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

并发事务带来的问题
更新丢失(Lost Update)	当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题–最后的更新覆盖了由其他事务所做的更新。
脏读(Dirty Reads)	事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。
不可重读(Non-Repeatable Reads)	事务A读取到了事务B已经提交的修改数据，不符合隔离性
幻读(Phantom Reads)	事务A读取到了事务B提交的新增数据，不符合隔离性
事务隔离级别：脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

不同问题的举例分析：

脏读： 一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致的状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此作进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象的叫做“脏读”。

不可重读：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。

幻读： 一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

①、原子性—实现原理undo log

事务日志
redo log(重做日志)	保证事务持久性
undo log(回滚日志)	事务原子性和隔离性实现的基础。

InnoDB实现回滚，靠的是undo log：当事务对数据库进行修改时，InnoDB会生成对应的undo log；如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

undo log属于逻辑日志，它记录的是sql执行相关的信息。当发生回滚时，InnoDB会根据undo log的内容做与之前相反的工作：对于每个insert，回滚时会执行delete；对于每个delete，回滚时会执行insert；对于每个update，回滚时会执行一个相反的update，把数据改回去。

以update操作为例：当事务执行update时，其生成的undo log中会包含被修改行的主键(以便知道修改了哪些行)、修改了哪些列、这些列在修改前后的值等信息，回滚时便可以使用这些信息将数据还原到update之前的状态。

②、持久性—实现原理redo log

redo log和undo log都属于InnoDB的事务日志。下面先聊一下redo log存在的背景。

InnoDB作为MySQL的存储引擎，数据是存放在磁盘中的，但如果每次读写数据都需要磁盘IO，效率会很低。为此，InnoDB提供了缓存(Buffer Pool)，Buffer Pool中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲：当从数据库读取数据时，会首先从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘读取后放入Buffer Pool；当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中（这一过程称为刷脏）。

Buffer Pool的使用大大提高了读写数据的效率，但是也带了新的问题：如果MySQL宕机，而此时Buffer Pool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。

于是，redo log被引入来解决这个问题：当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作；当事务提交时，会调用fsync接口对redo log进行刷盘。如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。

既然redo log也需要在事务提交时将日志写入磁盘，为什么它比直接将Buffer Pool中修改的数据写入磁盘(即刷脏)要快呢？主要有以下两方面的原因：

（1）刷脏是随机IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序IO。

（2）刷脏是以数据页（Page）为单位的，MySQL默认页大小是16KB，一个Page上一个小修改都要整页写入；而redo log中只包含真正需要写入的部分，无效IO大大减少。

	redo log	binlog
作用不同	用于crash recovery的，保证MySQL宕机也不会影响持久性；	用于point-in-time recovery的，保证服务器可以基于时间点恢复数据，此外binlog还用于主从复制。
层次不同	是InnoDB存储引擎实现的	是MySQL的服务器层实现的，同时支持InnoDB和其他存储引擎。
内容不同	是物理日志，内容基于磁盘的Page；	binlog的内容是二进制的，根据binlog_format参数的不同，可能基于sql语句、基于数据本身或者二者的混合。
写入时机不同	redo log的写入时机相对多元；	binlog在事务提交时写入；

③、隔离性—锁机制和MVCC

与原子性、持久性侧重于研究事务本身不同，隔离性研究的是不同事务之间的相互影响。

隔离性是指，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。严格的隔离性，对应了事务隔离级别中的Serializable (可串行化)，但实际应用中出于性能方面的考虑很少会使用可串行化。

隔离性追求的是并发情形下事务之间互不干扰。简单起见，我们仅考虑最简单的读操作和写操作(暂时不考虑带锁读等特殊操作)，那么隔离性的探讨，主要可以分为两个方面：

(一个事务)写操作对(另一个事务)写操作的影响：锁机制保证隔离性

(一个事务)写操作对(另一个事务)读操作的影响：MVCC保证隔离性

④、一致性—一致性是事务追求的最终目标：前面提到的原子性、持久性和隔离性，都是为了保证数据库状态的一致性

一致性是指事务执行结束后，数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。

数据库的完整性约束包括但不限于：实体完整性（如行的主键存在且唯一）、列完整性（如字段的类型、大小、长度要符合要求）、外键约束、用户自定义完整性（如转账前后，两个账户余额的和应该不变）。

4、MVCC机制详解

最大的优点是读不加锁，因此读写不冲突，并发性能好。InnoDB实现MVCC，多个版本的数据可以共存，主要是依靠数据的隐藏列(也可以称之为标记位)和undo log。其中数据的隐藏列包括了该行数据的版本号、删除时间、指向undo log的指针等等；当读取数据时，MySQL可以通过隐藏列判断是否需要回滚并找到回滚需要的undo log，从而实现MVCC；

详解：

1、对于事务id为12的操作，先查询select * from account(创建了查询快照，记录执行sql这一刻最大的已提交事务id(快照点已提交最大事务id))，对于事务id为13的操作，先删除id=1的记录，然后更新id=2的记录，再提交对于删除操作，mysql底层会记录好被删除的数据行的删除事务id，对于更新操作mysql底层会新增一行相同数据并记录好对应的创建事务id

2、在id为12的事务里执行查询操作mysql底层会带上过滤条件，创建事务id <=max(当前事务id(12)，快照点已提交最大事务id），删2除事务id> max(当前事务id(12)，快照点已提交最大事务id）

注意：begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作InnoDB 表的语句，事务才真正启动，才会向mysql申请事务id，mysql内部是严格按照事务的启动顺序来分配事务id的

Mysql默认级别是repeatable-read，有办法解决幻读问题吗？

间隙锁在某些情况下可以解决幻读问题：要避免幻读可以用间隙锁在

Session_1下面执行update account set name = ‘zhuge’ where id > 10 and id <=20；则其他Session没法在这个范围所包含的间隙里插入或修改任何数据