SQL业务审核与优化

千家信息网最后更新 2024年11月11日SQL业务审核与优化转自http://www.cnblogs.com/Aiapple/p/5697229.html

审核

---

什么是业务审核

• 类似与code review
• 评审业务Schema和SQL设计
• 偏重关注性能
• 是业务优化的主要入口之一

审核提前发现问题，进行优化 上线后通过监控或巡检发现问题，进行优化 Schema设计审核

• 表和字段命名是否合规
• 字段类型，长度设计是否适当
• 表关联关系是否合理
• 主键，更新时间保留字段等是否符合要求
• 约束，默认值等配置是否恰当
• 了解业务，表数据量，增长模式
• 数据访问模式，均衡度
• 根据业务需求，表是否需要分区，是否有数据什么周期

SQL语句审核

• SQL语句的执行频率
• 表上是否有合适的索引
• 单次执行的成本
• 执行模式，锁情况分析
• 关注事务上下文

什么时候需要审核

• 业务开发阶段，上线前
• 业务版本变更，线上更新前
• • 新表和SQL上线
  • SQL查询条件变化
  • SQL查询频率变化
  • 业务逻辑导致现有表数据量规模变化

业务发布流程

• SQL审核需要开发与应用运维支持
• 充分沟通，做好必要性说明和教育工作
• 指定业务发布流程，嵌入DBA审核环节
• 积累经验，不断完善评审方法

慢查询

---

查询优化，索引优化，库表结构优化需要齐头并进。 慢查询两个步骤分析：

• 确认应用程序是否向数据库请求了大量超过需要的数据
• 确认mysql服务器层是否在处理大量超过需要的数据记录

是否向数据库请求了不需要的数据 典型案例：

• 查询不需要的记录
• 多表关联时返回全部列
• 总是取出全部列
• 重复查询相同的数据

mysql是否在扫描额外的记录 在确定查询只返回需要的数据后，接下来应该看看查询为了返回结果是否扫描了过多的数据。 mysql查询开销的三个指标：

• 响应时间
• 扫描的行数
• 返回的行数

这三个指标都会记录到mysql的慢日志中，索引检查慢日志记录是找出扫描行数过多的查询的好办。 响应时间：执行时间和等待时间； 判断一个响应时间是否是合理的值，可以使用"快速上限估计"。 扫描的行数和返回的行数 分析查询时，查看该查询扫描的行数是非常有帮助的。它一定程度上说明该查询找到需要的数据的效率高不高。 如果发现查询需要扫描大量的数据但只返回少数的行，优化方法：

• 使用索引覆盖扫描，把所有需要用的列都放到索引中。
• 改变库表结构。例如使用单独的汇总表
• 重写这个复杂的查询，让mysql优化器能够以更优化的方式执行这个查询。

有的时候将大查询分解为多个小查询是有必要的。 查询执行的基础

---

mysql查询执行路径

1. 客服端发送一条查询给服务器
2. 服务器先检查缓存。如果命中缓存，则立刻返回结果。否则进入下一阶段。
3. 服务器端进行SQL解析，预处理，再由优化器生成对应的执行计划。
4. mysql根据优化器生成的执行计划，调用存储引擎的API来执行查询。
5. 将结果返回给客户端

mysql客户端/服务器通信协议 mysql客户端和服务器之间的通信协议是"半双工"。任何时候只能一方发；不能同时发送； mysql连接时线程状态

mysql> show full processlist; +----+------+-----------+--------+---------+------+-------+------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+--------+---------+------+-------+------------------------+ | 39 | root | localhost | sakila | Sleep | 4 | | NULL | | 40 | root | localhost | sakila | Query | 0 | NULL | show full processlist | +----+------+-----------+--------+---------+------+-------+------------------------+ 2 rows in set (0.00 sec)

查询优化器

---

一条查询可以有很多种执行方式，最后都返回相同的结果。 优化器的作用就是找到这其中最好的执行计划。 mysql使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。 通过查询当前会话的last_query_cost的值来得知Mysql计算的当前查询的成本。 mysql>selectcount(*) from film_actor; +----------+|count(*) |+----------+|5462|+----------+1 row inset (0.00 sec)

 mysql> show status like 'last_query_cost'; +-----------------+-------------+ | Variable_name | Value | +-----------------+-------------+ | Last_query_cost | 1040.599000 | +-----------------+-------------+

