MySQL性能优化全攻略
一、索引优化
1.1 索引原理：为什么索引能加速查询？
MySQL索引基于B+树数据结构实现（InnoDB引擎），其核心优势在于以下几个关键特性：
-
高效的数据结构设计：
- 叶子节点存储完整数据（聚簇索引）或主键地址（非聚簇索引），减少磁盘IO次数
- 典型的3-4层层级结构，支持快速定位数据，百万级数据只需3-4次IO操作
- 叶子节点形成有序链表，支持高效的范围查询（如between、order by等操作）
-
性能对比示例：
- 无索引情况：当执行全表扫描时，如果表中有100万条数据，MySQL需要遍历所有记录才能找到结果，耗时可能达到秒级（如1-5秒）
- 有索引情况：通过B+树索引，仅需3-4次IO操作，耗时可以降至毫秒级（如10-50毫秒）
-
底层实现细节：
- InnoDB中每个索引页大小默认为16KB
- 假设主键为bigint（8字节），指针为6字节，一个索引页可存储约16KB/(8+6)≈1170个键值
- 3层B+树可存储约1170×1170×16≈2200万条记录
1.2 常见索引类型及适用场景
不同业务场景需要选择不同类型的索引，错误的索引选择等于"无效优化"。以下是详细说明：
索引类型对比表
| 索引类型 | 特点 | 适用场景 | 实际案例 |
|---|---|---|---|
| 主键索引（PRIMARY） | 唯一且非空，InnoDB默认聚簇索引 | 表的唯一标识 | 用户ID、订单ID、商品SKU |
| 唯一索引（UNIQUE） | 值唯一，允许空值 | 需要保证字段唯一性的场景 | 用户手机号、邮箱、身份证号 |
| 普通索引（INDEX） | 无约束，可重复 | 高频查询但不需唯一性的字段 | 商品分类、用户昵称、创建时间 |
| 联合索引（复合索引） | 多字段组合，遵循"最左前缀原则" | 多字段联合查询 | WHERE a=? AND b=? AND c>? |
| 前缀索引（KEY(column(n))） | 对字段前n个字符建立索引 | 长文本字段 | 用户地址、商品描述、备注信息 |
联合索引创建示例
-- 典型电商场景：频繁查询某个分类下价格低于X的最新商品
-- 查询模式：WHERE category_id=? AND price<? ORDER BY create_time DESC
-- 联合索引设计要点：
-- 1. 高频过滤字段(category_id)放在最左
-- 2. 范围查询字段(price)放在中间
-- 3. 排序字段(create_time)放在最后
CREATE INDEX idx_category_price_create ON goods(category_id, price, create_time);
-- 优化后的查询示例
EXPLAIN SELECT * FROM goods
WHERE category_id=3 AND price<1000
ORDER BY create_time DESC LIMIT 20;
1.3 索引设计的"黄金原则"
-
适度索引原则：
- 索引会加速查询但会降低DML操作（INSERT/UPDATE/DELETE）速度
- 每个索引需要单独维护B+树结构
- 生产环境建议：单表索引不超过5个，每个联合索引字段不超过3个
-
优先优化高频查询：
- 通过慢查询日志（slow_query_log）识别高频慢SQL
- 使用EXPLAIN分析执行计划
- 典型优化目标：减少filesort和temporary表
-
联合索引设计规则：
- 严格遵循"最左前缀"原则
- 示例：索引(a,b,c)支持以下查询：
- WHERE a=?
- WHERE a=? AND b=?
- WHERE a=? AND b=? AND c=?
- 不支持：
- WHERE b=?
- WHERE b=? AND c=?
- WHERE a=? AND c=?
-
选择性原则：
- 避免为选择性差的字段建索引
- 计算选择性公式：SELECT COUNT(DISTINCT column)/COUNT(*) FROM table
- 选择性<0.1的字段通常不适合单独建索引（如性别、状态标志）
-
表达式约束：
- 避免在索引字段上使用函数或运算
- 反面案例：
-- 索引失效 SELECT * FROM users WHERE SUBSTR(name,1,3)='张三'; SELECT * FROM orders WHERE amount+10>100; - 优化方案：
-- 使用前缀索引替代 CREATE INDEX idx_name_prefix ON users(name(3)); SELECT * FROM users WHERE name LIKE '张三%';
1.4 索引失效的10种常见场景（深度解析）
-
OR连接非索引字段：
-- 当b字段无索引时，a的索引也会失效 SELECT * FROM table WHERE a=1 OR b=2; -- 优化方案：改为UNION ALL SELECT * FROM table WHERE a=1 UNION ALL SELECT * FROM table WHERE b=2 AND a<>1; -
类型不匹配：
-- 字段是INT类型，但用字符串查询 SELECT * FROM users WHERE id='123'; -- 优化：保持类型一致 SELECT * FROM users WHERE id=123; -
否定操作：
-- 索引失效操作 SELECT * FROM products WHERE status NOT IN (1,2); SELECT * FROM orders WHERE id<>100; -- 优化方案 SELECT * FROM products WHERE status IN (3,4,5); -
范围查询后的索引失效：
-- 联合索引(a,b,c) -- b的索引会失效 SELECT * FROM table WHERE a>10 AND b=20; -- 优化方案：调整查询条件顺序或索引顺序 -
LIKE模糊查询：
-- 前导通配符导致索引失效 SELECT * FROM users WHERE name LIKE '%张三%'; -- 可使用索引的写法 SELECT * FROM users WHERE name LIKE '张三%'; -- 优化方案：考虑使用全文索引 -
函数操作索引字段：
-- 索引失效案例 SELECT * FROM orders WHERE DATE(create_time)='2024-01-01'; -- 优化方案 SELECT * FROM orders WHERE create_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-01 23:59:59'; -
排序与索引不匹配：
-- 索引(a,b) -- 导致filesort SELECT * FROM table ORDER BY b,a; -- 优化：调整ORDER BY顺序与索引一致 SELECT * FROM table ORDER BY a,b; -
GROUP BY无索引：
-- 无索引时会产生临时表 SELECT category,COUNT(*) FROM products GROUP BY category; -- 优化：为分组字段添加索引 ALTER TABLE products ADD INDEX idx_category(category); -
NULL值判断：
-- 可能失效的操作 SELECT * FROM users WHERE phone IS NOT NULL; -- 优化：考虑使用默认值替代NULL -
数据比例过高：
-- 当查询结果超过表数据的20-30%时，MySQL可能放弃索引 SELECT * FROM products WHERE status=1; -- status=1占80%数据 -- 优化方案：添加LIMIT或使用覆盖索引 SELECT id FROM products WHERE status=1 LIMIT 1000;
索引效果验证方法
使用EXPLAIN分析SQL执行计划，关键指标说明：
EXPLAIN SELECT * FROM goods WHERE category_id=1 AND price<100;
- type列：查询访问类型，从好到差：
- const > eq_ref > ref > range > index > ALL
- key列：实际使用的索引
- rows列：预估需要检查的行数
- Extra列：
- Using index：覆盖索引
- Using filesort：需要额外排序
- Using temporary：使用临时表
理想情况下，优化目标是达到range级别以上，避免出现ALL（全表扫描）。
二、SQL 语句优化
2.1 查询优化：减少"不必要的消耗"
1. 只查询需要的字段，避免使用 select *
反例：
select * from user where id=1
- 问题：此查询会返回所有字段，包括可能不需要的大字段（如头像、备注等），增加数据传输量
- 影响：当表中有BLOB/TEXT类型字段时，查询性能会显著下降
正例：
select id, name, phone from user where id=1
- 优点：
- 减少数据传输量，提高查询响应速度
- 如果查询的字段都包含在索引中，可以触发"覆盖索引"机制，避免回表操作
- 示例：假设在(id,name,phone)上有联合索引，此查询可直接使用索引完成
2. 避免"无限制的limit"分页查询
反例：
select id from goods order by create_time desc limit 100000, 10
- 问题：MySQL会先扫描前100010条数据，然后丢弃前100000条，效率极低
- 性能影响：随着偏移量增大，查询时间线性增长
正例：
select id from goods
where id < (select id from goods order by id desc limit 100000, 1)
order by id desc limit 10
- 优化原理：
- 利用主键索引快速定位分页起始点
- 通过子查询先获取第100000条记录的主键ID
- 然后使用该ID作为过滤条件进行分页
- 适用场景：适用于主键有序且连续的情况
3. 使用JOIN替代子查询
反例：
select name from user where id in (select user_id from order where status=1)
- 问题：子查询会生成临时表，效率较低
- 性能影响：当子查询结果集较大时，临时表创建和销毁开销大
正例：
select u.name from user u
join order o on u.id = o.user_id
where o.status=1
- 优点：
- JOIN操作通常比子查询效率更高
- 可以利用索引优化连接条件
- 数据量大时性能优势更明显
4. ORDER BY优化：避免Using filesort
核心原则：
- 让ORDER BY字段与索引顺序一致
- 避免在ORDER BY中对字段进行运算
反例：
select id from goods where category_id=1 order by price+1 desc
- 问题：对price字段进行运算会导致无法使用索引排序
- 执行计划：会显示"Using filesort"，表示需要额外排序操作
正例：
select id from goods where category_id=1 order by price desc
- 优化建议：
- 创建联合索引：(category_id, price)
- 确保ORDER BY字段与索引顺序一致
- 避免在ORDER BY中使用函数或表达式
2.2 插入优化：批量插入 + 减少事务开销
1. 批量插入替代循环单条插入
反例（100次单条插入）：
insert into user(name) values('张三');
insert into user(name) values('李四');
-- ...重复100次
- 问题：每次插入都需要建立连接、解析SQL、执行、提交等完整流程
- 性能影响：网络往返次数多，效率低下
正例（1次批量插入）：
insert into user(name) values('张三'),('李四'),('王五'),...,('赵六');
- 建议：
- 单次批量插入建议不超过1000条
- 大批量数据可分多次批量插入
- 可使用LOAD DATA INFILE替代大批量INSERT
2. 关闭自动提交事务
InnoDB默认autocommit=1，每条插入都会触发事务提交
优化方案：
set autocommit=0; -- 关闭自动提交
-- 执行批量插入
insert into user(name) values(...);
insert into user(name) values(...);
-- 更多插入操作...
commit; -- 手动提交事务
set autocommit=1; -- 恢复自动提交
- 优势：
- 减少事务提交次数
- 批量操作共享一个事务上下文
- 显著提高大批量插入性能
3. 有序主键插入优化
问题：
- InnoDB主键是聚簇索引，若主键无序（如UUID），会导致索引树频繁分裂
- 随机主键会使插入变为随机I/O，而非顺序I/O
解决方案：
-
单机环境：使用自增主键
CREATE TABLE user ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) ); -
分布式环境：使用雪花算法(Snowflake)
- 生成有序的64位ID
- 包含时间戳、工作机器ID、序列号等部分
- 示例实现：Twitter的Snowflake算法
2.3 更新/删除优化：避免"全表锁"
1. 必须加条件且条件字段有索引
反例：
update user set status=0
- 危险：无WHERE条件会更新全表
- 后果：触发全表锁，阻塞其他所有操作
正例：
update user set status=0 where id in (1,2,3)
- 要求：
- WHERE条件必须有效
- 条件字段应有索引（如主键id）
- 仅锁定符合条件的行
2. 批量更新拆分为小批量
问题场景：
update user set status=0 where id between 1 and 100000
- 风险：可能导致行锁升级为表锁
- 影响：长时间锁表阻塞其他查询
优化方案：
-- 第一批
update user set status=0 where id between 1 and 1000;
-- 间隔100ms
update user set status=0 where id between 1001 and 2000;
-- 继续分批...
- 最佳实践：
- 每次更新1000条左右
- 批次间间隔50-100ms
- 可配合程序循环实现自动化分批
- 监控锁等待情况调整批次大小
三、MySQL 配置优化
3.1 核心配置参数说明（my.cnf）
[mysqld] 配置段详解
1. 基础配置（文件路径与日志设置）
datadir=/var/lib/mysql # MySQL数据存储目录，存放所有数据库表文件
socket=/var/lib/mysql/mysql.sock # MySQL通信套接字文件路径
symbolic-links=0 # 禁用符号链接（安全考虑）
log-error=/var/log/mysqld.log # 错误日志路径（启动问题排查关键）
pid-file=/var/run/mysqld/mysqld.pid # 进程ID文件位置
典型应用场景：当MySQL启动失败时，首先检查/var/log/mysqld.log中的错误信息；数据迁移时需要修改datadir指向新位置。
2. 内存配置优化（性能关键）
innodb_buffer_pool_size=4G # InnoDB引擎核心缓存，存储数据页和索引页
# 举例：32G内存的生产服务器建议设为20G(62.5%)，64G内存可设为40G(62.5%)
# 可通过SHOW ENGINE INNODB STATUS查看缓冲池命中率（应>95%）
key_buffer_size=512M # MyISAM引擎专用缓存
# 现代应用大多使用InnoDB引擎，若确认无MyISAM表可降至16M
# 以下参数每个连接都会单独分配，需谨慎设置：
sort_buffer_size=2M # 排序操作缓冲区（ORDER BY, GROUP BY）
# 典型问题：设为256M时，1000连接将消耗256GB内存！
join_buffer_size=2M # 表连接操作缓冲区
read_buffer_size=1M # 全表扫描时的读取缓冲区
read_rnd_buffer_size=4M # 随机读操作缓冲区
3. 连接管理配置
max_connections=1000 # 最大并发连接数
# 电商大促期间需临时调高，日常根据SHOW STATUS LIKE 'Threads_connected'监控
# 连接过多会导致OOM，可通过连接池控制实际连接数
max_user_connections=800 # 单用户最大连接数限制
# 防止单个应用错误导致连接耗尽，如PHP应用连接泄漏
wait_timeout=600 # 非交互连接空闲超时（秒）
interactive_timeout=600 # 交互连接空闲超时（如mysql客户端）
# 典型优化：API服务可设为300，减少sleep连接堆积
4. InnoDB引擎高级配置
innodb_flush_log_at_trx_commit=1 # ACID事务保证级别
# 1=最高安全（每次提交刷盘），2=每秒刷盘（性能提升30%但可能丢失1秒数据）
# 支付系统必须=1，用户行为日志可=2
innodb_log_file_size=512M # 重做日志文件大小
# 建议设置能容纳1小时的写入量，太大影响崩溃恢复速度
# 修改需先停止MySQL，删除旧日志文件再启动
innodb_io_capacity=2000 # 预设磁盘IO能力
# SSD建议2000-5000，NVMe可设10000，HDD设为200-400
# 需配合innodb_io_capacity_max（默认2倍值）使用
innodb_read_io_threads=8 # 后台读线程数
innodb_write_io_threads=8 # 后台写线程数
# 现代服务器建议设为CPU核心数的50-75%
5. 查询监控日志配置
slow_query_log=1 # 开启慢查询日志
slow_query_log_file=/var/log/mysql/slow.log # 日志路径
long_query_time=2 # 慢查询阈值（秒）
# 微服务架构建议设为0.5秒，OLAP系统可放宽至5秒
log_queries_not_using_indexes=1 # 记录全表扫描查询
# 开发阶段强烈建议开启，生产环境谨慎使用（可能产生大量日志）
3.2 配置优化原则与实施指南
内存分配策略
-
缓冲池优先原则：
innodb_buffer_pool_size应设为可用物理内存的50-70%- 计算依据：
总内存 - (其他服务内存 + OS缓存) - 动态调整：MySQL 5.7+支持在线修改
SET GLOBAL innodb_buffer_pool_size=6G
- 计算依据：
-
连接内存控制：
sort_buffer_size等会话级参数需限制- 计算公式：
总内存 > max_connections × (sort_buffer_size + join_buffer_size + ...)
- 计算公式：
存储引擎调优
-
SSD优化方案：
innodb_io_capacity=4000 innodb_io_capacity_max=8000 innodb_flush_neighbors=0 # SSD无需聚集写入 -
高并发写入优化：
innodb_thread_concurrency=0 # 取消并发限制 innodb_write_io_threads=16 # 增加写线程
连接管理最佳实践
- 连接数计算公式：
建议max_connections = (平均QPS × 峰值时间 × 平均查询耗时) + 20%冗余 - 连接池配置：
- Java应用建议使用HikariCP
- 连接池大小应与MySQL的
max_connections匹配
监控与调整流程
- 初始配置后运行基准测试
- 监控关键指标：
SHOW STATUS LIKE 'Innodb_buffer_pool_read%'; SHOW STATUS LIKE 'Threads_connected'; SHOW ENGINE INNODB STATUS\G - 根据监控数据渐进式调整参数
四、存储引擎选择
MySQL 支持多种存储引擎，其中 InnoDB 和 MyISAM 是最常用的两种，选择错误会直接导致性能问题。不同引擎在事务处理、并发控制、数据恢复等关键特性上存在显著差异，深入了解这些差异对数据库设计和优化至关重要。
4.1 两大引擎核心差异
特性对比表
| 特性 | InnoDB | MyISAM |
|---|---|---|
| 事务支持 | 支持 ACID 事务（原子性、一致性、隔离性、持久性） | 不支持事务，无法保证数据一致性 |
| 锁机制 | 行级锁（仅锁定受影响的行，适合高并发写操作） | 表级锁（任何写操作都会锁定整个表） |
| 外键支持 | 支持外键约束（保证数据完整性） | 完全不支持外键 |
| 索引类型 | 聚簇索引（主键索引直接存储数据行） | 非聚簇索引（索引文件与数据文件分离） |
| 崩溃恢复 | 支持（通过 redo/undo 日志实现故障恢复） | 不支持（系统崩溃易导致数据丢失） |
| 全文索引 | MySQL 5.6 及以上版本支持 | 原生支持全文索引 |
| 数据缓存 | 同时缓存索引和数据 | 仅缓存索引 |
| 表空间 | 共享表空间或独立表空间 | 每个表对应独立的文件 |
性能特点对比
InnoDB 性能特点：
- 写操作性能优秀，特别是在高并发场景下
- 支持MVCC（多版本并发控制），读操作不会阻塞写操作
- 适合处理大量短期事务
- 支持热备份（通过工具如XtraBackup）
MyISAM 性能特点：
- 读操作性能极高（比InnoDB快约30%）
- 全表扫描性能优异
- 表压缩功能可以有效减少存储空间
- 统计信息精确，查询优化器选择执行计划更准确
4.2 选择建议与最佳实践
选择建议
优先选择 InnoDB 的情况：
- 当前 MySQL 5.5+ 版本的默认引擎
- 需要事务支持的场景（如电商订单、支付系统）
- 高并发写入环境（如社交媒体的用户动态）
- 数据安全性要求高的应用（如银行系统）
- 需要外键约束保证数据完整性
- 预计会有大量更新操作的系统
仅考虑 MyISAM 的特殊场景：
- 纯静态数据表（如地区编码表、国家列表）
- 日志表（只插入不更新的操作日志）
- 需要全文索引且MySQL版本低于5.6
- 数据仓库类应用（大量分析查询）
- 临时表或中间结果存储
实际应用示例
InnoDB 适用场景：
- 电商平台：订单系统、支付系统、购物车
- 社交应用：用户关系、消息系统
- 金融系统：账户余额、交易记录
MyISAM 适用场景：
- 博客系统的文章表（读多写少）
- 网站访问日志（只追加不修改）
- 数据仓库的报表表（只读分析）
引擎切换注意事项
-
从MyISAM切换到InnoDB：
- 确保应用能处理事务
- 可能需要调整自增列处理方式
- 重建所有索引（语法不同）
- 检查外键约束
-
性能优化建议：
- 为InnoDB配置足够的缓冲池（innodb_buffer_pool_size）
- 合理设置事务隔离级别
- 为MyISAM配置适当的键缓存（key_buffer_size）
-
禁用其他引擎的情况：
- Memory引擎：数据存储在内存中，服务器重启会丢失
- CSV引擎：无索引支持，仅适合数据导出导入
- Archive引擎：只支持插入和查询，不支持更新和删除
4.3 版本演进与未来趋势
随着MySQL的版本更新，存储引擎的特性也在不断演进：
- MySQL 5.6+：InnoDB开始支持全文索引
- MySQL 5.7+：InnoDB性能进一步提升
- MySQL 8.0+：MyISAM逐渐被边缘化，建议全面转向InnoDB
对于新项目，强烈建议统一使用InnoDB引擎，除非有明确的性能测试表明MyISAM在特定场景下优势明显。同时，定期监控引擎使用情况，通过慢查询日志和性能监控工具及时发现潜在问题。
五、分库分表
5.1 分库分表核心概念
分库（Database Sharding）
将一个数据库拆分为多个独立的数据库实例，通常有以下几种方式：
- 业务维度分库：如电商系统拆分为用户库、订单库、商品库
- 地域维度分库：如华北库、华东库、华南库
- 功能维度分库：如主库（读写）、从库（只读）、报表库
分表（Table Partitioning）
将一个大表拆分为多个结构相同的小表：
- 水平分表：按行拆分，各分表结构相同
- 垂直分表：按列拆分，将不同字段拆分到不同表
分片键（Sharding Key）
选择合适的分片键至关重要：
- 用户ID：适合用户数据分散的场景
- 订单ID/时间：适合订单类时序数据
- 哈希值：确保数据均匀分布
水平拆分 vs 垂直拆分
| 拆分方式 | 特点 | 适用场景 |
|---|---|---|
| 水平拆分 | 同表结构，按行拆分 | 数据量大的表 |
| 垂直拆分 | 按字段拆分表 | 字段多且访问模式差异大的表 |
5.2 分库分表场景
性能瓶颈场景
-
单表数据量过大：
- 索引失效：B+树层级变深，查询效率下降
- 维护成本高：ALTER TABLE操作耗时增加
- 示例：用户表超过2000万条后，查询延迟明显增加
-
单库并发过高：
- 连接池耗尽
- 锁竞争加剧
- 示例：大促期间订单库QPS突破8000，响应时间超过1秒
-
业务隔离需求：
- 关键业务隔离（如支付系统）
- 故障隔离（避免单点故障影响全局）
- 示例：用户登录服务与商品浏览服务分离
5.3 常见分库分表方案及实现
方案1：水平分表（哈希分片）
详细实现步骤：
- 选择分片键（如user_id）
- 确定分片数量（如10个分表）
- 实现哈希算法：
hash(user_id) % 10 - 路由到对应分表
扩容方案：
- 停机扩容：简单但影响业务
- 双写迁移：新旧分片同时写入
- 使用一致性哈希减少数据迁移量
方案2：水平分表（时间分片）
典型应用：
- 订单表：order_202301, order_202302...
- 日志表：log_20230101, log_20230102...
冷热数据分离：
- 热数据：最近3个月数据
- 温数据：3-12个月数据
- 冷数据：归档到对象存储
方案3：垂直分表
字段拆分原则：
- 高频查询字段放主表
- 大字段（BLOB/TEXT）放扩展表
- 低频字段放扩展表
示例：
-- 用户主表
CREATE TABLE user_basic (
id BIGINT PRIMARY KEY,
username VARCHAR(50),
mobile VARCHAR(20)
);
-- 用户详情表
CREATE TABLE user_detail (
user_id BIGINT PRIMARY KEY,
avatar VARCHAR(255),
bio TEXT,
FOREIGN KEY (user_id) REFERENCES user_basic(id)
);
分库分表工具对比
| 工具 | 类型 | 优点 | 缺点 | 适用场景 |
|---|---|---|---|---|
| Sharding-JDBC | 客户端代理 | 无中心化，性能好 | 仅支持Java | Java单体应用 |
| MyCat | 服务端代理 | 多语言支持 | 性能开销大 | 多语言微服务 |
| TDDL | 客户端代理 | 动态扩容能力 | 学习曲线陡 | 阿里云环境 |
进阶配置示例
ShardingSphere分库分表配置
spring:
shardingsphere:
datasource:
names: ds0,ds1
ds0:
type: com.zaxxer.hikari.HikariDataSource
jdbc-url: jdbc:mysql://db0:3306/demo
username: root
password: pass
ds1:
type: com.zaxxer.hikari.HikariDataSource
jdbc-url: jdbc:mysql://db1:3306/demo
username: root
password: pass
rules:
sharding:
tables:
t_order:
actual-data-nodes: ds$->{0..1}.t_order_$->{0..15}
database-strategy:
standard:
sharding-column: user_id
precise-algorithm-class-name: com.demo.algorithm.DatabaseShardingAlgorithm
table-strategy:
standard:
sharding-column: order_id
precise-algorithm-class-name: com.demo.algorithm.TableShardingAlgorithm
分布式事务配置
spring:
shardingsphere:
props:
sql-show: true
max-connections-size-per-query: 5
executor-size: 20
proxy-frontend-flush-threshold: 128
proxy-transaction-type: XA
proxy-backend-query-fetch-size: -1
check-table-metadata-enabled: false
sql-comment-parse-enabled: false
注意事项
-
分布式ID生成：
- 雪花算法（Snowflake）
- UUID
- 数据库自增序列（需中央协调）
-
跨分片查询：
- 避免全表扫描
- 使用分片键查询
- 考虑建立全局索引表
-
分布式事务：
- XA协议
- TCC模式
- SAGA模式
- 本地消息表
-
扩容步骤：
graph TD A[评估扩容需求] --> B[准备新节点] B --> C[配置双写] C --> D[数据迁移] D --> E[流量切换] E --> F[下线旧节点]
六、读写分离
6.1 读写分离原理详解
架构设计
采用"1 主 N 从"的架构模式，其中：
- 主库(Master)：专门处理所有写操作(INSERT、UPDATE、DELETE)和DDL操作
- 从库(Slave)：通常配置多个，专门处理SELECT查询请求，数量可根据读负载动态扩展
数据同步机制
主从库之间通过MySQL的binlog日志实现数据同步：
- 主库将变更操作记录到二进制日志(binlog)
- 从库的I/O线程读取主库的binlog
- 从库的SQL线程重放binlog中的操作
- 同步方式默认为异步，典型延迟在50-200ms之间
请求路由策略
- 写请求：100%路由到主库
- 读请求：可采用多种分配策略：
- 简单轮询(Round-Robin)
- 加权分配(如从库1:60%，从库2:40%)
- 基于从库负载动态调整
- 就近路由(如跨机房部署时优先访问同机房从库)
6.2 读写分离适用场景分析
高读低写场景
- 电商系统：
- 商品详情页浏览(QPS可达数万)
- 订单查询(下单后频繁查看状态)
- 内容平台：
- 新闻/博客阅读(发布后大量用户访问)
- 视频信息页(元数据查询)
- 社交网络：
- 朋友圈动态加载
- 用户主页访问
数据一致性要求
适合容忍短暂不一致的业务：
- 非核心数据(如商品评论数)
- 统计类数据(如UV/PV报表)
- 历史数据查询(如三个月前的订单)
不适用场景
- 金融交易系统(要求强一致性)
- 实时库存管理(需准确反映当前库存)
- 在线协作编辑(文档即时同步)
6.3 实现方式技术细节
方式1：应用层实现
实现要点
// Spring Boot多数据源配置示例
@Configuration
public class DataSourceConfig {
@Bean
@Primary
@ConfigurationProperties(prefix="spring.datasource.master")
public DataSource masterDataSource() {
return DataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties(prefix="spring.datasource.slave")
public DataSource slaveDataSource() {
return DataSourceBuilder.create().build();
}
}
// 使用AOP自动路由
@Aspect
@Component
public class ReadWriteAspect {
@Before("execution(* com.example..*.find*(..)) || execution(* com.example..*.get*(..))")
public void setReadDataSource() {
DynamicDataSource.setDataSource("slave");
}
@Before("execution(* com.example..*.save*(..)) || execution(* com.example..*.update*(..))")
public void setWriteDataSource() {
DynamicDataSource.setDataSource("master");
}
}
优缺点
- 优点：实现简单，无额外依赖
- 缺点：需修改业务代码，难以应对复杂路由规则
方式2：中间件实现
Sharding-JDBC高级配置
spring:
shardingsphere:
datasource:
names: ds_master,ds_slave1,ds_slave2
rules:
readwrite-splitting:
data-sources:
rw_ds:
static-strategy:
write-data-source-name: ds_master
read-data-source-names: ds_slave1,ds_slave2
load-balancer-name: weight_balancer
load-balancers:
weight_balancer:
type: WEIGHT
props:
ds_slave1: 70
ds_slave2: 30
其他中间件对比
| 中间件 | 协议层 | 性能损耗 | 功能完整性 |
|---|---|---|---|
| MyCat | 应用层 | 较高 | 完善 |
| ProxySQL | 代理层 | 中等 | 专业 |
| MySQL Router | 官方方案 | 低 | 基础 |
6.4 注意事项深度解析
数据延迟解决方案
-
强制读主库：
@Transactional(readOnly=false) // 通过事务声明强制走主库 public User getFreshUser(Long id) { return masterJdbcTemplate.queryForObject(...); } -
半同步复制配置：
-- 主库配置 INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; SET GLOBAL rpl_semi_sync_master_enabled=1; SET GLOBAL rpl_semi_sync_master_timeout=10000; # 10秒超时 -
GTID复制：确保数据一致性
CHANGE MASTER TO MASTER_AUTO_POSITION=1; # 启用基于GTID的复制
从库扩展建议
- 性能瓶颈测试：每新增一个从库，主库CPU增加约5-8%
- 推荐架构：
主库 ├── 从库1（同机房） ├── 从库2（同机房） ├── 从库3（异地灾备） └── 从库4（专用报表库）
故障转移实现
# Prometheus监控配置示例
alerting:
rules:
- alert: SlaveDown
expr: mysql_slave_status_slave_io_running == 0 or mysql_slave_status_slave_sql_running == 0
for: 1m
labels:
severity: critical
annotations:
summary: "MySQL slave {{ $labels.instance }} is down"
description: "Slave replication has been interrupted"
七、性能监控与问题排查
7.1 核心监控指标
查询性能指标
- 慢查询数量：记录执行时间超过设定阈值（通常为1-2秒）的查询数量，反映系统整体查询效率
- 平均查询耗时：所有查询的平均执行时间，业务高峰期应重点关注
- 全表扫描次数：未使用索引的扫描操作，可通过
show status like 'Handler_read%'查看 - 查询缓存命中率：反映查询缓存使用效率（MySQL 8.0+已移除该功能）
连接状态指标
- 当前连接数：
show status like 'Threads_connected'显示的实时连接数 - 活跃连接数：
show status like 'Threads_running'显示正在执行查询的连接数 - 连接超时次数：
Aborted_connects显示失败的连接尝试次数 - 最大连接数使用率：监控
max_connections限制是否合理
资源占用指标
- CPU使用率：重点关注
user和sys时间的比例 - 内存使用率：包括全局缓冲区和会话级内存使用
- 磁盘IO：
- 读吞吐量：
read_bytes/秒 - 写吞吐量：
write_bytes/秒 - IO等待时间：
iowait百分比
- 读吞吐量：
- 网络流量：进出数据库的网络数据量
InnoDB引擎指标
- 缓冲池命中率：关键指标，应保持在99%以上
- 日志刷盘次数：
innodb_os_log_written反映写入量 - 锁等待时间：
innodb_row_lock_time_avg反映并发性能 - 脏页比例：
innodb_buffer_pool_pages_dirty占比 - 读写比例：
innodb_rows_read与innodb_rows_inserted+updated+deleted的比值
7.2 常用监控工具
MySQL原生工具集
show status命令
- 完整语法：
show [global] status like 'pattern' - 常用示例：
show global status like 'Slow_queries'; -- 累计慢查询数 show status like 'Innodb_row_lock%'; -- 行锁统计 show status like 'Handler_read%'; -- 索引使用情况
show processlist命令
- 实时查看所有连接状态
- 关键字段说明：
Id: 连接IDUser: 连接用户Host: 客户端地址db: 当前数据库Command: 执行命令类型Time: 执行时间(秒)State: 当前状态Info: 正在执行的SQL(前100字符)
慢查询日志配置
- 开启慢查询日志：
set global slow_query_log = 'ON'; set global long_query_time = 1; -- 设置慢查询阈值(秒) set global log_queries_not_using_indexes = 'ON'; -- 记录未使用索引的查询 - 日志分析工具：
mysqldumpslow：MySQL自带分析工具pt-query-digest：Percona提供的更强大分析工具
第三方监控解决方案
Prometheus + Grafana组合
- 部署架构：
- Prometheus Server：定时抓取指标
- MySQL Exporter：暴露MySQL指标
- Grafana：可视化展示
- 核心优势：
- 支持自定义告警规则
- 可集成多种数据源
- 丰富的可视化图表库
Percona Monitoring and Management (PMM)
- 核心组件：
- PMM Server：集中式监控服务器
- PMM Client：部署在被监控主机上
- 特色功能：
- Query Analytics：SQL语句级性能分析
- 复制监控：主从延迟可视化
- 容量规划：基于历史数据的预测
Navicat图形化工具
- 主要功能：
- 可视化表结构分析
- 索引使用情况统计
- 空间占用分析
- 适用场景：
- 开发环境快速诊断
- 小型数据库日常维护
- 数据可视化分析
7.3 常见问题排查流程
查询性能下降排查标准流程
步骤1：确认问题范围
- 确定是全局性能下降还是特定查询变慢
- 检查监控指标变化趋势
步骤2：分析慢查询日志
- 定位高耗时SQL：
pt-query-digest /var/lib/mysql/slow.log - 按执行时间、锁定时间、返回行数排序
步骤3：执行计划分析
- 使用
EXPLAIN或EXPLAIN FORMAT=JSON：explain select * from orders where user_id=100; - 关键关注点：
type列：访问类型(ALL/index/range等)key列：实际使用的索引rows列：预估扫描行数Extra列：额外信息(Using filesort/Using temporary)
步骤4：索引优化检查
- 查看表索引：
show index from orders; - 检查索引使用情况(MySQL 8.0+)：
select * from sys.schema_unused_indexes where table_schema='mydb' and table_name='orders'; - 检查索引统计信息：
analyze table orders; show index from orders where Cardinality is not null;
步骤5：资源瓶颈分析
- CPU使用率：
top -p $(pgrep mysqld) - 磁盘IO：
iostat -x 1 - 内存使用：
show engine innodb status\G
步骤6：配置参数检查
- 关键参数验证：
show variables like 'innodb_buffer_pool%'; show variables like 'query_cache%'; show variables like 'tmp_table_size';
索引使用分析示例
场景：分析goods表的索引使用效率
-
查看现有索引：
show index from goods; -
检查未使用索引(MySQL 8.0+)：
select * from sys.schema_unused_indexes where table_schema='mydb' and table_name='goods'; -
检查索引效率：
select object_schema, object_name, index_name, rows_selected, rows_inserted, rows_updated, rows_deleted from performance_schema.table_io_waits_summary_by_index_usage where object_schema='mydb' and object_name='goods'; -
优化建议：
- 删除长期未使用的冗余索引
- 对高频查询字段添加复合索引
- 定期执行
ANALYZE TABLE更新统计信息
扫一扫
1728
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
