MySQL性能查询 [英] Mysql Performance Query

问题描述

首先，感谢您抽出宝贵时间阅读本文档.我正在尝试回答有关mysql的一些问题，但我只停留在一些问题上，这就是为什么我决定在可能的情况下寻求他人的帮助.或至少指向我正确的方向.任何帮助将不胜感激.

First of all thank you for taking time to read this. I'm trying to answer a few questions about mysql and I'm stuck on a fews and that's why I resolve to ask for somebody help if possible. Or at least to point me in the right direction. Any help will be very appreciated.

城市表包含230个国家/地区的4000个城市的人口数据.说明以下查询为何在MySQL 5.6中的性能不佳，并演示解决方案.

City table contains population data for 4000 cities across 230 countries. Explain why the following query performs poorly in MySQL 5.6, and demonstrate solutions.

SELECT `ID`, `CountryCode`, `Name`, `District`, `Population`
FROM `City`
WHERE (`CountryCode`, `Population`) IN (
    SELECT `CountryCode`, MAX(`Population`) AS `Population`
    FROM `City`
    GROUP BY `CountryCode`
);

推荐答案

您会认为此查询只执行一次子查询，保留结果，然后将其与外部查询中的行进行比较.但是MySQL并非如此. MySQL在优化程序的智能性方面存在差距，因此它将子查询视为依赖子查询，并针对外部查询的每个不同值重新执行该子查询.

You would think this query only executes the subquery once, keeps the result, and then compares it to rows in the outer query. But that's not the case with MySQL. MySQL has a gap in the intelligence of its optimizer, so it treats the subquery as a dependent subquery and re-executes it for each distinct value of the outer query.

要解决此问题，请将子查询作为派生表移入FROM子句.它将执行一次子查询，并将结果保留为内部临时表.然后加入表的另一个实例.

To fix this, move the subquery into the FROM clause as a derived table. It will execute the subquery once, and keep the result as an internal temporary table. Then join to the other instance of the table.

SELECT `ID`, `CountryCode`, `Name`, `District`, `Population`
FROM `City`
JOIN (
    SELECT `CountryCode`, MAX(`Population`) AS `Population`
    FROM `City`
    GROUP BY `CountryCode`
) AS _max USING (`CountryCode`, `Population`);

此外，您还应该按该顺序在两列(CountryCode,Population)上的城市索引，以便GROUP BY查询可以高效运行.

Also you should have an index on City over the two columns (CountryCode,Population) in that order, so the GROUP BY query can run efficiently.

这篇关于MySQL性能查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！