MySQL 一 體系結構

一、MySQL 體系結構

1、 Client Connectors 接入方
2、 Management Serveices & Utilities 系統管理和控制工具。例如：備份恢復、mysqldump、 mysql複製叢集、分割槽管理
3、 Connection Pool 連線池。管理緩衝使用者連線、使用者名稱、密碼、許可權校驗、執行緒處理等需要快取的需求 
4、 SQL Interface SQL介面。接受使用者的SQL命令，並且返回使用者需要查詢的結果。比如select from就是呼叫SQL Interface
5、 Parser 解析器。SQL命令傳遞到解析器的時候會被解析器驗證和解析。解析器是由Lex和YACC實現的
6、 Optimizer 查詢優化器。SQL語句在查詢之前會使用查詢優化器對查詢進行優化
    查詢執行的路徑：mysql 客戶端/服務端通訊 ——查詢快取 ——查詢優化處理 ——查詢執行引擎 ——返回客戶端
7、 Cache 和 Buffer 快取記憶體區。查詢快取，如果查詢快取有命中的查詢結果，查詢語句就可以直接去查詢快取中取資料
8、 pluggable storage Engines 外掛式儲存引擎。儲存引擎是MySql中具體的與檔案打交道的子系統。也是Mysql最具有特色的一個地方。 Mysql的儲存引擎是外掛式的
9、 file system 檔案系統。資料、日誌（redo，undo）、索引、錯誤日誌、查詢記錄、慢查詢等
10、MySQL 執行一條查詢語句的內部執行過程？
    1）客戶端先通過聯結器連線到 MySQL 伺服器
    2）聯結器許可權驗證通過之後，先查詢是否有快取，如果有快取（之前執行過此語句）則直接返回快取資料，如果沒有快取則進入分析器。
    3）分析器會對查詢語句進行語法分析和詞法分析，判斷 SQL 語法是否正確，如果查詢語法錯誤會直接返回給客戶端錯誤資訊，如果語法正確則進入優化器。
    4）優化器是對查詢語句進行優化處理，例如一個表裡面有多個索引，優化器會判別哪個索引效能更好。
    5）優化器執行完就進入執行器，就開始執行語句進行查詢比對，直到查詢到滿足條件的所有資料，然後進行返回。
    客戶端——聯結器（許可權驗證）——分析器（解析parser，SQL 語法驗證）——優化器（優化處理）——執行器
11、MySQL 提示“不存在此列”是執行到解析器階段報出的，因為解析器階段檢查 SQL 語句的正確性
12、查詢優化處理的三個階段：
    1）解析sql，通過lex詞法分析,yacc語法分析將sql語句解析成解析樹
    2）預處理階段，根據mysql的語法的規則進一步檢查解析樹的合法性，如：檢查資料的表和列是否存在，解析名字和別名的設定。還會進行許可權的驗證
    3）查詢優化器，優化器的主要作用就是找到最優的執行計劃
13、Mysql客戶端與服務端的通訊方式是“半雙工”；半雙工通訊：在任何一個時刻，要麼是有伺服器向客戶端傳送資料，要麼是客戶端向服務端傳送資料，這兩個動作不能同時發生。
    所以我們無法也無需將一個訊息切成小塊進行傳輸
14、檢視命令 show full processlist / show processlist    可通過kill {id}的方式進行連線的殺掉
    Sleep    執行緒正在等待客戶端傳送資料
    Query    連線執行緒正在執行查詢
    Locked    執行緒正在等待表鎖的釋放
    Sorting result    執行緒正在對結果進行排序
    Sending data    向請求端返回資料
15、MySQL 查詢快取的內容：SQL語句、SELECT操作的結果集。新的SELECT語句先去查詢快取，判斷是否存在可用的記錄集
16、判斷標準：與快取的SQL語句是否完全一樣，區分大小寫(簡單認為儲存了一個key-value結構，key為sql，value為sql查詢結果集)
17、使用場景：以讀為主的業務，資料生成之後就不常改變的業務。比如門戶類、新聞類、報表類、論壇類等
18、MySQL 查詢快取的優缺點
    優點：效率高，如果已經有快取則會直接返回結果
    缺點：任何更新表操作都會清空查詢快取，導致查詢快取非常容易失效，失效太頻繁導致快取命中率比較低
