Tipos de índices de Mysql y sus ventajas y desventajas

ÍndiceEs un tipo de archivo especial (los índices de tablas de datos InnoDB son una parte del espacio de datos), que contiene punteros de referencia a todos los registros de la tabla de datos.
Nota:
[1]¡Los índices no son infalibles! Los índices pueden acelerar las operaciones de búsqueda de datos, pero pueden ralentizar las operaciones de modificación de datos. Cada vez que se modifica un registro de datos, el índice debe actualizarse. Para compensar en cierta medida esta deficiencia, muchos comandos SQL tienen una opción DELAY_KEY_WRITE. Esta opción tiene el efecto de detener temporalmente a MySQL de actualizar el índice después de insertar cada nuevo registro y modificar cada registro existente, actualizando el índice hasta que se inserten todos los registros/Después de modificar, realice. En el caso de necesitar insertar muchos nuevos registros en una tabla de datos, la opción DELAY_KEY_WRITE tiene un efecto muy notable.
[2Además, los índices ocuparán un espacio considerable en el disco. Por lo tanto, solo se deben crear índices para las columnas que se consultan y ordenan con mayor frecuencia. Nota: si una columna contiene muchos datos repetidos, crear un índice no tendrá un efecto práctico significativo.
En teoría, se podría crear un índice para cada campo en la tabla de datos, pero MySQL limita el número total de índices en la misma tabla a16.

1. Índices de tablas de datos InnoDB

En comparación con las tablas de datos MyISAM, el índice es mucho más importante para los datos InnoDB. En las tablas de datos InnoDB, el índice es mucho más importante. En las tablas de datos InnoDB, el índice no solo actúa en la búsqueda de registros de datos, sino que también es la base del mecanismo de bloqueo de nivel de fila. 'Bloqueo de nivel de fila' significa bloquear los registros individuales que se están procesando durante la ejecución de operaciones de transacción, permitiendo que otros usuarios no accedan a ellos. Este bloqueo afecta (pero no se limita a) las órdenes SELECT…LOCK IN SHARE MODE, SELECT…FOR UPDATE, INSERT, UPDATE y DELETE.
Por consideraciones de eficiencia, el bloqueo de nivel de fila de las tablas de datos InnoDB ocurre realmente en sus índices, no en la tabla de datos en sí. Obviamente, el mecanismo de bloqueo de nivel de fila de datos solo puede funcionar cuando la tabla de datos relevante tiene un índice adecuado para bloquear.

2. Restricciones

Si la cláusula WHERE contiene un signo de desigualdad (WHERE columna != ...), MySQL no podrá utilizar el índice.
类似地，如果WHERE子句的查询条件里使用了函数(WHERE DAY(column) = …)，MySQL也将无法使用索引。
De manera similar, si la cláusula WHERE utiliza funciones (WHERE DAY(columna) = …), MySQL también no podrá usar el índice.
Si la cláusula WHERE utiliza operadores de comparación LIKE y REGEXP, MySQL solo puede usar el índice cuando el primer carácter del patrón de búsqueda no es un carácter de sustitución. Por ejemplo, si la condición de consulta es LIKE 'abc%', MySQL utilizará el índice; si la condición de consulta es LIKE '%abc', MySQL no utilizará el índice.
En la operación ORDER BY, MySQL solo utiliza el índice cuando la condición de orden no es una expresión de condición de consulta. (A pesar de esto, en las consultas que involucran múltiples tablas de datos, incluso si hay índices disponibles, esos índices no tienen mucho efecto en acelerar ORDER BY)
Si una columna de datos contiene muchos valores repetidos, incluso si se crea un índice para ella, no tendrá un buen efecto. Por ejemplo, si una columna de datos contiene solo valores como '0'/1″o″Y/No es necesario crear un índice para 'N' valores iguales.　

Índice común, índice único y índice principal

1. Índice común

. Índice común (definido por la palabra clave KEY o INDEX) tiene como único objetivo acelerar el acceso a los datos. Por lo tanto, debe crear índices solo para las columnas de datos que aparecen con mayor frecuencia en las condiciones de consulta (WHERE columna = …) o en las condiciones de orden (ORDER BY columna). Siempre que sea posible, debe elegir una columna de datos más ordenada y compacta (como una columna de tipo entero) para crear índices.

2. Índice único
El índice común permite que la columna de datos indexada contenga valores repetidos. Por ejemplo, debido a que las personas pueden tener el mismo nombre, el mismo nombre puede aparecer dos veces o más en la tabla de datos de información personal del empleado.
Si se puede determinar que una columna de datos solo contendrá valores diferentes entre sí, al crear un índice para esta columna de datos, debe definirse como un índice único utilizando la palabra clave UNIQUE. Los beneficios de hacer esto: primero, simplifica el trabajo de gestión de MySQL para este índice, por lo que se vuelve más eficiente; segundo, MySQL verificará automáticamente si el valor de este campo en el nuevo registro ya ha aparecido en algún campo de algún registro cuando se inserte un nuevo registro en la tabla de datos; si es así, MySQL rechazará la inserción de ese nuevo registro. Es decir, el índice único puede garantizar la unicidad de los registros de datos. De hecho, en muchos casos, el propósito de crear un índice único no es para mejorar la velocidad de acceso, sino solo para evitar que los datos se repitan.

3. Índice principal

Ya se ha resaltado repetidamente anteriormente: se debe crear un índice para el campo de clave principal, que es lo que se llama la "índice principal". La única diferencia entre el índice principal y el índice único es: el uso de la palabra clave PRIMARY en lugar de UNIQUE al definir el primero.

4. Índice de clave foránea

Si se define una condición de restricción de clave foránea para un campo de clave foránea, MySQL definirá un índice interno para ayudar a gestionar y usar la condición de restricción de clave foránea de manera más eficiente.

5. Índice compuesto

Los índices pueden cubrir múltiples columnas de datos, como el índice INDEX(columnA, columnB). Una característica de este tipo de índice es que MySQL puede optar por usar uno de estos índices. Si la operación de consulta solo necesita usar un índice en la columna de datos columnA, se puede usar el índice compuesto INDEX(columnA, columnB). Sin embargo, este uso solo se aplica a combinaciones de columnas que se colocan primero en el índice compuesto. Por ejemplo, INDEX(A, B, C) se puede usar como índice de A o (A, B), pero no como índice de B, C o (B, C).

6. Longitud del índice

Al definir índices para columnas de tipo CHAR y VARCHAR, se puede limitar la longitud del índice a un número de caracteres específico (este número debe ser menor que el número máximo de caracteres permitido por este campo). La ventaja de esto es que se puede generar un archivo de índice más pequeño y con una velocidad de búsqueda más rápida. En la mayoría de las aplicaciones, los datos de cadenas en la base de datos son principalmente nombres de diversa naturaleza, por lo que la longitud del índice se puede configurar como1015caracteres ya es suficiente para reducir el rango de búsqueda a muy pocos registros de datos.~
Al crear índices para columnas de tipo BLOB y TEXT, es necesario limitar la longitud del índice; la longitud máxima permitida por MySQL para un índice es255caracteres.
Índice completo

　　 Los índices comunes en los campos de texto solo pueden acelerar la búsqueda de cadenas de caracteres que aparecen en la parte superior del contenido del campo (es decir, los caracteres al principio del contenido del campo). Si el campo contiene un gran texto compuesto por varias, incluso varias palabras, los índices comunes no sirven de mucho. Este tipo de búsqueda a menudo se presenta en la forma LIKE %word%， lo que es muy complejo para MySQL, y si la cantidad de datos a procesar es grande, el tiempo de respuesta será muy largo.
Este tipo de situación es donde el índice de texto completo (full-text index) puede destacar en lugares. Al generar este tipo de índice, MySQL creará una lista de todas las palabras que aparecen en el texto, y la operación de consulta se realizará según esta lista para buscar los registros de datos relevantes. El índice de texto completo se puede crear junto con la tabla de datos o utilizarse más tarde cuando sea necesario.

A continuación se muestra el comando para agregar:
ALTER TABLE tablename ADD FULLTEXT(column1, column2)

Con el índice de texto completo, se puede usar el comando de consulta SELECT para buscar registros de datos que contienen una o más palabras dadas. A continuación se muestra la sintaxis básica de este tipo de comando de consulta:
SELECT * FROM tablename
WHERE MATCH(column1, column2) AGAINST(‘word1′, ‘word2′, ‘word3′)

Este comando copiará column1y column2el campo contiene word1、word2y word3se consultan todos los registros de datos.

Nota：Las tablas de datos InnoDB no admiten índices de texto completo.

Optimización de consultas y índices

　　　　　 Sólo cuando haya suficiente datos de prueba en la base de datos, los resultados de la prueba de rendimiento tendrán valor de referencia real. Si solo hay unos cientos de registros de datos en la base de datos de prueba, a menudo se cargan todos en la memoria después de ejecutar el primer comando de consulta, lo que hace que todas las consultas posteriores se ejecuten muy rápidamente - sin importar si se utiliza un índice o no. Sólo cuando los registros en la base de datos superen10solo tiene sentido cuando la cantidad total de datos también supera la cantidad total de memoria del servidor MySQL.

　　　　　 Cuando no se está seguro de en qué columnas de datos se debe crear un índice, las personas a menudo pueden obtener alguna ayuda del comando EXPLAIN SELECT. Esto es simplemente agregar una palabra clave EXPLAIN como prefijo a un comando SELECT común. Con esta palabra clave, MySQL no ejecutará el comando SELECT, sino que lo analizará. MySQL presentará el proceso de ejecución de la consulta y la información del índice (si hay uno) en forma de tabla.
En los resultados de salida del comando EXPLAIN, el1La columna es el nombre de la tabla de datos leída desde la base de datos, y se ordenan en el orden de lectura. La columna type especifica la relación de asociación (JOIN) entre esta tabla de datos y otras tablas de datos. En varios tipos de relaciones de asociación, la más eficiente es system, seguida de const, eq_ref, ref, range, index y All (All significa: para cada registro en la tabla de datos superior, todos los registros en esta tabla deben leerse una vez - este tipo de situación a menudo se puede evitar con un índice).

La columna possible_keys proporciona todas las índices disponibles que MySQL puede usar para buscar registros. La columna key es el índice que MySQL realmente usa, y su longitud en bytes se proporciona en la columna key_len. Por ejemplo, para un índice de columna INTEGER, esta longitud en bytes será4Si se utiliza un índice compuesto, también se puede ver en la columna key_len qué partes específicas de MySQL están utilizando. Como regla general, un valor más pequeño en la columna key_len es mejor (lo que significa más rápido).
La columna ref proporciona el nombre de la columna en otra tabla de la relación de asociación. La columna row indica el número de filas que MySQL espera leer de esta tabla al ejecutar esta consulta. El producto de todos los números en la columna row nos da una idea general de cuántas combinaciones debe manejar esta consulta.
Finalmente, la columna extra proporciona más información relacionada con la operación JOIN. Por ejemplo, si MySQL debe crear una tabla de datos temporal para ejecutar esta consulta, verá el texto 'using temporary' en la columna extra.

Esto es todo el contenido del artículo. Espero que sea útil para su aprendizaje y que todos nos apoyen en el tutorial de alarido.

Aviso: El contenido de este artículo se ha obtenido de la red, y los derechos de autor pertenecen al propietario original. El contenido ha sido contribuido y subido por los usuarios de Internet de manera autónoma. Este sitio web no posee los derechos de propiedad, no ha sido editado por humanos y no asume ninguna responsabilidad legal. Si encuentra contenido sospechoso de infracción de derechos de autor, por favor envíe un correo electrónico a: notice#w proporcionando evidencia relevante.3Declaración: El contenido de este artículo se ha obtenido de la red, y los derechos de autor pertenecen al propietario original. El contenido ha sido contribuido y subido por los usuarios de Internet de manera autónoma. Este sitio web no posee los derechos de propiedad, no ha sido editado por humanos y no asume ninguna responsabilidad legal. Si encuentra contenido sospechoso de infracción de derechos de autor, por favor envíe un correo electrónico a: notice#w proporcionando evidencia relevante. Una vez verificada, este sitio eliminará inmediatamente el contenido sospechoso de infracción.