MySQL性能优化实战：从慢查询到极致性能
登录
侧边栏壁纸
博主昵称
私人云

  • 累计撰写 1,041 篇文章
  • 累计收到 3 条评论
采集分类

MySQL性能优化实战：从慢查询到极致性能

私人云
私人云
2026-01-30 / 0 评论 / 0 阅读 / 正在检测是否收录...

前言

上周我们优化了一条执行时间从2.5秒降到50ms的复杂查询，用户体验立竿见影。MySQL性能优化看似神秘，其实有章可循。

这篇文章将分享我在电商系统中总结的实战经验。

一、性能诊断三板斧

1.1 开启慢查询日志

sql

SET GLOBAL slow_query_log = ON;SET GLOBAL long_query_time = 0.5;SET GLOBAL log_queries_not_using_indexes = ON;

1.2 分析慢查询

bash

# 安装mysqldumpslowmysqldumpslow -s c -t 10 /var/log/mysql/slow.log# 输出：# Count: 150 Lock_time: 0.15s Rows_sent: 5 Rows_examined: 50000# SELECT * FROM orders WHERE status=pending

1.3 EXPLAIN执行计划分析

sql

EXPLAIN SELECT * FROM orders oJOIN customers c ON o.customer_id = c.idWHERE o.created_date > 2024-01-01AND o.status = completed;-- 关键字段解读：-- type: index(索引扫描) > range(范围扫描) > ref(关联扫描) > ALL(全表扫描)-- key: 实际使用的索引-- rows: 扫描的行数-- filtered: 筛选比例

二、索引优化策略

2.1 创建高效索引

sql

-- ❌ 低效：字段顺序不当CREATE INDEX idx_order ON orders(status, customer_id, created_date);-- ✅ 优化：按查询频率排序CREATE INDEX idx_order ON orders(customer_id, created_date, status);-- ✅ 更优：覆盖索引（避免回表）CREATE INDEX idx_order_cover ON orders(customer_id, created_date, status, amount, pay_method);-- 联合索引最左匹配原则SELECT * FROM orders WHERE customer_id = 1 AND created_date > 2024-01-01;-- ✓ 使用索引SELECT * FROM orders WHERE created_date > 2024-01-01 AND customer_id = 1;-- ✗ 不走索引（没有customer_id作为前缀）

2.2 索引维护和监控

sql

-- 查看索引使用情况SELECT object_name, count_read, count_writeFROM performance_schema.table_io_waits_summary_by_index_usageWHERE object_schema != mysqlORDER BY count_read DESC;-- 找出冗余索引SELECT a.object_name, a.index_name AS 冗余索引, b.index_name AS 关键索引FROM performance_schema.table_io_waits_summary_by_index_usage aJOIN performance_schema.table_io_waits_summary_by_index_usage bON a.object_name = b.object_nameAND a.count_read = 0AND b.count_read > 0;-- 删除冗余索引DROP INDEX idx_old ON orders;-- 定期重建索引OPTIMIZE TABLE orders;

三、查询优化技巧

3.1 避免常见陷阱

sql

-- ❌ 错误1：不走索引的LIKESELECT * FROM users WHERE name LIKE %张%;-- ✅ 正确：前缀匹配SELECT * FROM users WHERE name LIKE 张%;-- ❌ 错误2：隐式类型转换SELECT * FROM orders WHERE order_id = 12345;-- 字符串转数字，不走索引-- ✅ 正确：类型匹配SELECT * FROM orders WHERE order_id = 12345;-- ❌ 错误3：函数包裹字段SELECT * FROM orders WHERE DATE(created_date) = 2024-01-15;-- ✅ 正确：范围查询SELECT * FROM ordersWHERE created_date >= 2024-01-15AND created_date < 2024-01-16;-- ❌ 错误4：OR导致索引失效SELECT * FROM ordersWHERE customer_id = 1 OR status = pending;-- ✅ 正确：使用UNIONSELECT * FROM orders WHERE customer_id = 1UNIONSELECT * FROM orders WHERE status = pending;

3.2 JOIN优化

sql