19、MySQL 查詢快取配置引數為 query_cache_type ，值為 DEMAND（按需使用），MySQL 8.0 之後直接刪除了查詢快取的功能
    0：不啟用查詢快取，預設值
    1：啟用查詢快取，只要符合查詢快取的要求，客戶端的查詢語句和記錄集都可以快取起來，供其他客戶端使用，加上 SQL_NO_CACHE將不快取
    2：啟用查詢快取，只要查詢語句中新增了引數 SQL_CACHE，且符合查詢快取的要求，客戶端的查詢語句和記錄集，則可以快取起來，供其他客戶端使用
20、show status like 'Qcache%' 命令可檢視快取情況
21、SHOW VARIABLES LIKE "%cache%" 可以檢視 query_cache_type 的狀態
22、query_cache_size    最小值為40K，預設1M，推薦設定為：64M/128M
23、query_cache_limit    限制查詢快取區最大能快取的查詢記錄集，預設1M
24、MySQL 查詢快取是發生在聯結器之後的
25、MySQL 查詢不快取的情況：
    1）當查詢語句中有一些不確定的資料時，則不會被快取。如包含函式NOW()，CURRENT_DATE()等類似的函式，或者使用者自定義的函式，儲存函式，使用者變數等都不會被快取
    2）當查詢的結果大於query_cache_limit設定的值時，結果不會被快取
    3）對於InnoDB引擎來說，當一個語句在事務中修改了某個表，那麼在這個事務提交之前，所有與這個表相關的查詢都無法被快取。因此長時間執行事務會大大降低快取命中率
    4）查詢的表是系統表
    5）查詢語句不涉及到表
26、為什麼mysql預設關閉了快取開啟
    1）在查詢之前必須先檢查是否命中快取,浪費計算資源
    2）如果這個查詢可以被快取，那麼執行完成後，MySQL發現查詢快取中沒有這個查詢，則會將結果存入查詢快取，這會帶來額外的系統消耗
    3）針對表進行寫入或更新資料時，將對應表的所有快取都設定失效。
    4）如果查詢快取很大或者碎片很多時，這個操作可能帶來很大的系統消耗
27、MySQL 的常用儲存引擎有 InnoDB、MyISAM、Memory等，從 MySQL 5.5.5 版本開始 InnoDB 就成為了預設的儲存引擎。
28、InnoDB 的特性：1）插入緩衝(insert buffer)  2）兩次寫(double write)  3）自適應雜湊索引(adaptive hash index)
29、MySQL 可以針對不同的表設定不同的引擎。在 create table 語句中使用 engine=引擎名（比如InnoDB）來設定此表的儲存引擎。
    如：create table student(
            id int primary key auto_increment,
            username varchar(120),
            age int
        ) ENGINE=InnoDB
30、InnoDB 和 MyISAM 的區別
    1）最大的區別是 InnoDB 支援事務，MyISAM 不支援事務
    2）InnoDB 支援崩潰後安全恢復，MyISAM 不支援崩潰後安全恢復
    3）InnoDB 支援行級鎖，MyISAM 不支援行級鎖，只支援到表鎖
    4）InnoDB 支援外來鍵，MyISAM 不支援外來鍵
    5）InnoDB 主鍵查詢效能高於 MyISAM
    6）MyISAM 效能比 InnoDB 高
    7）MyISAM 支援 FULLTEXT 型別的全文索引，InnoDB 不支援 FULLTEXT 型別的全文索引，但是 InnoDB 可以使用 sphinx 外掛支援全文索引，並且效果更好
31、一張自增表中有三條資料，刪除兩條資料之後重啟資料庫，再新增一條資料，此時這條資料的 ID 是幾？
    如果這張表的引擎是 MyISAM，那麼 ID=4，如果是 InnoDB 那麼 ID=2（MySQL 8 之前的版本）
32、記憶體表，指的是使用 Memory 引擎的表，建表語法是 create table … engine=memory。這種表的資料都儲存在記憶體裡，系統重啟的時候會被清空，但是表結構還在。
33、臨時表，可以使用各種引擎型別。使用 InnoDB 引擎或 MyISAM 引擎的臨時表，寫資料的時候是寫到磁碟上的