MySQL索引无效问题

Question

请问为什么 MySQL 没有使用 'playerLead_status_INDEX' 索引？

CREATE TABLE `bcs_player_lead`(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `email_id` varchar(100) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `status` int(11) DEFAULT NULL COMMENT '1-New,2-invitation send',
  PRIMARY KEY (`id`),
  KEY `playerLead_status_INDEX` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=510 DEFAULT CHARSET=latin1;

解释SELECT email_id,id,NULL,first_name,last_name,NULL,state,city,NULL,(2) as type FROM bcs_player_lead WHERE status='1'

id|select_type|table |type|possible_keys |key |key_len |ref |rows |Extra
1 |SIMPLE |bcs_player_lead |ALL |playerLead_status_INDEX|{null}|{null}|{null}| 458 |Using where

bcs_player_lead有两个键 -

1. 通过ID的PRIMARY
2. 通过status的playerLead_status_INDEX

谢谢！

- Abhi

---

不确定您使用的是哪个版本的MySQL，但考虑到您在“DEFAULT NULL”状态下可能会出现问题。我在MySQL网站上找到了这个 http://dev.mysql.com/doc/refman/5.5/en/create-table.html “在MySQL 5.5中，只有InnoDB，MyISAM和MEMORY存储引擎支持对可以具有NULL值的列进行索引。在其他情况下，您必须将索引列声明为NOT NULL，否则会出现错误。” 这是我能想到的唯一解释。 - Jason McD

有时候MySQL即使有索引可用也不会使用。发生这种情况的一个原因是优化器估计使用索引需要访问表中非常大比例的行。(在这种情况下，全表扫描可能更快，因为它需要较少的查找。) 请参见http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html。 - Leo

这可能是@Leo所说的情况，场景是表统计信息未更新。 - Rahul

只是检查一下，尝试在查询中添加LIMIT（可以是LIMIT 1000）=>“...但是，如果这样的查询使用LIMIT仅检索某些行，则MySQL仍然使用索引，因为它可以更快地找到要在结果中返回的少数行。...” - Leo

@Leo：有550行数据。但是，当我使用Force index时，它会考虑索引并正常工作。但问题又来了 - 上面的查询是Union All，所以当我添加“Force index”时，会显示错误。 - Abhi

Abhi，使用 force index 只是一个检查。在你的情况下，由于它只获取了 500 行，即使它不进行索引搜索，你也不会感到任何滞后。 - Rahul

Answer 1

我不是数据库管理员，但据我所知，有时即使存在索引，也不会使用索引：当表统计数据没有更新（或者）需要检索的行数可能较少，正如@Leo所说的那样（因为在您的情况下只需要检索458行）。

不过，您可以在查询中使用FORCE INDEX（index_name）来强制使用索引。尝试一次并检查EXPLAIN PLAN。

SELECT email_id,id,NULL,first_name,last_name,NULL,state,city,NULL,(2) as type 
FROM bcs_player_lead 
FORCE INDEX (playerLead_status_INDEX)
WHERE status='1'

您可能想要查看一下这篇帖子 MySQL不使用索引与WHERE IN子句？

- Rahul

---

是的，我认为它没有考虑索引，因为记录较少。谢谢。 - Abhi

Answer 2

查询规划器计算执行给定查询的多个路径的成本。选择成本最低的路径。使用索引扫描VS表扫描是两种路径。 让我们考虑这两条路径：

1. 由于您在状态上有一个索引，并且还尝试检索其他列，因此您的索引不是覆盖索引。因此，当键满足where子句时，innodb需要从磁盘读取该行的数据（因为索引本身没有它）。 “状态”字段似乎只有2个值。对于这两个值中的任何一个，都会有很多行符合条件。例如：如果标识了1k行，则意味着从磁盘读取1k个随机读取
2. 假设您的表有10k行。如果使用表扫描，则意味着扫描并读取表的所有页面。但这些大多数将是顺序读取。

因此，情况1可能比情况2产生更多的成本。您应该检查索引基数以了解这一点。通常，具有非常低基数和非常低选择性的索引是不好的选择，即唯一值非常低（状态），而实际行数非常高。

- uzumas