MySQL的四种不同查询的分析

数据库 MySQL
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)  
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.

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"); 
  • 1.
  • 2.
  • 3.

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"); 
  • 1.
  • 2.
  • 3.

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"); 
  • 1.
  • 2.
  • 3.

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");   
  • 1.
  • 2.
  • 3.

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

3.开工测试:

第一步:全表扫描

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

查看其执行计划:

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)  
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.

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

 

查询消耗的时间:

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

全表扫描总共话了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)  
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.

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

 

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

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

查看其执行计划:

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) 
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.

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

 

再看其执行消耗的时间:

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) 
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.

这次查询消耗时间为0.00079200。

 

查看各个阶段消耗的时间:

mysql> show profile for query 63;  
+--------------------------------+----------+  
| Status                         | Duration |  
+--------------------------------+----------+  
| starting                       | 0.000067 |  
| checking query cache for query | 0.000146 |  
| Opening tables                 | 0.000342 |  
| System lock                    | 0.000027 |  
| Table lock                     | 0.000115 |  
| init                           | 0.000056 |  
| optimizing                     | 0.000032 |  
| statistics                     | 0.000069 |  
| preparing                      | 0.000039 |  
| executing                      | 0.000022 |  
| Sending data                   | 0.000100 |  
| end                            | 0.000075 |  
| query end                      | 0.000022 |  
| freeing items                  | 0.000158 |  
| storing result in query cache  | 0.000045 |  
| logging slow query             | 0.000019 |  
| cleaning up                    | 0.000023 |  
+--------------------------------+----------+  
17 rows in set (0.00 sec) 
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.

看出最大的消耗也是在Sending data,第一次也是需要一些初始化操作。

 

第三步:根据非聚集索引查询

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

查看器执行计划:

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

可以看出,走的也是范围索引。同主键查询,那么就看其消耗时间了

mysql> show profiles;  
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+  
| Query_ID | Duration   | Query                                                                                                                                               |  
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+  
|       68 | 0.00106600 | select name ,gender from person4index where gender in ("male","female")                                                                             |  
|       69 | 0.00092500 | explain select name ,gender from person4index where gender in ("male","female")                                                                     |  
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+  
15 rows in set (0.00 sec) 
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.

这个非主键索引消耗的时间为:0.00106600,可以看出略大于组件索引消耗的时间。

 

看其具体消耗的阶段:

mysql> show profile for query 68 ;  
+--------------------------------+----------+  
| Status                         | Duration |  
+--------------------------------+----------+  
| starting                       | 0.000059 |  
| checking query cache for query | 0.000111 |  
| Opening tables                 | 0.000085 |  
| System lock                    | 0.000023 |  
| Table lock                     | 0.000067 |  
| init                           | 0.000183 |  
| optimizing                     | 0.000031 |  
| statistics                     | 0.000139 |  
| preparing                      | 0.000035 |  
| executing                      | 0.000020 |  
| Sending data                   | 0.000148 |  
| end                            | 0.000024 |  
| query end                      | 0.000019 |  
| freeing items                  | 0.000043 |  
| storing result in query cache  | 0.000042 |  
| logging slow query             | 0.000017 |  
| cleaning up                    | 0.000020 |  
+--------------------------------+----------+  
17 rows in set (0.00 sec) 
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.

看几个关键词的点;init,statistics,Sending data 这几个关键点上的消耗向比较主键的查询要大很多,特别是Sending data。因为若是走的非聚集索引,那么就需要回表进行再进行一次查询,多消耗一次IO。

 

第四部:根据覆盖索引查询数据

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

这里需要注意的是,我的字段查询顺序变了,是gender,name而不在是前面的name,gender,这样是为了走覆盖索引。具体看效果吧

 

还是先看执行计划:

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

最后栏Extra中表示走的就是覆盖索引。

 

看消耗的时间吧:

mysql> show profiles;  
+----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
| Query_ID | Duration   | Query                                                                                                                                                            |  
+----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
|       83 | 0.00115400 | select gender ,name from person4cindex where gender in ("male","female")                                                                                         |  
|       84 | 0.00074000 | explain select gender ,name from person4cindex where gender in ("male","female")                                                                                 |  
+----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.

我们看到消耗的时间是0.00115400,看这个数字好像挺高的,那么都花在什么地方了呢?

 

看下具体的消耗情况:

mysql> show profile for query 83 ;  
+--------------------------------+----------+  
| Status                         | Duration |  
+--------------------------------+----------+  
| starting                       | 0.000083 |  
| checking query cache for query | 0.000113 |  
| Opening tables                 | 0.000039 |  
| System lock                    | 0.000026 |  
| Table lock                     | 0.000075 |  
| init                           | 0.000128 |  
| optimizing                     | 0.000193 |  
| statistics                     | 0.000056 |  
| preparing                      | 0.000038 |  
| executing                      | 0.000021 |  
| Sending data                   | 0.000121 |  
| end                            | 0.000042 |  
| query end                      | 0.000021 |  
| freeing items                  | 0.000112 |  
| storing result in query cache  | 0.000043 |  
| logging slow query             | 0.000021 |  
| cleaning up                    | 0.000022 |  
+--------------------------------+----------+  
17 rows in set (0.00 sec)  
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.

很惊奇吧,在初始化和优化上消耗了这么多时间,取数据基恩差不多。

 

总  结:

有了上面这些数据,那么我们整理下吧。未存在缓存下的数据。

 

看这个表,全表扫描最慢,我们可以理解,同时主键查询比覆盖所有扫描慢也还能接受,但是为什么主键扫描会比非主键扫描慢?而且非主键查询需要消耗的1次查询的io+一次回表的查询IO,理论上是要比主键扫描慢,而出来的数据缺不是如此。那么就仔细看下是个查询方式在各个主要阶段消耗的时间吧。

查询是否存在缓存,打开表及锁表这些操作时间是差不多,我们不会计入。具体还是看init,optimizing等环节消耗的时间。

1.从这个表中,我们看到非主键索引和覆盖索引在准备时间上需要开销很多的时间,预估这两种查询方式都需要进行回表操作,所以花在准备上更多时间。

2.第二项optimizing上,可以清晰知道,覆盖索引话在优化上大量的时间,这样在二级索引上就无需回表。

3. Sendingdata,全表扫描慢就慢在这一项上,因为是加载所有的数据页,所以花费在这块上时间较大,其他三者都差不多。

4. 非主键查询话在freeingitems上时间最少,那么可以看出它在读取数据块的时候最少。

5.相比较主键查询和非主键查询,非主键查询在Init,statistics都远高于主键查询,只是在freeingitems开销时间比主键查询少。因为这里测试数据比较少,但是我们可以预见在大数据量的查询上,不走缓存的话,那么主键查询的速度是要快于非主键查询的,本次数据不过是太小体现不出差距而已。

6.在大多数情况下,全表扫描还是要慢于索引扫描的。

tips:

过程中的辅助命令:

1.清楚缓存

reset query cache ;

flush tables;

 

2.查看表的索引:

show index from tablename;

原文链接:http://inter12.iteye.com/blog/1430144

【编辑推荐】

  1. 甲骨文推出MySQL集群7.2
  2. MySQL Workbench 5.2.38发布
  3. MySQL内存表的特性与使用介绍
  4. 利用Java进行MySql数据库的导入和导出
  5. MySQL 5.5.21 GA发布(附下载)
责任编辑:林师授 来源: inter12的博客
相关推荐

2020-11-10 10:08:41

Kubernetes容器开发

2023-11-05 09:00:00

Linux命令MySQL

2020-06-24 07:49:13

Kubernetes场景网络

2017-08-09 14:34:12

MysqlJavaPython

2017-08-01 23:44:25

数据分析数据科学数据

2020-12-09 10:56:15

业务分析数据分析大数据

2020-12-09 11:21:48

大数据数据分析

2010-04-23 14:47:05

Oracle Data

2017-07-27 14:01:51

大数据数据分析类型模式

2021-08-12 11:37:23

数据分析错误

2018-01-03 08:52:27

MySQL数据库级别

2017-09-21 13:04:35

数据挖掘分析分析方法数据分析师

2020-12-10 16:15:04

物联网物联网分析计算类型

2011-11-24 16:34:39

Java

2021-09-01 10:05:04

机器学习人工智能算法

2024-09-26 14:27:14

2012-09-11 09:55:26

编程HTML5编程能力

2019-10-24 07:42:28

Java引用GC

2017-07-06 15:40:19

DevOps核心能力

2011-03-16 09:05:53

NATiptables
点赞
收藏

51CTO技术栈公众号