一、短链接风暴
正常的短连接模式是连接到数据库后、执行很少的SQL语句就断开、下次需要再重连、若使用的是短连接、在业务高峰期、可能会出现连接数暴涨的情况. MySQL建联的成本是比较高的、除正常的网络三次握手外、还需要登录权限判断和获得这个连接的数据读写权限.

DB压力小时、这些成本并不明显、但DB处理较慢时、连接数就会暴涨、max_connections参数、用来控制一个MySQL实例同时存在的连接数的上限、超过该值、系统会拒绝接下来的连接请求. 此时一个自然的想法是: 调高max_connections的值、但更多请求进来、可能会导致系统的负载进一步变大、大量的资源消耗在鉴权等逻辑上、已经拿到连接的线程拿不到CPU资源去执行请求, 适得其反.

可以怎么做呢 ?

1. 先处理掉那些占着连接、但不工作的线程.
   通过kill_connection主动踢掉. 类似提前设置wait_timeout参数(一个线程空闲wait_timeout秒之后、MySQL会主动断开连接)
   information_schema.innodb_trx 表可以查看事务具体状态.
   从服务端断开: kill connection + id. 此时Client处于sleep状态、连接被服务端主动断开后、Client不会马上知道、直到Client发起下一个请求, 才会收到 Lost Connection的错误.
   此时若Client不重新建联、而直接使用原连接重试、会觉得MySQL一直没恢复

2. 减少连接过程的消耗
   有的业务代码会在短时间内先申请大量数据库连接备用、若现在数据库确认是被连接打挂了、可以先让DB跳过鉴权. 重启DB、使用 –skip-grant-tables 参数启动.
   但这样MySQL会跳过所有的鉴权、包括连接过程和语句执行过程, 风险极高、尤其是允许外网访问时.
   Mysql8.0, 跳过鉴权时、会默认打开–skip-networking只允许本地Client可见.

二、慢查询性能问题.
可能引发慢查询一般有3种可能:

1. 索引没设计好

2. SQL没写好

3. MySQL选错了索引

4. 索引未设计好、MySQL5.6以后创建索引都支持Online了、一般高峰期DB被SQL打挂时、最高效的做法就是快速添加索引. 若有主备、最高在备库先执行、
   1) 在备库执行set sql_bin_log=off, 关闭binlog写入、执行alter语句添加索引
   2) 执行主备切换、
   3) 此时主库是B、备库是A、在A上执行 set sql_log_bin=off, 执行alter语句添加索引
   平时做变更时、应考虑 gh-ost 更稳妥、但紧急处理时、上边的方案最高效.

5. 语句没写好
   Mysql5.7以上的版本、提供了 query_rewrite 功能、可以将输入的语句改写成另外一种模式.
   eg. select * from t where id+1=10000 会让索引失效、可以通过一个改写语句解决

1
2
3
4

insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) 
values ("select * from t where id + 1 = ?", "select * from t where id = ? - 1", "db1");

call query_rewrite.flush_rewrite_rules(); // 让新插入的规则生效

3. 索引没选对、可以通过上线前的SQL分析来提早发现.

三、QPS突增问题
有时业务突然出现高峰、或者应用程序bug导致某个语句的QPS暴涨、也可能导致MySQL压力过大、影响服务.

1. 若是新业务bug导致、且新业务可以下掉、只是时间没那么快、可以直接从DB上把白名单禁掉.
2. 若这个新功能使用的是独立账号、可以将该账号禁用, 这样由它引发的QPS就会降到0
3. 若新功能是跟主体功能部署在一起的、只能通过处理语句来限制, 可以使用上边的重写功能、将压力最大的SQL重写为 select 1.
   当然该操作的风险很高、它可能导致:
4. 若别的功能也用到了该SQL、会被误伤
5. 很多业务不是靠一个SQL就完成逻辑的、这样会导致后边的业务一起失败.

一、主备延迟
数据同步的关键时间节点:

1. 主库A执行完一个事务、写入binlog、记为 T1;
2. 传给备库B、将备库B接收完binlog的时刻记为 T2;
3. 备库B执行完事务的时刻记为 T3;
   主备延迟: 同一个事务在备库执行完的时间和主库执行完的时间之间的差值: T3 - T1
   show slave status; - seconds_behind_master表示当前备库延迟时间

