MySQL知識點串講【适用于中進階開發】-持續更新

目錄

• 總述
• • • 01 | 基礎架構：一條SQL查詢語句是如何執行的？
    • 02 | 日志系統：一條SQL更新語句是如何執行的？
    • 03 | 事務隔離：為什麼你改了我還看不見？
    • 04 | 深入淺出索引（上）
    • 05 | 深入淺出索引（下）
    • 06 | 全局鎖和表鎖 ：給表加個字段怎麼有這麼多阻礙？
    • 07 | 行鎖功過：怎麼減少行鎖對性能的影響？
    • 08 | 事務到底是隔離的還是不隔離的？
    • 09 | 普通索引和唯一索引，應該怎麼選擇？
    • 10 | MySQL為什麼有時候會選錯索引？
    • 11 | 怎麼給字元串字段加索引？
    • 12 | 為什麼我的MySQL會“抖”一下？
    • 13 | 為什麼表資料删掉一半，表檔案大小不變？
    • 14 | count(*)這麼慢，我該怎麼辦？
    • 15 | 答疑文章（一）：日志和索引相關問題
    • 16 | “order by”是怎麼工作的？
    • 17 | 如何正确地顯示随機消息？
    • 18 | 為什麼這些SQL語句邏輯相同，性能卻差異巨大？
    • 19 | 為什麼我隻查一行的語句，也執行這麼慢？
    • 20 | 幻讀是什麼，幻讀有什麼問題？
    • 21 | 為什麼我隻改一行的語句，鎖這麼多？
    • 22 | MySQL有哪些“飲鸩止渴”提高性能的方法？
    • 23 | MySQL是怎麼保證資料不丢的？
    • 24 | MySQL是怎麼保證主備一緻的？
    • 25 | MySQL是怎麼保證高可用的？
    • 26 | 備庫為什麼會延遲好幾個小時？
    • 27 | 主庫出問題了，從庫怎麼辦？
    • 28 | 讀寫分離有哪些坑？
    • 29 | 如何判斷一個資料庫是不是出問題了？
    • 30 | 答疑文章（二）：用動态的觀點看加鎖
    • 31 | 誤删資料後除了跑路，還能怎麼辦？
    • 32 | 為什麼還有kill不掉的語句？
    • 33 | 我查這麼多資料，會不會把資料庫記憶體打爆？
    • 34 | 到底可不可以使用join？
    • 35 | join語句怎麼優化？
    • 36 | 為什麼臨時表可以重名？
    • 37 | 什麼時候會使用内部臨時表？
    • 38 | 都說InnoDB好，那還要不要使用Memory引擎？
    • 39 | 自增主鍵為什麼不是連續的？
    • 40 | insert語句的鎖為什麼這麼多？
    • 41 | 怎麼最快地複制一張表？
    • 42 | grant之後要跟着flush privileges嗎？
    • 43 | 要不要使用分區表？
    • 44 | 答疑文章（三）：說一說這些好問題
    • 45 | 自增id用完怎麼辦？
    • 應用
    • 總結

總述

01 | 基礎架構：一條SQL查詢語句是如何執行的？

• MySQL 可以分為 Server 層和存儲引擎層兩部分。
• Server 層：包括連接配接器、查詢緩存、分析器、優化器、執行器等，涵蓋 MySQL 的大多數核心服務功能，以及所有的内置函數（如日期、時間、數學和加密函數等），所有跨存儲引擎的功能都在這一層實作，比如存儲過程、觸發器、視圖等。
  • 連接配接器：
    • 用戶端如果太長時間沒操作，連接配接器就會自動将它斷開。這個時間是由參數 wait_timeout 控制的，預設值是 8 小時。
    • 因為連接配接過程非常複雜（三次握手、登陸驗證、權限驗證等），是以建議使用長連接配接。
    • 全部使用長連接配接有些時候 MySQL 占用記憶體漲得特别快，這是因為 MySQL 在執行過程中臨時使用的記憶體是管理在連接配接對象裡面的。這些資源會在連接配接斷開的時候才釋放。是以如果長連接配接累積下來，可能導緻記憶體占用太大，被系統強行殺掉（OOM），從現象看就是 MySQL 異常重新開機了。解決辦法：
      • 定期斷開長連接配接。使用一段時間，或者程式裡面判斷執行過一個占用記憶體的大查詢後，斷開連接配接，之後要查詢再重連。
      • 如果你用的是 MySQL 5.7 或更新版本，可以在每次執行一個比較大的操作後，通過執行 mysql_reset_connection 來重新初始化連接配接資源。這個過程不需要重連和重新做權限驗證，但是會将連接配接恢複到剛剛建立完時的狀态。
  • 查詢緩存：不建議使用。因為對一個表格的更新會将這個表格的所有緩存失效。MySQL 8.0 版本直接将查詢緩存的整塊功能删掉了
  • 分析器：進行詞法分析和文法分析。
  • 優化器：優化器是在表裡面多個索引的時候，決定使用哪個索引；或者一個語句有多個關聯語句的時候，決定各個表的連接配接順序。優化器階段完成後語句的執行方案就确定下來了，生成執行計劃。
  • 執行器：MySQL會先進行權限校驗，然後根據生成的執行計劃調用存儲引擎的API進行資料操作。
