MySQL系列-- 5. MySQL高階特性

王亦信Ashin發表於2017-10-22
MySql

5. MySQL高階特性

5.1 分割槽表

  • 對使用者來說，分割槽表是一個獨立的邏輯表，但是底層由多個物理字表組成。
    • 實現分割槽的程式碼實際上是對一組底層表的控制程式碼物件(Handler Object)的封裝。
    • 對分割槽表的請求，都會通過控制程式碼物件轉換成對儲存物件的介面呼叫。
    • 所以分割槽對於SQL層來說是一個完全封裝底層實現的黑盒子，對應用是透明的，但是對底層的檔案系統來看就很容易發現，每一個分割槽表都有一個使用#分隔命名的表檔案。
  • MySQL實現分割槽表的方式——對底層表的封裝——意味著索引也是按照分割槽的字表定義的，而沒有全域性索引。這和Oracle不同，在Oracle中可以更加靈活的定義索引和表是否分割槽
  • MySQL在建立表時使用PARTITION BY子句定義每個分割槽存放的資料。在執行查詢的時候，優化器會根據分割槽定義過濾那些沒有我們需要資料的分割槽，這樣查詢就無須掃描所有分割槽——只需要查詢包含需要資料的分割槽。
  • 分割槽的一個主要目的是將資料按照一個較粗的粒度分在不同的表中。這樣做可以將相關的資料存放在一起，另外，如果想一次批量刪除整個分割槽的資料也會變得很方便。
  • 分割槽起到非常大作用的場景：
    • 表非常大以至於無法全部都放在記憶體中，或者只在表的最後部分有熱點資料，其他均是歷史資料。
    • 分割槽表的資料更容易維護。例如，想批量刪除大量資料可以使用清除整個分割槽的方式。另外，還可以對一個獨立分割槽進行優化、檢查、修復等操作。
    • 分割槽表的資料可以分佈在不同的物理裝置上，從而高效地利用多個硬體裝置。
    • 可以使用分割槽表來避免某些特殊的瓶頸，例如InnoDB的單個索引的互斥訪問、ext3檔案系統的inode鎖競爭等。
    • 如果需要，還可以備份和恢復獨立的分割槽，這在非常大的資料集的場景下效果非常好。
  • 分割槽表部分比較重要的限制：
    • 一個表最多隻能有1024個分割槽
    • 在MySQL5.1中，分割槽表示式必須是整數，或者是返回整數的表示式。在MySQL5.5中，某些場景中可以直接使用列來進行分割槽
    • 如果分割槽欄位中有主鍵或者唯一索引的列，那麼所有主鍵列和唯一索引列都必須包含進來。
    • 分割槽表中無法使用外來鍵約束。

5.1.1 分割槽表的原理

  • 如前所述，分割槽表由多個相關的底層表實現，這些底層表也是由控制程式碼物件(Handler object)標識，所以可以直接訪問各個分割槽。
    • 儲存引擎管理分割槽的各個底層表和管理普通表一樣(所有的底層表都必須使用相同的儲存引擎)，分割槽表的索引只是在各個底層表上各自加上一個完全相同的索引
    • 從儲存引擎的角度來看，底層表和一個普通表沒有任何不同，儲存引擎也無須直到這是一個普通表還是一個分割槽表的一部分。
  • 分割槽表上的操作：
    • SELECT查詢：當查詢一個分割槽表的時候，分割槽層先開啟並鎖住所有的底層表，優化器先判斷是否可以過濾部分分割槽，然後再呼叫對應的儲存引擎介面訪問各個分割槽的資料。
    • INSERT操作：當寫入一條記錄時，分割槽層先開啟並鎖住所有的底層表，然後確定哪個分割槽接收這條記錄，再將記錄寫入對應底層表
    • DELETE操作：當刪除一條記錄，分割槽層先開啟並鎖住所有的底層表，然後確定資料對應的分割槽，最後對相應底層表進行刪除操作。
    • UPDATE操作：當更新一條記錄時，分割槽層先開啟並鎖住所有的底層表，MySQL先確定需要更新的記錄在哪個分割槽，然後取出資料並更新，在判斷更新後的資料應該放在哪個分割槽，最後對底層表進行寫入操作，並對原資料所在的底層表進行刪除操作。
  • 有些操作是支援過濾的。
    • 當刪除一條記錄時，MySQL需要先找到這條記錄，如果WHERE條件恰好和分割槽表示式匹配，就可以將所有不包含這條記錄的分割槽都過濾掉。這對UPDATE語句同樣有效。
    • 如果是INSERT操作，則本身就是隻命中一個分割槽，其他分割槽都會被過濾掉。MySQL先確定這條操作屬於哪個分割槽，再將記錄寫入對應的底層分割槽表，無須對任何其他分割槽進行操作。
  • 雖然每個操作都會”先開啟並鎖住所有的底層表“，但這並不是說分割槽表在處理的過程中是鎖住全表的。如果儲存引擎能夠自己實現行級鎖，例如InnoDB，則會在分割槽層釋放對應表鎖。這個加鎖和解鎖過程與普通InnoDB上的查詢類似。

5.1.2 分割槽表的型別

  • MySQL支援多種分割槽表。最多的是根據範圍進行分割槽，每個分割槽儲存落在某個範圍的記錄，分割槽表示式可以是列，也可以包含列的表示式。
  • PARTITION分割槽子句中可以使用各種函式，但是表示式返回的值必須是一個確定的整數，且不能是一個常數。
  • MySQL還支援鍵值、雜湊和列表分割槽，這其中有些還支援子分割槽。在MySQL5.5中，還可以使用RANGE COLUMNS型別的分割槽，這樣即使是基於時間的分割槽也無須再將其轉換成一個整數。
    • 按時間分割槽的InnoDB表，系統通過子分割槽可降低索引的互斥訪問的競爭。最近一年的分割槽的資料會被非常頻繁地訪問，這會導致大量的互斥量的競爭。使用雜湊子分割槽可以將資料切成多個小片，大大降低互斥量的競爭問題。
  • 其他的分割槽技術：
    • 根據鍵值進行分割槽，來減少InnoDB的互斥量競爭
    • 使用數學模函式來進行分割槽，然後將資料輪詢放入不同的分割槽。例如，可以對日期做模7的運算，或者更簡單地使用返回周幾的函式，如果只想保留最近幾天的資料，這樣分割槽很方便
    • 假設表有一個自增的主鍵列id，希望根據時間將最近的熱點資料集中存放。那麼必須將時間戳包含在主鍵當中才行，而這和主鍵本身的意義相矛盾。這種情況下可以使用這樣的分割槽表示式來實現同樣的目的: HASH(id DIV 1000000)，這將為100萬資料建立一個分割槽。一方面實現了當初分割槽的目的，另一方面比起使用時間範圍分割槽還避免了一個問題，就是當超過一定閾值時，如果使用時間範圍分割槽就必須新增分割槽。

