Mysql最全筆記包含腦圖

擷取腦圖方式請看最下面！

Mysql

1.mysql語句執行流程

查詢語句

• 1.通信協定
  • 1.通信類型
    • 1.同步通信：依賴于被調用方，受限與被調用方的性能，也就是說線程會阻塞，等待資料庫的傳回。
    • 2.異步通信：可以避免應用阻塞等待，但是不能節省SQL執行時間；如果并發過高，每個SQL執行都要建立一個連接配接，會給伺服器造成很大壓力，如果要異步，必須要考慮連接配接池。
  • 2.連接配接方式
    • 1.長連接配接（預設）：
    • 2.短連接配接：
  • 3.查詢連接配接相關配置
    • 1.wait_timeout 非互動式逾時時間，如JDBC
    • 2.interactive_timeout 互動式逾時時間，如資料庫工具
    • 3.檢視目前有多少個連接配接 show global status like ‘Thread%’;
      • 1.Threads_cached:緩存中的線程連接配接數
      • 2.Threads_connected:目前打開的連接配接數。
      • 3.Threads_created:為處理連接配接建立的線程數。
      • 4.Threads_running:非睡眠狀态的連接配接數，通常指并發連接配接數。
    • 4.檢視SQL的執行狀态， SHOW PROCESSLIST;
      • State
        • Sleep：線程正在等待用戶端，以向他發送一個新語句
        • Query：線程正在執行查詢或往用戶端發送請求
        • Locked：該查詢被其他查詢鎖定
        • Copying to tmp table on disk：臨時結果集合大于tmp_table_size 線程把記憶體格式持久化到磁盤
        • Sending data：線程正在為Select語句處理行，同時正在向用戶端發送資料
        • Sorting for group/order：線程正在分類，以滿足group/order要求
      • Info：執行的語句
      • 以及一些資料庫使用者資訊
    • 5.允許用戶端最大連接配接151個，最大可設定成16384，show variables like ‘max_connections’;
  • 4.支援的通信協定
    • 1.Unix Socket：比如在伺服器上，如果沒有指定-h，它就用socket方式登陸（本地連接配接）
    • 2.TCP/IP：如果指定-h參數，就會用TCP/IP協定
    • 3.命名管道：
    • 4.記憶體共享：
  • 5.通信方式（采用半雙工）
    • 1.單工：資料單向傳輸，比如遙控器
    • 2.半雙工：資料雙向傳輸，但不能同時傳輸，比如對講機，
    • 3.全雙工：資料雙向傳輸，可以同時傳輸，比如打電話
• 2.查詢緩存：mysql自帶緩存子產品，緩存預設是關閉的，8.0版本中被移除了
  • 為什麼不推薦使用？
• 3.文法解析&預處理：基于SQL文法進行詞法和語義的解析
  • 1.詞法解析：把一個完整的SQL語句打碎成一個個單詞
  • 2.文法解析：對SQL做一些文法檢查，比如單引号沒有閉合，然後根據MySQL定義的文法規則，根據SQL語句生成一個資料結構，這個資料結構為解析樹
  • 3.預處理器：詞法與文法解析隻能知道SQL語句是否符合MySQL的文法規則，（而無法判斷表是否存在、列名是否存在，檢查名字和别名保證沒有歧義），預處理後得到一個新的解析樹。
• 4.查詢優化器（Optimizer）&查詢執行計劃：

  • 查詢優化器：Last_query_cost 可以檢視開銷。

    得到解析樹之後，是不是就執行SQL語句了呢？

    這裡有一個問題，一條SQL語句是不是隻有一種執行方式，或者說資料庫最終執行的SQL是不是就是為們發送的SQL？

    這個答案是否行的，一條SQL語句是可以有很多種執行方式的，最終傳回相同的結果，他們是等價的，但是如果有這麼多執行方式，這些方式是怎麼得到的？最終選擇哪一種去執行？根據什麼判斷标準去選擇？

    • 1.什麼是優化器？
      • 根據解析樹生成不同的執行計劃，然後選擇最優的執行計劃，MySQL是基于開銷的優化器，哪種執行計劃開銷最小，就用哪種。
    • 2.優化器可以做什麼？
      • 1.對多張表進行關聯查詢的時候，以哪個表的資料作為基準表
      • 2.多個索引可以使用的時候，選擇哪個索引
    • 3.優化器是怎麼得到執行計劃的？
      • 1.開啟優化器的追蹤（預設關閉）SHOW VARIABLES LIKE ‘optimizer_trace’;
      • 2.執行一個SQL語句，優化器會生成執行計劃

      • 3.優化器分析的過程已經記錄到系統表裡 set optimizer_trace=“enabled=off”;

        steps主要分為3部分

        1.準備階段(join_preparation-SQL)

        2.優化階段(join_optimization-SQL)

        3.執行階段(join_execution-SQL)

    • 4.優化器得到的結果：優化器最終會把解析樹變成一個查詢執行計劃，查詢執行計劃是一個資料結構
  • 執行計劃
