


How to Create an Index on a Large MySQL Production Table Without Table Locking?
Oct 31, 2024 am 11:34 AMHow to Create an Index on a Large MySQL Production Table Without Table Locking
Problem Background:
Creating an index on a large MySQL table can be a daunting task, especially in a production environment where uninterrupted access is crucial. Traditional CREATE INDEX statements can result in a complete table lock, blocking all concurrent operations.
MySQL Version Considerations:
- In MySQL 5.6 and higher, index updates are performed online, allowing read and write operations to continue during index creation.
- However, in MySQL 5.5 and earlier, including InnoDB and MyISAM tables, index updates will block writes to the table.
Circular Masters Approach:
For MySQL versions prior to 5.6, one effective approach is the circular masters technique:
- Set up a secondary master (Master B) replicating from the primary master (Master A).
- Perform the schema update on Master B (allowing it to fall behind during the upgrade).
- Ensure that the schema change is compatible with replicating commands from the downversion schema on Master A.
- Atomically switch all clients from Master A to Master B.
- Update the schema on Master A and make it the secondary master.
Percona's pt-online-schema-change Tool:
This tool automates the circular masters approach by:
- Creating a new table with the updated schema.
- Keeping the new table in sync with the original table using a trigger.
- Copying rows in batches from the original table.
- Replacing the original table with the new table.
AWS RDS Considerations:
For MySQL databases hosted on Amazon's RDS, the "Read Replica Promotion" feature can be used to facilitate schema changes without table locking. This involves making changes on a read-only slave and then promoting it to become the new master.
Alternative Techniques:
- Use a temporary table: Create a temporary table with the new index, insert data from the original table, and replace the original table with the temporary table.
- Partition the table: Split the table into smaller partitions, create the index on each partition separately, and then merge the partitions back together.
- Use a background process: Create a separate background process that gradually creates the index while the table remains accessible for normal operations. This approach is not supported in all MySQL versions.
The above is the detailed content of How to Create an Index on a Large MySQL Production Table Without Table Locking?. For more information, please follow other related articles on the PHP Chinese website!

Hot AI Tools

Undress AI Tool
Undress images for free

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

TosecurelyconnecttoaremoteMySQLserver,useSSHtunneling,configureMySQLforremoteaccess,setfirewallrules,andconsiderSSLencryption.First,establishanSSHtunnelwithssh-L3307:localhost:3306user@remote-server-Nandconnectviamysql-h127.0.0.1-P3307.Second,editMyS

MySQL's default transaction isolation level is RepeatableRead, which prevents dirty reads and non-repeatable reads through MVCC and gap locks, and avoids phantom reading in most cases; other major levels include read uncommitted (ReadUncommitted), allowing dirty reads but the fastest performance, 1. Read Committed (ReadCommitted) ensures that the submitted data is read but may encounter non-repeatable reads and phantom readings, 2. RepeatableRead default level ensures that multiple reads within the transaction are consistent, 3. Serialization (Serializable) the highest level, prevents other transactions from modifying data through locks, ensuring data integrity but sacrificing performance;

To add MySQL's bin directory to the system PATH, it needs to be configured according to the different operating systems. 1. Windows system: Find the bin folder in the MySQL installation directory (the default path is usually C:\ProgramFiles\MySQL\MySQLServerX.X\bin), right-click "This Computer" → "Properties" → "Advanced System Settings" → "Environment Variables", select Path in "System Variables" and edit it, add the MySQLbin path, save it and restart the command prompt and enter mysql--version verification; 2.macOS and Linux systems: Bash users edit ~/.bashrc or ~/.bash_

MySQLWorkbench stores connection information in the system configuration file. The specific path varies according to the operating system: 1. It is located in %APPDATA%\MySQL\Workbench\connections.xml in Windows system; 2. It is located in ~/Library/ApplicationSupport/MySQL/Workbench/connections.xml in macOS system; 3. It is usually located in ~/.mysql/workbench/connections.xml in Linux system or ~/.local/share/data/MySQL/Wor

Turn on MySQL slow query logs and analyze locationable performance issues. 1. Edit the configuration file or dynamically set slow_query_log and long_query_time; 2. The log contains key fields such as Query_time, Lock_time, Rows_examined to assist in judging efficiency bottlenecks; 3. Use mysqldumpslow or pt-query-digest tools to efficiently analyze logs; 4. Optimization suggestions include adding indexes, avoiding SELECT*, splitting complex queries, etc. For example, adding an index to user_id can significantly reduce the number of scanned rows and improve query efficiency.

mysqldump is a common tool for performing logical backups of MySQL databases. It generates SQL files containing CREATE and INSERT statements to rebuild the database. 1. It does not back up the original file, but converts the database structure and content into portable SQL commands; 2. It is suitable for small databases or selective recovery, and is not suitable for fast recovery of TB-level data; 3. Common options include --single-transaction, --databases, --all-databases, --routines, etc.; 4. Use mysql command to import during recovery, and can turn off foreign key checks to improve speed; 5. It is recommended to test backup regularly, use compression, and automatic adjustment.

When handling NULL values ??in MySQL, please note: 1. When designing the table, the key fields are set to NOTNULL, and optional fields are allowed NULL; 2. ISNULL or ISNOTNULL must be used with = or !=; 3. IFNULL or COALESCE functions can be used to replace the display default values; 4. Be cautious when using NULL values ??directly when inserting or updating, and pay attention to the data source and ORM framework processing methods. NULL represents an unknown value and does not equal any value, including itself. Therefore, be careful when querying, counting, and connecting tables to avoid missing data or logical errors. Rational use of functions and constraints can effectively reduce interference caused by NULL.

To reset the root password of MySQL, please follow the following steps: 1. Stop the MySQL server, use sudosystemctlstopmysql or sudosystemctlstopmysqld; 2. Start MySQL in --skip-grant-tables mode, execute sudomysqld-skip-grant-tables&; 3. Log in to MySQL and execute the corresponding SQL command to modify the password according to the version, such as FLUSHPRIVILEGES;ALTERUSER'root'@'localhost'IDENTIFIEDBY'your_new
