• Author / Uploaded
• Juan Carlos Alvarado Garzón

Citation preview

ANALISIS SCRIPTS ESTUDIANTES:

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 ;

●

Creación

índice

sobre

las

columnas

"creation_date"

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

●

Ejecutando la consulta:

y

"status"

llamado

●

SELECT

*

FROM

WHERE

country

FORCE

description

INDEX

(country_by_desc)

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;

●

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;

●

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;

●

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;

●

Ejecutando consulta con comando Explain

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

●

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 ;

●

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;

●

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;

●

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;

●

Consulta Modificada

SELECT creation_date, description FROM norme_item_factor ORDER BY creation_date;

●

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