資料庫

最經典的陷阱：utf8 不是真正的 UTF-8

在 MySQL 與 MariaDB 中，名為 utf8 的字元集實際上是 utf8mb3，每個字元最多只用三個位元組。

真正完整的 UTF-8 是 utf8mb4，最多四個位元組。

差別在哪

• 常用中文字在三個位元組內，所以 utf8 平常看起來沒問題
• 但表情符號、部分罕用字與擴充區的字需要四個位元組
• 存入時會出現錯誤，或被截斷、變成問號

典型的症狀

• 使用者在留言或表單中輸入表情符號，資料被截斷
• 出現 Incorrect string value 錯誤
• 某些人名或罕用字無法正確儲存

新建的資料庫一律使用 utf8mb4，沒有例外。

排序規則的選擇

字元集決定「能存哪些字」，排序規則決定「怎麼比較與排序」。

實務建議

• 一般網站用不分大小寫的通用規則即可
• 整個資料庫、資料表、欄位要使用一致的排序規則
• MySQL 與 MariaDB 的可用選項不同——跨系統轉移時要注意

不一致會怎樣

JOIN 兩個排序規則不同的欄位時，可能出現：

Illegal mix of collations

或是索引無法使用，導致查詢突然變得極慢。 這是很難察覺的效能陷阱。

檢查目前的設定

-- 資料庫層級
SELECT default_character_set_name, default_collation_name
FROM information_schema.schemata
WHERE schema_name = 'your_database';

-- 資料表層級
SELECT table_name, table_collation
FROM information_schema.tables
WHERE table_schema = 'your_database';

-- 欄位層級
SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = 'your_database'
  AND character_set_name IS NOT NULL;

三個層級都要檢查。 資料庫改了但舊資料表沒改，是常見的狀況。

轉換為 utf8mb4

轉換前務必確認

1. 完整備份
2. 確認索引長度——見下段
3. 在測試環境先跑一次
4. 評估鎖表時間——大表轉換會鎖住一段時間

轉換指令

-- 資料庫預設
ALTER DATABASE dbname
  CHARACTER SET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

-- 資料表與既有資料
ALTER TABLE tablename
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

注意 CONVERT TO 會實際轉換既有資料，而只改 DEFAULT CHARACTER SET 只影響之後新增的欄位。

索引長度的限制

這是轉換時最常遇到的障礙。

索引有位元組長度上限。從三個位元組換成四個位元組，同樣的字元數會佔用更多空間，原本可以建立的索引可能超出限制。

可能出現的錯誤

Specified key was too long

處理方式

• 縮短欄位長度——例如 VARCHAR(255) 改為 VARCHAR(191)
• 使用前綴索引——只對前 N 個字元建立索引
• 確認使用較新的列格式與檔案格式——較新的設定支援更長的索引

現代版本多數已預設支援較長的索引，但從舊版沿用的資料表可能仍是舊格式，需要一併調整。

連線層的字元集也要對

資料庫改對了，但連線時宣告的字元集不對，一樣會亂碼。

PHP 端的設定

// PDO
$dsn = 'mysql:host=localhost;dbname=test;charset=utf8mb4';

// mysqli
$mysqli->set_charset('utf8mb4');

不要用 SET NAMES 語句——某些驅動的內部狀態不會同步更新，可能造成跳脫處理的問題。應使用驅動提供的設定方式。

伺服器端的預設

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[client]
default-character-set = utf8mb4

亂碼的排查順序

亂碼可能發生在任何一個環節，逐一確認：

1. 資料庫、資料表、欄位的字元集
2. 連線時的字元集
3. 應用程式的內部編碼
4. 網頁輸出的宣告——HTML 的 meta 與 HTTP 標頭
5. 檔案本身的編碼——PHP 檔案應存為不帶標記的 UTF-8

判斷是哪一段出問題

直接用資料庫用戶端查詢，看資料本身是否正確：

• 資料庫中就是亂碼——寫入時的環節有問題，需要修復資料
• 資料庫正常但網頁亂碼——讀取或輸出的環節有問題

這個判斷很重要——前者需要處理資料，後者只需要調整設定。

已經存入的亂碼資料

若資料本身已經以錯誤的編碼存入，直接轉換字元集會讓情況更糟。

常見的處理思路是：先轉為二進位型別保留原始位元組，再轉為正確的字元集。但這類操作風險高，務必先備份並在測試環境驗證。

資料量不大時，從乾淨的來源重新匯入，往往比修復更可靠。

本文以 MySQL 與 MariaDB 的常見版本為例，實際的指令、預設值與可用選項可能因版本與發行版而異。執行任何變更前請確實備份，並先在測試環境驗證。

