tipos - Índices de MySQL: ¿cuáles son las mejores prácticas?
reconstruir indices mysql (7)
Si una tabla tiene seis columnas y todas ellas se pueden buscar, ¿debería indexarlas todas o ninguna de ellas?
¿Estás buscando campo por campo o hay algunas búsquedas que usan campos múltiples? ¿Qué campos son los más buscados? ¿Cuáles son los tipos de campo? (El índice funciona mejor en INT que en VARCHAR, por ejemplo) ¿Ha intentado utilizar EXPLAIN en las consultas que se están ejecutando?
¿Cuáles son los impactos negativos en el rendimiento de la indexación?
Las ACTUALIZACIONES e INSERTOS serán más lentos. También hay requisitos adicionales de espacio de almacenamiento, pero eso no es importante en estos días.
Si tengo una columna VARCHAR 2500 que se puede buscar desde partes de mi sitio, ¿debería indexarla?
No, a menos que sea UNICO (lo que significa que ya está indexado) o solo busca las coincidencias exactas en ese campo (no usando LIKE o la búsqueda de texto completo de mySQL).
En general, pongo un índice en cualquier campo que voy a buscar o seleccionando usando una cláusula WHERE
Normalmente indexaría los campos que son los más consultados, y luego INT / BOOLEAN / ENUM en lugar de los campos que son VARCHARS. No olvide, a menudo necesita crear un índice en campos combinados, en lugar de un índice en un campo individual. Use EXPLAIN y verifique el registro lento.
He estado usando índices en mis bases de datos MySQL desde hace un tiempo, pero nunca he aprendido correctamente sobre ellos. En general, pongo un índice en cualquier campo que voy a buscar o seleccionar utilizando una cláusula WHERE pero a veces no parece tan blanco y negro.
¿Cuáles son las mejores prácticas para los índices de MySQL?
Ejemplos de situaciones / dilemas:
Si una tabla tiene seis columnas y todas ellas se pueden buscar, ¿debería indexarlas todas o ninguna de ellas?
.
¿Cuáles son los impactos negativos del rendimiento de la indexación?
.
Si tengo una columna VARCHAR 2500 que se puede buscar desde partes de mi sitio, ¿debería indexarla?
1/2) Los índices aceleran ciertas operaciones de selección pero ralentizan otras operaciones como insertar, actualizar y eliminar. Puede ser un buen equilibrio.
3) usa un índice de texto completo o tal vez esfinge
Definitivamente debe pasar un tiempo leyendo sobre la indexación, hay mucho escrito al respecto, y es importante comprender lo que está sucediendo.
En términos generales, el índice impone un orden en las filas de una tabla.
Por simplicidad, imagine que una tabla es solo un gran archivo CSV. Cada vez que se inserta una fila, se inserta al final . Entonces el orden "natural" de la tabla es solo el orden en que se insertaron las filas.
Imagine que tiene ese archivo CSV cargado en una aplicación de hoja de cálculo muy rudimentaria. Todo lo que hace esta hoja de cálculo es mostrar los datos y numera las filas en orden secuencial.
Ahora imagine que necesita encontrar todas las filas que tengan algún valor "M" en la tercera columna. Dado lo que tienes disponible, solo tienes una opción. Escanea la tabla comprobando el valor de la tercera columna para cada fila. Si tienes muchas filas, ¡este método (un "escaneo de tabla") puede llevar mucho tiempo!
Ahora imagina que además de esta tabla, tienes un índice. Este índice en particular es el índice de valores en la tercera columna. El índice enumera todos los valores de la tercera columna, en un orden significativo (por ejemplo, alfabéticamente) y para cada uno de ellos, proporciona una lista de números de fila donde aparece ese valor.
Ahora tiene una buena estrategia para encontrar todas las filas donde el valor de la tercera columna es "M". Por ejemplo, puedes realizar una búsqueda binaria . Mientras que la exploración de tabla requiere que busque N filas (donde N es el número de filas), la búsqueda binaria solo requiere que observe las entradas de índice log-n, en el peor de los casos. ¡Guau, eso es mucho más fácil!
Por supuesto, si tiene este índice y está agregando filas a la tabla (al final, ya que así es como funciona nuestra tabla conceptual), necesita actualizar el índice todas las veces. Así que haces un poco más de trabajo mientras escribes filas nuevas, pero ahorras mucho tiempo cuando estás buscando algo.
Entonces, en general, la indexación crea una compensación entre la eficiencia de lectura y la eficiencia de escritura. Sin índices, las inserciones pueden ser muy rápidas: el motor de base de datos simplemente agrega una fila a la tabla. A medida que agrega índices, el motor debe actualizar cada índice mientras realiza la inserción.
Por otro lado, las lecturas se vuelven mucho más rápidas.
Afortunadamente, eso cubre sus dos primeras preguntas (como otros respondieron, necesita encontrar el equilibrio adecuado).
Tu tercer escenario es un poco más complicado. Si está utilizando LIKE, los motores de indexación generalmente le ayudarán con su velocidad de lectura hasta el primer "%". En otras palabras, si está SELECCIONANDO DONDE la columna LIKE ''foo% bar%'', la base de datos usará el índice para buscar todas las filas donde la columna comienza con "foo", y luego deberá escanear ese conjunto de filas intermedio para encontrar el subconjunto que contiene "barra" SELECCIONAR ... DONDE la columna LIKE ''% bar%'' no puede usar el índice. Espero que puedas ver por qué.
Finalmente, debe comenzar a pensar en los índices en más de una columna. El concepto es el mismo, y se comporta de manera similar a lo LIKE: esencialmente, si tiene un índice en (a, b, c), el motor continuará usando el índice de izquierda a derecha lo mejor que pueda. Entonces, una búsqueda en la columna a podría usar el índice (a, b, c), como lo haría uno en (a, b). Sin embargo, el motor necesitaría hacer una exploración completa de la tabla si estuviera buscando DONDE b = 5 AND c = 1)
Espero que esto ayude a arrojar algo de luz, pero debo reiterar que es mejor que pasen unas horas investigando buenos artículos que expliquen estas cosas en profundidad. También es una buena idea leer la documentación de su servidor de base de datos en particular. La manera en que los planificadores de consultas implementan y usan los índices puede variar ampliamente.
En general, los índices ayudan a agilizar la búsqueda en la base de datos, teniendo la desventaja de utilizar espacio adicional en el disco y ralentizar las consultas INSERT / UPDATE / DELETE . Use EXPLAIN y lea los resultados para saber cuándo MySQL usa sus índices.
Si una tabla tiene seis columnas y todas pueden buscarse, ¿debería indexarlas todas o ninguna de ellas?
Indexar las seis columnas no siempre es la mejor práctica.
(a) ¿Va a utilizar alguna de esas columnas cuando busca información específica?
(b) ¿Cuál es la selectividad de esas columnas (cuántos valores distintos hay almacenados, en comparación con la cantidad total de registros en la tabla)?
MySQL usa un optimizador basado en costos, que trata de encontrar la ruta "más barata" al realizar una consulta. Y los campos con baja selectividad no son buenos candidatos.
¿Cuáles son los impactos de rendimiento negativo de la indexación?
Ya respondió: espacio en disco adicional, menor rendimiento durante la inserción - actualización - eliminar.
Si tengo una columna VARCHAR 2500 que se puede buscar desde partes de mi sitio, ¿debería indexarla?
Pruebe el índice FULLTEXT .
No repetiré algunos de los buenos consejos en otras respuestas, pero agregaré:
Índices compuestos
Puede crear índices compuestos, un índice que incluye múltiples columnas. MySQL puede usarlos de izquierda a derecha . Entonces si tienes:
Table A
Id
Name
Category
Age
Description
si tiene un índice compuesto que incluye Nombre / Categoría / Edad en ese orden, estas cláusulas WHERE usarían el índice:
WHERE Name=''Eric'' and Category=''A''
WHERE Name=''Eric'' and Category=''A'' and Age > 18
pero
WHERE Category=''A'' and Age > 18
no usaría ese índice porque todo tiene que ser usado de izquierda a derecha.
Explique
Use Explain / Explain Extended para comprender qué índices están disponibles para MySQL y cuál selecciona realmente. MySQL solo usará UNA clave por consulta .
EXPLAIN EXTENDED SELECT * from Table WHERE Something=''ABC''
Registro de consulta lenta
Active el registro lento de consultas para ver qué consultas se ejecutan lentamente.
Columnas anchas
Si tiene una columna ancha donde MÁS de la distinción ocurre en los primeros caracteres, puede usar solo los primeros N caracteres en su índice. Ejemplo: Tenemos una columna ReferenceNumber definida como varchar (255) pero el 97% de los casos, el número de referencia es de 10 caracteres o menos. Cambié el índice para ver solo los primeros 10 caracteres y mejoré bastante el rendimiento.
Vea presentaciones como Mas dominio del arte de la indexación .
Actualización 12/2012: publiqué una nueva presentación mía: Cómo diseñar índices, realmente . Presenté esto en octubre de 2012 en ZendCon en Santa Clara, y en diciembre de 2012 en Percona Live London.
Diseñar los mejores índices es un proceso que tiene que coincidir con las consultas que ejecuta en su aplicación.
Es difícil recomendar reglas de propósito general sobre qué columnas son las mejores para indexar, o si debe indexar todas las columnas, sin columnas, qué índices deben abarcar varias columnas, etc. Depende de las consultas que necesite ejecutar.
Sí, hay algunos gastos generales, por lo que no debe crear índices innecesariamente. Pero debe crear los índices que dan beneficio a las consultas que necesita para ejecutar rápidamente. La sobrecarga de un índice generalmente es superado por su beneficio.
Para una columna que es VARCHAR (2500), probablemente desee usar un índice FULLTEXT o un índice de prefijo:
CREATE INDEX i ON SomeTable(longVarchar(100));
Tenga en cuenta que un índice convencional no puede ayudar si está buscando palabras que pueden estar en el medio de ese varchar largo. Para eso, use un índice de texto completo.
Cargue datos de manera eficiente : los índices aceleran las recuperaciones pero ralentizan las inserciones y eliminaciones, así como las actualizaciones de los valores en las columnas indexadas. Es decir, los índices ralentizan la mayoría de las operaciones que implican escritura. Esto ocurre porque escribir una fila requiere escribir no solo la fila de datos, también requiere cambios en los índices. Cuantos más índices tenga una tabla, más cambios habrá que hacer y mayor será la degradación del rendimiento promedio. La mayoría de las tablas reciben muchas lecturas y pocas escrituras, pero para una tabla con un alto porcentaje de escrituras, el costo de la actualización del índice puede ser significativo.
Evite los índices : si no necesita un índice en particular para ayudar a que las consultas tengan un mejor rendimiento, no lo cree.
Espacio en disco : un índice ocupa espacio en disco, y múltiples índices ocupan correspondientemente más espacio. Esto puede hacer que alcance un límite de tamaño de tabla más rápidamente que si no hay índices. Evite los índices siempre que sea posible.
Para llevar: no indexar en exceso