联合索引命中测试

总的来讲就是最左前缀原理，但是这个具体的原理比较抽象，我们来做测试理解这个原理。

创建一张表，我们建立一个联合索引

CREATE TABLE IF NOT EXISTS `cheatcode` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `game` VARCHAR(45) NOT NULL DEFAULT '',
  `feature` VARCHAR(45) NOT NULL DEFAULT '',
  `password` VARCHAR(45) NOT NULL DEFAULT '',
  `comment` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  INDEX `CINDEX` (`game` ASC, `feature` ASC, `password` ASC))
ENGINE = InnoDB;

INSERT INTO `cheatcode`(`game`,`feature`,`password`)VALUES('warcraft','money','greedisgood');
INSERT INTO `cheatcode`(`game`,`feature`,`password`)VALUES('warcraft','showmap','iseedeadpeople');
INSERT INTO `cheatcode`(`game`,`feature`,`password`)VALUES('warcraft','invincible','whosyourdaddy');
INSERT INTO `cheatcode`(`game`,`feature`,`password`)VALUES('starcraft','showmap','blacksheepwall');
INSERT INTO `cheatcode`(`game`,`feature`,`password`)VALUES('starcraft','speed','operationcwal');

为了使所有的查询能走索引而不被优化器优化，我们再插入1000万条随机数据。

1. 精确匹配

索引查询条件顺序排列：

mysql> EXPLAIN SELECT * FROM `cheatcode` WHERE `game`='warcraft' AND `feature`='money' AND `password`='greedisgood';
+----+-------------+-----------+------------+------+---------------+--------+---------+-------------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key    | key_len | ref               | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | cheatcode | NULL       | ref  | CINDEX        | CINDEX | 546     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

索引查询条件无序排列：

mysql> EXPLAIN SELECT * FROM `cheatcode` WHERE `feature`='money' AND `game`='warcraft' AND `password`='greedisgood';
+----+-------------+-----------+------------+------+---------------+--------+---------+-------------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key    | key_len | ref               | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | cheatcode | NULL       | ref  | CINDEX        | CINDEX | 546     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

无任何影响，执行器在执行之前会优化。

2. 部分匹配

我们一共做以下几种测试：

1. 只命中game, feature, password其中之一

mysql> EXPLAIN SELECT * FROM `cheatcode` WHERE `game`='TRjJiqdNoPtwcJcmHBwt';
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+---------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key    | key_len | ref   | rows    | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+---------+----------+-------+
|  1 | SIMPLE      | cheatcode | NULL       | ref  | CINDEX        | CINDEX | 182     | const | 1230430 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM `cheatcode` WHERE `feature`='TRjJiqdNoPtwcJcmHBwt';
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | cheatcode | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10641430 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM `cheatcode` WHERE `password`='TRjJiqdNoPtwcJcmHBwt';
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | cheatcode | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10641430 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

这个测试至少说明联合索引只对排在第一位的game生效了，feature和password并没有生效，我们再做个总数查询耗时来验证一下：

mysql> SELECT COUNT(0) FROM `cheatcode` WHERE `game`='TRjJiqdNoPtwcJcmHBwt';
+----------+
| COUNT(0) |
+----------+
|   666747 |
+----------+
1 row in set (0.64 sec)

mysql> SELECT COUNT(0) FROM `cheatcode` WHERE `feature`='TRjJiqdNoPtwcJcmHBwt';
+----------+
| COUNT(0) |
+----------+
|   666747 |
+----------+
1 row in set (3.36 sec)

mysql> SELECT COUNT(0) FROM `cheatcode` WHERE `password`='TRjJiqdNoPtwcJcmHBwt';
+----------+
| COUNT(0) |
+----------+
|   666747 |
+----------+
1 row in set (3.57 sec)

验证结果符合。

2. 命中任意两个

mysql> EXPLAIN SELECT * FROM `cheatcode` WHERE `game`='TRjJiqdNoPtwcJcmHBwt' AND `feature`='TRjJiqdNoPtwcJcmHBwt';
+----+-------------+-----------+------------+------+---------------+--------+---------+-------------+---------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key    | key_len | ref         | rows    | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------------+---------+----------+-------+
|  1 | SIMPLE      | cheatcode | NULL       | ref  | CINDEX        | CINDEX | 364     | const,const | 1230430 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------------+---------+----------+-------+
1 row in set, 1 warning (0.01 sec)

mysql> EXPLAIN SELECT * FROM `cheatcode` WHERE `game`='TRjJiqdNoPtwcJcmHBwt' AND `password`='TRjJiqdNoPtwcJcmHBwt';
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+---------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys | key    | key_len | ref   | rows    | filtered | Extra                 |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+---------+----------+-----------------------+
|  1 | SIMPLE      | cheatcode | NULL       | ref  | CINDEX        | CINDEX | 182     | const | 1230430 |    10.00 | Using index condition |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+---------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM `cheatcode` WHERE `feature`='TRjJiqdNoPtwcJcmHBwt' AND `password`='TRjJiqdNoPtwcJcmHBwt';
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | cheatcode | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10641430 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

结果略有不同，从结果上来看，game+feature是最好的组合，两个字段全部命中了，而game+password则只命中了game，feature+password则一个也没命中，我们再做一下总数查询验证：

mysql>  SELECT COUNT(0) FROM `cheatcode` WHERE `game`='TRjJiqdNoPtwcJcmHBwt' AND `feature`='TRjJiqdNoPtwcJcmHBwt';
+----------+
| COUNT(0) |
+----------+
|   666747 |
+----------+
1 row in set (0.97 sec)

mysql> SELECT COUNT(0) FROM `cheatcode` WHERE `game`='TRjJiqdNoPtwcJcmHBwt' AND `password`='TRjJiqdNoPtwcJcmHBwt';
+----------+
| COUNT(0) |
+----------+
|   666747 |
+----------+
1 row in set (1.02 sec)

mysql>  SELECT COUNT(0) FROM `cheatcode` WHERE `feature`='TRjJiqdNoPtwcJcmHBwt' AND `password`='TRjJiqdNoPtwcJcmHBwt';
+----------+
| COUNT(0) |
+----------+
|   666747 |
+----------+
1 row in set (4.11 sec)

结果符合查询条件的索引命中情况，第一种组合，因为命中最多，所以性能最好。第二种次之，第三种没命中，就是走全表扫描统计。