89 lines
6.5 KiB
SQL
89 lines
6.5 KiB
SQL
-- /hard/hard-total/page-list-dept 与部门导出配套索引；目标：PolarDB MySQL 8。
|
||
-- 本脚本只读取索引元数据并按需创建索引，不写业务数据，不删除或替换现有索引。
|
||
-- 索引名称不是等效判断依据：已有其他名称的可见 BTREE 索引满足条件时同样跳过。
|
||
-- 若目标名称已被不同定义占用且不存在等效索引，CREATE INDEX 会报错并停止该条；
|
||
-- 应先核对 SHOW INDEX，不能把不同定义的同名索引当作本次所需索引。
|
||
-- 大表低峰逐条执行。INPLACE/LOCK=NONE 仍需短暂元数据锁，应先排查长事务，
|
||
-- 核对实际 PolarDB 小版本的在线 DDL 支持、可用空间和复制延迟，不并行建大表索引。
|
||
-- PolarDB DDL：https://help.aliyun.com/zh/polardb/polardb-for-mysql/user-guide/support-for-ddl-in-polardb-mysql
|
||
--
|
||
-- 代码发布依赖：先发布 Wallet 的 /wallet-transaction/department-statistics 新内部接口，
|
||
-- 再发布 Hardware。Hardware 新支付聚合直接读取 pjcloudx_wallet.wallet_transaction，
|
||
-- 两库须位于同一实例，Hardware 应用数据库账号须具备该表 SELECT 权限。
|
||
-- 测试使用的 root 账号权限不能作为生产应用账号权限的验证依据。
|
||
|
||
-- 一、订单按创建时间做范围覆盖扫描。
|
||
-- 等效索引须以前三列 tenant_id,del_flag,create_time 按此顺序开头，包含以下全部七列，
|
||
-- 所有显式索引列均为完整列（无前缀截断），且索引可见、类型为 BTREE。
|
||
-- 后续覆盖列允许不同顺序或额外列；只有前三列的窄索引不能替代本次覆盖索引。
|
||
SET @hard_dept_order_cover_exists = (
|
||
SELECT COUNT(*)
|
||
FROM (
|
||
SELECT INDEX_NAME,
|
||
SUM(SEQ_IN_INDEX = 1 AND COLUMN_NAME = 'tenant_id') AS tenant_first,
|
||
SUM(SEQ_IN_INDEX = 2 AND COLUMN_NAME = 'del_flag') AS deletion_second,
|
||
SUM(SEQ_IN_INDEX = 3 AND COLUMN_NAME = 'create_time') AS created_third,
|
||
COUNT(DISTINCT CASE WHEN COLUMN_NAME IN
|
||
('tenant_id','del_flag','create_time','order_status','wallet_id','user_dept_id','pay_price')
|
||
THEN COLUMN_NAME END) AS covered_columns,
|
||
SUM(SUB_PART IS NOT NULL) AS prefix_columns
|
||
FROM information_schema.STATISTICS
|
||
WHERE TABLE_SCHEMA = 'pjcloudx_hardware' AND TABLE_NAME = 'hard_order'
|
||
AND INDEX_TYPE = 'BTREE' AND IS_VISIBLE = 'YES'
|
||
GROUP BY INDEX_NAME
|
||
) existing_indexes
|
||
WHERE tenant_first = 1 AND deletion_second = 1 AND created_third = 1
|
||
AND covered_columns = 7 AND prefix_columns = 0
|
||
);
|
||
SET @hard_dept_order_cover_ddl = IF(@hard_dept_order_cover_exists = 0,
|
||
'CREATE INDEX idx_hard_order_dept_ct_cover ON pjcloudx_hardware.hard_order (tenant_id,del_flag,create_time,order_status,wallet_id,user_dept_id,pay_price) ALGORITHM=INPLACE LOCK=NONE',
|
||
'SELECT ''部门订单创建时间覆盖索引已存在，跳过'' AS message');
|
||
PREPARE hard_dept_order_cover_statement FROM @hard_dept_order_cover_ddl;
|
||
EXECUTE hard_dept_order_cover_statement;
|
||
DEALLOCATE PREPARE hard_dept_order_cover_statement;
|
||
|
||
-- 二、按租户和业务编号定位少量支付流水，再校验钱包、业务类型、删除状态和部门，取最新有效记录。
|
||
-- 复用 20261022_wallet_rule_transaction_page_index.sql 中的 idx_wt_tenant_bizid_type，
|
||
-- 不为部门报表再创建另一条更宽的同类索引。
|
||
-- 任何以完整 tenant_id,biz_id 两列开头的可见 BTREE 索引均视为可复用；后续列可有前缀。
|
||
-- biz_type 是 TINYTEXT，新增候选必须写 biz_type(8)，该候选不是完整覆盖索引。
|
||
-- biz_id 高度选择性，用索引定位后执行精确字节匹配及最新有效快照筛选。
|
||
SET @hard_dept_wallet_biz_lookup_exists = (
|
||
SELECT COUNT(*)
|
||
FROM (
|
||
SELECT INDEX_NAME,
|
||
SUM(SEQ_IN_INDEX = 1 AND COLUMN_NAME = 'tenant_id' AND SUB_PART IS NULL) AS tenant_first,
|
||
SUM(SEQ_IN_INDEX = 2 AND COLUMN_NAME = 'biz_id' AND SUB_PART IS NULL) AS complete_biz_second
|
||
FROM information_schema.STATISTICS
|
||
WHERE TABLE_SCHEMA = 'pjcloudx_wallet' AND TABLE_NAME = 'wallet_transaction'
|
||
AND INDEX_TYPE = 'BTREE' AND IS_VISIBLE = 'YES'
|
||
GROUP BY INDEX_NAME
|
||
) existing_indexes
|
||
WHERE tenant_first = 1 AND complete_biz_second = 1
|
||
);
|
||
SET @hard_dept_wallet_biz_lookup_ddl = IF(@hard_dept_wallet_biz_lookup_exists = 0,
|
||
'CREATE INDEX idx_wt_tenant_bizid_type ON pjcloudx_wallet.wallet_transaction (tenant_id,biz_id,biz_type(8)) ALGORITHM=INPLACE LOCK=NONE',
|
||
'SELECT ''钱包租户业务编号索引已存在，复用并跳过'' AS message');
|
||
PREPARE hard_dept_wallet_biz_lookup_statement FROM @hard_dept_wallet_biz_lookup_ddl;
|
||
EXECUTE hard_dept_wallet_biz_lookup_statement;
|
||
DEALLOCATE PREPARE hard_dept_wallet_biz_lookup_statement;
|
||
|
||
-- 三、钱包六类流水合并统计先复用既有 idx_wallet_tx_tenant_del_biz_ct_uid
|
||
-- (tenant_id,del_flag,biz_type(8),create_time,user_id)，本脚本不额外创建宽聚合索引。
|
||
-- 该索引来自 20261004_wallet_transaction_page_index.sql / 20260826 索引脚本，
|
||
-- 不是包含 dept_id、price 的覆盖索引。日期范围汇总使用 INDEX 优化器提示建议采用该索引，
|
||
-- 防止业务号索引仅按 tenant_id 扫描整个租户。请核实同名索引存在且定义正确；
|
||
-- 提示要求引擎支持 MySQL 8.0.20+ 的 INDEX hint，未知提示或缺失索引时会忽略并回退，
|
||
-- 因此发布须以实际 PolarDB 的 EXPLAIN 和 SHOW WARNINGS 验收，不能仅凭 SQL 成功。
|
||
-- 定向钱包或没有合法日期范围时不加此提示。
|
||
-- 对大批导入/临时造数后索引统计失真，应刷新统计后再核对计划；
|
||
-- 本脚本不自动对业务大表执行 ANALYZE，生产按维护窗口和实际统计情况处理。
|
||
--
|
||
-- 发布验收使用真实租户、日期、部门包含/排除及钱包类型参数执行 EXPLAIN ANALYZE：
|
||
-- 1. 订单时间范围扫描应能使用创建时间覆盖索引，避免逐条回表读取订单字段。
|
||
-- 2. 历史流水应通过 tenant_id,biz_id 等值定位，不能逐单扫描全部钱包流水；
|
||
-- 源 SQL 保留普通 biz_id 字符串等值，额外 BINARY 等值只作精确匹配校验。
|
||
-- 3. 检查 NO_MERGE 派生表及相关子查询实际 loops，确认历史部门不会被重复求值。
|
||
-- 4. 钱包合并统计只执行一次；定向钱包或其他高选择性条件允许选择更合适的既有索引。
|
||
-- 5. 核对页面与部门导出全部金额、次数、删除部门及零交易部门结果后再扩大灰度。
|