Mysql性能优化高级工程师.docx
- 文档编号:26702258
- 上传时间:2023-06-21
- 格式:DOCX
- 页数:38
- 大小:40.79KB
Mysql性能优化高级工程师.docx
《Mysql性能优化高级工程师.docx》由会员分享,可在线阅读,更多相关《Mysql性能优化高级工程师.docx(38页珍藏版)》请在冰豆网上搜索。
Mysql性能优化高级工程师
Mysql性能优化教程(php高级工程师)
目录
目录1
背景及目标2
Mysql执行优化2
认识数据索引2
为什么使用数据索引能提高效率2
如何理解数据索引的结构2
优化实战范例3
认识影响结果集4
影响结果集的获取4
影响结果集的解读4
常见案例及优化思路5
理解执行状态7
常见关注重点7
执行状态分析8
分析流程9
常见案例解析11
总结12
Mysql运维优化14
存储引擎类型14
内存使用考量14
性能与安全性考量14
存储/写入压力优化15
运维监控体系15
Mysql架构优化17
架构优化目标17
防止单点隐患17
方便系统扩容17
安全可控,成本可控17
分布式方案18
分库&拆表方案18
反范式设计(冗余结构设计)20
主从架构21
故障转移处理22
缓存方案22
缓存结合数据库的读取22
缓存结合数据库的写入23
总结24
背景和目标
●厦门游家公司()用于员工培训和分享。
●针对用户群为已经使用过mysql环境,并有一定开发经验的工程师
●针对高并发,海量数据的互联网环境。
●本文语言为口语,非学术标准用语。
●以实战和解决具体问题为主要目标,非应试,非常规教育。
友情提醒,在校生学习本教程可能对成绩提高有害无益。
●非技术挑战,非高端架构师培训,请高手自动忽略。
●本文档在2011年7月-12月持续更新,加强了影响结果集分析的内容并增补优化实战案例若干。
Mysql的执行优化
认识数据索引
为什么使用数据索引能提高效率
⏹关系型数据库的数据索引(Btree及常见索引结构)的存储是有序的。
⏹在有序的情况下,通过索引查询一个数据是无需遍历索引记录的
⏹关系型数据库数据索引的查询效率趋近于二分法查询效率,趋近于log2(N)。
⏹极端情况下(更新请求少,更新实时要求低,查询请求频繁),建立单向有序序列可替代数据索引。
⏹HASH索引的查询效率是寻址操作,趋近于1次查询,比有序索引查询效率更高,但是不支持比对查询,区间查询,排序等操作,仅支持key-value类型查询。
不是本文重点。
如何理解数据索引的结构
⏹数据索引通常默认采用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条为止,所涉及记录数未知。
除非满足条件的结果不足30条,否则不会遍历125000条记录。
●但是如果SQL中涉及了排序操作,比如orderbylastlogindesc再有limit0,30时,排序需要遍历所有area=’厦门’的记录,而不是满足即止。
⏹影响结果集越趋近于实际输出或操作的目标结果集,索引效率越高。
⏹影响结果集与查询开销的关系可以理解为线性相关。
减少一半影响结果集,即可提升一倍查询效率!
当一条搜索query可以符合多个索引时,选择影响结果集最少的索引。
⏹SQL的优化,核心就是对结果集的优化,认识索引是增强对结果集的判断,基于索引的认识,可以在编写SQL的时候,对该SQL可能的影响结果集有预判,并做出适当的优化和调整。
⏹Limit的影响,需要斟酌对待
◆如果索引与查询条件和排序条件完全命中,影响结果集就是limit后面的数字($start+$end),比如limit200,30影响结果集是230.而不是30.
◆如果索引只命中部分查询条件,甚至无命中条件,在无排序条件情况下,会在索引命中的结果集中遍历到满足所有其他条件为止。
比如select*fromuserlimit10;虽然没用到索引,但是因为不涉及二次筛选和排序,系统直接返回前10条结果,影响结果集依然只有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操作)将区间内名次+前区间的统计数字和,获得总名次。
●该方法关键在于,积分区间需要合理定义,保证积分提交成绩能平均散落在不同区间。
●如涉及较多其他条件,如日排行,总排行,以及其他独立用户去重等,请按照影响结果集思路自行发挥。
◆Redis方案
●Redis数据结构包括String,list,dict和Zset四种,在本案例中是非常好的替代数据库的方案,本文档只做简介,不做额外扩展。
●String哈希索引,key-value结构,主键查询效率极高,不支持排序,比较查询。
●List队列结构,在数据异步写入处理中可以替代memcache。
●Dict数组结构,存储结构化,序列化内容,可以针对数组中的特定列进行操作。
●Zset有序数组结构,分两个子结构,第一是多层树形的存储结构,第二是每个树形节点的计数器,这样类似于前面的分段方式,可以理解为多层分段方式,所以查询效率更高,缺点是更新效率有所增加。
⏹论坛翻页优化
◆背景,常见论坛帖子页SQL:
select*frompostwheretagid=$tagidorderbylastpostlimit$start,$end翻页。
索引为tagid+lastpost复合索引
◆挑战,超级热帖,几万回帖,用户频频翻到末页,limit25770,30一个操作下来,影响结果集巨大(25770+30),查询缓慢。
◆解决方法:
●只涉及上下翻页情况
⏹每次查询的时候将该页查询结果中最大的$lastpost和最小的分别记录为$minlastpost和$maxlastpost,上翻页查询为select*frompostwheretagid=$tagidandlastpost<$minlastpostorderbylastpostdesclimit30;下翻页为select*frompostwheretagid=$tagidandlastpost>$maxlastpostorderbylastpostlimit30;使用这种方式,影响结果集只有30条,效率极大提升。
●涉及跳转到任意页
⏹互联网上常见的一个优化方案可以这样表述,select*frompostwheretagid=$tagidandlastpost>=(selectlastpostfrompostwheretagid=$tagidorderbylastpostlimit$start,1)orderbylastpostlimit30;或者select*frompostwherepidin(selectpidfrompostwheretagid=$tagidorderbylastpostlimit$start,30);(第2条S语法在新的mysql版本已经不支持,新版本mysqlin的子语句不再支持limit条件,但可以分解为两条SQL实现,原理不变,不做赘述)
⏹以上思路在于,子查询的影响结果集仍然是$start+30,但是数据获取的过程(Sendingdata状态)发生在索引文件中,而不是数据表文件,这样所需要的系统开销就比前一种普通的查询低一个数量级,而主查询的影响结果集只有30条,几乎无开销。
但是切记,这里仍然涉及了太多的影响结果集操作。
◆延伸问题:
●来自于uchome典型查询SELECT*FROMuchome_threadWHEREtagid='73820'ORDERBYdisplayorderDESC,lastpostDESCLIMIT$start,30;
●如果换用如上方法,上翻页代码SELECT*FROMuchome_threadWHEREtagid='73820'andlastpost<$minlastpostORDERBYdisplayorderDESC,lastpostDESCLIMIT0,30;下翻页代码SELECT*FROMuchome_threadWHEREtagid='73820'andlastpost>$maxlastpostORDERBYdisplayorderDESC,lastpostASCLIMIT0,30;
●这里涉及一个orderby索引可用性问题,当orderby中复合索引的字段,一个是ASC,一个是DESC时,其排序无法在索引中完成。
所以只有上翻页可以正确使用索引,影响结果集为30。
下翻页无法在排序中正确使用索引,会命中所有索引内容然后排序,效率低下。
●总结:
⏹基于影响结果集的理解去优化,不论从数据结构,代码,还是涉及产品策略上,都需要贯彻下去。
⏹涉及limit$start,$num的搜索,如果$start巨大,则影响结果集巨大,搜索效率会非常难过低,尽量用其他方式改写为limit0,$num;确系无法改写的情况下,先从索引结构中获得limit$start,$num或limit$start,1;再用in操作或基于索引序的limit0,$num二次搜索。
⏹请注意,我这里永远不会讲关于外键和join的优化,因为在我们的体系里,这是根本不允许的!
架构优化部分会解释为什么。
理解执行状态
常见关注重点
●慢查询日志,关注重点如下
⏹是否锁定,及锁定时间
◆如存在锁定,则该慢查询通常是因锁定因素导致,本身无需优化,需解决锁定问题。
⏹影响结果集
◆如影响结果集较大,显然是索引项命中存在问题,需要认真对待。
●Explain操作
⏹索引项使用
◆不建议用usingindex做强制索引,如未如预期使用索引,建议重新斟酌表结构和索引设置。
⏹影响结果集
◆这里显示的数字不一定准确,结合之前提到对数据索引的理解来看,还记得嘛?
就把索引当作有序序列来理解,反思SQL。
●Setprofiling,showprofilesforquery操作
⏹执行开销
◆注意,有问题的SQL如果重复执行,可能在缓存里,这时要注意避免缓存影响。
通过这里可以看到。
◆执行时间超过0.005秒的频繁操作SQL建议都分析一下。
◆深入理解数据库执行的过程和开销的分布
●Showprocesslist执行状态监控
⏹这是在数据库负载波动时经常进行的一项操作
⏹具体参见如下
执行状态分析
●Sleep状态
⏹通常代表资源未释放,如果是通过连接池,sleep状态应该恒定在一定数量范围内
⏹实战范例:
因前端数据输出时(特别是输出到用户终端)未及时关闭数据库连接,导致因网络连接速度产生大量sleep连接,在网速出现异常时,数据库toomanyconnections挂死。
⏹简单解读,数据查询和执行通常只需要不到0.01秒,而网络输出通常需要1秒左右甚至更长,原本数据连接在0.01秒即可释放,但是因为前端程序未执行close操作,直接输出结果,那么在结果未展现在用户桌面前,该数据库连接一直维持在sleep状态!
●Waitingfornet,readingfromnet,writingtonet
⏹偶尔出现无妨
⏹如大量出现,迅速检查数据库到前端的网络连接状态和流量
⏹案例:
因外挂程序,内网数据库大量读取,内网使用的百兆交换迅速爆满,导致大量连接阻塞在waitingfornet,数据库连接过多崩溃
●Locked状态
⏹有更新操作锁定
⏹通常使用innodb可以很好的减少locked状态的产生,但是切记,更新操作要正确使用索引,即便是低频次更新操作也不能疏忽。
如上影响结果集范例所示。
⏹在myisam的时代,locked是很多高并发应用的噩梦。
所以mysql官方也开始倾向于推荐innodb。
●Copytotmptable
⏹索引及现有结构无法涵盖查询条件,才会建立一个临时表来满足查询要求,产生巨大的恐怖的i/o压力。
⏹很可怕的搜索语句会导致这样的情况,如果是数据分析,或者半夜的周期数据清理任务,偶尔出现,可以允许。
频繁出现务必优化之。
⏹Copytotmptable通常与连表查询有关,建议逐渐习惯不使用连表查询。
⏹实战范例:
◆某社区数据库阻塞,求救,经查,其服务器存在多个数据库应用和网站,其中一个不常用的小网站数据库产生了一个恐怖的copytotmptable操作,导致整个硬盘i/o和cpu压力超载。
Kill掉该操作一切恢复。
●Sendingdata
⏹Sendingdata并不是发送数据,别被这个名字所欺骗,这是从物理磁盘获取数据的进程,如果你的影响结果集较多,那么就需要从不同的磁盘碎片去抽取数据,
⏹偶尔出现该状态连接无碍。
⏹回到上面影响结果集的问题,一般而言,如果sendingdata连接过多,通常是某查询的影响结果集过大,也就是查询的索引项不够优化。
⏹前文提到影响结果集对SQL查询效率线性相关,主要就是针对这个状态的系统开销。
⏹如果出现大量相似的SQL语句出现在showproesslist列表中,并且都处于sendingdata状态,优化查询索引,记住用影响结果集的思路去思考。
●Storingresulttoquerycache
⏹出现这种状态,如果频繁出现,使用setprofiling分析,如果存在资源开销在SQL整体开销的比例过大(即便是非常小的开销,看比例),则说明querycache碎片较多
⏹使用flushquerycache可即时清理,也可以做成定时任务
⏹Querycache参数可适当酌情设置。
●Freeingitems
⏹理论上这玩意不会出现很多。
偶尔出现无碍
⏹如果大量出现,内存,硬盘可能已经出现问题。
比如硬盘满或损坏。
⏹i/o压力过大时,也可能出现Freeitems执行时间较长的情况。
●Sortingfor…
⏹和Sendingdata类似,结果集过大,排序条件没有索引化,需要在内存里排序,甚至需要创建临时结构排序。
●其他
⏹还有很多状态,遇到了,去查查资料。
基本上我们遇到其他状态的阻塞较少,所以不关心。
分析流程
●基本流程
⏹详细了解问题状况
◆Toomanyconnections是常见表象,有很多种原因。
◆索引损坏的情况在innodb情况下很少出现。
◆如出现其他情况应追溯日志和错误信息。
⏹了解基本负载状况和运营状况
◆基本运营状况
●当前每秒读请求
●当前每秒写请求
●当前在线用户
●当前数据容量
◆基本负载情况
●学会使用这些指令
⏹Top
⏹Vmstat
⏹uptime
⏹iostat
⏹df
●Cpu负载构成
⏹特别关注i/o压力(wa%)
⏹多核负载分配
●内存占用
⏹Swap分区是否被侵占
⏹如Swap分区被侵占,物理内存是否较多空闲
●磁盘状态
⏹硬盘满和inode节点满的情况要迅速定位和迅速处理
⏹了解具体连接状况
◆当前连接数
●Netstat–an|grep3306|wc–l
●Showprocesslist
◆当前连接分布showprocesslist
●前端应用请求数据库不要使用root帐号!
⏹Root帐号比其他普通帐号多一个连接数许可。
⏹前端使用普
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Mysql 性能 优化 高级工程师