209 lines
6.5 KiB
SQL
Executable File
209 lines
6.5 KiB
SQL
Executable File
-- 安全添加索引脚本（检查后添加，避免重复）
|
|
-- 使用方法：在MySQL客户端或phpMyAdmin中执行
|
|
|
|
-- ============================================
|
|
-- 1. 检查现有索引
|
|
-- ============================================
|
|
SELECT
|
|
TABLE_NAME,
|
|
INDEX_NAME,
|
|
GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) as COLUMNS
|
|
FROM
|
|
INFORMATION_SCHEMA.STATISTICS
|
|
WHERE
|
|
TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME IN (
|
|
'zyt_appointment',
|
|
'zyt_tcm_diagnosis',
|
|
'zyt_admin',
|
|
'zyt_diagnosis_view_records',
|
|
'zyt_prescription',
|
|
'zyt_admin_role'
|
|
)
|
|
GROUP BY
|
|
TABLE_NAME, INDEX_NAME
|
|
ORDER BY
|
|
TABLE_NAME, INDEX_NAME;
|
|
|
|
-- ============================================
|
|
-- 2. 安全添加索引（如果不存在）
|
|
-- ============================================
|
|
|
|
-- 预约表索引
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'zyt_appointment'
|
|
AND INDEX_NAME = 'idx_doctor_id') = 0,
|
|
'ALTER TABLE `zyt_appointment` ADD INDEX `idx_doctor_id` (`doctor_id`)',
|
|
'SELECT "索引 idx_doctor_id 已存在" as message'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'zyt_appointment'
|
|
AND INDEX_NAME = 'idx_patient_id') = 0,
|
|
'ALTER TABLE `zyt_appointment` ADD INDEX `idx_patient_id` (`patient_id`)',
|
|
'SELECT "索引 idx_patient_id 已存在" as message'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'zyt_appointment'
|
|
AND INDEX_NAME = 'idx_status') = 0,
|
|
'ALTER TABLE `zyt_appointment` ADD INDEX `idx_status` (`status`)',
|
|
'SELECT "索引 idx_status 已存在" as message'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'zyt_appointment'
|
|
AND INDEX_NAME = 'idx_appointment_date') = 0,
|
|
'ALTER TABLE `zyt_appointment` ADD INDEX `idx_appointment_date` (`appointment_date`)',
|
|
'SELECT "索引 idx_appointment_date 已存在" as message'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'zyt_appointment'
|
|
AND INDEX_NAME = 'idx_status_date_time') = 0,
|
|
'ALTER TABLE `zyt_appointment` ADD INDEX `idx_status_date_time` (`status`, `appointment_date`, `appointment_time`)',
|
|
'SELECT "索引 idx_status_date_time 已存在" as message'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'zyt_appointment'
|
|
AND INDEX_NAME = 'idx_doctor_status_date') = 0,
|
|
'ALTER TABLE `zyt_appointment` ADD INDEX `idx_doctor_status_date` (`doctor_id`, `status`, `appointment_date`)',
|
|
'SELECT "索引 idx_doctor_status_date 已存在" as message'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- 诊断表索引
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'zyt_tcm_diagnosis'
|
|
AND INDEX_NAME = 'idx_patient_name') = 0,
|
|
'ALTER TABLE `zyt_tcm_diagnosis` ADD INDEX `idx_patient_name` (`patient_name`(20))',
|
|
'SELECT "索引 idx_patient_name 已存在" as message'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'zyt_tcm_diagnosis'
|
|
AND INDEX_NAME = 'idx_assistant_id') = 0,
|
|
'ALTER TABLE `zyt_tcm_diagnosis` ADD INDEX `idx_assistant_id` (`assistant_id`)',
|
|
'SELECT "索引 idx_assistant_id 已存在" as message'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- 管理员表索引
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'zyt_admin'
|
|
AND INDEX_NAME = 'idx_name') = 0,
|
|
'ALTER TABLE `zyt_admin` ADD INDEX `idx_name` (`name`(20))',
|
|
'SELECT "索引 idx_name 已存在" as message'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- 诊断查看记录表索引
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'zyt_diagnosis_view_records'
|
|
AND INDEX_NAME = 'idx_diagnosis_confirmed') = 0,
|
|
'ALTER TABLE `zyt_diagnosis_view_records` ADD INDEX `idx_diagnosis_confirmed` (`diagnosis_id`, `is_confirmed`, `delete_time`)',
|
|
'SELECT "索引 idx_diagnosis_confirmed 已存在" as message'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- 处方表索引
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'zyt_prescription'
|
|
AND INDEX_NAME = 'idx_diagnosis_delete') = 0,
|
|
'ALTER TABLE `zyt_prescription` ADD INDEX `idx_diagnosis_delete` (`diagnosis_id`, `delete_time`)',
|
|
'SELECT "索引 idx_diagnosis_delete 已存在" as message'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- 管理员角色表索引
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'zyt_admin_role'
|
|
AND INDEX_NAME = 'idx_admin_role') = 0,
|
|
'ALTER TABLE `zyt_admin_role` ADD INDEX `idx_admin_role` (`admin_id`, `role_id`)',
|
|
'SELECT "索引 idx_admin_role 已存在" as message'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- ============================================
|
|
-- 3. 再次检查索引是否创建成功
|
|
-- ============================================
|
|
SELECT
|
|
TABLE_NAME,
|
|
INDEX_NAME,
|
|
GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) as COLUMNS,
|
|
INDEX_TYPE,
|
|
NON_UNIQUE
|
|
FROM
|
|
INFORMATION_SCHEMA.STATISTICS
|
|
WHERE
|
|
TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME IN (
|
|
'zyt_appointment',
|
|
'zyt_tcm_diagnosis',
|
|
'zyt_admin',
|
|
'zyt_diagnosis_view_records',
|
|
'zyt_prescription',
|
|
'zyt_admin_role'
|
|
)
|
|
GROUP BY
|
|
TABLE_NAME, INDEX_NAME, INDEX_TYPE, NON_UNIQUE
|
|
ORDER BY
|
|
TABLE_NAME, INDEX_NAME;
|
|
|
|
SELECT '索引创建完成！' as message;
|