程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL的四種不同查詢的分析

MySQL的四種不同查詢的分析

編輯:MySQL綜合教程

1.前置條件:

本次是基於小數據量,且數據塊在一個頁中的最理想情況進行分析,可能無具體的實際意義,但是可以借鑒到各種復雜條件下,因為原理是相同的,知小見大,見微知著!

打開語句分析並確認是否已經打開

  1. mysql> set profiling=1;     
  2. Query OK, 0 rows affected (0.00 sec)  
  3.  
  4. mysql> select @@profiling;  
  5. +-------------+  
  6. | @@profiling |  
  7. +-------------+  
  8. |           1 |  
  9. +-------------+  
  10. 1 row in set (0.01 sec)  

2.數據准備:

2.1全表掃描數據

  1. create table person4all(id int not null  auto_increment, name varchar(30) not null, gender varchar(10) not null ,primary key(id));  
  2. insert into person4all(name,gender) values("zhaoming","male");  
  3. insert into person4all(name,gender) values("wenwen","female"); 

2.2根據主鍵查看數據

  1. create table person4pri(id int not null  auto_increment, name varchar(30) not null, gender varchar(10) not null ,primary key(id));  
  2. insert into person4pri(name,gender) values("zhaoming","male");  
  3. insert into person4pri(name,gender) values("wenwen","female"); 

2.3根據非聚集索引查數據

  1. create table person4index(id int not null  auto_increment, name varchar(30) not null, gender varchar(10) not null ,primary key(id) , index(gender));  
  2. insert into person4index(name,gender) values("zhaoming","male");  
  3. insert into person4index(name,gender) values("wenwen","female"); 

2.4根據覆蓋索引查數據

  1. 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));    
  2. insert into person4cindex(name,gender) values("zhaoming","male");    
  3. insert into person4cindex(name,gender) values("wenwen","female");   

主要從以下幾個方面分析:查詢消耗的時間,走的執行計劃等方面。

3.開工測試:

第一步:全表掃描

  1. mysql> select * from person4all ;  
  2. +----+----------+--------+  
  3. | id | name     | gender |  
  4. +----+----------+--------+  
  5. |  1 | zhaoming | male   |  
  6. |  2 | wenwen   | female |  
  7. +----+----------+--------+  
  8. 2 rows in set (0.00 sec)  

查看其執行計劃:

  1. mysql> explain select * from person4all;  
  2. +----+-------------+------------+------+---------------+------+---------+------+------+-------+  
  3. | id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |  
  4. +----+-------------+------------+------+---------------+------+---------+------+------+-------+  
  5. |  1 | SIMPLE      | person4all | ALL  | NULL          | NULL | NULL    | NULL |    2 |       |  
  6. +----+-------------+------------+------+---------------+------+---------+------+------+-------+  
  7. 1 row in set (0.01 sec)  

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

查詢消耗的時間:

  1. mysql> show profiles;  
  2. +----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+  
  3. | Query_ID | Duration   | Query                                                                                                                             |  
  4. |       54 | 0.00177300 | select * from person4all                                                                                                          |  
  5. |       55 | 0.00069200 | explain select * from person4all                                                                                                  |  
  6. +----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+ 

全表掃描總共話了0.0017730秒

各個階段消耗的時間是:

  1. mysql> show profile for query 54;  
  2. +--------------------------------+----------+  
  3. | Status                         | Duration |  
  4. +--------------------------------+----------+  
  5. | starting                       | 0.000065 |  
  6. | checking query cache for query | 0.000073 |  
  7. | Opening tables                 | 0.000037 |  
  8. | System lock                    | 0.000024 |  
  9. | Table lock                     | 0.000053 |  
  10. | init                           | 0.000044 |  
  11. | optimizing                     | 0.000022 |  
  12. | statistics                     | 0.000032 |  
  13. | preparing                      | 0.000030 |  
  14. | executing                      | 0.000020 |  
  15. | Sending data                   | 0.001074 |  
  16. | end                            | 0.000091 |  
  17. | query end                      | 0.000020 |  
  18. | freeing items                  | 0.000103 |  
  19. | storing result in query cache  | 0.000046 |  
  20. | logging slow query             | 0.000019 |  
  21. | cleaning up                    | 0.000020 |  
  22. +--------------------------------+----------+  
  23. 17 rows in set (0.00 sec)  

