Afinamiento Y Rendimiento De Bases De Datos

Cenfotec

Especialidades en Tecnología de Bases de Datos

MBD 302 Afinamiento y Rendimiento de Bases de Datos

Profesor: MBA Rodolfo Zamora

Trabajo Extraclase: Recomendaciones de afinamiento en scripts

Objetivo:

Elaborar un informe de al menos 20 sugerencias sobre que hacer y que no hacer con respecto al uso de scripts en la tecnología que les tocó exponer el proyecto en el siguiente formato:

En vez de hacer esto… Haga esto otro… Justificación

Select * … Select campo 1… Esto hace que sea más eficiente por…

Forma de evaluación:

Valor 5%

Fecha Entrega Agosto 1ero.

Alumnos:

Joy Muñoz

Franklin Cordoba

Mauricio Rodriguez 

1- Para insertar una gran cantidad de datos se debe realizar de la siguiente manera:

mysql> INSERT INTO gente VALUES

-> ('Tulano','2000-12-02'),

-> ('Pegano','1993-02-10');

No de esta:

INSERT INTO gente VALUES ('Tulano','2000-12-02');

INSERT INTO gente VALUES ('Pegano','1993-02-10');

Ya que mysql creara y revisara cada índice por cada insert separa por eso se deben insertar todos los datos para que al final se generen los índices.

2- Para insertar datos de forma más veloz se debe excluir:

mysql> INSERT INTO ciudad5 (poblacion,nombre) VALUES

-> (7000000, 'Madrid'),

-> (9000000, 'París'),

-> (3500000, 'Berlín');

Siempre que se pueda se debe quitar la parte después del nombre de la tabla ya que mysql revisara estos capos por cada insert aunque no sea necesario.

Forma correcta:

mysql> INSERT INTO ciudad5 VALUES

-> (7000000, 'Madrid'),

-> (9000000, 'París'),

-> (3500000, 'Berlín');

3- Evitar el uso de los select * from.... ya que el rendimiento de las consultas se verá afectado por la gran cantidad de datos recuperados por esto se recomienda solo consultar los datos necesarios.

SELECT * FROM ciudad3;

Debe ser así:

SELECT nombre, codigo FROM ciudad3;

4-Realizar las consultas de acuerdo (si se tienen) al orden de los índices para agilizarla.

(Hay un índice en el campo codigo)

SELECT nombre, poblacion, descrpcion, escudo,codigo, FROM ciudad3;

Forma correcta

SELECT codigo,nombre, poblacion, descripcion, escudo FROM ciudad3;

5- Utilizar la función limit para limitar el resulset cuando sea posible, así se evita un exceso de consumo de ancho de banda y duración de la consulta.

SELECT * FROM usuarios WHERE edad BETWEEN 18 AND 25 ORDER BY id DESC LIMIT 0,50

6- Utilizar cuando sea posible solo columnas índices dentro del where, esto hará más rápido los querys.

SELECT codigo,nombre, poblacion, descripcion, escudo FROM ciudad3 where codigo='lis', and poblacion='9999';

7- Utilizar la combinación apropiada de caracteres en cada tabla según lo recomienda mysql, ejemplo al utilizar SET NAMES 'utf8';. Se ha demostrado un deterioro de las consultas (tiempo en realizarlas) por eso si no se va a utilizar tildes, ñ, u otro carácter para UTF-8 por default además esto se puede aplicar por tabla o toda la base de datos y columnas.

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

SET NAMES 'utf8';

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

[create_specification [, create_specification] ...]

create_specification:

[DEFAULT] CHARACTER SET charset_name

| [DEFAULT] COLLATE collation_name

8- Para la el uso de punteros en MySQL estos se deben cerrar, ya que el motor si no cierran los deja en memoria por un largo tiempo con lo que en un futuro podría provocar problemas de rendimiento.

CREATE PROCEDURE curdemo()

BEGIN

DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;

DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;

OPEN cur1;

OPEN cur2;

REPEAT

FETCH cur1 INTO a, b;

FETCH cur2 INTO c;

.............CODIGO............

UNTIL done END REPEAT;

CLOSE cur1;

CLOSE cur2;

END

9- Utilizar el Engine adecuado para lo que se necesita, ya sea InomDB ,MyISAM etc ya que esto afectara de forma significativa el desempeño de nuestras Base de Datos.

ALTER TABLE [NOMBRE_BBDD].[NOMBRE_TABLA] engine=MyISAM; poner el engine deseado.

SELECT CONCAT(‘ALTER TABLE ‘,table_schema,‘.’,table_name,‘ engine=MyISAM;’)

FROM information_schema.TABLES WHERE ENGINE = ‘InnoDB’ AND table_schema = ‘[NOMBRE_BASE_DE_DATOS]‘

10- Se debe desfragmentar o borrar (caso extremo) la memoria cache de las consultas, ya que según la documentación esto puede afectar el rendimiento. Ejemplo:

SHOW STATUS LIKE 'Qcache%';

+-------------------------+------------------------------------+

| Variable_name | Value |

+-------------------------+------------------------------------+

| Qcache_free_blocks | 36 |

| Qcache_free_memory | 138488 |

| Qcache_hits | 79570 |

| Qcache_inserts | 27087 |

| Qcache_lowmem_prunes | 3114 |

| Qcache_not_cached | 22989 |

| Qcache_queries_in_cache | 415 |

| Qcache_total_blocks | 912 |

+-------------------------+------------------------------------+

Aunque parezca que la mayoría de la memoria está libre, deberíamos desfragmentarla, o mejor borrarla de vez en cuando para refrescarla.

• Para desfragmentar la caché el comando es:

o FLUSH QUERY CACHE;

• Para borrar la caché directamente, el comando es:

o RESET QUERY CACHE;

11- Optimizar las consultas para mejor uso del Query caché.

// query cache No trabaja

$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");

// query cache Si trabaja!

$today = date("Y-m-d");

$r = mysql_query("SELECT username FROM

...