MySQL数据库优化技巧

一、MySQL优化概述

数据库性能优化是后端开发和运维的核心技能之一。MySQL作为最流行的开源关系型数据库，其优化效果直接影响应用的响应速度和系统稳定性。

优化的核心原则：

1. 优先索引优化：80%的性能问题可通过索引解决
2. SQL语句优化：避免全表扫描，减少数据扫描量
3. 配置参数优化：根据硬件资源合理配置
4. 表结构优化：合理设计字段类型和索引
5. 架构优化：读写分离、分库分表

性能优化金字塔：

        ┌─────────────┐
        │  架构优化   │  最高收益
        ├─────────────┤
        │  SQL优化    │
        ├─────────────┤
        │  索引优化   │
        ├─────────────┤
        │ 配置优化    │
        ├─────────────┤
        │ 硬件升级    │  成本最高
        └─────────────┘

二、索引优化实战

1. 索引类型与选择

B-Tree索引（默认）：

• 适用于：=、>、<、>=、<=、BETWEEN、LIKE前缀匹配
• 支持：普通索引、唯一索引、主键索引、联合索引

哈希索引：

• 适用于：Memory引擎，仅支持等值查询
• 特点：查询速度快，但不支持范围查询和排序

全文索引：

• 适用于：文本内容的关键词搜索
• 语法：MATCH(column) AGAINST('keyword')

2. 联合索引最佳实践

最左前缀原则：

-- 联合索引 (a, b, c)
-- 有效查询：
WHERE a = ? 
WHERE a = ? AND b = ?
WHERE a = ? AND b = ? AND c = ?

-- 无效查询（不使用索引）：
WHERE b = ? 
WHERE c = ?
WHERE b = ? AND c = ?

联合索引创建示例：

-- 按选择性从高到低排列
CREATE INDEX idx_user_status_created ON users(status, created_at);

-- 避免选择性低的字段放在前面
-- ❌ 错误：status只有2-3个值，选择性太低
CREATE INDEX idx_status_user ON users(status, user_id);

3. 索引使用技巧

-- 查看索引使用情况
EXPLAIN SELECT * FROM users WHERE id = 100;

-- 强制使用索引
SELECT * FROM users USE INDEX(idx_name) WHERE name = 'test';

-- 忽略索引
SELECT * FROM users IGNORE INDEX(idx_name) WHERE name = 'test';

4. 索引失效场景

✅ 索引有效：

WHERE name = '张三'                  -- 等值查询
WHERE name LIKE '张%'                -- 前缀匹配
WHERE age > 18                       -- 范围查询
WHERE status IN (1, 2)               -- IN查询

❌ 索引失效：

WHERE LEFT(name, 1) = '张'            -- 函数操作
WHERE name LIKE '%张三'               -- 前缀%
WHERE age + 1 = 19                    -- 字段运算
WHERE status = 1 OR name = 'test'     -- OR两边无索引
WHERE name IS NULL                    -- IS NULL（部分情况）

三、SQL语句优化

1. SELECT查询优化

避免SELECT *：

-- ❌ 不推荐：读取所有字段，浪费IO和内存
SELECT * FROM users;

-- ✅ 推荐：只读取需要的字段
SELECT id, name, email FROM users;

分页查询优化：

-- ❌ 深度分页性能差
SELECT * FROM orders LIMIT 1000000, 20;

-- ✅ 优化方案1：记录上次ID
SELECT * FROM orders WHERE id > 1000000 LIMIT 20;

-- ✅ 优化方案2：子查询延迟关联
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders LIMIT 1000000, 20) t
ON o.id = t.id;

JOIN查询优化：

-- 小表驱动大表
-- ✅ 正确：A表100条，B表10000条
SELECT * FROM A INNER JOIN B ON A.id = B.a_id;

-- 确保关联字段有索引
ALTER TABLE B ADD INDEX idx_a_id(a_id);

2. 子查询优化

优先使用JOIN替代IN子查询：

-- ❌ 子查询（MySQL5.5及以下性能差）
SELECT * FROM orders 
WHERE user_id IN (SELECT id FROM users WHERE status = 1);

-- ✅ JOIN写法（性能更优）
SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.status = 1;

3. GROUP BY优化

-- 确保GROUP BY字段有索引
ALTER TABLE orders ADD INDEX idx_created(created_at);

-- 避免临时表和文件排序
EXPLAIN SELECT DATE(created_at), COUNT(*) 
FROM orders GROUP BY DATE(created_at);

-- 使用覆盖索引
SELECT user_id, COUNT(*) FROM orders 
GROUP BY user_id;  -- user_id有索引即可

4. EXPLAIN分析工具

重点关注字段：

EXPLAIN示例分析：

EXPLAIN SELECT * FROM users WHERE name = 'test';

-- ✅ 好的执行计划：
-- type: ref, key: idx_name, rows: 1, Extra: Using where

-- ❌ 坏的执行计划：
-- type: ALL, key: NULL, rows: 100000, Extra: Using where

四、配置参数优化（my.cnf）

1. 内存相关配置

[mysqld]
# 缓冲池大小（推荐物理内存的50%-70%）
innodb_buffer_pool_size = 4G