• 存儲引擎層：負責資料的存儲和提取。其架構模式是插件式的，支援 InnoDB、MyISAM、Memory 等多個存儲引擎。現在最常用的存儲引擎是 InnoDB，它從 MySQL 5.5.5 版本開始成為了預設存儲引擎。也就是說，你執行 create table 建表的時候，如果不指定引擎類型，預設使用的就是 InnoDB。不過，你也可以通過指定存儲引擎的類型來選擇别的引擎，比如在 create table 語句中使用 engine=memory, 來指定使用記憶體引擎建立表。

02 | 日志系統：一條SQL更新語句是如何執行的？

• MySQL中兩個重要的日志系統redo log和bin log比較：
  • redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 層實作的，所有引擎都可以使用。
  • redo log 是實體日志，記錄的是“在某個資料頁上做了什麼修改”；binlog 是邏輯日志，記錄的是這個語句的原始邏輯，比如“給 ID=2 這一行的 c 字段加 1 ”。
  • redo log 是循環寫的，空間固定會用完；binlog 是可以追加寫入的。“追加寫”是指 binlog 檔案寫到一定大小後會切換到下一個，并不會覆寫以前的日志。
  • bin log有兩種模式，statement 格式的話是記sql語句， row格式會記錄行的内容，記兩條，更新前和更新後都有。
  • redo log循環寫入示意圖：

    

• 執行器和 InnoDB 引擎在執行 update 語句時的内部流程。【update T set c=c+1 where ID=2;】
  • 執行器先找引擎取 ID=2 這一行。ID 是主鍵，引擎直接用樹搜尋找到這一行。如果 ID=2 這一行所在的資料頁本來就在記憶體中，就直接傳回給執行器；否則，需要先從磁盤讀入記憶體，然後再傳回。
  • 執行器拿到引擎給的行資料，把這個值加上 1，比如原來是 N，現在就是 N+1，得到新的一行資料，再調用引擎接口寫入這行新資料。
  • 引擎将這行新資料更新到記憶體中，同時将這個更新操作記錄到 redo log 裡面，此時 redo log 處于 prepare 狀态。然後告知執行器執行完成了，随時可以送出事務。
  • 執行器生成這個操作的 binlog，并把 binlog 寫入磁盤。
  • 執行器調用引擎的送出事務接口，引擎把剛剛寫入的 redo log 改成送出（commit）狀态，更新完成。
  • 示意圖

    

• 如何根據日志檔案進行資料恢複？
  • 首先，找到最近的一次全量備份，如果你運氣好，可能就是昨天晚上的一個備份，從這個備份恢複到臨時庫；
  • 然後，從備份的時間點開始，将備份的 binlog 依次取出來，重放到中午誤删表之前的那個時刻。
  • 這樣你的臨時庫就跟誤删之前的線上庫一樣了，然後你可以把表資料從臨時庫取出來，按需要恢複到線上庫去。
• 小結
  • redo log 用于保證 crash-safe 能力。innodb_flush_log_at_trx_commit 這個參數設定成 1 的時候，表示每次事務的 redo log 都直接持久化到磁盤。
  • sync_binlog 這個參數設定成 1 的時候，表示每次事務的 binlog 都持久化到磁盤。
  • 兩階段送出是跨系統維持資料邏輯一緻性時常用的一個方案。

03 | 事務隔離：為什麼你改了我還看不見？

• 下面以innaDB存儲引擎為例
• 事務的四大特性：ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一緻性、隔離性、持久性）
• 事務的隔離級别：讀未送出（read uncommitted）、讀送出（read committed）、可重複讀（repeatable read）和串行化（serializable ）
  • 讀未送出是指，一個事務還沒送出時，它做的變更就能被别的事務看到。
  • 讀送出是指，一個事務送出之後，它做的變更才會被其他事務看到。
  • 可重複讀是指，一個事務執行過程中看到的資料，總是跟這個事務在啟動時看到的資料是一緻的。當然在可重複讀隔離級别下，未送出變更對其他事務也是不可見的。
  • 串行化，顧名思義是對于同一行記錄，“寫”會加“寫鎖”，“讀”會加“讀鎖”。當出現讀寫鎖沖突的時候，後通路的事務必須等前一個事務執行完成，才能繼續執行。
