正文

如何优化此查询...？

 2023-04-14  173

关键词：

【中文标题】如何优化此查询...？【英文标题】：How can I optimize this query...? 【发布时间】：2009-06-26 15:32:12 【问题描述】：

我有两张桌子，一张用于路线，一张用于机场。

Routes 包含刚刚超过 9000 行，并且我已对每一列进行了索引。机场只有 2000 行，我还为每一列编制了索引。

当我运行此查询时，最多可能需要 35 秒才能返回 300 行：

SELECT routes.* , a1.name as origin_name, a2.name as destination_name FROM routes
LEFT JOIN airports a1 ON a1.IATA = routes.origin
LEFT JOIN airports a2 ON a2.IATA = routes.destination
WHERE routes_build.carrier = "Carrier Name"

使用“DESCRIBE”运行它，我得到以下信息，但我不能 100% 确定它告诉我什么。

id | Select Type   | Table             | Type   | possible_keys        | Key            | Key_len   | ref    | rows     | Extra
--------------------------------------------------------------------------------------------------------------------------------------
1  | SIMPLE        | routes_build      | ref    | carrier,carrier_2    | carrier        | 678       | const  | 26       | Using where
--------------------------------------------------------------------------------------------------------------------------------------
1  | SIMPLE        | a1                | ALL    | NULL                 | NULL           | NULL      | NULL   | 5389     |
--------------------------------------------------------------------------------------------------------------------------------------
1  | SIMPLE        | a2                | ALL    | NULL                 | NULL           | NULL      | NULL   | 5389     |
--------------------------------------------------------------------------------------------------------------------------------------

我能想到的唯一选择是运行两个单独的查询并将它们与 PHP 连接起来，但我无法相信这样的事情会杀死 mysql 服务器。所以像往常一样，我怀疑我在做一些愚蠢的事情。 SQL 是我的第一个弱点。

【问题讨论】：

您确定您已按原样发布查询吗？您的表是一个地方的路线和另一个地方的 route_build。在执行计划方面，每个逗号都可能很重要。抱歉，我确实更改了名称以保持查询详细。现在修好了。 【参考方案1】：

就个人而言，我会首先删除左连接并用内连接替换它们，因为每条路线都必须有一个起点和终点。

【讨论】：

那么我该如何处理别名。我试过这个... SELECT routes.* , a1.name as origin_name, a2.name as destination_name FROM routes_build, airports as a1, airports as a2 WHERE a1.IATA = routes_build.origin AND a1.IATA = routes_build.destination AND routes_build.carrier = 'Ryanair' 但我没有得到任何结果。只要去掉 LEFT 这个词，你的原始 SQL 就会运行啊啊啊，谢谢。 35 秒降至 919 毫秒。完美！很快我将不得不认真阅读一本 MySQL 书籍。【参考方案2】：

它告诉您它没有使用索引来连接 airports 表。看看“行”列是如何如此巨大的，有 5000 多个？这就是它必须读取多少行才能回答您的查询。

我不知道为什么，因为您声称您已为每一列编制索引。什么是国际航空运输协会？它是独一无二的吗？我相信如果mysql认为索引效率低下它可能会忽略它。

编辑：如果 IATA 是一个唯一的字符串，也许尝试只索引它的一半？（你可以选择索引多少个字符）这可能会给mysql一个它可以使用的索引。

【讨论】：

啊，所以 IATA 是一个唯一的字符串。所以你的索引是无用的，因为你的索引与你的表有完全相同的条目数。您需要遇到一种情况，即您的索引的行数大大少于您的表，因为这样 mysql 将通过使用它来提高速度。尝试仅索引 IATA 列的一半。例如机场代码是 4 个字符，如果我记得的话，所以只有索引 2 个字符。我说的是机场表上的 IATA 列 - 抱歉没有说清楚不用担心。你实际上指出了我的数据的一个大问题，因为一些机场共享相同的 IATA 代码。有时这是一个错误，有时是 IATA 和 FAA 系统的问题。无论如何，我检查并修复了所有重复项，添加了唯一索引并将查询从 919 毫秒缩短到 425 毫秒。谢谢你。【参考方案3】：

SELECT  routes.*, a1.name as origin_name, a2.name as destination_name
FROM    routes_build
LEFT JOIN
        airports a1
ON      a1.IATA = routes_build.origin
LEFT JOIN
        airports a2
ON      a2.IATA = routes_build.destination
WHERE   routes_build.carrier = "Carrier Name"

从您的EXPLAIN PLAN 可以看出，您在airports.IATA 上没有索引。

您应该创建它以使查询快速工作。

名称还表明它应该是一个UNIQUE 索引，因为IATA 代码是唯一的。

更新：

请发布您的表格定义。发出此查询以显示它：

SHOW CREATE TABLE airports

另外我应该注意，除非您将 ft_max_word_len 设置为 MySQL 配置为 3 或更少，否则您在 IATA 上的 FULLTEXT 索引是无用的。

