`

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)
 

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)
 

这次查询消耗时间为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)
 

看出最大的消耗也是在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)

查看器执行计划：

 

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)
 

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

 

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)
 

这个非主键索引消耗的时间为：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)
 

看几个关键词的点；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)
 

这里需要注意的是，我的字段查询顺序变了，是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)
 

最后栏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")                                                                                 |
+----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 

我们看到消耗的时间是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次查询的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;

2
顶
0
踩
分享到：
| MYSQL中SQL执行分析
评论
3 楼 syw19901001 2014-12-06  
30多条mysql数据库优化方法,千万级数据库记录查询轻松解决
http://www.ihref.com/read-16422.html
2 楼 inter12 2012-02-29  
lmxbitihero 写道
mysql做同样查询执行时间不一定一样。得根据cpu，内存，硬盘的情况而定。你的思路很好，但是最好是弄个大数据量，否则没说服力。

嗯，对于你这点我在文章开始就已经提到了，只是给大家提供一个解决思路的思路。因为不同的环境下影响因素很多，所以若是我在这提出一个我自己的复杂环境下的数据也并不能带给大家多少帮助，还不如在一个单纯的环境下展示怎么去处理这类问题。
1 楼 lmxbitihero 2012-02-28  
mysql做同样查询执行时间不一定一样。得根据cpu，内存，硬盘的情况而定。你的思路很好，但是最好是弄个大数据量，否则没说服力。
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    尚硅谷mysql高级：索引、优化

    一张表只能有一个聚集索引，但可以有多个非聚集索引。 5. 复合索引：当需要基于多列进行排序或过滤时，可以创建复合索引，索引顺序很重要，应根据查询条件的频率和选择性进行调整。 6. 空间索引：用于地理空间数据...

    mysql 索引与执行计划

    2. **数据读取操作的操作类型**：例如，是否使用了索引扫描或全表扫描。 3. **可使用的索引**：列出可用于查询的索引。 4. **实际使用的索引**：指出哪些索引在查询过程中被实际使用。 5. **表之间的引用**：展示表...

    书籍：Oracle与MySQL数据库索引设计与优化

    2. 索引覆盖：如果查询只涉及到索引中的列，MySQL和Oracle都可能避免全表扫描，提高性能。 3. 避免空值索引：空值在索引中占特殊位置，可能导致索引效率下降。 4. 索引维护：定期分析和优化索引，删除无用的或低效...

    干货！MySQL常见的面试题＋索引原理分析.docx

    当对表中的某个字段创建索引后，数据库系统可以不必全表扫描，而是直接通过索引找到所需数据，从而大大提高查询效率。在MySQL中，索引主要分为两种类型：聚集索引（Clustered Index）和非聚集索引（Secondary Index...

    MySQL高级篇-索引（Index）的数据结构

    2. 非聚集索引（Secondary Index）：索引结构独立于数据行，包含指向数据行的指针。非聚集索引的叶子节点通常存储的是主键的值，而不是完整的行数据。 五、唯一索引与非唯一索引 1. 唯一索引（Unique Index）：确保...

    MySql索引知识点整理（一）

    非聚集索引（二级索引）则包含索引键和主键值，通过主键值再回表查找实际数据。 当我们创建索引时，应考虑以下因素：索引的选择性，即索引键值的唯一性，选择性越高，查询效率越高；索引的宽度，索引列越多，索引...

    数据库索引

    相反，非聚集索引的叶子节点除了包含键值外，还会包含一个“书签”（Bookmark），用于指示InnoDB存储引擎如何找到与索引对应的行数据。具体来说，InnoDB存储引擎会先遍历辅助索引，通过叶子级别的指针获取指向主键...

    MySQL索引及其原理1

    MyISAM使用非聚集索引，数据和索引分开存储，而InnoDB使用聚集索引，索引和数据在同一结构中。 7. **如何使用索引** - **创建索引**：可以在创建表时指定，也可在创建后添加。 - **删除索引**：当不再需要索引时...

    存储基础知识——索引的本质

    例如，对频繁查询但更新少的列创建索引，对小表或全表扫描更高效的表不创建索引。 4. 索引选择性：索引的选择性是指不同索引值的数目与总记录数的比例，比例越高，索引的区分度越大，查询效率也越高。但索引过多也...

    对mysql索引的研究和学习.docx

    - 优化数据访问：在大量数据中，索引可以避免全表扫描，直接定位到所需数据。 2. 索引的存储： - 索引文件通常以文件形式存储在磁盘上，具体存储形式与所使用的存储引擎有关，如InnoDB引擎使用B+树作为索引结构。...

    MySQL索引背后的数据结构及算法原理.pdf

    InnoDB的主键索引是聚集索引，数据行和索引键值存储在一起，而非聚集索引则分开存储，索引结构包含了指向数据行的指针。覆盖索引是一种优化策略，当查询只需要索引列而无需回表获取数据行时，可以直接从索引中获取...

    MySQL索引原理与实践.pptx

    在MySQL中，区分一级索引（主键索引）和二级索引（辅助索引）是很重要的。一级索引（聚集索引）的叶子节点直接存储了完整的数据行，数据行按照主键的顺序排列，因此基于范围的查找非常快速。然而，插入速度会受到...

    MySQL索引背后的数据结构及算法原理.docx

    在数据库查询中，避免全表扫描，利用索引进行快速定位，是提高查询效率的关键。例如，二叉查找树和二分查找虽然在有序数据中表现优秀，但并不适用于所有情况。而BTree则是一种自平衡的多路搜索树，能够保持数据排序...

    MySQL常见面试题总结.docx

    对于小表来说，全表扫描可能比使用索引更快。对于大型表，尤其是非常大的表，索引维护成本会随着数据量的增加而增加。在这种情况下，可以考虑使用分区等技术来处理数据。其次，对于很少出现在查询条件中的列，或者...

    数据库两大神器【索引和锁】1

    在没有索引的情况下，数据库需要按顺序遍历所有记录，对于大数据量的表，这种全表扫描效率极低。索引通过创建一种数据结构，如B+树，使得数据变得有序，从而能够快速定位到所需数据。当我们使用如`SELECT * FROM ...

    北邮大三下数据库实验六mysql版本.pdf

    - 考虑查询语句的编写方式，如是否能避免全表扫描，合理使用`group by`和`having`。 - 对于插入大量数据时，需要权衡索引带来的增效与插入速度的损失。 结论： MySQL的索引策略对查询性能有显著影响，尤其是在...

    mysql 学习总结

    因此，对于主键查询，InnoDB可以直接从索引页获取数据，而对于非主键索引，需要通过二级索引来找到主键，然后通过主键索引找到数据行。 内存数据库是指数据存储在内存中的数据库系统，如MySQL的内存表（MEMORY引擎...

    [每天进步一点点]mysql笔记整理（三）：索引

    理解并合理使用这些索引类型和原则，可以有效地优化MySQL数据库的查询性能，减少不必要的全表扫描，提升数据库应用的响应速度。在设计数据库时，需根据业务需求和查询模式，慎重考虑索引的选择和创建，以达到最佳的...

    MySQL查询优化实践-最终版.pdf

    例如，创建合适的索引，避免全表扫描，以及正确使用WHERE子句来过滤不必要的数据。在上述示例中，`UserInfo`表有三个索引：主键`userid`，唯一索引`idx_username`和普通索引`idx_registdate`。在执行查询时，MySQL...

Magicbox
Global site tag (gtag.js) - Google Analytics