-- ❌ 笛卡尔积（最常见的性能杀手）SELECT * FROM orders o, customers c;-- 如果orders 100万行，customers 10万行-- 结果：1000亿行！-- ✅ 使用显式JOIN和ON条件SELECT o.order_id, o.amount, c.customer_nameFROM orders oINNER JOIN customers c ON o.customer_id = c.idWHERE o.created_date > 2024-01-01;-- JOIN顺序优化（小表驱动大表）-- 方案1：使用STRAIGHT_JOIN指定顺序SELECT * FROM orders oSTRAIGHT_JOIN order_items oi ON o.id = oi.order_idWHERE o.status = completed;-- 方案2：让优化器自动选择-- MySQL 8.0+会自动优化，通常无需手动指定

3.3 子查询vs JOIN

sql

-- ❌ 子查询（可能产生临时表）SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE country = China);-- ✅ 使用JOIN（更高效）SELECT DISTINCT o.* FROM orders oINNER JOIN customers c ON o.customer_id = c.idWHERE c.country = China;-- EXISTS vs IN（大数据量时EXISTS更优）-- ❌ IN（需要扫描整个子查询结果）SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE vip_level > 3);-- ✅ EXISTS（可提前终止扫描）SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM customers cWHERE c.id = o.customer_id AND c.vip_level > 3);

四、分区和分片策略

4.1 表分区（单机优化）

sql

-- 创建分区表CREATE TABLE orders_partition (id BIGINT PRIMARY KEY AUTO_INCREMENT,customer_id INT NOT NULL,amount DECIMAL(10,2),created_date DATE NOT NULL,status VARCHAR(20)) PARTITION BY RANGE (YEAR(created_date)) (PARTITION p2022 VALUES LESS THAN (2023),PARTITION p2023 VALUES LESS THAN (2024),PARTITION p2024 VALUES LESS THAN (2025),PARTITION p_future VALUES LESS THAN MAXVALUE);-- 查询时自动选择分区SELECT * FROM orders_partitionWHERE created_date > 2024-01-01;-- 只扫描p2024和p_future分区-- 分区维护-- 删除旧分区ALTER TABLE orders_partition DROP PARTITION p2022;-- 添加新分区ALTER TABLE orders_partitionADD PARTITION (PARTITION p2025 VALUES LESS THAN (2026));

4.2 水平分片（分布式扩展）

python

# 分片键选择# ✅ 好的分片键：# - customer_id（数据均衡分布）# - user_id（业务含义清晰）# ❌ 不好的分片键：# - status（数据分布不均）# - region（容易出现热点）class ShardingManager:def __init__(self, db_hosts):self.db_hosts = db_hosts # 5个数据库节点self.shard_count = len(db_hosts)def get_shard_index(self, customer_id):"""一致性哈希或简单取模"""return customer_id % self.shard_countdef execute_query(self, customer_id, sql):"""根据customer_id路由到对应分片"""shard_index = self.get_shard_index(customer_id)db_host = self.db_hosts[shard_index]return db_host.execute(sql)# 使用sharding = ShardingManager([db1.example.com,db2.example.com,db3.example.com,db4.example.com,db5.example.com])result = sharding.execute_query(customer_id=12345,sql=SELECT * FROM orders WHERE customer_id = 12345)``*

五、缓存策略

5.1 查询缓存

sql

-- MySQL查询缓存（MySQL 5.7已弃用，8.0移除）-- 改用应用层缓存：Redis-- 缓存预热查询SELECT customer_id, COUNT(*) as order_countFROM ordersGROUP BY customer_id;

5.2 Redis缓存实现

python

import redisimport jsonimport timeclass OrderCache:def __init__(self):self.redis_client = redis.Redis(host=localhost, port=6379)self.ttl = 3600 # 1小时def get_customer_orders(self, customer_id):"""先查缓存，再查数据库"""# 1. 查询Redis缓存cache_key = fcustomer:{customer_id}:orderscached = self.redis_client.get(cache_key)if cached:return json.loads(cached)# 2. 缓存未命中，查询数据库orders = self.db.query(SELECT * FROM orders WHERE customer_id = %s,[customer_id])# 3. 写入缓存self.redis_client.setex(cache_key,self.ttl,json.dumps(orders))return ordersdef invalidate_cache(self, customer_id):"""订单更新时删除缓存"""cache_key = fcustomer:{customer_id}:ordersself.redis_client.delete(cache_key)# 使用cache = OrderCache()orders = cache.get_customer_orders(12345)# 下单后更新cache.invalidate_cache(12345)