默认为4。

IATA 代码的长度为 3 个字符，MySQL 在默认设置下不会使用 FULLTEXT 搜索如此短的单词。

【讨论】：

Question 声称他为机场的每一列都编制了索引。但是，是的，解释看起来没有索引。那里发生了一些奇怪的事情。这很奇怪，因为我在 airports.IATA 上有一个索引，实际上我有两个，一个索引和一个全文索引。能否请您发布您的表格定义：SHOW CREATE TABLE airports；我不知道索引在 MySQL 内部是如何工作的，但全文索引可能会减慢连接速度。典型的全文索引基于词法分析，这意味着这可能类似于使用“a1.IATA LIKE '%' + routes_build.origin + '%'” @Matthew：不应该有所作为：你可以在他的解释输出中看到没有使用索引。它正在手动加载所有行【参考方案4】：

在您实施 Martin Robins 的出色建议后（即从查询中删除单词 LEFT 的每个实例），尝试在 carrier、origin 和 destination 上为 routes_build 提供复合索引。

【讨论】：

【参考方案5】：

这实际上取决于您要获取的信息。您可能不需要两次加入机场，也可能不需要使用左连接。此外，如果您可以搜索数字字段而不是文本字段，那也会加快速度。

那么你想获取什么？

【讨论】：

我正在尝试获取路线，但它是机场代码以及机场名称。我有加入，因为我需要两次获得机场名称。一次为起点，一次为目的地。

如何优化此查询...？

】如何优化此查询...？【英文标题】：HowcanIoptimizethisquery...?【发布时间】：2009-06-2615:32:12【问题描述】：我有两张桌子，一张用于路线，一张用于机场。Routes包含刚刚超过9000行，并且我已对每一列进行了索引。机场只有2000行，... 查看详情

SQL Server - 如何优化此查询？

