Sunskey

MySQL Memory表

Memory的基础知识

内存表的数据部分以数组的方式单独存放，而主键id索引里，存的是每个数据的位置。主键id是hash索引，所以索引上的key并不是有序的。

InnoDB和Memory引擎的数据组织方式是不同的：

InnoDB引擎把数据放在主键索引上，其他索引上保存的主键id。这中方式成为索引组织表。

Memory引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，我们称之为堆组织表。

InnoDB和Memory引擎的区别

1.InnoDB表的数据总是有序存放的，而内存表就是按照写入的顺序。

2.当数据文件有空洞时候，InnoDB表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入新值。

3.InnoDB 表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次。而内存表没有这个区别，索引的“地位”都是相同的。

4.数据位置发生变化的时候，InnoDB表只需要修改主键索引，而内存表需要修改所有索引。

5.InnoDB 支持变长数据类型，不同记录的长度不同；内存表不支持Blob和Text字段，并且即使定义了 varchar（N）,实际也当作char(N)，也就是固定长度字符串来存储，因此内存表的每行数据长度相同。

不建议在生产环境上使用内存表

• 锁粒度问题

内存表不支持行锁，只支持表锁。跟行锁比起来对并发的支持不够好，决定了它在处理并发事务的时候，性能也不会太好。

• 数据持久化问题

M-S模式下，slave机器重启或者升级，会导致“数据丢失”。MySQL担心主库重启后，出现主备不一致，会在binlog里面写入一行DELETE FROM t1。

双M模式下，在备库重启的时候，备库的binlog的里面delete语句会传到主库，然后把主库内存表的内存删除。

MySQL 临时表

内存表，指的使用Memory引擎的表，建表语法是create table… engine = memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。

临时表，可以使用各种引擎（MySQL、Memory），写数据的时候写到磁盘上。

临时表的特性

1.建表语法是create temporary table ….

2.一个临时表只能被创建它的session访问，对其他线程不可见。

3.临时表可以与普通表同名

4.session A内有同名的临时表和普通表的时候，show create语句，以及增删改查访问的临时表。

5.show tables命令不显示临时表。

由于临时表只能被创建它的sesssion访问，session结束会自动删除，所以临时表就特别适合优化join的场景。

临时表的应用

1.由于不用担心线程之间的重名冲突，临时表经常会被用在复制查询的优化过程中。其中，分库分表系统的跨库查询就是一个典型的使用场景。

分区key的选择是“减少跨库和跨表查询”。如果大部分的语句都会包含f的等值条件，那么就要用f做分区键。

但是如果查询条件中没有分区字段，只能到所有的分区中查询满足条件的所有行，然后统一做order by的操作。两种常见的思路。

1.在proxy层的进程代码中实现排序。

优点：处理速度快。缺点：需要开发的工作量比较大，对proxy端的压力比较大。

2.在各个分库拿到的数据，汇总到一个MySQL实例的一个表中，然后在这个汇总实例上做逻辑操作。

在汇总库中创建一个临时库temp_ht，包含三个字段 v、k、modified；

在各个分库上执行

select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;

把分库执行的结果插入到temp_ht表中；

执行 select v from temp_ht order by t_modified desc limit 100; 得到结果。

在实际中，往往会发现每个分库的计算量都不饱和，所以会直接把临时表temp_ht放到32个分库中的某一个上。

临时表的命名

MySQL用一个frm文件保存表结构的定义，这个frm文件放在临时文件下，文件名的后缀是.frm，前缀是“#sql{进程 id}{线程 id}_序列号”

5.6以及之前的版本里，MySQL会在临时文件目录下创建一个相同前缀，以.ibd为后缀的文件，用来存放数据文件。

5.7版本开始，MySQL引入了一个临时文件表空间，专门用来存放临时文件的数据。

MySQL维护数据表，除了物理上要有文外，内存里面也有一套机制区别不同的表，每个表都对应一个table_def_key。

一个普通的table_def_key的值是由“库名+表名“得到的。

临时表，则是在”库名+表名“基础上，又加入了”server_id+thread_id”

在实现上，每个线程都维护了自己的临时表链表。

临时表和主备复制

当前binlog_format =row，那么跟 临时表有关的语句，就不会记录到binlog里面，也就是在binglog_format=statemnt/mixed的时候，binlog中才会记录临时表的操作。

