一 查詢sql的執(zhí)行過程
MySQL 可以分為 Server 層和存儲引擎層兩部分。
- Server 層包括連接器鲸阔、查詢緩存（SQL_CACHE）偷霉、分析器、優(yōu)化器褐筛、執(zhí)行器等类少，以及所有的內置函數(shù)（如日期、時間渔扎、數(shù)學和加密函數(shù)等）硫狞，所有跨存儲引擎的功能，比如存儲過程晃痴、觸發(fā)器残吩、視圖等。
- 存儲引擎層負責數(shù)據(jù)的存儲和提取愧旦。InnoDB世剖、MyISAM、Memory(內存) 等多個存儲引擎笤虫。InnoDB 在5.5.5后成為默認存儲引擎
緩存
show variables可以查看我們mysql的許多配置旁瘫，我們查一些需要的參數(shù)可以使用類似于模糊匹配的方式如下：
show variables like '%cache%';
我們可以指定查詢緩存的開關
- 臨時關閉mysql查詢緩存
set global query_cache_size=0 set global query_cache_type=0 - 永久的修改配置文件my.cnf ,添加下面的配置即可
query_cache_type=0 quey_cache_size=0 - 按需使用query_cache_type DEMAND
mysql> select SQL_CACHE（sql_no_cache） * from T where ID=10祖凫；
MySQL 8.0 版本直接將查詢緩存的整塊功能刪掉了，也就是說 8.0 開始徹底沒有這個功能了酬凳。
語法分析舉例：
根據(jù)詞法分析的結果惠况，語法分析器會根據(jù)語法規(guī)則，判斷你輸入的這個 SQL 語句是否滿足 MySQL 語法宁仔，如果我們輸入的不滿足就會報錯稠屠。如下我們selecr少輸入一個s
這里著重說一下優(yōu)化器
經(jīng)過了分析器，MySQL 就知道你要做什么了翎苫。在開始執(zhí)行之前权埠，還要先經(jīng)過優(yōu)化器的處理。
優(yōu)化器負責選擇執(zhí)行計劃煎谍，比如在表里面有多個索引的時候攘蔽，決定使用哪個索引；或者在一個語句有多表關聯(lián)（join）的時候呐粘，決定各個表的連接順序满俗。
舉個例子你執(zhí)行下面這樣的語句，這個語句是執(zhí)行兩個表的 join：
select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;
- 既可以先從表 t1 里面取出 c=10 的記錄的 ID 值作岖，再根據(jù) ID 值關聯(lián)到表 t2唆垃，再判斷 t2 里面 d 的值是否等于 20。
- 也可以先從表 t2 里面取出 d=20 的記錄的 ID 值痘儡，再根據(jù) ID 值關聯(lián)到 t1辕万，再判斷 t1 里面 c 的值是否等于 10。
這兩種執(zhí)行方法的邏輯結果是一樣的谤辜，但是執(zhí)行的效率會有不同蓄坏，而優(yōu)化器的作用就是決定選擇使用哪一個方案。優(yōu)化器階段完成后丑念，這個語句的執(zhí)行方案就確定下來了涡戳，然后進入執(zhí)行器階段。如果你還有一些疑問脯倚，比如優(yōu)化器是怎么選擇索引的渔彰，有沒有可能選擇錯等等
二 索引
對于查詢效率影響至關重要的索引，想必大家都知道這個詞推正。但是可能還不是非常了解恍涂。
索引的出現(xiàn)其實就是為了提高數(shù)據(jù)查詢的效率，就像書的目錄一樣植榕。
索引的常見模型：哈希表再沧、有序數(shù)組和搜索樹。
-
哈希表是一種以鍵 - 值（key-value）存儲數(shù)據(jù)的結構尊残，我們只要輸入待查找的鍵即 key炒瘸，就可以找到其對應的值即 Value淤堵。哈希的思路很簡單，把值放在數(shù)組里顷扩，用一個哈希函數(shù)把 key 換算成一個確定的位置拐邪，然后把 value 放在數(shù)組的這個位置。不可避免地隘截，多個 key 值經(jīng)過哈希函數(shù)的換算扎阶，會出現(xiàn)同一個值的情況。處理這種情況的一種方法是婶芭，拉出一個鏈表东臀。需要注意的是，圖中四個 ID_card_n 的值并不是遞增的犀农，這樣做的好處是增加新的 User 時速度會很快啡邑，只需要往后追加。但缺點是井赌，因為不是有序的，所以哈希索引做區(qū)間查詢的速度是很慢的贵扰。你可以設想下仇穗，如果你現(xiàn)在要找身份證號在[ID_card_X, ID_card_Y]這個區(qū)間的所有用戶，就必須全部掃描一遍了戚绕。所以纹坐，哈希表這種結構適用于只有等值查詢的場景，比如 Memcached 及其他一些 NoSQL 引擎舞丛。
-
-
有序數(shù)組在等值查詢和范圍查詢場景中的性能就都非常優(yōu)秀耘子。還是上面這個根據(jù)身份證號查名字的例子，如果我們使用有序數(shù)組來實現(xiàn)的話球切，示意圖如下所示：我們假設身份證號沒有重復谷誓，這個數(shù)組就是按照身份證號遞增的順序保存的。這時候如果你要查 ID_card_n2 對應的名字吨凑，用二分法就可以快速得到捍歪。
如果僅僅看查詢效率，有序數(shù)組就是最好的數(shù)據(jù)結構了鸵钝。但是糙臼，在需要更新數(shù)據(jù)的時候就麻煩了，你往中間插入一個記錄就必須得挪動后面所有的記錄恩商，成本太高变逃。
-
-
平衡二叉樹，（普通二叉搜索樹容易鏈化就不說了）
-
缺點：
太高了
數(shù)據(jù)處的（高）深度決定著他的IO操作次數(shù)怠堪，IO操作耗時大
太小了
每一個磁盤塊（節(jié)點/頁）保存的數(shù)據(jù)量太小了
沒有很好的利用操作磁盤IO的數(shù)據(jù)交換特性(我們可以一次讀4K數(shù)據(jù)的,現(xiàn)在只讀了1個結點進去,而且這個結點只存了一個數(shù)據(jù),非常浪費操作系統(tǒng)資源)
也沒有利用好磁盤IO的預讀能力（空間局部性原理）(預讀揽乱；每一次IO時名眉，不僅僅把當前磁盤地址的數(shù)據(jù)加載到內存，同時也把相鄰數(shù)據(jù)也加載到內存緩沖區(qū)中锤窑。)
從而帶來頻繁的IO操作
操作系統(tǒng)方面具體細節(jié)可以百度,百度百科比我說的好...
- 4.
B+樹
B樹和B+樹類似璧针，區(qū)別于其他樹的最大區(qū)別是:B樹和B+樹,每個結點中不再只有左右兩個孩子了,而是我們可以定義為任意個孩子,其中m個孩子就是m階樹。
4.1 B-Tree 
m階B樹定義
- 根節(jié)點至少包括兩個孩子
- 樹中每個節(jié)點最多含有m個孩子（m>=2）(m個孩子就稱之為m階樹)
- 關鍵字個數(shù)最多為m-1個(根據(jù)孩子結點來的,比孩子結點少一個)
- 除根節(jié)點和葉節(jié)點外渊啰，其他每個節(jié)點至少有ceil（m/2）個孩子
-
所有葉子節(jié)點都位于同一層
4.2為什么用B-樹可以很矮,很胖探橱，速度很快呢?
這是因為,我們mysql一般把一個結點數(shù)據(jù)定義為一頁,一頁數(shù)據(jù)是16K=16*1024byte,如果我們用的平衡二叉樹,假如定義的索引為int型id,一個id 4byte,加上其他數(shù)據(jù)一個id索引可能頁就8byte左右,這才占了一次io的多少?這何等的浪費資源??而我們用B樹后,B樹是多路平衡查找樹,我們可以定義 16*1024/8 大概兩千多路(索引或者孩子結點),這樣效率提升了何止一點半點呢!
這其實也就是為啥我們一般慎用uuid做主鍵,因為它長度太長了,如果用uuid,太占用空間,我們索引的路數(shù)會變少,層數(shù)變多,效率會有所下降.
4.3 B+Tree(Mysql使用的索引數(shù)據(jù)結構)
B+樹是B樹的變體，其結構定義基本與B樹相同绘证，除了：
- 非葉子節(jié)點的子樹指針與關鍵字
- B+非葉節(jié)點不保存數(shù)據(jù)相關信息隧膏，只保存關鍵字和子節(jié)點的引用所有搜索均在葉子結點結束
- 所有葉子節(jié)點均有一個鏈指針指向下一個葉子結點,相鄰節(jié)點具有順序引用的關系(便于范圍查找)
- B+節(jié)點關鍵字搜索采用閉合區(qū)間,就算我們中途知道到了相等的關鍵字也要一直到葉子 結點
B+Tree結構圖
![]()
4.3.0 B+Tree結論
B+Tree更適合用來做存儲索引
B+樹的磁盤讀寫代價更低
B+Tree內部結構并沒有指向關鍵字具體信息的指針,關鍵字不存放數(shù)據(jù),只存放索引信息,因此內部結點相對B+Tree更小; 如果把所有內部結點的關鍵字存放同一盤塊中,這個磁盤塊所能容納的關鍵字也更多,一次性讀入內存中的所需要查找的關鍵字也就越多,相對來說IO讀寫次數(shù)也就降低了B+樹的查詢效率更加穩(wěn)定
由于內部結點并不是最終指向文件內容的結點而只是葉子結點中關鍵字的索引,所以任何關鍵字的查找必須走一條從根節(jié)點到葉子結點的路,所有查詢的長度相同,導致每個數(shù)據(jù)的查詢效率也幾乎是相同的B+樹天然有序,更有利于對數(shù)據(jù)庫的掃描
B-Tree樹在提高IO性能時候,并沒有解決元素遍歷效率底下問題.而B+Tree只需要遍歷葉子結點就可以解決對全部關鍵字信息的掃描,做范圍查詢相當方便(所有葉子節(jié)點均有一個鏈指針指向下一個葉子結點)
5 如何使我們查詢效率更高呢？
5.1 避免回表
5.1.1什么是回表查詢嚷那？
這先要從InnoDB的索引實現(xiàn)說起胞枕，InnoDB有兩大類索引：
- 聚集索引(clustered index)
- 普通索引(secondary index)
InnoDB聚集索引和普通索引有什么差異？
聚集索引的葉子節(jié)點存儲行記錄魏宽，因此腐泻， InnoDB必須要有，且只有一個聚集索引
普通索引的葉子節(jié)點存儲主鍵值队询。
表里哪個字段代表聚集索引呢派桩？
（1）如果表定義了PK，則PK就是聚集索引蚌斩；
（2）如果表沒有定義PK铆惑，則第一個not NULL unique列是聚集索引；
（3）否則送膳，InnoDB會創(chuàng)建一個隱藏的row-id作為聚集索引员魏；
舉個栗子具體說明一下索引的建立結構，不妨設有表：
t(id PK, name KEY, sex, flag);
id是主鍵（聚集索引）叠聋，name是普通索引撕阎。
表中有四條記錄：
1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B
如果我們要根據(jù)普通索引name查一條數(shù)據(jù)，如
select * from t where name='lisi';晒奕，那么過程應該是咋樣的呢闻书？
（2）在通過聚集索引定位到行記錄脑慧；
這就是所謂的回表查詢魄眉，先定位主鍵值，再定位行記錄闷袒，它的性能較掃一遍索引樹更低坑律。因此我們平常應該多用主鍵進行sql操作。
解惑：
用不用的到索引其實不一定是用就是用不到啊，比如我們select * from user where id=3如果id是主鍵我們就可以用用到索引晃择，怎么證明呢? 可以用explain查看執(zhí)行計劃*冀值，直接在sql語句之前加explain就行，如EXPLAIN SELECT * FROM user WHERE id =3
比如：
下面我們看下執(zhí)行計劃輸出參數(shù)的釋義滑绒。
5.2 explain
expain出來的信息概要描述：
msql執(zhí)行計劃輸出參數(shù)詳細解析中文文檔地址https://www.docs4dev.com/docs/zh/mysql/5.7/reference/explain-output.html闷堡，其他技術中文文檔https://www.docs4dev.com/docs/zh#
type結果值從好到壞依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
system：查詢對象表只有一行數(shù)據(jù)，且只能用于MYISAM和Memary引擎的表疑故，這是最好的情況
const：基于主鍵或唯一索引查詢缚窿，最多返回一條結果
eq_ref：表連接時基于主鍵或非null的唯一索引完成掃描
ref：基于普通索引的等值查詢，最多返回一條結果
fulltext：全文檢索
ref_or_null：表連接類型時ref焰扳，但進行掃描的索引列中可能包含null值
index_merge：利用多個索引
index_subquery：子查詢中使用唯一索引
range：利用索引進行范圍搜索
index：全索引掃描
all：全表掃描
一般來說，得保證查詢至少達到range級別误续，最好能達到ref吨悍，type出現(xiàn)index和all時，表示走的是全表掃描沒有走索引蹋嵌，效率低下育瓜，這時需要對sql進行調優(yōu)。
這里有點疑問,我們在查詢列表接口通常要根據(jù)新舊進行排序返回栽烂，比如快訊列表根據(jù)更新時間排序每次分頁取20條躏仇，如果我們用自增id代替更新時間,是不是效率會高許多呢?看下面圖1，圖一是咱們一個有自增主鍵的表,我們真實使用explain來分析一下兩種查詢的執(zhí)行計劃圖1
6索引失效的情況,為啥會失效?
其實所有索引失效的原因,我們明白索引的結構就已經(jīng)知道了,即本質來說,索引失效原因就是 搜索條件模糊,搜索范圍廣,搜索順序不能按照索引走
1.前導模糊查詢不能利用索引(like '%XX'或者like '%XX%')
比如我們上面的name的值為表'ls','sj','ww','zs' ,如果where name like '%s'條件腺办，由于前面是
模糊的焰手，我們不知道從哪里查,所以不能利用索引的順序，必須一個個去找,怀喉，看是否滿足條件书妻。這樣會導致全索引掃描或者全表掃
描。
如果是這樣的條件where name like 'l% '躬拢，就可以查找name中l(wèi)開頭的name的位置躲履，當碰到s開頭的
數(shù)據(jù)時见间，就可以停止查找了，因為后面的數(shù)據(jù)一定不滿足要求工猜。這樣就可以利用索引了
2.應盡量避免在 where 子句中使用!=或<>操作符米诉，否則將引擎放棄使用索引而進行全表掃描。
3.如果是組合索引的話篷帅，如果不按照索引的順序進行查找史侣，比如直接使用第三個位置上的索引而忽略第一二個位置上的索引時，則會進行全表查詢
索引為c1,c2,c3,c4
如果我們直接where x=c3則是全表查詢犹褒，無法使用該索引的抵窒，因為c3字段使用索引的前提是c1，c2兩字段均使用了索引叠骑。建議大家下來看下最左匹配原則
4.條件中使用or
我們不要感覺用or就一定不走索引,許多情況索引沒有必定用到或者不用到,這跟我們條件信息有比較大的關系,比如如下圖2所示的一個or,咱們實時說話
5. 應盡量避免在where子句中對 字段進行函數(shù)操作或者表達式操作李皇，這將導致引擎放棄使用索引而進行全表掃描。
例子如下圖4 一個對主鍵絕對值進行查詢的sql
如果我們對值進行操作實際上是不會影響索引的如explain SELECT * from r_notice_info b where id =(1+1),但是不建議這樣做,java中的處理要更快些
