可能有多个 MySQL 索引

Question

鉴于以下 -

drop table if exists learning_indexes;

create table learning_indexes (
    id INT NOT NULL,
    col1 CHAR(30),
    col2 CHAR(30),
    col3 CHAR(30), 
    PRIMARY KEY (id),
    index idx_col1 (col1),
    index idx_col1_col2 (col1,col2)
);

explain

select
    col1,col2
from
    learning_indexes
where
    col1 = 'FOO'
    and col2 = 'BAR'

为什么 MySQL 选择 idx_col1 而不是 idx_col1_col2？

+----+-------------+------------------+------+------------------------+----------+---------+-------+------+-------------+
| id | select_type | table            | type | possible_keys          | key      | key_len | ref   | rows | Extra       |
+----+-------------+------------------+------+------------------------+----------+---------+-------+------+-------------+
|  1 | SIMPLE      | learning_indexes | ref  | idx_col1,idx_col1_col2 | idx_col1 | 91      | const |    1 | Using where |
+----+-------------+------------------+------+------------------------+----------+---------+-------+------+-------------+

这是我的版本信息-

+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 1.1.8               |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| version                 | 5.5.29              |
| version_comment         | Source distribution |
| version_compile_machine | i386                |
| version_compile_os      | osx10.7             |
+-------------------------+---------------------+

Answer 1

我同意 Floaf 的观点，即 MySQL 有时会选择错误的索引，但我认为这里不是这种情况。 MySQL 在决定选择哪个索引时包括行数和数据结构。

对于像这样一个相当简单的查询，如果表包含的行少于大约 100 行或者甚至是空的，MySQL 可能根本不使用任何索引。扫描所有表行似乎比使用索引在计算上更便宜。在您的解释计划中，您可以看到“key”列显示 idx_col1，但“Extra”列没有显示“using index”。

如果表包含超过 100 行，MySQL 将开始使用 idx_col1。解释计划将向您展示这一点。只有当 col1 中实际包含字符串 'FOO' 的行数超过 100 行时，MySQL 才会注意到使用 idx_col1 并不能充分减少暂定结果集，因为它必须扫描剩余的 100 行以获取值 ' BAR' 在 col2 中。因此，它将切换到 idx_col1_col2。

我不完全确定 MySQL 如何快速决定使用哪个索引，但我认为这与启发式方法和索引中各行的基数有关，即索引行的“选择性”程度。