这种情况下，创建临时表的语句会传到备库执行，因此备库的同步线程会创建这个临时表。主库在线程退出的时候，会自动删除临时表，但是备库同步线程是持续运行的。所以这时候需要在主库再写一个DROP TEMPORARY TBAlE传给备库执行。

不同线程创建同名给的临时表是没问题的，但是传到备库是怎么执行的呢？

MySQL在记录binlog的时候，会在主库执行这个语句的线程id写到binlog中。这样，在备库的应用线程就能直到每个语句的主库线程id，并利用这个线程id来构造临时表的table_def_table。

内存临时表

1. union 执行流程

在union时，这里的内存临时表起到了暂存数据的作用，而且计算过程还用上了临时表主键id的唯一约束，实现了union的语义。

2. group by 执行流程

如果需要并不需要对结果进行排序，那可以在SQL语句末尾增加 order by null。

group by 优化方法 – 索引

MySQL 5.7 版本支持了generated column 机制，用来实现列数据的关联更新。你可以用下面的方法创建一个列z，然后在z 列上创建一个索引。

group by 优化方法 – 直接排序

在group by语句中加入SQL_BIG_RESULT这个提示(hint)，就可以告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。

select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;

到底可不可以使用join

• 不让使用join，使用join有什么问题？
• 如果有两个大小不同的表做Join，应该用哪个表做驱动表？

Index Nested-Loop Join

select * from t1 straight_join t2 on (t1.a=t2.a);

1.从表t1中读入一行数据R;

2.从数据行R中，取出a字段到表t2里去查找；

3.取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；

4.重复执行步骤1 到 3，直到表t1的末尾循环结束。

这个过程就成我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，索引我们称之为”Index Nested-Loop Join“，简称NLJ。

在驱动表时走的全表扫描，而被驱动表走的树搜索。

假设被驱动表的行数是M。每次在被驱动表查一行数据，要先搜索索引a，在搜索主键索引，每次搜索一颗树近似复杂度是以2为底的M的对数，记为 log2M，所以在被驱动表上查一行的时间复杂度是 2*log2M。

假设驱动表的行数为N，执行过程就要扫描驱动表N行，对于每一行，到被驱动表上匹配一次。

近似复杂度是N + N2log2M。

显示，N对扫描行数的影响更大，因此应该让小表来做驱动表。

结论：

1.使用join语句，性能比强行拆分多个单表执行SQL语句的性能要好；

2.如果使用join语句的话，需要让小表做驱动表。

Simple Nested-Loop Join

被驱动表没有索引

select * from t1 straight_join t2 on (t1.a=t2.b);

如果继续使用算法2的话，这个算法的结果是正确的，算法叫做”Simple Nested-Loop Join”。整个扫描需要N*M行，显然效率和扫描的行数就十分的低下。

Block Nested-Loop Join

被驱动表上没有索引

1.把表t1的数据读入线程内存join_buffer中，由于我们这个语句中写的是select *，因此是把整个表t1放入内存；

2.扫描表t2，把表t2的每一行取出来，跟Join buffer中的数据做对比，满足join条件的，作为结果集返回。

总的扫描行数是1100行，由于join_buffer是以无序数组的方式组织，因此，对表t2中的每一行，都要做一百次判断，总共需要在内存中做的判断次数是：100* 1000 = 10万次。

假设小表的行数是N，大表的行数是M，那么在这个算法里：

1.两个表都做一次全表扫描，所以总的扫描行数是M+N。

2.内存中的 判断次数是M*N。

如果由于join_buffer过小，驱动表的数据行数是N，需要分K段才能完成算法流程，被驱动表的数据行数是M。

注意，这个这里的K不是常数，N越大K就会越大，因此把 K 表示为λ*N，显然λ的取值范围是 (0,1)。

这个算法的执行过程

1.扫描行数N+λNM；

2.内存判断 N*M 次。

join_buffer_size越大，一次可以放入的行越多，分成的段数也就越少，被驱动表的全表扫描次数就越少。

问题解答

• 能不能使用join语句。

1.如果可以使用Index Nested-Loop Join算法，也就是使用被驱动表上的索引，其实没有问题。

