-- ============================================================================
|
-- pat_med_outhosp 系列表按 serialnum 加索引（配合门急诊按天全量采集）
|
--
|
-- 为什么要加：
|
-- 1) 采集任务每个半小时把当天上万个诊疗记录整轮拉一遍，靠"整页批量查重"过滤已入库的，
|
-- 查重 SQL 是 where serialnum in (500个)。没有索引就是全表扫描，季度分表几百万行时一次就很慢；
|
-- 2) 唯一索引是"不重复"的最后一道防线：极端并发、断点回退、分页偏移都不会插出重复记录。
|
--
|
-- 执行顺序：
|
-- 第 1 步 看清有哪些表、现在有没有索引
|
-- 第 2 步 查重复数据（有重复的话第 3 步加唯一索引会失败，先处理重复）
|
-- 第 3 步 加唯一索引 uk_serialnum（脚本会自动跳过已有唯一索引的表）
|
-- 第 4 步 校验结果
|
--
|
-- 注意：ADD INDEX 走 online DDL（ALGORITHM=INPLACE, LOCK=NONE），不阻塞读写，
|
-- 但大表仍会消耗 IO，建议放在业务低峰执行。数据量大时分表一张一张来也可以。
|
-- 如果某张表报错，先单独执行第 2 步看它有没有重复数据。
|
-- ============================================================================
|
|
|
-- ----------------------------------------------------------------------------
|
-- 第 1 步：看有哪些 pat_med_outhosp 表，以及 serialnum 上的索引情况
|
-- has_unique_index = 1 表示已经有唯一索引（不用再加）
|
-- has_any_index = 0 表示完全没索引（查询会很慢，必须加）
|
-- ----------------------------------------------------------------------------
|
SELECT t.TABLE_NAME,
|
t.TABLE_ROWS,
|
(SELECT COUNT(*)
|
FROM information_schema.STATISTICS s
|
WHERE s.TABLE_SCHEMA = t.TABLE_SCHEMA
|
AND s.TABLE_NAME = t.TABLE_NAME
|
AND s.COLUMN_NAME = 'serialnum'
|
AND s.NON_UNIQUE = 0) AS has_unique_index,
|
(SELECT COUNT(*)
|
FROM information_schema.STATISTICS s
|
WHERE s.TABLE_SCHEMA = t.TABLE_SCHEMA
|
AND s.TABLE_NAME = t.TABLE_NAME
|
AND s.COLUMN_NAME = 'serialnum') AS has_any_index
|
FROM information_schema.TABLES t
|
WHERE t.TABLE_SCHEMA = DATABASE()
|
AND (t.TABLE_NAME = 'pat_med_outhosp' OR t.TABLE_NAME REGEXP '^pat_med_outhosp_[0-9]{4,8}$')
|
ORDER BY t.TABLE_NAME;
|
|
|
-- ----------------------------------------------------------------------------
|
-- 第 2 步：查重复的 serialnum（有重复就必须先清理，否则唯一索引建不上）
|
-- 执行后先看 dup_serialnum_total，等于 0 就可以直接进第 3 步
|
-- ----------------------------------------------------------------------------
|
DROP PROCEDURE IF EXISTS check_outhosp_serialnum_dup;
|
DELIMITER $$
|
CREATE PROCEDURE check_outhosp_serialnum_dup()
|
BEGIN
|
DECLARE done INT DEFAULT 0;
|
DECLARE tbl VARCHAR(64);
|
DECLARE cur CURSOR FOR
|
SELECT TABLE_NAME
|
FROM information_schema.TABLES
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND (TABLE_NAME = 'pat_med_outhosp' OR TABLE_NAME REGEXP '^pat_med_outhosp_[0-9]{4,8}$');
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
|
|
DROP TEMPORARY TABLE IF EXISTS tmp_dup_serialnum;
|
CREATE TEMPORARY TABLE tmp_dup_serialnum
|
(
|
table_name VARCHAR(64),
|
serialnum VARCHAR(64),
|
cnt INT
|
);
|
|
OPEN cur;
|
read_loop:
|
LOOP
|
FETCH cur INTO tbl;
|
IF done THEN
|
LEAVE read_loop;
|
END IF;
|
SET @s = CONCAT('INSERT INTO tmp_dup_serialnum SELECT ''', tbl,
|
''', serialnum, COUNT(*) FROM `', tbl,
|
'` WHERE serialnum IS NOT NULL AND serialnum <> ''''',
|
' GROUP BY serialnum HAVING COUNT(*) > 1');
|
PREPARE st FROM @s;
|
EXECUTE st;
|
DEALLOCATE PREPARE st;
|
END LOOP;
|
CLOSE cur;
|
|
SELECT * FROM tmp_dup_serialnum ORDER BY cnt DESC LIMIT 200;
|
SELECT COUNT(*) AS dup_serialnum_total FROM tmp_dup_serialnum;
|
END$$
|
DELIMITER ;
|
|
CALL check_outhosp_serialnum_dup();
|
-- 查完可以删掉这个过程：DROP PROCEDURE IF EXISTS check_outhosp_serialnum_dup;
|
|
-- 如果有重复，按下面的思路清理（保留 id 最小的那条，先备份再删）：
|
-- CREATE TABLE bak_dup_<表名>_<日期> AS
|
-- SELECT * FROM `pat_med_outhosp_20261012`
|
-- WHERE serialnum IN ('重复的serialnum1','重复的serialnum2');
|
-- DELETE t1 FROM `pat_med_outhosp_20261012` t1
|
-- JOIN `pat_med_outhosp_20261012` t2
|
-- ON t1.serialnum = t2.serialnum AND t1.id > t2.id
|
-- WHERE t1.serialnum IN ('重复的serialnum1','重复的serialnum2');
|
|
|
-- ----------------------------------------------------------------------------
|
-- 第 3 步：给所有 pat_med_outhosp 表加唯一索引 uk_serialnum
|
-- 已有唯一索引的表自动跳过；如果之前建过同名的普通索引 idx_serialnum，会先删掉再建唯一的
|
-- ----------------------------------------------------------------------------
|
DROP PROCEDURE IF EXISTS add_outhosp_serialnum_index;
|
DELIMITER $$
|
CREATE PROCEDURE add_outhosp_serialnum_index()
|
BEGIN
|
DECLARE done INT DEFAULT 0;
|
DECLARE tbl VARCHAR(64);
|
DECLARE has_unique INT DEFAULT 0;
|
DECLARE has_normal INT DEFAULT 0;
|
DECLARE cur CURSOR FOR
|
SELECT TABLE_NAME
|
FROM information_schema.TABLES
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND (TABLE_NAME = 'pat_med_outhosp' OR TABLE_NAME REGEXP '^pat_med_outhosp_[0-9]{4,8}$');
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
|
|
OPEN cur;
|
add_loop:
|
LOOP
|
FETCH cur INTO tbl;
|
IF done THEN
|
LEAVE add_loop;
|
END IF;
|
|
SELECT COUNT(*) INTO has_unique
|
FROM information_schema.STATISTICS
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = tbl
|
AND COLUMN_NAME = 'serialnum' AND NON_UNIQUE = 0;
|
|
IF has_unique > 0 THEN
|
SELECT CONCAT('[跳过] ', tbl, ' 已有 serialnum 唯一索引') AS msg;
|
ELSE
|
-- 先清掉可能存在的非唯一索引（保留唯一索引一个就够）
|
SELECT COUNT(*) INTO has_normal
|
FROM information_schema.STATISTICS
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = tbl
|
AND COLUMN_NAME = 'serialnum' AND NON_UNIQUE = 1 AND INDEX_NAME = 'idx_serialnum';
|
IF has_normal > 0 THEN
|
SET @s = CONCAT('ALTER TABLE `', tbl, '` DROP INDEX idx_serialnum');
|
PREPARE st FROM @s;
|
EXECUTE st;
|
DEALLOCATE PREPARE st;
|
END IF;
|
|
SET @s = CONCAT('ALTER TABLE `', tbl, '` ADD UNIQUE INDEX uk_serialnum (serialnum)');
|
PREPARE st FROM @s;
|
EXECUTE st;
|
DEALLOCATE PREPARE st;
|
SELECT CONCAT('[完成] ', tbl, ' 已添加唯一索引 uk_serialnum') AS msg;
|
END IF;
|
END LOOP;
|
CLOSE cur;
|
END$$
|
DELIMITER ;
|
|
CALL add_outhosp_serialnum_index();
|
-- 完成后删掉过程：DROP PROCEDURE IF EXISTS add_outhosp_serialnum_index;
|
|
-- 如果某张表报 "Duplicate entry ... for key 'uk_serialnum'"，说明它还有重复数据，
|
-- 回到第 2 步清理该表后，单独执行：
|
-- ALTER TABLE `pat_med_outhosp_20261012` ADD UNIQUE INDEX uk_serialnum (serialnum);
|
|
|
-- ----------------------------------------------------------------------------
|
-- 第 4 步：校验（每张表都应该 has_unique_index = 1）
|
-- ----------------------------------------------------------------------------
|
SELECT t.TABLE_NAME,
|
(SELECT COUNT(*)
|
FROM information_schema.STATISTICS s
|
WHERE s.TABLE_SCHEMA = t.TABLE_SCHEMA
|
AND s.TABLE_NAME = t.TABLE_NAME
|
AND s.COLUMN_NAME = 'serialnum'
|
AND s.NON_UNIQUE = 0) AS has_unique_index
|
FROM information_schema.TABLES t
|
WHERE t.TABLE_SCHEMA = DATABASE()
|
AND (t.TABLE_NAME = 'pat_med_outhosp' OR t.TABLE_NAME REGEXP '^pat_med_outhosp_[0-9]{4,8}$')
|
ORDER BY t.TABLE_NAME;
|
