索引与缓存的坑

Jun 9, 2019 · 2 min read · mysql cache

以为是一次小改动，欢喜自测上线，没多久500扑面而来。上机器，看日志，数据库连接报错。本地客户端连连看，返回如下：

1ERROR 1203 (42000): User xxx already has more than 'max_user_connections' active connections

当前用户的活跃连接超过了max_user_connections的设置，根据文档描述，该设置控制单个MySQL用户能够达到的并发连接数。超过此设置，MySQL连接失败，所有经过数据层的接口都会报500。

正常情况，这个项目典型的读多写少，读请求占97%以上，缓存早已稳稳加上，绝大多数读请求不会走到数据层，所以一个主库两个从库的连接数比例大概是：

1S:M:S = 1:2:1

在QPS为300到1000的区间时，主库连接数维持在55左右，而两个从库的连接数均维持在20左右，可见大多数请求已被缓存拦下。但是在出现上面的报错时，主库连接数依然维持在55左右，而两个从库连接数则均达到1200左右，基本所有的压力都集中在了从库，典型的缓存雪崩现象。为什么雪崩？别人家是缓存集中过期，而我们家业务代码修改了缓存key，导致缓存在“逻辑”上全部过期，雪崩。

如果只是数据层的机器出现访问压力，可能还扛得住。但读请求基本全在操作一张表，一千万左右记录，三列上建了唯一索引，例如

1A+B+C

以前的查询条件：

1SELECT * FROM t_name WHERE A=a AND B=b AND C=c;

命中索引，但现在应业务要求要去掉A=a，于是：

1SELECT * FROM t_name WHERE B=b AND C=c;

不符合最左前缀策略，索引未命中，所有查询全表扫描，大量查询时间在60秒到140秒之间。

四台机器的php-fpm进程数分别为500、500、900、450，而两个从库连接数2400左右，和所有机器进程数相近。可见，这种并发下所有机器的php-fpm进程都在工作，每台机器都达到了pm.max_children的设置。

怎么办？先回滚。

问题出现的逻辑：

1缓存key被修改 -> 读库 -> 索引没命中 -> 数据库连接数过多 -> 数据库连接失败 -> 500

这种并发，即使雪崩，只要索引命中，读出的数据入缓存，等旧缓存失效，一切照旧。

调整索引列顺序：

1B+C+A

explain看看：

1+----+-------------+---------+------+----------------+----------------+---------+-------------+------+-----------------------+
2| id | select_type | table   | type | possible_keys  | key            | key_len | ref         | rows | Extra                 |
3+----+-------------+---------+------+----------------+----------------+---------+-------------+------+-----------------------+
4|  1 | SIMPLE      | xxxxxxx | ref  | unq_xxx_xxx_xx | unq_xxx_xxx_xx | 304     | const,const |    1 | Using index condition |
5+----+-------------+---------+------+----------------+----------------+---------+-------------+------+-----------------------+
61 row in set (0.04 sec)

总结：

再小的修改，都要考虑下性能、可用性、扩展性、安全性
想清楚开发、测试、线上环境的数量差异对程序性能的影响
上线前最好先预发布测试
访问频率高的缓存key修改后，先预热，避免数据库压力陡增
大数据量表查询一定要explain确认缓存是否命中

备注：

第三台机器内存

1$ free -m
2               total        used        free      shared  buff/cache   available
3Mem:          15885        4016        6163         837        5706       10531
4Swap:          2047           0        2047

平均每个php-fpm进程占内存16M左右

 1ps -eo pid,ppid,command,user,%mem | grep php-fpm
 222324     1 php-fpm: master process (/o root      0.0
 322325 22324 php-fpm: pool webid         webid     0.1
 422326 22324 php-fpm: pool webid         webid     0.1
 522327 22324 php-fpm: pool webid         webid     0.1
 622328 22324 php-fpm: pool webid         webid     0.1
 722329 22324 php-fpm: pool webid         webid     0.1
 822330 22324 php-fpm: pool webid         webid     0.1
 922331 22324 php-fpm: pool webid         webid     0.1
1022332 22324 php-fpm: pool webid         webid     0.1
1122333 22324 php-fpm: pool webid         webid     0.1
1222334 22324 php-fpm: pool webid         webid     0.1
1322335 22324 php-fpm: pool webid         webid     0.1
1422336 22324 php-fpm: pool webid         webid     0.1
1522337 22324 php-fpm: pool webid         webid     0.1
16...