MySQL：有没有办法使用以下数据库结构优化多个表ORDER BY？-解网

问：

我有两个表，其结构和索引如下（为了目的更改了真实名称）：

mysql> describe table1;
+---------+---------------------+------+-----+-------------------+----------------+
| Field   | Type                | Null | Key | Default           | Extra          |
+---------+---------------------+------+-----+-------------------+----------------+
| id      | bigint(20) unsigned | NO   | PRI | NULL              | auto_increment |
| field1  | varchar(255)        | NO   | UNI | NULL              |                |
| date    | timestamp           | NO   | MUL | CURRENT_TIMESTAMP |                |
| text    | varchar(10000)      | NO   |     | NULL              |                |
| flag    | tinyint(1)          | YES  |     | 0                 |                |
+---------+---------------------+------+-----+-------------------+----------------+

mysql> show indexes from table1;
+--------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table1 |          0 | PRIMARY              |            1 | id          | A         |     1420047 |     NULL | NULL   |      | BTREE      |         |               |
| table1 |          0 | table1_field1_unique |            1 | field1      | A         |     1420047 |     NULL | NULL   |      | BTREE      |         |               |
| table1 |          1 | table1_date_idx      |            1 | date        | A         |     1420047 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

mysql> describe table2;
+------------------------+---------------------+------+-----+---------+----------------+
| Field                   | Type                | Null | Key | Default | Extra          |
+------------------------+---------------------+------+-----+---------+----------------+
| id                      | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| table1_id               | bigint(20) unsigned | NO   | MUL | NULL    |                |
| some1_id                | bigint(20) unsigned | YES  | MUL | NULL    |                |
| some1_name              | varchar(255)        | YES  | MUL | NULL    |                |
| some2_id                | bigint(20) unsigned | NO   | MUL | NULL    |                |
| some2_name              | varchar(255)        | NO   | MUL | NULL    |                |
| some3_name              | varchar(255)        | YES  | MUL | NULL    |                |
| some4_email             | varchar(255)        | YES  |     | NULL    |                |
| some4_name              | varchar(255)        | YES  | MUL | NULL    |                |
| some4_place1_gift       | varchar(255)        | YES  |     | NULL    |                |
| some4_place2_gift       | varchar(255)        | YES  |     | NULL    |                |
| some4_place3_gift       | varchar(255)        | YES  |     | NULL    |                |
+------------------------+---------------------+------+-----+---------+----------------+

mysql> show indexes from table2;
+--------------+------------+--------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name                 | Seq_in_index | Column_name| Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+--------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table2|          0 | PRIMARY                         |            1 | id         | A         |      462911 |     NULL | NULL   |      | BTREE      |         |               |
| table2|          1 | table2_table1_table1_id_foreign |            1 | table1_id  | A         |      462911 |     NULL | NULL   |      | BTREE      |         |               |
| table2|          1 | some4_name_idx                  |            1 | some4_name | A         |        5645 |     NULL | NULL   | YES  | BTREE      |         |               |
| table2|          1 | some3_name_idx                  |            1 | some3_name | A         |        3560 |     NULL | NULL   | YES  | BTREE      |         |               |
| table2|          1 | some2_id_idx                    |            1 | some2_id   | A         |         116 |     NULL | NULL   |      | BTREE      |         |               |
| table2|          1 | some1_id_idx                    |            1 | some1_id   | A         |         390 |     NULL | NULL   | YES  | BTREE      |         |               |
| table2|          1 | some1_name_idx                  |            1 | some1_name | A         |        1727 |     NULL | NULL   | YES  | BTREE      |         |               |
| table2|          1 | some2_name_idx                  |            1 | some2_name | A         |         221 |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+--------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

我有以下疑问：

SELECT
    table1.id AS table1_id,
    table1.field1,
    table1.date,
    table1.text,
    table2.id AS table2_id,
    table2.some1_id,
    table2.some1_name,
    table2.some2_id,
    table2.some2_name,
    table2.some3_name,
    table2.some4_email,
    table2.some4_name,
    table2.some4_place1_gift,
    table2.some4_place2_gift,
    table2.some4_place3_gift
