先在测试环境测试,没问题再上生产环境,避免生产环境数据库负载过多而崩溃
- DELIMITER //
-
- CREATE PROCEDURE batch_add_index_to_email()
- BEGIN
- DECLARE done INT DEFAULT FALSE;
- DECLARE start_id INT DEFAULT 0;
- DECLARE end_id INT;
- DECLARE batch_size INT DEFAULT 10000;
-
- -- 获取最大的ID
- SELECT MAX(id) INTO end_id FROM test_table;
-
- WHILE start_id < end_id DO
- -- 在每批次的数据上添加索引
- SET @sql = CONCAT(
- 'ALTER TABLE your_table ADD INDEX idx_email (email);'
- );
-
- -- 执行动态 SQL
- PREPARE stmt FROM @sql;
- EXECUTE stmt;
- DEALLOCATE PREPARE stmt;
-
- -- 更新批次的起始ID
- SET start_id = start_id + batch_size;
- END WHILE;
- END //
-
- DELIMITER ;
执行存储过程
CALL batch_add_index_to_email()