MySQL常見瓶頸

1. CPU
   CPU滿負(fù)載砂竖，一般發(fā)生在數(shù)據(jù)裝入內(nèi)存活從磁盤讀取數(shù)據(jù)時
2. 磁盤IO
   磁盤I/O瓶頸一般發(fā)生在裝入數(shù)據(jù)遠(yuǎn)大于內(nèi)存容量時
3. 服務(wù)器硬件性能：
   機器配置比較低罪治，可通過命令：top、free阱持、iostat夭拌、vmstat查看系統(tǒng)性能狀態(tài)

explain關(guān)鍵字

explain可以模擬優(yōu)化器執(zhí)行SQL查詢語句，從而知道是如何處理SQL語句的衷咽，分析查詢語句或者是表結(jié)構(gòu)的性能瓶頸鸽扁。

作用

1. 表的讀取順序
2. 數(shù)據(jù)讀取操作的操作類型
3. 哪些索引可以使用
4. 哪些索引實際被使用
5. 表之間的引用關(guān)系
6. 每張表有多少行被優(yōu)化器查詢

使用

explain + [SQL語句]
結(jié)果

名詞解釋

1. id
   select查詢的序列號，包含一組數(shù)字镶骗，表示查詢中執(zhí)行select紫玉活操作表的順序桶现。它的值有三種情況：
   

   a. id相同，執(zhí)行順序由上往下
   id相同
   b. id不相同鼎姊，如果是子查詢骡和，id的序號會遞增相赁，id值越大執(zhí)行優(yōu)先級越高
   id不相同
   
   
   c. id相同和不同的情況同時存在
   混合存在的情況

2. select_type
   查詢類型一般包含simple、primary慰于、subquery钮科、derived、union婆赠、union result這六種類型绵脯。主要由于區(qū)別普通查詢、聯(lián)合查詢休里、子查詢等復(fù)雜的查詢情況蛆挫。
   a. simple：簡單的select查詢語句，查詢中不包含子查詢和union
   b. primary：查詢中如果包含任何復(fù)雜的子部分妙黍，最外層的查詢則會被標(biāo)記為primary
   c. subquery：在select或where列表中包含了子查詢
   d. derived：在from列表中包含的子查詢被標(biāo)記為derived（衍生）悴侵，MySQL會遞歸執(zhí)行這些子查詢，把結(jié)果放在臨時表里
   e. union：如果第二個select出現(xiàn)在union之后拭嫁，則被標(biāo)記為union可免；如果union包含在from字句的子查詢中，外層的select將被標(biāo)記為derived
   f.union result：從union表獲取結(jié)果的select

3. table

4. type
   type表示訪問類型噩凹，是SQL的重要指標(biāo)巴元，它有system、const驮宴、eq_red逮刨、ref、fulltext堵泽、ref_or_null修己、index_merge、unique_subquery迎罗、index_subquery睬愤、range、index纹安、all尤辱；一日常工作中一般包含all、index厢岂、range光督、ref、eq_ref塔粒、const结借、system、null這八種類型卒茬，執(zhí)行效率從高到低依次是：system>const>eq_ref>ref>range>index>all
   百萬條數(shù)據(jù)以上船老，如果查詢類型是all咖熟，則需要進行查詢優(yōu)化，all走的是全表掃描柳畔。一般來說需要保證查詢至少達到range級別馍管，盡可能達到ref；
   system：表只有一行記錄（等于系統(tǒng)表）薪韩，這是const類型的特例咽斧，平時不會出現(xiàn)
   const：表示通過索引一次就找到了，const常用于比較primary key或者unique索引躬存，因為只匹配一行數(shù)據(jù)，所以很快舀锨；如將主鍵置于where條件列表中岭洲，MySQL就能將該查詢轉(zhuǎn)換為一個常量
   eq_ref：執(zhí)行的是唯一性索引掃描。對于每個索鍵坎匿，表中只有一條記錄與之匹配盾剩。常見于主鍵活唯一索引掃描
   ref：執(zhí)行的是非唯一性所以掃描，返回匹配替蔬，某個單獨值對應(yīng)的所有行告私，本質(zhì)上也是一種索引訪問，它返回所有匹配某個單獨值的行承桥，然后它可能會找到多個符合條件的行驻粟，所以他應(yīng)該屬于查找和掃描的混合體
   range：只檢索給定范圍的行，使用一個索引來選擇行凶异∈癯牛可顯示使用了哪個索引，一般就是在你的where語句中出現(xiàn)了between剩彬、>酷麦、<、in等查詢語句喉恋，這種返回掃描比全表掃描要好沃饶，因為它只需要檢索開始于索引的某一點和結(jié)束語的另外一點。
   index：全稱為full index scan（全索引掃描）轻黑，index與all的區(qū)別為index只遍歷索引樹糊肤，index是從索引中讀取，而all是從磁盤中的數(shù)據(jù)表讀取苔悦，index通常比all的全表掃描快轩褐，因為索引文件通常比數(shù)據(jù)文件小玖详；
   all：full table scan把介，全表掃描勤讽，將會遍歷全表以找到匹配的行
   一般情況下，盡量保證查詢達到range級別拗踢，數(shù)據(jù)量百萬以上之后能達到ref級別

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

