mostrar - reconstruir indices mysql

¿Diferencias entre ÍNDICE, PRIMARIO, ÚNICO, FULLTEXT en MySQL? (3)

¿Cuáles son las diferencias entre PRIMARY, UNIQUE, INDEX y FULLTEXT al crear tablas MySQL?

¿Cómo los usaría?

Diferencias

KEY o INDEX se refiere a un índice no único normal. Se permiten valores no distintos para el índice, por lo que el índice puede contener filas con valores idénticos en todas las columnas del índice. Estos índices no imponen ninguna restricción en sus datos, por lo que se usan solo para asegurarse de que ciertas consultas puedan ejecutarse rápidamente.
ÚNICO se refiere a un índice donde todas las filas del índice deben ser únicas. Es decir, la misma fila puede no tener valores no NULL idénticos para todas las columnas en este índice como otra fila. Además de ser utilizados para acelerar las consultas, los índices ÚNICOS se pueden usar para imponer restricciones en los datos, porque el sistema de base de datos no permite que se rompa esta regla de valores distintos al insertar o actualizar datos.
Su sistema de base de datos puede permitir que se aplique un índice ÚNICO a las columnas que permiten valores NULL, en cuyo caso se permite que dos filas sean idénticas si ambas contienen un valor NULL (la razón aquí es que NULL se considera no igual a sí mismo). Sin embargo, dependiendo de su aplicación, puede encontrar esto indeseable: si desea evitarlo, no debe permitir valores NULL en las columnas relevantes.
PRIMARY actúa exactamente igual que un índice UNIQUE, excepto que siempre se llama ''PRIMARY'', y puede que solo haya uno en una tabla (y siempre debería haber uno; aunque algunos sistemas de bases de datos no lo aplican). Un índice PRIMARIO está pensado como un medio primario para identificar de forma única cualquier fila en la tabla, por lo que a diferencia de UNIQUE, no debe usarse en ninguna columna que permita valores NULL. Su índice PRIMARIO debe estar en el menor número de columnas que sea suficiente para identificar de forma única una fila. A menudo, esto es solo una columna que contiene un número único de incremento automático, pero si hay algo más que pueda identificar de forma única una fila, como "código de país" en una lista de países, puede usar eso en su lugar.
Algunos sistemas de bases de datos (como InnoDB de MySQL) almacenarán los registros de una tabla en el disco en el orden en que aparecen en el índice PRIMARIO.
Los índices FULLTEXT son diferentes de todos los anteriores y su comportamiento difiere significativamente entre los sistemas de bases de datos. Los índices FULLTEXT solo son útiles para búsquedas de texto completo realizadas con la cláusula MATCH () / AGAINST (), a diferencia de las tres anteriores, que normalmente se implementan internamente utilizando b-trees (permitiendo la selección, clasificación o rangos desde la columna de la izquierda) o Tablas hash (permitiendo la selección comenzando desde la columna más a la izquierda).
Cuando los otros tipos de índice son de propósito general, un índice FULLTEXT es especializado, ya que sirve para un propósito limitado: solo se usa para una función de "búsqueda de texto completo".

Similitudes

Todos estos índices pueden tener más de una columna en ellos.
Con la excepción de FULLTEXT, el orden de las columnas es significativo: para que el índice sea útil en una consulta, la consulta debe usar columnas del índice que comienza desde la izquierda, no puede usar solo la segunda, tercera o cuarta parte de un índice, a menos que también utilice las columnas anteriores en el índice para hacer coincidir los valores estáticos. (Para que un índice FULLTEXT sea útil para una consulta, la consulta debe usar todas las columnas del índice).

Siento que esto ha sido bien cubierto, tal vez excepto por lo siguiente:

KEY / INDEX simple (o también llamado SECONDARY INDEX ) aumenta el rendimiento si la selectividad es suficiente. A este respecto, la recomendación habitual es que si la cantidad de registros en el conjunto de resultados en el que se aplica un índice supera el 20% de la cantidad total de registros de la tabla principal, entonces el índice será inefectivo. En la práctica, cada arquitectura será diferente, pero la idea sigue siendo correcta.
Los índices secundarios (y eso es muy específico para mysql) no se deben ver como objetos completamente separados y diferentes de la clave principal. De hecho, ambos deben usarse conjuntamente y, una vez que se conozca esta información, proporcionar una herramienta adicional al mysql DBA: en Mysql, los índices incluyen la clave principal. Conduce a mejoras significativas en el rendimiento, especialmente cuando se crean inteligentemente índices de cobertura implícitos como los descritos allí
Si cree que sus datos deberían ser UNIQUE , use un índice único. Puede pensar que es opcional (por ejemplo, trabajar a nivel de aplicación) y que un índice normal funcionará, pero en realidad representa una garantía para Mysql de que cada fila es única, lo que incidentalmente proporciona un beneficio de rendimiento.
Solo puede usar FULLTEXT (o también llamado SEARCH INDEX ) con Innodb (en MySQL 5.6.4 y versiones posteriores) y Myisam Engines
Solo puede usar FULLTEXT en los tipos de columnas CHAR , VARCHAR y TEXT
FULLTEXT índice FULLTEXT implica MUCHO más que solo crear un índice. Se crearon un montón de tablas del sistema, un sistema de almacenamiento en caché completamente independiente y algunas reglas y optimizaciones específicas aplicadas. Consulte http://dev.mysql.com/doc/refman/5.7/en/fulltext-restrictions.html y http://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-index.html

Todos estos son tipos de índices.

primario: debe ser único, es un índice, es (probablemente) el índice físico, puede ser solo uno por tabla.

Único: como dice. No puede tener más de una fila con una tupla de este valor. Tenga en cuenta que dado que una clave única puede estar sobre más de una columna, esto no significa necesariamente que cada columna individual en el índice sea única, sino que cada combinación de valores en estas columnas sea única.

índice: si no es primario o único, no restringe los valores insertados en la tabla, pero sí permite buscarlos de manera más eficiente.

texto completo: una forma más especializada de indexación que permite la búsqueda de texto completo. Piense en ello como (esencialmente) creando un "índice" para cada "palabra" en la columna especificada.