Optimizacion MySQL (Para programadores y DBA)

[^Tifa^]

Esto solo es el primer paso de como optimizar un motor MySQL para mejores resultados. Ire ofertando mas nuevas implementaciones en el futuro cercano.

EXPLAIN MySQL es una herramienta indispensable (no la única) a la hora de realizar tareas de otpimización sql.

Explain MySQL no es más que una manera de mostrar como MySQL procesa las sentencias SQL mediante sus índices y uniones. El uso de Explain MySQL permite ayudar a los DBAs, en una primera instancia, a mejor el diseño de base de datos agregando índices y permitiendo una selección de consultas más óptimas.

Lo único que debemos hacer para hacer uso de Explain MySQL es anteponer "Explain" a la SQL deseada.


   

Código (sql) [Seleccionar] Expandir

EXPLAIN SELECT * FROM `localidades` WHERE id =1


Ejecutando esta SQL, MySQL nos indica como la está procesando y nos mostrará un listado con información sobre índices, tablas, resultados, etc.

En el resultado de explain de mysql visualizaremos una tabla con 10 columnas de información para cada tabla implicada




De las columnas anteriores cabe destacar:

1. type: Esta columna indica el tipo de unión que se está usando (de más a menos óptimo).

Donde SIMPLE (Es cuando no se usan Uniones o SubConsultas)
Donde UNION (Es cuando usamos la clausula UNION en 2 tablas)
Donde SubQUERY ( Es cuando usamos Subconsultas)


2. const: Es la más óptima y se dá cuando la tabla tiene como máximo una fila que coincide. Como solo hay una fila coincidente, MySQL la considerará como constante por el optimizador.

3. eq_ref: Una fila será leída de la tabla A por cada combinación de fila de la tabla B. Este tipo es usada cuando todas las partes de un índice son usados para la consulta y el índice es UNIQUE o PRIMARY

4. ref: Todas las filas con valores en el índice que coincidan serán leídos desde esta tabla por cada combinación de filas de las tablas previas. Si la clave que es usada coincide sólo con pocas filas, esta unión es buena.

5. range: Sólo serán recuperadas las filas que estén en un rango dado, usando un índice para seleccionar las filas. La columna key indica que índice se usará, y el valor key_len contiene la parte más grande de la clave que fue usada. La columna ref será NULL para este tipo.

6. index: Este es el mismo que ALL, excepto que sólo el índice es escaneado. Este es usualmente más rápido que ALL, ya que el índice es usualmente de menor tamaño que la tabla completa.

7. ALL: Realiza un escaneo completo de tabla por cada combinación de filas de las tablas previas. Este caso es el peor de todos.

8. possible_keys: Esta columna indica los posibles índices a utilizar en la consulta

9. key: Esta columna indica el indice que MySQL actualmente está usando. Esta columna es NULL si no se ha elegido ninguno. Es interesante saber que podemos forzar a MySQL a usarlo (y también a ignorarlo) mediante el uso de FORCE INDEX, USE INDEX o IGNORE INDEX

10. key_len: El tamaño del índice usado. A menor valor mejor.

11. ref: La columna ref muestra que columna o constante es usada junto a la key para seleccionar las columnas de la tabla

12. rows: Indica el número de columnas que MySQL cree necesario examinar para ejecutar la SQL.

13. extra: Indica información adicional de como MySQL ha resuelto la SQL y hay que prestar atención si aparece USING FILESORT o USING TEMPORARY. En el primer caso, indica que MySQL debe hacer un paso extra para recuperar la información. En el segundo, MySQL necesita generar una tabla extra para mantener la información y después mostrarla y es típico al usar GROUP BY u ORDER BY.

En ocasiones, MySQL puede mostrar ALL en la columna type cuando:

1. La tabla es tan pequeña que MySQL ve más rapido hacer un escaneo completo de la tabla.

2. No hay restricciones usando las cláusulas ON o WHERE.

3. Se compara columnas indexadas con valores constantes.

4. Las claves utilizadas devuelven muchos registros que coinciden con el valor.

Aún así, hay que decir que éste no es el único método de optimización SQL, aunque es uno bueno

Fuente de lo publicado Anteriormente:
http://www.sergiquinonero.net/explain-mysql-o-como-optimiza-sql.html

[^Tifa^]

COMO: Optimizar el tiempo de respuesta de MySQL

Hay muchas maneras de mejorar el tiempo de respuesta del servidor MySQL: al modificar el tamaño de la caché, deteniendo la resolución de DNS ....
Vamos a ver cómo hacerlo.

