MySQL
Index et clés

Syntaxe

• - Créer un index simple

  CREATE INDEX nom_index ON nom_table ( nom_colonne1 [, nom_colonne2 , ...])

• - Créer un index unique

  CREATE UNIQUE INDEX nom_index ON nom_table ( nom_colonne1 [, nom_colonne2 , ...]

• - Index de baisse

  DROP INDEX nom_index ON nom_table [algorithm_option | lock_option ] ...

  option_algorithme : ALGORITHM [=] {DEFAULT | INPLACE | COPY}

  lock_option: LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

Remarques

Créer un index

-- Create an index for column 'name' in table 'my_table'
CREATE INDEX idx_name ON my_table(name);

Créer un index unique

Un index unique empêche l'insertion de données dupliquées dans une table. NULL valeurs NULL peuvent être insérées dans les colonnes qui font partie de l'index unique (car, par définition, une valeur NULL est différente de toute autre valeur, y compris une autre valeur NULL )

-- Creates a unique index for column 'name' in table 'my_table'
CREATE UNIQUE INDEX idx_name ON my_table(name);

Index de baisse

-- Drop an index for column 'name' in table 'my_table'
DROP INDEX idx_name ON my_table;

Créer un index composite

Cela créera un index composite des deux clés, mystring et mydatetime et accélérera les requêtes avec les deux colonnes de la clause WHERE .

CREATE INDEX idx_mycol_myothercol ON my_table(mycol, myothercol)

Note: la commande est importante! Si la requête de recherche n'inclut pas les deux colonnes dans la clause WHERE , elle ne peut utiliser que l'index le plus à gauche. Dans ce cas, une requête avec mycol dans le WHERE utilisera l'index, une requête recherchant myothercol sans rechercher également mycol ne le sera pas . Pour plus d'informations, consultez ce billet de blog .

Note: En raison de la façon dont fonctionne BTREE, les colonnes généralement interrogées dans les plages doivent aller dans la valeur la plus à droite. Par exemple, les colonnes DATETIME sont généralement interrogées comme WHERE datecol > '2016-01-01 00:00:00' . Les index BTREE gèrent les plages de manière très efficace, mais uniquement si la colonne interrogée en tant qu'intervalle est la dernière de l'index composite.

Touche AUTO_INCREMENT

CREATE TABLE (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ...
    PRIMARY KEY(id),
    ...  );

Notes principales:

• Commence par 1 et s'incrémente automatiquement de 1 lorsque vous ne parvenez pas à le spécifier sur INSERT ou spécifiez-le comme NULL .
• Les identifiants sont toujours distincts les uns des autres, mais ...
• Ne faites pas d'hypothèses (pas de lacunes, générées consécutivement, non réutilisées, etc.) sur les valeurs de l'ID, sauf qu'elles sont uniques à un instant donné.

Notes subtiles:

• Au redémarrage du serveur, la valeur «next» est «calculée» en tant que MAX(id)+1 .
• Si la dernière opération avant l'arrêt ou la panne était de supprimer l'ID le plus élevé, cet identifiant peut être réutilisé (cela dépend du moteur). Donc, ne faites pas confiance à auto_increments pour être unique en permanence ; ils sont uniques à tout moment.
• Pour les solutions multi-maîtres ou en cluster, voir auto_increment_offset et auto_increment_increment .
• C'est bien d'avoir autre chose que la PRIMARY KEY et simplement faire INDEX(id) . (Ceci est une optimisation dans certaines situations.)
• L'utilisation de AUTO_INCREMENT comme "clé de PARTITION " est rarement bénéfique. faire quelque chose de différent.
• Diverses opérations peuvent "brûler" des valeurs. Cela se produit quand ils pré-allouent des valeurs, alors ne les utilisez pas: INSERT IGNORE (avec la clé dup), REPLACE (qui est DELETE plus INSERT ) et d'autres. ROLLBACK est une autre cause des lacunes dans les identifiants.
• Dans Replication, vous ne pouvez pas faire confiance aux identifiants pour arriver au (x) esclave (s) dans l'ordre croissant. Bien que les identifiants soient attribués dans un ordre consécutif, les instructions InnoDB sont envoyées aux esclaves dans l’ordre COMMIT .