本文概述了苍穹应用定制化开发中MySQL数据库的慢SQL优化问题。强调了查询优化在数据库性能中的重要性,指出通过优化查询的子任务可以减少响应时间。介绍了通过执行计划(EXPLAIN)来分析SQL性能的方法,列举了常见慢SQL案例如未建索引、索引列顺序不对、无效索引、模糊查询导致的索引失效等,并给出了相应的优化建议。总结强调了在开发中合理创建索引、利用执行计划、避免无意义查询和过多排序等策略来提升MySQL性能。
苍穹定制化开发慢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开发规范
如有疑问或者更好的建议,欢迎小伙伴留言或者私聊~
苍穹定制化开发性能优化指南系列(一).pdf(918.43KB)
推荐阅读