我能做些什么来优化这个查询？

Question

我有一个MySQL查询，它可以跨三个表进行内部连接。以下是这三个表中每个表的定义。

stitching_output_oc

-- auto-generated definition
create table stitching_output_oc
(
    output_oc_unique_id  int auto_increment
        primary key,
    batches_unique_id_fk int                                not null,
    oc_number            char(22)                           not null,
    product_code         char(10)                           null,
    product_description  char(90)                           null,
    colour_code          char(10)                           not null,
    colour_description   char(60)                           null,
    creation_stamp       datetime default CURRENT_TIMESTAMP null,
    update_stamp         datetime                           null on update CURRENT_TIMESTAMP,
    constraint batch_output_unique
        unique (batches_unique_id_fk, oc_number, product_code, colour_code),
    constraint stitching_dispatch_OC_fk
        foreign key (batches_unique_id_fk) references stitching_batches (stitching_batches_unique_id)
            on delete cascade
);

stitching_output_size

-- auto-generated definition
create table stitching_output_size
(
    output_size_unique_id  int auto_increment
        primary key,
    output_oc_unique_id_fk int                                not null,
    size                   char(10)                           not null,
    output_quantity        decimal  default 0                 null,
    fit_type               char(50)                           null,
    creation_stamp         datetime default CURRENT_TIMESTAMP null,
    update_stamp           datetime                           null on update CURRENT_TIMESTAMP,
    constraint stitching_output_size_fk
        foreign key (output_oc_unique_id_fk) references stitching_output_oc (output_oc_unique_id)
            on delete cascade
);

stitching_batches

-- auto-generated definition
create table stitching_batches
(
    stitching_batches_unique_id int auto_increment
        primary key,
    batch_number                char(10)                                              not null,
    location                    char(10)                                              not null,
    status                      enum ('active', 'inactive') default 'active'          not null,
    work_start_time             time                        default '00:00:00'        not null,
    work_end_time               time                        default '00:00:00'        not null,
    break_start_time            time                        default '00:00:00'        not null,
    break_end_time              time                        default '00:00:00'        not null,
    creation_stamp              datetime                    default CURRENT_TIMESTAMP null,
    update_stamp                datetime                                              null on update CURRENT_TIMESTAMP,
    constraint batch_number
        unique (batch_number, location)
);

查询如下：

EXPLAIN ANALYZE
Select stitchingOC.oc_number as ocNumber, stitchingOC.product_code as productCode,
 DATE_FORMAT(stitchingSize.creation_stamp, '%Y-%m-%d') as date,
 DATE_FORMAT(stitchingSize.creation_stamp, "%H:%i") as hourMinutes,
 stitchingBatch.batch_number as batchNumber,
 sum(stitchingSize.output_quantity)as outputQuantity
 from stitching_output_size stitchingSize
 inner join stitching_output_oc stitchingOC on stitchingOC.output_oc_unique_id = stitchingSize.output_oc_unique_id_fk
 inner join stitching_batches stitchingBatch on stitchingBatch.stitching_batches_unique_id = stitchingOC.batches_unique_id_fk
 where IF('' IS NULL || '' = 'null' || TRIM('')  = '', stitchingBatch.batch_number like ('%'), stitchingBatch.batch_number like('')) and
 DATE_FORMAT(stitchingSize.creation_stamp, '%Y%m%d') >= '20220420'  and
 DATE_FORMAT(stitchingSize.creation_stamp, '%Y%m%d') <= IF('20220420' IS NULL || '20220420' = 'null' || TRIM('20220420')  = '', '20220420', '20220420')
 and stitchingBatch.location = 'IDU1'
 group by stitchingBatch.batch_number, DATE_FORMAT(stitchingSize.creation_stamp, '%Y-%m-%d'), DATE_FORMAT(stitchingSize.creation_stamp, "%H:%i"), stitchingOC.oc_number, stitchingOC.product_code
 order by stitchingBatch.batch_number, DATE_FORMAT(stitchingSize.creation_stamp, '%Y-%m-%d'), DATE_FORMAT(stitchingSize.creation_stamp, "%H:%i"), stitchingOC.oc_number, stitchingOC.product_code desc;

