2026年03月27日-MySQL查询优化实战

一、索引优化策略

1.1 索引设计原则

合理使用索引是提升MySQL查询性能的关键。以下是索引设计的核心原则：

最佳实践：

• 为WHERE子句、JOIN条件和ORDER BY字段创建索引
• 选择性高的字段（高基数）优先建立索引
• 避免在小表上创建过多索引
• 联合索引遵循最左前缀原则

-- 创建联合索引
CREATE INDEX idx_user_status_time ON users(status, created_at);

-- 查询能利用索引
SELECT * FROM users WHERE status = 1 AND created_at > '2026-01-01';

-- 查询无法利用索引（违反最左前缀）
SELECT * FROM users WHERE created_at > '2026-01-01';
​

1.2 索引失效场景

以下情况会导致索引失效：

-- 1. 使用函数
SELECT * FROM users WHERE YEAR(created_at) = 2026;

-- 优化方案
SELECT * FROM users WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

-- 2. 隐式类型转换
SELECT * FROM users WHERE phone = 13800138000;  -- phone字段为VARCHAR

-- 优化方案
SELECT * FROM users WHERE phone = '13800138000';

-- 3. LIKE查询以通配符开头
SELECT * FROM users WHERE name LIKE '%张%';

-- 优化方案：使用全文索引
ALTER TABLE users ADD FULLTEXT INDEX ft_name(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('张' IN BOOLEAN MODE);
​

二、查询优化技巧

2.1 使用EXPLAIN分析执行计划

EXPLAIN是MySQL提供的查询分析工具，可以帮助我们了解SQL语句的执行情况。

EXPLAIN SELECT u.*, o.order_count 
FROM users u 
LEFT JOIN (
    SELECT user_id, COUNT(*) as order_count 
    FROM orders 
    GROUP BY user_id
) o ON u.id = o.user_id 
WHERE u.status = 1;
​

关键指标说明：

• type：访问类型，从好到差为 system > const > eq_ref > ref > range > index > ALL
• key：实际使用的索引
• rows：预估扫描的行数
• Extra：额外信息，如"Using filesort"表示需要额外排序

2.2 分页查询优化

传统分页在数据量大时性能低下：

-- 传统方式（慢）
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

-- 优化方式：使用游标分页
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;

-- 或者使用延迟关联
SELECT * FROM orders o 
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 20
) t ON o.id = t.id;
​

三、实战案例：优化订单查询性能

问题描述

某电商系统的订单列表查询在数据量超过1000万后，响应时间从原来的200ms增长到5秒以上。

排查过程

1. 分析慢查询日志

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';
​

2. 使用EXPLAIN分析

EXPLAIN SELECT o.*, u.username, p.product_name
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 20;
​

发现type列为ALL，表示进行了全表扫描。

优化方案

1. 添加合适的索引

-- 为订单表添加状态和创建时间的联合索引
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 为订单明细表添加订单ID索引
CREATE INDEX idx_order_id ON order_items(order_id);

-- 为商品表添加主键索引（通常已有）
-- 确保users表的id字段有主键索引
​

2. 优化查询语句

-- 使用子查询减少JOIN的数据量
SELECT o.*, u.username, p.product_name
FROM (
    SELECT * FROM orders 
    WHERE status = 'pending' 
    ORDER BY created_at DESC 
    LIMIT 20
) o
LEFT JOIN users u ON o.user_id = u.id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id;
​

3. 使用覆盖索引

-- 创建覆盖索引，避免回表
CREATE INDEX idx_covering ON orders(status, created_at, id, user_id);
​

优化效果

• 查询响应时间从5秒降至150ms
• CPU使用率降低60%
• 数据库负载明显下降

四、表结构优化

4.1 选择合适的数据类型

-- 优化前
CREATE TABLE user_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(50),
    action VARCHAR(100),
    created_at DATETIME,
    status TINYINT
);

-- 优化后
CREATE TABLE user_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED,
    action VARCHAR(50),
    created_at TIMESTAMP,
    status TINYINT UNSIGNED,
    INDEX idx_user_time (user_id, created_at)
) ENGINE=InnoDB;
​

优化点：

• BIGINT改为INT UNSIGNED（减少存储空间）
• VARCHAR长度根据实际需求调整
• DATETIME改为TIMESTAMP（节省空间）
• 添加必要的索引

4.2 表分区策略

对于日志类大表，使用分区可以显著提升查询性能：

-- 按日期分区
CREATE TABLE access_logs (
    id BIGINT AUTO_INCREMENT,
    user_id INT UNSIGNED,
    url VARCHAR(255),
    created_at TIMESTAMP,
    PRIMARY KEY (id, created_at)
) ENGINE=InnoDB
PARTITION BY RANGE (UNIX_TIMESTAMP(created_at)) (
    PARTITION p202601 VALUES LESS THAN (UNIX_TIMESTAMP('2026-02-01')),
    PARTITION p202602 VALUES LESS THAN (UNIX_TIMESTAMP('2026-03-01')),
    PARTITION p202603 VALUES LESS THAN (UNIX_TIMESTAMP('2026-04-01')),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);
​

五、总结

MySQL查询优化需要从索引设计、查询语句、表结构等多个维度综合考虑。关键是要建立性能监控体系，定期分析慢查询日志，持续优化数据库性能。建议使用Percona Toolkit等工具进行性能分析和诊断，确保数据库系统稳定高效运行。