当前位置：首页 > 学习笔记 > 正文内容

数据库优化完全指南：SQL性能调优的核心实践

廖万里1天前学习笔记0

"数据库性能是系统性能的关键瓶颈。掌握索引优化、查询分析、执行计划解读，能让慢查询提速数十倍甚至上百倍。"

一、索引优化策略

索引类型选择

-- B+树索引：适合范围查询和排序
CREATE INDEX idx_user_created ON users(created_at);

-- 哈希索引：适合等值查询，不支持范围
CREATE INDEX idx_user_email ON users USING HASH(email);

-- 复合索引：遵循最左前缀原则
CREATE INDEX idx_user_status_created ON users(status, created_at);

-- 覆盖索引：查询字段全部在索引中
CREATE INDEX idx_user_cover ON users(status, created_at) INCLUDE (name, email);

-- 全文索引：适合文本搜索
CREATE FULLTEXT INDEX idx_article_content ON articles(content);

索引设计原则

-- 1. 选择性高的列优先
-- 选择性 = 不同值数量 / 总行数，越接近1越好
SELECT 
    COUNT(DISTINCT status) / COUNT(*) as status_selectivity,
    COUNT(DISTINCT user_id) / COUNT(*) as user_selectivity
FROM orders;

-- 2. 复合索引顺序：等值条件在前，范围条件在后
-- 查询：WHERE status = 'active' AND created_at > '2024-01-01'
CREATE INDEX idx_order_status_created ON orders(status, created_at);

-- 3. 避免索引列上使用函数
-- 错误：索引失效
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';

-- 4. 避免隐式类型转换
-- 错误：字符串与数字比较
SELECT * FROM users WHERE phone_number = 13800138000;
-- 正确：统一类型
SELECT * FROM users WHERE phone_number = '13800138000';

二、执行计划分析

MySQL EXPLAIN解读

EXPLAIN SELECT u.name, o.total 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.status = 'active' AND o.created_at > '2024-01-01';

-- 关键字段解读
-- id：执行顺序，越大越先执行
-- type：访问类型，从好到差：system > const > eq_ref > ref > range > index > ALL
-- key：实际使用的索引
-- rows：预估扫描行数
-- Extra：额外信息
--   Using index：覆盖索引
--   Using where：需要回表过滤
--   Using temporary：使用临时表
--   Using filesort：文件排序

-- 慢查询优化示例
-- 优化前：全表扫描
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- type: ALL, rows: 1000000

-- 优化后：索引范围扫描
EXPLAIN SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';
-- type: range, rows: 50000

三、查询优化技巧

避免SELECT *

-- 错误：查询所有列
SELECT * FROM users WHERE id = 1;

-- 正确：只查询需要的列
SELECT id, name, email FROM users WHERE id = 1;

-- 如果有覆盖索引，性能提升明显
CREATE INDEX idx_user_id_name_email ON users(id, name, email);

分页优化

-- 传统分页：OFFSET性能差
SELECT * FROM orders ORDER BY id LIMIT 10000, 20;

-- 游标分页：使用上次查询的最大ID
SELECT * FROM orders WHERE id > 10000 ORDER BY id LIMIT 20;

-- 延迟关联：先查ID，再关联查详情
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY created_at DESC LIMIT 10000, 20
) t ON o.id = t.id;

JOIN优化

-- 小表驱动大表
-- 小表在左，大表在右
SELECT o.* 
FROM (SELECT id FROM orders WHERE status = 'pending') small
JOIN order_items large ON small.id = large.order_id;

-- 确保JOIN字段有索引
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

-- 避免JOIN过多表
-- 建议：单次查询JOIN不超过3张表

四、数据库配置优化

# my.cnf关键配置

# InnoDB缓冲池：物理内存的60-80%
innodb_buffer_pool_size = 8G

# 日志文件大小
innodb_log_file_size = 1G

# 并发线程数
innodb_thread_concurrency = 16

# 查询缓存（MySQL 8.0已移除）
query_cache_size = 0
query_cache_type = 0

# 连接数
max_connections = 500

# 慢查询日志
slow_query_log = 1
long_query_time = 1

五、表结构优化

-- 选择合适的数据类型
-- 整数：TINYINT < SMALLINT < MEDIUMINT < INT < BIGINT
-- 字符串：CHAR定长，VARCHAR变长
-- 时间：DATETIME占8字节，TIMESTAMP占4字节

-- 范式与反范式平衡
-- 第三范式：消除传递依赖
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT NOT NULL,
    -- 不存储用户名，通过JOIN获取
);

-- 反范式：空间换时间，减少JOIN
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT NOT NULL,
    user_name VARCHAR(50),  -- 冗余存储
    INDEX idx_user_name (user_name)
);

-- 垂直拆分：大表拆小表
-- 主表存基本信息
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

-- 扩展表存详细信息
CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    bio TEXT,
    avatar VARCHAR(255)
);

六、监控与分析

-- 查看索引使用情况
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    CARDINALITY,
    SEQ_IN_INDEX
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'mydb'
ORDER BY TABLE_NAME, INDEX_NAME;

-- 查看表状态
SHOW TABLE STATUS LIKE 'orders';

-- 分析表统计信息
ANALYZE TABLE orders;

-- 查看慢查询
SELECT * FROM mysql.slow_log 
WHERE start_time > DATE_SUB(NOW(), INTERVAL 1 DAY)
ORDER BY query_time DESC 
LIMIT 10;

-- Performance Schema监控
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;