2.如果使用Block Nesetd-Loop Join算法，扫描的行数就会过多，尤其大表上的join，这样可能要扫描被驱动表很多次，会占用大量的系统资源，所以这种join尽量不要有。

• 如果要使用join，应该选择大表做驱动还是选择小表做驱动。

1.如果是Index Nested-Loop join算法，应该选择小表做驱动表。

2.如果是Block Nestd-Loop join算法

在join_buffer_size足够大的时候，是一样的。

在join_buffer_size不够大的时候，应该选择小表做驱动表。

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成后，计算参与join的各个字段的总数据量，数据小的那张表，就是“小表”，应该作为驱动表。

join该怎么优化

Multi-Range Read（MMR）优化

这个优化的主要目的是尽量使用顺序读盘。

因为大多数数据都是按照主键递增顺序插入得到的，所以可以认为i，如果按照主键的递增顺序查询的话，对磁盘的读比交接近顺序读，能够提升读性能。

MRR优化思路

1.根据索引a，定位到满足条件的记录，将id值放入read_rnd_buffer中；

2.将read_rnd_buffer中的id进行递增排序

3.排序后的id数组，依次到主键id索引中查记录，并作为结果返回。

MRR能够提升性能的核心在于，可以得到足够多的主键id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。

Batched Key Access

MySQL5.6以后引入这个算法，其实就是对NLJ算法的优化。

一次性从表驱动表中多拿一些行出来，一起传给被驱动表。

如果要使用BKA优化算法的话，需要执行SQL语句之前，先设置

set optimizer_switch=’mrr=on,mrr_cost_based=off,batched_key_access=on’;

BNL算法的性能问题

大表join操作虽然对IO有影响，但是语句执行结束后，对IO的影响也就结束了。但是，对Buffer Pool的影响是持续性的，需要依靠后续的查询请求慢慢恢复内存命中率。

BNL算法对系统的影响主要包括3个方面：

1.可能会多次扫描被驱动表，占用磁盘IO资源；

2.判断join条件需要执行M*N次对比，如果是大表就会占用非常多的CPU资源；

3.可能回暖导致Buffer Pool的热数据被淘汰，影响内存命中率。

BNL转BKA

一些情况下，我们可以在被驱动表上建立索引，这是就可以直接转成BKA算法了。

创建临时表

1.把表t2中满足条件的数据放在临时表tmp_t中；

2.为了让join使用BKA算法，给临时表tmp_t的字段b加上索引；

3.让表t1和tmp_t做join操作。

总体思路，无论是在原表上加索引，还是用有索引的临时表，我们的思路都是让join语句能够用上被驱动表上的索引，来触发BKA算法，提升查询性能。

扩展 - hash join

join_buffer里面维护的不是一个无序数组，而是一个hash表的话，那么就不是10亿次判断，而是100万次hash查找。这样的话，整条语句的执行速度就会快很多。

这，也是MySQL的优化器和执行器一直被诟病的一个原因：不支持哈希join.

具体的优化思路，可以自己在业务端实现。

1.select * from t1;取得表t1的全部1000行数据，在业务端存入一个hash结构

2.select * from t2 where b> =1 and b<=2000；获取t2中满足条件的2000行数据。

3.把这2000行数据，一行一行去到业务端，到hash结构的数据表中寻找匹配的数据。满足匹配的条件的这行数据你，就作为结果集的一行。

大数据对数据库的影响

全表扫描对server层的影响

1.获取一行，写入net_buffer。大小由net_buffer_length决定，默认16K。

2.重复获取行，直到net_buffer写满，调用网络接口发出去。

3.如果发送出去，就清空net_buffer，继续写入net_buffer。

4.如果发送函数返回EAGAIN或WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer写满了），进入等待。直到网络栈重新可写，在继续发送。

MySQL是“边读边发的”，如果客户端接受得慢，会导致MySQL服务端由于结果发不出去，这个事务的执时间边长。

因此，对于正常的线上业务来说，如果一个查询结果不会很多的话，建议使用mysql_store_result这个接口，直接把查询结果保存在本地内存。

一个查询语句的状态变化是这样的

• MySQL查询语句进入执行阶段后，首先把状态设置成“Sending data”
• 然后，发送执行结果的列相关的信息给客户端
• 再执行执语句的流程
• 执行完成后，把状态设置成空字符串。

