MySQL高频更新表性能优化咨询：磁盘重写与存储引擎选择

Hey there, let's tackle your questions one by one—sounds like you're stuck with a classic shared-hosting IO bottleneck on a table that gets lots of INT/CHAR updates, which is never a fun spot to be in. Here's what you need to know:

1. Will deleting and rewriting the table at night boost performance? #

Absolutely—this is a proven trick to fix table fragmentation, which is almost certainly a major contributor to your slowdown. Frequent updates (even on fixed-length types like INT, but especially with CHAR) leave InnoDB with fragmented data pages and scattered unused space. Deleting and rewriting the table rebuilds it from scratch:

• It organizes data into contiguous pages, cutting down on the number of IO operations needed to read/write data.
• It cleans up leftover space that accumulated from repeated updates and deletions.

Safe step-by-step for nighttime execution: #

• Backup first: Always dump the table before making changes—run mysqldump your_db your_table > backup.sql to create a safety copy.
• Create a clean copy: Use CREATE TABLE new_table AS SELECT * FROM your_table; to generate a fresh, unfragmented version. Don't forget to recreate all indexes on new_table (the CREATE TABLE ... AS SELECT command doesn't copy indexes automatically).
• Swap tables: Rename the original table to a backup name, then replace it with the new one: RENAME TABLE your_table TO your_table_old, new_table TO your_table;.
• Clean up: Once you confirm everything works as expected, drop the old table to free up space.

Since you can restrict access at night, you won't have to worry about concurrent writes messing up the process.

2. Does MySQL have a PostgreSQL-style VACUUM feature? #

Yes! For InnoDB (which you should be using), the OPTIMIZE TABLE command acts like PostgreSQL's VACUUM FULL. It rebuilds the table and all its indexes, defragments data pages, and reclaims unused space.

Key notes for using OPTIMIZE TABLE: #

• It locks the table during execution, so running it at night (when traffic is low or restricted) is non-negotiable—just like your table rewrite plan.
• Under the hood for InnoDB, OPTIMIZE TABLE is a wrapper around ALTER TABLE ... ENGINE=InnoDB, which triggers a full table rebuild.
• If you only need to update table statistics for the query optimizer (not a full defrag), use ANALYZE TABLE instead—it's faster and doesn't lock the table for nearly as long.

InnoDB also does automatic background "purge" operations to clean up old undo logs, but manual OPTIMIZE TABLE gives you a thorough, one-time cleanup.

3. Are there better storage engines for high-frequency updates, configurable via PHP? #

First off, InnoDB is already the best choice for high-frequency updates in most scenarios. Unlike MyISAM (which uses table-level locks), InnoDB uses row-level locks, so concurrent updates don't block each other as much. If you're still on MyISAM, switching to InnoDB alone could give you a massive performance lift.

Other options (with critical caveats): #

• Memory Engine: Stores data in RAM, making updates blazingly fast—but all data is lost when MySQL restarts. This is only practical if you can repopulate the table every time the server boots, which isn't feasible for most persistent web app data.
• TokuDB: Built for high write throughput and large datasets, but most shared hosting providers don't install this third-party engine. You'd need your host to enable it, which is unlikely on a shared instance.

PHP-configurable optimizations for InnoDB: #

• Adjust transaction isolation: Set the session isolation level to READ COMMITTED in your PHP code with SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;—this reduces lock contention compared to the default REPEATABLE READ.
• Supercharge batch updates: You already moved to a single transaction for 50+ updates, which is great. Take it further with INSERT ... ON DUPLICATE KEY UPDATE if your updates rely on unique keys—this is far more efficient than running multiple UPDATE statements even within a transaction.
• Trim unnecessary indexes: Too many indexes slow down updates, since each update has to modify all relevant indexes. Audit your table's indexes and remove any that aren't strictly required for queries.

Bonus Quick Wins #

• Check slow query logs: Ask your hosting provider to enable slow query logs, or use EXPLAIN on your update statements to spot inefficiencies.
• Avoid redundant updates: Make sure your PHP code only updates columns that actually changed—don't overwrite every column in a row if only one value has been modified.

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