sql - 优化内部连接

Question

我需要一些优化查询的帮助。我有一个查询运行 12 秒的时间太长，如果我能得到一些帮助来尝试优化它，我会很高兴，因为我不是 sql 大师。在这里:

SELECT   ID                                          ,
         user_login                                  ,
         user_nicename                               ,
         user_registered                             ,
         user_status                                 ,
         display_name                                ,
         t1.meta_value             AS account_type    ,
         1 t2.meta_value           AS views           ,
         GROUP_CONCAT(t4.term_id)  AS interests_skills,
         GROUP_CONCAT(t4.taxonomy) AS taxonomyComb    ,
         t4.term_id                                   ,
         t4.taxonomy
FROM     wp_users
         INNER JOIN wp_usermeta AS t1
         ON       (
                           t1.user_id = wp_users.ID
                  AND
                           (
                                    t1.meta_key   = 'account_type'
                           AND      t1.meta_value = 'individual'
                           )
                  )
         LEFT JOIN wp_usermeta AS t2
         ON       (
                           t2.user_id  = wp_users.ID
                  AND      t2.meta_key = 'views'
                  )
         LEFT JOIN wp_term_relationships AS t3
         ON       (
                           t3.object_id = (1000000+wp_users.ID)
                  )
         INNER JOIN wp_term_taxonomy AS t4
         ON       (
                           (
                                    t3.term_taxonomy_id = t4.`term_taxonomy_id`
                           AND      t4.taxonomy         = 'category'
                           AND      t4.term_id IN (396,410,411,416,142,417)
                           )
                  OR
                           (
                                    t3.term_taxonomy_id = t4.`term_taxonomy_id`
                           AND      t4.taxonomy         = 'skill'
                           AND      t4.term_id IN (461,463,464,466,490,468,470,491,473,474,475)
                           )
                  )
WHERE    t4.term_id IS NOT NULL
GROUP BY ID LIMIT 0,10

这里是解释

1 SIMPLE t4 range PRIMARY,term_id_taxonomy,taxonomy term_id_taxonomy 106 NULL 17 Using where; Using temporary; Using filesort

1 SIMPLE t1 ref user_id,meta_key meta_key 768 const 3773 Using where

1 SIMPLE wp_users eq_ref PRIMARY PRIMARY 8 jasper_gi.t1.user_id 1

1 SIMPLE t2 ref user_id,meta_key meta_key 768 const 2

1 SIMPLE t3 eq_ref PRIMARY,term_taxonomy_id PRIMARY 16 func,jasper_gi.t4.term_taxonomy_id 1 Using where; Using index

Answer 1

我喜欢您的 SQL 的布局 - 非常易于阅读。

每当我遇到这样的问题时，我都会尝试将其分解。从没有所有连接的基表开始，看看它是如何执行的 - 查看查询计划、验证结果等。

然后在每个连接中一次添加一个，直到您看到罪魁祸首。可能是几种情况的混合，可能是一些缺失的索引等。但是通过像这样系统地处理连接，您可以找到问题点并更好地了解要做什么。