G1 算法总结

August 20th, 2019 by JasonLe's Tech 108 views

G1收集器堆结构:

  • heap被划分为一个个相等的不连续的内存区域(regions),每个regions都有一个分代的角色:eden、survivor、old。
  • 对每个角色的数量并没有强制的限定,也就是说对每种分代内存的大小,可以动态变化。
  • G1最大的特点就是高效的执行回收,优先去执行那些大量对象可回收的区域(region)。

G1分成四个阶段回收:

新生代收集 -> 并发垃圾收集 -> 混合收集(Mixed GC) -> Full GC

其中新生代蕴含着CMS的清理:

当对象生成在EDEN区失败时,出发一次YGC,先扫描EDEN区中的存活对象,进入S0区,S0放不下的进入OLD区,再扫描S1区,若存活次数超过阀值则进入OLD区,其它进入S0区,然后S0和S1交换一次。

那么当发生YGC时,JVM会首先检查老年代最大的可用连续空间是否大于新生代所有对象的总和,如果大于,那么这次YGC是安全的,如果不大于的话,JVM就需要判断HandlePromotionFailure是否允许空间分配担保。

JVM继续检查老年代最大的可用连续空间是否大于历次晋升到老年代的对象的平均大小,如果大于,则正常进行一次YGC,尽管有风险(因为判断的是平均大小,有可能这次的晋升对象比平均值大很多);

如果小于,或者HandlePromotionFailure设置不允许空间分配担保,这时要进行一次Full GC。

==========

另外Mixed GC既包括YGC也包括Old GC,另外会把Humongous regions给清除掉

For G1 GC, any object that is more than half a region size is considered a “Humongous object“. Such an object is allocated directly in the old generation into “Humongous regions”. These Humongous regions are a contiguous set of regions.

Dead Humongous objects are freed at the end of the marking cycle during the cleanup phase also during a full garbage collection cycle.

Full GC 就不说了,会清理整个Heap,就是STW,在生产环境中尽量避免Full GC,另外无论什么GC,都要注意thread的停止时间。

https://stackoverflow.com/questions/35380827/g1-what-are-the-differences-between-mixed-gc-and-full-gc

G1 日志查看

August 12th, 2019 by JasonLe's Tech 113 views

如果你要在生产环境中使用G1 GC,下面这些跟日志相关的参数是必备的,有了这些参数,你才能排查基本的垃圾回收问题。

使用-XX:GCLogFileSize设置合适的GC日志文件大小,使用-XX:NumberOfGCLogFiles设置要保留的GC日志文件个数,使用-Xloggc:/path/to/gc.log设置GC日志文件的位置,通过上面三个参数保留应用在运行过程中的GC日志信息,我建议最少保留一个星期的GC日志,这样应用的运行时信息足够多的,方便排查问题。

新生代收集

和其他垃圾收集器一样,G1也使用-XX:PrintGCDetails打印出详细的垃圾收集日志,下面这张图是新生代收集的标准流程,我在这里将它分成了6个步骤:

四个关键信息

  • 新生代垃圾收集发生的时间——2016-12-12T10:40:18.811-0500,通过设置-XX:+PrintGCDateStamps参数可以打印出这个时间;
  • JVM启动后的相对时间——25.959
  • 这次收集的类型——新生代收集,只回收Eden分区
  • 这次收集花费的时间——0.0305171s,即30ms

列出了新生代收集中并行收集的详细过程

  • Parallel Time:并行收集任务在运行过程中引发的STW(Stop The World)时间,从新生代垃圾收集开始到最后一个任务结束,共花费26.6ms
  • GC Workers:有4个线程负责垃圾收集,通过参数-XX:ParallelGCThreads设置,这个参数的值的设置,跟CPU有关,如果物理CPU支持的线程个数小于8,则最多设置为8;如果物理CPU支持的线程个数大于8,则默认值为number * 5/8
  • GC Worker Start:第一个垃圾收集线程开始工作时JVM启动后经过的时间(min);最后一个垃圾收集线程开始工作时JVM启动后经过的时间(max);diff表示min和max之间的差值。理想情况下,你希望他们几乎是同时开始,即diff趋近于0。
  • Ext Root Scanning:扫描root集合(线程栈、JNI、全局变量、系统表等等)花费的时间,扫描root集合是垃圾收集的起点,尝试找到是否有root集合中的节点指向当前的收集集合(CSet)
  • Update RS(Remembered Set or RSet):每个分区都有自己的RSet,用来记录其他分区指向当前分区的指针,如果RSet有更新,G1中会有一个post-write barrier管理跨分区的引用——新的被引用的card会被标记为dirty,并放入一个日志缓冲区,如果这个日志缓冲区满了会被加入到一个全局的缓冲区,在JVM运行的过程中还有线程在并发处理这个全局日志缓冲区的dirty card。Update RS表示允许垃圾收集线程处理本次垃圾收集开始前没有处理好的日志缓冲区,这可以确保当前分区的RSet是最新的。
    • Processed Buffers,这表示在Update RS这个过程中处理多少个日志缓冲区。
  • Scan RS:扫描每个新生代分区的RSet,找出有多少指向当前分区的引用来自CSet。
  • Code Root Scanning:扫描代码中的root节点(局部变量)花费的时间
  • Object Copy:在疏散暂停期间,所有在CSet中的分区必须被转移疏散,Object Copy就负责将当前分区中存活的对象拷贝到新的分区。
  • Termination:当一个垃圾收集线程完成任务时,它就会进入一个临界区,并尝试帮助其他垃圾线程完成任务(steal outstanding tasks),min表示该垃圾收集线程什么时候尝试terminatie,max表示该垃圾收集回收线程什么时候真正terminated。
    • Termination Attempts:如果一个垃圾收集线程成功盗取了其他线程的任务,那么它会再次盗取更多的任务或再次尝试terminate,每次重新terminate的时候,这个数值就会增加。
  • GC Worker Other:垃圾收集线程在完成其他任务的时间
  • GC Worker Total:展示每个垃圾收集线程的最小、最大、平均、差值和总共时间。
  • GC Worker End:min表示最早结束的垃圾收集线程结束时该JVM启动后的时间;max表示最晚结束的垃圾收集线程结束时该JVM启动后的时间。理想情况下,你希望它们快速结束,并且最好是同一时间结束。

列出了新生代GC中的一些任务:

  • Code Root Fixup :释放用于管理并行垃圾收集活动的数据结构,应该接近于0,该步骤是线性执行的;
  • Code Root Purge:清理更多的数据结构,应该很快,耗时接近于0,也是线性执行。
  • Clear CT:清理card table

包含一些扩展功能

  • Choose CSet:选择要进行回收的分区放入CSet(G1选择的标准是垃圾最多的分区优先,也就是存活对象率最低的分区优先)
  • Ref Proc:处理Java中的各种引用——soft、weak、final、phantom、JNI等等。
  • Ref Enq:遍历所有的引用,将不能回收的放入pending列表
  • Redirty Card:在回收过程中被修改的card将会被重置为dirty
  • Humongous Register:JDK8u60提供了一个特性,巨型对象可以在新生代收集的时候被回收——通过G1ReclaimDeadHumongousObjectsAtYoungGC设置,默认为true。
  • Humongous Reclaim:做下列任务的时间:确保巨型对象可以被回收、释放该巨型对象所占的分区,重置分区类型,并将分区还到free列表,并且更新空闲空间大小。
  • Free CSet:将要释放的分区还回到free列表。
  • 展示了不同代的大小变化,以及堆大小的自适应调整。
    • Eden:1097.0M(1097.0M)->0.0B(967.0M):(1)当前新生代收集触发的原因是Eden空间满了,分配了1097M,使用了1097M;(2)所有的Eden分区都被疏散处理了,在新生代结束后Eden分区的使用大小成为了0.0B;(3)Eden分区的大小缩小为967.0M
    • Survivors:13.0M->139.0M:由于年轻代分区的回收处理,survivor的空间从13.0M涨到139.0M;
    • Heap:1694.4M(2048.0M)->736.3M(2048.0M):(1)在本次垃圾收集活动开始的时候,堆空间整体使用量是1694.4M,堆空间的最大值是2048M;(2)在本次垃圾收集结束后,堆空间的使用量是763.4M,最大值保持不变。
  • 第6点展示了本次新生代垃圾收集的时间
    • user=0.8:垃圾收集线程在新生代垃圾收集过程中消耗的CPU时间,这个时间跟垃圾收集线程的个数有关,可能会比real time大很多;
    • sys=0.0:内核态线程消耗的CPU时间 –real=0.03:本次垃圾收集真正消耗的时间;

并发垃圾收集

G1的第二种收集活动是并发垃圾收集,并发垃圾收集的触发条件有很多,但是做的工作都相同,它的日志如下图所示:

标志着并发垃圾收集阶段的开始:

  • GC pause(G1 Evacuation Pause)(young)(initial-mark):为了充分利用STW的机会来trace所有可达(存活)的对象,initial-mark阶段是作为新生代垃圾收集中的一部分存在的(搭便车)。initial-mark设置了两个TAMS(top-at-mark-start)变量,用来区分存活的对象和在并发标记阶段新分配的对象。在TAMS之前的所有对象,在当前周期内都会被视作存活的。

表示第并发标记阶段做的第一个事情:根分区扫描

  • GC concurrent-root-region-scan-start:根分区扫描开始,根分区扫描主要扫描的是新的survivor分区,找到这些分区内的对象指向当前分区的引用,如果发现有引用,则做个记录;
  • GC concurrent-root-region-scan-end:根分区扫描结束,耗时0.0030613s

表示并发标记阶段

  • GC Concurrent-mark-start:并发标记阶段开始。(1)并发标记阶段的线程是跟应用线程一起运行的,不会STW,所以称为并发;并发标记阶段的垃圾收集线程,默认值是Parallel Thread个数的25%,这个值也可以用参数-XX:ConcGCThreads设置;(2)trace整个堆,并使用位图标记所有存活的对象,因为在top TAMS之前的对象是隐式存活的,所以这里只需要标记出那些在top TAMS之后、阈值之前的;(3)记录在并发标记阶段的变更,G1这里使用了SATB算法,该算法要求在垃圾收集开始的时候给堆做一个快照,在垃圾收集过程中这个快照是不变的,但实际上肯定有些对象的引用会发生变化,这时候G1使用了pre-write barrier记录这种变更,并将这个记录存放在一个SATB缓冲区中,如果该缓冲区满了就会将它加入到一个全局的缓冲区,同时G1有一个线程在并行得处理这个全局缓冲区;(4)在并发标记过程中,会记录每个分区的存活对象占整个分区的大小的比率;
  • GC Concurrent-mark-end:并发标记阶段结束,耗时0.3055438s
  • 重新标记阶段,会Stop the World
    • Finalize Marking:Finalizer列表里的Finalizer对象处理,耗时0.0014099s;
    • GC ref-proc:引用(soft、weak、final、phantom、JNI等等)处理,耗时0.0000480s;
    • Unloading:类卸载,耗时0.0025840s;
    • 除了前面这几个事情,这个阶段最关键的结果是:绘制出当前并发周期中整个堆的最后面貌,剩余的SATB缓冲区会在这里被处理,所有存活的对象都会被标记;
  • 清理阶段,也会Stop the World
    • 计算出最后存活的对象:标记出initial-mark阶段后分配的对象;标记出至少有一个存活对象的分区;
    • 为下一个并发标记阶段做准备,previous和next位图会被清理;
    • 没有存活对象的老年代分区和巨型对象分区会被释放和清理;
    • 处理没有任何存活对象的分区的RSet;
    • 所有的老年代分区会按照自己的存活率(存活对象占整个分区大小的比例)进行排序,为后面的CSet选择过程做准备;
  • 并发清理阶段
    • GC concurrent-cleanup-start:并发清理阶段启动。完成第5步剩余的清理工作;将完全清理好的分区加入到二级free列表,等待最终还会到总体的free列表;
    • GC concurrent-cleanup-end:并发清理阶段结束,耗时0.0012954s

混合收集

