江苏建设网站公司简介,青岛外贸假发网站建设,手机价格大全,厦门seo推广外包文章出处#xff1a;http://inter12.iteye.com/blog/1430144 MYSQL的全表扫描#xff0c;主键索引(聚集索引、第一索引)#xff0c;非主键索引(非聚集索引、第二索引)#xff0c;覆盖索引四种不同查询的分析 1.前置条件#xff1a; 本次是基于小数据量#xff0c;且数据块…文章出处http://inter12.iteye.com/blog/1430144 MYSQL的全表扫描主键索引(聚集索引、第一索引)非主键索引(非聚集索引、第二索引)覆盖索引四种不同查询的分析 1.前置条件 本次是基于小数据量且数据块在一个页中的最理想情况进行分析可能无具体的实际意义但是可以借鉴到各种复杂条件下因为原理是相同的,知小见大见微知著 打开语句分析并确认是否已经打开 Java代码 mysql set profiling1; Query OK, 0 rows affected (0.00 sec) mysql select profiling; ------------- | profiling | ------------- | 1 | ------------- 1 row in set (0.01 sec) 2.数据准备 2.1全表扫描数据 Java代码 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根据主键查看数据 Java代码 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根据非聚集索引查数据 Java代码 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根据覆盖索引查数据 Java代码 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.开工测试 第一步全表扫描 Java代码 mysql select * from person4all ; ---------------------- | id | name | gender | ---------------------- | 1 | zhaoming | male | | 2 | wenwen | female | ---------------------- 2 rows in set (0.00 sec) 查看其执行计划 Java代码 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) 我们可以很清晰的看到走的是全表扫描而没有走索引 查询消耗的时间 Java代码 mysql show profiles; --------------------------------------------------------------------------------------------------------------------------------------------------------- | Query_ID | Duration | Query | | 54 | 0.00177300 | select * from person4all | | 55 | 0.00069200 | explain select * from person4all | --------------------------------------------------------------------------------------------------------------------------------------------------------- 全表扫描总共话了0.0017730秒 各个阶段消耗的时间是 Java代码 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) 第一次不走缓存的话需要检查是否存在缓存中打开表初始化等操作最大的开销在于返回数据。 第二步根据主键查询数据。 Java代码 mysql select name ,gender from person4pri where id in (1,2); ------------------ | name | gender | ------------------ | zhaoming | male | | wenwen | female | ------------------ 2 rows in set (0.01 sec) 查看其执行计划: Java代码 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) 从执行计划中我们可以看出走的是范围索引。 再看其执行消耗的时间 Java代码 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。 查看各个阶段消耗的时间 Java代码 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第一次也是需要一些初始化操作。 第三步根据非聚集索引查询 Java代码 mysql select name ,gender from person4index where gender in (male,female); ------------------ | name | gender | ------------------ | wenwen | female | | zhaoming | male | ------------------ 2 rows in set (0.00 sec) 查看器执行计划 Java代码 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) 可以看出走的也是范围索引。同主键查询那么就看其消耗时间了 Java代码 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可以看出略大于组件索引消耗的时间。 看其具体消耗的阶段 Java代码 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。 第四部根据覆盖索引查询数据 Java代码 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这样是为了走覆盖索引。具体看效果吧 还是先看执行计划 Java代码 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中表示走的就是覆盖索引。 看消耗的时间吧 Java代码 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看这个数字好像挺高的那么都花在什么地方了呢 看下具体的消耗情况 Sql代码 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理论上是要比主键扫描慢而出来的数据缺不是如此。那么就仔细看下是个查询方式在各个主要阶段消耗的时间吧。 查询是否存在缓存打开表及锁表这些操作时间是差不多我们不会计入。具体还是看initoptimizing等环节消耗的时间。 1.从这个表中我们看到非主键索引和覆盖索引在准备时间上需要开销很多的时间预估这两种查询方式都需要进行回表操作所以花在准备上更多时间。 2.第二项optimizing上可以清晰知道覆盖索引话在优化上大量的时间这样在二级索引上就无需回表。 3. Sendingdata全表扫描慢就慢在这一项上因为是加载所有的数据页所以花费在这块上时间较大其他三者都差不多。 4. 非主键查询话在freeingitems上时间最少那么可以看出它在读取数据块的时候最少。 5.相比较主键查询和非主键查询非主键查询在Initstatistics都远高于主键查询只是在freeingitems开销时间比主键查询少。因为这里测试数据比较少但是我们可以预见在大数据量的查询上不走缓存的话那么主键查询的速度是要快于非主键查询的本次数据不过是太小体现不出差距而已。 6.在大多数情况下全表扫描还是要慢于索引扫描的。 tips: 过程中的辅助命令 1.清楚缓存 reset query cache ; flush tables; 2.查看表的索引 show index from tablename;