6. key
   實際查詢中使用到的索引诸狭，如果為null，則表示沒有建索引或者建索引了沒有被使用君纫，查詢中如果使用了覆蓋索引驯遇，則改縮影會出現(xiàn)在key列表中（ 覆蓋索引（也叫索引覆蓋）：官方解釋是說select的數(shù)據(jù)列支用從索引中就能獲得，不必讀取數(shù)據(jù)行蓄髓，MySQL可以利用索引返回select列表中的字段叉庐，而不必根據(jù)索引再次讀取數(shù)據(jù)文件，簡而言之就是查詢的字段要被復(fù)合索引的字段所覆蓋会喝，select user_name ,user_age from t_user; create index idx_name_age on t_user(user_name,user_age);）

7. key_len
   表示索引中使用的字節(jié)數(shù)陡叠，可通過該列計算查詢中使用的索引長度；在不損失精確性的情況下肢执，長度越短越好枉阵，ken_len顯示的值為索引的最大可能長度，并非實際使用長度预茄，即key_len是根據(jù)表定義而計算出來的兴溜，不是通過表內(nèi)檢索出來的

8. ref
   顯示索引的那一列被使用了，如果可能的話反璃，還可以是一個常量昵慌，表示哪些列或者常量被用于查詢索引列上的值

9. rows
   根據(jù)表統(tǒng)計信息以及索引選用情況，大致估算出找到所需記錄需要讀取的行數(shù)

10. extra
    包含不適合在其他列中顯示但十分重要的額外信息淮蜈；一般包含using filesort斋攀、using temporary、using index梧田、using where淳蔼、using join buffer、impossible where裁眯、select tables optimized away鹉梨、distinct這八這種場景（比較重要的有：using filesort、using temporary穿稳、using index）存皂；
    using filesort：說明MySQL會對數(shù)據(jù)使用一個外部的索引排序，而不是按照表內(nèi)的索引順序進行讀取，mysql中無法利用索引完成的排序操作成為“文件排序”旦袋。extra出現(xiàn)using filesort的時候骤菠，SQL基本不符合性能要求，需要盡可能優(yōu)化;九死一生sql
    using temporary：查詢使用了臨時表保存中間結(jié)果疤孕，MYSQL在對查詢結(jié)果排序時使用臨時表商乎，查詢結(jié)束后再將臨時表刪除；常見于order by和分組查詢group by的場景祭阀；出現(xiàn)using temporary這種情況SQL必須優(yōu)化鹉戚，group by時，索引的列必須跟group by 后面的列保持一致
    using index：表示相應(yīng)的select操作中使用了覆蓋索引covering index专控，避免訪問了表的數(shù)據(jù)行抹凳，效率不錯；如果同時出現(xiàn)using where伦腐，表示索引被用來執(zhí)行索引鍵值查找却桶；如果沒有同時出現(xiàn)using where ，表示索引用來讀取數(shù)據(jù)而不是執(zhí)行查找動作蔗牡；
    using where：表示查詢使用了where過濾
    using join buffer：表示查詢使用了連接緩存（如果查詢語句中join比較多，可適當(dāng)調(diào)整mysql配置文件的join buffer配置）
    impossible where：where字句的值總是false嗅剖，不能用來獲取任何元素
    select tables optimized away：在沒有g(shù)roup by子句的情況下辩越，基于索引優(yōu)化MIN/MAX操作或者對于MyISAM存儲引擎優(yōu)化COUNT(*)操作，不必等到執(zhí)行階段再進行計算信粮，查詢執(zhí)行計劃生成的階段即完結(jié)優(yōu)化不常見忙也不常用
    distinct：優(yōu)化distinct操作黔攒，在找到第一匹配的原始之后就停止查找相同值的操作

