SQL语句优化思路

优化核心思路

优化流程：发现慢查询 → 分析执行计划 → 定位瓶颈 → 制定方案 → 验证效果

优化维度：索引设计、SQL重写、表结构、数据库配置

执行计划分析

使用EXPLAIN分析

explain SELECT 
    u.user_id,
    u.username,
    u.register_date,
    MIN(o.order_date) as first_purchase_date,
    MAX(o.order_date) as last_purchase_date,
    DATEDIFF(MIN(o.order_date), u.register_date) as days_to_first_purchase,
    DATEDIFF(MAX(o.order_date), MIN(o.order_date)) + 1 as customer_lifetime_days,
    DATEDIFF(CURRENT_DATE, MAX(o.order_date)) as days_since_last_order,
    COUNT(o.order_id) as total_orders,
    -- 客户状态判断
    CASE 
        WHEN DATEDIFF(CURRENT_DATE, MAX(o.order_date)) <= 30 THEN 'Active'
        WHEN DATEDIFF(CURRENT_DATE, MAX(o.order_date)) <= 90 THEN 'At Risk'
        ELSE 'Churned'
    END as customer_status
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id AND o.status = 'completed'
GROUP BY u.user_id, u.username, u.register_date
HAVING COUNT(o.order_id) > 0
ORDER BY last_purchase_date DESC;

关键指标说明

type: 访问类型（system > const > eq_ref > ref > range > index > ALL）

MySQL EXPLAIN中type访问类型的性能区别（从快到慢）：
system：表只有一行记录（系统表），最快
const：通过主键或唯一索引查询单行，如WHERE id=1
eq_ref：JOIN时通过主键或唯一索引关联，每个索引键只匹配一行
ref：通过普通索引查询，可能匹配多行，如WHERE name='张三'
range：索引范围扫描，如WHERE id BETWEEN 1 AND 100
index：全索引扫描，扫描整个索引树但不回表
ALL：全表扫描，最慢，需要扫描所有数据行

优化目标：尽量让查询达到const、eq_ref、ref级别，避免index和ALL类型的扫描。

key: 实际使用的索引
rows: 估算的扫描行数
Extra: 额外信息（Using index, Using where, Using temporary等）

监控与维护

慢查询日志

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;

索引维护

-- 查看索引使用情况
SHOW INDEX FROM orders;

-- 删除未使用的索引
DROP INDEX idx_unused ON orders;

定期分析表

-- 更新表统计信息
ANALYZE TABLE orders;

-- 优化表结构
OPTIMIZE TABLE orders;

优化实践总结

索引策略
- 为经常查询的列创建索引
- 复合索引遵循最左前缀原则
- 避免过多索引影响写入性能
查询优化
- 避免SELECT *，只查询需要的字段
- 合理使用JOIN，避免笛卡尔积
- 使用LIMIT限制返回数据量
- 数据量大时，EXISTS代替IN
表设计
- 选择合适的数据类型
- 避免过度规范化
- 考虑分区表处理大数据量
数据库配置优化
- InnoDB关键参数 : GLOBAL innodb_buffer_pool_size 、GLOBAL innodb_log_file_size、GLOBAL innodb_flush_log_at_trx_commit
- 连接配置：GLOBAL max_connections 、GLOBAL thread_cache_size
监控维护
- 定期分析慢查询
- 监控索引使用情况
- 及时清理无效索引

实践

电商订单查询优化

create database if not exists ds DEFAULT CHARACTER SET utf8 ;

use `ds`;
CREATE TABLE if not exists users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE if not exists orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    order_no VARCHAR(32) NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status TINYINT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE if not exists order_items (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    quantity INT NOT NULL
);

-- 测试数据
INSERT INTO users (username, email) VALUES
('user001', 'user001@example.com'),
('user002', 'user002@example.com');

INSERT INTO orders (user_id, order_no, total_amount, status) VALUES
(1, 'ORD201801010001', 299.99, 1),
(2, 'ORD201801010002', 399.99, 2);

INSERT INTO order_items (order_id, product_id, price, quantity) VALUES
(1, 1001, 299.99, 1),
(2, 1002, 399.99, 1);

查询优化对比

use `ds`;

-- 优化前：隐式连接，性能差
SELECT o.*, u.username 
FROM orders o, users u 
WHERE o.user_id = u.id AND u.username = 'user001' AND o.status = 1;

-- 查看索引
show index from  orders;
show index from  users;

-- 优化后：显式JOIN，添加索引
SELECT o.id, o.order_no, o.total_amount, u.username
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.username = 'user001' AND o.status = 1;

-- 关键索引
CREATE INDEX idx_orders_user_status ON orders(user_id, status, created_at);
CREATE INDEX idx_users_username ON users(username);

