1.前置条件：

本次是基于小数据量，且数据块在一个页中的最理想情况进行分析，可能无具体的实际意义，但是可以借鉴到各种复杂条件下，因为原理是相同的,知小见大，见微知著！

打开语句分析并确认是否已经打开

1. mysql> set profiling=1;
2. Query OK, 0 rows affected (0.00 sec)
3.  
4. mysql> select @@profiling;
5. +-------------+
6. | @@profiling |
7. +-------------+
8. | 1 |
9. +-------------+
10. 1 row in set (0.01 sec)

2.数据准备：

2.1全表扫描数据

1. create table person4all(id int not null auto_increment, name varchar(30) not null, gender varchar(10) not null ,primary key(id));
2. insert into person4all(name,gender) values("zhaoming","male");
3. insert into person4all(name,gender) values("wenwen","female");

2.2根据主键查看数据

1. create table person4pri(id int not null auto_increment, name varchar(30) not null, gender varchar(10) not null ,primary key(id));
2. insert into person4pri(name,gender) values("zhaoming","male");
3. insert into person4pri(name,gender) values("wenwen","female");

2.3根据非聚集索引查数据

1. create table person4index(id int not null auto_increment, name varchar(30) not null, gender varchar(10) not null ,primary key(id) , index(gender));
2. insert into person4index(name,gender) values("zhaoming","male");
3. insert into person4index(name,gender) values("wenwen","female");

2.4根据覆盖索引查数据

1. create table person4cindex(id int not null auto_increment, name varchar(30) not null, gender varchar(10) not null ,primary key(id) , index(name,gender));
2. insert into person4cindex(name,gender) values("zhaoming","male");
3. insert into person4cindex(name,gender) values("wenwen","female");

主要从以下几个方面分析：查询消耗的时间，走的执行计划等方面。

3.开工测试：

第一步：全表扫描

1. mysql> select * from person4all ;
2. +----+----------+--------+
3. | id | name | gender |
4. +----+----------+--------+
5. | 1 | zhaoming | male |
6. | 2 | wenwen | female |
7. +----+----------+--------+
8. 2 rows in set (0.00 sec)

查看其执行计划：

1. mysql> explain select * from person4all;
2. +----+-------------+------------+------+---------------+------+---------+------+------+-------+
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4. +----+-------------+------------+------+---------------+------+---------+------+------+-------+
5. | 1 | SIMPLE | person4all | ALL | NULL | NULL | NULL | NULL | 2 | |
6. +----+-------------+------------+------+---------------+------+---------+------+------+-------+
7. 1 row in set (0.01 sec)

我们可以很清晰的看到走的是全表扫描，而没有走索引！

查询消耗的时间：

1. mysql> show profiles;
2. +----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+
3. | Query_ID | Duration | Query |
4. | 54 | 0.00177300 | select * from person4all |
5. | 55 | 0.00069200 | explain select * from person4all |
6. +----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+

全表扫描总共话了0.0017730秒

各个阶段消耗的时间是：

1. mysql> show profile for query 54;
2. +--------------------------------+----------+
3. | Status | Duration |
4. +--------------------------------+----------+
5. | starting | 0.000065 |
6. | checking query cache for query | 0.000073 |
7. | Opening tables | 0.000037 |
8. | System lock | 0.000024 |
9. | Table lock | 0.000053 |
10. | init | 0.000044 |
11. | optimizing | 0.000022 |
12. | statistics | 0.000032 |
13. | preparing | 0.000030 |
14. | executing | 0.000020 |
15. | Sending data | 0.001074 |
16. | end | 0.000091 |
17. | query end | 0.000020 |
18. | freeing items | 0.000103 |
19. | storing result in query cache | 0.000046 |
20. | logging slow query | 0.000019 |
21. | cleaning up | 0.000020 |
22. +--------------------------------+----------+
23. 17 rows in set (0.00 sec)

第一次不走缓存的话，需要检查是否存在缓存中，打开表，初始化等操作，最大的开销在于返回数据。

第二步：根据主键查询数据。

1. mysql> select name ,gender from person4pri where id in (1,2);
2. +----------+--------+
3. | name | gender |
4. +----------+--------+
5. | zhaoming | male |
6. | wenwen | female |
7. +----------+--------+
8. 2 rows in set (0.01 sec)

查看其执行计划：

1. mysql> explain select name ,gender from person4pri where id in (1,2);
2. +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4. +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
5. | 1 | SIMPLE | person4pri | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |
6. +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
7. 1 row in set (0.00 sec)

从执行计划中我们可以看出，走的是范围索引。

再看其执行消耗的时间：

1. mysql> show profiles;
2. +----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+
3. | Query_ID | Duration | Query |
4. +----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+
5. | 63 | 0.00135700 | select name ,gender from person4pri where id in (1,2) |
6. | 64 | 0.00079200 | explain select name ,gender from person4pri where id in (1,2) |
7. +----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+
8. 15 rows in set (0.01 sec)