MYSQL的全表扫描,主键索引(聚集索引、第一索引),非主键索引(非聚集索引、第二索引),覆盖索引四种不同查询的分析

By Lee - Last updated: 星期二, 九月 10, 2013

MYSQL的全表扫描,主键索引(聚集索引、第一索引),非主键索引(非聚集索引、第二索引),覆盖索引四种不同查询的分析

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

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

Java代码 收藏代码
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全表扫描数据

Java代码 收藏代码
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根据主键查看数据

Java代码 收藏代码
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根据非聚集索引查数据

Java代码 收藏代码
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根据覆盖索引查数据

Java代码 收藏代码
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.开工测试:
第一步:全表扫描

Java代码 收藏代码
mysql> select * from person4all ;
+—-+———-+——–+
| id | name | gender |
+—-+———-+——–+
| 1 | zhaoming | male |
| 2 | wenwen | female |
+—-+———-+——–+
2 rows in set (0.00 sec)

查看其执行计划:

Java代码 收藏代码
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)

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

查询消耗的时间:

Java代码 收藏代码
mysql> show profiles;
+———-+————+———————————————————————————————————————————–+
| Query_ID | Duration | Query |
| 54 | 0.00177300 | select * from person4all |
| 55 | 0.00069200 | explain select * from person4all |
+———-+————+———————————————————————————————————————————–+

全表扫描总共话了0.0017730秒

各个阶段消耗的时间是:

Java代码 收藏代码
mysql> show profile for query 54;
+——————————–+———-+
| Status | Duration |
+——————————–+———-+
| starting | 0.000065 |
| checking query cache for query | 0.000073 |
| Opening tables | 0.000037 |
| System lock | 0.000024 |
| Table lock | 0.000053 |
| init | 0.000044 |
| optimizing | 0.000022 |
| statistics | 0.000032 |
| preparing | 0.000030 |
| executing | 0.000020 |
| Sending data | 0.001074 |
| end | 0.000091 |
| query end | 0.000020 |
| freeing items | 0.000103 |
| storing result in query cache | 0.000046 |
| logging slow query | 0.000019 |
| cleaning up | 0.000020 |
+——————————–+———-+
17 rows in set (0.00 sec)

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

发表在 数据库优化 • • ↑Top 文章来源

Pages: 1 2 3