SQL业务审核与优化

---

  什么是业务审核

• 类似与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> select count(*) from film_actor;
+----------+
| count(*) |
+----------+
|     5462 |
+----------+
1 row in set (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  inner join 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 in set (0.00 sec)

 

如何用好关联子查询，很多时候，关联子查询也是一种非常合理，自然，甚至是性能最好的写法。 where in(）肯定是不行的，但是 where exists（）有时是可以的；   2 union的限制   有时，mysql无法将限制条件从外层"下推"到内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。   如果希望union的各个子句能够根据limit只取部分结果集，或者希望能够先拍下再合并结果集的话，就需要在union的各个子句中分别使用这些子句。   如：  

（select first_name,last_name
  from sakila.actor
  order by last_name）
 union all
（select first_name,last_name
  from sakila.customer
  order by last_name）
 limit 20;

会将actor中200条记录和customer中599条记录放在一个临时表中，然后在从临时表中取出前20条；   而

（select first_name,last_name
  from sakila.actor
  order by last_name
  limit 20）
 union all
（select first_name,last_name
  from sakila.customer
  order by last_name
  limit 20）
 limit 20;

现在中间的临时表中只会包含40条记录。

  3  最大值和最小值优化 对于min()和max()查询，mysql的优化做得并不好。  

mysql> explain select min(actor_id) from actor where first_name=‘PENELOPE‘;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |  200 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

 

因为在first_name字段上没有索引，因此mysql将会进行一次全表扫描。 如果mysql能够进行主键扫描，那么理论上，mysql读到第一个满足条件的记录的时候，就是我们需要找的最小值了，因为主键时严格按照actor_id字段的大小顺序排序的。但这仅仅是如果，mysql这时只会做全表扫描。   优化min()，使用limit重写SQL：  

mysql> explain select actor_id from actor USE INDEX(PRIMARY) where first_name=‘PENELOPE‘ LIMIT 1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |  200 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

看着实验结果，似乎没有使用 主键索引，不知道是什么原因导致.欢迎交流。   4  在同一个表上查询和更新 mysql不允许，对同一张表进行查询和更新：

mysql> update tbl AS outer_tbl
          set cnt = (
               select count(*) from tbl AS inner_tbl
               where inner_tbl.type = outer_tbl.type
          );
error:you can‘t specify target table ‘outer_tbl‘ for update in from clause

可以使用内连接来绕过这个限制。实际上，这执行了两个查询：一个是子查询中的select语句，另一个是多表关联update，只是关联的表是一个临时表。  

mysql> update tbl
          inner join (
               select type,count(*) AS cnt
               from tbl
               group by type
          )AS der using(type)
       set tbl.cnt = der.cnt;
 

  优化器的提示（hint）

---

  如果对优化器选择的执行计划不满意，可以使用优化器提供的几个提示（hint）来控制最终的执行计划。  

• HIGH_PRIORITY,LOW_PRIORITY
• • 这个提示告诉mysql，当多个语句同时访问某一个表的时候，哪些语句的优先级相对高些，哪些语句的优先级相对低些。
  • 只对使用表锁的存储引擎有效，不要在innodb或者其他有细粒度锁机制和并发控制的引擎中使用。
• DELAYED
• • 这个提示对insert，replace有效。mysql会将使用该提示的语句立即返回给客户端，并将插入的行数据放入到缓冲区，然后在表空间时批量将数据写入。
  • 日志系统使用这样的提示非常有效，或者是其他需要写入大量数据但是客户端却不需要等待单条语句完成I/O的应用。这个用法有一些限制：并不是所有的存储引擎都支持这样的做法；并且该提示会导致函数LAST_INSERT_ID无法正常工作。
• USE INDEX,IGNORE INDEX ,FORCE INDEX

  慢查询分析

---

  1 show status 了解各SQL的执行频率   默认使用参数为，session；可以使用global；

mysql> show status like ‘com%‘;
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Com_admin_commands        | 0     |
| Com_assign_to_keycache    | 0     |
| Com_alter_db              | 0     |
| Com_alter_procedure       | 0     |
| Com_alter_server          | 0     |
| Com_alter_table           | 0     |

com_xxx表示每个xxx语句执行的次数： com_select: 执行select操作的次数，一次查询只累加一次； com_insert: 执行insert操作的次数，对于批量插入的insert操作，只累加一次； com_update: 执行update操作的次数 com_delete:  执行delete操作的次数   上面这些参数对于所有存储引擎的表操作都会进行累计。下面几个参数只是针对innodb存储引擎，累加算法也略有不同。   innodb_rows_read: select查询返回的行数 innodb_rows_inserted: 执行insert操作插入的行数 innodb_rows_updated: 执行update操作更新的行数 innodb_rows_deleted:  执行delete操作删除的行数   通过以上参数，很容易了解当前数据库的应用是以插入更新为主还是以查询操作为主，大致的读写比例是多少；   可以通过com_commit 和 com_rollback 可以知道，事务回滚的比例； 如果比例过高则说明应用编写存在问题；   connections:  试图连接mysql服务器的次数 uptime:  服务器工作时间 slow_queries: 慢查询的次数；   2 定位低效 SQL  

• 慢查询日志，定位低效SQL；long_query_time，慢查询的标准时间；
• 慢查询是，查询结束之后才记录；因此他不是实时的；show processlist 查看mysql在进行的线程，查看线程的一些状态，可以实时地查看SQL的执行情况；

  3 explain分析低效查询SQL的执行计划  

mysql> explain select b from t where a =1;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | t     | ref  | a             | a    | 5       | const |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

 

当然explain也可以来查询使用了什么索引；  

• select_type
• • simple:简单表，即不使用表链接或者子查询
  • Primary：主查询，即外层的查询
  • union：union中的第二个或者后面的查询语句
  • subquery: 子查询中的第一个select
• table：输出结果集的表
• type：访问类型
• • all，全表扫描
  • index，索引全扫描
  • range，索引范围扫描，常见于< , >,between
  • ref,使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行
  • eq_ref,类似ref,区别在于使用了唯一索引；
  • const/system，表中最多有一个匹配行；Primary key 或 unique index;
  • null,不用访问表或者索引就可以得到结果
• possible_keys：表示查询时可能使用的索引
• key: 表示实际使用的索引
• key_len: 使用到索引字段的长度
• rows：扫描行的数量
• extra：执行情况的说明和描述；

  使用explain extended，可以得到更清晰易读的SQL，多出了warning，可以进一步分析SQL；   当然如果表 有分区，那么使用explain partition 可以找到select到底是在哪个分区查询的；   4 show profile 分析SQL 查看mysql是否支持profile；

mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+

  查看profiling是否开启，默认关闭：

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)
 

 

开启profiling：

mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)