• 5.存儲引擎
  • 1.存儲引擎介紹：MySQL支援多種存儲引擎，他們是可以替換的，是以是插件式的存儲引擎，存儲引擎是以表為機關的，建立表後還可以修改存儲引擎
  • 2.檢視存儲引擎 如何檢視存儲引擎呢？show table status from
    • Transactions 是否支援事務
    • XA 協定用來實作分布式事物（分為本地資料總管/事務管理器）
    • Savepoints 實作子事務。建立一個savepoints後，事務可以復原到這個點，不會影響到之前的操作。
  • 3.存儲引擎的比較show engines ;

    • InnoDB

      mysql 5.7 中的預設存儲引擎。InnoDB 是一個事務安全(與 ACID 相容)的 MySQL 存儲引擎，它具有送出、復原和崩潰恢複功能來保護使用者資料。InnoDB 行級鎖(不更新 為更粗粒度的鎖)和 Oracle 風格的一緻非鎖讀提高了多使用者并發性和性能。InnoDB 将 使用者資料存儲在聚集索引中，以減少基于主鍵的常見查詢的 I/O。為了保持資料完整性，

      InnoDB 還支援外鍵引用完整性限制。 特點:

      支援事務，支援外鍵，是以資料的完整性、一緻性更高。 支援行級别的鎖和表級别的鎖。 支援讀寫并發，寫不阻塞讀(MVCC)。 特殊的索引存放方式，可以減少 IO，提升查詢效率。 适合:經常更新的表，存在并發讀寫或者有事務處理的業務系統。

    • MyISAM

      特點:

      支援表級别的鎖(插入和更新會鎖表)。不支援事務。 擁有較高的插入(insert)和查詢(select)速度。

      存儲了表的行數(count 速度更快)。

      (怎麼快速向資料庫插入 100 萬條資料?我們有一種先用 MyISAM 插入資料，然後

      修改存儲引擎為 InnoDB 的操作。) 适合:隻讀之類的資料分析的項目。

    • Memory

      把資料放在記憶體裡面，讀寫的速度很快，但是資料庫重新開機或者崩潰，資料會全部消 失。隻适合做臨時表。

      将表中的資料存儲到記憶體中。

    • CSV

      它的表實際上是帶有逗号分隔值的文本檔案。csv 表允許以 csv 格式導入或轉儲資料， 以便與讀寫相同格式的腳本和應用程式交換資料。因為 csv 表沒有索引，是以通常在正 常操作期間将資料儲存在 innodb 表中，并且隻在導入或導出階段使用 csv 表。

      特點:不允許空行，不支援索引。格式通用，可以直接編輯，适合在不同資料庫之 間導入導出。

    • Archive

      這些緊湊的未索引的表用于存儲和檢索大量很少引用的曆史、存檔或安全審計資訊。 特點:不支援索引，不支援 update delete。

  • 如何選擇存儲引擎？
    • 如果對資料一緻性要求高，需要事務支援，可以選擇InnoDB
    • 如果資料查詢多更新少，對查詢性能要求比較高，可以選擇 MyISAM。
    • 如果需要一個用于查詢的臨時表，可以選擇 Memory。
    • 如果所有的存儲引擎都不能滿足你的需求,可以用C語言開發一個存儲引擎。
• 6.執行引擎：使用執行計劃操作存儲引擎，它利用存儲引擎提供的相應的API來完成操作。

