MySQL数据库性能优化.ppt

SQL程序走查(二) 分析SQL的EXPLAIN： 检查所有表空间扫描； 检查发生的所有排序，是否需要排序?； 检查发生的所有排序，是否有其它办法编写查询来消除排序？ SQL程序走查(三) 分析具体的SQL： SQL语句中选择的列是否都需要，不需要的移除掉； 检查各个查询中的所有列。其中有没有可为null的列？确保编写相应的SQL来处理null值； 对于AVG、MIN、MAX或SUM语句，确保编写相应的SQL来处理null值； 内联接和外联接的使用是否正确？ 检查所有的Union SQL语句。是否需要写为Union，或者SQL语句是否可以写为Union All? 检查所有的Order By语句。从业务上考量是否需要这些Order By语句么？有没有一个索引支持排序？如果与一个索引匹配，Order By可能非常高效。 SQL程序走查(三)续 分析具体的SQL： 检查所有Distinct语句。是否可能出现重复？如果有重复，考虑改写该语句以避免排序； 检查所有谓词，确保所有数学计算都在操作符的另一边完成，而不是应用在列本身； 检查所有谓词，确保左端无任何标量函数； 检查所有的“非”逻辑，按正面的方式来改写此非逻辑，这样会更高效。 检查所有的“”,对其进行改写，这样会更高效； SQL程序走查(三)再续 分析具体的SQL： 检查所有OR语句,对其进行改写，这样更高效； 检查所有的LIKE语句，确保其宿主变量不以“%”开头； 检查所有的组合索引，确保符合“最左前缀”原则； 检查所有的“In”语句，对其进行改写，这样会更高效； 确保宿主变量定义与列数据类型匹配 目录 表设计 正确创建和使用索引 MySQL配置参数 SQL程序走查 优化案例介绍 优化案例(一) 优化案例(二) 优化案例(三) 优化案例(四) 考勤表(att_attendance_record)分区处理； 考勤调整表(att_attendance_adjust)对字段 attendance_id 建立索引，避免全表扫描； 考勤表(att_attendance_record)对字段employee_id 建立索引； 按请假类型、部门进行分组统计的业务逻辑不能调整，但是部门名称、部门编号、部门ID都纳入分组，系统开销大； 按EXPLAIN分析，员工表master_data_employee、部门表 master_data_department、请假类型表config_leave_type都用了索引，KEY还是PRIMARY,不需优化； 考勤表(att_attendance_record)每天大约有1000条记录写入，基本上每天都会有记录写入，并且是集中写入。写操作太过频繁，不太适合建立索引； 筛选条件中左端谓词用到了COALESCE函数，即便对相应字段建立索引，索引也不会起作用； 调整WHERE后面条件语句的顺序调整。status=1能筛选出绝大部分数据么？ 具体优化时考虑到的因素一揽： 优化案例(五) 考勤调整表字段 attendance_id 建立索引： 优化案例(五) 考勤调整表字段 attendance_id 建立索引； 考勤表字段employee_id 建立索引 谢谢！ MySQL数据库性能优化 李计刚 2015-08 参考书籍和文档 《MySQL管理之道,性能调优、高可用与监控》第5章“性能调优” 《深入浅出 MySQL数据库开发、优化与管理维护(第2版)》第三部分“优化篇” 《 MySQL技术内幕(第4版)》第5章“查询优化” 《DB2 SQL性能调优秘笈》全书，重点第4章“SQL程序走查” 《构建高性能Web站点》第11章“数据库性能优化” 《SQL反模式》第13章 “反模式:无规划地使用索引” 《MySQL核心内幕,InnoDB存储引擎》 目录 表设计 正确创建和使用索引 MySQL配置参数 SQL程序走查 优化案例介绍 数据库表需最大程度遵守三范式 第一范式：数据库表中的字段都是单一属性的，不可再分； 第二范式：在第一范式基础上，除了主键以外的其它列都依赖于该主键； 第三范式：如果满足第二范式，并且除了主键以外的其它列都不传递依赖于主键列。 数据表和字段设计的原则 字段选择的一般原则是保小不保大，能占用字节少的字段就不用大字段； 字段的命名要有意义; 不用使用无法加索引的类型作为关键字段，比如text； 表中组合主键的字段个数越少越好； 没有冗余的数据库未必是最好的数据库，适当的时候需降低范式标准； 根据应用场合选择表的存储引擎 数据表选择合适的引擎 MyISAM 特点 数据存储方式简单，使用 B+ Tree 进行索引使用三个文件定义一个表：.MYI、.MYD、.frm； 少碎片、支持大文件、能够进行索引压缩； 访问速度飞快，是所有MySQL文件引擎