1、问题及数据

1.1、涉及到提醒单的SQL查询优化无力。

select count(*)
  from remind_bill
 where is_deleted= 0
   and group_id= 10545724654284683189
   and remind_type= 0
   and state= 0
   and(id_own_org in(10546172455175872464, 10907434497490929133, 10546443563656228053, 10546443563688497585, 11240984669917035533, 11240984669916854029, 11240984669917046024, 11240984669917046025, 10907434497444308271, 10546172455159754217, 10546050787350194851, 10545724654285435665, 10546443563727532329, 10546443563681314151, 10545724654285433611, 10545724654301013514, 10546443563674535370, 10546443563951056968, 10907434497418880888, 10907434497457246467, 10545724654297182142, 10546443563843224093, 10907434497420505040, 10907434497348682291, 10545724654285431864, 10545724654285432888, 10546050787350196051, 10907434497357402047, 10907434497470850803, 10546172455222800352, 10907434497378606711, 11240984669916797520, 11240984669917045590, 10545724654285433927, 10545724654294798264, 11240984669917045591, 10545724654285435972, 10546172455177448351, 10907434497493913040, 10546172455169452312, 10546443563693481390, 10691192243927853249, 10545724654300417571, 10546050787357371709, 10907434497334641736, 10546443564050948214, 10546443563706648555, 10546443564025546476, 10546443563701603420, 10907434497355836924, 10546443563765241972, 11240984669916906091, 10546050787350196607, 10546443563730997650, 10546443563808717302, 10546443563656232700, 11240984669917044087, 11240984669917034608, 10546443563682941047, 10546443563951281274, 11240984669916926586, 10545724654285437233, 10907434497435970332, 10545724654284683060, 11240984669917044088, 10907434497450373186, 10545724654285432442, 11240984669916795520, 10545724654285436537, 10907434497415854333, 10907434497301174552, 10545724654290417360, 10546443563673467473, 11240984669917044619, 10545724654285436809, 10546443563743386482, 10907434497311671722, 10907434497409259937, 10545724654285435012, 10545724654285433232, 10545724654301013341, 10546172455216330452, 11240984669916837022, 10545724654285436249, 10546443563687526479, 10546443564050351795, 10907434497455461159, 10546172455222804231, 11240984669916837546, 10546172455151212229, 10546443563686270699, 10546443563871165962, 10545724654285431400, 10546050787357371216, 10546172455177450556, 10907434497432600071, 10546443563928563347, 11240984669916922041, 10691192243899167765, 10546443563789742975, 10546443564017185040, 11240984669916836546, 10545724654285435318, 10907434497477711336, 10545724654285430464, 10545724654285430975, 10546050787357375177, 10545724654301013166, 10546172455177449497, 10907434497420630891, 10546050787357374387, 10907434497378599148, 10907434497319569762, 10907434497465670838, 11240984669916899548, 10907434497472394386, 10546443563665237491, 10907434497490337095, 10907434497475453784, 10546443563665242864, 10546443563712177468, 10546443564052747737, 10546443563661313351, 10546443563828292153, 10907434497341571632, 11240984669917037033, 10546050787357372333, 10546443563745965622, 10545724654285434536, 10546172455151212939, 10546172455169450621, 10907434497475442254, 10546443563918994915, 10546050787355528368, 11240984669916877048, 11240984669916907516)
    or car_id_own_org in(10546172455175872464, 10907434497490929133, 10546443563656228053, 10546443563688497585, 11240984669917035533, 11240984669916854029, 11240984669917046024, 11240984669917046025, 10907434497444308271, 10546172455159754217, 10546050787350194851, 10545724654285435665, 10546443563727532329, 10546443563681314151, 10545724654285433611, 10545724654301013514, 10546443563674535370, 10546443563951056968, 10907434497418880888, 10907434497457246467, 10545724654297182142, 10546443563843224093, 10907434497420505040, 10907434497348682291, 10545724654285431864, 10545724654285432888, 10546050787350196051, 10907434497357402047, 10907434497470850803, 10546172455222800352, 10907434497378606711, 11240984669916797520, 11240984669917045590, 10545724654285433927, 10545724654294798264, 11240984669917045591, 10545724654285435972, 10546172455177448351, 10907434497493913040, 10546172455169452312, 10546443563693481390, 10691192243927853249, 10545724654300417571, 10546050787357371709, 10907434497334641736, 10546443564050948214, 10546443563706648555, 10546443564025546476, 10546443563701603420, 10907434497355836924, 10546443563765241972, 11240984669916906091, 10546050787350196607, 10546443563730997650, 10546443563808717302, 10546443563656232700, 11240984669917044087, 11240984669917034608, 10546443563682941047, 10546443563951281274, 11240984669916926586, 10545724654285437233, 10907434497435970332, 10545724654284683060, 11240984669917044088, 10907434497450373186, 10545724654285432442, 11240984669916795520, 10545724654285436537, 10907434497415854333, 10907434497301174552, 10545724654290417360, 10546443563673467473, 11240984669917044619, 10545724654285436809, 10546443563743386482, 10907434497311671722, 10907434497409259937, 10545724654285435012, 10545724654285433232, 10545724654301013341, 10546172455216330452, 11240984669916837022, 10545724654285436249, 10546443563687526479, 10546443564050351795, 10907434497455461159, 10546172455222804231, 11240984669916837546, 10546172455151212229, 10546443563686270699, 10546443563871165962, 10545724654285431400, 10546050787357371216, 10546172455177450556, 10907434497432600071, 10546443563928563347, 11240984669916922041, 10691192243899167765, 10546443563789742975, 10546443564017185040, 11240984669916836546, 10545724654285435318, 10907434497477711336, 10545724654285430464, 10545724654285430975, 10546050787357375177, 10545724654301013166, 10546172455177449497, 10907434497420630891, 10546050787357374387, 10907434497378599148, 10907434497319569762, 10907434497465670838, 11240984669916899548, 10907434497472394386, 10546443563665237491, 10907434497490337095, 10907434497475453784, 10546443563665242864, 10546443563712177468, 10546443564052747737, 10546443563661313351, 10546443563828292153, 10907434497341571632, 11240984669917037033, 10546050787357372333, 10546443563745965622, 10545724654285434536, 10546172455151212939, 10546172455169450621, 10907434497475442254, 10546443563918994915, 10546050787355528368, 11240984669916877048, 11240984669916907516)

1.2、数据

1.2.1、服务提醒

每周慢查询249条，平均耗时1.3s。
10545724654284683189（兔师傅）、10546050787360507919（西安恒泰汽车服务有限公司）、10545511425563128304(一番车道) 这三家公司触发慢查询次数最多。
RemindBillReadFacade@queryRemindBillCount 近三个月平均响应时间：12.7ms 请求数：928.3K
RemindBillReadFacade@queryRemindBillUnCompletedCount 近三个月平均响应时间：68.3ms 请求数：374.8K
ServiceRemindBillReadFacade@queryServiceRemindBillList 近三个月平均响应时间：28.7ms 请求数：101.05K
服务提醒单现有数量：2464000条（百万级别）、服务提醒单操作日志现有数量：2371677条（百万级别）
服务提醒单每日新增数量：5096条（千级别）、服务提醒单操作日志每日新增数量：10022条（万级别）

从上面数据可以看出，本次优化主要解决兔师傅等门店较多的大客户的用户体验问题。

1.2.2、未迁移提醒数据

保险提醒现有数据：2678857条（百万级别），每日增量：10246条（万级别）
特殊人群提醒现有数据：9695180条（近千万级别），每日增量：100354条（十万级别）

如果所有提醒都迁移完成后，数据量剧增，到时候必须要做分库分表和对接ES,否则可能会影响正常的功能使用。

1.2.3、根据groupId分表后提醒单数据

group_id	服务提醒单数量
10545724654284683189	327544
10546172455174347299	74656
10545511425563128304	51467
10546050787360507919	42275
10545360219000198060	37992
10546172455173835772	32988
10546443563683618514	28356
10546050787353579790	22154
10545511425564218169	17932
10546172455150344939	12469

id_own_group	保险提醒单数量
10546050787360507919	149208
10546443563846901705	147508
10545511425563128304	101822
10546172455173835772	88605
10545360219000198060	73165
10546172455216487588	63005
10546172455174347299	59283
10546443563834764203	38264
10546050787353579790	28588

id_own_group	特殊人群提醒单数量
10545055917999668983	2174549
10546443563846901705	1884708
10545360219000198060	1078077
10545435426611755185	511770
10546443563710234093	356497
10546443563713262850	247156
10546443563754919092	243911
10546443563959457579	209337
10546172455194190154	182971

2、问题解决价值

1.1、实际客户价值

优化兔师傅等门店较多客户的用户体验，进入营销看板及服务提醒页面速度快，体验好。
所有提醒数据模型统一后，可以深挖数据，创造对客户更有价值的信息。
提醒单数据量特别多时，不影响客户正常使用提醒功能

统一模型后深挖数据例子：

客户快过生日了，且有个保养需要提醒，这时候可以给客户定制化提醒：比如因为客户生日快到了，专门为他定了了一个活动，来保养送xxx套餐卡之类。

1.2、研发团队价值

解决慢查询问题，提升mysql巡检得分
所有提醒数据模型统一，便于管理
提醒数据量增大后，不用因为优化接口问题而浪费开发人力资源

3、两个解决方案对比

3.1、分库分表

3.1.1、优点

代码侵入性小，改动小，测试范围小。
提醒单数量查询效率提升
提醒单列表查询效率提升

3.1.2、缺点

按照公司分表，门店多的公司数据过多，数据倾斜。
所有提醒迁移完成后，数据量剧增，查询提醒单会有性能风险。
数据迁移问题
兔师傅等拥有门店比较多的客户，对于标题1的问题，依然解决不了。

3.2、对接ES

3.2.1、优点

所有提醒迁移完成后，数据量剧增，查询提醒单性能不会影响太大
ES大数据量数据统计性能较好
提醒单查询时，附加信息不必重新查询操作日志获取，可以直接走es,查询效率应有明显提升。
对于问题1，对于or查询支持较好，可解决问题。

3.2.2、缺点

学习成本高
代码侵入性大，改动大，测试范围大。
查询接口改造

3.2.3、mapper查询语句改造分析

3.2.3.1、分库分表

不做service改动只修改mapper查询语句，在mapper层切换分库分表数据源

需排查提醒单相关sql,Sharding-JDBC的语法并不是支持所有普通JDBC的语法,参考：https://shardingsphere.apache.org/document/legacy/4.x/document/cn/features/sharding/use-norms/sql/

3.2.3.2、对接ES

首先根据条件查询es，获取返回的提醒单主键id等，批量查询信息。原有接口出入参不变，需要改动service。

3.2.3.3、分析

1）如果先分库分表，修改mapper，切换代理。比如:

还需修改Sharding-JDBC不支持的语法。

1	select a,b,c from reming_bill where id_own_org in (xxx) //走分库分表逻辑

再对接es,需要新增mapper，原有的sql废弃，比如：

1	select c from reming_bill where id in (xxx) //走分库分表逻辑

其中查询的a,b从搜索中获取，c从数据库获取。

2）如果先接es,新增mapper。比如：

1	select c from reming_bill where id in (xxx) //走单表逻辑

再分库分表，切换代理，sql不必修改。

4、优化思路

根据第3步分析，短期内，先做分库分表可以提升各种查询的效率，且改动范围小，影响范围也小。

但是不能解决标题1的问题，目前服务提醒单每天增量数据5千，分表意义不是很大。

对接ES虽然耗时长，但是能解决现有问题（兔师傅查询耗时太长）。

对于代码改造，如果先分库分表再对接ES,mapper层需要改两次，如果先对接es，再分库分表，mapper层只需要改一次。

因为马上要在提醒的基础上做商机，所以表结构难免会修改，商机做完，预计提醒单数据也就达到300万，所以分库分表可以先不做。

对接搜索应该提前做，否则做商机的时候开发工作量太大，对接搜索时间太紧张。

所以提醒单优化计划分两步走：首先对接ES，之后分库分表。

如果先对接ES,写新的mapper时，需注意Sharding-JDBC的语法并不是支持所有普通JDBC的语法，要提前了解，规避。