[数据库] 数据库笔记 一

一、MySQL体系结构

1、MySQL体系结构

　　

　　从概念上来说，数据库是文件的集合，数据库实例是程序，应用程序需要通过数据库实例才能和数据库打交道，也就是说用户对数据库数据的任何操作，包括数据库定义，数据查询、数据维护等都是在数据库实例下进行的。简单来说，数据库是由一个一个文件组成的，要对这些文件执行诸如SELECT、INSERT、DELETE等数据库操作，不能通过简单的操作文件来更改数据库内容，而是需要通过数据库实例完成对数据库的操作。

　　（1）第一层：处理客户端连接、授权认证等。

　　（2）第二层：服务器层（Server层），负责查询语句的解析、优化、缓存以及内置函数的实现、存储过程等。

　　（3）第三层：存储引擎，负责MySQL中数据的存储和提取。MySQL中服务器层不管理事务，事务是由存储引擎实现的。MySQL支持事务的存储引擎有InnoDB、NDB Cluster等，其中InnoDB的使用最为广泛；其他存储引擎不支持事务，如MyISAM、Memory等。

　　注意：存储引擎的基于表的，而不是基于数据库的。

　　SQL执行流程如下图：

　　　　

2、MySQL存储引擎

　　（1）Innodb引擎

　　　　Innodb存储引擎将数据放在一个逻辑的表空间中。Innodb通过使用多版本并发控制（MVCC）来获得高并发性，并实现了四种隔离级别，默认为RepeatRead（可重复读），同时使用next-key（临建锁）避免了幻读现象的产生。

　　　　对于表中的数据的存储，Innodb存储引擎采用了聚集（clustered）的方式，因此每个表的存储都是按照主键的顺序存放的，如果在表定义时没有显示指定主键，innodb存储引擎会为每一行生成一个6字节的RowID，并以此作为主键。

　　（2）MyISAM引擎

　　　　MyISAM不支持事务和行锁，但是支持表锁和全文索引。MyISAM的缓冲池中只缓存索引文件，而不缓冲数据文件。MyISAM存储引擎表由MYD和MYI组成，MYD用来存放数据文件，MYI用来存放索引文件。

　　（3）Memory引擎

　　　　Memory引擎将数据都存放到内存中，如果数据库重启或者发送崩溃，表中的数据都会消失。Memory引擎表非常适合用于存储临时数据的临时表，Memory引擎默认使用哈希索引，而不是B+tree索引。

　　　　MySQL使用Memory存储引擎作为临时表存放查询的中间结果集，因此如果中间结果集大于Memory存储引擎表的容量，或者中间结果包含TEXT或BLOB列类型字段，这是MySQL会把其转换到MyISAM存储引擎表而存放到磁盘，由于MyISAM缓存中不缓存数据，因此查询性能比较低。

二、事务的特性ACID

　　（1）原子性（Atomicity)：原子性是指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做；
　　　　如果事务中一个sql语句执行失败，则已执行的语句也必须回滚，数据库退回到事务前的状态。实现原子性的关键，是当事务回滚时能够撤销所有已经成功执行的sql语句。
　　　　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之前的状态。
　　（2）持久性（Durability）：持久性是指事务一旦提交，它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
　　　　当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作；

　　　　Redo Log采用的是WAL（Write-ahead logging，预写式日志），所有修改都先写入Redo Log日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。
　　　　当事务提交时，会调用fsync接口对Redo Log进行刷盘，将日志保存到日志文件里。如果MySQL宕机，重启时可以读取redo log文件中的数据，对数据库进行恢复。
　　　　1.redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。
　　　　2.undo用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录。
　　（3）隔离性（Isolation）隔离性是指，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
　　　　一个事务写操作对另一个事务写操作的影响：锁机制保证隔离性
　　　　一个事务写操作对另一个事务锁读操作的影响（即select ... for update查询）：锁机制保证隔离性
　　　　一个事务写操作对另一个事务读操作的影响（即普通select查询）：MVCC保证隔离性

　　　　InnoDB实现了四种不同事务的隔离级别：读未提交(Read Uncommitted)，读提交(Read Committed, RC)，可重复读(Repeated Read, RR)，串行化(Serializable)
　　　　可重复读是InnoDB默认的隔离级别，在RR下：
　　　　（1）普通的select使用快照读(snapshot read)，底层使用MVCC来实现，MVCC全称Multi-Version Concurrency Control，即多版本的并发控制协议。在同一时刻，不同的事务读取到的数据可能是不同的(即多版本)。MVCC最大的优点是读不加锁，因此读写不冲突，并发性能好。
　　　　InnoDB实现MVCC，多个版本的数据可以共存，主要是依靠数据的隐藏列(也可以称之为标记位)和undo log。其中数据的隐藏列包括了该行数据的版本号、删除时间、指向undo log的指针等等；当读取数据时，MySQL可以通过隐藏列判断是否需要回滚并找到回滚需要的undo log，从而实现MVCC。

　　（4）一致性（Consistency）：数据库总是从一个一致性的状态转到另一个一致性的状态。

三、锁与MVCC

（1）行锁与表锁

　　锁机制的基本原理可以概括为：事务在修改数据之前，需要先获得相应的锁；获得锁之后，事务便可以修改数据；该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。

　　按照粒度，锁可以分为表锁、行锁以及其他位于二者之间的锁。

　　表锁在操作数据时会锁定整张表，并发性能较差；行锁则只锁定需要操作的数据，并发性能好。但是由于加锁本身需要消耗资源(获得锁、检查锁、释放锁等都需要消耗资源)，因此在锁定数据较多情况下使用表锁可以节省大量资源。

　　MySQL中不同的存储引擎支持的锁是不一样的，例如MyIsam只支持表锁，而InnoDB同时支持表锁和行锁，且出于性能考虑，绝大多数情况下使用的都是行锁。

（2）MVCC

　　MySQL的Innodb采用的是行锁，而且采用了多版本并发控制MVCC来提高读操作的性能。

　　什么是多版本并发控制呢 ？其实就是在每一行记录的后面增加两个隐藏列，记录创建版本号和删除版本号，而每一个事务在启动的时候，都有一个唯一的递增的版本号。 

　　1、在插入操作时 ： 记录的创建版本号就是事务版本号。 

　　　　比如我插入一条记录, 事务id 假设是1 ，那么记录如下：也就是说，创建版本号就是事务版本号。　

　　　　

　　2、在更新操作的时候，采用的是先标记旧的那行记录为已删除，并且删除版本号是事务版本号，然后插入一行新的记录的方式。 

　　　　比如，针对上面那行记录，事务Id为2 要把name字段更新

　　　　update table set name= 'new_value' where id=1;

　　　　

　　3、删除操作的时候，就把事务版本号作为删除版本号。比如 

　　　　delete from table where id=1; 

  　　　

　　4、查询操作： 

　　　　从上面的描述可以看到，在查询时要符合以下两个条件的记录才能被事务查询出来： 

　　　　1) 删除版本号 大于 当前事务版本号，就是说删除操作是在当前事务启动之后做的。 

　　　　2) 创建版本号小于或者等于当前事务版本号 ，就是说记录创建是在事务中（等于的情况）或者事务启动之前。

　　　　这样就保证了各个事务互不影响。从这里也可以体会到一种提高系统性能的思路，就是： 通过版本号来减少锁的争用。

　　　　另外，只有read-committed和 repeatable-read 两种事务隔离级别才能使用MVCC，read-uncommited由于是读到未提交的，所以不存在版本的问题，而serializable 则会对所有读取的行加锁。 

四、并发操作下可能会出现的问题

　　（1）脏读：当前事务(A)中可以读到其他事务(B)未提交的数据（脏数据），这种现象是脏读

　　　　

　　（2）不可重复读：在事务A中先后两次读取同一个数据，两次读取的结果不一样，这种现象称为不可重复读。脏读与不可重复读的区别在于：前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据。举例如下：

　　　　

　　（3）幻读：在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了。举例如下：

　　　　

五、事务隔离级别

　　SQL标准中定义了四种隔离级别，并规定了每种隔离级别下上述几个问题是否存在。一般来说，隔离级别越低，系统开销越低，可支持的并发越高，但隔离性也越差。隔离级别与读问题的关系如下：

　　

　　在实际应用中，读未提交在并发时会导致很多问题，而性能相对于其他隔离级别提高却很有限，因此使用较少。可串行化强制事务串行，并发效率很低，只有当对数据一致性要求极高且可以接受没有并发时使用，因此使用也较少。因此在大多数数据库系统中，默认的隔离级别是读已提交(如Oracle)或可重复读（后文简称RR）。

　　InnoDB默认的隔离级别是RR可重复读，并且InnoDB通过临建锁实现的RR避免了幻读问题。

六、Innodb解决方法　　

（1）脏读

　　

　　当事务A在T3时间节点读取zhangsan的余额时，会发现数据已被其他事务修改，且状态为未提交。此时事务A读取最新数据后，根据数据的undo log执行回滚操作，得到事务B修改前的数据，从而避免了脏读。

（2）不可重复读

　　

　　当事务A在T2节点第一次读取数据时，会记录该数据的版本号（数据的版本号是以row为单位记录的），假设版本号为1；当事务B提交时，该行记录的版本号增加，假设版本号为2；当事务A在T5再一次读取数据时，发现数据的版本号（2）大于第一次读取时记录的版本号（1），因此会根据undo log执行回滚操作，得到版本号为1时的数据，从而实现了可重复读。

