多表合并插入单表性能优化：替代UNION的高效方案咨询

Hey there! I get it—waiting hours for a UNION-based table creation is no fun. Let's break down smarter, faster ways to move all your tables_1_1 through tables_35_3 data into one target table.

First, Fix the UNION Mistake (If You Made It) #

Chances are your initial slowdown came from using UNION instead of UNION ALL. UNION forces deduplication and sorting across all your tables, which is brutal for large datasets. Swap it out for UNION ALL in a direct INSERT, and you'll see an immediate speed boost:

INSERT INTO target_table (col1, col2, col3)
SELECT col1, col2, col3 FROM tables_1_1
UNION ALL
SELECT col1, col2, col3 FROM tables_1_2
UNION ALL
-- Repeat for all your tables...
SELECT col1, col2, col3 FROM tables_35_3;

This skips the unnecessary sorting step and just appends all rows directly.

1. Use a Stored Procedure to Loop Through Tables #

If you have dozens of tables, writing out every UNION ALL line is tedious. A stored procedure can automate looping through all matching tables and inserting their data one by one—this also avoids loading all data into memory at once.

Here's a MySQL example (adjust syntax for your database):

DELIMITER //
CREATE PROCEDURE BulkInsertFromTables()
BEGIN
  DECLARE table_name VARCHAR(255);
  DECLARE done INT DEFAULT FALSE;
  -- Cursor to fetch all tables matching your pattern
  DECLARE table_cursor CURSOR FOR 
    SELECT table_name 
    FROM information_schema.tables 
    WHERE table_name LIKE 'tables_%_%' 
      AND table_schema = DATABASE(); -- Target your current schema
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN table_cursor;
  insert_loop: LOOP
    FETCH table_cursor INTO table_name;
    IF done THEN
      LEAVE insert_loop;
    END IF;
    -- Dynamically build and execute the INSERT statement
    SET @insert_sql = CONCAT(
      'INSERT INTO target_table SELECT * FROM ', table_name
    );
    PREPARE stmt FROM @insert_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;
  CLOSE table_cursor;
END //
DELIMITER ;

-- Run the procedure
CALL BulkInsertFromTables();

Pro tip: Add batch commits (e.g., commit every 10 tables) to reduce transaction log overhead, especially for large tables.

2. Optimize the Target Table for Fast Inserts #

Before you start inserting, tweak the target table to minimize overhead:

• Drop indexes and foreign keys temporarily. Rebuild them after all inserts are done—maintaining indexes during every insert kills performance.
• Turn off autocommit (for InnoDB) or use batch transactions. For example, in MySQL, set SET autocommit = 0; at the start, then COMMIT; every 5-10 table inserts.
• Temporarily adjust database settings: For InnoDB, set innodb_flush_log_at_trx_commit = 2 (reduces disk I/O) and bump up innodb_buffer_pool_size if you have extra RAM. Reset these after the migration.

3. Use Database-Specific Bulk Tools #

For maximum speed, leverage your database's built-in bulk load utilities:

• MySQL: Export each source table to CSV with SELECT ... INTO OUTFILE, then use LOAD DATA INFILE to batch-import all CSVs into the target table. This is way faster than INSERT/SELECT for large datasets.
• PostgreSQL: Use COPY to export tables to CSV, then COPY target_table FROM 'file.csv' to import. You can also add the PARALLEL hint to your INSERT/SELECT if your PostgreSQL version supports it.
• SQL Server: Use the bcp utility for export/import, or run INSERT INTO target_table WITH (TABLOCK) SELECT * FROM source_table—the TABLOCK hint enables minimal logging for faster bulk inserts.

4. Parallel Processing (If You Have the Resources) #

If your server has multiple CPU cores, split the work across parallel processes:

• Write a shell script or Python script that spawns multiple threads/processes, each handling a subset of tables (e.g., one process handles tables_1_*, another tables_2_*, etc.).
• Test with a small subset first to avoid locking conflicts—most databases handle concurrent INSERTs to the same table smoothly.

To recap: Start with UNION ALL instead of UNION if you stick with the direct INSERT approach. For larger datasets, use a stored procedure for automation, optimize the target table, or go all-in with bulk tools. Parallel processing can cut down time even more if you have the hardware.

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