为什么MySQL不使用索引进行大于比较? [英] Why does MySQL not use an index for a greater than comparison?

问题描述

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

I am trying to optimize a bigger query and ran into this wall when I realized this part of the query was doing a full table scan, which in my mind does not make sense considering the field in question is a primary key. I would assume that the MySQL Optimizer would use the index.

这是桌子:

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 ;

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

This simple query seems to do a full table scan:

SELECT * FROM userapplication WHERE application_id > 1025;

这是EXPLAIN的输出:

This is the output of the 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 |
+----+-------------+-------------------+------+---------------+------+---------+------+--------+-------------+`

有什么主意如何防止这个简单的查询进行全表扫描?还是我不走运?

Any ideas how to prevent this simple query from doing a full table scan? Or am I out of luck?

推荐答案

MyISAM表未聚集，PRIMARY KEY索引是辅助索引，需要进行额外的表查找才能获取其他值.

MyISAM tables are not clustered, a PRIMARY KEY index is a secondary index and requires an additional table lookup to get the other values.

遍历索引并进行查找要贵几倍.如果您的条件不是非常有选择性(在总记录中占有很大份额)，则MySQL会认为表扫描更便宜.

It is several times more expensive to traverse the index and do the lookups. If you condition is not very selective (yields a large share of total records), MySQL will consider table scan cheaper.

为防止其执行表扫描，您可以添加提示:

To prevent it from doing a table scan, you could add a hint:

SELECT  *
FROM    userapplication FORCE INDEX (PRIMARY)
WHERE   application_id > 1025

，尽管不一定会更有效.

, though it would not necessarily be more efficient.

这篇关于为什么MySQL不使用索引进行大于比较?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！