1.前置條件：

專注于為中小企業(yè)提供成都網(wǎng)站制作、網(wǎng)站設(shè)計(jì)服務(wù),電腦端+手機(jī)端+微信端的三站合一,更高效的管理,為中小企業(yè)三臺免費(fèi)做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動了上千企業(yè)的穩(wěn)健成長，幫助中小企業(yè)通過網(wǎng)站建設(shè)實(shí)現(xiàn)規(guī)模擴(kuò)充和轉(zhuǎn)變。

本次是基于小數(shù)據(jù)量，且數(shù)據(jù)塊在一個頁中的最理想情況進(jìn)行分析，可能無具體的實(shí)際意義，但是可以借鑒到各種復(fù)雜條件下，因?yàn)樵硎窍嗤?知小見大，見微知著！

打開語句分析并確認(rèn)是否已經(jīng)打開

 
 
 
 

  
  
  
  
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.數(shù)據(jù)準(zhǔn)備：

2.1全表掃描數(shù)據(jù)

 
 
 
 

  
  
  
  
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根據(jù)主鍵查看數(shù)據(jù)

 
 
 
 

  
  
  
  
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根據(jù)非聚集索引查數(shù)據(jù)

 
 
 
 

  
  
  
  
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根據(jù)覆蓋索引查數(shù)據(jù)

 
 
 
 

  
  
  
  
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");   
 
 
 
 

主要從以下幾個方面分析：查詢消耗的時(shí)間，走的執(zhí)行計(jì)劃等方面。

3.開工測試：

第一步：全表掃描

 
 
 
 

  
  
  
  
mysql> select * from person4all ;  
  
  
  
  
+----+----------+--------+  
  
  
  
  
| id | name     | gender |  
  
  
  
  
+----+----------+--------+  
  
  
  
  
|  1 | zhaoming | male   |  
  
  
  
  
|  2 | wenwen   | female |  
  
  
  
  
+----+----------+--------+  
  
  
  
  
2 rows in set (0.00 sec)  
 
 
 
 

查看其執(zhí)行計(jì)劃：

 
 
 
 

  
  
  
  
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)  
 
 
 
 

我們可以很清晰的看到走的是全表掃描，而沒有走索引！

查詢消耗的時(shí)間：

 
 
 
 

  
  
  
  
mysql> show profiles;  
  
  
  
  
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+  
  
  
  
  
| Query_ID | Duration   | Query                                                                                                                             |  
  
  
  
  
|       54 | 0.00177300 | select * from person4all                                                                                                          |  
  
  
  
  
|       55 | 0.00069200 | explain select * from person4all                                                                                                  |  
  
  
  
  
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+ 
 
 
 
 

全表掃描總共話了0.0017730秒

各個階段消耗的時(shí)間是：

 
 
 
 

  
  
  
  
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)  
 
 
 
 

第一次不走緩存的話，需要檢查是否存在緩存中，打開表，初始化等操作，最大的開銷在于返回?cái)?shù)據(jù)。

第二步：根據(jù)主鍵查詢數(shù)據(jù)。

 
 
 
 

  
  
  
  
mysql> select name ,gender from person4pri where id in (1,2);  
  
  
  
  
+----------+--------+  
  
  
  
  
| name     | gender |  
  
  
  
  
+----------+--------+  
  
  
  
  
| zhaoming | male   |  
  
  
  
  
| wenwen   | female |  
  
  
  
  
+----------+--------+  
  
  
  
  
2 rows in set (0.01 sec) 
 
 
 
 

查看其執(zhí)行計(jì)劃：

 
 
 
 

  
  
  
  
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) 
 
 
 
 

從執(zhí)行計(jì)劃中我們可以看出，走的是范圍索引。

再看其執(zhí)行消耗的時(shí)間：

 
 
 
 

  
  
  
  
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) 
 
 
 
 

這次查詢消耗時(shí)間為0.00079200。

查看各個階段消耗的時(shí)間：

 
 
 
 

  
  
  
  
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，第一次也是需要一些初始化操作。

第三步：根據(jù)非聚集索引查詢

 
 
 
 

  
  
  
  
mysql> select name ,gender from person4index where gender in ("male","female");  
  
  
  
  
+----------+--------+  
  
  
  
  
| name     | gender |  
  
  
  
  
+----------+--------+  
  
  
  
  
| wenwen   | female |  
  
  
  
  
| zhaoming | male   |  
  
  
  
  
+----------+--------+  
  
  
  
  
2 rows in set (0.00 sec) 
 
 
 
 