5.1.3 如何使用分割槽表

  • 假設從一個非常大有10億條記錄的表找出最近幾個月的資料：
    • 因為資料量巨大，肯定不能在每次查詢的時候掃描全表
    • 考慮到索引在空間和維護上的消耗，也不希望使用索引。
      • 除非是覆蓋查詢，否則伺服器需要根據索引掃描的結果回表。
      • 如果真的使用索引，會發現資料不是按照想要的方式聚集的，而且會有大量的碎片產生，導致一個查詢產生大量的隨機IO
    • 剩下的路：
      • 讓所有查詢都只在資料表上做順序掃描
      • 將資料表和索引全部都快取在記憶體裡
      • 使用分割槽
  • 理解分割槽時還可以將其當作索引的最初形態，以代價非常小的方式定位到需要的資料在哪一片區域。在這一片區域中，可以做順序掃描，可以建索引，可以將資料快取到記憶體中，等等。因為分割槽無須額外的資料結構記錄每個分割槽有哪些資料——分割槽不需要精確定位每條資料的位置，也就無須額外的資料結構——因此代價非常低。
  • 保證大資料量的可擴充套件性的策略：
    • 全量掃描資料，不要任何索引：
      • 可以使用簡單的分割槽方式存放表，不要任何索引，根據分割槽的規則大致定位需要的資料位置。只要能夠使用WHERE條件，將需要的資料限制在少數分割槽中，則效率是很高的。當然，也需要做一些簡單的運算保證查詢的響應時間能夠滿足需求。
      • 使用該策略假設不用將資料完全放入到記憶體中，同時還假設需要的資料全部在磁碟上。因為記憶體相對很小，資料很快會被擠出記憶體，所以快取起不了任何作用。
      • 這個策略適用於以正常的方式訪問大量資料的時候。
      • 必須將查詢需要掃描的分割槽個數限制在一個很小的數量。
    • 索引資料，並分離熱點：
      • 如果資料有明顯的“熱點”，而且除了這部分資料，其他資料很少被訪問到，那麼可以將這部分熱點資料單獨放在一個分割槽中，讓這個分割槽的資料能有有機會都快取在記憶體中。
      • 這樣查詢就可以只訪問一個很小的分割槽表，能夠使用索引，也能夠有效地使用快取。

5.1.4 什麼情況下會出問題

上一節介紹的兩個分割槽策略都基於兩個很重要的假設：查詢能夠過濾掉很多額外的分割槽，分割槽本身並不會帶來很多額外的代價。

可能會遇到問題的場景：

  • NULL值會使分割槽過濾無效

    分割槽的表示式的值可以是NULL：第一個分割槽是一個特殊分割槽。

    • 假設按照PARTITION BY RANGE YEAR(order_date)分割槽，那麼所有order_date為NULL或者是一個非常值的時候，記錄都會放到第一個分割槽。
      • 假設有如下查詢:WHERE order_date BETWEEN '2012-01-01' AND '2012-01-31' ,實際上MySQL會檢查兩個分割槽，因為YEAR()在接收非法值時會返回NULL而把記錄放到第一個分割槽。
    • 如果第一個分割槽非常大，特別是當使用"全量掃描資料，不要任何索引"的策略時，代價會非常大。
    • 優化技巧：
      • 建立一個無用的第一個分割槽，例如：PARTITION p_nulls VALUES LESS THAN (0)。這樣即使需要檢查第一個分割槽，代價也非常小
      • (最優)MySQL5.5以後不需要第一個優化技巧，因為可以直接使用列本身而不是基於列的函式進行分割槽。PARTITION BY RANGE COLUMNS(order_date)
  • 分割槽列和索引列不匹配：
    • 如果定義的索引列和分割槽列不匹配，會導致查詢無法進行分割槽過濾。
      • 假設在列a上定義了索引，而在列b上進行分割槽。因為每個分割槽都有其獨立的索引，索引掃描列a上的索引就需要掃描每一個分割槽內對應的索引。
    • 應該避免建立和分割槽列不匹配的索引，除非查詢中還同時包含了可以過濾分割槽的條件：
      • 其他問題：如果在一個關聯查詢中，分割槽表在關聯順序中是第二個表，並且關聯使用的索引和分割槽條件不匹配。那麼關聯時針對第一個表符合條件的每一個行，都需要訪問並搜尋第二個表的所有分割槽。
  • 選擇分割槽的成本可能很高
    • 不同型別的分割槽，由於其實現方式不同，所以它們的效能也不同
      • 尤其是範圍分割槽，對於回答“這一行屬於哪個分割槽”這樣的成本可能會非常高，因為伺服器需要掃描所有的分割槽的列表來找到正確的答案。類似這樣的線性搜尋的效率不高，隨著分割槽數的增長，成本會越來越高。
      • 其他的分割槽型別，如鍵分割槽和雜湊分割槽，則沒有這樣的問題
    • 對大多數系統來說，100個左右的分割槽是沒有問題的。
  • 開啟鎖並鎖住所有底層表的成本可能很高
    • 當查詢訪問分割槽表的時候，MySQL需要開啟鎖並鎖住所有底層表，這是分割槽表的另一個開銷。
      • 這個操作在分割槽過濾之前發生，因此無法通過分割槽過濾降低此開銷，並且該開銷也和分割槽型別無關，會影響所有的查詢。
    • 對一些本身操作非常快的查詢，比如根據主鍵查詢單行，會帶來明顯的額外開銷。
    • 優化技巧：
      • 用批量操作的方式來降低單個操作的此類開銷，例如使用批量插入或者LOAD DATA INFILE、一次刪除多行資料，等等
      • 限制分割槽的個數
  • 維護分割槽的成本可能很高
    • 某些分割槽維護操作的速度會非常快，例如新增或者刪除分割槽(刪除一個大分割槽可能會很慢，不過這是另一回事)
    • 而有些操作，如重組分割槽或者類似ALTER語句的操作，速度會比較慢，因為需要複製資料。

分割槽實現中的一些其他限制：

  • 所有分割槽都必須使用相同的儲存引擎
  • 分割槽函式中可以使用的函式和表示式也有一些限制
  • 某些儲存引擎不支援分割槽
  • 對於MyISAM的分割槽表，不能再使用LOAD INDEX INTO CACHE操作
  • 對於MyISAM表，使用分割槽表時需要開啟更多的檔案描述符。每一個分割槽對於儲存引擎來說都是一個獨立的表，即使分割槽表只佔用一個表快取條目，檔案描述符還是需要多個。

5.1.5 查詢優化

  • 訪問分割槽表，需在WHERE條件中帶入分割槽列，即使有時候看似多餘，這樣就可以讓優化器過濾掉無須訪問的分割槽。

    • MySQL只能在使用分割槽函式的列的本身進行比較才能過濾分割槽，而不能根據表示式的值去過濾分割槽，即使這個表示式就是分割槽函式也不行。這和查詢中使用獨立的列才能使用索引的道理是一樣的。

      -- 無法使用分割槽
      mysql> EXPLAIN PARTITIONS SELECT * FROM sales_by_day WHERE YEAR(day) = 2010\G;
      -- 可使用分割槽
      mysql> EXPLAIN PARTITIONS SELECT * FROM sales_by_day
      -> WHERE day BETWEEN '2010-01-01' AND '2010-12-31'\G;複製程式碼

  • 優化器在處理查詢的過程中總是儘可能聰明地去過濾分割槽。例如，若分割槽表是關聯操作中的第二張表，且關聯條件是分割槽鍵，MySQL就只會在對應的分割槽裡匹配行。(EXPLAIN無法顯示這種情況下的分割槽過濾，因為這是執行時的分割槽過濾，而不是查詢優化階段的)

5.1.6 合併表

合併表是一種早期的、簡單的分割槽實現，和分割槽表相比有一些不同的限制，並且缺乏優化。合併表允許使用者單獨訪問各個子表。分割槽表是未來的發展趨勢，合併表是一種將被淘汰的技術，在未來版本可能會被刪除，在這裡不做過多闡述。

