MySQL慢查询定位与执行计划分析
MySQL性能调优的第一步是识别慢查询。通过开启慢查询日志捕获执行时间超过阈值的SQL语句，再使用EXPLAIN分析执行计划，定位全表扫描、临时表、文件排序等性能瓶颈。这套方法论适用于大多数数据库运维场景。
开启慢查询日志并配置阈值：
-- 查看当前慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
-- 动态开启慢查询日志（重启后失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1; -- 超过1秒的SQL记录
SET GLOBAL log_queries_not_using_indexes = 'ON'; -- 记录未使用索引的查询
-- 永久生效需写入 my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1使用EXPLAIN分析SQL执行计划：
EXPLAIN SELECT
o.order_id, o.total_amount, u.username, p.product_name
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.create_time >= '2026-01-01'
AND o.status = 'PAID'
ORDER BY o.total_amount DESC
LIMIT 20;EXPLAIN输出中需要重点关注以下字段：
+----+--------+--------+------------------+---------+---------+------------------+--------+----------------------------------------------+
| id | select_type| table | type | key | key_len | ref | rows | Extra |
+----+--------+--------+------------------+---------+---------+------------------+--------+----------------------------------------------+
| 1 | SIMPLE | o | ref | idx_status_time | 130 | const | 15000 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | u | eq_ref | PRIMARY | 4 | test.o.user_id | 1 | NULL |
| 1 | SIMPLE | oi | ref | idx_order_id | 4 | test.o.order_id | 3 | NULL |
| 1 | SIMPLE | p | eq_ref | PRIMARY | 4 | test.oi.product_id| 1 | NULL |
+----+--------+--------+------------------+---------+---------+------------------+--------+----------------------------------------------+type字段的值表示访问类型，性能从好到差：system > const > eq_ref > ref > range > index > ALL。上述结果中orders表出现了Using temporary和Using filesort，说明查询创建了临时表并进行了文件排序，这是需要优化的重点。
复合索引设计与覆盖索引优化
索引设计是SQL查询优化的核心。MySQL的InnoDB引擎使用B+树索引，理解最左前缀原则和覆盖索引概念对性能调优至关重要。
复合索引遵循最左前缀原则：索引(a, b, c)可以用于查询条件a、a,b、a,b,c，但不能用于b,c或c。索引列的顺序应将区分度高的列放在前面，将范围查询的列放在最后。
-- 针对上述慢查询的索引优化
-- 原始查询条件：status = 'PAID' AND create_time >= '2026-01-01'
-- 排序：ORDER BY total_amount DESC
-- 错误索引：将范围查询列放前面
-- CREATE INDEX idx_time_status ON orders(create_time, status);
-- 问题：create_time是范围查询，后面的status无法走索引
-- 正确索引：等值查询在前，范围查询在后
CREATE INDEX idx_status_time_amount ON orders(status, create_time, total_amount);
-- 如果查询只需要索引包含的列，可以使用覆盖索引避免回表
-- 覆盖索引：查询的所有列都在索引中，不需要回表查数据行
EXPLAIN SELECT order_id, total_amount
FROM orders
WHERE status = 'PAID' AND create_time >= '2026-01-01';
-- Extra列显示 Using index，表示使用了覆盖索引索引创建的注意事项：单表索引数量不宜超过5-6个，过多索引会影响写入性能；索引列长度尽量短，长字符串使用前缀索引；避免在索引列上使用函数或类型转换，否则索引失效。
-- 索引失效的常见场景
-- 1. 函数操作导致索引失效
SELECT * FROM orders WHERE YEAR(create_time) = 2026;
-- 改写为范围查询：
SELECT * FROM orders
WHERE create_time >= '2026-01-01' AND create_time < '2027-01-01';
-- 2. 隐式类型转换
SELECT * FROM orders WHERE order_no = 123456789;
-- 如果order_no是varchar类型，应改为：
SELECT * FROM orders WHERE order_no = '123456789';
-- 3. LIKE以通配符开头
SELECT * FROM products WHERE product_name LIKE '%手机%';
-- 如果必须使用前缀模糊匹配，考虑全文索引或搜索引擎
-- 4. OR条件中有一侧无索引
SELECT * FROM orders WHERE order_id = 100 OR remark = '加急';
-- 确保OR两侧的列都有索引，或改用UNION ALL分库分表方案与ShardingSphere实践
当单表数据量超过千万级别，B+树索引层级增加导致查询性能下降，写入性能也受影响。分库分表是解决这一问题的标准方案。ShardingSphere是目前Java生态中主流的分库分表中间件，支持声明式配置和透明化的SQL路由。
分片策略选择：水平分表按行拆分数据到多个表，适合单表数据量巨大的场景；水平分库将数据分散到不同数据库实例，能同时缓解单库连接数和磁盘IO瓶颈。分片键的选择是核心决策，应选择查询条件中频繁使用且分布均匀的列。
// Spring Boot + ShardingSphere 配置
// application.yml
spring:
shardingsphere:
datasource:
names: ds0,ds1
ds0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.1.10:3306/order_db_0
username: root
password: password
ds1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.1.11:3306/order_db_1
username: root
password: password
rules:
sharding:
tables:
t_order:
actual-data-nodes: ds${0..1}.t_order_${0..3}
database-strategy:
standard:
sharding-column: user_id
sharding-algorithm-name: db-inline
table-strategy:
standard:
sharding-column: order_id
sharding-algorithm-name: table-inline
key-generate-strategy:
column: order_id
key-generator-name: snowflake
sharding-algorithms:
db-inline:
type: INLINE
props:
algorithm-expression: ds${user_id % 2}
table-inline:
type: INLINE
props:
algorithm-expression: t_order_${order_id % 4}
key-generators:
snowflake:
type: SNOWFLAKE
props:
worker-id: 1分库分表后的跨库查询问题需要特别处理。分片键不相同时，Join查询需要广播表或绑定表支持。对于聚合统计类查询，ShardingSphere会合并各分片结果。复杂的跨库Join建议在应用层组装，或引入Elasticsearch等搜索引擎做查询分离。
Redis缓存策略与缓存一致性
NoSQL选型应用中Redis是最常用的缓存方案。缓存能大幅降低数据库压力，但引入了缓存与数据库一致性这一经典难题。Cache-Aside（旁路缓存）模式是生产环境最常用的缓存策略。
// Cache-Aside 模式的标准实现
@Service
public class ProductService {
@Autowired
private ProductRepository productRepository;
@Autowired
private RedisTemplate redisTemplate;
private static final String CACHE_PREFIX = "product:";
private static final long CACHE_TTL = 3600; // 缓存1小时
// 读操作：先读缓存，缓存未命中再读数据库
public Product getProduct(Long id) {
String key = CACHE_PREFIX + id;
// 1. 查缓存
Product product = (Product) redisTemplate.opsForValue().get(key);
if (product != null) {
return product;
}
// 2. 缓存未命中，查数据库
product = productRepository.findById(id).orElse(null);
if (product != null) {
// 3. 写入缓存（带随机TTL防止缓存雪崩）
long ttl = CACHE_TTL + ThreadLocalRandom.current().nextInt(300);
redisTemplate.opsForValue().set(key, product, ttl, TimeUnit.SECONDS);
} else {
// 4. 空值缓存（防止缓存穿透）
redisTemplate.opsForValue().set(key, "NULL", 60, TimeUnit.SECONDS);
}
return product;
}
// 写操作：先更新数据库，再删除缓存
@Transactional
public void updateProduct(Long id, ProductDTO dto) {
// 1. 更新数据库
Product product = productRepository.findById(id)
.orElseThrow(() -> new BusinessException("商品不存在"));
product.setName(dto.getName());
product.setPrice(dto.getPrice());
productRepository.save(product);
// 2. 删除缓存（而非更新缓存，避免并发写入导致脏数据）
redisTemplate.delete(CACHE_PREFIX + id);
}
} 缓存三大问题及解决方案：
// 1. 缓存穿透：大量请求查询不存在的数据
// 解决方案：布隆过滤器 + 空值缓存
public Product getProductWithBloomFilter(Long id) {
// 布隆过滤器检查：如果不存在直接返回
if (!bloomFilter.mightContain(id)) {
return null;
}
return getProduct(id); // 正常的Cache-Aside流程
}
// 2. 缓存击穿：热点Key过期瞬间大量请求打到数据库
// 解决方案：互斥锁（双重检查）
public Product getProductWithLock(Long id) {
String key = CACHE_PREFIX + id;
Product product = (Product) redisTemplate.opsForValue().get(key);
if (product != null) {
return product;
}
// 获取分布式锁
String lockKey = "lock:product:" + id;
try {
boolean locked = redisTemplate.opsForValue()
.setIfAbsent(lockKey, "1", 10, TimeUnit.SECONDS);
if (locked) {
// 双重检查
product = (Product) redisTemplate.opsForValue().get(key);
if (product != null) {
return product;
}
// 查数据库并写入缓存
product = productRepository.findById(id).orElse(null);
if (product != null) {
redisTemplate.opsForValue().set(key, product,
CACHE_TTL, TimeUnit.SECONDS);
}
return product;
} else {
// 等待并重试
Thread.sleep(50);
return getProductWithLock(id);
}
} finally {
redisTemplate.delete(lockKey);
}
}
// 3. 缓存雪崩：大量Key同时过期
// 解决方案：TTL加随机值（已在getProduct中实现）
// 数据库高可用架构层面：限流降级、多级缓存（本地缓存+Redis）数据备份恢复方面，MySQL推荐使用Percona XtraBackup做物理热备份，结合binlog实现PITR（基于时间点的恢复）。Redis使用RDB定期快照+AOF增量日志的策略，AOF配置appendfsync everysec在性能和数据安全性之间取得平衡。分库分表环境下的备份需要各分片独立备份，恢复时注意分片间的数据一致性校验。这些数据库高可用架构的实践，是保障业务数据安全的底线方案。
原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/mysql-xing-neng-diao-you-shi-zhan-man-cha-xun-fen-xi-suo/
