Mysql 性能优化教程.docx

Mysql 性能优化教程.docx

《Mysql 性能优化教程.docx》由会员分享，可在线阅读，更多相关《Mysql 性能优化教程.docx（26页珍藏版）》请在冰豆网上搜索。

Mysql性能优化教程

Mysql性能优化教程

目录

目录1

背景及目标2

Mysql执行优化2

认识数据索引2

为什么使用数据索引能提高效率2

如何理解数据索引的结构2

优化实战范例3

认识影响结果集4

影响结果集的获取4

影响结果集的解读4

常见案例及优化思路5

理解执行状态6

常见关注重点6

执行状态清单7

分析流程8

常见案例解析10

总结11

Mysql运维优化13

存储引擎类型13

内存使用考量13

性能与安全性考量13

存储压力优化14

运维监控体系14

Mysql架构优化16

架构优化目标16

防止单点隐患16

方便系统扩容16

安全可控，成本可控16

分布式方案17

分库&拆表方案17

主从架构19

故障转移处理20

缓存方案20

缓存结合数据库的读取20

缓存结合数据库的写入20

背景及目标

●厦门游家公司（）用于员工培训和分享。

●针对用户群为已经使用过mysql环境，并有一定开发经验的工程师

●针对高并发，海量数据的互联网环境。

●本文语言为口语，非学术标准用语。

●以实战和解决具体问题为主要目标，非应试，非常规教育。

友情提醒，在校生学习本教程可能对成绩提高有害无益。

●非技术挑战，非高端架构师培训，请高手自动忽略。

●本文档在2011年7月7日更新，加强了影响结果集分析的内容并增补优化实战案例若干。

Mysql执行优化

认识数据索引

为什么使用数据索引能提高效率

⏹数据索引的存储是有序的

⏹在有序的情况下，通过索引查询一个数据是无需遍历索引记录的

⏹数据索引的查询效率趋近于二分法查询效率，趋近于log2（N）。

⏹极端情况下（更新请求少，更新实时要求低，查询请求频繁），建立单向有序序列可替代数据索引。

如何理解数据索引的结构

⏹数据索引通常默认采用btree索引，（内存表也使用了hash索引）。

⏹单向有序排序序列是查找效率最高的（二分查找，或者说折半查找），使用树形索引的目的是为了达到快速的更新和增删操作。

⏹在极端情况下（比如数据查询需求量非常大，而数据更新需求极少，实时性要求不高，数据规模有限），直接使用单一排序序列，折半查找速度最快。

⏹在进行索引分析和SQL优化时，可以将数据索引字段想象为单一有序序列，并以此作为分析的基础。

涉及到复合索引情况，复合索引按照索引顺序拼凑成一个字段，想象为单一有序序列，并以此作为分析的基础。

⏹一条数据查询只能使用一个索引，索引可以是多个字段合并的复合索引。

但是一条数据查询不能使用多个索引。

优化实战范例

●实战范例1：

ip地址反查

⏹资源：

Ip地址对应表，源数据格式为startip,endip,area

源数据条数为10万条左右，呈很大的分散性

⏹目标：

需要通过任意ip查询该ip所属地区

性能要求达到每秒1000次以上的查询效率

⏹挑战：

如使用betweenstartipandendip这样的条件数据库操作，因为涉及两个字段的betweenand,无法有效使用索引。

如果每次查询请求需要遍历10万条记录，根本不行。

⏹方法：

一次性排序（只在数据准备中进行，数据可存储在内存序列）

折半查找（每次请求以折半查找方式进行）

●实战范例2：

目标：

查找与访问者同一地区的异性，按照最后登录时间逆序

⏹挑战：

高访问量社区的高频查询，如何优化。

查询SQL:

select*fromuserwherearea=’$area’andsex=’$sex’orderbylastlogindesclimit0,30;

建立复合索引并不难，area+sex+lastlogin三个字段的复合索引,如何理解？

⏹解读：

首先，忘掉btree，将索引字段理解为一个排序序列。

另外，牢记数据查询只能使用一个索引，每个字段建立独立索引的情况下，也只能有一条索引被使用！

如果只使用area会怎样？

搜索会把符合area的结果全部找出来，然后在这里面遍历，选择命中sex的并排序。

遍历所有area=’$area’数据！

如果使用了area+sex，略好，仍然要遍历所有area=’$area’andsex=’$sex’数据，然后在这个基础上排序！

！

Area+sex+lastlogin复合索引时（切记lastlogin在最后），该索引基于area+sex+lastlogin三个字段合并的结果排序，该列表可以想象如下。

广州女$时间1

广州女$时间2

广州女$时间3

…

广州男

….

深圳女

….

数据库很容易命中到area+sex的边界，并且基于下边界向上追溯30条记录，搞定！

在索引中迅速命中所有结果，无需二次遍历！

认识影响结果集

影响结果集的获取

⏹通过Explain分析SQL，查看rows列内容

⏹通过慢查询日志的Rows_examined:

后面的数字

⏹影响结果集数字是查询优化的重要中间数字，工程师在开发和调试过程中，应随时关注这一数字。

影响结果集的解读

