当 where 中的字段包含 shipping_at_gmt 与 shipping_status|shipping_type|operated_source 时，则强制使用组合索引，否则不强制使用组合索引
1、参考：列表接口响应超时的优化
2、参考：在 Spatie\QueryBuilder 中，查询 SQL 强制使用索引
3、但是，当 where 中的字段包含 shipping_at_gmt 与 shipping_status、operator_user_id 时，强制使用组合索引后，是一个负优化了。 SQL 执行耗时整理如下
#################################
# 耗时 24 秒
select
count(*) as aggregate
from
order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
where
`order_shipping_logs`.`operator_user_id` in (20000463)
and `order_shipping_logs`.`shipping_status` = 3
and `shipping_at_gmt` >= '2024-07-23 03:27:52'
and `shipping_at_gmt` <= '2024-10-23 03:27:52';
# 耗时 29 秒
select
`order_shipping_logs`.*
from
order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
where
`order_shipping_logs`.`operator_user_id` in (20000463)
and `order_shipping_logs`.`shipping_status` = 3
and `shipping_at_gmt` >= '2024-07-23 03:27:52'
and `shipping_at_gmt` <= '2024-10-23 03:27:52'
order by
`operated_at_gmt` desc
limit
100 offset 0;
# 耗时 1.5 秒
select
count(*) as aggregate
from
order_shipping_logs
where
`order_shipping_logs`.`operator_user_id` in (20000463)
and `order_shipping_logs`.`shipping_status` = 3
and `shipping_at_gmt` >= '2024-07-23 03:27:52'
and `shipping_at_gmt` <= '2024-10-23 03:27:52';
# 耗时 1.4 秒
select
`order_shipping_logs`.*
from
order_shipping_logs
where
`order_shipping_logs`.`operator_user_id` in (20000463)
and `order_shipping_logs`.`shipping_status` = 3
and `shipping_at_gmt` >= '2024-07-23 03:27:52'
and `shipping_at_gmt` <= '2024-10-23 03:27:52'
order by
`operated_at_gmt` desc
limit
100 offset 0;
# 以上的 4 条 SQL，强制使用组合索引属于负优化。原因在于 `operator_user_id` in (20000463) 的数据量（2338）很小
#################################
# 耗时 27 秒
select
count(*) as aggregate
from
order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
where
`order_shipping_logs`.`operator_user_id` in (20000250)
and `order_shipping_logs`.`shipping_type` = 1
and `order_shipping_logs`.`shipping_status` = 3
and `shipping_at_gmt` >= '2024-07-23 03:43:11'
and `shipping_at_gmt` <= '2024-10-23 03:43:11';
# 耗时 32 秒
select
`order_shipping_logs`.*
from
order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
where
`order_shipping_logs`.`operator_user_id` in (20000250)
and `order_shipping_logs`.`shipping_type` = 1
and `order_shipping_logs`.`shipping_status` = 3
and `shipping_at_gmt` >= '2024-07-23 03:43:11'
and `shipping_at_gmt` <= '2024-10-23 03:43:11'
order by
`operated_at_gmt` desc
limit
100 offset 0;
# 耗时 102 秒
select
count(*) as aggregate
from
order_shipping_logs
where
`order_shipping_logs`.`operator_user_id` in (20000250)
and `order_shipping_logs`.`shipping_type` = 1
and `order_shipping_logs`.`shipping_status` = 3
and `shipping_at_gmt` >= '2024-07-23 03:43:11'
and `shipping_at_gmt` <= '2024-10-23 03:43:11';
# 耗时 67 秒
select
`order_shipping_logs`.*
from
order_shipping_logs
where
`order_shipping_logs`.`operator_user_id` in (20000250)
and `order_shipping_logs`.`shipping_type` = 1
and `order_shipping_logs`.`shipping_status` = 3
and `shipping_at_gmt` >= '2024-07-23 03:43:11'
and `shipping_at_gmt` <= '2024-10-23 03:43:11'
order by
`operated_at_gmt` desc
limit
100 offset 0;
# 以上的 4 条 SQL，强制使用组合索引属于正优化。原因在于 `operator_user_id` in (20000463) 的数据量（18060318）很大
#################################
# 耗时 24 秒
select
count(*) as aggregate
from
order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
where
`order_shipping_logs`.`operator_user_id` in (4)
and `order_shipping_logs`.`shipping_status` = 3
and `shipping_at_gmt` >= '2024-07-23 07:28:36'
and `shipping_at_gmt` <= '2024-10-23 07:28:36';
# 耗时 30 秒
select
`order_shipping_logs`.*
from
order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
where
`order_shipping_logs`.`operator_user_id` in (4)
and `order_shipping_logs`.`shipping_status` = 3
and `shipping_at_gmt` >= '2024-07-23 07:28:36'
and `shipping_at_gmt` <= '2024-10-23 07:28:36'
order by
`operated_at_gmt` desc
limit
100 offset 0;
# 耗时 7 秒
select
count(*) as aggregate
from
order_shipping_logs
where
`order_shipping_logs`.`operator_user_id` in (4)
and `order_shipping_logs`.`shipping_status` = 3
and `shipping_at_gmt` >= '2024-07-23 07:28:36'
and `shipping_at_gmt` <= '2024-10-23 07:28:36';
# 耗时 6 秒
select
`order_shipping_logs`.*
from
order_shipping_logs
where
`order_shipping_logs`.`operator_user_id` in (4)
and `order_shipping_logs`.`shipping_status` = 3
and `shipping_at_gmt` >= '2024-07-23 07:28:36'
and `shipping_at_gmt` <= '2024-10-23 07:28:36'
order by
`operated_at_gmt` desc
limit
100 offset 0;
# 以上的 4 条 SQL，强制使用组合索引属于负优化。原因在于 `operator_user_id` in (4) 的数据量（323230）不大
#################################
# 耗时 24 秒
select
count(*) as aggregate
from
order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
where
`order_shipping_logs`.`operator_user_id` in (4)
and `order_shipping_logs`.`logistic_channel_id` in (2775)
and `order_shipping_logs`.`shipping_type` = 1
and `order_shipping_logs`.`shipping_status` = 3
and `shipping_at_gmt` >= '2024-07-23 07:40:53'
and `shipping_at_gmt` <= '2024-10-23 07:40:53';
# 耗时 29 秒
select
`order_shipping_logs`.*
from
order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
where
`order_shipping_logs`.`operator_user_id` in (4)
and `order_shipping_logs`.`logistic_channel_id` in (2775)
and `order_shipping_logs`.`shipping_type` = 1
and `order_shipping_logs`.`shipping_status` = 3
and `shipping_at_gmt` >= '2024-07-23 07:40:53'
and `shipping_at_gmt` <= '2024-10-23 07:40:53'
order by
`operated_at_gmt` desc
limit
100 offset 0;
# 耗时 11 秒
select
count(*) as aggregate
from
order_shipping_logs
where
`order_shipping_logs`.`operator_user_id` in (4)
and `order_shipping_logs`.`logistic_channel_id` in (2775)
and `order_shipping_logs`.`shipping_type` = 1
and `order_shipping_logs`.`shipping_status` = 3
and `shipping_at_gmt` >= '2024-07-23 07:40:53'
and `shipping_at_gmt` <= '2024-10-23 07:40:53';
# 耗时 10 秒
select
`order_shipping_logs`.*
from
order_shipping_logs
where
`order_shipping_logs`.`operator_user_id` in (4)
and `order_shipping_logs`.`logistic_channel_id` in (2775)
and `order_shipping_logs`.`shipping_type` = 1
and `order_shipping_logs`.`shipping_status` = 3
and `shipping_at_gmt` >= '2024-07-23 07:40:53'
and `shipping_at_gmt` <= '2024-10-23 07:40:53'
order by
`operated_at_gmt` desc
limit
100 offset 0;
# 以上的 4 条 SQL，强制使用组合索引属于负优化。原因在于 `operator_user_id` in (4) 的数据量（323230）不大
4、初步总结出规律，当 where 中的字段包含 shipping_at_gmt 与 shipping_status|shipping_type|operated_source 时，则强制使用组合索引，否则不强制使用组合索引。针对 `operator_user_id` in (20000463) 的数据量（18060318）很大 的情况不用考虑，因为生产环境中不存在如此极端的情况。最终实现如下
$whereColumns = array_unique(Arr::pluck($builder->getQuery()->wheres, 'column'));
$filteredWhereColumns = Arr::where($whereColumns, function ($value, $key) {
return $value != 'shipping_at_gmt';
});
// 当筛选字段包含 交运时间 与 交运状态|交运类型|操作来源 时，强制使用组合索引 order_shipping_logs_sag_os_ss_st_index
if (in_array('shipping_at_gmt', $whereColumns) && !empty($filteredWhereColumns) && empty(array_diff($filteredWhereColumns, ['order_shipping_logs.shipping_status', 'order_shipping_logs.shipping_type', 'order_shipping_logs.operated_source']))) {
$builder->from(DB::raw('order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)'));
}
5、最终实现的规则，打印调试结果符合预期
Array
(
[0] => order_shipping_logs.operator_user_id
[1] => order_shipping_logs.logistic_channel_id
[2] => order_shipping_logs.shipping_type
[3] => order_shipping_logs.shipping_status
[4] => shipping_at_gmt
)
0 不强制使用组合索引
Array
(
[0] => order_shipping_logs.shipping_type
[1] => order_shipping_logs.shipping_status
[2] => shipping_at_gmt
)
1 强制使用组合索引
Array
(
[0] => order_shipping_logs.operated_source
[1] => order_shipping_logs.shipping_type
[2] => order_shipping_logs.shipping_status
[3] => shipping_at_gmt
)
1 强制使用组合索引
Array
(
[0] => order_shipping_logs.shipping_type
[1] => shipping_at_gmt
)
1 强制使用组合索引
Array
(
[0] => shipping_at_gmt
)
0 不强制使用组合索引
Array
(
[0] => order_shipping_logs.operator_user_id
[1] => shipping_at_gmt
)
0 不强制使用组合索引
Array
(
[0] => order_shipping_logs.shipping_type
[1] => operated_at_gmt
)
0 不强制使用组合索引
