联合索引

比较简单的是单列索引。遇到多条件查询时，不可避免会使用到多列索引。联合索引又叫联合索引，对于联合索引（多列b+tree，使用多列值组合而成的b+tree索引）。遵循最左侧原则，从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c)。可以支持a ，a,b ，a,b,c 这3种组合进行查找，但不支持 b,c进行查找。当使用最左侧字段时，索引就十分有效，也就是我们创建了三个索引：(a)、（a,b）（a,b,c）

• 当(a=? and b=? and c=?)这样的数据来检索的时候，b+树会优先比较a列来确定下一步的所搜方向，如果a列相同再依次比较b列和c列，最后得到检索的数据；顺序最优，联合发挥效力，最少回表次数
• 当(b=? and c=?)这样的没有a列的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候a列就是第一个比较因子，必须要先根据a列来搜索才能知道下一步去哪里查询。无法利用联合索引查询
• 当(a=? and c=?)这样的数据来检索时，b+树可以用a列来指定搜索方向，但下一个字段b列的缺失，所以只能把a列的数据找到，然后再匹配c列的数据了 只能用a的查询，依据c做匹配（索引下推），减少回表次数

这个是非常重要的性质，即索引的最左匹配特性，where后的查询顺序与索引创建顺序一致。需要注意，联合索引是首先使用多列索引的第一列构建的索引树，用上面key index (a,b,c)的例子就是优先使用a列构建，当a列值相等时再以b列排序，若b列的值也相等则以c列排序

最左前缀原则

如果为每一种查询都设计一个索引，索引是不是太多了。如果我现在要按照市民的身份证号去查他的家庭地址呢？虽然这个查询需求在业务中出现的概率不高，但总不能单独为一个不频繁的请求创建一个（身份证号，地址）的索引吧，应该怎么做呢？

B+ 树这种索引结构，可以利用索引的最左前缀来定位记录。为了直观地说明这个概念，我们用（name，age）这个联合索引来分析

可以看到，索引项是按照索引定义里面出现的字段顺序排序的

• 当你的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到 ID4，然后向后遍历得到所有需要的结果。
• 如果你要查的是所有名字第一个字是“张”的人，你的 SQL 语句的条件是"where name like ‘张 %’"。这时，你也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。

这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符，那么既然最左边的字段这么重要，我们该怎么安排联合索引的顺序呢？