⏹查询条件与索引的关系决定影响结果集。

◆影响结果集不是输出结果数，不是查询返回的记录数，而是索引所命中的结果数。

◆范例select*fromuserwherearea=’厦门’andsex=’女’

●假设索引为area

●User表中area=’厦门’的有125000条，而搜索返回结果为60233条。

●影响结果集是125000条，索引先命中125000条厦门用户，再遍历以sex=’女’进行筛选操作，得到60233条结果。

●如果该SQL增加limit0,30的后缀。

查询时，先命中area=’厦门’，然后依顺序执行sex=’女’筛选操作，直到满足可以返回30条为止，所涉及记录数未知。

但不会遍历125000条记录。

●但是如果SQL中涉及了排序操作，比如orderbylastlogindesc再有limit0,30时，排序需要遍历所有area=’厦门’的记录，而不是满足即止。

⏹影响结果集越趋近于实际输出或操作的目标结果集，索引效率越高。

⏹影响结果集与查询开销的关系可以理解为线性相关。

减少一半影响结果集，即可提升一倍查询效率！

当一条搜索query可以符合多个索引时，选择影响结果集最少的索引。

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

◆如果索引与查询条件和排序条件完全命中，影响结果集就是limit后面的数字（$start+$end），比如limit200,30影响结果集是230.而不是30.

◆如果索引只命中部分查询条件，甚至无命中条件，在无排序条件情况下，会在索引命中的结果集中遍历到满足所有其他条件为止。

比如select*fromuserlimit10;影响结果集是全表，因为没用到索引，但是因为不涉及在影响结果集内二次筛选和排序，系统直接返回前10条结果，就不存在效率影响。

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

例如Select*fromuserorderbytimelinedesclimit10;如果timeline不是索引，影响结果集是全表，就存在需要全表数据排序，这个效率影响就巨大。

再比如Select*fromuserwherearea=’厦门’orderbytimelinedesclimit10;如果area是索引，而area+timeline未建立索引，则影响结果集是所有命中area=’厦门’的用户，然后在影响结果集内排序。

常见案例及优化思路

⏹微秒级优化案例

◆某游戏用户进入后显示最新动态，SQL为select*fromuserfeedwhereuid=$uidorderbytimelinedesclimit20;主键为$uid。

该SQL每天执行数百万次之多，高峰时数据库负载较高。

通过showprocesslist显示大量进程处于Sendingdata状态。

没有慢查询记录。

仔细分析发现，因存在较多高频用户访问，命中uid=$uid的影响结果集通常在几百到几千，在上千条影响结果集情况下，该SQL查询开销通常在0.01秒左右。

建立uid+timeline复合索引，将排序引入到索引结构中，影响结果集就只有limit后面的数字，该SQL查询开销锐减至0.001秒，数据库负载骤降。

⏹Innodb锁表案例

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

出现了一个SQL语句（deletefromtabnamewherexid=…），这个SQL非常用SQL，仅在特定情况下出现，每天出现频繁度不高（一天仅10次左右），数据表容量百万级，但是这个xid未建立索引，于是悲惨的事情发生了，当执行这条delete的时候，真正删除的记录非常少，也许一到两条，也许一条都没有；但是！

由于这个xid未建立索引，delete操作时遍历全表记录，全表被delete操作锁定，select操作全部被locked，由于百万条记录遍历时间较长，期间大量select被阻塞，数据库连接过多崩溃。

这种非高发请求，操作目标很少的SQL，因未使用索引，连带导致整个数据库的查询阻塞，需要极大提高警觉。

⏹实时排名策略优化

◆背景：

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

◆原方案：

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

●selectcount（*）fromjifenwheregameid=$gameidandfenshu>$fenshu

◆问题与挑战

●即便索引是gameid+fenshu复合索引，涉及count操作，当分数较低时，影响结果集巨大，查询效率缓慢，高峰期会导致连接过多。

◆优化思路

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

●将游戏积分预定义分成数个积分断点，然后分成积分区间，原始状态，每个区间设置一个统计数字项，初始为0。

●每次积分提交时，先确定该分数属于哪两个区间之间，这个操作非常简单，因为区间是预定义的，而且数量很少，只需遍历即可，找到最该分数符合的区间，该区间的统计数字项（独立字段，可用内存处理，异步回写数据库或文件）+1。

记录该区间上边界数字为$duandian。

●SQL:

selectcount（*）fromjifenwheregameid=$gameidandfenshu>$fenshuandfenshu<$duandian，如果处于第一区间，则无需$duandian，这样因为第一区间本身也是最好的成绩，影响结果集不会很多。

通过该SQL获得其在该区间的名次。

●获取前面区间的总数总和。

（该数字是直接从上述提到的区间统计数字获取，不需要进行count操作）将区间内名次+前区间的统计数字和，获得总名次。

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

●如涉及较多其他条件，如日排行，总排行，以及其他独立用户去重等，请按照影响结果集思路自行发挥。

⏹论坛翻页优化

◆背景，常见论坛帖子页SQL:

select*frompostwheretagid=$tagidorderbylastpostlimit$start,$end翻页。

索引为tagid+lastpost