-- 定时更新 INSERT INTO table_counts VALUES ('orders', (SELECTCOUNT(*) FROM orders), NOW()) ON DUPLICATE KEY UPDATE row_count =VALUES(row_count), updated_at = NOW();
-- 查询 SELECT row_count FROM table_counts WHERE table_name ='orders'; -- 耗时：1ms
3.5 案例 #5：JOIN 优化
优化前
1 2 3 4 5
SELECT o.*, u.username, u.email FROM orders o JOIN users u ON o.user_id = u.id WHERE o.created_at >='2026-01-01' AND u.status ='active';
EXPLAIN 分析：
1 2 3 4 5 6
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 5000 | Using where | | 1 | SIMPLE | o | ALL | NULL | NULL | NULL | NULL | 10000| Using where | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
问题：
两张表都全表扫描
无索引
优化后
添加索引：
1 2 3 4 5 6
-- users 表 ALTER TABLE users ADD INDEX idx_status (status);
-- orders 表 ALTER TABLE orders ADD INDEX idx_user_id (user_id); ALTER TABLE orders ADD INDEX idx_created_at (created_at);
EXPLAIN 分析：
1 2 3 4 5 6
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | u | ref | idx_status | idx_status| 1 | const| 4000 | Using where | | 1 | SIMPLE | o | ref | idx_user_id | idx_user_id| 8 | u.id | 2 | Using where | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
效果对比：
指标
优化前
优化后
提升
users 扫描
5000
4000
20%
orders 扫描
10000
2
99.98%
耗时
3000ms
150ms
95%
四、索引设计最佳实践
4.1 索引设计原则
原则
说明
示例
选择性高
区分度高的列优先
用户名 > 性别
最左前缀
组合索引从左到右
(a,b,c) 支持 a, (a,b), (a,b,c)
避免冗余
不创建重复索引
已有 (a,b) 不需要 (a)
适度索引
不是越多越好
每表 3-5 个索引为宜
覆盖查询
索引包含查询列
SELECT id FROM t WHERE id = 1
4.2 索引选择策略
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
graph TB A[查询分析] --> B{单列还是多列？} B -->|单列 | C[单列索引] B -->|多列 | D[组合索引] D --> E{有等值条件？} E -->|是 | F[等值列放前面] E -->|否 | G[范围列放后面] F --> H{有排序？} H -->|是 | I[排序列加入索引] H -->|否 | J[完成] I --> K{有 GROUP BY？} K -->|是 | L[GROUP BY 列加入索引] K -->|否 | J L --> J
4.3 索引维护
4.3.1 索引碎片整理
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
-- 查看表碎片 SELECT TABLE_NAME, DATA_FREE, DATA_LENGTH, INDEX_LENGTH, ROUND(DATA_FREE / (DATA_LENGTH + INDEX_LENGTH) *100, 2) AS fragmentation_rate FROM information_schema.TABLES WHERE TABLE_SCHEMA ='crystalforge';
-- 查看索引使用情况 SELECT OBJECT_NAME, INDEX_NAME, COUNT_READ, COUNT_WRITE, SUM_TIMER_WAIT FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA ='crystalforge' ORDERBY COUNT_READ DESC;
-- 查看未使用的索引 SELECT TABLE_NAME, INDEX_NAME FROM information_schema.STATISTICS WHERE TABLE_SCHEMA ='crystalforge' AND INDEX_NAME NOTIN ( SELECT INDEX_NAME FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA ='crystalforge' );
-- 创建测试表 CREATE TABLE test_index ( id INTPRIMARY KEY AUTO_INCREMENT, username VARCHAR(50), email VARCHAR(100), age INT, status VARCHAR(20), created_at DATETIME );
-- 插入测试数据 DELIMITER $$ CREATEPROCEDURE insert_test_data(IN count INT) BEGIN DECLARE i INTDEFAULT1; WHILE i <= count DO INSERT INTO test_index (username, email, age, status, created_at) VALUES ( CONCAT('user', i), CONCAT('user', i, '@example.com'), FLOOR(RAND() *50), ELT(FLOOR(RAND() *3) +1, 'active', 'inactive', 'pending'), DATE_SUB(NOW(), INTERVALFLOOR(RAND() *365) DAY) ); SET i = i +1; END WHILE; END$$ DELIMITER ;
CALL insert_test_data(100000);
-- 测试无索引查询 EXPLAIN SELECT*FROM test_index WHERE username ='user50000'; -- 耗时：500ms
-- 添加索引 CREATE INDEX idx_username ON test_index(username);
-- 测试有索引查询 EXPLAIN SELECT*FROM test_index WHERE username ='user50000'; -- 耗时：5ms
