MySQL慢速查询请求修复，覆盖提升速度-Java 学习之路

我在高负载应用程序中得到了请求：

SELECT posts.id as post_id, posts.uid, text, date, like_count, 
       dislike_count, comments_count, post_likes.liked, image, aspect, 
       u.name as user_name, u.avatar, u.avatar_date, u.driver, u.number as user_number, 
       u.city_id as user_city_id, u.birthday as user_birthday, u.show_birthday, u.auto_model, u.auto_color, 
       u.verified , u.gps_x as user_gps_x, u.gps_y as user_gps_y, u.map_activity, u.show_on_map 
FROM posts 
     LEFT OUTER JOIN post_likes ON post_likes.post_id = posts.id and post_likes.uid = '478831' 
     LEFT OUTER JOIN users u ON posts.uid = u.id 
WHERE posts.info = 0  AND
 ( posts.uid = 478831 OR EXISTS(SELECT friend_id 
                                FROM friends 
                                WHERE user_id = 478831 
                                  AND posts.uid = friend_id 
                                  AND confirmed = 2) 
  ) 
order by posts.id desc limit 0, 20;

执行时间在6-7秒之间 . - 绝对不好 .

EXPLAIN EXTENDED output:


*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: posts
         type: ref
possible_keys: uid,info
          key: info
      key_len: 1
          ref: const
         rows: 471277
     filtered: 100.00
        Extra: Using where

*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: post_likes
         type: ref
possible_keys: post_id
          key: post_id
      key_len: 8
          ref: anumbers.posts.id,const
         rows: 1
     filtered: 100.00
        Extra:

*************************** 3. row ***************************
           id: 1
  select_type: PRIMARY
        table: u
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: anumbers.posts.uid
         rows: 1
     filtered: 100.00
        Extra:

*************************** 4. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: friends
         type: eq_ref
possible_keys: user_id_2,user_id,friend_id,confirmed
          key: user_id_2
      key_len: 9
          ref: const,anumbers.posts.uid,const
         rows: 1
     filtered: 100.00
        Extra: Using index
4 rows in set, 2 warnings (0.00 sec)

mysql> `show index from posts;`
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| posts |          0 | PRIMARY  |            1 | id          | A         |     1351269 |     NULL | NULL   |      | BTREE      |         |               |
| posts |          1 | uid      |            1 | uid         | A         |      122842 |     NULL | NULL   |      | BTREE      |         |               |
| posts |          1 | gps_x    |            1 | gps_y       | A         |     1351269 |     NULL | NULL   |      | BTREE      |         |               |
| posts |          1 | city_id  |            1 | city_id     | A         |          20 |     NULL | NULL   |      | BTREE      |         |               |
| posts |          1 | info     |            1 | info        | A         |          20 |     NULL | NULL   |      | BTREE      |         |               |
| posts |          1 | group_id |            1 | group_id    | A         |          20 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

mysql> `show index from post_likes;`
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| post_likes |          0 | PRIMARY  |            1 | id          | A         |    10276317 |     NULL | NULL   |      | BTREE      |         |               |
| post_likes |          1 | post_id  |            1 | post_id     | A         |     3425439 |     NULL | NULL   |      | BTREE      |         |               |
| post_likes |          1 | post_id  |            2 | uid         | A         |    10276317 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

mysql> `show index from users;`
+-------+------------+-------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users |          0 | PRIMARY     |            1 | id           | A         |      497046 |     NULL | NULL   |      | BTREE      |         |               |
| users |          0 | number      |            1 | number       | A         |      497046 |     NULL | NULL   |      | BTREE      |         |               |
| users |          1 | name        |            1 | name         | A         |       99409 |     NULL | NULL   |      | BTREE      |         |               |
| users |          1 | show_phone  |            1 | show_phone   | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
| users |          1 | show_mail   |            1 | show_mail    | A         |          12 |     NULL | NULL   |      | BTREE      |         |               |
| users |          1 | show_on_map |            1 | show_on_map  | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| users |          1 | show_on_map |            2 | map_activity | A         |      497046 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

mysql> `show index from friends;`
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| friends |          0 | PRIMARY   |            1 | id          | A         |     1999813 |     NULL | NULL   |      | BTREE      |         |               |
| friends |          0 | user_id_2 |            1 | user_id     | A         |      666604 |     NULL | NULL   |      | BTREE      |         |               |
| friends |          0 | user_id_2 |            2 | friend_id   | A         |     1999813 |     NULL | NULL   |      | BTREE      |         |               |
| friends |          0 | user_id_2 |            3 | confirmed   | A         |     1999813 |     NULL | NULL   |      | BTREE      |         |               |
| friends |          1 | user_id   |            1 | user_id     | A         |      499953 |     NULL | NULL   |      | BTREE      |         |               |
| friends |          1 | friend_id |            1 | friend_id   | A         |      499953 |     NULL | NULL   |      | BTREE      |         |               |
| friends |          1 | confirmed |            1 | confirmed   | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

我做了什么（试图做）： Set FORCE KEY(uid) for post table - 在2秒内近似快速地工作 . 当然我已经尝试重写这个查询并做了这样的事情：

SELECT posts.id as post_id, posts.uid, text, date, like_count, dislike_count, comments_count, post_likes.liked, image, aspect, 
       u.name as user_name, u.avatar, u.avatar_date, u.driver, u.number as user_number, 
       u.city_id as user_city_id, u.birthday as user_birthday, u.show_birthday, u.auto_model, u.auto_color, 
       u.verified , u.gps_x as user_gps_x, u.gps_y as user_gps_y, u.map_activity, u.show_on_map 
