本周SQL优化实战分享

站长

2023年07月20日 22:41 · 阅读数 14

分享一下本周SQL优化的两个场景。如果能对读者有一定的启发，共同探讨，不胜荣幸。

版本信息：mysql,5.7.19 引擎: innodb

场景1

我们有一张常口表，里面的数据由各种数据源合并而来，所以人员可能有多个手机号其中还包括座机号。这点在这篇文章里也分享过。juejin.cn/post/723435… 现在人员详情页面需展示同手机号的人员列表，同手机号是包含，而非等同关系。

在人员列表里手机号页面有做展示，那么点击跳转人员详情的时候，是可以把手机号通过URL带过来的，但前端说参数过多，不好控制，所以只传递了人员ID参数。

所以后端查询的时候先得通过主键ID把手机号查出来。之所以不一次性通过join带出手机号再关联同手机号人员，是关联与被关联人员手机号都可能存在多个。

select * 还是select 指定字段

原通过主键查询手机号的SQL，是直接用的mybatis生成器自动生成的SQL。

<select id="selectPhoneByPrimaryKey" parameterType="java.lang.String" resultMap="BaseResultMap">
    select
    phone
    from t_person_info
    where ID = #{id,jdbcType=VARCHAR}
  </select>

Base_Column_List可想而知是全部字段，类似于select *,这本身没什么，但其中有一部份字段长度在几百，全部加起来也算是个大字段，全部提取对效率还是有一定的影响，所以改为select phone 查询手机一个字段。

select
    phone
    from t_person_info
    where ID = #{id,jdbcType=VARCHAR}

勘误：聚集索引的叶子节点包含完整的行数据，所以这里通过主键ID查phone本身就不需要回表。 -- 更追求极致一点，可以添加一个id和phone的覆盖索引，避免回表。

这一点的优化相对比较鸡肋，都在1-2ms之间看不出明显差别，但把limit放大的时候，还是能看出差距。

表数据70万左右。

select * form table limit 10000

本周SQL优化实战分享

select phone form table limit 10000

本周SQL优化实战分享

174ms vs 7ms

确实是聊胜于无。但是到底是select * 还是select 指定字段，确实还是存在着一些争议。

一般情况下，表字段少，且不存在大字段，用select * 确实能减少许多麻烦，加减字段不用改sql，多个查询子功能可以共用等。而且，页面查询多是分页，不太可能一下子查询10000条这种情况。

占用内存，不必要的IO，增加网络负担，拒绝覆盖索引，确实也是select *的问题。

我觉得需要根据具体情况，自行判断，没必要太过教条。

全文检索

