optimización del rendimiento con mysql

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.

mailto:[email protected]


http://slideshare.net/capitangolo


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?





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…


… nuestro servidor se cuelga.A esto se le llama morir de éxito.


Optimizar la aplicaciónOptimizar la base de datosEscalar

Pasos para no morir de éxito


Hacer más con lo mismoObjetivo: Aumentar Consultas / segundo¿Cómo?: Menor tiempo de ejecución

Optimización


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.


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.


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.


0s 1s 2s 3s 4s 5s 6s 7s 8s 9s

C max = 1

Si optimizamos esas consultas para que tarden medio segundo...


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.


0s 1s 2s 3s 4s 5s 6s 7s 8s 9s

Dejando muchos más recursos libres.


Más información

http://www.slideshare.net/capitangolo/no-mueras-de-exito

Dejando muchos más recursos libres.

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.


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.


Uso de Memoria

Thread Cache

Buffers y Cachés

Tablas en memoria

Tablas temporales

Buffers de cliente


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


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


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


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)


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


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)


¿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)


¿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


Bloqueos MyISAM (I)

Bloqueo a nivel de tabla

Problemático cuando hay concurrencia


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!!!



0s 1s 2s 3s 4s 5s 6s 7s 8s 9s

SELECT x FROM tabla …

SELECT x FROM tabla

INSERT INTO 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!!!



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


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.


Key Cache (I)

Caché para índices MyISAM

key_buffer_size > 0

En MyISAM existe una caché de claves


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


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


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.


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


Transacciones (I)

Atomic

Consistent

Isolated

Durable

START TRANSACTION

COMMIT

ROLLBACK

AutoCommit = 1


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.


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


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


Í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.


Consejos (I)

Usar claves primarias pequeñas (Enteros)

Cargar datos ordenados por clave primaria

Indexar prefijos (No hay compresión de índices)


Consejos (II)

innodb_additional_mem_pool_sizeinnodb_autoextend_incrementinnodb_thread_concurrency

SHOW ENGINE INNODB STATUS

3 - Optimización de consultas




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


Tablas



SELECT

result


MySQL server

Query Cache

Parse

Optimization

Execution


Tablas



SELECT result

SELECT

result



MySQL server

Funcionamiento de la Query Cache (II)

SELECT

result

Tablas

Query Cache

Parse

Optimization

Execution


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.


MySQL server

Funcionamiento de la Query Cache (III)

UPDATE

Query Cache

Parse

Optimization

Execution


Tablas

¿Estos datos están siempre actualizados?Sí, porque si se modifican los datos subyacentes, la caché se limpia.


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


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 |+-------------------------+---------+


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 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


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


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


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.


Preparar una suite de test: (II) Consultas

Logsgeneralconsultas lentas

Podemos hacer suites de test a partir de consultas SQL


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


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


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


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.


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


> 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


> 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.


> 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.


*********** 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.


*********** 1. row **********

id: 1

select_type: SIMPLE

table: Country

type: const


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


AND Country.Code="ESP"\G

La columna sobre la que se aplican las condiciones es importante.


Forzar Índices

USE INDEX (índices)IGNORE INDEX (índices)FORCE INDEX (índices)


STRAIGHT_JOIN

fuerza a que un JOIN se construya en el orden en el que se ha declarado


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


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









dictado por las condiciones WHERE

y utilizando claves para seleccionar los registros de otras tablas



EXPLAIN SELECT City.Name, Country.Name FROM City, Country WHERE Country.Code = City.CountryCode AND Country.Code = "ESP";

Ejemplo


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




SELECT City.Name,

Country.Name

FROM City,

Country


AND Country.Code = "ESP";

II Dibujar Tablas y Relaciones


SELECT City.Name,

Country.Name

FROM City,

Country




City Country


SELECT City.Name,

Country.Name

FROM City,

Country




City CountryCountryCode

Code

City


SELECT City.Name,

Country.Name

FROM City,

Country



III Añadir columnas de SELECT y WHERE

CountryCountryCode

Code

City


SELECT City.Name,

Country.Name

FROM City,

Country




CountryCountryCode

Code

Code = "ESP"

City


SELECT City.Name,

Country.Name

FROM City,

Country




CountryCountryCode

Code

Code = "ESP"

Name Name

City


SELECT City.Name,

Country.Name

FROM City,

Country



IV Añadir Índices

CountryCountryCode

Code

Code = "ESP"

Name Name

PK

PK

City


V Elegir el mejor plan de actuación

CountryCountryCode

Code

Code = "ESP"

Name Name

PK

PK

SELECT City.Name,

Country.Name

FROM City,

Country



City



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



City



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"




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




SELECT City.Name,

Country.Name

FROM City,

Country



City


V Cotejar con EXPLAIN

CountryCountryCode

Code

Code = "ESP"

Name Name

PK

PK

EXPLAIN

SELECT City.Name,

Country.Name

FROM City,

Country



*********** 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


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




EXPLAIN

SELECT City.Name,

Country.Name

FROM City,

Country



*********** 1. row **********

table: Country

type: const

key: PRIMARY

rows: 1

Extra:

*********** 2. row **********

table: City

type: ALL

key: NULL

rows: 4079

Extra: Using where


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í


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




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


ALTER TABLE titles ADD KEY curr_titles(to_date, emp_no, title);

Ejercicio


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.



SELECT Name FROM City, (

SELECT Capital FROM Country WHERE Country.Continent = 'Europe'

) co

WHERE City.ID = co.Capital\G******* 1. row *******

id: 1


table: <derived2>

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 46

Extra:

******* 2. row *******

id: 1


table: City

type: eq_ref


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



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


key: PRIMARY

key_len: 4

ref: world.Country.Capital

rows: 1

Extra:

A veces se pueden reescribir como un JOIN


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.


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.


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.


Cachés propias

City

Name

Country

ContinentName

City_Country

City_NameContinent

Country_Name



Cachés propias

City

Name

Country

ContinentName

City_Country

City_NameContinent

Country_Name

?


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

http://datacharmer.blogspot.com/2009/04/mysql-54-performance-with-logging.html

http://datacharmer.blogspot.com/2009/04/mysql-54-performance-with-logging.html


MySQL 5.5

x 1.59


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


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;


Triggers

Auditoría

Forzar Integridad

Mantener cachés


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




¡Gracias!

!

Optimización del rendimiento con MySQL ( LSWC Noviembre 2011)

Víctor Jiménez Cerrada <[email protected]>@capitangolohttp://slideshare.net/capitangolo





optimización del rendimiento con mysql

Technology