优化实战：4399员工培训——MySQL性能调优与案例解析

需积分: 10 47 浏览量更新于2024-07-21 收藏 148KB DOC 举报

本篇文档是一份关于MySQL性能优化的实战经验分享，由厦门游家公司（4399.com）针对已有MySQL基础且具备开发经验的工程师群体撰写。文章背景是在高并发、海量数据的互联网环境中，旨在解决实际问题而非应试教育，特别适合在职人员提升技能。主要内容分为以下几个部分： 1. **背景及目标**：文档针对厦门游家公司员工培训，关注的是提高MySQL在实际工作中的性能。目标是帮助工程师理解和应用优化策略，如针对高并发和大数据场景。 2. **Mysql执行优化**：强调数据索引的重要性，包括关系型数据库的Btree索引和其高效查询的特点，以及在某些特定条件下的有序序列可以作为替代。同时，介绍了HASH索引，虽然查询效率高，但不支持复杂的操作。 3. **数据索引的理解**：详细解释了Btree索引（默认类型）和其在内存表中的应用，以及单向有序序列的查找优势。指出树形索引的设计目标是为了支持高效的增删改查。 4. **影响结果集**：探讨了查询结果集的影响因素，如何解读查询结果，并提供了相关案例以说明优化思路。 5. **执行状态分析**：讲解了关注的重点，包括执行状态的监控和分析流程，以及常见案例的解析。 6. **运维优化**：涵盖了存储引擎选择、内存使用、性能与安全性的权衡，以及存储/写入压力的优化策略，如监控体系的构建。 7. **Mysql架构优化**：着重于防止单点风险、系统扩展性、安全性和成本控制，讨论了分布式解决方案，如分库、拆表、反范式设计和主从架构等。 8. **故障转移处理和缓存策略**：介绍了缓存在读写操作中的结合使用，以及如何在系统中实现故障转移。 9. **总结**：文档最后重申了实战和问题解决的核心，以及持续更新的时间线，强化了影响结果集分析和优化实战案例的部分。这篇文档提供了一个实用的MySQL性能优化框架，适合在实际工作中进行参考和应用。

 SQL 的优化，核心就是对结果集的优化，认识索引是增强对结果集的判断，基于

索引的认识，可以在编写 SQL 的时候，对该 SQL 可能的影响结果集有预判，并

做出适当的优化和调整。

 Limit 的影响，需要斟酌对待

 如果索引与查询条件和排序条件完全命中，影响结果集就是 limit 后面的数字

（$start + $end），比如 limit 200,30 影响结果集是 230. 而不是 30.

 如果索引只命中部分查询条件，甚至无命中条件，在无排序条件情况下，会

在索引命中的结果集中遍历到满足所有其他条件为止。比如 select * from

user limit 10; 虽然没用到索引，但是因为不涉及二次筛选和排序，系统直接

返回前 10 条结果，影响结果集依然只有 10 条，就不存在效率影响。

 如果搜索所包含的排序条件没有被索引命中，则系统会遍历是所有索引所命

中的结果，并且排序。例如 Select * from user order by timeline desc limit 10;

如果 timeline 不是索引，影响结果集是全表，就存在需要全表数据排序，这

个效率影响就巨大。再比如 Select * from user where area=’厦门’ order by

timeline desc limit 10; 如果 area 是索引，而 area+timeline 未建立索引，则影响

结果集是所有命中 area=’厦门’的用户，然后在影响结果集内排序。

常见案例及优化思路

 毫秒级优化案例

 某游戏用户进入后显示最新动态， SQL 为 select * from userfeed where

uid=$uid order by timeline desc limit 20; 主键为$uid 。该 SQL 每天执行数百万

次之多，高峰时数据库负载较高。通过 show processlist 显示大量进程处于

Sending data 状态。没有慢查询记录。仔细分析发现，因存在较多高频用户

访问，命中 uid=$uid 的影响结果集通常在几百到几千，在上千条影响结果集

情况下，该 SQL 查询开销通常在 0.01 秒左右。建立 uid+timeline 复合索引，

将排序引入到索引结构中，影响结果集就只有 limit 后面的数字，该 SQL 查

询开销锐减至 0.001 秒，数据库负载骤降。

 Innodb 锁表案例

 某游戏数据库使用了 innodb，innodb 是行级锁，理论上很少存在锁表情况。