（3）幻读

　　InnoDB实现的RR通过next-key lock临建锁机制避免了幻读现象。

　　加锁的select(select ... in share mode / select ... for update), update, delete等语句，它们的锁，依赖于它们是否在唯一索引(unique index)上使用了唯一的查询条件(unique search condition)，或者范围查询条件(range-type search condition)：
　　1、在唯一索引上使用唯一的查询条件，会使用记录锁(record lock)，而不会封锁记录之间的间隔，即不会使用间隙锁(gap lock)与临键锁(next-key lock)
　　2、范围查询条件，会使用间隙锁与临键锁，锁住索引记录之间的范围，避免范围间插入记录，以避免产生幻影行记录，以及避免不可重复的读

　　next-key lock是行锁的一种，实现相当于record lock(记录锁) + gap lock(间隙锁)；其特点是不仅会锁住记录本身(record lock的功能)，还会锁定一个范围(gap lock的功能)。当然，这里我们讨论的是不加锁读：此时的next-key lock并不是真的加锁，只是为读取的数据增加了标记（标记内容包括数据的版本号等）；准确起见姑且称之为类next-key lock机制。还是以前面的例子来说明：

　　

　　当事务A在T2节点第一次读取0<id<5数据时，标记的不只是id=1的数据，而是将范围(0,5)进行了标记，这样当T5时刻再次读取0<id<5数据时，便可以发现id=2的数据比之前标记的版本号更高，此时再结合undo log执行回滚操作，避免了幻读。

　　可能出现的问题：

　　概括来说，InnoDB实现的RR，通过锁机制、数据的隐藏列、undo log和类next-key lock，实现了一定程度的隔离性，可以满足大多数场景的需要。不过需要说明的是，RR虽然避免了幻读问题，但是毕竟不是Serializable，不能保证完全的隔离，下面是一个例子，大家可以自己验证一下。

　　

七、MySQL如何查询某个字段长度最大的记录

　　首先查询字段的最大长度值

　　SELECT max(length(message)) FROM apple;

　　假设查询结果为60220
　　接着查询具体的记录

　　SELECT * FROM apple where length(message)=60220;

八、InnoDB共有七种类型的锁：

1、共享/排它锁(Shared and Exclusive Locks) 

　　共享锁（Share Locks，记为S锁），读取数据时加S锁，也称读锁：select ... lock in share mode加共享锁。

　　排他锁（eXclusive Locks，记为X锁），修改数据时加X锁，select ... for update或者增、删、改操作加排他锁
　　其兼容互斥表如下：

　　　　S　　      X
　　S   兼容 　　互斥
　　X   互斥 　　互斥

　　即：

　　（1）多个事务可以拿到一把S锁，读读可以并行；

　　（2）而只有一个事务可以拿到X锁，写写/读写必须互斥；

　　共享锁与排他锁的玩法是：

　　　　共享锁之间不互斥，简记为：读读可以并行

　　　　排他锁与任何锁互斥，简记为：写读，写写不可以并行

　　可以看到，一旦写数据的任务没有完成，数据是不能被其他任务读取的，这对并发度有较大的影响。写事务没有提交，读相关数据的select也会被阻塞，由于数据加排他锁后，读相关数据也会被阻塞，因此并发性很低，由此引出了MVCC多版本并发控制，MVCC就是通过“读取旧版本数据”来降低并发事务的锁冲突，提高任务的并发度。

　　只有Innodb有行级共享锁、排他锁，MyISAM只有表级共享锁、排他锁。

2、意向锁(Intention Locks)

　　意向锁是指，未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向。　　

　　意向锁有这样一些特点：

　　　　(1)首先，意向锁，是一个表级别的锁(table-level locking)

　　　　(2)意向锁分为：

　　　　　　意向共享锁(intention shared lock, IS)，它预示着，事务有意向对表中的某些行加共享S锁

　　　　　　意向排它锁(intention exclusive lock, IX)，它预示着，事务有意向对表中的某些行加排它X锁

　　　　举个例子：

　　　　　　select ... lock in share mode，要设置IS锁；

　　　　　　select ... for update，要设置IX锁；

　　　　(3)意向锁协议(intention locking protocol)并不复杂：

　　　　　　事务要获得某些行的S锁，必须先获得表的IS锁

　　　　　　事务要获得某些行的X锁，必须先获得表的IX锁

　　　　(4)由于意向锁仅仅表明意向，它其实是比较弱的锁，意向锁之间并不相互互斥，而是可以并行，其兼容互斥表如下：

　　　　　　　　IS 　　　IX

　　　　　　IS   兼容 　　兼容

　　　　　　IX   兼容 　　兼容

　　　　(5)既然意向锁之间都相互兼容，那其意义在哪里呢？它会与表级的共享锁/排它锁互斥，其兼容互斥表如下：

　　　　　　　　S 　　　X
　　　　　　IS 兼容 　　互斥

　　　　　　IX 互斥 　　互斥

　　意向锁的作用就是：

　　　　当一个事务在需要获取资源的锁定时，如果该资源已经被排他锁占用，则数据库会自动给该事务申请一个该表的意向锁。

　　　　如果自己需要一个共享锁定，就申请一个意向共享锁。如果需要的是某行（或者某些行）的排他锁定，则申请一个意向排他锁。

　　　　注：意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。

　　意向锁的作用：

　　例如下面的例子：

　　　　（1）事务A锁住了表中的一行，让这一行只能读，不能写，即行级S锁。

　　　　（2）之后，事务B申请整个表的写锁，及表级X锁。

　　如果没有意向锁，如果事务B申请成功，那么理论上它就能修改表中的任意一行，这与A持有的行锁是冲突的。

　　数据库需要避免这种冲突，就是说要让B的申请被阻塞，直到A释放了行锁。

　　数据库要怎么判断这个冲突呢？

　　　　step1：判断表是否已被其他事务用表锁锁表

　　　　step2：判断表中的每一行是否已被行锁锁住。

　　注意step2中通过遍历查询，这样的判断方法效率实在不高，因为需要遍历整个表。于是就有了意向锁。在意向锁存在的情况下，事务A必须先申请表的意向共享锁IS，成功后再申请一行的行锁X。

　　在意向锁存在的情况下，上面的判断可以改成

　　　　step1：不变

　　　　step2：发现表上有意向共享锁，说明表中有些行被共享行锁锁住了，因此，事务B申请表的写锁会被阻塞。

　　最终结论：

　　　　（1）申请意向锁的动作是数据库完成的，就是说，事务A申请一行的行锁的时候，数据库会自动先开始申请表的意向锁，不需要我们程序员使用代码来申请。

　　　　（2）IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。行级别的X和S按照普通的共享、排他规则即可。

　　所以之前的示例中第2步不会冲突，只要写操作不是同一行，就不会发生冲突。

3、插入意向锁(Insert Intention Locks)

　　参考：InnoDB并发插入，居然使用意向锁？ https://mp.weixin.qq.com/s?__biz=MjM5ODYxMDA5OQ==&mid=2651961461&idx=1&sn=b73293c71d8718256e162be6240797ef&chksm=bd2d0da98a5a84bfe23f0327694dbda2f96677aa91fcfc1c8a5b96c8a6701bccf2995725899a&scene=21#wechat_redirect

　　插入意向锁，是间隙锁(Gap Locks)的一种（所以，也是实施在索引上的），它是专门针对insert操作的。多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。

　　例如：在MySQL，InnoDB，RR下：

　　　　　　t(id unique PK, name);

　　数据表中有数据：

　　　　10, shenjian

　　　　20, zhangsan

　　　　30, lisi

　　事务A先执行，在10与20两条记录中插入了一行，还未提交：

　　　　insert into t values(11, xxx);

　　事务B后执行，也在10与20两条记录中插入了一行：

　　　　insert into t values(12, ooo);

　　(1)会使用什么锁？

　　(2)事务B会不会被阻塞呢？

　　　　回答：虽然事务隔离级别是RR，虽然是同一个索引，虽然是同一个区间，但插入的记录并不冲突，故这里：

　　　　使用的是插入意向锁

　　　　并不会阻塞事务B

4、记录锁(Record Locks)

　　记录锁，它封锁索引记录，例如：

　　　　select * from t where id=1 for update;

　　它会在id=1的索引记录上加X锁，以阻止其他事务插入，更新，删除id=1的这一行。

　　需要说明的是：

　　　　select * from t where id=1;

　　则是快照读(SnapShot Read)，它并不加锁

5、间隙锁(Gap Locks)

　　间隙锁，它封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。

　　依然是上面的例子，InnoDB，RR：

　　　　t(id PK, name KEY, sex, flag);

　　表中有四条记录：

　　　　1, shenjian, m, A

　　　　3, zhangsan, m, A

　　　　5, lisi, m, A

　　　　9, wangwu, f, B

　　这个SQL语句

　　　　select * from t where id between 8 and 15 for update;

　　会封锁区间，以阻止其他事务id=10的记录插入。

　　画外音：

　　为什么要阻止id=10的记录插入？

　　如果能够插入成功，头一个事务执行相同的SQL语句，会发现结果集多出了一条记录，即幻影数据。

　　间隙锁的主要目的，就是为了防止其他事务在间隔中插入数据，以导致幻读。

　　如果把事务的隔离级别降级为读提交(Read Committed, RC)，间隙锁则会自动失效。

