MySQL索引测试

在MySQL中，即使表中没有数据，查询优化器也会考虑使用索引来执行查询。但在某些情况下，查询优化器可能会选择不使用索引，这通常是基于成本效益分析的结果。
表中没有任何数据时，无论是否使用索引，查询结果都是一样的。但直接扫描表（全表扫描）可能比使用索引更快，因为即使是空表，访问索引也需要一定的开销，但并不会带来性能提升。

> create table test(id int primary key, name varchar(10));
Query OK, 0 rows affected (0.10 sec)

mysql> explain select * from test where id=1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> insert into test value(1, 'a');
Query OK, 1 row affected (0.01 sec)

mysql> explain select * from test where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

使用如下SQL语句建表，并使用b列作为条件查询。按照索引最左匹配原则，此时应该无法使用索引。但如果表中只包含主键列和索引列，此时查询优化器还是会使用索引。示例如下：

CREATE TABLE `t1` (
  `id` int NOT NULL,
  `a` int DEFAULT NULL,
  `b` int DEFAULT NULL,
  `c` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `inx_abc` (`a`,`b`,`c`)
);

CREATE TABLE `t2` (
  `id` int NOT NULL,
  `a` int DEFAULT NULL,
  `b` int DEFAULT NULL,
  `c` int DEFAULT NULL,
  `d` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `inx_abc` (`a`,`b`,`c`)
);

# 插入两条数据
insert into t1 value(1, 1, 1, 1);
insert into t2 value(1, 1, 1, 1, 1);

> explain select * from t1 where b=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | inx_abc       | inx_abc | 15      | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

> explain select * from t2 where b=1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

同理，以下SQL也有类似现象：

> explain select * from t1 where b=1 and c=1;
> explain select * from t1 where c=1;
> 
> explain select * from t2 where b=1 and c=1;
> explain select * from t2 where c=1;