索引解決什麼問題

沒有索引時，查詢需要逐列掃描整張資料表。資料量小時感覺不出來，但資料成長到數萬、數十萬筆之後，差異會非常明顯。

索引讓資料庫能快速定位到符合條件的資料，代價是額外的儲存空間與寫入時的維護成本。

用 EXPLAIN 判斷查詢

EXPLAIN SELECT * FROM tw_posts
WHERE cat_id = 5 AND status = 1
ORDER BY created_at DESC
LIMIT 20;

重點看四個欄位

Extra 中值得注意的訊息

• Using filesort——排序無法利用索引，需額外排序
• Using temporary——需要建立暫存表，通常出現在分組或排序
• Using index——這是好事，代表只讀索引就能完成，不必回表
• Using where——在取出資料後才過濾

目標是避免 type 為 ALL、且 key 不為 NULL。

常見的索引缺失

一、WHERE 條件的欄位沒有索引

最基本也最常見。經常用來過濾的欄位應該有索引。

二、JOIN 的關聯欄位沒有索引

兩表關聯時，被關聯的欄位若沒有索引，會造成大量的重複掃描。

三、ORDER BY 無法利用索引

排序欄位若不在索引中，或順序不符，就會出現 filesort。

複合索引的順序很重要

複合索引是多個欄位組成的索引。欄位的順序決定了它能被哪些查詢使用。

-- 建立
CREATE INDEX idx_cat_status_time
  ON tw_posts (cat_id, status, created_at);

能使用這個索引的查詢

• 只用 cat_id 過濾
• 用 cat_id + status
• 用 cat_id + status + created_at 排序

不能有效使用的

• 只用 status 過濾——跳過了第一個欄位
• 只用 created_at 排序

原則：從最左邊的欄位開始，連續使用才有效。

順序怎麼決定

1. 等值比較的欄位放前面
2. 範圍比較的欄位放後面——範圍條件之後的欄位無法再用於過濾
3. 排序用的欄位放最後

會讓索引失效的寫法

一、在索引欄位上套用函式

-- 索引失效
WHERE DATE(created_at) = '2026-08-05'

-- 建議
WHERE created_at >= '2026-08-05 00:00:00'
  AND created_at < '2026-08-06 00:00:00'

二、開頭的萬用字元

-- 無法使用索引
WHERE title LIKE '%關鍵字%'

-- 可以使用索引
WHERE title LIKE '關鍵字%'

需要全文檢索時，應使用全文索引或專門的搜尋引擎，而不是用前後萬用字元的 LIKE。

三、型別不一致

欄位是數字型別但查詢傳入字串，或反之，可能導致隱含轉換而無法使用索引。

四、對欄位做運算

-- 索引失效
WHERE price * 2 > 1000

-- 建議
WHERE price > 500

不是加越多索引越好

每個索引都有成本：

• 佔用儲存空間
• 每次寫入都要維護——新增、修改、刪除都會變慢
• 過多索引會讓查詢最佳化器的判斷變複雜

不需要加索引的情況

• 資料量很小的資料表——全表掃描反而更快
• 區別度很低的欄位——例如只有兩三種值的狀態欄位，單獨建索引效益有限
• 幾乎不用於查詢條件的欄位

找出沒在用的索引

可查詢系統的索引使用統計，找出長期未被使用的索引並評估移除。

慢查詢日誌

這是找出問題查詢最直接的方法。

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 0

設定的建議

• long_query_time 先設 1 秒，找出最嚴重的；改善後再逐步調低
• log_queries_not_using_indexes 平常關閉——開啟會產生大量記錄，包含小表的正常全掃描

分析日誌

可使用專門的分析工具彙整，找出「總耗時最多」的查詢——不一定是單次最慢的那個，而是執行頻繁又不夠快的那個。

應用層的常見問題

有時候問題不在單一查詢，而在查詢的方式。

迴圈中重複查詢

取出一百筆主資料，再逐筆查詢關聯資料，就是一百零一次查詢。

應改為一次查詢取得所有關聯資料，或使用 JOIN。這通常是效能問題的最大來源。

撈取不必要的欄位

-- 不建議
SELECT * FROM tw_posts;

-- 建議
SELECT post_id, title, created_at FROM tw_posts;

只取需要的欄位，尤其是資料表中有長文字欄位時，差異明顯。

沒有分頁

一次撈出全部資料再由應用程式處理，資料量大時會耗盡記憶體。應在資料庫端分頁。

PHP 端的記憶體問題見PHP 錯誤排查與日誌判讀。

