Optimizacion MySQL (Para programadores y DBA)
^Tifa^:
Tengo varios amigos webmasters que de vez en cuando suelen recurrir a preguntarme o solicitarme algun tipo de ayuda referente a optimizacion de consultas en el motor MySQL.
Resulta que en dias pasados, un amigo muy cercano me solicito ayuda debido a que el posee una tabla con los links URL de cada web en su proyecto, como son muchos URLs (miles de sublinks) que heredan de otros links, el tiene una primary key por cada link, pero puestos en el tema todos saben lo dificil que es recordarse o relacionar el valor que posee el primary key del link numero 2000 por ejemplo, por ende el utilizaba como indice el campo URL de su tabla. Pero dicho campo era varchar(100) lo cual solia relantelizar la respuestas del servidor a la hora de muchas consultas masivas de clickeos en links aun siendo dicho campo un indice.
Como su aplicacion corre en MySQL, y el unico motor de almacenamiento en MySQL que soporta hash indices es el motor MEMORY el cual solo guarda temporalmente en memoria los datos, y el usaba MyISAM, procedi a ofrecerle un pseudo hash indice en MYISAM.
Por ejemplo implemento la siguiente tabla:
Código
mysql> DESCRIBE ejemplo;
+-------+---------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | Extra |
+-------+---------------+------+-----+---------+-------+
| id | SMALLINT(6) | NO | PRI | 0 | |
| url | VARCHAR(50) | YES | | NULL | |
| url_x | DECIMAL(10,0) | YES | MUL | NULL | |
+-------+---------------+------+-----+---------+-------+
3 ROWS IN SET (0.01 sec)
mysql> INSERT INTO ejemplo VALUES(5, 'www.google.com/index?234web%3%.asp', crc32('www.google.com/index?234web%3%.asp'));
Query OK, 1 ROW affected (0.00 sec)
mysql> INSERT INTO ejemplo VALUES(6, 'www.amigos.com/amistad/indice/fotos/personas', crc32('www.amigos.com/amistad/indice/fotos/personas'));
Query OK, 1 ROW affected (0.00 sec)
mysql> SELECT * FROM ejemplo;
+----+----------------------------------------------+------------+
| id | url | url_x |
+----+----------------------------------------------+------------+
| 4 | www.yahoo.com | 3748556277 |
| 3 | www.mysql.com | 2595849497 |
| 2 | www.google.com | 526628817 |
| 1 | www.google.com/INDEX.php | 1007078110 |
| 5 | www.google.com/INDEX?234web%3%.asp | 4027181565 |
| 6 | www.amigos.com/amistad/indice/fotos/personas | 1498747455 |
+----+----------------------------------------------+------------+
6 ROWS IN SET (0.00 sec)
En lo anterior presento como quite el indice del campo URL que es VARCHAR, asigne un campo 'extra' llamado URL_X de tipo NUMERIC (Las consultas sobre indices constantes numericos son de mayor velocidad que en campos caracteres). y le declare un INDEX a este ultimo campo, dejando al campo ID como llave primaria y a URL_X como INDEX.
Ahora hago una ligera consulta:
Código
mysql> SELECT url,id FROM ejemplo WHERE url_x = crc32('www.yahoo.com') AND id > 0;
+---------------+----+
| url | id |
+---------------+----+
| www.yahoo.com | 4 |
+---------------+----+
1 ROW IN SET (0.00 sec)
mysql> SELECT url,id FROM ejemplo WHERE url_x = crc32('www.google.com/index?234web%3%.asp') AND id > 0;
+------------------------------------+----+
| url | id |
+------------------------------------+----+
| www.google.com/INDEX?234web%3%.asp | 5 |
+------------------------------------+----+
1 ROW IN SET (0.00 sec)
Diran que es pesado estar realizando puros INSERT repitiendo la URL en 2 campos a la vez siempre, por ende para manejar de manera automatica lo anterior se puede hacer un TRIGGER:
Código
mysql> delimiter ;
mysql> DESCRIBE ejemplo;
+-------+---------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | Extra |
+-------+---------------+------+-----+---------+-------+
| id | SMALLINT(6) | NO | PRI | 0 | |
| url | VARCHAR(50) | YES | | NULL | |
| url_x | DECIMAL(10,0) | YES | MUL | NULL | |
+-------+---------------+------+-----+---------+-------+
3 ROWS IN SET (0.01 sec)
mysql> INSERT INTO ejemplo(id,url) VALUES(7,'www.gifmania.com/perros/chihuahua.jpg');
Query OK, 1 ROW affected (0.02 sec)
mysql> INSERT INTO ejemplo(id,url) VALUES(8,'www.gifmania.com/perros/peluche.jpg');
Query OK, 1 ROW affected (0.00 sec)
mysql> INSERT INTO ejemplo(id,url) VALUES(9,'www.gifmania.com/perros/pitbull.jpg');
Query OK, 1 ROW affected (0.00 sec)
mysql> SELECT * FROM ejemplo;
+----+----------------------------------------------+------------+
| id | url | url_x |
+----+----------------------------------------------+------------+
| 4 | www.yahoo.com | 3748556277 |
| 3 | www.mysql.com | 2595849497 |
| 2 | www.google.com | 526628817 |
| 1 | www.google.com/INDEX.php | 1007078110 |
| 5 | www.google.com/INDEX?234web%3%.asp | 4027181565 |
| 6 | www.amigos.com/amistad/indice/fotos/personas | 1498747455 |
| 7 | www.gifmania.com/perros/chihuahua.jpg | 1056638155 |
| 8 | www.gifmania.com/perros/peluche.jpg | 1463517104 |
| 9 | www.gifmania.com/perros/pitbull.jpg | 3203321590 |
+----+----------------------------------------------+------------+
9 ROWS IN SET (0.00 sec)
mysql> SELECT url,id FROM ejemplo WHERE url_x = crc32('www.gifmania.com/perros/pitbull.jpg') AND id > 0;
+-------------------------------------+----+
| url | id |
+-------------------------------------+----+
| www.gifmania.com/perros/pitbull.jpg | 9 |
+-------------------------------------+----+
1 ROW IN SET (0.00 sec)
Tambien se puede implementar un TRIGGER update para las situaciones de actualizacion del campo URL. Ahora la mejor parte, analizar la consulta con EXPLAIN para ver el nivel de Optimizacion de la misma:
Código
mysql> EXPLAIN SELECT url,id FROM ejemplo WHERE url_x = crc32('www.gifmania.com/perros/pitbull.jpg') AND id > 0;
+----+-------------+---------+------+---------------+-------+---------+-------+------+-------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | REF | ROWS | Extra |
+----+-------------+---------+------+---------------+-------+---------+-------+------+-------------+
| 1 | SIMPLE | ejemplo | REF | PRIMARY,url_x | url_x | 6 | const | 1 | USING WHERE |
+----+-------------+---------+------+---------------+-------+---------+-------+------+-------------+
1 ROW IN SET (0.00 sec)
Donde se ve claramente acorde al optimizador interno de MySQL, que se da uso del indice URL_X con un valor constante y se analiza sencillamente 1 sola fila de toda la tabla, el motor sabe exactamente ya donde ir, y para alcanzar su objetivo solamente analizo 1 fila.
Sin embargo si se aplica el EXPLAIN sobre el campo URL (Que es VARCHAR no tiene ningun indice asignado):
Código
mysql> EXPLAIN SELECT url,id FROM ejemplo WHERE url = 'www.gifmania.com/perros/peluche.jpg' AND id > 0;
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | REF | ROWS | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | ejemplo | ALL | PRIMARY | NULL | NULL | NULL | 9 | USING WHERE |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
1 ROW IN SET (0.00 sec)
mysql> notee;
Aca se ve que no aplica ningun indice, ni aprovecha ninguna llave ni nada analiza todas las filas existentes en mi tabla para retornarme el valor.
Diran que es mas factible asignar indice al campo URL y ya estuvo, pero por optimizacion las lecturas de un indice sobre valores de longitud dinamica donde puedo encontrar valores de 50 caracteres como de 20 caracteres en la proxima verificacion es algo que relantiza el proceso y provoca lo que mi amistad estaba padeciendo.
El tuvo que reconstruir su tabla, pero su proyecto va muchisimo mejor en respuesta de solicitudes, y ya que es una tecnica muy popular colocar urls en las tablas es preferible asignarle un pseudoindice que funcione como un hash indice que asignar dicho campo de longitud variable y de poca optimizacion.
Navegación
[*] Página Anterior