MySQL 索引设计与 SQL 语句优化实战代码
索引设计优化
创建复合索引以覆盖高频查询场景，避免全表扫描。以下是一个订单表的索引优化示例：
-- 原始表结构（未优化） CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT, order_date DATETIME, status VARCHAR(20), amount DECIMAL(10,2), INDEX (user_id) ); -- 优化后的索引设计 ALTER TABLE orders ADD INDEX idx_user_status (user_id, status), ADD INDEX idx_date_amount (order_date, amount);SQL 语句优化
避免使用SELECT *，明确指定查询字段，利用覆盖索引减少回表操作：
-- 低效查询 SELECT * FROM orders WHERE user_id = 100 AND status = 'paid'; -- 优化后查询（使用覆盖索引） SELECT id, user_id, status FROM orders WHERE user_id = 100 AND status = 'paid';分页查询优化
使用延迟关联技术优化大数据量分页：
-- 低效分页（全表扫描） SELECT * FROM orders ORDER BY order_date DESC LIMIT 10000, 20; -- 优化分页（先查ID再关联） SELECT t1.* FROM orders t1 JOIN (SELECT id FROM orders ORDER BY order_date DESC LIMIT 10000, 20) t2 ON t1.id = t2.id;JOIN 查询优化
确保 JOIN 字段有索引，避免笛卡尔积：
-- 原始低效JOIN SELECT o.*, u.name FROM orders o LEFT JOIN users u ON o.user_id = u.id WHERE o.status = 'shipped'; -- 优化方案（确保user_id和status有联合索引） ALTER TABLE users ADD INDEX idx_id (id); SELECT o.id, o.amount, u.name FROM orders o USE INDEX (idx_user_status) LEFT JOIN users u ON o.user_id = u.id WHERE o.status = 'shipped';避免隐式类型转换
确保查询条件与字段类型匹配：
-- 低效查询（导致索引失效） SELECT * FROM orders WHERE user_id = '100'; -- user_id是INT类型 -- 优化查询 SELECT * FROM orders WHERE user_id = 100;使用 EXPLAIN 分析执行计划
EXPLAIN SELECT * FROM orders WHERE order_date > '2023-01-01' ORDER BY amount DESC LIMIT 10;