这是EXPLAIN ANALYZE的输出

"-> Sort: stitchingBatch.batch_number, `date`, hourMinutes, stitchingOC.oc_number, stitchingOC.product_code DESC  (actual time=172.412..172.429 rows=21 loops=1)
    -> Table scan on <temporary>  (actual time=0.003..0.023 rows=21 loops=1)
        -> Aggregate using temporary table  (actual time=172.296..172.343 rows=21 loops=1)
            -> Nested loop inner join  (cost=2730.96 rows=7601) (actual time=26.777..171.803 rows=122 loops=1)
                -> Nested loop inner join  (cost=70.50 rows=16) (actual time=0.067..1.909 rows=153 loops=1)
                    -> Index scan on stitchingOC using batch_output_unique  (cost=16.25 rows=155) (actual time=0.042..0.213 rows=155 loops=1)
                    -> Filter: ((stitchingBatch.location = 'IDU1') and (0 <> if(<cache>((true)),(stitchingBatch.batch_number like '%'),(stitchingBatch.batch_number like ''))))  (cost=0.25 rows=0) (actual time=0.005..0.007 rows=1 loops=155)
                        -> Single-row index lookup on stitchingBatch using PRIMARY (stitching_batches_unique_id=stitchingOC.batches_unique_id_fk)  (cost=0.25 rows=1) (actual time=0.002..0.003 rows=1 loops=155)
                -> Filter: ((date_format(stitchingSize.creation_stamp,'%Y%m%d') >= '20220420') and (date_format(stitchingSize.creation_stamp,'%Y%m%d') <= <cache>(if((false),'20220420','20220420'))))  (cost=125.77 rows=490) (actual time=1.105..1.107 rows=1 loops=153)
                    -> Index lookup on stitchingSize using stitching_output_size_fk (output_oc_unique_id_fk=stitchingOC.output_oc_unique_id)  (cost=125.77 rows=490) (actual time=0.074..0.704 rows=395 loops=153)
"

到目前为止我尝试过的事情：

1. 从SQL查询中删除IF条件。我可以在代码中处理这种处理，因为我有足够的处理能力，但是SQL查询是目前的瓶颈。但是，删除它对执行

并没有多大影响。

1. 删除order子句，以便我可以在代码中处理它。这会减少一些执行时间，因为查询计划中的排序会被删除，这是其中的一大部分。

但是，我想弄清楚这里是否有更明显的东西--比如在其中一个表上定义一个覆盖指数？不过，我很困惑，因为瓶子的脖子似乎不是内部连接，而是临时表的创造，我不知道如何避免。

注意事项

我知道时间存储在stitching_batches表中的格式很差，但这只是我们现在必须面对的问题。

Answer 1

要做的最快的事情是查看如何使用索引来提高WHERE子句中条件检查的速度。

例如，在WHERE子句中使用stitchingBatch.location = 'IDU1'作为条件，因此在stitchingBatch.location上添加一个索引。

不幸的是，您的大部分WHERE都是无法使用索引的条件内容。

这里的条件是：

IF('' IS NULL || '' = 'null' || TRIM('')  = '',
 stitchingBatch.batch_number like ('%'), stitchingBatch.batch_number like(''))

将无法使用任何索引。

检查的目的是什么，比如'' IS NULL和'' = 'null'，它们总是会变成假的，而TRIM('') = ''则永远是真的？把这一切都处理掉。

日期筛选可以在stitchingSize.creation_stamp上使用索引，但必须更改WHERE子句。与其将日期类型转换为字符串，不如直接使用日期。所以而不是

DATE_FORMAT(stitchingSize.creation_stamp, '%Y%m%d') >= '20220420'

您可以使用

stitchingSize.creation_stamp >= STR_TO_DATE('20220420, '%Y%m%d')

原始版本将每行中的邮票转换为字符串，然后将其与另一个字符串进行比较。它必须对表中的每一行执行一次此操作。我的版本只将字符串转换为日期一次，然后本地比较日期。如果您在creation_stamp上有一个索引，那么MySQL只需查看索引中应该检查的行。

https://use-the-index-luke.com很好地介绍了如何使用索引。