注: 主备机的系统时间不一致时、会不会导致主备延迟值不准 ?
不会. 备库连接主库时、会通过执行 select unix_timestamp 获得当前主库的系统时间、与本机不一致时、会在执行 seconds_behind_master 时扣除差值

二、主备延迟的来源

1. 有些部署条件下、备库所在机器的性能较差
   此时一般将备库设为非双1模式. 现在一般会使用对称部署(主备规格相同).
2. 使用对称部署、为何仍然存在主备延迟 ?
   备库可能压力较大、存在一些慢查询、导致备库查询消耗大量CPU资源、影响数据同步、造成主备延迟.
   解决:
   1) 使用一主多从、分担读压力
   2) 通过binlog输出到外部系统、如: hadoop, 提供统计类查询
3. 采用了一主多从、备库的压力不超过主库的情况下、还有什么情况会导致主备延迟呢 ?
   1) 可能存在大的事务, 因为主库上必须等事务执行完成才会写入binlog、再传给备库、若一个SQL执行10min、这个事务就很可能会导致从库延迟10min.(所以最好不要一次delete太多数据)
   2) 大表DDL也是一个大事务的场景
4. 如果备库上也不做大事务了、还有什么原因会导致主备延迟吗 ?
   备库的并行复制能力.

三、主备切换策略:

1. 可靠性优先.
   1) 先判断备库B的 seconds_behind_master 是否小于指定值, 若否、持续观察
   2) 把主库A改成只读状态, readonly设为true.
   3) 判断备库B的 seconds_behind_master 值、直到变为0
   4) 把备库B改为可写入, readonly设为false.
   5) 将业务请求切到B
   2~5 的过程、系统处于不可用状态.

2. 可用性优先
   将1中、4)、5) 调整到最开始执行、直接让备库可读写、系统就无不可用时间、但会产生数据不一致. 暂时称为可用性优先策略
   此时若 binlog_format 为: statement、则会发生数据不一致的线下
   若 binlog_format 为: row、会提示错误

所以、MySQL高可用系统的可用性是依赖于主备延迟的、延迟的时间越小、在主库故障时、服务恢复需要的时间就越短, 可用性就越高.

一、binlog的写入机制

1. binlog写入逻辑:
   1) 事务执行过程中、先写日志导binlog cache、事务提交时、再把binlog cache写入到binlog文件中.
   2) 一个事务的binlog不能被拆开、因此不论事务有多大、也要确保一次性写入. 系统给binlog cache每个线程分配一片内存(binlog_cache_size大小), 超过会先暂存到磁盘.
   3) 事务提交时、执行器会把binlog cache里的完整事务写入binlog、清空binlog cache.
2. 每个线程有自己的binlog cache、但共用binlog文件.
   事务提交、先写入到文件系统的page cache(write), 然后调用fsync写入磁盘(占用IOPS)
   write和fsync的时机:
   sync_binlog=0, 每次事务提交只write、不fsync
   sync_binlog=1, 每次事务提交都执行fsync
   sync_binlog=N, 每次事务提交都write、累积N个事务才fsync
   所以、在出现IO瓶颈的场景里、可以将sync_binlog设置为一个比较大的值、可以提升性能.
   风险: 若主机异常重启、会丢失最近N个事务的binlog.

二、redo log的写入机制
事务执行过程中会先写redo log buffer, 然后才写redo log

从redo log的三种状态说起:

1. 存在redo log buffer中、物理上是在 mysql 进程内存中.
2. 写到磁盘write、但未持久化fsync、物理上是在文件系统的Page Cache中
3. 持久化到磁盘

1、2的过程都很快、但3的速度就慢很多了. InnoDB提供了三种策略, 通过 innodb_flush_log_at_trx_commit参数控制:

1. 0, 表示每次事务提交只把redo log留在redo log buffer中
2. 1, 表示每次事务提交都将redo log直接持久化到磁盘
3. 2, 表示每次事务提交都把redo log写到Page Cache.
   InnoDB 有一个后台线程、每隔1s、会把redo log buffer中的日志调用write写到FS Pae Cache、然后调用 fsync 持久化到磁盘.

注意: 事务执行过程中的redo log也是在buffer中、可能会被后台线程一起持久化到磁盘
还有两种场景会将一个未提交的事务redo log写入磁盘:

