原文鏈接：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的知識的人。