MySQL基础技能与原理――高级应用-LinuxToneOrg.PPT

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变量存储数组。 如果连接数过多会怎么样？ 如果连接MySQL的线程太多，那么每个线程分配到的join_buffer+sort_buffer总量就会很大，占用大量内存。 如何设置连接池？ 建议最小值和最大值差距不要太大，避免连接反复的创建和释放，带来CPU开销。如果连接数一直比较平稳的应用，则最小值和最大值在平均线附近设置即可。如果连接数长期较高，则最小值也设置大一些。如果连接数长期较低，则最大值偏小设置。 应用程序实现JOIN 如果需要跨主机进行JOIN，跨应用进行JOIN，或者数据库不能获得较好的执行计划，都可以自己通过程序来实现JOIN。 例如：SELECT a.*,b.* FROM a,b WHERE a.col1=b.col1 AND a.col2 10 ORDER BY a.col2; 可以利用程序实现，先SELECT * FROM a WHERE a.col210 ORDER BY a.col2;--(1) 利用(1)的结果集，做循环，SELECT * FROM b WHERE b.col1=a.col1; 这样可以避免排序，可以在