1. redo log buffer占用的空间即将达到innodb_log_buffer_size 一半的时候、后台线程会主动写盘. (此时事务未提交、只是write、并未fsync, 即: 只留在FS Page Cache)
2. 并行事务提交时, 顺带将该事务的 redo log buffer持久化到磁盘、eg. Trx A执行到一半、Trx B要把buffer数据写入磁盘、会顺带把Trx A的日志一起持久化到磁盘
   注意: 若将 innodb_flush_log_at_trx_commit 设为1, redo log在prepare阶段就要持久化一次、因为有一个崩溃恢复依赖于prepare的redo log + binlog.
   通常说的双1配置、是redo log 和binlog的刷盘机制都设为1, 即: 一个事务完整提交前、需要等待两次刷盘: redo log(prepare阶段) 和 binlog

思考: TPS 2w/s 的话、写盘就是 4w/s, 但磁盘能力只有2w/s, 是怎么实现的呢 ?
组提交: 三个并发事务trx1, trx2, trx3, 对应LSN(日志逻辑序列化、单调递增)分别为:50, 120, 160, trx1写盘时、这组(trx1->3)已经有3个事务、LSN也变成了160, 去写盘时、带的LSN=160, 等trx1返回时、所有LSN<160的redo log都已持久化到磁盘, trx2,trx3可直接返回.

在并发更新场景下、第一个事务写完 redo log buffer、调用fsync越晚、组员越多、节约IOPS效果越好.
binlog的write 和 fsync的时间间隔短、组提交优化不如redo log.
可以通过设置以下参数来提升效果:

1. binlog_group_commit_sync_delay, 延迟x 微秒后才调用fsync
2. binlog_group_commit_sync_no_delay_count, 累积x次以后调用fsync
   二者满足其一就调用 fsync

另: 不建议设置 innodb_flush_log_at_trx_commit=0, 因为这样redo log只保存在内存中、MySQL异常重启会丢失数据、风险太大. 而redo log写到 FS Page Cache的速度也是很快的、不会损失很多性能, 可以保证异常重启不丢数据、风险小很多.

[toc]

SQL执行流程

MySQL架构图

server层、

包含连接器、查询缓存、分析器、优化器、执行器等、涵盖mysql大部分核心功能、
及mysql所有的内置函数(日期、时间、数学和加密函数等)、所有跨存储引擎的功能
(视图、存储过程、触发器等)都在这一层实现

连接器

1
2
3
4
5
6
7
8
9
10

mysql -h$ip -P$port -u$user -p
若 用户名和密码 认证失败、会返回 Access denied for user 的错误
若认证通过、这个连接里的权限都会依赖此时权限表查到的权限
此时用admin账号修改该用户的权限、不会立即生效、会在重新连接时生效

连接完成后、无后续动作、则连接处于sleep状态、

client 长时间(wait_time参数控制)不操作、连接器会断开连接

连接断开之后、再发送请求、会收到 Lost connection to mysql server during query的响应

查询缓存

1
2

key 是 sql、value是查询结果 - 任意更新会导致缓存失效
不建议使用

分析器

1
2
3
4
5
6
7
8
9

词法分析: 根据关键词识别语句类型 select/update/insert
语法分析: 若有语法错误、会返回 You have an error in your SQL 

elect 1 from t where id=1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'elect 1 from t where id=1' at line 1
                         
