mysql索引的使用

数据库的explain关键字和联合索引优化：

本篇文章简单的说一下mysql查询的优化以及explain语句的使用。（新手向）

因为这篇文章是面向查询的，直观一点，首先我们创建一个表：
student 结构如下

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`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8 |
+---------+----------------------------------------------------------
--------------------+
1 row in set (0.00 sec)

表中我预置了一些数据，如下所示：
mysql> select * from student;
+----+-------------+-----------+------------+
| ID | CName | ClassName | SchoolName |
+----+-------------+-----------+------------+
| 12 | rockderia1 | 一年一班 | 花园中学 |
| 13 | rockderia2 | 一年一班 | 花园中学 |
| 14 | rockderia3 | 一年一班 | 花园中学 |
| 15 | rockderia4 | 一年一班 | 花园中学 |
| 16 | rockderia5 | 一年一班 | 花园中学 |
| 17 | rockderia6 | 一年一班 | 花园中学 |
| 18 | rockderia7 | 一年一班 | 花园中学 |
| 19 | rockderia8 | 一年一班 | 花园中学 |
| 20 | rockderia01 | 一年二班 | 花园中学 |
| 21 | rockderia02 | 一年二班 | 花园中学 |
| 22 | rockderia03 | 一年二班 | 花园中学 |
| 23 | rockderia04 | 一年二班 | 花园中学 |
| 24 | rockderia05 | 一年二班 | 花园中学 |
| 25 | rockderia06 | 一年二班 | 花园中学 |
| 26 | rockderia07 | 一年二班 | 花园中学 |
| 27 | rockderia08 | 一年二班 | 花园中学 |
| 28 | rockderia11 | 一年一班 | 冷锋中学 |
| 29 | rockderia12 | 一年二班 | 冷锋中学 |
| 30 | rockderia13 | 一年二班 | 冷锋中学 |
| 31 | rockderia14 | 一年二班 | 冷锋中学 |
| 32 | rockderia21 | 一年一班 | 拓荒学院 |
| 33 | rockderia22 | 一年一班 | 拓荒学院 |
| 34 | rockderia23 | 一年一班 | 拓荒学院 |
| 35 | rockderia24 | 一年一班 | 拓荒学院 |
| 36 | rockderia25 | 一年二班 | 拓荒学院 |
| 37 | rockderia26 | 一年二班 | 拓荒学院 |
+----+-------------+-----------+------------+
数据量不是特别的多。所以不管我们怎么查询，其实都可以很快的获得结果。这也是新手学习数据库的时候很容易陷入的一个误区。当接触到大数据的时候，有的时候就会发现我们的查询结果有的时候会很慢。

比如，上边的表的数据如果是100万行，如果你用学校名、班级名、姓名来锁定所查询的结果，那么你会发现找出结果会花费很多很多的时间。如下：

select * from student where CName = "rockderia1" and ClassName = "一年一班" and SchoolName = "花园中学";

但是，如果我们使用另一则搜索语句：

select * from student where ID = 12;

你会发现会瞬间获得结果。我们首先来了解一下explain关键字。这个关键字是专门针对select语句查询的，用法就是加在select语句之前。我们执行一下下面的语句：

explain select * from student where CName = "rockderia1" and ClassName = "一年一班" and SchoolName = "花园中学";

获得结果集
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | student | ALL | NULL | NULL | NULL | NULL | 26 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

关于这条结果集，我们先主要看这几个字段 type、key、rows。

其中
[1]type 显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：

system > const > eq_ref > ref >fulltext > ref_or_null > index_merge > unique_subquery >index_subquery > range > index > ALL

我们的ALL是最差的...

[2]key 显示MySQL实际决定使用的键。如果没有索引被选择，键是NULL。

我们没有用到索引，值是NULL

[3]rows 现实这个数表示mysql要遍历多少数据才能找到。
我们的这次查询遍历了26条数据，就是整张表的数据行数...

由此可见，如果不做处理，这条查询语句其实是将表中的26条数据都当作查询目标结果。然后针对这些结果逐条去判断是否满足where后的条件。

我们再来执行以下如下语句：

mysql> explain select * from student where ID = 12;
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | student | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+

我们看到果然和上边那句语句有很大的不同。首先遍历的语句数竟然变成了一条（rows = 1），并且key也有了值，叫做PRIMARY。type更是变成了效率非常高的 const

其实，这两则语句最大的区别就是下面的那则语句我们的搜索条件用到了索引。

这里引出一个概念：索引。
关于索引的需要先知道以下几点问题：

1.索引的用途？
索引的目的是为了提升查询速度。

2.索引的弊端？
天下没有免费的午餐，所以提升查询效率的同时，维护索引需要在表增删改的时候花费时间去维护，同时索引需要空间去维护（维持一定的结构）。

3.索引分类
索引一般分为：普通索引、唯一索引、自增主键、普通主键。

4.索引和主键的区别？
[1]主键是可以唯一标记一行数据的列或列组合。重点是唯一性。索引所组成的组合不一定会标记唯一的一行数据。除非是一组唯一索引。
[2]唯一索引和主键类似，是不允许重复的。但是唯一索引的值可能为空。主键不可以。所以可以说主键是唯一索引，但是反之不可以。
[3]创建主键的同时会默认创建一则索引。

5.索引的查询原理。
索引的用途就是方便查找，他需要和真实的数据划分界限。这里我打一个比方。数据库中的每行数据，我们给他比作旅馆里的一个房间。而索引系统就好比一张平面图，上面画了所有的房间。常见的自增长主键，自带一条索引，
所以维护这套索引就需要在平面图上每个房间的位置上都标上对应的房间号。所以服务员接到任务给301房间送水的时候，服务员没必要挨个房间进屋问：这里是不是301呀？有你们的水！ 而是在平面图上一查，发现301的位置
在三楼堵头，直接过去给301送水。这就是为什么上述利用主键查询的时候遍历的数据只有1的原因。
但有的时候需求就没这么简单了，假如旅馆的房间都是固定的都有1234四个床位。服务员又接到需求，房间里边1号床住着张三，2号床住着李四，3号床住着张飞的那个房间要了吃的。服务员看了看平面图，瞬间懵逼。无奈他只
好挨个房间去问1号床是谁..二号床是谁..对上号了，还不能停止，因为，巧了！说不定还有另外一个房间一床也叫张三，二床也叫李四，三床也叫张飞，你也得给他拿一份吃的！所以不多解释，所有房间都得墨迹一遍。
其实刚刚的
select * from student where CName = "rockderia1" and ClassName = "一年一班" and SchoolName = "花园中学";
就是完成的这个过程了。现在终于能理解当数据有很多的时候为什么查询的这么慢了。

这个时候就会说了，在平面图上贴上每个房间的人的名字不就好了！没错，这就是自定义索引的过程！当然维护索引（修改平面图）需要很长的的时间，但是一旦维护完毕，再去按照需求寻找将会无比简单（所有的查询动作全在平面图，
也就是索引上完成了）

6.索引的实现原理。
上边的例子很直观了，其实就算是把所有的要查询的数据都放在了索引里维护，在索引中查询的时候也得需要一个的结构。一般对于索引而言方便搜索的结构可能为（B树、B+树）或者hash结构。具体是什么样的结构要取决于你用什么样的数据库存储引擎
（innodb就是引擎的名字）。hash结构的查询复杂度最低，直接根据坐标锁定一个值。而B树的查询复杂度为树的深度。但是支持模糊查询。

简单了解完索引，我们来试一下。试着给student表创建一条索引。使得上述的查询效率更高。

create index NameClassSchool on student(CName, ClassName, SchoolName);

我们发现show create table当中多出一则信息： KEY `NameClassSchool` (`CName`,`ClassName`,`SchoolName`)

再去执行：
explain select * from student where CName = "rockderia1" and ClassName = "一年一班" and SchoolName = "花园中学";

mysql> explain select * from student where CName = "rockderia1" and ClassName = "一年一班" and SchoolName = "花园中学";
+----+-------------+---------+------+-----------------+-----------------+---------+-------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+-----------------+-----------------+---------+-------------------+------+--------------------------+
| 1 | SIMPLE | student | ref | NameClassSchool | NameClassSchool | 189 | const,const,const | 1 | Using where; Using index |
+----+-------------+---------+------+-----------------+-----------------+---------+-------------------+------+--------------------------+

可以看出，rows变成了1。说明除了索引操作外，只寻找了/操作了一行数据而已。

mysql索引的使用