如何为指定schema内的所有表批量添加相同的独立非唯一索引

方案一：通过系统表批量生成DDL语句（无脚本的极简方案） #

不需要编写外部PHP脚本，直接通过数据库自带的INFORMATION_SCHEMA系统表就可以生成所有表的建索引语句，以MySQL为例操作步骤如下：

• 执行查询生成批量建索引语句，示例中为所有归档表添加idx_create_time、idx_uid两个非唯一索引，你可以根据自己的需求修改索引字段和名称：

SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` ADD INDEX idx_create_time(`create_time`), ADD INDEX idx_uid(`uid`);') 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = '替换为你的归档schema名称'
-- 如果归档表有统一命名前缀，可增加下面的条件过滤，避免误操作其他业务表
AND TABLE_NAME LIKE '归档表前缀%';

• 导出上述查询的所有结果，先抽取2-3条测试执行无问题后，即可全量执行所有生成的ALTER语句。

如果你的数据库是PostgreSQL，只需要把系统表替换为pg_tables调整下CONCAT的语法即可，逻辑完全一致。

关于单条查询/事务实现的可行性 #

• 无法用单条原生SQL直接完成全量建索引的需求，现有SQL标准没有提供批量操作多表建索引的语法。
• 不建议用事务包裹所有DDL操作：
  • MySQL等大多数数据库执行DDL时会触发隐式提交，包裹事务不会生效。
  • 即使是PostgreSQL这类支持DDL事务的数据库，几百张大表的建索引操作放在同一个事务中，一旦出错回滚耗时会是正常执行的数倍，风险极高。

优化建议 #

如果你的归档表数据量较大、担心建索引锁表影响业务，可以在生成ALTER语句时增加无锁参数，以MySQL为例：

SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` ADD INDEX idx_create_time(`create_time`), ADD INDEX idx_uid(`uid`), ALGORITHM=INPLACE, LOCK=NONE;') 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = '替换为你的归档schema名称';

参数ALGORITHM=INPLACE会避免建索引时生成临时表拷贝全量数据，LOCK=NONE允许建索引期间表的正常读写，大大降低对业务的影响。

内容的提问来源于stack exchange，提问作者quayfee