Pregunta ¿Diferencias entre INDEX, PRIMARY, UNIQUE, FULLTEXT en MySQL?


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

¿Cómo los usaría?


527
2018-04-02 00:32


origen


Respuestas:


Diferencias

  • LLAVE o ÍNDICE se refiere a un índice normal no único. Se permiten valores no distintivos para el índice, por lo que el índice mayo contienen filas con valores idénticos en todas las columnas del índice. Estos índices no imponen ninguna restricción sobre sus datos, por lo que solo se usan para garantizar que ciertas consultas se puedan ejecutar 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 utilizarse para acelerar las consultas, los índices UNIQUE se pueden usar para imponer restricciones a los datos, ya que el sistema de base de datos no permite que esta regla de valores distintos se rompa 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 NULOS, en cuyo caso dos filas pueden ser idénticas si ambas contienen un valor NULO (el razonamiento es que NULL no se considera a sí mismo). Dependiendo de su aplicación, sin embargo, usted mayo encuentre esto indeseable: si desea evitar esto, debe rechazar valores NULOS en las columnas relevantes.

  • PRIMARIO actúa exactamente como un índice ÚNICO, excepto que siempre se llama 'PRIMARIO', y puede haber solo uno en una tabla (y allí debería siempre se uno; aunque algunos sistemas de bases de datos no hacen cumplir esto). Un índice PRIMARIO tiene la intención de ser un medio primario para identificar de manera única cualquier fila en la tabla, por lo que a diferencia de ÚNICO, no debe usarse en ninguna columna que permita valores NULOS. Su índice PRIMARIO debe estar en el menor número de columnas que sean suficientes para identificar de manera única una fila. A menudo, esta es solo una columna que contiene un número único autoincrementado, pero si hay algo más que pueda identificar de manera única una fila, como "countrycode" en una lista de países, puede usarla en su lugar.

    Algunos sistemas de bases de datos (como el InnoDB de MySQL) almacenarán los registros de una tabla en el disco en el orden en que aparecen en el índice PRIMARIO.

  • TEXTO COMPLETO los índices 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 con la cláusula MATCH () / AGAINST (), a diferencia de los tres anteriores, que normalmente se implementan internamente utilizando b-trees (que permiten seleccionar, ordenar o rangos desde la columna de la izquierda) o tablas hash (que permiten la selección comenzando desde la columna más a la izquierda).

    Donde los otros tipos de índice son de propósito general, un índice FULLTEXT está especializado, ya que tiene 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 comenzando desde la izquierda; no puede usar solo la segunda, tercera o cuarta parte de un index, a menos que también use 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 columnas del índice).


602
2018-04-02 06:22



Todos estos son tipos de índices.

primario: debe ser único, es un índice, es (probable) 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 en 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.


132
2018-04-02 00:45



Siento que esto ha sido bien cubierto, tal vez con excepción de lo siguiente:

  • Sencillo KEY / INDEX (o llamado de otra manera SECONDARY INDEX) aumentan el rendimiento si la selectividad es suficiente. En este aspecto, la recomendación habitual es que si la cantidad de registros en el conjunto de resultados en el que se aplica un índice excede el 20% del total de registros de la tabla principal, entonces el índice no será efectivo. En la práctica, cada arquitectura será diferente, pero la idea sigue siendo correcta.

  • Los índices secundarios (y eso es muy específico de mysql) no deben verse como objetos completamente separados y diferentes de la clave principal. De hecho, ambos deben usarse conjuntamente y, una vez que se conoce esta información, proporcionan una herramienta adicional al DBA mysql: en Mysql, los índices incrustan la clave primaria. Conduce a importantes mejoras de rendimiento, específicamente cuando se construyen inteligentemente índices de cobertura implícitos como descrito allí

  • Si siente que sus datos deberían ser UNIQUE, usa un índice único. Puede pensar que es opcional (por ejemplo, resolverlo a nivel de aplicación) y que un índice normal servirá, pero en realidad representa una garantía para Mysql de que cada fila es única, lo que de paso proporciona un beneficio de rendimiento.

  • Solo puedes usar FULLTEXT (o llamado de otra manera SEARCH INDEX) con Innodb (en MySQL 5.6.4 y versiones posteriores) y Myisam Engines

  • Solo puedes usar FULLTEXT en CHAR, VARCHAR y TEXT tipos de columnas
  • FULLTEXT índice implica MUCHO más que simplemente crear un índice. Hay un conjunto de tablas del sistema creadas, un sistema de caché completamente separado y algunas reglas y optimizaciones específicas aplicadas. Ver 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