``` 

##### 优化器

经过分析器、mysql知道了想要做什么、优化器干什么呢 ？

1. 表中有多个索引的时候、决定使用哪个索引
2. 多表join的时候、决定连表顺序

1
2
3
4
5

![image.png](https://upload-images.jianshu.io/upload_images/14027542-b2765ac683595a26.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)


##### 执行器

在分析器、优化器之后、mysql知道了要做什么、怎么做、就开始进入到执行器阶段、开始执行

1. 命中查询缓存、会在查询缓存返回结果时进行权限验证

2. 非命中 先验证权限、无 -> 直接返回错误

   有 -> 执行SQL

3. eg. mysql> select * from T where ID=10;

   若 id 无索引:
   1) 调用innodb引擎接口查询第一行、若id = 10、存入结果集、否则 跳过、继续
   2) 调用引擎接口取下一行、直至最后一行
   3) 执行器将满足结果的行作为结果集返回给client

   若id 有索引:
   1) 调用innodb查询 满足条件的 第一行、放入结果集
   2) 查询满足条件的下一行..
   3) 将结果集返回给client

   在db慢SQL中、有一个 rows_examined 字段、表示在SQL执行过程中扫描了多少行、
   是在 执行器 调用 引擎 获取数据行的时候累计的、
   注：有些情况下、执行器调用一次会扫描很多行、所以 实际扫描行数 != rows_examined

redolog(重做日志 - innodb引擎层特有)

1
2
3
4
5
6
7
8

MySQL使用AWL(Write-Ahead Logging)技术, 当有记录需要更新的时候、innodb就先把记录写到redo log、并更新内存、这时、更新就算完成. 当innodb引擎比较空闲的时候、会将操作更新到磁盘

触发更新的点:
1. redo log 写满时(redo log文件大小是固定的 - 循环覆盖) 
   write pos: 当前位置 
   check point: 当前要擦除的位置
   write和check间有空间、就代表可更新、否则、就要先擦除记录、推进checkpoint、
2.

binlog(归档日志 - MySQL server层实现)

1
2
3
4
5
6

mysql引擎实现的日志、与redo log 的差异？
1. redo log 是innodb特有的; binlog是mysql server层实现、所有引擎可以使用
2. redo log 记录的是物理日志、记录的是"在某个数据页上做了什么修改"
   binlog 是逻辑日志、记录的是原始逻辑、eg. 给id=2的c字段+1
3. redo log 循环写、空间固定 
    binlog 是追加写、日志写满会切换到下一个

为什么有redo log 和 binlog ？

1
2
3
4
5
6
7
8

sync_binlog: 1 - 每次都持久化到磁盘
binlog: statment格式、记录的是sql语句、
           row格式、记录的是行内容、更新前后都有

mysql整体包含: 
server层 - 主要做的是mysql功能层面的事情、
引擎层 - 负责存储
最初、mysql无innodb引擎、MySQL自带的引擎是myisam、但它没有crash-safe的能力、server层的binlog日志、只能用于归档、innodb是另外一个公司以插件形式引入、用redo log 来实现crash-safe能力

update语句执行

1
2
3
4
5
6

update table set c=c+1 where id=2
1. 执行器先找引擎取 id=2 这一行、id是主键、引擎可以直接使用树搜索找到. 若 id = 2 所在的数据页本来就在内存中、直接返回给执行器; 否则、先从磁盘读入内存、再返回
2. 执行器拿到引擎给出的数据行、把值 +1 、得到新的一行数据、再调用引擎接口写入新的数据行
3. 引擎将新的数据行更新到内存、同时将更新操作记录到 redo log. 此时redo log 处于 prepare 状态、然后告诉执行器、执行完成、随时可以提交事务.
4. 执行器生成操作的binlog、并把binlog写入磁盘
5. 执行器调用引擎的提交事务接口、引擎把刚刚写入的redo log改成commit状态、更新完成

为什么需要两阶段提交

1
2
3
4
5
6
7
8

 redo log 和 binlog 是两个独立的逻辑、若不使用两阶段提交、则会是2种情况
1. 先 redo log、 后binlog
   若redolog完成、binlog未完成、进程异常重启、redo log生效、为修改后的值 
  若使用binlog恢复临时库、由于binlog未写完就崩溃、会少一条记录、源库和备库就会出现不一致
2. 先binlog、后 redo log
    binlog写完后crash、redo log未写、在恢复时、认为事务无效、
    binlog已写完、恢复备份库时、认为有效、出现不一致...
所以、为了保证redo log 和 binlog提交状态的逻辑一致性

疑问

1
2
3
4
5
6

1. binlog 是每次update都会写磁盘？
2. 如果 redo log 第一阶段完成(prepare状态)、binlog完成、此时crash、在系统重启之后、这个log会被重放吗
   会、满足prepare 和 binlog的完整、在重启时、会自动执行commit
3. 极端情况下、redo log被写满、新的事务进入、需要擦除redo log(被修改的脏页被迫刷新到磁盘) -> 数据在 commit 之前被持久化、此时如何处理 ？
这些数据在内存中属于无效事务、其它事务读不到、即时被写入磁盘也没关系、再次读入内存时、依然是原逻辑
4. redo log 是顺序写 ？ binlog是随机写 ？

lock

1. 查看事务隔离级别

select @@transaction_isolation;

2. 设置全局事务(影响新的会话、不影响本会话)

set global transaction isolation level read committed;

3. 设置会话事务(影响本会话)

set session transaction isolation level read committed;

4. 查看mysql默认读取的 my.cnf 的命令

mysql –help | grep ‘my.cnf’

查看mysql是否使用了指定目录的 my.cnf

ps aux | grep ‘my.cnf’

4. 查看mysqlbinlog

   mysqlbinlog –no-defaults /var/lib/mysql/mysqld-bin.000001 –start-position=2425

5. 查看binlog的位置

   show variables like ‘%log_bin%’;

   开启binlog

   1 2 3 4

   server-id=1 log_bin=ON log_bin_basename=/var/lib/mysql/mysql-bin log_bin_index=/var/lib/mysql/mysql-bin.index

   查看所有的binlog文件

   show binary logs;

   查看指定binlog文件的内存

   show binlog events in ‘{name}’;

   当前日志的文件名和偏移位置

   show master status;

   刷新日志文件

   flush logs;

   查看死锁配置

   show variables like ‘%deadlock%’

6. mysqlbinlog工具查看
   基于时间:

   mysqlbinlog –start-datetime=’2019-05-19 13:00’ –stop-datetime=’2019-05-19 14:00’

   基于偏移量

   mysqlbinlog –start-postion=107 –stop-position=1000 -d {db} {binlog}

   row格式文件的查看 添加-vv参数

7. 开启一致性视图

   start transaction with consistent snapshot;

8. 查看innodb页大小

   show global status like ‘innodb_page_size’;
   show variables like ‘innodb_page_size’;

9. 查看表基本信息

   select * from information_schema.tables where TABLE_NAME like ‘car_order_finished_collect_2019’;

10. 开启profile

show variables like ‘profiling’; set profiling=1;

11. 显示所有的profile

    show profiles;

12. 显示第n个profile的详情

    show profile for query n;
    show profile all for query n;

13. 查看innodb redo log配置

    show variables like ‘%innodb_log_file%’;
    innodb_log_file_size 单个redo log大小、
    innodb_log_files_in_group redo log文件数量

14. 查看innodb io控制

    show variables like ‘innodb_io_capacity’

15. innodb脏页比例

    show variables like ‘innodb_max_dirty_pages_pct’

16. 计算innodb当前脏页比例

    show global status like ‘Innodb_buffer_pool_pages_%’;
    Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total

感谢林老师的mysql实战45讲
https://time.geekbang.org/column/intro/139

希望老师越来越好、也希望自己能学得一二~

锁的种类和概念

Shared and Exclusive Locks

1
2
3
4
5
6
7

Shard Lock: 共享锁
官方: permits the transaction that holds the lock to read a row.
eg. select * from table where id=1 lock in share mode;

Exclusive Locks: 排他锁
官方: permits the transaction that holds the lock to update or delete a row
eg. select * from table where id=1 for update;

Intention Locks

1
2
3
4
5

锁是加在table上的、表示要对下一个层级(记录)加锁
Intention shared(IS): Transaction T intends to set S locks on individual rows in table t
Intention exclusive(IX): Transaction T intends to set X lock on those rows
在db层看到的是：
Table Lock table `db`.`table` trx_id 12121212 lock mode IX

Record Locks

1
2
3
4
5
6

在db层看到的是:
Record Locks space id in 281 page no 3 n bits 72 index PRIMARY of table `db`.`table` trx id 12121212 lock_mode X rec but not gap
锁是加在索引上的(从 index primary of table `db`.`table` ) 可以看出
记录锁有两种类型: 
1. lock_mode X locks rec but not gap
2. lock_mode S locks rect bot not gap

Gap Locks

1
2
3
4

在db层看到的是:
Record Locks space id 281 pages no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133588125 lock_mode X locks gap before rec
gap锁是用来防止insert的
锁的不是记录、而是记录之间的间隙、eg. (10,20)

Insert intention Locks

1
2

在db层看到的是:
Record Locks space id 279 page no 3 n bits 72 index primary of table `lc_3`.`t1` trx id 133587907 lock_mode X insert intention waiting

next-Key locks

1
2
3

在db看到的是:
Record Locks space id 281 page no 5 n bits index idx_c of table `lc_3`.`t1` trx id 133587907 lock_mode X
Next-Key Locks = Gap Locks + Record Locks会同时锁住记录和间隙

Anto-inc Locks

1
2
3

在db层看到的结果是:
Table Lock table xx trx id 7498948 lock mode auto-inc waiting 
属于表级别的锁 http://keithlan.github.io/2017/03/03/auto_increment_lock/

显式锁 vs 隐式锁

1
2
3
4
5
6
7
8
9
10
11
12

显式锁(explicit lock)
显式加的锁, 在 show engine innoDB status 中能够看到、会在内存中产生对象、占用内存
eg. select ... for update, select ... lock in share mode ....

隐式锁(implicit lock)
是在索引中对记录逻辑的加锁、但实际上不产生锁对象、不占用内存空间
eg. insert into xx values(xx)
      update xx set t=t+1 where id=1;

implicit lock -> explicit lock
eg. 只有当implicit lock 产生冲突的时候、会自动转换成 explicit lock、降低锁开销
eg. A会话插入记录10、本身会加上 implicit lock、但是如果B 会话更新10这条记录、就会转换为 explicit lock

metadata lock

1
2

是server层实现的锁、与引擎无关
执行select时、若有ddl语句、会被阻塞、因为 select 会加上 metadata lock、防止元数据在访问过程中被修改

锁迁移

1
2

锁迁移、又叫锁继承
A锁住的记录是一条已经被标记为删除的记录、但是还没有被puge、然后这条被标记为删除的记录、被purge掉了、上边的锁就会给了下边一条记录、称为锁迁移

锁升级

1

一条全表更新的语句、db可能会对所有记录加锁、可能造成锁的开销很大、升级为页锁、或者表锁(mysql无锁升级)

锁分裂

1
2
3
4

1. innodb 实现的加锁、其实是在页上边做的、没办法直接对记录加锁
2. 一个页被读取到内存、会产生锁对象、锁对象里会有位图信息记录哪些heap no被锁住、heapno 表示的就是堆的序列号、可以认为就是定位到某一条记录
3. insert的时候、可能会产生页分裂
4. 若页分裂、原来对页上边的加锁位图信息也就变了、为了保持这种变化和锁信息、锁对象也会分裂、继续维护分裂后页的锁信息

锁合并

1

参考锁分裂

latch vs lock

1
2

latch: mutex、rw-lock、临界资源用完就释放、不支持死锁检测、应用程序维护 非db锁
lock: 事务结束后释放、支持死锁检测、db锁

mysql锁

根据加锁的范围、MySQL里边的锁可以分为全局锁、表级锁和行锁三类

全局锁

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

全局锁: 就是要对整个db实例加锁. mysql提供了一个全局锁支持: 
flush tables with read lock;(FTWRL)
此时: update、ddl、dml语句会被阻塞

典型场景: 全库逻辑备份

但是让整个db处于只读状态、比较危险:
若是操作主库、则在整个备份期间、都不能执行更新】整个业务基本上处于停滞状态
若操作从库、则备份期间从库不能执行主库同步过来的binlog、会导致主从延迟

那么、如果实现不影响业务的备份呢 ？

1. innodb引擎可以使用一致性读
   mysql自带备份工具mysqldump使用参数 --single-transaction 时、mysql就好启动一个事务、来确保拿到一致性视图

2. 为何不选择 set global readonly=true ?
   1) 在某些系统中、readonly可能被用来做其它逻辑、比如用来判断db是主还是备库 ？
   2) 在异常处理上的差异
      若执行FTWRL之后、mysql异常断开、mysql会自动释放这个全局锁、整个库可以回到正常更新的状态
      设置readonly、若client异常、则db会一直保持readonly状态、会导致整个库长时间不可写、风险较高
   3) MDL 在slave库上对super权限无效

表级锁

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

mysql的表级锁有2种: mysql表锁 和 元数据锁(meta data lock, MDL)

表锁的语法是: lock tables .. read/write. 与FTWRL类似、使用unlock tables主动释放锁、也可以在client断开时自动释放
既限制别的线程、也限制本线程
eg. 某个线程A中执行 lock tables t1 read, t2 write; 
则其它线程写t1, 读写t2 都会被阻塞、同时、线程A在执行unlock tables之前、也只能执行读t1, 读写t2的操作
但innodb可以支持行锁、一般就不使用 lock tables了、毕竟影响还是很大的

元数据锁:
不需要显式使用、在访问一个表的时候会被自动加上、MDL的作用是 保证读写的正确性
eg. 正在遍历某表的数据、在执行期间另外一个线程对这个表的结构做了变更、减少了一列、查询结果就会有问题

在mysql5.5的版本中引入了MDL、当对表curd操作时、会加MDL读锁、对表结构变更时、会加MDL写锁

* 读锁之间不互斥、可以同时对一张表增删改查
* 写锁、读写锁之间互斥、用来保证表结构变更操作的安全性
so. 两个线程同时对一个表做结构变更时、其中一个要等另外一个执行完才开始执行

案例分析

case: 给一个小表加字段、却导致db挂掉

1
2
3
4

note: 给表加字段或者修改字段或者加索引、都会导致全表扫描数据
在对大表操作时、都会特别小心、避免对线上造成影响、小表一般认为很快结束、会比较大意、其实、小表操作不当也会造成db挂掉

实验环境: mysql5.6 t是小表

可以看到: session A先启动、这时会对t加MDL读锁、session B操作不被影响
session C需要MDL写锁、blocked、此时db表现为 不可读写
若: 此时db的写十分频繁、且client有重试机制、超时后起一个新的session、这些session很快就会把连接打满

Q: 那么如何安全的给表加字段呢 ？

1
2
3
4
5
6
7
8
9
10
11
12
13

首先要解决长事务、事务不提交、就会一直占着MDL锁. 在mysql的information_schema库的innodb_trx表中、可以看到当前
正在执行的事务、若要做ddl变更的表刚好有长事务在执行、要考虑先暂停DDL或者kill掉事务

若要变更的是热点表、请求频繁、虽然数据量不大、但请求特别频繁、而不得不加字段、如何做 ?

此时kill未必管用、因为新的请求马上就进来了、此时比较理想的机制是: 
在alter table语句里设定等待时间、若在这个等待时间里能拿到MDL写锁最好、拿不到也不要阻塞后边的业务、先放弃
之后再重复

语法: 
alter table table nowait add column...
alter table table wait n add column...

查看锁表情况

show status like ‘table%’;

1
2

table_locks_immediate 指能够立即获取表级锁的次数
table_locks_waited 指的是不能立即获取表级锁而需要等待的次数、num越大、锁等待越多、有锁争用的情况

查看正在被锁定的表

show open tables where in_use>0;

锁涉及表说明

1
2
3
4

information_shcema库
innodb_trx 当前innodb内核中的活跃事务
innodb_locks 当前状态下产生的innodb锁、仅在有锁等待时打印
innodb_lock_waits 当前状态产生的innodb锁等待 仅在有锁等待时打印

innodb_trx表结构说明

1
2
3
4
5
6
7
8
9
10

字段名                 说明

trx_id innodb          存储引擎内部唯一的事物ID 
trx_state              当前事务状态（running和lock wait两种状态） 
trx_started            事务的开始时间 
trx_requested_lock_id  等待事务的锁ID，如trx_state的状态为Lock wait，那么该值带表当前事物等待之前事物占用资源的ID，若trx_state不是Lock wait 则该值为NULL 
trx_wait_started       事务等待的开始时间 
trx_weight             事务的权重，在innodb存储引擎中，当发生死锁需要回滚的时，innodb存储引擎会选择该值最小的进行回滚 
trx_mysql_thread_id     mysql中的线程id, 即show processlist显示的结果 
trx_query               事务运行的SQL语句

innodb_locks表结构说明

1
2
3
4
5
6
7
8
9
10
11
12

字段名       说明

lock_id      锁的ID 
lock_trx_id  事务的ID 
lock_mode    锁的模式（S锁与X锁两种模式） 
lock_type    锁的类型 表锁还是行锁（RECORD） 
lock_table   要加锁的表 
lock_index   锁住的索引 
lock_space   锁住对象的space id 
lock_page    事务锁定页的数量，若是表锁则该值为NULL 
lock_rec     事务锁定行的数量，若是表锁则该值为NULL 
lock_data    事务锁定记录主键值，若是表锁则该值为NULL（此选项不可信)

innodb_lock_waits表结构说明

1
2
3
4
5
6

字段名             说明 

requesting_trx_id  申请锁资源的事物ID 
requested_lock_id  申请的锁的ID 
blocking_trx_id    阻塞其他事物的事物ID 
blocking_lock_id   阻塞其他锁的锁ID

加锁的原则

1
2
3
4
5

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