FROM
    table2
INNER JOIN
    table1 ON table2.table1_id = table1.id
WHERE
    table2.some1_name = 'Some1_Name_Example'
    AND table2.some2_name = 'Some2_Name_Example'
    AND table2.some3_name = 'Some3_Name_Example'
    AND (
        table2.some2_id IN (1, 22, 975, 5981, 6127, 10861)
        OR table2.some1_id IN (2564, 4886, 12514, 12724, 13905, 15491, 16295, 18125, 22162, 20702, 20704, 21503)
        OR (table2.some2_id = 1277 AND table1.date < '2022-03-27 00:00:00')
        OR (table2.some2_id = 1678 AND table1.date < '2023-06-16 00:00:00')
        OR (table2.some2_id = 9519 AND table1.date < '2021-01-05 00:00:00')
        OR (table2.some1_id = 4648 AND table1.date < '2023-06-16 00:00:00')
    )
ORDER BY
    table1.date DESC,
    table2.id DESC
LIMIT 200;

查询时间：1.04秒这是此查询的 EXPLAIN 语句：

+----+-------------+--------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+----------------------------+--------+---------------------------------------------------------------------+
| id | select_type | table        | type   | possible_keys                                                                                       | key             | key_len | ref                  | rows   | Extra                                                               |
+----+-------------+--------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+----------------------------+--------+---------------------------------------------------------------------+
|  1 | SIMPLE      | table2       | ref    | table2_table1_id_foreign,some3_name_idx,some2_id_idx,some1_id_idx,some1_name_index,some2_name_index | some3_name_idx  | 768     | const                | 231455 | Using index condition; Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | events       | eq_ref | PRIMARY,table1_date_idx                                                                             | PRIMARY         | 8       | db.table2.table1_id  |      1 | Using where                                                         |
+----+-------------+--------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+----------------------------+--------+---------------------------------------------------------------------+

然后我删除子句（只留下）。ORDER BY table1.date DESCORDER BY table2.id DESC

查询时间：0.12秒。 EXPLAIN语句：

+----+-------------+--------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+--
| id | select_type | table        | type   | possible_keys                                                                                       | key             | key_len | ref                  | rows   | Extra        |
+----+-------------+--------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+--
|  1 | SIMPLE      | table2       | ref    | table2_table1_id_foreign,some3_name_idx,some2_id_idx,some1_id_idx,some1_name_index,some2_name_index | some3_name_idx  | 768     | const                | 231455 | Using where  |
|  1 | SIMPLE      | events       | eq_ref | PRIMARY,table1_date_idx                                                                             | PRIMARY         | 8       | db.table2.table1_id  |      1 | Using where  |
+----+-------------+--------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+---

看起来在查询执行中会产生巨大的开销。我不确定在这种情况下是否可以做任何事情来优化查询。有什么建议吗？ORDER BY table1.date DESC

P.S. 我知道子句看起来很奇怪，但我的主要问题是优化。WHEREORDER BY

mysql 查询优化 sql-order-by 数据库索引

0赞 nbk 11/11/2023

尝试将索引与 WHERE 和 ON 子句中的所有列组合在一起，而不是单个索引

答：

1赞 O. Jones 11/11/2023 #1

此方案有两个性能问题。

SELECT whole-mess-of-rows ... ORDER BY something LIMIT small-number是众所周知的性能反模式。为了满足查询，数据库必须获取大量数据，按特定顺序对其进行排序，然后丢弃除一小部分数据外的所有数据。有时可以使用索引来避免排序任务。

这就是为什么省略 ORDER BY 子句会加快查询速度的原因。它只返回前 200 行，以先生成的行为准。但是这些行不会很有用，因为它们的顺序是不可预测的。您不能合理地省略 ORDER BY。
覆盖索引将帮助 WHERE 筛选此查询。试试这个索引：
```
CREATE INDEX names ON table2
   (some1_name, some2_name, some_3_name, some2_id, some1_id)
```
它应该让事情变得更快一些。