6、临键锁(Next-key Locks)

　　临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。更具体的，临键锁会封锁索引记录本身，以及索引记录之前的区间。

　　如果一个会话占有了索引记录R的共享/排他锁，其他会话不能立刻在R之前的区间插入新的索引记录。

　　依然是上面的例子，InnoDB，RR：

　　　　t(id PK, name KEY, sex, flag);

　　表中有四条记录：

　　　　1, shenjian, m, A

　　　　3, zhangsan, m, A

　　　　5, lisi, m, A

　　　　9, wangwu, f, B

　　PK上潜在的临键锁为：

　　　　(-infinity, 1]

　　　　(1, 3]

　　　　(3, 5]

　　　　(5, 9]

　　　　(9, +infinity]

　　临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

　　InnoDB，select为啥会阻塞insert？ https://mp.weixin.qq.com/s?__biz=MjM5ODYxMDA5OQ==&mid=2651961471&idx=1&sn=da257b4f77ac464d5119b915b409ba9c&chksm=bd2d0da38a5a84b5fc1417667fe123f2fbd2d7610b89ace8e97e3b9f28b794ad147c1290ceea&scene=21#wechat_redirect

7、自增锁(Auto-inc Locks)

　　插入InnoDB自增列，居然是表锁？ https://mp.weixin.qq.com/s?__biz=MjM5ODYxMDA5OQ==&mid=2651961455&idx=1&sn=4c26a836cff889ff749a1756df010e0e&chksm=bd2d0db38a5a84a53db91e97c7be6295185abffa5d7d1e88fd6b8e1abb3716ee9748b88858e2&scene=21#wechat_redirect

　　自增锁是一种特殊的表级别锁（table-level lock），专门针对事务插入AUTO_INCREMENT类型的列。

　　最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

　　例如：t(id AUTO_INCREMENT, name);，其中id为自增

九、加锁分析

　　参考：别废话，各种SQL到底加了什么锁？ https://mp.weixin.qq.com/s?__biz=MjM5ODYxMDA5OQ==&mid=2651961508&idx=1&sn=9f31a95e5b8ec16fa0edc7de6087d2a1&chksm=bd2d0d788a5a846e3bf16d300fb9723047bd109fd22682c39bdf7ed4e77b167e333460f6987c&scene=21#wechat_redirect

1、普通select：select ... from t;

　　（1）在读未提交(Read Uncommitted)，读提交(Read Committed, RC)，可重复读(Repeated Read, RR)这三种事务隔离级别下，普通select使用快照读(snpashot read)，不加锁，并发非常高；

　　（2）在串行化(Serializable)这种事务的隔离级别下，普通select会升级为select ... in share mode;

2、加锁select

　　加锁select主要是指：

　　　　select ... for update

　　　　select ... lock in share mode

　　（1）如果，在唯一索引(unique index)上使用唯一的查询条件(unique search condition)，会使用记录锁(record lock)，而不会封锁记录之间的间隔，即不会使用间隙锁(gap lock)与临键锁(next-key lock)；

　　　　例如：假设有InnoDB表：

　　　　　　t(id PK, name);

　　　　表中有三条记录：

　　　　　　1, shenjian

　　　　　　2, zhangsan

　　　　　　3, lisi

　　　　SQL语句：

　　　　　　select * from t where id=1 for update;

　　　　　只会封锁记录，而不会封锁区间。

　　（2）其他的查询条件和索引条件，InnoDB会封锁被扫描的索引范围，并使用间隙锁与临键锁，避免索引范围区间插入记录；

3、update与delete

　　（1）和加锁select类似，如果在唯一索引上使用唯一的查询条件来update/delete，例如：

　　　　　　update t set name=xxx where id=1; 也只加记录锁；

　　（2）否则，符合查询条件的索引记录之前，都会加排他临键锁(exclusive next-key lock)，来封锁索引记录与之前的区间；

　　（3）尤其需要特殊说明的是，如果update的是聚集索引(clustered index)记录，则对应的普通索引(secondary index)记录也会被隐式加锁，这是由InnoDB索引的实现机制决定的：普通索引存储PK的值，检索普通索引本质上要二次扫描聚集索引。

4、insert

　　同样是写操作，insert和update与delete不同，它会用排它锁封锁被插入的索引记录，而不会封锁记录之前的范围。

　　同时，会在插入区间加插入意向锁(insert intention lock)，但这个并不会真正封锁区间，也不会阻止相同区间的不同KEY插入。

5、Mysql锁相关总结

　　MyISAM只支持表锁，InnoDB可以支持行锁。因此MyISAM：执行读写SQL语句时，会对表加锁，所以数据量大，并发量高时，性能会急剧下降。而InnoDB：细粒度行锁，在数据量大，并发量高时，性能比较优异。

　　常见坑：

　　　　InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。因此，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

　　例如：

　　　　表：t_user(uid PK, uname, age, sex) 存储引擎为：innodb;其中uid为主键，无其他索引：

　　　　　　update t_user set age=10 where uid = 1;

　　　　因为innodb会根据主键建立primary key，因此根据uid = 1查询命中索引，此时会会uid=1这条记录加行锁。

　　　　　　update t_user set age=10 where uid = 2;

　　　　如果表中没有uid = 2的记录，此时未命中索引，退化为表锁。

　　　　　　update t_user set age=10 where name='shenjian';

　　　　无索引，表锁。

　　启示：InnoDB务必建好索引，否则锁粒度较大，会影响并发。

十、MySQL加锁处理分析

　　MySQL 加锁处理分析*** http://hedengcheng.com/?p=771#_Toc374698322

　　　　　　　　　　　　　https://mp.weixin.qq.com/s/mGbMAcMsekufne2dhwLBug

　　快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外，下面会分析)

　　select * from table where ?;

　　当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

　　select * from table where ? lock in share mode;//共享锁
　　select * from table where ? for update;//排他锁
　　insert into table values (…);//排他锁
　　update table set ? where ?;
　　delete from table where ?;

　　所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。

　　为什么将 插入/更新/删除 操作，都归为当前读？可以看看下面这个 更新 操作，在数据库中的执行流程：

　　

　　从图中，可以看到，一个Update操作的具体流程:

　　（1）当Update SQL被发给MySQL后，MySQL Server会根据where条件，读取第一条满足条件的记录，然后InnoDB引擎会将第一条记录返回，并加锁 (current read)。

　　（2）待MySQL Server收到这条加锁的记录之后，会再发起一个Update请求，更新这条记录。

　　（3）一条记录操作完成，再读取下一条记录，直至没有满足条件的记录为止。

　　因此，Update操作内部，就包含了一个当前读。同理，Delete操作也一样。Insert操作会稍微有些不同，简单来说，就是Insert操作可能会触发Unique Key的冲突检查，也会进行一个当前读。

　　注：根据上图的交互，针对一条当前读的SQL语句，InnoDB与MySQL Server的交互，是一条一条进行的，因此，加锁也是一条一条进行的。先对一条满足条件的记录加锁，返回给MySQL Server，做一些DML操作；然后在读取下一条加锁，直至读取完毕。

　　在RC，RR隔离级别下，SQL1：

　　select * from t1 where id = 10;

　　select操作均不加锁，采用的是快照读

下面针对delete操作，分析其加锁策略：

1、delete from t1 where id = 10;这样一条sql如何加锁？

　　如果事务隔离级别为RC：Read Committed：

（1）如果id是主键，并且事务隔离级别为RC：Read Committed

　　id是主键时，此SQL只需要在id=10这条记录上加X锁即可。

　　

（2）id不是主键，而是一个Unique索引（唯一索引），并且事务隔离级别为RC：Read Committed

　　

　　id是unique索引，而主键是name列。此时，加锁的情况由于组合一有所不同。由于id是unique索引，因此delete语句会选择走id列的索引进行where条件的过滤，在找到id=10的记录后，首先会将unique索引上的id=10索引记录加上X锁，同时，会根据读取到的主键name列，回主键索引(聚簇索引)，然后将聚簇索引上的name = ‘d’ 对应的主键索引项加X锁。

　　为什么聚簇索引上的记录也要加锁？

　　试想一下，如果并发的一个SQL，是通过主键索引来更新：update t1 set id = 100 where name = ‘d’; 此时，如果delete语句没有将主键索引上的记录加锁，那么并发的update就会感知不到delete语句的存在，违背了同一记录上的更新/删除需要串行执行的约束。

　　结论：若id列是unique列，其上有unique索引。那么SQL需要加两个X锁，一个对应于id unique索引上的id = 10的记录，另一把锁对应于聚簇索引上的[name=’d’,id=10]的记录。

（3）id不是主键，只有一个普通索引，并且事务隔离级别为RC：Read Committed

　　

　　首先，id列索引上，满足id = 10查询条件的记录，均已加锁。同时，这些记录对应的主键索引上的记录也都加上了锁。与（2）唯一的区别在于，（2）最多只有一个满足等值查询的记录，而（3）会将所有满足查询条件的记录都加锁。

　　结论：若id列上有非唯一索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。

（4）id不是主键，只是一个普通字段，没有索引，并且事务隔离级别为RC：Read Committed

　　

　　由于id列上没有索引，因此只能走聚簇索引，进行全部扫描。从图中可以看到，满足删除条件的记录有两条，但是，聚簇索引上所有的记录，都被加上了X锁。无论记录是否满足条件，全部被加上X锁。既不是加表锁，也不是在满足条件的记录上加行锁。

 　　为什么不是只在满足条件的记录上加锁呢？

