《MySQL排查指南》简记

排查指南

1 记录查询

set global general_log='on';
set global log_output='table'; 
select argument.event_time from mysql.general_log where thread_id = this_thread_id order by event_time;# 改查询将返回锁定线程执行的查询列表 

或是file --->select * from mysql.grneral_log\G
` 常用存入文件的方式，方便监控查看整体执行的动作

2 select 优化，索引会降低数据更新的效率(特别是insert)，要从整个业务的性能出发

1.将查询拆成小段
2.explain +  show warnings;

`非唯一的行不会产生eq_ref或更好的类型
`列中重复值很多时，不适合建立索引`

不要只看explain,要看实际执行的时间。

以下仅测试使用，因为要相信优化器，可能和高版本的优化冲突，反而用不上新特性
ignore index #忽略索引
force index #强制使用某索引

#在优化任何单个查询的时候，请时刻注意整个业务的性能

3 perror + mysql error code

perror 150

#http://bugs.mysql.com

4 主从数据不一致(复制不检查数据一致性)

`推荐基于row或者重写存储过程，使其不依赖已经存在的临时表`
1.主节点有逻辑错误
2.在向临时表中插入数据完成复制并清空临时表的事件之后，且在调用查询并向主表插入数据的存储过程之前，重启服务器。因此，从服务器仅是重新创建一个空的临时表并且没有插入任何数据。(主从动作不一致)

5 慢查询

1.调优查询本身
2.调优表(加索引)
3.调优服务器

6 构建测试数据

#开启GTID之后不支持
insert into t1 select * from t2;#可限制插入字段，多执行几次，数据量就有了

7 查询Handler_%

通过在查询计划前后查询Handler_%的状态可以检查是否使用了索引：
show status like 'Handler_%'; #为当前会话，在测试前请使用flush status 重置这些变量
root@localhost[(none)]> show status like 'Handler_%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 0     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 0     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 0     |
| Handler_read_last          | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
Handler_read_rnd_next 值过高，代表从datafile中读取下一个值的频率就越高。一般代表全表扫描，影响性能。
Handler_read_key 表示读取索引的请求数目，相对于Handler_read_rnd_next差值不应该过多。过多说明没有使用到索引。
Handler_commit 事物提交次数 和Handler_read_first 读索引中第一项的次数(值为1，表示全索引扫描，只读取了一次)

8 日志

mysqld got signal 11;
表明mysql向操作系统申请资源(访问文件，或是内存)后终止了。代表分段错误。

9 表损坏

MyiSAM  .frm   .myd  .myi

关键字：repair或crashed 表示表损坏

1.
check table tablename;  #验证是否损坏了
repair table tablename;  #修复

极端情况，repair table extended 执行速度比 repair table 慢，但是可以修复99%的问题。
更极端的情况，repair table use_frm tablename;  他会不相信索引文件的信息，通过.frm文件重建索引。

2.myisamchk ，服务关闭之后修复，或是flush tables+lock table table_name write;之后执行。最好不要访问表。

3.mysqlcheck-->check+repair 支持 --all-databases,可远程


InnoDB 
在共享表空间中存储数据和索引。就算是使用独立表空间，表定义仍然在共享表空间中。

1. 
启动时，会自动修复。如果数据损坏的很严重，需要人为。

2.--innodb_force_recovery 启动项。0-6,0为不强制修复，1是最低级别，6最高。推荐从1开始增加，尝试启动服务。
启动成功之后，通过select into outfile 将表转储到文件中，然后使用drop和create 命令重新创建表。
最后使用innodb_force_recovery=0 重新启动服务，加载转储的数据文件。
循环以上，直到修复所有表。

10 并发

表锁，行锁，页锁，元数据锁。 其中页锁存在于BDB存储引擎中。
`元数据是DDL语句的更改信息，create drop,alter，cruncate等`
lock table .. read
lock table .. write

1.show processlist;

表锁，像myisam 会阻塞其他连接对相同表的更新

行锁，innodb 会阻塞其他连接对相同行的更新(innodb_lock_wait_timeout默认50s)
update t set a=sleep(200) where; #带有等待时间的更新语句

`where条件是主键或是唯一键可以减少锁，增加并发`

`监控器 show engine innodb status\G`

#I_S库的数据都是内存信息，当前信息。
`I_S.processlist表` where time >50

#以下两个表在高版本中将移除。8.0 ，5.7还有。
`I_S.innodb_locks`  是否有锁等待
`I_S.innodb_lock_waits` 保存已获取锁和等待锁的信息
requesting_trx_id  被阻塞的事物ID
blocking_trx_id   持有锁的事物ID