5.2 檢視

  • 檢視本身是一個虛擬表，不存放任何資料。在使用SQL語句訪問檢視的時候，它返回的資料是MySQL從其他表生成的。

    • 檢視和表是在同一個名稱空間，MySQL在很多地方和表是同樣對待的。不過不能對檢視建立觸發器，也不能使用DROP TABLE命令刪除檢視。
    • MySQL5.0版本之後開始引進。
    • 個人理解：檢視不會對查詢產生任何優化，只是對結果進行一個更好的展示，因為其底層的原理是查詢原有的表。某些情況下可以幫助提升效能。

  • 工作原理:

    -- 實現檢視最簡單的辦法是將SELECT語句的結果存放到臨時表中。
    mysql> CREATE VIEW Oceania AS
    -> SELECT * FROM Country WHERE Continent = 'Oceania'
    -> WITH CHECK OPTION;
    -- 當需要訪問檢視的時候，可直接訪問這個臨時表
    mysql> SELECT Code, Name FROM Oceania WHERE Name = 'Australia';
    -- MySQL使用的並演算法：重寫含有檢視的查詢，將檢視的定義SQL直接包含進查詢的SQL中：
    mysql> SELECT Code, Name FROM Country
    -> WHERE Continent = 'Oceania' AND Name = 'Australia';
    -- MySQL 使用的臨時表演算法，以下SQL是為展示用的。這樣做會有明顯的效能問題，優化器也很難優化在這個臨時表上的查詢。
    mysql> CREATE TEMPORARY TABLE TMP_Oceania_123 AS
    -> SELECT * FROM Country WHERE Continent = 'Oceania';
    mysql> SELECT Code, Name FROM TMP_Oceania_123 WHERE Name = 'Australia';複製程式碼

    MySQL使用合併演算法 和臨時表演算法 來處理檢視。如果可能，儘可能使用合併演算法。

    • MySQL甚至可以巢狀地定義檢視，也就是在一個檢視上再定義另一個檢視。
    • 可以在EXPLAIN EXTENDED之後使用SHOW WARNINGS來檢視使用檢視的查詢重寫的結果
      • 如果是採用臨時表演算法實現的檢視，EXPLAIN中的select_type會顯示為派生表(DERIVED)。如果產生的底層派生表很大，那麼執行EXPLAIN可能會非常慢。因為在5.5及之前的版本中，EXPLAIN是需要實際執行併產生派生表的。

    兩種演算法的實現細節：

    檢視的兩種實現
    檢視的兩種實現

    使用臨時表演算法實現檢視的場景：

    • 檢視中包含GROUP BY、DISTINCT、任何聚合函式、UNION、子查詢等，只要無法在原表記錄和檢視記錄中建立一一對映的場景。

    檢視的實現演算法是檢視本身的屬性，和作用在檢視上的查詢語句無關。例如，可以為一個基於簡單查詢的檢視制定使用臨時表演算法：CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM sakila.actor;，這樣無論基於執行什麼樣的查詢，檢視都會生成一個臨時表。

5.2.1 可更新檢視

  • 可更新檢視(updatable view)是指可以通過更新這個檢視來更新檢視涉及的相關表
    • 只要指定了合適的條件，就可以更新、刪除甚至向檢視中寫入資料
    • 更新檢視的查詢也可以是一個關聯語句，但是被更新的列必須來自同一個表中
    • 所有使用臨時表演算法實現的檢視都無法被更新
    • CHECK OPTION子句表示任何通過檢視更新的行，都必須符合檢視本身的WHERE條件定義。所以不能更新檢視以外的列
    • MySQL不支援在檢視上建任何觸發器。某些關聯式資料庫允許在檢視上簡歷INSTEAD OF觸發器來精確控制在修改檢視資料時做些什麼。

5.2.2 檢視對效能的影響

  • 某些情況下檢視也可以幫助提升效能，而且檢視還可以和其他提升效能的方式疊加使用。
  • 提升效能的應用場景：
    • 在重構schema的時候，使得在修改檢視底層表結構的時應用程式碼還可能繼續不報錯的執行
    • 實現基於列的許可權控制，卻不需要真正的系統中建立許可權，因此沒有任何額外的開銷
    • 使用偽臨時檢視：
      • MySQL雖然不能建立只在當前連結中存在的真正的臨時檢視，但是可以建一個特殊名字的檢視，然後在連線結束的時候刪除該檢視。這樣在連線過程中就可以在FROM子句中使用這個檢視，MySQL處理檢視和子查詢的程式碼路徑完全不同，所以它們的效能也不同
      • 可以使用連線ID作為檢視名字的一部分來避免衝突。在應用發生崩潰和別的意外導致未清理臨時檢視的時候，這個技巧使得清理臨時檢視變得更簡單。
    • 使用臨時表演算法實現的檢視，在某些時候效能查詢會很糟糕(雖然可能比直接使用等效查詢語句更好一點)
      • MySQL以遞迴的方式執行這類檢視，先會執行外層查詢，即使外層查詢優化器將其優化得很好，但是MySQL優化器可能無法像其他的資料庫那樣做更多的內外結合的優化。外層查詢的WHERE條件無法“下推”到構建檢視的臨時表的查詢中，臨時表也無法建立索引。
  • 注意檢視背後的複雜性，可能它引用了很多表。如果打算使用檢視來提升效能，需要做比較詳細的測試。即使是合併演算法實現的檢視也會有額外開銷，而且檢視效能很難預測。因為在MySQL的優化器中，檢視的程式碼執行路徑也完全不同，這部分程式碼測試還不夠全面，可能會有一些隱藏缺陷和問題，所以目前的檢視還不是那麼成熟。

5.2.3 檢視的限制

  • MySQL還不支援物化檢視(指檢視結果資料存放在一個可以檢視的表中，並定期從原始表重新整理資料到這個表)，也不支援在檢視中建立索引。可以使用構建快取表或者彙總表的辦法來模擬物化檢視和索引

  • MySQL並不會儲存檢視定義的原始SQL語句，所以不能通過執行SHOW CREATE VIEW後再簡單地修改其結果的方式來重新定義檢視。

    • 如果打算修改檢視，並且沒法找到檢視的原始的建立語句的話，可以通過使用檢視.frm檔案最後一行獲取一些資訊。如果有FILE許可權，甚至可直接使用LOAD_FILE()來讀取.frm中的檢視建立資訊，在加上一些字元處理工作。

      mysql> SELECT
      -> REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
      -> REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
      -> SUBSTRING_INDEX(LOAD_FILE('/var/lib/mysql/world/Oceania.frm'),
      -> '\nsource=', −1),
      -> '\\_','\_'), '\\%','\%'), '\\\\','\\'), '\\Z','\Z'), '\\t','\t'),
      -> '\\r','\r'), '\\n','\n'), '\\b','\b'), '\\\"','\"'), '\\\'','\''),
      -> '\\0','\0')
      -> AS source;複製程式碼

5.3 外來鍵約束

  • InnoDB是目前MySQL中唯一支援外來鍵的內建儲存引擎。
  • 使用外來鍵是有成本的。
    • 比如外來鍵通常都要求每次在修改資料時都要在另外一張表中多執行一次查詢操作。雖然InnoDB強制外來鍵使用索引，但還是無法消除這種約束檢查的開銷。如果外來鍵列的選擇性很低，則會導致一個非常大且選擇性很低的索引。
  • 某些場景下外來鍵會提升一些效能：
    • 如果想確保兩個相關表始終有一致的資料，那麼使用外來鍵比在應用程式中檢查一致性的效能要高得多。
    • 外來鍵在相關資料的刪除和更新上，也比在應用中維護要更高效，不過，外來鍵維護操作是逐行進行的，所以這樣的更新會比批量刪除和更新要慢。
  • 外來鍵約束使得查詢需要額外訪問一些別的表，這也意味著需要額外的鎖。
    • 如果向子表中寫入一條記錄，外來鍵約束會讓InnoDB檢查對應的父表的記錄，也就需要對父表對應記錄進行加鎖操作，來確保這條記錄不會在這個事務完成之時就被刪除了。這會導致額外的鎖等待，甚至會導致一些死鎖。因為沒有直接訪問這些表，所以這類死鎖問題往往很難排除。
  • 有時，可以使用觸發器來代替外來鍵，對於相關資料的同時更新外來鍵更合適，但是如果外來鍵只是用作數值約束，那麼觸發器或者顯式地限制取值會更好些(這裡，可以直接使用ENUM型別)
  • 如果只是用外來鍵做約束，那通常在應用程式裡實現該約束會更好。外來鍵會帶來很大的額外消耗。

5.4 在MySQL內部儲存程式碼(暫時無用，簡單介紹)

