1. MySQL性能优化全景图
作为关系型数据库的标杆产品，MySQL的性能优化一直是开发者关注的焦点。记得2013年处理过一个电商促销系统，当时QPS冲到8000+时数据库响应明显变慢，经过系列优化后最终支撑住了双11流量洪峰。这场实战让我深刻认识到：MySQL优化不是某个银弹技术，而是需要从索引设计、SQL编写到架构演进的全方位体系化实践。
2. 索引优化实战精要
2.1 B+树索引原理深度解析
MySQL的InnoDB引擎采用B+树作为索引数据结构，其特点包括：
- 所有数据存储在叶子节点，非叶子节点仅存储键值
- 叶子节点通过指针连接形成有序链表
- 树的高度通常维持在3-4层
这种结构使得：
- 等值查询时间复杂度为O(log n)
- 范围查询效率极高（利用叶子节点链表）
- 全表扫描实际是遍历叶子节点链表
重要提示：索引列的顺序直接影响查询效率。假设有联合索引(a,b,c)，以下查询能利用索引：
- WHERE a=1 AND b=2 AND c=3
- WHERE a=1 AND b>2
但WHERE b=2这类查询无法使用该索引
2.2 索引设计黄金法则
- 最左前缀原则实践
sql复制-- 创建联合索引
ALTER TABLE orders ADD INDEX idx_region_status(region, status);
-- 有效查询
SELECT * FROM orders WHERE region='east';
SELECT * FROM orders WHERE region='east' AND status='shipped';
-- 无效查询（无法使用索引）
SELECT * FROM orders WHERE status='shipped';
- 索引选择性优化
计算字段的选择性：
sql复制SELECT
COUNT(DISTINCT user_id)/COUNT(*) AS selectivity
FROM users;
经验值：
- 高于0.2：适合建索引
- 低于0.1：考虑其他优化方式
- 覆盖索引妙用
sql复制-- 普通查询（需要回表）
SELECT * FROM products WHERE category='electronics';
-- 优化为覆盖索引查询
ALTER TABLE products ADD INDEX idx_category_name(category, name);
SELECT category, name FROM products WHERE category='electronics';
3. SQL语句优化实战
3.1 执行计划深度解读
使用EXPLAIN关键字段解析：
sql复制EXPLAIN SELECT * FROM orders WHERE user_id=100;
重点关注：
- type列：从优到劣排序为 system > const > eq_ref > ref > range > index > ALL
- key_len：实际使用的索引长度
- rows：预估需要检查的行数
- Extra：Using filesort、Using temporary 表示需要优化
3.2 高频问题SQL优化案例
案例1：分页查询优化
sql复制-- 原始低效写法
SELECT * FROM logs ORDER BY create_time DESC LIMIT 10000, 20;
-- 优化方案（利用覆盖索引+延迟关联）
SELECT t.* FROM logs t
JOIN (
SELECT id FROM logs
ORDER BY create_time DESC
LIMIT 10000, 20
) tmp ON t.id=tmp.id;
案例2：大数据量COUNT优化
sql复制-- 低效的全表COUNT
SELECT COUNT(*) FROM user_actions;
-- 优化方案
-- 方案1：使用近似值
SHOW TABLE STATUS LIKE 'user_actions';
-- 方案2：维护计数表
CREATE TABLE counter (
table_name VARCHAR(100) PRIMARY KEY,
count BIGINT NOT NULL
);
4. 分库分表架构设计
4.1 拆分策略对比
| 策略类型 | 适用场景 | 优点 | 缺点 |
|---|---|---|---|
| 水平拆分 | 单表数据量大 | 扩展性强 | 跨分片查询复杂 |
| 垂直拆分 | 字段冷热分离 | 减少IO | 需要业务改造 |
| 按时间拆分 | 有明显时间特征 | 管理方便 | 历史数据访问不便 |
4.2 ShardingSphere实战配置
yaml复制# 分片规则配置示例
spring:
shardingsphere:
datasource:
names: ds0,ds1
sharding:
tables:
t_order:
actual-data-nodes: ds$->{0..1}.t_order_$->{0..15}
table-strategy:
inline:
sharding-column: order_id
algorithm-expression: t_order_$->{order_id % 16}
database-strategy:
inline:
sharding-column: user_id
algorithm-expression: ds$->{user_id % 2}
4.3 分布式事务解决方案
- XA协议：MySQL 5.7+支持
sql复制-- 开启XA事务
XA START 'transaction_id';
-- 执行SQL...
XA END 'transaction_id';
XA PREPARE 'transaction_id';
XA COMMIT 'transaction_id';
- Seata框架：
java复制@GlobalTransactional
public void crossDatabaseOperation() {
// 操作多个数据源
}
5. 生产环境调优参数
5.1 InnoDB核心参数
ini复制[mysqld]
innodb_buffer_pool_size = 12G # 建议设为物理内存的50-70%
innodb_log_file_size = 2G # 重做日志大小
innodb_flush_log_at_trx_commit = 2 # 平衡安全性与性能
innodb_read_io_threads = 8 # 读线程数
innodb_write_io_threads = 4 # 写线程数
5.2 连接池优化建议
java复制// HikariCP推荐配置
HikariConfig config = new HikariConfig();
config.setMaximumPoolSize(50); // 最大连接数
config.setMinimumIdle(10); // 最小空闲连接
config.setIdleTimeout(600000); // 空闲超时(ms)
config.setConnectionTimeout(30000); // 连接超时(ms)
config.setMaxLifetime(1800000); // 连接最大存活时间
6. 监控与性能分析体系
6.1 关键性能指标
sql复制-- 查询缓存命中率
SELECT
(1 - (Qcache_not_cached / (Qcache_hits + Qcache_inserts))) * 100 AS hit_rate
FROM performance_schema.global_status
WHERE variable_name IN ('Qcache_hits','Qcache_inserts','Qcache_not_cached');
-- InnoDB缓冲池命中率
SELECT
(1 - (innodb_buffer_pool_reads / innodb_buffer_pool_read_requests)) * 100
AS hit_rate FROM performance_schema.global_status;
6.2 慢查询分析技巧
sql复制-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; # 超过1秒的查询
-- 使用pt-query-digest分析
pt-query-digest /var/log/mysql/mysql-slow.log
7. 真实案例：电商系统优化实录
某电商平台在促销期间出现数据库响应缓慢，通过以下步骤解决：
-
问题定位：
- 发现orders表全表扫描频繁
- 支付流水表索引失效
-
优化措施：
- 为orders表添加复合索引(user_id, status)
- 重建支付流水表的create_time索引
- 将商品描述等大字段移到单独表
-
效果对比：
指标 优化前 优化后 QPS 1200 4500 平均响应时间 800ms 120ms CPU使用率 95% 65%
这套优化方案最终支撑系统平稳度过流量高峰，核心在于抓住了索引设计和查询模式优化这两个关键点。