优化mysql的索引性能

Question

我使用的mysql版本是：

'aurora_version','1.17.98'
'innodb_version','1.2.10'
'protocol_version','10'
'slave_type_conversions',''
'version','5.6.10-log'
'version_comment','MySQL Community Server (GPL)'
'version_compile_machine','x86_64'
'version_compile_os','Linux'

这是我正在使用的db表结构(相关部分)(表名access_logs)：

id| EventTime | EventType | EventName ... | event_log_domain_id

这张表大约有1100万份记录。

以下是查询数据的常见方式：

1)与event_log_domain_id一起使用in语句并执行计数

SELECT COUNT(*) FROM `access_logs ` WHERE `access_logs`.`event_log_domain_id ` IN (8, 59, 920, 1054, 2227) AND `access_logs `.`EventType ` = 1

2)不与event_log_domain_id一起使用in语句并执行计数

SELECT  COUNT(*)
    FROM  access_logs USE INDEX(event_log_domain_id)
    WHERE  `access_logs`.`event_log_domain_id ` = 1304
      AND  `access_logs`.`EventType ` = 1 

3)不执行计数

SELECT  `access_logs `.* FROM access_logs WHERE `access_logs `.`event_log_domain_id ` = 1304 AND `access_logs `.`EventType ` IN (1, 5)  ORDER BY id desc LIMIT 50

我对event_log_domain_id和EventType都有索引。

目前为止，数数表现是最差的。似乎计数查询甚至不使用索引。即使当我提供索引名时，也要为ex：

SELECT  COUNT(*)
    FROM   `access_logs` USE INDEX(event_log_domain_id)
    WHERE  `access_logs`.`event_log_domain_id` IN (8, 59, 920, 1054, 2227 )
      AND  `access_logs`.`EventType` = 1 

没有多大的帮助提高性能，甚至可能使其更糟。

另一方面，select查询(#3)在提供USE IDEX时性能更好。

我还考虑在event_log_domain_id和EventType上添加复合索引。重建索引需要一些时间，所以我不想在不首先了解性能含义的情况下随机尝试。

是否有我可以为计数操作创建的特殊索引？此时，对于最坏的情况，查询#1和#2运行时分别为240秒或120秒。

Answer 1

当心！在某些表名和列名中有额外的空间。(我在包装查询时删除了它们。)

这是所有这些查询的最佳索引，说USE INDEX没有好处：

INDEX(EventType, event_log_domain_id)  -- in this order
INDEX(event_log_domain_id, EventType)  -- in this order for #3

这个索引将处理WHERE并“覆盖”。包括这两个索引。

更多关于索引的内容：http://mysql.rjweb.org/doc.php/index_烹饪书_mysql --它解释了复合索引应该如何从用=测试的所有列开始，然后转到IN，最后是一个“范围”。(您的示例没有范围)。