总的来讲就是最左前缀原理，但是这个具体的原理比较抽象，我们来做测试理解这个原理。
创建一张表，我们建立一个联合索引
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)
结果符合查询条件的索引命中情况，第一种组合，因为命中最多，所以性能最好。第二种次之，第三种没命中，就是走全表扫描统计。
网友评论