52 lines
2.9 KiB
SQL
52 lines
2.9 KiB
SQL
-- 不活跃钱包统计专用索引。仅新增索引，不修改业务数据；大表请低峰逐条执行并观察 IO。
|
|
-- 每段先检查可见 BTREE 索引的列前缀，等价索引已存在时跳过。
|
|
-- 若同名索引定义不同，CREATE INDEX 将报错，应检查后人工处理，禁止盲目删除重建。
|
|
|
|
SET @wallet_inactive_ddl = IF(EXISTS (
|
|
SELECT 1 FROM (
|
|
SELECT INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS cols
|
|
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 cols = 'tenant_id,user_id,del_flag,create_time'
|
|
OR cols LIKE 'tenant_id,user_id,del_flag,create_time,%'
|
|
), 'SELECT ''wallet inactive: equivalent transaction index exists''',
|
|
'CREATE INDEX idx_wallet_inactive_tx_user_time ON pjcloudx_wallet.wallet_transaction (tenant_id,user_id,del_flag,create_time)');
|
|
PREPARE wallet_inactive_stmt FROM @wallet_inactive_ddl;
|
|
EXECUTE wallet_inactive_stmt;
|
|
DEALLOCATE PREPARE wallet_inactive_stmt;
|
|
|
|
SET @wallet_inactive_ddl = IF(EXISTS (
|
|
SELECT 1 FROM (
|
|
SELECT INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS cols
|
|
FROM information_schema.STATISTICS
|
|
WHERE TABLE_SCHEMA = 'pjcloudx_wallet' AND TABLE_NAME = 'wallet_manage'
|
|
AND IS_VISIBLE = 'YES' AND INDEX_TYPE = 'BTREE'
|
|
GROUP BY INDEX_NAME
|
|
) indexes WHERE cols = 'tenant_id,del_flag,user_id,create_time'
|
|
OR cols LIKE 'tenant_id,del_flag,user_id,create_time,%'
|
|
), 'SELECT ''wallet inactive: equivalent user index exists''',
|
|
'CREATE INDEX idx_wallet_inactive_user ON pjcloudx_wallet.wallet_manage (tenant_id,del_flag,user_id,create_time)');
|
|
PREPARE wallet_inactive_stmt FROM @wallet_inactive_ddl;
|
|
EXECUTE wallet_inactive_stmt;
|
|
DEALLOCATE PREPARE wallet_inactive_stmt;
|
|
|
|
SET @wallet_inactive_ddl = IF(EXISTS (
|
|
SELECT 1 FROM (
|
|
SELECT INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS cols
|
|
FROM information_schema.STATISTICS
|
|
WHERE TABLE_SCHEMA = 'pjcloudx_wallet' AND TABLE_NAME = 'wallet_manage'
|
|
AND IS_VISIBLE = 'YES' AND INDEX_TYPE = 'BTREE'
|
|
GROUP BY INDEX_NAME
|
|
) indexes WHERE cols = 'tenant_id,del_flag,dept_id,user_id,create_time'
|
|
OR cols LIKE 'tenant_id,del_flag,dept_id,user_id,create_time,%'
|
|
), 'SELECT ''wallet inactive: equivalent department index exists''',
|
|
'CREATE INDEX idx_wallet_inactive_dept_user ON pjcloudx_wallet.wallet_manage (tenant_id,del_flag,dept_id,user_id,create_time)');
|
|
PREPARE wallet_inactive_stmt FROM @wallet_inactive_ddl;
|
|
EXECUTE wallet_inactive_stmt;
|
|
DEALLOCATE PREPARE wallet_inactive_stmt;
|
|
|
|
-- 执行后核对 SHOW INDEX，并对 Mapper 的 countUsers / pageUsers / listWallets
|
|
-- 分别进行 EXPLAIN ANALYZE，验证近期流水存在性检查和最后流水取首行使用用户时间索引。
|