SQL程序走查 优化案例介绍
什么是索引？
索引的好处与坏处
创建索引的好处
帮助用户提高查询速度； 利用索引的唯一性来控制记录的唯一性； 可以加速表与表之间的连接 ； 降低查询中分组和排序的时间
创建索引的坏处
存储索引占用磁盘空间； 执行数据修改操作(INSERT、UPDATE、DELETE) 产生索引维护
数据表选择合适的引擎
MyISAM 特点
数据存储方式简单，使用 B+ Tree 进行索引使用三个文件 定义一个表：.MYI、.MYD、.frm； 少碎片、支持大文件、能够进行索引压缩； 访问速度飞快，是所有MySQL文件引擎中速度最快的； 不支持一些数据库特性，比如 事务、外键约束等； 表级锁，性能稍差，更适合读取多的操作(查询和更新操作并 行时，查询操作需等待更新操作结束)； 表数据容量有限，一般建议单表数据量介于50w–200w； 增删查改以后要使用 myisamchk 检查优化表
参数名称
innodb_buffer_pool_siz e innodb_additional_mem _pool_size innodb_log_buffer_size
参数说明
InnoDB使用该参数指定大小的内存来缓冲数据和 索引 指定InnoDB用来存储数据字典和其他内部数据结 构的内存池大小 指定InnoDB用来存储日志数据的缓存大小，如果 您的表操作中包含大量并发事务（或大规模事务 ），并且在事务提交前要求记录日志文件，请尽 量调高此项值，以提高日志效率 决定索引处理的速度。key_buffer_size只对 MyISAM表起作用。即使你不使用MyISAM表，但 是内部的临时磁盘表是MyISAM表，也要使用该 值。 缓存select语句和结果集大小的参数
索引的正确使用(七)
避免使用“or” ，采用其它方式重写
索引的正确使用(八)
避免使用<>，用其它方式改写
索引的正确使用(八)
使用组合索引时，注意“最左前缀”这个基本原
则 最左前缀：就是最左优先，我们创建了 lname、fname和age的多列索引,相当于创 建了lname单列索引，(lname,fname)的组 合索引以及(lname,fname,age)组合索引； SELECT ｀uid｀ FROM people WHERE ｀fname｀=„Zhiqun‟ AND ｀age｀=26 上述查询语句因违法“最左前缀”原则，系 统通常会扫描整表以匹配数据！
数据表选择合适的引擎
InnoDB 特点
使用 Table Space 的方式来进行数据存储 (ibdata1, ib_logfile0)； 支持事务、外键约束等数据库特性； 行级锁, 读写性能都非常优秀。(在默认“可重复读”事 务隔离下，查询和更新操作并行时，查询操作不需等待) ； 能够承载大数据量的存储和访问； 拥有自己独立的缓冲池，能够缓存数据和索引； 在关闭自动提交的情况下，与MyISAM引擎速度差异不大
数据表和字段设计的原则
字段选择的一般原则是保小不保大，能占用 字节少的字段就不用大字段； 字段的命名要有意义; 不用使用无法加索引的类型作为关键字段， 比如text； 表中组合主键的字段个数越少越好； 没有冗余的数据库未必是最好的数据库，适 当的时候需降低范式标准； 根据应用场合选择表的存储引擎
正确创建和使用索引
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能筛选出绝大部分数据么？
设置大小
默认128M,要设置为物理 内存的60%~70% 默认8M，一般设置为 16M即可。 默认8M，一般设置为 16M~64M即可。
key_buffer_
MySQL配置参数(三)
使用查询缓存
MySQL配置参数(四)
定位慢查询和不使用索引的查询
优化案例(五)
考勤调整表字段 attendance_id 建立索引：
优化案例(五)
考勤调整表字段 attendance_id 建立索引； 考勤表字段employee_id 建立索引
谢谢！
默认值100，一般512~1000
MySQL配置参数(二)
调整全局缓存的大小
global_buffers=innodb_buffer_pool_size+innodb_additional_mem_pool_size +innodb_log_buffer_size+key_buffer_size+query_cache_size
设置大小
默认128K,一般128K~256K
默认2M，一般128K~256K 默认256K,一般 128K~256K 默认192KB 默认128K, 128K~256K
binlog_cache_size
max_connections
在事务过程中容纳二进制日志SQL 语句的缓存大小
最大连接数
128K~256K
索引创建的总体原则
在了解表的具体应用场景基础上建立索引； 为所有主键和外键列建立索引； 对出现在WHERE子句、JOIN子句、ORDER BY或GROUP BY子句中的列考虑建立索引； 对需要确保唯一性的列考虑建立索引； 对于WHERE子句中用AND连接并频繁使用的列使用 组合索引，最频繁的列放在最左边； 数据更新频繁的列不宜建立索引； 数据量较小的表也不宜建立索引
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可能非常高效。
MySQL数据库性能优化 李计刚 2015-08
参考书籍和文档
《MySQL管理之道,性能调优、高可用与监控》第5章“性能调优” 《深入浅出 MySQL数据库开发、优化与管理维护(第2版)》第三部分 “优化篇”
《 MySQL技术内幕(第4版)》第5章“查询优化”
《DB2 SQL性能调优秘笈》全书，重点第4章“SQL程序走查” 《构建高性能Web站点》第11章“数据库性能优化”
参数名称
read_buffer_size
sort_buffer_size read_rnd_buffer_size thread_stack join_buffer_size tmp_table_size
参数说明
MySQL读入缓冲区大小
MySQL执行排序使用的缓冲大小 MySQL的随机读缓冲区大小 每个线程的堆栈大小 Join连接操作时，如果关联的字段 没有索引，会出现此参数。 MySQL的临时表缓冲大小
索引的正确使用(一)
去除查询条件左端的任何标量函数
索引的正确使用(二)
去除查询条件左端的任何数学运算
索引的正确使用(三)
确保宿主变量定义与列数据类型匹配
索引的正确使用(四)
查询条件中避免使用in
索引的正确使用(五)
尽可能用UNION ALL取代UNION
索引的正确使用(六)
查询条件中使用like时避免宿主变量以‘%‟开头
SQL程序走查(三)续
分析具体的SQL：
检查所有Distinct语句。是否可能出现重复？如果有重复， 考虑改写该语句以避免排序； 检查所有谓词，确保所有数学计算都在操作符的另一边完成， 而不是应用在列本身； 检查所有谓词，确保左端无任何标量函数； 检查所有的“非”逻辑，按正面的方式来改写此非逻辑，这 样会更高效。 检查所有的“<>”,对其进行改写，这样会更高效；
目录
表设计
正确创建和使用索引
MySQL配置参数
SQL程序走查 优化案例介绍
MySQL配置参数(一)
调整线程缓存的大小
per_thread_buffers=(read_buffer_size+read_rnd_buffer_size+sort_buffer_s ize+thread_stack+join_buffer_size+binlog_cache_size)*max_connections