• 事務隔離級别使用場景
  • 假設你在管理一個個人銀行賬戶表。一個表存了賬戶餘額，一個表存了賬單明細。到了月底你要做資料校對，也就是判斷上個月的餘額和目前餘額的差額，是否與本月的賬單明細一緻。你一定希望在校對過程中，即使有使用者發生了一筆新的交易，也不影響你的校對結果。這時候使用“可重複讀”隔離級别就很友善。事務啟動時的視圖可以認為是靜态的，不受其他事務更新的影響。
• 事務隔離級别的實作【以可重複讀為例】
  • 每次更新都将更新内容進行記錄
  • 假設三個事務A、B、C分别将值從1依次改為了2、3、4，復原日志中會進行如下記錄（類似于GIT等版本控制工具，将每次的修改記錄下來）

    

  • 當read-view A需要讀取1時，需要将目前值4依次執行圖中的復原操作得到
  • 復原日志如何回收呢？沒有其它事物線程還在使用目前版本的undo時候，purge程序進行回收。
  • 是以盡量避免使用長事務

04 | 深入淺出索引（上）

請參考文章：學習MySql索引的資料結構，隻需這一篇就夠了

05 | 深入淺出索引（下）

請參考文章：學習MySql索引的資料結構，隻需這一篇就夠了

知識點補充：

• 自增主鍵防止頁分裂，邏輯删除并非實體删除防止頁合并
• 因為InnoDB是聚簇索引，是以要盡量減小主鍵的大小
• 要盡量通過覆寫索引減少回表次數
• MySQL 5.6版本之後引入了索引下推，當一個表格建有（name，age）的聯合索引時通過索引下推優化後的樣子如圖：
  • SQL為：

    select * from tuser where name like '張%' and age=10

    

06 | 全局鎖和表鎖 ：給表加個字段怎麼有這麼多阻礙？

• 全局鎖典型的應用場景是做全庫的邏輯備份，當然也可以通過事務來做，但是不是所有引擎都支援事務
• 表鎖一般是在資料庫引擎不支援行鎖的時候才會被用到的。如果你發現你的應用程式裡有 lock tables 這樣的語句，你需要追查一下，比較可能的情況是：
  • 要麼是你的系統現在還在用 MyISAM 這類不支援事務的引擎，那要安排更新換引擎；
  • 要麼是你的引擎更新了，但是代碼還沒更新。我見過這樣的情況，最後業務開發就是把 lock tables 和 unlock tables 改成 begin 和 commit，問題就解決了。

07 | 行鎖功過：怎麼減少行鎖對性能的影響？

• 兩階段鎖協定：在 InnoDB 事務中，行鎖是在需要的時候才加上的，但并不是不需要了就立刻釋放，而是要等到事務結束時才釋放。
• 如果你的事務中需要鎖多個行，要把最可能造成鎖沖突、最可能影響并發度的鎖盡量往後放。
• 死鎖和死鎖監測
• 解決死鎖的政策：
  • 直接進入等待，直到逾時。逾時時間可以通過參數 innodb_lock_wait_timeout 來設定【該方法不好控制逾時時間的設定，太短容易誤傷，太長體驗不好】
  • 進行死鎖檢測，發現死鎖後，主動復原死鎖鍊條中的某一個事務，讓其他事務得以繼續執行，将參數innodb_deadlock_detect 設定為 on，表示開啟這個邏輯。【當高并發同時更新同一行時，死鎖監測成本很高，每次監測時間複雜度都是O(n)級别】
  • 控制并發度：
    • 可以通過中間件實作或者修改MySQL源碼，讓相同更新在引擎之前排隊
    • 或者将高頻更新的資料拆解為多行記錄，例如将高頻修改的賬戶分為多條資料存儲，這樣并發請求就降低了。不過需要在業務中進行相應的控制

08 | 事務到底是隔離的還是不隔離的？

• begin/start transaction 指令并不是一個事務的起點，在執行到它們之後的第一個操作 InnoDB 表的語句，事務才真正啟動。如果你想要馬上啟動一個事務，可以使用 start transaction with consistent snapshot 這個指令。
• 在可重複讀隔離級别下，事務在啟動的時候會基于整庫拍一個快照
• 快照工作原理
  • 每個事務在開始的時候都會向InnoDB事務系統中申請一個transaction id，該id是按照申請順序嚴格遞增的
  • 每次事務更新資料的時候，都會生成一個新的資料版本，并且把 transaction id 指派給這個資料版本的事務 ID，記為 row trx_id【資料表中的一行記錄，其實可能有多個版本 (row)，每個版本有自己的 row trx_id】
  • 一個記錄被多個事務連續更新後的狀态如圖

    

  • 注意：V1-V4四個版本中真實存在的為V4，U1-U3三個復原操作會記錄到undo log（復原日志）中，當需要之前的版本資料時會根據目前資料以及復原日志計算出來
  • 可重複讀隔離級别下的事務，會以自己啟動的時刻為準，如果一個資料版本是在啟動之後生成的，就不認（不可見），判斷基準是根據目前最新資料的row trx_id和自己啟動時的transaction id進行比較判斷
