Replicacion de Bases de datos en Mysql

ANALISIS SCRIPTS

ESTUDIANTES:

• GUSTAVO ADOLFO MARQUEZ PRIETO

GRUPO:

PIS000024

CARRERA:

Ingeniería de Software

ACTIVIDADES:

Para los siguientes ejercicios entregue los comandos SQL necesarios para realizar las operaciones y el análisis solicitado en un documento en Word donde explique lo encontrado.

1.       Para la tabla country en su base de datos cree un índice utilizando la columna "description" llamado country_by_desc. Cree un índice sobre las columnas "creation_date" y "status" llamado "country_by_ds". Ejecute las siguientes sentencias y utilice el comando EXPLAIN para explicar el comportamiento de los índices:

SELECT * FROM country FORCE INDEX (description)
WHERE description like 'CO'
ORDER BY description;
SELECT * FROM country
ORDER BY creation_date DESC, status ASC; 

Solución:

• Creación Base de datos

-- CREATE DATABASE nombre_bd;

CREATE DATABASE prueba;

• Selección base de datos

-- USE nombre_bd;

USE prueba;

• Creación tabla

-- CREATE TABLE nombre_tabla (atributos);

-- Siempre que sea posible se deben definir los atributos como NOT NULL, ya que facilita el uso de indices y evita la comprobacion en consultas en que se comprueba si cierto valor es nulo.

CREATE TABLE country

(id INT PRIMARY KEY NOT NULL,

description VARCHAR(500) NOT NULL,

creation_date DATE NOT NULL,

status VARCHAR(10) NOT NULL);

• Insertando valores

-- INSERT INTO nombre_tabla VALUES (valores_columnas);

INSERT INTO country

VALUES 

(1,'COLOMBIA',now(), 'active'),

(2,'PERU',now(), 'active'),

(3,'VENEZUELA', now(), 'active'),

(4,'ECUADOR', now(), 'active'),

(5,'MEXICO',now(), 'active'),

(6,'COSTA RICA',now(), 'active');

• Creación índice columna "description" llamado country_by_desc

-- Creacion indice columna "description" llamado country_by_desc

-- CREATE INDEX nombre_indice ON nombre_tabla (nombre_columna);

CREATE INDEX country_by_desc ON country (description);

• Consulta índices creados en la tabla country

-- Consulta de indices en la tabla country

SHOW INDEXES FROM country ;

[pic 1]

• Creación índice sobre las columnas "creation_date" y "status" llamado "country_by_ds"

-- CREATE INDEX nombre_indice ON nombre_tabla (nombre_columna);

CREATE INDEX country_by_ds ON country(creation_date, status);

• Consulta índices creados en la tabla country

-- Consulta de indices en la tabla country

SHOW INDEXES FROM country ;

[pic 2]

• Ejecutando la consulta:
• SELECT * FROM country FORCE INDEX (country_by_desc)
  WHERE description like 'CO'
  ORDER BY description con el comando explain;

-- EXPLAIN EXTENDED consulta sin indice creado

EXPLAIN EXTENDED SELECT * FROM country

WHERE description like 'CO'

ORDER BY description;

[pic 3]

• Como podemos ver la consulta en type muestra que tiene que recorrer toda la tabla (ALL) para obtener el resultado revisando las 5 filas (rows). Lo que la hace ineficiente.

-- EXPLAIN EXTENDED consulta con indice creado

EXPLAIN EXTENDED SELECT * FROM country FORCE INDEX (country_by_desc)

WHERE description like 'CO'

ORDER BY description;

[pic 4]

• Como podemos ver la consulta en la columna type especifica que se realizará una revisión por rango (range) y solo revisarán las filas necesarias, en este caso 1 fila (row) que corresponden a los registros que se encuentran en el rango solicitado. Este ejemplo sencillo nos muestra lo importante que puede ser esta herramienta para realizar consultas mucho más efectivas y que permitan optimizar el consumo de nuestro DBMS. Si tenemos en cuenta que consultas de este estilo se realizan concurrentemente y constantemente veremos que cada optimización realizada será de utilidad frente a la eficiencia de las aplicaciones que realicen consultas sobre las bases de datos.

• Ejecutando la consulta:
• SELECT * FROM country ORDER BY creation_date DESC, status ASC;

-- EXPLAIN EXTENDED consulta

SELECT * FROM country

ORDER BY creation_date DESC, status ASC;

[pic 5]

• Se puede apreciar que la consulta, al no contar con una condición WHERE que haga referencia al índice creado y que no se fuerce a la utilización de alguno, tendrá que recorrer las 5 filas de la tabla

2.       Optimice las siguientes consultas suponiendo que se realizan con frecuencia y que no existen índices creados sobre las tablas afectadas, a menos que se indique lo contrario.

• Ejecutando consulta sin Índice
• SELECT description FROM norme_item_factor
   WHERE consecutive BETWEEN 10 and 1000;

[pic 6]

• Ejecutando consulta con comando Explain

EXPLAIN EXTENDED SELECT description FROM norme_item_factor WHERE consecutive BETWEEN 10 and 1000;

[pic 7]

• Como podemos ver la consulta en type muestra que tiene que recorrer toda la tabla (ALL) para obtener el resultado revisando las 5 filas (rows). Lo que la hace ineficiente.

• Creando el índice idx_consecutive en la columna consecutive

-- CREATE INDEX nombre_indice ON nombre_tabla (nombre_columna);

CREATE INDEX idx_consecutive ON norme_item_factor (consecutive);

• Consulta de índices

SHOW INDEXES FROM norme_item_factor ;

[pic 8]

• Ejecutando consulta con comando Explain y forzando el indice

EXPLAIN EXTENDED SELECT description FROM norme_item_factor FORCE INDEX (idx_consecutive)

WHERE consecutive BETWEEN 10 and 1000;

[pic 9]

• Como podemos ver la consulta en la columna type especifica que se realizará una revisión por rango (range) y solo revisarán las filas necesarias, en este caso 4 filas (rows) que corresponden a los registros que se encuentran en el rango solicitado.

• Ejecutando consulta sin Índice
• SELECT creation_date, description FROM norme_item_factor
   ORDER BY creation_date,description;

[pic 10]

• Consultando tiempo de ejecución de que toma cada consulta

-- Modifica el perfil

SET profiling = 1;

-- Muestras las consultas ejecutadas y el tiempo de ejecucion en Milisegundos

SHOW profiles;

[pic 11]

• Consulta Modificada

SELECT creation_date, description FROM norme_item_factor

 ORDER BY creation_date;

[pic 12]

• La cláusula ORDER BY permite devolver una consulta ordenada sobre uno o más campos,  este proceso es muy costoso y hay que evitarlo en lo posible para evitar sobrecargas, debido a ello, se puede evidenciar que al dejar solo el ordenamiento por el campo “creation_date”, se redujo el tiempo de ejecución en milisegundo en mas del 40% del tiempo inicial