Mysql 性能优化教程.docx

1、Mysql 性能优化教程Mysql 性能优化教程目录目录 1背景及目标 2Mysql 执行优化 2认识数据索引 2为什么使用数据索引能提高效率 2如何理解数据索引的结构 2优化实战范例 3认识影响结果集 4影响结果集的获取 4影响结果集的解读 4常见案例及优化思路 5理解执行状态 6常见关注重点 6执行状态清单 7分析流程 8常见案例解析 10总结 11Mysql 运维优化 13存储引擎类型 13内存使用考量 13性能与安全性考量 13存储压力优化 14运维监控体系 14Mysql 架构优化 16架构优化目标 16防止单点隐患 16方便系统扩容 16安全可控，成本可控 16分布式方案 17分库

2、&拆表方案 17主从架构 19故障转移处理 20缓存方案 20缓存结合数据库的读取 20缓存结合数据库的写入 20背景及目标 厦门游家公司（）用于员工培训和分享。 针对用户群为已经使用过mysql环境，并有一定开发经验的工程师 针对高并发，海量数据的互联网环境。 本文语言为口语，非学术标准用语。 以实战和解决具体问题为主要目标，非应试，非常规教育。友情提醒，在校生学习本教程可能对成绩提高有害无益。 非技术挑战，非高端架构师培训，请高手自动忽略。 本文档在2011年7月7日更新，加强了影响结果集分析的内容并增补优化实战案例若干。Mysql 执行优化认识数据索引为什么使用数据索引能提高效率 数据索

3、引的存储是有序的 在有序的情况下，通过索引查询一个数据是无需遍历索引记录的 数据索引的查询效率趋近于二分法查询效率，趋近于 log2(N)。 极端情况下（更新请求少，更新实时要求低，查询请求频繁），建立单向有序序列可替代数据索引。如何理解数据索引的结构 数据索引通常默认采用btree索引，（内存表也使用了hash索引）。 单向有序排序序列是查找效率最高的（二分查找，或者说折半查找），使用树形索引的目的是为了达到快速的更新和增删操作。 在极端情况下（比如数据查询需求量非常大，而数据更新需求极少，实时性要求不高，数据规模有限），直接使用单一排序序列，折半查找速度最快。 在进行索引分析和SQL优化时

4、，可以将数据索引字段想象为单一有序序列，并以此作为分析的基础。涉及到复合索引情况，复合索引按照索引顺序拼凑成一个字段，想象为单一有序序列，并以此作为分析的基础。 一条数据查询只能使用一个索引，索引可以是多个字段合并的复合索引。但是一条数据查询不能使用多个索引。优化实战范例 实战范例1： ip地址反查 资源： Ip地址对应表，源数据格式为 startip, endip, area 源数据条数为 10万条左右，呈很大的分散性 目标： 需要通过任意ip查询该ip所属地区性能要求达到每秒1000次以上的查询效率 挑战： 如使用 between startip and endip 这样的条件数据库操作，

5、因为涉及两个字段的between and, 无法有效使用索引。如果每次查询请求需要遍历10万条记录，根本不行。 方法： 一次性排序（只在数据准备中进行，数据可存储在内存序列） 折半查找（每次请求以折半查找方式进行） 实战范例2：目标：查找与访问者同一地区的异性，按照最后登录时间逆序 挑战：高访问量社区的高频查询，如何优化。 查询SQL: select * from user where area=$area and sex=$sex order by lastlogin desc limit 0,30; 建立复合索引并不难， area+sex+lastlogin 三个字段的复合索引,如何理解？

6、 解读：首先，忘掉btree，将索引字段理解为一个排序序列。另外，牢记数据查询只能使用一个索引，每个字段建立独立索引的情况下，也只能有一条索引被使用！ 如果只使用area会怎样？搜索会把符合area的结果全部找出来，然后在这里面遍历，选择命中sex的并排序。 遍历所有 area=$area数据！如果使用了area+sex，略好，仍然要遍历所有area=$area and sex=$sex数据，然后在这个基础上排序！ Area+sex+lastlogin复合索引时（切记lastlogin在最后），该索引基于area+sex+lastlogin 三个字段合并的结果排序，该列表可以想象如下。 广州女

7、$时间1 广州女$时间2 广州女$时间3 广州男. 深圳女.数据库很容易命中到 area+sex的边界，并且基于下边界向上追溯30条记录，搞定！在索引中迅速命中所有结果，无需二次遍历！认识影响结果集影响结果集的获取 通过Explain 分析SQL，查看 rows 列内容 通过慢查询日志的Rows_examined: 后面的数字 影响结果集数字是查询优化的重要中间数字，工程师在开发和调试过程中，应随时关注这一数字。影响结果集的解读 查询条件与索引的关系决定影响结果集。 影响结果集不是输出结果数，不是查询返回的记录数，而是索引所命中的结果数。 范例 select * from user where