这个结果表示mysql优化器认为大概需要做1040个数据页的随机查找才能完成上面的查询。这是根据一系列的统计信息计算得来的：每个表或者索引的页面个数，索引的基数(索引中不同值的数量)，索引和数据行的长度，索引分布情况。 优化器在评估成本的时候并不考虑任何层面的缓存，它假设读取任何数据都需要一次磁盘I/O。 mysql优化器选错执行计划的原因：

• 统计信息不准确
• 执行计划中的成本估算不等同于实际执行的成本。
• • 有的计划虽然要读取更多页，但是这些页在缓存中。
• mysql的最有可能和你想的最优不一样。
• • 比如你希望执行时间尽可能的短，而mysql只是基于成本模型选择的最优执行计划。
• mysql从不考虑其他并发执行的查询，这可能会影响到当前查询速度。
• mysql不会考虑不受其控制的操作的成本。
• • 如执行存储过程或者用户自定义函数的成本

优化策略：

• 静态优化
• • 直接对解析树进行分析，并完成优化。优化器通过一些简单的代数变换将where条件转换成另一种等价形式。静态优化在第一次完成后一直有效。可以认为这是一种"编译时优化"
• 动态优化
• • 动态优化和查询的上下文有关。也和其他很多因素有关，例如where中的取值，索引中条目，等等。每次查询的时候都重新评估，可以认为这是一种"运行时优化"

mysql能够处理的优化类型

• 重新定义关联表的顺序。
• 将外联结转成内连接
• 使用等价变化规则
• • 合并和减少一些比较，移除一些恒成立和一些恒不成立的判断
  • 优化count(),min(),max()，min()就直接拿BTree树最左端数据行
• 预估并转换为常数表达式
• 覆盖索引扫描
• 子查询优化
• 提前终止查询
• 等值传播

在查询中添加hint,提示优化器， 优化器的局限性

---

1 关联子查询 mysql的子查询实现得非常糟糕；最糟糕的一类查询是where条件中包含IN()的子查询语句。 例如，我们希望找到sakila数据库中，演员actor_id为1，参演过的所有影片信息。很自然的，我们会按照下面的方式

mysql> select * from film where film_id in ( select film_id from film_actor where actor_id =1) \G;

我们一般认为，mysql会首先将子查询的actor_id=1的所有film_id都找到，然后再去做外部查询，如

select * from film where film_id in （1,23,25,106,140）；

然而，mysql不是这样做的。 mysql会将相关的外层表压到子查询中，它认为这样可以更高效率地查找数据行。 当然我们可以使用连接替代子查询重写这个SQL，来优化； mysql> explain select*from film f innerjoin film_actor fa where f.film_id=fa.film_id and actor_id =1; +----+-------------+-------+--------+------------------------+---------+---------+-------------------+------+-------+| id | select_type |table| type | possible_keys |key| key_len | ref | rows | Extra |+----+-------------+-------+--------+------------------------+---------+---------+-------------------+------+-------+|1| SIMPLE | fa | ref |PRIMARY,idx_fk_film_id |PRIMARY|2| const |19|||1| SIMPLE | f | eq_ref |PRIMARY|PRIMARY|2| sakila.fa.film_id |1||+----+-------------+-------+--------+------------------------+---------+---------+-------------------+------+-------+2 rows inset (0.00 sec) 如何用好关联子查询，很多时候，关联子查询也是一种非常合理，自然，甚至是性能最好的写法。 where in(）肯定是不行的，但是 where exists（）有时是可以的； 2 union的限制 有时，mysql无法将限制条件从外层"下推"到内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。 如果希望union的各个子句能够根据limit只取部分结果集，或者希望能够先拍下再合并结果集的话，就需要在union的各个子句中分别使用这些子句。 如： （select first_name,last_name from sakila.actor orderby last_name） unionall （select first_name,last_name from sakila.customer orderby last_name）

 limit 20;

会将actor中200条记录和customer中599条记录放在一个临时表中，然后在从临时表中取出前20条； 而 （select first_name,last_name from sakila.actor orderby last_name