数据库SQL调优的核心技巧与实战应用

-- 创建复合索引时，遵循最左前缀原则
CREATE INDEX idx_user_status_date ON users(status, created_at, user_id);
 
-- 避免在索引列上使用函数
-- 不推荐
SELECT * FROM users WHERE DATE(created_at) = '2024-01-01';
 
-- 推荐
SELECT * FROM users 
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02';

-- 创建覆盖索引，避免回表查询
CREATE INDEX idx_covering ON orders(user_id, order_date, total_amount);
 
-- 查询字段都在索引中，无需回表
SELECT user_id, order_date, total_amount 
FROM orders 
WHERE user_id = 12345 AND order_date >= '2024-01-01';

-- 不推荐：返回所有列，增加IO负担
SELECT * FROM products WHERE category_id = 10;
 
-- 推荐：只查询需要的列
SELECT product_id, product_name, price 
FROM products 
WHERE category_id = 10;

-- 确保JOIN字段有索引
CREATE INDEX idx_order_user_id ON orders(user_id);
CREATE INDEX idx_user_user_id ON users(user_id);
 
-- 使用合适的JOIN类型
SELECT o.order_id, u.username, o.total_amount
FROM orders o
INNER JOIN users u ON o.user_id = u.user_id
WHERE o.order_date >= '2024-01-01';

-- 不推荐：相关子查询，性能较差
SELECT * FROM products p 
WHERE p.price > (
    SELECT AVG(price) FROM products WHERE category_id = p.category_id
);
 
-- 推荐：使用JOIN替代子查询
SELECT p.* 
FROM products p
INNER JOIN (
    SELECT category_id, AVG(price) as avg_price
    FROM products 
    GROUP BY category_id
) avg_p ON p.category_id = avg_p.category_id
WHERE p.price > avg_p.avg_price;

-- MySQL执行计划分析
EXPLAIN FORMAT=JSON
SELECT u.username, COUNT(o.order_id) as order_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE u.created_at >= '2024-01-01'
GROUP BY u.user_id;

-- 不推荐：OFFSET越大性能越差
SELECT * FROM orders 
ORDER BY order_id DESC 
LIMIT 100000, 20;
 
-- 推荐：使用游标分页
SELECT * FROM orders 
WHERE order_id < 9999999
ORDER BY order_id DESC 
LIMIT 20;

-- 基于时间戳的游标分页
SELECT * FROM messages 
WHERE created_at < '2024-01-15 10:30:00'
ORDER BY created_at DESC 
LIMIT 20;

-- 创建统计表
CREATE TABLE daily_stats (
    stat_date DATE PRIMARY KEY,
    total_orders INT,
    total_amount DECIMAL(10,2),
    updated_at TIMESTAMP
);
 
-- 定时任务更新统计
INSERT INTO daily_stats (stat_date, total_orders, total_amount)
SELECT 
    DATE(order_date) as stat_date,
    COUNT(*) as total_orders,
    SUM(total_amount) as total_amount
FROM orders 
WHERE order_date >= CURDATE() - INTERVAL 1 DAY
GROUP BY DATE(order_date)
ON DUPLICATE KEY UPDATE
    total_orders = VALUES(total_orders),
    total_amount = VALUES(total_amount),
    updated_at = NOW();

-- PostgreSQL物化视图
CREATE MATERIALIZED VIEW mv_user_stats AS
SELECT 
    user_id,
    COUNT(*) as order_count,
    SUM(total_amount) as total_spent,
    MAX(order_date) as last_order_date
FROM orders 
GROUP BY user_id;
 
-- 刷新物化视图
REFRESH MATERIALIZED VIEW mv_user_stats;

-- 原始查询
SELECT * FROM orders 
WHERE user_id = 12345 
ORDER BY order_date DESC 
LIMIT 10;
 
-- 优化步骤1：创建复合索引
CREATE INDEX idx_user_date ON orders(user_id, order_date DESC);
 
-- 优化步骤2：只查询必要字段
SELECT order_id, order_date, status, total_amount 
FROM orders 
WHERE user_id = 12345 
ORDER BY order_date DESC 
LIMIT 10;
 
-- 优化步骤3：考虑分区表
CREATE TABLE orders_2024 (
    LIKE orders INCLUDING ALL
) PARTITION BY RANGE (order_date);

-- 创建汇总表
CREATE TABLE monthly_sales_summary (
    year_month VARCHAR(7) PRIMARY KEY,
    total_sales DECIMAL(15,2),
    order_count INT,
    avg_order_value DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- 存储过程定时更新
DELIMITER //
CREATE PROCEDURE update_monthly_summary()
BEGIN
    INSERT INTO monthly_sales_summary (year_month, total_sales, order_count, avg_order_value)
    SELECT 
        DATE_FORMAT(order_date, '%Y-%m') as year_month,
        SUM(total_amount) as total_sales,
        COUNT(*) as order_count,
        AVG(total_amount) as avg_order_value
    FROM orders 
    WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 2 MONTH)
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
    ON DUPLICATE KEY UPDATE
        total_sales = VALUES(total_sales),
        order_count = VALUES(order_count),
        avg_order_value = VALUES(avg_order_value);
END //
DELIMITER ;

-- MySQL慢查询配置
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';
 
-- 分析慢查询
SELECT 
    query_time, 
    lock_time, 
    rows_examined, 
    rows_sent,
    sql_text
FROM mysql.slow_log 
ORDER BY query_time DESC 
LIMIT 10;

-- 监控索引使用情况
SELECT 
    object_schema,
    object_name,
    index_name,
    count_read,
    count_fetch
FROM performance_schema.table_io_waits_summary_by_index_usage 
WHERE object_schema NOT IN ('mysql','performance_schema')
ORDER BY count_read DESC;

-- MySQL自动优化建议
SELECT 
    table_schema,
    table_name,
    column_name,
    cardinality,
    COUNT(*) as total_rows
FROM information_schema.columns c
JOIN information_schema.tables t 
    ON c.table_schema = t.table_schema AND c.table_name = t.table_name
WHERE c.table_schema NOT IN ('mysql','information_schema','performance_schema')
    AND cardinality / COUNT(*) < 0.1
GROUP BY table_schema, table_name, column_name
HAVING total_rows > 1000;