本文介绍了SQL查询优化技巧，包括等价替换LEFT JOIN为JOIN，避免ORDER BY在COUNT查询中，考虑业务场景是否需要ORDER BY和COUNT，以及如何避免在多表关联中使用SELECT *，减少DISTINCT关键字使用，利用SEARCH语法加速LIKE查询，并避免索引失效的各种情况。通过这些方法，可以提升SQL查询效率和性能。
等价替换left join / right join查询
多表的left join / right join关联查询性能较低，可以考虑等价替换为join的SQL语句或者是用逗号分隔各个表，然后用where条件替代on条件作为关联条件的方式，关联查询多表(目前也推荐用逗号分隔，where条件替代on条件的方式进行多表内连接查询)。
例如对于同时符合以下设定的场景下：
如果A表为主表，B为从表。
其中，出参包含AB两个表的数据，查询的条件可能来源于A，B表。
A表有记录，B表未必有记录。
B表的code字段与A表的主键id字段关联。
各个查询条件之间是AND关系。
那么，我们的查询SQL语句最直观地会写为：
select A.id, B.id from A left join B on A.id=B.code where ……但是这样的SQL语句在宽表引擎下并非性能最佳， 可以等价替换为如下：
如果查询条件来源于A,B两表，并且查询条件都是AND逻辑拼接。可以改为：
select A.id, B.id from A, B where A.id=B.code and ……如果查询条件只来源于A表，可改为使用以下的两条SQL：
select A.id from A where ……将以上查询获取得到的A表的id传入到下列B表的SQL，查询获取B表的出参。
select B.id from B where B.code=?如果查询条件只来源于B表，可以改为使用以下的两条SQL：
Select B.id, B.code from B where ……将以上查询获取得到的B表的code传入到下列A表的SQL，查询获取A表的出参。
select A.id from A where A.id=?
count查询不应该带order by
count查询的目的是为了返回符合条件的总记录数，但是order by则是对符合条件的记录进行排序，查询总数无需排序，排序则会增加非必要的耗时，应该在count查询语句中删除order by。
结合实际业务场景考虑是否在查询SQL中带有order by，count
如果模型中满足某条件的数据量很大，查询的SQL语句中带有order by，count过程非常缓慢。可结合自身业务场景考虑是否可以放弃。
如果业务上的 order by，count 不能删除，可以通过以下的方案进行优化：
涉及order by的优化
一般情况下，一个接口只需要一个order by，并且是对lastModifiedDate进行倒序查询输出的。
但是lastModifiedDate字段是频繁修改的字段，同时也是平台的对象模型中的必带字段，不适合建立索引，所以其为非索引字段。
例如，假设业务场景为：查询人员的页面上展示人员按照修改时间倒序排列，此时，如果需要新增一个人员进入系统。
此时，倒序排列最主要的目的应该是添加了一个人员，添加成功后可以看见自己添加的人员信息，保证自己的添加是成功的。这时候，我们可以通过调用addPerson接口添加人员，当addPerson调用添加成功后，不再调用queryPerson进行查询，而是在修改前的记录上进行view model的修改，从而实现页面数据变更的效果。此时，查询接口中即使不使用order by也可以满足添加了一条记录成功后，在页面最前面显示自己的那条记录。当页面再次刷新(即，再次调用queryPerson接口时，刚才添加的记录或许不会在当前页面展示)，但是无条件查询毕竟是模糊查询，如果需要精确查询刚才添加的记录，可以精确输入一些过滤条件进行查询，则可以直接获取得到刚才添加的记录。
涉及count的优化
由于count查询严重影响查询体验，所以可以考虑分情况讨论是否需要查询count。count一般用于前端分页展示选择页数，来进行分页选择查询。count查询的优化方案，将脚本查询接口的count输出改为可选输出，然后通过入参控制是否输出，如果需要输出再查询count，否则不返回count。
对于页面上调用脚本的查询接口时，先试探查询传入start=5000,limit=5001，首次查询先通过入参控制不查询count并且携带其他查询条件，如果返回记录不为空，则表示满足条件的记录大于5000条。
如果能查出一条记录则表示记录超过5000，则后续的正常分页查询也不查询count，只将分页查询的返回分页数据展示到页面上，页面也不展示count。页面也只可以翻页到0~5000的记录，5000以上的无法看。
如果查不到记录(表示满足条件的数量少于5000)。则可以正常返回count，将返回的count用于前端分页的总记录数量，此时返回的分页数据亦可以正常展示，用法与普通的分页查询用法一致。
多表关联不要使用select * 的方式返回对应字段的记录
由于多表关联字段的数量较多，如果是select * ，可能会返回很多不需要的字段记录，导致增加了非必要的数据传输。建议不要使用select * ，而是根据需要返回的字段一个个列出来，例如只需要id就写为select id。
非必要场景下，避免使用distinct关键字进行记录去重
如果确定了返回的记录不会有重复的，应该避免使用distinct关键字进行去重，distinct关键字在并发情况下性能较差。
单表的like查询用search语法来加速
如果待搜索的字段建立的时候勾选了“是否可搜索”的选项，如图1，则该字段会将其字段上的记录秒级同步到平台的缓存上(即可以使用search语法进行搜索)，该字段上的值在模型对象中的记录与平台缓存中的值是准同步的(两者相差1秒左右)。由于缓存上的搜索性能很高，体验较好，可以将耗时严重的单表特定字段的搜索(如，涉及到textArea类型的字段，平台没法建立数据库索引，勾选了该"是否可搜索"的选项)，将其对象模型的搜索修改为缓存上的搜索。
如果对象模型中的部分字段勾选了"是否可搜索"，部分字段未勾选该选项，但是对于单表搜索的条件既涉及到可搜索字段，也涉及到不可搜索字段时候，可以使用search语法进行搜索。对于search语法进行搜索，平台会判断实现可搜索的字段从缓存中筛选，普通字段从对象模型中搜索。由于可搜索字段在缓存与对象中的同步时间相差1秒左右，所以不可在操作完该字段的记录后即刻通过search查询，这样会出现部分字段(对象模型中非可搜索的字段)是新的，部分字段的值(可搜索的字段)是旧的，从而导致查询结果有误。
假设场景：假设PE_Person表中的code字段创建时勾选了“是否搜索”的选项，现在需要模糊搜索code字段包含'abc'，并且name字段的值为'xxx'的记录。其中code为勾选了"是否可搜索"的字段，name字段是未勾选"是否可搜索"的普通字段。
原SQL为：
select id from PE_Person where code like '%abc% and name='xxx''。可以优化为：
search id from PE_Person where code like '%abc%' and name='xxx'。图1 是否可搜索
SQL拼接应该尽可能避免可能导致使用不到索引的情况
平台SQL的where条件中的参数如果类型不匹配，会导致索引不生效。
在完整的SQL语句前加上explain for可以查看SQL语句的执行计划，从而判断该SQL是否使用索引查询。
例如，想要查看select id from PE_Person where name='abc'这条SQL语句的执行计划，那么可以在这条语句前面加上explain for，即执行 explain for select id from PE_Person where name='abc' 可以看到其执行计划。
在索引字段上使用like进行模糊查询匹配，like操作符后的值的左边带有%时：
例如，假设PE_Person对象模型中的name字段为索引字段。模糊查询PE_Person对象模型中的name字段中包含'bbb'的记录，其执行的SQL为select id from PE_Person where name like '%bbb%'，是不使用索引而进行全表扫描匹配记录的。
建议方案：
如果知道了待搜索的name的精确值，则使用精确的=号查询，如select id from PE_Person where name='abbbc'。
如果不知道的name的精确值，但是可以知道待搜索的name字段的前缀，则可以只使用右边的%左边精确填写，如select id from PE_Person where name like 'abbb%'。
数据类型出现隐式转化
如SQL语句中的where条件后的值，原本该字段上的数据类型为文本，但是查询的过程中传入的是数字，使得查询过程中出现了隐式转换，导致索引无效，从而全表扫描。查询应该保证查询条件的值应该与对象模型中的值的类型一致。
例如，select id from PE_Person where name=123，但是name在模型对象中是字符串类型。
应该改为select id from PE_Person where name='123'
在索引列上使用 IS NULL 或 IS NOT NULL操作
索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可。
例如：
explain for select name from de_devices where name is not null。使用or语句做SQL拼接
例如：
select id from PE_Person where id='0I05000000OFyd0pgSKO' or PersonName='张三'。对索引字段进行计算操作、字段上使用函数
例如，PE_Person模型对象中的字段PersonName为索引字段，其中我们希望搜索条件满足PersonName为'abc'的记录。如果写为如下的两条SQL语句，会导致无法使用索引。
select id from PE_Person where PersonName=lower('ABC') 或者 select id from PE_Person where lower(PersonName)='ABC'改进方案：
可以将函数处理放置在执行SQL前执行，执行SQL时候则不用包含相关函数。
例如以上的场景可以先将‘ABC’通过函数转换为'abc'再进行SQL语句查询：
select id from PE_Person where PersonName='ABC'对索引字段使用<>符号进行SQL查询
select name from PE_Person where id <> 'abc123'
-END-
更多相关文章
智慧园区升级公告—20.2版本
【精彩回顾】看看智慧园区高性能编码规范宣讲会都讲了什么？
共创行业新价值！华为全联接2020如约而至
华为中国生态之行2020河北峰会，智慧园区分论坛等你！
《智慧园区设计标准》第一次评审会暨第二次工作会议成功召开
智慧园区南向认证合作伙伴及设备清单
《未来智慧园区白皮书》已发布，附下载链接！
华为智慧社区：科技赋能，给您温暖的家！
扫一扫
1333
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