調校的順序

1. 開啟慢查詢日誌，找出實際的問題查詢
2. 用 EXPLAIN 分析
3. 補上缺少的索引
4. 檢查應用層是否有重複查詢的問題
5. 最後才調整伺服器參數

順序反過來是常見的錯誤。 參數調到極致，也救不了一個缺索引的查詢。

本文以 MySQL 與 MariaDB 的常見版本為例，實際的指令、預設值與可用選項可能因版本與發行版而異。執行任何變更前請確實備份，並先在測試環境驗證。

兩種備份方式

對多數企業網站，邏輯備份就足夠——資料量通常不大，還原時間可以接受，而且跨版本或跨主機的相容性較好。

邏輯備份的常用做法

基本備份

mysqldump -u user -p \
  --single-transaction \
  --default-character-set=utf8mb4 \
  dbname > backup.sql

幾個重要選項

• --single-transaction——對 InnoDB 取得一致性快照，不會鎖表。這是最重要的一項
• --default-character-set=utf8mb4——避免匯出時發生編碼問題
• --routines --triggers --events——若有預存程序、觸發器、排程事件，必須加上，否則不會被備份
• --no-tablespaces——某些權限受限的環境需要

第三項很常被漏掉。 還原後才發現觸發器不見了，通常已經是事後。

壓縮輸出

mysqldump -u user -p --single-transaction dbname \
  | gzip > backup_$(date +%F).sql.gz

SQL 文字的壓縮率很高，通常能省下大量空間。

只備份結構或只備份資料

mysqldump --no-data dbname > schema.sql    -- 只要結構
mysqldump --no-create-info dbname > data.sql -- 只要資料

還原

mysql -u user -p dbname < backup.sql

# 壓縮檔
gunzip < backup.sql.gz | mysql -u user -p dbname

還原前的注意事項

• 確認目標資料庫的字元集正確——否則可能造成亂碼
• 還原會覆蓋現有資料——先備份現況
• 大型備份的還原可能很久——需評估停機時間

加速還原

大型資料還原時，可暫時調整部分設定以提升速度，但這些調整會降低耐久性保證，還原完成後必須改回。

備份的四個必要條件

1. 不與資料庫存在同一台主機——主機故障或被入侵時，備份也會一起消失
2. 包含完整的資料庫——不只是檔案，還有預存程序與觸發器
3. 保留多個時間點——問題可能數週後才被發現，最新的備份可能已包含問題
4. 測試過能還原

第四項最常被忽略

沒有實際測試過的備份不算備份。 常見的狀況是備份檔案一直在產生，真的要用時才發現：

• 檔案損毀或不完整
• 缺少觸發器或預存程序
• 字元集錯誤導致還原後亂碼
• 備份腳本其實早就失敗了，只是沒人發現

建議每半年實際還原一次到測試環境驗證。

備份頻率的判斷

問一個問題：如果還原到昨天的狀態，會損失什麼？

• 純展示型網站——每週或每月即可
• 經常更新內容——每日
• 有訂單、會員、表單累積——每日，並考慮時間點還原機制

時間點還原

若需要還原到「某個特定時刻」而非「最近一次備份」，需要搭配二進位日誌。

原理

• 定期做完整備份
• 二進位日誌記錄期間的所有變更
• 還原時先套用完整備份，再重播日誌到指定時間點

啟用

[mysqld]
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 7

注意日誌會佔用磁碟空間，需設定保留期限並納入磁碟監控。

什麼情況需要

典型的用途是「誤刪資料」——例如今天下午三點有人誤刪了一批訂單，可以還原到二點五十九分的狀態。

對有交易的網站，這個機制的價值很高。

自動化備份腳本

基本要素：

• 密碼不要寫在指令列中——會出現在行程列表。應使用設定檔或環境變數
• 檔名包含日期
• 自動清理過期備份
• 失敗時要通知——這一項最常被忽略
• 備份完成後傳到異地

失敗通知為什麼重要

備份腳本靜默失敗是很常見的情況——磁碟滿了、權限改了、密碼變了，腳本每天照跑但什麼都沒產生。

應該同時監控「有沒有失敗」與「有沒有正常產生新檔案」——只監控失敗是不夠的，因為排程若根本沒執行，就不會有失敗記錄。

還原演練的檢查項目

1. 備份檔案能正常解壓縮與讀取
2. 還原過程沒有錯誤
3. 資料筆數與來源相符
4. 中文與特殊字元顯示正常
5. 預存程序、觸發器、檢視表都在
6. 應用程式能正常連線與運作

業主端的備份觀念見操作紀錄、備份與資料救回。

