此筆記為尚硅谷MySQL高級(jí)篇部分內(nèi)容
目錄
一、索引失效案例
二、關(guān)聯(lián)查詢優(yōu)化
1、采用左外連接
2、采用內(nèi)連接
3、join語句原理
1.驅(qū)動(dòng)表和被驅(qū)動(dòng)表
2.Simple Nested-Loop Join(簡(jiǎn)單嵌套循環(huán)連接)
3.Index Nested-Loop Join(索引嵌套循環(huán)連接)
4.Block Nested-Loop Join(塊嵌套循環(huán)連接)
5.Join小結(jié)
6.Hash Join
4、小結(jié)
三、子查詢優(yōu)化
四、排序優(yōu)化
filesort算法:雙路排序和單路排序
五、GROUP BY優(yōu)化
六、優(yōu)化分頁查詢
七、優(yōu)先考慮覆蓋索引
1、什么是覆蓋索引？
2、覆蓋索引的利弊?
八、如何給字符串添加索引
1、前綴索引
2、前綴索引對(duì)覆蓋索引的影響?
九、索引下推 ICP
1、索引條件下推使用前后
2、ICP的開啟/關(guān)閉
3、?ICP的使用條件?
十、普通索引 vs 唯一索引
1、查詢過程
2、更新過程
3、change buffer的使用場(chǎng)景
十一、?其它查詢優(yōu)化策略
1、EXISTS 和 IN 的區(qū)分
2、COUNT(*)與COUNT(具體字段)效率
3、關(guān)于SELECT(*)
4、LIMIT 1 對(duì)優(yōu)化的影響
5、多使用COMMIT
十二、淘寶數(shù)據(jù)庫，主鍵如何設(shè)計(jì)的？
1、自增ID的問題
2、業(yè)務(wù)字段做主鍵
3、淘寶的主鍵設(shè)計(jì)
4、推薦的主鍵設(shè)計(jì)
都有哪些維度可以進(jìn)行數(shù)據(jù)庫調(diào)優(yōu)?簡(jiǎn)言之:
索引失效、沒有充分利用到索引——索引建立
關(guān)聯(lián)查詢太多JOIN (設(shè)計(jì)缺陷或不得已的需求)——SQL優(yōu)化
服務(wù)器調(diào)優(yōu)及各個(gè)參數(shù)設(shè)置(緩沖、線程數(shù)等)———調(diào)整my.cnf。
數(shù)據(jù)過多――分庫分表
關(guān)于數(shù)據(jù)庫調(diào)優(yōu)的知識(shí)點(diǎn)非常分散。不同的DBMS，不同的公司，不同的職位，不同的項(xiàng)目遇到的問題都不盡相同。這里我們分為三個(gè)章節(jié)進(jìn)行細(xì)致講解。
雖然SQL查詢優(yōu)化的技術(shù)有很多，但是大方向上完全可以分成
物理查詢優(yōu)化和邏輯查詢優(yōu)化兩大塊。
物理查詢優(yōu)化是通過
索引和表連接方式等技術(shù)來進(jìn)行優(yōu)化，這里重點(diǎn)需要掌握索引的使用。邏輯查詢優(yōu)化就是通過SQL
等價(jià)變換提升查詢效率，直白一點(diǎn)就是說，換一種查詢寫法執(zhí)行效率可能更高。
一、索引失效案例
MySQL中
提高性能的一個(gè)最有效的方式是對(duì)數(shù)據(jù)表設(shè)計(jì)合理的索引。索引提供了高效訪問數(shù)據(jù)的方法，并且加快查詢的速度，因此索引對(duì)查詢的速度有著至關(guān)重要的影響。
使用索引可以
快速地定位表中的某條記錄，從而提高數(shù)據(jù)庫查詢的速度，提高數(shù)據(jù)庫的性能。如果查詢時(shí)沒有使用索引，查詢語句就會(huì)
掃描表中的所有記錄。在數(shù)據(jù)量大的情況下，這樣查詢的速度會(huì)很慢。大多數(shù)情況下都（默認(rèn)）采用
B+樹來構(gòu)建索引。只是空間列類型的索引使用R-樹，并且MEMORY表還支持hash索引。其實(shí)，用不用索引，最終都是優(yōu)化器說了算。優(yōu)化器是基于什么的優(yōu)化器?基于
cost開銷(CostBaseOptimizer)，它不是基于規(guī)則(Rule-BasedOptimizer)，也不是基于語義。怎么樣開銷小就怎么來。另外，SQL語句是否使用索引，跟數(shù)據(jù)庫版本、數(shù)據(jù)量、數(shù)據(jù)選擇度都有關(guān)系。 ?
1、全值匹配我最愛
意思是創(chuàng)建聯(lián)合索引多個(gè)索引同時(shí)生效。
2、最佳左前綴法則
在MySQL建立聯(lián)合索引時(shí)會(huì)遵守最佳左前綴匹配原則，即最左優(yōu)先，在檢索數(shù)據(jù)時(shí)從聯(lián)合索引的最左邊開始匹配。
如果索引了多列，要遵守最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過索引中的列。
結(jié)論:MySQL可以為多個(gè)字段創(chuàng)建索引，一個(gè)索引可以包括16個(gè)字段。對(duì)于多列索引，過濾條件要使用索引必須按照索引建立時(shí)的順序，依次滿足，一旦跳過某個(gè)字段，索引后面的字段都無法被使用。如果查詢條件中沒有使用這些字段中第1個(gè)字段時(shí)，多列(或聯(lián)合）索引不會(huì)被使用。
拓展：Alibaba《Java開發(fā)手冊(cè)》
索引文件具有 B-Tree 的最左前綴匹配特性，如果左邊的值未確定，那么無法使用此索引。
3、主鍵插入順序
4、計(jì)算、函數(shù)、類型轉(zhuǎn)換(自動(dòng)或手動(dòng))導(dǎo)致索引失效
5、類型轉(zhuǎn)換導(dǎo)致索引失效
6、范圍條件右邊的列索引失效
7、不等于(!= 或者<>)索引失效
8、is null可以使用索引，is not null無法使用索引
結(jié)論: 最好在設(shè)計(jì)數(shù)據(jù)表的時(shí)候就將
字段設(shè)置為 NOT NULL 約束，比如你可以將INT類型的字段，默認(rèn)值設(shè)置為0。將字符類型的默認(rèn)值設(shè)置為空字符串。拓展: 同理，在查詢中使用
not like也無法使用索引，導(dǎo)致全表掃描
9、like以通配符%開頭索引失效
在使用LIKE關(guān)鍵字進(jìn)行查詢的查詢語句中，如果匹配字符串的第一個(gè)字符為“%”，索引就不會(huì)起作用。只有“%"不在第一個(gè)位置，索引才會(huì)起作用。
拓展：Alibaba《Java開發(fā)手冊(cè)》 【強(qiáng)制】頁面搜索嚴(yán)禁左模糊或者全模糊，如果需要請(qǐng)走搜索引擎來解決。
10、OR 前后存在非索引的列，索引失效
在WHERE子句中，如果在OR前的條件列進(jìn)行了索引，而在OR后的條件列沒有進(jìn)行索引，那么索引會(huì)失效。也就是說，OR前后的兩個(gè)條件中的列都是索引時(shí)，查詢中才使用索引。
因?yàn)镺R的含義就是兩個(gè)只要滿足一個(gè)即可，
因此只有一個(gè)條伴列進(jìn)行了索引是沒有意義的，只要有條件列沒有進(jìn)行索引，就會(huì)進(jìn)行全表掃描，因此索引的條件列也會(huì)失效。
11、數(shù)據(jù)庫和表的字符集統(tǒng)一使用utf8mb4
統(tǒng)一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，統(tǒng)一字符集可以避免由于字符集轉(zhuǎn)換產(chǎn)生的亂碼。不 同的 字符集 進(jìn)行比較前需要進(jìn)行
轉(zhuǎn)換會(huì)造成索引失效。
12、練習(xí)及一般性建議
練習(xí):假設(shè):index(a,b,c)
一般性建議:
對(duì)于單列索引,l盡量選擇針對(duì)當(dāng)前query過濾性更好的索引
在選擇組合索引的時(shí)候，當(dāng)前query中過濾性最好的字段在索引字段順序中，位置越靠前越好。。在選擇組合
索引的時(shí)候，盡量選擇能夠包含當(dāng)前query中的where子句中更多字段的索引。
在選擇組合索引的時(shí)候，如果某個(gè)字段可能出現(xiàn)范圍查詢時(shí)，盡量把這個(gè)字段放在索引次序的最后面。
總之，書寫SQL語句時(shí)，盡量避免造成索引失效的情況。
二、關(guān)聯(lián)查詢優(yōu)化
1、采用左外連接
2、采用內(nèi)連接
內(nèi)連接主被驅(qū)動(dòng)表是由優(yōu)化器決定的。優(yōu)化器認(rèn)為哪個(gè)成本比較小，就采用哪種作為驅(qū)動(dòng)表。如果兩張表只有一個(gè)有索引，那有索引的表作為
被驅(qū)動(dòng)表。
原因：驅(qū)動(dòng)表要全查出來。有沒有索引你都得全查出來。
兩個(gè)索引都存在的情況下， 數(shù)據(jù)量大的 作為
被驅(qū)動(dòng)表（小表驅(qū)動(dòng)大表）
原因：驅(qū)動(dòng)表要全部查出來，而大表可以通過索引加快查找
3、join語句原理
1.驅(qū)動(dòng)表和被驅(qū)動(dòng)表
2.Simple Nested-Loop Join(簡(jiǎn)單嵌套循環(huán)連接)
3.Index Nested-Loop Join(索引嵌套循環(huán)連接)
4.Block Nested-Loop Join(塊嵌套循環(huán)連接)
5.Join小結(jié)
1、整體效率比較:INLJ > BNLJ > SNLJ
2、永遠(yuǎn)用小結(jié)果集驅(qū)動(dòng)大結(jié)果集(其本質(zhì)就是減少外層循環(huán)的數(shù)據(jù)數(shù)量)(小的度量單位指的是表行
3、為被驅(qū)動(dòng)表匹配的條件增加索引(減少內(nèi)層表的循環(huán)匹配次數(shù))
4、增大join buffer size的大小(一次緩存的數(shù)據(jù)越多，那么內(nèi)層包的掃表次數(shù)就越少)
5、減少
驅(qū)動(dòng)表不必要的字段查詢（字段越少，join buffer 所緩存的數(shù)據(jù)就越多)6、在決定哪個(gè)表做驅(qū)動(dòng)表的時(shí)候，應(yīng)該是兩個(gè)表按照各自的條件過濾，過濾完成之后，計(jì)算參與join的各個(gè)字段的總數(shù)據(jù)量，數(shù)據(jù)量小的那個(gè)表，就是“小表”，應(yīng)該作為驅(qū)動(dòng)表。
6.Hash Join
從MySQL的8.0.20版本開始將廢棄BNLJ，因?yàn)閺腗ySQL8.0.18版本開始就加入了hash join默認(rèn)都會(huì)使用hash join
4、小結(jié)
保證被驅(qū)動(dòng)表的JOIN字段已經(jīng)創(chuàng)建了索引
需要JOIN 的字段，數(shù)據(jù)類型保持絕對(duì)一致。
LEFT JOIN 時(shí)，選擇小表作為驅(qū)動(dòng)表，
大表作為被驅(qū)動(dòng)表。減少外層循環(huán)的次數(shù)。INNER JOIN 時(shí)，MySQL會(huì)自動(dòng)將
小結(jié)果集的表選為驅(qū)動(dòng)表。選擇相信MySQL優(yōu)化策略。能夠直接多表關(guān)聯(lián)的盡量直接關(guān)聯(lián)，不用子查詢。(減少查詢的趟數(shù))
不建議使用子查詢，建議將子查詢SQL拆開結(jié)合程序多次查詢，或使用 JOIN 來代替子查詢。
衍生表建不了索引
三、子查詢優(yōu)化
MySQL從4.1版本開始支持子查詢，使用子查詢可以進(jìn)行SELECT語句的嵌套查詢，即一個(gè)SELECT查詢的結(jié)果作為另一個(gè)SELECT語句的條件。
子查詢可以一次性完成很多邏輯上需要多個(gè)步驟才能完成的SQL操作。子查詢是MySQL的一項(xiàng)重要的功能，可以幫助我們通過一個(gè)SQL語句實(shí)現(xiàn)比較復(fù)雜的查詢。但是，子查詢的執(zhí)行效率不高。
原因:
①執(zhí)行子查詢時(shí)MySQL需要為內(nèi)層查詢語句的查詢結(jié)果
建立一個(gè)臨時(shí)表，然后外層查詢語句從臨時(shí)表中查詢記錄。查詢完畢后，再撤銷這些臨時(shí)表。這樣會(huì)消耗過多的CPU和IO資源，產(chǎn)生大量的慢查詢。②子查詢的結(jié)果集存儲(chǔ)的臨時(shí)表，不論是內(nèi)存臨時(shí)表還是磁盤臨時(shí)表都
不會(huì)存在索引，所以查詢性能會(huì)受到一定的影響。③對(duì)于返回結(jié)果集比較大的子查詢，其對(duì)查詢性能的影響也就越大。
在MySQL中，可以使用連接（JOIN）查詢來替代子查詢。連接查詢不需要
建立臨時(shí)表，其速度比子查詢要快，如果查詢中使用索引的話，性能就會(huì)更好
四、排序優(yōu)化
問題: 在WHERE 條件字段上加索引但是為什么在ORDER BY字段上還要加索引呢?
回答:
在MySQL中，支持兩種排序方式，分別是
FileSort和Index排序。
Index排序中，索引可以保證數(shù)據(jù)的有序性，不需要再進(jìn)行排序，
效率更高。FileSort排序則一般在
內(nèi)存中進(jìn)行排序，占用CPU較多。如果待排結(jié)果較大，會(huì)產(chǎn)生臨時(shí)文件I/O到磁盤進(jìn)行排序的情況，效率較低。優(yōu)化建議:
SQL中，可以在WHERE子句和ORDER BY子句中使用索引，目的是在WHERE子句中
避免全表掃描，在ORDER BY子句避免使用FileSort排序。當(dāng)然，某些情況下全表掃描，或者FileSort排序不一定比索引慢。但總的來說，我們還是要避免，以提高查詢效率。盡量使用Index完成ORDER BY排序。如果WHERE和ORDER BY后面是相同的列就使用單索引列;如果不同 就使用聯(lián)合索引。
無法使用Index時(shí)，需要對(duì)FileSort方式進(jìn)行調(diào)優(yōu)。 ?
filesort算法:雙路排序和單路排序
五、GROUP BY優(yōu)化
group by使用索引的原則幾乎跟order by一致，group by即使沒有過濾條件用到索引，也可以直接使用索引。.
group by先排序再分組，遵照索引建的最佳左前綴法則
當(dāng)無法使用索引列，增大
max_length_for_sort_data和sort_buffer_size參數(shù)的設(shè)置where效率高于having，能寫在where限定的條件就不要寫在having中了
減少使用order by，和業(yè)務(wù)溝通能不排序就不排序，或?qū)⑴判蚍诺匠绦蚨巳プ?/p>
Order by、group by、distinct這些語句較為耗費(fèi)CPU，數(shù)據(jù)庫的CPU資源是極其寶貴的。
包含了order by、group by、distinct這些查詢的語句，where條件過濾出來的結(jié)果集請(qǐng)保持在1000行以內(nèi)，否則SQL會(huì)很慢。
六、優(yōu)化分頁查詢
七、優(yōu)先考慮覆蓋索引
1、什么是覆蓋索引？
理解方式一：索引是高效找到行的一個(gè)方法，但是一般數(shù)據(jù)庫也能使用索引找到一個(gè)列的數(shù)據(jù)，因此它 不必讀取整個(gè)行。畢竟索引葉子節(jié)點(diǎn)存儲(chǔ)了它們索引的數(shù)據(jù)；當(dāng)能通過讀取索引就可以得到想要的數(shù) 據(jù)，那就不需要讀取行了。一個(gè)索引包含了滿足查詢結(jié)果的數(shù)據(jù)就叫做覆蓋索引。
理解方式二：非聚簇復(fù)合索引的一種形式，它包括在查詢里的SELECT、JOIN和WHERE子句用到的所有列 （即建索引的字段正好是覆蓋查詢條件中所涉及的字段）。
簡(jiǎn)單說就是，
索引列+主鍵包含SELECT 到 FROM之間查詢的列?
2、覆蓋索引的利弊?
八、如何給字符串添加索引
1、前綴索引
MySQL是支持前綴索引的。默認(rèn)地，如果你創(chuàng)建索引的語句不指定前綴長度，那么索引就會(huì)包含整個(gè)字符串。
使用前綴索引，定義好長度，就可以做到既節(jié)省空間，又不用額外增加太多的查詢成本。前面已經(jīng)講過區(qū)分度，區(qū)分度越高越好。因?yàn)閰^(qū)分度越高，意味著重復(fù)的鍵值越少。
2、前綴索引對(duì)覆蓋索引的影響?
結(jié)論：
使用前綴索引就用不上覆蓋索引對(duì)查詢性能的優(yōu)化了，這也是你在選擇是否使用前綴索引時(shí)需要考慮的一個(gè)因素。
九、索引下推 ICP
面試常問
Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一種在存儲(chǔ)引擎層使用索引過濾數(shù)據(jù)的一種優(yōu)化方式。
如果沒有ICP，存儲(chǔ)引擎會(huì)遍歷索引以定位基表中的行，并將它們返回給MySQL服務(wù)器，由MySQL服務(wù)器評(píng)估
WHERE后面的條件是否保留行。啟用ICP后，如果部分
WHERE條件可以僅使用索引中的列進(jìn)行篩選，則MySQL服務(wù)器會(huì)把這部分WHERE條件放到存儲(chǔ)引擎篩選。然后，存儲(chǔ)引擎通過使用索引條目來篩選數(shù)據(jù)，并且只有在滿足這一條件時(shí)才從表中讀取行。
好處: ICP可以減少存儲(chǔ)引擎必須訪問基表的次數(shù)和MySQL服務(wù)器必須訪問存儲(chǔ)引擎的次數(shù)。
但是，ICP的
加速效果取決于在存儲(chǔ)引擎內(nèi)通過ICP篩選掉的數(shù)據(jù)的比例。
1、索引條件下推使用前后
看個(gè)靈魂畫圖?
2、ICP的開啟/關(guān)閉
3、?ICP的使用條件?
十、普通索引 vs 唯一索引
從性能的角度考慮，你選擇唯一索引還是普通索引呢？選擇的依據(jù)是什么呢？
1、查詢過程
2、更新過程
3、change buffer的使用場(chǎng)景
十一、?其它查詢優(yōu)化策略
面試常問
1、EXISTS 和 IN 的區(qū)分
2、COUNT(*)與COUNT(具體字段)效率
3、關(guān)于SELECT(*)
在表查詢中，建議明確字段，不要使用 * 作為查詢的字段列表，推薦使用SELECT <字段列表> 查詢。原因：
① MySQL 在解析的過程中，會(huì)通過
查詢數(shù)據(jù)字典將"*"按序轉(zhuǎn)換成所有列名，這會(huì)大大的耗費(fèi)資源和時(shí) 間。② 無法使用
覆蓋索引
4、LIMIT 1 對(duì)優(yōu)化的影響
針對(duì)的是會(huì)掃描全表的 SQL 語句，如果你可以確定結(jié)果集只有一條，那么加上 LIMIT 1 的時(shí)候，當(dāng)找 到一條結(jié)果的時(shí)候就不會(huì)繼續(xù)掃描了，這樣會(huì)加快查詢速度。
如果數(shù)據(jù)表已經(jīng)對(duì)字段建立了唯一索引，那么可以通過索引進(jìn)行查詢，不會(huì)全表掃描的話，就不需要加 上
LIMIT 1了。
5、多使用COMMIT
只要有可能，在程序中盡量多使用 COMMIT，這樣程序的性能得到提高，需求也會(huì)因?yàn)?COMMIT 所釋放 的資源而減少。
COMMIT 所釋放的資源：
回滾段上用于恢復(fù)數(shù)據(jù)的信息
被程序語句獲得的鎖
redo / undo log buffer 中的空間
管理上述 3 種資源中的內(nèi)部花費(fèi)
十二、淘寶數(shù)據(jù)庫，主鍵如何設(shè)計(jì)的？
面試中可能會(huì)問你主鍵如何設(shè)計(jì)，面試官主要看你的設(shè)計(jì)思想想法
1、自增ID的問題
自增ID做主鍵，簡(jiǎn)單易懂，幾乎所有數(shù)據(jù)庫都支持自增類型，只是實(shí)現(xiàn)上各自有所不同而已。自增ID除了簡(jiǎn)單，其他都是缺點(diǎn)，總體來看存在以下幾方面的問題：
可靠性不高
存在自增ID回溯的問題，這個(gè)問題直到最新版本的MySQL 8.0才修復(fù)。
安全性不高
對(duì)外暴露的接口可以非常容易猜測(cè)對(duì)應(yīng)的信息。比如：/User/1/這樣的接口，可以非常容易猜測(cè)用戶ID的 值為多少，總用戶數(shù)量有多少，也可以非常容易地通過接口進(jìn)行數(shù)據(jù)的爬取。
性能差
自增ID的性能較差，需要在數(shù)據(jù)庫服務(wù)器端生成。
交互多
業(yè)務(wù)還需要額外執(zhí)行一次類似 last_insert_id() 的函數(shù)才能知道剛才插入的自增值，這需要多一次的網(wǎng)絡(luò)交互。在海量并發(fā)的系統(tǒng)中，多1條SQL，就多一次性能上的開銷。
局部唯一性
最重要的一點(diǎn)，自增ID是局部唯一，只在當(dāng)前數(shù)據(jù)庫實(shí)例中唯一，而不是全局唯一，在任意服務(wù)器間都是唯一的。對(duì)于目前分布式系統(tǒng)來說，這簡(jiǎn)直就是噩夢(mèng)。
2、業(yè)務(wù)字段做主鍵
為了能夠唯一地標(biāo)識(shí)一個(gè)會(huì)員的信息，需要為 會(huì)員信息表 設(shè)置一個(gè)主鍵。那么，怎么為這個(gè)表設(shè)置主鍵，才能達(dá)到我們理想的目標(biāo)呢？ 這里我們考慮業(yè)務(wù)字段做主鍵。
3、淘寶的主鍵設(shè)計(jì)
在淘寶的電商業(yè)務(wù)中，訂單服務(wù)是一個(gè)核心業(yè)務(wù)。請(qǐng)問， 訂單表的主鍵 淘寶是如何設(shè)計(jì)的呢？是自增ID嗎？
打開淘寶，看一下訂單信息：
4、推薦的主鍵設(shè)計(jì)
非核心業(yè)務(wù) ：對(duì)應(yīng)表的主鍵自增ID，如告警、日志、監(jiān)控等信息。
核心業(yè)務(wù) ：主鍵設(shè)計(jì)至少應(yīng)該是全局唯一且是單調(diào)遞增。全局唯一保證在各系統(tǒng)之間都是唯一的，單調(diào)遞增是希望插入時(shí)不影響數(shù)據(jù)庫性能。
高級(jí)篇筆記PDF自取文章來源：http://www.zghlxwxcb.cn/news/detail-477648.html
鏈接：https://pan.baidu.com/s/1pVqrTwIZFoED77i-EFmw6g?pwd=3333?
提取碼：3333文章來源地址http://www.zghlxwxcb.cn/news/detail-477648.html
到了這里，關(guān)于【MySQL高級(jí)篇筆記-索引優(yōu)化與查詢優(yōu)化(中) 】的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
