mysql - 在 3300 万行的巨大表中优化 mysql 中的长查询

Question

查询:

SELECT users.id as uid, name, avatar, avatar_date, driver, messages.id AS mid,messages.msg, messages.removed, messages.from_anonym_id, messages.t
    o_anonym_id, (messages.date DIV 1000) AS date, from_id = 162077 as outbox, !(0 in (SELECT read_state FROM messages as msgs 
WHERE (msgs.from_id = messages.from_id or msgs.from_id = messages.user_id) and msgs.user_id = 162077 and removed = 0)) as read_state 
FROM dialog, messages, users 
WHERE messages.id = dialog.mid and ((uid1 = 162077 and users.id = uid2) or (uid2 = 162077 and users.id = uid1) ) 
ORDER BY dialog.mid DESC LIMIT 0, 101;

表结构:

mysql> desc messages;
+----------------+------------------+------+-----+---------+----------------+
| Field          | Type             | Null | Key | Default | Extra          |
+----------------+------------------+------+-----+---------+----------------+
| id             | int(11)          | NO   | PRI | NULL    | auto_increment |
| from_id        | int(11)          | NO   | MUL | NULL    |                |
| user_id        | int(11)          | NO   | MUL | NULL    |                |
| group_id       | int(11)          | NO   |     | NULL    |                |
| to_number      | varchar(30)      | NO   | MUL | NULL    |                |
| msg            | text             | NO   |     | NULL    |                |
| image          | varchar(20)      | NO   |     | NULL    |                |
| date           | bigint(20)       | NO   |     | NULL    |                |
| read_state     | tinyint(1)       | NO   |     | 0       |                |
| removed        | tinyint(1)       | NO   | MUL | NULL    |                |
| from_anonym_id | int(10) unsigned | NO   | MUL | NULL    |                |
| to_anonym_id   | int(10) unsigned | NO   | MUL | NULL    |                |
+----------------+------------------+------+-----+---------+----------------+

mysql> desc dialog;
+----------------+------------------+------+-----+---------+----------------+
| Field          | Type             | Null | Key | Default | Extra          |
+----------------+------------------+------+-----+---------+----------------+
| id             | int(11)          | NO   | PRI | NULL    | auto_increment |
| uid1           | int(11)          | NO   | MUL | NULL    |                |
| uid2           | int(11)          | NO   | MUL | NULL    |                |
| mid            | int(11)          | NO   | MUL | NULL    |                |
| from_anonym_id | int(10) unsigned | NO   | MUL | NULL    |                |
| to_anonym_id   | int(10) unsigned | NO   | MUL | NULL    |                |
+----------------+------------------+------+-----+---------+----------------+


mysql> show index from messages;
+----------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name       | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| messages |          0 | PRIMARY        |            1 | id             | A         |    42944290 |     NULL | NULL   |      | BTREE      |         |               |
| messages |          1 | user_id_2      |            1 | user_id        | A         |     2147214 |     NULL | NULL   |      | BTREE      |         |               |
| messages |          1 | user_id_2      |            2 | read_state     | A         |     2862952 |     NULL | NULL   |      | BTREE      |         |               |
| messages |          1 | user_id_2      |            3 | removed        | A         |     2862952 |     NULL | NULL   |      | BTREE      |         |               |
| messages |          1 | from_id        |            1 | from_id        | A         |      825851 |     NULL | NULL   |      | BTREE      |         |               |
| messages |          1 | from_id        |            2 | to_number      | A         |      825851 |     NULL | NULL   |      | BTREE      |         |               |
| messages |          1 | to_number      |            1 | to_number      | A         |          29 |     NULL | NULL   |      | BTREE      |         |               |
| messages |          1 | idx_user_id    |            1 | user_id        | A         |     2044966 |     NULL | NULL   |      | BTREE      |         |               |
| messages |          1 | idx_from_id    |            1 | from_id        | A         |      447336 |     NULL | NULL   |      | BTREE      |         |               |
| messages |          1 | removed        |            1 | removed        | A         |          29 |     NULL | NULL   |      | BTREE      |         |               |
| messages |          1 | from_anonym_id |            1 | from_anonym_id | A         |          29 |     NULL | NULL   |      | BTREE      |         |               |
| messages |          1 | to_anonym_id   |            1 | to_anonym_id   | A         |          29 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
12 rows in set (0.01 sec)

mysql> show index from dialog;
+--------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name       | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dialog |          0 | PRIMARY        |            1 | id             | A         |     6378161 |     NULL | NULL   |      | BTREE      |         |               |
| dialog |          1 | uid1           |            1 | uid1           | A         |      455582 |     NULL | NULL   |      | BTREE      |         |               |
| dialog |          1 | uid1           |            2 | uid2           | A         |     6378161 |     NULL | NULL   |      | BTREE      |         |               |
| dialog |          1 | uid2           |            1 | uid2           | A         |     2126053 |     NULL | NULL   |      | BTREE      |         |               |
| dialog |          1 | idx_mid        |            1 | mid            | A         |     6378161 |     NULL | NULL   |      | BTREE      |         |               |
| dialog |          1 | from_anonym_id |            1 | from_anonym_id | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
| dialog |          1 | to_anonym_id   |            1 | to_anonym_id   | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

PS请不要给我任何理论配方，只有实际的例子。提前致谢。

如果我删除这条语句

!(0 in (SELECT read_state FROM messages as msgs 
WHERE (msgs.from_id = messages.from_id or msgs.from_id = messages.user_id) and msgs.user_id = 162077 and removed = 0)) as read_state

与原始查询相比，查询效果非常好:一组 101 行(0.04 秒)

我想这是主要问题，但我需要这个字段。可能有人可以扭转这一轮并使其更快，会很高兴。

Answer 1

这是您的查询，其中固定了 join 语法并为外部查询中的表添加了表别名:

SELECT u.id as uid, name, avatar, avatar_date, driver, m.id AS mid, m.msg,
       m.removed, m.from_anonym_id, m.t
       o_anonym_id, (m.date DIV 1000) AS date, from_id = 162077 as outbox,
       !(0 in (SELECT read_state
               FROM messages m2 
               WHERE (m2.from_id = m.from_id or m2.from_id = m.user_id) and
                     m2.user_id = 162077 and removed = 0
              )
        ) as read_state 
FROM dialog d join
     messages m
     on m.id = d.mid join
     users u
     on (uid1 = 162077 and users.id = uid2) or
        (uid2 = 162077 and users.id = uid1)
ORDER BY d.mid DESC
LIMIT 0, 101;

如果查询在 select 子句中没有子查询的情况下运行良好，我建议替换它。 in 可能是一个开销很大的运算符，尤其是在 or 条件下。所以我建议将其替换为:

 (case when exists (select 1
                    from messages m2
                    where m2.user_id = 162077 and m2.removed = 0 and
                          m2.from_id = m.from_id and m2.read_state = 0
                   )
       then 0
       when exists (select 1
                    from messages m2
                    where m2.user_id = 162077 and m2.removed = 0 and
                          m2.from_id = m.user_id and m2.read_state = 0
                   )
       then 0
       else 1
   end)

并且，您需要一个关于messages(from_id, user_id, removed, read_state) 的索引。