8、 area=厦门 and sex=女 假设 索引为 area User表中 area=厦门的有 125000条，而搜索返回结果为60233条。 影响结果集是125000条，索引先命中125000条厦门用户，再遍历以sex=女进行筛选操作，得到60233条结果。 如果该SQL 增加 limit 0,30的后缀。查询时，先命中 area=厦门，然后依顺序执行 sex=女 筛选操作，直到满足可以返回30条为止，所涉及记录数未知。但不会遍历125000条记录。 但是如果SQL中涉及了排序操作，比如 order by lastlogin desc 再有limit 0,30时，排序需要遍历所有area=厦

9、门 的记录，而不是满足即止。 影响结果集越趋近于实际输出或操作的目标结果集，索引效率越高。 影响结果集与查询开销的关系可以理解为线性相关。减少一半影响结果集，即可提升一倍查询效率！当一条搜索query可以符合多个索引时，选择影响结果集最少的索引。 Limit 的影响，需要斟酌对待 如果索引与查询条件和排序条件完全命中，影响结果集就是limit后面的数字（$start + $end），比如 limit 200,30 影响结果集是230. 而不是30. 如果索引只命中部分查询条件，甚至无命中条件，在无排序条件情况下，会在索引命中的结果集 中遍历到满足所有其他条件为止。比如 select * fro

10、m user limit 10; 影响结果集是全表，因为没用到索引，但是因为不涉及在影响结果集内二次筛选和排序，系统直接返回前10条结果，就不存在效率影响。 如果搜索所包含的排序条件没有被索引命中，则系统会遍历是所有索引所命中的结果，并且排序。例如 Select * from user order by timeline desc limit 10; 如果timeline不是索引，影响结果集是全表，就存在需要全表数据排序，这个效率影响就巨大。再比如 Select * from user where area=厦门 order by timeline desc limit 10; 如果area是

11、索引，而area+timeline未建立索引，则影响结果集是所有命中 area=厦门的用户，然后在影响结果集内排序。 常见案例及优化思路 微秒级优化案例 某游戏用户进入后显示最新动态，SQL为 select * from userfeed where uid=$uid order by timeline desc limit 20; 主键为$uid 。 该SQL每天执行数百万次之多，高峰时数据库负载较高。 通过 show processlist 显示大量进程处于Sending data状态。没有慢查询记录。 仔细分析发现，因存在较多高频用户访问，命中 uid=$uid的影响结果集通常在几百到几

12、千，在上千条影响结果集情况下，该SQL查询开销通常在0.01秒左右。 建立uid+timeline 复合索引，将排序引入到索引结构中，影响结果集就只有limit 后面的数字，该SQL查询开销锐减至0.001秒，数据库负载骤降。 Innodb锁表案例 某游戏数据库使用了innodb，innodb是行级锁，理论上很少存在锁表情况。出现了一个SQL语句(delete from tabname where xid=)，这个SQL非常用SQL，仅在特定情况下出现，每天出现频繁度不高（一天仅10次左右），数据表容量百万级，但是这个xid未建立索引，于是悲惨的事情发生了，当执行这条delete 的时候，真正

13、删除的记录非常少，也许一到两条，也许一条都没有；但是！由于这个xid未建立索引，delete操作时遍历全表记录，全表被delete操作锁定，select操作全部被locked，由于百万条记录遍历时间较长，期间大量select被阻塞，数据库连接过多崩溃。这种非高发请求，操作目标很少的SQL，因未使用索引，连带导致整个数据库的查询阻塞，需要极大提高警觉。 实时排名策略优化 背景： 用户提交游戏积分，显示实时排名。 原方案： 提交积分是插入记录，略， select count(*) from jifen where gameid=$gameid and fenshu$fenshu 问题与挑战 即便索

14、引是 gameid+fenshu 复合索引，涉及count操作，当分数较低时，影响结果集巨大，查询效率缓慢，高峰期会导致连接过多。 优化思路 减少影响结果集，又要取得实时数据，单纯从SQL上考虑，不太有方法。 将游戏积分预定义分成数个积分断点，然后分成积分区间，原始状态，每个区间设置一个统计数字项，初始为0。 每次积分提交时，先确定该分数属于哪两个区间之间，这个操作非常简单，因为区间是预定义的，而且数量很少，只需遍历即可，找到最该分数符合的区间， 该区间的统计数字项（独立字段，可用内存处理，异步回写数据库或文件）+1。 记录该区间上边界数字为$duandian。 SQL: select cou

15、nt(*) from jifen where gameid=$gameid and fenshu$fenshu and fenshu$duandian，如果处于第一区间，则无需$duandian，这样因为第一区间本身也是最好的成绩，影响结果集不会很多。 通过该SQL获得其在该区间的名次。 获取前面区间的总数总和。（该数字是直接从上述提到的区间统计数字获取，不需要进行count操作）将区间内名次+前区间的统计数字和，获得总名次。 该方法关键在于，积分区间需要合理定义，保证积分提交成绩能平均散落在不同区间。 如涉及较多其他条件，如日排行，总排行，以及其他独立用户去重等，请按照影响结果集思路自行发挥。 论坛翻页优化 背景，常见论坛帖子页 SQL: select * from post where tagid=$tagid order by lastpost limit $start, $end 翻页 。索引为 tagid+lastpost