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;