58 lines
3.8 KiB
SQL
58 lines
3.8 KiB
SQL
-- /hard/hard-total/page-list-cs 配套索引，线上目标为 PolarDB MySQL 8；已在 MySQL 8.0.46 验证。
|
|
-- 应与本次 SQL 改造一同发布。大表低峰期执行，先核对剩余磁盘和实际版本的在线 DDL 支持。
|
|
-- 不删除现有索引；只跳过前导列及覆盖列均等效的可见完整 BTREE 索引。
|
|
-- 窄索引 (tenant_id,del_flag,pay_time) 不能替代本次支付覆盖索引。
|
|
-- 同一个支付索引覆盖页面的钱包、用户部门和用餐时段筛选，避免这些场景重新逐行读取订单主表。
|
|
-- PolarDB DDL 行为参考：https://help.aliyun.com/zh/polardb/polardb-for-mysql/user-guide/support-for-ddl-in-polardb-mysql
|
|
|
|
SET @hard_cs_pay_cover_exists = (
|
|
SELECT COUNT(*)
|
|
FROM (
|
|
SELECT INDEX_NAME,
|
|
GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ',') AS columns_in_order,
|
|
SUM(COLUMN_NAME IN ('tenant_id','del_flag','pay_time','order_status','merchant_type','merchant_id','merchant_dept_id','pay_price','wallet_id','user_dept_id','hour_type')) 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 columns_in_order LIKE 'tenant_id,del_flag,pay_time,%'
|
|
AND covered_columns = 11 AND prefix_columns = 0
|
|
);
|
|
SET @hard_cs_pay_cover_ddl = IF(@hard_cs_pay_cover_exists = 0,
|
|
'CREATE INDEX idx_hard_order_cs_pay_cover ON pjcloudx_hardware.hard_order (tenant_id,del_flag,pay_time,order_status,merchant_type,merchant_id,merchant_dept_id,pay_price,wallet_id,user_dept_id,hour_type) ALGORITHM=INPLACE LOCK=NONE',
|
|
'SELECT ''商户支付覆盖索引已存在，跳过'' AS message');
|
|
PREPARE hard_cs_pay_cover_statement FROM @hard_cs_pay_cover_ddl;
|
|
EXECUTE hard_cs_pay_cover_statement;
|
|
DEALLOCATE PREPARE hard_cs_pay_cover_statement;
|
|
|
|
SET @hard_cs_refund_audit_exists = (
|
|
SELECT COUNT(*)
|
|
FROM (
|
|
SELECT INDEX_NAME,
|
|
GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ',') AS columns_in_order,
|
|
SUM(COLUMN_NAME IN ('tenant_id','del_flag','status','audit_time','order_id','refund_price')) 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_after_sale'
|
|
AND INDEX_TYPE = 'BTREE' AND IS_VISIBLE = 'YES'
|
|
GROUP BY INDEX_NAME
|
|
) existing_indexes
|
|
WHERE columns_in_order LIKE 'tenant_id,del_flag,status,audit_time,%'
|
|
AND covered_columns = 6 AND prefix_columns = 0
|
|
);
|
|
SET @hard_cs_refund_audit_ddl = IF(@hard_cs_refund_audit_exists = 0,
|
|
'CREATE INDEX idx_hard_after_sale_cs_audit ON pjcloudx_hardware.hard_after_sale (tenant_id,del_flag,status,audit_time,order_id,refund_price) ALGORITHM=INPLACE LOCK=NONE',
|
|
'SELECT ''商户退款时间覆盖索引已存在，跳过'' AS message');
|
|
PREPARE hard_cs_refund_audit_statement FROM @hard_cs_refund_audit_ddl;
|
|
EXECUTE hard_cs_refund_audit_statement;
|
|
DEALLOCATE PREPARE hard_cs_refund_audit_statement;
|
|
|
|
-- 发布后对真实租户、日期和筛选参数生成的 SQL 执行 EXPLAIN ANALYZE（执行真实查询）。
|
|
-- 默认时间汇总：支付走 idx_hard_order_cs_pay_cover 范围覆盖扫描；退款先走
|
|
-- idx_hard_after_sale_cs_audit 的 audit_time 范围，再按主键读取关联订单。
|
|
-- 定向商户、钱包等筛选允许选择其他更合适的索引，不要求强制使用上述索引。
|
|
-- SQL 中 NO_GROUP_INDEX / JOIN_PREFIX 提示已在 MySQL 8.0.46 验证；PolarDB 上用实际小版本、租户参数重新验计划。
|
|
-- 若先建索引再灰度代码，过渡期旧 SQL 可能改变执行计划，应检查旧服务延迟，尽快完成同批灰度。
|