MySQL实战：问题排查与监控

常见问题

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

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

查看索引基数

show index from table_name
1

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

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

analyze table
1

count 语句慢，如何解决？

count(非索引字段)：无法使用覆盖索引，最慢
count(索引字段)：可以使用覆盖索引但依然要取出数据判空
count(1)：不需要取出数据，但需要判断1是否为null
count(*)：经过专门优化，不需要判空，理论最快

获取不到数据库连接

事务提交慢导致获取不到数据库连接

查看超时释放

show variables where variable_name like '%timeout'
1

查看线程

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

查看最大连接数

show variables like '%max_connections%'
1

修改最大连接数

set GLOBAL max_connections = 200;
1

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

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

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

数据库服务器负载过大

CPU使用率不断升高，QPS不断降低

避免大事务

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

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

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

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

查看长事务：information_schema库innodb_trx表
查看锁：information_schema库innodb_locks表
查看阻塞的事务：information_schema库innodb_lock_waits表

监控指标

性能类指标

功能类指标

QPS

各种请求的数量

show global status like 'com%'
1

各种请求的和

show global status like 'queries'
1

qps = (queries2- queries1) / 时间间隔

TPS

show global status where variable_name in ('con_insert', 'com_delete', 'com_update')
1

tc = com_insert + com_delete + com_update

tps = (tc2 - tc1) / (time2 - time1)

数据库并发数

show global status like 'threads_running'
1

数据库连接数

show global status like 'threads_connected'
1

报警阈值：threads_connected / max_connections > 0.8

Innodb缓存命中率

show global status like 'innodb_buffer_pool_read%'
1

(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / （Innodb_buffer_pool_read_requests） *100%

Innodb_buffer_pool_read_requests：从缓存池中读取的次数
Innodb_buffer_pool_reads：从物理磁盘读取的次数

数据库可用性

周期性的连接数据库并执行select @@version

监控慢查询

通过慢查询日志监控

死锁

查看最近一次死锁信息

SHOW ENGINE INNODB STATUS
1

使用pt-deadlock-logger工具