统计查询优化

-- 优化前：WHERE中使用函数
SELECT DATE(created_at) as date, COUNT(*), SUM(total_amount)
FROM orders
WHERE DATE(created_at) = '2018-01-01';

-- 优化后：避免函数，使用范围查询
SELECT DATE(created_at) as date, COUNT(*), SUM(total_amount)
FROM orders
WHERE created_at >= '2018-01-01 00:00:00'
AND created_at < '2018-01-02 00:00:00';

-- 覆盖索引
CREATE INDEX idx_orders_created_amount ON orders(created_at, total_amount);

分页查询优化

-- 优化前：深度分页，性能差
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10000, 20;

-- 优化后：子查询优化
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY created_at DESC LIMIT 10000, 20
) tmp ON o.id = tmp.id;

-- 最佳：游标分页
SELECT * FROM orders 
WHERE created_at < '2018-01-08 11:30:00'
ORDER BY created_at DESC LIMIT 20;

复杂JOIN优化

-- 多表关联查询优化
SELECT u.username, o.order_no, p.name as product_name
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.created_at >= '2025-01-01'
ORDER BY o.created_at DESC;

-- 关键索引
CREATE INDEX idx_orders_created_user ON orders(created_at, user_id);
CREATE INDEX idx_order_items_order_product ON order_items(order_id, product_id);

子查询优化

-- 优化前：相关子查询
SELECT * FROM orders o
WHERE EXISTS (
    SELECT 1 FROM order_items oi
    WHERE oi.order_id = o.id AND oi.product_id = 1001
);

-- 优化后：JOIN代替子查询
SELECT DISTINCT o.*
FROM orders o
INNER JOIN order_items oi ON o.id = oi.order_id
WHERE oi.product_id = 1001;

数据库配置优化

-- InnoDB关键参数
SET GLOBAL innodb_buffer_pool_size = 1073741824; -- 1GB
SET GLOBAL innodb_log_file_size = 268435456;     -- 256MB
SET GLOBAL innodb_flush_log_at_trx_commit = 2;

SELECT @@innodb_buffer_pool_size/1024/1024/1024 as innodb_buffer_pool_size_GB;
SELECT @@innodb_log_file_size/1024/1024/1024 as innodb_log_file_size_GB;
SELECT @@innodb_flush_log_at_trx_commit;

-- 连接配置
SET GLOBAL max_connections = 1000;
SET GLOBAL thread_cache_size = 50;

SELECT @@max_connections;
SELECT @@thread_cache_size;

innodb_buffer_pool_size

innodb_buffer_pool_size - 内存缓冲池
作用： InnoDB存储引擎的数据和索引缓存池，MySQL最重要的内存参数

推荐设置：
专用数据库服务器：物理内存的 70-80%
混合服务器：物理内存的 50-60%
最小值：128MB，建议不低于1GB

影响：
过小：频繁磁盘I/O，性能急剧下降
过大：系统内存不足，可能导致OOM
合理：大幅提升读写性能，减少磁盘访问

查看：select @@innodb_buffer_pool_size/1024/1024/1024 as innodb_buffer_pool_size_GB ;
设置：SET GLOBAL innodb_buffer_pool_size=402653184;

innodb_log_file_size

innodb_log_file_size - 事务日志文件大小

作用： InnoDB重做日志文件大小，影响写入性能和崩溃恢复时间

推荐设置：
轻量应用：256MB - 512MB
中等负载：512MB - 1GB
重写负载：1GB - 2GB

影响：
过小：频繁日志切换，写入性能下降
过大：崩溃恢复时间长，启动慢
合理：平衡写入性能和恢复时间

max_connections

max_connections - 最大连接数

作用： 服务器允许的最大并发连接数

推荐设置：
小型应用：100-300
中型应用：300-800
大型应用：800-2000

影响：
过小：连接拒绝，应用无法访问
过大：内存消耗增加，上下文切换频繁
合理：满足并发需求，不浪费资源

示例配置

-- 8GB内存服务器示例配置
innodb_buffer_pool_size = 5G
innodb_log_file_size = 1G
max_connections = 500

-- 查看当前设置
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_log_file_size';
SHOW VARIABLES LIKE 'max_connections';

分区表优化

-- 按月分区
CREATE TABLE orders_partitioned (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    order_no VARCHAR(32) NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
    PARTITION p201901 VALUES LESS THAN (201902),
    PARTITION p201902 VALUES LESS THAN (201903),
    PARTITION p201903 VALUES LESS THAN (201904),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

posted @ 2019-11-13 17:59 lvlin241 阅读(701) 评论(0) 收藏举报

刷新页面返回顶部