在并发收集阶段结束后,你会看到混合收集阶段的日志,如下图所示,该日志的大部分跟之前讨论的新生代收集相同,只有第1部分不一样:GC pause(G1 Evacuation Pause)(mixed),0.0129474s,这一行表示这是一个混合垃圾收集周期;在混合垃圾收集处理的CSet不仅包括新生代的分区,还包括老年代分区——也就是并发标记阶段标记出来的那些老年代分区。

Full GC

如果堆内存空间不足以分配新的对象,或者是Metasapce空间使用率达到了设定的阈值,那么就会触发Full GC——你在使用G1的时候应该尽量避免这种情况发生,因为G1的Full Gc是单线程、会Stop The World,代价非常高。Full GC的日志如下图所示,从中你可以看出三类信息

  1. Full GC的原因,这个图里是Allocation Failure,还有一个常见的原因是Metadata GC Threshold;
  2. Full GC发生的频率,每隔几天发生一次Full GC还可以接受,但是每隔1小时发生一次Full GC则不可接受;
  3. Full GC的耗时,这张图里的Full GC耗时150ms(PS:按照我的经验,实际运行中如果发生Full GC,耗时会比这个多很多)

基础配置参数中,我这里还想介绍两个:-XX:+PrintGCApplicationStoppedTime-XX:+PrintGCApplicationConcurrentTime,这两个参数也可以为你提供有用的信息,如下图所示:
  1. 记录了应用线程在安全点被暂停的总时间(也就是STW的总时间)
  2. 记录了让所有应用线程进入安全点所花费的总时间
  3. 记录了在两个安全点之间应用线程运行的时间

https://www.redhat.com/en/blog/collecting-and-reading-g1-garbage-collector-logs-part-2?source=author&term=22991

《MySQL实战45讲》学习笔记 31~45讲

July 2nd, 2019 by JasonLe's Tech 128 views

31 | 误删数据后除了跑路,还能怎么办?

千万不能误删

误删行

binlog_format=row 和 binlog_row_image=FULL 可以使用Flashback回放。
不建议直接在主库使用,应该在备库执行,然后再将确认过的临时库的数据,恢复回主库。

误删库 / 表

取全量备份,和全量备份时间点之后的binlog恢复。但mysqlbinlog不够快。
一个加速的方法,将全量备份恢复的临时实例,设置为线上备库的从库。

延迟复制备库

MySQL 5.6 版本引入,通过 CHANGE MASTER TO MASTER_DELAY = N 命令,可以指定这个备库持续保持跟主库有N 秒的延迟。

32 | 为什么还有kill不掉的语句?

kill query + 线程 id:表示终止这个线程中正在执行的语句;

kill connection + 线程 id,这里 connection 可缺省,表示断开这个线程的连接,如果这个线程有语句正在执行,也是要先停止正在执行的语句的。

mysql kill命令不是直接终止线程。

  1. 把 session 的运行状态改成 THD::KILL_QUERY(将变量 killed 赋值为 THD::KILL_QUERY);
  2. 给 session 的执行线程发一个信号。有些session 由于锁在等待,信号让session 退出等待来处理THD::KILL_QUERY 状态。

mysql处理过程中有许多埋点,这些“埋点”的地方判断线程状态,如果发现线程状态是 THD::KILL_QUERY,才开始进入语句终止逻辑。

如果碰到一个被 killed 的事务一直处于回滚状态,尽量不要重启,因为重启之后该做的回滚动作还是不能少的,所以从恢复速度的角度来说,应该让它自己结束。如果这个语句可能会占用别的锁,或者由于占用 IO 资源过多,从而影响到了别的语句执行的话,就需要先做主备切换,切到新主库提供服务。避免大事务。

33 | 我查这么多数据,会不会把数据库内存打爆?

全表扫描对 server 层的影响

net_buffer由参数 net_buffer_length 定义的,默认是 16k。
mysql是遍读遍发的,所以当net_buffer写满的时候就需要等待。使用show processlist可以看到state=“Sending to client”。
mysql还要一个state=“Sending data”,它的意思只是“正在执行”。

全表扫描对 InnoDB 的影响

介绍 WAL 机制时,分析了Buffer Pool 加速更新的作用。Buffer Pool 还有一个更重要的作用,就是加速查询。执行 show engine innodb status可以查看一个系统当前的 BP 命中率。
InnoDB Buffer Pool 的大小是由参数 innodb_buffer_pool_size 确定的,一般建议设置成可用物理内存的 60%~80%。

InnoDB 内存管理用的是最近最少使用 (LRU) 算法,这个算法的核心就是淘汰最久未使用的数据。如果在查询历史数据使用这个算法,会导致很多请求会从磁盘读取数据。所以mysql对LRU算法进行了改进。

在 InnoDB 实现上,按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。

  1. 访问数据页P3,在young区所以把它移到链表同步。
  2. 如果访问不存在的数据,则把链表尾部数据淘汰,但把新数据页Px放在LRU_old处。
  3. 处于 old 区域的数据页,每次被访问的时候都要做下面这个判断:
    若这个数据页在 LRU 链表中存在的时间超过了 1 秒,就把它移动到链表头部;
    如果这个数据页在 LRU 链表中存在的时间短于 1 秒,位置保持不变。

1s由参数 innodb_old_blocks_time 控制的。其默认值是 1000,单位毫秒。

34 | 到底可不可以使用join?

表结构

CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`)
) ENGINE=InnoDB;

drop procedure idata;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=1000)do
insert into t2 values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();

create table t1 like t2;
insert into t1 (select * from t2 where id<=100)

Index Nested-Loop Join

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

t1 是驱动表,t2 是被驱动表。

  1. 从表 t1 中读入一行数据 R;
  2. 从数据行 R 中,取出 a 字段到表 t2 里去查找;
  3. 取出表 t2 中满足条件的行,跟 R 组成一行,作为结果集的一部分;
  4. 重复执行步骤 1 到 3,直到表 t1 的末尾循环结束。

t1只有100行,所有一共扫描200行。
如果执行select * from t1,再执行select * from t2 where a=$R.a。虽然都可以走索引,也只扫描200行。但需要执行101行sql。
如果可以走索引:

  1. 使用 join 语句,性能比强行拆成多个单表执行 SQL 语句的性能要好;
  2. 如果使用 join 语句的话,需要让小表做驱动表。

Simple Nested-Loop Join

如果驱动表用不上索引。select * from t1 straight_join t2 on (t1.a=t2.b);

因为t2.b没有索引,所以需要全表扫描。总共需扫描100*1000行。

MySQL 没有使用 Simple Nested-Loop Join 算法,而是使用了“Block Nested-Loop Join”算法,简称BNL。

Block Nested-Loop Join

  1. 把表 t1 的数据读入线程内存 join_buffer 中,由于我们这个语句中写的是 select *,因此是把整个表 t1 放入了内存;
  2. 扫描表 t2,把表 t2 中的每一行取出来,跟 join_buffer 中的数据做对比,满足 join 条件的,作为结果集的一部分返回。

虽然都会扫描100*1000行,但BNL是内存判断,所以会快一点。

小结

  1. 如果可以使用被驱动表的索引,join 语句还是有其优势的;
  2. 不能使用被驱动表的索引,只能使用 BNL 算法,这样的语句就尽量不要使用;
  3. 在使用 join 的时候,应该让小表做驱动表。

如果被驱动表是个大表,会把冷数据的page加入到buffer pool,并且BNL要扫描多次,两次扫描的时间可能会超过1秒,使上节提到的分代LRU优化失效,把热点数据从buffer pool中淘汰掉,影响正常业务的查询效率。

35 | join语句怎么优化?

表结构

create table t1(id int primary key, a int, b int, index(a));
create table t2 like t1;
drop procedure idata;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=1000)do
insert into t1 values(i, 1001-i, i);
set i=i+1;
end while;

set i=1;
while(i<=1000000)do
insert into t2 values(i, i, i);
set i=i+1;
end while;

end;;
delimiter ;
call idata();

Multi-Range Read 优化

回表是指,InnoDB 在普通索引 a 上查到主键 id 的值后,再根据一个个主键 id 的值到主键 id 的值到主键索引上去查整行数据的过程。主键索引是一棵 B+ 树,在这棵树上,每次只能根据一个主键 id 查到一行数据。因此,回表肯定是一行行搜索主键索引的。

如果随着 a 的值递增顺序查询的话,id 的值就变成随机的,那么就会出现随机访问,性能相对较差。虽然“按行查”这个机制不能改,但是调整查询的顺序,还是能够加速的。

因为大多数的数据都是按照主键递增顺序插入得到的,所以我们可以认为,如果按照主键的递增顺序查询的话,对磁盘的读比较接近顺序读,能够提升读性能。
MRR 优化的设计思路:

  1. 根据索引 a,定位到满足条件的记录,将 id 值放入 read_rnd_buffer 中 ;
  2. 将 read_rnd_buffer 中的 id 进行递增排序;
  3. 排序后的 id 数组,依次到主键 id 索引中查记录,并作为结果返回。

read_rnd_buffer 的大小是由 read_rnd_buffer_size 参数控制。如果想要稳定地使用 MRR 优化的话,需要设置set optimizer_switch=“mrr_cost_based=off”,如果不设置,优化器会判断消耗,倾向于不使用MRR。

Batched Key Access

MySQL 在 5.6 版本后开始引入的 Batched Key Acess(BKA) 算法了。其实就是对 NLJ 算法的优化。
NLJ 算法执行的逻辑是:从驱动表 t1,一行行地取出 a 的值,再到被驱动表 t2 去做 join。也就是说,对于表 t2 来说,每次都是匹配一个值。这时MRR 的优势就用不上了。
BKA 算法就是缓存多行传给其他表,流程如下:

启动BKA:set optimizer_switch=‘mrr=on,mrr_cost_based=off,batched_key_access=on’;

BNL 算法的性能问题

上篇文章末尾说了,如果一个使用 BNL 算法的 join 语句,多次扫描一个冷表,而且这个语句执行时间超过 1 秒,就会在再次扫描冷表的时候,把冷表的数据页移到LRU 链表头部。
为了减少这种影响,可以考虑增大join_buffer_size 的值,减少对被驱动表的扫描次数。

优化的常见做法是,给被驱动表的 join 字段加上索引,把 BNL 算法转成 BKA 算法。
还可以考虑使用临时表。使用临时表的大致思路是:

  1. 把表 t2 中满足条件的数据放在临时表 tmp_t 中;
  2. 为了让 join 使用 BKA 算法,给临时表 tmp_t 的字段 b 加上索引;
  3. 让表 t1 和 tmp_t 做 join 操作。

create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

扩展 -hash join

mysql目前还没有hash索引,MariaDB支持。
所以可以自己实现在业务端。实现流程大致如下:

  1. select * from t1;取得表 t1 的全部 1000 行数据,在业务端存入一个 hash 结构;
  2. select * from t2 where b>=1 and b<=2000; 获取表 t2 中满足条件的 2000 行数据。
  3. 把这 2000 行数据,一行一行地取到业务端,到 hash 结构的数据表中寻找匹配的数据。满足匹配的条件的这行数据,就作为结果集的一行。

这个过程会比临时表方案的执行速度还要快一些。

36 | 为什么临时表可以重名?

上节提到了临时表。
如果是使用 InnoDB 引擎或者 MyISAM 引擎的临时表,写数据的时候是写到磁盘上的。当然,临时表也可以使用 Memory 引擎。
临时表的特点:

  1. 临时表只能被创建它的 session 访问,对其他线程不可见。所以在这个 session 结束的时候,会自动删除临时表。
  2. 临时表可以与普通表同名(还是不要这么做)。
  3. session A 内有同名的临时表和普通表的时候,show create 语句,以及增删改查语句访问的是临时表。
  4. show tables 命令不显示临时表。

临时表的应用

分表分库跨库查询
分库分表系统都有一个中间层 proxy,如果 sql 能够直接确定某个分表,这种情况是最理想的。
但如果涉及到跨库,一般有两种方式

  1. 在 proxy 层的进程代码中实现排序,但对 proxy 的功能和性能要求较高。
  2. 把各个分库拿到的数据,汇总到一个 MySQL 实例的一个表中,然后在这个汇总实例上做逻辑操作。如果每个分库的计算量都不饱和,那么直接可以在把临时表放到某个分库上。

为什么临时表可以重名?

MySQL 要给临时 InnoDB 表创建一个 frm 文件保存表结构定义,还要有地方保存表数据。
这个 frm 文件放在临时文件目录下,文件名的后缀是.frm,前缀是“#sql{进程 id}_{线程 id}_ 序列号”。可以使用 select @@tmpdir 命令,来显示实例的临时文件目录。

表中数据存放:

  1. MySQL5.6 会在临时文件目录下创建一个相同前缀、以.ibd 为后缀的文件,用来存放数据文件;
  2. MySQL5.7版本开始引入了一个临时文件表空间,专门用来存放临时文件的数据。因此,我们就不需要再创建 ibd 文件了。

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

  1. 一个普通表的 table_def_key 的值是由“库名+ 表名”得到。
  2. 临时表,table_def_key 在“库名 + 表名”基础上,又加入了“server_id+thread_id”。

临时表和主备复制

如果当前的 binlog_format=row,那么跟临时表有关的语句,就不会记录到 binlog 里。
binlog_format=statment/mixed 的时候,binlog 中才会记录临时表的操作。
这种情况下,创建临时表的语句会传到备库执行,因此备库的同步线程就会创建这个临时表。主库在线程退出的时候,会自动删除临时表,但是备库同步线程是持续在运行的。所以,这时候我们就需要在主库上再写一个 DROP TEMPORARY TABLE 传给执行。

37 | 什么时候会使用内部临时表?

create table t1(id int primary key, a int, b int, index(a));
delimiter ;;
create procedure idata()
begin
declare i int;

set i=1;
while(i<=1000)do
insert into t1 values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();

执行这条语句(select 1000 as f) union (select id from t1 order by id desc limit 2);

  • 第二行key=PRIMARY,说明第二个子句用到了索引 id
  • 第三行 Extra 字段,说明 UNION 时使用了临时表 (Using temporary)

执行流程:

  1. 创建一个内存临时表,这个临时表只有一个整型字段 f,并且 f 是主键字段。
  2. 执行第一个子查询,得到 1000 这个值,并存入临时表中。
  3. 执行第二个子查询:拿到第一个1000,但已存在存入失败,拿到第二个999,存入成功。
  4. 从临时表中按行取出数据(1000和999两行),返回结果,并删除临时表。

如果把上面这个语句中的 union 改成 union all的话,就不需要“去重”。这样执行的时候,就依次执行子查询,得到的结果直接作为结果集的一部分,发给客户端。因此也就不需要临时表了。

group by 执行流程

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

  1. Using index,表示这个语句使用了覆盖索引,选择了索引 a,不需要回表;
  2. Using temporary,表示使用了临时表;
  3. Using filesort,表示需要排序。

执行流程:

  1. 创建内存临时表,表里有两个字段 m 和 c,主键是 m;
  2. 扫描表 t1 的索引 a,依次取出叶子节点上的 id 值,计算 id%10 的结果,记为 x;如果临时表中没有主键为 x 的行,就插入一个记录 (x,1),如果表中有主键为 x 的行,就将 x 这一行的 c 值加 1;
  3. 遍历完成后,再根据字段 m 做排序(内存临时表的排序 17 篇文章有),得到结果集返回给客户端。

如果不需要排序则直接取内存临时表的数据。

但内存临时表的大小是有限制的,参数 tmp_table_size 就是控制这个内存大小的,默认是 16M。如果内存不够则使用磁盘临时表。

group by 优化方法

索引

如果可以确保输入的数据是有序的,那么计算 group by 的时候,就只需要从左到右,顺序扫描,依次累加。

  1. 当碰到第一个 1 的时候,已经知道累积了 X 个 0,结果集里的第一行就是 (0,X);
  2. 当碰到第一个 2 的时候,已经知道累积了 Y 个 1,结果集里的第二行就是 (1,Y);

InnoDB 的索引,就可以满足这个输入有序的条件。
直接排序
如果临时表数据量特别大,可让 MySQL 直接走磁盘临时表,在 group by 语句中加入 SQL_BIG_RESULT 这个提示(hint)。
MySQL 的优化器会直接用数组来存,而不是B+ 树存储。这样 select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;

执行流程:

  1. 初始化 sort_buffer,确定放入一个整型字段,记为 m;
  2. 扫描表 t1 的索引 a,依次取出里面的 id 值, 将 id%100 的值存入 sort_buffer 中;
  3. 扫描完成后,对 sort_buffer 的字段 m 做排序(如果 sort_buffer 内存不够用,,就会利用磁盘临时文件辅助排序);
  4. 排序完成后,就得到了一个有序数组。

总结:

MySQL 什么时候会使用内部临时表?

  1. 如果语句执行过程可以一边读数据,一边直接得到结果,是不需要额外内存的,否则就需要额外的内存,来保存中间结果;
  2. join_buffer 是无序数组,sort_buffer 是有序数组,临时表是二维表结构;
  3. 如果执行逻辑需要用到二维表特性,就会优先考虑使用临时表。比如,union 需要用到唯一索引约束, group by 还需要用到另外一个字段来存累积计数。

group by使用的指导原则:

  1. 如果对 group by 语句的结果没有排序要求,要在语句后面加 order by null;
  2. 尽量让 group by 过程用上表的索引,确认方法是 explain 结果里没有 Using temporary 和 Using filesort;
  3. 如果 group by 需要统计的数据量不大,尽量只使用内存临时表;也可以通过适当调大 tmp_table_size 参数,来避免用到磁盘临时表;
  4. 如果数据量实在太大,使用 SQL_BIG_RESULT 这个提示,来告诉优化器直接使用排序算法得到 group by 的结果。

38 | 都说InnoDB好,那还要不要使用Memory引擎?

内存表的数据组织结构

表 t1 使用 Memory 引擎, 表 t2 使用InnoDB 引擎。

create table t1(id int primary key, c int) engine=Memory;
create table t2(id int primary key, c int) engine=innodb;
insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);
insert into t2 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);

可以看到两个引擎顺序不一致。
InnoDB 表的数据就放在主键索引树上,主键索引是 B+ 树。
与 InnoDB 引擎不同,Memory 引擎的数据和索引是分开的。

内存表的数据部分以数组的方式单独存放,而主键 id 索引里,存的是每个数据的位置。主键 id 是 hash 索引,可以看到索引上的 key 并不是有序的。
在内存表 t1 中,执行 select * 按数组顺序全表扫描。因此,0 就是最后一个被读到。

所以InnoDB 和 Memory 引擎的数据组织方式是不同的:

  • InnoDB 引擎把数据放在主键索引上,其他索引上保存的是主键 id。这种方式,我们称之为索引组织表(Index Organizied Table)。
  • Memory 引擎采用的是把数据单独存放,索引上保存数据位置的数据组织形式,我们称之为堆组织表(Heap Organizied Table)。

两个引擎的一些典型不同:

  1. InnoDB 表的数据总是有序存放的,而内存表的数据就是按照写入顺序存放的;
  2. 当数据文件有空洞的时候,InnoDB 表在插入新数据的时候,为了保证数据有序性,只能在固定的位置写入新值,而内存表找到空位就可以插入新值;
  3. 数据位置发生变化的时候,InnoDB 表只需要修改主键索引,而内存表需要修改所有索引;
  4. InnoDB 表用主键索引查询时需要走一次索引查找,用普通索引查询的时候,需要走两次索引查找。而内存表没有这个区别,所有索引的“地位”都是相同的。
  5. InnoDB 支持变长数据类型,不同记录的长度可能不同;内存表不支持 Blob 和 Text 字段,并且即使定义了 varchar(N),实际也当作 char(N),也就是固定长度字符串来存储,因此内存表的每行数据长度相同。

由于内存表的这些特性,每个数据行被删除以后,空出的这个位置都可以被接下来要插入的数据复用。内存表 t1 的这个主键索引是哈希索引,因此如果执行范围查询是用不上主键索引的,需要走全表扫描。

hash 索引和 B-Tree 索引

内存表也是支持 B-Tree 索引的alter table t1 add index a_btree_index using btree (id);

不建议你在生产环境上使用内存表,这里的原因主要包括两个方面:
内存表的锁
内存表不支持行锁,只支持表锁。
数据持久性问题
数据库重启的时候,所有的内存表都会被清空。
主从模式,从库掉电重启收到主库请求会找不到行。双主模式下,一台掉电重启会发送delete到另一台清空数据。

create temporary table temp_t(id int primary key, a int, b int, index (b))engine=memory;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

39 | 自增主键为什么不是连续的?

不同的引擎对于自增值的保存策略不同。

  • MyISAM 引擎的自增值保存在数据文件中。
  • InnoDB 引擎保存在内存里,MySQL 8.0 版本后才有自增值持久化能力,记录在redo log中。MySQL 5.7 及之前的版本,每次重启后,第一次打开表的时候,都会去找自增值的最大值 max(id),然后将 max(id)+1 作为这个表当前的自增值。

自增值修改机制

如果字段 id 被定义为 AUTO_INCREMENT

  1. 如果插入数据时 id 字段指定为 0、null 或未指定值,那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段;
  2. 如果插入数据时 id 字段指定了具体的值,就直接使用语句里指定的值。

假设,某次要插入的值是 X,当前的自增值是 Y。

  1. 如果 X<Y,那么这个表的自增值不变;
  2. 如果>=Y,就需要把当前自增值修改为新的自增值。

新的自增值生成算法是:从 auto_increment_offset 开始,以 auto_increment_increment 为步长,持续叠加,直到找到第一个大于 X 的值,作为新的自增值。(双主架构可以设置一个库的自增id都是奇数,另一个都是偶数)。

自增值的修改时机

自增值会在插入数据之前自增。
所以唯一键冲突是导致自增主键 id 不连续的第一种原因。类似,事务回滚也会产生类似的现象。

自增锁的优化

MySQL 5.1.22 版本引入了一个新策略,新增参数 innodb_autoinc_lock_mode,默认值是 1。

  1. 语句执行结束后才释放锁;
  2. 普通 insert 语句,自增锁在申请之后就马上释放;类似 insert … select 这样的批量插入数据的语句,自增锁还是要等语句结束后才被释放;
  3. 这个参数的值被设置为 2 时,所有的申请自增主键的动作都是申请自增主键的动作都是申请后就释放锁。

生产上,如果有insert … select、replace … select 和 load data 语句,这种批量插入数据的场景时,建议设置:innodb_autoinc_lock_mode=2 ,并且 binlog_format=row。
对于批量插入数据的语句,MySQL 有一个批量申请自增 id 的策略:同一个语句去申请自增 id,每次申请到的自增 id 个数都是上一次的两倍。所以如果多申请了id也会导致自增主键 id 不连续。

普通insert语句,即使 innodb_autoinc_lock_mode 设置为 1,也不会等语句执行完成才释放锁。因为在申请自增 id 的时候,是可以精确计算出需要多少个 id 的,然后一次性申请,申请完成后锁就可以释放了。

40 | insert语句的锁为什么这么多?

insert … select 语句

可重复读隔离级别下,binlog_format=statement。
表结构

CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);

create table t2 like t

session B执行时需要对表 t 的所有行和间隙加锁。如果没有锁,就可能出现 session B 的 insert 语句先执行,但是后写入 binlog 的情况。所以会引起主备不一致。

insert 循环写入

执行 insert … select 的时候,对目标表也不是锁全表,而是只锁住需要访问的资源。现在有这么一个需求:要往表 t2 中插入一行数据,这一行的 c 值是表 t 中 c 值的最大值加 1。insert into t2(c,d) (select c+1, d from t force index(c) order by c desc limit 1);

这个语句的加锁范围,就是表 t 索引 c 上的 (3,4]和 (4,supremum] 这两个 next-key lock,以及主键索引上 id=4 这一行。它的执行流程也比较简单,从表 t 中按照索引 c 倒序,扫描第一行,拿到结果写入到表 t2 中。insert into t(c,d) (select c+1, d from t force index(c) order by c desc limit 1);

如果执行这句sql,可以看到,这时候的 Rows_examined 的值是 5。并且使用了临时表。

Explain 结果 rows=1 是因为受到了 limit 1 的影响。可能不准确。
使用执行Innodb_rows_read 语句查看查看sql执行前后扫描行数。

可以看到,这个语句执行前后,Innodb_rows_read 的值增加了 4。因为默认临时表是使用 Memory 引擎的,所以这 4 行查的都是表 t,也就是说对表 t 做了全表扫描。

  1. 创建临时表,表里有两个字段 c 和 d。
  2. 按照索引 c 扫描表 t,依次取 c=4、3、2、1,然后回表,读到 c 和 d 的值写入临时表。这时,Rows_examined=4。
  3. 由于语义里面有 limit 1,所以只取了临时表的第一行,再插入到表 t 中。这时,Rows_examined 的值加 1,变成了 5。

这个语句会导致在表 t 上做全表扫描,并且会给索引 c 上的所有间隙都加上共享的 next-key lock。
这个语句的执行为什么需要临时表,原因是这类一边遍历数据,一边更新数据的情况,如果读出来的数据直接写回原表,就可能在遍历过程中,读到刚刚插入的记录,新插入的记录如果参与计算逻辑,就跟语义不符。
由于实现上这个语句没有在子查询中就直接使用 limit 1,从而导致了这个语句的执行需要遍历整个表 t。

create temporary table temp_t(c int,d int) engine=memory;
insert into temp_t (select c+1, d from t force index(c) order by c desc limit 1);
insert into t select * from temp_t;
drop table temp_t;

insert 唯一键冲突

insert into … on duplicate key update

语义的逻辑是,插入一行数据,如果碰到唯一键约束,就执行后面的更新语句。insert into t values(11,10,10) on duplicate key update d=100;

41 | 怎么最快地复制一张表?

如果可以控制对源表的扫描行数和加锁范围很小的话,我们简单地使用 insert … select 语句即可实现。

create database db1;
use db1;

create table t(id int primary key, a int, b int, index(a))engine=innodb;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=1000)do
insert into t values(i,i,i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();

create database db2;
create table db2.t like db1.t

mysqldump 方法

mysqldump -h$host -P$port -u$user –add-locks=0 –no-create-info –single-transaction –set-gtid-purged=OFF db1 t –where=”a>900″ –result-file=/client_tmp/t.sql

导出 CSV 文件

//导出 select * from db1.t where a>900 into outfile ‘/server_tmp/t.csv’; //导入 load data infile ‘/server_tmp/t.csv’ into table db2.t;

物理拷贝方法

在 MySQL 5.6 版本引入了可传输表空间(transportable tablespace) 的方法,可以通过导出 + 导入表空间的方式,实现物理拷贝表的功能。
假设我们现在的目标是在 db1 库下,复制一个跟表 t 相同的表 r:

  1. 执行 create table r like t,创建一个相同表结构的空表;
  2. 执行 alter table r discard tablespace,这时候 r.ibd 文件会被删除;
  3. 执行 flush table t for export,这时候 db1 目录下会生成一个 t.cfg 文件;
  4. 在 db1 目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd;这两个命令;
  5. 执行 unlock tables,这时候 t.cfg 文件会被删除;
  6. 执行 alter table r import tablespace,将这个 r.ibd 文件作为表 r 的新的表空间,由于这个文件的数据内容和 t.ibd 是相同的,所以表 r 中就有了和表 t 相同的数据。

42 | grant之后要跟着flush privileges吗?

先创建一个用户:create user ‘ua’@’%‘ identified by ‘pa’;

这条命令做了两个动作:

  1. 磁盘上,往 mysql.user 表里插入一行,由于没有指定权限,所以这行数据上所有表示权限的字段的值都是 N;
  2. 内存里,往数组 acl_users 里插入一个 acl_user 对象,这个对象的 access 字段值为 0。

全局权限

// 增加权限 grant all privileges on *.* to ‘ua’@’%‘ with grant option; // 取消权限 revoke all privileges on *.* from ‘ua‘@’%;

将上述第1步权限字段的值 N 全改为 Y;把上述第2步内存数组 acl_users 全改为1。

db 权限

grant all privileges on db1.* to ‘ua’@’%’ with grant option;

grant 操作对于已经存在的连接的影响,在全局权限和基于 db 的权限效果是不同的。如果当前会话已经处于某一个 db 里面, use 这个库的时候拿到的库权限会保存在会话变量中,所以 revoke 会不生效。

表权限和列权限

表权限定义存放在表 mysql.tables_priv 中,列权限定义存放在表 mysql.columns_priv 中。这两类权限,组合起来存放在内存的 hash 结构 column_priv_hash 中。

create table db1.t1(id int, a int);

grant all privileges on db1.t1 to ‘ua’@’%’ with grant option;
GRANT SELECT(id), INSERT (id,a) ON mydb.mytbl TO ‘ua’@’%’ with grant option;

flush privileges 使用场景

正常情况下,grant 命令之后,没有必要跟着执行 flush privileges 命令,因为会同时刷新内存数据。
但当数据表中的权限数据跟内存中的权限数据不一致的时候,flush privileges 语句可以用来重建内存数据,达到一致状态。这种不一致往往是由不规范的操作导致的,比如直接用 DML 语句操作系统权限表。

44 | 答疑文章(三):说一说这些好问题

join 的写法

create table a(f1 int, f2 int, index(f1))engine=innodb;
create table b(f1 int, f2 int)engine=innodb;
insert into a values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
insert into b values(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);

  1. 如果用 left join 的话,左边的表一定是驱动表吗?
  2. 如果两个表的 join 包含多个条件的等值匹配,是都要写到 on 里面呢,还是只把一个条件写到 on 里面,其他条件写到 where 部分?

其实就是下面这两种写法的区别:

select * from a left join b on(a.f1=b.f1) and (a.f2=b.f2); /*Q1*/ select * from a left join b on(a.f1=b.f1) where (a.f2=b.f2);/*Q2*/

Q1的explain:

Q1使用BNL算法,第 35 篇文章《join 语句怎么优化?》中讲过。
Q2的explain:

Q1使用NLJ算法,执行流程是这样的:顺序扫描表 b,每一行用 b.f1 到表 a 中去查,匹配到记录后判断 a.f2=b.f2 是否满足,满足条件的话就作为结果集的一部分返回。
差别
在 MySQL 里,NULL 跟任何值执行等值判断和不等值判断的结果,都是 NULL。所以 Q2 没有1和2。

Q2这条语句虽然用的是 left join,但是语义跟 join 是一致的。优化器会把Q2优化成join。因为表 a 的 f1 上有索引,就把表 b 作为驱动表,这样就可以用上 NLJ 算法。使用show warning;可以看到优化后的语句。

所以使用 left join 时,左边的表不一定是驱动表
如果需要 left join 的语义,就不能把被驱动表的字段放在 where 条件里面做等值判断或不等值判断,必须都写在 on 里面。

select * from a join b on(a.f1=b.f1) and (a.f2=b.f2); /*Q3*/ select * from a join b on(a.f1=b.f1) where (a.f2=b.f2);/*Q4*/

在这种情况下,join 将判断条件是否全部放在 on 部分就没有区别了。

Simple Nested Loop Join 的性能问题

Simple Nested Loop Join 算法,其实也是把数据读到内存里,然后按照匹配条件进行判断,为什么性能差距会这么大呢?
解释这个问题,需要用到 MySQL 中索引结构和 Buffer Pool 的相关知识点:

  1. 在对被驱动表做全表扫描的时候,如果数据没有在 Buffer Pool 中,就需要等待这部分数据从磁盘读入;多次访问容易将这些数据页放到 Buffer Pool 的头部,影响 Buffer Pool 的命中率。
  2. 即使被驱动表数据都在内存中,每次查找“下一个记录的操作”,都是类似指针操作。而 join_buffer 中是数组,遍历的成本更低。

distinct 和 group by 的性能

select a from t group by a order by null;

select distinct a from t;

group by 没有聚合函数,这两句sql的效率相同。

  1. 创建一个临时表,临时表有一个字段 a,并且在这个字段 a 上创建一个唯一索引;
  2. 遍历表 t,依次取数据插入临时表中:如果发现唯一键冲突,就跳过;否则插入成功;
  3. 遍历完成后,将临时表作为结果集返回给客户端。

备库自增主键问题

第 39 篇文章《自增主键为什么不是连续的?》评论区,@帽子掉了 同学问到:在 binlog_format=statement 时,语句 A 先获取 id=1,然后语句 B 获取 id=2;接着语句 B 提交,写 binlog,然后语句 A 再写 binlog。这时候,如果 binlog 重放,是不是会发生语句 B 的 id 为 1,而语句 A 的 id 为 2 的不一致情况呢?
不会,虽然 statement 格式下“自增 id 的生成顺序,和 binlog 的写入顺序可能是不同的”。

45 | 自增id用完怎么办?

表定义自增值 id

表定义的自增值达到上限后的逻辑是:再申请下一个 id 时,得到的值保持不变。

create table t(id int unsigned auto_increment primary key) auto_increment=4294967295;
insert into t values(null);
// 成功插入一行 4294967295
show create table t;
/* CREATE TABLE `t` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295;
*/

insert into t values(null);
//Duplicate entry ‘4294967295’ for key ‘PRIMARY’

主键冲突,如果 4 个字节无符号整型 (unsigned int) 不够用的情况下,可以使用 8 个字节的 bigint unsigned。

InnoDB 系统自增 row_id

如果你创建的 InnoDB 表没有指定主键,那么 InnoDB 会给你创建一个不可见的,长度为 6 个字节的 row_id。InnoDB 维护了一个全局的 dict_sys.row_id 值,所有无主键的 InnoDB 表,每插入一行数据,都将当前的 dict_sys.row_id 值作为要插入数据的 row_id,然后把 dict_sys.row_id 的值加 1。
如果到达上限后,再有插入数据的行为要来申请 row_id,拿到以后再取最后 6 个字节的话就是 0,然后继续循环。所以会导致覆盖数据。

Xid

redo log 和 binlog 相配合的时候,它们有一个共同的字段叫作 Xid。它在 MySQL 中是用来对应事务的。
MySQL 内部维护了一个全局变量 global_query_id,每次执行语句的时候将它赋值给 Query_id,然后给这个变量加 1。如果当前语句是这个事务执行的第一条语句,那么 MySQL 还会同时把 Query_id 赋值给这个事务的 Xid。
而 global_query_id 是一个纯内存变量,重启之后就清零了。所以你就知道了,在同一个数据库实例中,不同事务的 Xid 也是有可能相同的。但是 MySQL 重启之后会重新生成新的 binlog 文件,这就保证了,同一个 binlog 文件里,Xid 一定是唯一的。
不过 global_query_id 达到上限后,会继续从 0 开始计数,由于 global_query_id 为8个字节,所以一般不会出现到达上限的情况。

Innodb trx_id

Xid 是由 server 层维护的。InnoDB 内部使用 Xid ,就是为了能够在 InnoDB 事务和 server 之间做关联。但是,InnoDB 自己的 trx_id,是另外维护的。
InnoDB 内部维护了一个 max_trx_id 全局变量,每次需要申请一个新的 trx_id 时,就获得 max_trx_id 的当前值,然后并将 max_trx_id 加 1。
InnoDB 数据可见性的核心思想是:每一行数据都记录了更新它的 trx_id,当一个事务读到一行数据的时候,判断这个数据是否可见的方法,就是通过事务的一致性视图与这行数据的 trx_id 做对比。
对于正在执行的事务,你可以从 information_schema.innodb_trx 表中看到事务的 trx_id。
但是对于只读事务,InnoDB 并不会分配 trx_id。

max_trx_id 会持久化存储,重启也不会重置为 0,那么从理论上讲,只要一个 MySQL 服务跑得足够久,就可能到达上限,然后从 0 开始的情况。然后就会导致脏读。但只存在理论上,如果一个 MySQL 实例的 TPS 是每秒 50 万,持续这个压力的话,在 17.8 年后,就会出现这个情况。

thread_id

show processlist 里面的第一列,就是 thread_id。
系统保存了一个全局变量 thread_id_counter,每新建一个连接,就将 thread_id_counter 赋值给这个新连接的线程变量。
thread_id_counter 定义的大小是 4 个字节,到达上限则从0开始。

 

 

排查Linux服务器丢包故障

June 24th, 2019 by JasonLe's Tech 143 views

最近遇到一个棘手的问题,公司的海外服务器有大量的Tcp ListenDrops,经过检查服务日志,没有发现任何异常日志,然后我进行了艰苦的排查。

使用tcpdump工具进行抓包,由于公司数据比较敏感,就自己搭了一个演示环境来展示。

➜ ~ sudo tcpdump tcp -i ens33 -vv -s 0 -t and “tcp[tcpflags] & (tcp-syn) != 0”
tcpdump: listening on ens33, link-type EN10MB (Ethernet), capture size 262144 bytes
IP (tos 0x0, ttl 64, id 24397, offset 0, flags [DF], proto TCP (6), length 60)
192.168.175.128.50990 > 61.135.169.125.https: Flags [S], cksum 0x575c (incorrect -> 0x79ee), seq 4195193728, win 29200, options [mss 1460,sackOK,TS val 315071380 ecr 0,nop,wscale 7], length 0
IP (tos 0x0, ttl 64, id 32393, offset 0, flags [DF], proto TCP (6), length 60)
192.168.175.128.50992 > 61.135.169.125.https: Flags [S], cksum 0x575c (incorrect -> 0xaa39), seq 2868687428, win 29200, options [mss 1460,sackOK,TS val 315071380 ecr 0,nop,wscale 7], length 0
IP (tos 0x0, ttl 64, id 46350, offset 0, flags [DF], proto TCP (6), length 60)
192.168.175.128.50994 > 61.135.169.125.https: Flags [S], cksum 0x575c (incorrect -> 0x77da), seq 366705091, win 29200, options [mss 1460,sackOK,TS val 315071380 ecr 0,nop,wscale 7], length 0
IP (tos 0x0, ttl 128, id 19582, offset 0, flags [none], proto TCP (6), length 44)
61.135.169.125.https > 192.168.175.128.50990: Flags [S.], cksum 0x555f (correct), seq 2100960485, ack 4195193729, win 64240, options [mss 1460], length 0
IP (tos 0x0, ttl 128, id 19586, offset 0, flags [none], proto TCP (6), length 44)
61.135.169.125.https > 192.168.175.128.50992: Flags [S.], cksum 0x8649 (correct), seq 2037522446, ack 2868687429, win 64240, options [mss 1460], length 0
IP (tos 0x0, ttl 128, id 19587, offset 0, flags [none], proto TCP (6), length 44)
61.135.169.125.https > 192.168.175.128.50994: Flags [S.], cksum 0x4ca9 (correct), seq 1789015583, ack 366705092, win 64240, options [mss 1460], length 0

通过这个日志,我们能看出我的主机向61.135.169.125发送 SYN 和 61.135.169.125向我回包的过程,能看出满足三次握手的过程,里面的Flags就是TCP标志位,S 是 SYN,. 就是ack。根据TCP包头:

0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
| Source Port | Destination Port |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
| Sequence Number |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
| Acknowledgment Number |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
| Data | |C|E|U|A|P|R|S|F| |
| Offset| Res. |W|C|R|C|S|S|Y|I| Window |
| | |R|E|G|K|H|T|N|N| |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
| Checksum | Urgent Pointer |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
| Options | Padding |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
| data |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

上面的那个Options就是我们重点关注的。

一般丢包会从以下几个方面考虑:

防火墙拦截

服务器端口无法连接,通常就是查看防火墙配置了,虽然这里已经确认同一个出口IP的客户端有的能够正常访问,但也不排除配置了DROP特定端口范围的可能性。

连接跟踪表溢出

除了防火墙本身配置DROP规则外,与防火墙有关的还有连接跟踪表nf_conntrack,Linux为每个经过内核网络栈的数据包,生成一个新的连接记录项,当服务器处理的连接过多时,连接跟踪表被打满,服务器会丢弃新建连接的数据包。dmesg |grep nf_conntrack

Ring Buffer溢出

通过ethtool或/proc/net/dev可以查看因Ring Buffer满而丢弃的包统计,在统计项中以fifo标识:

➜ ~ ethtool -S ens33 | grep tx_fifo_errors
tx_fifo_errors: 0

如果发现服务器上某个网卡的fifo数持续增大,可以去确认CPU中断是否分配均匀,也可以尝试增加Ring Buffer的大小,通过ethtool可以查看网卡设备Ring Buffer最大值,修改Ring Buffer当前设置: ethtool -G eth0 rx 4096 tx 4096

netdev_max_backlog溢出

netdev_max_backlog是内核从NIC收到包后,交由协议栈(如IP、TCP)处理之前的缓冲队列。每个CPU核都有一个backlog队列,与Ring Buffer同理,当接收包的速率大于内核协议栈处理的速率时,CPU的backlog队列不断增长,当达到设定的netdev_max_backlog值时,数据包将被丢弃。

半连接队列溢出

半连接队列指的是TCP传输中服务器收到SYN包但还未完成三次握手的连接队列,队列大小由内核参数tcp_max_syn_backlog定义。

PAWS

PAWS全名Protect Againest Wrapped Sequence numbers,目的是解决在高带宽下,TCP序列号在一次会话中可能被重复使用而带来的问题。

如上图所示,客户端发送的序列号为A的数据包A1因某些原因在网络中“迷路”,在一定时间没有到达服务端,客户端超时重传序列号为A的数据包A2,接下来假设带宽足够,传输用尽序列号空间,重新使用A,此时服务端等待的是序列号为A的数据包A3,而恰巧此时前面“迷路”的A1到达服务端,如果服务端仅靠序列号A就判断数据包合法,就会将错误的数据传递到用户态程序,造成程序异常。

PAWS要解决的就是上述问题,它依赖于timestamp机制,理论依据是:在一条正常的TCP流中,按序接收到的所有TCP数据包中的timestamp都应该是单调非递减的,这样就能判断那些timestamp小于当前TCP流已处理的最大timestamp值的报文是延迟到达的重复报文,可以予以丢弃。在上文的例子中,服务器已经处理数据包Z,而后到来的A1包的timestamp必然小于Z包的timestamp,因此服务端会丢弃迟到的A1包,等待正确的报文到来。

PAWS机制的实现关键是内核保存了Per-Connection的最近接收时间戳,如果加以改进,就可以用来优化服务器TIME_WAIT状态的快速回收。

TIME_WAIT状态是TCP四次挥手中主动关闭连接的一方需要进入的最后一个状态,并且通常需要在该状态保持2*MSL(报文最大生存时间),它存在的意义有两个:

1.可靠地实现TCP全双工连接的关闭:关闭连接的四次挥手过程中,最终的ACK由主动关闭连接的一方(称为A)发出,如果这个ACK丢失,对端(称为B)将重发FIN,如果A不维持连接的TIME_WAIT状态,而是直接进入CLOSED,则无法重传ACK,B端的连接因此不能及时可靠释放。

2.等待“迷路”的重复数据包在网络中因生存时间到期消失:通信双方A与B,A的数据包因“迷路”没有及时到达B,A会重发数据包,当A与B完成传输并断开连接后,如果A不维持TIME_WAIT状态2*MSL时间,便有可能与B再次建立相同源端口和目的端口的“新连接”,而前一次连接中“迷路”的报文有可能在这时到达,并被B接收处理,造成异常,维持2*MSL的目的就是等待前一次连接的数据包在网络中消失。

TIME_WAIT状态的连接需要占用服务器内存资源维持,Linux内核提供了一个参数来控制TIME_WAIT状态的快速回收:tcp_tw_recycle,它的理论依据是:

在PAWS的理论基础上,如果内核保存Per-Host的最近接收时间戳,接收数据包时进行时间戳比对,就能避免TIME_WAIT意图解决的第二个问题:前一个连接的数据包在新连接中被当做有效数据包处理的情况。这样就没有必要维持TIME_WAIT状态2*MSL的时间来等待数据包消失,仅需要等待足够的RTO(超时重传),解决ACK丢失需要重传的情况,来达到快速回收TIME_WAIT状态连接的目的。

如何确认

通过netstat可以得到因PAWS机制timestamp验证被丢弃的数据包统计:

$ netstat –s |grep –e “passive connections rejected because of time stamp” –e “packets rejects in established connections because of timestamp”
387158 passive connections rejected because of time stamp
825313 packets rejects in established connections because of timestamp

通过sysctl查看是否启用了tcp_tw_recycle及tcp_timestamp:

$ sysctl net.ipv4.tcp_tw_recycle
net.ipv4.tcp_tw_recycle = 1
$ sysctl net.ipv4.tcp_timestamps
net.ipv4.tcp_timestamps = 1

 

这次问题正是因为服务器同时开启了tcp_tw_recycle和timestamps,而客户端正是使用NAT来访问服务器,造成启动时间相对较短的客户端得不到服务器的正常响应。

如何解决

如果服务器作为服务端提供服务,且明确客户端会通过NAT网络访问,或服务器之前有7层转发设备会替换客户端源IP时,是不应该开启tcp_tw_recycle的,而timestamps除了支持tcp_tw_recycle外还被其他机制依赖,推荐继续开启:

Linux提供了丰富的内核参数供使用者调整,调整得当可以大幅提高服务器的处理能力,但如果调整不当,就会引进莫名其妙的各种问题,比如这次开启tcp_tw_recycle导致丢包,实际也是为了减少TIME_WAIT连接数量而进行参数调优的结果。我们在做系统优化时,时刻要保持辩证和空杯的心态,不盲目吸收他人的果,而多去追求因,只有知其所以然,才能结合实际业务特点,得出最合理的优化配置。

上面这些都是网卡层面的丢包,而我遇到的情况是tcp丢包,网卡并没有什么异常,最后通过tcpdump抓包,发现有大量的SYN,没有ack回包,究其原因就是tcp_tw_recycle导致的,其中针对这个time stamp就是放在TCP包头的Options 参数里,通过tcpdump也是可以看到这个参数的(TS val 315071380),默认我们的服务器经过LB打过来后,经过NAT访问服务器时会产生新的问题:同一个NAT背后的多个客户端时间戳是很难保持一致的(timestamp机制使用的是系统启动相对时间),对于服务器来说,两台客户端主机各自建立的TCP连接表现为同一个对端IP的两个连接,按照Per-Host记录的最近接收时间戳会更新为两台客户端主机中时间戳较大的那个,而时间戳相对较小的客户端发出的所有数据包对服务器来说都是这台主机已过期的重复数据,因此会直接丢弃,因此产生tcp listen drop。

 

参考

https://www.cnblogs.com/chenpingzhao/p/9108570.html

《MySQL实战45讲》学习笔记 16~31讲

June 22nd, 2019 by JasonLe's Tech 131 views

16 | “order by”是怎么工作的?

CREATE TABLE `t` (
`id` int(11) NOT NULL,
`city` varchar(16) NOT NULL,
`name` varchar(16) NOT NULL,
`age` int(11) NOT NULL,
`addr` varchar(128) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `city` (`city`)
) ENGINE=InnoDB;
 

假设按照下面的sql查询并排序。 select city,name,age from t where city=’杭州’ order by name limit 1000 ;

Extra中”Using filesort”表示排序,mysql会给每个线程分配一个块内存(sort_buffer)用来排序。
city索引示意图:

sql执行过程:

  1. 初始化sort_buffer,确定放入name、city、age 这三个字段;
  2. 从city索引找到第一个city=’杭州’的主键id,图中的ID_X;
  3. 根据id去聚集索引取这三个字段,放到sort_buffer;
  4. 在从city索引取下一个;
  5. 重复3、4查询所有的值;
  6. 在sort_buffer按name快速排序;
  7. 按照排序结果取前1000行返回给客户端。
  8. 如果sort_buffer太小,内存放不下排序的数据,则需要使用外部排序,利用磁盘临时文件辅助排序。这取决于排序所需内存和参数 sort_buffer_size。

下面方法可以确定排序是否使用临时文件:

<php>

SET optimizer_trace=’enabled=on’;
/* @a 保存 Innodb_rows_read 的初始值 */
select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = ‘Innodb_rows_read’;
/* 执行语句 */
select city, name,age from t where city=’杭州’ order by name limit 1000;
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
/* @b 保存 Innodb_rows_read 的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = ‘Innodb_rows_read’;
/* 计算 Innodb_rows_read 差值 */
select @b-@a;
</php>

通过查看 OPTIMIZER_TRACE,number_of_tmp_files表示排序使用的临时文件数,外部排序一般使用归并排序算法。
rows表示满足city=’杭州’有4000条,examined_rows=4000表示4000行参与排序。
sort_mode packed_additional_fields表示排序过程字符串做了“紧凑”处理。name字段定义varchar(16),排序过程中按照实际长度分配空间。
最后一个查询语句 select @b-@a返回结果是 4000,表示只扫描了4000行。

这边老师把internal_tmp_disk_storage_engine 设置成MyISAM,否则,select @b-@a结果为 4001。因为innodb把数据从临时表取出来时,会让Innodb_rows_read 的值加 1。

rowid 排序

如果排序的单行长度太大mysql会使用另一种算法。

SET max_length_for_sort_data = 16;

city、name、age 这三个字段的定义总长度是 36 > max_length_for_sort_data,所以会使用别的算法。
该算法和全字段排序的差别:

sort_buffer只会确定放入name 和 id字段,所以只会取这两个字段。
最后根据name排完序,会根据id字段去原表取city、name 和 age 三个字段返回给客户端。
需要注意,不做合并操作,而是直接将原表查到的字段返回给客户端。
和上述过程对比:

examined_rows和rows没有变化,但select @b-@a会变成5000。因为排完序需要去原表再取1000行。

全字段排序 VS rowid 排序
对于 InnoDB 表来说,rowid 排序会要求回表多造成磁盘读,因此不会被优先选择。
假设从city索引上取出来的行天然按照name递增排序,就不需要再进行排序了。
所以可以建一个city和name的联合索引:alter table t add index city_user(city, name);

整个查询流程就变成了:

  1. 从索引(city, name)找到第一个city=’杭州’的主键id;
  2. 到聚集索引取name、city、age三个字段,作为结果集一部分直接返回;
  3. 从索引(city, name)取下一个。
  4. 重复2、3,直到查到1000条记录,或不满足city=’杭州’时结束。

没有”Using filesort”。
使用覆盖索引:alter table t add index city_user_age(city, name, age);

但维护索引是有代价的,所以需要权衡。

小结

mysql> select * from t where city in (‘杭州’,” 苏州 “) order by name limit 100;

上述sql需要排序,因为name不是递增的。
可以将sql拆分成两条,最后通过程序内存取前100条。
进一步,如果需要分页,“limit 10000,100”,则可以使用下面的思想:

select * from t where city=” 杭州 ” order by name limit 10100;
select * from t where city=” 苏州 ” order by name limit 10100。

根据,name排序,然后取10001~10100,但这样返回的数据量较大,所以可以改成:

select id,name from t where city=” 杭州 ” order by name limit 10100;
select id,name from t where city=” 苏州 ” order by name limit 10100。

根据,name排序,然后取10001~10100,然后在通过id查询100条数据。

另外
评论区大神多,特别是@某、人,看到好多次了。下面是他的回答:
问题一 :这种无条件查列表页除了全表扫还有其他建立索引的办法么
1)无条件查询如果只有order by create_time,即便create_time上有索引,也不会使用到。
因为优化器认为走二级索引再去回表成本比全表扫描排序更高。
所以选择走全表扫描,然后根据老师讲的两种方式选择一种来排序
2)无条件查询但是是order by create_time limit m.如果m值较小,是可以走索引的.
因为优化器认为根据索引有序性去回表查数据,然后得到m条数据,就可以终止循环,那么成本比全表扫描小,则选择走二级索引。
即便没有二级索引,mysql针对order by limit也做了优化,采用堆排序。这部分老师明天会讲
问题二 : 如果加入 group by , 数据该如何走
如果是group by a,a上不能使用索引的情况,是走rowid排序。
如果是group by limit,不能使用索引的情况,是走堆排序
如果是只有group by a,a上有索引的情况,又根据选取值不同,索引的扫描方式又有不同
select * from t group by a –走的是索引全扫描,至于这里为什么选择走索引全扫描,还需要老师解惑下
select a from t group by a –走的是索引松散扫描,也就说只需要扫描每组的第一行数据即可,不用扫描每一行的值
问题三 :老师之后的文章会有讲解 bigInt(20) 、 tinyint(2) 、varchar(32) 这种后面带数字与不带数字有何区别的文章么 。 每次建字段都会考虑长度 ,但实际却不知道他有何作用
bigint和int加数字都不影响能存储的值。
bigint(1)和bigint(19)都能存储2^64-1范围内的值,int是 2^32-1。只是有些前端会根据括号里来截取显示而已。建议不加varchar()就必须带,因为varchar()括号里的数字代表能存多少字符。假设varchar(2),就只能存两个字符,不管是中文还是英文。目前来看varchar()这个值可以设得稍稍大点,因为内存是按照实际的大小来分配内存空间的,不是按照值来预分配的。

17 | 如何正确地显示随机消息?

mysql> CREATE TABLE `words` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`word` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

delimiter ;;
create procedure idata()
begin
declare i int;
set i=0;
while i<10000 do
insert into words(word) values(concat(char(97+(i div 1000)), char(97+(i % 1000 div 100)), char(97+(i % 100 div 10)), char(97+(i % 10))));
set i=i+1;
end while;
end;;
delimiter ;

call idata();

需求:每次随机获取三个word;

内存临时表

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

explain

这个 Extra 的意思就是,需要临时表,并且需要在临时表上排序。
上一篇文章的一个结论:对于 InnoDB 表来说,执行全字段排序会减少磁盘访问,因此会被优先选择。
**对于内存表,回表过程只是简单地根据数据行的位置,直接访问内存得到数据,根本不会导致多访问磁盘。**所以,MySQL 这时就会选择 rowid 排序。
上述sql的执行流程:

  1. 创建一个memory引擎的临时表,第一个字段double类型,假设字段为R,第二个字段varchar(64),记为字段W。并且这个表没有索引。
  2. 从 words 表中,按主键顺序取出所有的 word 值。对于每一个 word 值,调用 rand() 函数生成一个大于 0 小于 1 的随机小数,并把这个随机小数和 word分别存入临时表的 R 和 W 字段中,到此,扫描行数是 10000。
  3. 接着在没有索引的内存临时表上,按字段R排序。
  4. 初始化sort_buffer。sort_buffer和临时表一直两个字段。
  5. 临时表全表扫描去取R值和位置信息(稍后解释),放入sort_buffer两个字段,此时扫描行数增加10000,变成20000。
  6. 在sort_buffer对R值排序。
  7. 排序完成取前三行,总扫描行数变成20003行。

通过慢查询日志(slow log)可以看到

# Query_time: 0.900376 Lock_time: 0.000347 Rows_sent: 3 Rows_examined: 20003
SET timestamp=1541402277;
select word from words order by rand() limit 3;

磁盘临时表

tmp_table_size限制了内存临时表的大小,默认16M。如果内存大于tmp_table_size,则会转成磁盘临时表。
磁盘临时表使用的引擎默认是 InnoDB,由参数 internal_tmp_disk_storage_engine 控制。
复现:

set tmp_table_size=1024;
set sort_buffer_size=32768;
set max_length_for_sort_data=16;
/* 打开 optimizer_trace,只对本线程有效 */
SET optimizer_trace=’enabled=on’;
/* 执行语句 */
select word from words order by rand() limit 3;
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

部分PTIMIZER_TRACE 的结果如下:

由于max_length_for_sort_data 设置成 16,所以参与排序的是R字段和row_id字段组成的行。
R字段8个字节,rowid是6个字节,总行数10000,这样总共140000字节,超过sort_buffer_size,但没有使用临时文件。
是因为MySQL 5.6 版本引入的一个新的排序算法,即:优先队列排序算法。
因为sql只需要去R值最小的3个rowid,所以不需要将所有的数据排序,所以没有使用临时文件(归并排序算法)。

优先级队列算法执行流程如下:

  1. 先取前三行,构造成一个堆。
  2. 取下一行(R’,rowid’),跟当前堆最大的R比较,如果 R’小于 R,把这个 (R,rowid)从堆中去掉,换成 (R’,rowid’);
  3. 重复第 2 步,直到第 10000 个 (R’,rowid’) 完成比较。

上图OPTIMIZER_TRACE 结果中,filesort_priority_queue_optimization 这个部分的chosen=true,就表示使用了优先队列排序算法。select city,name,age from t where city=‘杭州’ order by name limit 1000;

这句sql没有使用优先队列排序算法,因为limit 1000堆大小超过了sort_buffer_size 大小。

随机排序方法

随机选取一个word值。

mysql> select max(id),min(id) into @M,@N from t ;

set @X= floor((@M@N+1)*rand() + @N);

select * from t where id >= @X limit 1;

取 max(id) 和 min(id) 都是不需要扫描索引,而第三步的 select 也可以用索引快速定位,可以认为就只扫描了3行。
但id中间可能有空洞,所以不同行概率不一样。
所以,为了得到严格随机的结果,你可以用下面这个流程:

mysql> select count(*) into @C from t;
set @Y = floor(@C * rand());
set @sql = concat(“select * from t limit “, @Y, “,1”);
prepare stmt from @sql;
execute stmt;
DEALLOCATE prepare stmt;

MySQL 处理 limit Y,1 的做法就是按顺序一个一个地读出来,丢掉前 Y 个,然后把下一个记录作为返回结果,此这一步需要扫描 Y+1 行。
再加上,第一步扫描的 C 行,总共需要扫描 C+Y+1 行,执行代价比第一个随机算法的代价要高。
另外一个思路:

mysql> select count(*) into @C from t;
set @Y1 = floor(@C * rand());
set @Y2 = floor(@C * rand());
set @Y3 = floor(@C * rand());
select * from t limit @Y1,1; // 在应用代码里面取 Y1、Y2、Y3 值,拼出 SQL 后执行
select * from t limit @Y2,1;
select * from t limit @Y3,1;

18 | 为什么这些SQL语句逻辑相同,性能却差异巨大

案例一:条件字段函数操作

不要对字段进行计算。select * from tradelog where id + 1 = 10000; select count(*) from tradelog where month(t_modified)=7;

案例二:隐式类型转换

数据类型与字段类型不同的,将导致全表扫描。//判断mysql怎么进行数据类型转换,下面的字符串会转成数字,返回1 select10> 9;

案例三:隐式字符编码转换

两张表编码格式不一致也会导致全表查询。

19 | 为什么我只查一行的语句,也执行这么慢?

本节表结构和数据。

mysql> CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into t values(i,i)
set i=i+1;
end while;
end;;
delimiter ;
call idata();

第一类:查询长时间不返回

等MDL锁

show processlist;

//或下面sql,可以找出pid(设置 performance_schema=on,相比于设置为 off 会有 10% 左右的性能损失)

select blocking_pid from sys.schema_table_lock_waits;

等flush

select * from information_schema.processlist where id= ‘pid’;

如果查到如下图所示,则表示有线程正要对表进行flush操作。

MySQL 里面对表做 flush 操作的用法,一般有以下两个:flush tables t with read lock; flush tables with read lock;

等行锁 select * from t sys.innodb_lock_waits where locked_table=table_name \G

KILL pid 断开连接,隐含逻辑自动回滚这个连接里面正在执行的线程,释放行锁。

第二类:查询慢

select * from t where c=50000 limit 1;

如果字段c上没有索引,这个语句只能走id主键顺序扫描,需要扫描5万行。
扫描一行却很慢的语句

session B执行100万次后,生成了100万个undo log,所以第一个select 快照读要将undo log执行100万次回到快照的版本。而第二个select当前读。

https://www.cnblogs.com/rjzheng/p/9950951.html

20 | 幻读是什么,幻读有什么问题?

幻读是什么?

当前读,新插入的行。

间隙锁和next-key lock

innodb为了解决幻读加入了间隙锁,锁住一个索引区间(开区间)。
锁住索引记录的区间,或第一条索引记录之前的范围,或者最后一条索引记录之后的范围。

间隙锁和行锁合成next-key lock,前开后闭区间。

如下,间隙锁的引入容易导致死锁。
因为select for update会加入间隙锁。

begin; select * from t where id=N for update; /* 如果行不存在 */

insert into t values(N,N,N); /* 如果行存在 */

update t set d=N set id=N;

commit;

如果业务可以容忍可重复读,可使用重复读+binlog 格式设置为 row,可重复读没有间隙锁。

21 | 为什么我只改一行的语句,锁这么多?

两个“原则”、两个“优化”和一个“bug”

  1. 原则 1:加锁的基本单位是next-key lock。
  2. 原则 2:查找过程中访问到的对象才会加锁。
  3. 优化1:索引等值查询,唯一索引,行锁。
  4. 优化2:索引等值查询,向右遍历且最后一个值不满足等值条件时,next-key lock 退化为间隙锁。
  5. 一个bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止。

limit加锁
limit删除数据时,只会扫描limit行数,不会继续扫描,所以加锁粒度更小。
在删除数据时,尽量加limit。

22 | MySQL有哪些“饮鸩止渴”提高性能的方法

短连接风暴

max_connections
wait_timeout 参数,一个线程空闲这么多秒后自动断开连接。

  • 断开占着连接不工作线程,先考虑事务外进程。服务端主动断开连接,客户端不一定能正确处理。
  • 减少连接过程的消耗,–skip-grant-tables 参数,不安全。

慢查询性能问题

  • 索引问题
    建索引
    主备架构,先增加备库索引。更新前执行set sql_log_bin=off。
  • 语句问题
  • qps突增问题

23 | MySQL是怎么保证数据不丢的?

binlog写入机制

上图说明事务提交时,执行器把 binlog cache 里的完整事务写入到 binlog 中,并清空binlog cache。
图中的write只是写文件系统的page cache。

write 和 fsync 的时机,是由参数 sync_binlog 控制的:

  1. sync_binlog=0 的时候,表示每次提交事务都只 write,不 fsync;
  2. sync_binlog=1 的时候,表示每次提交事务都会执行 fsync;
  3. sync_binlog=N(N>1) 的时候,表示每次提交事务都 write,但累积 N 个事务后才 fsync。

redo log写入机制

redo log buffer不需要每次都持久化硬盘,mysql异常重启,这部分日志就会丢失。
未提交的事务可能会被持久化到硬盘。

关于控制刷盘的innodb_flush_log_at_trx_commit参数,在02 | 日志系统:一条SQL更新语句是如何执行的中提到过。
Innodb还有一个后台线程,每隔一秒,就会把 redo log buffer 中的日志,调用 write 写到文件系统的件系统的 page cache,然后调用 fsync 持久化到磁盘。

除了后台线程每秒一次的轮询,还有两个场景会让一个没有提交的事务的redo log刷盘。

  1. 当redo log buffer占用空间即将达到innodb_log_buffer_size一半时,后台线程会主动刷盘。该动作只是写到page cache。
  2. 并行事务提交时,会顺带刷盘。A事务写了一些redo log buffer,另一个事务B提交,innodb_flush_log_at_trx_commit=1,所以事务B要把redo log buffer的日志全部刷盘。这时会把事务A在redo log buffer日志一起刷盘。

如果把innodb_flush_log_at_trx_commit设置成1,redo log在prepare需持久化一次,所以在15 | 答疑文章(一):日志和索引相关问题中,提到redo log 已经prepare,并且已经写完binlog就可以异常恢复。

每秒一次后台轮询刷盘,再加上崩溃恢复的逻辑,InnoDB 就认为 redo log 在 commit 的时候就不需要 fsync 了,只会 write 到文件系统的 page cache 中就够了。

组提交(group commit)机制
日志逻辑序列号(log sequence number,LSN)是单调递增的,用来对应 redo log 的写入点。每次写入长度为 length 的 redo log,LSN 的值就会加上 length。
LSN 也会写到 InnoDB 的数据页中,来确保数据页不会被多次执行重复的 redo log。

上图三个并发事务(trx1,trx2,trx3)在prepare阶段写完redo log buffer,持久化到磁盘的过程,对应的 LSN 分别是 50、120 和 160。当trx1开始刷盘,trx1会被选为leader,这个组有三个事务,这时LSN变成160,trx1写盘时会把LSN小于160的redo log都持久化,这时trx2和trx3可以直接返回。

实际上write是两步,如下图。

所以binlog也可以组提交,不过通常情况第三步执行较快,binlog组提交效果较差。所以可以优化以下两个参数。

binlog_group_commit_sync_delay参数,表示延迟多少微秒后才调用 fsync;
binlog_group_commit_sync_no_delay_count 参数,表示累积多少次以后才调用 fsync。

不建议将innodb_flush_log_at_trx_commit设置成0,设置成2只是多一个写page cache,效率相差不大,但2在mysql异常重启不会丢数据,只在主机掉电才会丢数据。

如果 binlog 写完盘以后发生 crash,这时候还没给客户端答复就重启了。等客户端再重连进来,发现事务已经提交成功了,这不是 bug。
数据库的 crash-safe 保证的是:

如果客户端收到事务成功的消息,事务就一定持久化了;
如果客户端收到事务失败(比如主键冲突、回滚等)的消息,事务就一定失败了;
如果客户端收到“执行异常”的消息,应用需要重连后通过查询当前状态来继续后续的逻辑。此时数据库只需要保证内部(数据和日志之间,主库和备库之间)一致就可以了。
关于1应该是不一定的。

两个留言:
sync_binlog = N:每个事务write后就响应客户端了。刷盘是N次事务后刷盘。N次事务之间宕机,数据丢失。
binlog_group_commit_sync_no_delay_count=N: 必须等到N个后才能提交。换言之,会增加响应客户端的时间。但是一旦响应了,那么数据就一定持久化了。宕机的话,数据是不会丢失的。

innodb的 redo log 在commit的时候不进行fsync,只会write 到page cache中。当sync_binlog>1,如果redo log 完成了prepare持久化落盘,binlog只是write page cache,此时commit标识完成write 但没有落盘,而client收到commit成功,这个时候主机掉电,启动的时候做崩溃恢复,没有commit标识和binglog,事务会回滚。sync_binlog设置为大于1的值,会丢binlog日志,此时数据也会丢失。

24 | MySQL是怎么保证主备一致的?

MySQL 主备的基本原理

binlog 的三种格式对比
表结构:

mysql> CREATE TABLE `t` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `t_modified`(`t_modified`)
) ENGINE=InnoDB;

insert into t values(1,1,’2018-11-13′);
insert into t values(2,2,’2018-11-12′);
insert into t values(3,3,’2018-11-11′);
insert into t values(4,4,’2018-11-10′);
insert into t values(5,5,’2018-11-09′);

mysql> delete from t /*comment*/ where a>=4 and t_modified<=’2018-11-10′ limit 1;
当 binlog_format=statement 时,binlog 里面记录的就是 SQL 语句的原文。mysql> show binlog events in ‘master.000001’;

delete 命令的执行效果图:

statement 格式,并且语句中有 limit,这个命令可能是 unsafe 的,可能会出现主备数据不一致。
比如上面那个delete:

如果delete使用索引A,那么会根据索引 a 找到第一个满足条件的行,也就是说删除的是a=4 这一行;
但如果使用的是索引 t_modified,那么删除的就是 t_modified=’2018-11-09’也就是 a=5这一行。
因为主备使用索引不一致会导致删除不同数据。

binlog_format=‘row’

  1. Table_map event,用于说明接下来要操作的表是test 库的表 t;
  2. Delete_rows event,用于定义删除的行为。

上图显示事务从8900开始,借助mysqlbinlog 工具,可以看到详细的log。mysqlbinlog vv data/master.000001 –start-position=8900;

  • 事务在server id 1这个库执行。
  • 每个event都有crc32的值,binlog_checksum控制。
  • Table_map event表示打开的表,map到数字226,如果有多张表,每个表都有一个对应的 Table_map event、都会 map 到一个单独的数字。
  • mysqlbinlog -vv 参数是为了把内容都解析出来,所以从结果里面可以看到各个字段的值(比如,@1=4、 @2=4 这些值)。
  • binlog_row_image 的默认配置是 FULL,因此 Delete_event 里面,包含了删掉的行的所有字段的值。如果设置为 MINIMAL,只会记录 id=4 这个信息。

为什么会有 mixed 格式的 binlog?
因为row格式如果删除10万行,就要记录10万条记录到binlog,会占据大量的存储空间和IO资源。
mixed 格式会判断sql是否会引起主备不一致,有可能,就用 row 格式,否则就用 statement 格式。

主流还是使用row 格式,因为该格式可以恢复数据。
用 binlog 来恢复数据的标准做法是,用 mysqlbinlog 工具解析出来,然后把解析结果发给整个MySQL 执行。类似下面的命令:mysqlbinlog master.000001 –start-position=2738 –stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;

循环复制问题

实际生产上使用比较多的是双 M 结构。

业务逻辑在节点 A 上更新了一条语句,然后再把生成的 binlog 发给节点 B,节点 B 执行完这条更新语句后也会生成 binlog。(参数 log_slave_updates 设置为 on,表示备库执行 relay log 后生成 binlog)。
下面逻辑可以解决两个节点间的循环复制的问题:

  • 规定两个库的 server id 必须不同;
  • 一个备库接到 binlog 并在重放的过程中,生成与原 binlog 的 server id 相同的新的 binlog;
  • 每个库收到主库发来的日志,判断server id是否和自己相同,相同直接丢弃日志。

25 | MySQL是怎么保证高可用的?

主备延迟

  1. 主库A完成事务写入binlog,这个时刻记为T1;
  2. 之后传给备库B,备库接受完binlog的时刻记为T2;
  3. 备库B执行完这个事务记为T3。

所谓主备延迟,就是同一个事务T3-T1。
在备库执行show slave status 命令,seconds_behind_master显示了当前备库延迟,精度秒。

延迟来源:

  • 为了省钱,备库机器较差。
  • 备库常用来读,查询压力大。一般可以这样处理:一主多从,或者通过binlog输出到外部系统,比如Hadoop。
  • 大事务,因为主库上必须等事务执行完成才会写入binlog。PS. 关于第三点,我在实习的时候就吃过这个亏,说多了都是泪。还好不是重要的库。
  • 大表DDL。
  • 主备延迟的一个大方向原因,备库的并行复制能力。

可靠性优先策略

优先考虑。
在上图双M结果下,从状态1到状态2切换的详细过程:

  • 判断备库B现在的seconds_behind_master,如果小于某个值(比如 5 秒)继续下一步,否则持续重试这一步;
  • 把主库A改成只读状态,即把readonly 设置成true;
  • 判断备库 B 的 seconds_behind_master的值,直到这个值变成 0 为止;
  • 把备库 B 改成可读写状态,也就是把 readonly 设置为 false;
  • 把业务请求切到备库 B。

上述切换流程,一般由专门的HA系统完成,但会存在一段时间都不可用时间。

可用性优先策略

如果强行把步骤4、5调整到最开始执行,这样就几乎不存在不可用时间,但会引起数据不一致。
数据不一致的例子,表结构如下(自增id):

mysql> CREATE TABLE `t` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`c` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

insert into t(c) values(1),(2),(3);

执行两条插入语句:

insert into t(c) values(4);
insert into t(c) values(5);

假设,现在主库其他数据表有大量更新,主备延迟达到5秒。在插入一条c=4语句,发起主备切换。
下图,binlog_format=mixed时

步骤2,主库A执行完insert,插入一行(4,4),之后开始进行主备切换。
步骤3,由于5秒延迟,备库还没来得及应用“插入c=4”这个中转日志,就开始接受客户端“插入c=5”的命令。
步骤4,备库B插入一行(4,5),并把这个binlog发给主库A。
步骤5,备库B执行“插入c=4”,插入一行(5,4)。binlog传给A,插入(5,5).
binlog_format=row时

因为该格式会记录插入行的所有字段值,所以只会有一行不一致。两边的主备同步的应用线程会报错 duplicate key error 并停止。

可靠性异常切换

假设,主库A和备库B主备延迟30分钟,这时A掉电,HA系统要切换B作为主库。这时必须等到备库B seconds_behind_master=0 之后,才能切换。

26 | 备库为什么会延迟好几个小时?

备库通过sql_thread更新数据,5.6版本之前只支持单线程复制,所以主库并发高、TPS高会出现严重的主备延迟。

上图为改进的多线程复制模型,coordinator为原来的sql_thread,但不再直接更新数据,只负责中转日志和分发事务。worker数量由参数 slave_parallel_workers 决定(32核推荐配置8~16)。
coordinator分发需满足两个基本要求:

  1. 不能造成更新覆盖。这就要求更新同一行的两个事务,必须被分发到同一个 worker 中。
  2. 同一个同一个事务不能被拆开,必须放到同一个worker 中。

并行复制策略

按库分发,hash库名到一个worker 中,MySQL 5.6 版本的并行复制策略。
按表分发,需将相同表hash到一个worker 中。
按行分发,按“库名 + 表名 + 唯一索引 a 的名字 +a 的值”hash到一个worker 中。
MariaDB 利用了redo log 组提交 (group commit)特性,因为能在一组中提交,一定不会修改同一行。

MySQL 5.7 并行复制策略由参数 slave-parallel-type 来控制,配置成DATABASE使用5.6版本的策略,LOGICAL_CLOCK使用MariaDB 的策略,但进行了优化(针对两阶段提交)。

MySQL 5.7.22 新增了一个并行复制策略,基于 WRITESET 的并行复制。

27 | 主库出问题了,从库怎么办?

虚线箭头表示的是主备关系,也就是 A 和 A’互为主备, 从库 B、C、D 指向的是主库 A。
相比于一主一备,一主多从结构在切换完成后,A’会成为新的主库,从库 B、C、D 也要改接到 A’。

基于位点的主备切换

当我们把节点 B 设置成节点 A’的从库的时候,需要执行一条change master 命令:

CHANGE MASTER TO
//主库A’的信息
MASTER_HOST=$host_name
MASTER_PORT=$port
MASTER_USER=$user_name
MASTER_PASSWORD=$password
//同步位点
MASTER_LOG_FILE=$master_log_name
MASTER_LOG_POS=$master_log_pos

最后两个参数表示要从主库的 master_log_name 文件的 master_log_name 文件的 master_log_pos 这个位置的日志继续同步。而这个位置就是我们所说的同步位点,也就是主库对应的文件名和日志偏移量。
同步位点很难取到精确位置,因为不能丢数据,需要需要找一个“稍微靠前”的位点,然后判断跳过已经执行过的事务。

等待新主库 A’把中转日志(relay log)全部同步完成;
在 A’上执行 show master status 命令,得到当前 A’上最新的 File 和 Position;
取原主库 A 故障的时刻 T;
用 mysqlbinlog 工具解析 A’的 File,得到 T 时刻的位点。

mysqlbinlog File –stop-datetime=T –start-datetime=T

主动跳过事务:
set global sql_slave_skip_counter=1;
start slave;
跳过设置 slave_skip_errors 参数,直接设置跳过指定错误。“1032”删除找不到行,“1062”主键冲突。同步完成后,稳定一段时间,去掉该设置。

GTID

MySQL 5.6 版本引入了 GTID。
GTID启动加上参数 gtid_mode=on 和 enforce_gtid_consistency=on。
GTID 的全称是 Global Transaction Identifier,也就是全局事务 ID,是一个事务在提交的时候生成的,是这个事务的唯一标识。它由两部分组成,格式是://server_uuid实例第一次启动时生成,全局唯一 //gno一个整数,初始值是 1,每次提交事务的时候分配给这个事务,并+1 //mysql文档中叫GTID=source_id:transaction_id GTID=server_uuid:gno

如果从库中已经存在了某事务,使用以下方式跳过。前三句执行了一个空事务,并把GTID加到了从库的集合中。

set gtid_next=’aaaaaaaa-cccc-dddd-eeee-ffffffffffff:10′;
begin;
commit;
set gtid_next=automatic;
start slave;

基于 GTID 的主备切换

CHANGE MASTER TO
MASTER_HOST=$host_name
MASTER_PORT=$port
MASTER_USER=$user_name
MASTER_PASSWORD=$password
//使用GTID协议
master_auto_position=1

从库会把自己的GTID集合传给切换的主库,主库会计算差集,然后把不同的同步给从库。

28 | 读写分离有哪些坑?

上节的主从是由客户端直联的。另一种架构proxy。

代理架构,客户端不会感知数据库端的细节,只需对接代理。但加一层代理,链路会变长,而且代理也需要高可用架构。
不管是哪种架构,主备都存在延迟。

强制走主库方案

对于一些需要拿到实时结果的请求,分发到主库上。但对一些都需要实时结果的金融业务,就需要放弃读写分离。

sleep方案

延迟几秒再去读从库,但超过这个时间的同步还是拿不到最新的数据。

判断主备无延迟方案

  1. 判断show slave status 结果里的 seconds_behind_master 参数的值是否等于0,但该值精度为秒。
  2. 对比位点确保主备无延迟,Master_Log_File 和 Relay_Master_Log_File、Read_Master_Log_Pos 和 Exec_Master_Log_Pos 这两组值完全相同,就表示接收到的日志已经同步完成。
  3. 对比 GTID 集合确保主备无延迟,Retrieved_Gtid_Set、Executed_Gtid_Set是否相同。

配合 semi-sync

要解决这个问题,就要引入半同步复制,也就是semi-sync replication。

事务提交的时候,主库把 binlog 发给从库;
从库收到 binlog 以后,发回给主库一个 ack,表示收收到;
主库收到这个 ack 以后,才能给客户端返回“事务完成”的确认。
但一主多从的情况主库只要收到一个从库返回ack,就会提交事务。所以在查询其他从库时,可能还是会存在主备延迟。
其实,判断同步位点的方案还有另外一个潜在的问题,即:如果在业务更新的高峰期,主库的位点或者 GTID 集合更新很快,那么上面的两个位点等值判断就会一直不成立,很可能出现从库上迟迟无法响应查询请求的情况。

等主库位点方案

实际上并不需要等待主备完全同步,其实从库查询trx1时只需要该事务完成就可以返回:

首先看一条sql : select master_pos_wait(file, pos[, timeout]);

  1. 它是在从库执行的;
  2. 参数 file 和 pos 指的是主库上的文件名和位置;
  3. timeout 可选,设置为正整数 N 表示这个函数最多等待N 秒。

这个会返回一个正整数 M,表示从命令开始执行,到应用完 file 和 pos 表示的 binlog 位置,执行了多少事务。除了正常返回之外,还会返回:

  1. 如果执行期间,备库同步线程发生异常,则返回 NULL;
  2. 如果等待超过 N 秒,就返回 -1;
    1. 如果刚开始执行的时候,就发现已经执行过这个位置了,则返回 0。

所以可以这么判断:

  1. trx1 事务更新完成后,马上执行 show master status 得到当前主库执行到的File 和 Position;
  2. 选定一个从库执行查询语句;
  3. 在从库上执行 select master_pos_wait(File, Position, 1);
  4. 如果返回值是 >=0 的正整数,则在这个从库执行查询语句;
  5. 否则,到主库执行查询语句。

所以可能存在将流量打到主库的情况,所以需要做好主库限流策略。

GTID 方案

select wait_for_executed_gtid_set(gtid_set, 1);

  1. 等待,直到这个库执行的事务中包含传入的 gtid_set,返回 0;
  2. 超时返回 1。

MySQL 5.7.6 版本开始,允许在执行完更新类事务后,把这个事务的 GTID 返回给客户端,这样等 GTID 的方案就可以减少一次查询。

  1. trx1 事务更新完成后,从返回包直接获取这个事务的 GTID,记为 gtid1;
  2. 选定一个从库执行查询语句;
  3. 在从库上执行 select wait_for_executed_gtid_set(gtid1, 1);
  4. 如果返回值是 0,则在这个从库执行查询语句;
  5. 否则,到主库执行查询语句。

29 | 如何判断一个数据库是不是出问题了?

select 1 判断
当前并发查询数超过innodb_thread_concurrency时, select 1会返回,但执行查询命令时会等待。
该参数默认值是0,表示不限制并发查询数,建议把 innodb_thread_concurrency 设置为 64~128 之间的值。不是并发连接数。

查表判断

在系统库(mysql 库)里创建一个表,比如命名为 health_check,里面只放一行数据,然后定期执行:mysql> select * from mysql.health_check;
但有其他一个问题,更新事务要写 binlog,binlog 所在磁盘的空间占用率达到 100%,那么所有的更新语句和事务提交的 commit 语句就都会被堵住。但是,系统这时候还是可以正常读数据的。

更新判断

常见做法是放一个 timestamp 字段,用来表示最后一次执行检测的时间。但备库不能写同一行,所以需要使用多行,id为server_id。mysql> update mysql.health_check set t_modified=now();

但有可能,机器的I/O已经100%,但刚好健康检查的sql拿到了资源,成功返回了。

内部统计

关于磁盘利用率100%的问题。MySQL 5.6 版本以后提供的 performance_schema 库,就在 file_summary_by_event_name 表里统计了每次 IO 请求的时间。

老师比较倾向的方案,是优先考虑 update 系统表,,然后再配合增加检测 performance_schema的信息。

30 | 答疑文章(二):用动态的观点看加锁

先复习一下老师在 21 | 为什么我只改一行的语句,锁这么多? 文章中提到了两个“原则”、两个“优化”和一个“bug”。
该文章基于下面的表结构:

CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

不等号条件里的等值查询
等值查询和“遍历”有什么区别?为什么我们文章的例子里面,where 条件是不等号,这个过程里也有等值查询?

begin;
select * from t where id>9 and id<12 order by id desc for update;
上面的规则可以知道加锁区间(0,5]、(5,10] 和 (10, 15)。

  1. 首先这个查询语句的语义是 order by id desc,要拿到满足条件的所有行,优化器必须先找到“第一个 id<12 的值”。
  2. 这个过程是通过索引树的搜索过程得到的,在引擎内部,其实是要找id=12 的这个值,只是最终没找到,但找到了(10,15) 这个间隙。
  3. 然后向左遍历,在遍历过程中,就不是等值查询了,会扫描到 id=5 这一行,所以会加一个 next-key lock (0,5]。

也就是说,在执行过程中,通过树搜索的方式定位记录的时候,用的是“等值查询”的方法。

等值查询的过程

下面这个语句的加锁范围是什么?begin; select id from t where c in(5,20,10) lock in share mode;

in 语句使用了索引 c 并且 rows=3,说明这三个值都是通过 B+ 树搜索定位的。
在查找 c=5 的时候,先锁住了 (0,5]。但是因为 c 不是唯一索引,为了确认还有没有别的记录 c=5,就要向右遍历,找到 c=10 才确认没有了,这个过程满足优化 2,所以加了间隙锁 (5,10)。
同样的,执行 c=10 这个逻辑的时候,加锁的范围是(5,10] 和 (10,15);执行 c=20 这个逻辑的时候,加锁的范围是 (15,20] 和 (20,25)。
这条语句在索引 c 上加的三个记录锁的顺序是:先加 c=5 的记录锁,再加 c=10 的记录锁,最后加 c=20 的记录锁。

怎么看死锁?

select id from t where c in(5,20,10) order by c desc for update;

当执行上述命令时,加锁顺序和会之前那句相反,会产生死锁。show engine innodb status;

三部分:

TRANSACTION,是第一个事务的信息;
TRANSACTION,是第二个事务的信息;
WE ROLL BACK TRANSACTION (1),最终回滚了第一个事务。

得到的结论:

由于锁是一个个加的,要避免死锁,对同一组资源,要按照尽量相同的顺序访问;
在发生死锁的时刻,for update 这条语句占有的资源更多,回滚成本更大,所以 InnoDB 选择了回滚成本更小的 lockin share mode 语句,来回滚。

怎么看锁等待?

session A 并没有锁住 c=10 这个记录,delete之后不能insert。

由于 delete 操作把 id=10 这一行删掉了,原来的两个间隙 (5,10)、(10,15)变成了一个 (5,15)。

update 的例子

虽然session A 的加锁范围是索引 c 上的 (5,10]、(10,15]、(15,20]、(20,25] 和 (25,supremum],但update后加锁范围变成了下图:

注意:根据 c>5 查到的第一个记录是 c=10,因此不会加(0,5] 这个 next-key lock。

之后 session B 的第一个 update 语句,要把c=5 改成 c=1,你可以理解为两步:插入 (c=1, id=5) 这个记录;删除 (c=5, id=5) 这个记录。

按照我们上一节说的,索引 c 上 (5,10) 间隙是由这个间隙右边的记录,也就是 c=10 定义的。所以通过这个操作,session A 的加锁范围变成了: