让MySQL从多个索引中读取？

Question

让我们从一个简单的例子开始：

CREATE TABLE `test` (
`id` INT UNSIGNED NOT NULL,
`value` CHAR(12) NOT NULL,
INDEX (`id`),
INDEX (`value`)
) ENGINE = InnoDB;

所以两列都被索引了。我认为这意味着MySQL不再需要读取实际的表了，因为所有的数据都存储在索引中。

mysql> EXPLAIN SELECT id FROM test WHERE id = 1;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | test  | ref  | id            | id   | 4       | const |    1 | Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+

“使用索引”，非常好。据我理解，这意味着它是从索引中读取数据，而不是从实际表读取数据。但我真正想要的是“价值”栏。

mysql> EXPLAIN SELECT value FROM test WHERE id = 1;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | test  | ref  | id            | id   | 4       | const |    1 |       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+

这次没有“使用索引”。

我想，如果我添加一个覆盖两列的索引，可能会有所帮助。

ALTER TABLE `test` ADD INDEX `id_value` (`id`,`value`);

现在让我们再次运行前面的select语句，并告诉它使用新的索引。

mysql> EXPLAIN SELECT id, value FROM test USE INDEX (id_value) WHERE id = 1;
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
|  1 | SIMPLE      | test  | ref  | id_value      | id_value | 4       | const |    1 | Using index |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

赞美上帝，它是从索引上读来的。

但实际上，我并不需要其他任何东西的综合指数。可以让MySQL从两个单独的索引中读取吗？

任何见解都将不胜感激。

编辑： Ok，又一个例子。这个表定义为原始表(因此每个列都有一个索引)。

mysql> EXPLAIN SELECT t1.value
    -> FROM test AS t1
    -> INNER JOIN test AS t2
    -> ON t1.id <> t2.id AND t1.value = t2.value
    -> WHERE t1.id = 1;
+----+-------------+-------+------+---------------+-------+---------+----------+------+-------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref      | rows | Extra       |
+----+-------------+-------+------+---------------+-------+---------+----------+------+-------------+
|  1 | SIMPLE      | t1    | ref  | id,value      | id    | 4       | const    |    1 |             |
|  1 | SIMPLE      | t2    | ref  | value         | value | 12      | t1.value |    1 | Using where |
+----+-------------+-------+------+---------------+-------+---------+----------+------+-------------+

这当然必须从两个索引中读取(因为这两个字段都在join条件下使用)，但是它仍然从实际记录读取数据，对吗？为什么不直接使用它从索引中读取的数据呢？还是它实际上使用了这些数据而没有说“使用索引”？

再次感谢

Answer 1

因此，key、ref和rows列更能说明问题。在每种情况下，它们都表示MySQL选择了一个索引，在该索引中有一个要查找的值，因此只能从表中检索一行。这就是你想要的。

在第二个查询中，MySQL仍然需要从记录中检索value，即使它已经通过索引将记录定位在id上。如果您的WHERE标准是基于value查找的，那么将使用该索引，并且不需要检索记录。

手册 on Using index Extra信息：

仅使用索引树中的信息从表中检索列信息，而不必进行额外的查找来读取实际行。当查询仅使用作为单个索引的一部分的列时，可以使用此策略。 如果Extra列也表示Using where，则意味着索引被用于执行键值查找。如果没有Using where，优化器可能会读取索引以避免读取数据行，但不会将其用于查找。例如，如果索引是查询的覆盖索引，优化器可以扫描它而不使用它进行查找。 对于具有用户定义的聚集索引的InnoDB表，即使Extra列中没有Using index，也可以使用该索引。如果type是index，key是PRIMARY，情况就是这样。

Answer 2

在第一个查询中，MySQL表示using index，因为它可以单独查看索引和索引来回答查询。它不需要转到表中查找id列的对应值，因为这实际上与索引中已经得到的内容相同。

在第二个查询中，MySQL确实需要查看表以获取正确的值，但它仍然使用索引，正如您在EXPLAIN语句的key列中看到的那样。

在第三个查询中，MySQL再次不必再查看表了，因为它回答查询所需的所有信息都在多列索引中。

Answer 3

想一想索引是如何工作的。

比方说，您的test表中有10k条记录，value列上有索引。在用数据填充表时(或显式地使用ANALYZE命令)，数据库在表和所有索引上保存统计信息。

在您发出查询时，有几种方法可以传递数据。在非常简化的test表和value列中，如下所示：

SELECT * FROM test WHERE value = 'a string';

数据库query planner有两个选项：

1. 对整个表执行顺序扫描并过滤结果或
2. 执行索引扫描以查找所需的数据条目。

查询索引有一定的性能损失，因为数据库必须在索引中查找值。如果我们假设您有一个“良好形状”的B-tree索引(即平衡)，那么您最多可以在索引中找到14个查找项(如2^14 > 10k，希望这里没有弄错)。因此，为了提供1行a string值，数据库必须在索引中执行最多14次查找，在表中执行1次额外查找。在不幸的情况下，这将意味着系统将执行15个随机I/O操作，从您的磁盘读取自定义数据部分。

在这种情况下，只有一个value需要在索引中查找，而且您的表的大小相当大，索引操作将大大提高性能。但是，有一个点之后，索引扫描变得更昂贵，然后是直接的顺序扫描：

• 当你的桌子占用磁盘上非常小的尺寸时；
• 当您的查询需要查找test表中记录总数的10%时(数字10%非常接近，不要认为它是理所当然的)。

需要考虑的事项：

• 数字数据类型的比较操作要便宜得多，然后比较字符串；
• 统计准确性；
• 查询索引/表的频率，或者在数据库共享池中查找所需数据的概率。

这些都会影响性能，也会影响数据库选择交付数据的计划。

因此，索引并不总是好的。

要回答您的to read from 2 separate indexes问题:您正在寻找的特性称为Bitmap index，据我所知，它在MySQL中不可用。