33 lines
2.0 KiB
SQL
33 lines
2.0 KiB
SQL
-- /wallet/wallet-manage/qb-total 历史余额统计索引（MySQL 8）。
|
||
-- 先在钱包库执行本脚本，再发布 Wallet 服务；不修改钱包、流水或余额数据。
|
||
-- 大流水表建索引会产生 IO/空间开销，请在低峰执行并观察磁盘和复制延迟。
|
||
-- 显式 GROUP BY tenant_id,del_flag,wallet_id,user_id 与此前缀配合使用跳组扫描。
|
||
-- create_time 确定月末之前的最新时间，id 解决同秒多笔流水的取值。
|
||
-- 不替换既有钱包流水/业务查询索引；其他索引承担不同查询场景。
|
||
|
||
SET @wallet_qb_ddl = IF(EXISTS (
|
||
SELECT 1 FROM (
|
||
SELECT INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS cols,
|
||
SUM(SUB_PART IS NOT NULL) AS prefix_columns,
|
||
SUM(COLLATION != 'A') AS descending_columns
|
||
FROM information_schema.STATISTICS
|
||
WHERE TABLE_SCHEMA = 'pjcloudx_wallet' AND TABLE_NAME = 'wallet_transaction'
|
||
AND IS_VISIBLE = 'YES' AND INDEX_TYPE = 'BTREE'
|
||
GROUP BY INDEX_NAME
|
||
) indexes
|
||
WHERE prefix_columns = 0
|
||
AND descending_columns = 0
|
||
AND (cols = 'tenant_id,del_flag,wallet_id,user_id,create_time,id'
|
||
OR LEFT(cols, LENGTH('tenant_id,del_flag,wallet_id,user_id,create_time,id') + 1)
|
||
= 'tenant_id,del_flag,wallet_id,user_id,create_time,id,')
|
||
), 'SELECT ''wallet qb: equivalent history index exists''',
|
||
'CREATE INDEX idx_wallet_qb_latest ON pjcloudx_wallet.wallet_transaction (tenant_id,del_flag,wallet_id,user_id,create_time,id) ALGORITHM=INPLACE LOCK=NONE');
|
||
PREPARE wallet_qb_stmt FROM @wallet_qb_ddl;
|
||
EXECUTE wallet_qb_stmt;
|
||
DEALLOCATE PREPARE wallet_qb_stmt;
|
||
|
||
-- 同名异构索引会报错，核对后人工处理，不自动删除旧索引。
|
||
-- 发布后用真实 tenant、月份及筛选参数执行 EXPLAIN ANALYZE：
|
||
-- 分组子查询应显示 Using index for group-by / Covering index skip scan for grouping，
|
||
-- 窗口节点处理的是钱包/历史所属用户候选数，不应是整月之前的全部流水数。
|