列表接口响应超时的优化（基于 SQL 灵活控制是否强制使用 MySQL 组合索引）

由永夜 · 2024/11/20

1、在性能环境中，列表接口响应超时。如图1

图1

2、决定接口执行的 SQL，在本地环境中连接性能环境的 MySQL，然后在 Laravel Telescope 中查看生成的 SQL 耗时，发现一条 select count(*) as aggregate from 耗时远远超出其他的 SQL，时长 164915.88ms。如图2

图2

select
  count(*) as aggregate
from
  `order_shipping_logs`
where
  `order_shipping_logs`.`shipping_type` = 1
  and `shipping_at_gmt` >= '2024-07-21 03:04:04'
  and `shipping_at_gmt` <= '2024-10-21 03:04:04'

3、shipping_type 列的值为 1 总数为 8354440（查询耗时 174 秒），shipping_type 列的值为 0 总数为 0（查询耗时 1.3 秒），即列的基数较低（也就是大部分行的 shipping_type 值相同），仅为该列建立索引的效果可能有限。在 shipping_type 为 1 时，查询需要处理大量数据，性能受到影响。如图3

shipping_type 列的值为 1 总数为 8354440（查询耗时 174 秒），shipping_type 列的值为 0 总数为 0（查询耗时 1.3 秒），即列的基数较低（也就是大部分行的 shipping_type 值相同），仅为该列建立索引的效果可能有限。在 shipping_type 为 1 时，查询需要处理大量数据，性能受到影响。

图3

4、决定删除单列索引，创建组合索引，将这两列一起索引。组合索引的顺序为：shipping_at_gmt, shipping_type，从而先按时间范围过滤，再进行 shipping_type 的过滤。shipping_type 具有低基数（即重复值很多，只有少数几种可能的值，如 0 和 1），而 shipping_at_gmt 是一个高基数列（具有很多唯一值，代表不同的时间点），优先索引高基数的列可以提升查询性能。耗时 330 秒。如图4

决定删除单列索引，创建组合索引，将这两列一起索引。组合索引的顺序为：shipping_at_gmt, shipping_type，从而先按时间范围过滤，再进行 shipping_type 的过滤。shipping_type 具有低基数（即重复值很多，只有少数几种可能的值，如 0 和 1），而 shipping_at_gmt 是一个高基数列（具有很多唯一值，代表不同的时间点），优先索引高基数的列可以提升查询性能。耗时 330 秒。

图4

alter table
  `order_shipping_logs`
add
  index `order_shipping_logs_shipping_at_gmt_shipping_type_index`(`shipping_at_gmt`, `shipping_type`);

alter table
  `order_shipping_logs`
add
  index `order_shipping_logs_shipping_at_gmt_shipping_status_index`(`shipping_at_gmt`, `shipping_status`);

alter table `order_shipping_logs` drop index `order_shipping_logs_shipping_type_index`;

alter table `order_shipping_logs` drop index `order_shipping_logs_shipping_status_index`;

5、再次执行步骤 2 中的 SQL ，耗时 4 秒，符合预期。如图5

图5

6、但是当 SQL 的 WHERE 条件中，同时包含 3 个字段时，即 shipping_at_gmt、shipping_type、shipping_status ，查询耗时 95 秒。

SELECT count(*) AS AGGREGATE FROM `order_shipping_logs_copy1` WHERE `order_shipping_logs_copy1`.`shipping_type` = 1 AND `order_shipping_logs_copy1`.`shipping_status` = 3 AND `shipping_at_gmt` >= '2024-07-21 06:54:55' 
AND `shipping_at_gmt` <= '2024-10-21 06:54:55'

7、决定调整第 4 步骤的索引结构。决定删除单列索引，创建组合索引，将这两列一起索引。组合索引的顺序为：shipping_type, shipping_at_gmt。避免要单独保留两个单列索引。当 SQL 的 WHERE 条件中，同时包含 3 个字段时，即 shipping_at_gmt、shipping_type、shipping_status ，查询耗时 95 秒。仍然是全表扫描。如图6

决定调整第 4 步骤的索引结构。决定删除单列索引，创建组合索引，将这两列一起索引。组合索引的顺序为：shipping_type, shipping_at_gmt。避免要单独保留两个单列索引。当 SQL 的 WHERE 条件中，同时包含 3 个字段时，即 shipping_at_gmt、shipping_type、shipping_status ，查询耗时 95 秒。仍然是全表扫描。