在未來一段時間還不會用到，需要用到再看，感覺更適合DBA，這裡只列舉常用的方式。

5.4.1 儲存過程和函式

5.4.2 觸發器

可以在執行INSERT、UPDATE或者DELETE的時候，執行一些特定的操作。可以在MySQL中指定是在SQL語句執行前觸發還是在執行後觸發。

5.4.3 事件

類似於LINUX的定時任務，不過完全是在MySQL內部實現。

5.4.4 在儲存過程中保留註釋

5.5 遊標(暫時無用，簡單介紹)

MySQL在伺服器中提供只讀的、單向的遊標，而且只能在儲存過程或者更底層的客戶端API中使用。因為遊標中指向的物件都是儲存在臨時表中而不是實際查詢到的資料，所以MySQL遊標總是可讀的。

5.6 繫結變數

  • 繫結變數的SQL語句：INSERT INTO tbl(col1, col2, col3) VALUES (?, ?, ?);。繫結變數的SQL，使用問號標記可以接收引數的位置，當真正需要執行具體查詢的時候，則使用具體值代替這些問號。
  • 當建立一個繫結變數SQL時，客戶端(如C或JAVA等)向伺服器傳送了一個SQL語句的原型。伺服器端收到這個SQL語句的框架後，解析並儲存這個SQL語句的部分執行計劃，返回給客戶端一個SQL語句處理控制程式碼。以後每次執行這類查詢，客戶端都制定使用這個控制程式碼。
  • MySQL在使用繫結變數的時候可以更高效地執行大量重複語句的原因：
    • 在伺服器端只需要解析一次SQL語句。
    • 在伺服器端某些優化器的工作只需要執行一次，因為它會快取一部分的執行計劃。
    • 以二進位制的方式只傳送引數和控制程式碼，比起每次傳送ASCII碼文字效率更高。不過最大的節省還是來自於BLOB和TEXT欄位，繫結變數的形式可以分塊傳輸，而無須一次性傳輸。二進位制協議在客戶端也可以節省很多內春，減少了網路開銷，還節省了將資料從儲存原始格式轉換成文字格式的開銷。
    • 僅僅是引數而不是整個查詢語句需要傳送到伺服器端，所以網路開銷會更小。
    • MySQL在儲存引數的時候，直接將其存放到快取中，不再需要在記憶體中多次複製。
  • 繫結變數相對也更加安全。無須在應用程式中處理轉義，一則更簡單明瞭，二則也大大減少了SQL隱碼攻擊和攻擊的風險。

5.6.1 繫結變數的優化

理論上有些優化器只需要做一次，但實際上，下面的操作還是都會被執行。根據優化器什麼時候工作，可以將優化分為三類：

  • 在準備階段：伺服器解析SQL語句，移除不可能的條件，並重寫子查詢
  • 在第一次執行的時候：如果可能的話，伺服器先簡化巢狀迴圈的關聯，並將外關聯轉換成內關聯
  • 在每次SQL語句執行時，伺服器做如下事情：
    • 過濾分割槽
    • 如果可能的話，儘量移除COUNT()、MIN()和MAX()
    • 移除常數表示式
    • 檢測常量表
    • 做必要的等值傳播
    • 分析和優化ref、range和索引優化等訪問資料的方法。
    • 優化關聯順序。

5.6.2 SQL介面的繫結變數(暫時無用，簡單介紹)

最主要的用途就是在儲存過程中使用。

5.6.3 繫結變數的限制

  • 繫結變數是會話級別的，所以連線之間不能共用繫結變數控制程式碼。同樣地，一旦連線斷開，則原來的控制程式碼也不能再使用。（連線池和持久化連線可以在一定程度上緩解這個問題）
  • MySQL5.1之前，繫結變數的SQL是不能使用查詢快取的。
  • 並不是所有的時候使用繫結變數都能獲得更好的效能。如果只是執行一次SQL，那麼使用繫結變數的方式五一比直接執行多了一次額外的準備消耗階段，而且還需要一次額外的網路開銷。（要正確的使用繫結變數，還需要在使用完成之後，釋放相關的資源）
  • 當前版本下，還不能在儲存函式中使用繫結變數，但是在儲存過程中可以使用
  • 如果總是忘記釋放繫結變數資源，則在伺服器端很容易發發生資源洩漏。繫結變數SQL總是的限制是一個全侷限制，所以某一個其他的錯誤可能會對所有其它的執行緒都產生影響。
  • 有些操作，如BEGIN，無法在繫結變數中完成。

三種繫結變數型別的部分割槽別：

  • 客戶端模擬的繫結變數：客戶端的驅動程式接收到一個帶引數的SQL，再將指定的值帶入其中，最後將完整的查詢傳送到伺服器端。
  • 伺服器端的繫結變數：客戶端使用特殊的二進位制協議將帶引數的字串傳送到伺服器端，然後使用二進位制協議將具體的引數值傳送給伺服器端執行。
  • SQL介面的繫結變數：客戶端先傳送一個帶引數的字串到伺服器端，這類似與使用PREPARE的SQL語句，然後傳送設定引數的SQL，最後使用EXECUTE來執行SQL。所有這些都是用普通的文字傳輸協議。

5.7 使用者自定義函式(暫時無用，簡單介紹)

使用支援C語言呼叫約定的任何程式語言來實現使用者自定義函式(UDF)。UDF必須事先編譯後並動態連結到伺服器上。

5.8 外掛(暫時無用，簡單介紹)

外掛型別：

  • 儲存過程外掛
  • 後臺外掛
  • INFORMATION_SCHEMA外掛
  • 全文解析外掛
  • 審計外掛
  • 認證外掛

5.9 字符集和校對

字符集是指一種從二進位制編碼到某類字元符號的對映，可以參考如何使用一個位元組來表示英文字元。校對是指一組用於某個字符集的排序規則。

5.9.1 MySQL如何使用字符集

每種字符集都可能有多種校對規則，並且都有一個預設的校對規則，每個校對規則都是針對某個特定的字符集，因此把字符集和校對規則統稱為字符集。

MySQL有很多選擇用於控制字符集，這些選項和字符集很容易混淆。只有基於字元的值才真正的有字符集的概念。對於其他型別的值，字符集只是一個設定，指定用哪種字符集來做比較或者其他操作。

MySQL的設定：

  • 建立物件時的預設設定：

    • 建立資料庫的時候，將根據伺服器上character_set_server設定來設定該資料庫的預設字符集
    • 建立表的時候，將根據資料庫的字符集設定指定這個表的字符集設定
    • 建立列的時候，將根據表的設定指定列的字符集設定。
    • 以上三個階層，每一層都只是指定一個預設值，當這一層沒有指定字符集的時候，預設值才會生效。

  • 伺服器和客戶端通訊時的設定：

    • 伺服器和客戶端通訊的時候，他們可能使用不同的字符集。這時，伺服器端將進行必要的翻譯轉換工作：

      • 伺服器端總是假設客戶端是按照character_set_client設定的字元來傳輸資料和SQL語句的。
      • 當伺服器收到客戶端的SQL語句時，它先將其轉換成字符集character_set_connection。它還是用這個設定來決定如何將資料轉換成字串。
      • 當伺服器端返回資料或者錯誤資訊給客戶端時，它會將其轉換成character_set_result。

      客戶端和伺服器的字符集
      客戶端和伺服器的字符集

    • 根據需要，可以使用SET NAMES或者SET CHARACTER語句來改變上面的設定。不過在伺服器上使用這個命令只能改變伺服器端的設定。客戶端程式和客戶端的API也需要使用正確的字符集才能避免在通訊時出現問題。

MySQL比較兩個字串的大小時，通過將其轉換成同一個字符集再進行比較，如果兩個字符集不相容的話，則會丟擲錯誤。MySQL還會為每個字串設定一個“可轉換性”，這個設定決定了值的字符集的優先順序，因而會印象MySQL做字符集隱式轉換後的值。

  • 還可以使用字首和COLLATE子句來指定字串的字符集或者校對字符集。

    mysql> SELECT _utf8 'hello world' COLLATE utf8_bin;複製程式碼