　　这是由于MySQL的实现决定的。如果一个条件无法通过索引快速过滤，那么存储引擎层面就会将所有记录加锁后返回，然后由MySQL Server层进行过滤。因此也就把所有的记录，都锁上了。

　　注：在实际的实现中，MySQL有一些改进，在MySQL Server过滤条件，发现不满足后，会调用unlock_row方法，把不满足条件的记录放锁 (违背了2PL的约束)。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。

　　结论：若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束。

如果务隔离级别为RR：Repeatable Read：

（5）如果id是主键，并且事务隔离级别为RR：Repeatable Read　

　　id是主键时，此SQL只需要在id=10这条记录上加X锁即可。与（1）相同。

（6）id不是主键，而是一个Unique索引，并且事务隔离级别为RR：Repeatable Read
　　若id列是unique列，其上有unique索引。那么SQL需要加两个X锁，一个对应于id unique索引上的id = 10的记录，另一把锁对应于聚簇索引上的[name=’d’,id=10]的记录。与（2）相同。

（7）id不是主键，只有一个普通索引，并且事务隔离级别为RR：Repeatable Read
　　Repeatable Read隔离级别下，id列上有一个非唯一索引，对应SQL：delete from t1 where id = 10;

　　

　　这个多出来的GAP锁，就是RR隔离级别，相对于RC隔离级别，不会出现幻读的关键。确实，GAP锁锁住的位置，也不是记录本身，而是两条记录之间的GAP。所谓幻读，就是同一个事务，连续做两次当前读 (例如：select * from t1 where id = 10 for update;)，那么这两次当前读返回的是完全相同的记录 (记录数量一致，记录本身也一致)，第二次的当前读，不会比第一次返回更多的记录 (幻象)。　　

　　首先，通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁，然后返回；

　　然后读取下一条，重复进行。直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。

　　如果查询的数据不存在，则只加GAP间隙锁，因此其他事务可以执行更新、删除操作，但是无法插入数据。

（8）id不是主键，只是一个普通字段，没有索引，并且事务隔离级别为RR：Repeatable Read

　　

 　　若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有GAP，杜绝所有的并发 更新/删除/插入 操作

（9）Serializable

　　针对前面提到的简单的SQL，最后一个情况：Serializable隔离级别。对于SQL2：delete from t1 where id = 10; 来说，Serializable隔离级别与Repeatable Read隔离级别完全一致，因此不做介绍。

　　Serializable隔离级别，影响的是SQL1：select * from t1 where id = 10; 这条SQL，在RC，RR隔离级别下，都是快照读，不加锁。但是在Serializable隔离级别，SQL1会加读锁，也就是说快照读不复存在，MVCC并发控制降级为Lock-Based CC。

　　结论：在MySQL/InnoDB中，所谓的读不加锁，并不适用于所有的情况，而是隔离级别相关的。Serializable隔离级别，读不加锁就不再成立，所有的读操作，都是当前读。

2、复杂的SQL加锁原理

　　再来看一个稍微复杂点的SQL，用于说明MySQL加锁的另外一个逻辑。SQL用例如下：

　　

　　如图中的SQL，会加什么锁？假定在Repeatable Read隔离级别下 (Read Committed隔离级别下的加锁情况，留给读者分析。)，同时，假设SQL走的是idx_t1_pu索引。

　　（1）那就是一个SQL中的where条件如何拆分？

　　　　具体的介绍，参考：http://hedengcheng.com/?p=577 。在这里，我直接给出分析后的结果：

　　　　　　Index key：pubtime > 1 and puptime < 20。此条件，用于确定SQL在idx_t1_pu索引上的查询范围。

　　　　　　Index Filter：userid = ‘hdc’ 。此条件，可以在idx_t1_pu索引上进行过滤，但不属于Index Key。

　　　　　　Table Filter：comment is not NULL。此条件，在idx_t1_pu索引上无法过滤，只能在聚簇索引上过滤。

　　在分析出SQL where条件的构成之后，再来看看这条SQL的加锁情况 (RR隔离级别)，如下图所示：

　　

