MySQL
Índices y claves

Sintaxis

• - Crear índice simple.

  CREAR index_name ÍNDICE DE LA nombre_tabla (nombre1_columna [, COLUMN_NAME2, ...])

• - Crear índice único

  CREAR index_name índice único en nombre_tabla (nombre1_columna [, COLUMN_NAME2, ...]

• - Índice de caída

  DROP INDEX index_name ON tbl_name [ algorithm_option | lock_option ] ...

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

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

Observaciones

Crear índice

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

Crear un índice único

Un índice único impide la inserción de datos duplicados en una tabla. NULL valores NULL se pueden insertar en las columnas que forman parte del índice único (ya que, por definición, un valor NULL es diferente de cualquier otro valor, incluido otro valor NULL )

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

Índice de caída

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

Crear índice compuesto

Esto creará un índice compuesto de ambas claves, mystring y mydatetime y acelerará las consultas con ambas columnas en la cláusula WHERE .

CREATE INDEX idx_mycol_myothercol ON my_table(mycol, myothercol)

Nota: ¡ El orden es importante! Si la consulta de búsqueda no incluye ambas columnas en la cláusula WHERE , solo puede usar el índice de la izquierda. En este caso, una consulta con mycol en el WHERE utilizará el índice, una consulta que myothercol sin buscar también mycol no . Para más información echa un vistazo a esta entrada de blog .

Nota: Debido a la forma en que funciona BTREE, las columnas que generalmente se consultan en rangos deben ir en el valor más a la derecha. Por ejemplo, las columnas DATETIME suelen consultar como WHERE datecol > '2016-01-01 00:00:00' . Los índices BTREE manejan los rangos de manera muy eficiente, pero solo si la columna que se consulta como rango es la última en el índice compuesto.

Tecla AUTO_INCREMENT

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

Notas principales:

• Comienza con 1 e incrementa en 1 automáticamente cuando no puede especificarlo en INSERT , o lo especifica como NULL .
• Los identificadores siempre son distintos entre sí, pero ...
• No haga suposiciones (sin espacios, generados consecutivamente, no reutilizados, etc.) sobre los valores de la identificación que no sean únicos en un momento dado.

Notas sutiles:

• Al reiniciar el servidor, el valor 'siguiente' se 'calcula' como MAX(id)+1 .
• Si la última operación antes de apagar o colapsar fue eliminar la ID más alta, esa ID se puede reutilizar (esto depende del motor). Por lo tanto, no confíe en que auto_increments sea permanentemente único ; Solo son únicos en cualquier momento.
• Para soluciones multi-master o agrupadas, vea auto_increment_offset y auto_increment_increment .
• Está bien tener otra cosa como la PRIMARY KEY y simplemente hacer INDEX(id) . (Esta es una optimización en algunas situaciones).
• El uso de AUTO_INCREMENT como la "clave de PARTITION " rara vez es beneficioso; hacer algo diferente.
• Varias operaciones pueden "quemar" valores. Esto sucede cuando asignan previamente los valores, luego no los use: INSERT IGNORE (con clave de duplicación), REPLACE (que es DELETE más INSERT ) y otros. ROLLBACK es otra causa de lagunas en los identificadores.
• En la Replicación, no puede confiar en que los identificadores lleguen a los esclavos en orden ascendente. Aunque los identificadores se asignan en orden consecutivo, las declaraciones de InnoDB se envían a los esclavos en el orden COMMIT .