MySQL的四種不同查詢的分析
作者：inter12
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;
原文鏈接：http://inter12.iteye.com/blog/1430144
【編輯推薦】
責任編輯：林師授
來源：
inter12的博客
