不使用索引的mySQL查询优化器

Question

在我们的生产环境( Amazon上的mySQL 5.6.40 )中，对于某些查询，查询优化器忽略表索引并花费几分钟来完成，而在我们的非RDS测试环境(mySQL 5.7)中，由于使用索引，相同的查询只需一小部分秒。

与其立即在RDS上从mySQL 5.6.40升级到5.7 (假设优化器工作得更好！)，我想知道在查询直接出现时是否缺少了什么.我有点惊讶优化器没有选择索引。

示例查询：

select id, username, agent, app_instance_id, end_time, event_time, 
last_modified 
from event where username='joebloggs' and app_instance_id<>'ad69469a- 
d73f-4b33-a1c5-bde03c765d89' and last_modified>'2018-08-20 20:14:51.394' 
order by last_modified asc limit 20

执行时间：

• mySQL 5.6.40: 6m+
• mySQL 5.7: 0.02s

解释：

在mySQL 5.6.40上解释显示优化器不使用任何索引(第5列“可能的键”，第9列“额外”)

'1'，‘简单’，‘事件’，‘范围’，'last_modified，用户名，appinstanceid，user_app_last'，‘最后_修改’，'6'，NULL，'1128956'，‘使用where’

而在mySQL 5.7上，解释显示优化器选择一个索引。

'1'，‘简单’，‘事件’，空，‘范围’，‘用户名，last_modified，appinstanceid，user_app_last'，’最后_修改‘，'6'，NULL，'1030540'，'0.54'，使用索引条件；使用where’

长话短说，我们的RDS实例曾经运行过mySQL 5.5，而且都运行得很好.但是，由于5.5寿命结束，被迫升级到5.6.40，这个问题已经浮出水面。

我还运行了ANALYZE表，以确保为优化器更新stats等。任何洞察力都将不胜感激。谢谢!

更新：

1. 我们的客户端应用程序正在使用JPA，JPA可以配置为使用索引提示。只是查询优化器在5.7上更好，我们应该升级，还是应该提供提示。而不依赖于查询优化器？
2. 在RDS上对一个新的测试实例(mySQL 5.7.23升级到5.6.40)上运行进一步的测试之后，在相同的查询(优化器是否选择使用索引)上，它似乎被击中或错过。

Answer 1

在任何系统中，这可能是有益的，可能是最佳的：

INDEX(username, last_modified) -- in this order

请提供SHOW CREATE TABLE，以便我们可以看到哪些索引，数据类型，引擎等，您有。

我推荐的索引

• 是一个“综合”指数。
• 与两个单列索引不相同。
• 有意从通过=测试的列开始。
• 忽略<>不起作用。
• 包括一个‘范围’(在last_modified上)。
• 它应该使用，但可能不使用ORDER BY，从而允许它有效地使用LIMIT。在没有到达LIMIT的情况下，它注定要扫描整个表，因此需要“很长”的时间。

附带注意：UUIDs (比如app_instance_id似乎是什么)在大表中索引时效率很低。对于这个查询，我希望很少有行具有这个特殊的值。

要进一步调查“为什么”，请回答以下问题：

• 表中包含username='joebloggs'的百分比。和
• 表中包含last_modified>'2018-08-20 20:14:51.394'的百分比是多少？

如果其中任何一个都超过了20%，那么优化者明智地选择忽略相应的指数。可以说，一个国家的统计数字比另一个数字要好。让我们看看SHOW INDEXES FROM event。

用于设计好的索引的烹饪书。

尽量避免索引提示--它们今天可能会有所帮助，但明天会受到伤害(以不同的常量表示)。

Answer 2

编辑：

正如用户@Rick所评论的，在使用MySQL索引提示之前，最好先改进索引。关于查找匹配索引的进一步描述，可以在他为为给定的选择构建最佳索引 in MySQL编写的优秀食谱中找到。此外，如果您已经有索引(a，b)，请不要添加索引(A)。

有关更好的解决方案，请参见他的回答。

原始答案：

原生SQL查询

您可以使用MySQL指数提示向优化器展示如何选择索引。

由JPA创建的

查询您可以使用子查询首先使用首选索引，并减少主查询的行数。

示例：

SELECT e.id, e.username, e.agent, e.appInstanceId, e.endTime, e.eventTime, e.lastModified 
FROM Event e
WHERE e.username = 'joebloggs'
AND e.appInstanceId <> 'ad69469a-d73f-4b33-a1c5-bde03c765d89'
AND EXISTS(SELECT esub FROM Event esub WHERE esub = e AND esub.lastModified > '2018-08-20 20:14:51.394')
ORDER BY e.lastModified