# 日志缓冲区（16M-64M）
innodb_log_buffer_size = 16M

# 排序缓冲区（每个连接）
sort_buffer_size = 2M

# 连接缓冲区
join_buffer_size = 2M

# 临时表大小
tmp_table_size = 64M
max_heap_table_size = 64M

2. 连接与并发配置

# 最大连接数（根据并发调整）
max_connections = 500

# 线程缓存
thread_cache_size = 64

# 超时设置
wait_timeout = 600
interactive_timeout = 600

3. InnoDB专属优化

# 事务日志大小（256M-1G）
innodb_log_file_size = 256M
innodb_log_files_in_group = 2

# 刷新日志策略（1=最安全，2=高性能）
innodb_flush_log_at_trx_commit = 1

# IO线程数
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# 脏页刷新比例
innodb_max_dirty_pages_pct = 75

# 独立表空间
innodb_file_per_table = 1

# 刷新方法
innodb_flush_method = O_DIRECT

4. 查询缓存（MySQL8.0已移除）

# 开启查询缓存
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M

五、表结构优化

1. 字段类型选择

整数类型：

字符串类型：

-- ✅ 固定长度用CHAR
CHAR(32)    -- MD5、UUID

-- ✅ 可变长度用VARCHAR
VARCHAR(50)  -- 用户名、邮箱
VARCHAR(255) -- 标题、简介

-- ✅ 大文本用TEXT
TEXT         -- 文章内容
LONGTEXT     -- 超长内容

时间类型：

-- ✅ DATETIME：范围大，与时区无关
DATETIME  -- '1000-01-01'到'9999-12-31'

-- ✅ TIMESTAMP：占空间小，自动更新
TIMESTAMP -- '1970-01-01'到'2038-01-19'

-- ❌ 避免用字符串存时间
VARCHAR(20) -- 性能差，无法使用时间函数

2. 字段设计原则

-- ✅ 非空字段设置默认值
status TINYINT NOT NULL DEFAULT 0

-- ✅ 金额用DECIMAL
amount DECIMAL(10,2) NOT NULL DEFAULT 0.00

-- ❌ 避免NULL字段（索引效率低）
name VARCHAR(50) NULL  -- 改为 NOT NULL DEFAULT ''

-- ✅ 枚举类型用TINYINT
-- 0=未知，1=男，2=女
gender TINYINT NOT NULL DEFAULT 0

3. 表设计规范

• 单表字段数：控制在20-50个
• 单表数据量：建议500万以内，超量考虑分表
• 避免大字段：TEXT/BLOB尽量单独拆表
• 适当冗余：减少JOIN，用空间换时间

六、慢查询分析与优化

1. 开启慢查询日志

[mysqld]
# 开启慢查询
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log

# 超过1秒记录
long_query_time = 1

# 记录未使用索引的查询
log_queries_not_using_indexes = 1

2. 慢查询日志分析

# 查看慢查询日志
tail -f /var/log/mysql/slow.log

# 使用mysqldumpslow分析
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 参数说明：
# -s t: 按时间排序
# -s c: 按次数排序
# -t 10: 显示前10条

3. 常用性能监控命令

-- 查看连接数
SHOW STATUS LIKE 'Threads_connected';

-- 查看QPS
SHOW STATUS LIKE 'Questions';

-- 查看TPS
SHOW STATUS LIKE 'Com_commit';
SHOW STATUS LIKE 'Com_rollback';

-- 查看缓存命中率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

-- 查看慢查询数量
SHOW STATUS LIKE 'Slow_queries';

-- 查看当前运行的SQL
SHOW PROCESSLIST;

-- 杀掉慢查询
KILL 
<process_id>;

七、高级优化策略

1. 读写分离

          ┌─────────────┐
          │   应用层    │
          └──────┬──────┘
                 │
          ┌──────▼──────┐
          │  代理层     │  (ProxySQL/MaxScale)
          └──┬──────┬───┘
             │      │
      ┌──────▼─┐ ┌─▼──────┐
      │  主库  │ │ 从库1  │  写操作
      └────────┘ └────────┘  读操作
                 ┌────────┐
                 │ 从库2  │
                 └────────┘

2. 分库分表

水平分表（按数据行拆分）：

orders_00  (user_id % 4 = 0)
orders_01  (user_id % 4 = 1)
orders_02  (user_id % 4 = 2)
orders_03  (user_id % 4 = 3)

垂直分库（按业务拆分）：

user_db    用户库
order_db   订单库
product_db 商品库

3. 缓存策略

-- 热点数据缓存到Redis
-- 1. 先查缓存，命中直接返回
-- 2. 未命中查数据库，写入缓存
-- 3. 设置合理过期时间

-- 更新策略：先更数据库，再删缓存
UPDATE users SET name = ? WHERE id = ?;
DEL cache:user:100;

总结

MySQL优化方法论：

1. 定位问题：慢查询日志 + EXPLAIN分析
2. 优先索引：合理创建和使用索引
3. SQL优化：避免全表扫描，减少数据扫描
4. 配置调优：根据硬件调整参数
5. 架构升级：读写分离、分库分表、缓存

记住：过早优化是万恶之源，先定位瓶颈再针对性优化！