　　从图中可以看出，在Repeatable Read隔离级别下，由Index Key所确定的范围，被加上了GAP锁；Index Filter锁给定的条件 (userid = ‘hdc’）何时过滤，视MySQL的版本而定，在MySQL 5.6版本之前，不支持Index Condition Pushdown(ICP)，因此Index Filter在MySQL Server层过滤，在5.6后支持了Index Condition Pushdown，则在index上过滤。若不支持ICP，不满足Index Filter的记录，也需要加上记录X锁，若支持ICP，则不满足Index Filter的记录，无需加记录X锁 (图中，用红色箭头标出的X锁，是否要加，视是否支持ICP而定)；而Table Filter对应的过滤条件，则在聚簇索引中读取后，在MySQL Server层面过滤，因此聚簇索引上也需要X锁。最后，选取出了一条满足条件的记录[8,hdc,d,5,good]，但是加锁的数量，要远远大于满足条件的记录数量。

　　结论：在Repeatable Read隔离级别下，针对一个复杂的SQL，首先需要提取其where条件。Index Key确定的范围，需要加上GAP锁；Index Filter过滤条件，视MySQL版本是否支持ICP，若支持ICP，则不满足Index Filter的记录，不加X锁，否则需要X锁；Table Filter过滤条件，无论是否满足，都需要加X锁。

十一、死锁原理与分析

　　深入理解MySQL如何加锁，有两个比较重要的作用：

　　　　（1）可以根据MySQL的加锁规则，写出不会发生死锁的SQL；

　　　　（2）可以根据MySQL的加锁规则，定位出线上产生死锁的原因；

　　下面，来看看两个死锁的例子 (一个是两个Session的两条SQL产生死锁；另一个是两个Session的一条SQL，产生死锁)：

　　

　　

　　上面的两个死锁用例。第一个非常好理解，也是最常见的死锁，每个事务执行两条SQL，分别持有了一把锁，然后加另一把锁，产生死锁。

　　第二个用例，虽然每个Session都只有一条语句，仍旧会产生死锁。要分析这个死锁，首先必须用到本文前面提到的MySQL加锁的规则。

　　针对Session 1，从name索引出发，读到的[hdc, 1]，[hdc, 6]均满足条件，不仅会加name索引上的记录X锁，而且会加聚簇索引上的记录X锁，加锁顺序为先[1,hdc,100]，后[6,hdc,10]。而Session 2，从pubtime索引出发，[10,6],[100,1]均满足过滤条件，同样也会加聚簇索引上的记录X锁，加锁顺序为[6,hdc,10]，后[1,hdc,100]。发现没有，跟Session 1的加锁顺序正好相反，如果两个Session恰好都持有了第一把锁，请求加第二把锁，死锁就发生了。

　　结论：死锁的发生与否，并不在于事务中有多少条SQL语句，死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。而使用本文上面提到的，分析MySQL每条SQL语句的加锁规则，分析出每条语句的加锁顺序，然后检查多个并发SQL间是否存在以相反的顺序加锁的情况，就可以分析出各种潜在的死锁情况，也可以分析出线上死锁发生的原因。

十二、在RR事务隔离级别下的加锁

　　MySQL默认的事务隔离级别是 Repeated Read (RR)，假设使用的存储引擎是InnoDB，在这个隔离级别下：

　　（1）读取到数据，都是其他事务已提交的数据；--- 避免了脏读

　　（2）同一个事务中，相同的连续读，得到的结果应该是相同的； --- 避免了不可重复读

　　（3）不会出现insert幻象读； --- 避免了幻读

　　其中：不可重复读是针对同一条记录来说的，事务A两次读取同一条记录是一致的，不受事务B提交的影响

　　　　　幻读是针对记录的行数来说的，事务A的两次范围读，数据的行数是一致的。

　　假设有数据表：

　　　　t(id int PK, name);

　　假设目前的记录是：

　　　　10, shenjian

　　　　20, zhangsan

　　　　30, lisi

　　Case 1：

　　事务A先执行，并且处于未提交状态：

　　　　update t set name=’a’ where id=10; ---此时A会在id=10的记录上加X锁，其他事务的增删改操作都会阻塞

　　事务B后执行：

　　　　update t set name=’b’ where id=10;

　　因为事务A在PK id=10上加了行锁，因此事务B会阻塞。

　　Case 2：

　　事务A先执行，并且处于未提交状态：

　　　　delete from t where id=40;

　　事务A想要删除一条不存在的记录。

　　事务B后执行：

　　　　insert into t values(40, ‘c’);

　　事务B想要插入一条主键不冲突的记录。

　　问题1：事务B是否阻塞？

　　　　会阻塞

　　问题2：如果事务B阻塞，锁如何加在一条不存在的记录上呢？

　　　　间隙锁的出现主要集中在同一个事务中先delete 后 insert的情况下，当我们通过一个参数去删除一条记录的时候，如果参数在数据库中存在，那么这个时候产生的是普通行锁，锁住这个记录，然后删除， 然后释放锁。

　　　　如果这条记录不存在，问题就来了， 数据库会扫描索引，发现这个记录不存在， 这个时候的delete语句获取到的就是一个间隙锁，然后数据库会向左扫描扫到第一个比给定参数小的值， 向右扫描扫描到第一个

比给定参数大的值， 然后以此为界，构建一个区间， 锁住整个区间内的数据。只有RR隔离级别才会有间隙锁

十三、Innodb快照读（普通查询：select ... from t）和加锁读（select ... from t for update）的区别

　　在快照读(Snapshot Read)情况下，MySQL数据库，InnoDB存储引擎，为了提高并发，使用MVCC机制，在并发事务时，通过读取数据行的历史数据版本，不加锁，来提高并发的一种不加锁一致性读(Consistent Nonlocking Read)。

　　在读提交(RC)，可重复读(RR)两个不同的事务的隔离级别下，快照读和加锁读都有什么不同呢？

　　在RC级别下：

　　　　（1）快照读通过MVCC机制保证没有脏读，即在事务A在读取数据时，发现事务B修改了数据但是没有提交，事务A根据undo log获取B修改前的数据。

　　　　（2）加锁读通过加行记录X锁保证其他事务的修改操作会被阻塞。

　　在RR级别下：

　　　　（1）快照读通过MVCC机制保证没有脏读，即在事务A在读取数据时，发现事务B修改了数据但是没有提交，事务A根据undo log获取B修改前的数据。

　　　　（2）快照读通过MVCC机制保证可重复读，即在事务A在读取数据时，会给改行记录增加一个版本号，如果事务B修改了数据并提交会修改该版本号，事务A再一次读取时发现版本号被修改了，事务A根据undo log获取B修改前的数据。

　　　　（3）加锁读通过加行记录X锁保证其他事务的修改操作会被阻塞。

　　事务总能够读取到，自己写入(update /insert /delete)的行记录。

　　RC下，快照读总是能读到最新的行数据快照，当然，必须是已提交事务写入的。

　　RR下，某个事务首次read记录的时间为T，未来不会读取到T时间之后已提交事务写入的记录，以保证连续相同的read读到相同的结果集

　　例如表： t(id PK, name);其中id为主键。

　　表中有三条记录：

　　　　1, shenjian

　　　　2, zhangsan

　　　　3, lisi

　　case 1，两个并发事务A，B执行的时间序列如下（A先于B开始，B先于A结束）：

　　　　A1: start transaction;

　　　　　　B1: start transaction;

　　　　A2: select * from t;

　　　　　　B2: insert into t values (4, wangwu);

　　　　A3: select * from t;

　　　　　　B3: commit;

　　　　A4: select * from t;

　　提问1：假设事务的隔离级别是可重复读RR，事务A中的三次查询，A2, A3, A4分别读到什么结果集？

　　回答：RR下

　　　　（1）A2读到的结果集肯定是{1, 2, 3}，这是事务A的第一个read，假设为时间T；

　　　　（2）A3读到的结果集也是{1, 2, 3}，因为B还没有提交；

　　　　（3）A4读到的结果集还是{1, 2, 3}，因为事务B是在时间T之后提交的，A4得读到和A2一样的记录；

　　提问2：假设事务的隔离级别是读提交RC，A2, A3, A4又分别读到什么结果集呢？

　　回答：RC下

　　　　（1）A2读到的结果集是{1, 2, 3}；

　　　　（2）A3读到的结果集也是{1, 2, 3}，因为B还没有提交；

　　　　（3）A4读到的结果集是{1, 2, 3, 4}，因为事务B已经提交；--即出现幻读

　　case 2，仍然是上面的两个事务，只是A和B开始时间稍有不同（B先于A开始，B先于A结束）：

　　　　　　B1: start transaction;

　　　　A1: start transaction;

　　　　A2: select * from t;

　　　　　　B2: insert into t values (4, wangwu);

　　　　A3: select * from t;

　　　　　　B3: commit;

　　　　A4: select * from t;

　　提问3：假设事务的隔离级别是可重复读RR，事务A中的三次查询，A2, A3, A4分别读到什么结果集？

　　提问4：假设事务的隔离级别是读提交RC，A2, A3, A4的结果集又是什么呢？

　　回答：事务的开始时间不一样，不会影响“快照读”的结果，所以结果集和case 1一样。

　　case 3，仍然是并发的事务A与B（A先于B开始，B先于A结束）：

　　　　A1: start transaction;

　　　　　　B1: start transaction;

　　　　　　B2: insert into t values (4, wangwu);

　　　　　　B3: commit;

　　　　A2: select * from t;

　　提问5：假设事务的隔离级别是可重复读RR，事务A中的A2查询，结果集是什么？

　　提问6：假设事务的隔离级别是读提交RC，A2的结果集又是什么呢？

　　回答：在RR下，A2是事务A的第一个read，假设为时间T，它能读取到T之前提交事务写入的数据行，故结果集为{1, 2, 3, 4}。在RC下，没有疑问，一定是{1, 2, 3, 4}。

 

　　case 4，事务开始的时间再换一下（B先于A开始，B先于A结束）：

　　　　　　B1: start transaction;

　　　　A1: start transaction;

　　　　　　B2: insert into t values (4, wangwu);

　　　　　　B3: commit;

　　　　A2: select * from t;

　　提问7：假设事务的隔离级别是可重复读RR，事务A中的A2查询，结果集是什么？

　　提问8：假设事务的隔离级别是读提交RC，A2的结果集又是什么呢？

　　回答：事务的开始时间不一样，不会影响“快照读”的结果，所以结果集和case 3一样。

十四、MySQL一条select语句执行流程

　　

　　

1  建立连接

　　客户端通过连接器连接到数据库，经过三次握手后与服务端建立TCP连接；包括建立连接、获取用户权限、维持连接、管理连接。

　　连接的一般命令就是我们常用的登陆数据库的命令：

　　　　mysql -u$username -h$host -p$port -P

　　命令执行后，连接器接收到请求之后使用用户密码进行身份验证，若用户名或者密码不对，或者数据库做了登录 ip 限制，都会收到异常信息。若登陆成功，那么就代表连接成功建立。

　　之后连接器会维持当前连接，接下来连接器会查询出该用户的权限，后面所有的操作都会基于该权限，即使操作过程中有其他进程修改了该用户的权限。

　　

　　对于Java应用程序来说，一般会把建立好的连接放入数据库连接池中进行复用，只要这个连接不关闭，就会一直在MySQL服务端保持着。连接完成后，若没有任何操作，连接就处于休眠状态，用命令 show processlist;查看，就是 Sleep 状态的进程：

　　　　

　　注意，这里有个Time，表示这个连接多久没有动静了，上面例子是63906秒没有动静，默认地，如果超过8个小时还没有动静，连接器就会自动断开连接，可以通过wait_timeout参数进行控制。

2  查询缓存

　　https://www.cnblogs.com/Alight/p/3981999.html　　　　

　　在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。

　　如果当前的查询恰好命中了查询缓存，那么在返回查询结果之前MySQL会检查一次用户权限。若权限没有问题，MySQL会跳过所有其他阶段（解析、优化、执行等），直接从缓存中拿到结果并返回给客户端。

　　查询时如果没有命中查询缓存，MYSQL 会判断该查询是否可以被缓存，而且系统中还没有对应的缓存，则会将其结果写入查询缓存。

　　如果查询中有不确定数据，例如CURRENT_DATE()和NOW()函数,那么查询完毕后则不会被缓存。所以，包含不确定数据的查询是肯定不会找到可用缓存的

　　mysql查询缓存的内容为 select 的结果集，在内存中是以 HASH 结构来进行映射。

　　cache 会使用完整的 sql 字符串做 key，并区分大小写，空格等。即两个 sql 必须完全一致才会导致 cache 命中。

　　可以通过下面的SQL来查看缓存命中次数（是个累加值）：

　SHOW STATUS LIKE 'Qcache_hits';

1  查看当前查询缓存相关参数状态：

　　SHOW VARIABLES LIKE '%query_cache%';

　　

2  缓存配置参数解释

　　（1）query_cache_type: 查询缓存类型，是否打开缓存

　　　　a、0(OFF)：关闭 Query Cache 功能，任何情况下都不会使用 Query Cache；

　　　　b、1(ON)：开启 Query Cache 功能，但是当SELECT语句中使用SQL_NO_CACHE提示后，将不使用Query Cache；

　　　　c、2(DEMAND)：开启Query Cache 功能，但是只有当SELECT语句中使用了SQL_CACHE 提示后，才使用Query Cache。

　　备注1:

　　　　如果query_cache_type为on而又不想利用查询缓存中的数据，可以用下面的SQL：　　　　　

　　SELECT SQL_NO_CACHE * FROM my_table WHERE condition;

　　　　如果值为2，要使用缓存的话，需要使用SQL_CACHE开关参数：

　　SELECT SQL_CACHE * FROM my_table WHERE condition;

3  缓存失效

　　　　a) 一旦表数据进行任何一行的修改，基于该表相关 cache 立即全部失效，并且从缓冲区中移出；所以查询缓存适合有大量相同查询的应用，不适合有大量数据更新的应用。

　　　　　在表的结构或数据发生改变时，查询缓存中的数据不再有效。有这些INSERT、UPDATE、 DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE都会导致缓存数据失效。可以使用下面三个SQL来清理查询缓存： 

　　1、FLUSH QUERY CACHE; // 清理查询缓存内存碎片。

　　2、RESET QUERY CACHE; // 从查询缓存中移出所有查询。

　　3、FLUSH TABLES; //关闭所有打开的表，同时该操作将会清空查询缓存中的内容。

　　　　b) 为什么不做聪明一点判断修改的是否 cache 的内容？因为分析 cache 内容太复杂，服务器需要追求最大的性能。

