非常慢的查询，我可以做些什么来改进？

我有一个问题，这个查询是自动生成的后端框架，我正在使用。查询速度慢，导致脚本出现问题。
更新：在跟随@the impaler的回答之后，我的初始查询的问题得到了解决，执行时间从30秒到300毫秒。
唯一的问题是在我的where子句中添加了一些额外的条件之后，执行时间又增加到了28s-32s。
旧查询：

SELECT `growthservice_growthserviceaccountdata`.`id`, `growthservice_growthserviceaccountdata`.`username`, 
   `growthservice_growthserviceaccountdata`.`name`, `growthservice_growthserviceaccountdata`.`bio`, 
   `growthservice_growthserviceaccountdata`.`avatar`, `growthservice_growthserviceaccountdata`.`language`, 
   `growthservice_growthserviceaccountdata`.`gender`, `growthservice_growthserviceaccountdata`.`follower_count`,
   `growthservice_growthserviceaccountdata`.`following_count`, `growthservice_growthserviceaccountdata`.`like_count`, 
   `growthservice_growthserviceaccountdata`.`post_count`, `growthservice_growthserviceaccountdata`.`is_private`, 
   `growthservice_growthserviceaccountdata`.`is_business`, `growthservice_growthserviceaccountdata`.`is_verified`, 
   `growthservice_growthserviceaccountdata`.`is_fetched`, `growthservice_growthserviceaccountdata`.`created_date`, 
   `growthservice_growthserviceaccountdata`.`updated_date` 
FROM `growthservice_growthserviceaccountdata` 
INNER JOIN `growthservice_growthservicerelationdata` 
ON (`growthservice_growthserviceaccountdata`.`id` = `growthservice_growthservicerelationdata`.`account_id`) 
WHERE (
    `growthservice_growthservicerelationdata`.`source_id` = 6812397029810258950 
    AND NOT (`growthservice_growthserviceaccountdata`.`id` IN (
        SELECT U0.`subject_id` AS Col1 FROM `growthservice_log` U0 
        WHERE (U0.`account_id` = 6570863662218543109 AND U0.`action` = 'LIKE' AND U0.`subject_type` = 'USER')
        )
        )
        ) LIMIT 55;

新建查询

SELECT `growthservice_growthserviceaccountdata`.`id`, `growthservice_growthserviceaccountdata`.`username`, 
   `growthservice_growthserviceaccountdata`.`name`, `growthservice_growthserviceaccountdata`.`bio`, 
   `growthservice_growthserviceaccountdata`.`avatar`, `growthservice_growthserviceaccountdata`.`language`, 
   `growthservice_growthserviceaccountdata`.`gender`, `growthservice_growthserviceaccountdata`.`follower_count`,
   `growthservice_growthserviceaccountdata`.`following_count`, `growthservice_growthserviceaccountdata`.`like_count`, 
   `growthservice_growthserviceaccountdata`.`post_count`, `growthservice_growthserviceaccountdata`.`is_private`, 
   `growthservice_growthserviceaccountdata`.`is_business`, `growthservice_growthserviceaccountdata`.`is_verified`, 
   `growthservice_growthserviceaccountdata`.`is_fetched`, `growthservice_growthserviceaccountdata`.`created_date`, 
   `growthservice_growthserviceaccountdata`.`updated_date` 
FROM `growthservice_growthserviceaccountdata` 
INNER JOIN `growthservice_growthservicerelationdata` 
ON (`growthservice_growthserviceaccountdata`.`id` = `growthservice_growthservicerelationdata`.`account_id`) 
WHERE (
    `growthservice_growthservicerelationdata`.`source_id` = 6812397029810258950 
    AND `growthservice_growthserviceaccountdata`.`following_count` >= 30 
    AND `growthservice_growthserviceaccountdata`.`follower_count` >= 10 
    AND NOT (`growthservice_growthserviceaccountdata`.`username` LIKE BINARY '%user%') 
    AND NOT (`growthservice_growthserviceaccountdata`.`id` IN (
        SELECT U0.`subject_id` AS Col1 FROM `growthservice_log` U0 
        WHERE (U0.`account_id` = 6570863662218543109 AND U0.`action` = 'LIKE' AND U0.`subject_type` = 'USER')
        )
        )
        ) 
ORDER BY `growthservice_growthserviceaccountdata`.`created_date` DESC
LIMIT 55;

编辑查询以显示表别名与长名称的可读性

SELECT 
            gsad.id, 
            gsad.username,
            gsad.name,
            gsad.bio,
            gsad.avatar,
            gsad.`language`,
            gsad.gender, 
            gsad.follower_count,
            gsad.following_count, 
            gsad.like_count,
            gsad.post_count, 
            gsad.is_private, 
            gsad.is_business, 
            gsad.is_verified,
            gsad.is_fetched, 
            gsad.created_date,
            gsad.updated_date 
        FROM
            growthservice_growthserviceaccountdata gsad
                INNER JOIN growthservice_growthservicerelationdata gsrd
                    ON sad.id = gsrd.account_id
        WHERE 
                gsrd.source_id = 6812397029810258950
            AND sad.following_count >= 30
            AND sad.follower_count >= 10 
            AND NOT sad.username LIKE BINARY '%user%'
            AND NOT sad.id IN ( SELECT U0.subject_id AS Col1 
                                    FROM growthservice_log U0
                                    WHERE (U0.account_id = 6570863662218543109 
                                        AND U0.action = 'LIKE' 
                                        AND U0.subject_type = 'USER') 
                            )
        ORDER BY 
            sad.created_date DESC
        LIMIT 55;