通过profile，我们能够更清楚地了解SQL执行的过程。 如何使用：  

mysql> select count(*) from payment;
+----------+
| count(*) |
+----------+
|    16049 |
+----------+
1 row in set (0.02 sec)

 

通过show profiles，找到对应SQL的 query id;

mysql> show profiles;
+----------+------------+------------------------------+
| Query_ID | Duration   | Query                        |
+----------+------------+------------------------------+
|        1 | 0.01064275 | select count(*) from payment |
|        2 | 0.00048225 | show databases               |
|        3 | 0.00015000 | show DATABASE()              |
|        4 | 0.00039975 | show tables                  |
+----------+------------+------------------------------+

通过show profile for query id ，分析具体的SQL； 能够看到执行过程中线程的每个状态和消耗的时间；

mysql> show profile for query 4;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000058 |
| checking permissions | 0.000009 |
| Opening tables       | 0.000050 |
| System lock          | 0.000008 |
| init                 | 0.000012 |
| optimizing           | 0.000005 |
| statistics           | 0.000012 |
| preparing            | 0.000010 |
| executing            | 0.000007 |
| checking permissions | 0.000132 |
| Sending data         | 0.000042 |
| end                  | 0.000007 |
| query end            | 0.000007 |
| closing tables       | 0.000005 |
| removing tmp table   | 0.000009 |
| closing tables       | 0.000006 |
| freeing items        | 0.000015 |
| logging slow query   | 0.000005 |
| cleaning up          | 0.000006 |
+----------------------+----------+
19 rows in set (0.00 sec)

