SQL查询优化
获取有性能问题的SQL
通过用户
通过慢查日志获取存在性能问题的SQL
实时获取存在性能问题的SQL
使用慢查询日志获取有性能问题的SQL
slow_query_log 启动停止记录慢查日志（默认是关闭的）这个参数是动态的 可以通过set global来设置这个参数
可以通过脚本来实现这个开关的控制
set global slow_query_log=on;
slow_query_log_file 指定慢查询日志的存储路劲及文件
默认情况下保存在MySQL的数据目录当中，最好是将日志存储和数据存储分开
long_query_time 指定记录慢查日志SQL执行时间的伐值（可以精确到微秒），但是单位是秒
set global long_query_time = 0;
记录所有符合条件的语句
包括查询语句 和 数据修改语句 已经回滚的SQL 默认值为10s ，通常为1毫秒
long_queries_not_using_indexes 是否记录未使用索引的SQL
实时获取有性能问题的SQL
这里可以通过一个SQL脚本周期性的执行这条SQL语句,这样就可以获取到实时监控SQL性能的效果
select id,`user`,`host`,DB,command,`time`,state,info from information_schema.processlist;
SQL的解析预处理及生成执行计划
查询为什么会慢
1.客户端发送SQL请求给服务器
2.服务器检查时候可以在查询缓存中命中该SQL
a.优先检查这个查询是否命中查询缓存中的数据(前提是查询缓存是否打开),检测缓存是否命中的时候都需要对缓存进行加锁操作
MySQL8.0已经取消了查询缓存,小型项目不想使用缓存的话可以建议在8.0以下的版本开启
b.通过一个对大小写敏感的哈希查找实现的(要在查询缓存当中直接返回结果 是并不容易的)
Hash查找只能进行匹配
c.对于一个对写比较频繁的系统使用查询缓存可能会降低查询处理的效率
3.服务器端进行SQL解析,预处理,再由优化器生成对应的执行计划
4.根据执行计划,调用存储引擎API来查询数据
5.将结果返回给客户端
MySQL依照上述的执行计划和存储引擎进行交互
这个阶段包括了多个子过程
解析SQL,预处理,优化SQL执行计划
语法解析阶段是通过关键字对MySQL语句进行解析,并生成一棵对应的"解析树"
MySQL解析器将使用MySQL语法规则验证和解析查询
包括检查语法是否使用了正确的关键字
关键字的顺序是否正确等
预处理阶段是根据目送去了规则进一步检查解析树是否合法
检查查询中所涉及的表和数据列是否存在及名字或别名是否存在歧义等等,如果不语法检查全部都通过了,查询优化器就可以生成查询计划了
会造成MySQL生成错误的执行计划的原因(MySQL是基于其成本模型选择最优的执行计划)
统计信息不准确
执行计划中的成本估算不等同于实际的执行计划的成本
MySQL执行的最有计划与你认为的最优不一样
MySQL从不会考虑其他并发的查询,这可能会印象当前的查询速度
MySQL有时候也会基于一些固定的规则来生成执行计划
MySQL不会考虑不受其版本控制的成本
存储过程、用户自定义函数
MySQL优化器可优化的SQL类型
重新定义表的关联顺序
优化器会根据统计信息来决定表的关联顺序
将外连接转化为内连接（也就是说并不是所有的left join和right join这种外连接都是以外链接方式来执行的【也就是出现外连接的效果等 同于内连接，外连接+where 】）
where条件和库表结构
使用等价变换规则
例如 where 5=5 and a>5优化为a>5
优化count()、min()、max()
将一个表达式转化为常数表达式
使用等价变换规则（覆盖索引，所查询的数据中，索引包含了所有所需要查询的列 ）
子查询优化
将子查询转换为关联查询（减少查询的次数 ）
提前终止查询（查询的结果是不存在的），可以通过执行计划的扩展列可以知道
对in()进行优化（在很多的SQL server中in的作用类似于or，但是MySQL不一样）
MySQL会先对in中的条件进行排序，再通过二分查找判断该值是否满足条件，这样相对与其他的关系型数据库更快
确定查询处理各个阶段所消耗的时间
减少查询所消耗的时间加快查询的响应速度
使用profile
、
还可以配合其他的命令
例如
show profile cpu for query 3;
在使用profile的时候，都会抛出警告，提示在以后的版本当中将不再使用profile，请使用Performance_Schema来进行替换
使用performance_schema
在5.6之后的版本建议开启这个功能
开启监控,使用performance_schema需要开启上面两项
如果开启performance_schema对于全局都是有效的
特定的SQL查询优化
大表的数据修改最好是分批处理
这是一个样本，在使用过程中，只需要修改红框部分就可以了
对大表结构的修改
对表的列的字段类型进行修改和改变字段的宽度都是会进行锁表的
无法接解决主从数据库延迟问题
1.方案一：利用主从复制的架构，先在从服务器上进行修改，在进行主从切换（存在一定的风险），再对用原主（从）服务器进行修改
2.方案二：先在主服务器上建立一个新表，新表的结构就是旧表进行修改过后的结构，再将老表的数据导入新表当中，并在老表中建立一系列的触发器， 将老表中的数据同步到新表当中，当数据完成同步之后，再对老表添加一个排它锁并重新命名,最终删除老表
3.排他锁又称为写锁，简称X锁，顾名思义，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其 他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。
这样可以减少加锁的时间，缺点就是操作比较复杂，但是可以通过工具来实现
使用汇总表优化查询
准们建立一个汇总表，在数据库服务器的工作量比较小的情况下进行
网友评论