优化从何入手数据库处理一个请求会经过客户端连接、查询缓存、SQL 解析、查询优化、存储引擎、磁盘 I/O 等多个环节每个环节都可能成为瓶颈。优化维度全景图维度常见手段硬件使用 SSD、RAID10 阵列、增加内存连接调整max_connections、减少应用端连接数配置优化innodb_buffer_pool_size、日志刷新策略缓存引入 Redis/Memcached 做前置缓存架构主从复制、读写分离、分库分表SQL 索引慢查询分析、执行计划优化、索引设计二、硬件与基础配置优化2.1 硬件建议磁盘使用 RAID10兼顾 RAID0 的性能 RAID1 的可靠性内存越大越好InnoDB 会将其用作缓存CPU高频优于多核多数场景2.2 关键 MySQL 配置my.cnf123456789101112131415161718192021# InnoDB 缓冲池大小专用服务器建议设置物理内存的 70%~80%innodb_buffer_pool_size 4G# 连接数max_connections 500max_user_connections 50# 日志刷盘策略非金融级可设为 2 提升写入性能innodb_flush_log_at_trx_commit 2sync_binlog 1# 独立表空间innodb_file_per_table 1# 慢查询日志slow_query_log 1long_query_time 0.5slow_query_log_file /var/lib/mysql/mysql-slow.log# 默认存储引擎default-storage-engine InnoDB说明innodb_flush_log_at_trx_commit 2 表示每秒刷盘性能好但可能丢失 1 秒数据1 最安全但最慢三、架构优化当单库无法承载负载时需要从架构层面突破历史演进3.1 主从复制与读写分离异步复制主库提交后立即返回不等待从库。延迟可能较大。半同步复制至少一个从库接收 binlog 并写入 relay log 后才返回。可减少数据丢失风险。启用半同步需安装插件1234-- 主库安装INSTALL PLUGIN rpl_semi_sync_master SONAMEsemisync_master.so;-- 从库安装INSTALL PLUGIN rpl_semi_sync_slave SONAMEsemisync_slave.so;GTID 复制全局事务 ID简化主从切换和故障恢复。开启方式12gtid_mode ONenforce_gtid_consistency ON读写分离借助中间件如 ProxySQL、HAProxy将写请求发往主库读请求均衡分发到从库。3.2 高可用方案对比方案特点MHA / MMM成熟需要多个节点切换时间秒级MGRMySQL Group Replication官方原生支持多主强一致性Galera Cluster多主同步无延迟但写扩展有限Keepalived 主从简单但需要配合虚拟 IP 和脚本3.3 分库分表当单表数据量达到千万级甚至亿级分库分表是必然选择。垂直分库按业务模块拆分如订单库、用户库降低单库压力。垂直分表将宽表切分成主表 扩展表如把text、blob字段独立。水平分表按某个键如用户 ID、时间将数据分散到多张结构相同的表中。单库垂直分库垂直分表逆规范化适当增加冗余字段减少 JOIN 查询提高读性能。适合读多写少的场景。四、SQL 优化实战4.1 打开慢查询日志12SET GLOBAL slow_query_log ON;SET GLOBAL long_query_time 0.5; -- 0.5 秒使用mysqldumpslow分析1mysqldumpslow -s t -t 20 /var/lib/mysql/mysql-slow.log4.2 使用 profiling 查询单条 SQL 耗时1234SET profiling 1;SELECT * FROM user WHERE id 1;SHOW PROFILES;SHOW PROFILE FOR QUERY 1;4.3 EXPLAIN 执行计划分析EXPLAIN是 SQL 优化的核心工具。示例123EXPLAIN SELECT t.tname, c.cname, tc.phoneFROM teacher t, course c, teacher_contact tcWHERE t.tid c.tid AND t.tcid tc.tcid AND (c.cid 2 OR tc.tcid 3);关键列解读列名含义id查询序号越大越先执行相同则顺序执行select_typeSIMPLE简单查询、PRIMARY主查询、SUBQUERY子查询、DERIVED派生表、UNION等type连接类型性能从高到低systemconsteq_refrefrangeindexALLpossible_keys可能用到的索引key实际使用的索引key_len使用的索引长度字节帮助判断联合索引中使用了哪些列rows预估扫描的行数Extra额外信息如Using index覆盖索引、Using filesort文件排序需要优化、Using temporary使用临时表type 优化目标至少达到range争取ref或const。小技巧key_len计算示例utf8mb4 每个字符 4 字节若phone varchar(11)且utf8mb4索引长度 ≈ 11×4 2 46 字节。五、索引与表结构优化5.1 索引使用原则在 WHERE、JOIN、ORDER BY、GROUP BY 涉及的列上建立索引区分度高的列优先联合索引遵循最左前缀原则避免索引失效LIKE %xxx、函数操作、隐式类型转换、OR 条件等5.2 表结构优化实例垂直拆分适用于表过宽或含大字段12345- 原表CREATE TABLE article (id INT, title VARCHAR(200), content TEXT, ...);-- 拆分后CREATE article_base (id INT, title VARCHAR(200));CREATE article_content (id INT, content TEXT);水平拆分按时间或 ID 范围1-- 按年份分区或使用应用层路由到 order_2023、order_2024 等六、锁机制与死锁分析6.1 InnoDB 锁类型行锁锁定单行记录Record Lock间隙锁锁定范围Gap Lock防止幻读Next-Key Lock 行锁 间隙锁RR 隔离级别下默认使用6.2 死锁的四个必要条件两个或以上事务锁资源不兼容排他锁互斥每个事务持有锁并申请新锁循环等待示例死锁演示事务 A事务 BBEGIN SELECT * FROM user WHERE id3 FOR UPDATE;BEGIN DELETE FROM user WHERE id4;UPDATE user SET namemimi WHERE id4;等待 B 释放等待 A 释放 id3 的锁→ 死锁6.3 查看与处理死锁123456789-- 查看当前锁等待的信息SHOW STATUS LIKEinnodb_row_lock%;-- 查看正在运行的事务SELECT * FROM information_schema.innodb_trx;-- 查看锁详细信息MySQL 8.0SELECT * FROM performance_schema.data_locks;SELECT * FROM sys.innodb_lock_waits;发现阻塞线程后可使用KILL thread_id终止。6.4 避免死锁的建议多表操作按固定顺序访问将大事务拆分为多个小事务降低隔离级别如 RR → RC可减少间隙锁为表添加合理索引避免表锁升级七、常用监控命令速查12345678910111213141516-- 查看当前连接SHOW PROCESSLIST;SELECT * FROM information_schema.PROCESSLIST;-- 查看各 SQL 执行次数SHOW GLOBAL STATUS LIKECom_%;-- 查看服务端变量SHOW VARIABLES LIKE%connect%;SHOW VARIABLES LIKEslow_query_log;-- 查看表锁情况SHOW GLOBAL STATUS LIKEtable_locks%;-- 查看打开文件数SHOW GLOBAL STATUS LIKEopen_files;八、总结调优路线图阶段核心动作1. 硬件层升级 SSD、增加内存、使用 RAID102. 配置层调整innodb_buffer_pool_size、连接数、日志刷盘策略3. 架构层读写分离、主从半同步、分库分表、引入缓存4. SQL 层开启慢查询日志、分析 EXPLAIN、优化索引、避免全表扫描5. 锁层面减少长事务、合理设计索引、降低隔离级别若业务允许最后记住一条真理没有任何一次调优是“无脑加配置”能解决的必须结合业务特点与监控数据循序渐进。本文内容整理自《MySQL性能优化分析》实战笔记希望对你有所帮助。如果觉得有用欢迎点赞、收藏、转发关注我获取更多数据库与后端技术干货。分类: Java生态体系, MySQL数据库标签: java, MySQL免责声明本内容来自平台创作者博客园系信息发布平台仅提供信息存储空间服务。好文要顶 关注我 收藏该文 微信分享k8s-Mango粉丝 - 0 关注 - 31加关注10« 上一篇 MySQL数据同步一篇就够了» 下一篇 一条 INSERT一条 UPDATE同时执行会阻塞吗——MySQL RR 隔离级别锁机制全解析posted on 2026-04-26 12:34 k8s-Mango 阅读(374) 评论(3) 收藏 举报刷新页面返回顶部登录后才能查看或发表评论立即 登录 或者 逛逛 博客园首页【推荐】 凌霞 618 年中大促Halo 与 1Panel 产品全线半价叠加满减【推荐】HarmonyOS 6.1.0 创新特性“悬浮页签沉浸光感”精品文章专题【推荐】科研领域的连接者艾思科蓝一站式科研学术服务数字化平台编辑推荐[经验分享] 我的第一个 Skill让 Agent 在对话中成长自进化机制的五层实现代码之外一个技术人的职场困境与自我和解贩卖焦虑的时代我终于接住了真实的焦虑工良吐槽篇万字长文细说 AI 落地之笑谈导航博客园首页新随笔联系订阅管理2026年6月日一二三四五六311234567891011121314151617181920212223242526272829301234567891011公告昵称 k8s-Mango园龄 12年4个月粉丝 0关注 31加关注搜索常用链接我的随笔我的评论我的参与最新评论我的标签我的标签java(52)MySQL(20)分布式(11)redis(7)docker(6)IT软件运维(5)Maven(4)Linux(4)nexus(3)kafka(3)更多积分与排名积分 - 25375排名 - 66391随笔分类Docker相关(6)Java生态体系(58)MySQL数据库(18)Zookeeper(5)大数据相关(5)随笔档案2026年6月(7)2026年5月(12)2026年4月(9)2025年8月(5)2025年6月(2)2024年12月(1)2024年7月(1)2024年6月(1)2023年10月(1)2022年10月(3)2021年6月(1)2021年3月(7)2021年2月(4)2021年1月(1)2020年11月(1)更多阅读排行榜1. 使用Docker-Compose部署PostgreSQL(4146)2. 通过Dockerfile 将jdk 制作为基础镜像(2132)3. Nexus3 docker hosted私有仓库搭建指南(1907)4. Centos7 环境下安装filebeat(1737)5. MySQL binlog底层主从同步原理(1296)评论排行榜1. MySQL 性能调优完全指南从硬件到 SQL一篇吃透(3)2. 使用Docker-Compose部署PostgreSQL(2)3. Centos7 环境下安装filebeat(1)推荐排行榜1. 探秘InnoDB搞懂它的内存、线程、磁盘与日志刷盘策略(1)2. MySQL 性能调优完全指南从硬件到 SQL一篇吃透(1)3. Docker-Compose安装(1)博客园 © 2004-2026