前言
「慢查询」主要指数据查询执行超时的现象，当数据量大 + 查询条件复杂时，通常会出现「慢查询」。
通过阅读本文，你将了解：
- 飞书低代码平台中，出现「慢查询」的主要业务场景；
- 治理慢查询的实践案例，以及对应的优化策略。
实践案例
- 查询条件未命中索引
（1）业务场景
单表数据量过大，超过十万甚至百万的记录，同时检索命中率较小，且检索字段无索引
（2）慢查询案例
某业务单表数据过大，超过 180W 数据，其中某查询字段（column1）无索引，全表扫描，只命中了1条记录
（3）参考SQL
SELECT /*+ hint_by_join_reorder */ `t1`.`id`, `t1`.`id`, `t1`.`column1`, `t1`.`created_at`, `t1`.`column2`, `t1`.`updated_at`, `t1`.`is_deleted`FROM `mt_data_178178178` t1WHERE `t1`.`column1` = ? AND `t1`.`is_deleted` = ? AND `t1`.`column9` = ? AND `t1`.`is_deleted` = ? AND `t1`.`is_deleted` = ?limit ???
（4）优化策略
- 如果该字段是文本、整型、日期、整形等，直接在应用对象设计页开启字段的「索引」功能。
250px|700px|reset
- 如果该字段为共享对象的字段，提交 Oncall 由 Data 同学协助配置
- 筛选条件过滤性能差
（1）业务场景
单表数据量较大，同时筛选条件配置不合理，导致筛选出来的数据过多，进而导致查询缓慢
（2）慢查询案例
某业务单表数据过大，超过 5000W 数据，其中某 t1 采用「不包含」无法命中索引，全表扫描，查询缓慢
（3）参考SQL
SELECT COUNT(*)FROM `mt_data_178178178` t1 LEFT join `mt_relation_data_178178178` t2 ON `t1`.`id` = `t2`.`referencing_record_id` AND `t2`.`referencing_field_id` = ? AND `t2`.`is_deleted` = ?WHERE AND `t1`.`is_deleted` = ? AND ( `t1`.`id` <> ? OR `t1`.`id` is NULL ) AND `t1`.`is_deleted` = ???
（4）优化策略
使用「包含」、「不属于」为模糊查询，不会命中索引，等把检索条件的 `t1`.`id` <> ？ 优化成 `t1`.`id` in (?,?)，可以命中索引，加速查询
- 页面显示引用对象和字段过多
（1）业务场景
某 CRM 应用场景主对象关联了四十多个对象，并且设置了超过四十多个引用字段，当被关联的对象数据量过大的时候，就需要做多个对象的数据关联查询。
（2）慢查询案例
每个引用对象的字段都显示在页面上，每显示一个引用对象的字段，就会出现一次 join 连表操作，如下图 SQL 所示，在数据量超过百万级别的时候，查询直接超时
（3）参考SQL
SELECT count(*)FROM `mt_data_137137137137` t1INNER JOIN `mt_relation_data_137137137137` t3 ON (`t1`.id = `t3`.referencing_record_id)AND (`t3`.referencing_field_id = ?)AND (`t3`.is_deleted = ?)LEFT OUTER JOIN `mt_relation_data_137137137137` t2 ON (`t1`.id = `t2`.referencing_record_id)AND (`t2`.referencing_field_id = ?)AND (`t2`.is_deleted = ?)LEFT OUTER JOIN `mt_relation_data_mg_169169169169` t5 ON (`t3`.referenced_record_id = `t5`.referencing_record_id)AND (`t5`.referencing_field_id = ?)AND (`t5`.is_deleted = ?)LEFT OUTER JOIN `mt_data_174174174174` t4 ON (`t2`.referenced_record_id = `t4`.id)AND (`t4`.is_deleted = ?)AND (`t4`.tenant_id = ?)AND (`t4`.object_id = ?)WHERE ((`t1`.column14 IN ( (SELECT `t6`.id FROM `mt_data_mg_169169169169` t6 INNER JOIN `mt_hierarchy_data_169169169169` t7 ON (`t6`.id = `t7`.record_id) AND (`t7`.field_id = ?) WHERE (`t6`.is_deleted = ?) AND (`t7`.parent_id IN (?+))))) OR (`t1`.column66 IN ( (SELECT `t8`.id FROM `mt_data_mg_169169169169` t8 INNER JOIN `mt_hierarchy_data_169169169169` t9 ON (`t8`.id = `t9`.record_id) AND (`t9`.field_id = ?) WHERE (`t8`.is_deleted = ?) AND (`t9`.parent_id IN (?+))))) OR (`t4`.column15 IN ( (SELECT `t10`.id FROM `mt_data_mg_169169169169` t10 INNER JOIN `mt_hierarchy_data_169169169169` t11 ON (`t10`.id = `t11`.record_id) AND (`t11`.field_id = ?) WHERE (`t10`.is_deleted = ?) AND (`t11`.parent_id IN (?+))))) OR (`t5`.referenced_record_id IN ( (SELECT `t12`.id FROM `mt_data_1693827740119070` t12 INNER JOIN `mt_hierarchy_data_1693827740119070` t13 ON (`t12`.id = `t13`.record_id) AND (`t13`.field_id = ?) WHERE (`t12`.is_deleted = ?) AND (`t13`.parent_id IN (?+))))) OR (`t1`.created_by IN ( (SELECT `t14`.id FROM `mt_data_mg_169169169169` t14 INNER JOIN `mt_hierarchy_data_169169169169` t15 ON (`t14`.id = `t15`.record_id) AND (`t15`.field_id = ?) WHERE (`t14`.is_deleted = ?) AND (`t15`.parent_id IN (?+)))))) AND ((`t1`.column66 IN ( (SELECT `t16`.id FROM `mt_data_mg_169169169169` t16 INNER JOIN `mt_hierarchy_data_169169169169` t17 ON (`t16`.id = `t17`.record_id) AND (`t17`.field_id = ?) INNER JOIN `mt_relation_data_mg_169169169169` t18 ON (`t17`.parent_id = `t18`.referencing_record_id) AND (`t18`.referencing_field_id = ?) AND (`t18`.is_deleted = ?) INNER JOIN `mt_relation_data_mg_169169169169` t19 ON (`t18`.referenced_record_id = `t19`.referencing_record_id) AND (`t19`.referencing_field_id = ?) AND (`t19`.is_deleted = ?) INNER JOIN `mt_data_mg_169169169169_2QWfcXGiDLFCiYSQfyTdg` t20 ON (`t19`.referenced_record_id = `t20`.id) AND (`t20`.is_deleted = ?) AND (`t20`.tenant_id = ?) AND (`t20`.object_id = ?) WHERE (`t16`.is_deleted = ?) AND (`t20`.column442 IN (?+))))) OR (`t1`.column14 IN ( (SELECT `t21`.id FROM `mt_data_mg_169169169169` t21 INNER JOIN `mt_hierarchy_data_169169169169` t22 ON (`t21`.id = `t22`.record_id) AND (`t22`.field_id = ?) INNER JOIN `mt_relation_data_mg_169169169169` t23 ON (`t22`.parent_id = `t23`.referencing_record_id) AND (`t23`.referencing_field_id = ?) AND (`t23`.is_deleted = ?) INNER JOIN `mt_relation_data_mg_169169169169` t24 ON (`t23`.referenced_record_id = `t24`.referencing_record_id) AND (`t24`.referencing_field_id = ?) AND (`t24`.is_deleted = ?) INNER JOIN `mt_data_mg_169169169169_2QWfcXGiDLFCiYSQfyTdg` t25 ON (`t24`.referenced_record_id = `t25`.id) AND (`t25`.is_deleted = ?) AND (`t25`.tenant_id = ?) AND (`t25`.object_id = ?) WHERE (`t21`.is_deleted = ?) AND (`t25`.column442 IN (?+))))) OR (`t1`.created_by IN ( (SELECT `t26`.id FROM `mt_data_mg_169169169169` t26 INNER JOIN `mt_hierarchy_data_169169169169` t27 ON (`t26`.id = `t27`.record_id) AND (`t27`.field_id = ?) INNER JOIN `mt_relation_data_mg_169169169169` t28 ON (`t27`.parent_id = `t28`.referencing_record_id) AND (`t28`.referencing_field_id = ?) AND (`t28`.is_deleted = ?) INNER JOIN `mt_relation_data_mg_169169169169` t29 ON (`t28`.referenced_record_id = `t29`.referencing_record_id) AND (`t29`.referencing_field_id = ?) AND (`t29`.is_deleted = ?) INNER JOIN `mt_data_mg_169169169169_2QWfcXGiDLFCiYSQfyTdg` t30 ON (`t29`.referenced_record_id = `t30`.id) AND (`t30`.is_deleted = ?) AND (`t30`.tenant_id = ?) AND (`t30`.object_id = ?) WHERE (`t26`.is_deleted = ?) AND (`t30`.column442 IN (?+))))) OR (`t1`.column66 IN ( (SELECT `t31`.id FROM `mt_data_mg_169169169169` t31 INNER JOIN `mt_hierarchy_data_169169169169` t32 ON (`t31`.id = `t32`.record_id) AND (`t32`.field_id = ?) INNER JOIN `mt_relation_data_mg_169169169169` t33 ON (`t32`.parent_id = `t33`.referencing_record_id) AND (`t33`.referencing_field_id = ?) AND (`t33`.is_deleted = ?) INNER JOIN `mt_relation_data_mg_169169169169` t34 ON (`t33`.referenced_record_id = `t34`.referencing_record_id) AND (`t34`.referencing_field_id = ?) AND (`t34`.is_deleted = ?) INNER JOIN `mt_data_mg_169169169169_2QWfcXGiDLFCiYSQfyTdg` t35 ON (`t34`.referenced_record_id = `t35`.id) AND (`t35`.is_deleted = ?) AND (`t35`.tenant_id = ?) AND (`t35`.object_id = ?) WHERE (`t31`.is_deleted = ?) AND (`t35`.column442 IN (?+))))) OR (`t1`.column14 IN ( (SELECT `t36`.id FROM `mt_data_mg_169169169169` t36 INNER JOIN `mt_hierarchy_data_169169169169` t37 ON (`t36`.id = `t37`.record_id) AND (`t37`.field_id = ?) INNER JOIN `mt_relation_data_mg_169169169169` t38 ON (`t37`.parent_id = `t38`.referencing_record_id) AND (`t38`.referencing_field_id = ?) AND (`t38`.is_deleted = ?) INNER JOIN `mt_relation_data_mg_169169169169` t39 ON (`t38`.referenced_record_id = `t39`.referencing_record_id) AND (`t39`.referencing_field_id = ?) AND (`t39`.is_deleted = ?) INNER JOIN `mt_data_mg_169169169169_2QWfcXGiDLFCiYSQfyTdg` t40 ON (`t39`.referenced_record_id = `t40`.id) AND (`t40`.is_deleted = ?) AND (`t40`.tenant_id = ?) AND (`t40`.object_id = ?) WHERE (`t36`.is_deleted = ?) AND (`t40`.column442 IN (?+))))) OR (`t1`.created_by IN ( (SELECT `t41`.id FROM `mt_data_mg_169169169169` t41 INNER JOIN `mt_hierarchy_data_169169169169` t42 ON (`t41`.id = `t42`.record_id) AND (`t42`.field_id = ?) WHERE (`t41`.is_deleted = ?) AND (`t42`.parent_id IN (?+))))) OR (`t1`.column66 IN ( (SELECT `t43`.id FROM `mt_data_mg_169169169169` t43 INNER JOIN `mt_hierarchy_data_169169169169` t44 ON (`t43`.id = `t44`.record_id) AND (`t44`.field_id = ?) WHERE (`t43`.is_deleted = ?) AND (`t44`.parent_id IN (?+)))))) AND (`t1`.is_deleted = ?) AND (`t3`.referenced_record_id = ?) AND (`t1`.column10 = ?)))
（4）优化策略
当被关联对象数据量过大的时候，减少关联对象的引用字段的显示个数，推荐将引用字段控制在 10 个之内，未配置在页面上的引用字段将不会触发连表查询，需要显示更多引用字段的场景，可以在业务上分拆成两个表格显示。
- 数据集过大模糊查询性能差
（1）业务场景
某 CRM 营销应用，500万 数据总量，业务需要频繁根据模糊查询相关记录
（2）慢查询案例
数据量过大，由于业务需要模糊查询，无法命中索引，导致查询超时
（3）参考SQL
SELECT COUNT(*)FROM `mt_data_178178178` t1WHERE `t1`.`is_deleted` = ? AND `t1`.`column42` COLLATE utf8mb4_general_ci LIKE ???
（4）优化策略
由于业务本身无法直接走索引查询，但是数据量过多，可以考虑缩小查询范围，比如增加「日期」、「可搜索字段」等作为基础筛选条件，将数据集缩小后再执行模糊查询
- Count 查询记录数量过多
（1）业务场景
某个 CRM 系统，具有极多的订单，订单数量超过千万级别，页面需要显示订单总量，过滤条件较为复杂
（2）慢查询案例
随着订单量增多，查询开始明显变慢，到千万级别的时候直接查询超时
（3）参考SQL
COUNT(*) FROM `mt_data_178178178` t1 WHERE `t1`.`is_deleted`=? AND LOWER(`t1`.`column42`) LIKE LOWER(?)))
（4）优化策略
- 提交 Oncall 由 Data 同学协助配置列式 DB，该 DB 能较好的支持 COUNT/SUM 等聚合操作
- 缺点是数据同步有秒级的延迟，用于可接受延迟的数据查询场景
- 数据表格导出未加排序条件超时
（1）业务场景
用户的数据量比较大，表格的导出组件没有配置筛选条件，导致数据库使用默认的排序，导致导出失败
（2）慢查询案例
随着数据增多，查询开始明显变慢，到千万级别的时候直接查询超时
（3）参考SQL
COUNT(*) FROM `mt_data_178178178` t1 WHERE `t1`.`is_deleted`=? AND LOWER(`t1`.`column42`) LIKE LOWER(?)))
（4）优化策略
- 缩小查询的时间范围
- 导出的组件配置区分度高的字段，比如查询条件中的日期，最好使用筛选的日期字段。不要走到默认的排序字段：创建时间和 ID
- 命中索引数据量过多首次查询超时
（1）业务场景
用户的数据量极大（百万量级），已经命中索引，预期是能够很快返回
（2）慢查询案例
但是由于数据库预加载数据导致首次超时，第二次执行后查询速度很快，但是由于数据库有清理缓存数据的逻辑，导致后续首次查询又变慢
（3）参考SQL
SELECT *FROM `mt_data_178178178` t1WHERE (`t1`.`column24` = ? OR `t1`.`column24` = ? OR `t1`.`column24` = ?) AND `t1`.`is_deleted` = ? AND `t1`.`column2` = ? AND (`t1`.`column22` >= ? AND `t1`.`column22` <= ?)ORDER BY `t1`.`id` ASCLIMIT ???
- 页面筛选条件为文本
（1）业务场景
用户数据量极大（百万级），表格数据导出时失败/超时
（2）慢查询案例
表格页面能筛选出数据(但加载慢)，导出数据时失败，页面筛选条件字段为文本时，对应到查询时为 like 查询，容易导致慢查询。
250px|700px|reset
250px|700px|reset
（3）优化策略
- 通过 日期/ ID 等带索引的字段，进一步缩小查询范围，可以按照周度等维度循环获取数据
- 在流程中，可以增加额外预处理节点（缓存预热）并增加异常处理的方式来实现重试