查看器執(zhí)行計(jì)劃：

 
 
 
 

  
  
  
  
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)  
 
 
 
 

可以看出，走的也是范圍索引。同主鍵查詢，那么就看其消耗時(shí)間了

 
 
 
 

  
  
  
  
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) 
 
 
 
 

這個非主鍵索引消耗的時(shí)間為：0.00106600，可以看出略大于組件索引消耗的時(shí)間。

看其具體消耗的階段：

 
 
 
 

  
  
  
  
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) 
 
 
 
 

看幾個關(guān)鍵詞的點(diǎn)；init,statistics,Sending data 這幾個關(guān)鍵點(diǎn)上的消耗向比較主鍵的查詢要大很多，特別是Sending data。因?yàn)槿羰亲叩姆蔷奂饕?，那么就需要回表進(jìn)行再進(jìn)行一次查詢，多消耗一次IO。

第四部：根據(jù)覆蓋索引查詢數(shù)據(jù)

 
 
 
 

  
  
  
  
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，這樣是為了走覆蓋索引。具體看效果吧

還是先看執(zhí)行計(jì)劃：

 
 
 
 

  
  
  
  
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中表示走的就是覆蓋索引。

看消耗的時(shí)間吧：

 
 
 
 

  
  
  
  
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")                                                                                 |  
  
  
  
  
+----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
 
 
 
 

我們看到消耗的時(shí)間是0.00115400，看這個數(shù)字好像挺高的，那么都花在什么地方了呢？

看下具體的消耗情況：

 
 
 
 

  
  
  
  
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)  
 
 
 
 

很驚奇吧，在初始化和優(yōu)化上消耗了這么多時(shí)間，取數(shù)據(jù)基恩差不多。

總  結(jié)：

有了上面這些數(shù)據(jù)，那么我們整理下吧。未存在緩存下的數(shù)據(jù)。

看這個表，全表掃描最慢，我們可以理解，同時(shí)主鍵查詢比覆蓋所有掃描慢也還能接受，但是為什么主鍵掃描會比非主鍵掃描慢？而且非主鍵查詢需要消耗的1次查詢的io+一次回表的查詢IO，理論上是要比主鍵掃描慢，而出來的數(shù)據(jù)缺不是如此。那么就仔細(xì)看下是個查詢方式在各個主要階段消耗的時(shí)間吧。

查詢是否存在緩存，打開表及鎖表這些操作時(shí)間是差不多，我們不會計(jì)入。具體還是看init，optimizing等環(huán)節(jié)消耗的時(shí)間。

1.從這個表中，我們看到非主鍵索引和覆蓋索引在準(zhǔn)備時(shí)間上需要開銷很多的時(shí)間，預(yù)估這兩種查詢方式都需要進(jìn)行回表操作，所以花在準(zhǔn)備上更多時(shí)間。

2.第二項(xiàng)optimizing上，可以清晰知道，覆蓋索引話在優(yōu)化上大量的時(shí)間，這樣在二級索引上就無需回表。

3. Sendingdata，全表掃描慢就慢在這一項(xiàng)上，因?yàn)槭羌虞d所有的數(shù)據(jù)頁，所以花費(fèi)在這塊上時(shí)間較大，其他三者都差不多。

4. 非主鍵查詢話在freeingitems上時(shí)間最少，那么可以看出它在讀取數(shù)據(jù)塊的時(shí)候最少。

5.相比較主鍵查詢和非主鍵查詢，非主鍵查詢在Init，statistics都遠(yuǎn)高于主鍵查詢，只是在freeingitems開銷時(shí)間比主鍵查詢少。因?yàn)檫@里測試數(shù)據(jù)比較少，但是我們可以預(yù)見在大數(shù)據(jù)量的查詢上，不走緩存的話，那么主鍵查詢的速度是要快于非主鍵查詢的，本次數(shù)據(jù)不過是太小體現(xiàn)不出差距而已。

6.在大多數(shù)情況下，全表掃描還是要慢于索引掃描的。

tips:

過程中的輔助命令：

1.清楚緩存

reset query cache ;

flush tables;

2.查看表的索引：

show index from tablename;

原文鏈接：http://inter12.iteye.com/blog/1430144

文章題目：MySQL的四種不同查詢的分析
瀏覽路徑：http://uogjgqi.cn/article/dphiphg.html