拿到手机号以后，根据手机号去查询关联人员。因为是包含关系，所以同事一开始用的是like模糊匹配。

 select p.id, p.id as pid,p.name,p.idcard,p.phone,count( w.EVENT_NO ) AS count 
      from t_person_info p 
      left join t_other w on w.pid = p.ID
        where
            <foreach collection="phones" item="phone" separator="or" open="(" close=")">
                p.phone like concat("%",#{phone},"%")
            </foreach>
         and p.id != #{id}
        group by p.id

这里的!=有可能会导致索引失效，这时候可以在sql去掉，然后在代码中过滤掉当前人员。

因为where条件中有 p.id != #{id},执行计划倒是从从ALL上升到了range。耗时1.5秒。

本周SQL优化实战分享

将phone加上全文索引。 where 条件改为

match(p.phone) against (#{phones} IN boolean MODE) and p.id != #{id}

每个手机号需要全匹配，所以这里使用布尔模式，因为手机号有多个，需要做到or，又因为涉及到座机号，其中带的-可能会被mysql识别为逻辑运算符。

具体参照我写的这篇文章 juejin.cn/post/723435…

布尔模式的逻辑运算符

+ select * from t_user where match(phone) AGAINST('a +b' in boolean mode) 其中 + 会被识别成逻辑运算符，而不是将a +b作为一个整体，以下同理。 'a +b' 指'a'和'b'必须同时出现才满足搜索条件。
- select * from t_user where match(phone) AGAINST('0797 -12345' in boolean mode) 0797 -12345指0797必须包含，但不包含12345才能满足搜索条件。以下查询排除了包含0797-12345的记录。注意-前后空格 0797 -12345才表示包含0797 同时不包含12345. 0797-12345等于0797 - 12345，它并不等于0797 -12345。有图为证：
> < 提高/降低该条匹配数据的权重值。不管使用>还是 <，其权重值均大于没使用其中任何一个的。 select * from t_user where match(phone) AGAINST('0797(>94649 <12345)' in boolean mode) 表示匹配0797,同时包含94649的列往前排，包含12345的往后排 select * from t_user where match(phone) AGAINST('a > b' in NATURAL LANGUAGE mode)
() 相当于表达式分组，参考上一个例子。
* 通配符，只能在字符串后面使用
" 完全匹配，被双引号包起来的单词必须整个被匹配。 select * from t_user where match(phone) AGAINST('"0797-1789"' in boolean mode) "0797-1789"中不可再分。其它包含0797-1234等记录就不再匹配。
空格表示 or

这里使用6，7来解决上述的两种问题。如下SQL，与以下4个手机号其中一个全区配的人员都将被筛选出来。

#{phone}参数应为"135****6" "136****9" "1387****2" "0791-123"格式。

本周SQL优化实战分享

耗时从1.5秒降到了2毫秒。

场景2

还是常口表，列表查询。

排序

每个用户呢会关联一些事件，无需理会什么是事件，反正这张表中的每条记录与事件表形成一对多的关联关系。事件实时进入。然后再用户列表展示的时候需要根据关联的事件数来进行排序。

实时join关联事件表，耗时4.9秒。 sql执行计划 extra为 Using temporary; Using filesort 产生了临时表和IO文件排序。当然快不起来。

本周SQL优化实战分享

这还是在没有查询条件，以及没有深度分页的情况下。

那么很明显，需要在用户表建一个冗余字段，保存用户所关联的事件数，再对这个字段建立索引。

但这会牺牲一定的实时性。以及需要定时任务去统计用户的关联事件数。

然后需要跟产品沟通，因为我们的产品是2B的，还需要跟客户进行沟通。

结合我们的业务场景，经过我们的努力沟通，客户认为牺牲适当的实时性，换来页面的响应效率，是值得的。

然后耗时降到了3毫秒。

本周SQL优化实战分享

一旦 where having order by 里的字段是通过max,min,count等计算出来的虚拟字段，那么肯定会产生 Using temporary; Using filesort 临时表和IO文件排序。要想办法消灭，不管从业务还是技术上。

适当的建立冗余字段，或者宽表。

但阿里巴巴java开发手册，禁止3张表以上的关联，毕竟只是比较理想的状态。

幸福的公司都是相似的；不幸的公司我看也有相似不幸。不外乎难搞的产品，多变的客户，睿(s)智(13)的老板。

深度分页

上面小节同样的sql,首页查询只需耗时2ms，但是到了700000以后，耗时达到了2.6秒。

本周SQL优化实战分享

这就是著名的mysql深度分页的问题。通过执行计划，可以明显的看出，mysql会将前 700015条数据取出来，然后丢掉前700000条，只取后15条数据。前面读取的700000条数据是不必要耗时操作。

本周SQL优化实战分享

解决深度分页的方式有几种。看具体情况，没有通用的办法。

利用覆盖索引

或者叫利用不回表。这里为了便利，用主键索引id来演示，innodb下，主键索引为聚簇索引，本身就是回表啦，相当于普通索引省掉了回表操作。

本周SQL优化实战分享

如此查询只需200毫秒左右。

本周SQL优化实战分享

但是，这里不合适把需要展示的字段全部建成一个覆盖索引。

利用覆盖索引延迟关联

先通过覆盖索引把id拿到，再把这15条数据去关联一次拿到其它字段不就好了吗？

select p.id ,p.name,p.idcard,p.phone
from t_person_info p
inner join (select id from t_person_info order by EVENTCOUNT desc limit 700000,15) p2 on p.id = p2.id

如此同样只需要200毫秒左右。

本周SQL优化实战分享

其它方式

其它方式，通过记录上次的位置，通过子查询，都只适用于id为自增主键的情况。

不适用我的这个业务场景。

类似于这样的SQL

select id ,name,idcard,phone,EVENTCOUNT from t_person_info where id <=(select id from t_person_info order by EVENTCOUNT limit 700000, 1) limit 15;

由于历史友商等原因，我们的数据ID有部份是UUID，它是不连续的，且人员关联事件数EVENTCOUNT也不连续，大量的人员集中在某一个数量上，这都使得此种方式不可取。

分页插件

在做列表展示时肯定需要分页，分页就需要查询总数。分页插件pagehelper默认会生成一个查询总数的方法。

假如mapper查询方法为selectList(),那么查询总数的方法名为selectList_COUNT()。对应的SQL为SELECT count(0) FROM 原sql

在一些比较比较简单的SQL的时候，分页的SQL还是会进行重写，比较去掉多余的select字段，不必要的排序等。

但当SQL比较复杂的时候，那就是直接在原SQL上包一层select count(0)。

本周SQL优化实战分享

这个时候我们就可以自已去实现这个selectList_COUNT()这个方法，让它执行效率更高的自定义SQL.

完。