Recuerdo que, una vez en el trabajo, estábamos teniendo problemas con nuestro sistema de bases de datos.  Los servidores MySQL eran lentos para responder, pero cuando se accedia a las máquinas, la carga estaba bien, había bastantes pocas preguntas en marcha, pero MySQL no daba informaron de ella estaba abrumado.

1.  Deshabilitar nombre de host DNS Lookup
  Después de buscar la razón por la que el tráfico no iba a la perfección, se determina que el servidor MySQL estaba haciendo un montón de consultas de resolución de nombre!!  ¿Para qué?  ¿Por qué si MySQL solo realizaba conexiones locales?
  Buscando en la página del manual mysqld, encontre que esto podría ser desactivado mediante la adición de la opción - skip-name-resolve.

Bajo sistemas basados en Debian, como Ubuntu, Knoppix ...  y en la mayoría de distribuciones Linux, los archivos de configuración de MySQL están ubicados en / etc / mysql / my.cnf.
A fin de aplicar la opción - skip-name-resolve cuando arranca mysqld, sólo tiene que añadir:

[mysqld]
  .....
  ......
  skip-name-resolve

NOTA: Cuando se activa esta opción, sólo se puede utilizar números IP en la tabla de permisos de MySQL.
Aquí hay un pequeño punto de referencia:
Con una resolución de DNS de nombre de host:

Con una resolución de DNS de nombre de host activa:

date; mysql-u root-h 192,168 .1 .4; date
  Frid Jul 21 23: 56: 58 CEST 2006
  ERROR 1130 (00000): Host '192 .168.1.3 'no se le permite conectarse a este servidor MySQL
  Frid Jul 21 23: 57: 00 CEST 2006

Vease que se tomo de 2-3 segundos antes de recibir respuesta del servidor que la IP del cliente no puede conectarse.
Una vez que el nombre de host de DNS no está habilitado:

date; mysql-u root-h 192,168 .1 .4; date
  Frid Jul 21 23: 56: 37 CEST 2006
  ERROR 1130 (00000): Host '192 .168.1.3 'no se le permite conectarse a este servidor MySQL
  Frid Jul 21 23: 56: 37 CEST 2006

El servidor está respondiendo al instante.

(Solo recomendable para datawarehouse bajo MySQL o tablas en motor donde no se utilize INSERT)

* La cache de consultas (Query Cache) es muy beneficiosa para entornos Datawarehouse
o en tablas con motores comprimidos. Solamente es efectiva en estos tipos puesto
que la cache de MySQL se resetea inmediatamente al utilizar clausulas como
INSERT, UPDATE o DELETE.

Que beneficios otorga tener activada la cache de consultas en MySQL?

* Pues otorga guardar en cache las consultas realizadas, optimizando asi una segunda vez
realizando la misma consulta el tiempo de espera de respuesta mejora en un 260% su rapidez
de respuesta.

2.  Activar la caché de consultas
Mediante la comprobación de la memoria caché de consultas:

mysql> SHOW STATUS LIKE% 'Qcache';

(Si salen todos los valores en 0 es porque la cache esta desactivada, debera ser activada
dentro de my.cnf)

Aca seran mostrados los valores actuales de la cache de consultas y cuantas consultas
estan guardadas en cache para su pronta respuesta:

mysql> show status like 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16765336 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 2        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 5        |
| Qcache_queries_in_cache | 2        |
| Qcache_total_blocks     | 7        |
+-------------------------+----------+
8 rows in set (0.00 sec)

En caso de agotarse la cache puede ser modificada dentro del archivo my.cnf

query_cache_size = 52428800;

O de manera temporal dentro del motor (Temporal por si el motor es reiniciado se pierde dicha configuracion)

mysql> SET GLOBAL query_cache_size = 52428800;

PD: Los valores son especificados en bytes, por lo cual traduciendo el valor anterior en MB serian 50MB.

[^Tifa^]

Memcached: un alivio para las bases de datos

