本周SQL优化实战分享
分享一下本周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}
更追求极致一点，可以添加一个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识别为逻辑运算符。
具体参照我写的这篇文章 https://juejin.cn/post/7234355976458518586
布尔模式的逻辑运算符
+
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"格式 。
耗时从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.
完。