如何优化这个MYSQL查询-连接多个表

Question

我有一个大查询（MYSQL），需要连接多个表：

SELECT * FROM
    `AuthLogTable`,
    `AppTable`,
    `Company`,
    `LicenseUserTable`,
    `LicenseTable`,
    `LicenseUserPool`,
    `PoolTable` 

WHERE
    `LicenseUserPool`.`UserID`=`LicenseUserTable`.`UserID` and 
    `LicenseUserTable`.`License`=`LicenseTable`.`License` and 
    LEFT(RIGHT(`AuthLogTable`.`User`, 17), 16)=`LicenseUserPool`.`UserID` and 
    `LicenseUserPool`.`PoolID`=`PoolTable`.`id` and 
    `Company`.`id`=`LicenseTable`.`CompanyID` and 
    `AuthLogTable`.`License` = `LicenseTable`.`License` and 
    `AppTable`.`AppID` = `AuthLogTable`.`AppID` AND 
    `PoolTable`.`id` IN (-1,1,2,4,15,16,17,5,18,19,43,20,3,6,8,10,29,30,7,11,12,24,25,26,27,28,21,23,22,31,32,33,34,35,36,37,38,39,40,41,42,-1)

ORDER BY 
     `AuthLogTable`.`AuthDate` DESC，
     `AuthLogTable`.`AuthTime` DESC

LIMIT 0,20

我使用 explain 命令并得到以下结果：

如何使查询更快？在大表中需要几秒钟。

"Showing rows 0 - 19 ( 20 total, Query took 3.5825 sec)"

据我所知，查询中使用的字段已经在每个表中建立了索引。

AuthLogTable 表已经设置了索引。

- justyy

---

标记使用的数据库管理系统产品，因为不同的产品有不同的优化方式。同时列出索引、外键等信息。并且请尝试格式化查询语句，代码不易阅读... - jarlh

你尝试过对查询涉及的表运行ANALYZE TABLE吗？似乎需要通过文件排序进行排序的内容的估计有误（如果没有LIMIT，你会得到多少行数据？） - mabi

学习使用正确的“join”语法。 - Gordon Linoff

Answer 1

您可以尝试在数据上运行此查询，而不使用'order by'子句，并查看是否有所不同（还要运行“explain”）。如果是这样，您可以考虑在排序字段上添加索引/索引。'Using temporary; using filesort;'表示创建了临时表，然后进行排序，没有索引需要花费时间。

据我所知，联接样式并不重要，因为查询被解析成另一种形式。但您仍然可能希望使用ANSI联接语法（也请参阅此问题ANSI joins versus "where clause" joins）。

- borowis

---

如果你想要优化查询，考虑在这些字段上建立索引。但请注意，索引会影响插入性能并有一些存储索引数据的要求。例如，在MySQL中可以为AuthDate和AuthTime添加BTREE索引。如需更多详情，请查看MySQL文档 https://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html - borowis

是的，我明白了。更多信息在这里https://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html。看起来问题在于`在某些情况下，MySQL无法使用索引解析ORDER BY，尽管它仍然使用索引查找与WHERE子句匹配的行。 这些情况包括以下内容： 1.您在不同的键上使用ORDER BY`。现在您可以尝试在两个字段上添加索引，并确保“Using filesort;”从explain输出中消失。 - borowis

好的，我完成了关于ORDER BY优化的文章，但我不能确定在两个字段上添加索引是否有效，但这是值得尝试的。如果不起作用，那么查询应该以某种方式重新组织（想法是摆脱慢速文件排序），或者您可以在应用程序代码中对数据进行排序。 - borowis

我尝试在两个字段上添加索引，正如Borys Zibrov所说，它不起作用。 - justyy

是的，那很不幸。你可以在代码中进行排序，或者你必须重新编写查询（很难确定具体的方式，我建议你a）尝试将AuthLogTable作为查询执行计划中的第一个非const类型表格（例如，你能从PoolTable中连接它吗？）我不确定是否可能b）尝试使用子查询进行实验，并查找SO中的“order by optimization”问题）。 - borowis

Answer 2

首先，考虑修改您的查询以正确使用JOINS。另外，请确保对在JOIN ON子句、WHERE条件和ORDER BY子句中使用的列进行了索引。

select * from `AuthLogTable`
join `AppTable` on `AppTable`.`AppID` = `AuthLogTable`.`AppID`
join  `LicenseTable` on `AuthLogTable`.`License` = `LicenseTable`.`License`
join `Company` on `Company`.`id`=`LicenseTable`.`CompanyID`
join `LicenseUserTable` on `LicenseUserTable`.`License`=`LicenseTable`.`License`
join `LicenseUserPool` on `LicenseUserPool`.`UserID`=`LicenseUserTable`.`UserID`
join `PoolTable`  on `LicenseUserPool`.`PoolID`=`PoolTable`.`id`
where LEFT(RIGHT(`AuthLogTable`.`User`, 17), 16)=`LicenseUserPool`.`UserID`
and `PoolTable`.`id` IN (-1,1,2,4,15,16,17,5,18,19,43,20,3,6,8,10,29,30,7,11,12,24,25,26,27,28,21,23,22,31,32,33,34,35,36,37,38,39,40,41,42,-1)
order by `AuthLogTable`.`AuthDate` desc,  `AuthLogTable`.`AuthTime` desc 
limit 0,20;

- Rahul

---

我本来打算接受你的答案，直到我运行了你的查询，发现它变慢了两倍... “显示行 0 - 19（共 20 行，查询用时 7.4764 秒）” - justyy

嗯，我可能不正确，时间会有所不同...但从解释中看，它与问题中的查询没有区别。 - justyy

@DoctorLai，请再试一次，更改了连接顺序。同时确保您连接的列都正确地建立了索引。 - Rahul

您IP地址为143.198.54.68，由于运营成本限制，当前对于免费用户的使用频率限制为每个IP每72小时10次对话，如需解除限制，请点击左下角设置图标按钮（手机用户先点击左上角菜单按钮）。 - Rahul

Answer 3

请尝试以下查询：

SELECT *
FROM `AuthLogTable`
JOIN `AppTable` ON (`AppTable`.`AppID` = `AuthLogTable`.`AppID`)
JOIN `LicenseUserPool` ON (LEFT(RIGHT(`AuthLogTable`.`User`, 17), 16)=`LicenseUserPool`.`UserID`)
JOIN `LicenseUserTable` ON (`LicenseUserPool`.`UserID`=`LicenseUserTable`.`UserID`)
JOIN `LicenseTable` ON (`AuthLogTable`.`License` = `LicenseTable`.`License`
                        AND `LicenseUserTable`.`License`=`LicenseTable`.`License`)
JOIN `Company` ON (`Company`.`id`=`LicenseTable`.`CompanyID`)
JOIN `PoolTable` ON (`LicenseUserPool`.`PoolID`=`PoolTable`.`id`)
WHERE `PoolTable`.`id` IN (-1,1,2,4,15,16,17,5,18,19,43,20,3,6,8,10,29,30,7,11,12,24,25,26,27,28,21,23,22,31,32,33,34,35,36,37,38,39,40,41,42,-1)
ORDER BY `AuthLogTable`.`AuthDate` DESC， `AuthLogTable`.`AuthTime` DESC LIMIT 0,20

- James Jithin

---

你有一个演示例子来证明这确实使用了不同的执行计划吗？你能解释一下在“explain”中是什么让你认为优化器没有正确处理连接吗？ - mabi

@mabi，正如你所要求的那样，我没有设置表来解释查询。已经被要求“尝试”，并没有说它是最优化的。 - James Jithin