“Optimización de sentencias MySQL” jueves 26 de septiembre de 2013.
-
Upload
dimas-mejias -
Category
Documents
-
view
226 -
download
2
Transcript of “Optimización de sentencias MySQL” jueves 26 de septiembre de 2013.
![Page 1: “Optimización de sentencias MySQL” jueves 26 de septiembre de 2013.](https://reader036.fdocumento.com/reader036/viewer/2022062808/5665b49d1a28abb57c92a8f9/html5/thumbnails/1.jpg)
“Optimización de sentencias MySQL”
jueves 26 de septiembre de 2013
![Page 2: “Optimización de sentencias MySQL” jueves 26 de septiembre de 2013.](https://reader036.fdocumento.com/reader036/viewer/2022062808/5665b49d1a28abb57c92a8f9/html5/thumbnails/2.jpg)
Sobre mí
Nombre: Óscar / angro
Email: [email protected]
Desarrollador PHP, MySQL, Java, Android...
Co-fundador y programador de Friends of Azeroth.
Twitter: @angro
![Page 3: “Optimización de sentencias MySQL” jueves 26 de septiembre de 2013.](https://reader036.fdocumento.com/reader036/viewer/2022062808/5665b49d1a28abb57c92a8f9/html5/thumbnails/3.jpg)
MYSQL
![Page 4: “Optimización de sentencias MySQL” jueves 26 de septiembre de 2013.](https://reader036.fdocumento.com/reader036/viewer/2022062808/5665b49d1a28abb57c92a8f9/html5/thumbnails/4.jpg)
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.
![Page 5: “Optimización de sentencias MySQL” jueves 26 de septiembre de 2013.](https://reader036.fdocumento.com/reader036/viewer/2022062808/5665b49d1a28abb57c92a8f9/html5/thumbnails/5.jpg)
POTENCIAL
● Tablas con millones de registros.
● Joins de 3 tablas con millones de registros.
● Sentencias de 20 minutos reducidas a 20 segundos.
![Page 6: “Optimización de sentencias MySQL” jueves 26 de septiembre de 2013.](https://reader036.fdocumento.com/reader036/viewer/2022062808/5665b49d1a28abb57c92a8f9/html5/thumbnails/6.jpg)
ÍNDICES (INDEX)
![Page 7: “Optimización de sentencias MySQL” jueves 26 de septiembre de 2013.](https://reader036.fdocumento.com/reader036/viewer/2022062808/5665b49d1a28abb57c92a8f9/html5/thumbnails/7.jpg)
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.
![Page 8: “Optimización de sentencias MySQL” jueves 26 de septiembre de 2013.](https://reader036.fdocumento.com/reader036/viewer/2022062808/5665b49d1a28abb57c92a8f9/html5/thumbnails/8.jpg)
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.
![Page 9: “Optimización de sentencias MySQL” jueves 26 de septiembre de 2013.](https://reader036.fdocumento.com/reader036/viewer/2022062808/5665b49d1a28abb57c92a8f9/html5/thumbnails/9.jpg)
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.
![Page 10: “Optimización de sentencias MySQL” jueves 26 de septiembre de 2013.](https://reader036.fdocumento.com/reader036/viewer/2022062808/5665b49d1a28abb57c92a8f9/html5/thumbnails/10.jpg)
NÚMEROS / ENUMS
![Page 11: “Optimización de sentencias MySQL” jueves 26 de septiembre de 2013.](https://reader036.fdocumento.com/reader036/viewer/2022062808/5665b49d1a28abb57c92a8f9/html5/thumbnails/11.jpg)
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.
![Page 12: “Optimización de sentencias MySQL” jueves 26 de septiembre de 2013.](https://reader036.fdocumento.com/reader036/viewer/2022062808/5665b49d1a28abb57c92a8f9/html5/thumbnails/12.jpg)
EJEMPLO DE ELECCIÓN
Tenemos una tabla con un campo estado_civil. Puede ser:
1. Campo texto: “soltero”, “casado”, etc.2. 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.
![Page 13: “Optimización de sentencias MySQL” jueves 26 de septiembre de 2013.](https://reader036.fdocumento.com/reader036/viewer/2022062808/5665b49d1a28abb57c92a8f9/html5/thumbnails/13.jpg)
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.
![Page 14: “Optimización de sentencias MySQL” jueves 26 de septiembre de 2013.](https://reader036.fdocumento.com/reader036/viewer/2022062808/5665b49d1a28abb57c92a8f9/html5/thumbnails/14.jpg)
SUBCONSULTAS
![Page 15: “Optimización de sentencias MySQL” jueves 26 de septiembre de 2013.](https://reader036.fdocumento.com/reader036/viewer/2022062808/5665b49d1a28abb57c92a8f9/html5/thumbnails/15.jpg)
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
![Page 16: “Optimización de sentencias MySQL” jueves 26 de septiembre de 2013.](https://reader036.fdocumento.com/reader036/viewer/2022062808/5665b49d1a28abb57c92a8f9/html5/thumbnails/16.jpg)
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.
![Page 17: “Optimización de sentencias MySQL” jueves 26 de septiembre de 2013.](https://reader036.fdocumento.com/reader036/viewer/2022062808/5665b49d1a28abb57c92a8f9/html5/thumbnails/17.jpg)
PRUEBAS - PREMISASTablas
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
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
SENTENCIAS
![Page 18: “Optimización de sentencias MySQL” jueves 26 de septiembre de 2013.](https://reader036.fdocumento.com/reader036/viewer/2022062808/5665b49d1a28abb57c92a8f9/html5/thumbnails/18.jpg)
PRUEBAS - RESULTADOS
● Registros recuperdados: 60.● Con subconsulta: 0,84821075 segundos.● Con LEFT JOIN: 0,00185125 segundos.● Reducción de tiempo 458 veces.
![Page 19: “Optimización de sentencias MySQL” jueves 26 de septiembre de 2013.](https://reader036.fdocumento.com/reader036/viewer/2022062808/5665b49d1a28abb57c92a8f9/html5/thumbnails/19.jpg)
ORDER BY
![Page 20: “Optimización de sentencias MySQL” jueves 26 de septiembre de 2013.](https://reader036.fdocumento.com/reader036/viewer/2022062808/5665b49d1a28abb57c92a8f9/html5/thumbnails/20.jpg)
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.
![Page 21: “Optimización de sentencias MySQL” jueves 26 de septiembre de 2013.](https://reader036.fdocumento.com/reader036/viewer/2022062808/5665b49d1a28abb57c92a8f9/html5/thumbnails/21.jpg)
Gracias