一文讀懂“建立正確的索引，對數(shù)據(jù)庫性能提升的重要性”

原文： https://www.enmotech.com/web/detail/1/767/1.html 

成都創(chuàng)新互聯(lián)公司主要從事成都網(wǎng)站設(shè)計、成都網(wǎng)站制作、網(wǎng)頁設(shè)計、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)大峪,十載網(wǎng)站建設(shè)經(jīng)驗,價格優(yōu)惠、服務(wù)專業(yè),歡迎來電咨詢建站服務(wù):18982081108

文章轉(zhuǎn)載自公眾號  架構(gòu)師之路 架構(gòu)師之路 ， 作者 58沈劍

導(dǎo)讀：本文和大家說明常見的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%都是上面這些類型。
 
上面各類掃描方式由快到慢：
system > const > eq_ref > ref > range > index > ALL

下面一一舉例說明。

一、system

---

explai select * from mysql.time_zone;

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

這類掃描是速度最快的。

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

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

二、const

---

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

create table user (
id int primary key,
name varchar(20)
)engine=innodb;
 
insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');

const掃描的條件為：
（1）命中主鍵(primary key)或者唯一(unique)索引；
（2）被連接的部分是一個常量(const)值；
 
explain select * from user where id=1;
如上例，id是PK，連接部分是常量1。
畫外音：別搞什么類型轉(zhuǎn)換的幺蛾子。
 
這類掃描效率極高，返回數(shù)據(jù)量少，速度非常快。

三、eq_ref

---

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

create table user (
id int primary key,
name varchar(20)
)engine=innodb;
 
insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
 
create table user_ex (
id int primary key,
age int
)engine=innodb;
 
insert into user_ex values(1,18);
insert into user_ex values(2,20);
insert into user_ex values(3,30);
insert into user_ex values(4,40);
insert into user_ex values(5,50);

eq_ref掃描的條件為，對于前表的每一行(row)，后表只有一行被掃描。
 
再細(xì)化一點(diǎn)：
（1）join查詢；
（2）命中主鍵(primary key)或者非空唯一(unique not null)索引；
（3）等值連接；

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

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

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

四、ref

---

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

create table user (
id int,
name varchar(20) ,
index(id)
)engine=innodb;
 
insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
 
create table user_ex (
id int,
age int,
index(id)
)engine=innodb;
 
insert into user_ex values(1,18);
insert into user_ex values(2,20);
insert into user_ex values(3,30);
insert into user_ex values(4,40);
insert into user_ex values(5,50);

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

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

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

explain select * from user where id=1;

當(dāng)id改為普通非唯一索引后，常量的連接查詢，也由const降級為了ref，因為也可能有多于一行的數(shù)據(jù)被掃描。
 
ref掃描，可能出現(xiàn)在join里，也可能出現(xiàn)在單表普通索引里，每一次匹配可能有多行數(shù)據(jù)返回，雖然它比eq_ref要慢，但它仍然是一個很快的join類型。

五、range

---

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

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

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

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

像上例中的between，in，>都是典型的范圍(range)查詢。
畫外音：必須是索引，否則不能批量"跳過"。

六、index

---

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

explain count (*) from user;

如上例，id是主鍵，該count查詢需要通過掃描索引上的全部數(shù)據(jù)來計數(shù)。
畫外音：此表為InnoDB引擎。
 
它僅比全表掃描快一點(diǎn)。

七、ALL

---

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

create table user (
id int,
name varchar(20)
)engine=innodb;
 
insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
 
create table user_ex (
id int,
age int
)engine=innodb;
 
insert into user_ex values(1,18);
insert into user_ex values(2,20);
insert into user_ex values(3,30);
insert into user_ex values(4,40);
insert into user_ex values(5,50);

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

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

今天這篇文章中，這個相同的join語句出現(xiàn)了三次：
（1）掃描類型為eq_ref，此時id為主鍵；
（2）掃描類型為ref，此時id為非唯一普通索引；
（3）掃描類型為ALL，全表掃描，此時id上無索引；
 
有此可見，建立正確的索引，對數(shù)據(jù)庫性能的提升是多么重要。
 
另外，不正確的SQL語句，可能導(dǎo)致全表掃描。
 
全表掃描代價極大，性能很低，是應(yīng)當(dāng)極力避免的，通過explain分析SQL語句，非常有必要。

總結(jié)

---

（1）explain結(jié)果中的type字段，表示（廣義）連接類型，它描述了找到所需數(shù)據(jù)使用的掃描方式；
（2）常見的掃描類型有：
system>const>eq_ref>ref>range>index>ALL
其掃描速度由快到慢；
（3）各類掃描類型的要點(diǎn)是：

• 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。

出處：架構(gòu)師之路（ID：road5858）

想了解更多關(guān)于數(shù)據(jù)庫、云技術(shù)的內(nèi)容嗎？

快來關(guān)注“數(shù)據(jù)和云”公眾號、“云和恩墨”官方網(wǎng)站，我們期待與大家一同學(xué)習(xí)和進(jìn)步！

（掃描上方二維碼，關(guān)注“數(shù)據(jù)和云”公眾號，即可查看更多科技文章）

當(dāng)前題目：一文讀懂“建立正確的索引，對數(shù)據(jù)庫性能提升的重要性”
轉(zhuǎn)載來于：http://bm7419.com/article16/jdchdg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供軟件開發(fā)、搜索引擎優(yōu)化、做網(wǎng)站、品牌網(wǎng)站設(shè)計、網(wǎng)站設(shè)計、小程序開發(fā)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)