ejemplo - unique index mysql
mysql demasiados índices? (5)
Dedico algo de tiempo a optimizar nuestra base de datos actual.
Estoy buscando índices específicamente.
Hay algunas preguntas:
- ¿Hay demasiados índices?
- ¿Qué acelerarán los índices?
- ¿Qué ralentizarán los índices?
- ¿Cuándo es una buena idea agregar un índice?
- ¿Cuándo es una mala idea agregar un índice?
- Pro y Con de índices múltiples vs índices de varias columnas?
¿Hay demasiados índices?
Los índices deben estar informados por el problema en cuestión: las tablas, las consultas que ejecutará su aplicación, etc.
¿Qué acelerarán los índices?
SELECTs.
¿Qué ralentizarán los índices?
INSERT será más lento, porque debe actualizar el índice.
¿Cuándo es una buena idea agregar un índice?
Cuando su aplicación necesita otra cláusula WHERE.
¿Cuándo es una mala idea agregar un índice?
Cuando no lo necesita para consultar o aplicar restricciones de exclusividad.
Pros y contras de múltiples índices vs índices de múltiples columnas?
No entiendo la pregunta. Si tiene una restricción de exclusividad que incluye múltiples columnas, por supuesto, modele como tal.
¿Hay demasiados índices?
Sí, como todo, demasiados índices ralentizarán la manipulación de datos.
¿Cuándo es una buena idea agregar un índice?
Una buena idea para agregar un índice es cuando sus consultas son demasiado lentas (es decir, tiene demasiadas uniones en sus consultas). Debe usar esta optimización solo después de construir un modelo sólido, para ajustar el rendimiento.
¿Hay demasiados índices?
Sí. No salga buscando crear índices, créelos según sea necesario.
¿Qué acelerarán los índices?
Cualquier consulta en la tabla / vista de índices.
¿Qué ralentizarán los índices?
Cualquier instrucción INSERT contra la tabla indexada se ralentizará, porque cada nuevo registro deberá indexarse.
¿Cuándo es una buena idea agregar un índice?
Cuando una consulta no se está ejecutando a una velocidad aceptable. Puede estar filtrando en registros que no forman parte del PK agrupado, en cuyo caso debe agregar índices según los filtros que está buscando (si el rendimiento lo considera adecuado).
¿Cuándo es una mala idea agregar un índice?
Cuando lo haces por el mero hecho de hacerlo , es decir, una optimización excesiva.
Pro y Con de índices múltiples vs índices de varias columnas?
Depende de las consultas que intentas mejorar.
¿Qué acelerarán los índices?
Recuperación de datos - declaraciones SELECT.
¿Qué ralentizarán los índices?
Manipulación de datos: instrucciones INSERT, UPDATE, DELETE.
¿Cuándo es una buena idea agregar un índice?
Si siente que desea obtener un mejor rendimiento de recuperación de datos.
¿Cuándo es una mala idea agregar un índice?
En las tablas que verán la manipulación de datos pesados - inserción, actualización ...
Pro y Con de índices múltiples vs índices de varias columnas?
Las consultas deben abordar el orden de las columnas cuando se trata de un índice de cobertura (un índice en más de una columna), de izquierda a derecha en la definición de columna de índice. El orden de las columnas en la declaración no es relevante, solo el de las columnas 1, 2 y 3: una declaración necesita una referencia a la columna 1 antes de poder usar el índice. Si solo hay una referencia a la columna 2 o 3, no se pudo usar el índice de cobertura de 1/2/3.
En MySQL, solo se puede usar un índice por SELECT / instrucción en la consulta (las subconsultas / etc se ven como una declaración separada). Y hay un límite en la cantidad de espacio por mesa que MySQL permite. Además, ejecutar una función en una columna indexada hace que el índice sea inútil, IE:
WHERE DATE(datetime_column) = ...
No estoy de acuerdo con algunas de las respuestas sobre esta pregunta.
¿Hay demasiados índices?
Por supuesto. No cree índices que no sean utilizados por ninguna de sus consultas. No crees índices redundantes. Use herramientas como pt-duplicate-key-checker y pt-index-usage para ayudarlo a descubrir los índices que no necesita.
¿Qué acelerarán los índices?
- Condiciones de búsqueda en la cláusula WHERE.
- Únete a las condiciones
- Algunos casos de ORDER BY.
- Algunos casos de GROUP BY.
- Limitaciones ÚNICAS.
- Restricciones de LLAVE EXTRAÑA.
- Búsqueda de texto completo.
Otras respuestas han informado que INSERT / UPDATE / DELETE son más lentos cuantos más índices tenga. Eso es cierto, pero considere que muchos usos de UPDATE y DELETE también tienen cláusulas WHERE y en MySQL, UPDATE y DELETE también admiten JOINs. Los índices pueden beneficiar estas consultas más que compensar la sobrecarga de actualizar índices.
Además, InnoDB bloquea las filas afectadas por una ACTUALIZACIÓN o ELIMINACIÓN. Llaman a este bloqueo a nivel de fila, pero en realidad es bloqueo a nivel de índice. Si no hay un índice para restringir la búsqueda, InnoDB tiene que bloquear muchas más filas que la fila específica que está cambiando. Incluso puede bloquear todas las filas en la tabla. Estos bloqueos bloquean los cambios realizados por otros clientes, incluso si no entran en conflicto lógicamente.
¿Cuándo es una buena idea agregar un índice?
Si sabe que necesita ejecutar una consulta que se beneficiaría de un índice en uno de los casos anteriores.
¿Cuándo es una mala idea agregar un índice?
Si el índice es un prefijo izquierdo de otro índice existente, o el índice no ayuda con ninguna de las consultas que necesita ejecutar.
Pro y Con de índices múltiples vs índices de varias columnas?
En algunos casos, MySQL puede realizar una optimización de fusión de índices y unirse o intersecar los resultados de búsquedas independientes de índices. Pero ofrece un mejor rendimiento para definir un solo índice, por lo que no es necesario realizar la fusión de índices.
Para uno de mis clientes de consultoría, definí un índice de varias columnas en una tabla de muchos a muchos donde no había índice, ¡y mejoré su consulta de combinación por un factor de 94 millones!
Diseñar los índices adecuados es un proceso complejo, basado en las consultas que necesita optimizar . No debe establecer reglas generales como "indexar todo" o "indexar nada para evitar ralentizar las actualizaciones".
Ver también mi presentación Cómo diseñar índices, realmente .