“Sending data”并不是指“正在发送数据”，而可能是处于执行器过程中的任意阶段。

当一个线程处于“等待客户端接受结果”的状态，才会显示“Sending to client“；如果显示成”Sending data“，意思是正在执行。

全表扫描对InnoDB的影响

内存的数据页是在Buffer Pool(BP)中管理，在WAL里Buffer Pool起到了加速更新的作用。而实际上，Buffer Pool还是一个更重要的作用，加速查询。

Buffer Pool对查询的加速效果，依赖一个重要的指标，即：内存命中率。

可以通过show engine innodb status，查看BP命中率，一个稳定服务的线上系统，要保证响应时间符合要求的话，内存命中率要99%.

InnoDB buffer Pool的大小是由参数innodb_buffer_pool_size确定的，一般建议设置成可用物理内存60%-80%。

在InnoDB改进的LRU算法中，按照5：3的比例把整个LRU链表分成了young区域和old区域。

• 扫描过程中，需要新插入的数据页，都被放到了old区域；
• 一个数据页里面由多条记录，这个数据页会被多次访问到，但是由于是顺序扫描，这个数据在第一次被访问和最后一次被访问的时间间隔不会超过1秒，因此还是会被保留在old区域。
• 在继续扫描后续数据，之前的这个数据页页不会在被访问到，于是始终没有机会移到链表头部，很快就会被淘汰。

可以看见虽然也用到了Buffer Pool，但是对young区域完全没有影响，从而保证了Buffer Pool响应正常业务的查询。

Kill不掉的语句

两个kill命令：一个kill query + 线程id，表示终止这个线程中正在执行的语句。

一个是kill connection + 线程id，表示断开这个线程的连接。当然如果这个线程有语句正在执行，也要先停止正在执行的语句。

kill执行的过程

1.一个语句执行过程中有多处“埋点”，在这些“埋点”的地方判断线程状态，如果发现线程状态是THD::KILL_QUERY，才开始进入语句终止逻辑。

2.如果处于等待状态，必须是一个可以被唤醒的等待，否则根本不会执行到“埋点”处

3.语句从开始进入终止逻辑，到终止逻辑完全完成，是由一个过程的。

kill无效的两类情况

1.线程没有执行到判断线程状态的逻辑。

2.终止逻辑耗时较长。

• 超大事务执行期间被Kill。回滚需要对事务执行期间生成的所有新数据版本做回收操作，耗时很长。
• 大查询回滚。产生了比较大的临时文件，删除临时文件可能需要等待IO资源，导致耗时边长。
• DDL命令执行到最后的阶段，如果被Kill，需要删除中间过程的临时文件，也可能受IO资源影响。

关于kill客户端的误解

1.Ctil + C 是MySQL客户端另外启动一个连接，然后发送一个kill query命令。

2.如果库里面的表特别多，连接就会很慢。

• 执行show databases;
• 切到db1库，执行show tables;
• 把这两个命令的结果用于构建一个本地的哈希表

真正耗时的步骤在第三步。我们感知的连接过程慢，其实并不是连接慢，也不是服务器慢，而是客户端慢。

如果在命令中加上-A，就可以关闭这个自动补全的功能，然后迅速返回。

3.-quick是一个更容易引起误会的参数，设置这个参数可能会降低服务端的性能。

客户端接受服务器返回结果方式两种

• 本地缓存，也就是在本地开一片内存，结果存起来。如果用API开发，对应就是mysql_store_result。
• 不缓存，读一个处理一个。如果用API开发，对应就是mysql_use_result。

MySQL客户端默认采用第一种方式，如果加上-quick，就会使用第二种不缓存的方式。

-quick可以达到以下的三种效果

• 跳过表名的自动补全功能。
• mysql_store_result需要申请本地内存来缓存结果，如果结果太大，可能会影响客户端本地机器性能。
• 不会把执行命令记录到本地的命令历史文件中。

误删数据，如何恢复？

误差数据的4个方法

1.使用delete语句误删数据行

2.使用drop table或者truncate table语句误删数据表

3.使用drop database 语句误删除数据库

4.使用rm 命令误删除整个MySQL实例。

误删行

利用Flashback工具通过闪回把数据恢复回来。需要确保binlog_format=row和binlog_row_image=FULL

