文章目錄

• • 4.性能分析
  • • 4.1 MySQL常見瓶頸
    • 4.2 Explain
  • 5.查詢優(yōu)化
  • • 5.1 索引失效
    • 5.2 索引優(yōu)化

4.性能分析

4.1 MySQL常見瓶頸

CPU :SQL中對大量數(shù)據(jù)進(jìn)行比較、關(guān)聯(lián)、排序、分組 IO：實例內(nèi)存滿足不了緩存數(shù)據(jù)或排序等需要，導(dǎo)致產(chǎn)生大量 物理 IO。查詢執(zhí)行效率低，掃描過多數(shù)據(jù)行。 鎖:不適宜的鎖的設(shè)置，導(dǎo)致線程阻塞，性能下降。死鎖，線程之間交叉調(diào)用資源，導(dǎo)致死鎖，程序卡住。 服務(wù)器硬件的性能瓶頸：top,free, iostat和vmstat來查看系統(tǒng)的性能狀態(tài)

4.2 Explain

1.Explain是什么(查看執(zhí)行計劃)

使用EXPLAIN關(guān)鍵字可以模擬優(yōu)化器執(zhí)行SQL查詢語句，從而知道MySQL是如何處理你的SQL語句的。 分析你的查詢語句或是表結(jié)構(gòu)的性能瓶頸

官網(wǎng)介紹

2. Explain能干嘛

表的讀取順序 哪些索引可以使用 數(shù)據(jù)讀取操作的操作類型 哪些索引被實際使用 表之間的引用 每張表有多少行被優(yōu)化器查詢

3.explain怎么玩

Explain + SQL語句 mysql> select * from students; +------+-------+ | id | name | +------+-------+ | 1 | zhao1 | | 2 | zhao2 | | 3 | zhao3 | +------+-------+ 3 rows in set (0.00 sec)mysql> explain select * from students; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | students | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec) 執(zhí)行計劃包含的信息 +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | students | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ 建表腳本CREATE TABLE t1(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));CREATE TABLE t2(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));CREATE TABLE t3(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));CREATE TABLE t4(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000)));INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000)));INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000)));INSERT INTO t4(content) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)));

4.各字段解釋

4.1 id

select查詢的序列號,包含一組數(shù)字，表示查詢中執(zhí)行select子句或操作表的順序

三種情況：

1.id相同，執(zhí)行順序由上至下

id相同，執(zhí)行順序由上至下 此例中 先執(zhí)行where 后的第一條語句 t1.id = t2.id 通過 t1.id 關(guān)聯(lián) t2.id 。 而t2.id 的結(jié)果建立在 t2.id=t3.id 的基礎(chǔ)之上。 2.id不同，如果是子查詢，id的序號會遞增，id值越大優(yōu)先級越高，越先被執(zhí)行

id不同，如果是子查詢，id的序號會遞增，id值越大優(yōu)先級越高，越先被執(zhí)行 3.id相同不同，同時存在

id如果相同，可以認(rèn)為是一組，從上往下順序執(zhí)行； 在所有組中，id值越大，優(yōu)先級越高，越先執(zhí)行衍生表 = derived2 --> derived + 2 （2 表示由 id =2 的查詢衍生出來的表。type 肯定是 all ， 因為衍生的表沒有建立索引）

4.2 select_type

select_type有哪些?

查詢的類型，主要是用于區(qū)別普通查詢、聯(lián)合查詢、子查詢等的復(fù)雜查詢

類型說明

SIMPLE	簡單的 select 查詢,查詢中不包含子查詢或者UNION
PRIMARY	查詢中若包含任何復(fù)雜的子部分，最外層查詢則被標(biāo)記為Primary
DERIVED	在FROM列表中包含的子查詢被標(biāo)記為DERIVED(衍生) 。MySQL會遞歸執(zhí)行這些子查詢, 把結(jié)果放在臨時表里。
SUBQUERY	在SELECT或WHERE列表中包含了子查詢
DEPENDENT SUBQUERY	在SELECT或WHERE列表中包含了子查詢,子查詢基于外層
UNCACHEABLE SUBQUREY	無法被緩存的子查詢
UNION	若第二個SELECT出現(xiàn)在UNION之后，則被標(biāo)記為UNION；若UNION包含在FROM子句的子查詢中,外層SELECT將被標(biāo)記為：DERIVED
UNION RESULT	從UNION表獲取結(jié)果的SELECT

