MySQL (一) - 排错指南

MySQL (一) - 排错指南

1 基础知识

语法错误

(1) 使用反引号:

1
SELECT `id` FROM `t1` WHERE `accessible` = 1;

(2) 通过通用查询日志来记录你的每次查询:

1
2
3
4
5
6
# 临时打开通用查询日志
mysql> SET GLOBAL general_log='on';
# 将日志记录记录在表中
mysql> SET GLOBAL log_output='table';
# 查询通用日志记录表
mysql> SELECT * FROM mysql.general_log;

SELECT 返回错误的结果

(1) EXPLAIN EXTENDED 命令加 SHOW WARNINGS 命令:

1
2
mysql> EXPLAIN EXTENDED SELECT count(*) FROM items WHERE id IN (SELECT id FROM items_links);
mysql> SHOW WARNINGS;

EXPLAIN EXTENDED 之后,使用 SHOW WARNINGS 命令能够查看查询是如何优化与执行的

获取查询信息

(1) 使用 perror 获取错误码对应的错误字符串,注意是在 shell 中获取的:

1
$perror 150

慢查询

通过 EXPLAIN 信息调优查询

我们要重点观察两个数据:

  • type: 连接的执行方式
  • rows: 查询执行过程中检查的行数的估计

type 的类型:

  • type: ALL: 没有索引,读取所有行
  • type: index: 只有索引树被扫描
  • type: ref: 只使用了索引的最左前缀,或者索引不是 UNIQUEPRIMARY KEY
  • type: eq_ref: 唯一索引,非唯一的行永远不会产生 eq_ref 或者更好的类型
  • type: const: 确定最多只会有一行匹配

避免在生产环境中使用 IGNORE INDEXFORCE INDEX

存储引擎问题

  • MyISAM: table_name.frm: 包含表的结构,table_name.MYD 包含存储数据,table_name.MYI 保存索引:
1
2
mysql> CHECK TABLE t2;
mysql> REPAIR TABLE t2;

也可以使用工具 mysqlcheck 命令来检查。

2 并发问题

InnoDB 引擎使用简写的 S 代表读锁/共享锁,用 X 代表写锁/排它锁

2.1 表锁

当访问表并且该表所使用的存储引擎支持表锁的时候,即会产生表锁,比如 MyISAM 引擎。当你怀疑是并发线程影响了查询的时候,使用 SHOW PROCESSLIST 命令查看状态:

1
2
mysql> SHOW PROCESSLIST;
mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;

然后重点观察 statusinfo 这两行的数据

2.2 行锁

InnoDB 是当前使用行锁的主要的存储引擎。为了确定 InnoDB 中一个请求是否阻塞,可以执行 SHOW ENGINE INNODB STATUS 命令,该命令是 InnoDB 监控器机制的一部分:

1
mysql> SHOW ENGINE INNODB STATUS;

2.3 事务

未提交的事务会持有锁,哪怕该影响到特定行的查询在数小时前已经结束了。当执行多语句事务的时候,应该尽可能及时提交事务,哪怕事务不会修改任何航,也不要在事务最后的更新都已经完成的情况下仍不提交事务。

InnoDB 引擎有内部的死锁探测器,当其发现有死锁的时候,它会回滚其中的一个事务,并会报告一个立即可见的错误。SHOW ENGINE INNODB STATUS 命令也可以用来检查死锁的一些信息

2.4 元数据锁

为了确保数据一致性,在有其他事务使用表的情况下,对该表的 DDL 操作应该阻塞

2.5 并发如何影响性能

如果一个查询突然开始执行缓慢,第一步应该确认它是否是合理优化过的,最简单的确认方式就是在一个隔离的、单线程的环境里去执行该查询。如果该查询仍然执行缓慢,那么它或者需要优化,或者最近大量更新操作导致索引统计数据过期了。如果一个查询在单线程环境中很快完成但是在多线程环境中执行缓慢,这基本可以确定你遇到了并发问题

要打开 InnoDB 监控器,需要在任何数据库中创建一个叫做 innodb_monitor 的表:

1
2
$mysql test -A
mysql> CREATE TABLE innodb_monitor(f1 INT) ENGINE=InnoDB;

启动 MySQL 命令客户端时候的 -A 选项在你尝试调试与并发相关的问题时非常有用。正常情况下,客户端会请求可用表列表,然后,客户端会被其他连接持有的锁阻塞,这回阻塞客户端调试,选项 -A 会组织表列表请求。

2.6 复制和并发

当排查复制问题的时候,需要记住主服务器总是多线程的,而从服务器在单个线程中执行所有更新。每个事务都只会在其提交的时候向二进制日志中写入数据

3 配置选项对服务器的影响

mysqladmin 实用工具有一个 ping 命令,它能够报告 MySQL 服务器当前的状态是运行还是停止:

1
mysqladmin -hlocalhost -p3306 ping
1
2
3
SET [SESSION] var_name=value
SET GLOBAL var_name=value
SHOW [SESSION|GLOBAL| VARIABLES LIKE '变量名称'

4 MySQL 环境

Linux/UNIX 上可以通过 vmstat 来查看 mysqld 是否有大量磁盘交换。使用 sysbench 来测试数据库和操作系统性能:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# install sysbench
sudo apt install sysbench
man sysbench

# CPU benchmark
sysbench --test=cpu --cpu-max-prime=20000 run

# 1. Create a 150G file
sysbench --test=fileio --file-total-size=150G prepare
# 2. File benchmark
sysbench --test=fileio --file-total-size=150G --file-test-mode=rndrw --init-rng=on --max-time=300 --max-requests=0 run
# 3. Clean file
sysbench --test=fileio --file-total-size=150G cleanup

# 1. Create a `test` table
sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-password=yourrootsqlpassword prepare
# 2. MySQL benchmark
sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-password=yourrootsqlpassword --max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 run
# 3. Clean `test` table
sysbench --test=oltp --mysql-db=test --mysql-user=root --mysql-password=yourrootsqlpassword cleanup

一个小例子说明硬件延迟是如何影响一条普通的 UPDATE 查询的,我们将自动提交打开,并同是打开二进制日志文件:

1
UPDATE test_rrbs SET f1 = md5(id*2) WHERE id BETWEEn 200000 AND 300000;

这个简单的查询会在下面的情况下遭遇延迟:

  1. 客户端发送命令到服务端经历半个 RTT
  2. 执行 UPDATEWHERE 子句,mysqld 读磁盘
  3. 由于自动提交开启 mysqld 会对此事务做一个 fsync 调用
  4. 为写入二进制日志文件 mysqld 做一次 fsync 调用
  5. 为提交改变 mysqld 做一次 fsync 调用
  6. 客户端接收到来自服务器的结果,这是 RTT 的另一半

5 复制故障诊断

查看复制状态信息的有用命令,从库上运行:

1
mysql> SHOW SLAVE STATUS;

mysqlbinlog 将二进制日志文件转为可视的格式

6 问题排查技术与工具

慢查询日志: 记录运行时间超过 long_query_time 秒的查询,该变量的默认值是 10

推荐书籍:

  • 《Expert MySQL》
  • 《Understanding MySQL Internals》
  • 《MySQL 5.1 Plugin Development》
  • 《High Performance MySQL》
  • 《MySQL High Availability》

收集信息的工具:

7 最佳实践

参考

推荐文章