图6

alter table
  `order_shipping_logs`
add
  index `order_shipping_logs_shipping_type_shipping_at_gmt_index`(`shipping_type`, `shipping_at_gmt`);

alter table
  `order_shipping_logs`
add
  index `order_shipping_logs_shipping_status_shipping_at_gmt_index`(`shipping_status`, `shipping_at_gmt`);

8、决定继续保留第 4 步骤的索引结构。不再删除单列索引，创建组合索引，将这两列一起索引。当 SQL 的 WHERE 条件中，同时包含 3 个字段时，即 shipping_at_gmt、shipping_type、shipping_status ，查询耗时 51 秒。仍然是全表扫描。

9、决定继续保留第 4 步骤的索引结构。不再删除单列索引，创建组合索引，将这三列一起索引。分别执行如下 SQL，执行耗时基本上在 5 秒左右。符合预期。但是，在新增加第 4 个字段后，执行耗时超过 60 秒。

alter table
  `order_shipping_logs_copy2`
add
  index `order_shipping_logs_shipping_at_gmt_status_type_index`(`shipping_at_gmt`, `shipping_status`, `shipping_type`);

# 耗时 3.4 秒
SELECT
	count(*) AS AGGREGATE 
FROM
	`order_shipping_logs_copy2` 
WHERE
	`order_shipping_logs_copy2`.`shipping_type` = 1 
	AND `shipping_at_gmt` >= '2024-07-21 03:04:04' 
	AND `shipping_at_gmt` <= '2024-10-21 03:04:04';

# 耗时 3.4 秒
SELECT count(*) AS AGGREGATE FROM `order_shipping_logs_copy2` 
WHERE 
	`order_shipping_logs_copy2`.`shipping_status` = 3 
	AND `shipping_at_gmt` >= '2024-07-21 03:04:04'
	AND `shipping_at_gmt` <= '2024-10-21 03:04:04';

# 耗时 3.7 秒
SELECT count(*) AS AGGREGATE FROM `order_shipping_logs_copy2` 
WHERE 
	`order_shipping_logs_copy2`.`shipping_type` = 1 
	AND `order_shipping_logs_copy2`.`shipping_status` = 3 
	AND `shipping_at_gmt` >= '2024-07-21 06:54:55' 
	AND `shipping_at_gmt` <= '2024-10-21 06:54:55';

# 耗时 123 秒
SELECT count(*) AS AGGREGATE FROM `order_shipping_logs_copy2` 
WHERE 
	`order_shipping_logs_copy2`.`operated_source` in (2) 
	AND `order_shipping_logs_copy2`.`shipping_type` = 1 
	AND `order_shipping_logs_copy2`.`shipping_status` = 3 
	AND `shipping_at_gmt` >= '2024-07-21 06:54:55' 
	AND `shipping_at_gmt` <= '2024-10-21 06:54:55';

# 耗时 148 秒
SELECT count(*) AS AGGREGATE FROM `order_shipping_logs_copy2` 
WHERE 
	`order_shipping_logs_copy2`.`operated_source` IN ( 2 ) 
	AND `order_shipping_logs_copy2`.`shipping_type` = 1 
	AND `shipping_at_gmt` >= '2024-07-21 09:34:15' 
	AND `shipping_at_gmt` <= '2024-10-21 09:34:15'

# 耗时 7.8 秒
SELECT count(*) AS AGGREGATE FROM `order_shipping_logs_copy2` WHERE `shipping_at_gmt` >= '2024-07-21 09:34:15' 
AND `shipping_at_gmt` <= '2024-10-21 09:34:15';

10、最终决定将 tinyint 类型的 3 个字段与 shipping_at_gmt ，总计 4 个字段，创建一个组合索引，字段顺序基于基数从高到低排序。发现有时候不会使用到组合索引 order_shipping_logs_sag_os_ss_st_index，执行耗时可能超过 60 秒。

ALTER TABLE `order_shipping_logs_copy3` ADD INDEX `order_shipping_logs_sag_os_ss_st_index` ( `shipping_at_gmt`, `operated_source`, `shipping_status`, `shipping_type` );