Cuando la performance de un sistema web se torna crítica y el tiempo de respuesta puede quedar afectado por las numerosas visitas simultáneas, debemos echar mano a herramientas “poco convencionales”, este es el caso de Memcached.
¿Qué es Memcached?
Memcached es definido por Danga Interactive, la empresa que lo desarrollo y mantiene el proyecto bajo licencia BSD como un “sistema distribuido de alta performance para el cacheo de objetos en memoria, genérico por naturaleza, pero pensado para incrementar la velocidad de aplicaciones web dinámicas, aliviando la carga de las bases de datos”.
La idea es muy simple y a la vez muy efectiva: un servicio que escucha en un host y un port específico, que puede almacenar cadenas de bytes (strings). De esta manera, podríamos estar guardando en memoria una estructura serializada de PHP o Java, un string encodeado con Json, o un documento de cualquier formato (después de todo no dejan de ser cadenas de bytes).
Existe actualmente una implementacion de Memcached Server que corre en Win32. Si bien no es oficial, cuenta con el apoyo de varios desarrolladores de la comunidad.
Funcionamiento
A grandes rasgos, el Memcached Server almacena los strings (internamente denominados ítems), en una gran tabla de hash, y los mapea según la clave que le asociemos a dicho ítem.
Dicha tabla de hash adopta una estructura de porciones de memoria de tamaño variable (denominadas slabs), con el objetivo de optimizar la asignación del espacio de memoria.
Un dato no menor es el slab de mayor tamaño (1 Mb), siendo este por consiguiente el tamaño máximo que un ítem puede tener si quiere ser almacenado en Memcached Server (aunque este valor puede ser modificado desde el código fuente).
Su arquitectura escalable nos permite mantener un pool de Memcached servers, característica que bien puede ser explotada en momentos donde la cantidad de conexiones no puede ser gestionada por un único Server. En ese caso, el pool optará por otro Server, balanceando la carga de conexiones.
Protocolo
La comunicación de clientes con servidor es muy simple, y basada en comandos. El protocolo Memcached implementa 3 comandos de almacenamiento, con pequeñas diferencias en su funcionamiento:
SET: Actualiza el objeto si no existía anteriormente, o lo agrega en caso contrario.
ADD: Agrega el objeto solo si no existe.
REPLACE: Actualiza el objeto solo si existe.
Memcached permite controlar el tiempo de vida de un objeto, indicando el “tiempo de expiración” para el mismo, en el momento de realizar una operación de almacenamiento.
A su vez, posee un comando de recuperación: GET. Podemos eliminar un objeto mediante el comando DELETE. Además, el protocolo implementa comandos para recuperar estadísticas, vaciar el cache, utilizar algún tipo de compresión, entre otros.
Ejemplo de uso
<?php

// Inicializamos y conectamos
$memcache = new Memcache;
$memcache->connect('localhost', 11211) or die("No podemos conectarnos");

// Creamos una estructura a cachear
$pichongol = new stdClass;
$pichongol->nombre = “Daniel”;
$pichongol->apellido = “Lopez”;

// Almacenamos la estructura con una expiracion de 10 segundos
$memcache->set(“pichongol”, $pichongol, false, 10) or die ("No podemos guardar la estructura");

// Recuperamos la estructura
$result = $memcache->get(“pichongol”);
echo "Estructura recuperada:<br/>\n";
print_r($result);

?> 
¿Quiénes usan Memcached?
El desarrollo inicial y sus posteriores mejoras nacen como una necesidad de incrementar velocidades de respuesta para las peticiones web, en sitios de tráfico masivo.
Actualmente, su uso continúa expandiéndose, a medida que el proyecto toma mayor fuerza con la ayuda de varios desarrolladores de la comunidad Open Source que revisan y agregan nuevas capacidades al proyecto.
La siguiente es una lista acotada de sitios que actualmente utilizan Memcached para resolver cuestiones de escalabilidad:
LiveJournal
Memcached nace como un desarrollo para el backend de LiveJournal, y como tal, es el primer sitio que lo implementa.
Slashdot
Wikipedia
Fotolog
Luego de tener ciertas falencias de performance (producto de tener que manejar en promedio, de 35 a 40 millones de page views diarios) implementaron Memcached, logrando una gran mejoría en la prestación del servicio.
Hi5
Facebook
Contribuyó al proyecto, aportando muchas mejoras relacionadas con el manejo de memoria y los algoritmos de hashing, principalmente. Es actualmente la mayor implementación conocida, compuesta de alrededor de 200 servers de 1GB de memoria cada uno.

Conclusión
Memcached y escalabilidad van de la mano, y todos podemos sacar jugo de esta interesante herramienta. Nuevamente se demuestra que las ideas brillantes son simples por naturaleza.

Fuente:  http://www.maestrosdelweb.com/editorial/memcached-un-alivio-para-las-bases-de-datos/

[^Tifa^]

BenchMarks :

Ya que algunos usuarios tendran la duda de como testear el rendimiento de sus respectivas Base de Datos. Hago incapie a MySQL Becnhmark Suite, suelo generalmente descargar las nuevas fuentes de MySQL desde su website ya que esta incluye el Benchmark, para personas tan relacionadas con PERL como yo    esto queda como anillo al dedo:

Fuente:  http://dev.mysql.com/doc/refman/5.0/es/mysql-benchmarks.html

7.1.4. El paquete de pruebas de rendimiento (benchmarks) de MySQL

Esta sección debería contener una descripción técnica del paquete de pruebas de rendimiento de MySQL (así como del crash-me), pero esa descripción aún no ha sido escrita. Sin embargo, puede hacerse una buena idea de cómo hacer pruebas de rendimiento viendo el código y los resultados dentro del directorio sql-bench en el código fuente de la distribución de MySQL.

La finalidad de este paquete de pruebas de rendimiento es visualizar qué operaciones se realizan bien y cuáles lo hacen pobremente en cada implementación de SQL.

Estas pruebas de rendimiento no son multi hilo, así que miden el tiempo mínimo para las operaciones realizadas. Se planea agregar en un futuro pruebas multi hilo al paquete.

Para usar el paquete, deben satisfacerse los siguientes requisitos:

    *

      El paquete de pruebas de rendimiento se proporciona con el código fuente de la distribución de MySQL. También puede descargar una distribución liberada de http://dev.mysql.com/downloads/, o usar nuestro repositorio de código fuente(ver Sección 2.8.3, "Instalar desde el árbol de código fuente de desarrollo").
    *

      Los scripts de las pruebas de rendimiento están escritos en Perl y usan el módulo de Perl DBI para acceder a los servidores de bases de datos, así que DBI debe estar instalado. También es necesario el controlador DBI específico para cada servidor al que se quiere realizar las pruebas. Por ejemplo, para probar MySQL, PostgreSQL, y DB2, debe tener los módulos DBD::mysql, DBD::Pg, and DBD::DB2 instalados. Ver Sección 2.13, "Notas sobre la instalación de Perl".

Una vez obtenido el código fuente de la distribución de MySQL, el paquete de pruebas de rendimiento se encuentra en el directorio sql-bench. Para ejecutar las pruebas de rendimiento, compílese MySQL, váyase al directorio sql-bench y ejecútese el script run-all-tests:

shell> cd sql-bench
shell> perl run-all-tests --server=nombre_servidor

nombre_servidor debe ser uno de los servidores soportados. Para obtener la lista completa de opciones y servidores soportados, invóquese el comando:

shell> perl run-all-tests --help

El script crash-me también está situado dentro del directorio sql-bench. crash-me intenta determinar qué características soporta una base de datos y cuáles son sus capacidades y limitaciones. Esto lo consigue ejecutando consultas. Determina por ejemplo:

    *

      Cuáles tipos de columnas se soportan
    *

      Cuántos índicies se soportan
    *

      Qué funciones se soportan
    *

      Qué tamaño puede alcanzar una consulta
    *

      Que tamaño puede alcanzar una columna VARCHAR

Para más información acerca de resultados de pruebas de rendimiento, visítese http://dev.mysql.com/tech-resources/benchmarks/.

Para obtener scripts en otros lenguajes scripting como Python por ejemplo, pueden obtener algunos del blog de Mark Callaghan

http://mysqlha.blogspot.com/2009/02/code-for-benchmarking.html

Tambien si quieren medir rendimiento tanto en MySQL como otras Base de Datos:

http://osdb.sourceforge.net/index.php?page=home

[^Tifa^]

MOTORES DE ALMACENAMIENTO MYSQL


* MyISAM, el motor por defecto, permite lo típico, pero no permite transacciones, toda las consultas se realizan con autocommit. Por lo demás no hay mucho que comentar, como curiosidad decir que los BLOB o TEXT pueden ser indices, e incluso un campo que sea indice puede tomar valor NULL. Usa Arboles B internamete para los indices (separado de los datos) y tiene herramientas para chequeo y reparación de tablas.


* BLACKHOLE: si tiene un nivel de inglés tan patetico como el mio (o superior) fijo que descubres que hace este motor (blackhole = agujero negro). Sería el equivalente a /dev/null mayormente. Y dirás, ¿y esto para que cojones lo quiero yo?, pues puede llegar a ser útil, pues cuando realizas una transacción con este motor, auque no se guardan los datos, ni te va a devolver nada, si que crea LOG de la sentencia SQL que se "ha ejecutado". El caso típico podría ser establecer un servidor esclavo para que de ese modo guardará el log de lo que pasa en el master.