一些特殊情況：

  • 詭異的character_set_database設定：當改變預設資料庫的時候，這個變數也會跟著改變。
  • LOAD DATA INFILE：資料庫總是將檔案中的字元按照字符集character_set_database來解析
  • SELECT INTO OUTFILE：將結果不做任何轉碼地寫入檔案
  • 嵌入式轉義序列：MySQL會根據character_set_client的設定來解析轉義序列，即使字串中包含字首或者COLLATE子句也一樣。因為對解析器來說，字首並不是一個指令，只是一個關鍵字。

5.9.2 選擇字符集和校對規則

  • 可以使用命令SHOW CHARACTERSET和SHOW COLLATION來檢視MYSQL支援的字符集和校對規則。

  • 極簡原則：最好先為伺服器或者資料庫選擇一個合理的字符集，然後根據不同的實際情況，讓某些列選擇合適的字符集。

  • 對於校對規則通常需要考慮的一個問題是，是否以大小寫敏感的方式比較字串，或者是以字串編碼的二進位制值來比較大小。二進位制校對規則直接使用字元的位元組進行比較，而大小寫敏感的校對規則在多位元組字符集時如德語有更復雜的比較規則。

  • MySQL如何選擇字符集和校對規則:

    MySQL如何選擇字符集和校對規則
    MySQL如何選擇字符集和校對規則

5.9.3 字符集和校對規則如何影響查詢

某些字符集和校對規則可能會需要更多的CPU操作、消耗更多的記憶體和儲存空間，甚至還會影響索引的正常使用。

  • 不同的字符集和校對規則之間的轉換可能會帶來額外的系統開銷。
    • 只有排序查詢要求的字符集與伺服器資料的字符集相同的時候，才能使用索引來排序。索引根據資料列的校對規則進行排序。
  • MySQL會在需要的時候進行字符集轉換：
    • 當時用兩個字符集不同的列來關聯兩個表的時候，MySQL會嘗試轉換其中一個列的字符集。
  • UTF-8是一種多位元組編碼，它儲存一個字元會使用變成的位元組數。在MySQL內部，通常使用一個定長的時間來儲存字串，在進行相關操作，這樣的目的是希望總是保證快取中有足夠的空間來儲存字串。
    • 在多位元組字符集中，一個字元不再是一個位元組。可以用LENGTH()和CHAR_LENGTH()來計算字串的長度。在多位元組字符集中，兩者返回的結果會不同，因此要使用後者
    • 如果要索引一個UTF-8字符集的索引，MySQL會假設每一個字元都是三個位元組，索引最長索引字首的限制一下縮短到原來的三分之一。對MySQL使用索引有一些影響，比如無法使用索引覆蓋掃描。
    • 如果全部直接使用UTF-8字符集，從效能角度來並不好，只會消耗更多的儲存空間，因為很多應用無須使用該字符集。
  • 考慮字符集需要根據儲存的具體記憶體來決定：
    • 儲存的內容主要是英文字元，可以使用UTF-8，因為其只佔用一個位元組
    • 儲存一些非拉丁語系的字元，可以使用cpl256
    • 儲存別的語言，使用UTF-8。
    • 當從某個具體的語種編碼轉成UTF-8時，儲存空間的使用會相對增加。如果使用的是InnoDB表，那麼字符集的改變可能會導致資料的大小超過可以在頁記憶體儲的臨界值，需要儲存在額外的外部儲存區，這會導致嚴重的空間浪費和空間碎片。
  • 有時候根本不需要使用任何的字符集。通常只有在做大小寫無關的比較、排序、字串操作的時候才需要使用字符集。如果資料庫不關心字符集，那麼可以直接將所有東西儲存到二進位制列中，包括UTF-8編碼資料。這麼做可能還需要一個列記錄字元的編碼集，導致很多難以排除的錯誤。因此如果可能建議儘量不要這麼做。

5.10 全文索引(暫時無用，簡單介紹)

  • 全文索引有著自己獨特的語法，沒有索引也可以工作，如果有索引效率會更高。
  • 全文索引可以支援各種字元內容的搜尋，也支援自然語言搜尋和布林搜尋。
  • 只有在MyISAM引擎支援，5.6版本後的InnoDB也已經實驗性質的支援
    • MyISAM的全文索引是一種特殊的B-Tree索引，共有兩層。第一層是所有關鍵字，然後對於每一個關鍵字的第二層，包含的是一組相關的“文件指標”

5.10.1 自然語言的全文索引

計算每一個文件物件和查詢的相關度。相關度是基於匹配的關鍵詞個數，以及關鍵詞在文件中出現的個數。在整個索引中出現次數越少的詞語，匹配的相關度就越高，相反非常常見的單詞就不會被搜尋。

5.10.2 布林全文索引

可以在查詢中自定以某個被搜尋詞語的相關性。布林搜尋通過停用詞列表過濾掉那些噪聲詞，另外還要求搜尋的關鍵詞長度必須大於ft_min_word_len並小於ft_max_word_len。搜尋返回的結果是未經排序的。

5.10.3 MySQL5.1中全文索引的變化

5.10.4 全文索引的限制和替代方案

  • 限制：
    • 只有一種影響相關性的方法：詞頻
    • 資料量大小
    • 還會影響優化器的工作。索引選擇、WHER子句、ORDER BY都有可能不是按照預計的方式工作。

5.10.5 全文索引的配置和優化

  • 定期地進行全文索引重建等日常維護可提升效能
  • 保證索引快取足夠大，從而保證所有的全文索引都能快取在記憶體中
  • 提供一個好的停用詞列表
  • 忽略一些太短的單詞可以提升全文索引的效率
  • 停用詞表和允許最小詞長都可以減少索引詞語來提升全文索引效率，但同時會降低搜尋的精確度。
  • 當向一個全文索引的表中匯入大量資料的時候，最後先DISABLE KEYS來禁用全文索引，然後在匯入資料後再ENABLE KEYS來建立全文索引。
  • 如果資料集非常大，則需要對資料進行手動分割槽，然後將資料分佈到不同的節點，再做並行的搜尋。

5.11 分散式（XA）事務

儲存引擎的事務特效能勾保證在儲存引擎級別實現ACID，而分散式事務則讓儲存引擎級別的ACID擴充套件到資料庫層面，甚至擴充套件到多個資料庫之間，這需要兩個階段提交實現：

  • 第一階段：XA事務中需要一個事務協調器來保證所有的事務參與者都完成了準備工作。
  • 第二階段：如果協調器收到所有的參與者都準備好的訊息，就會告訴所有的事務可以提交了。
  • MySQL在這個XA事務過程中扮演一個參與者的角色，而不是協調者

5.11.1 內部XA事務

  • 作用：協調內部儲存引擎和二進位制日誌
  • MySQL中各個儲存引擎是完全獨立的，彼此不知道對方的存在，所以一個跨儲存引擎的事務就需要一個外部的協調者。如果不使用XA協議，例如，跨儲存引擎的事務提交就只是順序地要求每個儲存引擎格子提交，如果在某個儲存提交過程中發生系統崩潰，就會破壞事務的特性。
  • 如果將MySQL記錄的二進位制日誌操作看作是一個獨立的儲存引擎，在儲存引擎提交的同時，需要將提交的資訊寫入二進位制檔案，這就是一個分散式事務，只不過二進位制日誌的參與者是MySQL本身。
  • XA事務為MySQL帶來巨大的效能下降。從MySQL5.0開始，它破壞了MySQL內部的“批量提交”(一種通過單磁碟IO操作完成多個事務提交的技術)，使得MySQL不得不進行多次額外的fsync()呼叫。

