×
Update your MySQL Schema & a Million Rows Headache-free

Update your MySQL Schema & a Million Rows Headache-free

Blog

Accueil > Blog > Blog > 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

 

 

1

 

 

  • 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

 

 

2

 

 

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

 

 

 

3

 

 

 

Retrouvez nos offres en PHP !

Date de publication : 26 mars 2020