更新語句：基本流程和查詢流程是一緻的，它也要經過解析器、優化器、執行器處理，差別在于拿到符合條件的資料之後的操作

簡化過程：

1.事務開始，從記憶體或磁盤讀取到這條資料，傳回給Server的執行器

2.将查詢到的行進行修改

3.将修改的結果更新到記憶體Buffer pool中

4.記錄 redo log ，并将記錄狀态置為prepare狀态

5.修改後，送出事務

6.server 層 寫入bin log 中

7.執行 commit

8.将 redo log裡的事務記錄置為 commit 狀态

• 1.緩沖池Buffer Pool

  InnooDB的資料都是放在磁盤上的，InnoDB 操作資料有一個最小的邏輯單 位，叫做頁(索引頁和資料頁)，我們對于資料的操作不是直接操作磁盤，因為磁盤的速度慢，InnoDB使用了一種緩沖池的技術，也就是把磁盤讀到的頁放到一塊記憶體區域裡

  下次讀取相同的頁，先判斷是不是在緩存池裡面，如果是直接讀取，不用再次通路磁盤。

  修改資料的時候，先修改緩沖池裡的頁。記憶體的資料頁和磁盤資料不一緻的時候，我們把它叫做髒頁。InnoDB裡面有專門的線程把Buffer Pool的資料寫入到磁盤，每隔一段時間就一次性的把多個修改寫入磁盤，這個動作叫做刷髒

  Buffer Pool 是 InnoDB 裡面非常重要的一個結構，它的内部又分成幾塊區域

  • Buffer Pool，預設128M，采用LRU算法來管理緩沖池（連結清單實作，分成來young和old），經過淘汰的資料就是熱點資料

    Buffer Pool 緩存的是頁面資訊，包括資料頁、索引頁。

  • Change Buffer 寫緩沖區，如果這個資料頁不是唯一索引，不存在資料重複的情況，可以先把修改記錄在記憶體的緩沖池中，進而提升更新語句（insert、delete、update）執行速度

    5.5 之前叫 Insert Buffer 插入緩沖，現在也能支 持 delete 和 update。

    最後把 Change Buffer 記錄到資料頁的操作叫做 merge。什麼時候發生 merge?

    有幾種情況:在通路這個資料頁的時候，或者通過背景線程、或者資料庫 shut down、 redo log 寫滿時觸發。

    如果資料庫大部分索引都是非唯一索引，并且業務是寫多讀少，不會在寫資料後立刻讀取，幾句可以使用Change Buffer，寫多讀少的業務，可以調大這個值

    innodb_change_buffer_max_size 預設占比 25%

  • Adaptive Hash Index：自适應哈希索引，對于一些熱點資料頁，InnoDB會自動建立自适應hash索引，在B+Tree索引的基礎上建立。

  • log buffer（redo log緩存區）：用來處理資料庫當機或者重新開機，這些資料丢失，如果寫到一半，甚至可能會破壞資料檔案導緻資料庫不可用。

    為了避免這個問題，InnoDB 把所有對頁面的修改操作專門寫入redo log，并且在資料庫啟動時從redo log進行恢複操作(實作 crash-safe)——用它來實作事務的持久性。

    預設2個檔案，每個48M，這種日志和磁盤配合的整個過程就是WAL（Write-Ahead Logging），它的關鍵點就是先寫日志，再寫磁盤。

    • 為什麼要先寫日志再寫磁盤？考慮 随機IO和順序IO，寫日志比較快

      我們先來了解一下随機 I/O 和順序 I/O 的概念。 磁盤的最小組成單元是扇區，通常是 512 個位元組。 作業系統和記憶體打交道，最小的機關是頁 Page。 作業系統和磁盤打交道，讀寫磁盤，最小的機關是塊 Block。

      刷盤是随機 I/O，而記錄日志是順序 I/O，順序 I/O 效率更高。是以先把修改寫入日志，可以延遲刷盤時機，進而提升系統吞吐。

      當然redo log也不是每一次都直接寫入磁盤，Log buffer專門用來儲存即将要寫入日志的資料，預設16M，它一樣可以接收磁盤IO

    • 什麼時候寫入redo log中？

      在我們寫入資料到磁盤的時候，作業系統本身是有緩存的。flush 就是把作業系統緩 沖區寫入到磁盤。log buffer 寫入磁盤的時機，由一個參數控制，預設是 1。

      0（延遲寫）：log buffer 将每秒一次的寫入log 中，并且log 的flush操作同時進行，該模式下，在事務送出的時候，不會主動出發寫入磁盤操作。

      1（預設，實時寫，實時刷）:每次事務送出時log buffer的資料寫入log中，并且刷到磁盤中

      2.(實時寫，延遲刷)：每次事務送出會把log buffer的資料寫入log中，但是flush 操作并不會同時進行，該模式下MySQL會每秒執行一次flush操作

  • 背景線程：重新整理buffer記憶體池中的資料到磁盤
    • master thread： 負責重新整理緩存資料到磁盤并協調排程其它背景程序。
    • IO thread：分為 insert buffer、log、read、write 程序。分别用來處理 insert buffer、 redo log、讀寫請求的 IO 回調。
    • purge thread：用來回收 undo 頁
    • page cleaner thread：用來重新整理髒頁。
