Mysql 不正确的优化器工作

Question

我有下一个 innodb 表:

CREATE TABLE events (
  id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  server int(11) UNSIGNED NOT NULL,
  internal_id bigint(20) UNSIGNED NOT NULL,
  pid int(11) UNSIGNED NOT NULL,
  event_datetime DATETIME NOT NULL,
  event_stamp int(11) NOT NULL,
  status tinyint(3) UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  INDEX IDX_events (event_stamp, pid),
  INDEX IDX_events2 (event_stamp),
  UNIQUE INDEX UK_events_hash (internal_id, server)
)
ENGINE = INNODB;

记录数是 ~ 500 万。当我执行下一条 SQL 时:

EXPLAIN SELECT SQL_NO_CACHE id, internal_id, pid, status FROM events WHERE event_stamp BETWEEN UNIX_TIMESTAMP('2017-01-01') AND UNIX_TIMESTAMP(CURDATE());

Profiler 说有 2 个可能的索引，但没有一个被使用。总执行时间为 0.105 毫秒。然后我添加“FORCE INDEX (IDX_events2)”，探查器显示已使用索引，总执行时间为 0.02 毫秒。

那么为什么优化器认为不使用索引并遍历 ~200 万条记录比使用索引更快？使用索引执行得更快，这是合乎逻辑的。

解释输出:
1 SIMPLE c (null) ALL IDX_events,IDX_events2 (null) (null) (null) 5944539 50 使用位置

并用力:
1 SIMPLE c (null) range IDX_events2 IDX_events2 4 (null) 2972​​269 100 使用索引条件

Answer 1

大多数数据库使用B树进行索引。在这种情况下，数据库优化器不使用索引，因为没有索引扫描速度更快

200 万/500 万 > 20%，我想进行表扫描比使用索引更快。索引并不总是最好的解决方案Avoid full table scan

The output from EXPLAIN shows ALL in the type column when MySQL uses a full table scan to resolve a query. This usually happens under the following conditions:

You are using a key with low cardinality (many rows match the key value) through another column. In this case, MySQL assumes that by using the key it probably will do many key lookups and that a table scan would be faster.

在您的查询中，您使用范围运算符。在大多数情况下，避免索引扫描会更快。当您创建索引时，扫描索引页面以查找匹配项的成本更高。