執行計劃
-
id
sql比較複雜的話，id列值會有好幾個，它表示具體sql語句要執行的順序
- type
表示通路資料或進行查詢的時候，所對應的類型是什麼。效率優先級由低到高，all->index -> range -> index_ref -> cost -->system。all代表全表掃描 ，效率最低的一種方式，system效率是最高的。
如果sql語句裡面有type=all，一定要進行sql優化的，盡可能的保證type類型的值在range以上。
執行計劃的官網文檔
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
點開type，可以看到每種類型的解釋
all的效率是最低的。
- key
- 目前的sql語句在執行的時候索引列用的是哪個，有沒有在使用索引
- key_len
- 索引長度
- extra
- 額外資訊，比如using index、using index condition、using where、using filesort(用檔案排序，涉及到檔案的io，性能較差)
什麼條件都不帶的排序 怎麼進行優化
大部分的排序都是在記憶體中進行的，比如冒泡、歸并、快排、希爾排序、基數排序、堆排序。
如果資料量非常非常大的話，記憶體存不下怎麼辦？可以利用索引，
索引要看名字,不要看行數
使用索引排序，
使用臨時檔案排序。利用索引的效率更快一些。
如果是一個全亂序的數組，在進行檢視的時候，需要把所有的數組加載到記憶體裡面，然後再進行一個排序。
如果資料本身是有序的，直接從資料檔案中把資料取出來。
利用了索引的有序性這個特點來進行相關的優化和調整，這就是執行計劃帶給我們的意義。
索引系統設計所需的基本理論知識
執行計劃最關鍵最核心的點在于索引，索引加快資料的通路。
索引采用b+樹資料結構。
資料存儲在磁盤，讀取磁盤資料，記憶體和磁盤資料進行交換。
- 從硬體層面優化
IO （輸入和輸出） 是硬體層面解決問題，比如将機械磁盤換成固态磁盤（機械硬碟是一種傳統的硬碟，它包括磁盤的轉軸，磁頭控制器，資料轉換器和控制馬達。而固态硬碟，就是由固态的電子儲存晶片組成的硬碟）。
- 軟體層面優化方向
盡量減少讀取次數，每次讀取量盡量少。
考慮存儲的資料是什麼類型的資料（資料格式），比如書的目錄，先找到目錄名稱對應的頁面，直接翻到對應頁看找到内容。
索引是由關鍵字和指針組成，存儲是K-V格式的資料。用什麼樣的資料結構來進行存儲這個資料？
比如hash表、樹（二叉樹、紅黑樹、avl樹、btree、b+tree等），但不管什麼樣的資料，要保持K-V的資料格式。
查詢出來的資料是一行一行的，而寫到磁盤是一個個固定的檔案。
一個檔案假設1T的磁盤，沒有1T的記憶體空間存儲全量資料，保證資料在讀取的時候要分塊讀取，比如每次讀10M，切分成一個一個的單獨塊，分的次數多些或者需要讀的時候再讀，不需要讀的時候就不讀了。
作業系統基本概念
- 局部性原理
- 資料和程式都有聚內建群的傾向，叫空間局部性，在進行資料拼接的時候把某些具體類别的資料放到一起，比如相同特征的資料放在一起，不相同不放在一塊；之前被通路過的資料有可能很快被下一次通路到，這個叫時間局部性。
- 磁盤預讀
- 記憶體跟磁盤發生磁盤互動的時候 ，有一個基本的邏輯機關叫做頁，頁的大小跟作業系統相關，一般是4K或8K，在進行資料互動的時候資料大小是頁的整數倍，比如某一頁是4KB，讀資料的時候可以是8K、16K、32K 、64K，必須是整數倍。分塊的時候，每次讀取大于4K或者 4K整數倍的資料，也沒有固定塊大小多少，是4K的整數倍就行了。
innodb存儲引擎，每次讀取16KB的資料 ，不管磁盤檔案多大，每次就讀取16KB。
借鑒了這些理論知識來進行索引系統的設計。
如何存儲資料
K-V資料格式，K是索引的值，V代表目前整個行記錄/資料。
把資料聚內建群的進行存放，需要選擇合适的資料結構，一個是hash表，一個是樹。
- hash表
計算下目前key的hash位置，放入某一個格子，如果有元素就往上面追加，如果沒有元素直接放入進去。
hash資料結構的問題
- hash沖突
- 需要優良的hash算法，否則會造成hash碰撞或hash沖突問題。比如0246有元素，1357沒有元素，沒有元素會浪費存儲空間，導緻資料散列不均勻。
- 無序
- 無序會導緻無法進行範圍查詢，本身hash是散列的方式亂序放入。比如id>3 ，将會進行全表掃描， 效率極低。
- 需要大量的記憶體空間
- 在建立的時候，必須指定好長度，後面會進行擴容，但是對記憶體的消耗是比較大的。
mysql中是有hash索引的
https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_hash_index
對于memory這種存儲引擎，是支援hash索引的。在記憶體裡，就算挨個對比，效率依然很高，不在磁盤中，不會發生記憶體和磁盤之間的資料交換。
innodb支援自适應hash。
樹是比較複雜的
RST（魯棒序列樹）和AVL（平衡二叉查找樹）和紅黑樹都屬于二叉樹。每一個節點有且僅有兩個分支，隻不過在AVL和紅黑樹會有一個平衡的過程，保證左右子樹空間存儲的資料盡可能一緻。AVL是嚴格意義上的平衡樹，紅黑樹是非嚴格。
将二叉樹作為資料結構，會有什麼問題
每個節點有且僅有兩個分支。左邊是小于key的，右邊是大于key的。如果想往目前結構裡面插入更多的資料的話，就可能會導緻目前這棵樹變得無限深，太深的話，會導緻io次數會變高，最根本的原因在于二叉有且僅有兩個分支，但是它裡面依然有借鑒的地方即有序，缺點在于二叉。
為了保證這棵樹不變高，變成一個"矮胖子"，變形為多叉排序樹（無論三叉還是四叉...）,保證每個節點上盡可能多的去存儲資料。
https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
b樹和b+樹在進行資料插入的時候，有一個叫degree 度的， 表示每個節點最多可以存放多少個元素值。
每個節點裡面隻能放一個key值 ，多叉意味着有多個分支 （多個分支的話，一定要區分分支的範圍）是以上面一定是多個值，有多個的範圍。
如果degree=4 就說明每個節點中可以放4-1=3的資料量。
10個資料，分三層。整個b樹裡面沒有重複資料。
怎麼把這樣資料映射到資料庫表裡面？
在檢索一行記錄的時候，有key值和value（整行記錄），就意味着key值和整行元素變成一個集合放到節點裡面，變成某一個節點的值。在這種情況下做了一個演變或者叫變形，
這是一個b樹，在b樹裡面每一個磁盤塊叫頁，每個磁盤塊預設16KB（這個大小可以自己去調整，預設是16KB）。在每個磁盤塊裡面，放了三個類型的資料，第一個類型指針，第二個類型是key值（索引列裡面的數值），第三個類型是data整行的行記錄。
b樹的資料和key值放在一起，對于目前的三層的b樹而言，如果要讀取28這條記錄的話，先拿到根節點，拿28和16做對比，大于16，小于34，則找到p2，通過p2找到磁盤3，在磁盤3上繼續做判斷，28大于25，小于31，找到p2指針，再通過磁盤3的p2指針找到磁盤8，進而找到key=28的資料。
在這整個周遊的過程中，讀了多少資料或者讀了多大的資料，每一塊是16KB，讀了3個磁盤塊，16x3，一共讀取了48kb的資料。
目前這樣的一個資料結構,能存儲的資料量有多少
一個表用這樣三層資料結構來存儲的話，這個表裡面可以存儲多少條資料。
假設一個data就代表一行記錄，假設一行記錄等于1kb，目前磁盤塊裡面最多可以存放15條記錄。key和指針都是占空間的，為了友善計算，假設這些東西不占用空間，如果一個磁盤快存儲16個數值的話，三層共16的三次方4096條記錄，資料量很少。那如果想存儲8000條怎麼辦，隻能往下加一層，高度就變高了。為什麼才存儲了4000多條資料，是誰占用了大量的存儲空間？
data 這裡面要把分支變多，在整個基礎之上，能不能讓第一層和第二層不存資料 ，隻放key值和指針。
在這個資料之上，就變成了b+樹的資料結構。
b+樹的特點:
- 葉子節點有一個雙向連結清單可以進行操作，
- 有很多重複資料，第三層有所有資料（key和data），第一、第二層有部分key資料，屬于重複資料。
- 第一層和第二層不存儲data資料，隻有最下面的葉子節點存儲資料
B+Tree是在BTree的基礎之上做的一種優化，變化如下：
- B+Tree每個節點可以包含更多的節點，這麼做的原因，第一個是為了降低樹的高度，第二個是将資料範圍變為多個區間，區間越多，資料檢索越快
- 非葉子節點存儲key，葉子節點存儲key和資料
- 葉子節點兩兩指針互相連接配接（符合磁盤的預讀特性），順序查詢性能更高
- 在B+Tree上有兩頭指針，一頭指向根節點，另一頭指向關鍵字最小的葉子節點，而且所有葉子節點（即資料節點）之間是一種鍊式結構，是以可以對B+Tree進行兩種查詢運算：一種是對主鍵的範圍查找和分頁查找，另一種是從根節點開始，進行随機查找。
第一、二層少了data，葉子節點用一個雙向連結清單把所有資料連結起來了
那目前b+能支援的資料存儲量有多少？
資料總量沒變，還是三層，還是讀48K的資料，假設p1和20是一組，共同占用10個位元組，第一個磁盤塊可以存儲多少個分支？16K等于16 X 1024個位元組，為了好算，1024當1000來算，除以10即16 X 1000 / 10 =1600個分支，第二層也是1600個分支，第三層一個data為1kb即一個磁盤塊存儲16條資料，三層可以存儲的資料規模是4000萬，擴充了1萬倍，是以3到4層的b+樹，足以支撐千萬級的資料量。
整體的塊大小是不變的，key值發生了變化，如果key值+指針占100個位元組，16x1000/100即160X160X16=40萬零9600。
索引類型用int類型還是varchar類型
int 4個位元組，varchar如果指定3個位元組，在進行實際數值存儲的時候，要看占用的位元組數，一般情況下，你用varchar的情況肯定超過了10或超過4 ，但不排除其他情況，是以一般情況下用int。