那些年踩过的 MySQL 索引的坑

柔情只为你懂 2022-11-05 08:37 276阅读 0赞

# 坑 1：查询没走索引导致的 MySql lock wait timeout exceeded #

需求：修复表数据，修改会员订单表 p\_member\_order 的 pay\_time，同步修改会员子订单表 p\_member\_order\_item 的 pay\_time，这两个操作需要加事务。

p\_member\_order 的数量量为 1883 1645，p\_member\_order\_item 的数据量为 3252 7087

实现类：

@Component
    @Scope("prototype")
    public class FixOrderPayTimeToolsAction extends AdminAction {
    
        private static Logger logger = LoggerFactory.getLogger(FixOrderPayTimeToolsAction.class);
    
        /**
         * 0 不更改数据，1更改数据
         */
        private int update = 0;
        private Long minId;
        private Long maxId;
        private Integer maxPoolSize = 3;
    
        @Autowired
        private IMemberOrderService memberOrderService;
    
        @Autowired
        private ILrOnlineOrderDao lrOnlineOrderDao;
    
        @Autowired
        private ILrOnlineColdOrderDao lrOnlineColdOrderDao;
    
        private ThreadPoolExecutor executorService = new ThreadPoolExecutor(
    					1, maxPoolSize, 0, TimeUnit.MILLISECONDS,
    					new ArrayBlockingQueue<>(2048), new ThreadPoolExecutor.CallerRunsPolicy());
    
        @Override
        public String execute() {
    
            logger.info("修复 - 开始 {}", update == 1 ? "修复数据":"不修复");
    
            if (minId == null) {
                minId = 0L;
            }
            if (maxId == null) {
                maxId = 19205834L;
            }
            long startId = minId;
    
            while (true){
                logger.info("startId:{}", startId);
                // 串行获取
                List<MemberOrderBean> list = Collections.synchronizedList(memberOrderService.getListByIdFlow(startId, maxId, 5000));
                if (CollectionUtils.isEmpty(list)) {
                    logger.info("没有数据了，修复完成");
                    break;
                }
                startId = list.get(list.size() - 1).getId();
    
                logger.info("startId=" + startId + ", endId=" + maxId + "is running [ pool size = " + executorService.getPoolSize() +
                        ", active threads = " + executorService.getActiveCount() +
                        ", queued tasks = " + executorService.getQueue().size() +
                        ", completed tasks = " + executorService.getCompletedTaskCount()  +
                        "]");
                // 并行处理
                for (int i = 0; i < list.size(); i++) {
                    int finalI = i;
                    MemberOrderBean orderBean = list.get(finalI);
                    executorService.execute(()-> {
                        try{
                            fixData(orderBean);
                        }catch (Exception e){
                            logger.info("fix data fail" + e.getMessage());
                        }
                    });
                }
            }
            logger.info("修复 - 结束");
            return SUCCESS;
        }
    
        private void fixData(MemberOrderBean orderBean) {
            LrOrderBean successMemberOrder;
            // 从冷库获取
            successMemberOrder = LrOnlineColdOrderDao.getSuccessMemberOrder(orderBean.getUserId(), orderBean.getOrderId());
            if (successMemberOrder == null) {
                // 从热库获取
                successMemberOrder = LrOnlineOrderDao.getSuccessMemberOrder(orderBean.getUserId(), orderBean.getOrderId());
            }
            if (successMemberOrder != null) {
                if (!orderBean.getPayTime().equals(successMemberOrder.getNotifyTime())) {
                    logger.info("memberOrder id {} orderId {} userId{} 当前 payTime:{} 修改为：notifyTime:{}"
                            , orderBean.getId(), orderBean.getOrderId(), orderBean.getUserId(), DateUtils.getDayStr(orderBean.getPayTime(), DateUtils.DATETIME)
                            , DateUtils.getDayStr(successMemberOrder.getNotifyTime(), DateUtils.DATETIME));
    
                    if (update == 1) {
                        MemberOrderBean updateMemberOrderBean = new MemberOrderBean();
                        updateMemberOrderBean.setId(orderBean.getId());
                        updateMemberOrderBean.setUserId(orderBean.getUserId());
                        updateMemberOrderBean.setPayTime(successMemberOrder.getNotifyTime());
                        memberOrderService.updateOrderAndItem(updateMemberOrderBean);
                    }
                }
            }
        }
    }
    
    @Service
    public class MemberOrderServiceImpl implements IMemberOrderService {
        @Override
        @Transactional(value = DataSourceConsts.AUDIOBOOK_TX_MANAGER_NAME, rollbackFor = Throwable.class)
        public int updateOrderAndItem(MemberOrderBean updateBean) {
            // 修改会员订单表
            memberOrderManager.updatePayTimeById(updateBean);
            // 修改会员子订单表
            MemberOrderItemBean updateItemBean = new MemberOrderItemBean();
            updateItemBean.setOrderId(updateBean.getId());
            updateItemBean.setPayTime(updateBean.getPayTime());
            updateItemBean.setUserId(updateBean.getUserId());
            memberOrderItemManager.updatePayTimeByOrderId(updateItemBean);
    
            return 0;
        }
    }

