Tags: promysql

Hacer las consultas más eficiente (3) - La importancia de los índices

By admin on Apr 11, 2009 | In Gestión etc., Trucos avanzado | Send feedback »

Se dice a menudo que la mala utilización de índices (o la falta de un índice) es la manera más rápida de matar a un sistema.
Un índice funciona por leer una lista de campos pre-clasificados para acceder a la consulta, en lugar de tener que realizar un gran ordenación en la memoria. Hay que recordar sin embargo que los índices vienen a un costo, principalmente en el uso de espacio de almacenamiento en disco (y también dificultan el proceso de actualización - INSERT, DELETE o UPDATE). Una cosa importante a tener en cuenta cuando haciendo la creación de un índice, es que es preferible reducir al mínimo el tamaño de los campos indexados. Un índice en un pequeño campo de tipo int es mucho mejor que un índice en un campo de tipo varchar(100). Si es necesario índice de un campo de tipo varchar, consideren la posibilidad de limitar el índice de los primeros caracteres, por ejemplo:

Code:

CREATE INDEX addr_line_indx ON members (addr_line1(15));


El índice general, funciona mejor cuando se usa la cláusula WHERE directamente con el campo por ejemplo WHERE col = 123 , o cuando se busca el maximo valor de la columna (MAX) o el mínimo valor de la columna (MIN). El índice también se puede utilizar para consultas de correspondencia con patron - la cláusula LIKE -, siempre que la comparación de cadenas no se inicia con un comodín (por ejemplo el signo de porcentaje). Así que, usando "WHERE campoA LIKE 'FARSC%'", se utilizará un índice en esta consulta, el siguiente no: "WHERE campoA LIKE '%FARSCAPE%'".
Para leer más sobre cómo MySQL usa índices (Lamentablemente esta sección todavía no se ha traducido al español):

mysql manual

Otro factor a considerar en poner un índice en un campo, es el de la selectividad. Si hay demasiados registros de datos del mismo valor (por ejemplo, si el campo registros de sexo, 'M' o 'F') luego Mysql le resultará más fácil para cargar todos los registros en la memoria y escanear a través de ellos, en lugar de utilizar el índice. La base de datos Mysql mantiene estadísticas sobre los índices de las tablas por lo que puede hacer una suposición en cuanto al momento de utilizar un índice o no. Por lo tanto, sólo tiene sentido utilizar los índices en los que el campo tiene un alto selectivty es decir, el conjunto de datos contiene su mayoría o totalidad valores únicos.

Una cosa para recordar en la creación de un índice que no tiene que limitarse a una sola columna. Por ejemplo, si se consulta a menudo en dos columnas Nombre y apellido, puede crear un multi-columna (o compuesto) índice:

Code:

CREATE INDEX name_idx ON members (apellido, nombre);

El orden de los componentes del índice es significativo. En este escenario, el índice sólo se utiliza en el campo nombre, cuando la cláusula WHERE incluye el campo apellido. Cuando las consultas sólo se especifican el campo nombre, un índice puede tener que ser creado. Además, si utiliza la cláusula de OR, el índice se ignora. Puedes leer más acerca de índices compuestos aquí

manual sobre multiple column

A veces, un índice no se utiliza en una consulta porque una función de conversión se lleva a cabo en el campo. Si puedes aislar un campo indexado en el lado izquierdo de la cláusula WHERE, puede persuadir a Mysql a utilizar el índice de la consulta

El mejor ejemplo de esto es algo que encontré en el libro Pro Mysql (Kruckenberg and Pipes):

Ejemplo de mala optimización:

Code:

SELECT * FROM customer_orders
WHERE TO_DAYS(pedido_alto) - TO_DAYS(NOW()) <= 7;

.
Buena optimización (el índice del campo pedido_alto se utilizará):

Code:

SELECT * FROM customer_orders
WHERE pedido_alto >= DATE_SUB(NOW(), INTERVAL 7 DAY);


Habrá casos en que sin embargo no se puede realizar una búsqueda de un campo sin necesidad de utilizar una función o transformación de este campo. En estos casos, si la consulta es necesario a menudo, puede ser más eficaz para crear una columna adicional que contiene el valor calculado de la columna.
Por ejemplo (de nuevo desde el libro ProMysql), la siguiente consulta SQL no va a utilizar un índice, incluso si existe en la columna EMAIL_ADDRESS

Code:

SELECT * FROM customers
WHERE email_address LIKE '%aol.com';


La solución para esto es crear una columna adicional que contiene el reverso de la dirección de correo electrónico y, a continuación, poner un índice sobre esta columna. Entonces podemos utilizar el índice a través de la siguiente consulta:

Code:

SELECT * FROM customers
WHERE email_address_reversed
  LIKE CONCAT(REVERSE('%aol.com'), '%');


Por supuesto, así como el espacio en disco adicional necesario para la columna adicional e índice, también tenemos que garantizar que el campo email_address_reversed se rellenará regularmente. Esto podría ser a través de la utilización de los disparadores en las inserciones y actualizaciones. O puede ejecutar una rutina de actualización de la tabla en intervalos regulares. Todo depende de la frecuencia con que necesitas para ejecutar la consulta de manera eficiente.

Una última cosa que pueden hacer sus consultas correr más rápido. Si la tabla ha tenido una gran cantidad de borra, inserciones y actualizaciones, puede ser un valor con el comando OPTIMIZE TABLE, por ejemplo

Code:

OPTIMIZE TABLE large_order_table;


Esto efectivamente es para reclamar el usuario no usado y para defragmentar el fichero de datos. También se mantiene el índice de las estadísticas actuales, que ayuda a MySQL a decidir cuándo usar un índice en una columna.

Saludos, Mark

Tags: indices, índices compuestos, optimization, promysql