• 恢复数据

具体恢复数据，对单个事务做如下处理

1.对于Insert语句，对应的binlog类型是write_rows_event，把它改写成delete_rows_event即可。

2.对于delete语句，将delete_rows_event改成为write_rows_event;

3.对于update，binlog里面记录了数据行修改前和修改后的值，对调这两行的位置即可。

对于多个事务，则需要将事务顺序调过来执行。

恢复数据比较安全的做法，是恢复出一个备份，找一个从库作为临时库，在这个临时库上执行这些操作，然后在将确认过的临时库的数据，恢复回主库。

• 如何预防

1.把sql_safe_updates参数设置为on，如果在update或者update写where条件或者没有包含索引，这条语句的执行就会报错。

2.代码上线前，必须经过SQL审计。

误删库/表

这种情况下，想要恢复数据，就需要使用全量备份，加增量日志的方式了。这个方案要求线上有定期的全量备份，并且实时备份binlog，恢复流程如下。

1.取出最近一次全量备份

2.用备份恢复出一个临时库

3.从日志备份里面，去除凌晨0点之后的日志

4.把这些日志，除了误删除数据的语句外，全部应用到临时库。

加速过程

1.可以使用mysqlbinlog命令，加上一个-database参数，用来指定误删除的库。

2.应用日志，需要跳过12点误操作的那个语句的binlog。

如果没有使用GTID模式，先用–stop-position参数执行到误操作之前的日志，然后再用-start-position从误操作之后的日志继续执行。

如果使用了GTID模式，那么只需要执行set gtid_next= gtid1;bengin;commit;先把这个GTID加到临时实例的GTID集合，之后按照顺序执行binlog的时候，就会自动跳过误操作的语句。

效率问题：1.如果是表，只需要重放这张表 2.mysqlbinlog解析过程是单线程，效率低。

一种加速的方法是,在用备份恢复出临时实例之后，将这个临时实例设置成线上备库的从库。

1.在 start slave 之前，先通过执行change replication filter replicate_do_table = (tbl_name) 命令，就可以让临时库只同步误操作的表；

2.这样做也可以用上并行复制技术，来加速整个数据恢复过程。

思路：误删库或者表后，恢复数据的思路主要就是备份，在加上应用binlog的方式。

延迟复制备库

如果有非常核心的业务，不允许太长的恢复时间，可以考虑搭建延迟复制的备库。MySQL 5.6版本引入。

延迟复制的备库是一种特殊的备库，通过CHANGE MASTER TO MASTER_DELAY = N 命令，可以指定这个备库持续保持跟主库有N秒的延迟。

预防误删库/表的方法

1.账号的分离。

• 只给开DML权限，而不给truncate/drop权限。而如果业务开发人员有DDL需求的话，也可以开发管理系统。
• 如果是DBA团队成员，日常也都规定只使用只读账号，必要的时候才使用更新账号。

2.制定操作规范。

• 在删除表之前，必须先对表做改名的操作。确保无影响后，再删除这张表。

• 改表名的是偶，要求表明加固定的后缀，然后删除表你的动作必须通过管理系统执行。

rm 删除数据

只要不是恶意地把整个集群删除，而是只删除其中的某一个节点数据的话，HA系统就会开始工作，选出一个新的主库，从而保证整个集群的正常工作。

如何判断一个数据库是不是出问题了？

select 1 判断

实际上，select 1成功返回，只能说这个库的进程还在，并不能说明主库没问题。

例如：innodb_thread_concurrency参数控制InnoDB的并发线程上限。一旦并发线程达到这个值，InnoDB在接收新请求的时候，就会进入等待状态，直到有线程退出。

通过innodb_thread_concurrency设置为64-128之间的值。

并发连接：show processlist的结果里，看到的几千个连接。

并发查询：当前正在执行的语句。

实际上，在线程进入锁等待以后，并发线程的计数会减1，也就是等待行锁（间隙锁）的线程不算在128以内。

查表判断

为了能够检测InnoDB并发线程过多导致额系统不可用的系统。一般的做法是，在系统库里创建一个表，比如命令为health check，里面只放一行数据，然后定期执行。

但是磁盘满的时候，会影响正常写数据，而不影响读数据。

更新判断

