Posted Updated develop23 minutes read (About 3510 words)

MySQL 調優 | Charset & Collation 不一致導致的 JOIN 索引失效

放假前夕, 海外團隊請我們協助查詢出一些舊系統相關的交易資料, 資料也算平易近人, 在簡短討論 15 分鐘後就下線去組 SQL 了, 結果當我 JOIN 到指定 table 的時後, 發現查詢速度竟然顯著的下降, 到我完全不能接受的程度.

當年唸書時學長有交代一系列的 SQL 調優流程, 在多年的 CRUD 的荼毒下 在年假氣氛的渲染下我只記得先下個 EXPLAIN 看看.

type Extra
ALL Using where; Using join buffer (Block Nested Loop)

Using where; Using join buffer (Block Nested Loop) ?!

恩… 這問題大了, type 都 ALL 了, 我印象中 Using join buffer (Block Nested Loop) 代表 JOIN 的時候沒有有效地使用 INDEX, SQL 是直接暴力在比對資料, 而且這個系統用的應該是 MySQL 6 以前的版本.

這個問題的原由一般常見的是 JOIN 欄位沒有設 index, 但是這次的欄位都是用 order_id 這種一看就該下索引的欄位, 且這個 id 是字母 + 數字的形式, 基本上也可以排除兩個表的 id JOIN 型別不同的問題 (例如一張表用 int 一張表用 varchar, 這樣 MySQL 就需要 CAST(o.id AS char)), 同時 JOIN 條件也沒有函式讓索引失效(如 JOIN xxx ON LOWER(xxx.email) = LOWER(ooo.email)).

在 SHOW INDEX 無果後就針對這兩張表的 id 去查 SHOW FULL COLUMNS, 果然看出了一些端倪:

Field Type Collation
order_id varchar(32) utf8_general_ci

和

Field Type Collation
order_id varchar(32) utf8mb4_bin

啊你們的 charset 和 Collation 怎麼都不一樣…

稍微查證了一下, 原來兩個表是由不同團隊開發和維護，一個使用 utf8；後來新建的使用 utf8mb4。兩邊的 charset 與 collation 不一致，導致 MySQL 在 JOIN 時必須做隱式轉換(Implicit conversion)，索引因此失效。

再查證一遍 MySQL EXPLAIN 的 Output, 果然看到了 convert(xxx.order_id using utf8mb4)..., 就是 charset & collation mismatch 導致 Index 全部失效了.

utf8 我記得是 3-byte UTF-8(現在應該會叫 utf8mb3), utf8mb4 才是完整 4-byte UTF-8, 接著順便上網看了一下細節, utf8mb4_general_ci 和 utf8mb4_bin 的差異:

collation 說明
utf8mb4_general_ci 不區分大小寫
utf8mb4_bin binary 精準比較
比較 general_ci bin
A = a true false
銀行 = 银行 可能 true false

找到原因修正起來也很簡單, 和主管簡單回報了這件事情後, 我回家也讓 Claude 幫我生成了兩組資料做測試, 重新模擬一次這個問題.

不過是說 production 都沒有相關的效能問題嗎？還是只有 sit/uat 沒同步, 我也無從得知.

實驗 A：Charset Mismatch（utf8 vs utf8mb4）

模擬我在工作中遇到的情況, 舊系統 charset 用 utf8, 新服務用 utf8mb4, 跨庫 or 新舊表 JOIN 時產生效能問題.

環境準備

  1. docker-compose 啟動一個 Mysql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
version: '3.8'

services:
  mysql:
    image: mysql:8
    container_name: all-you-can-test-mysql
    restart: unless-stopped
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: imroot1234
      MYSQL_DATABASE: all-tests
    volumes:
      - ./mysql_data:/var/lib/mysql
  1. 建立 DATABASE (utf8mb4)
1
2
CREATE DATABASE IF NOT EXISTS charset_demo DEFAULT CHARSET utf8mb4;
USE charset_demo;

場景說明

  • 商品表 product：模擬舊系統，建表時用 utf8（歷史遺留）
  • 訂單明細表 order_item：模擬新系統，建表時用 utf8mb4
  • JOIN 欄位：product_code（商品編碼）

建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 商品表（舊系統 —— utf8 + utf8_general_ci）
DROP TABLE IF EXISTS product;