11
2018-01-19 01:54



PRIMARIO

Un índice único donde todas las columnas clave deben definirse como NOT NULL. Si no se declaran explícitamente como NOT NULL, MySQL los declara de manera implícita (y silenciosa). Una tabla puede tener solo una PRIMARY KEY. El nombre de una LLAVE PRINCIPAL siempre es PRIMARIO, por lo que no se puede usar como nombre para ningún otro tipo de índice.

Si no tiene una LLAVE PRIMARIA y una aplicación le pide la LLAVE PRINCIPAL en sus tablas, MySQL devuelve el primer índice UNICO que no tiene columnas NULAS como la LLAVE PRIMARIA.

En las tablas de InnoDB, mantenga la TECLA PRIMARIA corta para minimizar la sobrecarga de almacenamiento para los índices secundarios. Cada entrada de índice secundaria contiene una copia de las columnas de clave principal para la fila correspondiente.

En la tabla creada, una PRIMARY KEY se coloca primero, seguida por todos los índices UNIQUE, y luego los índices no únicos. Esto ayuda al optimizador de MySQL a priorizar qué índice usar y también más rápidamente para detectar claves UNIQUE duplicadas.

UNA LLAVE PRINCIPAL puede ser un índice de columnas múltiples. Sin embargo, no puede crear un índice de múltiples columnas utilizando el atributo de clave PRIMARY KEY en una especificación de columna. Hacerlo solo marca esa columna individual como primaria. Debe usar una cláusula PRIMARY KEY (index_col_name, ...) separada.

Si una CLAVE PRIMARIA consta de una sola columna que tiene un tipo entero, también puede referirse a la columna como _rowid en las instrucciones SELECT.

ÚNICO

Un índice UNIQUE crea una restricción tal que todos los valores en el índice deben ser distintos. Se produce un error si intenta agregar una nueva fila con un valor clave que coincida con una fila existente. Para todos los motores, un índice UNIQUE permite valores NULL múltiples para columnas que pueden contener NULL. Si especifica un valor de prefijo para una columna en un índice ÚNICO, los valores de columna deben ser únicos dentro del prefijo. Si un índice ÚNICO consta de una sola columna que tiene un tipo entero, también puede referirse a la columna como _rowid en las sentencias SELECT.

CLAVE | ÍNDICE

KEY es normalmente un sinónimo de INDEX. se refiere a un índice normal no único.

TEXTO COMPLETO

Un índice FULLTEXT es un tipo especial de índice utilizado para búsquedas de texto completo. Solo los motores de almacenamiento InnoDB y My support admiten índices FULLTEXT. Solo se pueden crear a partir de columnas CHAR, VARCHAR y TEXT. La indexación siempre ocurre en toda la columna; la indexación de prefijo de columna no es compatible y cualquier longitud de prefijo se ignora si se especifica. La cláusula A WITH PARSER se puede especificar como un valor de index_option para asociar un plugin de analizador con el índice si las operaciones de indexación y búsqueda de texto completo requieren un tratamiento especial. Esta cláusula es válida solo para los índices FULLTEXT. InnoDB y MyISAM son compatibles con los complementos del analizador de texto completo.


0
2018-06-24 02:14