常见的作法是放一个timestamp字段，用来表示最后一次执行检测的时间。

可以在mysql.health_check表上存入多行数据，并采用server_id做主键，这样可以保证主、备各自的检测命令不会发生冲突。

外部检测天然有一个问题，就是随机性。外部检测都需要定时轮询，所以系统可能已经出问题了 ，但是却需要等到下一个检测发起执行语句的时候。

内部统计

MySQL5.6版本以后提供perfomance_schema库，就在file_summary_by_event_name表里统计了每次IO请求的时间。

每一次操作数据库，performance都需要额外地统计这些信息，所以存在性能损耗。建议只开启自己需要的统计项进行统计，然后根据时间判断耗时情况，如果大于指定秒数（200ms）则认为系统不可用。

如何正确显示随机消息

内存临时表

使用oder by rand()

对于InnoDB表，执行全字段排序会减少磁盘访问，因此会被优先选择。

对于内存表，回表过程只是简单地根据数据行的位置，直接访问内存得到数据，根本不会导致多访问磁盘，从而选择rowid排序。

select word from words order by rand() limit 3;

整个执行过程

1.创建一个临时表。这个临时表使用的是 memory 引擎，表里有两个字段，第一个字段是 double 类型，为了后面描述方便，记为字段 R，第二个字段是 varchar(64) 类型，记为字段 W。并且，这个表没有建索引。

2.从 words 表中，按主键顺序取出所有的 word 值。对于每一个 word 值，调用 rand() 函数生成一个大于 0 小于 1 的随机小数，并把这个随机小数和 word 分别存入临时表的 R 和 W 字段中，到此，扫描行数是 10000。

3.现在临时表有 10000 行数据了，接下来你要在这个没有索引的内存临时表上，按照字段 R 排序。

4.初始化 sort_buffer。sort_buffer 中有两个字段，一个是 double 类型，另一个是整型。

5.从内存临时表中一行一行地取出 R 值和位置信息（我后面会和你解释这里为什么是“位置信息”），分别存入 sort_buffer 中的两个字段里。

6.这个过程要对内存临时表做全表扫描，此时扫描行数增加 10000，变成了 20000。在 sort_buffer 中根据 R 的值进行排序。注意，这个过程没有涉及到表操作，所以不会增加扫描行数。

7.排序完成后，取出前三个结果的位置信息，依次到内存临时表中取出 word 值，返回给客户端。这个过程中，访问了表的三行数据，总扫描行数变成了 20003

如果你创建的表没有主键，或者把一个表的主键删掉了，那么 InnoDB 会自己生成一个长度为 6 字节的 rowid 来作为主键。

对于有主键的InnoDB表来说，整个rowid就是主键ID。

对于没有主键的InnoDB表来说，这个rowid就是系统生成的。

order by rand() 使用了内存临时表，内存临时表排序的时候使用了rowid排序算法。

磁盘临时表

tmp_table_size限制了内存表的大小，默认值是16M。如果临时表的大小超过了tmp_table_size，那么内存临时表就会转成磁盘临时表。

磁盘临时表默认使用InnoDB，是由参数Internal_tmp_disk_storage_engine取控制。

