1、前置条件：
本次是基于小数据量，且数据块在一个页中的最理想情况进行分析，可能无具体的实际意义，但是可以借鉴到各种复杂条件下，因为原理是相同的,知小见大，见微知著！
打开语句分析并确认是否已经打开
- mysql> set profiling=1;
- Query OK, 0 rows affected (0.00 sec)
- mysql> select @@profiling;
- +-------------+
- | @@profiling |
- +-------------+
- | 1 |
- +-------------+
- 1 row in set (0.01 sec)
|
2、数据准备：
2.1 全表扫描数据
- create table person4all(id int not null auto_increment, name varchar(30) not null, gender varchar(10) not null ,primary key(id));
- insert into person4all(name,gender) values("zhaoming","male");
- insert into person4all(name,gender) values("wenwen","female");
|
2.2 根据主键查看数据
- create table person4pri(id int not null auto_increment, name varchar(30) not null, gender varchar(10) not null ,primary key(id));
- insert into person4pri(name,gender) values("zhaoming","male");
- insert into person4pri(name,gender) values("wenwen","female");
|
2.3 根据非聚集索引查数据
- create table person4index(id int not null auto_increment, name varchar(30) not null, gender varchar(10) not null ,primary key(id) , index(gender));
- insert into person4index(name,gender) values("zhaoming","male");
- insert into person4index(name,gender) values("wenwen","female");
|
2.4 根据覆盖索引查数据
- 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));
- insert into person4cindex(name,gender) values("zhaoming","male");
- insert into person4cindex(name,gender) values("wenwen","female");
|
主要从以下几个方面分析：查询消耗的时间，走的执行计划等方面。
3、开工测试：
第一步：全表扫描
- mysql> select * from person4all ;
- +----+----------+--------+
- | id | name | gender |
- +----+----------+--------+
- | 1 | zhaoming | male |
- | 2 | wenwen | female |
- +----+----------+--------+
- 2 rows in set (0.00 sec)
|
查看其执行计划：
- mysql> explain select * from person4all;
- +----+-------------+------------+------+---------------+------+---------+------+------+-------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+------------+------+---------------+------+---------+------+------+-------+
- | 1 | SIMPLE | person4all | ALL | NULL | NULL | NULL | NULL | 2 | |
- +----+-------------+------------+------+---------------+------+---------+------+------+-------+
- 1 row in set (0.01 sec)
|
我们可以很清晰的看到走的是全表扫描，而没有走索引！
查询消耗的时间：
- mysql> show profiles;
- +----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+
- | Query_ID | Duration | Query |
- | 54 | 0.00177300 | select * from person4all |
- | 55 | 0.00069200 | explain select * from person4all |
- +----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+
|
