103 lines
5.8 KiB
SQL
103 lines
5.8 KiB
SQL
-- ============================================================================
|
||
-- 排行榜(admin leaderboard)统计所需的复合索引 —— MySQL 手动执行版
|
||
-- ============================================================================
|
||
--
|
||
-- 背景:
|
||
-- 这些索引声明在各 ORM model 的 __table_args__ 里,但 Base.metadata.create_all
|
||
-- 对【已存在的表】不会补建索引。本项目虽会在启动时运行 Alembic，但大表
|
||
-- 补索引刻意不放进启动迁移，避免在线建索引拖长或阻塞服务启动。
|
||
-- 因此在【已有数据的 MySQL 部署】上需要低峰期执行本脚本，否则排行榜的
|
||
-- 单日聚合查询会退化为全表扫描,拖慢并占用连接。
|
||
--
|
||
-- 用法:
|
||
-- 1) 先选中目标库(与 database.mysql_url 里的库名一致):
|
||
-- USE your_deerflow_db;
|
||
-- 2) 直接整文件执行即可。本脚本是【幂等】的:已存在的索引会跳过,不会报错,
|
||
-- 可重复执行。
|
||
--
|
||
-- 注意:
|
||
-- * 大表上建索引可能耗时;MySQL 8.0 在线 DDL 默认 ALGORITHM=INPLACE 一般不阻塞
|
||
-- 写入,但仍建议在低峰期执行,或先在备库/预发验证。
|
||
-- * 涉及列均为 String(64) / String(20) 等短列,utf8mb4 下复合索引长度远低于
|
||
-- 3072 字节上限,无需前缀索引。
|
||
-- * 索引名与 migration 20260608_01 保持一致,日后若启用 alembic 也不会重复创建。
|
||
-- ============================================================================
|
||
|
||
-- ---- 通用幂等封装:仅当索引不存在时才创建 --------------------------------------
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS _create_index_if_missing //
|
||
CREATE PROCEDURE _create_index_if_missing(
|
||
IN p_table VARCHAR(64),
|
||
IN p_index VARCHAR(64),
|
||
IN p_columns VARCHAR(255)
|
||
)
|
||
BEGIN
|
||
DECLARE v_exists INT DEFAULT 0;
|
||
SELECT COUNT(*) INTO v_exists
|
||
FROM information_schema.statistics
|
||
WHERE table_schema = DATABASE()
|
||
AND table_name = p_table
|
||
AND index_name = p_index;
|
||
|
||
IF v_exists = 0 THEN
|
||
SET @ddl = CONCAT('CREATE INDEX `', p_index, '` ON `', p_table, '` (', p_columns, ')');
|
||
PREPARE stmt FROM @ddl;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
SELECT CONCAT('CREATED ', p_table, '.', p_index) AS result;
|
||
ELSE
|
||
SELECT CONCAT('SKIPPED ', p_table, '.', p_index, ' (already exists)') AS result;
|
||
END IF;
|
||
END //
|
||
DELIMITER ;
|
||
|
||
-- ---- runs ---------------------------------------------------------------------
|
||
CALL _create_index_if_missing('runs', 'ix_runs_created_user_status', '`created_at`, `user_id`, `status`');
|
||
CALL _create_index_if_missing('runs', 'ix_runs_user_created', '`user_id`, `created_at`');
|
||
CALL _create_index_if_missing('runs', 'ix_runs_thread_created', '`thread_id`, `created_at`');
|
||
CALL _create_index_if_missing('runs', 'ix_runs_created_assistant_user', '`created_at`, `assistant_id`, `user_id`');
|
||
CALL _create_index_if_missing('runs', 'ix_runs_created_model_user', '`created_at`, `model_name`, `user_id`');
|
||
|
||
-- ---- tool_call_metrics -------------------------------------------------------
|
||
CALL _create_index_if_missing('tool_call_metrics', 'ix_tool_call_metrics_created_user', '`created_at`, `user_id`');
|
||
CALL _create_index_if_missing('tool_call_metrics', 'ix_tool_call_metrics_run_created', '`run_id`, `created_at`');
|
||
CALL _create_index_if_missing('tool_call_metrics', 'ix_tool_call_metrics_created_skill_user', '`created_at`, `skill_name`, `user_id`');
|
||
CALL _create_index_if_missing('tool_call_metrics', 'ix_tool_call_metrics_created_tool_user', '`created_at`, `tool_name`, `user_id`');
|
||
|
||
-- ---- llm_call_metrics --------------------------------------------------------
|
||
CALL _create_index_if_missing('llm_call_metrics', 'ix_llm_call_metrics_created_user', '`created_at`, `user_id`');
|
||
CALL _create_index_if_missing('llm_call_metrics', 'ix_llm_call_metrics_run_created', '`run_id`, `created_at`');
|
||
CALL _create_index_if_missing('llm_call_metrics', 'ix_llm_call_metrics_created_model_user', '`created_at`, `model_name`, `user_id`');
|
||
|
||
-- ---- threads_meta ------------------------------------------------------------
|
||
CALL _create_index_if_missing('threads_meta', 'ix_threads_meta_created_user', '`created_at`, `user_id`');
|
||
CALL _create_index_if_missing('threads_meta', 'ix_threads_meta_user_created', '`user_id`, `created_at`');
|
||
|
||
-- ---- scheduled_task_runs -----------------------------------------------------
|
||
-- NOT EXISTS anti-join used when the leaderboard excludes scheduler runs.
|
||
CALL _create_index_if_missing('scheduled_task_runs', 'ix_scheduled_task_runs_agent_run_id', '`agent_run_id`');
|
||
|
||
-- ---- position_roundtable_sessions --------------------------------------------
|
||
-- Explicit taskId history is loaded by taskId and newest update time.
|
||
CALL _create_index_if_missing('position_roundtable_sessions', 'ix_position_roundtable_sessions_task_updated', '`external_task_id`, `updated_at`');
|
||
|
||
-- ---- 清理临时存储过程 --------------------------------------------------------
|
||
DROP PROCEDURE IF EXISTS _create_index_if_missing;
|
||
|
||
-- ---- 执行后自检:确认排行榜索引都已存在 ----------------------------------------
|
||
SELECT table_name, index_name
|
||
FROM information_schema.statistics
|
||
WHERE table_schema = DATABASE()
|
||
AND index_name IN (
|
||
'ix_runs_created_user_status', 'ix_runs_user_created', 'ix_runs_thread_created',
|
||
'ix_runs_created_assistant_user', 'ix_runs_created_model_user',
|
||
'ix_tool_call_metrics_created_user', 'ix_tool_call_metrics_run_created',
|
||
'ix_tool_call_metrics_created_skill_user', 'ix_tool_call_metrics_created_tool_user',
|
||
'ix_llm_call_metrics_created_user', 'ix_llm_call_metrics_run_created',
|
||
'ix_llm_call_metrics_created_model_user',
|
||
'ix_threads_meta_created_user', 'ix_threads_meta_user_created',
|
||
'ix_scheduled_task_runs_agent_run_id'
|
||
)
|
||
GROUP BY table_name, index_name
|
||
ORDER BY table_name, index_name;
|