* CSV, motor completamente trivial, que guarda cada tabla en un fichero y cada fila de datos es una linea con los datos separados por comas. Queda claro, no?. Para hacer la gracia decir que no soporta indices (imagina buscar en ficheros... coste secuencial! O(n) OMFG!). Este formato sería usado mas bien para crear archivos listos para ser importados por otros programas.


* ARCHIVE, el motor almacen almacen, solo soporta INSERT's y SELECT's, es decir un almacen!. Además, siempre que escribes datos se comprimen (con zlib), así que es el motor típico para una base de datos histórica o cuando vamos a tener una cantidad realmente enorme de datos (quizás sea la idonea para GIS?, habría que meditarlo...). Decir que si se realizan muchos SELECT a la vez que se realizan INSERT provocaría que el motor se hiciese la picha un lio, ¿por qué? Porque cuando se hace un INSERT los datos van a un buffer (para no tener que recomprimir, con zlib, para cada p**a linea que se inserta supongo...) y éstos datos serán flusheados cuando se realice el SELECT, ahora piensa cientos de INSERT y SELECT en paralelo. Da miedo, eh?


* EXAMPLE, este no sirve para nada, jaja. Es solo un ejemplo de motor, para poder mirar su código y crear motores hechos y derechos


* FEDERATED, motor nuevo que se incorporó en la versión 5 de MySQL, para poder crear bases de datos federadas, esto significa que estaremos consultando a una bases de datos remota, es decir en nuestro servidor creamos la tabla pero le decimos, oye que esta tabla esta en otro lado, si eso, le preguntas, que fijo que te responde. Este modelo tiene ciertas limitaciones, no permite ALTER's ni transacciones.


* MERGE, este es facil, si tienes dos tablas con motor MyISAM y con la misma estructura, al crear una tabla MERGE, juntarás los datos de ambas tablas. Un caso para el cual puede ser útil este motor, podría ser, por ejemplo, diferentes tablas de log en diferentes servidores y te creas en uno de ellos tablas FEDERATED de esas tablas (que serán MyISAM) y entonces creas una tabla de "log_principal" (usando MERGE) que tendrá el log de todos los servidores. arrr marinero.
   

* MEMORY, tablas que se guardan en memoria, es decir, cuando reinicies MySQL, adios datos. No le encuentro ninguna utilidad la verdad, si quieres un almacenamiento temporal, que sentido tiene entonces usar un SGBD? Pues ninguno!.
   

* Berkeley DB (BDB para los friends), una de las bases de datos openSource más famosa y utilizada. El motor es independiente de MySQL, con las ventajas e inconvenientes que esto pueda acarrear. Permite transacciones (COMMIT & ROLLBACK) y solo puede ejecutarse en sistemas operativos soportados (Linux x86 y Windows, si; Mac OS X feo y Linux AMD64/Alpha, no). Como curiosidad decir que su organización de ficheros se basa en solo dos, puesto que utiliza árboles B donde, en cada nodo, están tanto los datos como el índice primario (lo cual implica que será algo más lento a la hora de recorrerlo secuencialmente)


* InnoDB, es el motor más avanzado (junto con BDB) en cuanto a opciones y funcionalidad. Permite transacciones seguras (COMMIT y tal) y está orientado a manejar grandes cantidad de datos. Realiza el bloqueo usando como granualidad la fila (BDB lo hace a nivel de página, es decir mayor salvo casos raros de filas enormes) e incluso soporta lecturas consistentes tanto bloqueantes como no bloqueantes.

Como reflexión final decir que los únicos motores que soportar transacciones seguras son BDB.

Cada Motor de Almacenamiento tiene su propia funcionalidad de manipular la data recibida y guardada. Dependiendo las funcionalidades de cada cual esto implicara mayor rapidez o lentitud bajo ciertas condiciones. Se dice bastante que Myisam es mas rapido en lectura que InnoDB por ejemplo y lo cual es cierto. Pero esto dependera en gran parte a como la aplicacion que se conecta manejara las consultas si da preferencia a consultas donde utilize indices clustered en este lado InnoDB llevara la delantera frente a Myisam y rapidez, puesto que InnoDB soporta indices clustered mas MySQL no.

Que son indices clusteres y indices no clusteres:

http://207.46.16.252/es-es/magazine/2008.02.sqlqa.aspx

[^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 (sql) [Seleccionar] Expandir



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 (sql) [Seleccionar] Expandir



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 (sql) [Seleccionar] Expandir



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 (sql) [Seleccionar] Expandir



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 (sql) [Seleccionar] Expandir



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.