文档介绍：目录目录 1背景及目标 2Mysql执行优化 2认识数据索引 2为什么使用数据索引能提高效率 2如何理解数据索引的结构 2优化实战范例 3认识影响结果集 4影响结果集的获取 4影响结果集的解读 4常见案例及优化思路 5理解执行状态 7常见关注重点 7执行状态分析 8分析流程 9常见案例解析 11总结 12Mysql运维优化 14存储引擎类型 14内存使用考量 14性能与安全性考量 14存储/写入压力优化 15运维监控体系 15Mysql架构优化 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