requested_lock_id 被请求的锁信息
blocking_lock_id  持有锁的信息

`I_S.innodb_trx` 可以帮我们找到需要kill 的 trx_mysql_thread_id,可以通过show processlist再次确认。一般是kill持有锁的ID

11 死锁

死锁是指当两个或是多个竞争事务彼此等待对方释放锁，从而导致事务永远无法终止的情况。
`在行锁级别，死锁是无法100%避免的`

死锁探测器会自动回滚一个事务，成功执行另个事务。

show engine innodb status\G 可以看到最近的一次死锁，可能会被覆盖
waiting for this lock to be granted 相关部分，表示事务在等待哪个锁
holds the locks 相关部分，表示阻塞事务的锁的信息

一般来说，DDL语句和事务相关的语句和管理语句会产生隐式提交。#在事务中隐式提交会提交执行之前的语句，如create table 会提交insert，语句顺序，begin,insert,create table,rollback;

12 元数据锁

metadata lock  又称DML锁
为保证数据一致性，在其他事务使用表的情况下，对表的DDL操作应该阻塞。MySQL版本>5.5.3

`在事务开始的时候，他会获取所有需要使用的表上的元数据锁，并在事务结束后释放。其他需要修改这些表定义的线程都需要等待事务结束`

lock_wait_timeout 默认值是1年，可以设置为1s.更快的返回错误提示。

13 hang

1.show processlist; 看Time ,state,info
2.show engine innodb status\G  看transactions部分
3.
`P_S.mutex_instances`  #该表会列出自服务器启动以来所有的冲突。
desc mutex_instances;
+-----------------------+---------------------+------+-----+---------+-------+
| Field                 | Type                | Null | Key | Default | Extra |
+-----------------------+---------------------+------+-----+---------+-------+
| NAME                  | varchar(128)        | NO   |     | NULL    |       |
| OBJECT_INSTANCE_BEGIN | bigint(20) unsigned | NO   |     | NULL    |       |
| LOCKED_BY_THREAD_ID   | bigint(20) unsigned | YES  |     | NULL    |       |
+-----------------------+---------------------+------+-----+---------+-------+

`select * from mutex_instances where LOCKED_BY_THREAD_ID is not null\G

#要查出谁在等待这些冲突，可以查询events_waits_current表

select THREAD_ID,EVENT_NAME,SOURCE,TIMER_START,OBJECT_INSTANCE_BEGIN,OPERATION from events_waits_current where THREAD_ID in (select LOCKED_BY_THREAD_ID from mutex_instances where LOCKED_BY_THREAD_ID is not null)\G

注意THREAD_ID不是连接线程的id,不能kill

可以通过theads表找到连接线程的id，字段名为  PROCESSLIST_ID

14 复制

基于语句的复制，更容易造成主从数据不一致，如，对没有非唯一约束的表更新
推荐基于row的格式记录日志(日志中记录原始数据)

基于语句格式(日志中记录原始的sql)

事务只会在提交之后在日志中写入数据。

一个事务中，包含事务表和非事务表，回滚和崩溃时，也容易造成主从数据不一致。

15 sql_mode

strict_trans_tables ,严格模式

16 Max_allowed_packet

`限制服务器和客户端之间传递的单个数据包的字节数
`大表或是blob类型`

show variables like '%max_allowed%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 67108864   |

select repeat('a',67108865); #超过了阈值
+----------------------+
| repeat('a',67108865) |
+----------------------+
| NULL                 |
+----------------------+
1 row in set, 1 warning (0.00 sec)

show warnings;

| Warning | 1301 | Result of repeat() was larger than max_allowed_packet (67108864) - truncated |

17 secure_file_priv

限制load file 函数 load data 和select  into  outfile 语句只能使用指定目录

18 open_files_limit

允许打开的文件数，如果值过小，会出现无法建立新连接。和操作系统和用户设置有关ulimit -n,和启动脚本有关。 cat /proc/pid/limits 查看软硬连接可打开的文件数。

19 服务器启动时设置core选择来获得一个核心文件

#核心文件包含一个进程的内存映像，当进程意外终止时会创建它
gdb core.222

load data 和select into outfile 语句只能使用指定目录

## 18 open_files_limit

```shell
允许打开的文件数，如果值过小，会出现无法建立新连接。和操作系统和用户设置有关ulimit -n,和启动脚本有关。 cat /proc/pid/limits 查看软硬连接可打开的文件数。

19 服务器启动时设置core选择来获得一个核心文件

#核心文件包含一个进程的内存映像，当进程意外终止时会创建它
gdb core.222

–[美] Sveta Smirnova 著