【译】MySQL 性能查询

原文链接：https://blog.mclaughlinsoftware.com/2021/12/09/mysql-query-performance/

原文作者：maclochlainn

在MySQL视图这一章中，我用两种方式编写了查询来介绍SQL调优的思想。 这是介绍JSON类型之前的最后一个主题。

在解释了如何使用EXPLAIN语法之后，我给了学生这个基于Sakila示例数据库的查询。 查询只使用内部连接，根据经验，内部连接通常比子查询更快、更高效。

SELECT   ctry.country AS country_name
,        SUM(p.amount) AS tot_payments
FROM     city c INNER JOIN address a
ON       c.city_id = a.city_id INNER JOIN customer cus
ON       a.address_id = cus.address_id INNER JOIN payment p
ON       cus.customer_id = p.customer_id INNER JOIN country ctry
ON       c.country_id = ctry.country_id
GROUP BY ctry.country;

它生成了以下表格式的执行计划输出:

+----+-------------+-------+------------+--------+---------------------------+--------------------+---------+------------------------+------+----------+------------------------------+
| id | select_type | table | partitions | type   | possible_keys             | key                | key_len | ref                    | rows | filtered | Extra                        |
+----+-------------+-------+------------+--------+---------------------------+--------------------+---------+------------------------+------+----------+------------------------------+
|  1 | SIMPLE      | cus   | NULL       | index  | PRIMARY,idx_fk_address_id | idx_fk_address_id  | 2       | NULL                   |  599 |   100.00 | Using index; Using temporary |
|  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY,idx_fk_city_id    | PRIMARY            | 2       | sakila.cus.address_id  |    1 |   100.00 | NULL                         |
|  1 | SIMPLE      | c     | NULL       | eq_ref | PRIMARY,idx_fk_country_id | PRIMARY            | 2       | sakila.a.city_id       |    1 |   100.00 | NULL                         |
|  1 | SIMPLE      | ctry  | NULL       | eq_ref | PRIMARY                   | PRIMARY            | 2       | sakila.c.country_id    |    1 |   100.00 | NULL                         |
|  1 | SIMPLE      | p     | NULL       | ref    | idx_fk_customer_id        | idx_fk_customer_id | 2       | sakila.cus.customer_id |   26 |   100.00 | NULL                         |
+----+-------------+-------+------------+--------+---------------------------+--------------------+---------+------------------------+------+----------+------------------------------+
5 rows in set, 1 warning (0.02 sec)

然后，我使用MySQL工作台生成以下可视化执行计划：

然后，我将其与在SELECT-list中使用相关子查询的重构查询版本进行比较。 这个例子来自于Alan Beaulieu的《学习SQL，第三版》的附录B。

SELECT ctry.country
,      (SELECT   SUM(p.amount)
        FROM     city c INNER JOIN address a
        ON       c.city_id = a.city_id INNER JOIN customer cus
        ON       a.address_id = cus.address_id INNER JOIN payment p
        ON       cus.customer_id = p.customer_id
        WHERE    c.country_id = ctry.country_id) AS tot_payments
FROM   country ctry;

它生成了以下表格执行计划输出：

+----+--------------------+-------+------------+------+---------------------------+--------------------+---------+------------------------+------+----------+-------------+
| id | select_type        | table | partitions | type | possible_keys             | key                | key_len | ref                    | rows | filtered | Extra       |
+----+--------------------+-------+------------+------+---------------------------+--------------------+---------+------------------------+------+----------+-------------+
|  1 | PRIMARY            | ctry  | NULL       | ALL  | NULL                      | NULL               | NULL    | NULL                   |  109 |   100.00 | NULL        |
|  2 | DEPENDENT SUBQUERY | c     | NULL       | ref  | PRIMARY,idx_fk_country_id | idx_fk_country_id  | 2       | sakila.ctry.country_id |    5 |   100.00 | Using index |
|  2 | DEPENDENT SUBQUERY | a     | NULL       | ref  | PRIMARY,idx_fk_city_id    | idx_fk_city_id     | 2       | sakila.c.city_id       |    1 |   100.00 | Using index |
|  2 | DEPENDENT SUBQUERY | cus   | NULL       | ref  | PRIMARY,idx_fk_address_id | idx_fk_address_id  | 2       | sakila.a.address_id    |    1 |   100.00 | Using index |
|  2 | DEPENDENT SUBQUERY | p     | NULL       | ref  | idx_fk_customer_id        | idx_fk_customer_id | 2       | sakila.cus.customer_id |   26 |   100.00 | NULL        |
+----+--------------------+-------+------------+------+---------------------------+--------------------+---------+------------------------+------+----------+-------------+
5 rows in set, 2 warnings (0.00 sec)

并且，MySQL工作台生成了以下可视化执行计划：

对于经验丰富的人来说，表格执行计划可以识别出性能更好的查询，但是可视化执行计划更适合SQL调优新手。

第二个查询性能最好，因为它通过最好地利用索引读取最少的数据。 和往常一样，我希望这些例子能够帮助那些希望学习更多关于MySQL的知识的人。