四種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;
關鍵字:MySQL、不同、查詢、分析
新文章:
- CentOS7下圖形配置網絡的方法
- CentOS 7如何添加刪除用戶
- 如何解決centos7雙系統后丟失windows啟動項
- CentOS單網卡如何批量添加不同IP段
- CentOS下iconv命令的介紹
- Centos7 SSH密鑰登陸及密碼密鑰雙重驗證詳解
- CentOS 7.1添加刪除用戶的方法
- CentOS查找/掃描局域網打印機IP講解
- CentOS7使用hostapd實現無AP模式的詳解
- su命令不能切換root的解決方法
- 解決VMware下CentOS7網絡重啟出錯
- 解決Centos7雙系統后丟失windows啟動項
- CentOS下如何避免文件覆蓋
- CentOS7和CentOS6系統有什么不同呢
- Centos 6.6默認iptable規則詳解