苍穹定制化开发慢SQL优化 

1 前言

作为苍穹应用定制化开发者，苍穹标配MySQL数据库毫无疑问是一门必修课。系统的稳定、高效、高并发等指标，很大程度上取决于数据库性能是否够优，可见性能优化的重要性。谈到MySQL性能优化，查询优化作为优化的源头，它也是最能体现一个系统是否够快。希望该系列文章能够给你带来收获，让你更系统、更全面的掌握MySQL性能优化的技能、技巧。本篇从“为什么查询速度这么慢”开始谈起，让你能够清楚的知道查询可能会慢在哪些环节，这样将有助于你更好的优化查询，做到“心中有数，高人一筹”。

2 为什么查询这么慢

真正衡量查询速度的是响应时间。如果把查询看作是一个任务，那么它是由一系列子任务组成的，每个任务都会消耗一定的时间。如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行的更快。

MySQL在执行查询的时候，有哪些子任务，哪些子任务花费的时间最多？ 这就需要借助一些工具，或者一些方法(如：执行计划)对查询进行剖析，来定位发现究竟慢在哪。

对于MySQL，最简单衡量查询开销的三个指标如下： 

• 响应时间

• 扫描的行数

• 返回的行数

没有哪个指标能够完全来衡量查询的开销，但它们能够大致反映MySQL内部执行查询时需要访问多少数据，并可以大概推算出查询运行的实际。 这三个指标都会记录到MySQL的慢日志中，所以检查慢SQL记录是找出扫描行数过多查询的办法。

目前苍穹慢sql指标定义：在一定时间范围内，执行时间超过3s或扫描行数大于10W且达到一定次数的sql。

3 通过执行计划分析SQL

评估查询开销的时候，需要考虑一下从表中找到某一行数据的成本。 MySQL有好几种访问方式可以查找并返回一行结果。这些访问方式可能需要访问很多行才能返回一条结果，也有些访问方式可能无需扫描就能返回结果。

通过执行计划能够帮助我们更加明确的来进行SQL优化。执行SQL之前，使用SQL执行计划（如：EXPLAIN SELECT * FROM DEMO）检查脚本性能。根据执行计划找到存在性能问题的SQL语句，以帮助我们优化SQL提供方向和依据。

EXPLAIN的查询结果还会告诉我们索引主键是如何被利用的，数据表是如何被搜索或排序的等等。

语法格式是：EXPLAIN SELECT语句;

在执行计划EXPLAIN语句中的type列反映了访问类型。访问类型有很多种，从全表扫描到索引扫描，范围扫描，唯一索引，常数索引等。这里列的这些，速度是从慢到快，扫描的行数也是从多到少。

4 苍穹定制化慢SQL案例分析

本节通过公有云苍穹平台定期扫描定制化开发SQL执行计划，筛选出一些慢SQL，分析其慢查询原因。

4.1 未建索引

案例：二开表单或标准单据新增二开字段字段未建索引。

【慢SQL语句】

【执行计划】

【原因分析】

执行计划ftype=ALL显示该SQL将遍历全表以找到匹配的行，未通过索引查询，导致全表扫描。

【优化建议】二开字段fk_xyzn_orgfield，fk_xyzn_orderdate分别创建索引。

4.2 索引列顺序不对

案例：gl凭证表中有字段创建了索引，但索引失效。

【慢SQL语句】

【执行计划】

【原因分析】

凭证表虽然创建了联合索引，查询条件也使用了fk_cqcy_bookeddata和forgid，但执行计划fkey被标记为PRIMARY,显示查询通过主键索引，并没有通过联合索引，联合索引失效。

【优化建议】

调整联合索引的顺序或是建立fk_cqcy_bookeddata和forgid联合索引。对于此类问题一般为单据列表的查询，查询条件在客户使用时一般是任意组合的，所以对于高频查询的字段且重复率低的一般也可单独建立索引。

4.3 无效索引

案例：单据列表查询，数据量较大，重复率高，加索引无效。

【慢SQL语句】

【原因分析】

fenable字段重复率高，加索引无效，导致全表扫描。

【优化建议】

加条件过滤缩小范围，默认加时间等字段可减少查询的数量，去掉默认排序，使用平台提供的sqlquery方式。

4.4 模糊查询、索引失效

案例：单据列表查询，使用了模糊查询，索引失效

【慢SQL语句】

【原因分析】

本例fk_keas_e_orderid加了索引，由于该表数据量大，使用了like，执行时间依然超过3s。

【优化建议】

如果客户允许或者是查询的都是以某些特殊字段开头的，如上面的例子都是以MAKMGA开头的，那么调整单据查询的模糊查询方式，确保是左匹配方式能使用到索引。

4.5 where与order by语句，使用了不同的索引

案例：支付单据使用了fistop和fbillno进行排序，但是没有创建索引排序

【慢SQL语句】

【执行计划】

【原因分析】

查询中使用了order by排序，但是待排序的内容没有由所使用的索引直接完成排序，mysql有可能要进行文件排序（在执行计划中出现using filesort的情况）

注：using filesort不一定引起mysql的性能问题。但如果查询次数非常多，那么每次在mysql中进行排序，还是会有影响的。

【优化建议】

创建fistop和fbillno索引，使用mysql索引，将待排序的内容放到索引中，直接利用索引排序。

4.6 无意义的SQL查询

案例：物料基础资料fnumber和fname字段在物料中都不会为空，此查询无意义。

【慢SQL语句】

【优化建议】

应该结合实际业务情况，避免无意义的sql查询。

5 总结

（1）二开表单或标准单据中新增二开字段，涉及到高频查询一定要考虑索引，避免全表扫描；

（2）开发过程中应该善于分析执行计划，在开发环境下可查询执行计划是否准确，合理创建索引；

（3）大表数据量的查询，应该增加默认过滤条件，尽量避免没有过滤条件或是极少的过滤条件；

（4）善于利用苍穹平台提供的方案优化，如sqlquery方式、模糊查询的优化等；

（5）非必须要求，不使用排序，避免出现文件排序；

（6）创建索引建议：

• 非频繁引用和更新的列不用创建索引，过多的索引会影响插入和更新速度而影响整体性能，一般索引数量不超过5个；

• 选择性高的字段建索引，数据重复且分布平均的字段，建索引效果不大；

• 注意组合索引的顺序，利用索引的最左原则，最常使用的字段放前面，区分度最大的字段放在前面；

• 尽量使用复合索引，而不是添加新的索引，避免冗余索引，如(a,b,c)，(a,b) ,(a)；

• 尽可能建普通索引而不是唯一索引。

下载路径：云之家企业云盘

企业文件->【苍穹平台】共享资料库05->定制化开发标准化工具包->03开发规范

如有疑问或者更好的建议，欢迎小伙伴留言或者私聊~