MySQL基础技能与原理――高级应用.pdfVIP

MySQL基础技能与原理 ——高级应用 MySQL DBA Team 彭立勋（） 内容概要 • 6. MySQL数据库同步方案 • 7. MySQL与应用程序的优化 • 8. MySQL管理基础 • 9. 网站常见错误分析 • 10. MySQL故障案例 MySQL数据库同步方案 • Trigger同步方案(Otter) • Log同步方案（Erosa） • 自带Replication同步方案 Trigger同步方案 • Otter采用Trigger方式进行同步。基本原理是为每张要同步的表加上一个触发 器，表上增删改都会触发Trigger把主键记录到一张同步表，同步程序读取同步 表的主键和修改类型，到目标表去执行相同的操作，保证最终一致。 • 优点：安全可靠，不会因为日志格式变化或数据库本身的机制变动带来同步程 序的大改动。 • 缺点：对数据库入侵大，需要为每张表建立Trigger ，并且在数据库建立存储过 程，执行存储过程和触发器会带来一定的数据库压力。 Log同步方案 • Erosa采用解析日志的方式进行同步。通过解析Oracle的RedoLog或MySQL的 BinLog，获取在数据库上执行的SQL及其顺序，分发到目标数据库去执行一 遍，以获得最终一致。 • 优点：对数据库入侵小，可以将日志拷贝到异地分析，对数据库本身不会增 加压力。 • 缺点：对日志格式依赖大，日志格式发生变动则同步程序需要进行较大改 动。 Replication同步方案 • MySQL 自带Replication复制方案，Oracle也有基于块复制的Stanf by方案。 MySQL通过将执行的增删改SQL记录到日志，传输到备机再执行一遍保证最终 一致性。可以设置日志格式为ROW （按行记录），Statement （按SQL记录）， MIXED （混合式，让MySQL决定每次记录用哪个格式），DDL全部通过 Statement方式记录。 • 优点：MySQL 自带，无需第三方工具，部署方便，MySQL 自带丰富的参数可以 实现各种复制方案，包括双Master，级联Slave等。 • 缺点：无法实现多源复制，复制位置丢失很难恢复复制。 MySQL与应用程序的优化 • 预处理与绑定变量 • 连接池设置及影响 • 应用程序实现JOIN 预处理与绑定变量 • PREPARE语句：SET@sql1=‘xxx’; PREPARE stmt1 FROM @sql1; • 变量用问号代替（绑定变量）。 • 作用：将SQL预先解析，在一个Session 内再次使用此语句时不再重新解 析。 • EXECUTE语句：EXECUTE stmt1 USING @val1,@val2; • 作用：将绑定变量赋予具体的值并且执行。 • DEALLOCATE语句：DEALLOCATE PREPARE stmt1; • 作用：销毁一个预解析SQL，释放资源。 • 为何使用绑定绑定变量？ • 因为绑定变量比直接传值更安全，尤其是拼接SQL非常容易被注入攻击。 同时绑定变量也可以提高一点效率。 • 为何使用预处理，尤其在存储过程？ • 因为预处理可以避免SQL 的反复解析，尤其是执行速度快的小SQL，解析时 间甚至可能超过执行时间，采用预处理可以显著提高性能。 连接池设置及影响 • MySQL如何处理连接？ • 每次客户端向数据库服务器请求一个连接，服务器根据用户名密码在已有 连接内查找一个相同配置的连接，分配给请求。如果找不到，则数据库分配 一个新连接给当前请求。 • 分配新连接做哪些工作？ • MySQL新开一个连接，就会创建一个新的mysql结构体，其中包括 Join_buffer 、Sort_buffer和一个Session变量存储数组。 • 如