1. 树叶云首页
  2. 技术资讯

MySQL的四种不同查询的分析

运维••技术资讯

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) 

这次查询消耗时间为0.00079200。

 

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

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

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

 

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

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

查看器执行计划：

  1. mysql> explain select name ,gender from person4index where gender in ("male","female");  
  2. +----+-------------+--------------+-------+---------------+--------+---------+------+------+-------------+  
  3. | id | select_type | table        | type  | possible_keys | key    | key_len | ref  | rows | Extra       |  
  4. +----+-------------+--------------+-------+---------------+--------+---------+------+------+-------------+  
  5. |  1 | SIMPLE      | person4index | range | gender        | gender | 12      | 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. |       68 | 0.00106600 | select name ,gender from person4index where gender in ("male","female")                                                                             |  
  6. |       69 | 0.00092500 | explain select name ,gender from person4index where gender in ("male","female")                                                                     |  
  7. +----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+  
  8. 15 rows in set (0.00 sec) 

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

 

看其具体消耗的阶段：

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

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

 

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

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

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

 

还是先看执行计划：

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

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

 

看消耗的时间吧：

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

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

 

看下具体的消耗情况：

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

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

 

总  结：

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

 

看这个表，全表扫描最慢，我们可以理解，同时主键查询比覆盖所有扫描慢也还能接受，但是为什么主键扫描会比非主键扫描慢？而且非主键查询需要消耗的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发布(附下载)

文章来源网络，作者：运维，如若转载，请注明出处：https://shuyeidc.com/wp/254539.html<

赞 (0)
运维的头像运维
00
上一篇2025-04-30 10:21
下一篇 2025-04-30 10:23

相关推荐

  • 如何制作个人主题技术资讯

    个人主题怎么制作？

    制作个人主题是一个将个人风格、兴趣或专业领域转化为视觉化或结构化内容的过程，无论是用于个人博客、作品集、社交媒体账号还是品牌形象，核心都是围绕“个人特色”展开，以下从定位、内容规划、视觉设计、技术实现四个维度,详细拆解制作个人主题的完整流程，明确主题定位：找到个人特色的核心主题定位是所有工作的起点，需要先回答……

    运维的头像运维
    2025-11-20
    0
  • 如何管理社群营销技术资讯

    社群营销管理关键是什么？

    社群营销的核心在于通过建立有温度、有价值、有归属感的社群，实现用户留存、转化和品牌传播，其管理需贯穿“目标定位-内容运营-用户互动-数据驱动-风险控制”全流程,以下从五个维度展开详细说明：明确社群定位与目标社群管理的首要任务是精准定位，需明确社群的核心价值（如行业交流、产品使用指导、兴趣分享等）、目标用户画像……

    运维的头像运维
    2025-11-20
    0
  • 香港公司如何网站备案技术资讯

    香港公司网站备案需要什么材料？

    香港公司进行网站备案是一个涉及多部门协调、流程相对严谨的过程，尤其需兼顾中国内地与香港两地的监管要求，由于香港公司注册地与中国内地不同，其网站若主要服务内地用户或使用内地服务器，需根据服务器位置、网站内容性质等，选择对应的备案路径（如工信部ICP备案或公安备案），以下从备案主体资格、流程步骤、材料准备、注意事项……

    运维的头像运维
    2025-11-20
    0
  • 如何企业上云推广技术资讯

    如何企业上云推广

    企业上云已成为数字化转型的核心战略,但推广过程中需结合行业特性、企业痛点与市场需求，构建系统性、多维度的推广体系，以下从市场定位、策略设计、执行落地及效果优化四个维度，详细拆解企业上云推广的实践路径，精准定位：明确目标企业与核心价值企业上云并非“一刀切”的方案，需先锁定目标客户群体，提炼差异化价值主张，客户分层……

    运维的头像运维
    2025-11-20
    0
  • PS如何设计搜索框技术资讯

    PS设计搜索框的实用技巧有哪些？

    在PS中设计一个美观且功能性的搜索框需要结合创意构思、视觉设计和用户体验考量，以下从设计思路、制作步骤、细节优化及交互预览等方面详细说明，帮助打造符合需求的搜索框，设计前的规划明确使用场景：根据网站或APP的整体风格确定搜索框的调性，例如极简风适合细线条和纯色，科技感适合渐变和发光效果，电商类则可能需要突出搜索……

    运维的头像运维
    2025-11-20
    0

发表回复

您的邮箱地址不会被公开。必填项已用 * 标注