5.11.2 外部XA事務

  • 作用：MySQL可以參與到外部的分散式事務中
  • MySQL能夠作為參與者完成一個外部的分散式事務。但它對XA協議支援並不完整，例如，XA協議要求在一個事務中的多個連線可以做關聯，但目前版本的MySQL還不能支援。
  • 因為通訊延遲和事務參與者本身可能失敗，所以外部XA事務比內部消耗會更大。
    • 如果在廣域網中使用XA事務，通常會因為不可預測的網路效能導致事務失敗。
    • 如果有太多不可控因素，例如，不穩定的網路通訊或者使用者長時間地等待而不提交，則最好避免使用XA事務。任何可能讓事務提交發生延遲的操作代價都很大，因為它影響的不僅是自己本身，還會讓所有參與者都在等待。
  • 還可以使用別的方式實現高效能的分散式事務。例如，可以在本地寫入資料，並將其放入佇列，然後在一個更小、更快的事務中自動分發。還可以使用MySQL本身的複製機制來傳送資料。很多應用程式都可以完全避免使用分散式事務。
  • XA事務是一種在多個伺服器之間同步資料的辦法。如果由於某些原因不能使用MySQL本身的複製，或者效能不是瓶頸的時候，可以嘗試使用。

5.12 查詢快取

MySQL的快取型別：

  • 某些場景下實現快取查詢的執行計劃，對於相同型別的SQL就可以跳過SQL解析和執行計劃生成階段
  • 快取完整的SELECT查詢結果，也就是“查詢快取”

MySQL查詢快取儲存查詢返回的完整結果。當查詢命中該快取，MySQL會立刻返回結果，跳過了解析、優化和執行階段。

  • 查詢快取系統會跟蹤查詢中涉及的每個表，如果這些表發生變化，那麼和這個表相關的所有的查詢快取資料都將失效。這種機制效率看起來很低，但是實現代價很小，而這點對於一個非常繁忙的系統來說非常重要。
  • 查詢快取對應用是完全透明的。應用程式無須關心MySQL是通過查詢快取返回還是實際執行返回的結果。

隨著現在的通用伺服器越來越大，查詢快取被發現是一個影響伺服器擴充套件性的因素。它可能成為整個伺服器的資源競爭單點，在多核伺服器上還可能導致伺服器僵死。建議預設關閉查詢快取，如果查詢快取作用很大的話，那就配置一個很小的查詢快取空間(如幾十兆)。

