Optimization of list interface response timeouts (flexible control based on SQL to control whether MySQL combined index is mandatory)

1. In the performance environment, the list interface response timeout. as shown in Figure 1

Figure 1

2. Determine the sql of the interface to execute, connect the mysql of the performance environment in the local environment, and then view the generated sql in Laravel Telescope, and find a select count(*) as aggregate FROM is much longer than other SQL, with a duration of 164915.88ms. as shown in Figure 2

Figure 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. The value of the Shipping_Type column is 1, the total number is 8354440 (query time 174 seconds), and the value of the Shipping_Type column is 0 and the total number is 0. 1.3 seconds), that is, the base of the column is low (that is, the shipping_type value of most rows is the same), and the effect of indexing only the column may be limited. When the shipping_type is 1, the query needs to process a lot of data, and the performance is affected. as shown in Figure 3

Figure 3

4. Decide to delete the single-column index, create a combined index, and index the two columns together. The order of the combined index is: shipping_at_gmt, shipping_type, so as to filter by time range, and then filter shipping_type. Shipping_Type has a low cardinality (i.e. there are many repeat values, only a few possible values, such as 0 and 1), while SHIPPING_AT_GMT It is a high base sequence (with many unique values, representing different time points), and the columns that preferentially index the high cardinality can improve the query performance. It took 330 seconds. as shown in Figure 4

Figure 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_SHIPING_LOGS_SHIPPING_TYPE_INDEX`;

ALTER TABLE `ORDER_SHIPPING_LOGS` DROP INDEX `ORDER_SHIPING_LOGS_SHIPPING_STATUS_INDEX`;

5. Execute the SQL in step 2 again, which takes 4 seconds, in line with expectations. as shown in Figure 5

Figure 5

6. However, when the WHERE condition of SQL contains 3 fields at the same time, namely Shipping_AT_GMT, Shipping_Type, Shipping_Status , the query takes 95 seconds.

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. Decide to adjust the index structure of step 4. Decide to delete a single column index, create a combined index, and index the two columns together. The order of the combined index is: shipping_type, shipping_at_gmt. Avoid keeping two single column indexes individually. When the SQL WHERE condition contains 3 fields at the same time, namely SHIPING_AT_GMT, SHIPPING_TYPE, SHIPPING_STATUS , the query takes 95 seconds. Still a full table scan. as shown in Figure 6

Figure 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. Decide to keep the index structure of step 4. No more single-column indexes are deleted, combined indexes are created, and the two columns are indexed together. When the SQL WHERE condition contains 3 fields at the same time, namely Shipping_AT_GMT, SHIPPING_TYPE, SHIPPING_STATUS , the query takes 51 seconds. Still a full table scan.

9. Decide to keep the index structure of step 4. No more single-column indexes are deleted, combined indexes are created, and these three columns are indexed together. Execute the following SQL separately, and the execution time is basically about 5 seconds. in line with expectations. However, after adding the 4th field, the execution took more than 60 seconds.

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 seconds
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 seconds
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 seconds
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 seconds
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 seconds
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 seconds
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. It is finally decided to combine the 3 fields of the TinyInt type with the shipping_at_gmt , a total of 4 fields, and create a combined index. It is found that sometimes the combined index ORDER_SHIPPING_LOGS_SAG_OS_SS_ST_INDEX will not be used, and the execution may take more than 60 seconds.

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 seconds
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 seconds
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 seconds
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 seconds
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 seconds
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 seconds
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 seconds
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 seconds
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. Finally, it is decided to force the use of the combined index ORDER_SHIPPING_LOGS_SAG_OS_SS_ST_INDEX when the condition contains Shipping_AT_GMT and the other 3 fields, and the time-consuming control is 5 seconds or so. The final index structure is as follows. as shown in Figure 7

Figure 7

# 4.2 seconds
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 seconds
Explain Select
count(*) as aggregate
from
`order_shipping_logs_copy3`
FORCE INDEX (ORDER_SHIPING_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 seconds
Explain Select
count(*) as aggregate
from `order_shipping_logs_copy3`
FORCE INDEX (ORDER_SHIPING_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 seconds
select
count(*) as aggregate
from
`order_shipping_logs_copy3`
FORCE INDEX (ORDER_SHIPING_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 seconds
select
count(*) as aggregate
from `order_shipping_logs_copy3`
FORCE INDEX (ORDER_SHIPING_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 seconds
select
count(*) as aggregate
from `order_shipping_logs_copy3`
FORCE INDEX (ORDER_SHIPING_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 seconds
select
count(*) as aggregate
from `order_shipping_logs_copy3`
FORCE INDEX (ORDER_SHIPING_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 seconds
Explain Select
count(*) as aggregate
from
`order_shipping_logs_copy3`
FORCE INDEX (ORDER_SHIPING_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. However, when executing the following SQL, when the combined index is not used, it takes 1.4 seconds. It takes 2.1 seconds to use the combined index. On the contrary, the time-consuming has increased. That is, if you force the combination index ORDER_SHIPING_LOGS_SAG_OS_SS_ST_INDEX if you include SHIPPING_AT_GMT and 3 other fields, you can make sure that SQL Execution will not take more than 60 seconds, but at some point the execution time increases. At present, it is acceptable.

create table `order_shipping_logs` (
`id` bigint unsigned not null auto_increment,
`Shipping_Status` tinyint unsigned not null default2commentDelivery status, 2: in delivery; 3: successful delivery; 4: failure of delivery; 5: successful cancellation; 6: canceled failure,
`SHIPPING_AT_GMT` DateTime default null commentdelivery time,
`Shipping_type` tinyint unsigned not null default1commentDelivery type, 1: order delivery; 2: Cancellation of delivery,
`operated_source` tinyint unsigned not null default2commentSource of operation, 1: regular delivery; 2: manual delivery; 3: back to pending; 4: modify/delete logistics channels; 5: modify/delete the waybill number;,
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=Order Delivery Log Form;

# 1.3 seconds
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 seconds
select
count(*) as aggregate
from
`order_shipping_logs_copy3`
FORCE INDEX (ORDER_SHIPING_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. In the performance environment, the response time of the list interface is about 30 seconds, which is not as expected. Expected to be within 10 seconds. as shown in Figure 8

8

14. Determine the SQL for the interface to execute, connect the MySQL performance environment in the local environment, and the response time of the list interface is about 10 seconds. Then look at the generated SQL in Laravel Telescope and find a SELECT `ORDER_SHIPPING_LOGS`.* FROM ORDER_SHIPPING_LOGS FORCE INDEX It takes much longer than other SQL, with a duration of 30679.38ms. Then select count(*) as aggregate from requires the use of the combinatorial index, and the select `order_shipping_logs`.* from does not need to force the combination index. as shown in Figure 9

Figure 9

15. Reference: Force Laravel 9 to use the combined index in the paging count() query, and at the same time make the actual data query not use the forced index. Due to time constraints, the implementation is suspended.

16. Reference: When the fields in WHERE contain SHIPPING_AT_GMT and SHIPPING_STATUS|SHIPPING_TYPE|OPERATED_SOURCE when the combined index is forced to be used, otherwise the combined index is not mandatory