4.3 table

顯示這一行的數(shù)據(jù)是關(guān)于哪張表的

4.4 type 訪問類型

顯示查詢使用了何種類型， type顯示的是訪問類型，是較為重要的一個指標(biāo)，結(jié)果值從最好到最壞依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range(盡量保證) > index > ALL 常用的：system>const>eq_ref>ref>range>index>ALL 一般來說，得保證查詢至少達(dá)到range級別，最好能達(dá)到ref。 類型說明

system	表只有一行記錄（等于系統(tǒng)表），這是const類型的特列，平時不會出現(xiàn)，這個也可以忽略不計
const	表示通過索引一次就找到了,const用于比較primary key或者unique索引。因為只匹配一行數(shù)據(jù)，所以很快如將主鍵置于where列表中，MySQL就能將該查詢轉(zhuǎn)換為一個常量
eq_ref	唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見于主鍵或唯一索引掃描
ref	非唯一性索引掃描，返回匹配某個單獨值的所有行.本質(zhì)上也是一種索引訪問，它返回所有匹配某個單獨值的行，然而，它可能會找到多個符合條件的行，所以他應(yīng)該屬于查找和掃描的混合體
range	只檢索給定范圍的行,使用一個索引來選擇行。key 列顯示使用了哪個索引。一般就是在你的where語句中出現(xiàn)了between、<、>、in等的查詢這種范圍掃描索引掃描比全表掃描要好，因為它只需要開始于索引的某一點，而結(jié)束語另一點，不用掃描全部索引。
index	Full Index Scan，index與ALL區(qū)別為index類型只遍歷索引樹。這通常比ALL快，因為索引文件通常比數(shù)據(jù)文件小。（也就是說雖然all和Index都是讀全表，但index是從索引中讀取的，而all是從硬盤中讀的）
all	Full Table Scan，將遍歷全表以找到匹配的行

4.5 possible_keys

顯示可能應(yīng)用在這張表中的索引，一個或多個。 查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢實際使用

4.6 key

實際使用的索引。如果為NULL，則沒有使用索引 查詢中若使用了覆蓋索引，則該索引和查詢的select字段重疊 對比下圖兩個 sql 語句。和 key 的值：當(dāng)查詢具體某一字段時，且那個字段有索引時，key 值會顯示為索引。

4.7 key_len

表示索引中使用的字節(jié)數(shù)，可通過該列計算查詢中使用的索引的長度。

EXPLAIN SELECT * FROM emp WHERE emp.deptno=109 AND emp.`ename`='AvDEjl'

如何計算

總結(jié)一下：char(30) utf8 --> key_len = 30*3 +1 表示 utf8 格式需要 *3 (跟數(shù)據(jù)類型有關(guān)) 允許為 NULL +1 ，不允許 +0動態(tài)類型 +2 (動態(tài)類型包括 : varchar , detail text() 截取字符竄)