CREATE TABLE product (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_code VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    INDEX idx_product_code (product_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- 訂單明細表（新系統 —— utf8mb4 + utf8mb4_bin）
DROP TABLE IF EXISTS order_item;

CREATE TABLE order_item (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(48) NOT NULL,
    product_code VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_product_code (product_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

這裡備註一下, 你可能會看到:

1
2
3
4
5
6
7
8
# 這個是回傳 warning, 可以無視
Unknown table 'charset_demo.product'

# 重點是這個
'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
'utf8mb3_general_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
'utf8mb3_general_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.

MySQL 官方已宣告 utf8mb3 將被移除, 所以其實在 MySQL 8 的環境下跑, 自然就會收到警示訊息, 不過我們現在是模擬新舊系統, 所以理解後忽略即可.

執行完後可以在 ide 內看到字元集 product 是 utf8mb3, order_item 是 utf8mb4.

灌入測試資料

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 商品表：50,000 筆
INSERT INTO product (product_code, product_name, category, price)
SELECT
    CONCAT('SKU', LPAD(n, 8, '0')),
    CONCAT('Product-', n),
    ELT(1 + FLOOR(RAND()*10),
        'Electronics','Clothing','Food','Furniture','Sports',
        'Books','Toys','Beauty','Health','Garden'),
    ROUND(10 + RAND()*9990,2)
FROM (
    SELECT @row := @row + 1 AS n
    FROM information_schema.columns,
         (SELECT @row := 0) t
    LIMIT 50000
) x;

-- 訂單明細表：200,000 筆
INSERT INTO order_item (order_no, product_code, quantity, unit_price, created_at)
SELECT
    CONCAT('ORD', LPAD(FLOOR(1 + RAND() * 80000), 8, '0')),
    CONCAT('SKU', LPAD(FLOOR(1 + RAND() * 50000), 8, '0')),
    FLOOR(1 + RAND() * 10),
    ROUND(10 + RAND() * 9990, 2),
    DATE_ADD('2024-01-01', INTERVAL FLOOR(RAND() * 365) DAY)
FROM (
    SELECT @row := @row + 1 AS n
    FROM information_schema.columns c1,
         information_schema.columns c2,
         (SELECT @row := 0) t
    LIMIT 200000
) x;

Step 1：確認 Charset / Collation 不一致

1
2
3
4
5
SHOW FULL COLUMNS FROM product LIKE 'product_code';
-- utf8mb3_general_ci

SHOW FULL COLUMNS FROM order_item LIKE 'product_code';
-- utf8mb4_bin

Step 2：EXPLAIN 觀察問題

1
2
3
4
5
6
7
8
9
10
11
EXPLAIN
SELECT
    p.product_code,
    p.product_name,
    p.category,
    SUM(oi.quantity) AS total_qty,
    SUM(oi.quantity * oi.unit_price) AS total_sales
FROM order_item oi
LEFT JOIN product p ON oi.product_code = p.product_code
GROUP BY p.product_code, p.product_name, p.category
ORDER BY total_sales DESC;

結果（問題狀態）

table type possible_keys key rows Extra
oi ALL NULL NULL 199,500 Using temporary; Using filesort
p ALL idx_product_code NULL 3,690 Range checked for each record (index map: 0x2)

首先看 p 表的 possible_keys 顯示 idx_product_code, 等於有索引，但 key 是 NULL, 表示實際沒用到. 雖然 MySQL 知道有索引可用，但因為 charset/collation 不一致，實際上無法有效利用, 最壞情況下的比較量級是 200,000 × 50,000 = 100 億次 (至於 rows 數量差異是 MySQL 估算值, 不用太在意).

再來看 Extra 跑出來的內容不再是 Using where; Using join buffer (Block Nested Loop), 取而代之的是 Range checked for each record (index map: 0x2).

在 MySQL 5.7 中 Block Nested Loop, 直接放棄索引，把資料塞進 join buffer 暴力比對, 而 MySQL 8.0 Range checked for each record 是對每一筆 order_item 的資料都重新評估一次這筆值用索引划不划算.

本質上還是因為 collation mismatch 導致無法直接走 B-Tree lookup

另外看 SHOW WARNINGS 的輸出, 幫大家畫重點:

Cannot use ref access on index ‘idx_product_code’ due to type or collation conversion on field ‘product_code’

雖然 8.0 的 rewritten query 中不像 5.7 會顯式出現 convert()，但這句 warning 已經明確說明因為 type 或 collation 的轉換，索引無法使用

1
2
Cannot use ref access on index 'idx_product_code' due to type or collation conversion on field 'product_code'
/* select#1 */ select `charset_demo`.`p`.`product_code` AS `product_code`,`charset_demo`.`p`.`product_name` AS `product_name`,`charset_demo`.`p`.`category` AS `category`,sum(`charset_demo`.`oi`.`quantity`) AS `total_qty`,sum((`charset_demo`.`oi`.`quantity` * `charset_demo`.`oi`.`unit_price`)) AS `total_sales` from `charset_demo`.`order_item` `oi` left join `charset_demo`.`product` `p` on((`charset_demo`.`oi`.`product_code` = `charset_demo`.`p`.`product_code`)) where true group by `charset_demo`.`p`.`product_code`,`charset_demo`.`p`.`product_name`,`charset_demo`.`p`.`category` order by `total_sales` desc

綜上所述，MySQL 在比較時需要將 utf8 側的 p.product_code 轉換為 utf8mb4，這個轉換導致 B-Tree 索引無法使用，退化為全表掃描

Step 3：修復

1
2
ALTER TABLE product
MODIFY COLUMN product_code VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;

Step 4：修復後 EXPLAIN

1
2
3
4
5
6
7
8
9
EXPLAIN
SELECT
    p.product_code, p.product_name, p.category,
    SUM(oi.quantity) AS total_qty,
    SUM(oi.quantity * oi.unit_price) AS total_sales
FROM order_item oi
LEFT JOIN product p ON oi.product_code = p.product_code
GROUP BY p.product_code, p.product_name, p.category
ORDER BY total_sales DESC;

修復後結果

table type key rows Extra
oi ALL NULL 199,500 Using temporary; Using filesort
p ref idx_product_code 1 NULL

p 表從掃描 50,000 行變為每次只查 1 行，Range checked for each record 消失。

實驗 B：Collation-Only Mismatch（同 charset，不同 collation）

既然都在測了，順便也驗證一下另一種情況：charset 相同都是 utf8mb4，但 collation 不同。這在實務中也算常見, 不同團隊建表時各自選了不同的 collation 預設值。

場景說明

  • 商品表 product_b：使用 utf8mb4_general_ci（某些框架的預設值）
  • 訂單明細表 order_item_b：使用 utf8mb4_bin（另一團隊偏好精確比對）

建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 商品表（utf8mb4_general_ci）
DROP TABLE IF EXISTS product_b;
CREATE TABLE product_b (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_code VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    INDEX idx_product_code (product_code)
) ENGINE=InnoDB;

-- 訂單明細表（utf8mb4_bin）
DROP TABLE IF EXISTS order_item_b;
CREATE TABLE order_item_b (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(48) NOT NULL,
    product_code VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_product_code (product_code)
) ENGINE=InnoDB;

灌入測試資料

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 商品表 B：50,000 筆
INSERT INTO product_b (product_code, product_name, category, price)
SELECT
    CONCAT('SKU', LPAD(n, 8, '0')),
    CONCAT('Product-', n),
    ELT(1 + FLOOR(RAND()*10),
        'Electronics','Clothing','Food','Furniture','Sports',
        'Books','Toys','Beauty','Health','Garden'),
    ROUND(10 + RAND()*9990, 2)
FROM (
    SELECT @row := @row + 1 AS n
    FROM information_schema.columns,
         (SELECT @row := 0) t
    LIMIT 50000
) x;

-- 訂單明細表 B：200,000 筆
INSERT INTO order_item_b (order_no, product_code, quantity, unit_price, created_at)
SELECT
    CONCAT('ORD', LPAD(FLOOR(1 + RAND()*80000), 8, '0')),
    CONCAT('SKU', LPAD(FLOOR(1 + RAND()*50000), 8, '0')),
    FLOOR(1 + RAND()*10),
    ROUND(10 + RAND()*9990, 2),
    DATE_ADD('2024-01-01', INTERVAL FLOOR(RAND()*365) DAY)
FROM (
    SELECT @row := @row + 1 AS n
    FROM information_schema.columns c1,
         information_schema.columns c2,
         (SELECT @row := 0) t
    LIMIT 200000
) x;

Step 1：確認 Collation 不一致

1
2
3
4
5
SHOW FULL COLUMNS FROM product_b LIKE 'product_code';
-- utf8mb4_general_ci

SHOW FULL COLUMNS FROM order_item_b LIKE 'product_code';
-- utf8mb4_bin

Step 2：EXPLAIN 觀察

1
2
3
4
5
6
7
8
9
EXPLAIN
SELECT
    p.product_code, p.product_name, p.category,
    SUM(oi.quantity) AS total_qty,
    SUM(oi.quantity * oi.unit_price) AS total_sales
FROM order_item_b oi
LEFT JOIN product_b p ON oi.product_code = p.product_code
GROUP BY p.product_code, p.product_name, p.category
ORDER BY total_sales DESC;
table type possible_keys key rows Extra
oi ALL NULL NULL 199,500 Using temporary; Using filesort
p ALL idx_product_code NULL 3,701 Range checked for each record (index map: 0x2)

可以看到結果一樣是 Range checked for each record (index map: 0x2).

Step 3：修復

1
2
ALTER TABLE product_b
MODIFY COLUMN product_code VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;

知識總結

為什麼 Charset Mismatch 比 Collation Mismatch 更致命？

比較 Charset Mismatch Collation-Only Mismatch
例子 utf8 vs utf8mb4 utf8mb4_general_ci vs utf8mb4_bin
是否需要字符集轉換 是，必須 CONVERT() 不需要，只需統一排序規則
索引是否失效 100% 失效 通常失效，但取決於版本和組合
MySQL 隱式行為 自動將低位元組的一方升級（utf8 -> utf8mb4） 根據 coercibility 規則選一方轉換

Charset 不同必然導致 collation 不同（因為 collation 隸屬於 charset），所以 charset mismatch 同時觸發兩層問題。

MySQL 隱式轉換的規則

MySQL 依照 coercibility 等級決定轉換方向：

  1. 字面值（coercibility = 4）讓步於欄位（coercibility = 2）
  2. 欄位之間，utf8 會被升級為 utf8mb4（superset wins）
  3. 被轉換的那側欄位被包在函數裡 → B-Tree 索引無法使用

如何提前發現？

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 1. 檢查 JOIN 兩側的 charset 和 collation
SHOW FULL COLUMNS FROM table_a LIKE 'join_column';
SHOW FULL COLUMNS FROM table_b LIKE 'join_column';

-- 2. EXPLAIN 後看 SHOW WARNINGS
EXPLAIN SELECT ... ;
SHOW WARNINGS;
-- 看有沒有 convert() 或 collation 轉換

-- 3. 批量掃描整個資料庫中同名欄位的不一致
SELECT
    t1.TABLE_NAME AS table_1,
    t1.COLUMN_NAME AS column_name,
    t1.CHARACTER_SET_NAME AS charset_1,
    t1.COLLATION_NAME AS collation_1,
    t2.TABLE_NAME AS table_2,
    t2.CHARACTER_SET_NAME AS charset_2,
    t2.COLLATION_NAME AS collation_2
FROM information_schema.COLUMNS t1
JOIN information_schema.COLUMNS t2
    ON t1.COLUMN_NAME = t2.COLUMN_NAME
    AND t1.TABLE_SCHEMA = t2.TABLE_SCHEMA
    AND t1.TABLE_NAME < t2.TABLE_NAME
WHERE t1.TABLE_SCHEMA = 'my_database'
    AND (t1.CHARACTER_SET_NAME != t2.CHARACTER_SET_NAME
         OR t1.COLLATION_NAME != t2.COLLATION_NAME);

整體建議

由於這次是回去摸舊的系統, 現在較新的專案都推薦一律用 utf8mb4, 因為 MySQL 的 utf8mb3 只支援 3 字節，不是真正的 UTF-8, 然後在設計 DB 時也應該要有詳細的規章與規範, 來統一整個專案的 collation, 通常是推薦 utf8mb4_0900_ai_ci (語義正確的排序)或 utf8mb4_bin(精確比對).

若專案有 DBA 的話, 應該可以在 my.cnf 層級設定預設值, 避免依賴開發者記得在每張表指定, 但老實說多數中型團隊還不一定會有 DBA, 通常都是一個 倒楣 資深的工程師去兼顧, 另外我自己也推薦使用 flyway DB, 用 git 管理下方便在 Code Review 時檢查 DDL, 特別是跨團隊、跨服務的共用欄位.

不過我最推薦的還是在 CI pipeline 裡加一個 lint step, 跑上面 information_schema 的查詢，當有不一致就直接 fail build, 就可以提前發現問題.

結語

不得不說, 做 CRUD 久了, 真的會忘記一些在 SQL 上調優的流程與手法, 這次在測試的時候發現 EXPLAIN 的輸出跟我預期的不太一樣, 也比較了一下 MySQL 5.7 和 8 在 EXPLAIN 上的差別, 不僅重新複習了一下, 也學到了不少新知識.

最後是想感嘆, 一個團隊的運作, 往往細節決定成敗, 這次 Charset 和 Collation 的問題其實也反映出同一個專案在不同團隊下管理與承接時, 是否有細部的交接與比對, 還是只要能運作就好? 這些問題也會隨著時間逐漸增加解決難度, 比如我上面輕描淡寫的說使用 ALTER TABLE 修復, 但實際在生產環境, 這會需要評估資料的影響範圍, 執行與鎖表時間等, 那真的不如我們把問題扼殺在搖籃裡面.

如同 0.95 * 0.95 一樣, 長此以往, 終有一天會跌落我們能接受的及格線.

#
Follow

Categories

Recents

Archives

Tags

Agile1
BFF1
Cache1
Casha5
Clean Architecture3
DB4
DDD2
Design Pattern4
DevOps1
Exception3
Garage1
Grafana1
Jmeter1
Life4
MQ2
MinIO1
Observation1
Prometheus1
RBAC1
Redis3
Rstar2
S3 Bucket1
SDLC1
SOLID2
Saga2
Spring Cloud6
Spring boot2
System Design4
chit-chat1
colorly4
develop22
flow1
github Actions1
troll2
×