第一次不走緩存的話,需要檢查是否存在緩存中,打開表,初始化等操作,最大的開銷在於返回數據。

第二步:根據主鍵查詢數據。

  1. mysql> select name ,gender from person4pri where id in (1,2);  
  2. +----------+--------+  
  3. | name     | gender |  
  4. +----------+--------+  
  5. | zhaoming | male   |  
  6. | wenwen   | female |  
  7. +----------+--------+  
  8. 2 rows in set (0.01 sec) 

查看其執行計劃:

  1. mysql> explain select name ,gender from person4pri where id in (1,2);  
  2. +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+  
  3. | id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra       |  
  4. +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+  
  5. |  1 | SIMPLE      | person4pri | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where |  
  6. +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+  
  7. 1 row in set (0.00 sec) 

從執行計劃中我們可以看出,走的是范圍索引。

再看其執行消耗的時間:

  1. mysql> show profiles;  
  2. +----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+  
  3. | Query_ID | Duration   | Query                                                                                                                             |  
  4. +----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+  
  5. |       63 | 0.00135700 | select name ,gender from person4pri where id in (1,2)                                                                             |  
  6. |       64 | 0.00079200 | explain select name ,gender from person4pri where id in (1,2)                                                                     |  
  7. +----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+  
  8. 15 rows in set (0.01 sec) 

這次查詢消耗時間為0.00079200。

查看各個階段消耗的時間:

  1. mysql> show profile for query 63;  
  2. +--------------------------------+----------+  
  3. | Status                         | Duration |  
  4. +--------------------------------+----------+  
  5. | starting                       | 0.000067 |  
  6. | checking query cache for query | 0.000146 |  
  7. | Opening tables                 | 0.000342 |  
  8. | System lock                    | 0.000027 |  
  9. | Table lock                     | 0.000115 |  
  10. | init                           | 0.000056 |  
  11. | optimizing                     | 0.000032 |  
  12. | statistics                     | 0.000069 |  
  13. | preparing                      | 0.000039 |  
  14. | executing                      | 0.000022 |  
  15. | Sending data                   | 0.000100 |  
  16. | end                            | 0.000075 |  
  17. | query end                      | 0.000022 |  
  18. | freeing items                  | 0.000158 |  
  19. | storing result in query cache  | 0.000045 |  
  20. | logging slow query             | 0.000019 |  
  21. | cleaning up                    | 0.000023 |  
  22. +--------------------------------+----------+  
  23. 17 rows in set (0.00 sec) 

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

第三步:根據非聚集索引查詢

  1. mysql> select name ,gender from person4index where gender in ("male","female");  
  2. +----------+--------+  
  3. | name     | gender |  
  4. +----------+--------+  
  5. | wenwen   | female |  
  6. | zhaoming | male   |  
  7. +----------+--------+  
  8. 2 rows in set (0.00 sec) 

查看器執行計劃:

  1. mysql> explain select name ,gender from person4index where gender in ("male","female");  
  2. +----+-------------+--------------+-------+---------------+--------+---------+------+------+-------------+  
  3. | id | select_type | table        | type  | possible_keys | key    | key_len | ref  | rows | Extra       |  
  4. +----+-------------+--------------+-------+---------------+--------+---------+------+------+-------------+  
  5. |  1 | SIMPLE      | person4index | range | gender        | gender | 12      | NULL |    2 | Using where |  
  6. +----+-------------+--------------+-------+---------------+--------+---------+------+------+-------------+  
  7. 1 row in set (0.00 sec)  

可以看出,走的也是范圍索引。同主鍵查詢,那麼就看其消耗時間了

  1. mysql> show profiles;  
  2. +----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+  
  3. | Query_ID | Duration   | Query                                                                                                                                               |  
  4. +----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+  
  5. |       68 | 0.00106600 | select name ,gender from person4index where gender in ("male","female")                                                                             |  
  6. |       69 | 0.00092500 | explain select name ,gender from person4index where gender in ("male","female")                                                                     |  
  7. +----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+  
  8. 15 rows in set (0.00 sec) 

這個非主鍵索引消耗的時間為:0.00106600,可以看出略大於組件索引消耗的時間。

