función de índice MySQL
Los índices son archivos especiales (los índices en las tablas de datos de InnoDB son un componente del espacio de tablas) que contienen punteros de referencia a todos los registros de la tabla de datos. Los índices no son una panacea. Los índices pueden acelerar las operaciones de recuperación de datos, pero pueden ralentizar las operaciones de modificación de datos. Cada vez que se modifica un registro de datos, se debe actualizar el índice. Para compensar hasta cierto punto esta deficiencia, muchos comandos SQL tienen una entrada DELAY_KEY_WRITE. La función de esta opción es evitar temporalmente que MySQL actualice el índice inmediatamente después de que se inserte cada nuevo registro y cada uno existente sea modificado por este comando. La actualización del índice esperará hasta que todos los registros hayan sido insertados/modificados. La opción DELAY_KEY_WRITE es muy útil cuando es necesario insertar muchos registros nuevos en una tabla de datos. Además, los índices ocupan un espacio considerable en el disco duro. Por lo tanto, sólo se deben indexar las columnas de datos más consultadas y clasificadas con más frecuencia. Tenga en cuenta que si una columna de datos contiene mucho contenido duplicado, indexarla no tendrá mucho efecto práctico.
Teóricamente, es posible construir un índice para cada campo en la tabla de datos, pero MySQL limita el número total de índices en la misma tabla de datos a 16.
1. Índices en tablas de datos de InnoDB
En comparación con las tablas de datos de InnoDB, los índices en tablas de datos de InnoDB son mucho más importantes. En las tablas de datos de InnoDB, los índices no solo desempeñan un papel en la búsqueda de registros de datos, sino que también son la base para el mecanismo de bloqueo a nivel de fila de datos. "Bloqueo a nivel de fila de datos" significa bloquear registros individuales que se procesan durante la ejecución de operaciones de transacción, evitando que otros usuarios accedan a ellos. Este bloqueo afecta (pero no se limita a) los comandos SELECT, LOCKINSHAREMODE, SELECT, FORUPDATE y los comandos INSERT, UPDATE y DELETE. Por razones de eficiencia, el bloqueo a nivel de fila de las tablas InnoDB ocurre en realidad en sus índices, no en la tabla misma. Obviamente, el mecanismo de bloqueo a nivel de fila de datos solo puede ser efectivo cuando la tabla de datos relevante tiene un índice adecuado para bloquear.
2. Restricciones
Si hay un signo de desigualdad (WHERE color!=) en la condición de consulta de la cláusula WHERE, MySQL no podrá usar el índice. De manera similar, si se usa la función (WHERE DAY (columna) =) en la condición de consulta de la cláusula WHERE, MySQL no podrá usar el índice. En una operación JOIN (cuando es necesario extraer datos de varias tablas de datos), MySQL solo puede usar índices cuando el tipo de datos de la clave principal y la clave externa son el mismo.
Si los operadores de comparación LIKE y REGEXP se usan en la condición de consulta de la cláusula WHERE, MySQL puede usar el índice solo si el primer carácter de la plantilla de búsqueda no es un carácter comodín. Por ejemplo, si la condición de consulta es COMO 'abc%', MySQL usará el índice; si la condición de consulta es COMO '%abc', MySQL no usará el índice.
En una operación ORDER BY, MySQL usa un índice solo si la condición de clasificación no es una expresión de condición de consulta. (Sin embargo, en consultas que involucran varias tablas de datos, incluso si hay índices disponibles, esos índices tendrán poco efecto en la aceleración de ORDER BY). Si una columna de datos contiene muchos valores duplicados, incluso si está indexada, no tendrá buenos resultados. Por ejemplo, si una columna de datos contiene solo valores como "0/1" o "Y/N", no es necesario crear un índice para ella. 1． Índice ordinario
La única tarea de un índice ordinario (un índice definido por la palabra clave KEY o INDEX) es acelerar el acceso a los datos. Por lo tanto, solo se deben crear índices para aquellas columnas de datos que aparecen con mayor frecuencia en las condiciones de consulta (DONDE columna =) o condiciones de clasificación (columna ORDER BY).
Siempre que sea posible, debe elegir una columna de datos con los datos más ordenados y compactos (como una columna de datos de tipo entero) para crear un índice.
2. Índice único
El índice normal permite que la columna de datos indexados contenga valores duplicados. Por ejemplo, debido a que las personas pueden tener el mismo nombre, el mismo nombre puede aparecer dos o más veces en la misma tabla de datos del "Perfil del empleado".
Si puede estar seguro de que una determinada columna de datos solo contendrá valores diferentes entre sí, debe utilizar la palabra clave UNIQUE para definirla como un índice único al crear un índice para estos datos. columna. Los beneficios de esto son: primero, simplifica la administración de este índice por parte de MySQL y el índice se vuelve más eficiente; segundo, MySQL verificará automáticamente el valor de este campo en el nuevo registro cuando se inserte un nuevo registro en la tabla de datos. este campo ya apareció en un registro; de ser así, MySQL se negará a insertar el nuevo registro. En otras palabras, un índice único puede garantizar la unicidad de los registros de datos. De hecho, en muchas ocasiones, el propósito de crear índices únicos no es aumentar la velocidad de acceso, sino simplemente evitar la duplicación de datos.
3. Índice primario
Se ha enfatizado muchas veces antes: se debe crear un índice para el campo de clave principal. Este índice es el llamado "índice primario". La única diferencia entre un índice primario y un índice único es que la palabra clave utilizada al definir el primero es PRIMARIA en lugar de ÚNICA.
4． Índice de clave externa
Si se define una restricción de clave externa para un campo de clave externa, MySQL definirá un índice interno para ayudarlo a administrar y utilizar la restricción de clave externa de la manera más eficiente.
5. Índice compuesto
Un índice puede cubrir varias columnas de datos, como un índice ÍNDICE (columnaA, columnaB). La característica de este tipo de índice es que MySQL puede utilizar dicho índice de forma selectiva. Si la operación de consulta solo requiere un índice en la columna de datos de la columna A, puede utilizar el índice compuesto ÍNDICE (columna A, columna B). Sin embargo, este uso solo se aplica a la combinación de columnas de datos que ocupan el primer lugar en el índice compuesto. Por ejemplo, INDEX (A, B, C) se puede utilizar como índice en A o (A, B), pero no como índice en B, C o (B, C). Al definir índices para columnas de datos de tipo CHAR y VARCHAR, puede limitar la longitud del índice a una cantidad determinada de caracteres (este número debe ser menor que la cantidad máxima de caracteres permitidos para este campo). La ventaja de esto es que puede generar un archivo de índice de menor tamaño y más rápido en velocidad de recuperación. En la mayoría de las aplicaciones, los datos de cadena en la base de datos se basan principalmente en varios nombres. Configurar la longitud del índice entre 10 y 15 caracteres es suficiente para limitar el alcance de la búsqueda a unos pocos registros de datos. Al crear índices para columnas de datos de tipo BLOB y TEXT, se deben imponer restricciones en la longitud del índice. El índice máximo permitido por MySQL es un índice normal en un campo de texto de índice de texto completo que solo puede acelerar la cadena que aparece en; el frente del contenido del campo (es decir, los caracteres al comienzo del contenido del campo) para realizar la operación de búsqueda. Si el campo almacena un fragmento de texto más grande compuesto por varias o incluso varias palabras, los índices ordinarios serán de poca utilidad. Esta recuperación a menudo se presenta en forma de , lo cual es complejo para MySQL y puede generar tiempos de respuesta prolongados si la cantidad de datos que se deben procesar es grande.
Este tipo de situación es donde el índice de texto completo (full-textindex) puede mostrar sus talentos. Al generar este tipo de índice, MySQL creará una lista de todas las palabras que aparecen en el texto y las operaciones de consulta utilizarán esta lista para recuperar registros de datos relevantes. El índice de texto completo se puede crear junto con la tabla de datos, o puede agregarlo más tarde cuando sea necesario usando el siguiente comando:
ALTER TABLE nombretabla ADD FULLTEXT (columna1, columna2) Con el índice de texto completo , puede utilizar El comando de consulta SELECT recupera registros de datos que contienen una o más palabras determinadas.
La siguiente es la sintaxis básica de este tipo de comando de consulta:
SELECT * FROM tablename
WHERE MATCH (columna1,columna2) CONTRA('palabra1','palabra2','palabra3 ')
El comando anterior consultará todos los registros de datos que contengan palabra1, palabra2 y palabra3 en los campos columna1 y columna2.
Nota: Las tablas de datos de InnoDB no admiten índices de texto completo. Solo cuando hay suficientes datos de prueba en la base de datos, los resultados de las pruebas de rendimiento pueden tener un valor de referencia real. Si solo hay unos pocos 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 hará que los comandos de consulta posteriores se ejecuten muy rápidamente, independientemente de si se utilizan índices o no. Los resultados de la prueba de rendimiento de la base de datos son significativos solo cuando la cantidad de registros en la base de datos excede 1000 y la cantidad total de datos excede la memoria total en el servidor MySQL.
Cuando las personas no están seguras de qué columnas de datos deben usarse para crear índices, a menudo pueden obtener ayuda del comando EXPLAIN SELECT. En realidad, esto es simplemente anteponer un comando SELECT normal con la palabra clave EXPLAIN. Con esta palabra clave, MySQL no ejecutará el comando SELECT, sino que lo analizará. MySQL enumerará el proceso de ejecución de consultas y los índices utilizados en una tabla.
En la salida del comando EXPLAIN, la columna 1 es el nombre de la tabla de datos leída de la base de datos y están ordenados en el orden en que se leen. La columna de tipo especifica la relación (JOIN) entre esta tabla de datos y otras tablas de datos. Entre varios tipos de relaciones, la más eficiente es system, seguida de const, eq_ref, ref, range, index y All (Todo significa: correspondiente a cada registro en la tabla de datos de nivel superior, estos datos Todos los registros de la tabla deben leerse una vez (esto a menudo se puede evitar con un índice).
La columna de datos posibles_keys proporciona los distintos índices que MySQL puede utilizar al buscar registros de datos. La columna de datos clave es el índice realmente seleccionado por MySQL. La longitud de este índice en bytes se proporciona en la columna de datos key_len. Por ejemplo, para un índice en una columna de datos INTEGER, la longitud del byte sería 4. Si se utiliza un índice compuesto, también puede ver qué partes utiliza MySQL en la columna de datos key_len. Como regla general, cuanto menor sea el valor en la columna de datos key_len, mejor.
La columna de datos de referencia proporciona el nombre de la columna de datos en otra tabla de datos de la relación. La columna de datos de fila es el número de filas de datos que MySQL espera leer de esta tabla de datos al ejecutar esta consulta. El producto de todos los números en la columna de datos de la fila le da una idea de cuántas combinaciones necesita procesar la consulta.
Finalmente, la columna de datos adicionales proporciona más información sobre la operación JOIN. Por ejemplo, si MySQL debe crear una tabla de datos temporal al ejecutar esta consulta, verá las palabras usando temporal en la columna adicional.