背景：某工单表 ，业务反馈查询缓慢。遂排查，以下是排查结果：
- 为查询主视图表，包含5年的工单数据，单表数据 400W+，字段较多。
- 已经针对常用的 SN、工单号、手机号等字段建立了索引，但是手机号和用户名支持全模糊匹配
- 分页接口排序为工单创建时间倒序
查询对应库慢sql，针对高频慢查询，输出常用搜索字段。
根据结果主要优化分为三步
- 常用搜索字段增加索引
- 减少模糊匹配字段，或改成右模糊搜索
- 区分冷热数据，做冷备
最后还引入了 Doris ，最终实现查询控制在 200 ms 之内
一、索引优化
从 DBA 处导出慢 SQL 日志，统计经常出现的慢查询相关查询条件，给这些字段加索引，主要思路如下
- 利用好mysql 8.0的倒序索引 create_time desc
- 区分度高的，返回结果<10不需要加 create_time desc。
- 区分度低的，需要加 create_time desc
create index idx_create_time_desc
on work_order (create_time desc);
create index idx_main_order_sort
on work_order (is_main_order asc, create_time desc);
create index sn_idx
on work_order (sn);
create index order_type_idx
on work_order (order_type asc,create_time desc);
说服业务，使用精准查询，承诺后续使用搜索引擎实现模糊+任意字段匹配。
优化结果：常用查询控制在 600ms 之内。
二、冷热备
按照 1 年为纬度，区分 work_order （热表）和 work_order_history（冷表）。每天执行定时任务，将热表过时数据写入冷表，然后删除热表数据。
这里主要面临的挑战是——分页跨表查询的问题
主要通过三个方面解决
1.UI调整
UI 默认选中查询近一年数据，这样只需要查询 work_order（热表），效率非常高。这个纬度的数据也能满足大部份用户的查询需求。
但是部份用户依然有查询历史数据的需求，因此需要做分页聚合。
2.分页聚合
当用户选择大于一年的工单数据时，按照分页大小，例如 50 条，先查询 work_order（热表）。
- 查询结果>50，则这一页数据全部在热表，只查询热表数据。
- 如果查询结果=0，数据全部在冷表，只查询冷表
- 如果(0,50)，
如果 offset + limit <= count_hot：全部数据都在热表，只查热表。
如果 offset > count_hot：数据全部在冷表，查询冷表，但偏移量改为 offset - count_hot。
如果 offset < count_hot 且 offset + limit > count_hot：跨表了。分别取热表尾部和冷表头部，然后在内存中合并。
当跨表时，这个速度并不会提升太多。并且客户还是希望可以支持任意字符模糊，因此做一个搜索引擎方案是必要的。开始选型的技术为 Meilisearch和 ClickHouse，最后发现我司已经购买了 Doris 来做数仓，且可以直接兼容 MySQL 查询方式
三、接入数仓
同步对应表到数仓，在项目上配置多数据源，分页直接查询数仓库。接入了数仓之后，就算是模糊查询都能控制在 200ms 以内。
四、一些疑问
1.使用了数仓之后，冷热备还有意义吗？
意义其实是有的，当原始表数据太多的情况下，做状态更新这些操作的效率也会下降。
把已经不会更新的过期数据放到冷表中可以增加剩余热数据的操作效率。
评论区