出现了一个 SQL 语句(delete from tabname where xid=…)，这个 SQL 非常用

SQL，仅在特定情况下出现，每天出现频繁度不高（一天仅 10 次左右），数

据表容量百万级，但是这个 xid 未建立索引，于是悲惨的事情发生了，当执

行这条 delete 的时候，真正删除的记录非常少，也许一到两条，也许一条都

没有；但是！由于这个 xid 未建立索引，delete 操作时遍历全表记录，全表被

delete 操作锁定，select 操作全部被 locked，由于百万条记录遍历时间较长，

期间大量 select 被阻塞，数据库连接过多崩溃。

这种非高发请求，操作目标很少的 SQL，因未使用索引，连带导致整个数据

库的查询阻塞，需要极大提高警觉。

 实时排名策略优化

 背景：用户提交游戏积分，显示实时排名。

 原方案：

 提交积分是插入记录，略，

 select count(*) from jifen where gameid=$gameid and fenshu>$fenshu

 问题与挑战

 即便索引是 gameid+fenshu 复合索引，涉及 count 操作，当分数较低时，

影响结果集巨大，查询效率缓慢，高峰期会导致连接过多。

 优化思路

 减少影响结果集，又要取得实时数据，单纯从 SQL 上考虑，不太有方法。

 将游戏积分预定义分成数个积分断点，然后分成积分区间，原始状态，

每个区间设置一个统计数字项，初始为 0。

 每次积分提交时，先确定该分数属于哪两个区间之间，这个操作非常简

单，因为区间是预定义的，而且数量很少，只需遍历即可，找到最该分

数符合的区间，该区间的统计数字项（独立字段，可用内存处理，异步

回写数据库或文件）+1。记录该区间上边界数字为$duandian。

 SQL: select count(*) from jifen where gameid=$gameid and fenshu>$fenshu

and fenshu<$duandian，如果处于第一区间，则无需$duandian，这样因为

第一区间本身也是最好的成绩，影响结果集不会很多。通过该 SQL 获得

其在该区间的名次。

 获取前面区间的总数总和。（该数字是直接从上述提到的区间统计数字

获取，不需要进行 count 操作）将区间内名次+前区间的统计数字和，获

得总名次。

 该方法关键在于，积分区间需要合理定义，保证积分提交成绩能平均散

落在不同区间。

 如涉及较多其他条件，如日排行，总排行，以及其他独立用户去重等，

请按照影响结果集思路自行发挥。

 Redis 方案

 Redis 数据结构包括 String,list,dict 和 Zset 四种，在本案例中是非常

好的替代数据库的方案，本文档只做简介，不做额外扩展。

 String 哈希索引，key-value 结构，主键查询效率极高，不支持排序，

比较查询。

 List 队列结构，在数据异步写入处理中可以替代 memcache。

 Dict 数组结构，存储结构化，序列化内容，可以针对数组中的特定列进

行操作。

 Zset 有序数组结构，分两个子结构，第一是多层树形的存储结构，第二

是每个树形节点的计数器，这样类似于前面的分段方式，可以理解为多

层分段方式，所以查询效率更高，缺点是更新效率有所增加。

 论坛翻页优化

 背景，常见论坛帖子页 SQL: select * from post where tagid=$tagid order by

lastpost limit $start, $end 翻页。索引为 tagid+lastpost 复合索引

 挑战，超级热帖，几万回帖，用户频频翻到末页，limit 25770,30 一个操作下

来，影响结果集巨大(25770+30)，查询缓慢。

 解决方法：

 只涉及上下翻页情况

 每次查询的时候将该页查询结果中最大的 $lastpost 和最小的分别记

剩余25页未读，继续阅读

kawaiiboi

粉丝: 0

优化实战：4399员工培训——MySQL性能调优与案例解析

MySQL性能优化实践与架构优化策略

MySQL性能优化实践与进阶

MySQL性能优化实践与架构升级

MySQL性能优化实践.docx

大型项目MySQL性能优化实践

MySQL性能优化实践与策略

大型移动项目MySQL性能优化实践

大型项目MySQL性能优化实践与策略

MySQL性能优化实践与索引深度解析

深入浅出MySQL性能优化实践指南

最新资源