mysql - 为涉及 join、group/order-by、union 等的 mysql 查询在表上创建索引

Question

下面是在 mysql 进程列表中有 10 个 "copying to tmp table" 状态的慢速查询的解释输出。

explain SELECT distinct
    (radgroupreply.groupname),
    count(distinct (radusergroup.username)) AS users
FROM
    radgroupreply
        LEFT JOIN
    radusergroup ON radgroupreply.groupname = radusergroup.groupname
WHERE
    (radgroupreply.groupname NOT LIKE 'FB-%' AND radgroupreply.groupname NOT LIKE '%Dropped%')
GROUP BY radgroupreply.groupname 
UNION SELECT distinct
    (radgroupcheck.groupname),
    count(distinct (radusergroup.username))
FROM
    radgroupcheck
        LEFT JOIN
    radusergroup ON radgroupcheck.groupname = radusergroup.groupname
WHERE
    (radgroupcheck.groupname NOT LIKE 'FB-%' AND radgroupcheck.groupname NOT LIKE '%Dropped%')
GROUP BY radgroupcheck.groupname
ORDER BY groupname asc;

+----+--------------+---------------+------+---------------+------+---------+------+-------+----------------------------------------------+
| id | select_type  | table         | type | possible_keys | key  | key_len | ref  | rows  | Extra                                        |
+----+--------------+---------------+------+---------------+------+---------+------+-------+----------------------------------------------+
|  1 | PRIMARY      | radgroupreply | ALL  | NULL          | NULL | NULL    | NULL |   456 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY      | radusergroup  | ALL  | NULL          | NULL | NULL    | NULL | 10261 |                                              |
|  2 | UNION        | radgroupcheck | ALL  | NULL          | NULL | NULL    | NULL |   167 | Using where; Using temporary; Using filesort |
|  2 | UNION        | radusergroup  | ALL  | NULL          | NULL | NULL    | NULL | 10261 |                                              |
|NULL| UNION RESULT | <union1,2>    | ALL  | NULL          | NULL | NULL    | NULL |  NULL | Using filesort                               |
+----+--------------+---------------+------+---------------+------+---------+------+-------+----------------------------------------------+
5 rows in set (0.00 sec)

我无法理解这个创建复合/单一索引和优化的查询，因为它有多个连接、分组和类似操作。

Answer 1

从以下三个观察开始。

1. select distinct 是不必要的(group by 负责)。
2. left join 是不必要的(where 子句将它们变成内部联接)。
3. UNION 应该是UNION ALL。我怀疑您是否真的想承担删除重复项的开销。

因此，您可以将查询编写为:

SELECT rr.groupname, count(distinct rg.username) AS users  
FROM radgroupreply rr JOIN
     radusergroup rg
     ON rr.groupname = rg.groupname                                                  
WHERE rr.groupname NOT LIKE 'FB-%' AND rr.groupname NOT LIKE '%Dropped%'
GROUP BY rr.groupname 
UNION ALL
SELECT rc.groupname, count(rg.username)
FROM radgroupcheck rc JOIN
     radusergroup rg
     ON rc.groupname = rg.groupname                                 
WHERE rc.groupname NOT LIKE 'FB-%' AND rc.groupname NOT LIKE '%Dropped%'
GROUP BY rc.groupname
ORDER BY groupname asc;

此查询可以利用 radusergroup(groupname) 上的索引。我猜将使用 rc(radusergroup) 上的索引。

如果不需要，我还建议您删除 COUNT(DISTINCT) 中的 DISTINCT。