移动应用网站开发,番禺区画册设计公司,自媒体专用网站免费,网站页面模板 建设中文章目录 1. 用动态的观点看加锁1.1 不等号条件里的等值查询1.2 等值查询的过程1.3 怎么看死锁#xff1f;1.4 怎么看锁等待#xff1f;1.5 update 的例子 2. 误删数据后怎么办?2.1 删除行2.2 误删库/表2.3 延迟复制备库2.4 预防误删库 / 表的方法2.4.1 账号分离2.4.2 制定操… 文章目录 1. 用动态的观点看加锁1.1 不等号条件里的等值查询1.2 等值查询的过程1.3 怎么看死锁1.4 怎么看锁等待1.5 update 的例子 2. 误删数据后怎么办?2.1 删除行2.2 误删库/表2.3 延迟复制备库2.4 预防误删库 / 表的方法2.4.1 账号分离2.4.2 制定操作规范 2.5 rm 删除数据 3. 为何有kill不掉的语句3.1 收到 kill 以后线程做什么3.2 关于客户端的误解 1. 用动态的观点看加锁
加锁规则。这个规则中包含了两个“原则”、两个“优化”和一个“bug”
原则 1加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。原则 2查找过程中访问到的对象才会加锁。优化 1索引上的等值查询给唯一索引加锁的时候next-key lock 退化为行锁。优化 2索引上的等值查询向右遍历时且最后一个值不满足等值条件的时候next-key lock 退化为间隙锁。一个 bug唯一索引上的范围查询会访问到不满足条件的第一个值为止。
基于下面这个表 t
CREATE TABLE t (id int(11) NOT NULL,c int(11) DEFAULT NULL,d int(11) DEFAULT NULL,PRIMARY KEY (id),KEY c (c)
) ENGINEInnoDB;insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);1.1 不等号条件里的等值查询
看下这个例子分析一下这条查询语句的加锁范围
begin;
select * from t where id9 and id12 order by id desc for update;利用上面的加锁规则这个语句的加锁范围是主键索引上的 (0,5]、(5,10]和 (10, 15)。也就是说id15 这一行并没有被加上行锁。
加锁单位是 next-key lock都是前开后闭区间但是这里用到了优化 2即索引上的等值查询向右遍历的时候 id15 不满足条件所以 next-key lock 退化为了间隙锁 (10, 15)。
查询语句中 where 条件是大于号和小于号这里的“等值查询”又是从哪里来的呢这里先拆解一下加锁过程
图 1 索引 id 示意图
首先这个查询语句的语义是 order by id desc要拿到满足条件的所有行优化器必须先找到“第一个 id12 的值”。这个过程是通过索引树的搜索过程得到的在引擎内部其实是要找到 id12 的这个值只是最终没找到但找到了 (10,15) 这个间隙。然后向左遍历在遍历过程中就不是等值查询了会扫描到 id5 这一行所以会加一个 next-key lock (0,5]。
也就是说在执行过程中通过树搜索的方式定位记录的时候用的是“等值查询”的方法。
1.2 等值查询的过程
这个语句的加锁范围是什么
begin;
select id from t where c in(5,20,10) lock in share mode;先来看这条语句的 explain 结果
图 2 in 语句的 explain 结果 可以看出这条 in 语句使用了索引 c 并且 rows3说明这三个值都是通过 B 树搜索定位的。
在查找 c5 的时候先锁住了 (0,5]。但是因为 c 不是唯一索引为了确认还有没有别的记录 c5就要向右遍历找到 c10 才确认没有了这个过程满足优化 2所以加了间隙锁 (5,10)。 同样的执行 c10 这个逻辑的时候加锁的范围是 (5,10] 和 (10,15) 执行 c20 这个逻辑的时候加锁的范围是 (15,20] 和 (20,25)。
这条语句在索引 c 上加的三个记录锁的顺序是先加 c5 的记录锁再加 c10 的记录锁最后加 c20 的记录锁。
这些锁是“在执行过程中一个一个加的”而不是一次性加上去的。
有另外一个语句是这么写的
select id from t where c in(5,20,10) order by c desc for update;间隙锁是不互锁的但是这两条语句都会在索引 c 上的 c5、10、20 这三行记录上加记录锁。
这里需要注意一下由于语句里面是 order by c desc 这三个记录锁的加锁顺序是先锁 c20然后 c10最后是 c5。
也就是说这两条语句要加锁相同的资源但是加锁顺序相反。当这两条语句并发执行的时候就可能出现死锁。
1.3 怎么看死锁
图 3 是在出现死锁后执行 show engine innodb status 命令得到的部分输出。
这个命令会输出很多信息有一节 LATESTDETECTED DEADLOCK就是记录的最后一次死锁信息。
图 3 死锁现场
这图中的几个关键信息
这个结果分成三部分
(1) TRANSACTION是第一个事务的信息(2) TRANSACTION是第二个事务的信息WE ROLL BACK TRANSACTION (1)是最终的处理结果表示回滚了第一个事务。
第一个事务的信息中
WAITING FOR THIS LOCK TO BE GRANTED表示的是这个事务在等待的锁信息index c of table test.t说明在等的是表 t 的索引 c 上面的锁lock mode S waiting 表示这个语句要自己加一个读锁当前的状态是等待中Record lock 说明这是一个记录锁n_fields 2 表示这个记录是两列也就是字段 c 和主键字段 id0: len 4; hex 0000000a; asc ;; 是第一个字段也就是 c。值是十六进制 a也就是 101: len 4; hex 0000000a; asc ;; 是第二个字段也就是主键 id值也是 10这两行里面的 asc 表示的是接下来要打印出值里面的“可打印字符”但 10 不是可打印字符因此就显示空格。第一个事务信息就只显示出了等锁的状态在等待 (c10,id10) 这一行的锁。当然既然出现死锁了就表示这个事务也占有别的锁但是没有显示出来。别着急从第二个事务的信息中推导出来。
第二个事务显示的信息
“ HOLDS THE LOCK(S)”用来显示这个事务持有哪些锁index c of table test.t 表示锁是在表 t 的索引 c 上hex 0000000a 和 hex 00000014 表示这个事务持有 c10 和 c20 这两个记录锁WAITING FOR THIS LOCK TO BE GRANTED表示在等 (c5,id5) 这个记录锁。
从上面这些信息中我们就知道
“lock in share mode”的这条语句持有 c5 的记录锁在等 c10 的锁“for update”这个语句持有 c20 和 c10 的记录锁在等 c5 的记录锁。
因此导致了死锁。这里可以得到两个结论
由于锁是一个个加的要避免死锁对同一组资源要按照尽量相同的顺序访问在发生死锁的时刻for update 这条语句占有的资源更多回滚成本更大所以 InnoDB 选择了回滚成本更小的 lock in share mode 语句来回滚。 1.4 怎么看锁等待
图 4 delete 导致间隙变化 可以看到由于 session A 并没有锁住 c10 这个记录所以 session B 删除 id10 这一行是可以的。但是之后session B 再想 insert id10 这一行回去就不行了。
看一下此时 show engine innodb status 的结果
图 5 锁等待信息 几个关键信息
index PRIMARY of table test.t 表示这个语句被锁住是因为表 t 主键上的某个锁。lock_mode X locks gap before rec insert intention waiting 这里有几个信息
insert intention 表示当前线程准备插入一个记录这是一个插入意向锁。为了便于理解你可以认为它就是这个插入动作本身。gap before rec 表示这是一个间隙锁而不是记录锁。
那么这个 gap 是在哪个记录之前的呢接下来的 0~4 这 5 行的内容就是这个记录的信息。n_fields 5 也表示了这一个记录有 5 列
0: len 4; hex 0000000f; asc ;; 第一列是主键 id 字段十六进制 f 就是 id15。所以这时我们就知道了这个间隙就是 id15 之前的因为 id10 已经不存在了它表示的就是 (5,15)。1: len 6; hex 000000000513; asc ;; 第二列是长度为 6 字节的事务 id表示最后修改这一行的是 trx id 为 1299 的事务。2: len 7; hex b0000001250134; asc % 4;; 第三列长度为 7 字节的回滚段信息。可以看到这里的 acs 后面有显示内容 (% 和 4)这是因为刚好这个字节是可打印字符。后面两列是 c 和 d 的值都是 15。
因此就知道了由于 delete 操作把 id10 这一行删掉了原来的两个间隙 (5,10)、(10,15变成了一个 (5,15)。
session A 执行完 select 语句后什么都没做但它加锁的范围突然“变大”了 所谓“间隙”其实根本就是由“这个间隙右边的那个记录”定义的。
1.5 update 的例子
图 6 update 的例子 session A 的加锁范围是索引 c 上的 (5,10]、(10,15]、(15,20]、(20,25]和 (25,supremum]。 注意根据 c5 查到的第一个记录是 c10因此不会加 (0,5]这个 next-key lock。 之后 session B 的第一个 update 语句要把 c5 改成 c1,可以理解为两步
插入 (c1, id5) 这个记录删除 (c5, id5) 这个记录。
按照上一节说的索引 c 上 (5,10) 间隙是由这个间隙右边的记录也就是 c10 定义的。所以通过这个操作session A 的加锁范围变成了图 7 所示的样子
图 7 session B 修改后 session A 的加锁范围 接下来 session B 要执行 update t set c 5 where c 1 这个语句了一样地可以拆成两步:
插入 (c5, id5) 这个记录删除 (c1, id5) 这个记录。
第一步试图在已经加了间隙锁的 (1,10) 中插入数据所以就被堵住了。 思考 一个空表有间隙吗这个间隙是由谁定义的怎么验证这个结论呢 一个空表就只有一个间隙。 在空表上执行begin; select * from t where id1 for update; 这个查询语句加锁的范围就是 next-key lock (-∞, supremum]。 验证方法
复现空表的 next-key lock
show engine innodb status 部分结果 2. 误删数据后怎么办?
先对和 MySQL 相关的误删数据做下分类
使用 delete 语句误删数据行使用 drop table 或者 truncate table 语句误删数据表使用 drop database 语句误删数据库使用 rm 命令误删整个 MySQL 实例。
2.1 删除行
使用 delete 语句误删了数据行可以用 Flashback 工具通过闪回把数据恢复回来。 原理是: 修改 binlog 的内容拿回原库重放。而能够使用这个方案的前提是需要确保 binlog_formatrow 和 binlog_row_imageFULL。 具体恢复数据时对单个事务做如下处理
对于 insert 语句对应的 binlog event 类型是 Write_rows event把它改成 Delete_rows event 即可同理对于 delete 语句也是将 Delete_rows event 改为 Write_rows event而如果是 Update_rows 的话binlog 里面记录了数据行修改前和修改后的值对调这两行的位置即可。
如果误操作不是一个而是多个会怎么样呢比如下面三个事务
(A)delete ...
(B)insert ...
(C)update ...要把数据库恢复回这三个事务操作之前的状态用 Flashback 工具解析 binlog 后写回主库的命令是
(reverse C)update ...
(reverse B)delete ...
(reverse A)insert ...也就是说如果误删数据涉及到了多个事务的话需要将事务的顺序调过来再执行。
需要说明的是不建议你直接在主库上执行这些操作。 恢复数据比较安全的做法是恢复出一个备份或者找一个从库作为临时库在这个临时库上执行这些操作然后再将确认过的临时库的数据恢复回主库。 这是因为一个在执行线上逻辑的主库数据状态的变更往往是有关联的。可能由于发现数据问题的时间晚了一点儿就导致已经在之前误操作的基础上业务代码逻辑又继续修改了其他数据。所以如果这时候单独恢复这几行数据而又未经确认的话就可能会出现对数据的二次破坏。 不止要说误删数据的事后处理办法更重要是要做到事前预防。有以下两个建议
把 sql_safe_updates 参数设置为 on。这样一来如果我们忘记在 delete 或者 update 语句中写 where 条件或者 where 条件里面没有包含索引字段的话这条语句的执行就会报错。代码上线前必须经过 SQL 审计。
设置了 sql_safe_updateson如果真的要把一个小表的数据全部删掉应该怎么办呢 如果确定这个删除操作没问题的话可以在 delete 语句中加上 where 条件比如 where id0。
但是delete 全表是很慢的需要生成回滚日志、写 redo、写 binlog。
所以从性能角度考虑你该优先考虑使用 truncate table 或者 drop table 命令。
2.2 误删库/表
这种情况下要想恢复数据就需要使用全量备份加增量日志的方式了。这个方案要求线上有定期的全量备份并且实时备份 binlog。
在这两个条件都具备的情况下假如有人中午 12 点误删了一个库恢复数据的流程如下
取最近一次全量备份假设这个库是一天一备上次备份是当天 0 点用备份恢复出一个临时库从日志备份里面取出凌晨 0 点之后的日志把这些日志除了误删除数据的语句外全部应用到临时库。
流程的示意图如下所示
图 1 数据恢复流程 -mysqlbinlog 方法 说明:
为了加速数据恢复如果这个临时库上有多个数据库可以在使用 mysqlbinlog 命令时加上一个–database 参数用来指定误删表所在的库。这样就避免了在恢复数据时还要应用其他库日志的情况。在应用日志的时候需要跳过 12 点误操作的那个语句的 binlog
如果原实例没有使用 GTID 模式只能在应用到包含 12 点的 binlog 文件的时候先用–stop-position 参数执行到误操作之前的日志然后再用–start-position 从误操作之后的日志继续执行如果实例使用了 GTID 模式就方便多了。假设误操作命令的 GTID 是 gtid1那么只需要执行 set gtid_nextgtid1;begin;commit; 先把这个 GTID 加到临时实例的 GTID 集合之后按顺序执行 binlog 的时候就会自动跳过误操作的语句。
不过即使这样使用 mysqlbinlog 方法恢复数据还是不够快主要原因有两个
如果是误删表最好就是只恢复出这张表也就是只重放这张表的操作但是 mysqlbinlog 工具并不能指定只解析一个表的日志用 mysqlbinlog 解析出日志应用应用日志的过程就只能是单线程。
一种加速的方法是在用备份恢复出临时实例之后将这个临时实例设置成线上备库的从库这样:
在 start slave 之前先通过执行change replication filter replicate_do_table (tbl_name) 命令就可以让临时库只同步误操作的表这样做也可以用上并行复制技术来加速整个数据恢复过程。
过程的示意图如下所示:
图 2 数据恢复流程 -master-slave 方法 图中 binlog 备份系统到线上备库有一条虚线是指如果由于时间太久备库上已经删除了临时实例需要的 binlog 的话我们可以从 binlog 备份系统中找到需要的 binlog再放回备库中。 假设发现当前临时实例需要的 binlog 是从 master.000005 开始的但是在备库上执行 show binlogs 显示的最小的 binlog 文件是 master.000007意味着少了两个 binlog 文件。这时我们就需要去 binlog 备份系统中找到这两个文件。
把之前删掉的 binlog 放回备库的操作步骤是这样的
从备份系统下载 master.000005 和 master.000006 这两个文件放到备库的日志目录下打开日志目录下的 master.index 文件在文件开头加入两行内容分别是 “./master.000005”和“./master.000006”;重启备库目的是要让备库重新识别这两个日志文件现在这个备库上就有了临时库需要的所有 binlog 了建立主备关系就可以正常同步了。
不论是把 mysqlbinlog 工具解析出的 binlog 文件应用到临时库还是把临时库接到备库上这两个方案的共同点是误删库或者表后恢复数据的思路主要就是通过备份再加上应用 binlog 的方式。
就是说这两个方案都要求备份系统定期备份全量日志而且需要确保 binlog 在被从本地删除之前已经做了备份。
但是一个系统不可能备份无限的日志还需要根据成本和磁盘空间资源设定一个日志保留的天数。
建议 不论使用上述哪种方式都要把这个数据恢复功能做成自动化工具并且经常拿出来演练。 虽然“发生这种事大家都不想的”但是万一出现了误删事件能够快速恢复数据将损失降到最小也应该不用跑路了。如果临时再手忙脚乱地手动操作最后又误操作了对业务造成了二次伤害那就说不过去了。 2.3 延迟复制备库
虽然可以通过利用并行复制来加速恢复数据的过程但是这个方案仍然存在“恢复时间不可控”的问题。
如果一个库的备份特别大或者误操作的时间距离上一个全量备份的时间较长比如一周一备的实例在备份之后的第 6 天发生误操作那就需要恢复 6 天的日志这个恢复时间可能是要按天来计算的。
有什么方法可以缩短恢复数据需要的时间呢
如果有非常核心的业务不允许太长的恢复时间我们可以考虑搭建延迟复制的备库。 这个功能是 MySQL 5.6 版本引入的。
一般的主备复制结构存在的问题是如果主库上有个表被误删了这个命令很快也会被发给所有从库进而导致所有从库的数据表也都一起被误删了。
延迟复制的备库是一种特殊的备库通过 CHANGE MASTER TO MASTER_DELAY N 命令可以指定这个备库持续保持跟主库有 N 秒的延迟。 比如把 N 设置为 3600这就代表了如果主库上有数据被误删了并且在 1 小时内发现了这个误操作命令这个命令就还没有在这个延迟复制的备库执行。 这时候到这个备库上执行 stop slave再通过之前介绍的方法跳过误操作命令就可以恢复出需要的数据。 这样的话就随时可以得到一个只需要最多再追 1 小时就可以恢复出数据的临时实例也就缩短了整个数据恢复需要的时间。
2.4 预防误删库 / 表的方法
2.4.1 账号分离
这样做的目的是避免写错命令。比如
我们只给业务开发同学 DML 权限而不给 truncate/drop 权限。而如果业务开发人员有 DDL 需求的话也可以通过开发管理系统得到支持。即使是 DBA 团队成员日常也都规定只使用只读账号必要的时候才使用有更新权限的账号。
2.4.2 制定操作规范
这样做的目的是避免写错要删除的表名。比如
在删除数据表之前必须先对表做改名操作。然后观察一段时间确保对业务无影响以后再删除这张表。改表名的时候要求给表名加固定的后缀比如加 _to_be_deleted)然后删除表的动作必须通过管理系统执行。并且管理系删除表的时候只能删除固定后缀的表。
2.5 rm 删除数据
对于一个有高可用机制的 MySQL 集群来说最不怕的就是 rm 删除数据了。只要不是恶意地把整个集群删除而只是删掉了其中某一个节点的数据的话HA 系统就会开始工作选出一个新的主库从而保证整个集群的正常工作。
这时要做的就是在这个节点上把数据恢复回来再接入整个集群。
现在不止是 DBA 有自动化系统SA系统管理员也有自动化系统所以也许一个批量下线机器的操作会让你整个 MySQL 集群的所有节点都全军覆没。
应对这种情况建议只能是说尽量把备份跨机房或者最好是跨城市保存。 小结
预防远比处理的意义来得大定期检查备份的有效性也很有必要可以使用show grants 命令查看账户的权限如果权限过大可以建议 DBA 同学给你分配权限低一些的账号。 3. 为何有kill不掉的语句
MySQL 中有两个 kill 命令
一个是 kill query 线程 id表示终止这个线程中正在执行的语句一个是 kill connection 线程 id这里 connection 可缺省表示断开这个线程的连接当然如果这个线程有语句正在执行也是要先停止正在执行的语句的。
在使用 MySQL 的时候使用了 kill 命令却没能断开这个连接。再执行 show processlist 命令看到这条语句的 Command 列显示的是 Killed。我们就来讨论一下这个问题。
其实大多数情况下kill query/connection 命令是有效的。比如执行一个查询的过程中发现执行时间太久要放弃继续查询这时我们就可以用 kill query 命令终止这条查询语句。
还有一种情况是语句处于锁等待的时候直接使用 kill 命令也是有效的。我们一起来看下这个例子
图 1 kill query 成功的例子
看到session C 执行 kill query 以后session B 几乎同时就提示了语句被中断。
3.1 收到 kill 以后线程做什么
session B 是直接终止掉线程什么都不管就直接退出吗显然这是不行的。 当对一个表做增删改查操作时会在表上加 MDL 读锁。所以session B 虽然处于 blocked 状态但还是拿着一个 MDL 读锁的。如果线程被 kill 的时候就直接终止那之后这个 MDL 读锁就没机会被释放了。 kill 并不是马上停止的意思而是告诉执行线程说这条语句已经不需要继续执行了可以开始“执行停止的逻辑了”。
实现上当用户执行 kill query thread_id_B 时MySQL 里处理 kill 命令的线程做了两件事
把 session B 的运行状态改成 THD::KILL_QUERY(将变量 killed 赋值为 THD::KILL_QUERY)给 session B 的执行线程发一个信号。
为什么要发信号呢 因为像图 1 的我们例子里面session B 处于锁等待状态如果只是把 session B 的线程状态设置 THD::KILL_QUERY线程 B 并不知道这个状态变化还是会继续等待。发一个信号的目的就是让 session B 退出等待来处理这个 THD::KILL_QUERY 状态。 这里隐含了这么三层意思
一个语句执行过程中有多处“埋点”在这些“埋点”的地方判断线程状态如果发现线程状态是 THD::KILL_QUERY才开始进入语句终止逻辑如果处于等待状态必须是一个可以被唤醒的等待否则根本不会执行到“埋点”处语句从开始进入终止逻辑到终止逻辑完全完成是有一个过程的。
再看一个 kill 不掉的例子 innodb_thread_concurrency 不够用的例子。首先执行 set global innodb_thread_concurrency2将 InnoDB 的并发线程上限数设置为 2然后执行下面的序列
图 2 kill query 无效的例子
可以看到
sesssion C 执行的时候被堵住了但是 session D 执行的 kill query C 命令却没什么效果直到 session E 执行了 kill connection 命令才断开了 session C 的连接提示“Lost connection to MySQL server during query”但是这时候如果在 session E 中执行 show processlist你就能看到下面这个图 这时候id12 这个线程的 Commnad 列显示的是 Killed。也就是说客户端虽然断开了连接但实际上服务端上这条语句还在执行过程中。
为什么在执行 kill query 命令时这条语句不像第一个例子的 update 语句一样退出呢 在实现上等行锁时使用的是 pthread_cond_timedwait 函数这个等待状态可以被唤醒。但是在这个例子里12 号线程的等待逻辑是这样的每 10 毫秒判断一下是否可以进入 InnoDB 执行如果不行就调用 nanosleep 函数进入 sleep 状态。 也就是说虽然 12 号线程的状态已经被设置成了 KILL_QUERY但是在这个等待进入 InnoDB 的循环过程中并没有去判断线程的状态因此根本不会进入终止逻辑阶段。 当 session E 执行 kill connection 命令时是这么做的:
把 12 号线程状态设置为 KILL_CONNECTION关掉 12 号线程的网络连接。因为有这个操作所以你会看到这时候 session C 收到了断开连接的提示。
为什么执行 show processlist 的时候会看到 Command 列显示为 killed 呢其实这就是因为在执行 show processlist 的时候有一个特别的逻辑 如果一个线程的状态是KILL_CONNECTION就把Command列显示成Killed。 其实即使是客户端退出了这个线程的状态仍然是在等待中。
那这个线程什么时候会退出呢 只有等到满足进入 InnoDB 的条件后session C 的查询语句继续执行然后才有可能判断到线程状态已经变成了 KILL_QUERY 或者 KILL_CONNECTION再进入终止逻辑阶段。 小结一下: 这个例子是 kill 无效的第一类情况即线程没有执行到判断线程状态的逻辑。跟这种情况相同的还有由于 IO 压力过大读写 IO 的函数一直无法返回导致不能及时判断线程的状态。
另一类情况是终止逻辑耗时较长。这时候从 show processlist 结果上看也是 CommandKilled需要等到终止逻辑完成语句才算真正完成。
这类情况比较常见的场景有以下几种
超大事务执行期间被 kill。这时候回滚操作需要对事务执行期间生成的所有新数据版本做回收操作耗时很长。大查询回滚。如果查询过程中生成了比较大的临时文件加上此时文件系统压力大删除临时文件可能需要等待 IO 资源导致耗时较长。DDL 命令执行到最后阶段如果被 kill需要删除中间过程的临时文件也可能受 IO 资源影响耗时较久。
如果直接在客户端通过 CtrlC 命令是不是就可以直接终止线程呢 不可以。其实在客户端的操作只能操作到客户端的线程客户端和服务端只能通过网络交互是不可能直接操作服务端线程的。 而由于 MySQL 是停等协议所以这个线程执行的语句还没有返回的时候再往这个连接里面继续发命令也是没有用的。实际上执行 CtrlC 的时候是 MySQL 客户端另外启动一个连接然后发送一个 kill query 命令。 3.2 关于客户端的误解
第一个误解如果库里面的表特别多连接就会很慢。 图 4 连接等待 每个客户端在和服务端建立连接的时候需要做的事情就是 TCP 握手、用户校验、获取权限。但这几个操作显然跟库里面表的个数无关。 但实际上正如图中的文字提示所说的当使用默认参数连接的时候MySQL 客户端会提供一个本地库名和表名补全的功能。为了实现这个功能客户端在连接成功后需要多做一些操作 执行 show databases切到 db1 库执行 show tables把这两个命令的结果用于构建一个本地的哈希表。 在这些操作中最花时间的就是第三步在本地构建哈希表的操作。所以当一个库中的表个数非常多的时候这一步就会花比较长的时间。也就是说感知到的连接过程慢其实并不是连接慢也不是服务端慢而是客户端慢。 图中的提示也说了如果在连接命令中加上 -A就可以关掉这个自动补全的功能然后客户端就可以快速返回了。除了加 -A 以外加–quick(或者简写为 -q) 参数也可以跳过这个阶段 第二个误解–quick 是一个更容易引起误会的参数。 是不是觉得这应该是一个让服务端加速的参数但实际上恰恰相反设置了这个参数可能会降低服务端的性能。 MySQL 客户端发送请求后接收服务端返回结果的方式有两种 一种是本地缓存也就是在本地开一片内存先把结果存起来。如果用 API 开发对应的就是 mysql_store_result 方法。另一种是不缓存读一个处理一个。如果用 API 开发对应的就是 mysql_use_result 方法。 MySQL 客户端默认采用第一种方式而如果加上–quick 参数就会使用第二种不缓存的方式。 采用不缓存的方式时如果本地处理得慢就会导致服务端发送结果被阻塞因此会让服务端变慢。 既然这样为什么要给这个参数取名叫作 quick 呢这是因为使用这个参数可以达到以下三点效果 第一点就是前面提到的跳过表名自动补全功能。第二点mysql_store_result 需要申请本地内存来缓存查询结果如果查询结果太大会耗费较多的本地内存可能会影响客户端本地机器的性能第三点是不会把执行命令记录到本地的命令历史文件。 思考 如果碰到一个被 killed 的事务一直处于回滚状态你认为是应该直接把 MySQL 进程强行重启还是应该让它自己执行完成呢为什么呢 因为重启之后该做的回滚动作还是不能少的所以从恢复速度的角度来说应该让它自己结束。 如果这个语句可能会占用别的锁或者由于占用 IO 资源过多从而影响到了别的语句执行的话就需要先做主备切换切到新主库提供服务。 切换之后别的线程都断开了连接自动停止执行。接下来还是等它自己执行完成。这个操作属于我们在文章中说到的减少系统压力加速终止逻辑。 来自林晓斌《MySql实战45讲》