索引分析

單表

create index idx_xxx  on t_test(xxx,xxxx);

where后面有范圍比較會使得范圍字段后面的索引失效問題
example：

select *  from t_test where user_name='aaa'  and  user_age >10  order by  city_code desc limit 1;

explain 結(jié)果

三個字段的索引結(jié)果

重建索引
drop index idx_nac on t_test ;
create index idx_nac on t_test(user_name,city_code);

image.png

雙表

如果有l(wèi)eft join查詢，則索引應(yīng)建立在右表秘车，也就是a left jon b里的b表典勇；
如果有right join，則索引應(yīng)該建立在左表叮趴，也就是a right join b里的a表

多表

多表的情況下索引最好建立在需要經(jīng)常查詢的字段中

join語句優(yōu)化

1. 盡可能減少join語句中的嵌套循環(huán)總次數(shù)割笙，永遠(yuǎn)用小結(jié)果集驅(qū)動大的結(jié)果集——小表驅(qū)動大表，小表會走全表掃描眯亦；
2. 優(yōu)先優(yōu)化嵌套循環(huán)的內(nèi)循環(huán)
3. 保證join語句中被驅(qū)動表上join條件字段已經(jīng)被索引
4. 當(dāng)無法保證被驅(qū)動表的join條件字段被索引且內(nèi)存充足的前提下伤溉，可以優(yōu)先增加MySQL的join buffer配置

索引失效分析

索引原則

1. 全值匹配的索引效率最優(yōu)
2. 索引匹配原則為：最佳左前綴法則（如果索引配置了多列般码，查詢的時候要遵循最左法則，因為查詢是從索引的最左邊列開始并且不跳過中間的列）
3. 不在索引列上做任何操作（計算谈火、函數(shù)侈询、手動或自動類型轉(zhuǎn)換，）糯耍，這些操作會導(dǎo)致索引失效而轉(zhuǎn)向全表掃描
4. 不能使用索引中最左邊的列作為范圍條件（范圍條件查詢會導(dǎo)致索引失效）
5. 盡量使用覆蓋索引（只訪問索引列對應(yīng)的查詢）扔字，減少select *
6. mysql 在使用不等于（！=或者<>）的時候無法使用索引會導(dǎo)致全表掃描
7. is null温技、is not null 無法使用索引
8. like一通配符開頭（'%abs...'）mysql索引會失效會變成全表掃描革为，通配符%在右邊會走索引，覆蓋索引可以使通配符%aaa%生效
9. 字符串不加單引號會導(dǎo)致索引失效（select * from
   t_test where user_name= '250' ; select * from t_test where user_name=250）
10. 少用or舵鳞，用它連接時可能會導(dǎo)致索引失效（不會失效：select * form t_test where user_name='aa' or user_name='cc';失效：select * from t_test where user_name='aa' or user_age=12）
11. group by 大部分場景都會進行排序震檩，如果排序的時候出現(xiàn)filesort則可能會出現(xiàn)temporary table，導(dǎo)致性能直線下降
12. 一般order by都是給定范圍進行排序蜓堕，order by 使用索引左前綴原則

總結(jié)五句：
火車不能沒有頭抛虏；
火車中間不能斷；
索引列上不計算套才；
like開頭跟常量迂猴；
范圍之后全失效

建議

1. 對于單值索引，盡量選擇針對當(dāng)前query過濾性更好的索引
2. 在選擇組合索引時背伴，當(dāng)前query中過濾性最好的字段在索引字段排序中沸毁，位置越靠左越好
3. 在選擇組合索引是，盡量選擇可以包含當(dāng)前query中where子句中更多字段的索引（索引覆蓋原則）
4. 盡可能通過分析統(tǒng)計信息和調(diào)整query的寫法來達到選擇適合索引的目的
5. 盡可能在索引列上完成排序查詢

MYSQL查詢優(yōu)化

