MySQL Index 設計第一節 - 從 Log 分析 Query

Schema 設計是根據 data,而 Index 設計是根據 Query

Relational schema design is based on data, but Index design is based on queries.

原文出處: how-to-design-indexes-really

design table is different from b

收集 Query Log

  • 打開 slow-query_log 設定:
    mysql> SET GLOBAL slow_query_log = ON;
  • 設定 query 執行時間 > 0s 就進行 log:
    mysql> SET GLOBAL long_query_time = 0;

pt-query-digest

  • Install pt-query-digest tool
  • 查詢自己 Slow query log 的存放位置
    mysql> show variables like '%slow_query%';
    +---------------------+-----------------------------------+
    | Variable_name       | Value                             |
    |---------------------+-----------------------------------|
    | slow_query_log      | ON                                |
    | slow_query_log_file | /usr/local/var/mysql/win-slow.log |
    +---------------------+-----------------------------------+
    2 rows in set
    Time: 0.025s
  • 用法:
    $ pt-query-digest /usr/local/var/mysql/win-slow.log > ~/pgd.txt

我們會看到類似以下的圖 Your mysql digest profile.

### 如何解讀 profile 資訊
# Profile
.# Rank Query ID           Response time   Calls R/Call  Apdx V/M   Item
.# ==== ================== =============== ===== ======= ==== ===== =======
.#    1 0xA8D2BBDE7EBE7822 4932.2992 28.8%    78 63.2346 0.00 5.22 SELECT person_info
.#    2 0xFE25DAF5DBB71F49 4205.2160 24.6%   130 32.3478 0.00 3.47 SELECT title
.#    3 0x70DAC639802CA233 1299.6269  7.6%    14 92.8305 0.00 0.17 SELECT cast_info
.#    4 0xE336B880F4FEC4B8 1184.5101  6.9%   294  4.0289 0.36 2.29 SELECT cast_info
.#    5 0x60550B93960F1837  905.1648  5.3%    60 15.0861 0.05 1.33 SELECT name
.#    6 0xF46D5C09B4E0CA2F  777.2446  4.5% 16340  0.0476 1.00 0.17 SELECT char_name
.#    7 0x09FCFFF0E5BC929F  747.4346  4.4%   130  5.7495 0.53 7.69 SELECT name
.#    8 0x9433950BE12B9470  744.1755  4.4% 14368  0.0518 1.00 0.18 SELECT name
.#    9 0x4DC0E044996DA715  448.5637  2.6%   130  3.4505 0.65 8.31 SELECT title
.#   10 0x09FB72D72ED18E93  361.1904  2.1%    78  4.6306 0.28 1.89 SELECT cast_info title
  • Rank 1: 呼叫數次 78 次,呼叫時間花費超過 1 分鐘 (calls: 78, R/Call: 63.2346)
  • Rank 6: 呼叫頻率很高的 Query ,每次都很快 (calls: 16340, R/Call: 0.0476)

案例 - 電話簿情境

以電話簿為例,我們對電話簿打 index, 其順序為 last_name, first_name, phone_number (順序很重要)

mysql> CREATE INDEX phone_idx ON TelephoneBook (`last_name`, `first_name`, `phone_number`);

➤ 簡易條件搜尋

mysql> SELECT * FROM TelephoneBook WHERE `last_name` = 'Smith';
  • 由於 last_name 的 index 已經有預先排序的 (pre-sorted) ,所以這對上述的查詢是有提升速度的幫助
  • 有效減小搜尋範圍,因為要查詢的內容已經排序過了

➤ 混合條件搜尋

mysql> SELECT * FROM TelephoneBook WHERE `last_name` = 'Smith' AND `first_name` = 'John';
  • 同時搜尋 last_name 跟 first_name 一樣是有助益的

➤ Index 失敗的搜尋

mysql> SELECT * FROM TelephoneBook WHERE `first_name` = 'John';
  • 這是無法預測的分佈,任何人的 first_name 都可能是 John,故需要做 scan whole table 全表搜尋
