关于使用Turbo未生效问题

mlovewt 发表于 5 天前

本帖最后由 mlovewt 于 2025-11-24 11:20 编辑

使用docker安装的greatsql,开启Turbo引擎后，sql 查询未生效，单表大约100万数据

一、开始Turbo
INSTALL PLUGIN turbo SONAME 'turbo.so';
SET global turbo_enable=ON;
SET global turbo_cost_threshold=0;

二、建表语句
CREATE TABLE `generic_survey_response_log` (
`record_id` bigint unsigned NOT NULL AUTO_INCREMENT,
`survey_year` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '关联的调研年份标识',
`attempt_number` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '该年度内的参与序号',
`log_timestamp` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '记录生成的具体时间戳',
`entity_code_level0` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '参与者直接归属实体代码',
`entity_name_level0` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '参与者直接归属实体名称',
`parent_entity_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '参与者上级实体代码',
`entity_code_level1` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '层级路径上的实体代码1',
`entity_name_level1` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '层级路径上的实体名称1',
`entity_code_level2` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '层级路径上的实体代码2',
`entity_name_level2` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '层级路径上的实体名称2',
`entity_code_level3` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '层级路径上的实体代码3',
`entity_name_level3` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '层级路径上的实体名称3',
`entity_code_level4` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '层级路径上的实体代码4',
`entity_name_level4` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '层级路径上的实体名称4',
`entity_code_level5` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '层级路径上的实体代码5',
`entity_name_level5` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '层级路径上的实体名称5',
`entity_code_level6` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '层级路径上的实体代码6',
`entity_name_level6` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '层级路径上的实体名称6',
`entity_code_level7` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '层级路径上的实体代码7',
`entity_name_level7` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '层级路径上的实体名称7',
`participant_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '系统内唯一参与者标识符',
`role_id` bigint DEFAULT NULL COMMENT '参与者角色ID（如有）',
`category_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '问题所属大类编码',
`category_description` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '问题所属大类说明',
`question_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '具体问题的唯一编码',
`question_text` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '具体问题的内容描述',
`response_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '选择答案的编码（空值表示未作答）',
`response_text` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '选择答案的文本描述',
`points_awarded` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '此题获得的分数',
`domain_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '业务领域或专业条线代码',
`center_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '所属中心或部门代码',
`is_supervisor_flag` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '标记是否为管理层人员',
`employment_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '用工性质（如正式、外包等）',
`sub_domain_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '业务领域的进一步细分代码',
PRIMARY KEY (`record_id` DESC) USING BTREE,
KEY `idx_entity_code_l0` (`entity_code_level0`) USING BTREE,
KEY `idx_participant` (`participant_id`) USING BTREE,
KEY `idx_entity_code_l1` (`entity_code_level1`) USING BTREE,
KEY `idx_entity_code_l2` (`entity_code_level2`) USING BTREE,
KEY `idx_entity_code_l3` (`entity_code_level3`) USING BTREE,
KEY `idx_entity_code_l4` (`entity_code_level4`) USING BTREE,
KEY `idx_entity_code_l5` (`entity_code_level5`) USING BTREE,
KEY `idx_entity_code_l6` (`entity_code_level6`) USING BTREE,
KEY `idx_entity_code_l7` (`entity_code_level7`) USING BTREE,
KEY `idx_domain` (`domain_code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=956169 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='通用调研问卷回答明细日志表';

三、执行sql

SELECT
`role_id`,
`input_entity_code` AS v_org_code,    -- 输入参数：目标实体代码
`input_entity_name` AS v_org_name,    -- 输入参数：目标实体名称
`input_parent_entity_code` AS v_parent_org_code, -- 输入参数：目标父级实体代码
IFNULL(`domain_code`, '1') AS line,    -- 业务领域代码
'1' AS wt_id,                         -- 固定维度ID（示例）
IFNULL(`employment_type`, '1') AS job_type, -- 用工性质代码
ROUND(
   CASE
         WHEN IFNULL(COUNT(`question_code`), 0) = 0 THEN 0
         ELSE SUM(`points_awarded`) / COUNT(`question_code`)
   END, 2
) AS total_avg,                         -- 整体平均得分（总体满意度）

ROUND(
   CASE
         WHEN IFNULL(
               COUNT(
                     CASE
                        WHEN `category_description` = 'Category_A_Specific_Items' THEN `question_code`
                     END
               ), 0
            ) = 0
         THEN 0
         ELSE SUM(
               CASE
                     WHEN `category_description` = 'Category_A_Specific_Items' THEN `points_awarded`
               END
            ) /
            COUNT(
               CASE
                     WHEN `category_description` = 'Category_A_Specific_Items' THEN `question_code`
               END
            )
   END, 2
) AS category_a_avg,                   -- 分类A平均得分（示例：对管理层满意度）

ROUND(
   CASE
         WHEN IFNULL(
               COUNT(
                     CASE
                        WHEN `category_description` = 'Category_B_Specific_Items' THEN `question_code`
                     END
               ), 0
            ) = 0
         THEN 0
         ELSE SUM(
               CASE
                     WHEN `category_description` = 'Category_B_Specific_Items' THEN `points_awarded`
               END
            ) /
            COUNT(
               CASE
                     WHEN `category_description` = 'Category_B_Specific_Items' THEN `question_code`
               END
            )
   END, 2
) AS category_b_avg,                   -- 分类B平均得分（示例：对企业认同度）

ROUND(
   CASE
         WHEN IFNULL(
               COUNT(
                     CASE
                        WHEN `category_description` = 'Category_C_Specific_Items' THEN `question_code`
                     END
               ), 0
            ) = 0
         THEN 0
         ELSE SUM(
               CASE
                     WHEN `category_description` = 'Category_C_Specific_Items' THEN `points_awarded`
               END
            ) /
            COUNT(
               CASE
                     WHEN `category_description` = 'Category_C_Specific_Items' THEN `question_code`
               END
            )
   END, 2
) AS category_c_avg,                   -- 分类C平均得分（示例：对工作本身满意度）

ROUND(
   CASE
         WHEN IFNULL(
               COUNT(
                     CASE
                        WHEN `category_description` = 'Category_D_Specific_Items' THEN `question_code`
                     END
               ), 0
            ) = 0
         THEN 0
         ELSE SUM(
               CASE
                     WHEN `category_description` = 'Category_D_Specific_Items' THEN `points_awarded`
               END
            ) /
            COUNT(
               CASE
                     WHEN `category_description` = 'Category_D_Specific_Items' THEN `question_code`
               END
            )
   END, 2
) AS category_d_avg,                   -- 分类D平均得分（示例：对工作回报满意度）

ROUND(
   CASE
         WHEN IFNULL(
               COUNT(
                     CASE
                        WHEN `category_description` = 'Category_E_Specific_Items' THEN `question_code`
                     END
               ), 0
            ) = 0
         THEN 0
         ELSE SUM(
               CASE
                     WHEN `category_description` = 'Category_E_Specific_Items' THEN `points_awarded`
               END
            ) /
            COUNT(
               CASE
                     WHEN `category_description` = 'Category_E_Specific_Items' THEN `question_code`
               END
            )
   END, 2
) AS category_e_avg,                   -- 分类E平均得分（示例：对员工关怀度）

COUNT(DISTINCT `participant_id`) AS curr_person, -- 当前范围总人数
COUNT(DISTINCT CASE WHEN `question_code` IS NOT NULL THEN `participant_id` END) AS curr_answer, -- 实际参与答题人数
ROUND(
   CASE
         WHEN IFNULL(COUNT(DISTINCT `participant_id`), 0) = 0 THEN 0
         ELSE (COUNT(DISTINCT CASE WHEN `question_code` IS NOT NULL THEN `participant_id` END) * 100.0)
            / COUNT(DISTINCT `participant_id`)
   END, 2
) AS curr_rate                         -- 答题参与率 (%)

FROM `generic_survey_response_log`
WHERE (
   `entity_code_level0` = `input_entity_code` -- 匹配最直接归属
OR `entity_code_level1` = `input_entity_code` -- 匹配层级路径上第1级
OR `entity_code_level2` = `input_entity_code` -- 匹配层级路径上第2级
OR `entity_code_level3` = `input_entity_code` -- 匹配层级路径上第3级
OR `entity_code_level4` = `input_entity_code` -- 匹配层级路径上第4级
OR `entity_code_level5` = `input_entity_code` -- 匹配层级路径上第5级
OR `entity_code_level6` = `input_entity_code` -- 匹配层级路径上第6级
OR `entity_code_level7` = `input_entity_code` -- 匹配层级路径上第7级
)
GROUP BY
`domain_code`,    -- 按业务领域分组
`employment_type` -- 按用工性质分组
WITH ROLLUP;    -- 生成小计和总计行
-- 注意：脱敏后，如果 employment_type 字段没有 '1' 这个特殊值，
-- 则原SQL中 "没有 '1'，job_type" 的注释所指的缓存逻辑可能不再适用或需要调整。
-- 此处保留了 WITH ROLLUP 以维持原始聚合意图。

四、db状态

turbo_compat_options    COMPAT_FUNC,TIMESTAMP_TO_DOUBLE,TIME_ZONE,TIMESTAMP_REWRITE
turbo_condition_pushdown    OFF
turbo_cost_threshold    0.000000
turbo_enable    ON
turbo_enable_ps_and_sp    ON
turbo_enable_rollback    OFF
turbo_enable_warning    OFF
turbo_exec
turbo_index_range    OFF
turbo_max_temp_directory_size    1073741824
turbo_memory_limit    5368709120
turbo_parallel_read_buffer_size    2097152
turbo_release_retries    600
turbo_retry_pause    1000
turbo_skip_charset_check    OFF
turbo_temp_directory    duckdb_dp.data.tmp
turbo_worker_threads    4

五、执行计划

yejr 发表于 5 天前

想要使用Turbo引擎，要在执行SELECT时每次都加上相应的HINT才行，执行 set global修改还不够。例如：

EXPLAIN FORMAT=TREE SELECT /*+ SET_VAR(turbo_enable=ON) SET_VAR(turbo_cost_threshold=0) */ * FROM t1;

详情参考手册：https://greatsql.cn/docs/8.4.4-4/5-enhance/5-1-highperf-turbo-engine.html

mlovewt 发表于 4 天前

yejr 发表于 2025-11-20 20:50
想要使用Turbo引擎，要在执行SELECT时每次都加上相应的HINT才行，执行 set global修改还不够。例如：

EXPL ...

好的我今天再试试

wangcw 发表于 4 天前

Turbo引擎是GreatSQL特有的么

yejr 发表于 4 天前

wangcw 发表于 2025-11-21 13:50
Turbo引擎是GreatSQL特有的么

是

mlovewt 发表于 4 天前

yejr 发表于 2025-11-21 15:51
是

8.4.4-4 的并行查询 /*+ PQ(8) */ 这个取消了？

reddey 发表于 3 天前

mlovewt 发表于 2025-11-21 17:56
8.4.4-4 的并行查询 /*+ PQ(8) */ 这个取消了？

之前好像在社区文档中看到这样一句话，要取消并行查询的功能。

wangcw 发表于前天 08:36

reddey 发表于 2025-11-22 17:19
之前好像在社区文档中看到这样一句话，要取消并行查询的功能。

并行查询会有引起很多事务一致性问题，需要强一致性的最好不用。

yejr 发表于前天 09:59

mlovewt 发表于 2025-11-21 17:56
8.4.4-4 的并行查询 /*+ PQ(8) */ 这个取消了？

是的，这个功能现在取消了，可以改用Rapid或Turbo引擎

页: [1]

GreatSQL社区's Archiver

关于使用Turbo未生效问题