面试题：SQLite复杂表结构修改的事务处理与性能优化

Question

假设有一个SQLite数据库，其中有一张'orders'表，表结构复杂且关联了其他多张表。现在需要对'orders'表进行一系列修改，包括删除某些列、新增一些列并修改部分列的数据类型。请详细描述如何使用事务处理来确保操作的原子性，以及在修改过程中如何进行性能优化，避免长时间锁表影响数据库的正常使用，写出具体的思路和关键的SQL语句。

Answer 1

事务处理确保操作原子性思路

1. 开启事务：使用 BEGIN TRANSACTION; 语句开启一个事务，这样后续的所有修改操作都将作为一个整体，要么全部成功，要么全部回滚。
2. 执行修改操作：依次执行删除列、新增列和修改列数据类型的 SQL 语句。
3. 提交事务：如果所有修改操作都成功，使用 COMMIT; 语句提交事务，将修改永久保存到数据库。
4. 异常处理：如果在修改过程中出现任何错误，使用 ROLLBACK; 语句回滚事务，撤销之前的所有修改，确保数据库状态不变。

关键 SQL 语句

1. 删除列：

   ALTER TABLE orders DROP COLUMN column_to_delete;
   

2. 新增列：

   ALTER TABLE orders ADD COLUMN new_column data_type;
   

3. 修改列数据类型： 由于 SQLite 不支持直接修改列的数据类型，通常需要通过以下步骤实现：
   • 创建一个临时表：

     CREATE TEMPORARY TABLE orders_temp AS SELECT * FROM orders;
     

   • 删除原表：

     DROP TABLE orders;
     

   • 创建新表：

     CREATE TABLE orders (
       -- 按照原表结构定义列，但修改需要修改的数据类型
       column1 data_type1,
       column2 data_type2,
       new_column data_type3,
       -- 其他列...
     );
     

   • 将临时表数据插入新表：

     INSERT INTO orders SELECT * FROM orders_temp;
     

   • 删除临时表：

     DROP TABLE orders_temp;
     

性能优化思路

1. 批量操作：尽量将多个修改操作合并在一个事务中执行，减少事务的数量，从而减少锁表的次数。
2. 选择合适的时间：在数据库负载较低的时间段执行这些操作，例如凌晨等业务低谷期。
3. 使用索引：在修改完成后，确保相关列上的索引已正确重建或创建，以提高查询性能。
4. 减少锁粒度：在可能的情况下，尽量使用行级锁而不是表级锁。但 SQLite 本身在很多情况下会自动处理锁粒度，不过了解这一点有助于整体性能优化的思考。