本周SQL优化实战分享

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

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

场景1

咱们有一张常口表，里边的数据由各种数据源合并而来，所以人员可能有多个手机号其间还包括座机号。这点在这篇文章里也分享过。/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

select phone form table limit 10000

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秒。

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

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

每个手机号需求全匹配，所以这儿运用布尔形式，
由于手机号有多个，需求做到or，
又由于涉及到座机号，其间带的-可能会被mysql识别为逻辑运算符。

详细参照我写的这篇文章 /post/723435…

布尔形式的逻辑运算符

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 -12345指0797有必要包括，但不包括12345才干满意查找条件。
   以下查询排除了包括0797-12345的记载。
   

   

   留意-前后空格 0797 -12345才表明包括0797 同时不包括12345. 0797-12345等于0797 - 12345，它并不等于0797 -12345。
   有图为证：
   

   

   
   

   

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)
   

   

4. () 相当于表达式分组，参考上一个比如。
5. *
   通配符，只能在字符串后面运用
6. " 完全匹配，被双引号包起来的单词有必要整个被匹配。 select * from t_user where match(phone) AGAINST('"0797-1789"' in boolean mode) "0797-1789"中不可再分。其它包括0797-1234等记载就不再匹配。
   

   

7. 空格表明 or

这儿运用6，7来处理上述的两种问题。
如下SQL，与以下4个手机号其间一个全区配的人员都将被筛选出来。

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

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

场景2

仍是常口表，列表查询。

排序

每个用户呢会相关一些事情，无需理睬什么是事情，横竖这张表中的每条记载与事情表构成一对多的相关关系。
事情实时进入。然后再用户列表展现的时分需求依据相关的事情数来进行排序。

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

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

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

但这会献身必定的实时性。
以及需求守时任务去计算用户的相关事情数。

然后需求跟产品沟通，由于咱们的产品是2B的，还需求跟客户进行沟通。

结合咱们的事务场景，经过咱们的尽力沟通，客户以为献身恰当的实时性，换来页面的呼应效率，是值得的。

然后耗时降到了3毫秒。

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

恰当的树立冗余字段，或许宽表。

但阿里巴巴java开发手册，禁止3张表以上的相关，究竟仅仅比较抱负的状况。

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

深度分页

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

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

处理深度分页的办法有几种。 看详细情况，没有通用的办法。

使用掩盖索引

或许叫使用不回表。
这儿为了便当，用主键索引id来演示，innodb下，主键索引为聚簇索引，本身便是回表啦，相当于一般索引省掉了回表操作。

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

但是，这儿不合适把需求展现的字段悉数建成一个掩盖索引。

使用掩盖索引推迟相关

先经过掩盖索引把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毫秒左右。

其它办法

其它办法，经过记载前次的方位，经过子查询，都只适用于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)。

这个时分咱们就能够自已去完成这个selectList_COUNT()这个办法，让它执行效率更高的自定义SQL.

完。