MYSQL的全表扫描 主键索引(聚集索引 第一索引) 非主键索引(非聚集索引 第二索引)

文章出处：/blog/1430144

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

1.前置条件：

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

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

Java代码 mysql>setprofiling=1;QueryOK,0rowsaffected(0.00sec)mysql>select@@profiling;+-------------+|@@profiling|+-------------+|1|+-------------+1rowinset(0.01sec)

2.数据准备：

2.1全表扫描数据

Java代码 createtableperson4all(idintnotnullauto_increment,namevarchar(30)notnull,gendervarchar(10)notnull,primarykey(id));insertintoperson4all(name,gender)values("zhaoming","male");insertintoperson4all(name,gender)values("wenwen","female");

2.2根据主键查看数据

Java代码 createtableperson4pri(idintnotnullauto_increment,namevarchar(30)notnull,gendervarchar(10)notnull,primarykey(id));insertintoperson4pri(name,gender)values("zhaoming","male");insertintoperson4pri(name,gender)values("wenwen","female");

2.3根据非聚集索引查数据

Java代码 createtableperson4index(idintnotnullauto_increment,namevarchar(30)notnull,gendervarchar(10)notnull,primarykey(id),index(gender));insertintoperson4index(name,gender)values("zhaoming","male");insertintoperson4index(name,gender)values("wenwen","female");

2.4根据覆盖索引查数据

Java代码 createtableperson4cindex(idintnotnullauto_increment,namevarchar(30)notnull,gendervarchar(10)notnull,primarykey(id),index(name,gender));insertintoperson4cindex(name,gender)values("zhaoming","male");insertintoperson4cindex(name,gender)values("wenwen","female");

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

3.开工测试：

第一步：全表扫描

Java代码 mysql>select*fromperson4all;+----+----------+--------+|id|name|gender|+----+----------+--------+|1|zhaoming|male||2|wenwen|female|+----+----------+--------+2rowsinset(0.00sec)

查看其执行计划：

Java代码 mysql>explainselect*fromperson4all;+----+-------------+------------+------+---------------+------+---------+------+------+-------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+------------+------+---------------+------+---------+------+------+-------+|1|SIMPLE|person4all|ALL|NULL|NULL|NULL|NULL|2||+----+-------------+------------+------+---------------+------+---------+------+------+-------+1rowinset(0.01sec)

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

查询消耗的时间：

Java代码 mysql>showprofiles;+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+|Query_ID|Duration|Query||54|0.00177300|select*fromperson4all||55|0.00069200|explainselect*fromperson4all|+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+

全表扫描总共话了0.0017730秒

各个阶段消耗的时间是：

Java代码 mysql>showprofileforquery54;+--------------------------------+----------+|Status|Duration|+--------------------------------+----------+|starting|0.000065||checkingquerycacheforquery|0.000073||Openingtables|0.000037||Systemlock|0.000024||Tablelock|0.000053||init|0.000044||optimizing|0.000022||statistics|0.000032||preparing|0.000030||executing|0.000020||Sendingdata|0.001074||end|0.000091||queryend|0.000020||freeingitems|0.000103||storingresultinquerycache|0.000046||loggingslowquery|0.000019||cleaningup|0.000020|+--------------------------------+----------+17rowsinset(0.00sec)

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

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

Java代码 mysql>selectname,genderfromperson4priwhereidin(1,2);+----------+--------+|name|gender|+----------+--------+|zhaoming|male||wenwen|female|+----------+--------+2rowsinset(0.01sec)

查看其执行计划:

Java代码 mysql>explainselectname,genderfromperson4priwhereidin(1,2);+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+|1|SIMPLE|person4pri|range|PRIMARY|PRIMARY|4|NULL|2|Usingwhere|+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+1rowinset(0.00sec)

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

再看其执行消耗的时间：

Java代码 mysql>showprofiles;+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+|Query_ID|Duration|Query|+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+|63|0.00135700|selectname,genderfromperson4priwhereidin(1,2)||64|0.00079200|explainselectname,genderfromperson4priwhereidin(1,2)|+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+15rowsinset(0.01sec)

