日韩无码专区无码一级三级片|91人人爱网站中日韩无码电影|厨房大战丰满熟妇|AV高清无码在线免费观看|另类AV日韩少妇熟女|中文日本大黄一级黄色片|色情在线视频免费|亚洲成人特黄a片|黄片wwwav色图欧美|欧亚乱色一区二区三区

RELATEED CONSULTING
相關(guān)咨詢
選擇下列產(chǎn)品馬上在線溝通
服務(wù)時間：8:30-17:00
你可能遇到了下面的問題
關(guān)閉右側(cè)工具欄

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
同一個SQL語句，為啥性能差異這么大呢？（1分鐘系列）

《數(shù)據(jù)庫允許空值，往往是悲劇的開始》一文通過explain來分析SQL的執(zhí)行計劃，來分析null對索引命中情況的影響，有不少朋友留言，問explain結(jié)果中的type字段，ref，ALL等不一樣的值究竟是什么含義。

今天花1分鐘簡單說下，常見的type結(jié)果及代表的含義，并且通過同一個SQL語句的性能差異，說明建對索引多么重要。

explain結(jié)果中的type字段代表什么意思?

MySQL的官網(wǎng)解釋非常簡潔，只用了3個單詞：連接類型(the join type)。它描述了找到所需數(shù)據(jù)使用的掃描方式。

最為常見的掃描方式有：

  • system：系統(tǒng)表，少量數(shù)據(jù)，往往不需要進(jìn)行磁盤IO;
  • const：常量連接;
  • eq_ref：主鍵索引(primary key)或者非空唯一索引(unique not null)等值掃描;
  • ref：非主鍵非唯一索引等值掃描;
  • range：范圍掃描;
  • index：索引樹掃描;
  • ALL：全表掃描(full table scan);

畫外音：這些是最常見的，大家去explain自己工作中的SQL語句，95%都是上面這些類型。

上面各類掃描方式由快到慢：

 
 
 
 
    
  
  
  
  
  1. system > const > eq_ref > ref > range > index > ALL
    2.

下面一一舉例說明。

一、system

 
 
 
 
    
  
  
  
  
  1. explain select * from mysql.time_zone;
    2.

上例中，從系統(tǒng)庫mysql的系統(tǒng)表time_zone里查詢數(shù)據(jù)，掃碼類型為system，這些數(shù)據(jù)已經(jīng)加載到內(nèi)存里，不需要進(jìn)行磁盤IO。

這類掃描是速度最快的。

 
 
 
 
    
  
  
  
  
  1. explain select * from (select * from user where id=1) tmp;
    2.

再舉一個例子，內(nèi)層嵌套(const)返回了一個臨時表，外層嵌套從臨時表查詢，其掃描類型也是system，也不需要走磁盤IO，速度超快。

二、const

數(shù)據(jù)準(zhǔn)備：

 
 
 
 
    
  
  
  
  
  1. create table user (
    2. 
  
  
  
  
  2. id int primary key,
    3. 
  
  
  
  
  3. name varchar(20)
    4. 
  
  
  
  
  4. )engine=innodb;
    5. 
  
  
  
  
  5.  
    6. 
  
  
  
  
  6. insert into user values(1,'shenjian');
    7. 
  
  
  
  
  7. insert into user values(2,'zhangsan');
    8. 
  
  
  
  
  8. insert into user values(3,'lisi');
    9.

const掃描的條件為：

  • 命中主鍵(primary key)或者唯一(unique)索引;
  • 被連接的部分是一個常量(const)值;
 
 
 
 
    
  
  
  
  
  1. explain select * from user where id=1;
    2.

如上例，id是PK，連接部分是常量1。

畫外音：別搞什么類型轉(zhuǎn)換的幺蛾子。

這類掃描效率極高，返回數(shù)據(jù)量少，速度非?？?。

三、eq_ref