MySQL5.6引入：优先队列排序算法，不需要使用临时文件的算法(归并排序），而是采用了优先队列排序算法。

总之，不管使用那种类型的临时表，order_by_rand()这种写法都会让计算过程非常复杂，需要大量的扫描行数。

随机排序方法

1.取得整个表的行数，并记为C.

2.取得Y = floor(C*rand())。

3.再利用你limit Y，1取得一行。

加锁规则

加锁规则里面，包含了两个”原则‘、两个“优化”和一个“bug”。

1.原则1：加锁基本单位是next-key lock。前开后闭区间。

2.原则2：查找过程中访问到的对象才会加锁。

3.优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为 行锁。

4.优化2：索引上的等值查询，向右遍历且最后一个不满足等值条件的时候，next-key lock退化为间隙锁。

5.一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

案例一：等值查询间隙锁

案例二：非唯一索引等值锁

1. 根据原则 1，加锁单位是 next-key lock，因此会给 (0,5]加上 next-key lock。

2. 要注意 c 是普通索引，因此仅访问 c=5 这一条记录是不能马上停下来的，需要向右遍历，查到 c=10 才放弃。

3. 根据原则 2，访问到的都要加锁，因此要给 (5,10]加 next-key lock。但是同时这个符合优化 2：等值判断，向右遍历，最后一个值不满足 c=5 这个等值条件，因此退化成间隙锁 (5,10)。

4. 根据原则 2 ，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁，这就是为什么 session B 的 update 语句可以执行完成。

lock in share mode只锁覆盖索引，但是如果是for update 就不一样。执行for update，会顺便给主键索引上满足条件的行加上锁。

案例三：主键索引范围锁

mysql> select * from t where id=10 for update;
mysql> select * from t where id>=10 and id<11 for update;

语句1，只会对语句10进行加锁

而语句2则是行锁id =10 和 next-key（10，15】

案例四：非唯一索引范围锁

加锁范围为(5,10] 和 (10,15] 这两个 next-key lock。

案例五：唯一索引范围锁bug

yi因为id是唯一键，所以循环判断id = 15这一行就停止了 。但是实现上，InnoDB会往前扫面第一个不满足条件的行为止，因此（16，20】这个next - key -lock也会被锁上。

案例六：非唯一索引上存在“等值”的例子

这时，session A 在遍历的时候，先访问第一个 c=10 的记录。

同样地，根据原则 1，这里加的是 (c=5,id=5) 到 (c=10,id=10) 这个 next-key lock。

然后，session A 向右查找，直到碰到 (c=15,id=15) 这一行，循环才结束。

根据优化 2，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成 (c=10,id=10) 到 (c=15,id=15) 的间隙锁。

也就是说，这个 delete 语句在索引 c 上的加锁范围，就是下图中蓝色区域覆盖的部分。

案例七：limit语句加锁

这是因为，案例七里的 delete 语句明确加了 limit 2 的限制，因此在遍历到 (c=10, id=30) 这一行之后，满足条件的语句已经有两条，循环就结束了。因此，索引 c 上的加锁范围就变成了从（c=5,id=5) 到（c=10,id=30) 这个前开后闭区间，如下图所示：

在删除数据的时候尽量加limit。这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围。

案例八：一个死锁的例子

session A 启动事务后执行查询语句加 lock in share mode，在索引 c 上加了 next-key lock(5,10] 和间隙锁 (10,15)；

session B 的 update 语句也要在索引 c 上加 next-key lock(5,10] ，进入锁等待；

然后 session A 要再插入 (8,8,8) 这一行，被 session B 的间隙锁锁住。由于出现了死锁，InnoDB 让 session B 回滚。

其实是这样的，session B 的“加 next-key lock(5,10] ”操作，实际上分成了两步，先是加 (5,10) 的间隙锁，加锁成功；然后加 c=10 的行锁，这时候才被锁住的。

实际上next-key lock 来分析。具体执行的时候，要分成间隙锁和行锁两端来执行的。

读已提交除在外键条件下不存在间隙锁，还有个优化，即：语句执行过程中加上的行锁，在语句执行完成后，就把“不满足条件的行”上的行锁直接释放了，不需要等到事务提交。

什么是幻读

1.在可重复读的级别下，普通的查询时快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读“下才会出现。

2.上面sessionB的修改结果，被session A之后的select 语句用”当前读“看到，不能称为幻读。幻读仅专指”新插入的行“。

幻读有什么问题

1.破环了语义上的意思。”要把所有满足条件的行锁住，不准别的事务进行读写操作“

2.数据一致性的问题。

即使把所有的记录都加上锁，还是阻止不了新插入的记录。

如何解决幻读

1.产生幻读的原因是，行锁只能锁住行，但是新插入这个动作，要更新记录之间的”间隙“。因此，为了解决幻读，InnoDB引入新的锁，也就是间隙锁（Gap Lock）。

就这样不止给数据库中已有的6个记录加上行锁，还同时加了7个间隙锁。这样确保了无法插入新的记录。

跟间隙锁存在冲突关系的，是”往这个间隙中插入一个记录“这个操作。

间隙锁和行锁合成next-key lock，每个next-key lock是前开后闭区间。例如：分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。

间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。

间隙锁是在可重复读隔离级别下才会生效的。如果把隔离级别设置成读提交，就没有间隙锁。但是需要解决数据和日志不一致的问题，需要把binlog格式设置为row。