优化MySQL自连接查询

Question

我需要从符合某些条件的表中选择对行。

这里有一个SQL小提琴，演示了我想要的内容：

http://sqlfiddle.com/#!2/4fc2f/16/0

当表很小时，这个方法可以很好地工作--但问题是，我的生产表可能有数百万行和数千行，用于特定的日期。查询现在运行特定日期需要14到15秒。我该如何改进呢？

MySQL 5.5

Answer 1

你少了一个索引。尝试使用EXPLAIN来分析您的查询，它将对您有很大帮助。

解决方案很简单，如下所示：http://sqlfiddle.com/#!2/56deb/1/0

您需要添加一个索引，其中包括where语句中使用的列，以便使用它们：

KEY `night_of_2` (`night_of`,`student_id`,`check_class`)

此外，您还需要强制在联接上使用索引，因为您要将表加入到它自己：

JOIN checks checks2

力指数( night_of_2 ) ON ( checks1.night_of = checks2.night_of )

(如果有更好的方法，我想知道)：

致以敬意，

Answer 2

索引并不特别有效--在一个索引中可以有多个列。但是，为了获得正确的索引，您需要查看在数据库上运行的每个查询、每个查询的频率以及数据的分布。还是你只是让我们做你的家庭作业？根据所提供的信息，使用索引on (night_of、check_class、student_id)可以提高查询的效率，并且除了PK之外，还会丢失现有的索引。