# 耗时 4.2 秒
SELECT count(*) AS AGGREGATE FROM `order_shipping_logs_copy3` 
WHERE 
	`shipping_at_gmt` >= '2024-07-21 09:34:15' 
	AND `shipping_at_gmt` <= '2024-10-21 09:34:15';

# 耗时 5.8 秒
EXPLAIN SELECT
	count(*) AS AGGREGATE 
FROM
	`order_shipping_logs_copy3` 
WHERE
	`order_shipping_logs_copy3`.`operated_source` IN ( 1, 2 ) 
	AND `shipping_at_gmt` >= '2024-07-21 03:04:04' 
	AND `shipping_at_gmt` <= '2024-10-21 03:04:04'; 

# 耗时 90 秒
EXPLAIN SELECT count(*) AS AGGREGATE FROM `order_shipping_logs_copy3` 
WHERE 
	`order_shipping_logs_copy3`.`shipping_status` = 3 
	AND `shipping_at_gmt` >= '2024-07-21 03:04:04' 
	AND `shipping_at_gmt` <= '2024-10-21 03:04:04';

# 耗时 119 秒
SELECT
	count(*) AS AGGREGATE 
FROM
	`order_shipping_logs_copy3` 
WHERE
	`order_shipping_logs_copy3`.`shipping_type` = 1 
	AND `shipping_at_gmt` >= '2024-07-21 03:04:04' 
	AND `shipping_at_gmt` <= '2024-10-21 03:04:04'; 
	
# 耗时 36.4 秒
SELECT count(*) AS AGGREGATE FROM `order_shipping_logs_copy3` 
WHERE 
	`order_shipping_logs_copy3`.`operated_source` IN ( 1, 2 )
	AND `order_shipping_logs_copy3`.`shipping_status` = 3 
	AND `shipping_at_gmt` >= '2024-07-21 06:54:55' 
	AND `shipping_at_gmt` <= '2024-10-21 06:54:55';

# 耗时 42 秒
SELECT count(*) AS AGGREGATE FROM `order_shipping_logs_copy3` 
WHERE 
	`order_shipping_logs_copy3`.`operated_source` IN ( 1, 2 )
	AND `order_shipping_logs_copy3`.`shipping_type` = 1 
	AND `shipping_at_gmt` >= '2024-07-21 06:54:55' 
	AND `shipping_at_gmt` <= '2024-10-21 06:54:55';

# 耗时 178 秒
SELECT count(*) AS AGGREGATE FROM `order_shipping_logs_copy3` 
WHERE 
	`order_shipping_logs_copy3`.`shipping_status` = 3 
	AND `order_shipping_logs_copy3`.`shipping_type` = 1 
	AND `shipping_at_gmt` >= '2024-07-21 06:54:55' 
	AND `shipping_at_gmt` <= '2024-10-21 06:54:55';

# 耗时 162 秒
EXPLAIN SELECT
	count(*) AS AGGREGATE 
FROM
	`order_shipping_logs_copy3` 
WHERE
	`order_shipping_logs_copy3`.`operated_source` = 2
	AND `order_shipping_logs_copy3`.`shipping_type` = 1 
	AND `order_shipping_logs_copy3`.`shipping_status` = 3 
	AND `shipping_at_gmt` >= '2024-07-21 06:54:55' 
	AND `shipping_at_gmt` <= '2024-10-21 06:54:55';

11、最后决定当条件中包含 shipping_at_gmt 与另外 3 个字段时，强制使用组合索引 order_shipping_logs_sag_os_ss_st_index，耗时控制在 5 秒左右。最终的索引结构如下。如图7

图7

# 耗时 4.2 秒
EXPLAIN SELECT count(*) AS AGGREGATE FROM `order_shipping_logs_copy3` 
WHERE 
	`shipping_at_gmt` >= '2024-07-21 09:34:15' 
	AND `shipping_at_gmt` <= '2024-10-21 09:34:15';

# 耗时 3.6 秒
EXPLAIN SELECT
	count(*) AS AGGREGATE 
FROM
	`order_shipping_logs_copy3` 
FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
WHERE
	`order_shipping_logs_copy3`.`operated_source` IN ( 1, 2 ) 
	AND `shipping_at_gmt` >= '2024-07-21 03:04:04' 
	AND `shipping_at_gmt` <= '2024-10-21 03:04:04'; 

# 耗时 3.4 秒
EXPLAIN SELECT 
	count(*) AS AGGREGATE 
