When the fields in where contain Shipping_at_gmt and shipping_status|Shipping_Type|Operated_Source, the combined index is forced, otherwise the combined index is not mandatory.
1. Reference:Optimization of List Interface Response Timeout
2. Reference:In Spatie\QueryBuilder, query SQL enforces index
3. However, when the fields in where contain shipping_at_gmt, Shipping_status, operator_user_id, after forcing the combined index is a negative optimization. SQL execution time-consuming sorting is as follows
#################################
# 耗时 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. Preliminarily summarize the rules, when the fields in where contain Shipping_at_gmt and Shipping_Status|Shipping_Type|Operated_Source When the combined index is forced, otherwise the combined index is not mandatory. The amount of data (18060318) for `operator_user_id` in (20000463) is large, because there is no such extreme situation in the production environment. The final implementation is as follows
$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. The final implementation rules, print the debugging results in line with expectations
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 不强制使用组合索引