】SQLServer-如何优化此查询？【英文标题】：SQLServer-howcanthisquerybeoptimized?【发布时间】：2013-07-2218:36:01【问题描述】：此查询平均需要4秒。它将成为存储过程中的子查询，我需要它花费一秒。这是查询：(selectcustomercampaignname+\'$\'... 查看详情

如何使用 5 个子查询优化此查询？

】如何使用5个子查询优化此查询？【英文标题】：HowcanIoptimizethisquerywith5subqueries?【发布时间】：2014-11-2019:09:40【问题描述】：SELECT(SELECTCOALESCE(SUM(t.hours),0)ASallotted_hoursFROMtasksAStWHEREt.projects_id=8ANDt.complete!=100ANDt.nameLIKE\'%Ongoin 查看详情

如何优化此查询

】如何优化此查询【英文标题】：HowtoOptimizethisQuery【发布时间】：2011-02-1918:37:38【问题描述】：我正在学习MSSQLServer2008R2，所以请原谅我的无知。这个查询需要3秒，我想在1秒内完成。该查询仅用于测试目的，实际上我会加入不... 查看详情

如何优化此 SQL Server 查询 - 多个子查询

】如何优化此SQLServer查询-多个子查询【英文标题】：HowtooptimizethisSQLServerquery-Multiplesubqueries【发布时间】：2020-07-2422:41:07【问题描述】：我在SQLServer中有这个带有4个子查询的查询，我正在寻找优化它的方法：DECLARE@INICIODATEDECLARE@... 查看详情

如何在 Postgres 中优化此查询

】如何在Postgres中优化此查询【英文标题】：HowcanIoptimizethisqueryinPostgres【发布时间】：2021-02-1214:19:23【问题描述】：以下查询需要更多时间来运行。如何优化以下查询以运行更多记录？我已经为这个查询运行了ExplainAnalyze。附上... 查看详情

如何使用其中的一些子查询优化此查询

】如何使用其中的一些子查询优化此查询【英文标题】：Howtooptimizethisquerywithsomesubqueriesinit【发布时间】：2013-03-1810:05:46【问题描述】：我的查询是这样的：SELECTdate_format(created_at,\'%Y-%m-%d\')ASthe_date,COUNT(s.id)AStotal,(SELECTCOUNT(ks.id)FRO... 查看详情

如何在 Firebird 2.1 中优化此查询？

】如何在Firebird2.1中优化此查询？【英文标题】：HowcanIoptimizethisqueryinFirebird2.1?【发布时间】：2012-03-0103:47:12【问题描述】：我正在使用Firebird2.1，我需要一些帮助来优化此查询：（可能通过将IN-s替换为JOINS或其他方式来加快速度... 查看详情

如何在 MYSQL 中优化此查询？需要做啥

】如何在MYSQL中优化此查询？需要做啥【英文标题】：HowcanIoptimisethisqueryinMYSQL?Whatneedstobedone如何在MYSQL中优化此查询？需要做什么【发布时间】：2010-08-1900:40:40【问题描述】：请向下滚动到“25/08/2010更新”。我有一个已变成视图... 查看详情

如何在 MySQL 中优化此查询

】如何在MySQL中优化此查询【英文标题】：HowtooptimizethisqueryinMySQL【发布时间】：2015-10-2718:08:21【问题描述】：我有这两张表（Moodle2.8）：CREATETABLE`mdl_course`(`id`bigint(10)NOTNULLAUTO_INCREMENT,`category`bigint(10)NOTNULLDEFAULT\'0\',`sortorder`bigint(1 查看详情

如何优化此查询并替换 MAX 函数？

】如何优化此查询并替换MAX函数？【英文标题】：HowcanIoptimizethisqueryandreplacetheMAXfunction?【发布时间】：2014-07-0902:15:52【问题描述】：SELECTMAX(f_orderInteractionID)FROMtOrdersOINNERJOINtOrderInteractionsOIONOI.fk_orderID=O.f_orderIDGROUPBYOI.fk_ord 查看详情

MySQL - 如何优化此查询？

】MySQL-如何优化此查询？【英文标题】：MySQL-Howcanthisquerybeoptimised?【发布时间】：2010-06-2805:04:26【问题描述】：以下查询有效，但10条记录（2秒）的速度非常慢。分析说它创建了一个tmp表，但我不确定为什么。基本上，我将当前... 查看详情

如何优化此查询（或内部 AND 验证）？

】如何优化此查询（或内部AND验证）？【英文标题】：HowCanIoptimizethisquery(ORinsideANDvalidation)?【发布时间】：2011-11-2919:25:20【问题描述】：我遇到了这个查询的问题：SELECTcol1,col2,col3,...,colnFROMMyTableWHEREcol1=@value1AND(ISNULL(col2,\'c\')=ISNU... 查看详情

如何优化此 SQL 查询

】如何优化此SQL查询【英文标题】：HowcanIoptimisethisSQLquery【发布时间】：2011-12-1313:06:25【问题描述】：我正在编写一个软件，用于识别已放在网络服务器(CMS)上但不再需要且应该/可以删除的文件。首先，我尝试手动重现所有必需... 查看详情

我如何优化此查询以用于计算响应的 sql

】我如何优化此查询以用于计算响应的sql【英文标题】：Howcanioptimizethisqueryforsqlforcountingresponse【发布时间】：2019-12-2110:17:41【问题描述】：我有一个问题响应表，当我尝试通过查询来计算问题的响应数以创建图表时，加载需要65... 查看详情

如何优化此查询并使其执行得更快？ [关闭]

】如何优化此查询并使其执行得更快？[关闭]【英文标题】：HowcanIoptimizethisqueryandmakeitexecutefaster?[closed]【发布时间】：2013-01-0507:11:19【问题描述】：如何通过此查询获得更快的结果？SELECTpost_idFROMwp_postmetaWHEREmeta_key=\'wpcf-cooler-name... 查看详情

如何使用 JOINS 和嵌套 SELECT 优化此 SQL 查询？

】如何使用JOINS和嵌套SELECT优化此SQL查询？【英文标题】：HowcanIoptimizethisSQLquerywithJOINSandnestedSELECT?【发布时间】：2016-10-0415:24:46【问题描述】：我的SQL查询需要大量时间来执行，因为事务表在某种程度上非常巨大。我正在寻找提... 查看详情

优化 SQL：如何重写此查询以提高性能？（使用子查询，摆脱 GROUP BY？）

】优化SQL：如何重写此查询以提高性能？（使用子查询，摆脱GROUPBY？）【英文标题】：OptimizeSQL:Howtorewritethisquerytoboostperformance?(Usesubqueries,getridofGROUPBY?)【发布时间】：2019-12-2900:51:10【问题描述】：我正在使用MySQL5.7.18-16。我使用... 查看详情

正文

如何优化此查询...？

如何优化此查询...？

SQL Server - 如何优化此查询？

如何使用 5 个子查询优化此查询？

如何优化此查询

如何优化此 SQL Server 查询 - 多个子查询

如何在 Postgres 中优化此查询

如何使用其中的一些子查询优化此查询

如何在 Firebird 2.1 中优化此查询？

如何在 MYSQL 中优化此查询？需要做啥

如何在 MySQL 中优化此查询

如何优化此查询并替换 MAX 函数？

MySQL - 如何优化此查询？

如何优化此查询（或内部 AND 验证）？

如何优化此 SQL 查询

我如何优化此查询以用于计算响应的 sql

如何优化此查询并使其执行得更快？ [关闭]

如何使用 JOINS 和嵌套 SELECT 优化此 SQL 查询？

优化 SQL：如何重写此查询以提高性能？ （使用子查询，摆脱 GROUP BY？）

优化 SQL：如何重写此查询以提高性能？（使用子查询，摆脱 GROUP BY？）