• 第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。这段开头的问题里，我们要为高频请求创建 (身份证号，姓名）这个联合索引，并用这个索引支持“根据身份证号查询地址”的需求【查地址时回表查询】，这样不用创建多余索引
• 第二原则是节省空间，如果既有联合查询，又有基于 a、b 各自的查询呢？查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护 (a,b)、(b) 这两个索引，比如上面这个市民表的情况，name 字段是比 age 字段大的 ，那我就建议你创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。

基于以上的方式进行联合索引的创建。需要注意，

索引下推

我们还是以市民表的联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是 10 岁的所有男孩”。那么，SQL 语句是这么写的：

mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

因为最左前缀索引规则，所以这个语句在搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录 ID3。当然，这还不错，总比全表扫描要好，在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值，为什么age联合索引不生效呢？因为查询条件里面 name 字段的查询值后面是 % ，导致 age 条件无法和 name 一次进行联合索引的查询，所以 age 查询值是用不上的，存储引擎在(name,age)索引树以"张"进行前缀匹配的数搜索。所以查询顺序并不是 name->age，只有name，也就是因为 name 列使用了 like ，导致后面的索引无法再以组合索引生效

而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

MySQL数据库事务及锁机制

本部分回答以下几个问题，如果能回答正确，则证明本部分掌握好了。

MySQL中事务的四大特性，分别如何保证

并发事务的隔离问题有哪几种

事务有哪几种隔离级别应对并发隔离问题

MySQL有哪几种锁，分别有什么作用

隔离机制的实现方式

接下来我们看这部分的内容。事务（Transaction）是并发控制的单位，是用户定义的一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位事务体现出整体的概念，要么事务中的操作全部成功，要么全部失败体现在数据库sql里就是逻辑上相互依赖的一组sql语句 在 MySQL 中，事务支持是在引擎层实现的。MySQL 是一个支持多引擎的系统，但并不是所有的引擎都支持事务。比如 MySQL 原生的 MyISAM 引擎就不支持事务，这也是 MyISAM 被 InnoDB 取代的重要原因之一

事务的四大特性

事务最经典也经常被拿出来说例⼦就是转账了。假如⼩明要给⼩红转账1000元，这个转账会涉及到两个关键操作就是：将⼩明的余额减少1000元，将⼩红的余额增加1000元。万⼀在这两个操作之间突然出现错误⽐如银⾏系统崩溃，导致⼩明余额减少⽽⼩红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败

事务的四大特性就是ACID，也就是原子性、一致性、隔离性以及持久性

• 原子性（Atomicity），事务是最⼩的执⾏单位，不允许分割。事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。
• 一致性（Consistency），是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。
• 隔离性（Isolation），是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离，要串行执行。即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。关于事务的隔离性数据库提供了多种隔离级别，稍后会介绍到。
• 持久性（Durability），是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务以及正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成，否则就会造成我们看到提示事务处理完毕，导致数据库因为故障而没有执行事务的重大错误。

只有满足这四大特性，才能说这是一个标准的事务。原子性、一致性和持久性由MySQL的日志机制通过两阶段提交、crash-safe能力以及备份能力来满足，在日志机制这篇Blog里我详细的讨论过，那么我们当前最需要关注的其实就是事务的隔离性。

并发事务的隔离问题

当多个线程都开启事务操作数据库中的数据时，数据库系统要能进行隔离操作，以保证各个线程获取数据的准确性，我们先看看如果不考虑事务的隔离性，会发生的几种问题：

脏读

脏读（dirty reads），是指在一个事务处理过程里读取了另一个未提交的事务中的数据。当⼀个事务正在访问数据并且对数据进⾏了修改，⽽这种修改还没有提交到数据库中，这时另外⼀个事务也访问了这个数据，然后使⽤了这个数据。因为这个数据是还没有提交的数据，那么另外⼀个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的

用户A向用户B转账100元，对应SQL命令如下：
update account set money=money+100 where name=’B’;  (此时A通知B)
update account set money=money - 100 where name=’A’;

• 当只执行第一条SQL时，A通知B查看账户，B发现确实钱已到账（此时即发生了脏读）
• 无论第二条SQL是否执行，只要该事务不提交，则所有操作都将回滚
• 当B以后再次查看账户时就会发现钱其实并没有转

也就是脏读一般针对修改数据而言，Transaction 1 修改了一行数据，然后 Transaction 2 在 Transaction 1 还未提交修改操作之前读取了被修改的行。如果 Transaction 1 回滚了修改操作，那么Transaction 2读取的数据就可以看作是从未存在过的 A未提交的修改被B读了

不可重复读

不可重复读（non-repeatable reads），针对修改，是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。A已提交的修改被B读了

A向B转账100并且提交，B收到读到100，这时候A又向B转100并提交，B读到200

• 在某些情况下，不可重复读并不是问题，比如我们多次查询某个数据当然以最后查询得到的结果为主
• 不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据

Transaction 1 读取一行数据，然后 Transaction 2 修改或删除该行并提交修改操作。当 Transaction 1 试图重新读取该行时，它就会得到不同的数据值（如果该行被更新）或发现该行不再存在（如果该行被删除）

幻读

幻读（phantom read）是事务非独立执行时发生的一种现象。针对插入。

• 事务T1对一个表中所有的行的某个数据项做了从1修改为2的操作
• 这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为1并且提交给数据库。
• 而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。

幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项（修改），而幻读针对的是一批数据整体（插入或删除）。A已提交的新增删除数据被B读了

综合举例

对于同一个银行帐户A内有200元，甲进行提款操作100元，乙进行转帐操作100元到B帐户。如果事务没有进行隔离可能会并发如下问题：

• 脏读：甲先取款100元未提交，乙进行转帐查到帐户内剩有100元，这时甲放弃操作回滚，乙正常操作转账至B账户提交，帐户内最终更新为0元，乙读取了甲的脏数据，客户损失100元。
  不可重复读：甲乙同时开始都查到帐户内为200元，甲先开始取款100元提交，这时乙在准备最后更新的时候又进行了一次查询，发现结果是100元，这时乙就会很困惑，不知道转账后该将帐户改为100还是0，如果按照正常操作乙会将账户A更新为0，正确执行，所以说一般不可重复读不一定会有问题。
• 幻读：银行会计甲对所有账户的金额单位更新为美元，这时银行会计乙又插入了两条金额单位为人民币的数据，银行会计甲更新完后查数据发现有几条还是人民币，于是以为自己产生了幻觉

以上就是事务并发时会产生的问题。

事务的隔离级别

针对以上的四个常见的并发事务的问题，数据库设置了四种隔离级别来应对：

• Read uncommitted (读未提交)：最低级别，最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读
• Read committed (读已提交)：允许读取并发事务已经提交的数据，可以阻⽌脏读，但是幻读或不可重复读仍有可能发⽣
• Repeatable read (可重复读)：对同⼀字段的多次读取结果都是⼀致的，除⾮数据是被本身事务⾃⼰所修改，可以阻⽌脏读和不可重复读，但幻读仍有可能发⽣
• Serializable (串行化)： 最⾼的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执⾏，这样事务之间就完全不可能产⽣⼲扰，也就是说，该级别可以防⽌脏读、不可重复读以及幻读，对于同一行记录，写会加写锁，读会加读锁。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行

当然，随着隔离级别的增高，并发性能也会逐渐下降。

结合隔离问题和隔离机制举例说明，在这样一个事务流程下：

我们来看看在不同的隔离级别下，事务 A 会有哪些不同的返回结果，也就是图里面 V1、V2、V3 的返回值分别是什么。

• 若隔离级别是读未提交， 则 V1 的值就是 2。这时候事务 B 虽然还没有提交，但是结果已经被 A 看到了。因此，V2、V3 也都是 2。
• 若隔离级别是读提交，则 V1 是 1，V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2。
• 若隔离级别是可重复读，则 V1、V2 是 1，V3 是 2。之所以 V2 还是 1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。
• 若隔离级别是串行化，则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2。

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。

• 读未提交隔离级别下直接返回记录上的最新值，没有视图概念；
• 读提交隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的，所以会在执行sql时发生变动（需要判断事务是否已提交，提交后才变动）
• 可重复读隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。假设你在管理一个个人银行账户表。一个表存了账户余额，一个表存了账单明细。到了月底你要做数据校对，也就是判断上个月的余额和当前余额的差额，是否与本月的账单明细一致。你一定希望在校对过程中，即使有用户发生了一笔新的交易，也不影响你的校对结果。这时候使用“可重复读”隔离级别就很方便。事务启动时的视图可以认为是静态的，不受其他事务更新的影响
• 串行化隔离级别下直接用加读写锁的方式来避免并行访问

我们可以看到在不同的隔离级别下，数据库行为是有所不同的。MySQL InnoDB 存储引擎的默认⽀持的隔离级别是 REPEATABLE-READ（可重读），与 SQL 标准不同的地⽅在于

• InnoDB 存储引擎在 REPEATABLE-READ（可重读） 事务隔离级别下使⽤的是Next-Key Lock 锁算法，因此可以避免幻读的产⽣，这与其他数据库系统(如SQL Server) 是不同的。
• InnoDB 存储引擎的默认⽀持的隔离级别是 REPEATABLE-READ（可重读） 已经可以完全保证事务的隔离性要求，即达到了 SQL标准的 SERIALIZABLE(可串⾏化) 隔离级别。
• 因为隔离级别越低，事务请求的锁越少，所以⼤部分数据库系统的隔离级别都是 READ-COMMITTED(读取提交内容) ，但是你要知道的是InnoDB 存储引擎默认使⽤REPEATABLE-READ（可重读）并不会有任何性能损失。

InnoDB 存储引擎在分布式事务 的情况下⼀般会⽤到 SERIALIZABLE(可串⾏化) 隔离级别

隔离机制的实现

理解了事务的隔离级别，我们再来看看事务隔离具体是怎么实现的。视图可以理解为数据副本，每次创建视图时，将当前『已持久化的数据』创建副本，后续直接从副本读取，从而达到数据隔离效果，存在视图的 2 种隔离级别：

1. 读提交，在每一条 SQL 开始执行时创建视图，隔离作用域仅限该条 SQL 语句。
2. 可重复读，事务启动时创建视图，因此，在事务任意时刻，对记录读取的值都是一样的。

其他 2 种无视图的隔离级别：

1. 读未提交，直接返回记录最新值。
2. 串行化，串行化通过读写锁来避免并行访问，读-读：允许并发执行，读-写：只能串行，写-写：只能串行

这里我们展开说明可重复读。在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录

当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。

回滚日志

回滚日志记录在undo log，undo log记录的是逆过程流，比如对表进行了一次insert，对应的就是delete；对字段a进行一次+1操作，那undo log就是一次-1操作

回滚日志总不能一直保留吧，什么时候删除呢？答案是，在不需要的时候才删除。也就是说，系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。什么时候才不需要了呢？就是当一个查询事务开启以后，在这个时刻之后，事务提交/回滚之前，所有更新产生的undo log都不能被删除

长事务的问题

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小（逻辑清理）。最终只好为了清理回滚段，重建整个库（物理清理）。除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库

事务的启动方式

事务启动方式有两种，一种是显式提交，另一种是通过设置禁止自动提交。

• 显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
• set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。有些客户端连接框架会默认连接成功后先执行一个 set autocommit=0 的命令。这就导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务。因此，我会建议你总是使用 set autocommit=1, 通过显式语句的方式来启动事务。

有的开发同学会纠结“多一次交互”的问题。对于一个需要频繁使用事务的业务，第二种方式每个事务在开始时都不需要主动执行一次 “begin”，减少了语句的交互次数。建议使用 commit work and chain 语法。在 autocommit 为 1 的情况下，用 begin 显式启动的事务，如果执行 commit 则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行 begin 语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中