MySQL实战：问题排查

事务提交很慢

说一个我遇到的一个有意思的问题，有一段时间线上的接口特别慢，但是我查日志发现sql执行的很快，难道是网络的问题？

为了确定是否是网络的问题，我就用拦截器看了一下接口的执行时间，发现耗时很长，考虑到方法加了事务，难道是事务提交很慢？

于是我用pt-query-digest统计了一下1分钟左右的慢日志，发现事务提交的次数很多，但是每次提交事务的平均时长是1.5s左右，果然是事务提交很慢。

好了，现在就要看为什么事物提交慢了？

首先想到的难道是redolog太小了？

因为当redolog写满的时候会将脏页刷回到磁盘，然后看了一下redolog的大小居然为48mb。dba觉得innodb_log_file_size设置的太小了，打算从48mb 打算改成1G，然后重启实例，我让他们监控了一下redo log的使用大小，排除了这个可能

MySQL的InnoDB 存储引擎使用一个指定大小的Redo log空间（一个环形的数据结构）。Redo log的空间通过innodb_log_file_size和innodb_log_files_in_group（默认2）参数来调节。将这俩参数相乘即可得到总的可用Redo log 空间。尽管技术上并不关心你是通过innodb_log_file_size还是innodb_log_files_in_group来调整Redo log空间，不过多数情况下还是通过innodb_log_file_size 来调节

难道是BufferPool设置的太小了？

BufferPool的大小为80个G，并且空闲缓存页还有很多，排除了这种可能

后来猜测难道是主从同步拖慢了事务提交

dba将主从同步方式从半同步复制改为异步复制解决了这个问题（下午2点并发比较大，所以事务提交慢在2点这个时间点比较明显）

主从同步方式

异步复制：MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从库上，如果此时，强行将从提升为主，可能导致新主上的数据不完整

半同步复制：是介于全同步复制与全异步复制之间的一种，主库只需要等待至少一个从库节点收到并且 Flush Binlog 到 Relay Log 文件即可，主库不需要等待所有从库给主库反馈。同时，这里只是一个收到的反馈，而不是已经完全完成并且提交的反馈，如此，节省了很多时间

全同步复制：指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响

索引失效

我在之前的文章分析过索引失效的场景和原因，有兴趣可以看看

select * from room_info where house_code = 12003;

有这样一个sql，house_code类型为varchar类型，结果传入的具体值为数字，导致索引失效

有更合适的索引不走，怎么办？

MySQL在选取索引时，会参考索引的基数，基数是MySQL估算的，反映这个字段有多少种取值，估算的策略为选取几个页算出取值的平均值，再乘以页数，即为基数

查看索引基数

show index from table_name

使用force index可以强制使用索引

重新统计索引信息，会重新计算索引的基数

analyze table

数据库连接池打满

当获取不到数据库连接的时候，我们可以从下面几个方面排查

1. 事务提交很慢，比如在应用中我们调用外部接口，外部接口迟迟没有响应，导致事务不能及时提交，释放连接
2. 有慢sql
3. 并发比较大，比如很多线程都对数据库的某一行就行更新，这样就会造成排队获取锁，排队的事务比较多时，就会造成数据库连接耗尽

查看超时释放

show variables where variable_name like '%timeout'

查看线程

-- 查看当前数据库运行的所有线程
show processlist
-- show processlist只能列出前100条，想看所有使用如下命令
show full processlist

查看最大连接数

show variables like '%max_connections%'

修改最大连接数

set GLOBAL max_connections = 200;

读写分离导致读不到最新的数据

应用层做了读写分离后，有可能会读取到旧数据，此时可以强制从主库查询数据。

说一个由于读写分离导致的生产问题，我们在service层有一个切面，根据方法名的前缀来决定走主库还是从库，有个方法名是以get开头，切面就将数据源设置为从库，结果里面有写入的操作，到了生产环境一直报错。为什么我们在测试环境没遇到这个问题？因为我们在测试环境主库和从库配置的是同一个数据源

SQL不满足最左前缀原则

避免大事务

一般要在访问量比较低的时候执行批量修改数据

我们原来在生产遇到一个挺有意思的事情，批量更改数据，发出了大量的binlog，然后另外一个应用监听binlog同步数据，产生的大量binlog导致数据同步很慢，影响了正常的业务流程

数据修改尽量放在事务后部，降低锁时间

alter table 之前，查看是否有长事务还未提交

查看长事务：information_schema库innodb_trx表

查看锁：information_schema库innodb_locks表

查看阻塞的事务：information_schema库innodb_lock_waits表

参考博客

[1]https://zhuanlan.zhihu.com/p/468852206
[2]https://www.51cto.com/article/775516.html