为什么为SELECT (*).选择二级索引而不是聚集索引？

Question

在本查询中：

select count(*) from largetable;

选择了第二个索引：

mysql> explain select count(*) from largetable;
+----+-------------+------------+-------+---------------+------+---------+------+----------+-------------+
| id | select_type | table      | type  | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+------------+-------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | largetable | index | NULL          | iif  | 5       | NULL | 50000169 | Using index |
+----+-------------+------------+-------+---------------+------+---------+------+----------+-------------+
1 row in set (0.00 sec)

mysql> select count(*) from largetable;
+----------+
| count(*) |
+----------+
| 50000000 |
+----------+
1 row in set (5 min 52.02 sec)

而强制使用聚集索引：

select count(*) from largetable force index (primary);

提供更好的性能：

mysql> explain select count(*) from largetable force index (primary);
+----+-------------+------------+-------+---------------+---------+---------+------+----------+-------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows     | Extra       |
+----+-------------+------------+-------+---------------+---------+---------+------+----------+-------------+
|  1 | SIMPLE      | largetable | index | NULL          | PRIMARY | 4       | NULL | 50000169 | Using index |
+----+-------------+------------+-------+---------------+---------+---------+------+----------+-------------+
1 row in set (0.00 sec)

mysql> select count(*) from largetable force index (primary);
+----------+
| count(*) |
+----------+
| 50000000 |
+----------+
1 row in set (2 min 23.07 sec)

这是5分52秒，而2分23秒。

我想了解为什么MySQL的查询优化器选择一个辅助索引.

表中有5,000万行，ids从1到5 000万(无间隙)是按顺序插入的。

这在MySQL 5.5.11上。

这张桌子的设计如下：

create table largetable (
  id     int   primary key   auto_increment,
  field1 int,
    index iif (field1),
  ... some more columns, some with indexes ... each row is about 115 bytes ...
);

Answer 1

这个问题可能源于MySQL查询优化器进行选择的方式以及InnoDB内部表示索引的方式。

首先看看指数的基数。主键的基数必须始终是InnoDB表的实际行数。现在，看看field1的基数。如果索引iif小于主键的索引，则MySQL查询优化器将选择辅助索引。要验证field1的Cardinaliry值是否较低，请运行以下查询：

SELECT COUNT(DISTINCT field1) FROM largetable;
SELECT field1,COUNT(1) fieldcount FROM largetable
GROUP BY field1 WITH ROLLUP;

现在，看看索引的内部表示。二级索引将包含两个项: 1)列值(S)被索引，2)聚集索引(即k.a)中的rowid。( gen)。每次在辅助索引中引用列时，也会对实际行进行查找。想象一下:两个键在InnoDB中查找每一行。

将这两个问题结合在一起，您会发现一个基数低于主键的次要索引仍然将使用主键查找实际行。这就解释了为什么二级索引被选择在主键之上，并且查询所需的时间是主键的两倍，甚至更长。

有些人会不同意这种推理，因为我回答了一个类似于StackOverflow (2011年11月15日)的问题。虽然我的答案被接受了，但由于一些人对MySQL查询优化器和InnoDB索引结构的看法不同，所以它的得票率和下票数是混合的。。

如果来自Percona的任何人看到这个问题和我的答案，发现我的推理中有任何缺陷，请纠正我，这样所有人都可以学习。

更新2012-04-23 12:56美国东部时间

InnoDB存储引擎深入研究BTREE索引，对基数进行有意义的猜测。试着设置诺姆b

[mysqld]
innodb_stats_on_metadata = 0

根据文档，当禁用时，InnoDB不会在这些操作期间更新统计信息。禁用此变量可提高具有大量表或索引的架构的访问速度。它还可以提高涉及InnoDB表的查询的执行计划的稳定性。

Answer 2

我不记得MySQL的CREATE TABLE语法允许以您在问题中显示的方式创建主键。你能提供SHOW INDEXES FROM largetable;的输出吗？

我将查看MySQL 5.5更改日志，以查看与此相关的bug是否已在较新版本的MySQL中修复。此外，我建议在最新版本的MySQL 5.5.x中使用相同的表进行测试。截至今天，GA发布的5.5.x为5.5.23。