5.12.1 MySQL如何命中查詢快取

  • 快取存放在一個引用表中，通過一個雜湊值引用，這個雜湊值包括瞭如下因素：即查詢本身、當前要查詢的資料庫、客戶端協議的版本等一些其他可能會影響返回結果的資訊。

    • 當判斷快取是否命中時，MySQL不會解析、“正規化”或者引數化查詢語句，而是直接使用SQL語句和客戶端傳送過來的其他原始資訊。任何字元上的不同，例如空格、註釋，都會導致快取不命中。

    • 當查詢語句中有一些不確定的資料時，則不會被快取。例如包含函式NOW()或者CURRENT_DATE的查詢都不會被快取。

      • 包含任何使用者自定義函式、儲存函式、使用者變數、臨時表、MySQL庫中的系統表，或者任何包含級別許可權的表，都不會被快取。
      • 在檢查查詢快取之前，MySQL通過一個大小寫不敏感的檢檢視看SQL語句是否以SEL開頭。
      • 而檢查查詢快取的時候，MySQL還不會解析SQL語句，所以MySQL並不知道查詢語句中是否包含有返回不確定資料的函式。但是MySQL在任何時候只要發現不能被快取的部分，就會禁止這個查詢被快取。
      -- 如果希望換成一個帶日期的查詢，那麼最好將其日期提前計算好，而不要直接使用函式
      ... DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY) -- Not cacheable!
      ... DATE_SUB('2007-07-14’, INTERVAL 1 DAY) -- Cacheable複製程式碼

    • 子查詢和儲存過程都沒辦法使用查詢快取，另外5.1版本之前，繫結變數也無法使用。因為查詢快取是在完整的SELECT語句基礎上的，而且只是在剛收到SQL語句的時候才檢查。

    • 查詢快取在很多時候可以提升查詢效能，但本身是一個加鎖排他操作，另外開啟查詢快取對讀和寫都會帶來額外的消耗：

      • 讀查詢在開始之前必須先檢查是否命中快取
      • 如果這個讀查詢可以被快取，那麼當完成執行後，MySQL若發現查詢快取中沒有這個查詢，會將其結果存入查詢快取，這會帶來額外的系統消耗。
      • 這對寫操作也會有影響，因為當向某個表寫入資料的時候，MySQL必須將對應表的所有快取都設定失效。如果查詢快取非常大或者碎片很多，這個操作就可能帶來很大系統消耗(設定了很多的記憶體給查詢快取用的時候)

    • 對InnoDB來說，事務的一些特性會限制查詢快取的作用。當一個語句在事務中修改了某個表，MySQL會將這個表的對應的查詢快取都設定失效，而事實上，InnoDB的多版本特性會暫時將這個修改對其它事務遮蔽。

      • 在這個事務提交之前，這個表的相關查詢是無法被快取的，所以所有在這個表上面的查詢，內部或外部的事務，都只能在該事務提交後才被快取。因此，長時間執行的事務，會大大降低查詢快取的命中率。

    • 如果查詢快取使用了很大量的記憶體，快取失效操作就可能會成為一個非常嚴重的問題瓶頸。

      • 如果快取中存放了大量的查詢結果，那麼快取失效操作時整個系統都可能會僵死一會。因為這個操作是靠一個全域性鎖操作保護的，所有需要做該操作的查詢都要等待這個鎖，而且無論是檢測是否命中快取，還是快取失效檢測都需要等待這個全域性鎖。

5.12.2 查詢快取如何使用記憶體

  • 查詢快取是完全儲存在記憶體中。

    • 除了查詢結果，需要快取的還有很多別的維護相關的資料。這些基本的管理維護資料結構大概需要40KB的記憶體資源。
    • 用於查詢快取的記憶體被分成一個個的資料塊，資料塊是變長的。
      • 每一個資料塊中，儲存了自己的型別、大小和儲存的資料本身，還外加指向前一個和後一個資料塊的指標。資料塊的型別有：儲存查詢結果、儲存查詢和資料表的對映、儲存查詢文字等等。

  • 理想流程：

    • 當伺服器啟動的時候，它先初始化查詢快取需要的記憶體。這個記憶體池初始是一個完整的空閒塊，大小就是所配置的查詢快取再減去用於維護後設資料的資料結構鎖消耗的空間。
      • 通過函式malloc()向作業系統申請記憶體，在整個流程只在初次建立查詢快取的時候執行一次。
    • 當有查詢結果需要快取的時候，MySQL先從大的空閒塊中申請一個資料塊用於儲存結果。
      • 這個資料塊需要大於引數query_cache_min_res_unit的配置，即使查詢結果遠遠小於此，仍需要至少申請query_cache_min_res_unit空間。因為需要在查詢開始返回結果的時候就分配空間，而此時是無法預知查詢結果到底有多大的，所以MySQL無法為每一個查詢結果精確分配大小恰好匹配的快取空間。
      • 這個記憶體塊會盡可能小(也可能選擇較大的，這裡不介紹細節)，然後將結果存入其中。因為需要先鎖住資料塊，然後找到合適大小的資料塊，所以相對來說，分配記憶體塊是一個非常慢的操作，MySQL儘量避免這個操作的次數。
      • 這裡的分配記憶體塊，是指在空閒塊列表中找到一個合適的記憶體塊，或者從正在使用的、待淘汰的記憶體塊中回收再使用。也就是說，MySQL自己管理記憶體而不依賴與作業系統的記憶體管理。
    • 如果資料塊全部用完，但仍有剩餘資料需要儲存，MySQL會申請一塊新資料塊(仍然是儘可能小)繼續儲存結果資料。
    • 當查詢完成時，如果申請的記憶體空間仍有剩餘，MySQL會將其釋放，並放入空閒記憶體部分。

    查詢快取如何分配記憶體來儲存結果資料
    查詢快取如何分配記憶體來儲存結果資料

  • 實際流程：

    • 假設平均查詢結果非常小，伺服器在併發地向不同的兩個連線返回結果，返回完結果後MySQL回收剩餘資料塊空間時會發現，回收的資料塊小於query_cache_min_res_unit，所以不能夠直接在後續的記憶體塊分配中使用。考慮到這種情況，資料塊的分配就更復雜些。

      • 在收縮第一個查詢結果使用的快取空間時，就會在第二個查詢結果之間留下一個“空隙”——非常小的空閒空間，因為小於query_cache_min_res_unit而不能再次被查詢快取使用。這類空隙稱為碎片，在記憶體管理、檔案系統管理上都是經典問題。
      • 有很多情況下都會導致碎片，例如快取失效時，可能導致留下太小的資料塊無法在後續快取中使用。

      查詢快取中儲存查詢結果後剩餘的碎片
      查詢快取中儲存查詢結果後剩餘的碎片

5.12.3 什麼情況下查詢快取能發揮作用

  • 只有當快取帶來的資源節約大於其本身的資源消耗時才會給系統帶來效能提升。
  • 任何SELECT語句沒有從查詢快取中返回都稱為“快取未命中”，快取未命中的原因：
    • 查詢語句無法被快取，可能是因為查詢中包含一個不確定的函式，或者查詢結果太大。這都會導致狀態值Qcache_not_cached增加
    • MySQL從未處理這個查詢，所以結果也從不曾被快取過
    • 之前快取了查詢結果，但由於查詢快取的記憶體用完，需要將某些快取清除，或者由於資料表被修改導致快取失效
  • 伺服器上有大量快取未命中，但實際上最大多數查詢都被快取了，一定是有如下情況發生：
    • 查詢快取還沒有完成預熱。也就是說，MySQL還沒有將查詢結果都快取起來。
    • 查詢語句之前從未被執行過。如果應用程式不會重複執行一條查詢語句，那麼即使完成預熱仍然會有很多快取未被命中。
    • 快取失效的操作太多。
      • 快取碎片、記憶體不足、資料修改都會導致快取失效。
      • 如果配置了足夠的快取空間，而且query_cache_min_resunit設定也合理的話，那麼快取失效應該主要是資料修改導致的。可以通過Com*檢視資料修改的情況(包括Com_update，Com_delete)，也可以通過Qcache_lowmem_prunes來檢視有多少次失效是由於記憶體不足導致的。
  • 評估是否使用查詢快取的方法：
    • 理論上，可以通過開啟或者關閉快取時候的系統效率來決定是否需要開啟查詢快取。但是很難評估查詢快取是否能夠帶來效能提升。
      • SHOW STATUS只能提供一個全域性的效能指標，也很難評估效能的提升
    • 對於那些需要消耗大量資源的查詢通常都是非常適合快取。
      • 一些彙總計算查詢，如COUNT()
      • 複雜的SELECT語句，如多表JOIN後還需要排序和分頁，這類查詢每次執行消耗都很大，但是返回的結果集卻很小，非常適合查詢快取。不過需要注意，涉及表上的UPDATE、DELETE和INSERT相比SELECT來說要非常少。
    • 判斷查詢是否有效的直接資料是命中率，就是使用查詢快取返回結果佔總查詢的比率。
      • 當MySQL接收到一個SELECT查詢時，要麼增加Qcache_hints的值，要麼增加Com_select的值。
      • 查詢快取命中率是一個很難判斷的值。命中率多大才是好的？只要查詢快取帶來的效率提升大於它的消耗，即使只有30%的命中率也可以；快取了哪些查詢也很重要，例如，被快取的查詢本身消耗非常大，即使快取命中率低也可以接受
    • 考慮快取命中率的同時，通常還需要考慮快取失效帶來的額外消耗。
      • 極端的辦法，對某一個表先做一次只有查詢的測試，並且所有的查詢都命中快取，另一個相同的表只做修改操作。這時，查詢快取的命中率是100%，但因為會給更新操作帶來額外的消耗，所以查詢快取並不一定會帶來總體效率提升。這裡，所有的更新語句都會做一次快取失效檢查，而檢查的結果都是相同的，這會給系統帶來額外的資源浪費。
    • MySQL中如果更新操作和帶快取的操作混合，查詢快取帶來的好處很難衡量。
      • 如果快取的結果在失效前沒有被任何其他的SELECT語句使用，那麼這次快取操作就是浪費時間和記憶體。
      • 可以通過檢視Qcache_select和Qcache_inserts的相對值來檢視。如果每次查詢操作都是快取未命中，然後需要將查詢結果放到快取中，這兩個值應該差不多。所以在快取完成預熱後，最好的情況是Query_inserts遠遠小於Query_select
    • 命中率和“INSERT和SELECT比例”都無法直觀地反應快取的效率，還有另一個直觀的辦法：命中和寫入的比例，即Qcache_hints和Qcache_inserts的比率
      • 根據經驗，這個比值大於3:1時通常查詢快取是有效的，最好能夠達到10:1.
      • 如果應用沒有達到這個比率，可以考慮禁用查詢快取，除非能夠通過精確的計算得知：命中帶來的效能提升大於快取失效的消耗，並且查詢快取並沒有成為系統的瓶頸。
    • 觀察查詢快取記憶體的實際使用情況，來確定是否需要縮小或者擴大查詢快取。
      • 如果查詢快取達到幾十兆這樣的數量級，是有潛在風險的。(這和硬體以及系統大小有關)
    • 需要和系統的其他快取一起考慮，如InnoDB的快取池，或者MyISAM的索引快取。
    • 最好的判斷查詢快取是否有效的辦法還是通過檢視某類查詢時間消耗是否增大或者減少來判斷。

5.12.4 如何配置和維護查詢快取·

  • 配置：

    • query_cache_type:是否開啟查詢快取。可以是ON、OFF或者DEMAND。DEMAND表示只有在查詢語句中明確寫明SQL_CACHE的語句才放入查詢快取。這個變數可以是會話級別也可以是全域性級別。
    • query_cache_size：查詢快取使用的總記憶體空間，單位是位元組，必須是1024的整數倍，否則MySQL實際分配的資料可能會有不同。
    • query_cache_min_res_unit：在查詢快取中分配記憶體塊時的最小單位。
    • query_cache_limit：MySQL能夠快取的最大查詢結果。
      • 如果查詢結果大於這個值，則不會被快取。因為查詢快取在資料生成的時候就開始嘗試快取資料，所以只有當結果全部返回後，MySQL才知道查詢結果是否超出限制
      • 如果超出，MySQL則增加狀態值Qcache_not_cached，並將結果從查詢快取中刪除。如果事先知道有很多這樣的情況發生，那麼建議在查詢語句中加入SQL_NO_CACHE來避免查詢快取帶來的額外消耗。
    • query_cache_wlock_invalidate：如果某個資料表被其他的連線鎖住，是否仍然從查詢快取中返回結果。預設是OFF。

  • 減少碎片

    • 沒有什麼辦法能夠完全避免碎片，但是合適的query_cache_min_res_unit可以減少由碎片導致的記憶體空間浪費。
      • 設定合適的值可以平衡每個資料塊的大小和每次儲存結果時記憶體塊的申請次數，其實是在平衡記憶體浪費和CPU消耗。
      • 這個值太小，則浪費的空間更少，但是會導致更頻繁的記憶體塊申請操作。如果太多，則碎片會很多。
    • 這個引數的最合適大小和應用程式的查詢結果的平均大小直接相關。
      • 可以通過記憶體實際消耗(query_cache_size-Qcache_free_memory)除以Qcache_queries_in_cache計算單個查詢的平均快取大小。
      • 如果查詢結果大小很不均勻，那麼碎片和反覆的記憶體塊分配可能無法避免。
      • 如果發現快取了一個非常大的結果，可以通過引數query_cache_limit限制可以快取的最大查詢結果。
    • 可以通過引數Qcache_free_blocks來觀察碎片，反映了查詢快取中記憶體塊的多少。
      • 如果Qcache_free_blocks恰好達到Qcache_total_blocks/2，那麼查詢快取就有嚴重的碎片問題。
      • 如果還有很多空閒塊，而狀態值Qcache_lowmem_prunes還不斷增加，則說明由於碎片導致了過早的刪除查詢快取結果。
    • 可以使用FLUSH QUERY CACHE完成碎片整理。這個命令會將所有的查詢快取重新排序，並將所有的空閒空間都聚集到查詢快取的一塊區域上。
      • 會訪問所有的查詢快取，在這期間任何其他的連線都無法訪問查詢快取，從而導致伺服器僵死一段時間。因此，建議保持查詢快取空間足夠小
      • 清空快取由RESET QUERY CACHE完成

  • 提高查詢快取的使用率

    • 如果查詢快取不再有碎片問題，但命中率仍然很低，還可能是查詢快取記憶體空間太小導致的。如果MySQL無法為一個新的查詢快取結果的時候，則會刪除某個老的查詢快取
    • 當刪除老的查詢快取時，會增加狀態值Qcache_lowmem_prunes。如果這個值增長的很快，可能是由以下兩個原因導致的：
      • 如果還有很多空閒塊，那麼碎片可能就是罪魁禍首
      • 如果這時沒什麼空閒塊，就說明在這個系統壓力下，分配的查詢快取空間不夠大。可以通過檢查狀態值Qcache_free_memory來檢視還有多少沒有使用的記憶體。

    如何分析和配置查詢快取
    如何分析和配置查詢快取

5.12.5 InnoDB和查詢快取

  • 因為InnoDB有自己的MVVC機制，InnoDB會控制在一個事務中是否可以使用查詢快取，InnoDB會同時控制對查詢快取的讀和寫操作。
    • 事務是否可以訪問查詢快取決定於當前的事務ID，以及對應的資料表上是否有鎖。每一個InnoDB表的記憶體資料字典都儲存了一個事務ID，如果當前事務ID小於該事務ID，則無法訪問查詢快取。
    • 如果表上有任何鎖，那麼對這個表的任何查詢語句都是無法被快取的。
  • InnoDB下的查詢快取：
    • 所有大於該表計數器的事務才可以直接使用(讀和寫)查詢快取。
    • 該表的計數器並不是直接更新為對該表進行加鎖操作的事務ID，而是被更新成一個系統事務ID。所以，會發現該事務自身後續的更新操作也無法讀取和修改查詢快取。
  • 查詢快取儲存、檢索和失效操作都是在MySQL層面完成，InnoDB無法繞過或者延遲這個行為。
    • 但是InnoDB可以在事務中顯式地告訴MySQL何時應該讓某個表的查詢快取都失效。在有外來鍵限制的時候這是必須的，例如某個SQL有ON DELETE CASCADE。
  • 原則上，在InnoDB的MVVC架構下，當某些修改不影響其他事務讀取一致的資料時，是可以使用查詢快取的。但是這樣實現起來會很負責，InnoDB做了簡化，讓所有有加鎖操作的事務都不使用任何查詢快取，這個限制不是必須的。

5.12.6 通用查詢快取優化

  • 用多個小表代替一個大表對查詢快取有好處。這個設計將會使得失效策略能夠在一個更合適的粒度上進行。當然，不要讓這個原則過分影響設計，畢竟其它的一些優勢很容易就能彌補。
  • 批量寫入時只需要做一次快取失效，所以相比單條寫入的效率要高。注意不要同時做延遲寫和批量寫，否則可能會導致伺服器僵死較長時間。
  • 因為快取空間太大，在過期操作的時候可能會導致伺服器僵死。一個簡單的辦法就是控制快取空間的大小，或者直接禁用查詢快取。
  • 無法在資料庫或則表級別控制查詢快取，但是可以通過SQL_CACHE和SQL_NO_CACHE來控制某個SELECT語句是否需要進行快取。還可以修改會話級別的query_cache_type來控制查詢快取。
  • 對於寫密集型的應用來說，直接禁用查詢快取可能會提高系統效能。關閉查詢快取可以移除所有相關的消耗，例如將query_cache_size設定為0
  • 因為對互斥訊號量的競爭，有時直接關閉查詢快取對讀密集型的應用也會有好處。
  • 如果不想所有的查詢都進入查詢快取，可以將query_cache_type設定為DEMAND，然後在希望快取的查詢中加上SQL_CACHE

5.12.7 查詢快取的替代方案

查詢快取的工作原則是：執行查詢最快的方式就是不去執行。但是查詢仍然要傳送到伺服器端，伺服器端還需要做一點點工作。因此可以直接在客戶端進行快取。

5.13 總結

  • 分割槽表：分割槽表是一種粗粒度的、簡易的索引策略，適用於大資料量的過濾場景。最適合的場景是，在沒有合適的索引時，對其中幾個分割槽進行全表掃描，或者是隻有一個分割槽和索引是熱點，而且這個分割槽和索引都能夠在記憶體中；限制單表分割槽數不要超過150個，並且注意某些導致無法做分割槽過濾的細節，分割槽表對於單條記錄的程式並沒有什麼優勢，需要注意這類查詢的效能。
  • 檢視：對好幾個表的複雜查詢，使用檢視有時候會大大簡化問題。當檢視使用臨時表時，無法將WHERE條件下推到各個具體的表，也不能使用任何索引，需要特別注意這類查詢的效能。如果為了遍歷，使用檢視是很合適的。
  • 外來鍵：外來鍵限制會將約束放到MySQL中，這對於必須維護外來鍵的場景，效能會更高。不過這也會帶來額外的複雜性和額外的索引消耗，還會增加多表之間的互動，會導致系統中有更多的鎖和競爭。外來鍵可以被看作是一個確保系統完整性的額哇的特性，但是如果設計的是一個高效能的系統，那麼外來鍵就會顯得很臃腫了。很多人在更在意系統的效能的時候都不會使用外來鍵，而是通過應用程式來維護。
  • 儲存過程
  • 繫結變數
  • 外掛
  • 字符集：字符集是一種位元組到字元之間的對映，而校對規則是指一個字符集的排序方法。很多人都使用Latin1(預設字符集，對英語和某些歐洲語言有效)或者UTF-8。如果使用的是UTF-8，那麼在使用臨時表和緩衝區的時候需要注意：MySQL會按照每個字元三個位元組的最大佔用空間來分配儲存空間，這可能消耗更多的記憶體或者磁碟空間。注意讓字符集和MySQL字符集配置相符，否則可能會由於字符集轉換讓某些索引無法正常工作。
  • 全文索引
  • XA事務：很少會有人用MySQL的XA事務特性。除非你真正明白引數innodb_support_xa的意義，否則不要修改這個引數的值，並不是只有顯示使用XA事務時才需要設定這個引數。InnoDB和二進位制日誌也是需要使用XA事務來做協調的，從而確保在系統崩潰的時候，資料能夠一致地恢復。
  • 查詢快取：完全相同的查詢在重複執行的時候，查詢快取可以立即放回結果，而無須在資料庫中重新執行一次。根據經驗，在高併發壓力環境中查詢快取會導致系統效能的下降，甚至僵死。如果一定要使用查詢快取，那麼不要設定太大記憶體，而且只有在明確收益的時候才使用。查詢快取是一個非常方便的快取，對應用程式完全透明，無須任何額外的編碼，但是如果希望有更高效的查詢快取，建議使用memacched等其他快取方案。
  • ​

相關文章