FROM posts 
LEFT JOIN post_likes ON post_likes.post_id = posts.id and post_likes.uid = '478831' 
LEFT JOIN users u ON posts.uid = u.id LEFT JOIN friends AS f ON f.friend_id=posts.uid 
WHERE posts.info = 0  AND confirmed = 2 
group by posts.id, **posts.uid** 
order by posts.id desc limit 0, 20;

不幸的是GROUP BY posts.uid杀死了这个查询执行，因为帖子返回了很多行 .

替代简化查询（如果它可能有帮助）：

SELECT posts.id as post_id, posts.uid
FROM posts  
WHERE posts.info = 0  AND
 ( posts.uid = 478831 OR EXISTS(SELECT friend_id 
                                FROM friends 
                                WHERE user_id = 478831 
                                  AND posts.uid = friend_id 
                                  AND confirmed = 2) 
  ) 
order by posts.id desc limit 0, 20;

我需要的是 - 执行速度不到1秒的查询并输出相同的结果 . 任何帮助将不胜感激 . Thx提前 .

3 回答

与@StanislavL的解决方案类似，您可以进行连接而不是子查询： -

SELECT posts.id as post_id, posts.uid, text, date, like_count, 
       dislike_count, comments_count, post_likes.liked, image, aspect, 
       u.name as user_name, u.avatar, u.avatar_date, u.driver, u.number as user_number, 
       u.city_id as user_city_id, u.birthday as user_birthday, u.show_birthday, u.auto_model, u.auto_color, 
       u.verified , u.gps_x as user_gps_x, u.gps_y as user_gps_y, u.map_activity, u.show_on_map 
FROM posts 
     LEFT OUTER JOIN post_likes ON post_likes.post_id = posts.id and post_likes.uid = posts.uid
     LEFT OUTER JOIN users u ON posts.uid = u.id 
     LEFT OUTER JOIN friends f ON f.user_id = 478831 AND posts.uid = f.friend_id AND confirmed = 2
WHERE posts.info = 0  
AND (posts.uid = 478831 
OR f.friend_id IS NOT NULL) 
order by posts.id desc limit 0, 20;

但是，OR可能会阻止它有效地使用索引 . 为了避免这种情况，你可以做两个联合在一起的查询，第一个忽略friends表，第二个对于friends表进行INNER JOIN： -

SELECT posts.id as post_id, posts.uid, text, date, like_count, 
       dislike_count, comments_count, post_likes.liked, image, aspect, 
       u.name as user_name, u.avatar, u.avatar_date, u.driver, u.number as user_number, 
       u.city_id as user_city_id, u.birthday as user_birthday, u.show_birthday, u.auto_model, u.auto_color, 
       u.verified , u.gps_x as user_gps_x, u.gps_y as user_gps_y, u.map_activity, u.show_on_map 
FROM posts 
     LEFT OUTER JOIN post_likes ON post_likes.post_id = posts.id and post_likes.uid = posts.uid
     LEFT OUTER JOIN users u ON posts.uid = u.id 
WHERE posts.info = 0  
AND posts.uid = 478831
UNION
SELECT posts.id as post_id, posts.uid, text, date, like_count, 
       dislike_count, comments_count, post_likes.liked, image, aspect, 
       u.name as user_name, u.avatar, u.avatar_date, u.driver, u.number as user_number, 
       u.city_id as user_city_id, u.birthday as user_birthday, u.show_birthday, u.auto_model, u.auto_color, 
       u.verified , u.gps_x as user_gps_x, u.gps_y as user_gps_y, u.map_activity, u.show_on_map 
FROM posts 
     LEFT OUTER JOIN post_likes ON post_likes.post_id = posts.id and post_likes.uid = posts.uid
     LEFT OUTER JOIN users u ON posts.uid = u.id 
     INNER JOIN friends f ON f.user_id = 478831 AND posts.uid = f.friend_id AND confirmed = 2
WHERE posts.info = 0  
order by posts.id desc limit 0, 20;

都没有测试过

回复于 2024-04-26T16:33:02+08:00

您可以尝试重写在FROM部分中移动附加子查询的查询

SELECT posts.id as post_id, posts.uid, text, date, like_count, 
       dislike_count, comments_count, post_likes.liked, image, aspect, 
       u.name as user_name, u.avatar, u.avatar_date, u.driver, u.number as user_number, 
       u.city_id as user_city_id, u.birthday as user_birthday, u.show_birthday, u.auto_model, u.auto_color, 
       u.verified , u.gps_x as user_gps_x, u.gps_y as user_gps_y, u.map_activity, u.show_on_map 
FROM posts 
     LEFT OUTER JOIN post_likes ON post_likes.post_id = posts.id and post_likes.uid = '478831' 
     LEFT OUTER JOIN users u ON posts.uid = u.id 
     LEFT OUTER JOIN (SELECT friend_id 
                       FROM friends 
                      WHERE user_id = 478831 
                        AND confirmed = 2) f ON posts.uid = f.friend_id  
WHERE posts.info = 0  AND
 ( posts.uid = 478831 OR f.friend_id is not null) 
order by posts.id desc limit 0, 20;

回复于 2024-04-26T16:33:02+08:00

1
尝试添加以下索引：
```
create index post_info_uid on posts (info,uid );
create index friend_usr_confirm_id on friends (user_id, confirmed, friend_id);
```
然后优化表格：
```
optimize table posts, friends;
```
尝试“替代简化查询”，看看它是否运行得更快 . 如果没有，请发布解释计划：

EXPLAIN SELECT ... （使用"Alternative simplified query"完成）
回复于 2024-04-26T16:33:02+08:00

MySQL慢速查询请求修复，覆盖提升速度

3 回答

相关问题