【宋词】《生查子》 晏几道：关山魂梦长，鱼雁音尘少。两鬓可怜青，只为相思老。归梦碧纱窗，说与人人道。真个别离难，不似相逢好。

永夜

没有不值得去解决的问题，也没有不值得去学习的技术！

当 where 中的字段包含 shipping_at_gmt 与 shipping_status|shipping_type|operated_source 时，则强制使用组合索引，否则不强制使用组合索引

作者：

永夜

在

, , , , ,
Post Views: 141

1、参考：列表接口响应超时的优化

2、参考：在 Spatie\QueryBuilder 中，查询 SQL 强制使用索引

3、但是，当 where 中的字段包含 shipping_at_gmt 与 shipping_status、operator_user_id 时，强制使用组合索引后，是一个负优化了。 SQL 执行耗时整理如下

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）很大 的情况不用考虑，因为生产环境中不存在如此极端的情况。最终实现如下

PHP
$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、最终实现的规则，打印调试结果符合预期

Plaintext
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 / Laravel / Yii2 老项目维护与长期技术支持

如果你的 PHP / Laravel / Yii2 项目已经上线，但遇到原开发离职、Bug 长期无人修复、接口不稳定、性能下降、代码难以接手等问题，可以联系我做一次远程技术排查。

适合以下情况：
✅ 老旧 PHP 系统无人维护
✅ Laravel / Yii2 项目 Bug 修复
✅ 后台管理系统小功能迭代
✅ RESTful API 接口排查
✅ MySQL / Redis / Nginx 性能问题
✅ 长期远程兼职维护

可先从一次小问题开始：
✅ 线上报错排查
✅ 接口异常分析
✅ 慢查询与性能瓶颈定位
✅ 代码结构初步评估
✅ 部署环境与日志检查

如需咨询，请联系我，并注明：PHP 维护咨询。

联系方式：
Telegram：@shuijingwan
微信：13980074657
邮箱：shuijingwanwq@gmail.com

评论

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注

这个站点使用 Akismet 来减少垃圾评论。了解你的评论数据如何被处理。

更多文章