optimización del rendimiento con mysql
DESCRIPTION
Charla impartida el 10 de Noviembre de 2011 en el libre software world congress. Algunos trucos para mejorar el rendimiento de nuestro servidor MySQL Vídeo de la charla: http://www.youtube.com/watch?v=_-fMNjebEX0TRANSCRIPT
Optimización del rendimiento con MySQL ( LSWC Noviembre 2011)
Víctor Jiménez Cerrada <[email protected]>@capitangolohttp://slideshare.net/capitangolo
Bienvenidos a "Optimización del rendimiento con MySQL".Soy Víctor Jiménez y seré vuestro ponente para hoy.
Trabajo en Warp Networks S.L. http://www.warp.esDonde nos dedicamos a varias cosas, entre ellas, formación y consultoría MySQL
MySQL Training partner since 2006
Sun Microsystem Training partner 2009-2010
Oracle partner since 2010
Hemos sido partners de MySQL desde 2006y en esta charla voy a compartir parte de la experiencia que hemos obtenido desde entonces. Vamos a hablar de optimización del rendimiento con MySQL
Agenda
1- Introducción. ¿Por qué optimizar?
2- Arquitectura de MySQL
3- Optimización de consultas
4- Ruegos y Preguntas
5"
…
15"
20"
1 - Introducción ¿Por Qué optimizar?
2- Arquitectura de MySQL
3- Optimización de consultas
4- Ruegos y Preguntas
1 - Introducción ¿Por Qué optimizar?
Desarrollamos nuestras apps web sin importarnos mucho el rendimiento.Cuando nuestra web tiene éxito y se convierte en la gallina de los huevos de oro, tenemos más solicitudes, y si no tenemos cuidado…
1 - Introducción ¿Por Qué optimizar?
… nuestro servidor se cuelga.A esto se le llama morir de éxito.
1 - Introducción ¿Por Qué optimizar?
Optimizar la aplicaciónOptimizar la base de datosEscalar
Pasos para no morir de éxito
1 - Introducción ¿Por Qué optimizar?
Hacer más con lo mismoObjetivo: Aumentar Consultas / segundo¿Cómo?: Menor tiempo de ejecución
Optimización
1 - Introducción ¿Por Qué optimizar?
0s 1s 2s 3s 4s 5s 6s 7s 8s 9s
Tenemos una consulta que tarda 2,5 segundos en ejecutarse si se ejecuta sola.Pero lo normal es que esa consulta se ejecute en varios procesos en paralelo.
1 - Introducción ¿Por Qué optimizar?
0s 1s 2s 3s 4s 5s 6s 7s 8s 9s
Tenemos una consulta que tarda 2,5 segundos en ejecutarse si se ejecuta sola.Pero lo normal es que esa consulta se ejecute en varios procesos en paralelo.
1 - Introducción ¿Por Qué optimizar?
0s 1s 2s 3s 4s 5s 6s 7s 8s 9s
Dado que la máquina tiene más recursos ocupados, es posible que tarde más en ejecutarse.En este caso, se ha ejecutado la consulta cinco veces en paralelo, cada una un segundo más tarde que la anterior.
1 - Introducción ¿Por Qué optimizar?
0s 1s 2s 3s 4s 5s 6s 7s 8s 9s
Dado que la máquina tiene más recursos ocupados, es posible que tarde más en ejecutarse.En este caso, se ha ejecutado la consulta cinco veces en paralelo, cada una un segundo más tarde que la anterior.
1 - Introducción ¿Por Qué optimizar?
0s 1s 2s 3s 4s 5s 6s 7s 8s 9s
C = 5
En un momento se están ejecutando 5 consultas a la vez.El servidor necesita poder soportar estos picos.
1 - Introducción ¿Por Qué optimizar?
0s 1s 2s 3s 4s 5s 6s 7s 8s 9s
C max = 1
Si optimizamos esas consultas para que tarden medio segundo...
1 - Introducción ¿Por Qué optimizar?
0s 1s 2s 3s 4s 5s 6s 7s 8s 9s
C max = 1
... sólo se ejecuta una consulta cada vez.Reduciendo el nivel de concurrencia que tiene que soportar el servidor.
1 - Introducción ¿Por Qué optimizar?
0s 1s 2s 3s 4s 5s 6s 7s 8s 9s
Dejando muchos más recursos libres.
1 - Introducción ¿Por Qué optimizar?
Más información
http://www.slideshare.net/capitangolo/no-mueras-de-exito
Dejando muchos más recursos libres.
2 - Arquitectura MySQL
1- Introducción. ¿Por qué optimizar?
3- Optimización de consultas
4- Ruegos y Preguntas
2 - Arquitectura MySQL
Arquitectura Cliente - Servidor
mysqldmysqld-nt
mysqlWorkbenchPHP My Admin…
myisamchkmyisampack
MySQL tiene una arquitectura cliente servidor.Aunque hay algunos programas 'ninjas' que acceden directamente a los datos.
2 - Arquitectura MySQL
Uso de Disco
/usr/local/mysql
test
world
table.frm
City.frm
Country.frm
CountryLanguage.frm
Hostname.pid
Hostname.err
En disco guarda tablas, logs y archivos de estado.
2 - Arquitectura MySQL
Uso de Memoria
Thread Cache
Buffers y Cachés
Tablas en memoria
Tablas temporales
Buffers de cliente
2 - Arquitectura MySQL
Uso de Memoria
Por InstanciaReservado en el arranque del servidor
Compartido para todos los usuarios
Query Cache
Key Cache
InnoDB Buffer Pool
Por Sesión
Reservado por cada conexión
Principalmente para gestionar los resultados
sort_buffer
join_buffer
read_buffer
Hay que tener cuidado al configurar las variables de sesión.20MB de sort_buffer x 100 conexiones = 2GB de memoria
2 - Arquitectura MySQL
API C
Arquitectura interna
Query
Cache
Intérprete
Optimizador
Executador
Motores
MyISAM InnoDB
Memory CSV
Subsistemas
Funciones base
Hilos
Buffers y cachés
Red
Logs
Acceso y Permisos
2 - Arquitectura MySQL
Gestionan la persistencia y recuperación de los datos
Configuración a nivel de Tabla
Oficiales:
MyISAM Motor por defecto en MySQL 5.0
InnoDB Motor por defecto en MySQL 5.5
Memory
Archive
Blackhole
CSV
…
De terceros:
solidDB Nitro
InfoBrigth PBXT
Motores de Almacenamiento
2 - Arquitectura MySQL2.1 - MyISAM
2 - Arquitectura MySQL » MyISAM
MyISAM
Características
Formatos de fila
Bloqueos
Key Cache
Consejos
Merge
2 - Arquitectura MySQL » MyISAM
Características de MyISAM (I)
/usr/local/mysql
world
City.frm
City.MYD
City.MYI
MyISAM guarda la información en dos archivos.MYD(ata) y .MYI(ndex)
2 - Arquitectura MySQL » MyISAM
Características de MyISAM (II)
No soporta transacciones
Bloqueos a nivel de tabla
Para un backup binario, copiar:
.frm
.MYD
.MYI
Un backup binario es portable
2 - Arquitectura MySQL » MyISAM
Características de MyISAM (III)
Compresión de índicesprefijos en índices de tipo texto
Fulltext
ALTER TABLE table ADD FULLTEXT(column1, column2)
SELECT […] WHERE MATCH (column1, column2) AGAINST ('TEXT');
Concurrent inserts
concurrent_insert = 0 | 1 | 2
R-Tree index
Datos Geoposicionados (GIS)
2 - Arquitectura MySQL » MyISAM
¿Cuándo usar MyISAM?
Aplicaciones de sólo lectura
Aplicaciones con poca concurrencia
Búsquedas de texto
Escaneos de tabla
Carga masiva de datos
Almacenamiento masivo de datos (datawarehousing)
2 - Arquitectura MySQL » MyISAM
¿Cuándo NO usar MyISAM?
Almacenamiento Confiable y a prueba de caídas
Recuperación automática y rápida (HA)
Alta concurrencia
Bloqueos prolongados
Integridad Referencial
Transacciones
2 - Arquitectura MySQL » MyISAM
Bloqueos MyISAM (I)
Bloqueo a nivel de tabla
Problemático cuando hay concurrencia
2 - Arquitectura MySQL » MyISAM
Bloqueos MyISAM (II) - Consultas lentas
0s 1s 2s 3s 4s 5s 6s 7s 8s 9s
Los select obtienen bloqueo de lectura compartido.El insert solicita un bloqueo exclusivo de escritura.Todos los demás selects posteriores esperan a que el insert libere el bloqueo.Hasta que el primer select termina, no se terminan de ejecutar las demás consultas.¡¡¡LLegamos a tener concurrencia 7!!!
2 - Arquitectura MySQL » MyISAM
Bloqueos MyISAM (II) - Consultas lentas
0s 1s 2s 3s 4s 5s 6s 7s 8s 9s
SELECT x FROM tabla …
SELECT x FROM tabla
INSERT INTO tabla …
SELECT x FROM tabla …
SELECT x FROM tabla …
SELECT x FROM tabla …
SELECT x FROM tabla …
SELECT …
Los select obtienen bloqueo de lectura compartido.El insert solicita un bloqueo exclusivo de escritura.Todos los demás selects posteriores esperan a que el insert libere el bloqueo.Hasta que el primer select termina, no se terminan de ejecutar las demás consultas.¡¡¡LLegamos a tener concurrencia 7!!!
2 - Arquitectura MySQL » MyISAM
Bloqueos MyISAM (II) - Consultas lentas
0s 1s 2s 3s 4s 5s 6s 7s 8s 9s
SELECT …
SELECT …
INSERT …
SELECT …
SELECT …
SELECT …
SELECT …
SELECT …
Si optimizamos el primer select, los bloqueos bajan exponencialmente.Concurrencia 4
2 - Arquitectura MySQL » MyISAM
Bloqueos MyISAM (III) - Soluciones
concurrent_insertsLas inserciones se realizan al final del archivo de datos.
SELECT HIGH PRIORITYINSERT LOW PRIORITYINSERT DELAYED
Se almacenan en un buffer, que va insertando cuando la tabla está libre.
2 - Arquitectura MySQL » MyISAM
Key Cache (I)
Caché para índices MyISAM
key_buffer_size > 0
En MyISAM existe una caché de claves
2 - Arquitectura MySQL » MyISAM
Key Cache (II) - Monitorización
key_reads / key_read_requests < 1%
key_blocks_not_flushed
key_blocks_used
key_blocks_unused
key_read_requests
key_reads
key_writes_requests
key_writes
Hay que configurar la caché de claves para que quepan todas las claves en memoria.
2 - Arquitectura MySQL2.2 - InnoDB
2 - Arquitectura MySQL » InnoDB
InnoDB
Características
Transacciones
InnoDB Buffers
Índices
Consejos
2 - Arquitectura MySQL » InnoDB
Características de InnoDB (I): Almacenamiento
/usr/local/mysql
test
table.frm
ibdata1
ib_logfile0
ib_logfile1
InnoDB guarda toda la información de todas las tablas en el tablespace.Archivo ibdata
2 - Arquitectura MySQL » InnoDB
Características de InnoDB (II): Files per table
/usr/local/mysql
test
table.frm
ibdata1
ib_logfile0
ib_logfile1
innodb_file_per_table
table.ibd
Si configuramos innodb_file_per_table tenemos un sub-espacio de tabla por cada tablaArchivo .ibdEl espacio de tabla sigue conteniendo información de cada tabla, es necesario.
2 - Arquitectura MySQL » InnoDB
Características de InnoDB (III)
Transacciones full ACID
Bloqueo a nivel de fila
El bloqueo se realiza en la PK
Buscando rangos, se bloquean también los huecos
Bloqueo a nivel de tabla
Cachea tanto índices como datos
2 - Arquitectura MySQL » InnoDB
Transacciones (I)
Atomic
Consistent
Isolated
Durable
START TRANSACTION
COMMIT
ROLLBACK
AutoCommit = 1
2 - Arquitectura MySQL » InnoDB
Transacciones (II)
Isolation Levels:
READ UNCOMMITED
READ COMMITED
REPEATABLE READ
SERIALIZABLE
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED
CONCURRENCIA
AISLAMIENTO
+
-+
-
Podemos configurar el Nivel de aislamiento para mejorar el rendimiento si no necesitamos tanta integridad en los datos.
2 - Arquitectura MySQL » InnoDB
InnoDB Buffers (I)
LOG BUFFER
BUFFER POOL
MYSQL SERVER
ibdataib_logfiles
Buffer Pool
Caché de datos e índices
Log Buffer
Log de transacciones
logfiles
Log de transacciones
Redo log
ibdata
Diccionario de datos
Undo log
commit& checkpoints
checkpoints
2 - Arquitectura MySQL » InnoDB
InnoDB Buffers (II): Configuración
innodb_flush_log_at_trx_commitControla cómo el commit dispara el flush del log a disco
innodb_buffer_pool_size
80% de la memoria
Cuanto más grande, más datos se cachean
innodb_log_buffer_size
Un mayor tamaño permite que las transacciones grandes no tengan que escribir a disco.
innodb_log_file_size
Un mayor tamaño de logfile:
reduce el tiempo entre checkpoints
aumenta el tiempo de recuperación
2 - Arquitectura MySQL » InnoDB
Índices InnoDB
PK
PKPKPK
PK
Index
IndexIndexIndex
Index
En InnoDB, la clave primaria direcciona directamente a la tabla.El resto de claves, direccionan a la clave primaria.El resto de claves también incluyen la clave primaria.
2 - Arquitectura MySQL » InnoDB
Consejos (I)
Usar claves primarias pequeñas (Enteros)
Cargar datos ordenados por clave primaria
Indexar prefijos (No hay compresión de índices)
2 - Arquitectura MySQL » InnoDB
Consejos (II)
innodb_additional_mem_pool_sizeinnodb_autoextend_incrementinnodb_thread_concurrency
SHOW ENGINE INNODB STATUS
3 - Optimización de consultas
1- Introducción. ¿Por qué optimizar?
2- Arquitectura de MySQL
4- Ruegos y Preguntas
3 - Optimización de consultas3.1 - Query Cache
MySQL server
Query Cache
Parse
Optimization
Execution
MyISAM InnoDB Memory
Tablas
3 - Optimización de consultas » Query Cache
Funcionamiento de la Query Cache (I)
Si activamos la Query Cache,la primera vez que ejecutemos la consulta seguirá los mismos pasos.Con un paso adicional, a la vez que se devuelven los datos al usuario, se guarda una copia en la caché de consultas.
MySQL server
Query Cache
Parse
Optimization
Execution
MyISAM InnoDB Memory
Tablas
3 - Optimización de consultas » Query Cache
Funcionamiento de la Query Cache (I)
SELECT
result
Si activamos la Query Cache,la primera vez que ejecutemos la consulta seguirá los mismos pasos.Con un paso adicional, a la vez que se devuelven los datos al usuario, se guarda una copia en la caché de consultas.
MySQL server
Query Cache
Parse
Optimization
Execution
MyISAM InnoDB Memory
Tablas
3 - Optimización de consultas » Query Cache
Funcionamiento de la Query Cache (I)
SELECT result
SELECT
result
Si activamos la Query Cache,la primera vez que ejecutemos la consulta seguirá los mismos pasos.Con un paso adicional, a la vez que se devuelven los datos al usuario, se guarda una copia en la caché de consultas.
3 - Optimización de consultas » Query Cache
MySQL server
Funcionamiento de la Query Cache (II)
SELECT
result
Tablas
Query Cache
Parse
Optimization
Execution
MyISAM InnoDB Memory
SELECT result
La próxima vez que se ejecute la misma consulta,se obtendrán los datos diréctamente de la cache.Lo que es un proceso casi instantáneo.
3 - Optimización de consultas » Query Cache
MySQL server
Funcionamiento de la Query Cache (III)
UPDATE
Query Cache
Parse
Optimization
Execution
MyISAM InnoDB Memory
Tablas
¿Estos datos están siempre actualizados?Sí, porque si se modifican los datos subyacentes, la caché se limpia.
3 - Optimización de consultas » Query Cache
Configurar la Query Cache
query_cache_type0 (OFF)1 (ON) - SELECT SLQ_NO_CACHE2 (DEMAND) - SELECT SQL_CACHE
query_cache_sizequery_cache_limitquery_cache_min_res_unit
3 - Optimización de consultas » Query Cache
Monitorizar la Query Cache (I)
SET GLOBAL query_cache_size = 4 * 1024 * 1024;SHOW GLOBAL STATUS LIKE 'qcache%';+-------------------------+---------+| Variable_name | Value |+-------------------------+---------+| Qcache_free_blocks | 1 || Qcache_free_memory | 3555808 || Qcache_hits | 460 || Qcache_inserts | 173 || Qcache_lowmem_prunes | 0 || Qcache_not_cached | 37 || Qcache_queries_in_cache | 173 || Qcache_total_blocks | 366 |+-------------------------+---------+
3 - Optimización de consultas » Query Cache
Monitorizar la Query Cache (II)
SET GLOBAL query_cache_size = 4 * 1024 * 1024;SHOW GLOBAL STATUS LIKE 'qcache%';+-------------------------+---------+| Variable_name | Value |+-------------------------+---------+| Qcache_free_blocks | 1 || Qcache_free_memory | 3555808 || Qcache_hits | 460 || Qcache_inserts | 173 || Qcache_lowmem_prunes | 0 || Qcache_not_cached | 37 || Qcache_queries_in_cache | 173 || Qcache_total_blocks | 366 |+-------------------------+---------+
3 - Optimización de consultas » Query Cache
Monitorizar la Query Cache (III)
Uso = Qcache_hits / (Qcache_hits + COM_select)
SHOW GLOBAL STATUS LIKE 'Qcache_hits';+---------------+-------+| Variable_name | Value |+---------------+-------+| Qcache_hits | 460 |+---------------+-------+SHOW GLOBAL STATUS LIKE 'COM_select';+---------------+-------+| Variable_name | Value |+---------------+-------+| Com_select | 334 |+---------------+-------+
3 - Optimización de consultas3.2 - mysqlslap
3 - Optimización de consultas » mysqlslap
mysqlslap
$ mysqlslap [opciones]
opciones:
-i-c--create-schema-q
otras opciones:
--user --password --host --port --socket
ejemplo:
$ mysqlslap -i 10 -c 2 --create-schema=test -q test.sql
mysqlslap permite realizar benchmarking básico de MySQL
3 - Optimización de consultas » mysqlslap
salida de mysqlslap
$ mysqlslap -i 10 -c 2 --create-schema=test -q test.sqlBenchmark Average number of seconds to run all queries: 51.776 seconds Minimum number of seconds to run all queries: 51.776 seconds Maximum number of seconds to run all queries: 51.776 seconds Number of clients running queries: 2 Average number of queries per client: 239
$ mysqlslap -i 10 -c 2 --create-schema=test -q test.sql --csv=test.csv$ cat test.csv,mixed,51.776,51.776,51.776,2,239
3 - Optimización de consultas » mysqlslap
Preparar una suite de test
Datossimilares a los de producción
Consultassimilares a las de producciónsuficiente cantidadclaves desordenadas
Servidor MySQLExclusivo para el test
ResultadosLeer con cuidado
3 - Optimización de consultas » mysqlslap
Preparar una suite de test: (I) Datos
> CREATE TABLE CityHuge LIKE City;> INSERT INTO CityHuge SELECT NULL, Name, CountryCode, District, Population FROM City;> INSERT INTO CityHuge SELECT NULL, Name, CountryCode, District, Population FROM CityHuge;> SELECT COUNT(*) FROM CityHuge;+----------+| COUNT(*) |+----------+| 1044224 |+----------+
Podemos crear tablas grandes insertando los datos de esa misma tabla en sí misma.
3 - Optimización de consultas » mysqlslap
Preparar una suite de test: (II) Consultas
Logsgeneralconsultas lentas
Podemos hacer suites de test a partir de consultas SQL
3 - Optimización de consultas » mysqlslap
Preparar una suite de test: (II) Consultas
SQL$ mysql world -N --batch -e \"SELECT CONCAT(
'SELECT ID FROM CityHuge WHERE CountryCode=\"', code, '\";') FROM Country" > test.sql
Logsgeneralconsultas lentas
Podemos hacer suites de test a partir de consultas SQL
3 - Optimización de consultas » mysqlslap
Preparar una suite de test: (III) Servidor MySQL
Exclusivo para el test
Configuración similar a producción
Limpiar cachés
Desactivar la Query Cache
3 - Optimización de consultas » mysqlslap
Preparar una suite de test: (IV) Resultados
Una diferencia de un 10% no es representativa
Test A : 5,45 s
Test B : 5,95 s
0
1,5
3
4,5
6
A B
3 - Optimización de consultas » mysqlslap
Preparar una suite de test: (V) Resultados
En producción se ejecutan consultas de varios tipos mezcladas:Test A (5 selects tabla 1) : 0,11 s
Test B (5 selects tabla 2) : 45 s
Test C (400 inserts en tablas 1 y 2) : 15 s
Test D ( A + B + C intercalados) : 153s
A
B
C
A + B + C
D
0 40 80 120 160
El test D tarda ¡¡3 veces más!!No hay que fijarse en el valor absoluto, en producción no tardará eso.Hay que fijarse en el porcentaje de mejora entre una opción y otra.
3 - Optimización de consultas3.3 - EXPLAIN
3 - Optimización de consultas » EXPLAIN
EXPLAIN
EXPLAIN SELECT id FROM City WHERE Population > 1000000\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: City type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4079 Extra: Using where
Explain informa de cómo va a ejecutar MySQL una consulta.
3 - Optimización de consultas » EXPLAIN
ALTER TABLE City ADD INDEX (Population);ALTER TABLE City ADD INDEX `pop_code`(Population, CountryCode);EXPLAIN SELECT Name FROM City WHERE CountryCode = 'ESP' AND Population > 1000000\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: City type: rangepossible_keys: Population,pop_code key: Population key_len: 4 ref: NULL rows: 238 Extra: Using where
3 - Optimización de consultas » EXPLAIN
ALTER TABLE City ADD INDEX (Population);ALTER TABLE City ADD INDEX `pop_code`(Population, CountryCode);EXPLAIN SELECT Name FROM City WHERE CountryCode = 'ESP' AND Population > 1000000\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: City type: rangepossible_keys: Population,pop_code key: Population key_len: 4 ref: NULL rows: 238 Extra: Using where
3 - Optimización de consultas » EXPLAIN
ALTER TABLE City ADD INDEX (Population);ALTER TABLE City ADD INDEX `pop_code`(Population, CountryCode);EXPLAIN SELECT Name FROM City WHERE CountryCode = 'ESP' AND Population > 1000000\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: City type: rangepossible_keys: Population,pop_code key: Population key_len: 4 ref: NULL rows: 238 Extra: Using where
3 - Optimización de consultas » EXPLAIN
ALTER TABLE City ADD INDEX (Population);ALTER TABLE City ADD INDEX `pop_code`(Population, CountryCode);EXPLAIN SELECT Name FROM City WHERE CountryCode = 'ESP' AND Population > 1000000\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: City type: rangepossible_keys: Population,pop_code key: Population key_len: 4 ref: NULL rows: 238 Extra: Using where
3 - Optimización de consultas » EXPLAIN
> EXPLAIN SELECT * FROM City WHERE ID = '345'\G******** 1. row *********** id: 1 select_type: SIMPLE table: City type: constpossible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra:
> EXPLAIN SELECT id FROM City WHERE ID = '345'\G******** 1. row *********** id: 1 select_type: SIMPLE table: City type: constpossible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: Using index
3 - Optimización de consultas » EXPLAIN
> EXPLAIN SELECT * FROM City WHERE ID = '345'\G******** 1. row *********** id: 1 select_type: SIMPLE table: City type: constpossible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra:
> EXPLAIN SELECT id FROM City WHERE ID = '345'\G******** 1. row *********** id: 1 select_type: SIMPLE table: City type: constpossible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: Using index
Seleccionar sólo las columnas necesarias permite que:A veces los datos puedan leerse sólo de los índices (memoria).Se lean menos datos de disco en el resto de casos.
3 - Optimización de consultas » EXPLAIN
> EXPLAIN SELECT COUNT(id) FROM City WHERE CountryCode = 'ESP'\G
> EXPLAIN SELECT COUNT(*) FROM City WHERE CountryCode = 'ESP'\G
0
37,5
75,0
112,5
150,0
1 2 4 8
COUNT(id) COUNT(*)
c i COUNT(id) COUNT(*)
1 5 29,974 3,837
2 5 31,862 6,009
4 5 63,253 10,087
8 5 125,721 19,412
En este caso, lo que ocurre es que COUNT(id) cuenta el número de filas cuyo id es != NULL.Para ello tiene que comprobar valor a valor.
3 - Optimización de consultas » EXPLAIN
*********** 1. row **********
id: 1
select_type: SIMPLE
table: City
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4079
Extra:
*********** 2. row **********
id: 1
select_type: SIMPLE
table: Country
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: world.City.CountryCode
rows: 1
Extra: Using where
EXPLAIN
SELECT City.Name, Country.Name
FROM City, Country
WHERE Country.Code = City.CountryCode
AND Country.Name="Spain"\G
La columna sobre la que se aplican las condiciones es importante.
3 - Optimización de consultas » EXPLAIN
*********** 1. row **********
id: 1
select_type: SIMPLE
table: City
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4079
Extra:
*********** 2. row **********
id: 1
select_type: SIMPLE
table: Country
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: world.City.CountryCode
rows: 1
Extra: Using where
EXPLAIN
SELECT City.Name, Country.Name
FROM City, Country
WHERE Country.Code = City.CountryCode
AND Country.Name="Spain"\G
La columna sobre la que se aplican las condiciones es importante.
3 - Optimización de consultas » EXPLAIN
*********** 1. row **********
id: 1
select_type: SIMPLE
table: Country
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: const
rows: 1
Extra:
*********** 2. row **********
id: 1
select_type: SIMPLE
table: City
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4079
Extra: Using where
EXPLAIN
SELECT City.Name, Country.Name
FROM City, Country
WHERE Country.Code = City.CountryCode
AND Country.Code="ESP"\G
La columna sobre la que se aplican las condiciones es importante.
3 - Optimización de consultas » EXPLAIN
*********** 1. row **********
id: 1
select_type: SIMPLE
table: Country
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: const
rows: 1
Extra:
*********** 2. row **********
id: 1
select_type: SIMPLE
table: City
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4079
Extra: Using where
EXPLAIN
SELECT City.Name, Country.Name
FROM City, Country
WHERE Country.Code = City.CountryCode
AND Country.Code="ESP"\G
La columna sobre la que se aplican las condiciones es importante.
3 - Optimización de consultas » EXPLAIN
Forzar Índices
USE INDEX (índices)IGNORE INDEX (índices)FORCE INDEX (índices)
3 - Optimización de consultas » EXPLAIN
STRAIGHT_JOIN
fuerza a que un JOIN se construya en el orden en el que se ha declarado
3 - Optimización de consultas » EXPLAIN
ANALYZE TABLE
Actualiza las estadísticas de una tabla
Ejecutado periódicamente ayuda al optimizador
3 - Optimización de consultas3.4 - Metodología para elegir el índice más óptimo
3 - Optimización de consultas » Metodología
Metodología para elegir el índice más óptimo
3 - Optimización de consultas » Metodología
Saber qué queremos hacer
Metodología para elegir el índice más óptimo
3 - Optimización de consultas » Metodología
Saber qué queremos hacer
Saber cuál es la manera más óptima
Metodología para elegir el índice más óptimo
3 - Optimización de consultas » Metodología
Saber qué queremos hacer
Saber cuál es la manera más óptima
Saber qué hace mysql y por qué
Metodología para elegir el índice más óptimo
3 - Optimización de consultas » Metodología
Saber qué queremos hacer
Saber cuál es la manera más óptima
Saber qué hace mysql y por qué
Metodología para elegir el índice más óptimo
3 - Optimización de consultas » Metodología
Saber qué queremos hacer
Saber cuál es la manera más óptima
Saber qué hace mysql y por qué
MySQL sólo utilizará un índice por tabla
Metodología para elegir el índice más óptimo
3 - Optimización de consultas » Metodología
Saber qué queremos hacer
Saber cuál es la manera más óptima
Saber qué hace mysql y por qué
MySQL sólo utilizará un índice por tabla
MySQL procesará un JOIN realizando el menor número de accesos
Metodología para elegir el índice más óptimo
3 - Optimización de consultas » Metodología
Saber qué queremos hacer
Saber cuál es la manera más óptima
Saber qué hace mysql y por qué
MySQL sólo utilizará un índice por tabla
MySQL procesará un JOIN realizando el menor número de accesos
empezando por la tabla con un índice más optimo
Metodología para elegir el índice más óptimo
3 - Optimización de consultas » Metodología
Saber qué queremos hacer
Saber cuál es la manera más óptima
Saber qué hace mysql y por qué
MySQL sólo utilizará un índice por tabla
MySQL procesará un JOIN realizando el menor número de accesos
empezando por la tabla con un índice más optimo
dictado por las condiciones WHERE
Metodología para elegir el índice más óptimo
3 - Optimización de consultas » Metodología
Saber qué queremos hacer
Saber cuál es la manera más óptima
Saber qué hace mysql y por qué
MySQL sólo utilizará un índice por tabla
MySQL procesará un JOIN realizando el menor número de accesos
empezando por la tabla con un índice más optimo
dictado por las condiciones WHERE
y utilizando claves para seleccionar los registros de otras tablas
Metodología para elegir el índice más óptimo
3 - Optimización de consultas » Metodología
EXPLAIN SELECT City.Name, Country.Name FROM City, Country WHERE Country.Code = City.CountryCode AND Country.Code = "ESP";
Ejemplo
3 - Optimización de consultas » Metodología
EXPLAIN SELECT City.Name, Country.Name FROM City, Country WHERE Country.Code = City.CountryCode AND Country.Code = "ESP";
Existen Formateadores OnLine:
http://www.dpriver.com/pp/sqlformat.htm
I Formatear
3 - Optimización de consultas » Metodología
SELECT City.Name,
Country.Name
FROM City,
Country
WHERE Country.Code = City.CountryCode
AND Country.Code = "ESP";
II Dibujar Tablas y Relaciones
3 - Optimización de consultas » Metodología
SELECT City.Name,
Country.Name
FROM City,
Country
WHERE Country.Code = City.CountryCode
AND Country.Code = "ESP";
II Dibujar Tablas y Relaciones
City Country
3 - Optimización de consultas » Metodología
SELECT City.Name,
Country.Name
FROM City,
Country
WHERE Country.Code = City.CountryCode
AND Country.Code = "ESP";
II Dibujar Tablas y Relaciones
City Country
3 - Optimización de consultas » Metodología
SELECT City.Name,
Country.Name
FROM City,
Country
WHERE Country.Code = City.CountryCode
AND Country.Code = "ESP";
II Dibujar Tablas y Relaciones
City CountryCountryCode
Code
City
3 - Optimización de consultas » Metodología
SELECT City.Name,
Country.Name
FROM City,
Country
WHERE Country.Code = City.CountryCode
AND Country.Code = "ESP";
III Añadir columnas de SELECT y WHERE
CountryCountryCode
Code
City
3 - Optimización de consultas » Metodología
SELECT City.Name,
Country.Name
FROM City,
Country
WHERE Country.Code = City.CountryCode
AND Country.Code = "ESP";
III Añadir columnas de SELECT y WHERE
CountryCountryCode
Code
Code = "ESP"
City
3 - Optimización de consultas » Metodología
SELECT City.Name,
Country.Name
FROM City,
Country
WHERE Country.Code = City.CountryCode
AND Country.Code = "ESP";
III Añadir columnas de SELECT y WHERE
CountryCountryCode
Code
Code = "ESP"
Name Name
City
3 - Optimización de consultas » Metodología
SELECT City.Name,
Country.Name
FROM City,
Country
WHERE Country.Code = City.CountryCode
AND Country.Code = "ESP";
IV Añadir Índices
CountryCountryCode
Code
Code = "ESP"
Name Name
PK
PK
City
3 - Optimización de consultas » Metodología
V Elegir el mejor plan de actuación
CountryCountryCode
Code
Code = "ESP"
Name Name
PK
PK
SELECT City.Name,
Country.Name
FROM City,
Country
WHERE Country.Code = City.CountryCode
AND Country.Code = "ESP";
City
3 - Optimización de consultas » Metodología
V Elegir el mejor plan de actuación
CountryCountryCode
Code
Code = "ESP"
Name Name
PK
PK
Plan A
City » Country
Recorrer secuencialmente City
Por cada registro en City (4000):
Buscar una equivalencia en Country usando PK
Seleccionar la fila sólo si Code = "ESP"
De las filas seleccionadas, leer:
City.Name (de la tabla)
Country.Name (de la tabla)
SELECT City.Name,
Country.Name
FROM City,
Country
WHERE Country.Code = City.CountryCode
AND Country.Code = "ESP";
City
3 - Optimización de consultas » Metodología
V Elegir el mejor plan de actuación
CountryCountryCode
Code
Code = "ESP"
Name Name
PK
PK
Plan A
City » Country
Recorrer secuencialmente City
Por cada registro en City (4000):
Buscar una equivalencia en Country usando PK
Seleccionar la fila sólo si Code = "ESP"
De las filas seleccionadas, leer:
City.Name (de la tabla)
Country.Name (de la tabla)
Plan B
Country » City
Seleccionar Code = "ESP" de Country usando PK
Por cada registro en Country (1):
Recorre secuencialmente City
Seleccionar la fila sólo si CountryCode = Code
De las filas seleccionadas, leer:
City.Name (de la tabla)
Country.Name (de la tabla)
SELECT City.Name,
Country.Name
FROM City,
Country
WHERE Country.Code = City.CountryCode
AND Country.Code = "ESP";
City
3 - Optimización de consultas » Metodología
V Elegir el mejor plan de actuación
CountryCountryCode
Code
Code = "ESP"
Name Name
PK
PK
Plan A
City » Country
Recorrer secuencialmente City
Por cada registro en City (4000):
Buscar una equivalencia en Country usando PK
Seleccionar la fila sólo si Code = "ESP"
De las filas seleccionadas, leer:
City.Name (de la tabla)
Country.Name (de la tabla)
Plan B
Country » City
Seleccionar Code = "ESP" de Country usando PK
Por cada registro en Country (1):
Recorre secuencialmente City
Seleccionar la fila sólo si CountryCode = Code
De las filas seleccionadas, leer:
City.Name (de la tabla)
Country.Name (de la tabla)
SELECT City.Name,
Country.Name
FROM City,
Country
WHERE Country.Code = City.CountryCode
AND Country.Code = "ESP";
City
3 - Optimización de consultas » Metodología
V Elegir el mejor plan de actuación
CountryCountryCode
Code
Code = "ESP"
Name Name
PK
PK
Plan A
City » Country
Recorrer secuencialmente City
Por cada registro en City (4000):
Buscar una equivalencia en Country usando PK
Seleccionar la fila sólo si Code = "ESP"
De las filas seleccionadas, leer:
City.Name (de la tabla)
Country.Name (de la tabla)
Plan B
Country » City
Seleccionar Code = "ESP" de Country usando PK
Por cada registro en Country (1):
Recorre secuencialmente City
Seleccionar la fila sólo si CountryCode = Code
De las filas seleccionadas, leer:
City.Name (de la tabla)
Country.Name (de la tabla)
SELECT City.Name,
Country.Name
FROM City,
Country
WHERE Country.Code = City.CountryCode
AND Country.Code = "ESP";
City
3 - Optimización de consultas » Metodología
V Cotejar con EXPLAIN
CountryCountryCode
Code
Code = "ESP"
Name Name
PK
PK
EXPLAIN
SELECT City.Name,
Country.Name
FROM City,
Country
WHERE Country.Code = City.CountryCode
AND Country.Code = "ESP";
*********** 1. row **********
table: Country
type: const
key: PRIMARY
rows: 1
Extra:
*********** 2. row **********
table: City
type: ALL
key: NULL
rows: 4079
Extra: Using where
City
3 - Optimización de consultas » Metodología
V Cotejar con EXPLAIN
CountryCountryCode
Code
Code = "ESP"
Name Name
PK
PK
Plan B
Country » City
Seleccionar Code = "ESP" de Country usando PK
Por cada registro en Country (1):
Recorre secuencialmente City
Seleccionar la fila sólo si CountryCode = Code
De las filas seleccionadas, leer:
City.Name (de la tabla)
Country.Name (de la tabla)
EXPLAIN
SELECT City.Name,
Country.Name
FROM City,
Country
WHERE Country.Code = City.CountryCode
AND Country.Code = "ESP";
*********** 1. row **********
table: Country
type: const
key: PRIMARY
rows: 1
Extra:
*********** 2. row **********
table: City
type: ALL
key: NULL
rows: 4079
Extra: Using where
3 - Optimización de consultas » Metodología
VI Optimizar y Adaptar
¿Mi consulta estodo lo óptima que podría?
Terminé de optimizarSí
No
¿Explain sigue miplan de ejecución?
Optimizar
No
Adaptar
Sí
3 - Optimización de consultas » Metodología
VII Optimizar el uso de índices
¿Qué columnas añadir a un índice?
Columnas JOIN
En la 2ª tabla deben ser la 1ª columna de una clave
Condiciones WHERE
Se deben escribir sobre columnas indexadas, o
Crear un índice que contenga al resto de columnas
Columnas SELECT
Seleccionar sólo las necesarias
Si todas son parte del índice mejora el rendimiento
Dividir la consulta en trozos más pequeños
Ayuda a saber qué parte tiene peor rendimiento
City
Country
CountryCode
Code
Code = "ESP"
Name
Name
PK
PK
SELECT City.Name,
Country.Name
FROM City,
Country
WHERE Country.Code = City.CountryCode
AND Country.Code = "ESP";
3 - Optimización de consultas » Metodología
VII Optimizar el uso de índices
¿Qué columnas añadir a un índice?
Columnas JOIN
En la 2ª tabla deben ser la 1ª columna de una clave
Condiciones WHERE
Se deben escribir sobre columnas indexadas, o
Crear un índice que contenga al resto de columnas
Columnas SELECT
Seleccionar sólo las necesarias
Si todas son parte del índice mejora el rendimiento
Dividir la consulta en trozos más pequeños
Ayuda a saber qué parte tiene peor rendimiento
City
Country
CountryCode
Code
Code = "ESP"
Name
Name
PK
PK
SELECT City.Name,
Country.Name
FROM City,
Country
WHERE Country.Code = City.CountryCode
AND Country.Code = "ESP";
3 - Optimización de consultas » Metodología
VIII Adaptar
Para que MySQL siga nuestro plan:Asegurarnos de que hemos creado los índices
ANALIZE TABLE
FORCE | IGNORE INDEX
Reescribir la consulta
WHERE
Condiciones JOIN
Ejercicio
SELECT SUM(salary) FROM salaries WHERE from_date BETWEEN '1999-01-01' AND '2000-01-01';
Ejercicio
SELECT SUM(salary) FROM salaries WHERE from_date BETWEEN '1999-01-01' AND '2000-01-01';
ALTER TABLE salaries ADD INDEX date_salary(from_date, salary);
Ejercicio
SELECT t.title, AVG(s.salary) salario_medio FROM titles t, salaries s WHERE t.emp_no = s.emp_no AND t.to_date > NOW() AND s.to_date > NOW() GROUP BY t.title ORDER BY salario_medio DESC;
Ejercicio
SELECT t.title, AVG(s.salary) salario_medio FROM titles t, salaries s WHERE t.emp_no = s.emp_no AND t.to_date > NOW() AND s.to_date > NOW() GROUP BY t.title ORDER BY salario_medio DESC;
ALTER TABLE titles ADD KEY curr_titles(to_date, emp_no, title);
Ejercicio
SELECT t.title, AVG(s.salary) salario_medio FROM titles t, salaries s WHERE t.emp_no = s.emp_no AND t.to_date > NOW() AND s.to_date > NOW() GROUP BY t.title ORDER BY salario_medio DESC;
ALTER TABLE titles ADD KEY to_date(to_date);PRIMARY (emp_no, title, from_date)
Ejercicio
SELECT e.first_name, e.last_name, s.salary FROM employees e, titles t, salaries s WHERE e.emp_no = t.emp_no AND e.emp_no = s.emp_no AND t.title = 'Manager' AND t.to_date > NOW() AND s.to_date > NOW();
3 - Optimización de consultas3.5 - Optimización con Subconsultas
3 - Optimización de consultas » Optimización con subconsultas
Consultas no correlativasEXPLAIN
SELECT Name FROM City
WHERE City.ID IN (
SELECT Capital FROM Country WHERE Country.Continent = 'Europe'
)\G************* 1. row *************
id: 1
select_type: PRIMARY
table: City
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4079
Extra: Using where
************** 2. row **************
id: 2
select_type: DEPENDENT SUBQUERY
table: Country
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 239
Extra: Using where
Las subconsultas no correlativas son aquellas que se pueden ejecutar de manera independiente a la consulta principal.
3 - Optimización de consultas » Optimización con subconsultas
Consultas no correlativasEXPLAIN
SELECT Name FROM City, (
SELECT Capital FROM Country WHERE Country.Continent = 'Europe'
) co
WHERE City.ID = co.Capital\G******* 1. row *******
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 46
Extra:
******* 2. row *******
id: 1
select_type: PRIMARY
table: City
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: co.Capital
rows: 1
Extra:
******* 3. row *******
id: 2
select_type: DERIVED
table: Country
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 239
...
Las subconsultas no correlativas se pueden situar como una tabla con la que hacer JOIN
3 - Optimización de consultas » Optimización con subconsultas
Consultas no correlativasEXPLAIN
SELECT Name
FROM City, Country
WHERE City.ID = Country.Capital
AND Country.Continent = 'Europe'\G************* 1. row *************
id: 1
select_type: SIMPLE
table: Country
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 239
Extra: Using where
************** 2. row **************
id: 1
select_type: SIMPLE
table: City
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: world.Country.Capital
rows: 1
Extra:
A veces se pueden reescribir como un JOIN
3 - Optimización de consultas » Optimización con subconsultas
SELECT Name, Population FROM Country, (SELECT MAX(Population) max_pop FROM Country) co WHERE Country.Population = co.max_pop;
Funciones de agregación
Y otras, como cuando se usan funciones de agregación, no.
3 - Optimización de consultas » Optimización con subconsultas
Condiciones OR
SELECT ci.Name FROM City ci, Country co, CountryLanguage cl WHERE ci.CountryCode = co.Code AND co.Code = cl.CountryCode AND (ci.Name LIKE 'Es%' OR cl.Language LIKE 'Es%');
Las condiciones OR sobre campos de varias tablas no permite que se puedan usar índices.
3 - Optimización de consultas » Optimización con subconsultas
Condiciones OR
ALTER TABLE City ADD INDEX (CountryCode), ADD INDEX (Name);ALTER TABLE CountryLanguage ADD INDEX (Language);SELECT ci.Name FROM City ci, ( SELECT ID FROM City ci, Country co, CountryLanguage cl WHERE ci.CountryCode = co.Code AND co.Code = cl.CountryCode AND cl.Language LIKE 'Es%' UNION SELECT ID FROM City ci WHERE ci.Name LIKE 'Es%' ) ci_ids WHERE ci.ID = ci_ids.ID;
Se puede reescribir como subconsultas para ayudar a MySQL.
3 - Optimización de consultas3.6 - Optimización usando cachés propias
3 - Optimización de consultas » Optimización usando cachés propias
Cachés propias
City
Name
Country
ContinentName
Si vamos a consultar muchas veces las ciudades por continente, nos puede interesar desnormalizar creando una tabla caché que relacione directamente las ciudades con sus continentes.
3 - Optimización de consultas » Optimización usando cachés propias
Cachés propias
City
Name
Country
ContinentName
City_Country
City_NameContinent
Country_Name
Si vamos a consultar muchas veces las ciudades por continente, nos puede interesar desnormalizar creando una tabla caché que relacione directamente las ciudades con sus continentes.
3 - Optimización de consultas » Optimización usando cachés propias
Cachés propias
City
Name
Country
ContinentName
City_Country
City_NameContinent
Country_Name
?
Si vamos a consultar muchas veces las ciudades por continente, nos puede interesar desnormalizar creando una tabla caché que relacione directamente las ciudades con sus continentes.
3 - Optimización de consultas3.7 - Optimización con MySQL 5.5
3 - Optimización de consultas » MySQL 5.5
MySQL 5.5
Source: http://datacharmer.blogspot.com/2009/04/mysql-54-performance-with-logging.html
3 - Optimización de consultas » MySQL 5.5
MySQL 5.5
x 1.59
3 - Optimización de consultas » MySQL 5.5
MySQL 5.5
Actualizar es un cambio "fácil" de llevar a cabo.
3 - Optimización de consultas3.7 - Optimización con MySQL 5.53.7.1 - Particionado
3 - Optimización de consultas » Particionado
Particionado
http://www.slideshare.net/datacharmer/mysql-partitions
3 - Optimización de consultas3.7 - Optimización con MySQL 5.53.7.2 - Triggers
3 - Optimización de consultas » Triggers
Triggers
Código que se ejecuta:
ANTES
DESPUÉS
de
INSERTAR
MODIFICAR
BORRAR
3 - Optimización de consultas » Triggers
Triggers
Hasta 6 triggers por tabla
Sintaxis:
CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount;
3 - Optimización de consultas » Triggers
Triggers
Auditoría
Forzar Integridad
Mantener cachés
3 - Optimización de consultas » Triggers
Coste
A: 15,16 1 Índice
B: 15,20 1 Índice + 1 Trigger
C: 14,87 1 Trigger
0
3,8
7,6
11,4
15,2
A B C
Un trigger apenas afecta al rendimiento en comparación a un índice.
4.- Ruegos y Preguntas
1- Introducción. ¿Por qué optimizar?
2- Arquitectura de MySQL
3- Optimización de consultas
¡Gracias!
!
Optimización del rendimiento con MySQL ( LSWC Noviembre 2011)
Víctor Jiménez Cerrada <[email protected]>@capitangolohttp://slideshare.net/capitangolo