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.

Post Views: 83

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 不强制使用组合索引

PHP / Yii2 / Laravel Enterprise Development & Architecture Consulting

With more than 15 years of web development experience, I have worked extensively on enterprise platforms, business management systems, API services, and large-scale web applications. My expertise covers PHP, Yii2, Laravel, MySQL, Redis, RESTful APIs, and scalable backend architectures. I have led architecture design and core system development for multiple production projects and long-term enterprise systems.

Ideal For:
✅ Enterprise applications
✅ ERP / CRM systems
✅ RESTful API platforms
✅ Multi-tenant SaaS projects
✅ Legacy PHP system modernization

What I Offer:
✅ PHP / Yii2 / Laravel Development
✅ RESTful API Design & Development
✅ System Architecture Consulting
✅ Performance Optimization & Troubleshooting
✅ Long-Term Technical Support & Maintenance

If you would like to discuss your project, please contact me and mention: PHP Project Consultation.

Contact Me:
Telegram: @shuijingwan
WeChat: 13980074657
Email: shuijingwanwq@gmail.com

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.

评论

Leave a Reply Cancel reply

更多文章

🚀 Wireguard + Clash Verge Rev for domestic direct connection / foreign diversion (Vultr & ZgoCloud actual combat evolution version)

Input method selection failure record from Windows to Ubuntu:rime → wbpy

Starting with Bewild: The First Step of My Tech Blog Alliance Marketing

From Classic to Block (final chapter): The theme migration is over, I decided to stop tossing