數(shù)據(jù)準(zhǔn)備：

 
 
 
 
    
  
  
  
  
  1. create table user (
    2. 
  
  
  
  
  2. id int primary key,
    3. 
  
  
  
  
  3. name varchar(20)
    4. 
  
  
  
  
  4. )engine=innodb;
    5. 
  
  
  
  
  5.  
    6. 
  
  
  
  
  6. insert into user values(1,'shenjian');
    7. 
  
  
  
  
  7. insert into user values(2,'zhangsan');
    8. 
  
  
  
  
  8. insert into user values(3,'lisi');
    9. 
  
  
  
  
  9.  
    10. 
  
  
  
  
  10. create table user_ex (
    11. 
  
  
  
  
  11. id int primary key,
    12. 
  
  
  
  
  12. age int
    13. 
  
  
  
  
  13. )engine=innodb;
    14. 
  
  
  
  
  14.  
    15. 
  
  
  
  
  15. insert into user_ex values(1,18);
    16. 
  
  
  
  
  16. insert into user_ex values(2,20);
    17. 
  
  
  
  
  17. insert into user_ex values(3,30);
    18. 
  
  
  
  
  18. insert into user_ex values(4,40);
    19. 
  
  
  
  
  19. insert into user_ex values(5,50);
    20.

eq_ref掃描的條件為，對于前表的每一行(row)，后表只有一行被掃描。

再細(xì)化一點：

  • join查詢;
  • 命中主鍵(primary key)或者非空唯一(unique not null)索引;
  • 等值連接;
 
 
 
 
    
  
  
  
  
  1. explain select * from user,user_ex where user.id=user_ex.id;
    2.

如上例，id是主鍵，該join查詢?yōu)閑q_ref掃描。

這類掃描的速度也異常之快。

四、ref

數(shù)據(jù)準(zhǔn)備：

 
 
 
 
    
  
  
  
  
  1. create table user (
    2. 
  
  
  
  
  2. id int,
    3. 
  
  
  
  
  3. name varchar(20) ,
    4. 
  
  
  
  
  4. index(id)
    5. 
  
  
  
  
  5. )engine=innodb;
    6. 
  
  
  
  
  6.  
    7. 
  
  
  
  
  7. insert into user values(1,'shenjian');
    8. 
  
  
  
  
  8. insert into user values(2,'zhangsan');
    9. 
  
  
  
  
  9. insert into user values(3,'lisi');
    10. 
  
  
  
  
  10.  
    11. 
  
  
  
  
  11. create table user_ex (
    12. 
  
  
  
  
  12. id int,
    13. 
  
  
  
  
  13. age int,
    14. 
  
  
  
  
  14. index(id)
    15. 
  
  
  
  
  15. )engine=innodb;
    16. 
  
  
  
  
  16.  
    17. 
  
  
  
  
  17. insert into user_ex values(1,18);
    18. 
  
  
  
  
  18. insert into user_ex values(2,20);
    19. 
  
  
  
  
  19. insert into user_ex values(3,30);
    20. 
  
  
  
  
  20. insert into user_ex values(4,40);
    21. 
  
  
  
  
  21. insert into user_ex values(5,50);
    22.

如果把上例eq_ref案例中的主鍵索引，改為普通非唯一(non unique)索引。

 
 
 
 
    
  
  
  
  
  1. explain select * from user,user_ex where user.id=user_ex.id;
    2.

就由eq_ref降級為了ref，此時對于前表的每一行(row)，后表可能有多于一行的數(shù)據(jù)被掃描。

 
 
 
 
    
  
  
  
  
  1. explain select * from user where id=1;
    2.

當(dāng)id改為普通非唯一索引后，常量的連接查詢，也由const降級為了ref，因為也可能有多于一行的數(shù)據(jù)被掃描。

ref掃描，可能出現(xiàn)在join里，也可能出現(xiàn)在單表普通索引里，每一次匹配可能有多行數(shù)據(jù)返回，雖然它比eq_ref要慢，但它仍然是一個很快的join類型。

五、range

數(shù)據(jù)準(zhǔn)備：

 
 
 
 
    
  
  
  
  
  1. create table user (
    2. 
  
  
  
  
  2. id int primary key,
    3. 
  
  
  
  
  3. name varchar(20)
    4. 
  
  
  
  
  4. )engine=innodb;
    5. 
  
  
  
  
  5.  
    6. 
  
  
  
  
  6. insert into user values(1,'shenjian');
    7. 
  
  
  
  
  7. insert into user values(2,'zhangsan');
    8. 
  
  
  
  
  8. insert into user values(3,'lisi');
    9. 
  
  
  
  
  9. insert into user values(4,'wangwu');
    10. 
  
  
  
  
  10. insert into user values(5,'zhaoliu');
    11.