FROM `order_shipping_logs_copy3` 
FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
WHERE 
	`order_shipping_logs_copy3`.`shipping_status` = 3 
	AND `shipping_at_gmt` >= '2024-07-21 03:04:04' 
	AND `shipping_at_gmt` <= '2024-10-21 03:04:04';

# 耗时 3.4 秒
SELECT
	count(*) AS AGGREGATE 
FROM
	`order_shipping_logs_copy3` 
FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
WHERE
	`order_shipping_logs_copy3`.`shipping_type` = 1 
	AND `shipping_at_gmt` >= '2024-07-21 03:04:04' 
	AND `shipping_at_gmt` <= '2024-10-21 03:04:04'; 
	
# 耗时 3.7 秒
SELECT 
	count(*) AS AGGREGATE 
FROM `order_shipping_logs_copy3` 
FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
WHERE 
	`order_shipping_logs_copy3`.`operated_source` IN ( 1, 2 )
	AND `order_shipping_logs_copy3`.`shipping_status` = 3 
	AND `shipping_at_gmt` >= '2024-07-21 06:54:55' 
	AND `shipping_at_gmt` <= '2024-10-21 06:54:55';

# 耗时 3.9 秒
SELECT 
	count(*) AS AGGREGATE 
FROM `order_shipping_logs_copy3`
FORCE INDEX (order_shipping_logs_sag_os_ss_st_index) 
WHERE 
	`order_shipping_logs_copy3`.`operated_source` IN ( 1, 2 )
	AND `order_shipping_logs_copy3`.`shipping_type` = 1 
	AND `shipping_at_gmt` >= '2024-07-21 06:54:55' 
	AND `shipping_at_gmt` <= '2024-10-21 06:54:55';

# 耗时 3.7 秒
SELECT 
	count(*) AS AGGREGATE 
FROM `order_shipping_logs_copy3`
FORCE INDEX (order_shipping_logs_sag_os_ss_st_index) 
WHERE 
	`order_shipping_logs_copy3`.`shipping_status` = 3 
	AND `order_shipping_logs_copy3`.`shipping_type` = 1 
	AND `shipping_at_gmt` >= '2024-07-21 06:54:55' 
	AND `shipping_at_gmt` <= '2024-10-21 06:54:55';

# 耗时 4 秒
EXPLAIN SELECT
	count(*) AS AGGREGATE 
FROM
	`order_shipping_logs_copy3`
FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
WHERE
	`order_shipping_logs_copy3`.`operated_source` = 2
	AND `order_shipping_logs_copy3`.`shipping_type` = 1 
	AND `order_shipping_logs_copy3`.`shipping_status` = 3 
	AND `shipping_at_gmt` >= '2024-07-21 06:54:55' 
	AND `shipping_at_gmt` <= '2024-10-21 06:54:55';

12、但是，当执行如下 SQL 时，不使用组合索引时，耗时 1.4 秒。使用组合索引时，耗时 2.1 秒。相反，耗时增加了。也就是说，如果包含 shipping_at_gmt 与另外 3 个字段时，强制使用组合索引 order_shipping_logs_sag_os_ss_st_index 的话，可以确保 SQL 执行不会耗时超过 60 秒，但是在某些时候，执行耗时会增加。目前来看，可以接受。

CREATE TABLE `order_shipping_logs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `shipping_status` tinyint unsigned NOT NULL DEFAULT '2' COMMENT '交运状态，2：交运中；3：交运成功；4：交运失败；5：取消成功；6：取消失败',
  `shipping_at_gmt` datetime DEFAULT NULL COMMENT '交运时间',
  `shipping_type` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '交运类型，1：订单交运；2：取消交运',
  `operated_source` tinyint unsigned NOT NULL DEFAULT '2' COMMENT '操作来源，1：规则交运；2：手动交运；3：打回待处理；4：修改/删除物流渠道；5：修改/删除货运单号；6：重新交运；7：订单拆分；8：订单合并',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `order_shipping_logs_shipping_at_gmt_index` (`shipping_at_gmt`) USING BTREE,
  KEY `order_shipping_logs_operated_source_index` (`operated_source`) USING BTREE,
  KEY `order_shipping_logs_shipping_type_index` (`shipping_type`),
  KEY `order_shipping_logs_shipping_status_index` (`shipping_status`),
  KEY `order_shipping_logs_sag_os_ss_st_index` (`shipping_at_gmt`,`operated_source`,`shipping_status`,`shipping_type`)
) ENGINE=InnoDB AUTO_INCREMENT=16787 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单交运日志表';

# 耗时 1.3 秒
SELECT
	count(*) AS AGGREGATE 
FROM
	`order_shipping_logs_copy3` 
WHERE
	`order_shipping_logs_copy3`.`shop_plat_id` IN ( 1 ) 
	AND `order_shipping_logs_copy3`.`shop_id` IN ( 6 ) 
	AND `order_shipping_logs_copy3`.`operator_user_id` IN ( 9549 ) 
	AND `order_shipping_logs_copy3`.`logistic_company_id` IN ( 153 ) 
	AND `order_shipping_logs_copy3`.`operated_source` IN ( 2 ) 
	AND `order_shipping_logs_copy3`.`shipping_type` = 2 
	AND `order_shipping_logs_copy3`.`shipping_status` = 3 
	AND `shipping_at_gmt` >= '2024-07-22 02:01:14' 
	AND `shipping_at_gmt` <= '2024-10-22 02:01:14';

# 耗时 2.1 秒
SELECT
	count(*) AS AGGREGATE 
FROM
	`order_shipping_logs_copy3` 
FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
WHERE
	`order_shipping_logs_copy3`.`shop_plat_id` IN ( 1 ) 
	AND `order_shipping_logs_copy3`.`shop_id` IN ( 6 ) 
	AND `order_shipping_logs_copy3`.`operator_user_id` IN ( 9549 ) 
	AND `order_shipping_logs_copy3`.`logistic_company_id` IN ( 153 ) 
	AND `order_shipping_logs_copy3`.`operated_source` IN ( 2 ) 
	AND `order_shipping_logs_copy3`.`shipping_type` = 2 
	AND `order_shipping_logs_copy3`.`shipping_status` = 3 
	AND `shipping_at_gmt` >= '2024-07-22 02:01:14' 
	AND `shipping_at_gmt` <= '2024-10-22 02:01:14';

13、在性能环境中，列表接口响应时长为 30 秒左右，不太符合预期。预期为 10 秒以内。如图8

图8

14、决定接口执行的 SQL，在本地环境中连接性能环境的 MySQL，列表接口响应时长为 10 秒左右。然后在 Laravel Telescope 中查看生成的 SQL 耗时，发现一条 select `order_shipping_logs`.* from order_shipping_logs FORCE INDEX 耗时远远超出其他的 SQL，时长 30679.38ms。那么 select count(*) as aggregate from 需要强制使用组合索引，而 select `order_shipping_logs`.* from 不需要强制使用组合索引。如图9

决定接口执行的 SQL，在本地环境中连接性能环境的 MySQL，列表接口响应时长为 10 秒左右。然后在 Laravel Telescope 中查看生成的 SQL 耗时，发现一条 select `order_shipping_logs`.* from order_shipping_logs FORCE INDEX 耗时远远超出其他的 SQL，时长 30679.38ms。那么 select count(*) as aggregate from 需要强制使用组合索引，而 select `order_shipping_logs`.* from 不需要强制使用组合索引

图9

15、参考：强制 Laravel 9 在分页的 count() 查询中使用组合索引，同时让实际数据查询不使用强制索引。由于时间关系，暂缓实现。

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

列表接口响应超时的优化（基于 SQL 灵活控制是否强制使用 MySQL 组合索引）

您可能还喜欢...

发表回复取消回复

近期文章

近期评论

分类

其他操作

日历

2024 年 11 月
一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

列表接口响应超时的优化（基于 SQL 灵活控制是否强制使用 MySQL 组合索引）

您可能还喜欢...

任务的入库成功、入库失败，皆需要能够查询到发布记录的设计与实现(在 Yii 2.0 中具体实现，当响应状态码为 404、302 时，未执行到过滤器方法：afterAction($action, $result) 的分析解决) (二)

在 Docker 容器中，Nginx PHP 环境中响应 404 Not Found 的分析与解决

在 Windows 10 中执行命令行，报错：Call to undefined function App\Console\Commands\posix_getpid()

发表回复 取消回复

近期文章

近期评论

分类

其他操作

标签

日历

发表回复取消回复