• 2.redo log

  • redo log的特點

    1.redo log是InnoDB存儲引擎實作的，并不是所有存儲引擎都有

    2.不是記錄資料頁更新之後的狀态，而是記錄這個頁做了什麼改動，屬于實體日志。

    3.redo log大小是固定的，前面的内容會被覆寫，所有日志組成一個環形結構。

    當 write pos 和 check point相遇時說明寫滿，

2.MySQL體系結構

1.結構講解

• Connector：用來支援各種語言和SQL的互動
• Management Serveices & Utilities：系統管理和控制工具，包括備份恢複、MySQL複制、叢集等等
• Connection Pool：連接配接池，管理需要緩沖的資源，包括使用者密碼、權限、線程等
• SQL Interface：用來接收使用者的SQL指令，傳回使用者需要的查詢結果
• Parser：用來解析SQL語句
• Optimizer：查詢優化器
• Cache and Buffer：查詢存儲，除了行記錄的緩存之外，還有表緩存，Key緩存，權限緩存等
• Pluggable Storage Engines：插件式存儲引擎，它提供API給服務層使用

2.架構分層

• 連接配接層：管理連接配接，權限驗證

  用戶端要連接配接到MySQL伺服器的3306端口，必須要跟服務端建立連接配接，那麼管理所有的連接配接，驗證用戶端的身份和權限，這些功能就在連接配接層完成

• 服務層：語句分析->執行執行計劃->執行引擎->傳回結果

  連接配接層會把 SQL 語句交給服務層，這裡面又包含一系列的流程:

  比如查詢緩存的判斷、根據 SQL 調用相應的接口，對我們的 SQL 語句進行詞法和語 法的解析(比如關鍵字怎麼識别，别名怎麼識别，文法有沒有錯誤等等)。

  然後就是優化器，MySQL 底層會根據一定的規則對我們的 SQL 語句進行優化，最 後再交給執行器去執行。

• 存儲引擎層：存儲資料，提供讀寫接口

  存儲引擎就是我們的資料真正存放的地方，在 MySQL 裡面支援不同的存儲引擎。 再往下就是記憶體或者磁盤。

3.表空間分類

• 系統表空間：所有表共享系統表空間
  • 資料字典：由内部系統表組成，存儲表和索引的中繼資料

  • 雙寫緩沖區：我們不是有 redo log 嗎?但是有個問題，如果這個頁本身已經損壞了，用它來做崩潰恢複是沒有意義的。是以在對于應用 redo log 之前，需要一個頁的副本。如果出現了寫入失效，就用頁的副本來還原這個頁，然後再應用 redo log。這個頁的副本就是double write，InnoDB 的雙寫技術。通過它實作了資料頁的可靠性。

    InnoDB 的頁和作業系統的頁大小不一緻，InnoDB 頁大小一般為 16K，作業系統頁 大小為 4K，InnoDB 的頁寫入到磁盤時，一個頁需要分 4 次寫，如果存儲引擎正在寫入頁的資料到磁盤時發生當機，可能出現頁隻寫了一部分的情況，這種情況是部分寫失效，可能會導緻資料丢失。

    • 記憶體的double寫
    • 磁盤的double寫
  • Change Buffer
  • undo Logs：預設存放在系統表空間裡
