supervision-boot/sql/20261027_profile_query_indexes.sql

-- 暂缓执行：生产 result.txt 显示已有连接索引未被用作查找，需先核对字段类型与字符集。
-- 先执行 20261027_profile_query_column_check.sql，再根据结果修订本候选脚本。
-- 单位/个人问题库查询索引，MySQL InnoDB。
-- 在目标业务库执行；先核对字段类型、索引长度和剩余磁盘空间。
-- 按完整左前缀识别已有等价索引；LOCK=NONE 不支持时会报错，不自动降级为锁表。
-- 在低峰期逐条执行，建索引会消耗 I/O；无需删除任何现有索引或业务数据。

SET @profile_index_sql = IF(
    EXISTS (
        SELECT 1 FROM (
            SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ',') AS columns_in_index,
                   SUM(SUB_PART IS NOT NULL) AS prefix_parts
            FROM information_schema.STATISTICS
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'negative' AND INDEX_TYPE = 'BTREE'
            GROUP BY INDEX_NAME
        ) existing_indexes
        WHERE prefix_parts = 0 AND (columns_in_index = 'involveDepartId,crtTime' OR LEFT(columns_in_index, LENGTH('involveDepartId,crtTime') + 1) = CONCAT('involveDepartId,crtTime', ','))
    ),
    'SELECT ''idx_profile_depart_time: existing equivalent index, skipped'' AS result',
    'ALTER TABLE `negative` ADD INDEX `idx_profile_depart_time` (`involveDepartId`, `crtTime`), ALGORITHM=INPLACE, LOCK=NONE'
);
PREPARE profile_index_stmt FROM @profile_index_sql;
EXECUTE profile_index_stmt;
DEALLOCATE PREPARE profile_index_stmt;

SET @profile_index_sql = IF(
    EXISTS (
        SELECT 1 FROM (
            SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ',') AS columns_in_index,
                   SUM(SUB_PART IS NOT NULL) AS prefix_parts
            FROM information_schema.STATISTICS
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'negative' AND INDEX_TYPE = 'BTREE'
            GROUP BY INDEX_NAME
        ) existing_indexes
        WHERE prefix_parts = 0 AND (columns_in_index = 'processing_status,checkStatus,discoveryTime' OR LEFT(columns_in_index, LENGTH('processing_status,checkStatus,discoveryTime') + 1) = CONCAT('processing_status,checkStatus,discoveryTime', ','))
    ),
    'SELECT ''idx_profile_verified_time: existing equivalent index, skipped'' AS result',
    'ALTER TABLE `negative` ADD INDEX `idx_profile_verified_time` (`processing_status`, `checkStatus`, `discoveryTime`), ALGORITHM=INPLACE, LOCK=NONE'
);
PREPARE profile_index_stmt FROM @profile_index_sql;
EXECUTE profile_index_stmt;
DEALLOCATE PREPARE profile_index_stmt;

SET @profile_index_sql = IF(
    EXISTS (
        SELECT 1 FROM (
            SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ',') AS columns_in_index,
                   SUM(SUB_PART IS NOT NULL) AS prefix_parts
            FROM information_schema.STATISTICS
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'negative_blame' AND INDEX_TYPE = 'BTREE'
            GROUP BY INDEX_NAME
        ) existing_indexes
        WHERE prefix_parts = 0 AND (columns_in_index = 'negativeId,type,blameIdCode' OR LEFT(columns_in_index, LENGTH('negativeId,type,blameIdCode') + 1) = CONCAT('negativeId,type,blameIdCode', ','))
    ),
    'SELECT ''idx_profile_problem_person: existing equivalent index, skipped'' AS result',
    'ALTER TABLE `negative_blame` ADD INDEX `idx_profile_problem_person` (`negativeId`, `type`, `blameIdCode`), ALGORITHM=INPLACE, LOCK=NONE'
);
PREPARE profile_index_stmt FROM @profile_index_sql;
EXECUTE profile_index_stmt;
DEALLOCATE PREPARE profile_index_stmt;

SET @profile_index_sql = IF(
    EXISTS (
        SELECT 1 FROM (
            SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ',') AS columns_in_index,
                   SUM(SUB_PART IS NOT NULL) AS prefix_parts
            FROM information_schema.STATISTICS
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'negative_blame' AND INDEX_TYPE = 'BTREE'
            GROUP BY INDEX_NAME
        ) existing_indexes
        WHERE prefix_parts = 0 AND (columns_in_index = 'blameIdCode,type,negativeId' OR LEFT(columns_in_index, LENGTH('blameIdCode,type,negativeId') + 1) = CONCAT('blameIdCode,type,negativeId', ','))
    ),
    'SELECT ''idx_profile_person_problem: existing equivalent index, skipped'' AS result',
    'ALTER TABLE `negative_blame` ADD INDEX `idx_profile_person_problem` (`blameIdCode`, `type`, `negativeId`), ALGORITHM=INPLACE, LOCK=NONE'
);
PREPARE profile_index_stmt FROM @profile_index_sql;
EXECUTE profile_index_stmt;
DEALLOCATE PREPARE profile_index_stmt;

SET @profile_index_sql = IF(
    EXISTS (
        SELECT 1 FROM (
            SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ',') AS columns_in_index,
                   SUM(SUB_PART IS NOT NULL) AS prefix_parts
            FROM information_schema.STATISTICS
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sup_police' AND INDEX_TYPE = 'BTREE'
            GROUP BY INDEX_NAME
        ) existing_indexes
        WHERE prefix_parts = 0 AND (columns_in_index = 'id_code,del' OR LEFT(columns_in_index, LENGTH('id_code,del') + 1) = CONCAT('id_code,del', ','))
    ),
    'SELECT ''idx_profile_police_identity: existing equivalent index, skipped'' AS result',
    'ALTER TABLE `sup_police` ADD INDEX `idx_profile_police_identity` (`id_code`, `del`), ALGORITHM=INPLACE, LOCK=NONE'
);
PREPARE profile_index_stmt FROM @profile_index_sql;
EXECUTE profile_index_stmt;
DEALLOCATE PREPARE profile_index_stmt;