“Optimización de sentencias MySQL”

1. “Optimización de sentencias MySQL” jueves 26 de septiembre de 2013

2. Sobre mí Nombre: Óscar / angro Email: angro@regresoalfuturo.com Twitter: @angro Desarrollador PHP, MySQL, Java, Android... Co-fundador y programador de Friends of Azeroth.

3. MYSQL

4. Por qué se le subestima • No se profundiza en él. • Con un vistazo a la sentencia básica, se piensa que está controlado. • Mala fama por culpa de malos códigos.

5. POTENCIAL • Tablas con millones de registros. • Joins de 3 tablas con millones de registros. • Sentencias de 20 minutos reducidas a 20 segundos.

6. ÍNDICES (INDEX)

7. INDICES • No muerden. • Consumen poca memoria. • Aceleran mucho. • Obligatorios en campos usados en WHERE, ODER BY, etc. • Evitarlos en los campo texto siempre que sea posible.

8. PRUEBAS - PREMISAS • Tabla: CREATE TABLE tabla ( id int(11) NOT NULL AUTO_INCREMENT, nombre varchar(50) NOT NULL, edad int(11) NOT NULL, PRIMARY KEY (`id`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1; • 65.690 registros con “edad” aleatoria entre 1 y 100 • Sentencia de prueba: SELECT * FROM tabla WHERE edad = 56; • Servidor sin actividad.

9. PRUEBAS - RESULTADOS • Número de registros recuperados: 655. • Sin índice en “edad”: 0.06578975 segundos • Con índice en “edad”: 0.00014250 segundos • Reducción de tiempo 460 veces.

10. NÚMEROS / ENUMS

11. NÚMEROS / ENUMS • Los enum son números por detrás. • Si se puede elegir, siempre usar campos numéricos o enums. • Ocupan menos en disco y en memoria que su equivalente en texto. • Las búsquedas son mucho más rápidas que sobre texto.

12. EJEMPLO DE ELECCIÓN Tenemos una tabla con un campo estado_civil. Puede ser: • Campo texto: “soltero”, “casado”, etc. • Campo numérico: 1 - Soltero; 2 - Casado, etc. La 2, la mejor opción: • Más rápida para buscar por estado_civil • Ocupa menos memoria y disco. • Evitamos errores de comparación con faltas de ortografía, mayúsculas, caracteres especiales, etc.

13. ELEGIR ENTRE ENUM O NÚMERO • Enum para campos cuyas opciones no cambien en mucho tiempo. • Número para cualquier campo que tenga opciones que pueda cambiar regularmente.

14. SUBCONSULTAS

15. PARA ENTENDERNOS SELECT * FROM tabla1 WHERE campo1 IN (SELECT campo2 FROM tabla2) SELECT * FROM tabla1, (SELECT * FROM tabla2 WHERE campo2 = 3) as t2 WHERE t2.campo2 = tabla1.campo1

16. SUBCONSULTAS • Se deben evitar siempre que sea posible. • Hay que partirse la cabeza para buscar una alternativa. • Se pueden sustituir por left / right / inner join. • Igual que con los índices, entre usar subconsultas o joins hay un abismo.

17. PRUEBAS - PREMISAS Tablas CREATE TABLE `tabla1` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `campo1` int(11) NOT NULL,   PRIMARY KEY (`id`), KEY `campo1` (`campo1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `tabla2` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `campo2` int(11) NOT NULL,   PRIMARY KEY (`id`), KEY `campo2` (`campo2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 • Queremos sacar los registros de tabla1 cuyo campo1 esté en el campo2 de algún registro de tabla2 y además sea igual a 50 • 10000 registros en cada tabla con campo1 aleatorio entre 1 y 100 SENTENCIAS SELECT * FROM tabla1 WHERE campo1 IN (SELECT campo2 FROM tabla2 WHERE campo2 = 50) SELECT DISTINCT tabla1.* FROM tabla1 LEFT JOIN tabla2 ON campo1 = campo2 WHERE campo2 = 50

18. PRUEBAS - RESULTADOS • Registros recuperdados: 60. • Con subconsulta: 0,84821075 segundos. • Con LEFT JOIN: 0,00185125 segundos. • Reducción de tiempo 458 veces.

19. ORDER BY

20. ORDER BY • Cuidado con él, puede ralentizar mucho la consulta. • Estudiar si no es mejor ordenarlos fuera de MySQL. • Acompañado de un LIMIT normal, no suele haber problemas. • Si el orden no es importante, no lo pongas nunca.

21. Gracias