MySQL性能优化实战：从查询到架构的全方位指南

一、查询层面优化

1.1 索引优化策略

联合索引的最左前缀原则

联合索引的使用遵循最左前缀原则，即查询必须从索引的最左列开始。例如，对于索引 idx(a, b, c)：

• WHERE a = 1 — 使用索引
• WHERE a = 1 AND b = 2 — 使用索引
• WHERE b = 2 — 不使用索引

覆盖索引的应用

覆盖索引是指查询的所有字段都包含在索引中，避免回表操作。实战案例：

-- 创建覆盖索引
CREATE INDEX idx_user_cover ON users(id, name, email);

-- 查询时直接从索引获取数据，无需回表
SELECT id, name, email FROM users WHERE id = 100;
​

索引选择性分析

索引选择性 = 不重复值数量 / 总行数。选择性越高的列，索引效果越好：

SELECT 
    COUNT(DISTINCT column_name) / COUNT(*) as selectivity
FROM table_name;
​

1.2 SQL语句优化

避免SELECT *

SELECT * 会增加网络传输和内存消耗，只查询需要的字段：

-- 不推荐
SELECT * FROM orders WHERE user_id = 1;

-- 推荐
SELECT id, order_no, amount FROM orders WHERE user_id = 1;
​

优化子查询为JOIN

子查询可能导致性能问题，JOIN通常更高效：

-- 子查询版本
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- JOIN优化版本
SELECT DISTINCT u.* FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.amount > 1000;
​

合理使用EXISTS替代IN

EXISTS在子查询结果集较大时性能更优：

-- EXISTS版本（推荐）
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id AND o.amount > 1000
);
​

批量操作优化

批量插入比单条插入效率高10-100倍：

-- 单条插入
INSERT INTO logs (message) VALUES (log1);
INSERT INTO logs (message) VALUES (log2);

-- 批量插入
INSERT INTO logs (message) VALUES 
    (log1), 
    (log2), 
    (log3);
​

二、表结构设计优化

2.1 字段类型选择

数值类型优化

• 整数类型：TINYINT(1字节)、SMALLINT(2字节)、INT(4字节)、BIGINT(8字节)
• 根据实际范围选择最小类型
• 状态字段使用TINYINT而非VARCHAR

-- 不推荐
CREATE TABLE users (
    status VARCHAR(10)  -- "active", "inactive"
);

-- 推荐
CREATE TABLE users (
    status TINYINT  -- 0: inactive, 1: active
);
​

字符串类型优化

• CHAR定长，VARCHAR变长
• VARCHAR(N)中N表示字符数，存储空间=实际长度+1(或2)字节
• 对于固定长度字符串(如手机号)，CHAR更优

-- 手机号存储
CREATE TABLE users (
    phone CHAR(11)  -- 固定11位，使用CHAR
);

-- 用户名存储
CREATE TABLE users (
    username VARCHAR(50)  -- 长度不固定，使用VARCHAR
);
​

2.2 反范式化设计

适当的反范式化可以减少JOIN，提升查询性能：

案例：订单与用户信息

-- 范式化设计（3NF）
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10,2)
);

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50)
);

-- 反范式化优化
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    username VARCHAR(50),  -- 冗余用户名
    amount DECIMAL(10,2)
);
​

应用场景：

• 读多写少的场景
• 需要频繁JOIN的字段
• 字段更新不频繁

三、架构层面优化

3.1 读写分离

主从复制原理

1. 主库接收写操作，记录binlog
2. 从库I/O线程读取主库binlog并写入relay log
3. 从库SQL线程执行relay log中的操作

实现方案

使用MySQL Router或ProxySQL实现自动路由：

# 伪代码示例
def get_connection(operation):
    if operation in ["SELECT", "SHOW"]:
        return get_slave_connection()  # 读操作使用从库
    else:
        return get_master_connection()  # 写操作使用主库
​

3.2 分库分表

水平分表策略

按ID取模分表：

-- 将订单表分为16个表
CREATE TABLE orders_0 LIKE orders;
CREATE TABLE orders_1 LIKE orders;
-- ... orders_15

-- 路由逻辑
table_index = order_id % 16
table_name = f"orders_{table_index}"
​

垂直分库策略

按业务模块拆分：