4  缓存淘汰策略

　　既然内存是一块固定大小的，那么存放在内存里的数据就肯定有的会被淘汰。

　　下面是一个lur算法的基本模型：

　　

　　Innodb管理buffer pool的LRU算法是基于链表实现的，具体实现步骤如下：

　　state1：我们要查询p3的数据，由于p3是在缓存中的，那么久直接把p3移动到链表头部，也就是对应图中state2的状态

　　state3：查询px数据，由于我们查询的px数据不是在缓存中，那么就从磁盘中查询出px的数据放入缓存中的链表头部，但是由于内存满了，所以就会把pm的数据从链表尾部淘汰掉，从现象上来看就是最久没有被访问都的数据会被淘汰

上述淘汰策略存在的问题：

　　如果我们对一个冷数据表进行全表扫描，比如说日志表，这些不是正常用户访问的表，那么在buffer pool中就会大量存储这些表的数据，那么就会导致用户正常访问存放的业务数据会被淘汰掉，

从而导致大量数据需要重新读磁盘放入缓存，这样性能就会大大降低。

MySQL的优化：

　　

　　Innodb把整个内存的前5/8记为 young 区域，后3/8记为 old 区域，

　　state1：由于我们访问的p3是在young区域，那么就把p3移动到链表头部，即state2的状态。

　　state3：但是如果我们访问的数据如果是在old区域，比如说我们访问了px，这个时候会做个判断：如果px在内存中存活时间超过1秒，就会把它移动到young区域的链表头部，否则位置不动。

　　　　　　这个1秒是由参数innodb_old_blocks_time控制的，默认值是1000，单位毫秒。

再来看扫描全表的步骤：

　　扫描过程中被访问的数据页会被放在old区域。一个数据页有多条记录会被访问，所以这数据页会被多次访问到，但是由于是顺序扫描，这个数据页第一次被访问和最后一次被访问的时间间隔不会超过一秒，所以就会一直在old区域。

　　再继续扫描后面的数据页，之前的这个数据页也不会被访问到，因此就会一直在old区域，也就很快就会被淘汰掉了。

　　可以看到这个策略的最大收益，就是在扫描的过程中，虽然也用到了buffer pool，但是不会对young区域造成影响，也就保证了buffer pool响应业务的内存命中率。

3  解析

　　分析器拿到sql之后会尝试对sql语句进行词法分析和语法分析，校验语法的正确性，通过之后继续往下执行；

　　词法分析的作用是将整个查询分解为多个元素。

　　我们输入的 MySQL 命令，不过是一串长长的字符串，MySQL 的分析器会对其进行词法解析。　

　　select * from T where ID=1;

　　比如，上述语句是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。MySQL 从你输入的 select 这个关键字识别出来，这是一个查询语句。它也要把字符串 T 识别成一个表名，把字符串 ID 识别成一个列。

　　根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。如果你的语句不对，就会收到 You have an error in your SQL syntax 的错误提醒。

4  优化

　　MySQL 查询优化器最主要的目标是尽可能地使用索引，并且使用最严格的索引来消除尽可能多的数据行。

　　MySQL 的优化器有几个重要任务： 1、选择最合适的索引； 2、选择表扫还是走索引； 3、选择表关联顺序； 4、优化 where 子句； 5、排除管理中无用表； 6、决定 order by 和 group by 是否走索引；7、尝试使用 inner join 替换 outer join；8、简化子查询，决定结果缓存；9、合并试图；

　　优化实例1： 

　　SELECT col3 FROM mytable WHERE col1 = 'value1' AND col2 = 'value2';

　　假设col1上的测试匹配了900个数据行，col2上的测试匹配了300个数据行，而同时进行的测试只有30个数据行。

　　　　（1）先测试col1会有900个数据行，需要检查它们找到其中的30个与col2中的值匹配记录，其中就有870次是失败了。

　　　　（2）先测试col2会有300个数据行，需要检查它们找到其中的30个与col1中的值匹配的记录，只有270次是失败的，因此需要的计算和磁盘 I/O 更少。

　　其结果是，优化器会先测试 col2，因为这样做开销更小。

　　实例2：　

　　　　WHERE mycol < 4 / 2
　　　　WHERE mycol * 2 < 4

　　对于第一行，优化器把表达式 4/2 简化为 2，接着使用 mycol 上的索引来快速地查找小于 2 的值。

　　对于第二个表达式，MySQL 必须检索出每个数据行的 mycol 值，乘以 2，接着把结果与 4 进行比较。在这种情况下，不会使用索引。数据列中的每个值都必须被检索到，这样才能计算出比较表达式左边的值。

5  执行

　　执行开始之前，会先判断是否有操作权限，若没有，会抛出相关异常。

　　如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

　　比如我们这个例子中的表 T 中，ID 字段没有索引，那么执行器的执行流程是这样的：

　　　　1、调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；

　　　　2、调用引擎接口取下一行，重复相同的判断逻辑，直到取到这个表的最后一行。

　　　　3、执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

　　至此，这个语句就执行完成了。

　　对于有索引的表，执行的逻辑也差不多。第一次调用的是取满足条件的第一行这个接口，之后循环取满足条件的下一行这个接口，这些接口都是引擎中已经定义好的。

　　可以看出，是否有索引，执行效率区别还是很大的，没有索引需要取出所有数据，一个个进行比较；而有索引则是直接取满足条件的数据；

参考：

　　https://mp.weixin.qq.com/s?__biz=MjM5NjMyMjUzNg==&mid=2448131610&idx=1&sn=1938ca532efe902758b51c3eee33d30b&chksm=b2f42b9d8583a28b410ff846059e878e8a46ba05cdc1199eef0b88de78d4c216fe689b8c85f6&mpshare=1&scene=1&srcid=1123m90BQpXy9p8GgMXaiMlB#rd

　　https://mp.weixin.qq.com/s/ux5qgBMj0NQ_zKZIgG9H7A

十五、MySQL一条update语句执行流程

　　MySQL是如何做到可以恢复到任意一秒状态的？

　　https://mp.weixin.qq.com/s?__biz=MjM5NjMyMjUzNg==&mid=2448131616&idx=1&sn=5af80b03adef5846b7dc51015d99f7e7&chksm=b2f42ba78583a2b164c5304429f024c2869b87e92280e1ad66a092dcece54dcd7bb3285b1a55&scene=21#wechat_redirect

　　update T set c=c+1 where ID=2； 

　　其实执行流程和查询流程一致，只是最后执行器执行的是找到这条数据，并进行更新。另外，更新过程还涉及到一个重要的日志模块，即 redo log（重做日志）和 binlog（归档日志）。

（1）redo log

　　InnoDB 记录了对数据文件的物理更改，并保证总是日志先行。也就是所谓的 WAL（Write-Ahead Logging），即在持久化数据文件前，保证之前的 redo log 已经写到磁盘。MySQL 的每一次更新并没有每次都直接写入磁盘，而是InnoDB 引擎会先将记录写到 redo log 里，并更新到内存（ buffer pool ）中，然后再适当的时候，再把这个记录更新到磁盘。

　　当数据库对数据做修改的时候，需要把数据页从磁盘读到 buffer pool 中，然后在 buffer pool 中进行修改。那么这个时候 buffer pool 中的数据页就与磁盘上的数据页内容不一致，我们称 buffer pool的数据页为 dirty page 脏页。感觉就像先拷贝一份数据，对拷贝的数据进行修改，修改完毕后再覆盖到原数据。

　　　　

　　这里可以看出，所有的更新操作都是现在 dirty page 中进行的。如果这个时候发生非正常的 DB 服务重启，那么这些数据还在内存中，并没有同步到磁盘文件中（注意，同步到磁盘文件是个随机IO），也就是会发生数据丢失。如果这个时候，能够在有一个文件，当 buffer pool 中的 dirty page 变更结束后，把相应修改记录记录到这个文件（注意，记录日志是顺序 IO）。那么当 DB 服务发生crash 的情况，恢复 DB 的时候，也可以根据这个文件的记录内容，重新应用到磁盘文件，数据保持一致。这个文件就是 redo log ，用于记录数据修改后的记录，顺序记录。

　　在事务开始之后就产生 redo log，redo log 的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入 redo log 文件中。当对应事务的脏页写入到磁盘之后，redo log 的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。

　　redo log 文件以 ib_logfile[number] 命名，并以顺序的方式写入文件文件，写满时则回溯到第一个文件，进行覆盖写。

　　　　 

　　如图所示：

• • write pos 是当前记录的位置，一边写一边后移，写到最后一个文件末尾后就回到 0 号文件开头；

  • checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件；

　　write pos 和 checkpoint 之间还空着的部分，可以用来记录新的操作。

　　如果 write pos 追上 checkpoint，表示写满，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

　　redo log 文件是循环写入的，在覆盖写之前，总是要保证对应的脏页已经刷到了磁盘。在非常大的负载下，redo log 可能产生的速度非常快，导致频繁的刷脏操作，进而导致性能下降。

　　如果可预期会有这样的场景，我们建议调大 redo log 文件的大小。可以做一次干净的 shutdown，然后修改 redo log 配置，重启实例。

　　默认情况下，对应的物理文件位于数据库的 data 目录下的 ib_logfile1、ib_logfile2。
　　　　

　　redo log 有一个缓存区 Innodb_log_buffer，默认大小为 8M，Innodb 存储引擎先将重做日志写入 innodb_log_buffer 中。

　　　　

