Disavanzo delle prestazioni della chiave primaria composita in MySQL

Abbiamo una tabella con una chiave primaria composta composta da tre campi (ed è in MySQL 5.1). Ci sono circa 200 inserti e 200 selezioni al secondo su questa tabella, e la dimensione della tabella è di circa 1 milione di righe e sta aumentando.

La mia domanda è: la “chiave primaria composta” diminuisce le prestazioni degli inserti e dei selettivi su questa tabella?

Dovrei utilizzare un semplice campo ID INT a incremento automatico invece di una chiave primaria composta? (Penso che la risposta sia molto legata al modo in cui MySQL gestisce gli indici su più colonne)

INSERT prestazioni INSERT e UPDATE variano poco: sarà quasi la stessa per i tasti (INT) e (INT, INT) .

SELECT performance SELECT del PRIMARY KEY composito dipende da molti fattori.

Se la tua tabella è InnoDB , la tabella viene implicitamente raggruppata sul valore PRIMARY KEY .

Ciò significa che le ricerche per entrambi i valori saranno più veloci se entrambi i valori comprendono la chiave: non sarà richiesta alcuna ricerca aggiuntiva di chiavi.

Supponendo che la tua query sia qualcosa del genere:

 SELECT * FROM mytable WHERE col1 = @value1 AND col2 = @value2 

e il layout della tabella è questo:

 CREATE TABLE mytable ( col1 INT NOT NULL, col2 INT NOT NULL, data VARCHAR(200) NOT NULL, PRIMARY KEY pk_mytable (col1, col2) ) ENGINE=InnoDB 

, il motore dovrà solo cercare il valore esatto della chiave nella tabella stessa.

Se utilizzi un campo autoincrement come id falso:

 CREATE TABLE mytable ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, col1 INT NOT NULL, col2 INT NOT NULL, data VARCHAR(200) NOT NULL, UNIQUE KEY ix_mytable_col1_col2 (col1, col2) ) ENGINE=InnoDB 

, quindi il motore avrà bisogno, in primo luogo, di (col1, col2) i valori di (col1, col2) nell’indice ix_mytable_col1_col2 , recuperare il puntatore di riga dall’indice (il valore di id ) e fare un’altra ricerca per id nella tabella stessa.

Per le tabelle MyISAM , tuttavia, questo non fa alcuna differenza, perché le tabelle MyISAM sono organizzate in heap e il puntatore di riga è solo offset di file.

In entrambi i casi, verrà creato uno stesso indice (per PRIMARY KEY o UNIQUE KEY ) e verrà utilizzato allo stesso modo.

Se è InnoDB, la chiave primaria composta sarà inclusa in ogni voce in ciascuno degli indici secondari.

Ciò significa che

  • I tuoi indici secondari occuperanno tanto spazio quanto quelle colonne + tutte le colonne nella chiave primaria
  • È ansible utilizzare un indice secondario come indice di copertura se tutte le colonne richieste sono contenute nell’indice secondario + pk

Questi sono, ovviamente, uno svantaggio e un vantaggio rispettivamente.

Le chiavi primarie composite non sono necessariamente cattive, a volte possono essere davvero utili perché InnoDB le raggruppa, il che significa che le scansioni di intervallo (con collegamento a disco) su PK possono essere soddisfatte utilizzando un numero molto inferiore di operazioni IO rispetto a un indice non cluster .

Naturalmente se hai chiavi esterne in altri tavoli, sono più ampie e devono includere l’intera chiave dalla tabella principale.

Ma direi a conti fatti, in generale, no. Avere una chiave primaria composta NON causa un problema da solo. Avere una “grande” chiave primaria (ad es. Grandi varchar) può tuttavia, se questo supera i vantaggi del clustering e la possibilità di usare indici di copertura.

  1. Avere quella chiave primaria composita rallenta SELECT un pochino, anche se l’effetto è praticamente trascurabile e non vale la pena preoccuparsi.
  2. Il fatto che le colonne siano indicizzate rallenta il tuo INSERT e sicuramente stai facendo abbastanza INSERT per preoccuparti di ciò. Questo è molto più preoccupante se si tratta di una tabella MyISAM, in cui un INSERT blocca la tabella, piuttosto che se si tratta di una tabella InnoDB. Se, andando con la chiave primaria auto_increment, potresti lasciare quelle colonne non indicizzate, trarrai beneficio dal cambiamento. Se avresti ancora bisogno di mantenere le tre colonne indicizzate, anche se (per esempio, se devi forzare l’unicità sulla combinazione di esse), non farà nulla per te in termini di prestazioni.