• 獨占表空間：每張表會開辟一個表空間，這個檔案就是資料目錄下的 ibd 檔案，存放表的索引和資料
• 通用表空間
• 臨時表空間：存儲臨時表的資料，包括使用者建立的臨時表，和磁盤的内部臨時表

• undo log（撤銷日志/復原日志）：記錄了資料發生之前的資料狀态，如果修改時出現異常，可以用undo log 來實作復原操作。

  在執行undo 的時候，僅僅将資料從邏輯上恢複至事務之前的狀态，而不是從實體頁面的操作實作的，資料邏輯日志。

  redo log和undo log與事務相關，統稱為事務日志。

  undo log的資料預設在系統表空間 ibdata1中，因為共享表空間不會自動收縮，也可以單獨建立一個undo的表空間

  • 如何組織Undo Log 鍊

    詳細請參考https://blog.csdn.net/m0_37645820/article/details/89814582

4.資料庫完整性限制

• 1.域完整性：限制此單元格的資料正确，不對照此列的其他單元格比較（非空限制、預設值限制）
  • 1.非空限制
  • 2.預設值限制
• 2.實體完整性
  • 主鍵限制
  • 唯一限制
  • 自動增長列
• 3.參照完整性：表與表的關系，插入、更新、删除等操作都要與另外一張表對照，組織不正确的操作

3.Binlog：記錄了所有的 DDL 和 DML 語句

binlog 以事件的形式記錄了所有的 DDL 和 DML 語句(因為它記錄的是操作而不是 資料值，屬于邏輯日志)，可以用來做主從複制和資料恢複

跟redo log 不一樣，它的檔案内容是追加的，沒有大小限制。

1.導出成sql語句，把所有操作重新執行，實作資料恢複

2.實作主從複制，從主伺服器讀取bin log ，然後執行一遍

4.索引

1.什麼是索引：資料庫中的一個排序的資料結構，可以快速的查詢。

2.索引類型

• 普通索引：沒有任何限制
• 唯一索引：要求鍵值不能重複，主鍵是特殊的唯一索引

• 全文索引:針對比較大的資料，隻有文本類型的字段可以建立全文索引，比如char/varchar/text

  select * from table where match(content) against(‘content’ IN NATURAL LANGUAGE MODE);

3.索引結構（B+樹）

• 特點
  • 1.關鍵字的數量是跟樹相等的
  • 2.根節點和枝節點中都不會存儲資料，隻有葉子節點才存儲資料
  • 3.資料節點相連，形成一條有序的連結清單
  • 4.它時根據左閉右開[ )來檢索資料的

• 計算一個Page存儲多少資料

  假設一條記錄是1K，一個葉子節點可以存儲16條資料，非葉子節點可以存儲多少哥指針？

  假設索引字段是bigint，長度為8位元組，指針大小在innoDB 源碼中為6位元組，這樣一共14位元組。

  非葉子節點（1頁）存儲16384/14=1170個單元，代表1170個指針。

  樹深度為2的時候，共有11701170個葉子節點

  可以存儲的資料為11701170*16，在查詢資料時，一次頁的查找代表一次IO，也就是說，一張2000萬左右的表查詢最多需要3次IO。

  • 假設索引字段是bigint
    • 假設索引字段是bigint

4.索引存儲選型樹

• B+樹存儲的特點
  • 1.它是 B Tree 的變種，B Tree 能解決的問題，它都能解決。B樹解決了每個節點存儲更多關鍵字;路數更多的問題
  • 2.掃庫、掃表能力更強，周遊資料節點的指針就可以了
  • 3.磁盤讀寫能力相對于B 樹來說更強，根節點和葉子節點不儲存資料，是以一個節點可以存儲更多的關鍵字，加載的關鍵字也多
  • 4.排序能力更強
  • 5.效率穩定，葉子節點存儲資料，IO次數穩定
• 為什麼不用紅黑樹？
  • 1.隻有兩路
  • 2.不夠平衡

5.索引方式