在获取到最消耗时间的线程状态后，mysql支持进一步选择all,cpu,block io ,context switch,page faults等明细类型来查看mysql在使用什么资源上耗费了过高的时间。   例如选择查看cup的消耗时间：

mysql> show profile cpu for query 4;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting             | 0.000058 | 0.000000 |   0.000000 |
| checking permissions | 0.000009 | 0.000000 |   0.000000 |
| Opening tables       | 0.000050 | 0.000000 |   0.000000 |
| System lock          | 0.000008 | 0.000000 |   0.000000 |
| init                 | 0.000012 | 0.000000 |   0.000000 |
| optimizing           | 0.000005 | 0.000000 |   0.000000 |
| statistics           | 0.000012 | 0.000000 |   0.000000 |
| preparing            | 0.000010 | 0.000000 |   0.000000 |
| executing            | 0.000007 | 0.000000 |   0.000000 |
| checking permissions | 0.000132 | 0.000000 |   0.000000 |
| Sending data         | 0.000042 | 0.000000 |   0.000000 |
| end                  | 0.000007 | 0.000000 |   0.000000 |
| query end            | 0.000007 | 0.000000 |   0.000000 |
| closing tables       | 0.000005 | 0.000000 |   0.000000 |
| removing tmp table   | 0.000009 | 0.000000 |   0.000000 |
| closing tables       | 0.000006 | 0.000000 |   0.000000 |
| freeing items        | 0.000015 | 0.000000 |   0.000000 |
| logging slow query   | 0.000005 | 0.000000 |   0.000000 |
| cleaning up          | 0.000006 | 0.000000 |   0.000000 |
+----------------------+----------+----------+------------

show profile 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了；   而mysql5.6则通过trace文件进一步向我们展示了优化器是如何选择执行计划的。   5 通过trace 分析优化器如何选择执行计划   提供了对SQL的跟踪trace，通过trace文件能够进一步了解为什么优化器选择A执行计划而不选择B执行计划，帮助我们更好地理解优化器的行为。   使用方式： 首先打开trace，设置格式为json，设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能完整显示。   然后执行select；   最后在，information_schema.optimizer_trace中查看跟踪文件；    索引问题

---

  索引是数据库优化中最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的SQL性能问题。    索引的存储分类 索引是在mysql的存储引擎层中实现的，而不是在服务器层实现的。

• B-Tree 索引：大部分引擎都支持B-Tree索引
• HASH索引：只有memory引擎支持，使用场景简单。
• R-Tree索引：空间索引，Myisam引擎的一个特殊索引类型，主要用于地理空间数据类型
• Full-text：全文索引

  前缀索引，大大缩小索引文件的大小，但是在order by 和 group by 操作的时候无法使用前缀索引。    查看所有使用情况

mysql> show status like ‘Handler_read%‘;
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 6     |
| Handler_read_last     | 0     |
| Handler_read_next     | 16050 |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 297   |
+-----------------------+-------+
7 rows in set (0.00 sec)

• Handler_read_key :值高，证明索引正在工作；值低，说明增加索引得到的性能改善不高，因为索引不经常使用
• Handler_read_rnd_next：值高，意味着查询效率低，应该建立索引补救；

  优化方法

---

  定期分析表和检查表   分析表：

mysql> analyze table store;
+--------------+---------+----------+----------+
| Table        | Op      | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| sakila.store | analyze | status   | OK       |
+--------------+---------+----------+----------+
1 row in set (0.00 sec)

本语句用于分析和存储表的关键字分布，分析的结果将可以使得系统得到准确的统计信息，是的SQL能够生成正确的执行计划。 如果用户感觉实际执行计划并不是预期的执行计划，执行一次分析表可能会解决问题。   检查表：

mysql> check table store;
+--------------+-------+----------+----------+
| Table        | Op    
                        

                            

                                
< 上一篇
mybatis_SQL映射(2)
                            
                            

                                 
下一篇 >
使用shell操作mysql