Table users của mình vượt 10 triệu row thì slowdown bắt đầu xuất hiện. Mình chạy EXPLAIN khắp nơi, thêm index này index kia — đến một ngày nhìn lại thấy mình đang giữ hàng chục index mà query chẳng bao giờ đụng đến. Đó là lúc mình bắt đầu nghiêm túc với việc dọn dẹp unused indexes.
Tại sao Unused Indexes lại là vấn đề?
Nhiều người nghĩ index chỉ có lợi: query nhanh hơn, tìm kiếm dễ hơn. Nhưng mỗi index bạn tạo đều có cái giá phải trả:
- Tốc độ ghi chậm lại: Mỗi lần
INSERT,UPDATE,DELETE, MySQL phải cập nhật tất cả index liên quan. 5 index thừa đồng nghĩa 5 lần cập nhật thừa mỗi lần ghi. - Tốn dung lượng disk: Index lưu trên disk giống như data thật. Table 50GB với index thừa có thể chiếm thêm 10–20GB không cần thiết.
- Buffer pool bị lãng phí: InnoDB buffer pool cache index pages. Index thừa chiếm chỗ của data thực sự cần thiết, làm giảm cache hit rate.
- Query optimizer bị nhiễu: Optimizer phải tính toán nhiều execution plan hơn, đôi khi chọn nhầm index.
Với database production quy mô vài chục triệu row, đây không phải chuyện nhỏ có thể bỏ qua.
Chuẩn bị: Bật Performance Schema
MySQL theo dõi việc sử dụng index thông qua Performance Schema. Kiểm tra xem nó đã bật chưa:
SHOW VARIABLES LIKE 'performance_schema';Nếu kết quả là OFF, bật lên trong file cấu hình /etc/mysql/mysql.conf.d/mysqld.cnf (hoặc my.cnf tùy distro):
[mysqld]
performance_schema = ONSau đó restart MySQL:
sudo systemctl restart mysqlLưu ý: Performance Schema có overhead nhỏ về memory (~5–10%), nhưng với hầu hết production server thì hoàn toàn chấp nhận được. MySQL 5.7 trở lên mặc định đã bật sẵn.
Đảm bảo instrumentation cho index lookup đang active:
-- Kiểm tra index instrumentation
SELECT * FROM performance_schema.setup_consumers
WHERE NAME LIKE '%statements%';
-- Bật nếu chưa bật
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME = 'events_statements_history_long';Cách tìm Unused Indexes
Cách 1: Dùng sys Schema (nhanh nhất)
MySQL 5.7.7+ có sẵn view sys.schema_unused_indexes — truy vấn một câu là ra ngay:
SELECT *
FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('mysql', 'performance_schema', 'sys', 'information_schema')
ORDER BY object_schema, object_name;Output điển hình trông như này:
+---------------+-------------+------------------+
| object_schema | object_name | index_name |
+---------------+-------------+------------------+
| myapp | users | idx_users_phone |
| myapp | orders | idx_orders_ref |
| myapp | logs | idx_logs_level |
+---------------+-------------+------------------+Mình tìm thấy idx_users_phone — index này được tạo hồi feature tìm kiếm theo số điện thoại còn tồn tại, nhưng feature đó đã bỏ từ lâu mà index vẫn ngồi đó âm thầm chiếm tài nguyên. Kiểu vấn đề rất hay gặp trong project lâu năm.
Cách 2: Query chi tiết từ Performance Schema
Nếu muốn xem thêm thông tin số rows để ưu tiên xử lý, kết hợp performance_schema với information_schema:
SELECT
t.TABLE_SCHEMA AS db_name,
t.TABLE_NAME AS table_name,
s.INDEX_NAME AS index_name,
s.COLUMN_NAME AS column_name,
t.TABLE_ROWS AS approx_rows
FROM information_schema.STATISTICS s
JOIN information_schema.TABLES t
ON s.TABLE_SCHEMA = t.TABLE_SCHEMA
AND s.TABLE_NAME = t.TABLE_NAME
WHERE s.TABLE_SCHEMA NOT IN ('mysql', 'performance_schema', 'sys', 'information_schema')
AND s.INDEX_NAME != 'PRIMARY'
AND CONCAT(s.TABLE_SCHEMA, '.', s.TABLE_NAME, '.', s.INDEX_NAME) NOT IN (
SELECT CONCAT(object_schema, '.', object_name, '.', index_name)
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
AND count_star > 0
)
ORDER BY t.TABLE_ROWS DESC;Query này cho thêm số rows ước tính — giúp bạn ưu tiên xóa index trên table lớn trước vì impact cao hơn.
Cách 3: Phát hiện Redundant Indexes (index trùng lặp)
Ngoài unused, còn có index redundant — index mà MySQL không cần vì đã có index khác cover rồi:
SELECT *
FROM sys.schema_redundant_indexes
WHERE table_schema NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema');Ví dụ điển hình: có index composite trên (user_id, created_at), rồi lại tạo thêm index riêng trên (user_id) — cái sau hoàn toàn thừa vì index composite đã cover prefix đó rồi.
Phân tích và Quyết định trước khi xóa
Đừng xóa ngay khi thấy tên index trong danh sách. Đây là checklist mình luôn làm trước khi drop bất kỳ index nào:
1. Xác nhận thời gian quan sát đủ dài
Dữ liệu trong performance_schema được reset mỗi lần MySQL restart. Nếu server vừa restart 2 ngày trước, một index dùng cho monthly report cuối tháng sẽ bị liệt vào danh sách "unused" nhầm.
-- Kiểm tra MySQL đã chạy bao lâu (tính bằng giây)
SHOW GLOBAL STATUS LIKE 'Uptime';
-- 2592000 giây = 30 ngàyMình thường chờ ít nhất 30 ngày, lý tưởng là 90 ngày để cover các batch job chạy định kỳ theo quý.
2. Tìm trong application code
# Tìm tên index trong codebase (phòng trường hợp dùng FORCE INDEX)
grep -r "idx_users_phone" /var/www/myapp/
# Tìm các hint dùng index cứng
grep -r "FORCE INDEX\|USE INDEX\|IGNORE INDEX" /var/www/myapp/ --include="*.php"3. Backup định nghĩa index trước khi xóa
-- Xuất câu lệnh CREATE TABLE để backup
SHOW CREATE TABLE users\GLưu output vào file. Nếu sau này cần tạo lại index, bạn có ngay câu lệnh mà không cần đoán lại cấu trúc.
4. Drop index an toàn với ALGORITHM=INPLACE
Với MySQL 5.6+, dùng ALGORITHM=INPLACE, LOCK=NONE để tránh lock table khi đang có traffic:
-- Xóa một index
ALTER TABLE users
DROP INDEX idx_users_phone,
ALGORITHM=INPLACE,
LOCK=NONE;
-- Xóa nhiều index cùng lúc (ít lần rebuild hơn)
ALTER TABLE orders
DROP INDEX idx_orders_ref,
DROP INDEX idx_orders_old_status,
ALGORITHM=INPLACE,
LOCK=NONE;Nếu table đặc biệt lớn (50GB+) và cần zero-downtime tuyệt đối, cân nhắc pt-online-schema-change từ Percona Toolkit:
pt-online-schema-change \
--alter "DROP INDEX idx_users_phone" \
--execute \
D=myapp,t=usersKiểm tra & Monitoring sau khi xóa
Sau khi drop, theo dõi ít nhất 24–48 giờ để đảm bảo không có gì bị ảnh hưởng.
So sánh write performance
SELECT
OBJECT_NAME,
COUNT_WRITE,
ROUND(SUM_TIMER_WRITE / 1000000000, 2) AS write_time_ms,
ROUND(AVG_TIMER_WRITE / 1000000, 2) AS avg_write_us
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA = 'myapp'
ORDER BY SUM_TIMER_WRITE DESC
LIMIT 10;Kiểm tra dung lượng giải phóng được
SELECT
TABLE_NAME,
ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS total_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'myapp'
ORDER BY INDEX_LENGTH DESC;Script monitoring tự động hàng tuần
#!/bin/bash
# check_unused_indexes.sh
MYSQL_USER="monitor_user"
MYSQL_PASS="your_password"
DB="myapp"
UNUSED=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "
SELECT COUNT(*) FROM sys.schema_unused_indexes
WHERE object_schema = '$DB';" 2>/dev/null | tail -1)
if [ "$UNUSED" -gt "0" ]; then
echo "ALERT: $UNUSED unused indexes found in $DB" | \
mail -s "MySQL Unused Index Alert" [email protected]
fi# Thêm vào crontab để chạy mỗi thứ Hai 9 giờ sáng
0 9 * * 1 /opt/scripts/check_unused_indexes.shKết quả thực tế
Sau khi dọn dẹp index trên database production với table users 12 triệu row, mình đo được:
- Dung lượng index giảm từ 4.2GB xuống còn 2.8GB — tiết kiệm 33%
- Average INSERT time trên table users giảm khoảng 18%
- InnoDB buffer pool hit rate tăng nhẹ vì bớt index pages chiếm chỗ
Con số cụ thể sẽ khác tùy schema và workload, nhưng nguyên tắc luôn đúng: index không được dùng chỉ có hại, không có lợi. Dọn dẹp định kỳ là một phần của database maintenance mà junior thường bỏ qua — không phải vì khó, mà vì không biết công cụ đang có sẵn trong MySQL.
Bước tiếp theo sau khi dọn xong unused indexes là đánh giá các index đang được dùng có thực sự hiệu quả không — đó là câu chuyện của EXPLAIN ANALYZE và index selectivity, sẽ để dành cho bài sau.
