[Mysql性能优化教程.docVIP

Mysql 性能优化教程 目录 目录 1 背景及目标 2 Mysql 执行优化 2 认识数据索引 2 为什么使用数据索引能提高效率 2 如何理解数据索引的结构 2 如何理解影响结果集 3 理解执行状态 4 常见分析手段 4 分析流程 6 总结 7 Mysql 运维优化 9 存储引擎类型 9 内存使用考量 9 性能与安全性考量 9 存储压力优化 10 运维监控体系 10 Mysql 架构优化 11 架构优化目标 11 防止单点隐患 11 方便系统扩容 11 安全可控，成本可控 11 分布式方案 12 分库拆表方案 12 主从架构 14 故障转移处理 15 缓存方案 15 缓存结合数据库的读取 15 缓存结合数据库的写入 15  背景及目标 针对用户群为已经使用过mysql环境，并有一定开发经验的工程师 针对高并发，海量数据的互联网环境。 本文语言为口语，非学术标准用语。 以实战和解决具体问题为主要目标，非应试，非常规教育。友情提醒，在校生学习本教程可能对成绩提高有害无益。 非技术挑战，非高端架构师培训，请高手自动忽略。 Mysql 执行优化 认识数据索引 为什么使用数据索引能提高效率 数据索引的存储是有序的 在有序的情况下，通过索引查询一个数据是无需遍历索引记录的 极端情况下，数据索引的查询效率为二分法查询效率，趋近于 log2(N) 如何理解数据索引的结构 数据索引通常默认采用btree索引，（内存表也使用了hash索引）。 单一有序排序序列是查找效率最高的（二分查找，或者说折半查找），使用树形索引的目的是为了达到快速的更新和增删操作。 在极端情况下（比如数据查询需求量非常大，而数据更新需求极少，实时性要求不高，数据规模有限），直接使用单一排序序列，折半查找速度最快。 实战范例 ： ip地址反查 资源： Ip地址对应表，源数据格式为 startip, endip, area 源数据条数为 10万条左右，呈很大的分散性 目标： 需要通过任意ip查询该ip所属地区 性能要求达到每秒1000次以上的查询效率 挑战： 如使用 between … and 数据库操作，无法有效使用索引。 如果每次查询请求需要遍历10万条记录，根本不行。 方法： 一次性排序（只在数据准备中进行，数据可存储在内存序列） 折半查找（每次请求以折半查找方式进行） 在进行索引分析和SQL优化时，可以将数据索引字段想象为单一有序序列，并以此作为分析的基础。 实战范例：复合索引查询优化实战，同城异性列表 资源： 用户表user，字段 sex性别；area 地区；lastlogin 最后登录时间；其他略 目标： 查找同一地区的异性，按照最后登录时间逆序 高访问量社区的高频查询，如何优化。 查询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复合索引时（切记lastlogin在最后），该索引基于area+sex+lastlogin 三个字段合并的结果排序，该列表可以想象如下。 广州女$时间1 广州女$时间2 广州女$时间3 … 广州男 …. 深圳女 …. 数据库很容易命中到 area+sex的边界，并且基于下边界向上追溯30条记录，搞定！在索引中迅速命中所有结果，无需二次遍历！ 如何理解影响结果集 影响结果集是数据查询优化的一个重要中间数据 查询条件与索引的关系决定影响结果集 如上例所示，即便查询用到了索引，但是如果查询和排序目标不能直接在索引中命中，其可能带来较多的影响结果。而这会直接影响到查询效率 微秒级优化 优化查询不能只看慢查询日志，常规来说，0.01秒以上的查询，都是不够优化的。 实战范例 和上案例类似，某游戏社区要显示用户动态，select * from userfeed where uid=$uid order by lastlogin desc limit 0,30; 初期默认以uid为索引字段， 查询为命中所有uid=$uid的结果按照lastlogin排序。 当用户行为非常频繁时，