The old EXPLAIN output is:

    | id | select_type    

| table                                   | partitions | type   | possible_keys                                                                                                                                     | key                                                          | key_len | ref                                                                    | rows | filtered | Extra       |
|  1 | PRIMARY            | growthservice_growthservicerelationdata | NULL       | ref    | growthservice_growth_account_id_93684974_fk_growthser,growthservice_growth_source_id_86fb3471_fk_growthser                                        | growthservice_growth_source_id_86fb3471_fk_growthser         | 8       | const                                                                  | 5741 |   100.00 | Using where |
|  1 | PRIMARY            | growthservice_growthserviceaccountdata  | NULL       | eq_ref | PRIMARY,follower_count,following_count                                                                                                            | PRIMARY                                                      | 8       | app.growthservice_growthservicerelationdata.account_id                 | 1 |    22.22 | Using where |
|  2 | DEPENDENT SUBQUERY | U0                                      | NULL       | ref    | growthservice_log_account_id_ac95df3e_fk_accounts_account_id,growthservice_log_action_45cfd84b,growthservice_log_subject_id_17399893,subject_type | growthservice_log_account_id_ac95df3e_fk_accounts_account_id | 8       | const                                                                  | 2822 |     2.50 | Using where |

新的解释输出是：

| id | select_type        | table                                   | partitions | type   | possible_keys                                                                                              | key                                                  | key_len | ref                                                                    | rows | filtered | Extra       
|  1 | PRIMARY            | growthservice_growthservicerelationdata | NULL       | ref    | growthservice_growth_account_id_93684974_fk_growthser,growthservice_growth_source_id_86fb3471_fk_growthser | growthservice_growth_source_id_86fb3471_fk_growthser | 8       | const                                                                  | 5741 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY            | growthservice_growthserviceaccountdata  | NULL       | eq_ref | PRIMARY,follower_count,following_count,ix1                                                                 | PRIMARY                                              | 8       | app.growthservice_growthservicerelationdata.account_id |    1 |    25.00 | Using where
|  2 | DEPENDENT SUBQUERY | U0                                      | NULL       | ref    | growthservice_log_action_45cfd84b,growthservice_log_subject_id_17399893,subject_type,ix1                   | ix1                                                  | 1612    | const,const,const                                                      | 2564 |    10.00 | Using where

查询平均耗时30秒。
growthservice\u growthserviceaccountdata表有700万行。
growthservice\u growthservicerelationdata表有700万行。
表growthservice\u log有15万行。
我已经在where子句中筛选的所有字段上有了单列索引。
到底是什么会减慢查询速度，我可以做些什么来修复它？
一件非常奇怪的事情是，如果我从where子句中删除not in，查询只在300毫秒内执行，而不是30秒。

对于第一个变体，请尝试更改以下内容：

AND  NOT (a.`id` IN (
                        SELECT  U0.`subject_id` AS Col1
                            FROM  `growthservice_log` U0
                            WHERE  (U0.`account_id` = 6570863662218543109
                               AND  U0.`action` = 'LIKE'
                               AND  U0.`subject_type` = 'USER') ) )

到

AND NOT EXISTS ( SELECT 1
           FROM growthservice_log AS U0
           WHERE  U0.`account_id` = 6570863662218543109
             AND  U0.`action` = 'LIKE'
             AND  U0.`subject_type` = 'USER'
             AND  U0.subject_cell = a.`id` )

并拥有

U0:  (subject_type, action, account_id, subject_id)  -- The order does not matter
r:   (source_id, account_id)  -- In this order

我已经在where子句中筛选的所有字段上有了单列索引。
一个常见的新手错误。”复合索引（如上）有时比单列索引好得多。
没有一个 ORDER BY , LIMIT 是免费给你随机行。
第二个查询。。。
这是相当低效的：

NOT sad.username LIKE BINARY '%user%'

目的是什么？
为了 a ，可以使用其中之一：

INDEX(follower_count),
INDEX(following_count)

另一个尝试是移动 NOT IN （或 NOT EXISTS )从 WHERE 条款 HAVING 条款。如果这不能像删除它那样提高速度，那么我们可以讨论一个子查询：

SELECT *
    FROM (( SELECT ... all of the query but the NOT IN ...))
    WHERE NOT EXISTS ( ... )
    ORDER BY ...;   -- Yes, repeated

警告：你可能只得到54行，但也许这将是“足够好”，值得加速？

非常慢的查询，我可以做些什么来改进？

2条答案

相关问题

热门标签

最新问答