mysql - SQL查询优化与调试

Question

我正在尝试优化 mysql 数据库中的 sql 查询。尝试了不同的重写方式，添加/删除索引，但似乎没有任何方法可以减少负载。也许我错过了什么。查询:

select co.country_name as state, ci.city_name as city, ci.city_id, ci.country_id,
                        count(l.id) as num  
                        FROM cities ci 
                        INNER JOIN countries co ON (ci.country_id = co.country_id)
                        INNER JOIN dancers l ON (l.city_id = ci.city_id AND l.closed = 0 AND l.approved = 1 )
                        WHERE 1 AND ci.main=1                   
                        GROUP BY ci.city_id
                        ORDER BY city 

持续时间:2.01 秒 - 2.20 秒优化查询:

    select co.country_name as state, ci.city_name as city, ci.city_id, ci.country_id, count(l.id) as num from 
(select ci1.city_name, ci1.city_id, ci1.country_id from cities ci1 
where ci1.main=1) as ci 
INNER JOIN countries co ON (ci.country_id = co.country_id) 
INNER JOIN dancers l ON (l.city_id = ci.city_id AND l.closed = 0 AND l.approved = 1 ) GROUP BY ci.city_id ORDER BY city

持续时间:0.82 秒 - 0.90 秒

但我觉得这个查询可以进一步优化，但不知道如何优化它。有3张 table

Table 1 : countries ( country_id, country_name)
Table 2 : cities ( city_id, city_name, main, country_id)
Table 3 : dancers ( id, country_id, city_id, closed, approved) 

我正在尝试获取所有具有 main=1 的城市，并为每个城市计算所有进入这些城市的配置文件，并与国家/地区一起获取 country_name。

欢迎任何想法，谢谢。

稍后编辑:- 第一个查询解释

+----+-------------+-------+-------------+---------------------------------------------------------------------+-----------------+---------+------------------+-------+--------------------------------------------------------------------------------+
| id | select_type | table |    type     |                            possible_keys                            |       key       | key_len |       ref        | rows  |                                     Extra                                      |
+----+-------------+-------+-------------+---------------------------------------------------------------------+-----------------+---------+------------------+-------+--------------------------------------------------------------------------------+
|  1 | SIMPLE      | l     | index_merge | city_id,closed,approved,city_id_2                                   | closed,approved |     1,2 | NULL             | 75340 | Using intersect(closed,approved); Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | ci    | eq_ref      | PRIMARY,state_id_2,state_id,city_name,lat,city_name_shorter,city_id | PRIMARY         |       4 | db.l.city_id     |     1 | Using where                                                                    |
|  1 | SIMPLE      | co    | eq_ref      | PRIMARY                                                             | PRIMARY         |       4 | db.ci.country_id |     1 | Using where                                                                    |
+----+-------------+-------+-------------+---------------------------------------------------------------------+-----------------+---------+------------------+-------+--------------------------------------------------------------------------------+

第二个查询解释:

+----+-------------+------------+------+-----------------------------------+-------------+---------+------------------+-------+------------------------------------+
| id | select_type |   table    | type |           possible_keys           |     key     | key_len |       ref        | rows  |               Extra                |
+----+-------------+------------+------+-----------------------------------+-------------+---------+------------------+-------+------------------------------------+
|  1 | PRIMARY     | co         | ALL  | PRIMARY                           | NULL        | NULL    | NULL             |    51 | Using temporary; Using filesort    |
|  1 | PRIMARY     | <derived2> | ref  | <auto_key1>                       | <auto_key1> | 4       | db.co.country_id |   176 | Using where                        |
|  1 | PRIMARY     | l          | ref  | city_id,closed,approved,city_id_2 | city_id_2   | 4       | ci.city_id       |    44 | Using index condition; Using where |
|  2 | DERIVED     | ci1        | ALL  | NULL                              | NULL        | NULL    | NULL             | 11765 | Using where                        |
+----+-------------+------------+------+-----------------------------------+-------------+---------+------------------+-------+------------------------------------+

@used_by_already 查询说明:

+----+-------------+------------+-------------+-----------------------------------+-----------------+---------+------------------+-------+--------------------------------------------------------------------------------+
| id | select_type |   table    |    type     |           possible_keys           |       key       | key_len |       ref        | rows  |                                     Extra                                      |
+----+-------------+------------+-------------+-----------------------------------+-----------------+---------+------------------+-------+--------------------------------------------------------------------------------+
|  1 | PRIMARY     | co         | ALL         | PRIMARY                           | NULL            | NULL    | NULL             |    51 | NULL                                                                           |
|  1 | PRIMARY     | <derived2> | ref         | <auto_key0>                       | <auto_key0>     | 4       | db.co.country_id |   565 | Using where                                                                    |
|  2 | DERIVED     | l          | index_merge | city_id,closed,approved,city_id_2 | closed,approved | 1,2     | NULL             | 75341 | Using intersect(closed,approved); Using where; Using temporary; Using filesort |
|  2 | DERIVED     | ci1        | eq_ref      | PRIMARY,state_id_2,city_id        | PRIMARY         | 4       | db.l.city_id     |     1 | Using where                                                                    |
+----+-------------+------------+-------------+-----------------------------------+-----------------+---------+------------------+-------+--------------------------------------------------------------------------------+

Answer 1

我建议你试试这个:

SELECT
      co.country_name AS state
    , ci.city_name    AS city
    , ci.city_id
    , ci.country_id
    , ci.num
FROM (
      SELECT
            ci1.city_id
          , ci1.city_name
          , ci1.country_id
          , COUNT(l.id) AS num
      FROM cities ci1
      INNER JOIN dancers l ON l.city_id = ci1.city_id
            AND l.closed = 0
            AND l.approved = 1
      WHERE ci1.main = 1
      GROUP BY
            ci1.city_id
          , ci1.city_name
          , ci1.country_id
      ) AS ci
INNER JOIN countries co ON ci.country_id = co.country_id
;

并且如果需要，您可以提供解释计划输出以供进一步分析。在进行优化时，了解存在哪些索引并让他解释计划是必不可少的。

另外，MySQL 确实允许非标准的 GROUP BY 语法(其中只有选择列表中的一个或某些列包含在 group by 子句下)。在最新版本的 MySQL 中，GROUP BY 的默认行为已更改为 SQL 标准语法(其中选择列表中的所有“非聚合”列都必须包含在 group by 子句下)。虽然您现有的查询使用非标准分组依据语法，但此处提供的查询不符合要求。