如何提升Laravel中数据库迁移速度？数据填充速度正常

Hey there, sorry to hear your database migrations are dragging so much—20 minutes for 30 operations is definitely way slower than it should be, especially with basic data types like int, varchar, and enum. Let’s break down some practical fixes and optimizations you can try right away:

A lot of slow migrations stem from poorly timed index creation. If your scripts are adding indexes one by one after creating tables, you’re wasting time on extra disk I/O and table locks. Instead, define indexes directly in your table creation statement:

-- Slow approach: Create table then add index
CREATE TABLE products (id INT, sku VARCHAR(50));
CREATE INDEX idx_products_sku ON products(sku);

-- Faster approach: Include index in table definition
CREATE TABLE products (
  id INT,
  sku VARCHAR(50),
  INDEX idx_products_sku(sku)
);

Also, double-check for redundant indexes—if you’ve got multiple indexes covering similar query patterns, ditch the ones you don’t actively use.

Foreign key checks add significant overhead when modifying tables that reference each other. Most databases let you turn these off during migrations, then re-enable them afterward (just make sure your data is consistent first!). For MySQL/MariaDB:

SET FOREIGN_KEY_CHECKS = 0;
-- Run all your migration scripts here
SET FOREIGN_KEY_CHECKS = 1;

For PostgreSQL, you can use SET session_replication_role = replica; to bypass constraints temporarily.

If your migration tool (like Laravel Migrations, Django Migrations) runs every single DDL statement in a separate transaction, that’s adding unnecessary commit/rollback overhead. Check if your tool supports bundling multiple operations into one transaction. For example, in Laravel:

DB::transaction(function () {
    Schema::create('users', function (Blueprint $table) {
        $table->id();
        $table->string('email');
    });

    Schema::create('orders', function (Blueprint $table) {
        $table->id();
        $table->foreignId('user_id')->constrained();
    });
});

Sometimes the issue isn’t your scripts—it’s the server itself. During migrations, monitor:

• Disk I/O: If you’re using spinning HDDs, switching to SSDs will drastically speed up DDL operations (since table creation/modification is heavy on disk writes).
• CPU/Memory: If the server is maxed out on CPU or low on memory, the database can’t process migrations efficiently. Tweak configs like innodb_buffer_pool_size (MySQL) or shared_buffers (PostgreSQL) to allocate more memory to the database.

If you’re running migrations from your local machine to a remote server, a slow network connection can add minutes of latency. Try running migrations directly on the server (via SSH or a terminal session) to cut out the middleman.

DDL operations often lock tables, and if other services are hitting the database while migrations run, you’ll end up waiting on locks. Schedule migrations during off-peak hours, or temporarily pause non-critical services that access the database during the migration.

Take a second look at your scripts—are you doing work you don’t need to? For example:

• Dropping and recreating tables when a simple ALTER TABLE would suffice.
• Adding columns with non-NULL default values (this forces the database to rewrite every row, even if the default is trivial).

Start with the first two steps—index optimization and foreign key disabling—since those are the most common culprits for slow migrations with basic data types. If you can share more details about your database system (MySQL, PostgreSQL, etc.) and migration tool, I can narrow this down even further!

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