mysql - 日期字段mysql的索引

Question

我有一个包含 3 个字段的表格截图:

CREATE TABLE `screenshot` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `UserID` int(11) NOT NULL,
  `DateTaken` date NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `DateTaken` (`DateTaken`),
  KEY `UserID` (`UserID`) USING BTREE,
  CONSTRAINT `userID_foreign_key` FOREIGN KEY (`UserID`) REFERENCES `users` (`UserID`)
) ENGINE=InnoDB AUTO_INCREMENT=22514871 DEFAULT CHARSET=latin1

和

SELECT @@innodb_buffer_pool_size

结果:1​​6777216

查询:

SELECT COUNT(ID) total
        FROM screenshot WHERE DateTaken BETWEEN '2000-05-01' AND '2000-06-10'

结果:2828844

解释输出:

ID|select_type|   table  |type |possible_keys|   key   |key_len| rows  |Extra
1 |  SIMPLE   |screenshot|range|  DateTaken  |DateTaken|  3    |5730138|Using where; Using index

这是我的问题:我已将索引添加到 DateTaken 列，但扫描行(解释输出)比结果大。好像它做了一个完整的扫描表。查询的查询运行时间需要 15 秒。如何提高上述查询的速度？

Answer 1

没有问题。你的指数很好。解释...

EXPLAIN 中的 5730138 是一个估计。它可以比实际值大或小，有时会大很多。不要被它打扰。

您在该日期范围内有 280 万张屏幕截图，对吗？好吧，扫描索引以计算那么多行可能需要 15 秒。

如需进一步分析，请提供:
内存大小
innodb_buffer_pool_size
SHOW CREATE TABLE screenshot;(这将显示引擎)
表有多大(GB)
你有什么类型的磁盘(旋转与 SSD)

有了这些，我们可以进一步讨论缓存、I/O 和引擎的影响。它可能有助于解释“15 秒”与“20 秒”的区别。

(而且，是的，使用 COUNT(*)，而不是 COUNT(x)，除非您需要测试 x 是否为 NULL。)

如果您使用的是 InnoDB，则 INDEX(DateTaken, id) 与 INDEX(DateTaken) 相同，因此我建议您仓促接受该答案。

缓冲池

innodb_buffer_pool_size 应设置为大约 RAM 的 70%。你所拥有的是如此之小(旧的 16M 默认值)，甚至建议的索引也无法放入缓存中。因此，查询将始终访问磁盘，至少在某些时候是这样。增加缓冲池应该会显着提高速度，可能会降低到 2 秒。