MySQL的四种不同查询的分析

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 | +----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+

全表扫描总共话了0.0017730秒

 

各个阶段消耗的时间是：

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)

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

 

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

mysql> select name ,gender from person4pri where id in (1,2); +----------+--------+ | name | gender | +----------+--------+ | zhaoming | male | | wenwen | female | +----------+--------+ 2 rows in set (0.01 sec)

查看其执行计划：

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

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

 

再看其执行消耗的时间：

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

查看更多关于MySQL的四种不同查询的分析的详细内容...