如果您仍在为性能而苦苦挣扎，可以尝试所谓的延迟连接。首先使用子查询来获取所需行的 id 值。喜欢这个。

 SELECT
     table1.id AS table1_id,
     table2.id AS table2_id
 FROM
     table2
 INNER JOIN
     table1 ON table2.table1_id = table1.id
 WHERE
     table2.some1_name = 'Some1_Name_Example'
     AND table2.some2_name = 'Some2_Name_Example'
     AND table2.some3_name = 'Some3_Name_Example'
     AND (
         table2.some2_id IN (1, 22, 975, 5981, 6127, 10861)
         OR table2.some1_id IN (2564, 4886, 12514, 12724, 13905, 15491, 16295, 18125, 22162, 20702, 20704, 21503)
         OR (table2.some2_id = 1277 AND table1.date < '2022-03-27 00:00:00')
         OR (table2.some2_id = 1678 AND table1.date < '2023-06-16 00:00:00')
         OR (table2.some2_id = 9519 AND table1.date < '2021-01-05 00:00:00')
         OR (table2.some1_id = 4648 AND table1.date < '2023-06-16 00:00:00')
     )
 ORDER BY
     table1.date DESC,
     table2.id DESC
 LIMIT 200;

这与原始查询具有相同的排序然后丢弃问题，但排序的每一行都要小得多 - 只有几个值和一个“日期”。id

然后将其加入您的表以获取详细信息（我没有调试此内容）。

 SELECT
     table1.id AS table1_id,
     table1.field1,
     table1.date,
     table1.text,
     table2.id AS table2_id,
     table2.some1_id,
     table2.some1_name,
     table2.some2_id,
     table2.some2_name,
     table2.some3_name,
     table2.some4_email,
     table2.some4_name,
     table2.some4_place1_gift,
     table2.some4_place2_gift,
     table2.some4_place3_gift
     FROM (
         SELECT
             table1.id AS table1_id,
             table2.id AS table2_id
         FROM
             table2
         INNER JOIN
             table1 ON table2.table1_id = table1.id
         WHERE
             table2.some1_name = 'Some1_Name_Example'
             AND table2.some2_name = 'Some2_Name_Example'
             AND table2.some3_name = 'Some3_Name_Example'
             AND (
                 table2.some2_id IN (1, 22, 975, 5981, 6127, 10861)
                 OR table2.some1_id IN (2564, 4886, 12514, 12724, 13905, 15491, 16295, 18125, 22162, 20702, 20704, 21503)
                 OR (table2.some2_id = 1277 AND table1.date < '2022-03-27 00:00:00')
                 OR (table2.some2_id = 1678 AND table1.date < '2023-06-16 00:00:00')
                 OR (table2.some2_id = 9519 AND table1.date < '2021-01-05 00:00:00')
                 OR (table2.some1_id = 4648 AND table1.date < '2023-06-16 00:00:00')
             )
         ORDER BY
             table1.date DESC,
             table2.id DESC
         LIMIT 200
      ) ids
 INNER JOIN table1 ON ids.table1_id = table1.id
 INNER JOIN table2 ON ids.table2_id = table2.id AND table2.table1_id = table1.id
 ORDER BY     
    table1.date DESC,
    table2.id DESC

0赞 yuriifedorchuk 11/12/2023

谢谢伙计，延迟加入方法将时间缩短了大约 10-15%。我还提出了将字段从 to 复制/移动的可能解决方案（这实际上可以按照项目的业务逻辑进行）。这使得查询速度提高了 10 倍。但是，我仍然对索引的任何组合是否可以显着改进现有查询感兴趣。你提出的那些改进了条款，但它们并没有产生巨大的影响。datetable1table2WHERE

上一个：相同的查询 - 不同的 Azure SQL - 关闭逻辑读取 - 不同的性能

下一个：为什么 SCHEMABIND 会大幅提高性能？

MySQL：有没有办法使用以下数据库结构优化多个表ORDER BY？

MySQL: Is there any way to optimize multiple table ORDER BY with following DB structure?

评论

评论