本文以 MySQL 與 MariaDB 的常見版本為例，實際的指令、預設值與可用選項可能因版本與發行版而異。執行任何變更前請確實備份，並先在測試環境驗證。

連線數相關的問題

Too many connections

連線數達到上限。先確認實際狀況：

SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';
SHOW PROCESSLIST;

常見原因

• 應用端的行程數超過資料庫上限——PHP-FPM 的 max_children 設得比 max_connections 高
• 使用持久連線但未妥善管理
• 連線未正確關閉
• 有查詢卡住不放——連線被長時間佔用

處理

調高 max_connections 是治標。應先確認是不是有查詢卡住，用 SHOW PROCESSLIST 看有沒有長時間執行的語句。

相關的行程池設定見網頁伺服器的效能相關設定。

鎖等待與死結

Lock wait timeout exceeded

交易在等待另一個交易釋放鎖，超過等待時間。

-- 查看目前的交易
SELECT * FROM information_schema.innodb_trx;

-- 查看鎖等待情況（版本不同名稱有異）
SHOW ENGINE INNODB STATUS;

常見原因

• 交易開啟後長時間未提交——最常見
• 交易中包含了耗時的操作——例如呼叫外部服務
• 大量資料的更新未分批

處理原則

• 交易要短——只包含必要的資料庫操作
• 不要在交易中做網路請求或檔案處理
• 大批量更新要分批
• 必要時可終止卡住的連線

磁碟空間耗盡

這是實際會導致資料庫停止運作的事故。常見的空間消耗來源：

• 二進位日誌未設定保留期限——最常見
• 慢查詢日誌與一般日誌累積
• 暫存檔案
• 資料表本身的成長

檢查

-- 各資料表的大小
SELECT table_name,
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'your_database'
ORDER BY (data_length + index_length) DESC;

建議把磁碟用量納入監控，並設定二進位日誌的保留期限。

資料表損毀

多見於 MyISAM，InnoDB 較少見但仍可能發生。

CHECK TABLE tablename;
REPAIR TABLE tablename;      -- 僅適用 MyISAM

InnoDB 的損毀處理較複雜，通常需要以特殊模式啟動並匯出資料。操作前務必先備份現有檔案。

遇到這種情況時，從備份還原通常比嘗試修復更可靠。

資料表空間未釋放

刪除大量資料後，磁碟空間沒有減少。這是正常現象——空間被標記為可重複使用，但沒有歸還給作業系統。

OPTIMIZE TABLE tablename;

注意這個操作會鎖表並需要額外的磁碟空間（相當於該表的大小），大表應選在離峰時段執行。

查詢突然變慢

原本正常的查詢突然變慢，可能的原因：

1. 資料量成長——原本沒索引也夠快，資料多了就不行
2. 統計資訊過期——最佳化器選錯了執行計畫
3. 索引被刪除或失效
4. 排序規則不一致——JOIN 時導致索引無法使用
5. 伺服器資源被其他行程佔用

更新統計資訊

ANALYZE TABLE tablename;

這個操作成本低，遇到執行計畫異常時值得先試。

日常監控的指標

定期維護建議

每月

• 檢視慢查詢日誌，處理新出現的問題查詢
• 確認備份正常產生
• 檢查磁碟用量趨勢

每季

• 檢視資料表大小，評估是否需要歸檔舊資料
• 檢查未使用的索引
• 確認字元集與排序規則一致

每半年

• 實際還原一次備份到測試環境驗證
• 檢視版本的支援狀態
• 檢視使用者帳號與權限

權限的最小化

應用程式使用的資料庫帳號，不應該有超出需要的權限。

• 一般網站應用——通常只需要基本的增刪改查權限
• 不需要 DROP、CREATE USER、GRANT 等權限
• 不要用最高權限帳號連線
• 不同的應用使用不同的帳號
• 限制連線來源——若資料庫與網站同機，可限制為本機連線

這是降低入侵後損害範圍最有效的做法之一。詳見主機層與存取控制的防護設定。

排查的通用順序

1. 看錯誤日誌——資料庫的錯誤日誌通常直接說明原因
2. 看目前的連線與查詢——SHOW PROCESSLIST
3. 檢查系統資源——磁碟、記憶體、負載
4. 比對最近的變更——程式部署、設定調整、資料量成長
5. 在測試環境重現

第四項最有效——突然出現的問題，幾乎都能對應到某個具體的變更。

本文以 MySQL 與 MariaDB 的常見版本為例，實際的指令、預設值與可用選項可能因版本與發行版而異。執行任何變更前請確實備份，並先在測試環境驗證。