• Hash

  • 特點

    它的時間複雜度是 O(1)，查詢速度比較快。因為哈希索引裡面的資料不是 按順序存儲的，是以不能用于排序。

    查詢的資料會根據鍵值計算hash碼，是以隻能支援等值查詢，不支援範圍查詢

    如果字段重複很多的時候，會有大量的哈希沖突（拉連結清單法解決），降低查詢效率

  • 如何建立

    InnoDB 隻支援顯式建立 B+Tree 索引，對于一些熱點資料頁， InnoDB 會自動建立自适應 Hash 索引也就是在 B+Tree 索引基礎上建立 Hash 索引， 這個過程對于用戶端是不可控制的，隐式的。

• B+Tree隻能顯式建立

6.實作形式

• MySQL架構：MySQL是一個支援插件式存儲的資料庫，每個表都可以指定不用的存儲引擎，最常用的是MyISAM，InnoDB
• 資料存儲檔案：MySQL資料是以檔案的形式存儲在磁盤中的，目錄在 datadir參數可以查詢到，每個資料庫都有一個目錄，每個表都有.frm檔案(存儲表結構)
  • MyISAM
    • .MYD 存放資料
    • .MYI 存放索引
    • 特點：輔助索引和主鍵索引存儲和檢索資料的方式沒有任何差別，是索引檔案裡面找到磁盤位址，然後到資料檔案裡擷取資料
  • innoDB
    • .ibd 存放資料與索引

    • 特點：以主鍵為索引來組織資料存儲的，索引檔案和資料檔案是一個檔案，資料存儲在葉子節點上。輔助索引存儲的值是主鍵的内容，可能需要回表二次檢索，如果沒有主鍵，那麼會選擇第一個不包含NULL值的唯一索引作為主鍵，如果沒有則會選擇内置6位元組長的RowId作為隐藏的聚集索引，他會随着記錄而主鍵子增

      為什麼在輔助索引裡面存儲的是主鍵值而不是主鍵的磁盤位址呢?如果主鍵的資料 類型比較大，是不是比存位址更消耗空間呢?

      1.是因為有分裂和合并的操作，這個時候鍵值的位址會發生變化，還需要将輔助索引進行同步。

      2.InnoDB本身的以主鍵為主排序的表，對InnoDB來講，其在磁盤的順序已經由主鍵的順序決定了，不能在按照其他順序排序。

7.聚集索引：索引的邏輯順序與表資料行的實體存儲順序一緻

比如字典的目錄 是按拼音排序的，内容也是按拼音排序的，按拼音排序的這種目錄就叫聚集索引，按照部首的就不是聚集索引。

在 InnoDB 裡面，它組織資料的方式叫做叫做(聚集)索引組織表，是以主鍵索引是聚集索引，非主鍵都是非聚集索引。

8.索引使用原則

• 1.列的離散度：列的全部不同值和所有資料行的比例，離散度越大越适合建立索引。

  如果在 B+Tree 裡面的重複值太多，MySQL 的優化器發現走索引跟使用全表掃描差

  不了多少的時候，就算建了索引，也不一定會走索引。

• 2.聯合索引最左比對原則：建立聯合索引一定把最常用的列放在左邊
• 3.覆寫索引：使用輔助索引查詢，查詢資料列隻用從索引中就能取得，不必從資料區中讀取，這時候使用的索引就是覆寫索引，避免了回表操作

• 4.索引條件下推(ICP)

  索引的比較是在存儲引擎進行的，資料記錄的比較是在Server層進行的

  隻适用于二級索引

  • 開啟索引下推：set optimizer_switch=‘index_condition_pushdown=on’;

  • 使用的場景

    1.對于二級索引

    2.select的列不使用覆寫索引

    3.多條件查詢（where中多條件，where + order by）+聯合索引

    • 1.過濾like的模糊比對
    • 2.進行聯合索引的排序
• 5.索引上不能使用函數，會導緻索引失效

9.索引的建立原則