• 用户库：用户、账户、权限
• 订单库：订单、订单明细
• 商品库：商品、分类、库存

3.3 缓存架构

Redis缓存层设计

def get_user(user_id):
    # 先查缓存
    cache_key = f"user:{user_id}"
    user = redis.get(cache_key)
    
    if user:
        return json.loads(user)
    
    # 缓存未命中，查数据库
    user = db.query("SELECT * FROM users WHERE id = %s", user_id)
    
    # 写入缓存，设置过期时间
    redis.setex(cache_key, 3600, json.dumps(user))
    
    return user
​

缓存更新策略

• Cache-Aside：先更新DB，再删除缓存
• Write-Through：先写缓存，缓存同步写DB
• Write-Behind：先写缓存，缓存异步写DB

四、配置参数优化

4.1 InnoDB缓冲池

# my.cnf配置
innodb_buffer_pool_size = 4G  # 设置为物理内存的50-70%

# 多实例缓冲池（MySQL 5.6+）
innodb_buffer_pool_instances = 4
​

4.2 连接数优化

max_connections = 500  # 根据业务需求调整
thread_cache_size = 100  # 减少线程创建开销
​

4.3 查询缓存(MySQL 8.0已移除)

# MySQL 5.7及以下版本
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 2M
​

五、监控与诊断

5.1 慢查询日志

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;  -- 超过2秒的查询记录

-- 分析慢查询日志
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
​

5.2 EXPLAIN分析

EXPLAIN SELECT * FROM orders WHERE user_id = 1;

-- 关注关键字段：
-- type: ALL(全表扫描) -> index(索引扫描) -> range(范围扫描) -> ref(索引查找) -> const(单表最多一行)
-- rows: 预计扫描行数
-- Extra: Using filesort(文件排序) / Using temporary(使用临时表)
​

5.3 Performance Schema

-- 查看TOP SQL
SELECT 
    DIGEST_TEXT,
    COUNT_STAR as exec_count,
    AVG_TIMER_WAIT/1000000000000 as avg_time_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;
​

六、实战案例

案例1：电商订单查询优化

问题：订单列表查询耗时5秒以上

优化方案：

1. 创建覆盖索引
2. 使用分页限制返回数据量
3. 引入Redis缓存热门用户订单

-- 优化前
SELECT * FROM orders WHERE user_id = 12345 ORDER BY create_time DESC;

-- 优化后
-- 1. 创建索引
CREATE INDEX idx_user_time ON orders(user_id, create_time, id);

-- 2. 查询优化
SELECT id, order_no, amount, status 
FROM orders 
WHERE user_id = 12345 
ORDER BY create_time DESC 
LIMIT 20 OFFSET 0;
​

效果：查询时间从5秒降至100ms

案例2：千万级数据统计优化

问题：用户订单统计查询超时

优化方案：

1. 使用定时任务预计算统计数据
2. 创建汇总表

-- 创建统计汇总表
CREATE TABLE user_order_summary (
    user_id INT PRIMARY KEY,
    total_orders INT,
    total_amount DECIMAL(12,2),
    last_order_time DATETIME,
    update_time TIMESTAMP
);

-- 定时更新(每小时)
INSERT INTO user_order_summary
SELECT 
    user_id,
    COUNT(*) as total_orders,
    SUM(amount) as total_amount,
    MAX(create_time) as last_order_time,
    NOW() as update_time
FROM orders
GROUP BY user_id
ON DUPLICATE KEY UPDATE
    total_orders = VALUES(total_orders),
    total_amount = VALUES(total_amount),
    last_order_time = VALUES(last_order_time),
    update_time = NOW();
​

效果：统计查询从30秒降至1ms

七、总结

MySQL性能优化是一个系统工程，需要从多个层面综合考虑：

1. 查询优化：索引设计、SQL优化、执行计划分析
2. 表结构优化：字段类型选择、反范式化设计
3. 架构优化：读写分离、分库分表、缓存设计
4. 配置优化：缓冲池、连接数、线程池参数调整
5. 监控诊断：慢查询日志、EXPLAIN、Performance Schema

关键是要结合实际业务场景，找到性能瓶颈，有针对性地进行优化。记住："过早优化是万恶之源"，在优化前一定要做好性能测试和监控。