個人專題目錄
1. 性能下降SQL慢 執(zhí)行時間長 等待時間長
1.1 查詢語句寫的爛
1.2 索引失效
單值
復(fù)合
1.3 關(guān)聯(lián)查詢太多join(設(shè)計缺陷或不得已的需求)
1.4 服務(wù)器調(diào)優(yōu)及各個參數(shù)設(shè)置(緩沖\線程數(shù)等)
2. 常見通用的join查詢
2.1 SQL執(zhí)行順序
手寫
SELECT DISTINCT
< select_list >
FROM
< left_table > < join_type >
JOIN < right_table > ON < join_condition >
WHERE
< where_condition >
GROUP BY
< group_by_list >
HAVING
< having_condition >
ORDER BY
< order_by_condition >
LIMIT < limit_number >
機讀
FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>
總結(jié)
2.2 Join圖
2.3 建表SQL
2.4 7種Join
3. 索引簡介
3.1 是什么
MySQL官方對索引的定義為：索引(Index)是幫助MySQL高校獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。 可以得到索引的本質(zhì)：索引是數(shù)據(jù)結(jié)構(gòu)
- 索引的目的在于提高查詢效率，可以類比字典。
- 如果要查‘mysql’這個單詞，我們肯定需求定位到m字母，然后從上往下找到y(tǒng)字母，再找到sql.
- 如果沒有索引，順序遍歷
你可以簡單理解為"排好序的快速查找數(shù)據(jù)結(jié)構(gòu)"。
詳解(重要)
結(jié)論
數(shù)據(jù)本身之外,數(shù)據(jù)庫還維護著一個滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式指向數(shù)據(jù)， 這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)的基礎(chǔ)上實現(xiàn)高級查找算法,這種數(shù)據(jù)結(jié)構(gòu)就是索引。
一般來說索引本身也很大，不可能全部存儲在內(nèi)存中，因此索引往往以文件形式存儲在硬盤上
我們平時所說的索引，如果沒有特別指明，都是指B樹(多路搜索樹，并不一定是二叉樹)結(jié)構(gòu)組織的索引。其中聚集索引，次要索引，覆蓋索引， 復(fù)合索引，前綴索引，唯一索引默認(rèn)都是使用B+樹索引，統(tǒng)稱索引。當(dāng)然,除了B+樹這種類型的索引之外，還有哈希索引(hash index)等。
3.2 優(yōu)勢
類似大學(xué)圖書館建書目索引，提高數(shù)據(jù)檢索效率，降低數(shù)據(jù)庫的IO成本
通過索引列對數(shù)據(jù)進行排序，降低數(shù)據(jù)排序成本，降低了CPU的消耗
為什么要創(chuàng)建索引？這是因為，創(chuàng)建索引可以大大提高系統(tǒng)的查詢性能。
第一、通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。
第二、可以大大加快 數(shù)據(jù)的檢索速度，這也是創(chuàng)建索引的最主要的原因。
第三、可以加速表和表之間的連接，特別是在實現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義。
第四、在使用分組和排序子句進行數(shù)據(jù)檢索時，同樣可以顯著減少查詢中分組和排序的時間。
第五、通過使用索引，可以在查詢的過程中，使用查詢優(yōu)化器，提高系統(tǒng)的性能。
3.3 劣勢
實際上索引也是一張表，該表保存了主鍵和索引字段，并指向?qū)嶓w表的記錄,所以索引列也是要占用空間的
雖然索引大大提高了查詢速度，同時卻會降低更新表的速度,如果對表INSERT,UPDATE和DELETE。 因為更新表時，MySQL不僅要不存數(shù)據(jù)，還要保存一下索引文件每次更新添加了索引列的字段， 都會調(diào)整因為更新所帶來的鍵值變化后的索引信息
索引只是提高效率的一個因素，如果你的MySQL有大數(shù)據(jù)量的表，就需要花時間研究建立優(yōu)秀的索引，或優(yōu)化查詢語句
3.4 mysql索引分類
單值索引
即一個索引只包含單個列，一個表可以有多個單列索引
建議一張表索引不要超過5個
優(yōu)先考慮復(fù)合索引
唯一索引
索引列的值必須唯一，但允許有空值
復(fù)合索引
即一個索引包含多個列
基本語法
創(chuàng)建
CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));
如果是CHAR,VARCHAR類型，length可以小于字段實際長度； 如果是BLOB和TEXT類型，必須指定length。
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON(columnname(length));
刪除
DROP INDEX [indexName] ON mytable;
查看
SHOW INDEX FROM table_name\G
使用ALTER命令
3.5 mysql索引結(jié)構(gòu)
BTree索引
B-Tree索引，顧名思義，就是所有的索引節(jié)點都按照balance tree的數(shù)據(jù)結(jié)構(gòu)來存儲。B-tree結(jié)構(gòu)可以顯著減少定位記錄時所經(jīng)歷的中間過程，從而加快存取速度。
B-tree中，每個結(jié)點包含：
1、本結(jié)點所含關(guān)鍵字的個數(shù)；
2、指向父結(jié)點的指針；
3、關(guān)鍵字；
4、指向子結(jié)點的指針；
對于一棵m階B-tree，每個結(jié)點至多可以擁有m個子結(jié)點。各結(jié)點的關(guān)鍵字和可以擁有的子結(jié)點數(shù)都有限制，規(guī)定m階B-tree中，根結(jié)點至少有2個子結(jié)點，除非根結(jié)點為葉子節(jié)點，相應(yīng)的，根結(jié)點中關(guān)鍵字的個數(shù)為1m-1；非根結(jié)點至少有[m/2]（[]，向上取整）個子結(jié)點，相應(yīng)的，關(guān)鍵字個數(shù)為[m/2]-1m-1。
B-tree有以下特性：
1、關(guān)鍵字集合分布在整棵樹中；
2、任何一個關(guān)鍵字出現(xiàn)且只出現(xiàn)在一個結(jié)點中；
3、搜索有可能在非葉子結(jié)點結(jié)束；
4、其搜索性能等價于在關(guān)鍵字全集內(nèi)做一次二分查找；
5、自動層次控制；
由于限制了除根結(jié)點以外的非葉子結(jié)點，至少含有M/2個兒子，確保了結(jié)點的至少利用率，其最低搜索性能為：
其中，M為設(shè)定的非葉子結(jié)點最多子樹個數(shù)，N為關(guān)鍵字總數(shù)；
所以B-樹的性能總是等價于二分查找（與M值無關(guān)），也就沒有B樹平衡的問題；
由于M/2的限制，在插入結(jié)點時，如果結(jié)點已滿，需要將結(jié)點分裂為兩個各占M/2的結(jié)點；刪除結(jié)點時，需將兩個不足M/2的兄弟結(jié)點合并。
初始化介紹
一顆b+樹，淺藍(lán)色的塊我們稱為一個磁盤塊，可以看到每個磁盤塊包含幾個數(shù)據(jù)項(深藍(lán)色表示)和指針(黃色表示)，如磁盤塊1包含數(shù)據(jù)項17和35，包含指針P1、P2、P3.
P1表示小于17的磁盤塊，P2表示在17到35之間的磁盤塊，P3表示大于35的磁盤塊。
真實的數(shù)據(jù)存在于葉子節(jié)點，即3，5，9，10，13，15，28，29，36...
非葉子節(jié)點只不存儲真實的數(shù)據(jù)，只存儲引擎搜索方向的數(shù)據(jù)項，如17、35并不是真實存于數(shù)據(jù)表中。
【查找過程】
如果要查找數(shù)據(jù)項29，那么首先會把磁盤塊1由磁盤加載到內(nèi)存，此時發(fā)生一次IO，在內(nèi)存中用二分查找確實29在17和35之間，鎖定磁盤塊1的P2指針，內(nèi)存時間因為非常短(相比磁盤的IO)可以忽略不計，通過磁盤塊1的P2指針的磁盤地址把磁盤塊3由磁盤加載到內(nèi)存，發(fā)生第二次IO，29在26和30之間，鎖定磁盤塊3的P2指針，通過指針加載磁盤塊8到內(nèi)存，發(fā)生第三次IO，同時內(nèi)存中做二分查找找到29，結(jié)束查詢，總計三次IO。
Btree索引(或Balanced Tree)，是一種很普遍的數(shù)據(jù)庫索引結(jié)構(gòu)，oracle默認(rèn)的索引類型（本文也主要依據(jù)oracle來講）。其特點是定位高效、利用率高、自我平衡，特別適用于高基數(shù)字段，定位單條或小范圍數(shù)據(jù)非常高效。理論上，使用Btree在億條數(shù)據(jù)與100條數(shù)據(jù)中定位記錄的花銷相同。
數(shù)據(jù)結(jié)構(gòu)利用率高、定位高效
Btree索引的數(shù)據(jù)結(jié)構(gòu)如下：
結(jié)構(gòu)看起來Btree索引與Binary Tree相似，但在細(xì)節(jié)上有所不同，上圖中用不同顏色的標(biāo)示出了Btree索引的幾個主要特點：
樹形結(jié)構(gòu)：由根節(jié)(root)、分支(branches)、葉(leaves)三級節(jié)點組成，其中分支節(jié)點可以有多層。
多分支結(jié)構(gòu)：與binary tree不相同的是，btree索引中單root/branch可以有多個子節(jié)點(超過2個)。
雙向鏈表：整個葉子節(jié)點部分是一個雙向鏈表(后面會描述這個設(shè)計的作用)
單個數(shù)據(jù)塊中包括多條索引記錄
這里先把幾個特點羅列出來，后面會說到各自的作用。
結(jié)構(gòu)上Btree與Binary Tree的區(qū)別，在于binary中每節(jié)點代表一個數(shù)值，而balanced中root和Btree節(jié)點中記錄了多條”值范圍”條目(如:[60-70][70-80])，這些”值范圍”條目分別指向在其范圍內(nèi)的葉子節(jié)點。既root與branch可以有多個分支，而不一定是兩個，對數(shù)據(jù)塊的利用率更高。
在Leaf節(jié)點中，同樣也是存放了多條索引記錄，這些記錄就是具體的索引列值，和與其對應(yīng)的rowid。另外，在葉節(jié)點層上，所有的節(jié)點在組成了一個雙向鏈表。
了解基本結(jié)構(gòu)后，下圖展示定位數(shù)值82的過程：
演算如下：
讀取root節(jié)點，判斷82大于在0-120之間，走左邊分支。
讀取左邊branch節(jié)點，判斷82大于80且小于等于120，走右邊分支。
讀取右邊leaf節(jié)點，在該節(jié)點中找到數(shù)據(jù)82及對應(yīng)的rowid
使用rowid去物理表中讀取記錄數(shù)據(jù)塊(如果是count或者只select rowid，則最后一次讀取不需要)
在整個索引定位過程中，數(shù)據(jù)塊的讀取只有3次。既三次I/O后定位到rowid。
而由于Btree索引對結(jié)構(gòu)的利用率很高，定位高效。當(dāng)1千萬條數(shù)據(jù)時，Btree索引也是三層結(jié)構(gòu)(依稀記得億級數(shù)據(jù)才是3層與4層的分水嶺)。定位記錄仍只需要三次I/O，這便是開頭所說的，100條數(shù)據(jù)和1千萬條數(shù)據(jù)的定位，在btree索引中的花銷是一樣的。
平衡擴張
除了利用率高、定位高效外，Btree的另一個特點是能夠永遠(yuǎn)保持平衡，這與它的擴張方式有關(guān)。(unbalanced和hotspot是兩類問題，之前我一直混在一起)，先描述下Btree索引的擴張方式：
新建一個索引，索引上只會有一個leaf節(jié)點，取名為Node A，不斷的向這個leaf節(jié)點中插入數(shù)據(jù)后，直到這個節(jié)點滿，這個過程如下圖（綠色表示新建/空閑狀態(tài)，紅色表示節(jié)點沒有空余空間）：
當(dāng)Node A滿之后，我們再向表中插入一條記錄，此時索引就需要做拆分處理：會新分配兩個數(shù)據(jù)塊NodeB & C，如果新插入的值，大于當(dāng)前最大值，則將Node A中的值全部插入Node B中，將新插入的值放到Node C中；否則按照5-5比例，將已有數(shù)據(jù)分別插入到NodeB與C中。
無論采用哪種分割方式，之前的leaf節(jié)點A，將變成一個root節(jié)點，保存兩個范圍條目，指向B與C，結(jié)構(gòu)如下圖（按第一種拆分形式）：
當(dāng)Node C滿之后，此時 Node A仍有空余空間存放條目，所以不需要再拆分，而只是新分配一個數(shù)據(jù)塊Node D，將在Node A中創(chuàng)建指定到Node D的條目：
如果當(dāng)根節(jié)點Node A也滿了，則需要進一步拆分：新建Node E&F&G，將Node A中范圍條目拆分到E&F兩個節(jié)點中，并建立E&F到BCD節(jié)點的關(guān)聯(lián)，向Node G插入索引值。此時E&F為branch節(jié)點，G為leaf節(jié)點，A為Root節(jié)點：
在整個擴張過程中，Btree自身總能保持平衡，Leaf節(jié)點的深度能一直保持一致。
實際應(yīng)用中的一些問題
前面說完了Btree索引的結(jié)構(gòu)與擴張邏輯，接下來講一些Btree索引在應(yīng)用中的一些問題：
單一方向擴展引起的索引競爭(Index Contention)
若索引列使用sequence或者timestamp這類只增不減的數(shù)據(jù)類型。這種情況下Btree索引的增長方向總是不變的，不斷的向右邊擴展，因為新插入的值永遠(yuǎn)是最大的。
當(dāng)一個最大值插入到leaf block中后，leaf block要向上傳播，通知上層節(jié)點更新所對應(yīng)的“值范圍”條目中的最大值，因此所有靠右邊的block(從leaf 到branch甚至root)都需要做更新操作，并且可能因為塊寫滿后執(zhí)行塊拆分。
如果并發(fā)插入多個最大值，則最右邊索引數(shù)據(jù)塊的的更新與拆分都會存在爭搶，影響效率。在AWR報告中可以通過檢測enq: TX – index contention事件的時間來評估爭搶的影響。解決此類問題可以使用Reverse Index解決，不過會帶來新的問題。
Index Browning 索引枯萎(不知道該怎么翻譯這個名詞，就是指leaves節(jié)點”死”了，樹枯萎了)
其實oracle針對這個問題有優(yōu)化機制，但優(yōu)化的不徹底，所以還是要拿出來的說。
我們知道當(dāng)表中的數(shù)據(jù)刪除后，索引上對應(yīng)的索引值是不會刪除的，特別是在一性次刪除大批量數(shù)據(jù)后，會造成大量的dead leaf掛到索引樹上?？紤]以下示例，如果表100以上的數(shù)據(jù)會部被刪除了，但這些記錄仍在索引中存在，此時若對該列取max()：
通過與之前相同演算，找到了索引樹上最大的數(shù)據(jù)塊，按照記錄最大的值應(yīng)該在這里，但發(fā)現(xiàn)這數(shù)據(jù)塊里的數(shù)據(jù)已經(jīng)被清空了，與是利用Btree索引的另一個特點：leaves節(jié)點是一個雙向列表，若數(shù)據(jù)沒有找到就去臨近的一個數(shù)據(jù)塊中看看，在這個數(shù)據(jù)塊中發(fā)現(xiàn)了最大值99。
在計算最大值的過程中，這次的定位多加載了一個數(shù)據(jù)塊，再極端的情況下，大批量的數(shù)據(jù)被刪除，就會造成大量訪問這些dead leaves。
針對這個問題的一般解決辦法是重建索引，但記住! 重建索引并不是最優(yōu)方案，詳細(xì)原因可以看看這。使用coalesce語句來整理這些dead leaves到freelist中，就可以避免這些問題。理論上oracle中這步操作是可以自動完成的，但在實際中一次性大量刪除數(shù)據(jù)后，oracle在短時間內(nèi)是反應(yīng)不過來的。
Hash索引，了解
full-text全文索引，F(xiàn)ull-text索引就是我們常說的全文索引，他的存儲結(jié)構(gòu)也是b-tree。主要是為了解決在我們需要用like查詢的低效問題。只能解決’xxx%’的like查詢。如：字段數(shù)據(jù)為ABCDE，索引建立為- A、AB、ABC、ABCD、ABCDE五個。
R-Tree索引，了解
3.6 哪些情況需要創(chuàng)建索引
1.主鍵自動建立唯一索引
2.頻繁作為查詢的條件的字段應(yīng)該創(chuàng)建索引
3.查詢中與其他表關(guān)聯(lián)的字段，外鍵關(guān)系建立索引
4.頻繁更新的字段不適合創(chuàng)建索引
因為每次更新不單單是更新了記錄還會更新索引，加重IO負(fù)擔(dān)
5.Where條件里用不到的字段不創(chuàng)建索引
6.單間/組合索引的選擇問題，who？（在高并發(fā)下傾向創(chuàng)建組合索引）
7.查詢中排序的字段，排序字段若通過索引去訪問將大大提高排序的速度
8.查詢中統(tǒng)計或者分組字段
建立索引，一般按照select的where條件來建立，比如： select的條件是where f1 and f2，那么如果我們在字段f1或字段f2上建立索引是沒有用的，只有在字段f1和f2上同時建立索引才有用等。
3.7 哪些情況不要創(chuàng)建索引
1.表記錄太少
2.經(jīng)常增刪改的表,這是因為，修改性能和檢索性能是互相矛盾的。當(dāng)增加索引時，會提高檢索性能，但是會降低修改性能。當(dāng)減少索引時，會提高修改性能，降低檢索性能。因此，當(dāng)修改性能遠(yuǎn)遠(yuǎn)大于檢索性能時，不應(yīng)該創(chuàng)建索引。
3.數(shù)據(jù)重復(fù)且分布平均的表字段，因此應(yīng)該只為經(jīng)常查詢和經(jīng)常排序的數(shù)據(jù)列建立索引。 注意，如果某個數(shù)據(jù)列包含許多重復(fù)的內(nèi)容，為它建立索引就沒有太大的實際效果。
假如一個表有10萬行記錄，有一個字段A只胡T和F兩種值，且每個值的分布概率大約為50%，那么對這種表A字段建索引一般不會提高數(shù)據(jù)庫的查詢速度。
索引的選擇性是指索引列中不同值的數(shù)據(jù)與不用跟記錄數(shù)的比。如果一個表中有2000條記錄，表索引列有1980個不同的值，那么這個索引的選擇性是1980/2000=0.99。一個索引的選擇性越接近于1，這個索引的效率就越高。
- 對于那些定義為text, image和bit數(shù)據(jù)類型的列不應(yīng)該增加索引。這是因為，這些列的數(shù)據(jù)量要么相當(dāng)大，要么取值很少。
3.8 索引管理
普通索引
這是最基本的索引，它沒有任何限制MyIASM中默認(rèn)的BTREE類型的索引，也是我們大多數(shù)情況下用到的索引。
創(chuàng)建索引
CREATE INDEX index_name ON table_name (column(length))
ALTER TABLE table_name ADD INDEX index_name (column(length))
CREATE TABLE table_name (id int not null auto_increment,title varchar(30) ,PRIMARY KEY(id) , INDEX index_name (title(5)))
查看索引
SHOW INDEX FROM [table_name]
SHOW KEYS FROM [table_name] # 只在MySQL中可以使用keys關(guān)鍵字。
刪除索引
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
唯一索引
與普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值（注意和主鍵不同）。如果是組合索引，則列值的組合必須唯一，創(chuàng)建方法和普通索引類似
創(chuàng)建索引
CREATE UNIQUE INDEX index_name ON table_name (column(length))
ALTER TABLE table_name ADD UNIQUE index_name (column(length))
CREATE TABLE table_name (id int not null auto_increment,title varchar(30) ,PRIMARY KEY(id) , UNIQUE index_name (title(length)))
全文索引（FULLTEXT）
MySQL從3.23.23版開始支持全文索引和全文檢索，F(xiàn)ULLTEXT索引僅可用于 MyISAM 表；他們可以從CHAR、VARCHAR或TEXT列中作為CREATE TABLE語句的一部分被創(chuàng)建，或是隨后使用ALTER TABLE 或CREATE INDEX被添加。
對于較大的數(shù)據(jù)集，將你的資料輸入一個沒有FULLTEXT索引的表中，然后創(chuàng)建索引，其速度比把資料輸入現(xiàn)有FULLTEXT索引的速度更為快。不過切記對于大容量的數(shù)據(jù)表，生成全文索引是一個非常消耗時間非常消耗硬盤空間的做法。
創(chuàng)建索引
CREATE FULLTEXT INDEX index_name ON table_name(column(length))
ALTER TABLE table_name ADD FULLTEXT index_name( column)
CREATE TABLE table_name (id int not null auto_increment,title varchar(30) ,PRIMARY KEY(id) , FULLTEXT index_name (title))
組合索引（最左前綴）
CREATE TABLE article(id int not null, title varchar(255), time date);
平時用的SQL查詢語句一般都有比較多的限制條件，所以為了進一步榨取MySQL的效率，就要考慮建立組合索引。例如上表中針對title和time建立一個組合索引：ALTER TABLE article ADD INDEX index_title_time (title(50),time(10))。建立這樣的組合索引，其實是相當(dāng)于分別建立了下面兩組組合索引：
–title,time
–title
為什么沒有time這樣的組合索引呢？這是因為MySQL組合索引“最左前綴”的結(jié)果。簡單的理解就是只從最左面的開始組合。并不是只要包含這兩列的查詢都會用到該組合索引，如下面的幾個SQL所示：
1，使用到上面的索引
SELECT * FROM article WHERE title='測試' AND time=1234567890;
SELECT * FROM article WHERE title='測試';
2，不使用上面的索引
SELECT * FROM article WHERE time=1234567890;
創(chuàng)建索引
CREATE INDEX index_name ON table_name (column_list)
4. 性能分析
4.1 MySQL Query Optimizer
- Mysql中有專門負(fù)責(zé)優(yōu)化的SELECT語句的優(yōu)化器模塊，主要功能：通過計算分析系統(tǒng)中收集到的統(tǒng)計信息，為客戶端請求的Query提供他認(rèn)為最優(yōu)的執(zhí)行計劃（他認(rèn)為最優(yōu)的數(shù)據(jù)檢索方式，但不見得是DBA認(rèn)為最優(yōu)的，這部分最耗費時間）
- 當(dāng)客戶端向MYSQL請求一條Query，命令解析器模塊完成請求分類，區(qū)別出是SELECT并轉(zhuǎn)發(fā)給Mysql Query Optimizer，MySQL Query Optimizer 首先會對整條Query進行優(yōu)化，處理掉一些常量表達式的預(yù)算，直接換算成常量值。并對Query中的查詢條件進行簡化和轉(zhuǎn)換，如去掉一些無用或顯而易見的條件、結(jié)構(gòu)調(diào)等。然后分析Query中的Hint信息（如果有），看顯示Hint信息是否可以完全確定該Query的執(zhí)行計劃。如果沒有Hint或Hint信息還不足以完全確定執(zhí)行計劃，則會讀取所涉及對象的統(tǒng)計信息，根據(jù)Query進行寫相應(yīng)的計算分析，然后再得出最后的執(zhí)行計劃。
4.2 MySQL常見瓶頸
CPU:CPU在飽和的時候一般發(fā)生在數(shù)據(jù)裝入在內(nèi)存或從磁盤上讀取數(shù)據(jù)時候
IO:磁盤I/O瓶頸發(fā)生在裝入數(shù)據(jù)遠(yuǎn)大于內(nèi)存容量時
服務(wù)器硬件的性能瓶頸：top,free,iostat和vmstat來查看系統(tǒng)的性能狀態(tài)
4.3 Explain
是什么（查看執(zhí)行計劃）
使用EXPLAIN關(guān)鍵字可以模擬優(yōu)化器執(zhí)行SQL語句，從而知道MySQL是 如何處理你的SQL語句的。分析你的查詢語句或是結(jié)構(gòu)的性能瓶頸
官網(wǎng)介紹
能干嘛
表的讀取順序
數(shù)據(jù)讀取操作的操作類型
哪些索引可以使用
哪些索引被實際使用
表之間的引用
每張表有多少行被優(yōu)化器查詢
怎么玩
Explain+SQL語句
執(zhí)行計劃包含的信息
+----+-------------+-------+------+---------------+------+---------+------+------+-------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------
各個字段解釋
id
select查詢的序列號，包含一組數(shù)字，表示查詢中執(zhí)行select子句或操作表的順序
三種情況
id相同，執(zhí)行順序由上至下
id不同，如果是子查詢，id的序號會遞增，id值越大優(yōu)先級越高，越先被執(zhí)行
id相同不同，同時存在
select_type:查詢的類型，主要用于區(qū)別 普通查詢、聯(lián)合查詢、子查詢等的復(fù)雜查詢
| 類型名稱 | 描述 |
|---|---|
| SIMPLE | 簡單的select查詢，查詢中不包含子查詢或者UNION |
| PRIMARY | 查詢中若包含任何復(fù)雜的子部分，最外層查詢則被標(biāo)記為 |
| SUBQUERY | 在SELECT或者WHERE列表中包含了子查詢 |
| DERIVED | 在FROM列表中包含的子查詢被標(biāo)記為DERIVED（衍生） MySQL會遞歸執(zhí)行這些子查詢，把結(jié)果放在臨時表里。 |
| UNION | 若第二個SELECT出現(xiàn)在UNION之后，則被標(biāo)記為UNION; 若UNION包含在FROM子句的子查詢中，外層SELECT將被標(biāo)記為：DERIVED |
| UNION RESULT | 從UNION表獲取結(jié)果的SELECT |
table:顯示這一行的數(shù)據(jù)是關(guān)于哪張表的
type
ALL index range ref eq_ref const,system NULL
訪問類型排列
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
一般來說，得保證查詢至少達到range級別，最好能達到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
FullTable Scan,將遍歷全表以找到匹配的行
備注：
一般來說，得保證查詢只是達到range級別，最好達到ref
possible_keys
顯示可能應(yīng)用在這張表中的索引,一個或多個。 查詢涉及的字段上若存在索引，則該索引將被列出，但不一定被查詢實際使用
key
實際使用的索引。如果為null則沒有使用索引
查詢中若使用了覆蓋索引，則索引和查詢的select字段重疊
參閱: 3.USING index
key_len
表示索引中使用的字節(jié)數(shù)，可通過該列計算查詢中使用的索引的長度。在不損失精確性的情況下，長度越短越好
key_len顯示的值為索引最大可能長度，并非實際使用長度，即key_len是根據(jù)表定義計算而得，不是通過表內(nèi)檢索出的
ref
顯示索引那一列被使用了，如果可能的話，是一個常數(shù)。那些列或常量被用于查找索引列上的值
rows
根據(jù)表統(tǒng)計信息及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數(shù)
越少越好
Extra
包含不適合在其他列中顯示但十分重要的額外信息
1.Using filesort
說明mysql會對數(shù)據(jù)使用一個外部的索引排序，而不是按照表內(nèi)的索引順序進行讀取。 MySQL中無法利用索引完成排序操作成為“文件排序”
2.Using temporary
使用了臨時表保存中間結(jié)果，MySQL在對查詢結(jié)果排序時使用臨時表。常見于排序order by 和分組查詢 group by
3.USING index
表示相應(yīng)的select操作中使用了覆蓋索引（Coveing Index）,避免訪問了表的數(shù)據(jù)行，效率不錯！ 如果同時出現(xiàn)using where，表明索引被用來執(zhí)行索引鍵值的查找； 如果沒有同時出現(xiàn)using where，表面索引用來讀取數(shù)據(jù)而非執(zhí)行查找動作。
覆蓋索引（Covering Index）
- 覆蓋索引（Convering Index），一說為索引覆蓋。
- 理解方式一：就是select的數(shù)據(jù)列只用從索引中就能夠取得，不必讀取數(shù)據(jù)行，Mysql可以利用索引返回select列表中的字段，而不必根據(jù)索引再次讀取數(shù)據(jù)文件，換句話說查詢表要被所建的索引覆蓋。
- 理解方式二：索引是高效找到行的一個方法，但是一般數(shù)據(jù)庫也能使用索引找到一個列的數(shù)據(jù)，因此它不必讀取整個行。畢竟索引葉子節(jié)點存儲了它的索引的數(shù)據(jù);當(dāng)能通過讀取索引就可以得到想要的數(shù)據(jù)，那就不需要讀取行了。一個索引包含了(或覆蓋了)滿足查詢結(jié)果的數(shù)據(jù)就叫覆蓋索引。
- 注意：如果要使用覆蓋索引，一定要注意select列表中只取出需要的列，不能select *,因為如果將所有字段一起做索引會導(dǎo)致索引文件過大，查詢性能下降。
4.Using where
表面使用了where過濾
5.using join buffer
使用了連接緩存
6.impossible where
where子句的值總是false，不能用來獲取任何元組
7.select tables optimized away
在沒有GROUPBY子句的情況下，基于索引優(yōu)化MIN/MAX操作或者 對于MyISAM存儲引擎優(yōu)化COUNT(*)操作，不必等到執(zhí)行階段再進行計算， 查詢執(zhí)行計劃生成的階段即完成優(yōu)化。
8.distinct
優(yōu)化distinct，在找到第一匹配的元組后即停止找同樣值的工作
熱身Case
5. 索引優(yōu)化
索引不會包含有NULL值的列
只要列中包含有NULL值都將不會被包含在索引中，組合索引中只要有一列含有NULL值，那么這一列對于此組合索引就是無效的。所以我們在數(shù)據(jù)庫設(shè)計時不要讓字段的默認(rèn)值為NULL。create table table_name(c1 varchar(32) default ‘0’)
使用短索引
對串列進行索引，如果可能應(yīng)該指定一個前綴長度。例如，如果有一個CHAR(255)的列，如果在前10個或20個字符內(nèi)，多數(shù)值是惟一的，那么就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節(jié)省磁盤空間和I/O操作。
CREATE INDEX index_name ON table_name (column(length))
索引列排序
MySQL查詢只使用一個索引，因此如果where子句中已經(jīng)使用了索引的話，那么order by中的列是不會使用索引的。因此數(shù)據(jù)庫默認(rèn)排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列創(chuàng)建復(fù)合索引。
like語句操作
一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like “%aaa%” 不會使用索引，而like “aaa%”可以使用索引。
不要在列上進行運算
例如：select * from users where YEAR(adddate)<2007，將在每個行上進行運算，這將導(dǎo)致索引失效而進行全表掃描，因此我們可以改成：select * from users where adddate<’2007-01-01′
最后總結(jié)一下，MySQL只對以下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些時候的like(不以通配符%或_開頭的情形)。而理論上每張表里面最多可創(chuàng)建16個索引，不過除非是數(shù)據(jù)量真的很多，否則過多的使用索引也不是那么好玩的。
建議：一個表的索引數(shù)最好不要超過6個，若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有必要。
5.1 索引分析
單表
建表SQL
案例
兩表
建表SQL
案例
三表
建表SQL
案例
5.2 索引失效（應(yīng)該避免）
建表SQL
案例（索引失效）
1.全值匹配我最愛
2.最佳左前綴法則
如果索引了多例，要遵守最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過索引中的列。
3.不在索引列上做任何操作（計算、函數(shù)、（自動or手動）類型轉(zhuǎn)換），會導(dǎo)致索引失效而轉(zhuǎn)向全表掃描
4.存儲引擎不能使用索引中范圍條件右邊的列
5.盡量使用覆蓋索引（只訪問索引的查詢（索引列和查詢列一致）），減少select*
6.mysql在使用不等于（！=或者<>）的時候無法使用索引會導(dǎo)致全表掃描
7.is null,is not null 也無法使用索引
8.like以通配符開頭（'$abc...'）mysql索引失效會變成全表掃描操作
問題：解決like'%字符串%'索引不被使用的方法？？
1、可以使用主鍵索引
2、使用覆蓋索引，查詢字段必須是建立覆蓋索引字段
3、當(dāng)覆蓋索引指向的字段是varchar(380)及380以上的字段時，覆蓋索引會失效！
9.字符串不加單引號索引失效
10.少用or,用它連接時會索引失效
11.小總結(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不可以 |
| where a=3 and b>4 and c=5 | 使用到a和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 |
like KK%相當(dāng)于=常量 %KK和%KK% 相當(dāng)于范圍
【優(yōu)化總結(jié)口訣】
會值匹配我最愛，最左前綴要遵守；
帶頭大哥不能死，中間兄弟不能斷；
索引列上少計算，范圍之后會失效；
LIKE百分寫最右，覆蓋索引不寫星；
不等空值還有or，索引失敗要少用；
VAR引號不可丟，SQL高級也不難！
定值、范圍還是排序，一般order by是給個范圍
group by 基本上都需要進行排序，會有臨時表產(chǎn)生
5.3 一般性建議
對于單鍵索引，盡量選擇針對當(dāng)前query過濾性更好的索引
在選擇組合索引的時候，當(dāng)前Query中過濾性最好的字段在索引字段順序中，位置越靠前越好。
在選擇組合索引的時候，盡量選擇可以能包含當(dāng)前query中的where子句中更多字段的索引
盡可能通過分析統(tǒng)計信息和調(diào)整query的寫法來達到選擇合適索引的目的
避免全表掃描
對查詢進行優(yōu)化，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引。
避免判斷null值
應(yīng)盡量避免在 where 子句中對字段進行 null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進行全表掃描，如：
select id from t where num is null
可以在num上設(shè)置默認(rèn)值0，確保表中num列沒有null值，然后這樣查詢：
select id from t where num=0
避免不等值判斷
應(yīng)盡量避免在 where 子句中使用!=或<>操作符，否則引擎將放棄使用索引而進行全表掃描。
避免使用or邏輯
應(yīng)盡量避免在 where 子句中使用 or 來連接條件，否則將導(dǎo)致引擎放棄使用索引而進行全表掃描，如：
select id from t where num=10 or num=20
可以這樣查詢：
select id from t where num=10
union all
select id from t where num=20
慎用in和not in邏輯
in 和 not in 也要慎用，否則會導(dǎo)致全表掃描，如：
select id from t1 where num in(select id from t2 where id > 10)
此時外層查詢會全表掃描，不使用索引。可以修改為：
select id from t1,(select id from t1 where id > 10)t2 where t1.id = t2.id
此時索引被使用，可以明顯提升查詢效率。
注意模糊查詢
下面的查詢也將導(dǎo)致全表掃描：
select id from t where name like '%abc%'
模糊查詢?nèi)绻潜匾獥l件時，可以使用select id from t where name like 'abc%'來實現(xiàn)模糊查詢，此時索引將被使用。如果頭匹配是必要邏輯，建議使用全文搜索引擎（Elastic search、Lucene、Solr等）。
避免查詢條件中字段計算
應(yīng)盡量避免在 where 子句中對字段進行表達式操作，這將導(dǎo)致引擎放棄使用索引而進行全表掃描。如：
select id from t where num/2=100
應(yīng)改為:
select id from t where num=100*2
避免查詢條件中對字段進行函數(shù)操作
應(yīng)盡量避免在where子句中對字段進行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進行全表掃描。如：
select id from t where substring(name,1,3)='abc'--name以abc開頭的id
應(yīng)改為:
select id from t where name like 'abc%'
WHERE子句“=”左邊注意點
不要在 where 子句中的“=”左邊進行函數(shù)、算術(shù)運算或其他表達式運算，否則系統(tǒng)將可能無法正確使用索引。
組合索引使用
在使用索引字段作為條件時，如果該索引是復(fù)合索引，那么必須使用到該索引中的第一個字段作為條件時才能保證系統(tǒng)使用該索引，否則該索引將不會被使用，并且應(yīng)盡可能的讓字段順序與索引順序相一致。
不要定義無異議的查詢
不要寫一些沒有意義的查詢，如需要生成一個空表結(jié)構(gòu)：
select col1,col2 into #t from t where 1=0
這類代碼不會返回任何結(jié)果集，但是會消耗系統(tǒng)資源的，應(yīng)改成這樣：
create table #t(...)
exists
很多時候用 exists 代替 in 是一個好的選擇：
select num from a where num in(select num from b)
用下面的語句替換：
select num from a where exists(select 1 from b where num=a.num)
索引也可能失效
并不是所有索引對查詢都有效，SQL是根據(jù)表中數(shù)據(jù)來進行查詢優(yōu)化的，當(dāng)索引列有大量數(shù)據(jù)重復(fù)時，SQL查詢可能不會去利用索引，如一表中有字段sex，male、female幾乎各一半，那么即使在sex上建了索引也對查詢效率起不了作用。
表格字段類型選擇
盡量使用數(shù)字型字段，若只含數(shù)值信息的字段盡量不要設(shè)計為字符型，這會降低查詢和連接的性能，并會增加存儲開銷。這是因為引擎在處理查詢和連接時會逐個比較字符串中每一個字符，而對于數(shù)字型而言只需要比較一次就夠了。
盡可能的使用 varchar 代替 char ，因為首先可變長度字段存儲空間小，可以節(jié)省存儲空間，其次對于查詢來說，在一個相對較小的字段內(nèi)搜索效率顯然要高些。
查詢語法中的字段
任何地方都不要使用 select * from t ，用具體的字段列表代替“*”，不要返回用不到的任何字段。
索引無關(guān)優(yōu)化
不使用*、盡量不使用union，union all等關(guān)鍵字、盡量不使用or關(guān)鍵字、盡量使用等值判斷。
表連接建議不超過5個。如果超過5個，則考慮表格的設(shè)計。（互聯(lián)網(wǎng)應(yīng)用中）
表連接方式使用外聯(lián)優(yōu)于內(nèi)聯(lián)。
外連接有基礎(chǔ)數(shù)據(jù)存在。如：A left join B,基礎(chǔ)數(shù)據(jù)是A。
A inner join B，沒有基礎(chǔ)數(shù)據(jù)的，先使用笛卡爾積完成全連接，在根據(jù)連接條件得到內(nèi)連接結(jié)果集。
大數(shù)據(jù)量級的表格做分頁查詢時，如果頁碼數(shù)量過大，則使用子查詢配合完成分頁邏輯。
Select * from table limit 1000000, 10
Select * from table where id in (select pk from table limit 100000, 10)
