Monday, 17. November 2008 19:04
Hoy preguntaban por el canal #MySQL de freenode si era necesario usar un índice en una tabla que usaba para buscar a qué grupos pertenecía un usuario. Vamos, algo como esto:
mysql> desc t1\G
*************************** 1. row ***************************
Field: id
Type: int(11)
Null: NO
Key:
Default: NULL
Extra:
*************************** 2. row ***************************
Field: id2
Type: int(11)
Null: NO
Key:
Default: NULL
Extra:
No tiene índices intencionádamente. Así es como estaba la tabla del sujeto en cuestión. Por lo general, nunca es mala idea añadir algún índice salvo que andes mal de espacio y no solventes en gran medida el tiempo de búsqueda de un registro, cosa poco habitual. Vamos a recrear la situación.
Lo primero es crear las tablas y alimentarlas. Vamos a nuestra consola (mi opción preferida):
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`id2` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
Ahora toca añadir datos a la tabla. He usado http://www.generatedata.com/#generator, una aplicación web para generar valores de prueba. Tiene una limitación de 200 registros, aunque cabe la posibilidad de bajarse la aplicación para montárselo uno mismo o dar un donativo. Yo me lo bajé para generar 5000 registros. Vamos a ver lo que tarda en devolverme los 5000 registros:
mysql> select /*SQL_NO_CACHE*/ id,id2 from t1;
...
5000 rows in set (0.01 sec)
Vale, ahora veremos lo que tarda en devolver los registros de un determinado id:
mysql> select /*SQL_NO_CACHE*/ id,id2 from t1 where id=37;
...
53 rows in set (0.00 sec)
Perfecto. Para que fuera aún más real, tendríamos que meter muchos más registros. Dejo a cada cual que haga sus pruebas. De todas formas, no nos hacen falta los datos para saber cómo actúa Mysql. Y aquí viene en nuestra ayuda el comando EXPLAIN (http://dev.mysql.com/doc/refman/5.0/es/explain.html). Con él conocemos el plan de ejecución del optimizador de MySQL. Vemos un ejemplo:
mysql> explain select id,id2 from t1 where id = 37\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 38389
Extra: Using where
En este artículo no voy a explicar cada uno de los campos, ya haré otro sobre el tema, pero básicamente esto nos dice que la select no tiene ningún tipo de optimización porque:
– No hay índices posibles
– Por lo tanto, no se aplica ninguno
– El número de registros aproximados a ver son 38389, vamos, todos. Esto es lo que se llama un full scan de la tabla.
Por lo tanto, está claro que esta tabla es optimizable. Creamos un índice con el campo id, que es el que usamos en la búsqueda:
mysql> alter table t1 add index idx_id (id);
Query OK, 38389 rows affected (0.20 sec)
Records: 38389 Duplicates: 0 Warnings: 0
Y volvemos a hacer la query con el EXPLAIN
mysql> explain select id,id2 from t1 where id = 37\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ref
possible_keys: idx_id
key: idx_id
key_len: 4
ref: const
rows: 50
Extra:
1 row in set (0.00 sec)
La cosa cambia, ya hay índices posibles e índices aplicados, el número de registros ha cambiado y el campo Extra está vacío. Pero vamos a ver una curiosidad:
mysql> select count(id) from t1 where id = 37\G
*************************** 1. row ***************************
count(id): 53
1 row in set (0.01 sec)
En valor de rows es 50 sin embargo la query devuelve 53 registros. Ya comentaba antes que ese valor es aproximativo. Pero aún se puede optimizar más:
mysql> alter table t1 add index idx_id_id2 (id,id2);
Esto crea otro índice agrupando los dos campos de la tabla. Por lo que si ahora usamos el analizador:
mysql> explain select id,id2 from t1 where id = 37\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ref
possible_keys: idx_id,idx_id_id2
key: idx_id_id2
key_len: 4
ref: const
rows: 50
Extra: Using index
1 row in set (0.00 sec)
El índice usado ha pasado de ser idx_id a idx_id_id2 y en Extra ahora aparece “Using index”. Lo que hemos conseguido es que MySQL no tenga que ir a buscar los datos a la tabla, sino que los coja diréctamente del fichero de índices. Al final los ficheros quedan así:
-rw-rw---- 1 mysql mysql 338K 2008-11-17 18:55 t1.MYD
-rw-rw---- 1 mysql mysql 923K 2008-11-17 18:55 t1.MYI
Y después de elimiar el primer índice que creamos:
-rw-rw---- 1 mysql mysql 338K 2008-11-17 18:59 t1.MYD
-rw-rw---- 1 mysql mysql 538K 2008-11-17 18:59 t1.MYI
Como os comentaba, la contrapartida es el uso de espacio en disco pero en aplicaciones con altos volúmenes de datos, la velocidad de búsqueda puede ser más importante que el espacio usado.