第一組：key_len=deptno(int)+null + ename(varchar(20)*3+動態(tài) =4+1+20*3+2= 67 第二組：key_len=deptno(int)+null=4+1=5

key_len字段能夠幫你檢查是否充分的利用上了索引

4.8 ref

顯示索引的哪一列被使用了，如果可能的話，是一個常數(shù)。哪些列或常量被用于查找索引列上的值

4.9 rows

rows列顯示MySQL認(rèn)為它執(zhí)行查詢時必須檢查的行數(shù)。

4.10 Extra

包含不適合在其他列中顯示但十分重要的額外信息

5.查詢優(yōu)化

5.1 索引失效

索引的創(chuàng)建：

mysql> CREATE TABLE staffs (-> id INT PRIMARY KEY AUTO_INCREMENT,-> NAME VARCHAR (24) NULL DEFAULT '' COMMENT '姓名',-> age INT NOT NULL DEFAULT 0 COMMENT '年齡',-> pos VARCHAR (20) NOT NULL DEFAULT '' COMMENT '職位',-> add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入職時間'-> ) CHARSET utf8 COMMENT '員工記錄表' ;#staffs表的創(chuàng)建 mysql> INSERT INTO staffs(NAME,age,pos,add_time) VALUES('z3',22,'manager',NOW()); mysql> INSERT INTO staffs(NAME,age,pos,add_time) VALUES('July',23,'dev',NOW()); mysql> INSERT INTO staffs(NAME,age,pos,add_time) VALUES('2000',23,'dev',NOW()); mysql> INSERT INTO staffs(NAME,age,pos,add_time) VALUES(null,23,'dev',NOW());mysql> ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);#索引的創(chuàng)建

索引的失效：

全值匹配我最愛最佳左前綴法則： 如果索引了多列，要遵守最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過索引中的列。不在索引列上做任何操作（計算、函數(shù)、(自動or手動)類型轉(zhuǎn)換），會導(dǎo)致索引失效而轉(zhuǎn)向全表掃描存儲引擎不能使用索引中范圍條件右邊的列盡量使用覆蓋索引(只訪問索引的查詢(索引列和查詢列一致))，減少select *mysql 在使用不等于(!= 或者<>)的時候無法使用索引會導(dǎo)致全表掃描is not null 也無法使用索引,但是is null是可以使用索引的字符串不加單引號索引失效少用or,用它來連接時會索引失效like以通配符開頭('%abc...')mysql索引失效會變成全表掃描的操作

索引失效總結(jié)：

假設(shè)index(a,b,c) Where語句索引是否被使用

where a = 3	Y,使用到a
where a = 3 and b = 5	Y,使用到a，b
where a = 3 and b = 5 and c = 4	Y,使用到a,b,c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4	N
where a = 3 and c = 5	使用到a， 但是c不可以，b中間斷了
where a = 3 and b > 4 and c = 5	使用到a和b， c不能用在范圍之后，b后斷了
where a = 3 and b like ‘kk%’ and c = 4	Y,使用到a,b,c
where a = 3 and b like ‘%kk’ and c = 4	Y,只用到a
where a = 3 and b like ‘%kk%’ and c = 4	Y,只用到a
where a = 3 and b like ‘k%kk%’ and c = 4	Y,使用到a,b,c

5.2 索引優(yōu)化

單表查詢優(yōu)化

關(guān)聯(lián)查詢優(yōu)化

1、保證被驅(qū)動表的join字段已經(jīng)被索引，被驅(qū)動表 join 后的表為被驅(qū)動表 (需要被查詢)2、left join 時，選擇小表作為驅(qū)動表，大表作為被驅(qū)動表。但是 left join 時一定是左邊是驅(qū)動表，右邊是被驅(qū)動表 3、inner join 時，mysql會自己幫你把小結(jié)果集的表選為驅(qū)動表。mysql 自動選擇。小表作為驅(qū)動表。因為 驅(qū)動表無論如何都會被全表掃描？。所以掃描次數(shù)越少越好 4、子查詢盡量不要放在被驅(qū)動表，有可能使用不到索引。

子查詢優(yōu)化

有索引的情況下 用 inner join 是最好的 其次是 in ，exists最糟糕無索引的情況下用 小表驅(qū)動大表 因為join 方式需要distinct ，沒有索引distinct消耗性能較大 所以 exists性能最佳 in其次 join性能最差？無索引的情況下大表驅(qū)動小表 in 和 exists 的性能應(yīng)該是接近的 都比較糟糕 exists稍微好一點 超不過5% 但是inner join 優(yōu)于使用了 join buffer 所以快很多 如果left join 則最慢

order by關(guān)鍵字優(yōu)化

ORDER BY子句，盡量使用Index方式排序,避免使用FileSort方式排序盡可能在索引列上完成排序操作，遵照索引建的最佳左前綴如果不在索引列上，filesort有兩種算法：mysql就要啟動雙路排序和單路排序

分頁查詢的優(yōu)化—limit

GROUP BY關(guān)鍵字優(yōu)化 group by實質(zhì)是先排序后進(jìn)行分組，遵照索引建的最佳左前綴 當(dāng)無法使用索引列，增大max_length_for_sort_data參數(shù)的設(shè)置+增大sort_buffer_size參數(shù)的設(shè)置 where高于having，能寫在where限定的條件就不要去having限定了。

去重優(yōu)化

盡量不要使用 distinct 關(guān)鍵字去重：優(yōu)化

詳細(xì)內(nèi)容：索引優(yōu)化

總結(jié)

以上是生活随笔為你收集整理的数据库高级知识——索引优化分析（二）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。