看其具體消耗的階段:

  1. mysql> show profile for query 68 ;  
  2. +--------------------------------+----------+  
  3. | Status                         | Duration |  
  4. +--------------------------------+----------+  
  5. | starting                       | 0.000059 |  
  6. | checking query cache for query | 0.000111 |  
  7. | Opening tables                 | 0.000085 |  
  8. | System lock                    | 0.000023 |  
  9. | Table lock                     | 0.000067 |  
  10. | init                           | 0.000183 |  
  11. | optimizing                     | 0.000031 |  
  12. | statistics                     | 0.000139 |  
  13. | preparing                      | 0.000035 |  
  14. | executing                      | 0.000020 |  
  15. | Sending data                   | 0.000148 |  
  16. | end                            | 0.000024 |  
  17. | query end                      | 0.000019 |  
  18. | freeing items                  | 0.000043 |  
  19. | storing result in query cache  | 0.000042 |  
  20. | logging slow query             | 0.000017 |  
  21. | cleaning up                    | 0.000020 |  
  22. +--------------------------------+----------+  
  23. 17 rows in set (0.00 sec) 

看幾個關鍵詞的點;init,statistics,Sending data 這幾個關鍵點上的消耗向比較主鍵的查詢要大很多,特別是Sending data。因為若是走的非聚集索引,那麼就需要回表進行再進行一次查詢,多消耗一次IO。

第四部:根據覆蓋索引查詢數據

  1. mysql> select gender ,name from person4cindex where gender in ("male","female");  
  2. +--------+----------+  
  3. | gender | name     |  
  4. +--------+----------+  
  5. | female | wenwen   |  
  6. | male   | zhaoming |  
  7. +--------+----------+  
  8. 2 rows in set (0.01 sec) 

這裡需要注意的是,我的字段查詢順序變了,是gender,name而不在是前面的name,gender,這樣是為了走覆蓋索引。具體看效果吧

還是先看執行計劃:

  1. mysql> explain select gender ,name from person4cindex where gender in ("male","female");  
  2. +----+-------------+---------------+-------+---------------+------+---------+------+------+--------------------------+  
  3. | id | select_type | table         | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |  
  4. +----+-------------+---------------+-------+---------------+------+---------+------+------+--------------------------+  
  5. |  1 | SIMPLE      | person4cindex | index | NULL          | name | 44      | NULL |    2 | Using where; Using index |  
  6. +----+-------------+---------------+-------+---------------+------+---------+------+------+--------------------------+  
  7. 1 row in set (0.00 sec) 

最後欄Extra中表示走的就是覆蓋索引。

看消耗的時間吧:

  1. mysql> show profiles;  
  2. +----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
  3. | Query_ID | Duration   | Query                                                                                                                                                            |  
  4. +----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
  5. |       83 | 0.00115400 | select gender ,name from person4cindex where gender in ("male","female")                                                                                         |  
  6. |       84 | 0.00074000 | explain select gender ,name from person4cindex where gender in ("male","female")                                                                                 |  
  7. +----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 

我們看到消耗的時間是0.00115400,看這個數字好像挺高的,那麼都花在什麼地方了呢?

看下具體的消耗情況:

  1. mysql> show profile for query 83 ;  
  2. +--------------------------------+----------+  
  3. | Status                         | Duration |  
  4. +--------------------------------+----------+  
  5. | starting                       | 0.000083 |  
  6. | checking query cache for query | 0.000113 |  
  7. | Opening tables                 | 0.000039 |  
  8. | System lock                    | 0.000026 |  
  9. | Table lock                     | 0.000075 |  
  10. | init                           | 0.000128 |  
  11. | optimizing                     | 0.000193 |  
  12. | statistics                     | 0.000056 |  
  13. | preparing                      | 0.000038 |  
  14. | executing                      | 0.000021 |  
  15. | Sending data                   | 0.000121 |  
  16. | end                            | 0.000042 |  
  17. | query end                      | 0.000021 |  
  18. | freeing items                  | 0.000112 |  
  19. | storing result in query cache  | 0.000043 |  
  20. | logging slow query             | 0.000021 |  
  21. | cleaning up                    | 0.000022 |  
  22. +--------------------------------+----------+  
  23. 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;

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved