接着上篇，我们继续来探究索引。这次我们主要来探究关于联合索引的使用和联合、多表查询的规范。
继续看一下数据：
mysql> select * from student order by ID desc; +----+-------------+-----------+------------+ | ID | CName | ClassName | SchoolName | +----+-------------+-----------+------------+ | 37 | rockderia26| 一年二班 | 拓荒学院 | | 36 | rockderia25| 一年二班 | 拓荒学院 | | 35 | rockderia24| 一年一班 | 拓荒学院 | | 34 | rockderia23| 一年一班 | 拓荒学院 | | 33 | rockderia22| 一年一班 | 拓荒学院 | | 32 | rockderia21| 一年一班 | 拓荒学院 | | 31 | rockderia14| 一年二班 | 冷锋中学 | | 30 | rockderia13| 一年二班 | 冷锋中学 | | 29 | rockderia12| 一年二班 | 冷锋中学 | | 28 | rockderia11| 一年一班 | 冷锋中学 | | 27 | rockderia08| 一年二班 | 花园中学 | | 26 | rockderia07| 一年二班 | 花园中学 | | 25 | rockderia06| 一年二班 | 花园中学 | | 24 | rockderia05| 一年二班 | 花园中学 | | 23 | rockderia04| 一年二班 | 花园中学 | | 22 | rockderia03| 一年二班 | 花园中学 | | 21 | rockderia02| 一年二班 | 花园中学 | | 20 | rockderia01| 一年二班 | 花园中学 | | 19 | rockderia8 | 一年一班 | 花园中学 | | 18 | rockderia7 | 一年一班 | 花园中学 | | 17 | rockderia6 | 一年一班 | 花园中学 | | 16 | rockderia5 | 一年一班 | 花园中学 | | 15 | rockderia4 | 一年一班 | 花园中学 | | 14 | rockderia3 | 一年一班 | 花园中学 | | 13 | rockderia2 | 一年一班 | 花园中学 | | 12 | rockderia1 | 一年一班 | 花园中学 | +----+-------------+-----------+------------+ 26 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE student; +---------+------------------------------------------------------------------------ --------------------------------------------------------------------------------+ | Table | Create Table | +---------+------------------------------------------------------------------------ --------------------------------------------------------------------------------+ | student | CREATE TABLE `student` ( `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增长主键', `CName` varchar(20) DEFAULT NULL, `ClassName` varchar(20) DEFAULT NULL, `SchoolName` varchar(20) DEFAULT NULL, PRIMARY KEY (`ID`), KEY `NameClassSchool` (`CName`,`ClassName`,`SchoolName`) ) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8 | +---------+------------------------------------------------------------------------ --------------------------------------------------------------------------------+
mysql范围查询会使用索引吗。
我们看到一条联合索引 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 | +----+-------------+---------+------+-----------------+-----------------+---------+-------+------+--------------------------+ | 1 | SIMPLE | student | ref | NameClassSchool | NameClassSchool | 63 | const | 1 | Using where; Using index | +----+-------------+---------+------+-----------------+-----------------+---------+-------+------+--------------------------+
sql索引怎么用。
可以看到索引起了作用。只遍历了一行数据。
我们再来试一下：
mysql> EXPLAIN SELECT * FROM student WHERE classname = "一年一班"; +----+-------------+---------+-------+---------------+-----------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+-----------------+---------+------+------+--------------------------+ | 1 | SIMPLE | student | index | NULL | NameClassSchool | 189 | NULL | 26 | Using where; Using index | +----+-------------+---------+-------+---------------+-----------------+---------+------+------+--------------------------+
mysql in会用到索引吗、我们看到遍历了所有的数据。作为联合索引的几个字段，如果拿来单独使用，有些生效有些却不生效。其实联合索引有一套使用规则。即索引生效多少取决于按照索引字段的顺序从左往右提供多少。
比如：一则联合索引 key(a,b,c)
如果单独使用 a 那么索引生效 生效区域就是a。
如果单独使用 b 那么索引就不会生效。因为使用b的前提先使用a，也就是 同时使用ab 索引也是生效的。
如果不考虑范围查找，那么联合索引 a,b,c 能生效的使用组合就是 a ab abc
MySQL添加索引、索引在设置联合索引的时候一定要注意这个顺序。设置 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 | +----+-------------+---------+-------+-----------------+-----------------+---------+------+------+--------------------------+ | 1 | SIMPLE | student | range | NameClassSchool | NameClassSchool | 63 | NULL | 5 | Using where; Using index | +----+-------------+---------+-------+-----------------+-----------------+---------+------+------+--------------------------+ 1 row in set (0.00 sec)
我们发现索引是生效的，凡是满足范围查找规则的索引所对应的行被遍历。一共5则。我们继续追加：
MySQL索引失效？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 | +----+-------------+---------+-------+-----------------+-----------------+---------+------+------+--------------------------+ | 1 | SIMPLE | student | range | NameClassSchool | NameClassSchool | 126 | NULL | 5 | Using where; Using index | +----+-------------+---------+-------+-----------------+-----------------+---------+------+------+--------------------------+ 1 row in set (0.00 sec)
运行后发现，然并卵。所以一旦使用模糊查询，那么后续的索引就不会生效了。
mysql数据库索引、当然，模糊查询也有这个 左 的原则。比如一旦使用 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 | +----+-------------+---------+------+-----------------+-----------------+---------+-------+------+--------------------------+ | 1 | SIMPLE | student | ref | NameClassSchool | NameClassSchool | 63 | const | 2 | Using where; Using index | +----+-------------+---------+------+-----------------+-----------------+---------+-------+------+--------------------------+
mysql如何创建索引，
mysql> EXPLAIN SELECT * FROM student WHERE cname = "rockderia1" AND classname LIKE "一年二%"; +----+-------------+---------+-------+-----------------+-----------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+-----------------+-----------------+---------+------+------+--------------------------+ | 1 | SIMPLE | student | range | NameClassSchool | NameClassSchool | 126 | NULL | 1 | Using where; Using index | +----+-------------+---------+-------+-----------------+-----------------+---------+------+------+--------------------------+
由此关于这个左值的使用一目了然。
mysql索引下推。
下面我们来试一下联合查询。我们创建一个教师表。
mysql> show create table teacher; +---------+---------------------------------------------- | Table | Create Table +---------+---------------------------------------------- | teacher | CREATE TABLE `teacher` ( `id` int(11) NOT NULL AUTO_INCREMENT, `classname` varchar(20) DEFAULT NULL, `cname` varchar(20) DEFAULT NULL, `schoolname` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 | +---------+---------------------------------------------- 1 row in set (0.00 sec)
加一些基础数据：
+----+-----------+--------+------------+ | id | classname | cname | schoolname | +----+-----------+--------+------------+ | 1 | 一年一班 | lily1 | 花园中学 | | 2 | 一年一班 | lily2 | 花园中学 | | 3 | 一年二班 | lily3 | 花园中学 | | 4 | 一年二班 | lily11 | 花园中学 | | 5 | 一年二班 | lily12 | 冷锋中学 | +----+-----------+--------+------------+
mysql组合索引，我们试一下如下语句：
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 | +----+-------------+-------+-------+---------------+-----------------+---------+------+------+-----------------------------------------------------------------+ | 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 5 | NULL | | 1 | SIMPLE | s | index | NULL | NameClassSchool | 189 | NULL | 27 | 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 | +----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------+ | 1 | SIMPLE | t | ALL | PRIMARY | NULL | NULL | NULL | 5 | NULL | | 1 | SIMPLE | s | eq_ref | PRIMARY | PRIMARY | 4 | test.t.id | 1 | NULL | +----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------+
我们看到，作为关联条件双方如果都是变量，那么主表的索引会生效。但是从表不会从这条语句上生效。如果我们再追加：
EXPLAIN SELECT * FROM student s JOIN teacher t ON t.id = s.id and t.id > 3; +----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------+ | 1 | SIMPLE | t | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where | | 1 | SIMPLE | s | eq_ref | PRIMARY | PRIMARY | 4 | test.t.id | 1 | NULL | +----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------+
我们就会发现从表的索引也生效了，不过条件需要额外追加。如果条件不符合，那么：
mysql> EXPLAIN SELECT * FROM student s JOIN teacher t ON t.id = s.id and t.id = 3; +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | 1 | 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 | +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+----------------------------------------------------+ | 1 | SIMPLE | s | ref | NameClassSchool | NameClassSchool | 63 | const | 1 | Using where; Using index | | 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 5 | 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 | +----+-------------+-------+-------+---------------+-----------------+---------+------+------+----------------------------------------------------+ | 1 | SIMPLE | s | index | NULL | NameClassSchool | 189 | NULL | 27 | Using index | | 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 5 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+-------+---------------+-----------------+---------+------+------+----------------------------------------------------+
就像这般。在已有的索引系统上写出面对新需求效率更高的查询语句，显得格外重要。
![[转]iOS开发使用半透明模糊效果方法整理](http://cc.cocimg.com/api/uploads/20141223/1419302410116248.png)
