MySQL：有没有什么方法可以优化多表的ORDER BY与以下DB结构？

kknvjkwl  于 2023-11-16  发布在  Mysql
关注(0)|答案(1)|浏览(94)

我有两个具有以下结构和索引的表（真实的名称已更改）：

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 DESC子句（只留下ORDER 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.我知道WHERE子句看起来很奇怪，但我的主要问题是ORDER BY优化。

mysql

1条答案

按热度按时间
gg58donl

gg58donl1#

在这个场景中，您有两个性能问题。

  1. SELECT whole-mess-of-rows ... ORDER BY something LIMIT small-number是一个众所周知的性能反模式。为了满足查询，数据库必须获取大量数据，按照特定的顺序对数据进行排序，然后丢弃其中的一小部分。有时可以使用索引来避免排序任务。
    这就是为什么省略ORDER BY子句会使查询更快的原因。它只返回前200行，无论哪一行是最先生成的。但是这些行不会很有用，因为它们的顺序是 * 不可预测的 *。你不能合理地省略ORDER BY。
    1.覆盖索引将有助于此查询的WHERE过滤器。尝试此索引：
CREATE INDEX names ON table2
   (some1_name, some2_name, some_3_name, some2_id, some1_id)

字符串
它应该能让事情更快一点。
1.如果你还在为性能而挣扎，你可以尝试一个所谓的延迟连接。首先使用一个子查询来获取你需要的行的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值和一个`date。
然后将其连接到您的表中以获取详细信息（我没有调试这个）。

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）分享  回复(0）举报  2023-11-16
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com