SQL语句优化
- 对查询进行优化，要尽量避免全表扫描。在 where 或 order by 的列上加索引。
- 尽量避免在 where 子语句中有 where num is null，这样不用索引，要全表扫描，可用 0 代替 null
- 避免在 where 中用<>or!=,因为要全表扫描
- 尽量避免在 where 中用 or，因为若一个字段有索引，一个没有，则要全表扫描
- like”%abc%”，全表扫描
- 避免在 where 子语句中对字段进行函数操作，因为要全表扫描
- 使用复合索引时，必须用到该索引的第一个字段，否则索引不被使用。
- 尽量避免在 where 子句使用 != 或 <> 操作符
引擎将放弃使用索引而进行全表扫描 - 应尽量避免在 where 子句中对字段进行 null 值判断
否则将导致引擎放弃使用索引而进行全表扫描
如：select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0 - 很多时候用 exists 代替 in 是一个好的选择
- 用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤
1. 获取有性能问题SQL的方法
- 通过用户反馈(自然是不建议滴)
- 通过慢查询日志(时间可能较长)
- 实时获取
2.慢查询日志介绍
2.1 性能
开销较低,主要来自于磁盘I/O和存储日志所需磁盘空间,对于现代磁盘,主要问题就只在于所需的大量存储空间了
2.2 位置控制
-
slow_query_log 启动/停止记录慢查询日志(默认为off,手动配置文件on才能开启)
在运行的MySQL中,可通过set global启动
也可通过脚本定时控制
- slow_query_log_file 指定慢查询日志的存储路径及文件(默认在数据目录)
当然最好将日志/数据存储分开啦 -
long_query_time 指定记录慢查询日志SQL执行时间的阈值(默认单位s,可精确至ms)
默认值10s,通常改为0.001s即1ms较合适
- log_queries_not_using_indexes 是否记录未使用索引的SQL
2.3常用日志分析工具
2.3.1 mysqldumpslow(MySQL官方自带)
实例查询
2.3.2 pt-query-digest(推荐使用)
生成日志服务器及日志文件名
生成到slow.rep
执行vi slow.rep
提供信息远多于mysqldumpslow生成的
续
3. 实时获取
通过此表
实例
3.SQL的解析预处理及生成执行计划
3.1 查询速度为什么会慢
MySQL服务器处理查询请求的整个过程
3.2 查询缓存对SQL性能的影响
Hash查找只能进行全值匹配
命中缓存,在返回结果前,MySQL会检查用户权限,查询无需被解析,看出缓存直接返回结果其实很不容易
如果缓存中结果正确的,每次缓存牵涉到表被更新,都要对缓存也进行刷新,如此即使是同一个sql语句即使对同一个表查询中不同不涉及的字段被更新,下次查询这个sql同样无法命中
此外每次在对缓存进行检查SQL是否命中时,都要对缓存加锁
不建议使用查询缓存
-
query_cache_type 设置查询缓存是否可用(ON,OFF,DEMAND)
- query_cache_size 设置查询缓存的内存大小
- query_cache_limit 设置查询缓存可用存储的最大值
如果预先知道哦结果不会被缓存加上SQL_NO_CACHE可以提高效率 - query_cache_wlock_invalidate 设置数据表被锁后是否返回缓存中的数据(默认关闭)
- query_cache_min_res_unit 设置查询缓存分配的内存块最小单位
当
3.3 执行计划
3.3.1 可能造成MySQL生成错误的执行计划的原因
3.3.2 MySQL优化器可优化的SQL类型
-
重定义表的关联顺序
- 将外连接转化为内连接
如当有where条件和库表结构等会重写优化 -
对一些过滤规则进行等价变换
-
优化count(),min(),max()等聚合函数
优化器会使用B+索引和列是否为null来优化
所以直接选最左或者最右的记录即得min,max
由此会在查询计划中看到如下信息
- 将一个表达式转化为常数表达式
- 等价变换规则
- 子查询优化
可能转为关联查询,减少表的查询次数 -
提前终止查询
发现已经满足查询条件时立即终止,特例如limit子句
发现不成立条件,立即返回null
film table
由于id定义为无符号类型,所以直接终止了查询,并无读取任何数据 - 对in()条件进行优化
对in列表的元素先进行排序,再通过二分查找确定
3.4 确定查询处理各个阶段所消耗的时间
3.4.1使用profile
set profile = 1;
启动profiel,这是一个会话级别的配置
- 执行查询
-
show profiles;查看每一个查询所消耗的总时间的信息 -
show profile for query N;查询每个阶段所消耗的时间N为queryId
当执行 `show profile for query N;`后
若想查看CPU信息执行以下show profile cpu for query 1;
但是已经不被推荐使用
3.4.2使用performance_schema(MySQL5.6后默认开启)
启动监控项
上述执行结果
4 对特定SQL的查询优化
一个存储过程实例
4.1如何修改大表的结构
主从方式
减少主从延迟,操作有工具加减单
数据示例表
alt语句
执行过程
验证表确实被修改
4.2 如何优化not in和<>查询
下面为优化sql
4.3 使用汇总表优化查询
汇总表
显示每个商品评论数
