mysql - 为什么MySQL不使用索引进行大于比较？

Question

我正在尝试优化一个更大的查询并遇到了这堵墙，当我意识到这部分查询正在执行全表扫描时，考虑到所讨论的字段是主键，这在我看来是没有意义的。我会假设 MySQL 优化器会使用索引。

这是表格:

CREATE TABLE userapplication (
  application_id int(11) NOT NULL auto_increment,
  userid int(11) NOT NULL default '0',
  accountid int(11) NOT NULL default '0',
  resume_id int(11) NOT NULL default '0',
  coverletter_id int(11) NOT NULL default '0',
  user_email varchar(100) NOT NULL default '',
  account_name varchar(200) NOT NULL default '',
  resume_name varchar(255) NOT NULL default '',
  resume_modified datetime NOT NULL default '0000-00-00 00:00:00',
  cover_name varchar(255) NOT NULL default '',
  cover_modified datetime NOT NULL default '0000-00-00 00:00:00',
  application_status tinyint(4) NOT NULL default '0',
  application_created datetime NOT NULL default '0000-00-00 00:00:00',
  application_modified timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  publishid int(11) NOT NULL default '0',
  application_visible int(11) default '1',
  PRIMARY KEY  (application_id),
  KEY publishid (publishid),
  KEY application_status (application_status),
  KEY userid (userid),
  KEY accountid (accountid),
  KEY application_created (application_created),
  KEY resume_id (resume_id),
  KEY coverletter_id (coverletter_id),
 ) ENGINE=MyISAM ;

这个简单的查询似乎进行了全表扫描:

SELECT * FROM userapplication WHERE application_id > 1025;

这是 EXPLAIN 的输出:

+----+-------------+-------------------+------+---------------+------+---------+------+--------+-------------+| id | select_type | table             | type | possible_keys | key  | key_len | ref  | rows   | Extra       |+----+-------------+-------------------+------+---------------+------+---------+------+--------+-------------+|  1 | SIMPLE      | userapplication | ALL  | PRIMARY       | NULL | NULL    | NULL | 784422 | Using where |+----+-------------+-------------------+------+---------------+------+---------+------+--------+-------------+`

有什么想法可以防止这个简单的查询进行全表扫描吗？还是我运气不好？

Answer 1

让 MySql 决定查询计划可能会更好。有一个很好的执行索引扫描的效率可能低于全表扫描。

这个表在磁盘上有两个数据结构

1. 表格本身；和
2. 主键 B-Tree 索引。

当您运行查询时，优化器有两个关于如何访问数据的选项:

SELECT * FROM userapplication WHERE application_id > 1025;

使用索引

1. 扫描B-Tree索引找到application_id > 1025的所有行的地址
2. 阅读表格的相应页面以获取这些行的数据。

不使用索引

扫描整个表，并选择合适的记录。

选择最佳策略

查询优化器的工作是选择最有效的策略来获取您想要的数据。如果有很多行具有 application_id > 1025，那么使用索引实际上可能效率较低。例如，如果 90% 的记录具有 application_id > 1025，那么查询优化器必须扫描大约 90% 的 b 树索引叶节点，然后至少读取 90%表格以及获取实际数据；这将涉及从磁盘读取更多数据，而不仅仅是扫描表格。