-- 案例1：最左前缀原则 CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);
-- ✅ 以下查询能使用索引 SELECT*FROM orders WHERE user_id =123; SELECT*FROM orders WHERE user_id =123AND status ='PAID'; SELECT*FROM orders WHERE user_id =123AND status ='PAID'AND create_time >'2024-01-01';
-- ❌ 以下查询不能使用索引 SELECT*FROM orders WHERE status ='PAID'; SELECT*FROM orders WHERE create_time >'2024-01-01'; SELECT*FROM orders WHERE status ='PAID'AND create_time >'2024-01-01';
-- 案例2：区分度原则 -- 假设用户表有100万条记录，其中性别只有2个值，城市有1000个值 -- ❌ 错误的索引顺序 CREATE INDEX idx_wrong ON users(gender, city, user_id);
-- ✅ 正确的索引顺序 CREATE INDEX idx_correct ON users(user_id, city, gender);
-- 案例3：覆盖索引 -- 查询：SELECT user_id, amount, create_time FROM orders WHERE status = 'PAID'; -- ✅ 覆盖索引，避免回表 CREATE INDEX idx_status_cover ON orders(status, user_id, amount, create_time);
-- 案例4：范围查询优化 -- 查询：WHERE user_id = ? AND status = ? AND create_time BETWEEN ? AND ? -- ✅ 正确的索引设计 CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time); -- 等值条件在前，范围条件在后
-- 1. 小表驱动大表 -- ❌ 大表在前 SELECT*FROM big_table b JOIN small_table s ON b.id = s.big_id;
-- ✅ 小表在前（MySQL会自动优化，但明确更好） SELECT*FROM small_table s JOIN big_table b ON s.big_id = b.id;
-- 2. 确保JOIN条件有索引 -- 确保both tables的JOIN字段都有索引 CREATE INDEX idx_orders_user_id ON orders(user_id); CREATE INDEX idx_users_id ON users(id); -- 主键自动有索引
-- 3. 避免笛卡尔积 -- ❌ 缺少JOIN条件 SELECT*FROM orders o, order_items oi WHERE o.status ='PENDING';
-- ✅ 正确的JOIN SELECT*FROM orders o JOIN order_items oi ON o.id = oi.order_id WHERE o.status ='PENDING';
-- 4. 使用适当的JOIN类型 -- INNER JOIN vs LEFT JOIN vs RIGHT JOIN -- 只获取需要的数据
🛠️ 实战优化案例
案例1：慢查询优化
问题SQL：
1 2 3 4 5 6 7 8 9
-- 执行时间：2.5秒 SELECT u.name, u.email, COUNT(o.id) as order_count, SUM(o.amount) as total_amount FROM users u LEFTJOIN orders o ON u.id = o.user_id WHERE u.register_time >='2024-01-01' AND o.create_time >='2023-01-01' GROUPBY u.id, u.name, u.email ORDERBY total_amount DESC LIMIT 100;
分析过程：
1 2 3 4 5 6 7 8 9 10 11 12 13 14
-- 1. 查看执行计划 EXPLAIN SELECT u.name, u.email, COUNT(o.id) as order_count, SUM(o.amount) as total_amount FROM users u LEFTJOIN orders o ON u.id = o.user_id WHERE u.register_time >='2024-01-01' AND o.create_time >='2023-01-01' GROUPBY u.id, u.name, u.email ORDERBY total_amount DESC LIMIT 100;
-- 执行计划显示： -- 1. users表全表扫描 (type: ALL) -- 2. orders表全表扫描 (type: ALL) -- 3. 使用了临时表和文件排序 (Extra: Using temporary; Using filesort)
优化步骤：
1 2 3 4 5 6 7 8 9 10 11 12 13 14
-- 1. 查看执行计划 EXPLAIN SELECT u.name, u.email, COUNT(o.id) as order_count, SUM(o.amount) as total_amount FROM users u LEFTJOIN orders o ON u.id = o.user_id WHERE u.register_time >='2024-01-01' AND o.create_time >='2023-01-01' GROUPBY u.id, u.name, u.email ORDERBY total_amount DESC LIMIT 100;
-- 执行计划显示： -- 1. users表全表扫描 (type: ALL) -- 2. orders表全表扫描 (type: ALL) -- 3. 使用了临时表和文件排序 (Extra: Using temporary; Using filesort)
-- 原始慢查询：统计每月销售数据 -- 执行时间：8.2秒 SELECT DATE_FORMAT(create_time, '%Y-%m') asmonth, COUNT(*) as order_count, SUM(amount) as total_amount, AVG(amount) as avg_amount, COUNT(DISTINCT user_id) as unique_users FROM orders WHERE create_time >='2023-01-01'AND create_time <'2024-01-01' AND status ='COMPLETED' GROUPBY DATE_FORMAT(create_time, '%Y-%m') ORDERBYmonth;
-- 优化方案1：添加合适的索引 CREATE INDEX idx_orders_status_time ON orders(status, create_time);
-- 定时任务更新汇总表 INSERT INTO order_monthly_stats SELECT DATE_FORMAT(create_time, '%Y-%m-01') asmonth, COUNT(*) as order_count, SUM(amount) as total_amount, AVG(amount) as avg_amount, COUNT(DISTINCT user_id) as unique_users FROM orders WHERE create_time >='2024-01-01'AND create_time <'2024-02-01' AND status ='COMPLETED' GROUPBY DATE_FORMAT(create_time, '%Y-%m-01') ON DUPLICATE KEY UPDATE order_count =VALUES(order_count), total_amount =VALUES(total_amount), avg_amount =VALUES(avg_amount), unique_users =VALUES(unique_users);
-- ✅ 良好的索引设计 CREATE TABLE orders ( id BIGINTPRIMARY KEY AUTO_INCREMENT, user_id BIGINTNOT NULL, status VARCHAR(20) NOT NULL, amount DECIMAL(10,2) NOT NULL, create_time DATETIME NOT NULL, update_time DATETIME NOT NULL,
-- 基础索引 INDEX idx_user_id (user_id), INDEX idx_status (status), INDEX idx_create_time (create_time),
-- 复合索引（常用查询组合） INDEX idx_user_status (user_id, status), INDEX idx_status_time (status, create_time),
-- 覆盖索引（避免回表） INDEX idx_user_cover (user_id, status, amount, create_time) );
-- 2. 分区表（大数据量） CREATE TABLE orders_partitioned ( id BIGINTPRIMARY KEY AUTO_INCREMENT, user_id BIGINTNOT NULL, amount DECIMAL(10,2) NOT NULL, create_time DATETIME NOT NULL,
KEY idx_user_id (user_id), KEY idx_create_time (create_time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 PARTITIONBYRANGE (TO_DAYS(create_time)) ( PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')), PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')), PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')), PARTITION p_future VALUES LESS THAN MAXVALUE );
-- 查找未使用的索引 SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE FROM performance_schema.table_io_waits_summary_by_index_usage WHERE INDEX_NAME ISNOT NULL AND COUNT_STAR =0 AND OBJECT_SCHEMA ='your_database' ORDERBY OBJECT_SCHEMA, OBJECT_NAME;
-- 查找重复索引 SELECT a.TABLE_SCHEMA, a.TABLE_NAME, a.INDEX_NAME as index1, b.INDEX_NAME as index2, a.COLUMN_NAME FROM information_schema.STATISTICS a JOIN information_schema.STATISTICS b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA AND a.TABLE_NAME = b.TABLE_NAME AND a.COLUMN_NAME = b.COLUMN_NAME AND a.INDEX_NAME != b.INDEX_NAME WHERE a.TABLE_SCHEMA ='your_database' ORDERBY a.TABLE_NAME, a.COLUMN_NAME;
/** * 策略1：分页 + 索引 */ public List<Order> queryWithPaging(Long lastId, int size) { Stringsql=""" SELECT * FROM big_orders WHERE id > ? ORDER BY id LIMIT ? """; return jdbcTemplate.query(sql, newObject[]{lastId, size}, rowMapper); }
