force index工作记录

最近在优化订单，查询列表在5s左右，于是看看代码：

SELECT
    a.*, b.*,
    (
        SELECT
            c.AMOUNT
        FROM
            TB_INVOICE_APPLY c
        WHERE
            c.ORDER_ID = a.id
        ORDER BY
            c.CREATE_TIME DESC
        LIMIT 1
    ) AS invoiceAmount,
    f.voucher_error_msg AS voucherErrorMsg,
    ifnull(f.voucher_status, 3) AS voucherStatus,
    f.voucher_no AS voucherNo,
    f.voucher_create AS voucherCreate,
    f.voucher_time AS voucherCreateTime
FROM
    tb_order_info a
JOIN tb_order_insurance b ON a.id = order_id
LEFT JOIN tb_voucher_info f ON f.business_id = a.id
AND f.business_type = 6
WHERE
    1 = 1
AND order_type = 14
ORDER BY
    a.update_time
LIMIT 20

订单表有100多万，tb_order_insurance保险订单几十万，查询为撒这么慢，后来加explain 看看执行结果原来是排序update_time

SELECT
    a.*, b.*,
    (
        SELECT
            c.AMOUNT
        FROM
            TB_INVOICE_APPLY c
        WHERE
            c.ORDER_ID = a.id
        ORDER BY
            c.CREATE_TIME DESC
        LIMIT 1
    ) AS invoiceAmount,
    f.voucher_error_msg AS voucherErrorMsg,
    ifnull(f.voucher_status, 3) AS voucherStatus,
    f.voucher_no AS voucherNo,
    f.voucher_create AS voucherCreate,
    f.voucher_time AS voucherCreateTime
FROM
    tb_order_info a force index(index_update_time)
JOIN tb_order_insurance b ON a.id = order_id
LEFT JOIN tb_voucher_info f ON f.business_id = a.id
AND f.business_type = 6
WHERE
    1 = 1
AND order_type = 14
ORDER BY
    a.update_time
LIMIT 20

就加了这个force index(index_update_time) ，速度一下就提起来了。

还有一个脚本：

SELECT a.*,b.*,SUM(c.order_amount) as cumulative , ( SELECT c.AMOUNT FROM TB_INVOICE_APPLY c WHERE c.ORDER_ID =
a.id ORDER BY c.CREATE_TIME DESC LIMIT 1 ) AS invoiceAmount,f.voucher_error_msg as voucherErrorMsg,ifnull(f.voucher_status,3) as voucherStatus,f.voucher_no as voucherNo,
f.voucher_create as voucherCreate,f.voucher_time as voucherCreateTime
from
b_order_info a join tb_order_insurance b ON a.id=b.order_id
left join tb_voucher_info f on f.business_id=a.id and f.business_type=6
left join tb_order_info c on c.customer_id=a.customer_id and c.order_type=13 and c.order_status!=9
left join tb_order_insurance d on d.order_id=c.id and d.import_month <= b.import_month group by a.id
order by a.update_time desc 

这个脚本大概意思是查询这个订单对应相同客户的订单金额总和，大概要4s左右执行完，加上force index(index_update_time)后大概要10几秒怎么回事，该用的索引都用上了，结果还是很慢，于是决定把脚本拆分，第一个脚本不用异步，后面的数据全部异步获取，把这一块拆分出来l

eft join tb_order_info c on c.customer_id=a.customer_id and c.order_type=13 and c.order_status!=9 left join tb_order_insurance d on d.order_id=c.id and d.import_month <= b.import_month

Future<Map<Long, List<OrderInsurancePageDTO>>> cumulativeFuture = threadPoolTaskExecutor.submit(() -> {
    Map<Long, List<OrderInsurancePageDTO>> map = new HashMap<>();
    List<OrderInsurancePageDTO> bqxfPageParamAsyn2 = baseMapper.findBqxfPageParamAsyn2(bqxfPageParamAsyn);
    if (CollectionUtils.isNotEmpty(bqxfPageParamAsyn2)) {
        map = bqxfPageParamAsyn2.stream().filter(x -> x.getId() != null).collect(Collectors.groupingBy(OrderInsurancePageDTO::getId));
    }
    return map;
});

思路是，把前面查出来的20条数据作为主数据，因为看到（group by a.id），所以异步的数据还要依赖前面的id分组，所以把整个对象传入，用 union all 连接 20条数据 异步获取sum(c.order_amount)。

<select id="findBqxfPageParamAsyn2"resultType="com.sinoiov.eimp.order.dto.OrderInsurancePageDTO">
<foreach collection="bqxfPageParamAsyn" index="index" item="cl" separator=" UNION ALL ">
        select sum(c.order_amount) as orderAmount,c.customer_id,d.import_month,#{cl.id} as id from
        tb_order_info c
        left join tb_order_insurance d on d.order_id=c.id and d.import_month &lt;=#{cl.importMonth}
  where  1=1 AND c.customer_id=#{cl.customerId}  and c.order_type=#{cl.orderType}                 and c.order_status!=9
    </foreach>
</select>

于是我们来了解一下这个force index。

"force index" 是 MySQL 中用于强制查询优化器使用指定索引的一个查询提示。使用 "force index" 时，查询优化器会选择指定的索引进行查询操作，而不是根据其内部算法自动选择最优索引。这给开发者提供了一定程度的控制，以便在某些特定场景下优化查询性能。

利弊如下：
利：
1. 在某些情况下，查询优化器可能没有选择最优的索引，导致查询性能较差。这时，你可以通过 "force index" 强制使用你认为更合适的索引，从而提高查询性能。
2. 当你了解数据分布和访问模式时，你可以更好地控制索引选择，以便在特定场景下优化查询性能。

弊：
1. 强制使用指定索引可能导致查询优化器无法灵活地选择最优索引，从而降低查询性能。在大多数情况下，查询优化器能够自动选择合适的索引，因此强制使用索引可能并不总是最佳选择。
2. 使用 "force index" 会增加代码的维护成本，因为当表结构或数据分布发生变化时，手动指定的索引可能不再是最优选择。这可能导致性能问题和需要频繁地调整代码。
3. 过度依赖 "force index" 可能会使开发者忽略更基本的性能优化方法，如合理设计索引、优化表结构和使用更高效的查询语句等。

总之，"force index" 是一个强大的工具，但需谨慎使用,在项目中其他查询条件不走索引，也很麻烦。在使用 "force index" 之前，首先要确保已经尝试了其他性能优化方法，并明确了为什么查询优化器没有选择最佳索引。在某些特定场景下，合理使用 "force index" 可以提高查询性能，但请注意其潜在的弊端。