这次查询消耗时间为0.00079200。

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

Java代码 mysql>showprofileforquery63;+--------------------------------+----------+|Status|Duration|+--------------------------------+----------+|starting|0.000067||checkingquerycacheforquery|0.000146||Openingtables|0.000342||Systemlock|0.000027||Tablelock|0.000115||init|0.000056||optimizing|0.000032||statistics|0.000069||preparing|0.000039||executing|0.000022||Sendingdata|0.000100||end|0.000075||queryend|0.000022||freeingitems|0.000158||storingresultinquerycache|0.000045||loggingslowquery|0.000019||cleaningup|0.000023|+--------------------------------+----------+17rowsinset(0.00sec)

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

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

Java代码 mysql>selectname,genderfromperson4indexwheregenderin("male","female");+----------+--------+|name|gender|+----------+--------+|wenwen|female||zhaoming|male|+----------+--------+2rowsinset(0.00sec)

查看器执行计划：

Java代码 mysql>explainselectname,genderfromperson4indexwheregenderin("male","female");+----+-------------+--------------+-------+---------------+--------+---------+------+------+-------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+--------------+-------+---------------+--------+---------+------+------+-------------+|1|SIMPLE|person4index|range|gender|gender|12|NULL|2|Usingwhere|+----+-------------+--------------+-------+---------------+--------+---------+------+------+-------------+1rowinset(0.00sec)

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

Java代码 mysql>showprofiles;+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+|Query_ID|Duration|Query|+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+|68|0.00106600|selectname,genderfromperson4indexwheregenderin("male","female")||69|0.00092500|explainselectname,genderfromperson4indexwheregenderin("male","female")|+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+15rowsinset(0.00sec)

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

看其具体消耗的阶段：

Java代码 mysql>showprofileforquery68;+--------------------------------+----------+|Status|Duration|+--------------------------------+----------+|starting|0.000059||checkingquerycacheforquery|0.000111||Openingtables|0.000085||Systemlock|0.000023||Tablelock|0.000067||init|0.000183||optimizing|0.000031||statistics|0.000139||preparing|0.000035||executing|0.000020||Sendingdata|0.000148||end|0.000024||queryend|0.000019||freeingitems|0.000043||storingresultinquerycache|0.000042||loggingslowquery|0.000017||cleaningup|0.000020|+--------------------------------+----------+17rowsinset(0.00sec)

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

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

Java代码 mysql>selectgender,namefromperson4cindexwheregenderin("male","female");+--------+----------+|gender|name|+--------+----------+|female|wenwen||male|zhaoming|+--------+----------+2rowsinset(0.01sec)

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

还是先看执行计划：

Java代码 mysql>explainselectgender,namefromperson4cindexwheregenderin("male","female");+----+-------------+---------------+-------+---------------+------+---------+------+------+--------------------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+---------------+-------+---------------+------+---------+------+------+--------------------------+|1|SIMPLE|person4cindex|index|NULL|name|44|NULL|2|Usingwhere;Usingindex|+----+-------------+---------------+-------+---------------+------+---------+------+------+--------------------------+1rowinset(0.00sec)

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

看消耗的时间吧：

Java代码 mysql>showprofiles;+----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+|Query_ID|Duration|Query|+----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+|83|0.00115400|selectgender,namefromperson4cindexwheregenderin("male","female")||84|0.00074000|explainselectgender,namefromperson4cindexwheregenderin("male","female")|+----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+

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

看下具体的消耗情况：

Sql代码 mysql>showprofileforquery83;+--------------------------------+----------+|Status|Duration|+--------------------------------+----------+|starting|0.000083||checkingquerycacheforquery|0.000113||Openingtables|0.000039||Systemlock|0.000026||Tablelock|0.000075||init|0.000128||optimizing|0.000193||statistics|0.000056||preparing|0.000038||executing|0.000021||Sendingdata|0.000121||end|0.000042||queryend|0.000021||freeingitems|0.000112||storingresultinquerycache|0.000043||loggingslowquery|0.000021||cleaningup|0.000022|+--------------------------------+----------+17rowsinset(0.00sec)

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

总结：

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

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

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