1.4.事务和事务的隔离级别
1.4.1.为什么需要事务
事务是数据库管理系统（DBMS）执行过程中的一个逻辑单位（不可再进行分割），由一个有限的数据库操作序列构成（多个DML语句，select语句不包含事务），要不全部成功，要不全部不成功。
A 给B 要划钱，A 的账户-1000元， B 的账户就要+1000元，这两个update 语句必须作为一个整体来执行，不然A 扣钱了，B 没有加钱这种情况就是错误的。那么事务就可以保证A 、B 账户的变动要么全部一起发生，要么全部一起不发生。
1.4.2.事务特性
事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。
l 原子性（atomicity）
l 一致性（consistency）
l 隔离性（isolation）
l 持久性（durability）
1.4.2.1.原子性（atomicity）
一个事务必须被视为一个不可分割的最小单元，整个事务中的所有操作要么全部提交成功，要么全部失败，对于一个事务来说，不能只执行其中的一部分操作。比如：
连老师借给李老师1000元：
1.连老师工资卡扣除1000元
2.李老师工资卡增加1000元
整个事务的操作要么全部成功，要么全部失败，不能出现连老师工资卡扣除，但是李老师工资卡不增加的情况。如果原子性不能保证，就会很自然的出现一致性问题。
1.4.2.2.一致性（consistency）
一致性是指事务将数据库从一种一致性转换到另外一种一致性状态，在事务开始之前和事务结束之后数据库中数据的完整性没有被破坏。
连老师借给李老师1000元：
1.连老师工资卡扣除1000元
2.李老师工资卡增加1000元
扣除的钱（-500） 与增加的钱（500） 相加应该为0，或者说连老师和李老师的账户的钱加起来，前后应该不变。
1.4.2.3.持久性（durability）
一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，已经提交的修改数据也不会丢失。
1.4.2.4.隔离性（isolation）
一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
如果隔离性不能保证，会导致什么问题？
连老师借给李老师生活费，借了两次，每次都是1000，连老师的卡里开始有10000，李老师的卡里开始有500，从理论上，借完后，连老师的卡里有8000，李老师的卡里应该有2500。
我们将连老师向李老师同时进行的两次转账操作分别称为T1和T2，在现实世界中T1和T2是应该没有关系的，可以先执行完T1，再执行T2，或者先执行完T2，再执行T1，结果都是一样的。但是很不幸，真实的数据库中T1和T2的操作可能交替执行的，执行顺序就有可能是：
如果按照上图中的执行顺序来进行两次转账的话，最终我们看到，连老师的账户里还剩9000元钱，相当于只扣了1000元钱，但是李老师的账户里却成了2500元钱，多了10000元，这银行岂不是要亏死了？
所以对于现实世界中状态转换对应的某些数据库操作来说，不仅要保证这些操作以原子性的方式执行完成，而且要保证其它的状态转换不会影响到本次状态转换，这个规则被称之为隔离性。
1.4.3.事务并发引发的问题
我们知道MySQL是一个客户端／服务器架构的软件，对于同一个服务器来说，可以有若干个客户端与之连接，每个客户端与服务器连接上之后，就可以称之为一个会话（Session）。每个客户端都可以在自己的会话中向服务器发出请求语句，一个请求语句可能是某个事务的一部分，也就是对于服务器来说可能同时处理多个事务。
在上面我们说过事务有一个称之为隔离性的特性，理论上在某个事务对某个数据进行访问时，其他事务应该进行排队，当该事务提交之后，其他事务才可以继续访问这个数据，这样的话并发事务的执行就变成了串行化执行。
但是对串行化执行性能影响太大，我们既想保持事务的一定的隔离性，又想让服务器在处理访问同一数据的多个事务时性能尽量高些，当我们舍弃隔离性的时候，可能会带来什么样的数据问题呢？
1.4.3.1.脏读
当一个事务读取到了另外一个事务修改但未提交的数据，被称为脏读。
1、在事务A执⾏过程中，事务A对数据资源进⾏了修改，事务B读取了事务A修改后的数据。 2、由于某些原因，事务A并没有完成提交，发⽣了RollBack操作，则事务B读取的数据就是脏数据。 这种读取到另⼀个事务未提交的数据的现象就是脏读(Dirty Read)。
1.4.3.2.不可重复读
当事务内相同的记录被检索两次，且两次得到的结果不同时，此现象称为不可重复读。
事务B读取了两次数据资源，在这两次读取的过程中事务A修改了数据，导致事务B在这两次读取出来的 数据不⼀致。
1.4.3.3.幻读
在事务执行过程中，另一个事务将新记录添加到正在读取的事务中时，会发生幻读。
事务B前后两次读取同⼀个范围的数据，在事务B两次读取的过程中事务A新增了数据，导致事务B后⼀ 次读取到前⼀次查询没有看到的⾏。 幻读和不可重复读有些类似，但是幻读重点强调了读取到了之前读取没有获取到的记录。
1.1.4.SQL标准中的四种隔离级别
我们上边介绍了几种并发事务执行过程中可能遇到的一些问题，这些问题也有轻重缓急之分，我们给这些问题按照严重性来排一下序：
脏读 > 不可重复读 > 幻读
我们上边所说的舍弃一部分隔离性来换取一部分性能在这里就体现在：设立一些隔离级别，隔离级别越低，越严重的问题就越可能发生。有一帮人（并不是设计MySQL的大叔们）制定了一个所谓的SQL标准，在标准中设立了4个隔离级别：
READ UNCOMMITTED：未提交读。
READ COMMITTED：已提交读。
REPEATABLE READ：可重复读。
SERIALIZABLE：可串行化。
SQL标准中规定，针对不同的隔离级别，并发事务可以发生不同严重程度的问题，具体情况如下：
也就是说：
READ UNCOMMITTED隔离级别下，可能发生脏读、不可重复读和幻读问题。
READ COMMITTED隔离级别下，可能发生不可重复读和幻读问题，但是不可以发生脏读问题。
REPEATABLE READ隔离级别下，可能发生幻读问题，但是不可以发生脏读和不可重复读的问题。
SERIALIZABLE隔离级别下，各种问题都不可以发生。
1.4.5.MySQL中的隔离级别
不同的数据库厂商对SQL标准中规定的四种隔离级别支持不一样，比方说Oracle就只支持READ COMMITTED和SERIALIZABLE隔离级别。本书中所讨论的MySQL虽然支持4种隔离级别，但与SQL标准中所规定的各级隔离级别允许发生的问题却有些出入，MySQL在REPEATABLE READ隔离级别下，是可以禁止幻读问题的发生的。
MySQL的默认隔离级别为REPEATABLE READ，我们可以手动修改事务的隔离级别。
1.4.5.1.如何设置事务的隔离级别
我们可以通过下边的语句修改事务的隔离级别：
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;
其中的level可选值有4个：
level: {
REPEATABLE READ
| READ COMMITTED
| READ UNCOMMITTED
| SERIALIZABLE
}
设置事务的隔离级别的语句中，在SET关键字后可以放置GLOBAL关键字、SESSION关键字或者什么都不放，这样会对不同范围的事务产生不同的影响，具体如下：
使用GLOBAL关键字（在全局范围影响）：
比方说这样：
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
则： 只对执行完该语句之后产生的会话起作用。当前已经存在的会话无效。
使用SESSION关键字（在会话范围影响）：
比方说这样：
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
则：对当前会话的所有后续的事务有效
该语句可以在已经开启的事务中间执行，但不会影响当前正在执行的事务。
如果在事务之间执行，则对后续的事务有效。
上述两个关键字都不用（只对执行语句后的下一个事务产生影响）：
比方说这样：
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
则：只对当前会话中下一个即将开启的事务有效。下一个事务执行完后，后续事务将恢复到之前的隔离级别。该语句不能在已经开启的事务中间执行，会报错的。
如果我们在服务器启动时想改变事务的默认隔离级别，可以修改启动参数transaction-isolation的值，比方说我们在启动服务器时指定了--transaction-isolation=SERIALIZABLE，那么事务的默认隔离级别就从原来的REPEATABLE READ变成了SERIALIZABLE。
想要查看当前会话默认的隔离级别可以通过查看系统变量transaction_isolation的值来确定：
SHOW VARIABLES LIKE 'transaction_isolation';
或者使用更简便的写法：
SELECT @@transaction_isolation;
注意：transaction_isolation是在MySQL 5.7.20的版本中引入来替换tx_isolation的，如果你使用的是之前版本的MySQL，请将上述用到系统变量transaction_isolation的地方替换为tx_isolation。
1.4.6.MySQL事务
1.4.6.1.事务基本语法
事务开始
1、begin
2、START TRANSACTION（推荐）
3、begin work
事务回滚
rollback
事务提交
commit
使用事务插入两行数据，commit后数据还在
使用事务插入两行数据，rollback后数据没有了
1.4.6.2.保存点
如果你开启了一个事务，执行了很多语句，忽然发现某条语句有点问题，你只好使用ROLLBACK语句来让数据库状态恢复到事务执行之前的样子，然后一切从头再来，但是可能根据业务和数据的变化，不需要全部回滚。所以MySQL里提出了一个保存点（英文：savepoint）的概念，就是在事务对应的数据库语句中打几个点，我们在调用ROLLBACK语句时可以指定会滚到哪个点，而不是回到最初的原点。定义保存点的语法如下：
SAVEPOINT 保存点名称;
当我们想回滚到某个保存点时，可以使用下边这个语句（下边语句中的单词WORK和SAVEPOINT是可有可无的）：
ROLLBACK TO [SAVEPOINT] 保存点名称;
不过如果ROLLBACK语句后边不跟随保存点名称的话，会直接回滚到事务执行之前的状态。
如果我们想删除某个保存点，可以使用这个语句：
RELEASE SAVEPOINT 保存点名称;
1.4.6.3.隐式提交
当我们使用START TRANSACTION或者BEGIN语句开启了一个事务，或者把系统变量autocommit的值设置为OFF时，事务就不会进行自动提交，但是如果我们输入了某些语句之后就会悄悄的提交掉，就像我们输入了COMMIT语句了一样，这种因为某些特殊的语句而导致事务提交的情况称为隐式提交，这些会导致事务隐式提交的语句包括：
1.4.6.3.1.执行DDL
定义或修改数据库对象的数据定义语言（Datadefinition language，缩写为：DDL）。
所谓的数据库对象，指的就是数据库、表、视图、存储过程等等这些东西。当我们使用CREATE、ALTER、DROP等语句去修改这些所谓的数据库对象时，就会隐式的提交前边语句所属于的事务，就像这样：
BEGIN;
SELECT ... # 事务中的一条语句
UPDATE ... # 事务中的一条语句
... # 事务中的其它语句
CREATE TABLE ...
此语句会隐式的提交前边语句所属于的事务
1.4.6.3.2.隐式使用或修改mysql数据库中的表
当我们使用ALTER USER、CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD等语句时也会隐式的提交前边语句所属于的事务。
1.4.6.3.3.事务控制或关于锁定的语句
当我们在一个会话里，一个事务还没提交或者回滚时就又使用START TRANSACTION或者BEGIN语句开启了另一个事务时，会隐式的提交上一个事务，比如这样：
BEGIN;
SELECT ... # 事务中的一条语句
UPDATE ... # 事务中的一条语句
... # 事务中的其它语句
BEGIN; # 此语句会隐式的提交前边语句所属于的事务
或者当前的autocommit系统变量的值为OFF，我们手动把它调为ON时，也会隐式的提交前边语句所属的事务。
或者使用LOCK TABLES、UNLOCK TABLES等关于锁定的语句也会隐式的提交前边语句所属的事务。
1.4.6.3.4.加载数据的语句
比如我们使用LOAD DATA语句来批量往数据库中导入数据时，也会隐式的提交前边语句所属的事务。
1.4.6.3.5.关于MySQL复制的一些语句
使用START SLAVE、STOP SLAVE、RESET SLAVE、CHANGE MASTER TO等语句时也会隐式的提交前边语句所属的事务。
1.4.6.3.6.其它的一些语句
使用ANALYZE TABLE、CACHE INDEX、CHECK TABLE、FLUSH、 LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE、RESET等语句也会隐式的提交前边语句所属的事务。
1.5. MVCC
全称Multi-Version Concurrency Control，即多版本并发控制，主要是为了提高数据库的并发性能。 同一行数据平时发生读写请求时，会上锁阻塞住。但MVCC用更好的方式去处理读—写请求，做到在发生读—写请求冲突时不用加锁。 这个读是指的快照读，而不是当前读，当前读是一种加锁操作，是悲观锁。 那它到底是怎么做到读—写不用加锁的，快照读和当前读是指什么？我们后面都会学到。
1.5.1.MVCC原理
1.5.1.1.复习事务隔离级别
MySQL在REPEATABLE READ隔离级别下，是可以很大程度避免幻读问题的发生的（好像解决了，但是又没完全解决），MySQL是怎么做到的？
1.5.1.2.版本链
必须要知道的概念（每个版本链针对的一条数据）：
我们知道，对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列（row_id并不是必要的，我们创建的表中有主键或者非NULL的UNIQUE键时都不会包含row_id列）： trx_id：每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的事务id赋值给trx_id隐藏列。 roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。
（补充点：undo日志：为了实现事务的原子性，InnoDB存储引擎在实际进行增、删、改一条记录时，都需要先把对应的undo日志记下来。一般每对一条记录做一次改动，就对应着一条undo日志，但在某些更新记录的操作中，也可能会对应着2条undo日志。一个事务在执行过程中可能新增、删除、更新若干条记录，也就是说需要记录很多条对应的undo日志，这些undo日志会被从0开始编号，也就是说根据生成的顺序分别被称为第0号undo日志、第1号undo日志、...、第n号undo日志等，这个编号也被称之为undo no。）
为了说明这个问题，我们创建一个演示表
CREATE TABLE teacher (
number INT,
name VARCHAR(100),
domain varchar(100),
PRIMARY KEY (number)
) Engine=InnoDB CHARSET=utf8;
然后向这个表里插入一条数据：
INSERT INTO teacher VALUES(1, '李瑾', 'JVM系列');
现在表里的数据就是这样的：
假设插入该记录的事务id为60，那么此刻该条记录的示意图如下所示：
假设之后两个事务id分别为80、120的事务对这条记录进行UPDATE操作，操作流程如下：
每次对记录进行改动，都会记录一条undo日志，每条undo日志也都有一个roll_pointer属性（INSERT操作对应的undo日志没有该属性，因为该记录并没有更早的版本），可以将这些undo日志都连起来，串成一个链表，所以现在的情况就像下图一样：
对该记录每次更新后，都会将旧值放到一条undo日志中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被roll_pointer属性连接成一个链表，我们把这个链表称之为版本链，版本链的头节点就是当前记录最新的值。另外，每个版本中还包含生成该版本时对应的事务id。于是可以利用这个记录的版本链来控制并发事务访问相同记录的行为，那么这种机制就被称之为多版本并发控制(Mulit-Version Concurrency Control MVCC)。
1.5.1.3.ReadView
必须要知道的概念（作用于SQL查询语句）
对于使用READ UNCOMMITTED隔离级别的事务来说，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本就好了（所以就会出现脏读、不可重复读、幻读）。
对于使用SERIALIZABLE隔离级别的事务来说，InnoDB使用加锁的方式来访问记录（也就是所有的事务都是串行的，当然不会出现脏读、不可重复读、幻读）。
对于使用READ COMMITTED和REPEATABLE READ隔离级别的事务来说，都必须保证读到已经提交了的事务修改过的记录，也就是说假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的，核心问题就是：READ COMMITTED和REPEATABLE READ隔离级别在不可重复读和幻读上的区别是从哪里来的，其实结合前面的知识，这两种隔离级别关键是需要判断一下版本链中的哪个版本是当前事务可见的。
为此，InnoDB提出了一个ReadView的概念（作用于SQL查询语句），
这个ReadView中主要包含4个比较重要的内容： **m_ids：**表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。 **min_trx_id：**表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。 **max_trx_id：**表示生成ReadView时系统中应该分配给下一个事务的id值。注意max_trx_id并不是m_ids中的最大值，事务id是递增分配的。比方说现在有id为1，2，3这三个事务，之后id为3的事务提交了。那么一个新的读事务在生成ReadView时，m_ids就包括1和2，min_trx_id的值就是1，max_trx_id的值就是4。 **creator_trx_id：**表示生成该ReadView的事务的事务id。
1.5.1.4.READ COMMITTED
脏读问题的解决
READ COMMITTED隔离级别的事务在每次查询开始时都会生成一个独立的ReadView。
在MySQL中，READ COMMITTED和REPEATABLE READ隔离级别的的一个非常大的区别就是它们生成ReadView的时机不同。 我们还是以表teacher 为例，假设现在表teacher 中只有一条由事务id为60的事务插入的一条记录，接下来看一下READ COMMITTED和REPEATABLE READ所谓的生成ReadView的时机不同到底不同在哪里。 READ COMMITTED —— 每次读取数据前都生成一个ReadView 比方说现在系统里有两个事务id分别为80、120的事务在执行：Transaction 80
UPDATE teacher SET name = '马' WHERE number = 1;
UPDATE teacher SET name = '连' WHERE number = 1;
...
此刻，表teacher 中number为1的记录得到的版本链表如下所示：
假设现在有一个使用READ COMMITTED隔离级别的事务开始执行：
使用READ COMMITTED隔离级别的事务
BEGIN;
SELECE1：Transaction 80、120未提交
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'李瑾'
第1次select的时间点 如下图：
这个SELECE1的执行过程如下： 在执行SELECT语句时会先生成一个ReadView：
ReadView的m_ids列表的内容就是[80, 120]，min_trx_id为80，max_trx_id为121，creator_trx_id为0。
然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列name的内容是'连'，该版本的trx_id值为80，在m_ids列表内，所以不符合可见性要求（trx_id属性值在ReadView的min_trx_id和max_trx_id之间说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问），根据roll_pointer跳到下一个版本。 下一个版本的列name的内容是'马'，该版本的trx_id值也为80，也在m_ids列表内，所以也不符合要求，继续跳到下一个版本。 下一个版本的列name的内容是'李瑾'，该版本的trx_id值为60，小于ReadView中的min_trx_id值，所以这个版本是符合要求的，最后返回给用户的版本就是这条列name为'李瑾'的记录。
所以有了这种机制，就不会发生脏读问题！因为会去判断活跃版本，必须是不在活跃版本的才能用，不可能读到没有 commit的记录。
不可重复读问题
然后，我们把事务id为80的事务提交一下，然后再到事务id为120的事务中更新一下表teacher 中number为1的记录：
Transaction120
BEGIN;
更新了一些别的表的记录
UPDATE teacher SET name = '严' WHERE number = 1;
UPDATE teacher SET name = '晁' WHERE number = 1;
此刻，表teacher 中number为1的记录的版本链就长这样：
然后再到刚才使用READ COMMITTED隔离级别的事务中继续查找这个number为1的记录，如下：
使用READ COMMITTED隔离级别的事务
BEGIN;
SELECE1：Transaction 80、120均未提交
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'李瑾'
SELECE2：Transaction 80提交，Transaction 120未提交
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'连'
第2次select的时间点 如下图：
这个SELECE2的执行过程如下：
SELECT * FROM teacher WHERE number = 1;
在执行SELECT语句时会又会单独生成一个ReadView，该ReadView信息如下：
m_ids列表的内容就是[120]（事务id为80的那个事务已经提交了，所以再次生成快照时就没有它了），min_trx_id为120，max_trx_id为121，creator_trx_id为0。 然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列name的内容是'晁'，该版本的trx_id值为120，在m_ids列表内，所以不符合可见性要求，根据roll_pointer跳到下一个版本。 下一个版本的列name的内容是'严'，该版本的trx_id值为120，也在m_ids列表内，所以也不符合要求，继续跳到下一个版本。 下一个版本的列name的内容是'连'，该版本的trx_id值为80，小于ReadView中的min_trx_id值120，所以这个版本是符合要求的，最后返回给用户的版本就是这条列name为'连'的记录。
以此类推，如果之后事务id为120的记录也提交了，再次在使用READ COMMITTED隔离级别的事务中查询表teacher 中number值为1的记录时，得到的结果就是'晁'了，具体流程我们就不分析了。
但会出现不可重复读问题。
明显上面一个事务中两次
1.5.1.5.REPEATABLE READ
REPEATABLE READ解决不可重复读问题
REPEATABLE READ —— 在第一次读取数据时生成一个ReadView
对于使用REPEATABLE READ隔离级别的事务来说，只会在第一次执行查询语句时生成一个ReadView，之后的查询就不会重复生成了。我们还是用例子看一下是什么效果。
比方说现在系统里有两个事务id分别为80、120的事务在执行：Transaction 80
UPDATE teacher SET name = '马' WHERE number = 1;
UPDATE teacher SET name = '连' WHERE number = 1;
...
此刻，表teacher 中number为1的记录得到的版本链表如下所示：
假设现在有一个使用REPEATABLE READ隔离级别的事务开始执行：
使用READ COMMITTED隔离级别的事务
BEGIN;
SELECE1：Transaction 80、120未提交
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'李瑾'
这个SELECE1的执行过程如下： 在执行SELECT语句时会先生成一个ReadView：
ReadView的m_ids列表的内容就是[80, 120]，min_trx_id为80，max_trx_id为121，creator_trx_id为0。
然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列name的内容是'连'，该版本的trx_id值为80，在m_ids列表内，所以不符合可见性要求（trx_id属性值在ReadView的min_trx_id和max_trx_id之间说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问），根据roll_pointer跳到下一个版本。 下一个版本的列name的内容是'马'，该版本的trx_id值也为80，也在m_ids列表内，所以也不符合要求，继续跳到下一个版本。 下一个版本的列name的内容是'李瑾'，该版本的trx_id值为60，小于ReadView中的min_trx_id值，所以这个版本是符合要求的，最后返回给用户的版本就是这条列name为'李瑾'的记录。 之后，我们把事务id为80的事务提交一下，然后再到事务id为120的事务中更新一下表teacher 中number为1的记录：
Transaction120
BEGIN;
更新了一些别的表的记录
UPDATE teacher SET name = '严' WHERE number = 1;
UPDATE teacher SET name = '晁' WHERE number = 1;
此刻，表teacher 中number为1的记录的版本链就长这样：
然后再到刚才使用REPEATABLE READ隔离级别的事务中继续查找这个number为1的记录，如下：
使用READ COMMITTED隔离级别的事务
BEGIN;
SELECE1：Transaction 80、120均未提交
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'李瑾'
SELECE2：Transaction 80提交，Transaction 120未提交
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'李瑾'
这个SELECE2的执行过程如下：
因为当前事务的隔离级别为REPEATABLE READ，而之前在执行SELECE1时已经生成过ReadView了，所以此时直接复用之前的ReadView，之前的ReadView的m_ids列表的内容就是[80, 120]，min_trx_id为80，max_trx_id为121，creator_trx_id为0。
根据前面的分析，返回的值还是'李瑾'。
也就是说两次SELECT查询得到的结果是重复的，记录的列name值都是'李瑾'，这就是可重复读的含义。
总结一下就是：
ReadView中的比较规则(前两条)
1、如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
2、如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
1.5.1.6.MVCC下的幻读解决和幻读现象
前面我们已经知道了，REPEATABLE READ隔离级别下MVCC可以解决不可重复读问题，那么幻读呢？MVCC是怎么解决的？幻读是一个事务按照某个相同条件多次读取记录时，后读取时读到了之前没有读到的记录，而这个记录来自另一个事务添加的新记录。 我们可以想想，在REPEATABLE READ隔离级别下的事务T1先根据某个搜索条件读取到多条记录，然后事务T2插入一条符合相应搜索条件的记录并提交，然后事务T1再根据相同搜索条件执行查询。结果会是什么？按照ReadView中的比较规则(后两条)： 3、如果被访问版本的trx_id属性值大于或等于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。 4、如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间(min_trx_id < trx_id < max_trx_id)，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。
不管事务T2比事务T1是否先开启，事务T1都是看不到T2的提交的。请自行按照上面介绍的版本链、ReadView以及判断可见性的规则来分析一下。 但是，在REPEATABLE READ隔离级别下InnoDB中的MVCC 可以很大程度地避免幻读现象，而不是完全禁止幻读。怎么回事呢？我们来看下面的情况：
我们首先在事务T1中：
select * from teacher where number = 30;
很明显，这个时候是找不到number = 30的记录的。 我们在事务T2中，执行：
insert into teacher values(30,'豹','数据湖');
通过执行insert into teacher values(30,'豹','数据湖');，我们往表中插入了一条number = 30的记录。 此时回到事务T1，执行：
update teacher set domain='RocketMQ' where number=30;
select * from teacher where number = 30;
嗯，怎么回事？事务T1很明显出现了幻读现象。 在REPEATABLE READ隔离级别下，T1第一次执行普通的SELECT 语句时生成了一个ReadView（但是版本链没有），之后T2向teacher 表中新插入一条记录并提交，然后T1也进行了一个update语句。 ReadView并不能阻止T1执行UPDATE 或者DELETE 语句来改动这个新插入的记录，但是这样一来，这条新记录的trx_id隐藏列的值就变成了T1的事务id。
之后T1再使用普通的SELECT 语句去查询这条记录时就可以看到这条记录了，也就可以把这条记录返回给客户端。因为这个特殊现象的存在，我们也可以认为MVCC 并不能完全禁止幻读（就是第一次读如果是空的情况，且在自己事务中进行了该条数据的修改）。
1.5.1.7.MVCC小结
从上边的描述中我们可以看出来，所谓的MVCC（Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用READ COMMITTD、REPEATABLE READ这两种隔离级别的事务在执行普通的SELECT操作时访问记录的版本链的过程，这样子可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。
READ COMMITTD、REPEATABLE READ这两个隔离级别的一个很大不同就是：生成ReadView的时机不同，READ COMMITTD在每一次进行普通SELECT操作前都会生成一个ReadView，而REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView就好了，从而基本上可以避免幻读现象（就是第一次读如果ReadView是空的情况中的某些情况则避免不了）。
另外，所谓的MVCC只是在我们进行普通的SEELCT查询时才生效，截止到目前我们所见的所有SELECT语句都算是普通的查询，至于什么是个不普通的查询，后面马上就会讲到（锁定读）。
1.6. MySQL中的锁
InnoDB中锁非常多，总的来说，可以如下分类：
这些锁都是做什么的？具体含义是什么？我们现在来一一学习。
1.6.1.解决并发事务问题
我们已经知道事务并发执行时可能带来的各种问题，最大的一个难点是：一方面要最大程度地利用数据库的并发访问，另外一方面还要确保每个用户能以一致的方式读取和修改数据，尤其是一个事务进行读取操作，另一个同时进行改动操作的情况下。
1.6.2.并发事务问题
一个事务进行读取操作，另一个进行改动操作,我们前边说过，这种情况下可能发生脏读、不可重复读、幻读的问题。
怎么解决脏读、不可重复读、幻读这些问题呢？其实有两种可选的解决方案：
1.6.2.1.方案一：读操作MVCC，写操作进行加锁
事务利用MVCC进行的读取操作称之为一致性读，或者一致性无锁读，也称之为快照读，但是往往读取的是历史版本数据。所有普通的SELECT语句（plain SELECT）在READ COMMITTED、REPEATABLE READ隔离级别下都算是一致性读。
一致性读并不会对表中的任何记录做加锁操作，其他事务可以自由的对表中的记录做改动。
很明显，采用MVCC方式的话，读-写操作彼此并不冲突，性能更高，采用加锁方式的话，读-写操作彼此需要排队执行，影响性能。一般情况下我们当然愿意采用MVCC来解决读-写操作并发执行的问题，但是业务在某些情况下，要求必须采用加锁的方式执行。
1.6.2.2方案二：读、写操作都采用加锁的方式
适用场景：
业务场景不允许读取记录的旧版本，而是每次都必须去读取记录的最新版本，
比方在银行存款的事务中，你需要先把账户的余额读出来，然后将其加上本次存款的数额，最后再写到数据库中。在将账户余额读取出来后，就不想让别的事务再访问该余额，直到本次存款事务执行完成，其他事务才可以访问账户的余额。这样在读取记录的时候也就需要对其进行加锁操作，这样也就意味着读操作和写操作也像写-写操作那样排队执行。
脏读的产生是因为当前事务读取了另一个未提交事务写的一条记录，如果另一个事务在写记录的时候就给这条记录加锁，那么当前事务就无法继续读取该记录了，所以也就不会有脏读问题的产生了。
不可重复读的产生是因为当前事务先读取一条记录，另外一个事务对该记录做了改动之后并提交之后，当前事务再次读取时会获得不同的值，如果在当前事务读取记录时就给该记录加锁，那么另一个事务就无法修改该记录，自然也不会发生不可重复读了。
幻读问题的产生是因为当前事务读取了一个范围的记录，然后另外的事务向该范围内插入了新记录，当前事务再次读取该范围的记录时发现了新插入的新记录，我们把新插入的那些记录称之为幻影记录。采用加锁的方式解决幻读问题就有不太容易了，因为当前事务在第一次读取记录时那些幻影记录并不存在，所以读取的时候加锁就有点麻烦—— 因为并不知道给谁加锁。InnoDB中是如何解决的，我们后面会讲到。
1.6.3锁定读（LockingReads）/LBCC
也称当前读, 读取的是最新版本, 并且对读取的记录加锁, 阻塞其他事务同时改动相同记录，避免出现安全问题。
哪些是当前读呢？select lock in share mode (共享锁)、select for update (排他锁)、update (排他锁)、insert (排他锁/独占锁)、delete (排他锁)、串行化事务隔离级别都是当前读。
当前读这种实现方式，也可以称之为LBCC（基于锁的并发控制，Lock-Based Concurrency Control），怎么做到？
1.6.3.1. 共享锁和独占锁
在使用加锁的方式解决问题时，由于既要允许读-读情况不受影响，又要使写-写、读-写或写-读情况中的操作相互阻塞，MySQL中的锁有好几类：
共享锁英文名：Shared Locks，简称S锁。在事务要读取一条记录时，需要先获取该记录的S锁。
假如事务E1首先获取了一条记录的S锁之后，事务E2接着也要访问这条记录：
如果事务E2想要再获取一个记录的S锁，那么事务E2也会获得该锁，也就意味着事务E1和E2在该记录上同时持有S锁。
独占锁，也常称排他锁，英文名：Exclusive Locks，简称X锁。在事务要改动一条记录时，需要先获取该记录的X锁。
如果事务E2想要再获取一个记录的X锁，那么此操作会被阻塞，直到事务E1提交之后将S锁释放掉。
如果事务E1首先获取了一条记录的X锁之后，那么不管事务E2接着想获取该记录的S锁还是X锁都会被阻塞，直到事务E1提交。
所以我们说S锁和S锁是兼容的，S锁和X锁是不兼容的，X锁和X锁也是不兼容的，画个表表示一下就是这样：
X 不兼容X 不兼容S
S 不兼容X 兼容S
1.6.3.2.锁定读的SELECT语句
MySQ有两种比较特殊的SELECT语句格式：
SELECT * from test LOCK IN SHARE MODE;
一个事务中开启S锁
另一个事务中开启S锁，可以读
如果另外一个事务中开启X锁，阻塞！
也就是在普通的SELECT语句后边加LOCK IN SHARE MODE，如果当前事务执行了该语句，那么它会为读取到的记录加S锁，这样允许别的事务继续获取这些记录的S锁（比方说别的事务也使用SELECT ... LOCK IN SHARE MODE语句来读取这些记录），但是不能获取这些记录的X锁（比方说使用SELECT ... FOR UPDATE语句来读取这些记录，或者直接修改这些记录）。
如果别的事务想要获取这些记录的X锁，那么它们会阻塞，直到当前事务提交之后将这些记录上的S锁释放掉。
对读取的记录加X锁：
SELECT * from test FOR UPDATE;
也就是在普通的SELECT语句后边加FOR UPDATE，如果当前事务执行了该语句，那么它会为读取到的记录加X锁，这样既不允许别的事务获取这些记录的S锁（比方说别的事务使用SELECT ... LOCK IN SHARE MODE语句来读取这些记录），也不允许获取这些记录的X锁（比如说使用SELECT ... FOR UPDATE语句来读取这些记录，或者直接修改这些记录）。
一个事务中开启X锁
另外一个事务中的X锁阻塞
除非第一个事务提交
另外一个事务才能获得X锁
同样如果另外一个事务执行X锁，使用S锁也不行
如果别的事务想要获取这些记录的S锁或者X锁，那么它们会阻塞，直到当前事务提交之后将这些记录上的X锁释放掉。
1.2.1.3. 写操作的锁
平常所用到的写操作无非是DELETE、UPDATE、INSERT这三种：
DELETE：
对一条记录做DELETE操作的过程其实是先在B+树中定位到这条记录的位置，然后获取一下这条记录的X锁，然后再执行delete mark操作。我们也可以把这个定位待删除记录在B+树中位置的过程看成是一个获取X锁的锁定读。
INSERT：
一般情况下，新插入一条记录的操作并不加锁，InnoDB通过一种称之为隐式锁来保护这条新插入的记录在本事务提交前不被别的事务访问。当然，在一些特殊情况下INSERT操作也是会获取锁的，具体情况我们后边再说。
UPDATE：
在对一条记录做UPDATE操作时分为三种情况：
1、如果未修改该记录的键值并且被更新的列占用的存储空间在修改前后未发生变化，则先在B+树中定位到这条记录的位置，然后再获取一下记录的X锁，最后在原记录的位置进行修改操作。其实我们也可以把这个定位待修改记录在B+树中位置的过程看成是一个获取X锁的锁定读。
2、如果未修改该记录的键值并且至少有一个被更新的列占用的存储空间在修改前后发生变化，则先在B+树中定位到这条记录的位置，然后获取一下记录的X锁，将该记录彻底删除掉（就是把记录彻底移入垃圾链表），最后再插入一条新记录。这个定位待修改记录在B+树中位置的过程看成是一个获取X锁的锁定读，新插入的记录由INSERT操作提供的隐式锁进行保护。
3、如果修改了该记录的键值，则相当于在原记录上做DELETE操作之后再来一次INSERT操作，加锁操作就需要按照DELETE和INSERT的规则进行了。
1.6.4锁的粒度
我们前边提到的锁都是针对记录的，也可以被称之为行级锁或者行锁，对一条记录加锁影响的也只是这条记录而已，我们就说这个锁的粒度比较细；其实一个事务也可以在表级别进行加锁，自然就被称之为表级锁或者表锁，对一个表加锁影响整个表中的记录，我们就说这个锁的粒度比较粗。给表加的锁也可以分为共享锁（S锁）和独占锁（X锁）
1.6.4.1.表锁与行锁的比较
锁定粒度：表锁 > 行锁
加锁效率：表锁 > 行锁
冲突概率：表锁 > 行锁
并发性能：表锁 < 行锁
1.6.4.2.给表加S锁
如果一个事务给表加了S锁，那么：
别的事务可以继续获得该表的S锁
别的事务可以继续获得该表中的某些记录的S锁
别的事务不可以继续获得该表的X锁
别的事务不可以继续获得该表中的某些记录的X锁
1.6.4.3.给表加X锁
如果一个事务给表加了X锁（意味着该事务要独占这个表），那么：
别的事务不可以继续获得该表的S锁
别的事务不可以继续获得该表中的某些记录的S锁
别的事务不可以继续获得该表的X锁
别的事务不可以继续获得该表中的某些记录的X锁。
为了更好的理解这个表级别的S锁和X锁和后面的意向锁，我们举一个现实生活中的例子。我们用曾经很火爆的互联网风口项目共享Office来说明加锁：
共享Office有栋大楼，楼自然有很多层。办公室都是共享的，客户可以随便选办公室办公。每层楼可以容纳客户同时办公，每当一个客户进去办公，就相当于在每层的入口处挂了一把S锁，如果很多客户进去办公，相当于每层的入口处挂了很多把S锁（类似行级别的S锁）。
有的时候楼层会进行检修，比方说换地板，换天花板，检查水电啥的，这些维修项目并不能同时开展。如果楼层针对某个项目进行检修，就不允许客户来办公，也不允许其他维修项目进行，此时相当于楼层门口会挂一把X锁（类似行级别的X锁）。
上边提到的这两种锁都是针对楼层而言的，不过有时候我们会有一些特殊的需求：
A、有投资人要来考察Office的环境。
投资人和公司并不想影响客户进去办公，但是此时不能有楼层进行检修，所以可以在大楼门口放置一把S锁（类似表级别的S锁）。此时：
来办公的客户们看到大楼门口有S锁，可以继续进入大楼办公。
修理工看到大楼门口有S锁，则先在大楼门口等着，啥时候投资人走了，把大楼的S锁撤掉再进入大楼维修。
B、公司要和房东谈条件。
此时不允许大楼中有正在办公的楼层，也不允许对楼层进行维修。所以可以在大楼门口放置一把X锁（类似表级别的X锁）。此时：
来办公的客户们看到大楼门口有X锁，则需要在大楼门口等着，啥时候条件谈好，把大楼的X锁撤掉再进入大楼办公。
修理工看到大楼门口有X锁，则先在大楼门口等着，啥时候谈判结束，把大楼的X锁撤掉再进入大楼维修。
1.6.5.意向锁
但是在上面的例子这里头有两个问题：
如果我们想对大楼整体上S锁，首先需要确保大楼中的没有正在维修的楼层，如果有正在维修的楼层，需要等到维修结束才可以对大楼整体上S锁。
如果我们想对大楼整体上X锁，首先需要确保大楼中的没有办公的楼层以及正在维修的楼层，如果有办公的楼层或者正在维修的楼层，需要等到全部办公的同学都办公离开，以及维修工维修完楼层离开后才可以对大楼整体上X锁。
我们在对大楼整体上锁（表锁）时，怎么知道大楼中有没有楼层已经被上锁（行锁）了呢？依次检查每一楼层门口有没有上锁？那这效率也太慢了吧！于是InnoDB提出了一种意向锁（英文名：Intention Locks）：
意向共享锁 ，英文名：Intention Shared Lock，简称IS锁。当事务准备在某条记录上加S锁时，需要先在表级别加一个IS锁。
意向独占锁 ，英文名：Intention Exclusive Lock，简称IX锁。当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁。
视角回到大楼和楼层上来：
如果有客户到楼层中办公，那么他先在整栋大楼门口放一把IS锁（表级锁），然后再到楼层门口放一把S锁（行锁）。
如果有维修工到楼层中维修，那么它先在整栋大楼门口放一把IX锁（表级锁），然后再到楼层门口放一把X锁（行锁）。
之后：
如果有投资人要参观大楼，也就是想在大楼门口前放S锁（表锁）时，首先要看一下大楼门口有没有IX锁，如果有，意味着有楼层在维修，需要等到维修结束把IX锁撤掉后才可以在整栋大楼上加S锁。
如果有谈条件要占用大楼，也就是想在大楼门口前放X锁（表锁）时，首先要看一下大楼门口有没有IS锁或IX锁，如果有，意味着有楼层在办公或者维修，需要等到客户们办完公以及维修结束把IS锁和IX锁撤掉后才可以在整栋大楼上加X锁。
注意： 客户在大楼门口加IS锁时，是不关心大楼门口是否有IX锁的，维修工在大楼门口加IX锁时，是不关心大楼门口是否有IS锁或者其他IX锁的。IS和IX锁只是为了判断当前时间大楼里有没有被占用的楼层用的，也就是在对大楼加S锁或者X锁时才会用到。
总结一下：IS、IX锁是表级锁，它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录。就是说其实IS锁和IX锁是兼容的，IX锁和IX锁是兼容的。我们画个表来看一下表级别的各种锁的兼容性：
| 兼容性 | X | IX | S | IS |
|---|---|---|---|---|
| X | 不兼容 | 不兼容 | 不兼容 | 不兼容 |
| IX | 不兼容 | 不兼容 | ||
| S | 不兼容 | 不兼容 | ||
| IS | 不兼容 |
锁的组合性：（意向锁没有行锁）
| 组合性 | X | IX | S | IS |
|---|---|---|---|---|
| 表锁 | 有 | 有 | 有 | 有 |
| 行锁 | 有 | 有 |
1.6.6.MySQL中的行锁和表锁
MySQL支持多种存储引擎，不同存储引擎对锁的支持也是不一样的。当然，我们重点还是讨论InnoDB存储引擎中的锁，其他的存储引擎只是稍微看看。
1.6.6.1.其他存储引擎中的锁
对于MyISAM、MEMORY、MERGE这些存储引擎来说，它们只支持表级锁，而且这些引擎并不支持事务，所以使用这些存储引擎的锁一般都是针对当前会话来说的。比方说在Session 1中对一个表执行SELECT操作，就相当于为这个表加了一个表级别的S锁，如果在SELECT操作未完成时，Session 2中对这个表执行UPDATE操作，相当于要获取表的X锁，此操作会被阻塞，直到Session 1中的SELECT操作完成，释放掉表级别的S锁后，Session 2中对这个表执行UPDATE操作才能继续获取X锁，然后执行具体的更新语句。
因为使用MyISAM、MEMORY、MERGE这些存储引擎的表在同一时刻只允许一个会话对表进行写操作，所以这些存储引擎实际上最好用在只读，或者大部分都是读操作，或者单用户的情景下。 另外，在MyISAM存储引擎中有一个称之为Concurrent Inserts的特性，支持在对MyISAM表读取时同时插入记录，这样可以提升一些插入速度。关于更多Concurrent Inserts的细节，详情可以参考文档。
1.6.6.2.InnoDB存储引擎中的锁
InnoDB存储引擎既支持表锁，也支持行锁。表锁实现简单，占用资源较少，不过粒度很粗，有时候你仅仅需要锁住几条记录，但使用表锁的话相当于为表中的所有记录都加锁，所以性能比较差。行锁粒度更细，可以实现更精准的并发控制。下边我们详细看一下。
1.6.6.2.1.InnoDB中的表级锁
1.6.6.2.1.1.表级别的S锁、X锁、元数据锁
在对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，InnoDB存储引擎是不会为这个表添加表级别的S锁或者X锁的。
另外，在对某个表执行一些诸如ALTER TABLE、DROP TABLE这类的DDL语句时，其他事务对这个表并发执行诸如SELECT、INSERT、DELETE、UPDATE的语句会发生阻塞，同理，某个事务中对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，在其他会话中对这个表执行DDL语句也会发生阻塞。这个过程其实是通过在server层使用一种称之为元数据锁（英文名：Metadata Locks，简称MDL）来实现的，一般情况下也不会使用InnoDB存储引擎自己提供的表级别的S锁和X锁。
其实这个InnoDB存储引擎提供的表级S锁或者X锁是相当鸡肋，只会在一些特殊情况下，比方说崩溃恢复过程中用到。不过我们还是可以手动获取一下的，比方说在系统变量autocommit=0，innodb_table_locks = 1时，手动获取InnoDB存储引擎提供的表t的S锁或者X锁可以这么写：
LOCK TABLES t READ：InnoDB存储引擎会对表t加表级别的S锁。
LOCK TABLES t WRITE：InnoDB存储引擎会对表t加表级别的X锁。
请尽量避免在使用InnoDB存储引擎的表上使用LOCK TABLES这样的手动锁表语句，它们并不会提供什么额外的保护，只是会降低并发能力而已。
1.6.6.2.1.2.表级别的IS锁、IX锁
当我们在对使用InnoDB存储引擎的表的某些记录加S锁之前，那就需要先在表级别加一个IS锁，当我们在对使用InnoDB存储引擎的表的某些记录加X锁之前，那就需要先在表级别加一个IX锁。
IS锁和IX锁的使命只是为了后续在加表级别的S锁和X锁时判断表中是否有已经被加锁的记录，以避免用遍历的方式来查看表中有没有上锁的记录。我们并不能手动添加意向锁，只能由InnoDB存储引擎自行添加。
1.6.6.2.1.3.表级别的AUTO-INC锁
在使用MySQL过程中，我们可以为表的某个列添加AUTO_INCREMENT属性，之后在插入记录时，可以不指定该列的值，系统会自动为它赋上递增的值系统实现这种自动给AUTO_INCREMENT修饰的列递增赋值的原理主要是两个：
1、采用AUTO-INC锁，也就是在执行插入语句时就在表级别加一个AUTO-INC锁，然后为每条待插入记录的AUTO_INCREMENT修饰的列分配递增的值，在该语句执行结束后，再把AUTO-INC锁释放掉。这样一个事务在持有AUTO-INC锁的过程中，其他事务的插入语句都要被阻塞，可以保证一个语句中分配的递增值是连续的。
如果我们的插入语句在执行前不可以确定具体要插入多少条记录（无法预计即将插入记录的数量），比方说使用INSERT ... SELECT、REPLACE ... SELECT或者LOAD DATA这种插入语句，一般是使用AUTO-INC锁为AUTO_INCREMENT修饰的列生成对应的值。
2、采用一个轻量级的锁，在为插入语句生成AUTO_INCREMENT修饰的列的值时获取一下这个轻量级锁，然后生成本次插入语句需要用到的AUTO_INCREMENT列的值之后，就把该轻量级锁释放掉，并不需要等到整个插入语句执行完才释放锁。
如果我们的插入语句在执行前就可以确定具体要插入多少条记录，比方说我们上边举的关于表t的例子中，在语句执行前就可以确定要插入2条记录，那么一般采用轻量级锁的方式对AUTO_INCREMENT修饰的列进行赋值。这种方式可以避免锁定表，可以提升插入性能。
InnoDB提供了一个称之为innodb_autoinc_lock_mode的系统变量来控制到底使用上述两种方式中的哪种来为AUTO_INCREMENT修饰的列进行赋值，当innodb_autoinc_lock_mode值为0时，一律采用AUTO-INC锁；当innodb_autoinc_lock_mode值为1时，一律采用轻量级锁；当innodb_autoinc_lock_mode值为2时，两种方式混着来（也就是在插入记录数量确定时采用轻量级锁，不确定时使用AUTO-INC锁）。
不过当innodb_autoinc_lock_mode值为2时，可能会造成不同事务中的插入语句为AUTO_INCREMENT修饰的列生成的值是交叉的，在有主从复制的场景中是不安全的。
show variables like 'innodb_autoinc_lock_mode' ;
MySQL5.7.X中缺省为1。
1.6.7.2.InnoDB中的行级锁
行锁，也称为记录锁，顾名思义就是在记录上加的锁。但是要注意，这个记录指的是通过给索引上的索引项加锁。InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁。
不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。
同时当我们用范围条件而不是相等条件检索数据，并请求锁时，InnoDB会给符合条件的已有数据记录的索引项加锁。
不过即使是行锁，InnoDB里也是分成了各种类型的。换句话说即使对同一条记录加行锁，如果类型不同，起到的功效也是不同的。我们使用前面的teacher，增加一个索引，并插入几条记录。
INDEX `idx_number`(`number`)
我们来看看都有哪些常用的行锁类型。
Record Locks
也叫记录锁，就是仅仅把一条记录锁上，官方的类型名称为：LOCK_REC_NOT_GAP。比方说我们把number值为6的那条记录加一个记录锁的示意图如下：
记录锁是有S锁和X锁之分的，当一个事务获取了一条记录的S型记录锁后，其他事务也可以继续获取该记录的S型记录锁，但不可以继续获取X型记录锁；当一个事务获取了一条记录的X型记录锁后，其他事务既不可以继续获取该记录的S型记录锁，也不可以继续获取X型记录锁；
Gap Locks
我们说MySQL在REPEATABLE READ隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用MVCC方案解决，也可以采用加锁方案解决。但是在使用加锁方案解决时有问题，就是事务在第一次执行读取操作时，那些幻影记录尚不存在，我们无法给这些幻影记录加上记录锁。InnoDB提出了一种称之为Gap Locks的锁，官方的类型名称为：LOCK_GAP，我们也可以简称为gap锁。
间隙锁实质上是对索引前后的间隙上锁，不对索引本身上锁。
会话1开启一个事务，执行
begin;
update teacher set domain ='JVM' where number='6';
会对2~6之间和6到10之间进行上锁。
如图中为2~6和 6 ~ 10的记录加了gap锁，意味着不允许别的事务在这条记录前后间隙插入新记录。
begin;
insert into teacher value(7,'晁','docker');
为什么不能插入？因为记录（7,'晁','docker'）要 插入的话，在索引idx_number上，刚好落在6 ~ 10之间，是有锁的，当然不允许插入。
但是当SQL语句变为：insert into teacher value(70,'晁','docker');能插入吗？
当然能，因为70这条记录不在被锁的区间内。
1.6.7.死锁
1.6.7.1.概念
是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁。
举个例子：A和B去按摩洗脚，都想在洗脚的时候，同时顺便做个头部按摩，13技师擅长足底按摩，14擅长头部按摩。
这个时候A先抢到14，B先抢到13，两个人都想同时洗脚和头部按摩，于是就互不相让，扬言我死也不让你，这样的话，A抢到14，想要13，B抢到13，想要14，在这个想同时洗脚和头部按摩的事情上A和B就产生了死锁。怎么解决这个问题呢？
第一种，假如这个时候，来了个15，刚好也是擅长头部按摩的，A又没有两个脑袋，自然就归了B，于是B就美滋滋的洗脚和做头部按摩，剩下A在旁边气鼓鼓的，这个时候死锁这种情况就被打破了，不存在了。
第二种，C出场了，用武力强迫A和B，必须先做洗脚，再头部按摩，这种情况下，A和B谁先抢到13，谁就可以进行下去，另外一个没抢到的，就等着，这种情况下，也不会产生死锁。
所以总结一下：
死锁是必然发生在多操作者（M>=2个）情况下，争夺多个资源（N>=2个，且N<=M）才会发生这种情况。很明显，单线程自然不会有死锁，只有B一个去，不要2个，打十个都没问题；单资源呢？只有13，A和B也只会产生激烈竞争，打得不可开交，谁抢到就是谁的，但不会产生死锁。同时，死锁还有几个要求，1、争夺资源的顺序不对，如果争夺资源的顺序是一样的，也不会产生死锁；
2、争夺者拿到资源不放手。
1.6.7.2.MySQL中的死锁
MySQL中的死锁的成因是一样的。
会话1：
begin;
select * from
teacher where number = 1 for update;
会话2：
begin;
select * from
teacher where number = 3 for update;
会话1
select * from teacher where number = 3 for
update;
可以看到这个语句的执行将会被阻塞
会话2 ：
select * from
teacher where number = 1 for update;
MySQL检测到了死锁，并结束了会话2中事务的执行，此时，切回会话1，发现原本阻塞的SQL语句执行完成了。
同时通过
show engine innodb status\G
可以看见死锁的详细情况：
查看事务加锁的情况，不过一般情况下，看不到哪个事务对哪些记录加了那些锁，需要修改系统变量innodb_status_output_locks（MySQL5.6.16引入），缺省是OFF。
show
variables like 'innodb_status_output_locks';
我们需要设置为ON，
set global
innodb_status_output_locks = ON;
然后开启事务，并执行语句
1.范式
工作经验足的话，大部分建表都能满足前三范式。
- 第一范式：数据项不可再分；
- 第二范式：要求实体的属性完全依赖于主关键字；
- 第三范式：要求一个数据库表中不包含已在其它表中包含的非主关键字信息，即数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。
2.反范式
反范式设计是指为了达到更快的更新目的，把一条行记录拆成几条，作为桶并发更新，避免了mysql的互斥写锁行锁的劣势。
3.字段数据类型优化
老生常谈，能不null的字段就别null，占用高效率低；字段的规定范围尽可能准确；留意一些字段可以用enum（枚举）来创建，比如性别等固定几个特定值的字段；
4.MysQL的索引
常见的索引：B+树索引，哈希索引，全文索引； 常见树：B树、B+树、B**树；
5.b+树
核心是二分查找，结构类似平衡二叉树，根据键值快速查找数据。 二叉树是n个结点构成的有限集合。包含Root、子树等； 平衡二叉树，又称AVL树； 作用总结： 1.可以有效存储数据； 2.所有记录都存储在叶子节点上，非叶子存储的都是索引信息，并且从小到大排列好了； 3.有非常高的扇出，可以有效减少IO操作 扇出：是每个索引节点(Non-LeafPage)指向每个叶子节点(LeafPage)的指针； 扇出数 = 索引节点(Non-LeafPage)可存储的最大关键字个数 + 1；
mysql 哈希索引
在MySQL中，哈希索引是一种特殊类型的索引，用于提高特定类型查询的性能。与传统的B树索引不同，哈希索引将索引列的值通过哈希函数转换为哈希码，并将其存储在内存中的哈希表中。哈希表是一个基于哈希算法的数据结构，它可以通过哈希码快速定位和访问索引数据。
哈希索引适用于等值查询（例如通过主键进行的查找）的情况，而不适用于范围查询或排序操作。当执行一个等值查询时，MySQL会计算查询条件的哈希码，并使用哈希表直接定位到相应的索引数据。这种查找方式通常非常高效，因为它具有固定的查询时间复杂度（O(1)）。
然而，哈希索引也有一些限制和考虑事项：
- 哈希索引只适用于等值查询，而不支持范围查询、排序或部分匹配等操作。
- 哈希索引对于索引列的数据分布非常敏感。如果数据分布不均匀，可能会导致哈希冲突，影响查询性能。
- 哈希索引只能存储在内存中，无法利用磁盘空间。如果数据量很大，无法完全存储在内存中，哈希索引的效果会大打折扣。
- 哈希索引不支持部分索引，即无法仅对索引列的一部分进行哈希索引。
- 哈希索引在进行插入、删除和更新操作时，需要重新计算哈希码和维护哈希表，可能会导致性能开销。
要在MySQL中创建哈希索引，可以使用HASH索引类型。例如，创建一个名为idx_hash的哈希索引可以使用以下语法：
CREATE INDEX idx_hash ON your_table (your_column) USING HASH;
需要注意的是，哈希索引在不同的MySQL版本和存储引擎中的支持程度可能会有所不同。此外，哈希索引的创建和使用需要权衡数据的查询模式、数据分布和性能需求，以确保其在特定场景下能够带来性能改进。
MYSQL的索引
回表：通过辅助索引获得主键，然后再通过主键索引来找到一条完整的记录。二级索引（普通的索引），查询数据时会避免回表，优化的时候能尽量减少回表就减少回表。
联合索引
将表上多个列组合起来就是联合索引。
1.MySQL体系架构
1.1.MySQL的分支与变种
MySQL变种有好几个，主要有三个久经考验的主流变种：Percona Server，MariaDB和 Drizzle。它们都有活跃的用户社区和一些商业支持，均由独立的服务供应商支持。同时还有几个优秀的开源关系数据库，值得我们了解一下。
1.1.1.Drizzle
Drizzle是真正的MySQL分支，而且是完全开源的产品，而非只是个变种或增强版本。它并不与MySQL兼容不能简单地将MySQL后端替换为Drizzle。
Drizzle与MySQL有很大差别，进行了一些重大更改，甚至SQL语法的变化都非常大，设计目标之一是提供一种出色的解决方案来解决高可用性问题。在实现上，Drizzle清除了一些表现不佳和不必要的功能，将很多代码重写，对它们进行了优化，甚至将所用语言从C换成了C++。
此外，Drizzle另一个设计目标是能很好的适应具有大量内容的多核服务器、运行Linux的64位机器、云计算中使用的服务器、托管网站的服务器和每分钟接收数以万计点击率的服务器并且大幅度的削减服务器成本。
1.1.2.MariaDB
在Sun收购MySQL后，Monty Widenius，这位MySQL的创建者，因不认同MySQL开发流程而离开Sun。他成立了Monty程序公司，创立了MariaDB。MariaDB的目标是社区开发，Bug修复和许多的新特性实际上，可以将MariaDB视为MySQL的扩展集，它不仅提供MySQL提供的所有功能，还提供其他功能。MariaDB是原版MySQL的超集，因此已有的系统不需要任何修改就可以运行。
诸如Google，Facebook、维基百科等公司或者网站所使用了MariaDB。不过Monty公司不是以赢利为目的，而是由产品驱动的，这可能会带来问题，因为没有赢利的公司不一定能长久维持下去。
1.1.3.Percona Server
由领先的MySQL咨询公司Percona发布，Percona公司的口号就是“The Database Performance Experts”，Percona的创始人也就是《高性能MySQL》书的作者。
Percona Server是个与MySQL向后兼容的替代品，它尽可能不改变SQL语法、客户端/服务器协议和磁盘上的文件格式。任何运行在MySQL上的都可以运行在Percona Server上而不需要修改。切换到Percona Server只需要关闭MySQL和启动PerconaServer，不需要导出和重新导入数据。
Percona Server有三个主要的目标：透明，增加允许用户更紧密地查看服务器内部信息和行为的方法。比如慢查询日志中特别增加的详细信息；性能，Percona Server包含许多性能和可扩展性方面的改进，还加强了性能的可预测性和稳定性。其中主要集中于InnoDB；操作灵活性，Percona Server使操作人员和系统管理员在让MySQL作为架构的一部分而可靠并稳定运行时提供了很多便利。
一般来说，Percona Server中的许多特性会在后来的标准MySQL中出现。
国内公司阿里内部就运行了上千个Percona Server的实例。
1.2.MySQL的替代
1.2.1.Postgre SQL
PostgreSQL称自己是世界上最先进的开源数据库，同时也是个一专多长的全栈数据库。最初是1985年在加利福尼亚大学伯克利分校开发的。
PostgreSQL 的稳定性极强，在崩溃、断电之类的灾难场景下依然可以保证数据的正确；在高并发读写，负载逼近极限下，PostgreSQL的性能指标仍可以维持双曲线甚至对数曲线，到顶峰之后不再下降，表现的非常稳定，而 MySQL 明显出现一个波峰后下滑；
PostgreSQL多年来在GIS(地理信息)领域处于优势地位，因为它有丰富的几何类型，实际上不止几何类型，PostgreSQL有大量字典、数组、bitmap 等数据类型，相比之下mysql就差很多。所以总的来说，PostgreSQL更学术化一些，在绝对需要可靠性和数据完整性的时候，PostgreSQL是更好的选择。但是从商业支持、文档资料、易用性，第三方支持来说，MySQL无疑更好些。
1.2.2.SQLite
SQLite是世界上部署最广泛的数据库引擎，为物联网（IoT）下的数据库首选，并且是手机，PDA，甚至MP3播放器的下的首选。SQLite代码占用空间小，并且不需要数据库管理员的维护。SQLite没有单独的服务器进程，提供的事务也基本符合ACID。当然，简单也就意味着功能和性能受限。
2.MySql基础
2.1.MySQL体系架构
可以看出MySQL是由连接池、管理工具和服务、SQL接口、解析器、优化器、缓存、存储引擎、文件系统组成。
连接池
由于每次建立建立需要消耗很多时间，连接池的作用就是将这些连接缓存下来，下次可以直接用已经建立好的连接，提升服务器性能。
管理工具和服务
系统管理和控制工具，例如备份恢复、Mysql复制、集群等
SQL接口
接受用户的SQL命令，并且返回用户需要查询的结果。比如select ... from就是调用SQL接口
解析器
SQL命令传递到解析器的时候会被解析器验证和解析。解析器主要功能：1、将SQL语句分解成数据结构，后续步骤的传递和处理就是基于这个结构的。2、将SQL语句分解成数据结构，后续步骤的传递和处理就是基于这个结构的。
优化器
查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化。
缓存器
查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。
存储引擎(后面会细讲)
文件系统(后面会细讲)
2.1.1.连接层
当MySQL启动（MySQL服务器就是一个进程），等待客户端连接，每一个客户端连接请求，服务器进程会创建一个线程专门处理与这个客户端的交互。当客户端与该服务器断开之后，不会立即撤销线程，只会把他缓存起来等待下一个客户端请求连接的时候，将其分配给该客户端。每个线程独立，拥有各自的内存处理空间。
以下命令可以查看最大的连接数：
show VARIABLES like '%max_connections%'
连接到服务器，服务器需要对其进行验证，也就是用户名、IP、密码验证，一旦连接成功，还要验证是否具有执行某个特定查询的权限（例如，是否允许客户端对某个数据库某个表的某个操作）
2.1.2.Server层(SQL处理层)
这一层主要功能有：SQL语句的解析、优化，缓存的查询，MySQL内置函数的实现，跨存储引擎功能（所谓跨存储引擎就是说每个引擎都需提供的功能（引擎需对外提供接口）），例如：存储过程、触发器、视图等。
当然作为一个SQL的执行流程如下：
1.如果是查询语句（select语句），首先会查询缓存是否已有相应结果，有则返回结果，无则进行下一步（如果不是查询语句，同样调到下一步）
2.解析查询，创建一个内部数据结构（解析树），这个解析树主要用来SQL语句的语义与语法解析；
3.优化：优化SQL语句，例如重写查询，决定表的读取顺序，以及选择需要的索引等。这一阶段用户是可以查询的，查询服务器优化器是如何进行优化的，便于用户重构查询和修改相关配置，达到最优化。这一阶段还涉及到存储引擎，优化器会询问存储引擎，比如某个操作的开销信息、是否对特定索引有查询优化等。
2.1.2.1.缓存（了解即可）
show variables like '%query_cache_type%' -- 默认不开启
show variables like '%query_cache_size%' --默认值1M
SET GLOBAL query_cache_type = 1; --会报错
query_cache_type只能配置在my.cnf文件中！
缓存在生产环境建议不开启，除非经常有sql完全一模一样的查询
缓存严格要求2次SQL请求要完全一样，包括SQL语句，连接的数据库、协议版本、字符集等因素都会影响
从8.0开始，MySQL不再使用查询缓存，那么放弃它的原因是什么呢？
MySQL查询缓存是查询结果缓存。它将以SEL开头的查询与哈希表进行比较，如果匹配，则返回上一次查询的结果。进行匹配时，查询必须逐字节匹配，例如 SELECT * FROM e1; 不等于select * from e1;
此外，一些不确定的查询结果无法被缓存，任何对表的修改都会导致这些表的所有缓存无效。因此，适用于查询缓存的最理想的方案是只读，特别是需要检查数百万行后仅返回数行的复杂查询。如果你的查询符合这样一个特点，开启查询缓存会提升你的查询性能。
随着技术的进步，经过时间的考验，MySQL的工程团队发现启用缓存的好处并不多。
首先，查询缓存的效果取决于缓存的命中率，只有命中缓存的查询效果才能有改善，因此无法预测其性能。
其次，查询缓存的另一个大问题是它受到单个互斥锁的保护。在具有多个内核的服务器上，大量查询会导致大量的互斥锁争用。
通过基准测试发现，大多数工作负载最好禁用查询缓存(5.6的默认设置)：按照官方所说的：造成的问题比它解决问题要多的多，弊大于利就直接砍掉了。
2.1.3.存储引擎层
从体系结构图中可以发现，MySQL数据库区别于其他数据库的最重要的一个特点就是其插件式的表存储引擎。MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持，这些标准与存储引擎本身无关，可能是每个数据库系统本身都必需的，如SQL分析器和优化器等，而存储引擎是底层物理结构和实际文件读写的实现，每个存储引擎开发者可以按照自己的意愿来进行开发。需要特别注意的是，存储引擎是基于表的，而不是数据库。
插件式存储引擎的好处是，每个存储引擎都有各自的特点，能够根据具体的应用建立不同存储引擎表。由于MySQL数据库的开源特性，用户可以根据MySQL预定义的存储引擎接口编写自己的存储引擎。若用户对某一种存储引擎的性能或功能不满意，可以通过修改源码来得到想要的特性，这就是开源带给我们的方便与力量。
由于MySQL数据库开源特性，存储引擎可以分为MySQL官方存储引擎和第三方存储引擎。有些第三方存储引擎很强大，如大名鼎鼎的InnoDB存储引擎（最早是第三方存储引擎，后被Oracle收购)，其应用就极其广泛，甚至是MySQL数据库OLTP(Online Transaction Processing在线事务处理）应用中使用最广泛的存储引擎。
2.1.3.1.MySQL官方引擎概要
InnoDB存储引擎
InnoDB是MySQL的默认事务型引擎，也是最重要、使用最广泛的存储引擎。它被设计用来处理大量的短期(short-lived)事务，短期事务大部分情况是正常提交的，很少会被回滚。InnoDB的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很流行。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。如果要学习存储引擎，InnoDB也是一个非常好的值得花最多的时间去深入学习的对象，收益肯定比将时间平均花在每个存储引擎的学习上要高得多。所以InnoDB引擎也将是我们学习的重点。
MylSAM存储引擎
在MySQL 5.1及之前的版本，MyISAM是默认的存储引擎。MyISAM提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM不支持事务和行级锁，而且有一个毫无疑问的缺陷就是崩溃后无法安全恢复。尽管MyISAM引擎不支持事务、不支持崩溃后的安全恢复，但它绝不是一无是处的。对于只读的数据，或者表比较小、可以忍受修复（repair）操作，则依然可以继续使用MyISAM（但请不要默认使用MyISAM，而是应当默认使用InnoDB)。但是MyISAM对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁,写入时则对表加排他锁。MyISAM很容易因为表锁的问题导致典型的的性能问题。
Mrg_MylSAM
Merge存储引擎，是一组MyIsam的组合，也就是说，他将MyIsam引擎的多个表聚合起来，但是他的内部没有数据，真正的数据依然是MyIsam引擎的表中，但是可以直接进行查询、删除更新等操作。
Archive引擎
Archive存储引擎只支持INSERT和SELECT操作，在MySQL 5.1之前也不支持索引。Archive引擎会缓存所有的写并利用zlib对插入的行进行压缩，所以比MyISAM表的磁盘I/O更少。但是每次SELECT查询都需要执行全表扫描。所以Archive表适合日志和数据采集类应用，这类应用做数据分析时往往需要全表扫描。或者在一些需要更快速的INSERT操作的场合下也可以使用。Archive引擎不是一个事务型的引擎，而是一个针对高速插入和压缩做了优化的简单引擎。
Blackhole引擎
Blackhole引擎没有实现任何的存储机制，它会丢弃所有插入的数据，不做任何保存。但是服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者只是简单地记录到日志。这种特殊的存储引擎可以在一些特殊的复制架构和日志审核时发挥作用。但这种引擎在应用方式上有很多问题，因此并不推荐。
CSV引擎
CSV引擎可以将普通的CSV文件(逗号分割值的文件）作为MySQL的表来处理，但这种表不支持索引。CSV引擎可以在数据库运行时拷入或者拷出文件。可以将Excel等的数据存储为CSV文件，然后复制到MySQL数据目录下，就能在MySQL 中打开使用。同样，如果将数据写入到一个CSV引擎表，其他的外部程序也能立即从表的数据文件中读取CSV格式的数据。因此CSV引擎可以作为一种数据交换的机制，非常有用。
Federated引擎
Federated引擎是访问其他MySQL服务器的一个代理，它会创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，然后提取或者发送需要的数据。最初设计该存储引擎是为了和企业级数据库如Microsoft SQL Server和 Oracle的类似特性竞争的，可以说更多的是一种市场行为。尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。
Memory 引擎
如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表(以前也叫做HEAP表）是非常有用的。Memory表至少比MyISAM 表要快一个数量级，因为每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为frm类型。该文件中只存储表的结构。而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率，不需要进行磁盘I/O。所以Memory表的结构在重启以后还会保留，但数据会丢失。
Memory表支持 Hash索引，因此查找操作非常快。虽然Memory表的速度非常快，但还是无法取代传统的基于磁盘的表。Memroy表是表级锁，因此并发写入的性能较低。它不支持BLOB或TEXT类型的列，并且每行的长度是固定的，所以即使指定了VARCHAR 列，实际存储时也会转换成CHAR，这可能导致部分内存的浪费。
NDB集群引擎
使用MySQL服务器、NDB集群存储引擎，以及分布式的、share-nothing 的、容灾的、高可用的NDB数据库的组合，被称为MySQL集群（(MySQL Cluster)。
2.1.3.2.值得了解的第三方引擎
Percona的 XtraDB存储引擎
基于InnoDB引擎的一个改进版本，已经包含在Percona Server和 MariaDB中，它的改进点主要集中在性能、可测量性和操作灵活性方面。XtraDB可以作为InnoDB的一个完全的替代产品，甚至可以兼容地读写InnoDB的数据文件，并支持InnoDB的所有查询。
TokuDB引擎
使用了一种新的叫做分形树(Fractal Trees)的索引数据结构。该结构是缓存无关的，因此即使其大小超过内存性能也不会下降，也就没有内存生命周期和碎片的问题。TokuDB是一种大数据（Big Data)存储引擎，因为其拥有很高的压缩比，可以在很大的数据量上创建大量索引。现在该引擎也被Percona公司收购。
Tips ： 分形树，是一种写优化的磁盘索引数据结构。 分形树的写操作（Insert/Update/Delete）性能比较好，同时它还能保证读操作近似于B+树的读性能。据测试结果显示， TokuDB分形树的写性能优于InnoDB的B+树，读性能略低于B+树。分形树核心思想是利用节点的MessageBuffer缓存更新操作，充分利用数据局部性原理，将随机写转换为顺序写，这样极大的提高了随机写的效率。
Infobright
MySQL默认是面向行的，每一行的数据是一起存储的，服务器的查询也是以行为单位处理的。而在大数据量处理时，面向列的方式可能效率更高，比如HBASE就是面向列存储的。
Infobright是最有名的面向列的存储引擎。在非常大的数据量（数十TB)时，该引擎工作良好。Infobright是为数据分析和数据仓库应用设计的。数据高度压缩，按照块进行排序，每个块都对应有一组元数据。在处理查询时，访问元数据可决定跳过该块，甚至可能只需要元数据即可满足查询的需求。但该引擎不支持索引，不过在这么大的数据量级，即使有索引也很难发挥作用，而且块结构也是一种准索引 (quasi-index)。Infobright需要对MySQL服务器做定制，因为一些地方需要修改以适应面向列存储的需要。如果查询无法在存储层使用面向列的模式执行，则需要在服务器层转换成按行处理，这个过程会很慢。Infobright有社区版和商业版两个版本。
2.1.3.3.选择合适的引擎
这么多存储引擎，我们怎么选择?大部分情况下，InnoDB都是正确的选择，所以在MySQL 5.5版本将InnoDB作为默认的存储引擎了。对于如何选择存储引擎，可以简单地归纳为一句话:“除非需要用到某些InnoDB不具备的特性，并且没有其他办法可以替代，否则都应该优先选择InnoDB引擎”。比如，MySQL中只有MyISAM支持地理空间搜索。
当然，如果不需要用到InnoDB的特性，同时其他引擎的特性能够更好地满足需求，也可以考虑一下其他存储引擎。举个例子，如果不在乎可扩展能力和并发能力，也不在乎崩溃后的数据丢失问题，却对InnoDB的空间占用过多比较敏感，这种场合下选择MyISAM就比较合适。
除非万不得已，否则建议不要混合使用多种存储引擎，否则可能带来一系列复杂的问题，以及一些潜在的bug和边界问题。存储引擎层和服务器层的交互已经比较复杂，更不用说混合多个存储引擎了。至少，混合存储对一致性备份和服务器参数配置都带来了一些困难。
2.1.3.4.表引擎的转换
有很多种方法可以将表的存储引擎转换成另外一种引擎。每种方法都有其优点和缺点。常用的有三种方法
ALTER TABLE
将表从一个引擎修改为另一个引擎最简单的办法是使用ALTER TABLE 语句。下面的语句将mytable的引擎修改为InnoDB :
ALTER TABLE mytable ENGINE = InnoDB;
上述语法可以适用任何存储引擎。但需要执行很长时间，在实现上，MySQL会按行将数据从原表复制到一张新的表中，在复制期间可能会消耗系统所有的I/O能力，同时原表上会加上读锁。所以，在繁忙的表上执行此操作要特别小心。
导出与导入
还可以使用mysqldump工具将数据导出到文件，然后修改文件中CREATE TABLE语句的存储引擎选项，注意同时修改表名，因为同一个数据库中不能存在相同的表名，即使它们使用的是不同的存储引擎。
CREATE和 SELECT
先创建一个新的存储引擎的表，然后利用INSERT…SELECT语法来导数据:
CREATE TABLE innodb_table LIKE myisam_table;
ALTER TABLE innodb_table ENGINE=InnoDB;
INSERT INTO innodb_table SELECT * FROM myisam_table;
如果数据量很大，则可以考虑做分批处理，针对每一段数据执行事务提交操作。
2.1.3.5.检查MySQL的引擎
看我的MySQL现在已提供什么存储引擎:
show engines;
看我的MySQL当前默认的存储引擎:
show variables like '%storage_engine%';
2.1.3.6.MyISAM和InnoDB比较
2.2.MySQL中的目录和文件
2.2.1.bin目录
在MysQL的安装目录下有一个特别特别重要的bin目录，这个目录下存放着许多可执行文件。
其他系统中的可执行文件与此的类似。这些可执行文件都是与服务器程序和客户端程序相关的。
2.2.1.1.启动MySQL服务器程序
在UNIX系统中用来启动MySOL服务器程序的可执行文件有很多，大多在MySQL安装目录的bin目录下。
mysqld
mysqld这个可执行文件就代表着MySOL服务器程序，运行这个可执行文件就可以直接启动一个服务器进程。但这个命令不常用。
mysqld_safe
mysqld safe是一个启动脚本，它会间接的调用mysqld，而且还顺便启动了另外一个监控进程，这个监控进程在服务器进程挂了的时候，可以帮助重启它。另外,使用mysqld_safe启动服务器程序时，它会将服务器程序的出错信息和其他诊断信息重定向到某个文件中，产生出错日志，这样可以方便我们找出发生错误的原因。
mysql.server
mysql.server也是一个启动脚本，它会间接的调用mysqld_safe，在调用mysql.server时在后边指定start参数就可以启动服务器程序了
就像这样:
mysql.server start
需要注意的是，这个mysql.server文件其实是一个链接文件，它的实际文件是support-files/mysql.server，所以如果在bin目录找不到，到support-files下去找找，而且如果你愿意的话，自行用ln命令在bin创建一个链接。
另外，我们还可以使用mysql.server命令来关闭正在运行的服务器程序，只要把start参数换成stop就好了:
mysql.server stop
mysqld_multi
其实我们一台计算机上也可以运行多个服务器实例，也就是运行多个NySQL服务器进程。mysql_multi可执行文件可以对每一个服务器进程的启动或停止进行监控。
2.2.1.2.客户端程序
在我们成功启动MysTL服务器程序后，就可以接着启动客户端程序来连接到这个服务器喽， bin目录下有许多客户端程序，比方说mysqladmin、mysqldump、mysqlcheck等等。
我们常用的是可执行文件mysql，通过这个可执行文件可以让我们和服务器程序进程交互，也就是发送请求，接收服务器的处理结果。
mysqladmin执行管理操作的工具，检查服务器配置、当前运行状态，创建、删除数据库、设置新密码。
mysqldump数据库逻辑备份程序。
mysqlbackup备份数据表、整个数据库、所有数据库，一般来说mysqldump备份、mysql还原。
2.2.2.启动选项和参数
2.2.2.1.配置参数文件
当MySQL实例启动时，数据库会先去读一个配置参数文件，用来寻找数据库的各种文件所在位置以及指定某些初始化参数，这些参数通常定义了某种内存结构有多大等。在默认情况下，MySQL实例会按照-定的顺序在指定的位置进行读取，用户只需通过命令mysql --help|grep my.cnf来寻找即可。
当然，也可以在启动MySQL时，指定配置文件（非yum安装）：
这个时候，就会以启动时指定的配置文件为准。
MySQL数据库参数文件的作用和Oracle数据库的参数文件极其类似，不同的是，Oracle实例在启动时若找不到参数文件，是不能进行装载(mount）操作的。MySQL稍微有所不同，MySQL实例可以不需要参数文件，这时所有的参数值取决于编译MySQL时指定的默认值和源代码中指定参数的默认值。
MySQL数据库的参数文件是以文本方式进行存储的。可以直接通过一些常用的文本编辑软件进行参数的修改。
2.2.2.2.参数的查看和修改
可以通过命令show variables查看数据库中的所有参数，也可以通过LIKE来过滤参数名，前面查找数据库引擎时已经展示过了。从 MySQL 5.1版本开始，还可以通过information_schema架构下的GLOBAL_VARIABLES视图来进行查找，推荐使用命令 show variables，使用更为简单，且各版本的 MySQL数据库都支持。
参数的具体含义可以参考MySQL官方手册：
但是课程中遇到的参数会进行讲解。
MySQL数据库中的参数可以分为两类:动态(dynamic）参数和静态(static）参数。同时从作用范围又可以分为全局变量和会话变量。
动态参数意味着可以在 MySQL实例运行中进行更改，静态参数说明在整个实例生命周期内都不得进行更改，就好像是只读(read only)的。
全局变量（GLOBAL）影响服务器的整体操作。
会话变量（SESSION/LOCAL）影响某个客户端连接的操作。
举个例子，用default_storage_engine来说明，在服务器启动时会初始化一个名为default_storage_engine，作用范围为GLOBAL的系统变量。之后每当有一个客户端连接到该服务器时，服务器都会单独为该客户端分配一个名为default_storage_engine，作用范围为SESSION的系统变量，该作用范围为SESSION的系统变量值按照当前作用范围为GLOBAL的同名系统变量值进行初始化。
可以通过SET命令对动态的参数值进行修改。
SET的语法如下：
set [global || session ] system_var_name= expr
或者
set [@@global. || @@session.] system_var_name= expr
比如：
set read_buffer_size=524288;
set session read_buffer_size=524288;
set @@global.read_buffer_size=524288;
MySQL所有动态变量的可修改范围，可以参考MySQL官方手册的 Dynamic System Variables 的相关内容：
对于静态变量，若对其进行修改，会得到类似如下错误:
2.2.3.数据目录
我们知道像InnoDB、MyIASM这样的存储引擎都是把表存储在磁盘上的，而操作系统用来管理磁盘的那个东东又被称为文件系统，所以用专业一点的话来表述就是:像InnoDB、MyISAM这样的存储引擎都是把表存储在文件系统上的。当我们想读取数据的时候，这些存储引擎会从文件系统中把数据读出来返回给我们，当我们想写入数据的时候，这些存储引擎会把这些数据又写回文件系统。
2.2.3.1.确定MySQL中的数据目录
那说了半天，到底MySQL把数据都存到哪个路径下呢?其实数据目录对应着一个系统变量datadir，我们在使用客户端与服务器建立连接之后查看这个系统变量的值就可以了：
show variables like 'datadir';
当然这个目录可以通过配置文件进行修改，由我们自己进行指定。
2.2.3.2.数据目录中放些什么？
MySOL在运行过程中都会产生哪些数据呢?当然会包含我们创建的数据库、表、视图和触发器等用户数据，除了这些用户数据，为了程序更好的运行，MySQL也会创建一些其他的额外数据
2.2.3.2.1.数据库在文件系统中的表示
create database lijin charset=utf8;
cd
每当我们使用CREATE DATABASE语句创建一个数据库的时候，在文件系统上实际发生了什么呢?其实很简单，每个数据库都对应数据目录下的一个子目录，或者说对应一个文件夹，我们每当我们新建一个数据库时，MySQL会帮我们做这两件事儿:
1．在数据目录下创建一个和数据库名同名的子目录（或者说是文件夹)。
2．在该与数据库名同名的子目录下创建一个名为db.opt的文件，这个文件中包含了该数据库的各种属性，比方说该数据库的字符集和比较规则是个啥。
比方说我们查看一下在我的计算机上当前有哪些数据库︰
可以看到在当前有5个数据库，其中mysqladv数据库是我们自定义的，其余4个数据库是属于MySQL自带的系统数据库。我们再看一下数据目录下的内容:
当然这个数据目录下的文件和子目录比较多，但是如果仔细看的话，除了information_schema这个系统数据库外，其他的数据库在数居目录下都有对应的子目录。这个information_schema比较特殊，我们后面再讲它的作用。
2.2.3.2.2.表在文件系统中的表示
我们的数据其实都是以记录的形式插入到表中的，每个表的信息其实可以分为两种:
1.表结构的定义
2．表中的数据
表结构就是该表的名称是啥，表里边有多少列，每个列的数据类型是啥，有啥约束条件和索引，用的是啥字符集和比较规则各种信息，这些信息都体现在了我们的建表语句中了。为了保存这些信息，InnoDB和MyIASM这两种存储引擎都在数据目录下对应的数据库子目录下创建了一个专门用于描述表结构的文件，文件名是这样:表名.frm
比方说我们在lijin数据库下创建一个名为test的表:
那在数据库mysqladv对应的子目录下就会创建一个名为test.frm的用于描述表结构的文件。这个后缀名为.fm是以二进制格式存储的。
cd
那表中的数据存到什么文件中了呢?在这个问题上，不同的存储引擎就产生了有所不同，下边我们分别看一下InnoDB和MyISAM是用什么文件来保存表中数据的。
2.2.3.2.3.lnnoDB是如何存储表数据的
InnoDB的数据会放在一个表空间或者文件空间（英文名: table space或者file space)的概念，这个表空间是一个抽象的概念，它可以对应文件系统上一个或多个真实文件〈不同表空间对应的文件数量可能不同)。每一个表空间可以被划分为很多很多很多个页，我们的表数据就存放在某个表空间下的某些页里。表空间有好几种类型。
系统表空间(system tablespace)
这个所谓的系统表空间可以对应文件系统上一个或多个实际的文件，默认情况下，InnoDB会在数据目录下创建一个名为ibdata1(在你的数据目录下找找看有木有)、大小为12M的文件，这个文件就是对应的系纳表空间在文件系统上的表示。
这个文件是所谓的自扩展文件，也就是当不够用的时候它会自己增加文件大小，当然，如果你想让系统表空间对应文件系统上多个实际文件，或者仅仅觉得原来的ibdata1这个文件名难听，那可以在MySQL启动时配置对应的文件路径以及它们的大小，我们也可以把系统表空间对应的文件路径不配置到数据目录下，甚至可以配置到单独的磁盘分区上。
需要注意的一点是，在一个MySQL服务器中，系统表空间只有一份。从MySQL5.5.7到MySQL5.6.6之间的各个版本中，我们表中的数据都会被默认存储到这个系统表空间。
独立表空间(file-per-table tablespace)
在MySQL5.6.6以及之后的版本中，InnoB并不会默认的把各个表的数据存储到系统表空间中，而是为每一个表建立一个独立表空间，也就是说我们创建了多少个表，就有多少个独立表空间。使用独立表空间来存储表数据的话，会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，文件名和表名相同，只不过添加了一个.ibd的扩展名而已，所以完整的文件名称长这样:表名.ibd。
比方说假如我们使用了独立表空间去存储lijin数据库下的test表的话，那么在该表所在数据库对应的lijin目录下会为test表创建这两个文件:
test.frm和test.ibd
其中test.ibd文件就用来存储test表中的数据和索引。当然我们也可以自己指定使用系统表空间还是独立表空间来存储数据，这个功能由启动参数
innodb_file_per_table控制，比如说我们想刻意将表数据都存储到系统表空间时，可以在启动MySQL服务器的时候这样配置:
[server]
innodb_file_per_table=0
当imodb_file_per table的值为0时，代表使用系统表空间;当innodb_file_per table的值为1时，代表使用独立表空间。不过inmodb_file_per_table参数只对新建的表起作用，对于已经分配了表空间的表并不起作用。
其他类型的表空间
随着MySQL的发展，除了上述两种老牌表空间之外，现在还新提出了一些不同类型的表空间，比如通用表空间(general tablespace) ,undo表空间(undotablespace)、临时表空间〈temporary tablespace)等。
2.2.3.2.4.MyISAM是如何存储表数据的
在MyISAM中的数据和索引是分开存放的。所以在文件系统中也是使用不同的文件来存储数据文件和索引文件。而且和InnoDB不同的是，MyISA并没有什么所谓的表空间一说，表数据都存放到对应的数据库子目录下。
test_myisam表使用MyISAM存储引擎的话，那么在它所在数据库对应的lijin目录下会为myisam表创建三个文件:
其中test_myisam.MYD代表表的数据文件，也就是我们插入的用户记录; test_myisam.MYI代表表的索引文件，我们为该表创建的索引都会放到这个文件中。
2.2.3.3.日志文件
在服务器运行过程中，会产生各种各样的日志，比如常规的查询日志、错误日志、二进制日志、redo日志、Undo日志等等，日志文件记录了影响MySQL数据库的各种类型活动。
常见的日志文件有：错误日志（error log）、慢查询日志（slow query log）、查询日志（query log）、二进制文件（bin log）。
错误日志
错误日志文件对MySQL的启动、运行、关闭过程进行了记录。遇到问题时应该首先查看该文件以便定位问题。该文件不仅记录了所有的错误信息，也记录一些警告信息或正确的信息
用户可以通过下面命令来查看错误日志文件的位置：
show variables like 'log_error'\G;
当MySQL不能正常启动时，第一个必须查找的文件应该就是错误日志文件，该文件记录了错误信息。
慢查询日志
慢查询日志可以帮助定位可能存在问题的SQL语句，从而进行SQL语句层面的优化。
我们已经知道慢查询日志可以帮助定位可能存在问题的SQL语句，从而进行SQL语句层面的优化。但是默认值为关闭的，需要我们手动开启。
show VARIABLES like 'slow_query_log';
set GLOBAL slow_query_log=1;
开启1，关闭0
但是多慢算慢？MySQL中可以设定一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日志中。long_query_time参数就是这个阈值。默认值为10，代表10秒。
show VARIABLES like '%long_query_time%';
当然也可以设置
set global long_query_time=0;
默认10秒，这里为了演示方便设置为0
同时对于运行的SQL语句没有使用索引，则MySQL数据库也可以将这条SQL语句记录到慢查询日志文件，控制参数是：
show VARIABLES like '%log_queries_not_using_indexes%';
开启1，关闭0（默认）
show VARIABLES like '%slow_query_log_file%';
查询日志
查看当前的通用日志文件是否开启
show variables like '%general%'
开启通⽤⽇志查询： set global general_log = on;
关闭通⽤⽇志查询：set global general_log = off;
sele
查询日志记录了所有对MySQL数据库请求的信息，无论这些请求是否得到了正确的执行。
默认文件名：主机名.log
二进制日志（binlog）
二进制日志记录了所有的DDL和DML语句（除了数据查询语句select）,以事件形式记录，还包含语句所执⾏的消耗的时间，MySQL的⼆进制⽇志是事务安全型的
二进制日志的几种作用：
恢复（recovery）：某些数据的恢复需要二进制日志，例如，在一个数据库全备文件恢复后，用户可以通过二进制文件进行point-in-time的恢复
复制（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的MySQL数据库（一般称为slave或standby）与一台MySQL数据库（一般称为master或primary）进行实时同步
审计（audit）：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击
log-bin参数该参数用来控制是否开启二进制日志，默认为关闭
如果想要开启二进制日志的功能，可以在MySQL的配置文件中指定如下的格式：
“name”为二进制日志文件的名称
如果不提供name，那么数据库会使用默认的日志文件名（文件名为主机名，后缀名为二进制日志的序列号），且文件保存在数据库所在的目录（datadir下）
--启用/设置二进制日志文件(name可省略)
log-bin=name;
配置以后，就会在数据目录下产生类似于：
bin_log.00001即为二进制日志文件；bin_log.index为二进制的索引文件，用来存储过往产生的二进制日志序号，通常情况下，不建议手动修改这个文件。
二进制日志文件在默认情况下并没有启动，需要手动指定参数来启动。开启这个选项会对MySQL的性能造成影响，但是性能损失十分有限。根据MySQL官方手册中的测试指明，开启二进制日志会使性能下降1%。
查看binlog是否开启
show variables like 'log_bin';
mysql安装目录下修改my.cnf
log_bin=mysql-bin
binlog-format=ROW
server-id=1
expire_logs_days =30
2.2.3.3.其他的数据文件
除了我们上边说的这些用户自己存储的数据以外，数据文件下还包括为了更好运行程序的一些额外文件，当然这些文件不一定会放在数据目录下，而且可以在配置文件或者启动时另外指定存放目录。
主要包括这几种类型的文件:
·服务器进程文件。
我们知道每运行一个MySQL服务器程序，都意味着启动一个进程。MySQL服务器会把自己的进程ID写入到一个pid文件中。
socket文件
当用UNIX域套接字方式进行连接时需要的文件。
·默认/自动生成的SSL和RSA证书和密钥文件。
1.2.MySQL中的索引
InnoDB存储引擎支持以下几种常见的索引：B+树索引、全文索引、哈希索引，其中比较关键的是B+树索引
1.2.1.B+树索引
InnoDB中的索引自然也是按照B+树来组织的，前面我们说过B+树的叶子节点用来放数据的，但是放什么数据呢？索引自然是要放的，因为B+树的作用本来就是就是为了快速检索数据而提出的一种数据结构，不放索引放什么呢？但是数据库中的表，数据才是我们真正需要的数据，索引只是辅助数据，甚至于一个表可以没有自定义索引。InnoDB中的数据到底是如何组织的？
1.2.1.1.聚集索引/聚簇索引
InnoDB中使用了聚集索引，就是将表的主键用来构造一棵B+树，并且将整张表的行记录数据存放在该B+树的叶子节点中。也就是所谓的索引即数据，数据即索引。由于聚集索引是利用表的主键构建的，所以每张表只能拥有一个聚集索引。
聚集索引的叶子节点就是数据页。换句话说，数据页上存放的是完整的每行记录。因此聚集索引的一个优点就是：通过过聚集索引能获取完整的整行数据。另一个优点是：对于主键的排序查找和范围查找速度非常快。
如果我们没有定义主键呢？MySQL会使用唯一性索引，没有唯一性索引，MySQL也会创建一个隐含列RowID来做主键，然后用这个主键来建立聚集索引。
1.2.1.2.辅助索引/二级索引
聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据都是按照主键进行排序的。
如果我们想以别的列作为搜索条件怎么办？我们一般会建立多个索引，这些索引被称为辅助索引/二级索引。
（每建立一个索引，就有一颗B+树
对于辅助索引(Secondary Index，也称二级索引、非聚集索引)，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签( bookmark)。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。
比如辅助索引index(node)，那么叶子节点中包含的数据就包括了(note和主键)。
1.2.1.3.回表
辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引（聚集索引）来找到一个完整的行记录。这个过程也被称为 回表 。也就是根据辅助索引的值查询一条完整的用户记录需要使用到2棵B+树----一次辅助索引，一次聚集索引。
为什么我们还需要一次回表操作呢?直接把完整的用户记录放到辅助索引d的叶子节点不就好了么？如果把完整的用户记录放到叶子节点是可以不用回表，但是太占地方了，相当于每建立一棵B+树都需要把所有的用户记录再都拷贝一遍，这就有点太浪费存储空间了。而且每次对数据的变化要在所有包含数据的索引中全部都修改一次，性能也非常低下。
很明显，回表的记录越少，性能提升就越高，需要回表的记录越多，使用二级索引的性能就越低，甚至让某些查询宁愿使用全表扫描也不使用二级索引。
那什么时候采用全表扫描的方式，什么时候使用采用二级索引 + 回表的方式去执行查询呢？这个就是查询优化器做的工作，查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用二级索引 + 回表的方式。
1.2.1.4.联合索引/复合索引
前面我们对索引的描述，隐含了一个条件，那就是构建索引的字段只有一个，但实践工作中构建索引的完全可以是多个字段。所以，将表上的多个列组合起来进行索引我们称之为联合索引或者复合索引，比如index(a,b)就是将a,b两个列组合起来构成一个索引。
千万要注意一点，建立联合索引只会建立1棵B+树，多个列分别建立索引会分别以每个列则建立B+树，有几个列就有几个B+树，比如，index(note)、index(b)，就分别对note,b两个列各构建了一个索引。
而如果是index(note,b)在索引构建上，包含了两个意思：
1、先把各个记录按照note列进行排序。
2、在记录的note列相同的情况下，采用b列进行排序
从原理可知，为什么有最佳左前缀法则，就是这个道理
1.2.1.5.覆盖索引
既然多个列可以组合起来构建为联合索引，那么辅助索引自然也可以由多个列组成。
InnoDB存储引擎支持覆盖索引(covering index，或称索引覆盖)，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录(回表)。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。所以记住，覆盖索引并不是索引类型的一种。
1.2.2.哈希索引
InnoDB存储引擎除了我们前面所说的各种索引，还有一种自适应哈希索引，我们知道B+树的查找次数,取决于B+树的高度,在生产环境中,B+树的高度一般为3、4层,故需要3、4次的IO查询。
所以在InnoDB存储引擎内部自己去监控索引表，如果监控到某个索引经常用，那么就认为是热数据，然后内部自己创建一个hash索引，称之为自适应哈希索引( Adaptive Hash Index,AHI)，创建以后，如果下次又查询到这个索引，那么直接通过hash算法推导出记录的地址，直接一次就能查到数据，比重复去B+tree索引中查询三四次节点的效率高了不少。
InnoDB存储引擎使用的哈希函数采用除法散列方式，其冲突机制采用链表方式。注意，对于自适应哈希索引仅是数据库自身创建并使用的，我们并不能对其进行干预。
show engine innodb status
哈希索引只能用来搜索等值的查询,如 SELECT* FROM table WHERE index co=xxx。而对于其他查找类型,如范围查找,是不能使用哈希索引的,
因此这里出现了non-hash searches/s的情况。通过 hash searches: non- hash searches可以大概了解使用哈希索引后的效率。
innodb_adaptive_hash_index来考虑是禁用或启动此特性,默认AHI为开启状态。
1.2.3.全文索引
什么是全文检索（Full-Text Search）？它是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。它可以根据需要获得全文中有关章、节、段、句、词等信息，也可以进行各种统计和分析。我们比较熟知的Elasticsearch、Solr等就是全文检索引擎，底层都是基于Apache Lucene的。
举个例子，现在我们要保存唐宋诗词，数据库中我们们会怎么设计？诗词表我们可能的设计如下：
| 朝代 | 作者 | 诗词年代 | 标题 | 诗词全文 |
|---|---|---|---|---|
| 唐 | 李白 | 静夜思 | 床前明月光，疑是地上霜。 举头望明月，低头思故乡。 | |
| 宋 | 李清照 | 如梦令 | 常记溪亭日暮，沉醉不知归路，兴尽晚回舟，误入藕花深处。争渡，争渡，惊起一滩鸥鹭。 | |
| …. | …. | … | …. | ……. |
要根据朝代或者作者寻找诗，都很简单，比如“select 诗词全文 from 诗词表 where作者=‘李白’”，如果数据很多，查询速度很慢，怎么办？我们可以在对应的查询字段上建立索引加速查询。
但是如果我们现在有个需求：要求找到包含“望”字的诗词怎么办？用
“select 诗词全文 from 诗词表 where诗词全文 like‘%望%’”，这个意味着要扫描库中的诗词全文字段，逐条比对，找出所有包含关键词“望”字的记录，。基本上，数据库中一般的SQL优化手段都是用不上的。数量少，大概性能还能接受，如果数据量稍微大点，就完全无法接受了，更何况在互联网这种海量数据的情况下呢？怎么解决这个问题呢，用倒排索引。
倒排索引就是，将文档中包含的关键字全部提取处理，然后再将关键字和文档之间的对应关系保存起来，最后再对关键字本身做索引排序。用户在检索某一个关键字是，先对关键字的索引进行查找，再通过关键字与文档的对应关系找到所在文档。
于是我们可以这么保存
| 序号 | 关键字 | 蜀道难 | 静夜思 | 春台望 | 鹤冲天 |
|---|---|---|---|---|---|
| 1 | 望 | 有 | 有 | 有 | 有 |
如果查哪个诗词中包含上，怎么办，上述的表格可以继续填入新的记录
| 序号 | 关键字 | 蜀道难 | 静夜思 | 春台望 | 鹤冲天 |
|---|---|---|---|---|---|
| 1 | 望 | 有 | 有 | 有 | 有 |
| 2 | 上 | 有 | 有 |
从InnoDB 1.2.x版本开始，InnoDB存储引擎开始支持全文检索，对应的MySQL版本是5.6.x系列。不过MySQL从设计之初就是关系型数据库，存储引擎虽然支持全文检索，整体架构上对全文检索支持并不好而且限制很多，比如每张表只能有一个全文检索的索引，不支持没有单词界定符( delimiter）的语言，如中文、日语、韩语等。
所以MySQL中的全文索引功能比较弱鸡，了解即可。
1.2.4.索引在查询中的使用
索引在查询中的作用到底是什么？在我们的查询中发挥着什么样的作用呢？
请记住：
1、 一个索引就是一个B+树，索引让我们的查询可以快速定位和扫描到我们需要的数据记录上，加快查询的速度 。
2、一个select查询语句在执行过程中一般最多能使用一个二级索引，即使在where条件中用了多个二级索引。
1.2.4.高性能的索引创建策略
正确地创建和使用索引是实现高性能查询的基础。前面我们已经了解了索引相关的数据结构，各种类型的索引及其对应的优缺点。现在我们一起来看看如何真正地发挥这些索引的优势。
1.2.4.1.索引列的类型尽量小
我们在定义表结构的时候要显式的指定列的类型，以整数类型为例，有TTNYINT、NEDUMNT、INT、BIGTNT这么几种，它们占用的存储空间依次递增，我们这里所说的类型大小指的就是该类型表示的数据范围的大小。能表示的整数范围当然也是依次递增，如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用INT就不要使用BIGINT，能使用NEDIUMINT就不要使用INT，这是因为数据类型越小，在查询时进行的比较操作越快（CPU层次)数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘/0带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。
这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键适用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/0。
1.2.4.2.索引的选择性
创建索引应该选择选择性/离散性高的列。索引的选择性/离散性是指，不重复的索引值（也称为基数，cardinality)和数据表的记录总数（N)的比值，范围从1/N到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
很差的索引选择性就是列中的数据重复度很高，比如性别字段，不考虑政治正确的情况下，只有两者可能，男或女。那么我们在查询时，即使使用这个索引，从概率的角度来说，依然可能查出一半的数据出来。
哪列做为索引字段最好？当然是姓名字段，因为里面的数据没有任何重复，性别字段是最不适合做索引的，因为数据的重复度非常高。
怎么算索引的选择性/离散性？比如person这个表：
SELECT count(DISTINCT name)/count() FROM person; SELECT count(DISTINCT sex)/count() FROM person; SELECT count(DISTINCT age)/count() FROM person; SELECT count(DISTINCT area)/count() FROM person;
1.2.4.3.前缀索引
针对blob、text、很长的varchar字段，mysql不支持索引他们的全部长度，需建立前缀索引。
语法：Alter table tableName add key/index (column(X))
**缺点：**前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。
有时候后缀索引 (suffix index)也有用途（例如，找到某个域名的所有电子邮件地址)。MySQL原生并不支持反向索引，但是可以把字符串反转后存储，并基于此建立前缀索引。可以通过触发器或者应用程序自行处理来维护索引。
案例：
首先找到最常见的值的列表：
SELECT COUNT(DISTINCT LEFT(order_note,3))/COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(order_note,4))/COUNT(*)AS sel4,
COUNT(DISTINCT LEFT(order_note,5))/COUNT(*) AS sel5,
COUNT(DISTINCT LEFT(order_note, 6))/COUNT(*) As sel6,
COUNT(DISTINCT LEFT(order_note, 7))/COUNT(*) As sel7,
COUNT(DISTINCT LEFT(order_note, 8))/COUNT(*) As sel8,
COUNT(DISTINCT LEFT(order_note, 9))/COUNT(*) As sel9,
COUNT(DISTINCT LEFT(order_note, 10))/COUNT(*) As sel10,
COUNT(DISTINCT LEFT(order_note, 11))/COUNT(*) As sel11,
COUNT(DISTINCT LEFT(order_note, 12))/COUNT(*) As sel12,
COUNT(DISTINCT LEFT(order_note, 13))/COUNT(*) As sel13,
COUNT(DISTINCT LEFT(order_note, 14))/COUNT(*) As sel14,
COUNT(DISTINCT LEFT(order_note, 15))/COUNT(*) As sel15,
COUNT(DISTINCT order_note)/COUNT(*) As total
FROM order_exp;
可以看见，从第10个开始选择性的增加值很高，随着前缀字符的越来越多，选择度也在不断上升，但是增长到第15时，已经和第14没太大差别了，选择性提升的幅度已经很小了，都非常接近整个列的选择性了。
那么针对这个字段做前缀索引的话，从第13到第15都是不错的选择
在上面的示例中，已经找到了合适的前缀长度，如何创建前缀索引:
ALTER TABLE order_exp ADD KEY (order_note(14));
建立前缀索引后查询语句并不需要更改：
select * from order_exp where order_note = 'xxxx' ;
前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。
有时候后缀索引 (suffix index)也有用途（例如，找到某个域名的所有电子邮件地址)。MySQL原生并不支持反向索引，但是可以把字符串反转后存储，并基于此建立前缀索引。可以通过触发器或者应用程序自行处理来维护索引。
1.2.4.4.只为用于搜索、排序或分组的列创建索引
也就是说，只为出现在WHERE 子句中的列、连接子句中的连接列创建索引，而出现在查询列表中的列一般就没必要建立索引了，除非是需要使用覆盖索引；又或者为出现在ORDER BY或GROUP BY子句中的列创建索引，这句话什么意思呢？比如：
搜索
select order_note from .... and ....
只为 条件中的列建立索引即可
排序
SELECT * FROM order_exp ORDER BY insert_time, order_status,expire_time;
查询的结果集需要先按照insert_time值排序，如果记录的insert_time值相同，则需要按照order_status来排序，如果order_status的值相同，则需要按照expire_time排序。回顾一下联合索引的存储结构，u_idx_day_status索引本身就是按照上述规则排好序的，所以直接从索引中提取数据，然后进行回表操作取出该索引中不包含的列就好了。
1.2.4.5.多列索引
很多人对多列索引的理解都不够。一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。
我们遇到的最容易引起困惑的问题就是索引列的顺序。正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。反复强调过，在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY和DISTINCT等子句的查询需求。
所以多列索引的列顺序至关重要。对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE条件的查找。在这种情况下，这样设计的索引确实能够最快地过滤出需要的行，对于在WHERE子句中只使用了索引部分前缀列的查询来说选择性也更高。
然而，性能不只是依赖于索引列的选择性，也和查询条件的有关。可能需要根据那些运行频率最高的查询来调整索引列的顺序，比如排序和分组，让这种情况下索引的选择性最高。
同时，在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。
1.2.4.6.三星索引
三星索引概念
对于一个查询而言，一个三星索引，可能是其最好的索引。
满足的条件如下：
- 索引将相关的记录放到一起则获得一星 （比重27%）
- 如果索引中的数据顺序和查找中的排列顺序一致则获得二星（排序星） （比重27%）
- 如果索引中的列包含了查询中需要的全部列则获得三星（宽索引星） （比重50%）
这三颗星，哪颗最重要？第三颗星。因为将一个列排除在索引之外可能会导致很多磁盘随机读（回表操作）。第一和第二颗星重要性差不多，可以理解为第三颗星比重是50%，第一颗星为27%，第二颗星为23%，所以在大部分的情况下，会先考虑第一颗星，但会根据业务情况调整这两颗星的优先度。
一星：
一星的意思就是：如果一个查询相关的索引行是相邻的或者至少相距足够靠近的话，必须扫描的索引片宽度就会缩至最短，也就是说，让索引片尽量变窄，也就是我们所说的索引的扫描范围越小越好。
二星（排序星） ：
在满足一星的情况下，当查询需要排序，group by、 order by，如果查询所需的顺序与索引是一致的（索引本身是有序的），是不是就可以不用再另外排序了，一般来说排序可是影响性能的关键因素。
三星（宽索引星） ：
在满足了二星的情况下，如果索引中所包含了这个查询所需的所有列（包括 where 子句和 select 子句中所需的列，也就是覆盖索引），这样一来，查询就不再需要回表了，减少了查询的步骤和IO请求次数，性能几乎可以提升一倍。
1.2.4.6.设计三星索引实战
现在有表，SQL如下
CREATE TABLE customer (
cno INT,
lname VARCHAR (10),
fname VARCHAR (10),
sex INT,
weight INT,
city VARCHAR (10)
);
CREATE INDEX idx_cust ON customer (city, lname, fname, cno);
对于下面的SQL而言，这是个三星索引
select cno,fname from customer where lname=’xx’ and city =’yy’ order by fname;
来评估下：
第一颗星：所有等值谓词的列，是组合索引的开头的列，可以把索引片缩得很窄，符合。
根据之前讲过的联合索引，我们是知道条件已经把搜索范围搜到很窄了
第二颗星：order by的fname字段在组合索引中且是索引自动排序好的，符合。
第三颗星：select中的cno字段、fname字段在组合索引中存在，符合。
现在有表，SQL如下：
CREATE TABLE `test` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`user_name` VARCHAR (100) DEFAULT NULL,
`sex` INT (11) DEFAULT NULL,
`age` INT (11) DEFAULT NULL,
`c_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
) ENGINE = INNODB AUTO_INCREMENT = 12 DEFAULT CHARSET = utf8;
SQL语句如下：
select user_name,sex,age from test where user_name like 'test%' and sex =1 ORDER BY age
如果我们建立索引(user_name,sex,age)：
第三颗星，满足
第一颗星，满足
第二颗星，不满足，user_name 采用了范围匹配，sex 是过滤列，此时age 列无法保证有序的。
上述我们看到，此时索引(user_name,sex,age)并不能满足三星索引中的第二颗星（排序）。
于是我们改改，建立索引(sex, age，user_name)：
第一颗星，不满足，只可以匹配到sex，sex选择性很差，意味着是一个宽索引片(同时因为age也会导致排序选择的碎片问题)
第二颗星，满足，等值sex 的情况下，age是有序的，
第三颗星，满足，select查询的列都在索引列中，
对于索引(sex,age，user_name)我们可以看到，此时无法满足第一颗星，窄索引片的需求。
以上2个索引，都是无法同时满足三星索引设计中的三个需求的，我们只能尽力满足2个。而在多数情况下，能够满足2颗星，已经能缩小很大的查询范围了，具体最终要保留那一颗星（排序星 or 窄索引片星），这个就需要看查询者自己的着重点了，无法给出标准答案。1.3.MySQL性能调优
