Aurora MySQL存储过程中CREATE TABLE语句执行缓慢问题求助

Let's break down what's happening here, and why your fix of adding the primary key directly in the CREATE TABLE statement worked.

First, Recapping Your Scenario #

You've got a correlated CREATE TABLE ... SELECT query that runs reliably in ~14 seconds when executed standalone. But toss it into a stored procedure or run the procedure code as a regular SQL script, and it either takes forever or never completes. Splitting it into a separate CREATE TABLE followed by INSERT INTO didn't help—but when you switched to defining the primary key right in the CREATE TABLE clause (instead of creating the table first and adding the key via ALTER later), the problem vanished. You're on Aurora MySQL 5.7.mysql_aurora.2.07.1/2, having migrated from MyISAM to InnoDB, and you're curious why this fix works and how to debug similar issues.

Why Defining the Primary Key Upfront Fixes This #

The root cause ties directly to how InnoDB (and by extension, Aurora's optimized InnoDB implementation) organizes data and handles index creation:

1. InnoDB's Clustered Index Behavior #

InnoDB is a clustered index storage engine—this means the table's data is physically stored as part of the primary key index. If you create a table without a primary key, InnoDB automatically uses a hidden ROW_ID column as the clustered index. When you later add a primary key via ALTER TABLE, InnoDB has to completely rebuild the entire table: it reads every row, sorts it by the new primary key, and rewrites all the data to disk.

In a standalone query, this rebuild might be fast enough to not notice, but in a stored procedure context (which often runs within a single transaction, or has different resource scheduling), this full table rebuild can get stuck waiting for locks, exhaust IOPS, or trigger Aurora's internal replication/snapshot mechanisms that slow things to a crawl.

By defining the primary key in the CREATE TABLE ... SELECT statement, you skip this costly post-insert rebuild. InnoDB writes the data directly in the order of the primary key as it inserts rows from the SELECT query—this is far more efficient, especially for large datasets.

2. Stored Procedure Optimizer & Transaction Differences #

MySQL (and Aurora) can behave differently when executing queries inside a stored procedure vs. standalone:

• Execution Plan Caching: Stored procedures precompile execution plans, which might not account for the dynamic data distribution in your correlated query. When you add a primary key upfront, the optimizer can use that key to generate a more efficient join/sort plan for the SELECT part of the query.
• Transaction Context: Stored procedures run in a single transaction by default (unless you explicitly commit mid-procedure). Writing to a table without a primary key generates more undo/redo log activity, and holding that transaction open while the ALTER runs can lead to log bloat and lock contention. The upfront primary key reduces this log overhead and keeps the transaction shorter.

3. Aurora-Specific Optimizations #

Aurora has its own storage layer optimizations for bulk writes and index creation. When you create the table with the primary key and populate it in one step, Aurora can leverage its parallel write capabilities and avoid the extra overhead of syncing a table rebuild across replicas. The ALTER TABLE approach, by contrast, triggers a full table rewrite that has to be replicated to all Aurora read replicas, which adds significant latency.

Debugging Steps to Dig Deeper #

If you want to confirm the root cause or troubleshoot similar issues later, try these steps:

• Compare Execution Plans: Run EXPLAIN on the SELECT part of your query both standalone and inside the stored procedure. Look for differences in join types, index usage, or sort operations—you might see that the stored procedure was using a full table scan instead of leveraging the primary key.
• Check Resource Usage: Monitor Aurora's CloudWatch metrics (CPU, IOPS, disk latency) while running the procedure. If IOPS spike during the problematic run, that's a sign the table rebuild was hitting storage limits.
• Inspect InnoDB Status: Use SHOW ENGINE INNODB STATUS while the slow procedure is running to check for lock waits, transaction backlogs, or log buffer issues.
• Test Transaction Boundaries: Try adding explicit COMMIT statements in the stored procedure after table creation steps to see if releasing the transaction context improves performance.
• Review Optimizer Settings: Check if your stored procedure is overriding optimizer settings (like optimizer_switch or join_buffer_size) that could be forcing a suboptimal plan.

Wrap-Up #

Your fix worked because you eliminated the costly post-insert primary key rebuild that was getting bottlenecked in the stored procedure's transactional context. By letting InnoDB build the clustered index as it inserts data, you're playing to InnoDB's strengths—and taking advantage of Aurora's optimized storage layer to handle the bulk write efficiently.

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