1. 在保證數(shù)據(jù)完整性不受影響的情況下傻寂，永遠(yuǎn)使用小表驅(qū)動大表查詢息尺，因為小驅(qū)動大，連接次數(shù)少疾掰；大驅(qū)動小搂誉，鏈接次數(shù)大（類似嵌套for循環(huán)）
   查詢語句1：select * from A where id in (select id from B)
   等價于
   for sleect id from b
   for select * from A where A.id=B.id
   查詢語句1也可些成：select * from A where exists(select 1 from B where B.id=A.id)
   等價于
   for select * from A
   for select * from B where B.id=A.id
   所以可得出，當(dāng)A數(shù)據(jù)量小B數(shù)據(jù)量時静檬，exists比in效率高
2. order by查詢優(yōu)化
3. group by查詢優(yōu)化

order by查詢優(yōu)化

1. order by排序時勒葱，盡可能不適用select *，只查自己需要的部分字段巴柿；因為當(dāng)select的字段大小總和小于max_length_for_sort_data而排序字段不是TEXT|BLOB類型時凛虽，會用4.3.1版本后改進的單路排序算法，若是大于max_length_for_sort_data則會用老MySQL版本的多路排序算法广恢。同時查詢字段太多可能會導(dǎo)致另外一種情況——就是使用兩種算法查到的數(shù)據(jù)結(jié)果都有可能超過sort_buffer的容量凯旋，超過 之后會創(chuàng)建tmp文件進行合并排序，導(dǎo)致多次磁盤IO，但是使用單路排序算法的風(fēng)險會更大一些至非，所以這時候可以適當(dāng)提高sort_buffer_size配置
2. 適當(dāng)提高sort_buffer_size钠署，提高此參數(shù)配置對單路、多路排序算法有會提高效率荒椭，配置值不是無限大谐鼎，要根據(jù)系統(tǒng)配置能力適當(dāng)設(shè)置
3. 適當(dāng)提高max_length_for_sort_data，提高這個參數(shù)會增加改進算法的概率趣惠，單如果提高太多狸棍，數(shù)據(jù)總?cè)萘砍鰏ort_buffer_size的概率就會增大，會導(dǎo)致明顯的磁盤IO和CPU使用率降低

order by正常使用索引的場景

索引：idx_xxx(a,b,c);
order by a;
order by a,b;
order by a,b,c;
order by a desc ,b desc ,c desc;
如果where語句使用索引的左前綴定義為常量味悄，則order by以下場景能使用索引
where a= const order by b,c;
where a= const1 and b=const2 order by c;
where a= const1 and b>const2 order by b,c

order by不能使用索引場景（ 可能產(chǎn)生 filesort）

order by a asc,b desc,c desc;#排序不一致
where g=const order by b,c;#丟失a索引
where a=const order by c;#中間丟失b索引
where a=const order by a,d;#d不是索引的一部分
where a in (......) order by b,c;#范圍查找之后索引全失效了

group by查詢優(yōu)化

1. group by 實質(zhì)是先排序后進行分組草戈，按照索引最佳左前綴原則
2. 當(dāng)無法使用索引列時，適當(dāng)增大max_length_for_sort_data參數(shù)配置和增大sort_buffer_size配置
3. where性能高于having侍瑟，能在where限定的條件盡量不要寫在having里面

慢查詢?nèi)罩痉治?/h2>

MySQL慢查詢?nèi)罩臼莔ysql提供的一種日志記錄唐片，它用來記錄在mysql中響應(yīng)時間超過閥值的語句，具體指的是運行時間超過long_query_time的SQL語句涨颜，超過閥值的會被記錄在慢查詢?nèi)罩局蟹丫隆ＤJ(rèn)值為10秒，默認(rèn)情況下不被開啟庭瑰，如果沒有調(diào)優(yōu)需要時也不建議開啟揽思，因為開啟會增加日志文件寫入帶來性能影響。

a. 查看開啟狀態(tài)：show variables like '%slow_query_log%';
b. 當(dāng)前session開啟開關(guān)：set global slow_query_log =1;
c. 當(dāng)前session關(guān)閉開關(guān)：set global slow_query_log =0;
d. 永久性開啟關(guān)閉開關(guān)需要在my.conf配置
slow_query_log=1
slow_query_log_file=日志路徑
e. 查看慢查詢SQL時間配置：show variables like 'long_query_time';
修改慢查詢SQL時間后需要重新打開連接才能顯示已生效见擦，在原有session不會顯示已生效的值；
f. 查看系統(tǒng)慢SQL條數(shù)：show global status like ‘%Slow_queries%’;
g. 查看慢SQL文件位置：show variables like '%slow_query_log_file%';