順序影響問題 (Order Matters)
  • 順序是 index 被定義的關鍵
  • 只搜尋 last_name 欄位是會吃到 index 的; 如果只搜尋 first_name 會吃不到 index。代表 index 的順序是影響查詢是否吃到 index 的關鍵
  • 需要再多打一組 first_name 在前的 index 才能使以 first_name 為條件搜尋的 query 可以吃到 index
    mysql> CREATE INDEX phone_idx2 ON TelephoneBook (`first_name`, `last_name`, `phone_number`);
  • 如果搜尋兩個條件都是使用 = (equality),那麼欄位的順序對 index 不會有所影響
    mysql> SELECT * FROM TelephoneBook WHERE `last_name` = 'Smith' AND `first_name` = 'John';

➤ 範圍式比較 (Range Comparisons)

模糊搜尋 last_name 為 S 開頭的資料

mysql> SELECT * FROM TelephoneBook WHERE `last_name` = 'S%';

  • Index 可以讓我們減少搜尋範圍,只找 S 開頭的資料避免全部搜尋。故此例 index 仍然對搜尋有幫助

➤ 混合式範圍式比較 (Compound Range Comparisons)

範圍搜尋 last_name 同時指定 first_name 的情境

mysql> SELECT * FROM TelephoneBook WHERE `last_name` = 'S%' AND `first_name` = 'John';

  • 對整體來說,first_name 仍然處於無法預測的分佈,因為 John 並沒有和 last_name 是 S 開頭的組合在一起。故我們需要對所有 last_name 為 S 開頭的資料做全部查詢
順序影響問題 (Order Matters)

在 Range search 中,index 在查詢時無法幫助到後面的 (subsequent) 欄位。

根據先前打的 index:

mysql> CREATE INDEX phone_idx ON TelephoneBook (`last_name`, `first_name`, `phone_number`);

  • 第一欄 - last_name: 首欄對 Range search 有幫助
  • 第二欄 - fisrt_name: subsequent 後續的欄位是沒有助益的

➤ 排序 (Sorting by Index)

mysql> SELECT * FROM TelephoneBook WHERE `last_name` ORDER BY `first_name`;
這裏的 ORDER BY 沒有做任何事,因為先前打的 index 已經就是預排序 (pre-sorted) 了

➤ Index 無法幫助 Sorting

mysql> SELECT * FROM TelephoneBook WHERE `last_name` ORDER BY `phone_number`;
  • Sorting 任一欄位是另一個故事了 0rz.. 汗
  • phone_idx 的 index 已經是先對 last_name 再對 first_name 排序了,但並沒有對 phone_number 在數字上排序
順序影響問題 (Order Matters)
  • 如果搜尋條件順序是按照 index 的欄位順序,那麼 index 是有助益的
  • 但如果我們需要在 index 後面在進行另一種排序,或者需要排序的沒在索引中的欄位,那麼這將會很困難

➤ Index-Only 搜尋

如果我們需要的欄位是搜尋條件之外的欄位

mysql> SELECT `phone_number` FROM TelephoneBook WHERE `last_name` = 'Smith' AND `phone_number` = 'John';

  • 不需要額外成本,因為一開始 phone_idx 的 index 一次就是打 3 個欄位,就算我們在 query 時沒有明確地指定 phone_number

➤ Index 失敗的案例

如果我們需要的欄位不包含在 index 中,需要額外的搜尋成本

mysql> SELECT `business_hours` FROM TelephoneBook WHERE `last_name` = 'Smith Plumbing';

  • 這就是 extra work. 需要額外的作業取得 business_hours 的資訊
欄位影響問題 (Columns Matter)
  • 對一般的 Query 來說,Index 中欄位的配置是很重要的,即使 Query 中沒有用到欄位拿來做搜尋或排序

看完第一節 - 從 Log 分析 Query

進入第二節了嗎?

第二節: MySQL Index 設計 - 三星評分法則



文章系列

  1. MySQL 效能 - How to design Indexes, Really
  2. MySQL Index 設計第一節 - 從 Log 分析 Query
  3. MySQL Index 設計第二節 - 三星評分法則
  4. MySQL Index 設計第三節 - 檢驗與回顧設計不良的 Index