mysql性能优化教程.docVIP

Mysql 性能优化教程 目录 TOC \o 1-3 \h \z \u 目录 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 背景及目标 厦门游家公司（4399.com）用于员工培训和分享。 针对用户群为已经使用过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次以上的查询效率 挑战： 如使用 between startip and endip 这样的条件数据库操作，因为涉及两个字段的between and, 无法有效使用索引。 如果每次查询请求需要遍历10万条记录，根本不行。 方法： 一次性排序（只在数据准备中进行，数据可存储在内存序列） 折半查找（每次请求以折半查找方式进行） 实战范例2：目标：查找与访问者同一地区的异性，按照最后登录时间逆序 挑战：高访问量社区的高频查询，如何优化。 查询SQL: select * from user where area=’$area’ and sex=’$sex’ order by lastlogin desc limit 0,30; 建立复合索引并不难， area+sex+lastlogin 三个字段的复合索引,如何理解？ 解读：首先，忘掉btree，将索引字段理解为一个排序序列。 另外，牢记数据查询只能使用一个索引，每个字段建立独立索引的情况下，也只能有一条索引被使用！ 如果只使用area会怎样？搜索会把符合area的结果全部找出来，然后在这里面遍历，选择命中sex的并排序。 遍历所有 area=’$area’数据！ 如果使用了area+sex，略好，仍然要遍历所有area=’$area’ and sex=’$sex’数据，然后在这个基础上排序！！ Area+sex+lastlogin复合