六、批量操作优化

6.1 批量插入

sql

-- ❌ 逐条插入（1000次网络往返，极其低效）INSERT INTO orders (customer_id, amount) VALUES (1, 100);INSERT INTO orders (customer_id, amount) VALUES (2, 200);-- ...重复1000次-- ✅ 批量插入（1次网络往返）INSERT INTO orders (customer_id, amount) VALUES(1, 100),(2, 200),(3, 300),(4, 400),(5, 500);-- 更优：load data（最快，20倍性能提升）LOAD DATA INFILE /tmp/orders.csvINTO TABLE ordersFIELDS TERMINATED BY ,LINES TERMINATED BY \n(customer_id, amount);

6.2 批量更新

sql

-- ❌ 逐行更新UPDATE orders SET status = shipped WHERE id = 1;UPDATE orders SET status = shipped WHERE id = 2;-- ✅ 批量更新UPDATE orders SET status = shippedWHERE id IN (1, 2, 3, 4, 5);-- 使用CASE WHEN更新不同值UPDATE orders SETstatus = CASEWHEN amount > 1000 THEN vipWHEN amount > 100 THEN normalELSE regularENDWHERE created_date > 2024-01-01;

七、监控和告警

7.1 关键性能指标

sql

-- QPS（每秒查询数）SHOW STATUS LIKE Questions;-- TPS（每秒事务数）SHOW STATUS LIKE Innodb_trx_*;-- 连接数SHOW STATUS LIKE Threads_connected;SHOW STATUS LIKE Threads_running;-- 行锁等待SHOW ENGINE INNODB STATUS;

7.2 Prometheus监控配置

yaml

# prometheus.ymlscrape_configs:- job_name: mysqlstatic_configs:- targets: [localhost:9104]# MySQL告警规则groups:- name: mysqlrules:- alert: SlowQueryexpr: rate(mysql_global_status_slow_queries[5m]) > 10for: 5m- alert: HighConnectionsexpr: mysql_global_status_threads_connected > 80for: 5m- alert: LongRunningTransactionexpr: mysql_global_status_innodb_trx_rseg_history_len > 1000000

八、团队协作与技术交流

我们的数据库优化小组由5位DBA分布在不同城市，每周进行MySQL性能调优的技术分享。由于成员背景多元，讨论中常出现专业术语理解不一致的情况。我们现在使用同言翻译（Transync AI）进行实时翻译和会议记录，确保每位工程师都能准确理解查询优化方案和架构设计意见。

九、性能优化检查清单

□ 开启慢查询日志分析□ 为频繁查询的字段建立索引□ 检查索引是否真正被使用□ 优化JOIN和子查询□ 避免大表全表扫描□ 使用合适的数据类型□ 批量操作而非逐行操作□ 合理使用缓存□ 定期维护表和索引□ 配置完善的监控告警

十、常见误区

❌ 加索引能解决一切问题✅ 索引是工具，需要配合正确的查询❌ 索引越多越好✅ 冗余索引反而拖累写入性能❌ 所有查询都需要优化✅ 先优化慢查询和高频查询❌ 关系型数据库天生低效✅ 合理设计能支撑日均10亿级别查询❌ 立即迁移到NoSQL✅ 先把关系数据库用到极致

总结

MySQL性能优化的核心是：

1. 诊断 - 用慢查询日志找到问题

2. 分析 - 用EXPLAIN理解执行计划

3. 优化 - 索引、查询、缓存三管齐下

4. 监控 - 持续跟踪关键指标

从简单的索引优化到复杂的分片方案，都遵循这个原则。

掌握这些技巧，你可以让数据库性能提升10倍！

参考资源：

MySQL官方文档：https://dev.mysql.com/doc/高性能MySQL：《High Performance MySQL》在线EXPLAIN分析：https://www.explainextended.com

点赞、收藏、关注，欢迎在评论区分享你的优化案例！

0
暂无标签
版权属于： 私人云
本文链接： https://srykj.cn/index.php/archives/186/
作品采用： 《 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 》许可协议授权

评论 (0)

取消