美團網技巧團隊分享的MySQL索引及慢查詢優化教程。本站提示廣大學習愛好者：（美團網技巧團隊分享的MySQL索引及慢查詢優化教程）文章只能為提供參考，不一定能成為您想要的結果。以下是美團網技巧團隊分享的MySQL索引及慢查詢優化教程正文
MySQL憑仗著精彩的機能、昂貴的本錢、豐碩的資本，曾經成為絕年夜多半互聯網公司的首選關系型數據庫。固然機能精彩，但所謂“好馬配好鞍”，若何可以或許更好的應用它，曾經成為開辟工程師的?課，我們常常會從職位描寫上看到諸如“精曉MySQL”、“SQL語句優化”、“懂得數據庫道理”等請求。我們曉得普通的運用體系，讀寫比例在10:1閣下，並且拔出操作和普通的更新操作很少湧現機能成績，碰到最多的，也是最輕易出成績的，照樣一些龐雜的查詢操作，所以查詢語句的優化明顯是重中之重。
自己從13年7月份起，一向在美團焦點營業體系部做慢查詢的優化任務，合計十余個體系，累計處理和積聚了上百個慢查詢案例。跟著營業的龐雜性晉升，碰到的成績光怪陸離，八門五花，匪夷所思。本文旨在以開辟工程師的角度來說明數據庫索引的道理和若何優化慢查詢。
一個慢查詢激發的思慮
select count(*) from task where status=2 and operator_id=20839 and operate_time>1371169729 and operate_time<1371174603 and type=2;
體系應用者反響有一個功效愈來愈慢，因而工程師找到了下面的SQL。
而且興趣沖沖的找到了我，“這個SQL須要優化，給我把每一個字段都加上索引”
我很驚奇，問道“為何須要每一個字段都加上索引？”
“把查詢的字段都加上索引會更快”工程師信念滿滿
“這類情形完整可以建一個結合索引，由於是最左前綴婚配，所以operate_time須要放到最初，並且還須要把其他相干的查詢都拿來，須要做一個綜合評價。”
“結合索引？最左前綴婚配？綜合評價？”工程師不由墮入了尋思。
多半情形下，我們曉得索引可以或許進步查詢效力，但應當若何樹立索引？索引的次序若何？很多人卻只曉得年夜概。其實懂得這些概念其實不難，並且索引的道理遠沒有想象的那末龐雜。
MySQL索引道理
索引目標
索引的目標在於進步查詢效力，可以類比字典，假如要查“mysql”這個單詞，我們確定須要定位到m字母，然後從下往下找到y字母，再找到剩下的sql。假如沒有索引，那末你能夠須要把一切單詞看一遍能力找到你想要的，假如我想找到m開首的單詞呢？或許ze開首的單詞呢？是否是認為假如沒有索引，這個工作基本沒法完成？
索引道理
除辭書，生涯中到處可見索引的例子，如火車站的車次表、圖書的目次等。它們的道理都是一樣的，經由過程赓續的減少想要取得數據的規模來挑選出終究想要的成果，同時把隨機的事宜釀成次序的事宜，也就是我們老是經由過程統一種查找方法來鎖定命據。
數據庫也是一樣，但明顯要龐雜很多，由於不只面對著等值查詢，還有規模查詢(>、<、between、in)、隱約查詢(like)、並集查詢(or)等等。數據庫應當選擇怎樣樣的方法來應對一切的成績呢？我們回憶字典的例子，能不克不及把數據分紅段，然後分段查詢呢？最簡略的假如1000條數據，1到100分紅第一段，101到200分紅第二段，201到300分紅第三段......如許查第250條數據，只需找第三段便可以了，一會兒去除90%的有效數據。但假如是1萬萬的記載呢，分紅幾段比擬好？稍有算法基本的同窗會想到搜刮樹，其均勻龐雜度是lgN，具有不錯的查詢機能。但這裡我們疏忽了一個症結的成績，龐雜度模子是基於每次雷同的操作本錢來斟酌的，數據庫完成比擬龐雜，數據保留在磁盤上，而為了進步機能，每次又可以把部門數據讀入內存來盤算，由於我們曉得拜訪磁盤的本錢年夜概是拜訪內存的十萬倍閣下，所以簡略的搜刮樹難以知足龐雜的運用場景。
磁盤IO與預讀
後面提到了拜訪磁盤，那末這裡先簡略引見一下磁盤IO和預讀，磁盤讀取數據靠的是機械活動，每次讀取數據消費的時光可以分為尋道時光、扭轉延遲、傳輸時光三個部門，尋道時光指的是磁臂挪動到指定磁道所須要的時光，主流磁盤普通在5ms以下；扭轉延遲就是我們常常據說的磁盤轉速，好比一個磁盤7200轉，表現每分鐘能轉7200次，也就是說1秒鐘能轉120次，扭轉延遲就是1/120/2 = 4.17ms；傳輸時光指的是從磁盤讀出或將數據寫入磁盤的時光，普通在零點幾毫秒，絕對於前兩個時光可以疏忽不計。那末拜訪一次磁盤的時光，即一次磁盤IO的時光約等於5+4.17 = 9ms閣下，聽起來還挺不錯的，但要曉得一台500 -MIPS的機械每秒可以履行5億條指令，由於指令依附的是電的性質，換句話說履行一次IO的時光可以履行40萬條指令，數據庫動辄十萬百萬甚至萬萬級數據，每次9毫秒的時光，明顯是個災害。下圖是盤算機硬件延遲的比較圖，供年夜家參考：
斟酌到磁盤IO長短常昂揚的操作，盤算機操作體系做了一些優化，當一次IO時，不但把以後磁盤地址的數據，而是把相鄰的數據也都讀取到內存緩沖區內，由於部分預讀性道理告知我們，當盤算機拜訪一個地址的數據的時刻，與其相鄰的數據也會很快被拜訪到。每次IO讀取的數據我們稱之為一頁(page)。詳細一頁有多年夜數據跟操作體系有關，普通為4k或8k，也就是我們讀取一頁內的數據時刻，現實上才產生了一次IO，這個實際關於索引的數據構造設計異常有贊助。
索引的數據構造
後面講了生涯中索引的例子，索引的根本道理，數據庫的龐雜性，又講了操作體系的相干常識，目標就是讓年夜家懂得，任何一種數據構造都不是平空發生的，必定會有它的配景和應用場景，我們如今總結一下，我們須要這類數據構造可以或許做些甚麼，其實很簡略，那就是：每次查找數據時把磁盤IO次數掌握在一個很小的數目級，最好是常數數目級。那末我們就想到假如一個高度可控的多路搜刮樹能否能知足需求呢？就如許，b+樹應運而生。
詳解b+樹
如上圖，是一顆b+樹，關於b+樹的界說可以拜見B+樹，這裡只說一些重點，淺藍色的塊我們稱之為一個磁盤塊，可以看到每一個磁盤塊包括幾個數據項（深藍色所示）和指針（黃色所示），如磁盤塊1包括數據項17和35，包括指針P1、P2、P3，P1表現小於17的磁盤塊，P2表現在17和35之間的磁盤塊，P3表現年夜於35的磁盤塊。真實的數據存在於葉子節點即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非葉子節點只不存儲真實的數據，只存儲指引搜刮偏向的數據項，如17、35其實不真實存在於數據表中。
b+樹的查找進程
如圖所示，假如要查找數據項29，那末起首會把磁盤塊1由磁盤加載到內存，此時產生一次IO，在內存頂用二分查找肯定29在17和35之間，鎖定磁盤塊1的P2指針，內存時光由於異常短（比擬磁盤的IO）可以疏忽不計，經由過程磁盤塊1的P2指針的磁盤地址把磁盤塊3由磁盤加載到內存，產生第二次IO，29在26和30之間，鎖定磁盤塊3的P2指針，經由過程指針加載磁盤塊8到內存，產生第三次IO，同時內存中做二分查找找到29，停止查詢，總計三次IO。真實的情形是，3層的b+樹可以表現上百萬的數據，假如上百萬的數據查找只須要三次IO，機能進步將是偉大的，假如沒有索引，每一個數據項都要產生一次IO，那末總共須要百萬次的IO，明顯本錢異常異常高。
b+樹性質
1.經由過程下面的剖析，我們曉得IO次數取決於b+數的高度h，假定以後數據表的數據為N，每一個磁盤塊的數據項的數目是m，則有h=㏒(m+1)N，當數據量N必定的情形下，m越年夜，h越小；而m = 磁盤塊的年夜小 / 數據項的年夜小，磁盤塊的年夜小也就是一個數據頁的年夜小，是固定的，假如數據項占的空間越小，數據項的數目越多，樹的高度越低。這就是為何每一個數據項，即索引字段要盡可能的小，好比int占4字節，要比bigint8字節少一半。這也是為何b+樹請求把真實的數據放到葉子節點而不是內層節點，一旦放到內層節點，磁盤塊的數據項會年夜幅度降低，招致樹增高。當數據項等於1時將會退步成線性表。
2.當b+樹的數據項是復合的數據構造，好比(name,age,sex)的時刻，b+數是依照從左到右的次序來樹立搜刮樹的，好比當(張三,20,F)如許的數據來檢索的時刻，b+樹會優先比擬name來肯定下一步的所搜偏向，假如name雷同再順次比擬age和sex，最初獲得檢索的數據；但當(20,F)如許的沒有name的數據來的時刻，b+樹就不曉得下一步該查哪一個節點，由於樹立搜刮樹的時刻name就是第一個比擬因子，必需要先依據name來搜刮能力曉得下一步去哪裡查詢。好比當(張三,F)如許的數據來檢索時，b+樹可以用name來指定搜刮偏向，但下一個字段age的缺掉，所以只能把名字等於張三的數據都找到，然後再婚配性別是F的數據了， 這個長短常主要的性質，即索引的最左婚配特征。
慢查詢優化
關於MySQL索引道理是比擬死板的器械，年夜家只須要有一個理性的熟悉，其實不須要懂得得異常透辟和深刻。我們回頭來看看一開端我們說的慢查詢，懂得完索引道理以後，年夜家是否是有甚麼設法主意呢？先總結一下索引的幾年夜根本准繩
建索引的幾年夜准繩
1.最左前綴婚配准繩，異常主要的准繩，mysql會一向向右婚配直到碰到規模查詢(>、<、between、like)就停滯婚配，好比a = 1 and b = 2 and c > 3 and d = 4 假如樹立(a,b,c,d)次序的索引，d是用不到索引的，假如樹立(a,b,d,c)的索引則都可以用到，a,b,d的次序可以隨意率性調劑。
2.=和in可以亂序，好比a = 1 and b = 2 and c = 3 樹立(a,b,c)索引可以隨意率性次序，mysql的查詢優化器會幫你優化成索引可以辨認的情勢
3.盡可能選擇辨別度高的列作為索引,辨別度的公式是count(distinct col)/count(*)，表現字段不反復的比例，比例越年夜我們掃描的記載數越少，獨一鍵的辨別度是1，而一些狀況、性別字段能夠在年夜數據眼前辨別度就是0，那能夠有人會問，這個比例有甚麼經歷值嗎？應用場景分歧，這個值也很難肯定，普通須要join的字段我們都請求是0.1以上，即均勻1條掃描10筆記錄
4.索引列不克不及介入盤算，堅持列“清潔”，好比from_unixtime(create_time) = '2014-05-29'就不克不及應用到索引，緣由很簡略，b+樹中存的都是數據表中的字段值，但停止檢索時，須要把一切元素都運用函數能力比擬，明顯本錢太年夜。所以語句應當寫成
create_time = unix_timestamp('2014-05-29');
5.盡可能的擴大索引，不要新建索引。好比表中曾經有a的索引，如今要加(a,b)的索引，那末只須要修正本來的索引便可
回到開端的慢查詢
依據最左婚配准繩，最開端的sql語句的索引應當是status、operator_id、type、operate_time的結合索引；個中status、operator_id、type的次序可以倒置，所以我才會說，把這個表的一切相干查詢都找到，會綜合剖析；
好比還有以下查詢
select * from task where status = 0 and type = 12 limit 10; select count(*) from task where status = 0 ;
那末索引樹立成(status,type,operator_id,operate_time)就長短常准確的，由於可以籠罩到一切情形。這個就是應用了索引的最左婚配的准繩
查詢優化神器 - explain敕令
關於explain敕令信任年夜家其實不生疏，詳細用法和字段寄義可以參考官網explain-output，這裡須要強調rows是焦點目標，絕年夜部門rows小的語句履行必定很快（有破例，上面會講到）。所以優化語句根本上都是在優化rows。
慢查詢優化根本步調
0.先運轉看看能否真的很慢，留意設置SQL_NO_CACHE
1.where前提單表查，鎖定最小前往記載表。這句話的意思是把查詢語句的where都運用到表中前往的記載數最小的表開端查起，單表每一個字段分離查詢，看哪一個字段的辨別度最高
2.explain檢查履行籌劃，能否與1預期分歧（從鎖定記載較少的表開端查詢）
3.order by limit 情勢的sql語句讓排序的表優先查
4.懂得營業方應用場景
5.加索引時參照建索引的幾年夜准繩
6.不雅察成果，不相符預期持續從0剖析
幾個慢查詢案例
上面幾個例子具體說明了若何剖析和優化慢查詢
龐雜語句寫法
許多情形下，我們寫SQL只是為了完成功效，這只是第一步，分歧的語句書寫方法關於效力常常有實質的差異，這請求我們對mysql的履行籌劃和索引准繩有異常清晰的熟悉，請看上面的語句
select
distinct cert.emp_id
from
cm_log cl
inner join
(
select
emp.id as emp_id,
emp_cert.id as cert_id
from
employee emp
left join
emp_certificate emp_cert
on emp.id = emp_cert.emp_id
where
emp.is_deleted=0
) cert
on (
cl.ref_table='Employee'
and cl.ref_oid= cert.emp_id
)
or (
cl.ref_table='EmpCertificate'
and cl.ref_oid= cert.cert_id
)
where
cl.last_upd_date >='2013-11-07 15:03:00'
and cl.last_upd_date<='2013-11-08 16:00:00';
0.先運轉一下，53筆記錄 1.87秒，又沒有效聚合語句，比擬慢
53 rows in set (1.87 sec)
1.explain
+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+ | 1 | PRIMARY | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where; Using temporary | | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 63727 | Using where; Using join buffer | | 2 | DERIVED | emp | ALL | NULL | NULL | NULL | NULL | 13317 | Using where | | 2 | DERIVED | emp_cert | ref | emp_certificate_empid | emp_certificate_empid | 4 | meituanorg.emp.id | 1 | Using index | +----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+
簡述一下履行籌劃，起首mysql依據idx_last_upd_date索引掃描cm_log表取得379筆記錄；然後查表掃描了63727筆記錄，分為兩部門，derived表現結構表，也就是不存在的表，可以簡略懂得成是一個語句構成的成果集，前面的數字表現語句的ID。derived2表現的是ID = 2的查詢結構了虛擬表，而且前往了63727筆記錄。我們再來看看ID = 2的語句畢竟做了寫甚麼前往了這麼年夜量的數據，起首全表掃描employee表13317筆記錄，然後依據索引emp_certificate_empid聯系關系emp_certificate表，rows = 1表現，每一個聯系關系都只鎖定了一筆記錄，效力比擬高。取得後，再和cm_log的379筆記錄依據規矩聯系關系。從履行進程上可以看出前往了太多的數據，前往的數據絕年夜部門cm_log都用不到，由於cm_log只鎖定了379筆記錄。
若何優化呢？可以看到我們在運轉完後照樣要和cm_log做join,那末我們能不克不及之前和cm_log做join呢？細心剖析語句不難發明，其根本思惟是假如cm_log的ref_table是EmpCertificate就聯系關系emp_certificate表，假如ref_table是Employee就聯系關系employee表，我們完整可以拆成兩部門，並用union銜接起來，留意這裡用union，而不消union all是由於原語句有“distinct”來獲得獨一的記載，而union正好具有了這類功效。假如原語句中沒有distinct不須要去重，我們便可以直接應用union all了，由於應用union須要去重的舉措，會影響SQL機能。
優化過的語句以下
select emp.id from cm_log cl inner join employee emp on cl.ref_table = 'Employee' and cl.ref_oid = emp.id where cl.last_upd_date >='2013-11-07 15:03:00' and cl.last_upd_date<='2013-11-08 16:00:00' and emp.is_deleted = 0 union select emp.id from cm_log cl inner join emp_certificate ec on cl.ref_table = 'EmpCertificate' and cl.ref_oid = ec.id inner join employee emp on emp.id = ec.emp_id where cl.last_upd_date >='2013-11-07 15:03:00' and cl.last_upd_date<='2013-11-08 16:00:00' and emp.is_deleted = 0
2.不須要懂得營業場景，只須要改革的語句和改革之前的語句堅持成果分歧
3.現有索引可以知足，不須要建索引
4.用改革後的語句試驗一下，只須要10ms 下降了近200倍！
+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+ | 1 | PRIMARY | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where | | 1 | PRIMARY | emp | eq_ref | PRIMARY | PRIMARY | 4 | meituanorg.cl.ref_oid | 1 | Using where | | 2 | UNION | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where | | 2 | UNION | ec | eq_ref | PRIMARY,emp_certificate_empid | PRIMARY | 4 | meituanorg.cl.ref_oid | 1 | | | 2 | UNION | emp | eq_ref | PRIMARY | PRIMARY | 4 | meituanorg.ec.emp_id | 1 | Using where | | NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | | +----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+ 53 rows in set (0.01 sec)
明白運用場景
舉這個例子的目標在於推翻我們對列的辨別度的認知，普通上我們以為辨別度越高的列，越輕易鎖定更少的記載，但在一些特別的情形下，這類實際是有局限性的
select * from stage_poi sp where sp.accurate_result=1 and ( sp.sync_status=0 or sp.sync_status=2 or sp.sync_status=4 );
0.先看看運轉多長時光,951條數據6.22秒，真的很慢
951 rows in set (6.22 sec)
1.先explain，rows到達了361萬，type = ALL注解是全表掃描
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | sp | ALL | NULL | NULL | NULL | NULL | 3613155 | Using where | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
2.一切字段都運用查詢前往記載數，由於是單表查詢 0曾經做過了951條
3.讓explain的rows 盡可能切近親近951
看一下accurate_result = 1的記載數
select count(*),accurate_result from stage_poi group by accurate_result; +----------+-----------------+ | count(*) | accurate_result | +----------+-----------------+ | 1023 | -1 | | 2114655 | 0 | | 972815 | 1 | +----------+-----------------+
我們看到accurate_result這個字段的辨別度異常低，全部表只要-1,0,1三個值，加上索引也沒法鎖定特殊大批的數據
再看一下sync_status字段的情形
select count(*),sync_status from stage_poi group by sync_status; +----------+-------------+ | count(*) | sync_status | +----------+-------------+ | 3080 | 0 | | 3085413 | 3 | +----------+-------------+
異樣的辨別度也很低，依據實際，也不合適樹立索引
成績剖析到這，似乎得出了這個表沒法優化的結論，兩個列的辨別度都很低，即使加上索引也只能順應這類情形，很難做廣泛性的優化，好比當sync_status 0、3散布的很均勻，那末鎖定記載也是百萬級其余
4.找營業方去溝通，看看應用場景。營業方是這麼來應用這個SQL語句的，每隔五分鐘會掃描相符前提的數據，處置完成後把sync_status這個字段釀成1,五分鐘相符前提的記載數其實不會太多，1000個閣下。懂得了營業方的應用場景後，優化這個SQL就變得簡略了，由於營業方包管了數據的不屈衡，假如加上索引可以過濾失落絕年夜部門不須要的數據
5.依據樹立索引規矩，應用以下語句樹立索引
alter table stage_poi add index idx_acc_status(accurate_result,sync_status);
6.不雅察預期成果,發明只須要200ms，快了30多倍。
952 rows in set (0.20 sec)
我們再往返顧一下剖析成績的進程，單表查詢絕對來講比擬好優化，年夜部門時刻只須要把where前提外面的字段按照規矩加上索引就好，假如只是這類“無腦”優化的話，明顯一些辨別度異常低的列，不該該加索引的列也會被加上索引，如許會對拔出、更新機能形成嚴重的影響，同時也有能夠影響其它的查詢語句。所以我們第4步驟差SQL的應用場景異常症結，我們只要曉得這個營業場景，能力更好地幫助我們更好的剖析和優化查詢語句。
沒法優化的語句
select
c.id,
c.name,
c.position,
c.sex,
c.phone,
c.office_phone,
c.feature_info,
c.birthday,
c.creator_id,
c.is_keyperson,
c.giveup_reason,
c.status,
c.data_source,
from_unixtime(c.created_time) as created_time,
from_unixtime(c.last_modified) as last_modified,
c.last_modified_user_id
from
contact c
inner join
contact_branch cb
on c.id = cb.contact_id
inner join
branch_user bu
on cb.branch_id = bu.branch_id
and bu.status in (
1,
2)
inner join
org_emp_info oei
on oei.data_id = bu.user_id
and oei.node_left >= 2875
and oei.node_right <= 10802
and oei.org_category = - 1
order by
c.created_time desc limit 0 ,
10;
照樣幾個步調
0.先看語句運轉多長時光，10筆記錄用了13秒，曾經弗成忍耐
10 rows in set (13.06 sec)
1.explain
+----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+ | 1 | SIMPLE | oei | ref | idx_category_left_right,idx_data_id | idx_category_left_right | 5 | const | 8849 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | bu | ref | PRIMARY,idx_userid_status | idx_userid_status | 4 | meituancrm.oei.data_id | 76 | Using where; Using index | | 1 | SIMPLE | cb | ref | idx_branch_id,idx_contact_branch_id | idx_branch_id | 4 | meituancrm.bu.branch_id | 1 | | | 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 108 | meituancrm.cb.contact_id | 1 | | +----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+
從履行籌劃上看，mysql先查org_emp_info表掃描8849記載，再用索引idx_userid_status聯系關系branch_user表，再用索引idx_branch_id聯系關系contact_branch表，最初主鍵聯系關系contact表。
rows前往的都異常少，看不到有甚麼異常情形。我們在看一下語句，發明前面有order by + limit組合，會不會是排序量太年夜弄的？因而我們簡化SQL，去失落前面的order by 和 limit，看看究竟用了若干記載來排序
select
count(*)
from
contact c
inner join
contact_branch cb
on c.id = cb.contact_id
inner join
branch_user bu
on cb.branch_id = bu.branch_id
and bu.status in (
1,
2)
inner join
org_emp_info oei
on oei.data_id = bu.user_id
and oei.node_left >= 2875
and oei.node_right <= 10802
and oei.org_category = - 1
+----------+ | count(*) | +----------+ | 778878 | +----------+ 1 row in set (5.19 sec)
發明排序之前竟然鎖定了778878筆記錄，假如針對70萬的成果集排序，將是災害性的，怪不得這麼慢，那我們能不克不及換個思緒，先依據contact的created_time排序，再來join會不會比擬快呢？
因而改革成上面的語句，也能夠用straight_join來優化
select c.id, c.name, c.position, c.sex, c.phone, c.office_phone, c.feature_info, c.birthday, c.creator_id, c.is_keyperson, c.giveup_reason, c.status, c.data_source, from_unixtime(c.created_time) as created_time, from_unixtime(c.last_modified) as last_modified, c.last_modified_user_id from contact c where exists ( select 1 from contact_branch cb inner join branch_user bu on cb.branch_id = bu.branch_id and bu.status in ( 1, 2) inner join org_emp_info oei on oei.data_id = bu.user_id and oei.node_left >= 2875 and oei.node_right <= 10802 and oei.org_category = - 1 where c.id = cb.contact_id ) order by c.created_time desc limit 0 , 10;
驗證一下後果 估計在1ms內，晉升了13000多倍！
```sql 10 rows in set (0.00 sec)
本認為至此年夜工樂成，但我們在後面的剖析中漏了一個細節，先排序再join和先join再排序實際上開支是一樣的，為什麼晉升這麼多是由於有一個limit！年夜致履行進程是：mysql先按索引排序獲得前10筆記錄，然後再去join過濾，當發明不敷10條的時刻，再次去10條，再次join，這明顯在內層join過濾的數據異常多的時刻，將是災害的，極端情形，內層一條數據都找不到，mysql還傻乎乎的每次取10條，簡直遍歷了這個數據表！
用分歧參數的SQL實驗下
select
sql_no_cache c.id,
c.name,
c.position,
c.sex,
c.phone,
c.office_phone,
c.feature_info,
c.birthday,
c.creator_id,
c.is_keyperson,
c.giveup_reason,
c.status,
c.data_source,
from_unixtime(c.created_time) as created_time,
from_unixtime(c.last_modified) as last_modified,
c.last_modified_user_id
from
contact c
where
exists (
select
1
from
contact_branch cb
inner join
branch_user bu
on cb.branch_id = bu.branch_id
and bu.status in (
1,
2)
inner join
org_emp_info oei
on oei.data_id = bu.user_id
and oei.node_left >= 2875
and oei.node_right <= 2875
and oei.org_category = - 1
where
c.id = cb.contact_id
)
order by
c.created_time desc limit 0 ,
10;
Empty set (2 min 18.99 sec)
2 min 18.99 sec！比之前的情形還蹩腳許多。因為mysql的nested loop機制，碰到這類情形，根本是沒法優化的。這條語句終究也只能交給運用體系去優化本身的邏輯了。
經由過程這個例子我們可以看到，其實不是一切語句都能優化，而常常我們優化時，因為SQL用例回歸時落失落一些極端情形，會形成比本來還嚴重的效果。所以，第一：不要期望一切語句都能經由過程SQL優化，第二：不要過於自負，只針對詳細case來優化，而疏忽了更龐雜的情形。
慢查詢的案例就剖析到這兒，以上只是一些比擬典范的案例。我們在優化進程中碰到過跨越1000行，觸及到16個表join的“渣滓SQL”，也碰到過線上線下數據庫差別招致運用直接被慢查詢拖逝世，也碰到過varchar等值比擬沒有寫單引號，還碰到過笛卡爾積查詢直接把從庫弄逝世。再多的案例其實也只是一些經歷的積聚，假如我們熟習查詢優化器、索引的外部道理，那末剖析這些案例就變得特殊簡略了。
寫在前面的話
本文以一個慢查詢案例引入了MySQL索引道理、優化慢查詢的一些辦法論;並針對碰到的典范案例做了具體的剖析。其實做了這麼長時光的語句優化後才發明，任何數據庫層面的優化都抵不上運用體系的優化，異樣是MySQL，可以用來支持Google/FaceBook/Taobao運用，但能夠連你的小我網站都撐不住。套用比來比擬風行的話：“查詢輕易，優化不容易，且寫且珍愛！”