• 1.用于 where 判斷 order 排序和 join 的(on)字段上建立索引
• 2.索引的個數不要過多——浪費空間，更新變慢
• 3.區分度低的字段，例如性别，不要建索引——離散度太低，導緻掃描行數過多。
• 4.頻繁更新的值，不要作為主鍵或者索引——頁分裂
• 5.組合索引把散列性高(區分度高)的值放在前面
• 6.建立複合索引，而不是修改單列索引。
• 7.過長的字段，建立字首索引——字首的離散度盡可能符合要求
• 8.不建議用無序的值作為聚集索引——會産生存儲的碎片化

10.什麼時候用不到索引

• 1.索引列上使用函數、表達式、計算（±*/）
• 2.查詢時索引類型被隐式轉換，比如索引是string類型，查詢時用int類型
• 3.like條件中前面帶%
• 4.負向查詢，Not like 不能，<>和NOT IN在某些情況下可以
• 5.有些情況也不可以使用索引，由于是基于cost的，用不用索引最終由優化器說了算。

5.語句優化

1.主要是索引相關，盡量用到索引

2.in與exists差別

• in：優先查詢子查詢，再查詢主查詢，适用于子查詢資料量小

  SELECT * FROM A WHERE id IN (SELECT id FROM B);

  以上in()中的查詢隻執行一次，它查詢出B中的所有的id并緩存起來，然後檢查A表中查詢出的id在緩存中是否存在，如果存在則将A的查詢資料加入到結果集中，直到周遊完A表中所有的結果集為止。

• exists：優先查詢主查詢，主查詢的資料集去子查詢exists中去尋找，如果存在添加到結果集

  SELECT * FROM a WHERE EXISTS(SELECT 1 FROM b WHERE B.id = A.id);

  EXISTS()查詢會執行SELECT * FROM A查詢，執行A.length次，并不會将EXISTS()查詢結果結果進行緩存，因為EXISTS()查詢傳回一個布爾值true或flase，它隻在乎EXISTS()的查詢中是否有記錄，與具體的結果集無關。

  EXISTS()查詢是将主查詢的結果集放到子查詢中做驗證，根據驗證結果是true或false來決定主查詢資料結果是否得以儲存。

6.事務:資料庫管理系統執行過程中的一個邏輯機關，由一個或者多個操作構成。

1.事務的特性

• 原子性：事務要麼成功，要麼失敗（利用undo 日志來實作復原操作）

  以轉賬的場景為例，一個賬戶的餘額減少，對應一個賬戶的增加，這兩個一 定是同時成功或者同時失敗的。

• 一緻性：事務前後資料的完整性必須保持一緻
• 隔離性：多個使用者并發通路庫，每個使用者開啟一個事務，不能被其他事務的操作所幹擾
• 持久性：事務一旦被送出，它對資料庫中資料的改變就是永久性的，通過redo log 和 double write雙寫緩沖來實作的，double write實作了資料庫崩潰，資料頁本身不會被破壞

2.事務隔離級别

• 未送出讀（read-uncommitted）髒讀、不可重複讀、幻讀都不可避免
• 送出讀（read-committed）不可重複讀、幻讀都不可避免
• 可重複讀（repeatable-read）
• 串行化（serializable）

3.事務并發帶來的問題

總結：無論是髒讀、不可重複讀、幻讀，他們都是資料庫的讀一緻性文帝，都是在一個事務裡面前後兩次讀取結果出現不一緻情況，目前有兩種解決方案，LBCC （基于鎖的并發控制）：僅僅通過鎖來實作事務隔離，意味着不支援并發的讀寫操作，會極大影響操作資料的效率；MVCC（多版本并發控制）核心思想：可以查到這個事務開始之前已經存在的資料，即使後面修改或删除，目前事務查詢結果都是一樣的。在這個事務之後的新增資料，是查不到的。

• 髒讀

  一個事務裡面，由于其他的時候修改了 資料并且沒有送出，而導緻了前後兩次讀取資料不一緻的情況，這種事務并發的問題

• 不可重複讀

  一個事務讀取到了其他事務已送出的資料導緻前後兩次讀取資料不一緻的情況

• 幻讀

  一個事務前後兩次讀取資料資料不一緻，是由于其他事務插入資料造成的

4.事務并發帶來問題的解決方案

