Accueil > Blog > Nos Meetups > Update your MySQL Schema & a Million Rows Headache-free

Vous n’avez pas pu assister à notre dernier Mobitalks PHP ? Voici un récap’ (PART II) !

Pour lire le récap’ de 1ère partie du meetup, c’est par ici !

« Update your MySQL Schema & a Million Rows Headache-free », par Sylvain Fabre (CTO de AssoConnect)

Issues

• Queries crash because the production code relies on a column that does not exist in your database
• INSERT statements crash because the production code is not aware of a new column in your database
• A long running query locks down your database
• There is a downtime when the database schema is not compatible with the code

Why is MySQL slow to perform the query?

Several possible causes

• A lot of data to move/go through
• An old version of MySQL
• Uncommon edge cases

Prevent Locking

ALGORITHM=INPLACE & LOCK=NONE

• ALGORITHM=INPLACE prevents the engine from reorganizing the data
• LOCK=NONE prevents any locking mechanism

Use these clauses to block the execution of the query

• You won’t lock down your table
• The error message will tell you what to do to run the query

Schema Update

Add a new column

1. Add the new column with a default value set
2. Release a new version of your code to start writing into the column
3. Run any migration script
4. Release a new version of your code to read from & write into the column

Drop a column

1. Set a default value to the column
2. Release a new version of the code that won’t use this column
3. Drop the column

Rename a column

We want to rename the column from old_name to new_name

• Add a column new_name (don’t forget the default value)
• Release a new version of your code

*setOldName() will call setNewName()
*getOldName() is still used
*getNewName() is not used

• Copy values from old_name to new_name

• Set a default value to the old_name column
• Release a new version of your code

*Replace setOldName() with setNewName()
*Replace getOldName() with getNewName()
*Update raw SQL queries

• Drop the old column

Add a Foreign Key Constraint

• Slow because MySQL blocks writes while it checks each row for invalid values
• Solution

*Disable this check with SET FOREIGN_KEY_CHECKS=0;
*Create the foreign key constraint
*Check consistency with a SELECT query with a JOIN clause

Update a million Rows

MySQL procedure

• Batch the rows update only a few of them at the same time
• Ensure your row filtering is efficient
• Run it with the CLI client

Retrouvez nos offres en PHP !

Date de publication : 26 mars 2020