Mysql Block Nested-Loop查询导致cpu利用

问题现象

在客户的运营系统中，当客户执行某个条件的订单查询时，接口响应超时，同时服务器的cpu利用率高达100%

image.png

问题排查

问题sql

SELECT
    count( 0 ) 
FROM
    orders 
WHERE
    1 = 1 
    AND IS_PAYED = 2 
    AND ORDER_TYPE != 3 
    AND `ORDER_TYPE` = 1 
    AND `ORDER_NUM` IN ( SELECT ORDER_NUM FROM orders_bill WHERE OPERATION_ID = 37 );

备注

上述俩种表中的数据量分别是：4万 和七万

执行explain

执行计划情况

关于Block Nested-Loop

block Nested-Loop stackoverflow mysql文档

问题修复

修复方案一

对order_bill表建立索引，避免全表扫描

image.png

处理后的执行计划

加了索引之后

可以看到此时，已经使用了索引，同时mysql也不再使用Block Nested Loop

进一步优化

对order表也建立索引

联合索引

执行计划

执行计划

可以看到，此时对order表的查询也不再走全面扫描，而是使用了索引

最终优化

sql如下

EXPLAIN SELECT
    count( 0 ) 
FROM
    orders o
    INNER JOIN ( SELECT DISTINCT ORDER_NUM FROM orders_bill WHERE OPERATION_ID = 37 ) ob ON o.ORDER_NUM = ob.ORDER_NUM 
WHERE
    1 = 1 
    AND o.IS_PAYED = 2 
    AND o.ORDER_TYPE != 3 
    AND o.`ORDER_TYPE` = 1

执行计划

执行计划

最终优化后的查询结果

最终查询结果

参考文献

mysql官方文档

stackoverflow

stackoverflow
博客