如何在生产环境中千万表添加索引并保证数据一致性

技术分享文档：如何在生产环境中千万表添加索引并保证数据一致性

目录

1. 引言
2. 添加索引的挑战
3. 解决方案概述
4. 详细步骤
   4.1 创建新表并添加索引
   4.2 批量导入数据
   4.3 处理增量数据
   4.4 表名切换
5. 确保数据一致性
   5.1 暂停写操作
   5.2 记录增量数据
   5.3 应用增量数据
6. 设置回滚计划
   6.1 备份原表
   6.2 使用事务
   6.3 预备切换回旧表
7. 监控和验证
8. 总结

1. 引言

在电商平台中，商品评论表（product_reviews）是一个关键的表，记录了用户对商品的评价信息。随着评论数量的增加，查询和检索评论的效率变得尤为重要。本分享文档将介绍如何在生产环境中安全地对product_reviews表添加索引，以提高查询性能，同时保证数据一致性。

2. 添加索引的挑战

• 性能影响：直接在生产环境中添加索引可能导致长时间锁表，影响读写操作。
• 数据一致性：在添加索引过程中，如果有新的数据插入、更新或删除，可能导致数据不一致。
• 停机时间：为了尽量减少停机时间，需要找到一种可以在线执行的方案。

3. 解决方案概述

通过以下步骤，可以在生产环境中安全地对product_reviews表添加索引：

1. 创建新表并添加所需索引。
2. 批量将原表数据导入新表。
3. 处理在数据导入过程中产生的增量数据。
4. 切换表名，将新表替换为原表。

4. 详细步骤

4.1 创建新表并添加索引

首先，创建一张与product_reviews表结构相同的新表，并在新表上添加所需的索引。例如，我们需要为product_id和created_at字段添加索引，以加速基于商品和时间的查询。

CREATE TABLE new_product_reviews LIKE product_reviews;
CREATE INDEX idx_product_id ON new_product_reviews (product_id);
CREATE INDEX idx_created_at ON new_product_reviews (created_at);

4.2 批量导入数据

使用分页查询，将原表数据批量导入新表，以减少对系统的影响。

INSERT INTO new_product_reviews SELECT * FROM product_reviews WHERE id BETWEEN start_id AND end_id;

4.3 处理增量数据

在数据导入过程中，如果无法暂停写操作，需要记录并处理增量数据。可以使用触发器来记录所有的更改。

CREATE TABLE delta_changes (id INT,change_type CHAR(1),change_time TIMESTAMP,old_values TEXT,new_values TEXT
);CREATE TRIGGER record_changes
AFTER INSERT OR UPDATE OR DELETE ON product_reviews
FOR EACH ROW
BEGININSERT INTO delta_changes (id, change_type, change_time, old_values, new_values)VALUES (OLD.id, CASE WHEN OLD.id IS NULL THEN 'I' WHEN NEW.id IS NULL THEN 'D' ELSE 'U' END, NOW(), OLD.values, NEW.values);
END;

4.4 表名切换

在确保新表数据与原表完全一致后，进行表名切换操作。

RENAME TABLE product_reviews TO product_reviews_backup, new_product_reviews TO product_reviews;

5. 确保数据一致性

5.1 暂停写操作

在执行数据导入和切换操作之前，尽量暂停对product_reviews表的所有写操作，以确保数据一致性。

5.2 记录增量数据

如果无法暂停写操作，可以使用触发器或应用层日志记录所有对product_reviews表的写操作。

5.3 应用增量数据

在导入数据后，将记录的增量数据应用到新表，以确保数据一致性。

-- 插入增量数据
INSERT INTO product_reviews (columns) SELECT new_values FROM delta_changes WHERE change_type = 'I';-- 更新增量数据
UPDATE product_reviews SET columns = new_values FROM delta_changes WHERE change_type = 'U' AND id = delta_changes.id;-- 删除增量数据
DELETE FROM product_reviews WHERE id IN (SELECT id FROM delta_changes WHERE change_type = 'D');

6. 设置回滚计划

6.1 备份原表

在进行操作之前，备份product_reviews表的数据，以防出现问题时可以快速恢复。

CREATE TABLE product_reviews_backup AS SELECT * FROM product_reviews;

6.2 使用事务

在执行重要操作时，使用事务以便在出现错误时可以回滚。

START TRANSACTION;-- 进行数据操作-- 如果成功，提交事务
COMMIT;-- 如果失败，回滚事务
ROLLBACK;

6.3 预备切换回旧表

在切换表名操作之前，准备好切换回旧表的脚本，以防新表有问题时能快速恢复。

RENAME TABLE product_reviews_backup TO product_reviews;  -- 在出现问题时执行此操作

7. 监控和验证

在操作完成后，仔细监控新表的性能和数据一致性，确保其正常工作。如果发现问题，立即执行回滚操作。

8. 总结

通过本文介绍的方法，可以有效地在生产环境中对product_reviews表添加索引，并最小化对系统的影响。同时，通过谨慎的操作和全面的回滚计划，可以确保数据的一致性和系统的稳定性。希望这份文档对您的工作有所帮助。

---

这个技术分享文档可以帮助团队成员理解在生产环境中添加索引的最佳实践，并确保操作的安全性和有效性。