mysql邏輯架構簡介
和其它資料庫相比，mysql有點與眾不同，它的架構可以在多種不同場景中應用併發揮良好作用。主要體現在儲存引擎上，外掛式的儲存引擎架構及那個查詢處理和其他的系統任務以及資料的儲存提取相分離。這種架構可以根據業務的需求和實際需要選擇適合的儲存引擎。
- 連線層： 最上層是一些客戶端和連線服務，包含本地sock通訊和大多數基於客戶端/服務端工具的實現的類似於tcp/ip 的通訊。主要完成一些類似於連線處理、授權認證、及相關的安全方案。在該層上引入了執行緒池的概念，為通過認證安全接入的客戶端提供執行緒。同樣在該層上可以實現基本的SSL的安全連結。伺服器也會為安全接入的每個客戶端驗證他所具有的操作許可權。
- 服務層：第二層架構主要完成核心服務功能，如SQL結構，並完成快取的查詢，SQL的分析和優化及布馮內建函式的執行。所有跨儲存引擎的功能也在這一層實現，如過程、函式等。在該層，伺服器會解析查詢並創捷相應的內部解析樹，並對其完成相應的優化如確定查詢表的順序，是否利用索引等，最後生成相應的執行操作，如果是select語句，伺服器還會查詢內部的快取，如果快取空間足夠大，這樣在解決大量讀操作的環境中能夠很好的提升系統的效能。
- 引擎層：儲存引擎層，儲存引擎真正的負責了mysql中資料的儲存和提取，伺服器通過API與儲存引擎進行通訊。不同的儲存引擎具有的功能不同，這樣我們可以根據自己的實際需要進行選取。
- 資料儲存層：主要是將資料儲存在執行與裸裝置的檔案系統之上，並完成與儲存引擎的互動。
myisam和innodb 對比
| 對比項 | MyISAM | InnoDB |
|---|---|---|
| 主外來鍵 | 不支援 | 支援 |
| 事務 | 不支援 | 支援 |
| 行表鎖 | 表鎖，即使操作一條記錄也會鎖住整個表，不適合高併發的操作 | 行鎖，操作時只鎖某一行，不對其他行有影響，適合高併發的操作 |
| 快取 | 只快取索引，不快取真實資料 | 不僅快取索引還要快取真實資料，對記憶體要求較高，而且記憶體大小對效能有決定性的影響 |
| 表空間 | 小 | 大 |
| 關注點 | 效能 | 事務 |
索引是什麼 -—— 索引是一種資料結構
mysql 官方對索引的定義為：索引 是幫助mysql高效獲取資料的資料結構。（排好序的快速查詢資料結構）
索引的優勢與劣勢
優勢
- 提高資料檢索的效率，降低資料庫的IO成本
- 對資料進行排序，降低資料排序的成本，降低了CPU的消耗
劣勢
- 實際上索引也是一張表，該表儲存了主鍵與索引欄位，並只想實體表的記錄，索引索引列也是要佔用空間的
- 雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對錶進行insert、update和delete。
- 因為更新表時，mysql不僅要儲存資料，還有保持一下索引檔案每次更新新增了索引列的欄位，都會調整因為更新所帶來的鍵值變化後的索引資訊。
- 索引只是提高效率的一個因素，如果你的mysql有大資料量的表，就需要花時間研究建立最優秀的索引，或優秀查詢。
索引結構與檢索原理
哪些情況適合建索引，哪些情況不適合？
需要
- 主鍵自動簡歷唯一索引
- 頻繁作為查詢條件的欄位應該建立索引
- 查詢中其他表關聯的欄位，外來鍵關係建立索引
- 查詢中排序、統計或者分組欄位
不需要
- 表記錄太少
- 經常增刪改的表
- 資料包含許多重複的內容
explain 使用簡介
使用explain關鍵字可以模擬優化器執行sql查詢語句，從而知道mysql時如何處理你的sql語句的。分析你的查詢語句或是表結構的效能瓶頸。
explain 之id介紹
- select查詢的序列號，包含一組數字，表鎖查詢中執行select子句或操作表的順序
- id相同，執行順序由上至下
- id不同，如果是子查詢，id的序號會遞增，id值越大優先順序越高，有先被執行
- id相同不同，同時存在
explain 之select_type 介紹
查詢的型別，主要是用於區別普通查詢、聯合查詢、子查詢等的複雜查詢
- simple 簡單的select查詢，查詢中不包含子查詢或者union
- primary 查詢中若包含任何複雜的子部分，最外層查詢則被標記為
- subquery 在select或where列表中包含了子查詢
- derived 在from列表中包含的子查詢被標記為derived ， mysql會遞迴執行這些子查詢，吧結果放在臨時表中
- union 若第二個select出現在union之後，則被標記為union；若union包含在from子句的子查詢中，外層select將被標記為derived
explain 之type介紹
顯示查詢使用了何種型別，由好至壞的排序是：system > const > eq_ref > ref > range > index >all。 一般來說，得保證查詢至少達到range級別，最好能達到ref。
- system：表只有一行記錄，這是const型別的特例，平時不會出現，整個也可以忽略不計
- const： 表示通過索引一次就找到了，const用於比較primary key 或者 unique索引。因此之匹配一行資料，索引很快。如將主鍵至於where列表總，mysql就能將該查詢轉換為一個常量。
- eq_ref： 唯一性索引掃描，對於每個索引鍵，表中只有一條記錄與之匹配。常見與主鍵或唯一索引掃描
- ref： 非唯一性索引掃描，返回匹配某個單獨值的所有行，本質上也是一種索引訪問，他返回索引匹配某個單獨值的行，但是他可能會找出多個複合條件的行，所以他是屬於查詢和掃描的混合體。
- range ：只檢索給定範圍的行，使用一個索引來選擇行。key列顯示了哪個索引，一般就是你的where語句中出現了between、<、>、in 等的查詢，這種範圍掃描索引掃描比全表掃描要好，因為他只需要開始與索引的某個點，結束與另一個。
- index：full index scan index與all區別為index型別只遍歷索引樹，這通常比all快。
- all ： full table scan， 將遍歷全表以找到匹配的行。
explain 之possible_keys 和key介紹
possible_key 理論上可能用到的索引。key 實際使用的索引，如果為null，則沒有使用索引
explain之key_len介紹
表示索引中使用的位元組數，可通過該列計算查詢中使用索引的長度。在不損失精確性的情況下，長度越短越好。key_len顯示的值為索引欄位的最大可能長度，非實際使用長度，即key_len是根據表定義計算而得，不是通過表內檢索出來的。
explain 之ref介紹
顯示索引的哪一列被使用了，如果可能的話，是一個常數。哪些列或常量被用於查詢索引列上的值。
explain 之rows介紹
根據表統計資訊及所有的選用情況，大致估算出找到所需的記錄所需要讀取的行數。
explain 之extra介紹
包含不適合在其他列中顯示但十分重要的額外資訊，下面列舉出幾個比較常見的詞。
- using filesort（九死一生）：說明mysql會對資料使用一個外部的索引排序，而不是按照表內的索引順序進行讀取。mysql無法利用索引完成的排序操作稱為：檔案排序。
- using temorary（十死無生）：使用了臨時表儲存中間結果，mysql在對查詢結果排序時使用了臨時表。常見於排序order by 和分組查詢 group by。
- using index（還不錯）：表示相應的select操作中使用了覆蓋索引，避免訪問了表的資料行，效率不錯；如果同時出現了using where，表明索引被用來執行索引鍵值的查詢；否則，表面索引用來讀取資料而非執行查詢動作。
索引覆蓋的理解
- 就是select 的資料列只用從索引中就能夠取得，不必讀取資料行，mysql可以利用索引返回select列表中的欄位，而不必更加索引再次讀取資料檔案，換句話說查詢列要被所建的索引覆蓋
- 索引是高效找到行的一個辦法，但是一般資料庫也能使用索引找到一個列的資料，因此他不必讀取整個行。畢竟索引葉子節點儲存了他們索引的資料；當能通過讀取所有就可以得到想要的資料，那就不需要讀取行了。一個索引包含了滿足查詢結果的資料就叫做索引覆蓋。
索引失效的原因
- 是否符合最佳左字首法則
- 是否在索引列做任何操作（計算、函式、自動手動型別轉換）
- 儲存引擎不能使用索引範圍條件右邊的列
- mysql 在使用（!= <>)的時候無法使用索引會導致全表掃描
- is null, is not null 也無法使用索引
- like 以萬用字元開頭（‘%abc...’)mysql 索引失效會變成全表掃描的操作（只查詢索引中的欄位，"%ab%"不失效）
- 字串不加單引號索引失效
- 少用or，用它來連線會索引失效