• 更新邏輯
  • 更新資料都是先讀後寫的，而這個讀，隻能讀目前的值，稱為“目前讀”（current read）。
  • 即所有更新都是在目前資料版本的基礎上進行的更新，而不是事務啟動時的快照。
  • 除了 update 語句外，select 語句如果加鎖，也是目前讀。
• 表結構不支援“可重複讀”：這是因為表結構沒有對應的行資料，也沒有 row trx_id，是以隻能遵循目前讀的邏輯。MySQL 8.0 已經可以把表結構放在 InnoDB 字典裡了，也許以後會支援表結構的可重複讀。
• 請分析下面【事務 B 查到的 k 的值是 3，而事務 A 查到的 k 的值是 1】的原因

CREATE TABLE `t` ( `id` int(11) NOT NULL, `k` int(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);
           

09 | 普通索引和唯一索引，應該怎麼選擇？

• 查詢過程兩者差異（根據索引列等值查找）
  • 普通索引在找到滿足條件的記錄後，需要查找下一條記錄，直到遇到不滿足的值為止；
  • 唯一索引因為定義了唯一性，會停止繼續建索
  • 性能差異對比：基本沒有差異，因為InnoDB的資料是按照資料頁為機關進行讀寫的，是以普通索引需要查找的後續資料，一般是會一起加載到記憶體中的
• 更新過程操作過程
  • change buffer：當需要更新一個資料頁時，如果資料頁在記憶體中就直接更新，而如果這個資料頁還沒有在記憶體中的話，在不影響資料一緻性的前提下，InnoDB 會将這些更新操作緩存在 change buffer 中，這樣就不需要從磁盤中讀入這個資料頁了。
  • merge：将change buffer中的操作更新到原資料頁稱為merge。
  • merge的觸發條件：1.當查詢該資料頁的時候。2. 背景空閑線程自動merge。3. 資料庫正常shutdown的時候
• 唯一索引和普通索引都可以使用change buffer嗎？
  • 唯一索引不可以，普通索引可以使用change buffer
  • 因為當開始change buffer後，需要更新的資料不在記憶體中才會寫入change buffer，而唯一索引需要将資料加載到記憶體判斷唯一性，是以更新時不能使用change buffer
• 更新過程兩者差異（根據索引列等值查找）【包括插入新資料】
  • 經過前面分析得知，更新過程需要分兩種情況考慮。需要更新的資料頁在記憶體中和不在記憶體中兩種情況
  • 資料頁在記憶體中幾乎沒有差異，隻是唯一索引多了一些判斷
  • 資料頁不在記憶體中：
    • 當更新操作結束【很快】需要進行查詢時：開啟change buffer的普通索引性能【差】，因為很快觸發了merge
    • 當更新操作結束【好久才】需要進行查詢時：開啟change buffer的普通索引性能【好】，因為不需要讀取資料頁進行磁盤IO
    • 關閉change buffer普通索引和唯一索引幾乎沒什麼差異
  • change buffer和redo log差別與聯系
    • // TODO之後找大塊時間來整理
  • 總結，兩種索引如何選擇
    • 業務正确性優先。如果業務不能保證，或者業務就是要求資料庫來做限制，那麼沒得選，必須建立唯一索引。這種情況文章的意義在于，如果碰上了大量插入資料慢、記憶體命中率低的時候，可以給你多提供一個排查思路。
    • 然後，在一些“歸檔庫”的場景，你是可以考慮使用普通索引的。比如，線上資料隻需要保留半年，然後曆史資料儲存在歸檔庫。這時候，歸檔資料已經是確定沒有唯一鍵沖突了。要提高歸檔效率，可以考慮把表裡面的唯一索引改成普通索引。

10 | MySQL為什麼有時候會選錯索引？

• 一張表上可能會建有多個索引，我們在寫SQL并沒有指定索引，這樣選擇哪個索引是由優化器來決定的
• 優化器選擇索引的目的是找到最優的執行方案，參考内容包括但不限于：預計掃描行、CPU資源消耗、是否使用臨時表、是否排序等
• 掃描行數是如何判斷的呢？MySQL通過采樣統計擷取索引的區分度來，再根據區分度來預估行數。而資料表是會持續更新的，索引統計資訊也不會固定不變。是以，當變更的資料行數超過一定數量的時候，會自動觸發重新做一次索引統計。
• 像并發删除修改之類的操作很容易造成選擇索引錯誤
  • 假如一個事務查詢，另一個事務仍未送出，是以删除的資料隻是标記删除，資料仍然在資料頁中，後插入的資料需要找新的空位插入，這樣查詢時會掃描删除的資料+後插入的資料，同時算上回表掃描主鍵索引，是以比正常多好多
• 索引選擇錯誤的處理
  • force index 強行選擇一個索引【但不建議這麼做，因為一來這麼寫不優美，二來如果索引改了名字，這個語句也得改，顯得很麻煩。而且如果以後遷移到别的資料庫的話，這個文法還可能會不相容。】
  • 我們可以考慮修改語句，引導 MySQL 使用我們期望的索引。
  • 在有些場景下，我們可以建立一個更合适的索引，來提供給優化器做選擇，或删掉誤用的索引。

11 | 怎麼給字元串字段加索引？

• 常見方式有
  • 直接建立完整索引，這樣可能比較占用空間；
  • 建立字首索引，節省空間，但會增加查詢掃描次數，并且不能使用覆寫索引；
  • 倒序存儲，再建立字首索引，用于繞過字元串本身字首的區分度不夠的問題；
  • 建立 hash 字段索引，查詢性能穩定，有額外的存儲和計算消耗，跟第三種方式一樣，都不支援範圍掃描。在實際應用中，你要根據業務字
• 具體通過那種方式建立索引需要根據業務和成本效益來決定

12 | 為什麼我的MySQL會“抖”一下？

• 你的SQL語句為什麼變慢了？
  • InnoDB的redo log寫滿了，這個時候系統會停止所有更新操作，将部分redo log日志flush磁盤上
  • 記憶體不夠了，必須淘汰舊的資料頁釋放記憶體才可加載新的資料頁到記憶體中
    • 當淘汰的資料頁是幹淨的資料頁時直接釋放就行，不會有太大性能影響
    • 當需要淘汰大量髒資料頁時，因為需要flush髒頁，是以SQL執行會變慢
      • 髒頁是更新資料時更新記憶體，而引起的記憶體與磁盤不一緻的時，此時的資料頁為髒頁
• InnoDB刷髒頁的控制政策
  • 需要告訴InnoDB所在主機的IO能力，控制參數為：innodb_io_capacity
  • 通過fio測試工具擷取的方式為：

    fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest 
               

  • InnoDB 的刷盤速度就是要參考這兩個因素：一個是髒頁比例，一個是 redo log 寫盤速度。因為如果刷太慢，會出現記憶體髒頁太多，以及 redo log 寫滿。
  • 參數innodb_max_dirty_pages_pct 是髒頁比例上限，預設值是 75%

13 | 為什麼表資料删掉一半，表檔案大小不變？

• 知識拓展：
  • 一個 InnoDB 表包含兩部分，即：表結構定義和資料。
  • 在 MySQL 8.0 版本以前，表結構是存在以.frm 為字尾的檔案裡。
  • 而 MySQL 8.0 版本，則已經允許把表結構定義放在系統資料表中了。
  • 因為表結構定義占用的空間很小，是以我們今天主要讨論的是表資料。
  • 參數：innodb_file_per_table
    • 設定為 OFF，表的資料放在系統共享表空間，也就是跟資料字典放在一起；
    • 設定為 ON，每個 InnoDB 表資料存儲在一個以 .ibd 為字尾的檔案中。
    • 從 MySQL 5.6.6 版本開始，它的預設值就是 ON 。
    • 建議設定為NO，因為一個表單獨存儲為一個檔案更容易管理
  • 基于innodb_file_per_table 設定為NO的資料删除流程
    • 删除資料InnoDB隻做了删除辨別，并未做真正的删除，當在删除的範圍内插入新的資料時，會重用删除的位置
    • 當整個資料頁被删除時，可以被複用到任何位置
  • 删除資料會造成資料頁的空洞，同樣在中間插入資料也會造成資料頁的空洞，因為會造成頁分裂
  • 重建表
    • 當需要對表進行空間收縮時需要重建表
    • MySQL5. 6之後的重建表流程【Online DDL】
      • 建立一個臨時檔案，掃描表 A 主鍵的所有資料頁；
      • 用資料頁中表 A 的記錄生成 B+ 樹，存儲到臨時檔案中；
      • 生成臨時檔案的過程中，将所有對 A 的操作記錄在一個日志檔案（row log）中，對應的是圖中 state2 的狀态；
      • 臨時檔案生成後，将日志檔案中的操作應用到臨時檔案，得到一個邏輯資料上與表 A 相同的資料檔案，對應的就是圖中 state3 的狀态；
      • 用臨時檔案替換表 A 的資料檔案。

        

14 | count(*)這麼慢，我該怎麼辦？

• 在不同的 MySQL 引擎中，count(*) 有不同的實作方式【未加過濾條件where】
  • MyISAM 引擎把一個表的總行數存在了磁盤上，是以執行 count(*) 的時候會直接傳回這個數，效率很高；
  • 而 InnoDB 引擎就麻煩了，它執行 count(*) 的時候，需要把資料一行一行地從引擎裡面讀出來，然後累積計數。
• 用緩存系統儲存計數？
  • 因為資料庫支援事務與并發，是以無法通過類似于Redis的緩存資料庫，精确控制不同線程執行時刻的count數量
• 用資料庫儲存計數
  • 如果我們把這個計數直接放到資料庫裡單獨的一張計數表 C 中，又會怎麼樣呢？
  • 我們來看下現在的執行結果。雖然會話 B 的讀操作仍然是在 T3 執行的，但是因為這時候更新事務還沒有送出，是以計數值加 1 這個操作對會話 B 還不可見。

    

  • 又因為InnoDB支援崩潰恢複不丢失，是以邏輯上是一緻的
• InnoDB引擎提供資料原則：
  • server 層要什麼就給什麼；
  • InnoDB 隻給必要的值；
  • 現在的優化器隻優化了 count(*) 的語義為“取行數”，其他“顯而易見”的優化并沒有做。
• count不同字段差異
  • count(主鍵id)：InnoDB 引擎會周遊整張表，把每一行的 id 值都取出來，傳回給 server 層。server 層拿到 id 後，判斷是不可能為空的，就按行累加。
  • count(1)：InnoDB 引擎周遊整張表，但不取值。server 層對于傳回的每一行，放一個數字“1”進去，判斷是不可能為空的，按行累加。
  • count(字段)：
    • 如果這個“字段”是定義為 not null 的話，一行行地從記錄裡面讀出這個字段，判斷不能為 null，按行累加；
    • 如果這個“字段”定義允許為 null，那麼執行的時候，判斷到有可能是 null，還要把值取出來再判斷一下，不是 null 才累加。
    • 也就是前面的第一條原則，server 層要什麼字段，InnoDB 就傳回什麼字段。
  • count(*)
    • 并不會把全部字段取出來，而是專門做了優化，不取值。count(*) 肯定不是 null，按行累加。
  • 是以結論是：按照效率排序的話，count(字段)<count(主鍵 id)<count(1)≈count()，是以我建議你，盡量使用 count()。

15 | 答疑文章（一）：日志和索引相關問題

16 | “order by”是怎麼工作的？

• 案例一
  • SQL：

    select city,name,age from t where city='杭州' order by name limit 1000;

    【city字段建有索引】
  • explain:

    

  • Extra 這個字段中的“Using filesort”表示的就是需要排序，MySQL 會給每個線程配置設定一塊記憶體用于排序，稱為 sort_buffer。
  • 語句執行流程【全字段排序】
    • 初始化 sort_buffer，确定放入 name、city、age 這三個字段；
    • 從索引 city 找到第一個滿足 city='杭州’條件的主鍵 id；
    • 到主鍵 id 索引取出整行，取 name、city、age 三個字段的值，存入 sort_buffer 中；
    • 從索引 city 取下一個記錄的主鍵 id；
    • 重複步驟 3、4 直到 city 的值不滿足查詢條件為止；
    • 對 sort_buffer 中的資料按照字段 name 做快速排序；
    • 按照排序結果取前 1000 行傳回給用戶端。

    • 

• sort_buffer_size
  • MySQL 為排序開辟的記憶體（sort_buffer）的大小。如果要排序的資料量小于 sort_buffer_size，排序就在記憶體中完成。但如果排序資料量太大，記憶體放不下，則不得不利用磁盤臨時檔案輔助排序。
  • 使用臨時檔案輔助排序時MySQL 将需要排序的資料分成 12 份，每一份單獨排序後存在這些臨時檔案中。然後把這 12 個有序檔案再合并成一個有序的大檔案。（歸并排序算法）
• rowid 排序
  • 參數：max_length_for_sort_data
    • 是 MySQL 中專門控制用于排序的行資料的長度的一個參數。
    • 它的意思是，如果單行的長度超過這個值，MySQL 就認為單行太大，要換一個算法，因為當單行資料量太大時，全部緩存到記憶體中進行排序顯然不是一個很好的辦法
  • 設定方式

    SET max_length_for_sort_data = 16;

  • 新的算法放入 sort_buffer 的字段，隻有要排序的列（即 name 字段）和主鍵 id。
• 案例二（使用rowid 排序）【同樣使用案例一的表結構和sql】
  • 初始化 sort_buffer，确定放入兩個字段，即 name 和 id；
  • 從索引 city 找到第一個滿足 city='杭州’條件的主鍵 id；
  • 到主鍵 id 索引取出整行，取 name、id 這兩個字段，存入 sort_buffer 中；
  • 從索引 city 取下一個記錄的主鍵 id；
  • 重複步驟 3、4 直到不滿足 city='杭州’條件為止；
  • 對 sort_buffer 中的資料按照字段 name 進行排序；
  • 周遊排序結果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三個字段傳回給用戶端。
• 全字段排序 VS rowid 排序
  • 如果 MySQL 實在是擔心排序記憶體太小，會影響排序效率，才會采用 rowid 排序算法，這樣排序過程中一次可以排序更多行，但是需要再回到原表去取資料。
  • 如果 MySQL 認為記憶體足夠大，會優先選擇全字段排序，把需要的字段都放到 sort_buffer 中，這樣排序後就會直接從記憶體裡面傳回查詢結果了，不用再回到原表去取資料。
  • 這也就展現了 MySQL 的一個設計思想：如果記憶體夠，就要多利用記憶體，盡量減少磁盤通路。
  • 對于 InnoDB 表來說，rowid 排序會要求回表多造成磁盤讀，是以不會被優先選擇。
• 案例三【建立(city,name)聯合索引】
  • 從索引 (city,name) 找到第一個滿足 city='杭州’條件的主鍵 id；
  • 到主鍵 id 索引取出整行，取 name、city、age 三個字段的值，作為結果集的一部分直接傳回；
  • 從索引 (city,name) 取下一個記錄主鍵 id；
  • 重複步驟 2、3，直到查到第 1000 條記錄，或者是不滿足 city='杭州’條件時循環結束。
• 案例四【建立(city,name,age)聯合索引】–使用到覆寫索引
  • 從索引 (city,name,age) 找到第一個滿足 city='杭州’條件的記錄，取出其中的 city、name 和 age 這三個字段的值，作為結果集的一部分直接傳回；
  • 從索引 (city,name,age) 取下一個記錄，同樣取出這三個字段的值，作為結果集的一部分直接傳回；
  • 重複執行步驟 2，直到查到第 1000 條記錄，或者是不滿足 city='杭州’條件時循環結束。

17 | 如何正确地顯示随機消息？

• 如何從一個單詞表中随機選出三個單詞呢？
  • 使用order by rand() 來實作
    • select word from words order by rand() limit 3;
    • explain結果為：

      

    • 該條語句的執行流程為：
      • 建立一個臨時表。這個臨時表使用的是 memory 引擎，表裡有兩個字段，第一個字段是 double 類型，為了後面描述友善，記為字段 R，第二個字段是 varchar(64) 類型，記為字段 W。并且，這個表沒有建索引。
      • 從 words 表中，按主鍵順序取出所有的 word 值。對于每一個 word 值，調用 rand() 函數生成一個大于 0 小于 1 的随機小數，并把這個随機小數和 word 分别存入臨時表的 R 和 W 字段中，到此，掃描行數是 10000。
      • 現在臨時表有 10000 行資料了，接下來你要在這個沒有索引的記憶體臨時表上，按照字段 R 排序。
      • 初始化 sort_buffer。sort_buffer 中有兩個字段，一個是 double 類型，另一個是整型。
      • 從記憶體臨時表中一行一行地取出 R 值和位置資訊（我後面會和你解釋這裡為什麼是“位置資訊”），分别存入 sort_buffer 中的兩個字段裡。這個過程要對記憶體臨時表做全表掃描，此時掃描行數增加 10000，變成了 20000。
      • 在 sort_buffer 中根據 R 的值進行排序。注意，這個過程沒有涉及到表操作，是以不會增加掃描行數。
      • 排序完成後，取出前三個結果的位置資訊，依次到記憶體臨時表中取出 word 值，傳回給用戶端。這個過程中，通路了表的三行資料，總掃描行數變成了 20003。
      • 總結：order by rand() 使用了記憶體臨時表，記憶體臨時表排序的時候使用了 rowid 排序方法。
        • 對于有主鍵的 InnoDB 表來說，這個 rowid 就是主鍵 ID；
        • 對于沒有主鍵的 InnoDB 表來說，這個 rowid 就是由系統生成的；
        • MEMORY 引擎不是索引組織表。在這個例子裡面，你可以認為它就是一個數組。是以，這個 rowid 其實就是數組的下标。
  • 随機排序方法 - 1【先把問題簡化，隻随機選擇一個word值】
    • 取得這個表的主鍵 id 的最大值 M 和最小值 N;
    • 用随機函數生成一個最大值到最小值之間的數 X = (M-N)*rand() + N;
    • 取不小于 X 的第一個 ID 的行。
    • 當id不連續就不能使用該方法擷取了
  • 随機排序方法 - 2【先把問題簡化，隻随機選擇一個word值】
    • 取得整個表的行數，并記為 C。
    • 取得 Y = floor(C * rand())。 floor 函數在這裡的作用，就是取整數部分。
    • 再用 limit Y,1 取得一行。
  • 當id連續時使用随機算法1，當id不連續時使用随機算法2
  • 其他辦法：
    • 如果按照業務需求，随機取三個，資料庫還在設計階段,可以增加一個主鍵字段,用來記錄每行記錄的rowid，這樣一萬行，那就是連續的一萬，然後随機，用該随機rowid回表查詢該行記錄

18 | 為什麼這些SQL語句邏輯相同，性能卻差異巨大？

• 案例一：條件字段函數操作
  • SQL：

    select count(*) from tradelog where month(t_modified)=7;

  • 對索引字段做函數操作，可能會破壞索引值的有序性，是以優化器就決定放棄走樹搜尋功能。
• 案例二：隐式類型轉換
  • SQL：

    select * from tradelog where tradeid=110717;

    • tradeid字段 為varchar(32)類型
    • MySQL裡的轉換規則：字元串和數字做比較的話，是将字元串轉換成數字。
  • 則上面語句相當于：

    select * from tradelog where CAST(tradid AS signed int) = 110717;

• 案例三：隐式字元編碼轉換
  • SQL：

    select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;

  • 當表 l 和表 d 的 tradeid字段 字元集不一緻時，也會放棄索引進行全表掃描

19 | 為什麼我隻查一行的語句，也執行這麼慢？

• 查詢長時間不傳回：

  -SQL：

  select * from t where id=1;

  • 原因：大機率是表格被鎖住了
  • 解決辦法：執行

    show processlist

    ,看看目前語句處于什麼狀态，再進行處理
    • 等待MDL鎖
      • Waiting for table metadata lock

        

      • 這個狀态表示的是，現在有一個線程正在表 t 上請求或者持有 MDL 寫鎖，把 select 語句堵住了。
      • 這類問題的處理方式，就是找到誰持有 MDL 寫鎖，然後把它 kill 掉。
      • 複現步驟

        

    • 等待flush
      • Waiting for table flush

        

      • 可能的情況是有一個flush tables 指令被别的語句堵住了，然後它又堵住了我們的 select 語句。
      • 場景複現

        

      • 解決辦法，kill掉第一條語句

        

    • 等行鎖
      • 場景複現

        

      • 解決辦法，通過sys.innodb_lock_waits 表查詢獲得鎖的線程，然後kill掉
        • SQL ：

          select * from t sys.innodb_lock_waits where locked_table='

          test

          .

          t

          '\G

        • kill掉4這個連接配接

          

• 查詢慢：
  • 掃描行數多，需要建立索引
  • 該條語句被後開啟的其他事務大量修改，産生了大量的undo log
    • 複現方式

      

    • 原理分析：

      

20 | 幻讀是什麼，幻讀有什麼問題？

21 | 為什麼我隻改一行的語句，鎖這麼多？

22 | MySQL有哪些“飲鸩止渴”提高性能的方法？

23 | MySQL是怎麼保證資料不丢的？

24 | MySQL是怎麼保證主備一緻的？

25 | MySQL是怎麼保證高可用的？

26 | 備庫為什麼會延遲好幾個小時？

27 | 主庫出問題了，從庫怎麼辦？

28 | 讀寫分離有哪些坑？

29 | 如何判斷一個資料庫是不是出問題了？

30 | 答疑文章（二）：用動态的觀點看加鎖

31 | 誤删資料後除了跑路，還能怎麼辦？

32 | 為什麼還有kill不掉的語句？

33 | 我查這麼多資料，會不會把資料庫記憶體打爆？

34 | 到底可不可以使用join？

35 | join語句怎麼優化？

36 | 為什麼臨時表可以重名？

37 | 什麼時候會使用内部臨時表？

38 | 都說InnoDB好，那還要不要使用Memory引擎？

39 | 自增主鍵為什麼不是連續的？

40 | insert語句的鎖為什麼這麼多？

41 | 怎麼最快地複制一張表？

42 | grant之後要跟着flush privileges嗎？

43 | 要不要使用分區表？

44 | 答疑文章（三）：說一說這些好問題

45 | 自增id用完怎麼辦？

應用

總結

• 盡量避免使用長事務
• 如果你的事務中需要鎖多個行，要把最可能造成鎖沖突、最可能影響并發度的鎖盡量往後放
• begin/start transaction 指令并不是一個事務的起點，在執行到它們之後的第一個操作 InnoDB 表的語句，事務才真正啟動。如果你想要馬上啟動一個事務，可以使用 start transaction with consistent snapshot 這個指令。

▄█▀█●各位同仁，如果我的代碼對你有幫助，請給我一個贊吧，為了下次友善找到，也可關注加收藏呀

如果有什麼意見或建議，也可留言區讨論