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

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


场景1


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

在人员列表里手机号页面有做展示,那么点击跳转人员详情的时候,是可以把手机号通过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}

更追求极致一点,可以添加一个idphone的覆盖索引,避免回表。

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

表数据70万左右。

select * form table limit 10000

本周SQL优化实战分享-LMLPHP

select phone form table limit 10000

本周SQL优化实战分享-LMLPHP

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优化实战分享-LMLPHP

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

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

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

具体参照我写的这篇文章 https://juejin.cn/post/7234355976458518586


布尔模式的逻辑运算符


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

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

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

本周SQL优化实战分享-LMLPHP

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


场景2

还是常口表,列表查询。


排序


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

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

本周SQL优化实战分享-LMLPHP

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

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

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

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

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

然后耗时降到了3毫秒。

本周SQL优化实战分享-LMLPHP

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

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

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

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


深度分页


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

本周SQL优化实战分享-LMLPHP

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

本周SQL优化实战分享-LMLPHP

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


利用覆盖索引


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

本周SQL优化实战分享-LMLPHP

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

本周SQL优化实战分享-LMLPHP

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


利用覆盖索引延迟关联


先通过覆盖索引把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优化实战分享-LMLPHP


其它方式


其它方式,通过记录上次的位置,通过子查询,都只适用于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优化实战分享-LMLPHP

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


完。

05-26 01:12