慢SQL分析工具

分析語法：
s：表示SQL查詢排序方式
c：訪問次數(shù)
l：鎖定時間
r：返回記錄數(shù)量
t：查詢時間
al：平均鎖定時間
ar：平均返回記錄數(shù)
at：平均查詢時間
t：返回當(dāng)前前面多少條慢SQL記錄
g：后邊搭配一個正則匹配模式（大小寫不敏感）
例子：
得到返回記錄集最多的10個慢SQL
mysqldumpslow -s r -t 10 /var/..../slow.log
得到訪問次數(shù)最多的慢SQL
mysqldumpslow -s -c -t 10 /var/..../slow.log
得到找按照時間排序的前10條慢SQL里包含左連接的SQL
mysqldumpslow -s t -t 10 -g "left join" /var/....../slow.log
使用命令時羹令，建議結(jié)合more鲤屡，否則可能出現(xiàn)爆屏情況
mysqldumpslow -s t -t 10 /var....../slow.log | more

show profile分析

show profile用于分析SQL在mysql中執(zhí)行時資源消耗詳細(xì)情況，包括每個執(zhí)行細(xì)節(jié)的資源開銷信息
基礎(chǔ)語法

#查看配置
show variables  like '%profil%';
#profile默認(rèn)是關(guān)閉的福侈，需要打開profile配置
set  profiling =on;
#設(shè)置統(tǒng)計SQL的數(shù)量
set profiling_history_size= 500;
#查看統(tǒng)計的SQL結(jié)果
show profiles; 
#查看執(zhí)行id為30的SQL語句資源詳細(xì)情況酒来，包含CPU和磁盤IO
show profile cpu ,block io for query 30;
#查看執(zhí)行id為30的SQL語句的所有資源詳細(xì)情況
show profile all for query 30;
#關(guān)閉profile統(tǒng)計
set profiling =off;
#恢復(fù)默認(rèn)值15條
set profiling_history_size =15;

當(dāng)show profile *** for query n得到的資源詳情里的status列包含一下四種情形之一時，SQL語句問題比較大肪凛，必須優(yōu)化

1. convert heap to myisam：查詢結(jié)果太大堰汉，內(nèi)存不夠用且忘磁盤上搬了
2. create tmp table：創(chuàng)建臨時表，創(chuàng)建臨時表會拷貝數(shù)據(jù)和刪除臨時表操作
3. copying to tmp table on disk：把內(nèi)存中的臨時表復(fù)制到磁盤伟墙，高危操作
4. lock：鎖表

show profile分析步驟

1. 查看當(dāng)前mysql版本是否支持show profile
2. 開啟show profile功能翘鸭，并適當(dāng)調(diào)大profiling_history_size數(shù)量
3. 運行SQL語句
4. 查看show profile統(tǒng)計結(jié)果，show profiles
5. 通過show profile CPU, block io for query [queryId]診斷SQL戳葵，
6. 根據(jù)診斷結(jié)果定位問題就乓；（判斷是否包含上面的四種情形，如果有必須優(yōu)化）

全局查詢?nèi)罩痉治?/h2>

打開全局日志之后，mysql會記錄所有的SQL操作
全局查詢?nèi)罩鹃_啟有兩種方式

1. my.conf里開啟配置
   //開啟
   general_log=1
   //全局日志文件路徑
   general_log_file=/path/logfile
   //輸出格式
   log_output=FILE
2. 編碼開啟配置
   set global general_log=1;
   set global log_output='TABLE';
   開啟之后生蚁，所寫的SQL語句都會記錄到mysql的general_log表中噩翠，可以通過select查看表數(shù)據(jù)
3. 不要在生產(chǎn)環(huán)境開啟全局日志配置

---

mysql性能分析總結(jié)

1. 開啟慢查詢，捕獲慢查詢SQL
2. explain分析慢查詢SQL語句
3. show profile 查詢SQL在木mysql服務(wù)器里的執(zhí)行細(xì)節(jié)和生命周期情況
4. MYSQL數(shù)據(jù)服務(wù)器參數(shù)調(diào)優(yōu)
5. 全局查詢?nèi)罩緝?yōu)化