MySQL存储引擎选型咨询：InnoDB与MyISAM在电影剧本数据更新场景下的性能差异及适配方案

Hey there, let's break down your questions one by one based on your movie script data update scenario and the details you shared:

Background Recap #

• Environment: Windows 10, MySQL Workbench 8.0 CE
• Data: 1014 movie script records (~200MB total)
• Operation: Python script updating the script_clean field in clean_movie_script
• Performance Gap: 40 minutes with InnoDB vs. 2 minutes with MyISAM (20x speed difference)
• Engine Switch Side Effect: id column changed from INT PK NN Unique AutoIncrement to INT NN DEFAULT 0, and you can't switch back to InnoDB

Your original update script (for reference):

from random import randint
from time import sleep
import requests
from bs4 import BeautifulSoup as bs
import json
import pymysql
import traceback
import logging
from tqdm import tqdm

logging.basicConfig(format='%(asctime)s - %(message)s', level=logging.INFO)
mysql_code = "password"

def getData(id, script_raw):
    script_clean = remove_html_tags(script_raw).replace("'","''")
    save_data(script_clean, id)

def remove_html_tags(text):
    import re
    clean = re.compile('<.*?>')
    return re.sub(clean, '', text)

def save_data(script_clean, id):
    try:
        conn = pymysql.connect(host='localhost', user='admin', passwd=mysql_code, db='manuscriptproject')
        cur = conn.cursor()
        query = "UPDATE `clean_movie_script` SET `script_clean` = '%s' WHERE (`id` = '%s');"
        final_query = query % (script_clean, id)
        cur.execute(final_query)
        conn.commit()
        cur.close()
        conn.close()
    except Exception as e:
        logging.info("Error with query for id : " + str(id))
        logging.error(traceback.format_exc())
        logging.error(e)

def get_non_populated_records():
    conn = pymysql.connect(host='localhost', user='admin', passwd=mysql_code, db='manuscriptproject')
    cur = conn.cursor()
    cur.execute(
        "SElECT id, script FROM `movie_script` "
        "WHERE script IS NOT NULL "
        "ORDER BY id asc "
        "LIMIT 100000")
    data = list(cur.fetchall())
    conn.close()
    return data

if __name__ == "__main__":
    unpopulated_records = get_non_populated_records()
    for x in tqdm(unpopulated_records):
        try:
            getData(x[0], x[1])
        except Exception as e:
            print(e)

1. Should I choose InnoDB or MyISAM for my movie script update scenario? #

Short answer: InnoDB is the better long-term choice, but you need to optimize your script and table structure to match its strengths. Here's why:

• Data Safety: InnoDB supports transactions, crash recovery, and foreign keys—critical if you can't afford to lose partial updates or corrupt data (e.g., if your script fails mid-run). MyISAM has no transaction support, so a crash could leave your table in an inconsistent state.
• Scalability: InnoDB uses row-level locking instead of MyISAM's table-level locking. If you ever need to run concurrent updates (e.g., multiple scripts or users modifying data at once), InnoDB won't lock the entire table, which keeps performance high.
• MySQL's Default: InnoDB has been MySQL's default engine for over a decade, so it gets more active development, bug fixes, and optimizations than MyISAM (which is essentially maintenance-only now).

That said, MyISAM will feel faster for simple, single-threaded batch updates like your current script—but that speed comes with significant tradeoffs in data safety and future scalability.

2. Why is InnoDB so much slower than MyISAM, and does the id column configuration play a role? #

The 20x performance gap isn't just about the engine—it's a combination of how your script interacts with the database and engine-specific behavior, plus the id column change:

Primary Culprit: Your Script's Connection/Transaction Pattern #

Your script opens a new database connection, runs a single UPDATE, commits, and closes the connection for every single record. That's catastrophic for InnoDB's performance:

• InnoDB treats every single UPDATE as a separate transaction. By default, it flushes its redo log to disk on every commit (innodb_flush_log_at_trx_commit=1), which adds massive disk I/O overhead for 1000+ small transactions.
• MyISAM has no transaction system—writes are directly applied to the table, so it avoids this transaction log flush overhead entirely.

Role of the id Column #

• Original InnoDB Setup: When id was a primary key, InnoDB used it as the clustered index. Updates based on the clustered index are very efficient because the row data is stored directly with the index. This should have made InnoDB fast—but your script's connection/transaction pattern completely negated this advantage.
• Current MyISAM Setup: By removing the primary key and unique constraint from id, you turned MyISAM into a heap table (no clustered index). For simple single-row updates, this is slightly faster—but it's also dangerous: you can now have duplicate id values, which breaks your data integrity.
• Why You Can't Switch Back to InnoDB: InnoDB requires a primary key (if you don't define one, it creates a hidden 6-byte primary key). Since your current id column isn't unique or auto-incrementing, MySQL can't use it as a primary key. To switch back, you need to restore the id column's PK/Unique/AutoIncrement properties first.

3. Can I optimize the 2-minute MyISAM update time even further? #

Absolutely—2 minutes for 1014 records is still slower than it needs to be. Here are the top optimizations you can make:

Fix the Script's Connection/Transaction Pattern #

• Reuse a Single Connection: Instead of opening/closing a connection for every update, create one connection at the start of your script and reuse it for all updates. This eliminates the overhead of establishing a connection 1000+ times.
• Batch Commits: Disable auto-commit, then commit in batches (e.g., every 100 records) instead of after every single update. This reduces the number of disk flushes (even for MyISAM, this helps with write caching).
• Use Parameterized Queries: Your current string concatenation is not only a SQL injection risk but also forces MySQL to parse the query every time. Switch to parameterized queries:

  # Replace your save_data function's query with this:
  query = "UPDATE `clean_movie_script` SET `script_clean` = %s WHERE `id` = %s;"
  cur.execute(query, (script_clean, id))  # No string replacement needed!
  

  Parameterized queries let MySQL cache the query plan, which speeds up repeated executions.

Database-Level Optimizations #

• Add Back the Primary Key to id: Even for MyISAM, a primary key will speed up updates because MySQL can quickly locate the row using the index instead of scanning the entire table.
• Tune MyISAM Write Buffers: Increase myisam_sort_buffer_size and key_buffer_size (if you have enough RAM) to speed up index updates.
• Disable Indexes Temporarily: If you're doing a massive batch update, you can disable non-primary indexes before the update and rebuild them afterward. This avoids updating indexes for every single row.

Advanced: Batch Update Statements #

Instead of running 1014 separate UPDATE queries, you can generate a single batch update query using CASE statements. For example:

UPDATE clean_movie_script
SET script_clean = CASE
    WHEN id = 1 THEN 'clean script 1'
    WHEN id = 2 THEN 'clean script 2'
    -- ... more cases ...
END
WHERE id IN (1, 2, ...);

This reduces the number of round-trips between your Python script and MySQL from 1014 to 1, which can cut down runtime drastically.

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