接着上篇，我们继续来探究索引。这次我们主要来探究关于联合索引的使用和联合、多表查询的规范。
继续看一下数据：
mysql> select * from student order by ID desc; +----+-------------+-----------+------------+ | ID | CName | ClassName | SchoolName | +----+-------------+-----------+------------+ | rockderia26| 一年二班 | 拓荒学院 | | rockderia25| 一年二班 | 拓荒学院 | | rockderia24| 一年一班 | 拓荒学院 | | rockderia23| 一年一班 | 拓荒学院 | | rockderia22| 一年一班 | 拓荒学院 | | rockderia21| 一年一班 | 拓荒学院 | | rockderia14| 一年二班 | 冷锋中学 | | rockderia13| 一年二班 | 冷锋中学 | | rockderia12| 一年二班 | 冷锋中学 | | rockderia11| 一年一班 | 冷锋中学 | | rockderia08| 一年二班 | 花园中学 | | rockderia07| 一年二班 | 花园中学 | | rockderia06| 一年二班 | 花园中学 | | rockderia05| 一年二班 | 花园中学 | | rockderia04| 一年二班 | 花园中学 | | rockderia03| 一年二班 | 花园中学 | | rockderia02| 一年二班 | 花园中学 | | rockderia01| 一年二班 | 花园中学 | | rockderia8 | 一年一班 | 花园中学 | | rockderia7 | 一年一班 | 花园中学 | | rockderia6 | 一年一班 | 花园中学 | | rockderia5 | 一年一班 | 花园中学 | | rockderia4 | 一年一班 | 花园中学 | | rockderia3 | 一年一班 | 花园中学 | | rockderia2 | 一年一班 | 花园中学 | | rockderia1 | 一年一班 | 花园中学 | +----+-------------+-----------+------------+ rows in set (0.00 sec)
mysql> SHOW CREATE TABLE student; +---------+------------------------------------------------------------------------ --------------------------------------------------------------------------------+ | Table | Create Table | +---------+------------------------------------------------------------------------ --------------------------------------------------------------------------------+ | student | CREATE TABLE `student` ( `ID` ) NOT NULL AUTO_INCREMENT COMMENT '自增长主键', `CName` ) DEFAULT NULL, `ClassName` ) DEFAULT NULL, `SchoolName` ) DEFAULT NULL, PRIMARY KEY (`ID`), KEY `NameClassSchool` (`CName`,`ClassName`,`SchoolName`) ) ENGINE DEFAULT CHARSET=utf8 | +---------+------------------------------------------------------------------------ --------------------------------------------------------------------------------+
我们看到一条联合索引 KEY `NameClassSchool` (`CName`,`ClassName`,`SchoolName`)
我们来执行以下语句：
EXPLAIN SELECT * FROM student WHERE cname = "rockderia";
mysql> EXPLAIN SELECT * FROM student WHERE cname = "rockderia";
+----+-------------+---------+------+-----------------+-----------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+-----------------+-----------------+---------+-------+------+--------------------------+
| Using where; Using index |
+----+-------------+---------+------+-----------------+-----------------+---------+-------+------+--------------------------+
可以看到索引起了作用。只遍历了一行数据。
我们再来试一下：
mysql> EXPLAIN SELECT * FROM student WHERE classname = "一年一班";
+----+-------------+---------+-------+---------------+-----------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+-----------------+---------+------+------+--------------------------+
| Using where; Using index |
+----+-------------+---------+-------+---------------+-----------------+---------+------+------+--------------------------+
我们看到遍历了所有的数据。作为联合索引的几个字段，如果拿来单独使用，有些生效有些却不生效。其实联合索引有一套使用规则。即索引生效多少取决于按照索引字段的顺序从左往右提供多少。
比如：一则联合索引 key(a,b,c)
如果单独使用 a 那么索引生效 生效区域就是a。
如果单独使用 b 那么索引就不会生效。因为使用b的前提先使用a，也就是 同时使用ab 索引也是生效的。
如果不考虑范围查找，那么联合索引 a,b,c 能生效的使用组合就是 a ab abc
索引在设置联合索引的时候一定要注意这个顺序。设置 a,b,c联合索引，就相当于设置的单独的a索引和另一则联合索引啊a，b。要利用这个特性使一则联合索引的价值最大化。
接下来我们讨论范围查找：
mysql> EXPLAIN SELECT * FROM student WHERE cname LIKE "rockderia1%" ;
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+--------------------------+
| Using where; Using index |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+--------------------------+
row in set (0.00 sec)
我们发现索引是生效的，凡是满足范围查找规则的索引所对应的行被遍历。一共5则。我们继续追加：
EXPLAIN SELECT * FROM student WHERE cname LIKE "rockderia1%" AND classname = "一年一班";
如果遍历行数缩短为2行，那么就说明classname这条索引生效了。
mysql> EXPLAIN SELECT * FROM student WHERE cname LIKE "rockderia1%" AND classname = "一年一班";
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+--------------------------+
| Using where; Using index |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+--------------------------+
row in set (0.00 sec)
运行后发现，然并卵。所以一旦使用模糊查询，那么后续的索引就不会生效了。
当然，模糊查询也有这个 左 的原则。比如一旦使用 select * from student where cname like "%deria01"; 将不会有任何索引生效
我们插入一则数据：
38 | rockderia1 | 一年二班 | 花园中学 |
这时候,rockderia1 出现两个。我们执行：
EXPLAIN SELECT * FROM student WHERE cname = "rockderia1";
+----+-------------+---------+------+-----------------+-----------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+-----------------+-----------------+---------+-------+------+--------------------------+
| Using where; Using index |
+----+-------------+---------+------+-----------------+-----------------+---------+-------+------+--------------------------+
mysql> EXPLAIN SELECT * FROM student WHERE cname = "rockderia1" AND classname LIKE "一年二%";
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+--------------------------+
| Using where; Using index |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+--------------------------+
由此关于这个左值的使用一目了然。
下面我们来试一下联合查询。我们创建一个教师表。
mysql> show create table teacher; +---------+---------------------------------------------- | Table | Create Table +---------+---------------------------------------------- | teacher | CREATE TABLE `teacher` ( `id` ) NOT NULL AUTO_INCREMENT, `classname` ) DEFAULT NULL, `cname` ) DEFAULT NULL, `schoolname` ) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE DEFAULT CHARSET=utf8 | +---------+---------------------------------------------- row in set (0.00 sec)
加一些基础数据：
+----+-----------+--------+------------+ | id | classname | cname | schoolname | +----+-----------+--------+------------+ | 1 | 一年一班 | lily1 | 花园中学 | | 2 | 一年一班 | lily2 | 花园中学 | | 一年二班 | lily3 | 花园中学 | | 一年二班 | lily11 | 花园中学 | | 一年二班 | lily12 | 冷锋中学 | +----+-----------+--------+------------+
我们试一下如下语句：
EXPLAIN SELECT * FROM student s JOIN teacher t ON s.classname = t.classname;
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+-----------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+-----------------------------------------------------------------+
| NULL |
| Using where; Using index; Using join buffer (Block Nested Loop) |
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+-----------------------------------------------------------------+
我们看到，在t表中每次遍历5行数据，在s表遍历了27行数据。索引似乎都没有生效。我们改变以下语句：
mysql> EXPLAIN SELECT * FROM student s JOIN teacher t ON t.id = s.id;
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------+
| NULL |
| NULL |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------+
我们看到，作为关联条件双方如果都是变量，那么主表的索引会生效。但是从表不会从这条语句上生效。如果我们再追加：
EXPLAIN ;
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------+
| Using where |
| NULL |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------+
我们就会发现从表的索引也生效了，不过条件需要额外追加。如果条件不符合，那么：
mysql; +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
索引引擎会直接得知想要搜索的目标行不存在。不进行任何遍历。
当然，如果从表的搜索条件不是索引，那么就是另外一种情况：
mysql> explain select * from student s join teacher t on s.cname = t.cname and t.cname = "lily2";
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+----------------------------------------------------+
| const | 1 | Using where; Using index |
| Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+----------------------------------------------------+
如果是左连接的话，就又是另外一副景象了。因为左连接对于主表没有筛选工作，这就需要所有主表的数据进行操作：
mysql> explain select * from student s left join teacher t on s.cname = t.cname;
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+----------------------------------------------------+
| Using index |
| Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+----------------------------------------------------+
就像这般。在已有的索引系统上写出面对新需求效率更高的查询语句，显得格外重要。