Mysql生产环境索引失效(记录帖)

Mysql生产环境索引失效(记录帖)

背景：

         生产上有一台任务机器，是用来跑支付宝微信交易结算的任务，每个月1-10号任务执行时间正常，10号开始发现任务异常的变慢。16号左右执行时间恢复正常。

         一开始，分析代码，拿到感觉异常耗时sql（感觉的不对，以为是group by的某条语句结果最后发现不是它。。。），本地测试库执行EXPLAIN分析，sql正常，毫秒级别。以为是环境问题，由于没有空闲时间，放弃。。。结果本月又出现了这个情况，受不了。。。开始仔细排查。

 

定位原因：

         分析任务执行log，定位了两条update sql，同一张表。拿到mysql主库query log，定位sql，2条sql每条执行耗时 7s左右，我的天。。。

 

生产数据基础：

         大概说一下，具体细节就不描述了。数据是为门店交易的结算金额，表结构目前就两个索引，主键and交易日期trade_date，trade_date字段是date类型，全表没有null值，索引类型normal，索引方式btree。分表规则为月份，每个月大概1000W左右数据。每个trade_date交易日期大概在30W。生产mysql5.6，本地测试5.7。

 

一直走在优化的路上：

         sql已拿到，生产从库执行EXPLAIN update sql走起。

         The MySQL server is running with the --read-only option so it cannot execute this statement

Readonly，好吧，权限限制了我的想象。。。改为select试一下，正常索引。不放心本地试一下吧。

         本地测试库函数造了300W数据，试了一下。

EXPLAIN UPDATE ks_settlement_detail_201906 set ...... WHERE ...... and trade_date = '2019-06-09 00:00:00';

尽管不是很完美，但也不至于7s吧（图为后补的之前rows在6W左右）。执行本地1.5s。生产机器应该是1s内啊，神马情况。。。

ps：上次写到这里就睡觉了，今天想维护一下这个。不想半途而废。

         找到运维大神，生产执行EXPLAIN UPDATE sql。发现生产执行的sql时间是在7S左右，没有使用到了一点索引。

解决方案：

最后的解决方案就是使用了强制索引，force index(trade_date)，把时间控制在了1S内。

最后倔强的想法：

因为我是半路接手的这个项目，想现在推翻整个设计属实挺难。

1. 生产环境的数据在日期维度比较分散，测试覆盖不全，导致测试索引有效，生产索引失效。
2. 把生产数据导入到测试环境，也测试不出来索引失效的结果。生产mysql版本和测试mysql版本不一致，以后开发要尽量统一环境。因为负责的每个项目的mysql版本不一致，所以自己负责的项目所有开发环境的版本要尽量一致。
3. 从设计角度要避免sql的批处理操作。例如：group by，sum，批量update等。要使用程序计算，达到可扩展。先期数据量少你会觉得没什么，到了后期数据量增多的时候，可扩展性极低。会让自己优化的直挠头，必须要改结构了。