mysql的優(yōu)化大綱:

數(shù)據(jù)結(jié)構(gòu)算法: 二叉樹---->平衡二叉樹(紅黑樹)---->B樹(平衡多路查找樹)---->B+樹(B樹的進(jìn)階),也就是mysql的底層數(shù)據(jù)結(jié)構(gòu)

二叉樹: 每個節(jié)點(diǎn)最多有兩個子節(jié)，并遵循左邊小于右邊，數(shù)據(jù)隨機(jī)性情況樹杈越明顯,缺點(diǎn):隨著數(shù)據(jù)的增多，樹的結(jié)果會越高，數(shù)據(jù)查詢的效率會變慢，如果數(shù)據(jù)是按順序來排序依次進(jìn)入的話，樹的高度則會更明顯

紅黑樹:雖通過自旋平衡，子節(jié)點(diǎn)自動分為2個，從而減少樹的高度，當(dāng)數(shù)據(jù)有序時，更明顯，檢索結(jié)果更佳，數(shù)據(jù)量越大樹也會越高

b-tree：b-tree的出現(xiàn)，可以解決樹的高度的問題，他不在限制一個父節(jié)點(diǎn)中只有兩個子節(jié)點(diǎn)，而是允許M個子節(jié)點(diǎn)(M>2)。不僅如此，B樹的一個節(jié)點(diǎn)可以存儲多個元素，相交于二叉樹的結(jié)構(gòu)又將整體的樹高度降低了

B+tree：b+tree是b樹的一種升級優(yōu)化，更適合做存儲索引結(jié)構(gòu)，在B+tree中，非葉子節(jié)點(diǎn)上僅存儲key，不存儲數(shù)據(jù)，所有的數(shù)據(jù)均存儲在葉子的節(jié)點(diǎn)上面，并且是按照順序來排序的，此外在B+tree中·各個數(shù)據(jù)頁之間是雙向鏈表連接的，葉子節(jié)點(diǎn)中的數(shù)據(jù)是單向鏈表連接的。

聚集索引和非聚集索引的區(qū)別？

按物理存儲分類：innerdb屬于聚集索引，mysisam屬于非聚集索引

聚集索引:它的子葉同時存儲索引和數(shù)據(jù)

非聚集索引：它的子葉只存儲索引和指針，需要通過指針來查找具體的數(shù)據(jù)

效率：聚集索引更快

二級索引：所有非主鍵索引都稱為二級索引，并且二級索引為非聚集索引，查找數(shù)據(jù)需要進(jìn)行回表

覆蓋索引: 需要查詢的字段都在索列中的情況稱為覆蓋索引，索引列覆蓋了查詢字段的意思

索引下推：是mysql5.6之后針對掃描二級索引的一項(xiàng)改進(jìn)，用來在范圍查詢是減少回表的次數(shù)，將需要回表的查詢一次性完成

單列索引：只有一個索引字段(name)

聯(lián)合索引：多個索引字段(name,age,heigt)，聯(lián)合索引需要遵循最左前綴原則,因?yàn)闆]有第一列的話，直接訪問第二列，那么第二列肯定是無序的，直接訪問后面的列就用不到索引了

單向索引和聯(lián)合索引分別在什么場景創(chuàng)建，優(yōu)勢是什么？

聯(lián)合索引的優(yōu)勢：

1.減少開銷:

建立一個聯(lián)合索引，實(shí)際上相當(dāng)于創(chuàng)建了多個索引((a), (a,b), (a,b,c)),每單獨(dú)建一個索引，都會增加寫操作的開銷和磁盤的開銷，對于大量的數(shù)據(jù)，使用聯(lián)合查詢會大大減少開銷

2.覆蓋索引:

select a,b,c from table where a ='xx' and b='xxx'

那么mysql可以直接遍歷索引取得數(shù)據(jù)，而無需回表，減少了很多的io操作，減少io操作，特別是隨機(jī)io是dba主要的優(yōu)化機(jī)制

3.效率高：

索引列多，通過聯(lián)合索引篩選出的數(shù)據(jù)越少，比如有100萬條的數(shù)據(jù)表，有如下sql：

select col1,col2,col3 from table where col1='1' and colw='2' and col2='3'

如果是單列的話需要查找三次

索引的優(yōu)點(diǎn)：

1.提高檢索效率

2.降低排序成本，索引對應(yīng)的字段已經(jīng)有一個自動排序·功能的，默認(rèn)是升序asc

索引的缺點(diǎn):

1.創(chuàng)建索引和維護(hù)索引需要耗費(fèi)時間，這種時間會隨著數(shù)據(jù)增加而增加

2.索引需要占用無聊空間，數(shù)據(jù)量越大，占用越多

3.會降低表增刪改的效率，因?yàn)槊看卧鰟h改索引，都需要進(jìn)行動態(tài)維護(hù)

sql以及索引優(yōu)化:

1.創(chuàng)建索引減少掃描量·

2.調(diào)整索引減少計(jì)算量

3.索引覆蓋(減少不必訪問的列，避免回表查詢)

4.干預(yù)執(zhí)行計(jì)劃

5.sql改寫

如果索引失效，需要排查：

手動添加索引：force index(idx_name_age_position)

1.遵循最左前綴原則

2.不在索引列上做任何操作(計(jì)算，函數(shù)，(自動or手動)， 類型轉(zhuǎn)換),會導(dǎo)致索引失效而造成全表掃描

3.索引引擎不能使用索引中范圍條件中右邊的列,范圍查詢會使得字段無序，造成部分索引失效

4.盡量使用覆蓋索引(只訪問索引的查詢(索引列包含列),減少select * 語句，覆蓋索引不寫*

5.不等空值還有or，索引失效要少用

6.like百分寫在最右

7.字符串不加單引號索引會失效

8.范圍查詢優(yōu)化

trace工具用法:(跟蹤查詢過程和結(jié)果)

第一階段:sql準(zhǔn)備階段，格式化sql

第二階段:sql優(yōu)化階段

第三階段:預(yù)估表的訪問成本 -->對全表掃描情況行數(shù)，查詢成本,再查詢索引的成本，再根據(jù)估值分決定用索引還是全盤掃描

sql最佳實(shí)踐：

1.避免使用select *：?

1).會增加查詢解析器的成本

2).不走覆蓋索引會產(chǎn)生大量的回表查詢

3).浪費(fèi)cpu和內(nèi)存資源

2.小表驅(qū)動大表：

用數(shù)據(jù)量少的表用索引和條件大表進(jìn)行數(shù)據(jù)篩選，從而減少數(shù)據(jù)計(jì)算量，提高查詢效率

3.用連接查詢代替子查詢:

1).子查詢需要執(zhí)行兩次數(shù)據(jù)庫查詢，一次是外部，一次是嵌套子查詢。因此，使用連接查詢可以減少數(shù)據(jù)庫查詢的次數(shù)，提高查詢的效率

2).連接查詢可以更好的利用數(shù)據(jù)庫索引，提高查詢性能。子查詢通常會使用臨時表或內(nèi)存表，而連接查詢可以直接利用表上的索引。這意味著連接查詢可以更快的訪問表中的數(shù)據(jù)，減少查詢的資源消耗

3).對于大型數(shù)據(jù)集，使用連接查詢通常比使用子查詢更高效，子查詢通常需要掃描整個表，而連接查詢可以利用索引加速讀取操作