range掃描就比較好理解了，它是索引上的范圍查詢，它會在索引上掃碼特定范圍內(nèi)的值。

 
 
 
 
    
  
  
  
  
  1. explain select * from user where id between 1 and 4;
    2. 
  
  
  
  
  2. explain select * from user where idin(1,2,3);
    3. 
  
  
  
  
  3. explain select * from user where id>3;
    4.

像上例中的between，in，>都是典型的范圍(range)查詢。

畫外音：必須是索引，否則不能批量"跳過"。

六、index

index類型，需要掃描索引上的全部數(shù)據(jù)。

 
 
 
 
    
  
  
  
  
  1. explain count (*) from user;
    2.

如上例，id是主鍵，該count查詢需要通過掃描索引上的全部數(shù)據(jù)來計數(shù)。

畫外音：此表為InnoDB引擎。

它僅比全表掃描快一點。

七、ALL

數(shù)據(jù)準(zhǔn)備：

 
 
 
 
    
  
  
  
  
  1. create table user (
    2. 
  
  
  
  
  2. id int,
    3. 
  
  
  
  
  3. name varchar(20)
    4. 
  
  
  
  
  4. )engine=innodb;
    5. 
  
  
  
  
  5.  
    6. 
  
  
  
  
  6. insert into user values(1,'shenjian');
    7. 
  
  
  
  
  7. insert into user values(2,'zhangsan');
    8. 
  
  
  
  
  8. insert into user values(3,'lisi');
    9. 
  
  
  
  
  9.  
    10. 
  
  
  
  
  10. create table user_ex (
    11. 
  
  
  
  
  11. id int,
    12. 
  
  
  
  
  12. age int
    13. 
  
  
  
  
  13. )engine=innodb;
    14. 
  
  
  
  
  14.  
    15. 
  
  
  
  
  15. insert into user_ex values(1,18);
    16. 
  
  
  
  
  16. insert into user_ex values(2,20);
    17. 
  
  
  
  
  17. insert into user_ex values(3,30);
    18. 
  
  
  
  
  18. insert into user_ex values(4,40);
    19. 
  
  
  
  
  19. insert into user_ex values(5,50);
    20. 
 
 
 
 

 
 
 
 
    
  
  
  
  
  1. explain select * from user,user_ex where user.id=user_ex.id;
    2.

如果id上不建索引，對于前表的每一行(row)，后表都要被全表掃描。

今天這篇文章中，這個相同的join語句出現(xiàn)了三次：

  • 掃描類型為eq_ref，此時id為主鍵;
  • 掃描類型為ref，此時id為非唯一普通索引;
  • 掃描類型為ALL，全表掃描，此時id上無索引;

有此可見，建立正確的索引，對數(shù)據(jù)庫性能的提升是多么重要。

全表掃描代價極大，性能很低，是應(yīng)當(dāng)極力避免的，通過explain分析SQL語句，非常有必要。

總結(jié)

(1)explain結(jié)果中的type字段，表示(廣義)連接類型，它描述了找到所需數(shù)據(jù)使用的掃描方式;

(2)常見的掃描類型有：

 
 
 
 
    
  
  
  
  
  1. system>const>eq_ref>ref>range>index>ALL
    2.

其掃描速度由快到慢;

(3)各類掃描類型的要點是：

  • system最快：不進(jìn)行磁盤IO
  • const：PK或者unique上的等值查詢
  • eq_ref：PK或者unique上的join查詢，等值匹配，對于前表的每一行(row)，后表只有一行命中
  • ref：非唯一索引，等值匹配，可能有多行命中
  • range：索引上的范圍掃描，例如：between/in/>
  • index：索引上的全集掃描，例如：InnoDB的count
  • ALL最慢：全表掃描(full table scan)

(4)建立正確的索引(index)，非常重要;

(5)使用explain了解并優(yōu)化執(zhí)行計劃，非常重要;

思路比結(jié)論重要，希望大家有收獲。

畫外音：本文測試于MySQL5.6。

【本文為專欄作者“58沈劍”原創(chuàng)稿件，轉(zhuǎn)載請聯(lián)系原作者】


網(wǎng)站欄目：同一個SQL語句，為啥性能差異這么大呢？（1分鐘系列）
URL標(biāo)題：http://www.5511xx.com/article/djdehhg.html