　　然后会通过以下三种方式将 innodb 日志缓冲区的日志刷新到磁盘：

　　　　1、Master Thread 每秒一次执行刷新 Innodb_log_buffer 到重做日志文件；

　　　　2、每个事务提交时会将重做日志刷新到重做日志文件；

　　　　3、当 redo log 缓存可用空间少于一半时，重做日志缓存被刷新到重做日志文件；

　　有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

　　CrashSafe 能够保证 MySQL 服务器宕机重启后：

• 所有已经提交的事务的数据仍然存在

• 所有没有提交的事务的数据自动回滚

（2）Undo + Redo事务的简化过程　

　　假设有A、B两个数据，值分别为1,2，开始一个事务，事务的操作内容为：把1修改为3，2修改为4，那么实际的记录如下（简化）：

　　　　A.事务开始.

　　　　B.记录A=1到undo log.
　　　　C.修改A=3.
　　　　D.记录A=3到redo log.
　　　　E.记录B=2到undo log.
　　　　F.修改B=4.
　　　　G.记录B=4到redo log.
　　　　H.将redo log写入磁盘。

　　　　I.事务提交

（3）BinLog　

　　MySQL 整体可以分为 Server 层和引擎层。其实，redo log 是属于引擎层的 InnoDB 所特有的日志，而 Server 层也有自己的日志，即 binlog（归档日志）。

　　binlog 是逻辑格式的日志，可以简单认为就是执行过的事务中的 sql 语句。但又不完全是 sql 语句这么简单，而是包括了执行的 sql 语句（增删改）反向的信息。

也就意味着 delete 对应着 delete 本身和其反向的 insert；update 对应着 update 执行前后的版本的信息；insert 对应着 delete 和 insert 本身的信息。

　　事务提交的时候，一次性将事务中的 sql 语句按照一定的格式记录到 binlog 中。因此，对于较大事务的提交，可能会变得比较慢一些。binlog 的默认是保持时间由参数 expire_logs_days 配置，也就是说对于非活动的日志文件，在生成时间超过配置的天数之后，会被自动删除。

　　binlog和redo log的区别：

　　　　1、redo log 是 InnoDB 引擎特有的，binlog 是 MySQL 的 Server 层实现，所有引擎都可以使用；

　　　　2、内容不同：redo log 是物理日志，记录的是在数据页上做了什么修改,是正在执行中的 dml 以及 ddl 语句；

　　　　　　　　　　  binlog 是逻辑日志，记录的是语句的原始逻辑，已经提交完毕之后的 dml 以及 ddl sql 语句，如「给 ID=2 的这一行的 c 字段加 1」；

　　　　　　物理日志记录数据库物理变化，即变化的数据信息，而逻辑设备记录逻辑的变化，比如insert, update等动作，但不是记录具体数据

　　　　3、写方式不同：redo log 是循环写的，空间固定；binlog 是可以一直追加写的，一个文件写到一定大小后，会继续写下一个，之前写的文件不会被覆盖；

　　　　4、作用不同：redo log 主要用来保证事务安全，作为异常 down 机或者介质故障后的数据恢复使用，binlog 主要用来做主从复制和即时点恢复时使用；

　　　　5、另外，两者日志产生的时间，可以释放的时间，在可释放的情况下清理机制，都是完全不同的。

（4）针对上面的更新语句的具体执行流程：

　　执行器和 InnoDB 引擎在执行这个简单的 update 语句时的内部流程。

　　　　

　　　　1、执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用聚集索引找到这一行。如果ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。对应到上面讲的，就是将数据加载到 buffer pool 中。

　　　　2、执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

　　　　3、引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

　　　　4、执行器生成这个操作的 binlog，并把 binlog 写入磁盘；

　　　　5、执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

（5）两阶段提交

　　　　

　　MySQL 在 prepare 阶段会生成 xid，然后会在 commit 阶段写入到 binlog 中。在进行恢复时事务要提交还是回滚，是由 Binlog 来决定的。

　　由上面的二阶段提交流程可以看出，通过两阶段提交方式保证了无论在任何情况下，事务要么同时存在于 redo log 和 binlog 中，要么两个里面都不存在。这样就可以保证事务的 binlog 和 redo log 顺

序一致性。一旦阶段 2 中持久化 binlog 完成，就确保了事务的提交。

　　此外需要注意的是，每个阶段都需要进行一次 fsync 操作才能保证上下两层数据的一致性。

PS：记录 Binlog 是在 InnoDB 引擎 Prepare（即 Redo Log 写入磁盘）之后，这点至关重要。

另外需要注意的一点就是，SQL 语句产生的 Redo 日志会一直刷新到磁盘（master thread 每秒 fsync redo log），而 Binlog 是事务 commit 时才刷新到磁盘，如果 binlog 太大则 commit 时会慢。

　　Bin log 用于记录了完整的逻辑记录，所有的逻辑记录在 bin log 里都能找到，所以在备份恢复时，是以 bin log 为基础，通过其记录的完整逻辑操作，备份出一个和原库完整的数据。 

　　如果 redo log 执行了 update t set status = 1，此时原库的数据 status 已更新为 1，而 bin log 写入失败，没有记录这一操作，后续备份恢复时，其 status = 0，导致数据不一致）。 

　　其核心就是， redo log 记录的，即使异常重启，都会刷新到磁盘，而 bin log 记录的， 则主要数据库逻辑操作，主要用于备份恢复。

• • 　　当事务在 prepare 阶段 crash，数据库恢复的时候该事务未写入 binlog 并且 redo log 未提交，将该事务 rollback。

  • 　　当事务在 binlog 阶段 crash，此时日志还没有成功写入到磁盘中，启动时会 rollback 此事务。

  • 　　当事务在 binlog 日志已经 fsync 到磁盘后 crash，但是 InnoDB 没有来得及 commit，此时 MySQL 数据库 recovery 的时候将会读出 binlog 中的 xid，然后告诉 InnoDB 提交这些 xid 的事务，InnoDB 提交完这些事务后会回滚其它的事务，使 redo log 和 binlog 始终保持一致。

十六、MySQL分页查询优化 

　　一般的分页查询使用简单的 limit 子句就可以实现。limit 子句声明如下：

　　SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

　　例如下面语句：

select * from orders_history where type=8 limit 1000,10;

　　该条语句将会从表 orders_history 中查询offset: 1000开始之后的10条数据，也就是第1001条到第1010条数据（1001 <= id <= 1010）。

　　数据表中的记录默认使用主键（一般为id）排序，上面的结果相当于：

select * from orders_history where type=8 order by id limit 10000,10;

（1）测试查询记录量对时间的影响：

select * from orders_history where type=8 limit 10000,1;
select * from orders_history where type=8 limit 10000,10;
select * from orders_history where type=8 limit 10000,100;
select * from orders_history where type=8 limit 10000,1000;
select * from orders_history where type=8 limit 10000,10000;

　　三次查询时间如下：

查询1条记录：3072ms 3092ms 3002ms
查询10条记录：3081ms 3077ms 3032ms
查询100条记录：3118ms 3200ms 3128ms
查询1000条记录：3412ms 3468ms 3394ms
查询10000条记录：3749ms 3802ms 3696ms

　　基本可以确定，在查询记录量低于100时，查询时间基本没有差距，随着查询记录量越来越大，所花费的时间也会越来越多。

（2）针对查询偏移量的测试：

select * from orders_history where type=8 limit 100,100;
select * from orders_history where type=8 limit 1000,100;
select * from orders_history where type=8 limit 10000,100;
select * from orders_history where type=8 limit 100000,100;
select * from orders_history where type=8 limit 1000000,100;

　　三次查询时间如下：

查询100偏移：25ms 24ms 24ms
查询1000偏移：78ms 76ms 77ms
查询10000偏移：3092ms 3212ms 3128ms
查询100000偏移：3878ms 3812ms 3798ms
查询1000000偏移：14608ms 14062ms 14700ms

　　随着查询偏移的增大，尤其查询偏移大于10万以后，查询时间急剧增加。

　　这种分页查询方式会从数据库第一条记录开始扫描，所以偏移量越大，查询速度越慢，而且查询的数据越多，也会拖慢总查询速度。

（3）优化方式：使用 id 限定优化

　　这种方式假设数据表的id是连续递增的，则我们根据查询的页数和查询的记录数可以算出查询的id的范围，可以使用 id between and 来查询：

select * from orders_history where type=2 and id between 1000000 and 1000100 limit 100;

　　查询时间：15ms 12ms 9ms

　　这种查询方式能够极大地优化查询速度，基本能够在几十毫秒之内完成。限制是只能使用于明确知道id的情况，不过一般建立表的时候，都会添加基本的id字段，这为分页查询带来很多便利。

　　还可以有另外一种写法：

select * from orders_history where id >= 1000001 limit 100;

　　当然还可以使用 in 的方式来进行查询，这种方式经常用在多表关联的时候进行查询，使用其他表查询的id集合，来进行查询：

select * from orders_history where id in
　　(select order_id from trade_2 where goods = 'pen')
limit 100;

　　这种 in 查询的方式要注意：某些 mysql 版本不支持在 in 子句中使用 limit。

（4）代码中常用的分页查询写法

public void saveHotelPriceIndex() {
    　　int limit = 1000;
       long startId = 0;
       List<HotelPriceIndexDO> hotelPriceIndexDOList = hotelPriceDAO.queryForList(startId, limit);
       while (hotelPriceIndexDOList.size() > 0) {
       　　//保存至es
          JSONArray jsonArray = (JSONArray) JSONObject.toJSON(hotelPriceIndexDOList);
       　　BulkResponse response = saveChildrenIndexBulk(Dictionary.HOTEL_INDEX_NAME, Dictionary.PRICE_TYPE_NAME, jsonArray);
          saveIndexBulk(Dictionary.HOTEL_INDEX_NAME,Dictionary.PRICE_TYPE_NAME,jsonArray); //循环获取
          startId = hotelPriceIndexDOList.get(hotelPriceIndexDOList.size() - 1).getDataId();
          if (hotelPriceIndexDOList.size() < limit) {
          　　break;
          } else {
              hotelPriceIndexDOList = hotelPriceDAO.queryForList(startId, limit);
          }
     }
 }

 

 

参考：

　　1、分库分表技术演进&最佳实践

　　　　https://mp.weixin.qq.com/s?__biz=MzAxODcyNjEzNQ==&mid=2247486161&idx=1&sn=a8b68997a8e3e1623e66b83d5c21ce88&chksm=9bd0a749aca72e5f240a6ad1b28bcc923ee2874e16d9b9641b7efd99bc368baa963d081e2ba0&scene=21#wechat_redirect
　　2、为什么分库分表后不建议跨分片查询

　　　　 https://mp.weixin.qq.com/s/l1I5u3n-lSwDYfxC-V3low
　　3、Mybatis相关博客

　　　　 https://www.cnblogs.com/xiaoxi/category/929946.html

　　4、mysql博客：

　　　　https://www.cnblogs.com/duanxz/tag/mysql/default.html?page=2

　　5、分分钟解决 MySQL 查询速度慢与性能差

　　　　 https://mp.weixin.qq.com/s?__biz=MzU2NjIzNDk5NQ==&mid=2247485663&idx=1&sn=ca4b6b8b9f1f07130991ebf4280f3516&chksm=fcaed2c3cbd95bd5a7c0edad1570e4aa6743a31b737237d1614ad1e53cfbc791cbe00ef1718c&scene=0&key=cb077656fa10b4eb22cbad0217b717b7810f0bd671047098e38bf3bcce15fc365c684d12a527db0f7b05a091833f15adb8955eac5bab0b1facdb9c3777f8a313f5fbd5bb647bce8d27569780d0529eb0&ascene=1&uin=MjU3OTI0NDc0Mg%3D%3D&devicetype=Windows+10&version=62060720&lang=zh_CN&pass_ticket=bUI0a1DMiTeed1JhWLPDXnic6pU3sSLG%2BHlhGuYNEkgGE66FurCt3ct9gbyFdnrz

　　6、深入学习MySQL事务：ACID特性的实现原理***

　　　　https://www.cnblogs.com/kismetv/p/10331633.html
　　

　　7、4种事务的隔离级别，InnoDB如何巧妙实现？

　　　　 https://mp.weixin.qq.com/s?__biz=MjM5ODYxMDA5OQ==&mid=2651961498&idx=1&sn=058097f882ff9d32f5cdf7922644d083&chksm=bd2d0d468a5a845026b7d2c211330a6bc7e9ebdaa92f8060265f60ca0b166f8957cbf3b0182c&scene=21#wechat_redirect

　　8、详细分析MySQL事务日志(redo log和undo log)

　　　　 https://www.cnblogs.com/f-ck-need-u/archive/2018/05/08/9010872.html
　　9、关于MySQL，你未必知道的！***

　　　　https://mp.weixin.qq.com/s?__biz=MjM5ODYxMDA5OQ==&mid=2651961957&idx=1&sn=c4cdf7c27ade9c95fdf40c4c38e19da9&chksm=bd2d0fb98a5a86af13ec7f096bde37e1c8cd0d19e7124e6bdb53761314d5b64a39ba9fbd1355&scene=21#wechat_redirect
　　10、数据库索引，到底是什么做的？

　　　　 https://mp.weixin.qq.com/s?__biz=MjM5ODYxMDA5OQ==&mid=2651961486&idx=1&sn=b319a87f87797d5d662ab4715666657f&chksm=bd2d0d528a5a84446fb88da7590e6d4e5ad06cfebb5cb57a83cf75056007ba29515c85b9a24c&scene=21#wechat_redirect

　　11、索引的实现原理

　　　　https://blog.csdn.net/timer_gao/article/details/78013826
https://www.cnblogs.com/weiwenbo/p/6531977.html
　　12、MySQL实战 | 为什么要使用索引？

　　　　https://mp.weixin.qq.com/s?__biz=MjM5NjMyMjUzNg==&mid=2448131705&idx=1&sn=308f8e12e497a890144357c981eb7136&chksm=b2f42bfe8583a2e8d0d46953de74be3fde99791f0b617d7d66e263375d6b95dd9840a6d018c9&scene=21#wechat_redirect
　　B+树，它是一种非常适合用来做数据库索引的数据结构：
　　　　(1)很适合磁盘存储，能够充分利用局部性原理，磁盘预读；
　　　　(2)很低的树高度，能够存储大量数据；
　　　　(3)索引本身占用的内存很小；
　　　　(4)能够很好的支持单点查询，范围查询，有序性查询；
　　13、1分钟了解MyISAM与InnoDB的索引差异 https://mp.weixin.qq.com/s?__biz=MjM5ODYxMDA5OQ==&mid=2651961494&idx=1&sn=34f1874c1e36c2bc8ab9f74af6546ec5&chksm=bd2d0d4a8a5a845c566006efce0831e610604a43279aab03e0a6dde9422b63944e908fcc6c05&scene=21#wechat_redirect
　　MyISAM的索引：
　　　　MyISAM的索引与行记录是分开存储的，叫做非聚集索引（UnClustered Index）。其主键索引与普通索引没有本质差异：
　　　　有连续聚集的区域单独存储行记录
　　　　主键索引的叶子节点，存储主键，与对应行记录的指针
　　　　普通索引的叶子结点，存储索引列，与对应行记录的指针
　　　　画外音：MyISAM的表可以没有主键。
　　　　主键索引与普通索引是两棵独立的索引B+树，通过索引列查找时，先定位到B+树的叶子节点，再通过指针定位到行记录。
　　InnoDB的索引：
　　　　InnoDB的主键索引与行记录是存储在一起的，故叫做聚集索引（Clustered Index）：
　　　　没有单独区域存储行记录
　　　　主键索引的叶子节点，存储主键，与对应行记录（而不是指针）
　　　　画外音：因此，InnoDB的PK查询是非常快的。
　　因为这个特性，InnoDB的表必须要有聚集索引：
　　　　(1)如果表定义了PK，则PK就是聚集索引；
　　　　(2)如果表没有定义PK，则第一个非空unique列是聚集索引；
　　　　(3)否则，InnoDB会创建一个隐藏的row-id作为聚集索引；
　　聚集索引，也只能够有一个，因为数据行在物理磁盘上只能有一份聚集存储。
　　InnoDB的普通索引可以有多个，它与聚集索引是不同的：
　　普通索引的叶子节点，存储主键（也不是指针）
　　对于InnoDB表，这里的启示是：
　　　　(1)不建议使用较长的列做主键，例如char(64)，因为所有的普通索引都会存储主键，会导致普通索引过于庞大；
　　　　(2)建议使用趋势递增的key做主键，由于数据行与索引一体，这样不至于插入记录时，有大量索引分裂，行记录移动；
11、InnoDB，5项最佳实践，知其所以然？ https://mp.weixin.qq.com/s?__biz=MjM5ODYxMDA5OQ==&mid=2651961428&idx=1&sn=31a9eb967941d888fbd4bb2112e9602b&chksm=bd2d0d888a5a849e7ebaa7756a8bc1b3d4e2f493f3a76383fc80f7e9ce7657e4ed2f6c01777d&scene=21#wechat_redirect

12、就是这么迅猛的实现搜索需求 https://mp.weixin.qq.com/s?__biz=MjM5ODYxMDA5OQ==&mid=2651959917&idx=1&sn=8faeae7419a756b0c355af2b30c255df&chksm=bd2d07b18a5a8ea75f16f7e98ea897c7e7f47a0441c64bdaef8445a2100e0bdd2a7de99786c0&scene=21#wechat_redirect

13、MySql数据库索引原理 https://www.cnblogs.com/weizhixiang/p/5914120.html

14、MySQL共享锁（读锁、S锁）与排他锁（写锁、X锁） https://blog.csdn.net/u014292162/article/details/83271299

16、MySQL DELETE 删除语句加锁分析 http://www.fordba.com/lock-analyse-of-delete.html

17、mysql的mvcc（多版本并发控制） https://www.cnblogs.com/dongqingswt/p/3460440.html

18、SQL中的where条件，在数据库中提取与应用浅析 http://hedengcheng.com/?p=577

19、超赞，InnoDB调试死锁的方法！

　　https://mp.weixin.qq.com/s?__biz=MjM5ODYxMDA5OQ==&mid=2651961535&idx=1&sn=b62e9d71836ac5cf2d3cedf69e1ef395&chksm=bd2d0d638a5a84750adfc39d7e177a63330d6bde0f56600764b2d79e0fb9d96ad69e26e19ff1&scene=21#wechat_redirect

20、explain详解　　https://www.cnblogs.com/gomysql/p/3720123.html