• MVCC：undo log 實作的

  我可以查到在我這個事務開始之前已經存在的資料，即使它 在後面被修改或者删除了。在我這個事務之後新增的資料，我是查不到的。

  • 解決方案：InnoDB為每行記錄都實作了兩個隐藏的字段，DB_TRX_ID 6位元組，事務編号，記錄操作的事務ID；DB_ROLL_PTR 7位元組 ，復原指針 資料被删除活記錄為舊資料的時候，記錄目前事務ID

  • 案例

    https://www.processon.com/view/link/5d29999ee4b07917e2e09298 MVCC 示範圖

    • 1.隻能查找版本号小于等于目前事務版本的資料行，這樣可以確定事務讀取的行，要麼是在事務開始前已經存在的，要麼是事務自身插入或者修改過的。
    • 2.行的删除版本要麼未定義，要麼大于目前事務版本号，這可以確定事務讀取到的行，在事務開始之前未被删除

InnoDB 鎖

鎖的粒度

• 行鎖：鎖住一張表
• 表鎖：鎖住一行記錄

鎖的類型

• 共享鎖（Shared Locks）
• 排它鎖（Exclusive Locks）
  • 自動加排它鎖，比如增删改都會加排它鎖
  • 手動加排它鎖，for update
• 意向鎖
  • 如果一張表上面至少有一個意向共享鎖，說明有其他的事務給其中的某些資料行加上了共享鎖。
  • 如果一張表上面至少有一個意向排他鎖，說明有其他的事務給其中的某些資料行加上了排他鎖。

行鎖的原理

• 沒有索引的表、沒有非NULL唯一鍵限制：會進行全表掃描，然後把每一個隐藏的聚集索引（ROW_ID，6位元組）都鎖住
• 有索引的表
  • 聚簇索引:直接通過索引鎖定資料行
  • 其他索引:找到存儲的主鍵id（顯式ID與隐式ID），在通過主鍵鎖定資料行

鎖的算法

• 記錄鎖（Record Lock）：當我們對于唯一性的索引(包括唯一索引和主鍵索引)使用等值查詢，精準比對到一條記錄的時候，這個時候使用的就是記錄鎖，比如where id=1
• 間隙鎖（Gap Lock）：當我們查詢的記錄不存在，沒有命中任何一個 record，無論是用等值 查詢還是範圍查詢的時候，它使用的都是間隙鎖。

  • 關閉間隙鎖

    如果要關閉間隙鎖，就是把事務隔離級别設定成 RC，并且innodb_locks_unsafe_for_binlog 設定為 ON。

  • 左開右開的區間範圍
• 臨鍵鎖（Next-key Lock）：當我們使用了範圍查詢，不僅僅命中了 Record 記錄，還包含了 Gap 間隙，在這種情況下我們使用的就是臨鍵鎖
  • 左開右閉的區間範圍

鎖的退化

• 1.唯一性索引等值查詢比對到一條記錄的時候，退化成記錄鎖。
• 2.沒有比對到任何記錄的時候，退化成間隙鎖

鎖與事務隔離級别的關系

• 事務隔離級别
  • 未送出讀（read-uncommitted）（不加鎖）
  • 送出讀（read-committed）
    • 普通的select都是快照讀，使用MVCC實作
    • 加鎖的 select 都使用記錄鎖，因為基本不用 Gap Lock。
    • 外鍵限制檢查以及重複鍵檢查時會使用間隙鎖（Gap Lock）
  • 可重複讀（repeatable-read）
    • 普通的select都是快照讀，使用MVCC實作
    • 加鎖的select以及更新操作等語句使用目前讀，底層使用記錄鎖、間隙所、臨鍵鎖
  • 串行化（serializable）
    • 所有的 select 語句都會被隐式的轉化為in share mode,會和update、delete等操作互斥

ReadView

• 在RU隔離級别下，直接讀取版本最新的記錄。
• 在RR隔離級别下，每個事務開始時，會将目前系統中所有的活躍事務拷貝到一個清單生成ReadView
• 在RC隔離級别下，每個語句開始時，會将目前系統中的所有活躍事務拷貝到一個清單生成ReadView

關注公衆号，每周都有新内容

擷取腦圖請回複公衆号“mysql”擷取PDF版本腦圖，需要補充的知識點可以進行留言，逐漸進行完善。

最後感謝大家的關注