修改 sql：

update p_member_order set pay_time=#{payTime} where id=#{id}
    
    update p_member_order_item set update_time=now(), pay_time=#{payTime} where order_id = #{orderId}

开跑，然后报错

![20210304184547804.png][]

*  `Lock wait timeout exceeded`：并发环境下，后提交的事务等待前面处理的事务释放锁，但是在等待的时候超过了mysql的锁等待时间，就会引发这个异常

![2021030419015220.png][]

通过 [arthas 工具定位][arthas]，发现慢在了更改方法 updateOrderAndItem () ，等待了 55 s

检查 sql，发现 sql 没有走索引字段 user\_id，导致 update 订单子表的时间过长，阻塞了后面的事务

// 原来的 sql
    update p_member_order_item set update_time=now(), pay_time=#{payTime} where order_id = #{orderId}
    
    // 优化后的 sql
    update p_member_order_item set update_time=now(), pay_time=#{payTime} where 
    user_id = #{userId}
    order_id = #{orderId}

如果在事务中执行了一条没有索引条件的查询，引发全表扫描，把行级锁上升为全表记录锁定（等价于表级锁），多个这样的事务执行后，就很容易产生死锁和阻塞，最终应用系统会越来越慢，发

生阻塞或死锁。

**反思：操作数据量大的表时，先看该表哪些字段设置了索引，每条 sql 尽量保证索引字段值不为空，当然，纯靠自觉是很虚的，还有什么手段可以保证？**

# 坑 2：查询没命中索引导致 CPU 使用率飙升，被老大拉去教育 #

[啥？我写的一条SQL让公司网站瘫痪了...][SQL_...]

一张新上线的表，起始数据为 0，往后每日递增 60 万左右，定时任务每日定时查询一次该表，里面涉及按用户段并发查询，表数据量为 400万左右的时候，DBA 在群里发出警告

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5ZHlkMTEw_size_16_color_FFFFFF_t_70][]

吓得我赶紧排查，发现查询 sql 没有命中索引字段 int\_date，瞬间更迷惑了，因为 DAO 层每个方法都传了 int\_date 参数值，再去查看 xml ，发现

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5ZHlkMTEw_size_16_color_FFFFFF_t_70 1][]

![20210310235443276.png][]

这么明显的 bug 测试环境居然没被发现！！！因为是新表，而测试环境造的数据比较少，导致 bug 躲过一劫。

[20210304184547804.png]: /images/20221023/68535e57f01e424386eed62cb7ee71ab.png
[2021030419015220.png]: /images/20221023/d627da83d4254c9f9fc74c78e989a25d.png
[arthas]: https://arthas.aliyun.com/doc/quick-start.html
[SQL_...]: https://database.51cto.com/art/202010/629661.htm
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5ZHlkMTEw_size_16_color_FFFFFF_t_70]: /images/20221023/d954ab446df7476a9d043714e6df8cff.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5ZHlkMTEw_size_16_color_FFFFFF_t_70 1]: /images/20221023/8a57af25ac70401585ffaab098d4d694.png
[20210310235443276.png]: /images/20221023/3b901040f6a444f1a1c401d8ea0f1e69.png