Manual SQL Avanzado

Lenguaje Sql con MySQL avanzado

Parte I. Vistas

PARTE I. VISTAS Tema 1. Vistas 1.1 Antes de comenzar.................................................................................2 1.2 Ventajas de su utilización......................................................................6 1.3 Creación. Diccionario de Datos.............................................................8 1.4 Modificación........................................................................................14 1.5 Borrado ................................................................................................14 1.6 Operaciones sobre vistas. Restricciones..............................................15 1.7 Ejercicios resueltos ..............................................................................19

Página 1


Parte I. Vistas

TEMA 1. Vistas Alberto Carrera Martín

1.1 Antes de comenzar a) COMPROBACIÓN DEL ESTADO DEL SERVIDOR Antes de empezar a trabajar asegúrate que la base de datos está arrancada. Si utilizas Windows XP podrás comprobarlo mediante: Botón Inicio / Panel de Control / Rendimiento y Mantenimiento / Herramientas Administrativas / Servicios

Ilustración 1. Servicio de MySQL

En este caso se encuentra iniciado, si no fuera así lo podrías arrancar haciendo clic con el botón derecho sobre el servicio y eligiendo la opción de Iniciar. También lo puedes saber de otras formas y de manera gráfica ejecutando el monitor de sistema de MySQL que tras lanzarlo: Botón Inicio / Todos los programas / MySQL / MySQL System Tray Monitor

aparecerá con elélsiguiente icono en la barraque de estado Al hacer clic“corriendo” con el botóno derecho sobre se puede comprobar la base . de datos un está ejecutándose tal y como se aprecia en la primera línea de siguiente ilustración 2. El rectángulo verde del icono ya hace presagiar tal situación; si la figura que nos hubiéramos encontrado dentro del icono hubiera sido un cuadrado rojo, entonces la base de datos estaría parada y por tanto deberíamos arrancarla (opción Start Instance en ilustración 3 siguiente)

Página 2


Parte I. Vistas

Ilustración 2. Monitor de MySQL. Servicio Ilustración 3. Monitor de MySQL. Servicio detenido ejecutándose

b) CREACIÓN DE LA BASE DE DATOS DE EJEMPLO La realizaremos de dos maneras de las varias posibles existentes. Lee las dos y después pon en práctica la que desees (o puedes experimentar con las dos) b1) Creación de la base de datos ejemplo utilizando una ventana de consola de

windows

Los archivos binarios del servidor se encuentran instalados en la carpeta: C:\Archivos de programa\MySQL\MySQL Server 5.0\bin

Nota: La unidad de disco anterior (C:) y la ruta de carpetas puede cambiar en función de donde se realizara la instalación. Dentro de la carpeta anterior crea a su vez una carpeta denominada bases y copia allí el script tablas1.sql que encontrarás en la sección de materiales del curso. Una vez hecho, arranca una ventana de consola ( Botón Inicio / Todos los programas / Accesorios / Símbolo de sistema), sitúate en la carpeta anterior (C:\Archivos de programa\MySQL\MySQL Server 5.0\bin>) y ejecuta el comando siguiente que te permitirá conectarte a la base de datos como usuario root y al mismo tiempo ejecutar las instrucciones que contiene el script tablas1.sql copiado a la carpeta bases anterior: mysql -u root -p < bases\tablas1.sql La Instrucción anterior es equivalente a: mysql -u root -p < "C:\Archivos de programa\MySQL\MySQL Server 5.0\bin\bases\tablas1.sql"

Tras el mensaje de introducción de la clave del usuario root Enter password: ****** El script tablas1.sql crea, además de las tablas que puedes ver al final de este apartado, un usuario1 de clave usuario1 con todos los permisos de trabajo sobre la base de datos

Página 3


Parte I. Vistas

practica1 que se acaba de crear. Se puede comprobar si en la ventana de consola abres una conexión con el usuario1: D: \ Ar chi vos de pr ogr ama\ MySQL\ MySQL Ser ver 5. 0\ bi n>mysql - u usuar i o1 - p

Ent er password: *** *** ** Wel come t o t he MySQL moni t or . Commands end wi t h ; or \ g. Your MySQL connect i on i d i s 41 t o ser ver ver si on: 5. 0. 20a- nt Type ' hel p; ' or ' \ h' f or hel p. Type ' \ c' t o cl ear t he buf f er . mysql > SHOW DATABASES; +-- -- -- -- -- -- -- -- -- -- + | Dat abase | +-- -- -- -- -- -- -- -- -- -- + | i nf or mat i on_sche ma | | mysql | | pr acti ca1 | | te st | +-- -- -- -- -- -- -- -- -- -- + 4 r ows i n set ( 0. 77 sec) mysql > USE pr act i ca1 Dat abase changed mysql > SHOW TABLES; +-- -- -- -- -- -- -- -- -- -- -+ | Tabl es_i n_pr acti ca1 | +-- -- -- -- -- -- -- -- -- -- -+ | cent r os | | depar t ament os | | empl eados | +-- -- -- -- -- -- -- -- -- -- -+ 3 r ows i n set ( 0. 01 sec)

b2) Creación de la base de datos ejemplo utilizando una herramienta cliente Otra forma más atractiva de realizar la tarea anterior es utilizar la herramienta gráfica de MySQL Query Browser (para este

curso se ha utilizado la versión 1.1.2.0 que se encuentra en el fichero mysqlquery-browser-1.1.20-win.msi). Su instalación es muy sencilla. Tras lanzarla desde el monitor de MySQL visto anteriormente en el apartado 1.1.a (ilustraciones 2 y 3) o también mediante Botón Inicio / Todos los programas /MySQL / MySQL Query Browser aparecerá la la pantalla inicial de la ilustración 4 de derecha. Tras

introducir el usuario/clave de root, pulsa OK para avanzar a la siguiente pantalla. Ilustración 4. MySQL Query Browser

Página 4


Parte I. Vistas Observa que se ha introducido el usuario y la clave del Administrador root pero no la base de datos a la que se conecta (Default Schema) por lo que tras pulsar el botón OK puede aparecer la siguiente ilustración 5 de la izquierda pantalla para recordárnoslo:

Ilustración 5. MySQL Query Browser

Pulsaremos el botón Ignore para continuar y entrar en la herramienta cliente. Una vez en ella abriremos (opción File / Open Script…del menú) el script tablas1.sql que podrás encontrar en la sección de materiales del curso (ver ilustración 6)

Ilustración 6. Cargando un script en MySQL Query Browser

A continuación puedes ejecutar el script mediante el botón Execute (parte superior derecha como se puede apreciar en la ilustración 6 anterior) con lo que habrás creado el

Página 5


Parte I. Vistas

usuario usuario1 de clave usuario1 y la base de datos práctica1 con las 3 tablas que la componen (haciendo doble clic en cada una de ellas podrás ver sus registros)

Ilustración 7. Base de datos practica1 creada

1.2 Ventajas de su utilización Nota: Para probar los ejemplos siguientes puedes hacerlo conectándote a la base de datos (default schema) practica1 como usuario usuario1 de clave usuario1 mediante las dos formas vistas en el apartado anterior. Por su sencillez y claridad utilizaremos la segunda de ellas, la herramienta gráfica MySQL Query Browser. Después de introducir cada sentencia recuerda pulsar el botón Execute. Antes de definir lo que es una vista vamos a ver las ventajas que nos aportan. - Seguridad y confidencialidad: Si un empleado de la centralita telefónica necesita para su trabajo conocer exclusivamente el nombre y extensión telefónica de todos sus Compañeros, el resto de columnas con datos privados como el número de hijos, sueldo… de la tabla debe ser “invisible” para él. Por tanto el Administrador de la base de datos creará una “visión parcial” (vista1a en el ejemplo) de la tabla:

Instrucción 1

y sólo le permitirá consultar dicha vista y no la tabla srcinal:

Página 6


Parte I. Vistas

Instrucción 2

Desde el punto de vista de la administración, es más fácil administrar permisos sobre una vista que asignar privilegios a las columnas de las tablas. - Facilidad de uso para consultas “complejas”. Si un Jefe desea consultar los Centros junto con sus Departamentos y nombres de los Directores:

Ilustración 8. Centros, Departamentos y Directores

en lugar de tener que realizar la siguiente consulta:

Instrucción 3

el Administrador puede preparar la siguiente vista:

Página 7


Parte I. Vistas

Instrucción 4

para que el Jefe sólo tenga que consultar:

Instrucción 5

y con ello obtendrá las 8 filas y 5 columnas anteriores con toda la información sobre centros, departamentos y sus directores. Nota: Si no estás acostumbrado a la sintaxis anterior en la que se utiliza la cláusula JOIN … ON para unir tablas puedes utilizar esta otra:

Instrucción 6

Hay que advertir que aunque el aspecto de una vista sea el de una tabla, una vista no es nada más que una instrucción SELECT del SQL almacenada con un nombre (vista1a, vista1b…). Por tanto no contiene datos (filas) permanentemente como una tabla y de esta manera no hay ficheros asociados a ellas. De todas formas, como se puede comprobar con la última instrucción lanzada, la forma de manejar vistas y tablas es muy similar y podemos estar consultando un srcen de datos sin saber si éste es una tabla o una vista.

1.3 Creación. Diccionario de Datos Pueden utilizarse las vistas a partir de la versión de MySql 5.0.1. Para poder crear vistas se requiere el privilegio CREATE VIEW así como los privilegios para poder seleccionar las columnas que forman parte de la vista. Sintaxis básica: CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = {nombreusuario} | CURRENT_USER}] [SQL SECURITY {DEFINER | INVOKER}] VIEW nombre_vista [(lista_columnas)] AS sentencia_select [WITH [CASCADED | LOCAL] CHECK OPTION]

Página 8


Parte I. Vistas

Recuerda que las palabras reservadas que aparecen entre corchetes [] son opcionales. La | indica elegir una opción entre las posibles alternativas. La sentencia anterior crea una nueva vista o reemplaza una existente (cláusula OR REPLACE) si esta ya existiera. El creador de la vista, en este caso usuario1, debe estar autorizado para poder crear vistas, es decir, poseer el privilegio CREATE VIEW. Si observas las líneas 4 y 5 del script tablas1.sql con el que estás trabajando y que aparecen a continuación, el Administrador root se lo ha concedido, tanto este privilegio como todos los posibles:

Instrucción 7

El resto de cláusulas de creación de vistas: - ALGORITHM permite elegir entre dos modos de ejecución: MERGE o

TEMPTABLE. Si se utiliza MERGE, las consultas que se ejecutan sobre la vista se combinan con la consulta de definición de la vista para crear una nueva consulta. Si se usa TEMPTABLE, se utiliza la definición de la vista para crear una tabla temporal donde se almacenan los datos (y por tanto las consultas se ejecutan sobre esta tabla temporal). Es una cláusula opcional. Si no se especifica o se define como UNDEFINED, MySQL analizará la mejor opción posible, generalmente MERGE pues el inconveniente que presenta la opción TEMPTABLE es que las tablas temporales sobre las que trabaja no tienen índices y por tanto el rendimiento de las consultas puede ser inferior si se utiliza esta opción. - DEFINER (a partir de MySQL 5.1.2) especifica el propietario de la vista. Por defecto es el usuario actual (CURRENT_USER). - SQL SECURITY (a partir de MySQL 5.1.2) especifica el modo de interactuar con la vista. Se puede precisar que las consultas que se efectúen sobre la vista se traten con los privilegios del propietario de la vista (DEFINER) o con los privilegios de quien ejecute las consultas sobre la vista (INVOKER); la primera de las dos anteriores es la opción por defecto. - nombre_vista es el nombre que se da a la vista, no debe ser el mismo que el utilizado

para una tabla. Si se desea crear la vista en otra base de datos distinta de la actual entonces deberá especificarse la base de datos mediante la siguiente notación: base_datos.nombre_vista. - lista_columnas son los nombres de columnas que va a contener la vista. Si no se ponen, se entenderá que serán todas las columnas que devuelva la sentencia SELECT de consulta; esta última determinará las columnas y tablas que aparecerán en la vista; si se indica la lista de columnas deberá hacerse separada por comas y el número de nombres debe ser el mismo que el número de columnas devuelto por la sentencia SELECT. Al igual que las tablas, las vistas no pueden tener nombres de columnas duplicados. Las columnas devueltas por la sentencia SELECT pueden ser simples referencias a columnas de la tabla, pero también pueden ser expresiones conteniendo funciones, constantes, operadores, etc.

Página 9


Parte I. Vistas

- WITH CHECK OPTION añade una opción de seguridad a la hora de realizar operaciones de manipulación de datos utilizando vistas (en el apartado 1.6 veremos por su importancia algún ejemplo). Utilizando LOCAL los datos que se inserten a través de la vista deben respetar solamente las restricciones de la vista. Si se especifica CASCADED en el momento de la creación de la vista entonces deben respetarse las restricciones de la vista fuente o “padre” a partir de la cual fue creada. La opción por defecto es CASCADED. Ejemplos de utilización. Notas: - El nombre que le damos a las vistas en este tema, para su fácil localización, es vista1a, vista1b... Realmente debemos utilizar nombres mucho más significativos que indiquen qué información va a presentar (como p.ej. los utilizados en los ejemplos del apartado 6). - Todos los ejemplos se ejecutan desde el browser teniendo seleccionada la base de datos practica1 (ver ilustración 7 anterior). De esta manera se omite la selección de la misma (sentencia USE practica1) o preceder al nombre de cada tabla o vista el nombre de la base de datos ( SELECT * FROM practica1.vista1a;). Ejemplo1. Nombre y presupuesto de todos los departamentos del centro número 10:

Instrucción 8

Si consultamos la vista anterior:

Instrucción 9

Como comentábamos anteriormente, a efectos de consulta y manipulación, una vista puede ser considerada como una tabla. A continuación seleccionamos de la vista anterior aquellos departamentos que superan los 5000000 de presupuesto:

Página 10


Parte I. Vistas

Instrucción 10

Posiblemente una vez creada no puedas ver la vista en el panel del esquema a no ser que cierres el programa cliente gráfico y vuelvas a entrar. Para evitar esto, selecciona con el botón derecho la base de datos practica1 y elige la opción de refrescar ( Refresh) tal y como aparece en la ilustración 9:

Ilustración 9. Refrescando la base de datos practica1

Ejemplo 2. Nombre, salario y número de departamento de los empleados que superan los 400 de salario:

Instrucción 11

Ejemplo 3. A quién debemos felicitar el 12 de octubre:

Instrucción 12

Ejemplo 4 Nombre, número de departamento y antigüedad en años en la empresa de los empleados (recordad que la columna fecin contiene la fecha de ingreso en la empresa):

Página 11


Parte I. Vistas

Instrucción 13

Ejemplo 5 Total de empleados y media de sus salarios

Instrucción 14

¿Dónde se guarda la definición de la vista? Si miras el panel Schemata (ilustraciones 9 y 10) en la parte superior derecha del browser encontrarás información sobre las base de datos. Una de ellas, la information_schema, contiene datos acerca de los datos, es decir información sobre todos los objetos de las bases de datos existentes en el servidor, es lo que se conoce como DICCIONARIO DE DATOS o CATALOGO del SISTEMA. La información que contienen sus tablas es de sólo lectura; realmente no se trata de tablas sino de vistas. Haciendo doble clic en cada una de sus entradas verás toda la información relativa a la misma. Si lo haces sobre el objeto VIEWS podrás ver todas las características de las vistas existentes en las bases de datos del servidor MySQL (ilustración 11 siguiente):

Ilustración 10. Schemata

Página 12


Parte I. Vistas

Ilustración 11. Vista VIEWS

Si tuvieras acceso a varias bases de datos y sólo quisieras saber las vistas de que dispones en la base de datos practica1, la misma información anterior la podrías obtener:

Instrucción 15

Viendo las filas resultantes puedes comprobar que la columna TABLE_NAME contiene el nombre de la vista y que la columna VIEW_DEFINITION la sentencia SELECT que la construye. Por tanto si intentaras ver esta última columna solamente y para una vista en concreto:

Instrucción 16

Lo que ocurre con la anterior consulta es que puedes encontrar una expresión un poco confusa a la hora de mostrar la sentencia SELECT y además en caso de que ésta sea larga no verse por completo y aparecer partida. Por eso quizás sea mejor seleccionar la vista deseada del diccionario de datos, en concreto su columna VIEW_DEFINITION (p.ej. de cualquiera de las vistas que aparece en la ilustración 11 anterior) y con el botón derecho elegir la opción View Field in Popup Editor. También puedes conseguir ver completa la instrucción que construye la vista mediante una ventana cliente (Tools / MySQL Command Line Clientde la barra de menús) y el comando: SHOW CREATE VIEW practica1.vista1g; Página 13


Parte I. Vistas

A través de los conceptos aquí expuestos podrás comprender la funcionalidad del resto de columnas de la tabla (vista) anterior.

1.4 Modificación La sintaxis básica es muy similar a la de creación: ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = {nombreusuario} | CURRENT_USER}] [SQL SECURITY {DEFINER | INVOKER}] VIEW nombre_vista [(lista_columnas)] AS sentencia_select [WITH [CASCADED | LOCAL] CHECK OPTION]

Lo que hace es modificar la especificación de la vista. Realmente es como si se borrara y se volviera a crear con la nueva especificación de la sentencia SELECT por lo que no se suele utilizar esta sentencia puesto que se puede conseguir los mismos resultados que utilizando la cláusula REPLACE de la sentencia CREATE. Como equivale a borrarla y volverla a crear, el usuario que lo haga debe tener los privilegios de creación y borrado para vistas y los de las columnas referenciadas en la sentencia SELECT de creación de la vista. Ejemplo, si queremos que la última columna de la vista1f no sea la antigüedad en la empresa sino la edad de los empleados, podemos borrar la vista (siguiente apartado) y volverla a crear o:

Instrucción 17

1.5 Borrado A partir de la version de MySql 5.0.1 como las anteriores sentencias. Sintaxis: DROP VIEW [IF EXISTS] vista1 [, vista2] ... Borra una o más vistas. Para poder realizarlo se debe poseer los privilegios de borrado sobre vistas. La cláusula IF EXISTS evita que aparezca mensaje de error en caso de que la vista a borrar no exista y en su lugar aparece un mensaje informativo.

Página 14


Parte I. Vistas

Ejemplo de borrado de la vista vista1b

Instrucción 18

1.6 Operaciones sobre vistas. Restricciones Antes de indicar las operaciones que se pueden realizar sobre las vistas y sus restricciones, apuntar las normas más importantes a tener en cuenta a la hora de la crear vistas: - La sentencia SELECT de creación de la vista no puede hacer referencia en la cláusula FROM ni a una tabla temporal (TEMPORARY TABLE) ni contener una subconsulta. - La sentencia SELECT no puede referirse a variables de usuario o de sistema. - Dentro de un procedimiento almacenado (se estudian en el siguiente tema), la definición de la vista no puede hacer referencia ni a los argumentos del procedimiento ni a las variables locales del mismo. - Cualquier tabla o vista a la que referencia la nueva vista debe existir previamente. - No se puede asociar un disparador con una vista (se tratan los disparadores también en el siguiente tema de este curso). a) Consulta Ya visto en los apartados anteriores. b) Actualización En general, si una vistalaestá basada en una sola tabla, al modificar la vista se está modificando directamente tabla. Ejemplo: Si tenemos la siguiente vista:

Instrucción 19

Cualquiera de las dos actualizaciones siguientes nos lleva al mismo resultado, actualizando 3 filas, cambiando la extensión telefónica 760 por la 990 a 3 empleados de la empresa. La diferencia está en que sentencia de la derecha se está modificando la tabla empleados a través de la vista y en la de la izquierda se hace directamente a través de la tabla.

Instrucción 20

Instrucción 21

¿Es posible poder realizar actualizaciones de las tablas a través de las vistas? No siempre. Depende de la sentencia SELECT que construye la vista a partir de la tabla. Antes de ver algunas restricciones ¿Cómo se puede averiguar si se pueden Página 15


Parte I. Vistas

modificar datos de una tabla a través de una vista? Muy sencillo, consultando el diccionario de datos o lo que es lo mismo, la base de datos information_schema

Ilustración 12. Vista VIEWS

La columna IS_UPDATABLE de la vista VIEWS nos indica si a través de una vista puedo modificar (YES) o no una tabla. Recuerda que puedes obtener la misma información “sin moverte” de tu esquema (base de datos) practica1:

Instrucción 22

Página 16


Parte I. Vistas

¿Por qué vista1a y vista1g no son actualizables y el resto sí? Hay una serie de restricciones a tener en cuenta. Si se da alguna de las características siguientes en la sentencia SELECT que construye la vista a partir de la tabla, la vista no es actualizable: -

Contiene funciones de agregado como SUM(), MIN(), MAX(), COUNT() (por eso la vista1g no es actualizable. Contiene la cláusula DISTINCT o GROUP BY o HAVING o UNION o JOIN u ORDER BY (vista1a). Contiene una subconsulta.

c) Inserción ¿Es posible realizar inserciones en la tabla a través de una vista definida sobre ella? Si como podrás comprobar con este ejemplo, que inserta una fila en la tabla empleados a través de una vista construida sobre ella:

Instrucción 23

Pero la tabla base empleados tiene muchos más campos como número de departamento, salario, comisión, número de hijos… ¿Con qué valores se rellenan estos valores? Mira y verás:

Ilustración 13. Campos de la tabla empleados

¿Por qué se ha podido hacer esto? Si miras la descripción de la tabla mediante el script que lanzaste para crearla o directamente desde el browser (botón derecho sobre la tabla empleados de la base de datos practica1 – opción Edit Table) como aparece en la siguiente figura, verás que el único campo que no debe quedar nulo (por ser clave primaria en este caso) es el de número de empleado (numem):

Ilustración 14. Descripción de la tabla empleados

Página 17


Parte I. Vistas

Siempre que no nos dejemos de rellenar los campos obligatorios de la tabla base podremos hacer inserciones a través de la vista. En cambio, si utilizamos la vista vista1d (contiene las columnas nombre, salario y número de departamento) para insertar en la tabla base de empleados no podremos hacerlo pues nos estamos dejando de introducir el campo numem (que es obligatorio y no autonumérico). Realmente la información de esas tres últimas líneas no es del todo cierta, pues si pruebas a realizar la siguiente inserción:

Instrucción 24

te va a dejar hacerlo siendo que falta el campo obligatorio (y no autonumérico) del número de empleado (numem). La razón de ello, como podrás comprobar tal y como hemos hecho en la descripción de esta tabla, es que esta columna tiene como valor por defecto 0 y por tanto le asigna a Raquel el número de empleado 0. A partir de ese momento ya no puedes hacer más inserciones como la que viene a continuación pues ya no puede volver a asignarle el 0 como número de empleado al estar la clave repetida:

Instrucción 25

Lo que debes recordar es que siempre podremos realizar inserciones en las tablas a través de sus vistastener siempre y cuando no nos lostodas campos la tabla. Deberemos en cuenta además de dejemos la anterior las obligatorios restriccionesdeque aparecen al final del apartado b anterior. Antes de terminar el tema comentar una opción que puede añadir cierta seguridad a las vistas y que se había dejado planteada en el apartado 1.3 de este tema en relación a la creación de vistas y su sintaxis básica. La cláusula es: [WITH CHECK OPTION] ¿Para qué sirve ? Observa la siguiente vista:

Instrucción 26

La sentencia anterior crea una vista con todos los datos de los empleados del Departamento 100. A través de esta vista, como se ha trabajado en los puntos anteriores se puede realizar cualquier operación de manipulación de la tabla base empleados. Por eso, estas dos instrucciones son totalmente correctas:

Página 18


Parte I. Vistas

Instrucción 27

Instrucción 28

Pero en la segunda de ellas, hemos utilizado una vista que representa los empleados del Departamento 100 para introducir un empleado que no cumple realmente la cláusula WHERE del SELECT de creación de la vista ya que su departamento es el 120. Si hubiéramos creado la vista de esta otra forma (se ha añadido la comprobación al final):

Instrucción 29

no hubiéramos tenido problemas en añadir al empleado Mario Carrera por ser del Departamento 100 pero no nos hubiera dejado realizar la inserción de la empleada Carmen Bailín dando mensaje de error ya que antes de hacer la inserción MySQL chequea que los datos a introducir cumplan con la selección de la cláusula WHERE de la sentencia SELECT de creación de la vista que seleccionaba las filas de empleados del Departamento 100 y estamos intentando introducir una empleada de otro Departamento. Recordad que tal y como se expuso en el apartado 1.3, la cláusula WITH CHECK OPTION equivale a WITH CASCADED CHECK OPTION en caso de que no se especifique la opción LOCAL, al ser CASCADED la opción por defecto, y por tanto todas las vistas que se creen a partir de la vista vista1i deberán seguir respetando las restricciones de la cláusula WHERE de esta última.

1.7 Ejercicios resueltos. Los siguientes ejemplos sirven para completar los vistos en este tema así como para recordar las sentencias SQL y poder hacer los ejercicios propuestos. Centros y sus Departamentos (puedes utilizar si lo deseas la cláusula JOIN… ON en lugar de la aquí propuesta):

Instrucción 30 Ilustración 15. Resultado de la Instrucción 30

Página 19


Parte I. Vistas

Instrucción 31. Departamentos que no dependen jerárquicamente de otros

Instrucción 32. Número de empleados por Departamento

Ilustración 16. Resultado de la Instrucción 32

La misma consulta que la anterior instrucción 32 pero especificando el nombre del Departamento en lugar de su número:

Instrucción 33

Ilustración 17. Resultado de la instrucción 33

Página 20


Parte II. Programas almacenados

PARTE II. PROGRAMAS ALMACENADOS Tema 2. Programas almacenados en MySQL 2.1. Introducción...................................................................................................3 2.2. Ventajas de su utilización..............................................................................3 2.3. Edición de programas....................................................................................4 2.4. Fundamentos básicos del lenguaje ................................................................8 2.4.1. Variables, tipos de datos, comentarios y literales.................................8 2.4.2. Variables de usuario ...........................................................................12 2.4.3. Parámetros ..........................................................................................13 2.4.4. Principales operadores........................................................................15 2.4.5. Expresiones.........................................................................................16 2.4.6. Funciones incorporadas ......................................................................17 2.4.7. Bloques de instrucciones ....................................................................20 2.4.8. El comando IF ....................................................................................22 2.4.9. El comando CASE..............................................................................24 2.4.10. El comando LOOP, LEAVE e ITERATE........................................25 2.4.11. El comando REPEAT … UNTIL .....................................................26 2.4.12. El comando WHILE .........................................................................27 2.4.13. Bucles anidados................................................................................28 2.5. Procedimientos............................................................................................28 2.5.1. Creación de Procedimientos. Diccionario de datos............................28 2.5.2. Modificación de Procedimientos........................................................31 2.5.3. Borrado de Procedimientos ................................................................32 2.5.4. Utilización de instrucciones DDL y DML en procedimientos almacenados........................................................32 2.5.5. Utilización de instrucciones de consulta en procedimientos almacenados........................................................32 2.5.6 Almacenar en variables el valor de una fila de una tabla....................33 2.5.7 Sentencias preparadas. SQL dinámico ................................................35 2.6. Cursores.......................................................................................................38 2.6.1. Sentencias utilizadas con cursores. Ejemplos ....................................38 2.6.2. Cursores anidados...............................................................................43 2.7. Manejo de errores........................................................................................44 2.7.1 Introducción a la gestión de errores..................................................44 2.7.2 Tipos de manejador ..........................................................................48 2.7.3 La condición del manejador .............................................................49 2.7.4 ...................................................52 2.7.5 Orden Ámbitodedeactuación actuacióndel delmanejador manejador.................................................53 2.7.6 Ejemplo de tratamiento de errores....................................................54 2.8 Funciones....................................................................................................55 2.8.1 Creación de funciones. Diccionario de datos ...................................55 2.8.2 Ejemplos de utilización de Funciones ..............................................56 2.8.3 Modificación de funciones ...............................................................60 2.9 Triggers.......................................................................................................60 2.9.1 Creación de triggers. Diccionario de datos.......................................60 Página 1



2.9.2 Borrado de triggers ...........................................................................62 2.9.3 Utilización de triggers ......................................................................63 Anexo. MySQL desde otras aplicaciones 1. Introducción: Ventajas y desventajas ...................................................................66 2. Ejecución de procedimientos almacenados. Tratamiento de errores....................67 2.1 En PHP ................................................................................................67 2.2 En Visual Basic Express 2005.............................................................69 3. Ejecución de funciones.........................................................................................70 3.1 En PHP ................................................................................................70 3.2 En Visual Basic Express 2005.............................................................71 4. Ejecución de sentencias DDL...............................................................................71 4.1 En PHP ................................................................................................71 4.2 En Visual Basic Express 2005.............................................................72 5. Ejecución de sentencias preparadas......................................................................72 4.1 En PHP ................................................................................................72 4.2 En Visual Basic Express 2005.............................................................74

Página 2



TEMA 2 Programas almacenados en MySQL Alberto Carrera Martín

2.1 Introducción La aparición de los procedimientos almacenados, funciones y triggers (desencadenadores) en MySQL 5 ha supuesto una enorme revolución en este gestor de bases de datos que ya disfrutaba de una gran popularidad. Se ha producido el salto para que MySQL pueda ser utilizado como SGBD empresarial. A ello hay que añadir que la sintaxis de los programas es sencilla lo que facilita su escritura. Un programa almacenado es un conjunto de instrucciones almacenadas dentro del servidor de bases de datos y que se ejecutan en él. Este conjunto se identifica por un nombre. Tipos de programas almacenados: -

Procedimientos almacenados: El más común de los programas almacenados. Resuelven un determinado problema cuando son llamados y pueden aceptar varios parámetros de entrada y devolver varios de salida.

-

Similares de a los procedimientos salvo que sólo Funciones almacenadas devuelven un valor como: parámetro salida. La ventaja que presentan las funciones es que pueden ser utilizadas dentro de instrucciones SQL y por tanto aumentan considerablemente las capacidades de este lenguaje. Triggers o desencadenadores o disparadores: Son programas que se activan (“disparan”) ante un determinado suceso ocurrido dentro de la base de datos.

-

Por el momento, MySQL a diferencia de otros SGBD comerciales no ofrece la posibilidad de utilizar ni paquetes ni clases. Los programas almacenados en MySQL cumplen en gran medida el estándar ANSI de especificación de ANSI SQL:2003 SQL/PSM (Persistent Stored Module).

2.2 Ventajas de su utilización Ventajas de la utilización almacenados en comparación con los realizados en un determinado lenguaje yprogramas que no residen en el servidor: -

Mayor seguridad y robustez en la base de datos. Al permitir que los usuarios puedan ejecutar diferentes programas (los que estén autorizados), se está limitando e impidiendo el acceso directo a las tablas donde están almacenados los datos evitando la manipulación directa de éstas por parte de los usuarios y por tanto eliminando la posibilidad de pérdida accidental de los datos. Los programas serán los que accederán a las tablas. Página 3

Lenguaje Sql con MySQL avanzado -

-

-


Mejor mantenimiento de las aplicaciones que acceden a los programas almacenados y por tanto disminución de la posibilidad de aparición de errores. En lugar de que cada aplicación cliente disponga de sus propios programas para realizar operaciones de inserción de consulta o actualización, los programas almacenados permiten centralizar los métodos de acceso y actualización anteriores presentando una interfaz común para todos los programas. Mayor portabilidad de las aplicaciones (relacionada con el punto anterior) puesto que la lógica de la aplicación ya queda implementada en los programas almacenados permitiendo al programador centrarse sólo en su interfaz. Debido a la fuerte integración con el lenguaje SQL, no se necesita de ningún tipo de conector o driver como ODBC (Open DataBase Connectivity) o JDBC (Java DataBase Connectivity) para poder construir y ejecutar sentencias SQL. Bastará agrupar estas últimas bajo un programa almacenado que se llamará en el momento en el que se necesite. Reducción del tráfico de red. El cliente llama a un procedimiento del Servidor enviándole unos datos. Éste los recibe y tras procesarlos devuelve unos resultados. Por la red no viajan nada más que los datos. En contrapartida señalar que se produce una carga más elevada en el servidor, mucho más que si las aplicaciones se ejecutaran en los clientes, pero hoy en día no supone mucho inconveniente con la tecnología actual de servidor de que se dispone.

2.3 Edición de programas Para editar programas se pueden utilizar diferentes herramientas o métodos: -

A través de la línea de comando del cliente MySQL. Herramienta MySQL Query Browser. Cualquier editor de texto o cualquier otra herramienta de terceros como el TOAD para MySQL.

Como hemos hecho con el tema anterior de vistas, utilizaremos la herramienta MySQL Query Browser porque presenta mayores ventajas como ayuda incorporada, posibilidad de ejecutar sentencias SQL o la visualización de palabras clave del lenguaje resaltadas en diferentes colores entre otras características. Pasaremos a continuación a crear nuestro primer procedimiento. Para ello: 1. Arranca la herramienta MySQL Query Browser (Botón Inicio / Todos los Programas / MySQL / MySQL Query Borwser si trabajas bajo el sistema operativo Windows). Aunque el usuario root solo debe utilizarse para labores de administración principalmente, para probar los diferentes procedimientos de estos temas puedes conectarte con él y a la base de datos test tal y como muestra la siguiente figura:

Página 4



Ilustración 1. Conexión a la base de datos

2. Elige el comando u opción de menú File / New Script Tab. 3. Introduce las líneas de código que vienen a continuación: DELIMITER $$ DROP PROCEDURE IF EXISTS ejemplo1 $$ CREATE PROCEDURE ejemplo1 () BEGIN SELECT 'Mi primer programa en MySQL'; END$$ DELIMITER ; Breve explicación: MySQL utiliza el carácter “;” para finalizar cada sentencia SQL. Como dentro del cuerpo del procedimiento las instrucciones van separadas por “;” (5ª línea) para distinguirlas de las sentencias SQL necesitamos utilizar otro carácter delimitador (en la 1ª línea se habilita y en la última se vuelve a dejar el que estaba). La sentencia DROP PROCEDURE…. borra el procedimiento si este estuviera previamente creado (de no ponerlo daría error); en la 3ª línea se crea dicho procedimiento. La 4ª línea indica el comienzo del cuerpo del procedimiento que finaliza en la penúltima línea.

Ilustración 2. Nuestro primer procedimiento

Página 5



4. Pulsa el botón Guardar o comando del menú File / Save – Nombre del script: ejemplo1. 5. Para compilar el procedimiento anterior en busca de posibles errores (no es el caso si lo has copiado igual que aquí aparece) elige la opción de menú Script / Execute o el botón Execute (botón verde de la parte superior derecha de la ilustración 2 anterior). Si no te apareciera su nombre (ejemplo1) o cualquier otro objeto que crees en adelante en la pestaña Schemata tal y como figura en la ilustración 2 anterior, selecciona la base de datos test y con el botón derecho la opción Refresh (refrescar):

Ilustración 3. Opción Refresh

6. Para ejecutar el procedimiento, haz doble clic sobre su nombre ejemplo1 que figura dentro de la pestaña Schemata de la ilustración 2 anterior (o también en la ilustración 4 siguiente). Esto se traduce en la siguiente sentencia de llamada:

Ilustración 4. Ejecutando el procedimiento

A continuación pulsa el botón Execute (si no aparece en pantalla se consigue la misma funcionalidad pulsando la combinación de teclas + o eligiendo el comando Query / Execute del menú). Aparecerá el resultado de la ejecución del programa:

Página 6



Ilustración 5. Resultado de la ejecución

Puedes también crear procedimientos y funciones de una forma rápida utilizando la opción de menú Script / Create Stored Procedure – Function. De esta manera ahorrarás mucho tiempo introduciendo código al insertarse automáticamente las sentencias básicas. Para probar esta última posibilidad selecciona la base de datos test y pulsa la opción de menú anterior:

Ilustración 6. Creación de procedimientos mediante comandos de menú

Pulsa el botón Create PROCEDURE

Procedimiento 1 ejemplo1_bis

y verás que sólo tienes que escribir en la línea 6 que está vacía la instrucción: SELECT 'Mi primer programa en MySQL'; El nombre que precede al del procedimiento corresponde al de la base de datos donde queda almacenado dicho procedimiento, que es con la que estás trabajando al tenerla seleccionada. Página 7



Para modificar o volver a editar el contenido de cualquier programa puedes hacerlo: -

Si no aparece en pantalla, utiliza el comando File / Open Script del menú para abrir el script que contiene el programa almacenado. Modifica las líneas de código necesarias. Volver a guardarlo con la opción del menú File / Save. Sigue los mismos pasos vistos para compilarlo y ejecutarlo.

Si todo ha ido bien dispones del programa en dos sitios, tanto en el disco duro de tu ordenador como dentro de la base de datos. Podrías también modificar el contenido del programa almacenado seleccionándolo de la pestaña Schemata, después hacer clic con el botón derecho y comando Edit Procedure tal y como muestra la ilustración 7. Después de modificarlo puedes guardarlo, compilarlo y ejecutarlo como se ha detallado anteriormente. Quizás sea más interesante utilizar el primero de estos dos métodos, modificar directamente el script almacenado en el disco por razones de seguridad (disponer de una copia fuera de la base de datos), por poder migrar el procedimiento a otra base de datos distinta (observa en este último método en la línea 3 como este procedimiento ejemplo1bis está asociado a la base de datos test) y siempre se pueden guardar diferentes versiones en disco (ejemplo1, ejemplo 1_1, ejemplo1_2…) que contengan los diferentes cambios que se han ido produciendo en la mejora del programa. Ilustración 7. Otra forma de editar procedimientos

2.4 Fundamentos básicos del lenguaje 2.4.1 Variables, tipos de datos, comentarios y literales Las son elementos de datos con un nombre cuyo valor puede ir cambiando a lo largovariables de la ejecución del programa. SINTAXIS: DECLARE nombre_variable1 [ ,

nombre_variable2...] tipo

[DEFAULT

valor] ;

La declaración de variables se realiza antes del comienzo de las instrucciones (y antes de los cursores y manejadores de errores que se verá también en este curso)

Página 8



De la sintaxis anterior se deduce que se pueden declarar varias variables del mismo tipo seguidas y separadas por comas. Al mismo tiempo que se declaran las variables se pueden definir asignándoles un valor inicial mediante la cláusula DEFAULT; si no se les asigna ninguno entonces las variables quedan definidas al valor NULL. En el momento de la declaración hay que indicar el tipo de datos de la variable; pueden utilizarse cualquiera de los que se emplean en el momento de crear tablas mediante la instrucción CREATE TABLE. Veremos los que se utilizan más frecuentemente. TIPOS DE DATOS NUMÉRICOS INT, INTEGER Entero. Los valores pueden ir desde -2147483648 a 2147483647 para enteros con signo o desde 0 a 4294967295 para enteros sin signo TINYINT El más pequeño de los enteros. Rango entre -128 y -127 con signo, o de 0 a 255 sin signo SMALLINT Entero entre -32768 y 32767 (valores con signo) o entre 0 y 65535 (valores con signo) MEDIUMINT Entero de valores con signo que van de -8388608 a 8388607 o para valores sin signo de 0 a 16777215 BIGINT Entero grande. Con signo puede tomar valores desde 9223372036854775808 a 9223372036854775807 y sin signo de 0 a 18446744073709551615 FLOAT Real de precisión simple. Permite almacenar números de 1.7E38 a 1.7E38 con signo o de 0 a 3.4E38 para valores sin signo DOUBLE Real de precisión doble. Puede llegar a alcanzar valores de 0 a DECIMAL(precisión, escala) NUMERIC(precisión, escala)

1.7E308 para números sin signo Equivalen al tipo DOUBLE pero se diferencian en que ocupan bastante mayor espacio (por almacenar valores exactos y no aproximados). Si el número de decimales es importante (cantidades monetarias) es mejor utilizar el tipo NUMERIC. Precisión indica el número de dígitos totales, escala es el número de decimales a la derecha de la coma del total de dígitos que viene expresado en la precisión

TIPOS DE DATOS DE TEXTO CHAR(longitud) Cadenas de texto de longitud fija hasta un máximo de longitud de 255 caracteres. Si el valor a almacenar es más corto que la longitud de la variable el resto de caracteres se rellenan a blancos VARCHAR(longitud) Cadenas de texto de longitud variable hasta un máximo de 64 KB. A diferencia del tipo CHAR, si el valor a almacenar es más corto, el tamaño real de la variable es el número de caracteres que ocupa el valor pues no rellena a blancos. Como almacena la longitud junto con los caracteres, su utilización en los programas hace que le ejecución de éstos sea un poco más lenta que si se utiliza el tipo CHAR ENUM Almacena un valor concreto de un conjunto posible de valores SET Similar a ENUM pero permite guardar más de un valor TEXT Texto de hasta 64 KB. de tamaño LONGTEXT Texto de hasta 4 GB. de tamaño Página 9



TIPOS DE DATOS DE FECHA Y HORA DATE Fechas con el formato AAAA-MM-DD entre 1000-01-01 y 9999-12-31 DATETIME Fecha y hora con el formato AAAA-MM-DD hh:mm:ss. Para la parte de la hora el rango debe estar entre 00:00:00 y 23:59:59 OTROS TIPOS DE DATOS BLOB Hasta 64KB. de datos binarios LONGBLOB Hasta 4GB. de datos binarios A continuación un ejemplo de declaración de variables, el contenido de algunas de ellas se visualiza mediante las líneas de código 16 a 18

Procedimiento 2 variables1

Otro ejemplo de declaración de variables. Utilizamos la sentencia de asignación SET para asignar valores a variables como se verá más adelante:


Página 10



De las figuras anteriores se observa: -

-

Aparecen dos tipos de COMENTARIOS: o Comentarios de una sola línea, precedidos de -o Comentarios de varias líneas, entre /* */, aunque en este caso sólo se extiende el comentario a lo largo de una sola línea. Los LITERALES de texto y fecha van encerradas entre ‘’.

Las reglas para nombrar variables son bastante flexibles pues a diferencia de otros lenguajes se permite: 1. Nombres largos (más de 255 caracteres). 2. Caracteres especiales. 3. Pueden comenzar con caracteres numéricos. Todas las variables que se pueden utilizar deben ser escalares, es decir, un solo valor, a diferencia de otros lenguajes que permiten definir variables basadas en tipos de datos compuestos como son los registros, arrays… Para asignar valores a variables se utiliza la siguiente sintaxis: SET

nombre_variable1

=

expresión1

[,

nombre_variable2

=

expresión2 ...]

En este caso y a diferencia de otros lenguajes es necesario especificar la sentencia SET para asignar valores a las variables. Se puede en una sola instrucción realizar varias asignaciones:

Procedimiento 4 asigna1

Página 11



2.4.2 Variables de usuario Un tipo especial de variables son las variables de usuario ya que pueden ser manipuladas dentro y fuera de los programas almacenados. Son una característica de MySQL desde la versión 3. Ejemplo de utilización:


Puedes probar el procedimiento variables3 anterior desde el cliente de línea de comandos de MySQL. Accede a él desde dentro de la herramienta MySQL Query Browser mediante el comando de menú Tools / MySQL Command Line Client. Este tipo de variables no necesitan declaración y van precedidas del carácter @. Son de un tipo de datos variant y pueden almacenar texto, fechas y números. En las siguientes líneas se crea una variable de usuario de nombre v1, se inicializa a 20 y a continuación se llama (CALL) al programa variables3 que lo que hace es doblar el valor de la variable como se puede comprobar tanto en las líneas de procedimiento como en la ventana de ejecución desde la línea de comandos de MySQL: mysql > USE TEST Dat abase changed mysql > SET @v1=20; Quer y OK, 0 r ows af f ect ed ( 0. 00 sec) mysql > CALL vari abl es3( ) ; Quer y OK, 0 r ows af f ect ed ( 0. 00 sec) mysql > SELECT @ v1; +- - - - - - + | @v1 | +- - - - - - + | 40 | +- - - - - - + 1 r ow i n set ( 0. 00 sec)

Su alcance es de una sesión y por tanto son accesibles desde cualquier programa que se ejecuta durante esa sesión, lo que las asemeja al concepto de variables globales como se muestra en el siguiente ejemplo:

Procedimiento 7 variables5 Procedimiento 6 variables4

Página 12



Probando los dos procedimientos anteriores: mysql > USE TEST Dat abase changed mysql > CALL vari abl es4( ) ; +--- --- --- --- --- --- --- --- + | @v1 | +--- --- --- --- --- --- --- --- + | Raquel y Mar i o Car r er a | +--- --- --- --- --- --- --- --- + 1 r ow i n set ( 0. 00 sec) Quer y OK, 0 r ows af f ect ed ( 0. 02 sec)

2.4.3 Parámetros Los parámetros son variables que se envían y reciben de los programas a los que se llaman. Se definen en la cláusula CREATE de creación de los procedimientos de la siguiente forma: CREATE PROCEDURE( [ [ I N | OUT | I NOUT]

nombre_parámetro tipo de datos ...])

Los tipos de parámetros, según el modo en que se pasan al procedimiento llamado son: 1. IN: Opción por defecto. En otros lenguajes representa el modo de paso de parámetros por valor, es decir, el procedimiento almacenado trabaja con una “copia” del parámetro que recibe y por tanto no modifica nada el valor del parámetro que se le pasa al programa almacenado tal y como se puede ver en el siguiente ejemplo y posterior ejecución:

Procedimiento 8 parametro1

Ejecutando el procedimiento parametro1: mysql > USE TEST Dat abase changed mysql > SET @p1 =10; Quer y OK, 0 r ows af f ect ed ( 0. 02 sec) mysql > CALL par amet r o1 ( @p1) ; Quer y OK, 0 r ows af f ect ed ( 0. 02 sec) mysql > SELECT @ p1; +- - - - - - + | @p1 | +- - - - - - + | 10 | +- - - - - - + 1 r ow i n set ( 0. 00 sec)

Página 13



2. OUT: Es una forma de paso de parámetros por variable, es decir, las modificaciones del parámetro dentro del programa almacenado modifican directamente el parámetro pasado como argumento. Hasta que no se le asigne un valor determinado dentro del programa, su valor dentro de él será nulo. Se suelen utilizar como flags o indicadores de cómo ha ido la ejecución de un programa como veremos en el apartado de tratamiento de errores. Ejemplo:

Procedimiento 9 parametro2

Probando el procedimiento parametro2: Este procedimiento trabajará directamente sobre la variable que se le pase como argumento. mysql > USE TEST Dat abase changed mysql > SET @p1=100; Quer y OK, 0 r ows af f ect ed ( 0. 00 sec) mysql > CALL par amet r o2( @p1) ; +- - - - - - +- - - - - - + | v_v 1 | p_p1 | +- - - - - - +- - - - - - + | NULL | NULL | +- - - - - - +- - - - - - + 1 r ow i n set ( 0. 00 sec) Quer y OK, 0 r ows af f ect ed ( 0. 05 sec) mysql > SELECT @ p1; +- - - - - - + | @p1 | +- - - - - - + | 28 | +- - - - - - + 1 r ow i n set ( 0. 00 sec)

3. INOUT: Otra forma de paso de parámetros por variable pero con la característica de que se le puede pasar un valor inicial que el programa llamado tendrá en cuenta (y no lo considerará NULL como en caso del parámetro OUT). Ejemplo:

Página 14



Procedimiento 10 parametro3 Ej ecut ando el pr ocedi mi ent o par amet r o3: mysql > SET @p1=1000; Quer y OK, 0 r ows af f ect ed ( 0. 00 sec) mysql > CALL par amet r o3( @p1) ; Quer y OK, 0 r ows af f ect ed, 1 war ni ng ( 0. 00 sec) mysql > SELECT @ p1; +- - - - - - + | @p1 | +- - - - - - + | 6. 01 | +- - - - - - + 1 r ow i n set ( 0. 00 sec)

2.4.4 Principales Operadores Similares a los de otros lenguajes de programación. Se utilizan en la mayoría de los casos junto con la sentencia SET para asignar valores a variables, formando parte de expresiones de comparación y en bucles. OPERADORES MATEMÁTICOS + Suma Resta * Multiplicación / División DIV División entera % Resto

Procedimiento 11 operadores1

Página 15



OPERADORES DE COMPARACIÓN. Comparan dos valores y devuelven como resultado CIERTO (1), FALSO (0) O NULO (NULL)) OPERADOR SIGNIFICADO EJEMPLO Y RESULTADO > Mayor que 3 > 2  Cierto < Menor que 4 < 6  Cierto <= Menor o igual que 4 <= 3  Falso >= Mayor o igual que 4>=3  Cierto = Igual a 4 = 4  Cierto < > != Distinto de 4 <> 4  Falso <=> Comparación de valores nulos. NULL <=> NULL  Cierto Devuelve cierto si ambos valores son nulos BETWEEN Comprendido entre dos valores 45 BETWEEN 25 AND 50  Cierto IS NULL Si valor nulo 3 IS NULL  Falso IS NOT NULL Si valor no nulo 3 IS NOT NULL  Cierto NOT No comprendido entre dos valores 45 NOT BETWEEN 25 AND BETWEEN 50  Falso IN Pertenencia al conjunto o lista 45 IN (44, 45, 46)  Cierto NOT IN No pertenencia al conjunto o lista 45 NOT IN (44, 45, 46)  Falso LIKE Coincidencia con patrón de "ALBERTO CARRERA" búsqueda LIKE "%CARRERA"  Cierto TABLA DE VERDAD DEL OPERADOR LÓGICO AND AND (&&) CIERTO FALSO NULL CIERTO FALSO NULL CIERTO FALSO FALSO NULL FALSO NULL NULL NULL NULL TABLA DE VERDAD DEL OPERADOR LÓGICO OR OR (||) CIERTO FALSO NULL CIERTO CIERTO CIERTO CIERTO FALSO CIERTO FALSO NULL CIERTO NULL NULL NULL TABLA DE VERDAD DEL OPERADOR LÓGICO NOT NOT (!) CIERTO / TRUE (1) FALSO / FALSE (0) NULL FALSO (0) CIERTO (1) NULL

2.4.5 Expresiones Se trata de una combinación de literales, variables y operadores que se evalúan para devolver un valor. Ver líneas 12 a 14 del procedimiento anterior operadores1. Página 16



2.4.6 Funciones incorporadas En los programas almacenados pueden seguirse utilizando la mayoría de las funciones incluidas en MySQL y que se utilizan para formar las sentencias SQL, excepto las que trabajan con grupos de datos (cláusula GROUP BY) puesto que las variables en los programas almacenados son escalares y almacenan un solo valor. Por eso funciones como SUM, COUNT, MIN, MAX y AVG pueden emplearse en programas almacenados siempre y cuando devuelvan una fila y no varias (como consecuencia p.ej. en este último caso de utilizar la cláusula GROUP BY). A continuación se detallarán las más importantes. Para más información consultar el manual. FUNCIONES MATEMÁTICAS FUNCIÓN DEVUELVE EJEMPLO Y RESULTADO ABS(num) Valor absoluto de num SELECT ABS(-3)  3 SIGN(num) -1, 0 o 1 en función del SELECT SIGN(2), SIGN(-2), SIGN(0)  1, -1, 0 valor de num MOD(num1, Resto de la división de SELECT MOD (5,2)  1 num2) num1 por num2 FLOOR(num) Mayor valor entero SELECT FLOOR(23.9)  23 inferior a num CEILING(num) Menor valor entero SELECT CEILING(23.9)  24 superior a num ROUND(num) Redondeo entero más SELECT ROUND(23.5), próximo ROUND(23.4); 24 23 ROUND(num,d) TRUNCATE (num, d) POW(num1, num2) SQRT (num)

Redondeo a d decimales más próximo Num truncado a d decimales Num1 elevado a la num2 potencia Raíz cuadrada de num

SELECT ROUND(23.545,2), ROUND(23.44,1) 23,55 23,4 SELECT TRUNCATE (22.89, 1), TRUNCATE (15326,-3)  22,8 5000 SELECT POW(2,5)  32 SELECT SQRT(36)  6

FUNCIONES DE CADENA FUNCIÓN DEVUELVE

EJEMPLO Y RESULTADO LIKE(plantilla) Resultado de comparar una cadena SELECT 'ALBERTO' LIKE con una plantilla 'ALBER%'  1 (cierto) NOT LIKE Lo contrario a la fila anterior SELECT 'ALBERTO' NOT (plantilla) LIKE 'ABIERTO'  1 _ (subrayado) % \

Se trata de comodín que reemplaza un un carácter en una cadena Como el caso anterior pero para uno o más caracteres Como en otros lenguajes se trata del carácter de escape, si precede al comodín elimina su función y lo trata como un carácter más

SELECT LIKE 1 'ALBERT_''ALBERTO' SELECT 'ALBERTO' LIKE 'ALBER%'  1 (cierto) SELECT '30%' LIKE '30\%' 1

Página 17



FUNCIONES DE CADENA (Continuación) BINARY Por defecto en las comparaciones entre cadenas no se distingue mayúsculas de minúsculas salvo que se indique esta opción STRCMP(cad1, -1 si cad1 < cad2, 0 si cad1=cad2 o cad2) 1 si cad1 > cad2 UPPER(cad)

La cadena cad en mayúsculas

LOWER(cad)

La cadena cad en minúsculas

FUNCIONES DE FECHA FUNCIÓN DEVUELVE NOW() Fecha y hora según el formato ‘aaaa-mm-dd hh:mm:ss’ DAYOFWEEK(fecha) Cifra que representa el día de la semana (1 – domingo, 2 –lunes…) WEEKDAY(fecha) Ídem de DAYOFWEEK pero con otros valores: 0 – lunes, 1 – martes… DAYOFMONTH(fecha) Día del mes (entre 1 y DAYOFYEAR(fecha) MONTH(fecha); DAYNAME(fecha) MONTHNAME(fecha) QUARTER(fecha) WEEK(fecha [,inicio])

YEAR(fecha) HOUR(fecha) MINUTE(fecha) SECOND(fecha)

SELECT 'ALBERTO' LIKE BINARY 'Alberto'  0 (falso) SELECT STRCMP('ALBERTO', 'ABIERTO')  1 SELECT UPPER('Alberto')  ´ALBERTO’ SELECT LOWER('Alberto')  ‘alberto’

EJEMPLO Y RESULTADO SELECT NOW()  2006-08-01 00:40:25 SELECT DAYOFWEEK('196611-03')  5 | SELECT WEEKDAY('1966-1103')  3 SELECT

31) Día del año (entre 1 y 366) Mes del año (entre 1 y 12) Nombre del día de la fecha Nombre del mes

DAYOFMONTH('1966-11-03') 3 SELECT DAYOFYEAR('196611-03')  307 SELECT MONTH('1966-11-03')  11 SELECT DAYNAME('1966-1103')  ‘Thursday’ SELECT MONTHNAME('196611-03')  ‘November’ Trimestre del año (entre SELECT QUARTER('1966-111 y 4) 03')  4 Semana del año (entre 1 SELECT WEEK('2006-12-20',1) y 52). Inicio especifica el  51 comienzo de la semana. Si no se especifica vale 0 (domingo). Para empezar el lunes utilizar el 1 Año (entre 1000 y 9999) SELECT YEAR('2006-12-20')  2006 La hora SELECT HOUR(NOW()) 1 Los minutos SELECT MINUTE(NOW())  5 Los segundos SELECT SECOND(NOW())  58

Página 18

Lenguaje Sql con MySQL avanzado FUNCIONES DE FECHA (Continuación) TO_DAYS(fecha) Número de días transcurridos desde el año 0 hasta la fecha DATE_ADD(fecha, La fecha sumado el INTERVAL valor tipo intervalo especificado de intervalo DATEDIFF(fecha1, El número de días fecha2) transcurridos entre fecha1 y fecha2 CURDATE() Fecha actual según el CURRENT_DATE() formato ‘aaaa-mm-dd’ CURTIME() Fecha actual según el CURRENT_TIME() formato ‘hh:mm:ss’ DATE_FORMAT (fecha, Devuelve la fecha en el formato) formato especificado. Consultar el manual para las posibilidades de la opción formato. FUNCIONES DE CONTROL FUNCIÓN DESCRIPCIÓN IF(expr1, expr2, expr3)

IFNULL(expr1, expr2) NULLIF(expr1, expr2)

CASE valor WHEN comp1 THEN res1 [WHEN comp2 THEN res2] [ELSE reselse] END


SELECT TO_DAYS('2006-0801')  732889 SELECT DATE_ADD('2006-0801', INTERVAL 1 MONTH)  ‘2006-09-01’ SELECT DATEDIFF('2006-0801', '2006-07-26')  6 SELECT CURRENT_DATE()  ‘2006-08-01’ SELECT CURRENT_TIME()  ‘01:12:43’ SELECT DATE_FORMAT(NOW(), 'Hoy es %d de %M de %Y')  ‘Hoy es 01 de August de 2006’

EJEMPLO RESULTADO Si la expresión expr1 es cierta, SET @A=20; devuelve expr2, sino expr3 @B=15;

Y SET

SELECT IF(@A<@B, @A+@B, @A - @B); 5 Si la expresión expr1 es NULL SET @A=20; devuelve expr2, sino expr1 SELECT IFNULL(@A, 0);  20 Si la expresión expr1 es igual a SET @A=20; SET expr2, devuelve NULL sino @B=15; expr1 SELECT NULLIF(@B, @A);  15 Compara el valor con cada una de SELECT CASE las expresiones comp. Si se WEEKDAY(NOW()) verifica la igualdad entonces WHEN 5 THEN 'Fin de devuelve el valor res asociado, en semana' cualquier otro caso devuelve WHEN 6 THEN 'Fin de reselse semana' ELSE 'No es fin de semana' END;

Página 19



FUNCIONES DE AGREGACIÓN FUNCIÓN DEVUELVE AVG(columna)

Media de los valores de la columna especificada

COUNT (columna | *)

Número de valores no nulos de la columna (si esta se especifica como argumento). Utilizando el carácter * devuelve el número total de valores incluyendo los nulos

MIN(columna)

Valor mínimo de la columna

MAX(columna)

Valor máximo de la columna

SUM(columna)

Suma de valores contenidos en la columna

OTRAS FUNCIONES FUNCIÓN DESCRIPCIÓN CAST (expresión AS Convierte la expresión al tipo tipo) indicado CONVERT (expresión, tipo) LAST_INSERT_ID() Devuelve el valor creado por una columna de tipo AUTO_INCREMENT en la última inserción VERSION() Devuelve la versión del servidor MySQL CONNECTION_ID() Devuelve el identificador de conexión DATABASE() Devuelve la base de datos actual USER()

Devuelve el usuario actual

EJEMPLO Y RESULTADO SELECT AVG(salario) FROM empleados  302.9412 SELECT COUNT(comision) FROM empleados  14 SELECT COUNT(*) FROM empleados  34 (luego 20 trabajadores no tienen comisión) SELECT MIN(salario) FROM empleados  100 SELECT MAX(salario) FROM empleados  720 SELECT SUM(salario) FROM empleados  10300

EJEMPLO

Y

RESULTADO SELECT CONVERT(20060802, DATE)  ‘2006-08-02’ SELECT LAST_INSERT_ID()  0 SELECT VERSION()  ‘5.0.20a-nt’ SELECT CONNECTION_ID()  4 SELECT DATABASE()  ‘test’ SELECT USER()  root@localhost

2.4.7 Bloques de instrucciones Hasta ahora hemos estado trabajando con procedimientos con un solo bloque de instrucciones, comenzando con la sentencia BEGIN y terminando con la sentencia END: Página 20



CREATE {PROCEDURE | FUNCTION |TRIGGER} nombre_del_programa BEGIN Instrucciones

END;

Este es el caso más sencillo pues muchos programas almacenados MySQL contienen varios bloques agrupando un conjunto de instrucciones y comenzando cada uno de ellos con la instrucción BEGIN y finalizando con la END. Con la estructura de bloques se consigue reunir las instrucciones en agrupaciones lógicas que realizan una determinada función, como por ejemplo los bloques de los manejadores de errores que se tratan en este curso. También se un consigue el este ámbito de no lasson variables, definiéndolas dentro de bloquedelimitar interno, de modo visiblesdeclarándolas fuera de él. Eny cambio una variable externa al bloque interno será accesible también desde dentro de este último. En el caso en que la variable externa al bloque y la interna tuvieran el mismo nombre, dentro del bloque interno se estará referenciando a la variable interna. Un bloque no solo agrupa instrucciones sino también otros elementos que se tratan en este curso como: 1. Declaración de variables y condiciones. 2. Declaración de cursores. 3. Declaración de manejadores de error. 4. Código de programa. En el momento de declararse, para evitar mensajes de error, debe seguirse el orden anterior, comenzando en primer lugar por la declaración de variables. Como con otros lenguajes programación, bloques pueden etiquetarse. forma ocurre de actuar garantiza una de fácil lectura del los procedimiento y por tanto deEsta su mantenimiento y permite abandonar el bloque antes de que este concluya si así fuera necesario (sentencia LEAVE). Sintaxis: [etiqueta:] BEGIN Declaración de variables y condiciones. Declaración de cursores. Declaración de manejadores de error. Código de programa. END [etiqueta]; Ejemplos de aplicación:

Procedimiento 12 bloque1

Página 21






2.4.8 El comando IF Igual en su funcionamiento que otros lenguajes de programación. Ejecuta la acción cuya expresión (condición) es cierta (de no ser cierta ninguna entonces ejecuta la acción asociada a la sentencia ELSE). Sintaxis: IF

THEN instrucciones [ ELSEI F condición2 THEN instrucciones ....] [ ELSE instrucciones]

condición1

END I F;

Página 22



Ejemplo:

Procedimiento 16 condicional1

El valor inicial (línea 6) de la variable v_edad conduce a que la instrucción que se ejecute sea la de la línea 12 al no cumplir ninguna de las dos condiciones anteriores. La forma de actuar es la siguiente: Si la expresión de la línea 7 es cierta, se ejecutará la instrucción de la línea 8, sino si la expresión de la línea 9 es cierta entonces se ejecutará la instrucción de la línea 10; si no son ciertas ninguna de las dos expresiones entonces se ejecutará la instrucción de la línea 12. El ejemplo que viene a continuación nos sirve para ver una versión más “abreviada” de la sentencia condicional que la del ejemplo anterior así como para señalar el comportamiento de la sentencia condicional ante valores nulos.


En este caso se ejecutará la instrucción 10 puesto que la edad contiene un valor nulo que no hace cierta la la línea 7. Hay tener puessuseedad. está asignando el calificativo deexpresión “adulto” adeuna persona de que la que se cuidado desconoce Para estos casos puede utilizarse la forma más simple de la sentencia condicional:

Página 23




2.4.9 El comando CASE Similar a la sentencia condicional IF anterior. Todo lo que se puede expresar con la sentencia IF se puede expresar con la sentencia CASE. Esta última se suele utilizar cuando el número de expresiones o condiciones a evaluar es elevado y por tanto la lectura del código se hace más fácil y agradable. Existen 2 formas posibles si lo que se evalúa es una expresión o una condición: CASE

expresión WHEN valor1 THEN instrucciones

[ WHEN val or 2 THEN instrucciones

...]

[ ELSE instrucciones]

END CASE;

En el ejemplo siguiente, tras evaluar la expresión de la línea 8 se ejecutará la instrucción de la línea 10. El comportamiento ante una expresión nula es exactamente el mismo que para la sentencia IF. Si el valor en la línea 7 de la variable fuera NULL, el resultado del procedimiento sería el de la línea 14 siendo que no se corresponde por tanto con la opción de pago elegida.


Página 24



La otra sintaxis es similar a la anterior pero con condiciones en lugar de expresiones: CASE WHEN

condición1 THEN instrucciones [ WHEN condición2 THEN instrucciones ...]

[ ELSE instrucciones]

END CASE;

Ejemplo:


En el ejemplo anterior se ejecutaría la instrucción 8. Sólo se ejecuta la instrucción o instrucciones asociadas a una expresión o condición (no hace falta por tanto una instrucción de salida o de ruptura como ocurre en otros lenguajes). Una vez que se ejecuta se finaliza la instrucción CASE y la ejecución del programa continua por la línea siguiente.

2.4.10 El comando LOOP, LEAVE e ITERATE Sintaxis [ etiqueta: ] LOOP instrucciones

END LOOP [etiqueta] ;

Todas las instrucciones comprendidas entre las palabras reservadas LOOP Y END LOOP (bucle), se ejecutan un número de veces hasta que la ejecución del bucle se encuentra con la instrucción: LEAVE

etiqueta

en ese momento se abandona el bucle.

Página 25



En el siguiente ejemplo la instrucción 10 se ejecutará 4 veces:

Procedimiento 21 bucle1

La sentencia: ITERATE etiqueta se utiliza para forzar que la ejecución del bucle termine en el momento donde se encuentra con esta instrucción y continúe por el principio del bucle. De esta manera en el ejemplo que viene a continuación se ejecutará 3 veces la instrucción de la línea 13 (para los valores de i del 1 al 4 excepto el 3)


2.4.11 El comando REPEAT … UNTIL Sintaxis: [ etiqueta:] REPEAT END

instrucciones UNTI L expresión REPEAT etiqueta [ ]

Página 26



Las instrucciones se ejecutarán hasta que sea cierta la expresión. Por lo menos el conjunto de instrucciones se ejecuta una vez pues la evaluación de la expresión se hace posterior a la ejecución de las instrucciones. El mismo procedimiento bucle1 anterior pero utilizando esta otra instrucción quedaría:


Equivale a la sentencia iterativa vista anteriormente etiqueta: LOOP instrucciones I F expresión THEN END LOOP etiqueta;

LEAVEetiqueta; END I F;

Podría utilizarse la sentencia ITERATE pero puede llevar a situaciones contradictorias si llegara a ejecutar otra vez el conjunto de instrucciones aun habiendo hecha cierta la expresión de finalización del bucle. La sentencia LEAVE también puede utilizarse.

2.4.12 El comando WHILE Sintaxis: [ etiqueta: ] WHI LE END WHI

expresión instrucciones LE [ etiqueta]

DO

Ejecuta el conjunto de instrucciones mientras sea cierta la expresión. Ejemplo:


Página 27



Equivale a la instrucción iterativa anterior: etiqueta: LOOP I F !expresión THEN LEAVEetiqueta; resto de instrucciones; END LOOP etiqueta;

END I F;

2.4.13 Bucles anidados Consiste en utilizar comandos de repetición dentro de otros. Ejemplo de utilización:


Al ejecutar el ejemplo anterior se visualizarán los valores i-j: 1-1, 1-2, 2-1, 2-2 Como se ha indicado en el apartado anterior 2.4.7 de bloques de instrucciones, es importante etiquetar el comienzo y final del bucle no solo por la instrucción LEAVE o ITERATE sino por claridad en el seguimiento del código.

2.5 Procedimientos 2.5.1 Creación de procedimientos. Diccionario de Datos La sintaxis completa de creación de procedimientos es: CREATE PROCEDURE nombre_procedimiento ([parametro1[,...]]) [LANGUAGE SQL] [ [NOT] DETERMINISTIC] [ {CONTAINS SQL | MODIFIES SQL DATA | READS SQL DATA | NO SQL} ] [SQL SECURITY {DEFINER | INVOKER} ] [COMMENT comentario] bloque_de_instrucciones_del_procedimiento

Página 28



Para crear un procedimiento o función el usuario debe disponer del privilegio CREATE ROUTINE y el de ALTER ROUTINE para modificarlo o borrarlo; este último privilegio se asigna automáticamente al creador del procedimiento o función. Para poder ejecutar un procedimiento o función debe disponerse del privilegio EXECUTE. Aspectos a tener en cuenta: 1. El nombre del procedimiento cumple con las normas vistas en este capítulo referidas a nombrar variables. La lista de argumentos suministrados al procedimiento también ha sido estudiada. 2. La cláusula LANGUAGE SQL indica que el lenguaje utilizado en los procedimientos cumple el estándar SQL:PSM. Es una cláusula innecesaria en este momento pues los procedimientos en MySQL sólo soportan este estándar. Si MySQL en un futuro aceptara la escritura de procedimientos almacenados en otros lenguajes como C o Java entonces ya sería necesario indicar el lenguaje de programación utilizado en el procedimiento. 3. [NOT] DETERMINISTIC. Referido al comportamiento del procedimiento. Si un procedimiento es DETERMINISTIC, siempre ante una misma entrada devuelve una misma salida. Funciones numéricas como el valor absoluto, cuadrado, raíz cuadrada son DETERMINISTIC pues siempre devuelven el mismo resultado ante un mismo valor. Por otro lado, una función que devolviera el número de días transcurridos desde 1900 hasta la fecha sería NOT DETERMINISTIC pues cambia según el día que se ejecuta. Por defecto la opción es NOT DETERMINISTIC. Al igual que la anterior se puede prescindir de su utilización debido a que por el momento no es tenida en cuenta por el servidor. Más adelante podrá ser utilizada para la optimización de consultas. 4. [{CONTAINS SQL | MODIFIES SQL DATA | READS SQL DATA | NO SQL}] Indica el tipo de acceso que realizará el procedimiento a la base de datos, si sólo se va a leer datos se especificará la cláusula READ SQL DATA, si además de ello los modifica entonces la cláusula MODIFIES SQL DATA será la que se emplee. Si el procedimiento no realiza ningún tipo de acceso a la base de datos puede utilizarse la cláusula NO SQL. Por defecto se utiliza la opción CONTAINS SQL que indica que el procedimiento contiene consultas SQL. Estos parámetros se utilizan para mejorar el rendimiento. 5. [SQL SECURITY {DEFINER | INVOKER}] Indica si el procedimiento almacenado se ejecutará con los permisos del usuario que lo creó (DEFINER) o con los permisos del usuario que llama al procedimiento (INVOKER) La opción por defecto es DEFINER. Hay que tener muy en cuenta que con la opción DEFINER un usuario que lance el procedimiento podrá acceder a los datos aunque no posea los privilegios sobre las tablas que almacenan dichos datos; se trata de un mecanismo de acceso a los procedimientos sin dar directamente a los datos. 6. acceso [COMMENT comentario] Comentario sobre el procedimiento que puede ayudar al usuario a conocer/recordar el funcionamiento del procedimiento. Esta información puede consultarse como se ha visto anteriormente en el diccionario de datos. Puede prescindirse de dicha cláusula y realizar la escritura de los comentarios aclaratorios al principio del bloque de instrucciones del procedimiento utilizando los caracteres /* */ o --

Página 29



Para conocer toda la información sobre los procedimientos almacenados se procederá a consultar el diccionario de datos de manera similar a cómo se ha hecho anteriormente con otros objetos como las vistas. Se incluye también información para las funciones que se tratan más adelante. Sintaxis: SHOW {PROCEDURE | FUNCTION} STATUS [LIKE patrón] Ejemplo:

Ilustración 8. Información sobre los procedimientos creados

Utilizando un patrón de búsqueda:

Ilustración 9. Información sobre determinados procedimientos

El comando: SHOW CREATE [PROCEDURE | FUNCTION] nombre permite ver información de los procedimientos y funciones así como las líneas de código (al igual que con vistas seleccionar columna Create Procedure – botón derecho del ratón – Comando View Field in Popup Editor):

Página 30



Ilustración 10. Información sobre el código de los procedimientos

La otra forma de ver toda la información de un procedimiento es consultando directamente el DICCIONARIO DE DATOS. Instrucción 1

La tabla (vista) aporta más yinformación que la suministrada por los comandos SHOWROUTINES PROCEDURE STATUS SHOW CREATE.

2.5.2 Modificación de procedimientos Sintaxis: ALTER PROCEDURE nombre_procedimiento {CONTAI NS SQL | NO SQL | READS SQL DATA | MODI FI ES SQL DATA} | SQL SECURI TY {DEFI NER| I NVOKER} | COMMENT comentario

Se debe poseer el privilegio de ALTER ROUTINE para poder modificar procedimientos y funciones. El uso de las distintas opciones se ha expuesto en el apartado de creación.

2.5.3 Borrado de procedimientos Sintaxis: DROP PROCEDURE [IF EXISTS] nombre_procedimiento Al igual que para la modificación, se debe poseer el privilegio de ALTER ROUTINE para poder borrar procedimientos y funciones. Página 31



2.5.4 Utilización de instrucciones DDL y DML en procedimientos almacenados Junto con la recuperación de datos (se expone en el siguiente punto), se trata de una de las más importantes ventajas del empleo de programas almacenados (en este caso procedimientos). En el ejemplo siguiente, en las líneas 7 a 11 nos encontramos con instrucciones DDL (lenguaje definición de datos) cuya finalidad es la creación de una tabla. Por otro lado, la instrucción DML (lenguaje de manipulación de datos) de la línea 14 inserta un alumno de código i y nombre “alumno i” cada vez que se ejecuta dicha instrucción dentro del bucle (5 veces en total).

Procedimiento 26 – “procedimiento1”

Puede verse el resultado de la ejecución del procedimiento y la tabla creada con sus 5 filas refrescando la base de datos test de la pestaña Schemata (clic con el botón derecho del ratón más opción Refresh) y haciendo doble clic sobre el nombre de la tabla dos veces consecutivas (ilustración de la derecha) Ilustración 11. Tabla alumnos

2.5.5 Utilización de instrucciones de consulta en procedimientos almacenados A diferencia de otros gestores de bases de datos, en MySQL se puede (sólo en procedimientos) devolver como resultado de la ejecución del procedimiento un conjunto de filas. La funcionalidad de esta forma de recuperación se asemeja a la que se puede conseguir con el empleo de vistas.

Página 32



En el siguiente procedimiento se recupera el conjunto de filas de los departamentos que pertenecen al centro que se le pasa como argumento:

Ejemplo de ejecución:

Procedimiento 27 resultset1

mysql > CALL t est . r esul t set 1( 20) ; +-- -- -- -+-- -- -- -- -- -- -- -- -- -- -+-- -- -- -- -| numde | nomde | pr esu +-- -- -- -+-- -- -- -- -- -- -- -- -- -- -+-- -- -- -- -| 110 | DI RECCI ON COMERCI AL | 15000000 | 111 | SECTOR I NDUSTRI AL | 11000000 | 112 | SECTOR SERVI CI OS | 9000000 +-- -- -- -+-- -- -- -- -- -- -- -- -- -- -+-- -- -- -- -3 r ows i n set ( 0. 00 sec) Quer y OK, 0 r ows af f ect ed ( 0. 00 sec)

+ | + | | | +

El inconveniente es que el programa que recibe el conjunto de filas resultantes no puede ser un procedimiento MySQL sino otro programa escrito en otro lenguaje como Java o PHP (ver anexo de este tema). Para salvar este problema y poder enviar a un procedimiento MySQL un conjunto de registros se utilizan las tablas temporales:

Procedimiento 28 resultset2

El tiempo de vida de las tablas temporales es el tiempo de duración de la sesión.

2.5.6 Almacenar en variables el valor de una fila de una tabla No sólo las instrucciones SQL de definición y manipulación de datos (DDL y DML) pueden intercalarse en los procedimientos almacenados, la recuperación de los datos almacenados en la base de datos permitirá su posterior proceso o tratamiento. El siguiente ejemplo almacena una fila entera de la tabla creada en el procedimiento anterior (tabla alumnos) en las variables declaradas en las líneas 6 y 7, mostrando su contenido en la línea 12. En la línea 8 se señalan las columnas a recuperar (en este caso Página 33



las dos) y en la línea 9 se indica donde (variables) se guardarán los valores de dichas columnas según el orden en que aparecen (el valor de la columna id se guardará en la variable v_id, el de la columna alumno en la variable v_alumno).

Procedimiento 29 - “procedimiento2”

En el anterior ejemplo no ha habido ningún problema de ejecución pues cada una de las dos variables almacena sólo un ítem (el de la correspondiente columna), pues la consulta sólo devuelve una fila.

Ilustración 12. Resultado de la ejecución del procedimiento anterior

Pero ¿Qué ocurriría si la consulta devolviera más de una fila? Puedes probarlo a partir del anterior procedimiento, creando otro denominado procedimiento3: Opción de menú File / Save As…, cambia “procedimiento2” por “procedimiento3” en las filas 3 y 4, elimina la línea 11 y coloca un punto y coma al final de la línea 10 (por lo que la consulta pasará a devolver las 5 filas de la tabla al no existir cláusula WHERE); a continuación ejecuta el procedimiento3. Te encontrarás en la parte inferior de la pantalla de resultado con este error:

Ilustración 13. Error: La consulta sólo debería devolver una fila

recordándote que no se puede obtener como resultado de la ejecución de una instrucción SELECT … INTO más de una fila. Dicho error provoca que la ejecución del procedimiento finalice y no continúe por lo que las siguientes instrucciones a la que provocó el que error (SELECT… no se ejecutarían (en este caso solo una, la instrucción visualiza las dos INTO) variables). Otra cuestión importante ¿Qué ocurriría si no devolviera ningún valor la instrucción SELECT como es el caso del procedimiento4 que viene a continuación

Página 34



Procedimiento 30 - "procedimiento4"

A diferencia de otros lenguajes de programación de bases de datos no provocaría situación de error severa similar a la anterior del procedimiento3. El procedimiento4 finalizaría mostrando los contenidos de las variables v_id y v_alumno, nulo, puesto que no existe el alumno de id 100 y por tanto la sentencia SQL no recupera ninguna fila.

Ilustración 14. La consulta no devuelve filas de la tabla

2.5.7 Sentencias preparadas. SQL dinámico De la misma manera que otros grandes gestores de bases de datos, MySQL soporta la capacidad de preparar sentencias SQL para que estas pueden ser ejecutadas varias veces de manera eficiente y segura debido a que disminuye considerablemente el tiempo de análisis y preparación de la instrucción que va a ser ejecutada así como puede prevenir el problema de seguridad del SQL inyectado mediante la utilización de los parámetros o variables BIND (ver ejemplos en PHP en anexo). La sintaxis para crear una sentencia preparada es: PREPARE nombre_sentencia FROM texto_sql donde el texto_sql contiene marcadores (carácter ?) para representar los valores que se utilizarán en el momento de ejecutar la instrucción SQL. La sentencia preparada anterior se ejecuta mediante: EXECUTE nombre_sentencia [USING variable [,variable...]] donde la cláusula USING se encarga de suministrar los valores para los marcadores especificados en la sentencia PREPARE. La forma de especificar estos valores es en forma de variables de usuario (prefijo @ ). La sentencia preparada se puede eliminar mediante: DEALLOCATE PREPARE sentencia_preparada

Página 35



Ej empl o de sent enci as pr epar adas mysql > USE TEST Dat abase changed mysql > PREPARE al umnos_ i nser t _ di nami c VALUES( ?, ?) " ; Quer y OK, 0 r ows af f ect ed ( 0. 00 sec) St at ement pr epar ed

FROM

" I NSERT

I NTO

al umnos

mysql > SET @i d=' 1000' ; Quer y OK, 0 r ows af f ect ed ( 0. 00 sec) m ysqly>OK, SET0 @ e=f' ect Al um 1000 ; Quer rnom owsbraf edno( 0. 00 'sec) mysql > EXECUTE al umnos_ i nser t _ di nami c USI NG @i d, @nombr e; Quer y OK, 1 r ow af f ect ed ( 0. 02 sec) mysql > SET @i d=' 1001' ; Quer y OK, 0 r ows af f ect ed ( 0. 00 sec) mysql > SET @nombr e=' Al umno 1001' ; Quer y OK, 0 r ows af f ect ed ( 0. 00 sec) mysql > EXECUTE al umnos_ i nser t _ di nami c USI NG @i d, @nombr e; Quer y OK, 1 r ow af f ect ed ( 0. 02 sec) mysql > DEALLOCATE PREPARE al umnos_ i nser t _ di nami c; Quer y OK, 0 r ows af f ect ed ( 0. 00 sec)

Los procedimientos almacenados no necesitan el mecanismo de sentencias preparadas puesto que las instrucciones que se contienen ya se encuentran listas y preparadas para su ejecución. De todas formas su utilización dentro de procedimientos puede tener sentido si de desea ejecutar SQL dinámico dentro de ellos (no puede hacerse ni dentro de funciones ni de triggers). Una instrucción SQL es dinámica si es construida en tiempo de ejecución a diferencia de las instrucciones “estáticas” que se construyen cuando se compila el procedimiento. El empleo por tanto de SQL dinámico tiene sentido cuando no se conoce por completo la sentencia SQL en el momento de la compilación y necesita ser completada mediante algún dato procedente de una entrada del usuario o de otra aplicación. Al siguiente procedimiento, que utiliza SQL dinámico, se le puede pasar cualquier instrucción SQL como argumento:

Procedimiento 31 sql_dinamico1

Página 36



La ejecución del procedimiento anterior eliminará los dos alumnos introducidos: Ilustración 15. Ejecutando el procedimiento sql_dinamico1

Nota: Cuidado con el ejemplo del procedimiento anterior, su finalidad es totalmente ilustrativa pero no debe utilizarse y mucho menos ser llamado desde otras aplicaciones PHP, ASP, Visual Basic… pues garantiza al 100% que ocurra el grave problema del SQL inyectado, permitiendo al que invoca el procedimiento cualquier operación de cualquier tipo sobre la base de datos. El SQL dinámico no se usa muy a menudo y debe utilizarse sólo en los casos en que sea necesario debido a que es más complejo y menos eficiente que el SQL estático. Debe emplearse para realizar tareas o implementar utilidades que no pueden realizarse de otra manera. Un ejemplo muy característico de su empleo por su grado de optimización y rapidez es para encontrar filas a partir de múltiples criterios de búsqueda:

Procedimiento 32 sql_dinamico2

Para averiguar los empleados del departamento 121 contratados antes de la década de los 60 cuyo salario sea superior a 300: Ilustración 16. Ejecución del procedimiento sql_dinamico2

Página 37



o la misma consulta anterior pero sin importar la fecha de ingreso: Ilustración 17. Ejecución del procedimiento sql_dinamico2

Observa cómo el procedimiento anterior espera tres parámetros, dos de tipo numérico y otro de tipo fecha aunque alguno de ellos pueda ser nulo. La posibilidad de poder inyectar una instrucción SQL utilizando los procedimientos de esta manera es prácticamente nula.

2.6 Cursores Es el instrumento que se utiliza cuando la sentencia SQL dentro del programa devuelve más de una fila como hemos visto en el apartado anterior. De esta manera, un cursor es una zona de memoria que contiene un conjunto de filas resultantes de una sentencia SQL con la ventaja de que podremos recorrer, visualizar y manipular una a una cada una de esas filas. Sintaxis: DECLARE nombre_del_cursor CURSOR FOR sentencia_select; Un aspecto muy importante a tener en cuenta es que los cursores se declaran en los procedimientos u otros programas almacenados después de la declaración de variables, no hacerlo así producirá una situación de error.

2.6.1 Sentencias utilizadas con cursores. Ejemplos Veamos antes el siguiente ejemplo:

Procedimiento 33 cursor1

Página 38



Las sentencias asociadas al cursor se describen a continuación. Más adelante encontrarás una explicación gráfica que te ayudará a comprender mejor el funcionamiento de los cursores así como del procedimiento anterior: •

•

•

•

Declaración del cursor: En las líneas 8 a 11 se declara el cursor. Apertura del cursor: Cláusula OPEN (línea 12). El cursor se inicializa trayendo a memoria las filas (conjunto activo) que cumplen la condición del cursor, en este caso sólo 2 filas por la cláusula WHERE de la línea 11. El puntero del cursor se queda apuntando al comienzo del conjunto de filas recuperadas. Recuperación de las filas del cursor. Cláusula FETCH (línea 14): Cada vez que se ejecuta recupera la siguiente fila a la que apunta el cursor y avanza el puntero una posición. Cada fila recuperada la va almacenando en las variables v_id y v_alumno. Cierre del cursor: Cláusula CLOSE (línea 19). Desactiva el cursor liberando la zona de memoria ocupada por el cursor.

Puedes ver una ejecución más clara del procedimiento anterior abriendo una ventana cliente sin salir de la herramienta MySQL Query Browser mediante la opción de menú Tools / MySQL Command Line Client: mysql > cal l cur sor 1( ) ; +-- -- - -+-- -- -- -- - -+ | v_i d | v_al umno | +-- -- - -+-- -- -- -- - -+ | 1 | al umno 1 | +-- -- - -+-- -- -- -- - -+ 1 r ow i n set ( 0. 00 sec) +-- -- - -+-- -- -- -- - -+ | v_i d | v_al umno | +-- -- - -+-- -- -- -- - -+ | 2 | al umno 2 | +-- -- - -+-- -- -- -- - -+ 1 r ow i n set ( 0. 01 sec) ERROR 1329 ( 02000) : No pr ocess ed

dat a

-

zer o

r ows

f et ched,

sel ect ed,

or

¿Qué ha ocurrido realmente?

2 3

Página 39



Si observas en el esquema anterior, el recorrido de un cursor se asemeja mucho al recorrido de un fichero secuencial, produciéndose error cuando se intenta leer un registro y previamente se ha alcanzado el final del fichero (“ERROR 1329 (02000): No data - zero rows fetched, selected, or processed” ) La ejecución, reflejada paso a paso en el esquema, es la siguiente: Recuadro 1: Apertura del cursor (línea 12 del procedimiento anterior). Se traen a memoria las dos filas que cumplen la declaración del cursor. El puntero (flecha) del cursor apunta al comienzo de la primera fila. Recuadro 2: Primera vez que se ejecuta en el bucle la sentencia FETCH (línea 14). Se recupera la siguiente fila apuntada por el cursor, en este caso la primera, almacenando el valor de la primera columna (1) en la variable v_id y el valor de la segunda columna (alumno1) en la variable v_alumno. El puntero del cursor avanza una posición. Antes de finalizar el bucle se muestran los valores de las variables (línea 17)

Ilustración 18. Visualización de la primera fila del cursor

Recuadro 3: Segunda vez que se ejecuta en el bucle la sentencia FETCH (línea 14). Se recupera la siguiente fila apuntada por el cursor, en este caso la segunda, almacenando el valor de la primera columna (2) en la variable v_id y el valor de la segunda columna (alumno2) en la variable v_alumno. El puntero del cursor avanza una posición. Antes de finalizar el bucle se muestran los valores de las variables (línea 17):

Ilustración 19. Visualización de la segunda fila del cursor

Recuadro 4: Tercera vez que se ejecuta en el bucle la sentencia FETCH (línea 14). Se intenta recuperar la siguiente fila apuntada por el cursor pero no hay ninguna fila que recuperar pues el cursor ha finalizado, por tanto se produce la situación de error: Ilustración 20. Error en el cursor

Para tratar este error, el intento de recuperar una fila habiendo llegado ya al final del cursor, definiremos un manejador de error; los manejadores de error y el tratamiento de errores se estudiarán en un apartado próximo pero ya se avanza una pequeña parte del mismo para poder trabajar este punto: DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_ultima_fila=1;

Página 40




Explicación del procedimiento cursor2: 1. Con la instrucción de la línea 13 estamos declarando un manejador de error para el error NOT FOUND, que es el que se produce cuando se intenta recuperar una v_ultima_fila

fila habiendo al el final del cursor. Seproduzca utiliza una variable ( de nuestro) que tomará el llegado valor 1 en momento que se el error. Dentro programa podremos preguntar por su valor. 2. Cuando se produzca la situación de error, la ejecución del programa continuará (debido a la cláusula CONTINUE de la línea 13) y no finalizará en el punto donde se encuentra el error. En el momento en que la instrucción de la línea 16 (FETCH … INTO) falle por no poder leer ninguna fila del cursor, la variable v_ultima_fila tomará el valor 1 y la ejecución del procedimiento continuará por la línea siguiente al error (en este caso por la línea 17) para abandonar el bucle. Puedes ejecutar el programa desde la línea de comandos y comprobarás que ya no se finaliza el programa tras el error como en el procedimiento cursor1, puesto que en el momento en que se intente leer una fila del cursor posterior a la última, la condición de la línea 17 será cierta y por tanto la ejecución del programa abandonará el bucle (línea 18) y continuará por la línea 22 (recuerda que si el error no se hubiera tratado el programa hubiera finalizado y terminaría en el mismo punto en que se produjera el error).

Página 41



Ilustración 21. Ejecución correcta del cursor

El mismo recorrido del cursor pero con la instrucción iterativa REPEAT… UNTIL:

Procedimiento 35 cursor2 utilizando la instrucción REPEAT UNTIL

y con la instrucción WHILE:

Procedimiento 36 cursor2 utilizando la instrucción WHILE

Es muy habitual que las filas a recuperar del cursor dependan de un dato que se pasa como argumento. El procedimiento siguiente es el mismo que el procedimiento cursor2 salvo que el número de filas a recuperar viene determinado por el parámetro p_id que se le pasa al procedimiento (línea 4) y por tanto también se modifica la línea 12:

Página 42



Procedimiento 37 cursor2_1

La llamada al procedimiento: mysql> CALL cursor2_1(4); producirá como resultado la visualización de las 4 primeras filas de la tabla alumnos.

2.6.2 Cursores anidados En algunas ocasiones, del mismo modo que ocurre con bloques de instrucciones, será necesario incluir un cursor dentro de otro cursor. P.ej un cursor que recorriera las filas de todos los Centros y a su vez, por cada centro, otro cursor que recorriera todos los departamentos del mismo: Cent r o: 10 - SEDE CENTRAL Depar t ament o: DI RECCCI ON GENERAL Depar t ament o: ORGANI ZACI ON Depar t ament o: PERSONAL Depar t ament o: PROCESO DE DATOS Depar t ament o: FI NANZAS +--- --- --- --- --- --- --- --- --- --- --+ TOTAL DEPARTAMENTOS DEL CENTRO +--- --- --- --- --- --- --- --- --- --- --+ | 5 +--- --- --- --- --- --- --- --- --- --- --+ Cent r o: 20 - RELACI ON CON CLI ENTES Depar t ament o: DI RECCI ON COMERCI AL Depar t ament o: SECTOR I NDUSTRI AL Depar t ament o: SECTOR SERVI CI OS +--- --- --- --- --- --- --- --- --- --- --+ | TOTAL DEPARTAM ENTOS DEL CENTRO +--- --- --- --- --- --- --- --- --- --- --+ | 3 +--- --- --- --- --- --- --- --- --- --- --+

|

|

Página 43



El procedimiento quedaría:


Es muy importante señalar en el procedimiento anterior y cuando se utilicen cursores anidados que antes de leer una nueva fila del cursor externo, hay que resetear la variable utilizada para controlar el final del cursor (línea 38) para que el final del cursor interno no provoque un final del cursor externo sin haber terminado este último. Antes de terminar este apartado indicar que existe un tipo especial de cursores de actualización que se tratan en el tema siguiente de transacciones.

2.7 Manejo de errores 2.7.1 Introducción a la gestión de errores En general, si una sentencia SQL falla dentro de un programa almacenado se produce una situación de error, se interrumpe la ejecución del programa en ese punto y finaliza salvo en el caso de que el programa que falla hubiera sido llamado por otro; en ese caso la ejecución continua por el programa que llamó a este programa que ha causado el Página 44



error; ocurriría lo mismo si en lugar de un programa que es llamado desde otro programa nos encontráramos con un bloque interno dentro de otro bloque más externo; si se produjera error en el bloque anidado interno, la ejecución del programa se interrumpiría en el bloque interno para continuar por el externo. Como hemos visto anteriormente, este comportamiento se puede controlar definiendo los manejadores de error o handlers. Una handler es un bloque de instrucciones SQL que se ejecuta cuando se verifica una condición tras una excepción (error) generada por el servidor. Sintaxis: DECLARE {CO NTI NUE | EXI T | UNDO} HANDLER FOR {SQLSTATE sqlstate_code | MySQL error

code

|

nombre_condición}

instrucciones_del_manejador

Deben declararse después de las declaraciones de variables y cursores ya que referencian a estos en su declaración. El manejador puede ser de tres tipos: •

•

•

CONTINUE: La excepción o error generado no interrumpe el código del procedimiento. EXIT: Permite poner fin al bloque de instrucciones o programa en el que se genera la excepción. UNDO: No está soportada por el momento.

La condición de error del manejador puede expresarse también de 3 formas: • • •

Con un código estándar ANSI SQLSTATE. Con un código de error MySQL. Una expresión.

El manejador de error indica mediante un conjunto de instrucciones lo que hay que hacer en caso de que se produzca ese error. Ejemplos de situaciones de error:

Procedimiento 39 error1

El anterior procedimiento recibe dos argumentos, identificador de alumno y nombre del alumno y los inserta en la base de datos. Como modifica la base de datos se intercala la cláusula de la línea 5. Página 45



Si hacemos una llamada al anterior procedimiento desde la ventana cliente o desde la propia herramienta MySQL Query Browser:

Instrucción 2. Llamando al procedimiento error1

se puede comprobar viendo la tabla o realizando una consulta que añade al anterior alumno. ¿Qué ocurre si intentamos insertar un alumno de clave primaria repetida? CALL error1(6, 'Raquel M. Carrera'); Aparecerá un error con un número de error (1062) y una descripción (“entrada duplicada”) advirtiéndonos de tal situación; evidentemente no se realiza la inserción y la ejecución del programa se detiene y finaliza (pues no retorna a ningún otro programa ya que no hay ningún otro que lo llamó). Tomando nota del número de error y modificando el anterior procedimiento para añadir dicho control de error:


Una llamada al programa como: CALL error2(6, 'Raquel M. Carrera'); Producirá la siguiente salida: Resaltar que el error ha sido tratado y por tanto no finaliza la ejecución del programa en el momento en que se produce y si hubiera más líneas de código detrás de la línea 9, éstas se ejecutarían debido al tipo de manejador, CONTINUE; esto no ocurriría si el error no fuera tratado como hemos comprobado en el procedimiento error1 anterior ¿Qué ocurre si intentamos introducir un alumno de clave primaria nula? CALL error2(NULL, 'Mario A. Carrera'); Habrá que considerar y tratar también el error 1048 que se produce:

Página 46




Llegados a este punto podemos utilizar un tercer argumento en el procedimiento, un parámetro de tipo OUT, para que actúe como flag o indicador de cómo ha ido la ejecución del procedimiento:


Llamando al anterior procedimiento error4 desde el interior de otro (línea 7 de error5):


La primera ejecución del procedimiento: CALL error5( ) funcionará correctamente, apareciendo como fila/columna resultante : ‘Alumno dado de alta’. La segunda ejecución del mismo procedimiento anterior también funcionará correctamente pues la situación de error de código repetido es tratada y por tanto el programa finaliza correctamente y visualiza por pantalla que el número de matrícula ya existe. Volviendo otra vez a la declaración de un manejador, hemos visto que tiene 3 partes que pasaremos a ver a continuación más en detalle: 1. Tipo de manejador: CONTINUE o EXIT. 2. Condición del manejador: SQLSTATE sqlstate_code | MySQL

error code

|

nombre_condición.

3. Acciones o instrucciones que realiza el manejador.

Página 47



2.7.2 Tipos de manejador •

•

EXIT: El programa que causa el error finaliza, devolviendo el control al programa que le llamó. Si se produce en un bloque interno, entonces el control de la ejecución pasa al externo. CONTINUE: La ejecución continúa por la siguiente línea de código que causó el error.

En ambos casos el error es tratado por lo que la ejecución del programa puede considerarse correcta y antes de realizarse la opción CONTINUE o EXIT se ejecuta el conjunto de instrucciones asociados al manejador. Ejemplo de utilización del manejador EXIT:

Procedimiento 44. error6

Observa como en este caso hay dos bloques de instrucciones BEGIN…END. El manejador de errores con la opción EXIT está integrado con el bloque más interno. La llamada al procedimiento anterior: CALL error6(8, 'Fernando Carrera'); Dará como resultado la inserción del alumno y su mensaje correspondiente de alumno dado de alta. CALL error6(8, 'Conchita Martín'); dará como resultado un aviso indicando que el número de matrícula 8 ya existe. La instrucción de la línea 11 falla (no se lleva a cabo por tanto la inserción) puesto que no puede haber el doserror filasdedistintas con el mismo valor campolaidinstrucción (al ser claveasociada primaria) provocando clave repetida que hace queenseelejecute al manejador (línea 10). Como el tipo de manejador es de tipo EXIT, se interrumpe la ejecución del programa y se sale del bloque interno (si sólo hubiera habido un bloque se hubiera salido del programa); por tanto la instrucción de la línea 12 no llega a ejecutarse y la ejecución del programa sigue por la línea 15. Ejemplo de utilización del manejador CONTINUE. Observa que a diferencia del anterior caso, en este procedimiento no hay más que un bloque: Página 48



Procedimiento 45. error7

CALL error7(9, 'Pablo Martínez') ; dará como resultado la inserción del alumno Pablo y su mensaje correspondiente de alumno dado de alta. Como no falla la instrucción la expresión de la línea 11 es falsa y se ejecutará la línea 14. CALL error7(9, 'Luis Hueso') ; dará como resultado una aviso indicando que ese número de matrícula ya existe. Al intentar hacer la inserción de la línea 10 el procedimiento provoca una situación de error (igual que para el caso anterior de la cláusula EXIT) que es tratada inmediatamente después en el manejador de error poniendo a 1 el valor de la variable v_clave_repetida; por tanto, al ser tratado el error y de forma continua (cláusula CONTINUE línea 8) la ejecución del procedimiento continua por la línea 11 haciendo cierta la condición y avisando de la duplicidad de dicha matrícula. ¿Qué tipo de manejador usar? ¿EXIT o CONTINUE? Algunos lenguajes de programación de otros sistemas gestores de bases de datos incluyen por defecto sólo la opción EXIT (de todas maneras en estos casos se puede simular una opción CONTINUE colocando la instrucción propensa a causar error dentro de un bloque BEGIN…END). Si la lógica del programa obliga a abandonar la ejecución del mismo si se produce un error entonces se puede utilizar EXIT. En caso de que esté contemplado en la lógica del programa que se pueden ejecutar otras alternativas si se produce error podemos utilizar la cláusula CONTINUE.

2.7.3 La condición del manejador Has 3 formas posibles de indicar cuando debe ser invocado el conjunto de instrucciones del manejador de error para tratarlo. 1) Número de error de MySQL. 2) Código SQLSTATE estándar ANSI. 3) Condiciones de error con nombre.

Página 49



1) Número de error de MySQL, propio de MySQL, que dispone de un conjunto particular de números de error. Ej.: DECLARE CONTINUE HANDLER FOR 1062 SET v_clave_repetida=1; El número de error 1062 está asociado en MySQL al intento de almacenar un registro de clave duplicada. 2) Código SQLSTATE estándar ANSI: A diferencia del anterior, SQLSTATE no es definido por MySQL sino que es un estándar y por tanto el mismo error tiene asociado el mismo SQLSTATE independientemente del gestor de bases de datos utilizado: MySQL, Oracle, DB2, Microsoft SQL Server. En los anteriores gestores de bases de datos el SQLSTATE 23000 está asociado al error de clave duplicada. Por tanto en MySQL el anterior manejador de error y el siguiente son similares: DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET v_clave_repetida=1; ¿Cuál utilizar de los dos anteriores? En teoría el segundo permitiría una mayor portabilidad a otros gestores de bases de datos pero en la práctica los lenguajes de programación para Oracle y Microsoft SQL Server son incompatibles con el de MySQL por lo que no hace muy atractivo el uso de esta segunda opción (DB2 de IBM es “algo” compatible pues tanto DB2 como MySQL están basados en el estándar SQL:2003). Además hay códigos SQLSTATE que no corresponden con un código de error de MySQL sino a varios (para estos casos se utiliza el SQLSTATE genérico ‘HY000’) Por lo anteriormente expuesto seguiremos utilizando en este curso los códigos propios de error de MySQL. Para saber el código de un error, se puede averiguar de varias formas: 1. Como en otros lenguajes, provocarlo para obtener su número de error que luego utilizaremos en el manejador del error. Ej.: Hemos visto en páginas anteriores de este curso el error que se produce cuando se intenta leer una fila de un cursor y ya existen más: El 1329 indica el número de error de MySQL y entre paréntesis, en este caso 02000, el número SQLSTATE correspondiente. 2. Mirando el manual, suele venir una tabla detallada de errores con su número y descripción en un apéndice dedicado. 3) Condiciones de error con nombre 3.1) Predefinidas de MySQL SQLEXCEPTION SQLWARNING NOT FOUND Página 50



Ejemplos: /* Si ocurre cualquier condición de error (salvo la excepción NOT FOUND tratada en el apartado de cursores) la variable v_error valdrá 1 y continuará la ejecución del programa */ DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET v_error=1; /* Si ocurre cualquier condición de error (excepto la condición NOT FOUND) el procedimiento finaliza ejecutando antes la instrucción ROLLBACK (deshacer operaciones que se hubieran realizado) y otra de advertencia de la situación de error */ DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT 'Ocurrió un error. Procedimiento terminado'; END; /* Si la instrucción FETCH en un cursor no recupera ninguna fila*/ /* De 3 formas distintas: Condición de error con nombre, SQLSTATE y código de error de MySQL /* DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_ultima_fila=1; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET v_ultima_fila=1; DECLARE CONTINUE HANDLER FOR 1329 SET v_ultima_fila=1; •

•

•

SQLWARNING es un subconjunto de los códigos SQLSTATE, representando todos los códigos que empiezan por 01. NOT FOUND es un subconjunto de los códigos SQLSTATE, representando todos los códigos que comienzan por 02. SQLEXCEPTION es un subconjunto de los códigos SQLSTATE, representando todos los códigos que no comienzan ni por 01 ni por 02.

3.2) Definidos por el usuario Facilitan lectura del código y por tanto el fácil mantenimiento de la aplicación. Consiste en “bautizar” o darle un nombre a un código de error MySQL o SQLSTATE. Si intentas ejecutar el siguiente código que forma parte de un procedimiento:

Página 51



Bloque 1

Verás que produce una situación de error pues la tabla ya existe:

En la imagen siguiente, al 6) mismo tratamos el aerror MySQL anterior le asociamos un nombre (línea para tiempo facilitarque su comprensión la hora de realizar tareas como la de mantenimiento de la aplicación como se ha señalado. De esta manera ya se puede realizar la declaración del manejador de error de la línea 7.

Bloque 2

2.7.4 Orden de actuación del manejador Ante varias posibilidades de ejecutarse un manejador ¿Quién se ejecuta? Siempre el manejador asociado al error MySQL en primer lugar (en este caso líneas 1112 del bloque 3 siguiente). Si éste no estuviera declarado y definido entonces se ejecutaría el manejador asociado al código SQLSTATE. En último lugar el manejador asociado al manejador SQLEXCEPTION.

Bloque 3

El orden programas. de actuación los manejadores puede facilitarnos una forma trabajo en nuestros Losdeerrores de código MySQL más habituales podrándeser tratados en manejadores específicos y aquéllos que no sean considerados podrán ser atrapados en un manejador SQLEXCEPTION (equivaldría a la parte ELSE de una sentencia CASE que atraparía todo lo que no ha sido “filtrado”). En el siguiente ejemplo bloque 4 se declara un manejador para el tratar la excepción de clave duplicada (líneas 9 a 10), lo que ocurre es que el error producido es el código de

Página 52



error MySQL 1048 (el primer campo de la tabla no puede ser nulo) y no el 1062 como consecuencia de la línea 13. En esta situación se ejecuta el manejador SQLEXCEPTION (líneas 11-12)

Bloque 4

Se podría hacer uso de una función del tipo “err_code( )” que indique el código de error que se ha producido o una variable que almacenara el código de error y su mensaje. Esta función está incluida en los lenguajes de otros gestores de bases de datos y en otros (p.ej PHP) pero todavía no en MySQL (se espera para la versión 5.2, la especificación SQL:2003 si que lo incluye). También se echa en falta por el momento (aparecerá en la versión 5.2) la posibilidad de que el usuario provoque intencionadamente situaciones de error (sentencias del tipo SIGNAL, RAISE…) y poder atenderlas dentro de manejadores de error propios.

2.7.5 Ámbito de actuación del manejador Como hemos visto los manejadores actúan en todos aquellos bloques donde se han declarado; su alcance llega también a los bloques anidados. Ej.:

Bloque 5

Aunque la excepción se produce en el bloque interno (líneas 14 a 16), será atrapada por el manejador declarado en un nivel superior (bloque externo):

Si se atrapa en un bloque interno, ya no se propaga a la del bloque superior externo:

Página 53



Bloque 6

El comportamiento de los manejadores de error entre bloques internos y externos es similar al comportamiento de un procedimiento que llama a otro (el primer procedimiento actuaría como “externo” y el llamado como “interno”)

2.7.6 Ejemplo de tratamiento de errores El siguiente ejemplo resume todo lo tratado en este apartado 2.7. No se incluye el manejador de errores NOT FOUND pues en el procedimiento no se utilizan cursores.


Página 54



Ilustración 22. Probando el procedimiento 44 anterior

2.8 Funciones 2.8.1 Creación de funciones. Diccionario de Datos La sintaxis completa de creación de funciones es: nombre_función ([parametro1[,...]]) CREATE FUNCTION tipo_de_datos RETURNS [LANGUAGE SQL] [ [NOT] DETERMINISTIC] [ {CONTAINS SQL | MODIFIES SQL DATA | READS SQL DATA | NO SQL} ] [SQL SECURITY {DEFINER | INVOKER} ] [COMMENT comentario] bloque_de_instrucciones_de_la_función

Página 55



Como se ha visto en el tema de procedimientos, las funciones almacenadas son similares a los procedimientos salvo que sólo devuelven un valor. Por tanto gran parte de lo que se ha expuesto en el apartado de procedimientos es válido aquí. Los permisos que hay que poseer para crear funciones así como la información que proporciona el diccionario de datos se han expuesto en el apartado de procedimientos. La devolución del valor se produce utilizando la cláusula RETURN y no mediante variables OUT o INOUT. La ventaja que presentan las funciones es que pueden ser utilizadas dentro de instrucciones SQL y por tanto aumentan considerablemente las capacidades de este lenguaje. La utilización de funciones posibilita realizar una código más fácil de leer y mantener, pues agrupa en una función un conjunto de operaciones relacionadas lógicamente entre sí (fórmulas, reglas de negocio…). Además consultas SQL complejas pueden abreviarse con el empleo de funciones. La mayoría de las opciones de la sintaxis anterior son idénticas a las vistas en el tema de procedimientos. Además de ello: -

La cláusula RETURN es obligatoria e indica el tipo de dato que devuelve la función. No aparece en la sintaxis, pues no se puede utilizar, los parámetros IN, OUT, INOUT. Todos los parámetros pasados a la función se definen implícitamente del tipo IN. Dentro del cuerpo de la función debe aparecer por lo menos una instrucción RETURN, que devuelve el resultado de la función al programa llamado y finaliza su ejecución. Si la ejecución de la función llegara al final de la misma sin haber encontrado una instrucción RETURN, causaría un error. El seguimiento y mantenimiento de la función será mucho más simple si sólo aparece una sentencia RETURN dentro de la función; en ese caso habrá que utilizar una variable para conseguirlo.

2.8.2 Ejemplos de utilización de funciones La función funcion1 devuelve en euros las pesetas que se le pasan como argumento.

Función 1 "funcion1"

A continuación se indica cómo llamar a la función anterior creada en la base de datos test:

Página 56



mysql > SELECT t est . f unci on1( 10000) ; +-- -- -- -- -- -- -- -- -- -- -- + | t est . f unci on1( 10000) | +-- -- -- -- -- -- -- -- -- -- -- + | 60. 10 | +-- -- -- -- -- -- -- -- -- -- -- + 1 r ow i n set , 1 war ni ng ( 0. 00 sec)

Ídem de antes pero dejando el resultado de la función en una variable: mysql > SET @v_eur os=t est . f unci on1( 10000) ; Quer y OK, 0 r ows af f ect ed, 1 war ni ng ( 0. 00 sec) mysql > SELECT @v_ eur os; +- - - - - - - - - - + | @v_eur os | +- - - - - - - - - - + | 60. 10 | +- - - - - - - - - - + 1 r ow i n set ( 0. 00 sec)

En el siguiente ejemplo la función realiza la conversión de pesetas a euros y viceversa: Si el primer argumento es un 1 entonces convertirá a euros, si es un 2 convertirá a pesetas:


Ejemplo de ejecución de la función anterior; mysql > SELECT t est . f unci on2( 2, 6) ; +-- -- -- -- -- -- -- -- -- -- -+ | t est. f unci on2( 2, 6) | +-- -- -- -- -- -- -- -- -- -- -+ | 998. 32 | +-- -- -- -- -- -- -- -- -- -- -+ 1 r ow i n set , 1 war ni ng ( 0. 00 sec)

La función 2 puede mejorarse dejando un solo RETURN (se ejecutará siempre al ser la última instrucción de la función) y controlando que los dos únicos parámetros que se le pasen son 1 o 2 (si no es así la función devolverá NULL):

Página 57




Llamando a la función anterior desde dentro de un procedimiento:

Procedimiento 47 f1

Si en lugar del cuerpo de un procedimiento se utiliza una instrucción SELECT para llamar a la función:

Instrucción 3

La anterior sentencia se puede simplificar a partir de la siguiente función:


Página 58



Por lo que quedaría reducida a:

Instrucción 4

Si se desea obtener el número de empleados de un departamento que se le pasa como argumento:


La función 5 anterior más simplificada:


Para obtener el nombre de un alumno cuyo número de identificación se pasa como parámetro (devuelve nulo si este no existe) puede emplearse la siguiente función:


Página 59



2.8.3 Modificación de funciones Sintaxis: ALTER FUNCTI ON nombre_función {CONTAI NS SQL | NO SQL | READS SQL DATA | MODI FI ES SQL DATA} | SQL SECURI TY {DEFI NER| I NVOKER} | COMMENT comentario

Se debe poseer el privilegio ROUTINE modificar procedimientos y funciones. El usode deALTER las distintas opcionespara se hapoder expuesto en el apartado de creación de procedimientos.

2.8.4 Borrado de funciones Sintaxis: DROP FUNCTION [IF EXISTS] nombre_función Al igual que ocurre para la modificación, se debe poseer el privilegio de ALTER ROUTINE para poder borrar procedimientos y funciones.

2.9 Triggers 2.9.1 Creación de triggers. Diccionario de Datos Los triggers, también llamados desencadenadores o disparadores, son programas almacenados que se ejecutan (“disparan”) automáticamente en respuesta a algún suceso que ocurre en la base de datos. En MySQL ese tipo de suceso se corresponde con alguna instrucción DML (INSERT, UPDATE, DELETE) sobre alguna tabla. Suponen un mecanismo para asegurar la intregridad de los datos. Se emplean también como un método para realizar operaciones de auditoría sobre la base de datos. No hay que abusar de su utilización pues ello puede traer consigo una sobrecarga del sistema y por tanto un bajo rendimiento del mismo. Al final de este apartado verás ejemplos de su utilización que ayudarán a comprender su utilización. Sintaxis: CREATE [DEFINER={cuenta_usuario | CURRENT_USER}] TRIGGER nombre_trigger

{BEFORE | AFTER} {UPDATE | INSERT | DELETE} ON tabla FOR EACH ROW

cuerpo_del_trigger

Página 60



Para poder crear triggers, en versiones anteriores a la 5.16 se necesita el privilegio SUPER. A partir de esta el privilegio es el de TRIGGER. No se pueden crear ni sobre una tabla temporal ni sobre una vista, solamente sobre tablas. Aspectos a comentar de la sintaxis anterior: - DEFINER ={cuenta_usuario | CURRENT_USER } indica con qué privilegios se ejecutan las instrucciones del trigger. La opción por defecto es CURRENT_USER, que indica que las instrucciones se ejecutan con los privilegios del usuario que lanzó la instrucción de creación del trigger. La opción cuenta_usuario por otro lado hace que el trigger se ejecute con los privilegios de dicha cuenta. - Nombre del trigger. Sigue las mismas normas que para nombrar cualquier objeto de la base de datos. - BEFORE | AFTER. Señala cuando se ejecuta el trigger, antes (before) o después (alter) de la instrucción DML que lo provocó. - UPDATE | INSERT | DELETE. Define la operación DML asociada al trigger. - ON tabla. Define la tabla base asociada al trigger. - FOR EACH ROW. Indica que el trigger se ejecutará por cada fila de la tabla afectada por la operación DML. Esto es, si tenemos asociado un trigger a la operación de borrado de una tabla y se eliminan con una sola instrucción 6 filas de ésta última, el trigger se ejecutará 6 veces, una por cada fila eliminada. Otros gestores de bases de datos (así como futuras implementaciones de MySQL) consideran también el otro estándar de ANSI, la cláusula FOR EACH STATEMENT. Con esta segunda opción, el trigger se ejecutaría por cada operación DML realizada; en el ejemplo anterior, la instrucción de borrado daría lugar a que sólo se ejecutara el trigger una sola vez en lugar de 6 (filas afectadas) con esta futura cláusula. - Cuerpo del trigger: El conjunto de instrucciones que forman este programa almacenado. Si son más de una irán en un bloque BEGIN … END. No pueden contener una instrucción CALL de llamada a un procedimiento almacenado. Referencias a las columnas afectadas Dentro del cuerpo del trigger se puede hacer referencia a los valores de las columnas que están siendo modificadas con una sentencia DML (la que provocó que se disparara el trigger), incluso pueden cambiarse si así se considerara. Para ello se utilizan los objetos OLD. Demicolumna esta manera, un trigger de tipo OLD.micolumna BEFORE UPDATE afectandoNEW a unay columna , se en utilizará la expresión para conocer el valor de la columna antes de ser modificado y NEW.micolumna será el nuevo valor de la columna después de la modificación. Estos dos valores sólo tienen sentido los dos juntos en una modificación, pues ante una inserción (INSERT) no existe valor antiguo (OLD) y ante un borrado (DELETE) no existe un valor nuevo (NEW) pues el que existe se elimina. Ver ejemplo trigger1 del apartado 2.9.3.

Página 61



Dentro de un trigger de tipo BEFORE, puede cambiarse el nuevo valor mediante una sentencia de asignación SET por lo que anularía por completo el efecto de la instrucción DML que provocó el trigger. Eventos de disparo Como se ha indicado, un trigger se ejecuta automáticamente (dispara) antes o después de una instrucción DML: INSERT, UPDATE o DELETE. Además de la forma explícita anterior, pueden también ejecutarse las instrucciones del cuerpo del trigger si la modificación se produce de forma implícita, como sería el caso de una instrucción REPLACE pues en realidad equivale a una instrucción DELETE seguida de una INSERT (por lo tanto aquí se ejecutarían los triggers asociados a estas dos operaciones). ¿Triggers de tipo before o after? Prácticamente no hay diferencia. La ventaja que puede suponer utilizar los triggers de tipo BEFORE es que pueden cambiarse los valores modificados inicialmente por una instrucción UPDATE o INSERT mientras que con los triggers de tipo AFTER daría un error de ejecución. Teniendo en cuenta los 2 tipos de triggers y las tres operaciones DML distintas, podríamos tener tener 6 triggers por tabla: BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, BEFORE DELETE y AFTER DELETE. La vista TRIGGERS del diccionario de datos contendrá toda la información sobre cada trigger, existiendo una entrada (fila) en esta vista por cada trigger en el servidor; la información que aparece en las columnas es la que se ha tenido en cuenta a la hora de crearlo.

Ilustración 193. Vista triggers del diccionario de datos

2.9.2 Borrado de triggers Sintaxis: DROP TRIGGER nombre_trigger Se necesita el privilegio SUPER para borrar triggers.

Página 62



2.9.3 Utilización de triggers Ejemplos de utilización de triggers: Copias o réplicas de datos: Finalidad: Mantener sincronizada una copia de seguridad de unos datos. Para probar el ejemplo que viene a continuación lanzar antes la tabla: CREATE TABLE alumnos_replica (id INT PRIMARY KEY, alumno VARCHAR(30)) ENGINE=innodb; Puedes volver a crear si así lo deseas la tabla alumnos mediante el procedimiento1.sql. Si creas el siguiente trigger:

Trigger 1 "trigger1"

podrás comprobar que la siguiente instrucción: Instrucción 5

crea una fila nueva en la tabla alumnos como consecuencia de la instrucción 5 anterior y otra también idéntica en la tabla alumnos_replica debido a la ejecución automática del conjunto de instrucciones del trigger. Otras situaciones en las que se pueden emplear triggers podrían ser para llevar el mantenimiento del stock de artículos: En el momento en que se haga un pedido, se decrementa automáticamente el número de unidades pedidas de las existencias de ese artículo. Auditoría: Finalidad: Auditar las operaciones que se realizan sobre una tabla. Antes de lanzar el siguiente ejemplo, utilizaremos la tabla:

Página 63



CREATE TABLE AUDITA (mensaje VARCHAR(200)); Si editas el siguiente trigger:

Trigger 2 trigger 2

la instrucción siguiente:

Instrucción 6

modificará la columna alumno de la tabla alumnos dejando a Mario como nuevo nombre y por efecto del trigger creará la siguiente entrada en la tabla audita:

Ilustración 204. Utilización de triggers para auditar las operaciones en la tabla

En relación a este último trigger ¿Qué pasaría si el nombre del alumno fuera nulo? Validación de datos Además de las constrainsts o restricciones que se definen en el momento de crear las tablas, podemos utilizar los triggers para validar los datos a almacenar en una tabla y de esta manera mantener la consistencia de los mismos. Ejemplos de control de entrada de datos: -

El valor de una columna no debe ser negativo y estar comprendido entre 1 y 80. Un empleado que no sea vendedor no puede tener comisión.

- Un … empleado no puede ser jefe de si mismo. Siempre que nos encontremos con alguna situación similar a las anteriores no debemos dejar que se realice la modificación de la tabla. Un aspecto importantísimo a tener en cuenta es que la combinación operación DML + trigger asociado o trigger asociado + operación DML. Si el trigger falla, entonces falla la operación DML que intenta modificar la tabla de la base de datos. Esto puede ser útil para evitar entradas indeseadas en las tablas de la base de datos. De esta manera si en el Página 64



cuerpo del trigger se detecta que el dato no es correcto podría provocarse una situación de error que anulara y abortara la operación DML. El problema es que hasta la versión 5.2 por lo menos no aparecerá la instrucción SIGNAL (RAISE) que permita provocar errores como en otros gestores de bases de datos. No se puede utilizar SQL dinámico para simular una instrucción SIGNAL pues los triggers no admiten SQL dinámico. Otra estrategia a utilizar en este caso para forzar una situación de error y echar atrás el resultado de la sentencia DML que intenta modificar los datos es realizar una SELECT en el cuerpo del trigger que no recupere ninguna fila. Al ocurrir un error en el trigger, la combinación trigger + operación DML falla y por tanto aborta la ejecución de la operación DML (ídem si la combinación es operación DML + trigger). Supongamos que no se permite altas de alumnos con identificador negativo o cero como primera columna de la tabla alumnos. Bastará con que codifiquemos un trigger del tipo:

Trigger 3 "trigger3"

Si intentamosla realizar primeracon declave las negativa, dos siguientes inserciones quetanto vienen continuación, de una alumna el trigger fallará y por no sea llevará a cabo la operación DML que se ejecuta después (la inserción de la fila) como puede comprobarse después de listar la tabla alumnos. En cambio una inserción de un id superior a 0 hará que el trigger finalice correctamente no provocando situación de error y por tanto permitiendo después lanzar la operación DML de inserción:

Ilustración 25. Prueba del comportamiento de los triggers

Página 65



Anexo: MySQL desde otras aplicaciones 1 Introducción: Ventajas y desventajas Aunque sea de manera demostrativa, no se podía terminar este tema sin exponer cómo MySQL puede ser utilizado desde otros entornos y lenguajes de programación externos a él como son Access, PHP, Perl, Java, Python, C, C++ o .NET (C#, Visual Basic .NET) entre otros. Para ilustrar alguno de ellos se ha utilizado PHP 5.14 y la versión de Visual Basic Express 2005, por ser ampliamente utilizados entre la comunidad de programadores y por poderse obtener y utilizar de manera gratuita. La instalación y utilización de estos lenguajes así como los controladores de datos queda fuera del ámbito de este curso por lo que al alumno nunca se le va a exigir ninguno de los conceptos aquí expuestos en ningún tipo de ejercicio o prueba. Utilizar los elementos de MySQL desde otros entornos externos facilita: -

En entornos cliente / servidor, la carga en el lado del cliente se reduce considerablemente. Mantener actualizadas constantemente las versiones de las aplicaciones cliente es mucho más costoso que hacerlo si estas se encuentran centralizadas. Las aplicaciones cliente son más pequeñas al encontrarse desarrolladas muchas de las tareas en forma de programas almacenados. Una sola unidad de programa puede ser empleada por entornos totalmente diferentes como Java o .NET. Más facilidad en la portabilidad / escalabilidad de las aplicaciones cambiando la lógica solamente desde dentro los programas almacenados. Disminución del tráfico de red de al ejecutarse las operaciones en el servidor. Mecanismos de mayor seguridad evitando que el usuario acceda a la base de datos salvo a aquellos programas almacenados para los que esté autorizado.

Por otro lado, también puede dar lugar a la aparición de las siguientes desventajas: -

-

El rendimiento algunas operaciones (búsquedas con patrón, manejo de cadenas…) puede llegar a ser menor si se utilizan programas almacenados en lugar de hacerlo en otros lenguajes como PHP, Java o Perl. Pueden provocar una fragmentación lógica si una parte de la tarea se implementa con programas almacenados y la otra reside dentro de los programas cliente. Además depurar una aplicación puede llegar a ser dificultoso pues no existe la posibilidad de un solo depurador entre distintos entornos. Gran dificultad a la hora de portar o migrar los programas almacenados a otros gestores de bases de datos (sólo DB2 de IBM y MySQL cumplen el estándar ANSI para programas almacenados).

Página 66



2 Ejecución de procedimientos Tratamiento de errores

almacenados.

Los ejemplos que vienen a continuación realizan una llamada al procedimiento resultset1 visto en el apartado 2.5.5 de este tema (este último envía número de departamento, nombre y presupuesto de los departamentos cuyo centro se le pasa como argumento).

2.1 En PHP El script de este apartado recoge un número de Departamento mediante un formulario de entrada y manda el dato (id_centro) al procedimiento almacenado resulset1 para que este le envíe el número de departamento, nombre y presupuesto de los departamentos. Ej. de ejecución: Ilustración 216 (dcha.). Resultado de llamar al procedimiento resultset1 desde PHP

Página 67



Ilustración 227. LLamando a un procedimiento desde PHP

El mismo ejemplo pero sin hacer uso del procedimiento y ejecutando la sentencia Select directamente: Ilustración 238 (dcha). El mismo resultado pero sin hacer uso del procedimiento

Página 68



2.2 En Visual Basic Express 2005 La llamada al procedimiento vbnet1 que viene a continuación se realiza desde la ventana inmediato, los resultados se visualizan por la ventana de resultados:

Ilustración 249. Ejecución del procedimiento y resultados I mpor t s MySql . Data. MySql Cl i ent Modul e M odul e1 Sub vbnet 1( ByVal cent r o As I nt eger ) ' Proced i mi ent o que l l ama al proced i mi ent o MySQL r esul t set 1 ' envi ándol e el argument o cent r o que r eci be Di m host As Str i ng = "192. 168. 0. 5" Di m usuari o As Str i ng = "root" Di m cl ave As Str i ng = "------" Di m base_dat os As Str i ng = "test" Di m cadena_c onexi on As Str i ng = " Dat abase=" & base_ dat os & _ " ; Dat a Sourc e=" & host & _ "; User I d=" & usuari o & " ; Passwor d=" & cl ave ' Cr eaci ón de l a conexi ón Di m Mi Conexi on As New MySql Connect i on( cadena_conexi on) Tr y

Mi Conexi on. Open() ' Cr eaci ón de un nuevo comando Di m i nstr ucci on As M ySql Command = New M ySql Command( "r esul t set 1" , Mi Conexi on) ' El nuevo comando es de t i po pr ocedi mi ent o al macenado i nst r ucci on. CommandType = CommandType. St or edPr ocedur e ' Cr eaci ón del par ámet r o que se envi ar á como argument o Di m paramet r o1 As MySql Par amet er ' Asoci ando el par ámet r o cr eado de VB 2005 al ar gument o p_numce ' del pr ocedi mi ent o r esul t set1 d e MySQL y def i ni endo su ti po ( ent ero) paramet r o1 = i nst r ucci on. Par amet ers . Add( " p_numce" , MySql DbType. I nt32) ' Asi gnando al parámet r o el val or r eci bi do en el pr ocedi mi ent o VB 2005 paramet r o1. Val ue = cent r o ' El conj unt o de f i l as y col umnas resu l t ant es de l l amar ' al procedi mi ent o se al macenar á en un obj et o de t i po Dat aReader Di m MyReader As MySql DataReader = i nstr ucci on. Execut eReader ' Recor r i do de l as f i l as del DataReader Whi l e MyReader . Read ' I mpr esi ón de cada una de sus col umnas Consol e. Wr i t e(MyReader. GetI nt 32( 0) ) Consol e. Wr i t e( " - " & MyReader. Get St r i ng(1) ) Consol e. Wr i t eLi ne( " - " & MyReader . Get I nt 32( 2) ) End Whi l e MyReader . Cl ose( ) Cat ch Except i on As MySql Except i on Consol e. Wr i t eLi ne( "Ocur r i ó un er r or : " ) Consol e. Wr i t eLi ne(Exc ept i on. Messa ge) End Tr y Mi Conexi on. Cl ose( ) End Sub

Página 69



El mismo ejemplo pero utilizando directamente la instrucción Select '...... Tr y Mi Conexi on. Open( ) Di m cadenasel ect As St r i ng cadenasel ect = " Sel ect numde, nomde, pr esu f r om depar t ament os where numce= " & centr o Di m i nstr ucci on As M ySql Command = New MySql Command( cadenasel ect , Mi Conexi on) Di m MyReader As MySql Dat aReader = i nst r ucci on. Execut eReader Whi l e MyReader . Read Consol e. Wr i t e(MyReader. GetI nt 32( 0) ) Consol e. Wr i t e( " - " & MyReader . Get St r i ng( 1)) Consol e. Wr i t eLi ne( " - " & MyReader . Get I nt32( 2) ) End Whi l e MyReader. Cl ose( ) Catc h Except i on As MySql Excepti on ' ......... ....

3 Ejecución de funciones Los dos ejemplos que vienen a continuación realizan una llamada a la función funcion7 vista en el apartado 2.8.2 de este tema (devuelve el nombre de un alumno a partir de un identificador de alumno que se le pasa como parámetro). Se utilizan sentencias preparadas tanto en este apartado como en el 2.10.5.

3.1 En PHP El fragmento de script de este apartado recoge un número matrícula de alumno mediante un formulario de entrada y envía el dato (alumno_id)para a funcion7

laquefunción este devuelva el nombre. Ej. de ejecución Ilustración 30. Resultado de llamar a la función “funcion7” desde PHP

Ilustración 25. Llamando a una función desde PHP

Página 70



3.2 En Visual Basic Express 2005 Sub vbnet 2( ByVal al umno As I nt eger ) ' Ll amar a l a f unci ón MySQL envi ando como argument o el al umno ' y r eci bi endo ( r etur n) de é st a en el obj et o parametr o_devuel t o el nombr e del mi smo Di m host As Str i ng = "192. 168. 0. 5" Di m usuari o As Str i ng = "root" Di m cl ave As Str i ng = "------" Di m base_dat os As Str i ng = "test" Di m cadena_c onexi on As Str i ng = " Dat abase=" & base_ dat os & _ " ; Dat a Sourc e=" & host & _ "; User I d=" & usuar i o & " ; Passwor d=" & cl ave Di m Mi Conexi on As New MySql Connect i on( cadena_conexi on) Tr y Mi Conexi on. Open( ) Di m i nstr ucci on As M ySql Command = New M ySql Command( "f unci on7" , Mi Conexi on) i nst r ucci on. CommandType = CommandType. St or edPr ocedur e Di m par amet r o_envi ado As MySql Par amet er paramet r o_envi ado = i nst r ucci on. Par ameter s. Add( "p_i d" , MySql DbType. I nt32) par amet r o_envi ado. Val ue = al umno Di m par amet r o_devuel t o As MySql Par amet er = _ i nst r ucci on. Par ameter s. Add( " par amet r o_devuel t o", MySql DbType. St r i ng) parametr o_devuel t o. Di r ecti on = Par ameter Di r ect i on. ReturnV al ue i nst r ucci on. Execut eNonQuery( ) Consol e. Wr i t eLi ne( " Nombr e del al umno =" + parametr o_devuel t o. Val ue) Catc h Except i on As MySql Excepti on '..............

Ilustración 32. Ejecución del procedimiento y resultados

4 Ejecución de sentencias DDL

Los dos ejemplos que vienen a continuación crean una sencilla tabla en MySQL.

4.1 En PHP Nota: En el siguiente apartado 5.1 también se utilizan instrucciones DDL dentro del script PHP. El resultado de realizar dos llamadas al script PHP de creación de la tabla viene a continuación en las dos próximas ilustraciones:

Ilustración 263. Resultado de ejecutar el script PHP de creación de una tabla

Página 71



Ilustración 34. Resultado de ejecutar por segunda vez el script PHP de creación de una tabla

Ilustración 275. Creando una tabla desde PHP

4.2 En Visual Basic Express 2005 '.......... Di m Mi Conexi on As New MySql Connect i on( cadena_conexi on) Di m cadena_sql As Str i ng = " CREATE TABLE t abl a1 ( i d I NTEGER) " Di m i nstr ucci on As M ySql Command = New MySql Command( cadena_s ql , Mi Conexi on) Tr y Mi Conexi on. Open() i nst r ucci on. Execut eNonQuery( ) Consol e. Wr i t eLi ne( " Tabl a cr eada" ) Cat ch Except i on As MySql Except i on Consol e. Wr i t eLi ne( "Err or en l a cr eaci ón: " ) Consol e. Wr i t eLi ne(Exc ept i on. Messa ge) End Tr y '..........

5 Ejecución de sentencias preparadas Relacionado con el apartado 2.5.7 de este tema.

5.1 En PHP El siguiente script tiene misma funcionalidad que el yprocedimiento1 este tema (apartado 2.5.4) en ellaque es creaba la tabla alumnos se rellenaba convisto 5 deen ellos.

Página 72



Ilustración 286. Sentencias preparadas en PHP

Página 73



Llamando directamente al procedimiento 1:

Ilustración 297. Inserción de datos en la tabla sin utilizar sentencias preparadas

5.2 En Visual Basic Express 2005 ' I nserci ón en l a t abl a al umnos de l os al umnos: ' al umno 10, al umno 11. . . al umno 20 medi ant e SENTENCI AS PREPARADAS '.......... Di m Mi Conexi on As New MySql Connect i on( cadena_conexi on) Di m cadena_sql As Str i ng = " I NSERT I NTO ALUMNOS VALUES ( ?nuevo_i d, ?nuevo_al umno) " ' Cr eando un nuevo comando asoci ado a l a cadena Sel ect ant er i or Di m i nstr ucci on As M ySql Command = New MySql Command( cadena_s ql , Mi Conexi on) Tr y Mi Conexi on. Open( ) ' Cr eando el par ámet r o par amet r o_i d Di m parametr o_i d As MySql Par amet er ' Asoci ándol o con el comando cr eado parametr o_i d = i nst r ucci on. Par ameter s. Add( "? nuevo_i d", MySql DbType. I nt32) ' Cr eando el segundo par ámet r o par amet r o_al umno Di m par amet r o_al umno As MySql Par amet er ' Asoci ándol o con el comando cr eado par amet r o_al umno = i nst r ucci on. Par amet ers . Add( " ?nuevo_al umno", MySql DbType. St r i ng) Di m cont ador As I nt eger For cont ador = 10 To 20 parametr o_i d. Val ue = cont ador par amet r o_al umno. Val ue = " al umno " + cont ador. ToSt r i ng ' En cada i t eraci ón, i nserci ón del al umno "co nt ador" i nst r ucci on. Execut eNonQuery( ) Next Catc h Except i on As MySql Excepti on Consol e. Wr i t eLi ne( "Err or en l a i nserci ón: " ) Consol e. Wr i t eLi ne(Ex cept i on. Messa ge) End Tr y '..........

Página 74


Parte III. Transacciones

PARTE III. TRANSACCIONES Tema 3. Transacciones en MySQL 3.1 Introducción...........................................................................................2 3.2 Niveles de aislamiento...........................................................................3 3.3 Instrucciones de manejo de transacciones.............................................9 3.4 Transacciones y bloqueos....................................................................13 3.4.1 Deadlock. Cursores de actualización..............................15 3.5 Estrategias de bloqueo.........................................................................19 3.6 Aspectos a tener en cuenta a la hora de utilizar transacciones............21 Anexo. Manejo de transacciones desde otras aplicaciones 1. Desde PHP .............................................................................................21 2. Desde Visual Basic Express 2005 .........................................................22

Página 1



TEMA 3. Transacciones en MySQL Alberto Carrera Martín

3.1 Introducción TRANSACCIÓN en SQL: Conjunto de instrucciones SQL, agrupadas lógicamente, que o bien se ejecutan todas sobre la base de datos o bien no se ejecuta ninguna. Una transferencia bancaria entre dos cuentas supone un ejemplo claro para ilustrar el concepto de transacción. La transferencia se compone de dos operaciones (instrucciones): 1) Descontar de la libreta srcen a transferir la cantidad fijada. 2) Aumentar el saldo de la libreta destino con el importe de la cantidad transferida de la cuenta srcen. Está claro que la transacción bancaria anterior no se puede quedar “a medias”. O bien se aplican las dos operaciones lógicas que componen la transacción o bien no se realiza ninguna por mantener una consistencia contable. Si sólo llegara a realizarse una operación de las dos anteriores, esta se desharía. Las transacciones sobre la base de datos deben ajustarse al principio ACID (ATOMIC + ONSISTENT + ISOLATED + DURABLE), lo que implica que una transacción debe C ser: -

ATÓMICA (ATOMIC): Todas las operaciones que componen la transacción deben aplicarse sobre la base de datos o no aplicarse ninguna. Una transacción es indivisible.

-

CONSISTENTE (CONSISTENT): Se mantiene la consistencia de los datos antes y después de ejecutar la transacción.

-

AISLADA (ISOLATED): Cuando muchas transacciones se pueden llevar a cabo simultáneamente por uno o varios usuarios, la realización de una no debe afectar jamás a las otras y por tanto no llevar a una situación de error.

-

PERMANENTE (DURABLE): Una vez que la transacción ha sido confirmada (COMMIT) y por tanto los cambios de la base de datos guardados, éstos deben perdurar en el tiempo aun cuando el gestor de base de datos o el propio equipo fallen.

La capacidad transaccional de MySQL depende del sistema de almacenamiento empleado. Los dos más importantes son el MyISAM y el InnoDB. MyISAM: No admite transacciones. Utilizada para aplicaciones que en su mayoría son de sólo lectura. Si las aplicaciones casi no necesitan el empleo de las transacciones, es la forma de almacenamiento a utilizar pues el rendimiento es óptimo. Si el número de Página 2



transacciones y accesos concurrentes para modificar los datos de la base de datos es importante, entonces es mejor utilizar el siguiente modelo de almacenamiento. InnoDB: El modelo de almacenamiento de transacción segura de MySQL más popular. Soporta transacciones ACID así como bloqueos a nivel de fila y concurrencia (a costa de sacrificar un poco la velocidad de proceso). Es el tipo predeterminado de modelo de almacenamiento si se ha instalado el gestor de bases de datos utilizando el asistente y por tanto no se ha especificado a la hora de crear las tablas en la sentencia CREATE TABLE. Puedes comprobar dicho modelo de almacenamiento con la instrucción SHOW CREATE TABLE alumnos y ver la columna CREATE TABLE:

Ilustración 1. Almacenamiento InnoDB

3.2 Niveles de aislamiento Una sesión de base de datos es una conexión única a la base de datos que comienza cuando se entra en el sistema (login) y termina cuando se desconecta de ella. Cada vez que abrimos el programa cliente MySQL Query Browser estamos abriendo una sesión (podemos tener varias instancias de este programa abiertas  varias sesiones). Para averiguar el identificador de conexión que nos asigna el servidor, consultaremos la función que nos lo proporciona:

Página 3



Ilustración 2. Identificador de conexión I

La sesión termina cuando cerramos el programa MySQL Query Browser. También en alguna ocasión hemos creado alguna sesión para trabajar con la base de datos desde la ventana de línea de comandos de Windows:

Ilustración 3 Identificador de conexión II

En el momento de conectarnos de esta manera a MySQL se visualiza el identificador de conexión (4ª línea en la ilustración 3) que éste nos asigna. La conexión dura hasta que la abandonemos (comando EXIT). Cada sesión trabaja en su propia zona de memoria pudiendo llegar incluso a bloquear los datos de la base de datos con los que trabaja. Los niveles de aislamiento determinan la manera en que las transacciones de una sesión pueden afectar a los datos recuperados o accedidos por otra sesión. Hay por tanto dos conceptos interrelacionados: por una lado la concurrencia (varios sesiones realizando transacciones al mismo tiempo) y por otro el grado de consistencia de los datos. Determinan también el grado en que las transacciones se ajustan al principio ACID. Cuanto mayor es el grado de aislamiento, menor es el número de transacciones que se pueden realizar concurrentemente pero también es menor la posibilidad de que interfieran las transacciones. Por otro lado, cuanto menor es el grado de aislamiento, mayor es el número de transacciones que se pueden realizar concurrentemente pero el riesgo de conflicto entre transacciones es elevado.

Página 4



El estándar ANSI define 4 modelos de aislamiento, soportados todos ellos por el sistema de almacenamiento InnoDB de MySQL: -

Lectura no confirmada (también conocido como lectura sucia): Es el nivel más bajo. Permite que una transacción pueda leer filas que todavía no han sido confirmadas (COMMIT). El hecho de que utilizando este nivel se aumente el rendimiento del proceso no justifica que pueda permitirse que un usuario lea datos modificados por otro usuario que todavía puede deshacer dichas modificaciones.

-

Lectura confirmada. Sólo se permite lectura de datos que han sido confirmados. Si un usuario está ejecutando un procedimiento que recorre las filas recuperadas de una tabla mediante una sentencia SELECT, si otro usuario en ese momento realiza una modificación de la tabla, el primero no verá las modificaciones realizadas por este último.

-

Lectura repetible. Es el nivel por defecto. Las lecturas repetidas de la misma fila por la misma transacción dan los mismos resultados. Ningún cambio hecho en la base de datos por otros usuarios será visto por la transacción lanzada hasta que esta se confirme o deshaga, es decir, si se repite dentro de una transacción una instrucción SELECT, esta devolverá siempre los mismos resultados (excepto cuando dentro de la misma transacción pudieran realizarse cambios en esa filas recuperadas por la SELECT).

-

Secuenciable. Mayor nivel de aislamiento. Las transacciones se aíslan completamente dando la impresión de que se ejecutan secuencialmente, una detrás de otra. Para conseguir esto, los sistemas gestores de bases de datos bloquean cada fila leída para que otras sesiones no puedan modificar estos datos hasta que la transacción finalice. El bloqueo dura hasta que la transacción es confirmada o deshecha. Este nivel disminuye mucho el rendimiento del sistema y puede provocar situaciones de “abrazo mortal” como se verá más adelante.

El nivel de aislamiento por defecto se podría cambiar (no aconsejable): SET TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE} Los siguientes ejemplos nos ayudarán a comprender el manejo de transacciones: Nota: Recordar que se mantiene siempre la consistencia en lectura  Los datos que ven las transacciones son los últimos que se validaron. Antes de comenzar estos ejemplos ejecuta el procedimiento1 ya visto en temas anteriores y que crea la tabla alumnos con 5 alumnos: Abre dos sesiones nuevas de cliente de línea de comandos de MySQL (lo puedes hacer mediante la opción de menú Tools / MySQL Command Line Client). Ilustración 4. Tabla alumnos

Página 5



En este momento tendrás 3 sesiones abiertas, una del programa MySQL Query Browser y dos clientes de línea de comando de MySQL (ilustración 5). Puedes comprobar el identificador de cada sesión con la función vista anteriormente (SELECT CONNECTION_ID();):

Ilustración 5. Sesiones abiertas en la barra de tareas

Utilizaremos para la práctica las dos sesiones de clientes de línea de comando MySQL. Como los números de identificación de conexión serán distintos a los que utilices, llamaremos A a la conexión de cliente de línea de comando de identificador más bajo y B a la otra conexión de cliente cuyo identificador es más alto. Dejaremos la propiedad autocommit a 0 en las dos sesiones abiertas por lo que los cambios en la base de datos no se almacenan después de cada instrucción sino cuando se confirman explícitamente con la instrucción COMMIT. A > SET autocommit = 0; > USE TEST B > SET autocommit = 0; > USE TEST A > INSERT INTO alumnos VALUES (6, 'alumno6'); > SELECT * FROM alumnos WHERE id=6; +----+---------+ | id | alumno | +----+---------+ | 6 | alumno6 | +----+---------+ B > SELECT * FROM alumnos WHERE id=6; Empty set (0.00 sec) A > COMMIT;

B > SELECT * FROM alumnos WHERE id=6; Empty set (0.00 sec) > COMMIT; Query OK, 0 rows affected (0.00 sec) > SELECT * FROM alumnos WHERE id=6; +----+---------+ | id | alumno | +----+---------+ | 6 | alumno6 | +----+---------+ 1 row in set (0.00 sec)

Página 6



A > UPDATE alumnos SET alumno = 'Alberto Carrera' WHERE id=1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 > SELECT * FROM ALUMNOS WHERE id=1; +----+-----------------+ | id | alumno | +----+-----------------+ | 1 | Alberto Carrera | +----+-----------------+ 1 row in set (0.00 sec) B > SELECT * FROM ALUMNOS WHERE id=1; +----+----------+ | id | alumno | +----+----------+ | 1 | alumno 1 | +----+----------+ 1 row in set (0.00 sec) > UPDATE alumnos SET alumno = 'Raquel Carrera' WHERE id = 2; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 l> SELECT * FROM ALUMNOS WHERE id=2; +----+----------------+ | id | alumno | +----+----------------+ | 2 | Raquel Carrera | +----+----------------+ 1 row in set (0.00 sec) A mysql> SELECT * FROM ALUMNOS WHERE id=2; +----+----------+ | id | alumno | +----+----------+ | 2 | alumno 2 | +----+----------+ 1 row in set (0.00 sec) mysql> COMMIT; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM ALUMNOS; +----+-----------------+ | id | alumno | +----+-----------------+ | 1 | Alberto Carrera | | 2 | alumno 2 | | 3 | alumno 3 | | 4 | alumno 4 | | 5 | alumno 5 | | 6 | alumno6 | +----+-----------------+ 6 rows in set (0.00 sec)

Página 7



B mysql> SELECT * FROM ALUMNOS; +----+----------------+ | id | alumno | +----+----------------+ | 1 | alumno 1 | | 2 | Raquel Carrera | | 3 | alumno 3 | | 4 | alumno 4 | | 5 | alumno 5 | | 6 | alumno6 | +----+----------------+ 6 rows in set (0.00 sec)

mysql> COMMIT; Query OK, 0 rows affected (0.02 sec) mysql> SELECT * FROM ALUMNOS; +----+-----------------+ | id | alumno | +----+-----------------+ | 1 | Alberto Carrera | | 2 | Raquel Carrera | | 3 | alumno 3 | | 4 | alumno 4 | | 5 | alumno 5 | | 6 | alumno6 | +----+-----------------+ 6 rows in set (0.00 sec)

mysql> SELECT * FROM ALUMNOS; +----+-----------------+ | id | alumno | +----+-----------------+ | 1 | Alberto Carrera | | 2 | alumno 2 | | 3 | alumno 3 | | 4 | alumno 4 | | 5 | alumno 5 | | 6 | alumno6 | +----+-----------------+ 6 rows in set (0.00 sec) mysql> COMMIT; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM ALUMNOS; +----+-----------------+ | id | alumno | +----+-----------------+ | 1 | Alberto Carrera | | 3 2 | | alumno Raquel 3 Carrera | | | | 4 | alumno 4 | | 5 | alumno 5 | | 6 | alumno6 | +----+-----------------+ 6 rows in set (0.00 sec)

Nota, como no se ha utilizado la instrucción de comienzo de transacción START TRANSACTION la transacción ha empezado de manera implícita. Página 8



3.3 Instrucciones de manejo de transacciones -

START TRANSACTION: Comienzo de una nueva transacción. Si ya existe una iniciada, esta última finaliza con confirmación de datos (COMMIT). Cuando comienza una nueva transacción, la propiedad autocommit automáticamente pasa a estado 0 (OFF apagado) hasta que finaliza la transacción.

-

COMMIT: Termina la transacción guardando en la base de datos todos los cambios realizados por la transacción. Cualquier tipo de bloqueo que se mantuviera durante la transacción queda liberado.

-

ROLLBACK: Termina la transacción deshaciendo todos los cambios que hubiera realizado sobre la base de datos. Libera los bloqueos que hubiera realizado la transacción.

-

SAVEPOINT punto de salvaguarda: Crea un punto de salvaguarda al que se puede retroceder mediante la instrucción ROLLBACK TO SAVEPOINT.

-

ROLLBACK TO SAVEPOINT punto de salvaguarda. Realiza un ROLLBACK de todas las sentencias ejecutadas desde que se creó el punto de salvaguarda.

-

SET TRANSACTION: Permite cambiar el nivel de aislamiento de la

-

transacción como se ha visto anteriormente. LOCK TABLES: Permite bloquear explícitamente una o varias tablas. A la vez cierra todas las transacciones abiertas.

Por defecto, MySQL efectúa un COMMIT implícito después de ejecutar cada instrucción DML SQL por lo que los cambios se guardan después de cada instrucción. La propiedad (variable) autocommit que controla este comportamiento está puesta a 1.

Ilustración 6. Propiedad autocommit

Página 9



Para poder trabajar con transacciones: 1º) Cambiar la propiedad autocommit para que no se haga un COMMIT automáticamente después de cada instrucción SQL: SET autocommit=0 De todas formas, aunque es un buen método de trabajo en este y otros gestores, es redundante esta primera acción pues como se ha indicado la instrucción START TRANSACTION ya deja a 0 el valor de la propiedad anterior. 2º) Indicar el comienzo de la transacción con la instrucción START TRANSACTION Nota: Independientemente del valor de la propiedad anterior, todas las sentencias DDL del lenguaje: ALTER, CREATE, DROP … tienen el COMMIT o confirmación de manera implícita o automática. Un ejemplo sencillo de todo lo visto anteriormente:

Procedimiento 1 transac1

La llamada al procedimiento: CALL transac1(7,'Mario A. Carrera') insertará al alumno Mario. En cambio: CALL transac1(7, 'Carmen Bailin') dará error de clave duplicada. Como el error no es tratado al no existir manejador de errores, realizadolalainstrucción inserción. 10 no llegará a ejecutarse pero la transacción finalizará sin haber

Página 10



Otro ejemplo de código y ejecución:

Procedimiento 2 transac2 mysql> USE TEST Database changed mysql> call transac2(7, 'Blanca Bailin', @n_error, @texto_error); Query OK, 0 rows affected (0.00 sec) mysql> SELECT @n_error, @texto_error; +----------+-----------------+ | @n_error | @texto_error | +----------+-----------------+ | 1062 | Clave duplicada | +----------+-----------------+ 1 row in set (0.00 sec) mysql> call transac2(8, 'Irene Yera', @n_error, @texto_error); Query OK, 0 rows affected (0.03 sec) mysql> SELECT @n_error, @texto_error; +----------+--------------------------+ | @n_error | @texto_error | +----------+--------------------------+ | 0 | Alta de alumno realizada | +----------+--------------------------+ 1 row in set (0.00 sec) /* Borrado de la tabla alumnos*/ mysql> call transac2(9, 'Ana Escalada', @n_error, @texto_error); Query OK, 0 rows affected (0.00 sec) mysql> SELECT @n_error, @texto_error; +----------+-------------------+ | @n_error | @texto_error | +----------+-------------------+ | -1 | Ocurri├│ un error | +----------+-------------------+ 1 row in set (0.00 sec)

Página 11



Antes de ejecutar el tercer ejemplo asegúrate que tienes la tabla: CREATE TABLE AUDITA (mensaje VARCHAR(200))


Nota, probar diferentes opciones. El punto de ruptura se utiliza para deshacer grabaciones en la tabla audita. Se deja al alumno averiguar su funcionamiento. La ejecución de la llamada al procedimiento: CALL transac3 (30, 'Centro 30', 'Los Olivos - Huesca', 140, 'Departamento 140', 40000) Insertará la última fila en la tabla centros y departamentos:

Ilustración 7. Tabla centros después de la última inserción

Página 12



Ilustración 8. Tabla departamentos después de la última inserción

y las siguientes entradas en la tabla audita:

Ilustración 9. Tabla audita después de la última inserción

Si volvemos a ejecutar la misma llamada, se añaden nuevas líneas:

Ilustración 10. Tabla audita después de la última operación

3.4 Transacciones y bloqueos Observa la siguiente secuencia de operaciones que se realizan en dos sucursales bancarias sobre una misma cuenta. La primera columna tiempo (Tº) indica el orden en que se efectúan: Tº SUCURSAL A SUCURSAL B 0 1 Disminuye 200 € 2 Aumenta 300 € 3 Confirma operación 4 Confirma operación

SALDO INICIAL CUENTA 1.000 € 800 € 1.300 € 800 € 1.300 €

La situación anterior no debe aceptarse. Solución: Tº SUCURSAL A SUCURSAL B 0 1 2 3 4 5

Disminuye 200€

La cuenta está bloqueada hasta que la Sucursal A confirme la operación

Confirma operación

SALDO INICIAL CUENTA 1.000 € 800€ 800€

Aumenta 300 € Confirma operación

1.100 € 1.100 €

Página 13



En la anterior solución cada transacción de programas, usuarios… espera a que acabe la transacción ya iniciada en un fila y que por tanto está bloqueada (no hay espera si la operación simplemente es una lectura de datos de la fila y no una modificación de los mismos). Las situaciones en que se producen estos bloqueos son: -

Sentencia UPDATE: Las filas afectadas se bloquean hasta que se confirme o deshaga la transacción.

-

Sentencia INSERT: Si existe clave primaria, las filas insertadas quedan bloqueadas para prevenir que otra transacción pueda introducir otra fila con la misma clave.

-

Sentencia LOCK TABLES: Bloquea la tabla entera. No es muy eficiente pues reduce la concurrencia.

-

Si dentro de una sentencia SELECT se utilizan las clausulas FOR UPDATE o LOCK IN SHARE MODE, todas las filas devueltas por la sentencia SELECT serán bloqueadas: SELECT opciones de la sentencia SELECT [FOR UPDATE | LOCK IN SHARE MODE]

La cláusula LOCK IN SHARE MODE suele utilizarse para garantizar la integridad referencial. queasignarás estás intentando introducir los Existe datos ladeclave un nuevo departamentoImagina al que le un determinado centro. ajena: departamentos.numce  centros.numce. Puede ocurrir que durante el proceso, otra transacción elimine la fila correspondiente del centro en la tabla Centros. Al intentar introducir el departamento aparecerá una situación de error. Por eso, en estos casos, en primer lugar hay que asegurarse de que nadie modificará el Centro: SELECT * FROM CENTROS WHERE NUMCE=XX LOCK IN SHARE MODE Por otro lado la solución anterior no es buena para garantizar la clave única en una tabla. Imagina que cada departamento se obtiene sumando 10 al número último asignado. Si utilizamos la cláusula LOCK IN SHARE MODE puede ocurrir que dos transacciones lean al mismo tiempo el último id asignado, le sumen 10 y a la hora de modificarlo provoquen una situación de clave duplicada o como se ve más adelante una situación de abrazo mortal (deadlock). La solución para este caso: SELECT id FROM tablax… FOR UPDATE UPDATE tablax SET id… en la que primero se intenta leer con intención de modificar, utilizando además un bloqueo FOR UPDATE y luego, si ha sido posible realizar la operación anterior, entonces incrementarlo (UPDATE tablax…). El nivel de bloqueo es el mismo que para un UPDATE. Los bloqueos son levantados cuando la transacción se confirma o deshace. Página 14



3.4.1 Deadlock. Cursores de actualización También conocido como abrazo mortal. Esta situación ocurre cuando una transacción A intenta modificar los datos que están siendo modificados por una transacción B y a su vez esta última intenta modificar los datos que están siendo modificados por la primera transacción A. Tº SUCURSAL A SUCURSAL B 1 Modifica saldo 2

cuenta X

3

Modifica cuenta Y

Modifica cuenta Y saldo

4

Modifica cuenta X

SITUACION La cuenta X queda bloqueada por A cuenta Y queda bloqueada por saldo La B A queda a la espera pues Y está bloqueada saldo B queda a la espera pues X está bloqueada

Como consecuencia del abrazo mortal anterior, una de las dos transacciones realizará un ROLLBACK provocando una situación de error. Volvemos a la situación inicial de la tabla alumnos, 5 filas con 5 alumnos de id’s 1 al 5 y de nombres alumno1…alumno5. Abrimos 2 sesiones A y B: A mysql> USE TEST; Database changed mysql> SET autocommit = 0; B mysql> USE TEST; Database changed mysql> SET autocommit = 0; A mysql> UPDATE ALUMNOS SET alumno='Alberto Carrera' WHERE id=1; /* A ya puede ver el cambio producido pero B seguirá viendo alumno1 como nombre en lugar de Alberto Carrera */

B

Ilustración 11. Tiempo de espera superado Después de un tiempo de espera de B, aparece el error de la ilustración anterior, al intentar modificar una fila que está siendo modificada y todavía no ha sido confirmada o deshecha por A. mysql> UPDATE ALUMNOS SET alumno='Mario Carrera' WHERE id=2;

Página 15



A mysql>UPDATE ALUMNOS SET alumno='Carmen Bailin' WHERE id=2; B Si ejecutamos rápidamente: mysql>UPDATE ALUMNOS SET alumno='Raquel Carrera' WHERE id=1;

Ilustración 12. Abrazo mortal A

Ilustración 13. Tabla alumnos vista por el usuario A B

Ilustración 14. Tabla alumnos vista por el usuario B A mysql>COMMIT; B Sigue viendo la pantalla anterior última (ilustración 14). mysql>COMMIT; Ve la pantalla penúltima (ilustración 13)

Para probar el cuarto ejemplo hay que lanzar antes otra vez el procedimiento procedimiento1 para crear de nuevo la tabla alumnos.

Página 16



Procedimiento 4transac4 A USE TEST; SET autocommit = 0; B USE TEST; SET autocommit = 0; A UPDATE alumnos SET alumno= 'Mario Carrera' WHERE id=1; B CALL transac4 (1, 'Carmen Bailin', @p_n_error, @p_text_error); /* ……Después de un tiempo de espera – 50 segundos en el que parece que se ha quedado colgado el programa */ Query OK, 0 rows affected (51.55 sec) SELECT @p_n_error, @p_text_error; +------------+---------------------------+ | @p_n_error | @p_text_error | +------------+---------------------------+ | 1205 | Tiempo de espera excedido | +------------+---------------------------+

Página 17



A continuación una solución para evitar el DEADLOCK: Cursores de actualización (CURSOR FOR UPDATE)


Antes se finalizar este apartado señalar que, como hemos comprobado al intentar escribir en una fila bloqueada (sin ser deadlock), se ha producido un tiempo de espera de 50 segundos tras el que se ha producido un error de tiempo de espera excedido (1205) y la transacción se ha deshecho. La variable de sistema innodb_lock_wait_timeout es la que almacena el tiempo máximo de espera. Dicha variable puede modificarse en caso de aplicaciones con largas transacciones. También se puede como se ha hecho antes crear un manejador de error.

Página 18



3.5 Estrategias de bloqueo Si una transacción tiene necesidad de leer datos que posteriormente se pueden ver afectados por operaciones de manipulación (INSERT, UPDATE, DELETE), hay que tomar precauciones para que otra transacción no pueda modificar unos datos después de que hayan sido leídos por la primera transacción y antes de ser modificados por esta. Ej. Tº SUCURSAL A 0

SUCURSAL B

SITUACION SALDO CUENTAINICIAL X = 1.000 € 1.000€

1

LEE SALDO CON INTENCIÓN DE DISMINUIRLO EN 900 € SI ES POSIBLE PARA EVITAR DEJAR LA CUENTA EN NÚMEROS ROJOS

2

DISMINUYE LA 500€ CUENTA EN 500 € COMO EL SALDO PARA LA - 400€ SUCURSAL A ES DE 1.000€, A CONTINUACIÓN DISMINUYE EL SALDO EN 900€

3

Posibles soluciones: -

Estrategia de bloqueo pesimista: El planteamiento para este caso es que las transacciones concurrentes es muy fácil de que ocurran y por tanto hay que estar prevenido. Por tanto habrá que bloquear las filas después de leerlas. Otras transacciones que quieran modificar los datos deberán esperar.

-

Estrategia de bloqueo optimista: Asume que es muy poco probable que el valor de una fila que acabamos de leer cambie. Si asumimos este planteamiento como mínimo deberemos asegurarnos de que la fila no ha sido modificada y si así ha sido entonces la transacción no debe llevarse a cabo aun pudiéndose realizar.

A continuación un ejemplo de implementación de la estrategia pesimista:

Página 19




Es una estrategia muy segura pues asegura la consistencia entre la lectura (SELECT) y la operación DML (UPDATE), pero limita mucho el rendimiento del sistema al obligar a las transacciones a largas esperas para poder completarse. En la estrategia optimista, como la transacción no bloquea la fila que lee, antes de que realice la modificación sobre la misma debe asegurarse que el valor de la fila no ha cambiado desde que la leyó; en caso de que no se hubieran producido cambios se realizará y confirmará la transacción, en caso contrario no se llevará a cabo. Para la elección de una estrategia u otra hay que tener en cuenta la concurrencia y robustez: la estrategia pesimista supone menos errores y reintentos mientras que con la optimista se reduce el tiempo de duración de los bloqueos aumentando por tanto la concurrencia y el rendimiento del sistema. Generalmente suele utilizarse la estrategia pesimista y sólo se recurre a la optimista si la duración de los bloqueos o el número de filas que se bloquean es elevado en la estrategia pesimista. De todas formas el uso de una u otra depende mucho de las características de la aplicación.

Página 20



3.6 Aspectos a tener en cuenta a la hora de utilizar transacciones De todo lo visto anteriormente, podemos resumir: -

Siempre debe mantenerse la integridad de la base de datos para garantizar que los datos que almacenan son precisos, fieles a la realidad y lo más correctos posibles.

-

La duración de los bloqueos debe ser lo mínimo posible. De igual manera debe ser también mínimo el número de filas a bloquear por las transacciones.

-

La operación de ROLLBACK (deshacer) debe evitarse en los casos que se pueda pues es una operación costosa en el tiempo y en utilización de recursos. Por eso, habrá que adelantarse a las situaciones que pueden provocarlo como los intentos de inserción de filas de clave duplicada, realizando en este caso búsquedas antes de las inserciones. Relacionado con este punto, se evitarán en la medida en que se pueda la creación y utilización de puntos de salvaguarda; sólo si el número de situaciones posibles a controlar es elevado, quizás en ese caso sea rentable y aceptable su utilización Indicar también que la operación de confirmación o COMMIT al igual que la de ROLLBACK es una operación costosa pues trae consigo escritura física en disco de los datos de la caché de memoria del gestor de bases de datos. Por eso hay que utilizar esta instrucción de confirmación en los sistemas en tiempo real de un negocio para mantener los datos íntegros pero hay quepor minimizar scripts ejemplo. su uso en situaciones de carga masiva de datos a través de

-

Considerar siempre la estrategia de bloqueo pesimista salvo en los casos del final del apartado anterior que podrían llegar a causar frustración en el usuario por el bajo rendimiento del sistema.

-

En cada programa debe especificarse explícitamente el comienzo y fin de transacción y no asumir que los programas externos son los que validarán o desharán la transacción.

Anexo: Manejo de transacciones desde otras aplicaciones De la misma manera con 2, la misma intención se conindica que secómo ha expuesto el anexo del último apartado del ytema a continuación son manejadas las transacciones MySQL desde otras aplicaciones externas a ella.

1 Desde PHP En el siguiente ejemplo se intenta llevar a cabo una transacción consistente en cambiar el nombre del alumno de matrícula 1. Si todo va bien aparecerá el mensaje “Filas

Página 21



afectadas: 1 Transacción completada” llevándose a cabo la transacción y validándose mediante la instrucción de la línea 28. Si no puede realizarse es deshecha (línea 23).

Ilustración 15. Manejando transacciones desde PHP

2 Desde Visual Basic Express 2005 Idéntica operación que en el apartado 1 de este anexo: '.................. Dim MiConexion As New MySqlConnection(cadena_conexion) Dim cadena_sql As String = "UPDATE alumnos" + _ " SET alumno='Mario Carrera' " + _ " WHERE id=1" Dim instruccion As MySqlCommand = New MySqlCommand(cadena_sql, MiConexion) MiConexion.Open() Dim MiTransaccion As MySqlTransaction = MiConexion.BeginTransaction Try Dim filas_afectadas As Integer filas_afectadas = instruccion.ExecuteNonQuery() Console.WriteLine("Filas afectadas: " + filas_afectadas.ToString) MiTransaccion.Commit() Console.WriteLine("Transacción finalizada") Catch Exception As MySqlException Console.WriteLine("Error en la transacción: ") Console.WriteLine(Exception.Message) Try MiTransaccion.Rollback() Console.WriteLine("Transaction no realizada") Catch RollbackException As MySqlException Console.WriteLine("Rollback fallido:") Console.WriteLine(RollbackException.Message) End Try End Try '..................

Página 22

Lenguaje SQL con MySQL avanzado

Parte IV. Optimización

PARTE IV. OPTIMIZACIÓN DE MySQL. Tema 4. Optimización de MySQL. 4.1 Introducción.......................................................................................... 2 4.2 Los índices............................................................................................ 4 4.2.1 Índices parciales ………………………………………... 4 4.2.2 Índices Índices multicolumna (índices compuestos) …………… 55 4.2.3 full-text ………………………………………… 4.2.4 Los índices y el operador LI KE ……………………….. 7 4.2.5 Los índices y la cardinalidad …………………………… 8 4.3 Rendimiento y optimización de las consultas ………………………... 8 4.3.1 Detectar las consultas lentas. Activar el “slow query log” .. 8 4.3.2 Optimización de consultas mediante la sentencia EXPLAI N .. 9 4.3.3 Ejemplos del uso de la sentencia EXPLAI N ………………. 13 4.3.4 Comando ANALYZE TABLE………………………………….. 16 4.3.5 Comando OPTI MI ZE TABLE ………………………………… 18 4.4 Gestión de la memoria en el servidor MySQL ……………………… 20 4.4.1 Ajustar las propiedades de memoria de los threads que genera MySQL ………………………………………… 20 4.4.2 Utilización siempre que sea posible de las tablas tipo MEMORY 4.4.3 Descripción y uso de la Query Caché de MySQL ……… 21 4.4.4 Descripción y uso de la Caché de claves (Key Cache) de MySQL …………………………………………………. 24 4.4.5 Mejoras en los join, sort, y escaneo completo de tablas … 25 4.5 Herramientas relacionadas con el rendimiento................................... 28 4.5.1 Optimización de expresiones y funciones mediante la función BENCH MARK() …………………………………………………… 28

4.5.2 Test de rendimiento (Benchmarking) …………………. 29

Anexo 1. Uso de la herramienta DataGen para insertar múltiples filas en tablas de prueba …………………………………………………………………………….. 32 Anexo 2. Instalar interprete de Perl para Windows ( ActivePerl) …………….. 40 Anexo 3. MySQL Administrador ……………………………………………… 42 Introducción a MySQL Administrador −

− − − −

Instalación de MySQL Establecer una conexiónAdministrator con el servidor MySQL Administrator para administrar usuarios MySQL Administrator para crear gráficos customizados de monitorización del sistema

Página 1



TEMA 4. Optimización de MySQL. Javier Robles Cascallar

4.1 Introducción. Para poder seguir este tema es necesario partir de unos conocimientos básicos en la arquitectura de MySQL y en tareas de administración. En concreto al lector se le supone familiarizado en los siguientes aspectos: Estructura interna de MySQL. Conocer las distintas arquitecturas de almacenamiento (InnoDB, MyISAM…) Configuración a través de la línea de comandos y ficheros de opciones (my.ini). Conocer las variables de estado y variables de sistema de MySQL. Índices. Tipos de índices y su creación. Administración de cuentas de usuario y seguridad. (Toda esta información está recogida en el Bloque III del curso de SQL con MySQL que oferta el Aula Mentor) •

•

• •

El propósito de la optimización es ayudar a mejorar el rendimiento de la base de datos. Hay muchas maneras de optimizar MySQL, desde añadir un índice para acelerar el tiempo de respuesta de una consulta hasta cambiar el valor de una variable de configuración para mejorar la gestión de la memoria. Este tema no pretende cubrir todos los posibles aspectos que permiten mejorar el rendimiento de una base de datos como MySQL, ya que se saldría de los objetivos del curso, simplemente trata de llevar al lector, que ya conoce el lenguaje MySQL y las tareas básicas de administración, a dar un paso más, y comenzar a introducirle, mediante explicaciones y ejemplos, en los aspectos más destacables de las muchas posibilidades de optimización que un administrador puede llevar a cabo. Muchos de los ejemplos de este documento se han creado usando la base de datos llamada curso cuya descripción de las tablas l i bro s, bi bl i ot ecas, ej empl ar es, codi gospost al es es la siguiente: mysql > use cur so; mysql > desc l i br os; +-- -- -- -- -- -- -+-- -- -- -- -- -- -- -- -- +-- -- -- +-- -- -+-- -- -- -- -+-- -- -- -- -+ | --Fi--el--d -- -- -- -+-| Type Nul--l --| +Key Def--aul-- t-- | -+-Ext -r a-- --| -+ + -- -- -- -- -- -- -- -- | +--- -- |-+-| id | i nt ( 10) unsi gned | NO | PRI | NULL | aut o_i nc| | TI TULO | var char ( 200) | YES | | NULL | | | AUTOR | var char ( 100) | YES | | NULL | | | COMENTARI OS | var char ( 100) | YES | | NULL | | | NUMPAGI NAS | i nt ( 5) | YES | | NULL | | +-- -- -- -- -- -- -+-- -- -- -- -- -- -- -- -- +-- -- -- +-- -- -+-- -- -- -- -+-- -- -- -- -+ 5 r ows i n set ( 0. 00 sec)

Página 2



mysql > desc bi bl i ot ecas; +-- -- -- -- -- -+-- -- -- -- -- -- -- +-- -- -- +-- -- -+-- -- -- -- -+-- -- -- -- -- -- -- + | Fi el d | Type | Nul l | Key | Def aul t | Ext r a | +-- -- -- -- -- -+-- -- -- -- -- -- -- +-- -- -- +-- -- -+-- -- -- -- -+-- -- -- -- -- -- -- + | id | i nt ( 10) | NO | PRI | NULL | aut o_i ncr eme | | nombr e | var char ( 100) | YES | | NULL | | | di r ecci on | var char ( 60) | YES | | NULL | | | ci udad | var char ( 100) | YES | | NULL | | +-- -- -- -- -- -+-- -- -- -- -- -- -- +-- -- -- +-- -- -+-- -- -- -- -+-- -- -- -- -- -- -- + 4 r ows i n set ( 0. 01 sec) mysql > desc ej empl ar es; + -- -- -- -- -- -- -- -- | +--- -- |-+--- t-- | -+-| --Fi--el--d -- |+--Type Nul--l --| +Key Def--aul Ext -r a-- -- -- -- |-- + +-- -- -- -- +-- -- -- -- -- -- -- -- -- +-- -- -- +-- -- -+-- -- -- -- -+-- -- -- -- -- -- -- + | id | i nt ( 10) | NO | PRI | NULL | aut o_i ncr eme | | i d_t i t | i nt ( 10) unsi gned | YES | MUL | NULL | | | i d_bi b | i nt ( 10) | YES | MUL | NULL | | +-- -- -- -- +-- -- -- -- -- -- -- -- -- +-- -- -- +-- -- -+-- -- -- -- -+-- -- -- -- -- -- -- + 3 r ows i n set ( 0. 36 sec) mysql > desc cod i gospost al es; +-- -- -- -- -- -+-- -- -- -- -- -- -- +-- -- -- +-- -- -+-- -- -- -- -+-- -- -- -+ | Fi el d | Type | Nul l | Key | Def aul t | Ext r a | +-- -- -- -- -- -+-- -- -- -- -- -- -- +-- -- -- +-- -- -+-- -- -- -- -+-- -- -- -+ | PROVI NCI A | var char ( 30) | YES | | NULL | | | POBLACI ON | var char ( 100) | YES | | NULL | | | COD_POS | var char ( 8) | YES | MUL | NULL | | +-- -- -- -- -- -+-- -- -- -- -- -- -- +-- -- -- +-- -- -+-- -- -- -- -+-- -- -- -+

Página 3



4. 2 Índices. Los índices, como ya es bien conocido, permiten al servidor de base de datos localizar rápidamente, en las consultas, los registros buscados sin tener que hacer un recorrido completo de la tabla para su recuperación. Los administradores, ante el hecho de crear un índice sobre un atributo de una tabla, siempre deben analizar si el aumento de la velocidad en las respuestas a las consultas que afectan a esa tabla compensa el espacio en disco requerido para almacenar el índice y también la posible sobrecarga del procesador a la hora de actualizar el índice cuando se realizan modificaciones sobre los datos de la tabla. En este apartado no nos vamos a detener, por lo tanto, en como se crean, se modifican, o se eliminan índices en MySQL pues esos conocimientos ya se suponen adquiridos. Lo que se pretende es dar a conocer al lector determinados aspectos no tan conocidos sobre los índices y también posibles estrategias de indexación.

4. 2. 1 Índices parciales. En ocasiones es más conveniente generar un índice no sobre un atributo completo de una tabla sino sobre una parte de ese atributo. La principal ventaja de esta medida es el ahorro de espacio a la hora de almacenar dicho índice. Supongamos, por ejemplo, que tenemos una base de datos de libros donde se almacena el catálogo de una biblioteca . En esa base de datos hay una columna de una tabla donde se títulode título guarda elmedia delunlibro. hacemos índice sobrenuestro esa columna quemil la longitud títuloSison unos 30unbytes y que catálogo tienesabiendo unos cien registros implica que el índice puede ocupar unos 3Gbytes (probablemente no ocuparía tanto porque MySQL incorpora algoritmos de compresión para reducir el tamaño de los índices). Si en lugar de crear un índice por todo el titulo lo creamos por los seis primeros caracteres mediante un índice parcial podemos hacer que el tamaño final del índice se reduzca en una quinta parte. ALTER TABLE l i br os ADD I NDEX ( t i t ul o( 6) ) ;

El inconveniente de los índices parciales es que retornan a veces más filas de las deseadas que luego la consulta debe descartar porque no coinciden con lo que se esta buscando. Si seguimos con el ejemplo anterior la siguiente consulta: SELECT * FROM l i br os WHERE l ower ( t i t ul o) =’ l a casa encant ada’

Hace que el índice retorne todos los títulos que comiencen por ‘ l a cas’ como ‘ l a casa de l os espí r i t us’ o ‘ l a casquer i a f i na’ , filas que posteriormente la consulta debe descartar. Cuando queremos crear un índice para una columna tipo TEXT o BLOB, en ese caso, es necesario que el índice sea de tipo parcial. Por ejemplo: CREATE TABLE pr uebabl ob ( col _bl ob BLOB, I NDEX( col _bl ob( 15) ) ;

Página 4



4.2.2 Índices multicolumna (índices compuestos). Este tipo de columnas múltiples se justifica cuando sobre una tabla es muy frecuente realizar consultas sobre varios atributos simultáneamente. Por ejemplo si en mi tabla cl i ent es son muy frecuentes las búsquedas por los atributos apel l i do y nombr e simultáneamente podría crear un índice multicolumna haciendo: ALTER TABLE cl i ent es ADD I NDEX ( apel l i do( 6) , nombr e( 6) ) ;

Para no hacer el índice muy grande se indexan únicamente los seis primeros bytes de cada atributo. Si efectúo consultas del tipo: SELECT * FROM cl i ent es WHERE apel l i do=’ Rodr í guez’ and nombr e=’ Rami r o’ ;

Es cuando el resultado es generado muy rápidamente gracias al índice multicolumna. Es importante señalar que un índice multicolumna no es equivalente a crear un índice por separado para cada una de las columnas ya que MySQL solo es capaz de usar en una consulta un índice por cada tabla empleada en dicha consulta. También es muy importante para este tipo de índices el orden en que se colocan los atributos. En el caso del ejemplo una consulta del tipo: SELECT * FROM cl i ent es WHERE apel l i do=’ Rodr í guez’ ;

Se beneficia del índice creado, pero una consulta del tipo: SELECT * FROM cl i ent es WHERE nombr e=’ Rami r o’ ;

Requiere hacer un escaneo completo de la tabla puesto que todas las entradas del índice se inician con apel l i do y solo a continuación de dicha entrada se incluye el contenido de nombr e.

4.2.3 Índices Full-Text. Los índices full-text solo son aplicables a tablas tipo MyISAM y sirven para localizar rápidamente palabras en columnas tipo CHAR,VARCHAR o TEXT. Como estos índices almacenan un registro por cada palabra encontrada en cada campo indexado significa que son índices que consumen mucho espacio de almacenamiento pero que se ve compensado por el gran aumento de velocidad en las búsquedas. Vamos a ver con un ejemplo la diferencia de realizar una búsqueda de una palabra en un texto largo primero sin tener definido un índice full-text y posteriormente con un índice full-text añadido a la tabla. En nuestra base de datos curso tenemos la tabla af or i smos donde se han recopilado frases celebres de distintos personajes. La tabla tiene cien mil registros creados con la utilidad Datagen (ver Anexo 1. Uso de la herramienta DataGen para insertar múltiples filas en Página 5



tablas de prueba), evidentemente hay un montón de frases repetidas pero para el ejemplo

no es relevante, y tiene la siguiente estructura: CREATE TABLE ` af or i smos` ( `i d` i nt ( 10) unsi gned NOT NULL aut o_i ncr ement , `f echa` dat e def aul t NULL, `cuer po_car t a` var char( 350) def aul t NULL, PRI MARY KEY ( ` i d`) ) ENGI NE=MyI SAM

Se desea localizar cuantos aforismos incluyen la palabra ‘Unamuno’ como se supone que no tenemos aun un índice full-text definido sobre el atributo tenemos que usar el operador LI KE sel ect count ( *) f r om af or i smos where l ower( f r ase) LI KE ' %unamuno%' ; +- - - - - - - - - - + | count ( *) | +- - - - - - - - - - + | 458 | +- - - - - - - - - - + 1 r ow i n set ( 0. 23 sec)

El siguiente paso es crear el índice full-text para ver la diferencia: mysql > al t er t abl e af or i smos add f ul l t ext i ndex (f r ase) ; Quer y OK, 100000 r ows af f ect ed ( 9. 23 sec) Recor ds: 100000 Dupl i cat es: 0 War ni ngs: 0

Para usar un índice full-text es necesario utilizar la función MATCH ( campo) ( ‘ pal abr as’ ) . En el ejemplo la consulta será:

AGAI NST

mysql > sel ect count ( *) f r om af or i smos where mat ch( f r ase) agai nst ( ' unamuno' ) ; +- - - - - - - - - - + | count ( *) | +- - - - - - - - - - + | 458 | +- - - - - - - - - - + 1 r ow i n set ( 0. 01 sec)

Se observa la diferencia en el tiempo de respuesta. Esta diferencia se amplia si en lugar de una palabra buscamos, por ejemplo, cuantas frases de la tabla son de Unamuno o Machado. mysql > sel ect count ( *) f r om af or i smos wher e l ower ( f r ase) LI KE ' %unamuno%' or l ower ( f r ase) LI KE ' %machado%' ; +- - - - - - - - - - + | count ( *) | +- - - - - - - - - - + | 758 | +- - - - - - - - - - + 1 r ow i n set ( 0. 52 sec) mysql > sel ect count ( *) f r om af or i smos where mat ch( f r ase) agai nst ( ' unamuno machado' ) ; +- - - - - - - - - - + | count ( *) | +- - - - - - - - - - + | 758 | +- - - - - - - - - - + 1 r ow i n set ( 0. 02 sec)

Página 6



4.2.4 Los índices y el operador LIKE. Las consultas con el operador LIKE pueden hacer uso del índice creado sobre un atributo siempre que el argumento que acompaña a LIKE sea una cadena que no empiece por un carácter comodín. Es decir la siguiente consulta puede hacer uso del índice creado sobre la columna col1: SELECT * FROM mi _ t abl a WHERE col 1 LI KE ‘ hol a%’ ;

Por el contrario la siguiente consulta no puede hacer uso del índice y requiere escanear por completo la tabla: SELECT * FROM mi _ t abl a WHERE col 1 LI KE ‘ %hol a%’ ;

A partir de la versión MySQL 5 las consultas del tipo … LI KE ‘ %cadena%’ , siempre que cadena sea mayor de 3 caracteres, son resueltas mediante el algoritmo Turbo Boyer-Moore, lo que permite incrementar la velocidad de respuesta en este tipo de consultas.

4.2.5 Los índices y la cardinalidad. La cardinalidad de un índice indica el grado de duplicidad de valores repetidos que tiene el atributo (simple o compuesto) sobre el que está construido. En los índices de tipo PRIMARY la cardinalidad coincide con el número de filas de la tabla puesto que no hay valores repetidos ni valores nulos. En los índices tipo UNIQUE la cardinalidad suele ser un valor cercano al número de filas de la tabla puesto que estos índices si que pueden indexar valores nulos. Es en los índices ordinarios donde la cardinalidad puede ser muy variable. En muchos casos es preferible no tener un índice que tener uno con cardinalidad baja. Por ejemplo si tenemos una tabla con una columna de tipo ENUM no tiene mucho sentido definir un índice para dicha columna puesto que su cardinalidad será con toda probabilidad muy baja con respecto al número de registros de la tabla.

Página 7



4. 3 Rendimiento y optimización de las consultas. 4.3.1 Detectar las consultas lentas. Activar el “slow query log”. Uno de los pasos más importantes a la hora de optimizar y afinar MySQL consiste en localizar las consultas problemáticas, es decir aquellas que llevan mucho tiempo de procesamiento hasta generar la respuesta y se identifican como slow queries. La forma más cómoda, desde el punto de vista del administrador, que ofrece el servidor MySQL para detectar este tipo de consultas es activar lo que se conoce como el slow query log. Este tipo de log no viene activado en la configuración por defecto de MySQL y es necesario realizar las siguientes entradas en el fichero de configuración (my.ini o my.cnf) para que se active la próxima vez que se reinicie el servidor: [ mysql d] l ong_quer y_t i me l og- sl ow- quer i es

=5 = / var / l og/ mysql / mysql - sl ow. l og

Una vez activado, y según el ejemplo anterior, todas las consultas que superen los 5 segundos de tiempo de respuesta van a quedar registradas en el fichero mysql - sl ow. l og y por lo tanto fácilmente detectadas por el administrador. Veamos el siguiente extracto del fichero slow query log: . . # Ti me: 061122 23: 19: 37 # User @Host : r oot [ r oot ] @ l ocal host [ 127. 0. 0. 1] # Quer y_ t i me: 790 Lock_t i me: 0 Rows_ sent : 39 Rows_exami ned: 2123567 sel ect d escri pci on, di r ecci on, cant f r om ar t i cul o, al macen, st ock wher e ar t i cul o. i d_ar t =st ock. i d_ar t and al macen. i d_al m=st ock. i d_al m and al macen. t el ef ono=' 029- 8661395' ; . . .

En este extracto se puede observar la consulta que ha quedado registrada, el número de registros que han sido examinados (¡2123567 registros!), el tiempo empleado ha sido 790 segundos (algo más de ¡13 minutos!) Es evidente que si esta consulta se puede repetir en más ocasiones requiere una urgente optimización. El problema este logconsideradas es que si haylentas, mucho tráfico en el servidor es probableyque también haya muchascon consultas entonces el log crece rápidamente se hace difícil extraer de él información útil. Lo más importante es extraer del log las consultas lentas con mayor número de ocurrencias y también las consultas con más tiempo de procesamiento para proceder a su optimización (siempre que sea posible). Hay un script de Perl que viene con la distribución de MySQL que se llama mysqldumpslow y que puede ser muy útil para extraer información del slow query log cuando este ha crecido y ha alcanzado un tamaño considerable. Por ejemplo si queremos que mysqldumpslow

Página 8



extraiga del fichero log ordenado por número de ocurrencias las cinco consultas lentas que más veces aparecen en el fichero slow query log debemos lanzar el siguiente comando desde la consola: C: \ wamp\ mysql \ scr i pt s>per l mysql dumpsl ow - t 1 C: \ wamp\ l ogs\ mysql - sl ow. l og

Nota: Conviene observar que este ejemplo se esta realizando en un servidor MySQL bajo Windows por lo que necesita tener instalado un interprete de Perl para Windows (ver Anexo 2 de este documento: “Instalar interprete de Perl para Windows –ActivePerl”).

4.3.2 Optimización de consultas mediante la sentencia EXPLAIN.

La sentencia Explain es útil para averiguar como MySQL va a proceder a ejecutar la consulta mostrando al usuario el plan de ejecución de dicha consulta. El primer ejemplo del uso de Explain lo vamos a ver usando una consulta muy rápida sobre la tabla l i bro s de la base de datos curso para obtener información de un registro concreto conocido el valor de su clave primaria: mysql > sel ect t i t ul o, aut or f r om l i br os wher e i d=9012\ G ************ ************* ** 1 . r ow ************ ************* ** t i t ul o: Mar i posas nocturn as y d i ur nas : conocer y c l asi f i car l as mar i posas más i mpor t ant es de Eur opa aut or : Hel ga Hof mann, Thomas Mar kt anner Hof f mann, Hel ga Mar kt anner , Thomas 1 r ow i n set ( 0. 00 sec)

MySQL sabe antes de ejecutar la consulta anterior que solo puede haber un registro de salida a lo sumo, si es que existe ese valor de la clave primaria. La información que genera Explain sobre esta consulta es la siguiente: mysql > expl ai n sel ect t i t ul o, aut or f r om l i br os wher e i d=9012\ G ************ ************* ** 1 . r ow ************ ************* ** i d: 1 sel ect _t ype: SI MPLE ta bl e: l i bro s t ype: const possi bl e_keys: PRI MARY key: PRI MARY key_l en: 4 r ef : const r ows: 1 Ext r a: 1 r ow i n set ( 0. 00 sec)

Los campos de información que proporciona el comando Explain son, brevemente comentados, los siguientes: id: El identificador de la salida generada por Explain. Explain genera un registro por cada tabla involucrada en la consulta. select_type: Los posibles valores de este campo son SI MPLE, PRI MARY, UNI ON, DEPENDENTUNI ON, SUBSELECT, y DERI VED. Dependiendo del tipo de •

•

Página 9


• •


construcción de la consulta llevará uno u otro. En el ejemplo anterior es SI MPLE porque no se utilizan subconsultas ni uniones ni joins. table: El nombre de la tabla de la que se están leyendo los registros. type: Indica el tipo de join de la consulta. Puede tomar los valores const , eq_r ef , r ef , r ange, i ndex, o ALL . Vamos a comentar brevemente estos valores por la importancia que tiene una mala elección de join sobre el rendimiento de la consulta: ALL . Significa que es necesario recorrer y leer todos los registros de la tabla para satisfacer la consulta. Suele suceder cuando no hay ningún tipo de índice asociado o buscamos subcadenas que no pueden ser indexadas. Es importante evitar este tipo de consultas en tablas con elevado número de registros y más aun si en la consulta hay joins con otra tablas. Por ejemplo: 

sel ect ti tu l o f ro ml i bro s wher e aut or l i ke ' %CERVANTES%' 



const . Cuando es posible utilizar un único valor de clave primaria o índice tipo UNI QUE para resolver la consulta. En el ejemplo anterior (sel ect t i t ul o, aut or f r om l i br os wher e i d=9012 ) el type es const por que hay solo un valor constante en la consulta que es el valor 9012. r ange. Cuando para resolver la

consulta se utilizan índices para localizar datos entre un determinado rango de valores. La eficiencia de este tipo de consultas depende de la amplitud del rango y de la cardinalidad del índice. (Ya sabemos que un índice de baja cardinalidad indica que muchos registros están asociados a un mismo valor del índice): sel ect

ti tu l o f ro m l i bro s

wher e i d bet ween 6321 and 6327; 

i ndex. Cuando sobre una tabla hay

un índice asociado y MySQL detecta que es capaz de responder la consulta únicamente a partir de la información del propio índice sin necesidad de recuperar datos de la tabla asociada al índice. Por ejemplo si suponemos que a la tabla libros se le ha añadido el siguiente índice: mysql > ALTER TABLE l i br os ADD I NDEX ( t i t ul o) ;

MySQL es ahora capaz de responder a la siguiente consulta solo accediendo al índice recién creado: sel ect ti tu l o f ro ml i bro s wher e UPPER( t i t ul o) l i ke' %QUI J OTE%' ;

La principal ventaja de este tipo de búsqueda es que el índice es más pequeño que la tabla. Si la consulta se modifica añadiendo columnas que ya no están en el índice MySQL esta obligado a leer la tabla completa y el type pasaría a ALL . sel ect ti tu l o, aut or f ro m l i bro s wher e UPPER( t i t ul o) l i ke' %QUI J OTE%' ; 

r ef _or_n ul l . Cuando para la búsqueda se utiliza un índice ordinario que puede tener valores duplicados. En el siguiente ejemplo se hace una búsqueda de población introduciendo un código postal concreto. La tabla codi gospost al es tiene un índice asociado a este campo que es un índice ordinario (el código postal no puede ser clave primaria en la tabla por que varias poblaciones pueden pertenecer al mismo código). ref /

sel ect pobl aci on f r om codi gospost al es

Página 10



wher e cod_pos=' 300001' ;

Si la sentencia Explain retorna r ef _or _nul l es porque también se están buscando registros asociados a los valores nulos del índice. Por ejemplo: sel ect pobl aci on f r om codi gospost al es wher e cod_pos=' 300001' or cod _pos i s nul l ; 

eq_r ef . Este tipo de busqueda indica que hay un join establecido y que la tabla marcada como eq_r ef por la sentencia Explain avisa que está usando una clave primaria o índice tipo uni que para resolver la consulta de forma

mucho más rápida. Por ejemplo si queremos saber los títulos de las bibliotecas que tienen como nombre ‘Universidad Carlos III’ necesitamos hacer un join de las tres tablas de esta forma.

sel ect t i t ul o f r om ej empl ar es e , bi bl i ot ecas b, l i bros l wher e upper ( nombr e) l i ke ' %UNI VERSI DAD CARLOS I I I %' AND e. i d_bi b=b. i d and e. i d_t i t =l . i d

Si se observa la salida generada por Explain para esta sentencia se ve que se generan tres registros uno por cada tabla implicada: *************

************** 1. i d: 1 sel ect _t ype: SI MPLE t abl e: b t ype: ALL possi bl e_keys: PRI MARY key: NULL key_l en: NULL r ef : NULL r ows: 70 Ext r a: ************* ************** 2.

r ow **************

*************

r ow **************

*************

i d: 1 sel ect _t ype: SI MPLE t abl e: e t ype: r ef possi bl e_ke ys: f k_ej empl ar es1, f k_ej empl ar es2 key: f k_ej empl ar es2 key_l en: 5 re f : curso . b. i d r ows: 425 Ext r a: Usi ng wher e ************* ************** 3. r ow ************** i d: 1 sel ect _t ype: SI MPLE ta bl e: l t ype: eq_r ef possi bl e_keys: PRI MARY key: PRI MARY key_l en: 4 re f : curso . e. i d_ti t

*************

r ows: 1 Ext r a: Usi ng wher e

El primer registro marca el type para la tabla bi bl i ot ecas como ALL, es decir debe leer toda la tabla bi bl i ot ecas para obtener los registros. El segundo registro marca el type para la tabla ej empl ar es como r ef porque utiliza para el join, como referencia, la clave ajena que apunta a la tabla bi bl i ot ecas . Ese índice no es un índice único para la tabla ejemplares puesto que esta repetido en muchos registros.

Página 11


•

•

• •


Por último, y es a donde queríamos llegar, el tercer registro marca el type de la tabla l i bro s como eq_r ef porque el índice usado es la clave primaria de la tabla y por lo tanto es único, es decir, solo hay un posible registro de la tabla l i bro s una vez determinado el ejemplar. possible_keys: El listado de posibles índices que pueden intervenir en la consulta, o NULL si no hay ninguno posible. key: El nombre del índice que MySQL ha decidido usar para resolver la consulta. Debe encontrarse entre el listado de possible_keys anterior y se supone que es el más adecuado. key_len: El tamaño en bytes del índice seleccionado. ref: El campo ref normalmente solo toma valor distinto de NULL cuando el campo type indica que la consulta es r ef o eq_r ef (normalmente en las consultas tipo join donde hay involucradas claves ajenas). En este caso el campo ref muestra que columnas son usadas para seleccionar las filas de la tabla o bien si es un valor constante el usado en su lugar. En el siguiente ejemplo vamos a obtener los códigos de los ejemplares que se encuentran en las bibliotecas de la ciudad de Salamanca. sel ect i d_t i t f r om bi bl i ot ecas b, ej empl ar es e wher e upper ( ci udad) l i ke ' %SALAMANCA%' and b. i d=e. i d_bi b

•

•

Si se observa la sentencia Explain que se genera vemos que el registro debido a la tabla ejemplares tiene un campo type con valor r ef y el campo ref tiene el valor correspondiente a la columna de la tabla bi bl i ot eca a la que apunta una de las claves ajenas de esta tabla es decir bi bl i ote ca. i d. rows: Antes de ejecutar la consulta MySQL estima en este campo el número de filas que va a generar, conviene prestar atención cuando el valor de rows es muy alto, normalmente esta indicando que la consulta requiere un escaneo total de tabla (suele type como ALL ). Para que las estadísticas que ir asociado con un valor del campo genera MySQL y en las que se basa la estimación del valor del campo rows sean válidas conviene que esten actualizadas,para ello es necesario ejecutar de forma más o menos periodica las sentencias ANALYZE TABLEo OPTI MI ZE TABLE. Extra: Información adicional que muestra MySQL sobre la consulta. Los valores más comunes de este campo suelen ser: Usi ng wher e. Cuando la consulta tiene una cláusula WHERE. Usi ng f i l esor t . Cuando la consulta requiere una salida ordenada mediante ORDER BY sobre una columna no indexada o bien estando indexada pero con un índice que no ha sido usado para extraer las filas en dicha consulta. Usi ng i ndex . Cuando los datos de la consulta se obtienen leyendo únicamente información del índice sin necesidad de leer registros de la tabla (está muy asociado este comentario con el type index). Usi ng i nt er sect (solo para versiones posteriores a MySQL 5.0). Una mejora que incorpora MySQL 5.0 sobre las versiones anteriores es la capacidad de usar más de un índice para resolver una consulta sobre una tabla concreta que debe cumplir una doble condición enlazada por el operador AND. Cada uno de los índices esta asociado a cada una de las condiciones de la clausula WHI LE. En el siguiente apartado de este documento ‘4.3.3 Ejemplos del uso de la sentencia Explain’ se comenta una consulta donde aparece Usi ng i nt er sect . Usi ng uni on (solo para versiones posteriores a MySQL 5.0). Similar a usi ng i nst er sect con la diferencia que se emplea un OR para enlazar la doble condición que permite usar dos índices simultáneamente.  







Página 12



4.3.3 Ejemplos del uso de la sentencia EXPLAIN. Ejemplo1. En el siguiente ejemplo de Explain vamos a realizar una consulta para obtener información a partir de un rango de valores de la clave primaria. mysql > sel ect t i t ul o f r om l i br os wher e i d bet ween 6321 and 6327; +--- --- --- --- --- --- --- --- --- --- --- --+ | ti tu l o | +--- --- --- --- --- --- --- --- --- --- --- --+ | Psi col ogi a soci al | | Anat omi a gener al | | Vi aj es por Mar r uecos | | Los si et e pi l ar es de l a sabi dur i a | | La I l ust r aci on en España | | Del phi 3. 0 | +--- --- --- --- --- --- --- --- --- --- --- --+ 6 r ows i n set ( 0. 25 sec) mysql > expl ai n sel ect t i t ul o f r om l i br os wher e i d bet ween 6321 and 6327\ G ************

************* i d: 1 sel ect _t ype: SI MPLE ta bl e: l i bro s t ype: r ange possi bl e_keys: PRI MARY

** 1 . r ow ************

*************

**

PRI MARY key_lkey: en: 4 r ef : NULL r ows: 7 Ext r a: Usi ng wher e 1 r ow i n set ( 0. 00 sec)

Podemos observar la diferencia entre buscar en un rango o buscar un valor único. El campo type pasa de valer de const a r ange, lo que indica que esta buscando no un único valor sino un rango de ellos. También se observa que el campo rows vale 7, aunque la consulta devuelve 6 filas. Esto se debe a que el campo rows es obtenido a partir de una estimación de las filas que puede generar esa consulta. Lo que hace MySQL es estimar cuantas filas en una clave autonumérica puede haber entre los valores 6327 y 6321 y el resultado es 7 aunque su valor real almacenado sea 6 puesto que el registro con la clave 6325 no aparece en la tabla. Ejemplo 2. El siguiente ejemplo va a ser realizar una consulta buscando valores de una columna que no tiene índice asociado. mysql > sel ect count ( *) f r om l i br os wher e numpagi nas>700; +- - - - - - - - - - + | count ( *) | +- - - - - - - - - - + | 3738 | +- - - - - - - - - - + 1 r ow i n set ( 0. 41 sec)

Página 13



mysql > expl ai n sel ect count ( *) f r om l i br os wher e numpagi nas>700\ G ************ ************* ** 1 . r ow ************ ************* ** i d: 1 sel ect _t ype: SI MPLE ta bl e: l i bro s t ype: ALL possi bl e_key s: NULL key: NULL key_l en: NULL r ef : NULL r ows: 10009 Ext r a: Usi ng wher e 1 r ow i n set ( 0. 00 sec)

Se observa que el valor NULL en el campo key indica que no está utilizando ningún índice para resolver la cónsulta y el campo posible_keys índica que no existe ningún índice posible para resolverla. También conviene fijarse que el campo type indica ALL , es decir, el rango de búsqueda de posibles valores se extiende a toda la tabla. Vamos, a continuación, a crear un índice sobre la columna numpagi nas para comprobar como la consulta utiliza dicho índice, debido a ello mejora considerablemente su velocidad de respuesta y Explain nos va a confirmar que, efectivamente, el índice creado es utilizado por la consulta. mysql > ALTER TABLE l i br os ADD I NDEX ( numpagi nas) ; Quer y OK, 9918 r ows af f ect ed ( 0. 77 sec) Recor ds: 9918 Dupl i cat es: 0 War ni ngs: 0 mysql > sel ect SQL_NO_CACHE count ( *) f r om l i br os wher e numpagi nas>700; +- - - - - - - - - - + | count ( *) | +- - - - - - - - - - + | 3738 | +- - - - - - - - - - + 1 r ow i n set ( 0. 01 sec) mysql > expl ai n sel ect count ( *) f r om l i br os wher e numpagi nas>700\ G ************ ************* ** 1 . r ow ************ ************* ** i d: 1 sel ect _t ype: SI MPLE ta bl e: l i bro s t ype: r ange poss i bl e_keys: NUMPAGI NAS key: NUMPAGI NAS key_l en: 5 r ef : NULL r ows: 5044 Ext r a: Usi ng wher e; Usi ng i ndex 1 r ow i n set ( 0. 00 sec)

Se observa como la velocidad de ejecución se ha incrementado de 0.41 segundos a 0.01 segundos, también Explain indica que ha utilizado el índice NUMPAGINAS, ha cambiado el campo type a r ange al estar la columna indexada. Es interesante fijarse en la diferencia entre el número de filas estimadas 5044 y el número de filas reales 3738, es posible que MySQL sea capaz de mejorar esas estimaciones una vez ejecutado ANALI ZE TABLE sobre la tabla l i bro s .

Página 14



Ejemplo 3. Vamos a ver otro ejemplo de Explain en el que se va a efectuar una consulta basada en dos columnas indexadas. MySQL buscará seleccionar de los dos posibles índices aquel que menos filas tenga que examinar. mysql > sel ect count ( * ) f r om l i br os wher e numpagi nas >998 and i d < 6000; +- - - - - - - - - - + | count ( *) | +- - - - - - - - - - + | 5| + - - - i- n- - set - - + ( 0. 03 sec) 1- r- ow mysql > expl ai n sel ect count ( *) f r om l i br os wher e numpagi nas >998 and i d < 6000\ G ************

************* ** 1 . r ow ************ i d: 1 sel ect _t ype: SI MPLE ta bl e: l i bro s t ype: r ange poss i bl e_keys: PRI MARY, NUMPAGI NAS key: NUMPAGI NAS key_l en: 5 r ef : NULL r ows: 9 Ext r a: Usi ng wher e; Usi ng i ndex 1 r ow i n set ( 0. 00 sec)

*************

**

Se observa que en este caso MySQL ha optado entre PRI MARY y NUMPAGINAS por NUMPAGINAS porque ha detectado que el valor 998 está casi en uno de los extremos superiores del índice y van a ser muy pocas filas las que necesite examinar. Si cambiamos el valor de 998 por el de 220 vemos que MySQL opta por cambiar el índice elegido para la consulta porque detecta que prácticamente todas las filas de la tabla tienen un valor superior a 220 en la columna NUMPAGI NAS. mysql > sel ect count ( * ) f r om l i br os wher e numpagi nas >220 and i d < 6000; +- - - - - - - - - - + | count ( *) | +- - - - - - - - - - + | 5857 | +- - - - - - - - - - + 1 r ow i n set ( 0. 02 sec) m ysqli > ai n Gsel ect count ( *) f r om l i br os wher e numpagi nas >220 and d expl < 6000\ ************

************* ** 1 . r ow ************ i d: 1 sel ect _t ype: SI MPLE ta bl e: l i bro s t ype: r ange poss i bl e_keys: PRI MARY, NUMPAGI NAS key: PRI MARY key_l en: 4

*************

**

Página 15



r ef : NULL r ows: 5044 Ext r a: Usi ng wher e 1 r ow i n set ( 0. 00 sec)

Ejemplo 4. Utilizar la combinación de dos índices simultáneamente. Por ejemplo suponemos que queremos lanzar la siguiente consulta: sel ect * f r om l i br os wher e i d bet ween 1000 and 2000 and numpagi nas=300;

Vemos que en esta consulta se pueden usar dos índices simultáneamente para resolver la consulta, la clave primaria i d y la columna numpagi nas que también esta indexada como se Explain a esta consulta vio en el genera la ejemplo siguienteanterior. salida: El resultado de lanzar la sentencia ************

************* ** 1 . r ow ************ ************** * i d: 1 sel ect _t ype: SI MPLE ta bl e: l i bro s t ype: i ndex_mer ge poss i bl e_keys : PRI MARY, NUMPAGI NAS key: NUMPAGI NAS, PRI MARY key_l en: 5, 4 r ef : NULL r ows: 3 Ext r a: Usi ng i nt er sect ( NUMPAGI NAS, PRI MARY) ; Usi ng wher e 1 r ow i n set ( 0. 34 sec)

Esta posibilidad, como ya se ha comentado, solo es posible a partir de la versión MySQL 5.0 cuando el campo Extra, como en este ejemplo, incluye el mensaje Usi ng i nt er sect lo cual significa que MySQL esta usando un algoritmo index_merge utilizando los dos índices para resolver una consulta y al final estableciendo un merge con los resultados de la intersección generada por los registros devueltos por cada uno de los índices asociados a cada condición WHERE de la sentencia sel ect.

4.3.4 Comando ANALYZE

TABLE.

Este comando sirve para actualizar las estadísticas de una tabla y todos sus índices. Durante el análisis la tabla es bloqueada en modo read lock. El comando funciona solo para tablas de tipo MyI SAM, BDB e I nnoDB. La información que genera este comando la usa MySQL para decidir, por ejemplo, el orden de los joins entre varias tablas cuando se efectúa una consulta de este tipo. Es aconsejable ejecutar este comando periódicamente sobre todo para tablas que tienen frecuentes modificaciones en sus datos que afectan a las columnas indexadas. Mediante el uso del comando SHOW I NDEX podemos observar los cambios en la información relativa a los índices que genera el comando ANALYZE TABLE. Veamos el siguiente ejemplo sobre una tabla concreta l i bro s que tiene dos índices asociados, su clave primaria y numpaginas. mysql > show i ndex f r om l i br os \ G

Página 16

Lenguaje SQL con MySQL avanzado ************ ************* Tabl e: l i br os Non_ uni que: 0 Key_name: PRI MARY Seq_i n_i ndex: 1 Col umn_name: i d Col l at i on: A Car di nal i t y: 9558 Sub_par t : NULL Packed: NULL Nul l : I ndex_t ype: BTREE


** 1 . r ow ************

**************

*

Comment :************* ** 2 . r ow ************ ************ Tabl e: l i br os Non_ uni que: 1 Key_ name: NUMPAGI NAS Seq_i n_i ndex: 1 Col umn_name: NUMPAGI NAS Col l at i on: A Car di nal i t y: 1593 Sub_par t : NULL Packed: NULL Nul l : YES I ndex_t ype: BTREE Comment : 2 r ows i n set ( 0. 22 sec)

**************

*

Ejecutamos el comando ANALYZE TABLEsobre esta tabla mysql > anal yze t abl e l i br os; +-- -- -- -- -- -- -- +-- -- -- -| Tabl e | Op +-- -- -- -- -- -- -- +-- -- -- -| cur so. l i br os | anal yze +-- -- -- -- -- -- -- +-- -- -- -1 r ow i n set ( 0. 04 sec)

-+-- -- -- -- -- +-- -- -- -- -- + | Msg_t ype | Msg_t ext | -+-- -- -- -- -- +-- -- -- -- -- + | statu s | OK | -+-- -- -- -- -- +-- -- -- -- -- +

Volvemos a lanzar de nuevo el comando SHOW I NDEX para observar como ha cambiado la información relativa a los índices. El cambio afecta fundamentalmente al atributo Car di nal i t y, que se refiere al número estimado de valores distintos en el índice (ojo con la palabra estimado, porque no tiene por qué coincidir con el valor real en un momento dado). mysql > show i ndex f r om l i br os \ G ************ ************* ** 1 . r ow ************ Tabl e: l i br os Non_ uni que: 0 Key_name: PRI MARY Seq_i n_i ndex: 1 Col umn_name: i d Col l at i on: A Car di nal i t y: 10182 Sub_par t : NULL Packed: NULL Nul l : I ndex_t ype: BTREE Comment : ************ ************* ** 2 . r ow ************ Tabl e: l i br os

**************

*

**************

*

Página 17

Lenguaje SQL con MySQL avanzado Non_ uni que: Key_ name: Seq_i n_i ndex: Col umn_name: Col l at i on: Car di nal i t y: Sub_par t : Packed: Nul l : I ndex_t ype: Comment : 2 r ows i n set


1 NUMPAGI NAS 1 NUMPAGI NAS A 1697 NULL NULL YES BTREE ( 0. 37 sec)

4.3.5 Comando OPTIMIZE

TABLE.

Este comando debe ser usado cuando se han eliminado gran parte de las filas de una tabla o se han efectuado numerosas modificaciones en tablas con columnas tipo VARCHAR , BLOBo TEXT. Podemos decir que este comando equivale a desfragmentar el espacio de datos de una tabla. Solo funciona en tablas del tipo MyI SAM, BDB e I nnoDB. OPTI MI ZE TABLE también bloquea la tabla durante su ejecución, como hace ANALYZE TABLE, la diferencia es que OPTI MI ZE TABLE es mucho más lento puesto que tiene que

reallocar los datos de la tabla para hacer la desfragmentación por lo tanto es importante saber cuando se va a lanzar el proceso de optimización. Lo habitual seria una vez a la semana o al mes y solo para ciertas tablas. Veamos un ejemplo guiado paso a paso para ver los efectos de usar el comando OPTI MI ZE TABLE. 1. Se crea una tabla nueva para el ejemplo: cr eat e campo1 campo2 campo3

t abl e ej empl o_opt i mi ze ( i nt aut o_i ncr ement pr i mar y key, var char ( 50) , var char ( 100) ) engi ne=myi sam;

2. Insertamos valores aleatorios en las columnas campo2 y campo3 hasta tener unos 100.000 registros en la tabla. (Se ha usado la utilidad Datagen para insertar múltiples filas en una tabla. Para ver el uso de la utilidad Datagen consultar el Apéndice 1: “Uso de la herramienta DataGen para insertar múltiples filas en tablas de prueba”). 3. Se crea un índice sobre la columna campo3: cr eat e i ndex i ndi ce1 on ej empl o_opt i mi ze( campo3) ;

4. Vemos los datos de tamaño de la tabla mediante el comando show st at us : mysql > show t abl e st at us l i ke ' %ej empl o_opt %' \ G ************ ************* ** 1 . r ow ************ ************** Name: ej empl o_opt i mi ze Engi ne: MyI SAM Vers i on: 10 Row_f or mat : Dynami c Rows: 100000

*

Página 18

Lenguaje SQL con MySQL avanzado Avg_r ow_l engt h: Dat a_l engt h: Max_dat a_l engt h: I ndex_l engt h: Dat a_f r ee: Aut o_i ncr ement : Cr eat e_t i me: Updat e_t i me: Check_ t i me: Col l at i on: Checksum: Cr eat e_opt i ons:


39 3928312 281474976710655 1029120 0 110001 2006- 06- 15 13: 21: 16 2006- 06- 15 13: 25: 56 NULL l at i n1_swedi sh_ci NULL

1 r ow i n Com setment ( 0.:00 sec)

5. Hacemos un borrado múltiple y discontinuo de registros. Para ello mediante la siguiente sentencia podemos borrar uno de cada cuatro registros : mysql > del et e f r om ej empl o_opt i mi ze wher e mod( campo1, 4) =0; Quer y OK, 25000 r ows af f ect ed ( 0. 60 sec)

6. El tamaño de la tabla es el siguiente después del borrado: Rows: Avg_r ow_l engt h: Dat a_l engt h: Max_dat a_l engt h: I ndex_l engt h: Dat a_f r ee:

75000 39 3928312 281474976710655 1029120 983608

Donde se observa que el tamaño total no ha variado ni tampoco el tamaño del índice 7. Volvemos a insertar 25000 nuevos registros en la tabla nuevos usando la utilidad Datagen. Volvemos a tener por lo tanto 100000 registros. El tamaño de la tabla y el índice

indican lo siguiente: Rows: Avg_r ow_l engt h: Dat a_l engt h: Max_dat a_l engt h: I ndex_l engt h: Dat a_f r ee:

100000 42 4224160 281474976710655 1286144 0

8. Optimizamos la tabla mysql >opt i mi ze t abl e ej empl o_opt i mi ze;

9. Para finalizar observamos que los datos una vez optimizada son los siguientes: Rows: Avg_r ow_l engt h: Dat a_l engt h: Max_dat a_l engt h: I ndex_l engt h: Dat a_f r ee:

100000 39 3927044 281474976710655 1022976 0

Ha disminuido el tamaño total ocupado por los datos y el tamaño ocupado por el índice.

Página 19



4.4 Gestión de la memoria en el Servidor. Como ya se sabe toda la gestión de memoria en un servidor de base de datos se centra en evitar lo más posible el acceso a disco intentando realizar la mayor cantidad de operaciones exclusivamente en accesos a RAM. Los siguientes apartados pretenden explorar algunos aspectos que permiten optimizar el uso de la memoria RAM y minimizar en lo posible los accesos a disco.

threads 4.4.1 Ajustar las propiedades que genera MySQL. de memoria de los Un thread es un hilo de ejecución que genera MySQL cada vez que un nuevo cliente se conecta al servidor. Si lanzamos la siguiente sentencia podemos observar las dos variables que el administrador puede manipular para optimizar la gestión de la memoria en lo que se refiere a los hilos de ejecución. mysql > show var i abl es l i ke ' t hr ead%' ; +-- -- -- -- -- -- -- -- -- -+-- -- -- -- + | Var i abl e_name | Val ue | +-- -- -- -- -- -- -- -- -- -+-- -- -- -- + | t hr ead_ca che_si ze | 12 | | t hr ead_st ack | 196608 | +-- -- -- -- -- -- -- -- -- -+-- -- -- -- + t hr ead_st ack indica la cantidad de memoria que cada thread tiene La variable por garantizada el sistema cuando se inicia. La variable t hr ead_cach e_si ze el número de threads que se mantienen vivos en memoria caché esperando nuevas conexiones.

Por ejemplo, en un entorno de conexión a un servidor MySQL a través de un servidor web que genera muchas conexiones a la base de datos de muy corta duración, puede ser interesante para el administrador, incrementar el valor de thread_cache_size para ahorrar de esta manera el tiempo de creación de nuevos threads. Para observar el número de trheads que está generando nuestro servidor MySQL lo vemos fácilmente mediante SHOW STATUS. mysql > show st at us l i ke ' +-- -- -- -- -- -- -- -- -- -- -- -| Var i abl e_name +-- -- -- -- -- -- -- -- -- -- -- -| Del ayed_i nser t _t hr eads | Sl ow_l aunch_t hr eads | Thr eads_cach ed | Thr eads_con nect ed | Thr eads_cr eat ed | Thr eads_r unni ng +-- -- -- -- -- -- -- -- -- -- -- --

%t hr ead%' ; +-- -- -- -+ | Val ue | +-- -- -- -+ | 0 | | 0 | | 10 | | 2 | | 14 | | 1 | +-- -- -- -+

En este ejemplo se ve que el servidor ha creado catorce threads, aunque solo hay dos threads conectados y por lo tanto quedan diez esperando en caché.

Página 20



4.4 2 Utilización siempre que sea posible de las tablas tipo MEMORY. MySQL incorpora la posibilidad de crear tablas de almacenamiento solo en RAM usando la arquitectura de almacenamiento conocida como MEMORY(o HEAPen versiones anteriores). Este tipo de tablas también son usadas internamente por el servidor MySQL para la creación de tablas temporales en la resolución, por ejemplo, de consultas complejas. El administrador puede modificar el valor de la variable max_heap_t abl e_si ze , que determina el tamaño máximo que puede tener una tabla de tipo MEMORY. Si alguna aplicación genera un error del tipo The t abl e ' mess age_ buf f er _mem' i s f ul l , S1000, 1114

es cuando el administrador debería plantearse incrementar el valor de dicha variable.

4.4.3 Descripción y uso de la Query Cache de MySQL. Es importante entender las diferencias existentes entre los cuatro mecanismos de caché que administra el servidor MySQL: •

Buffer pool. Este caché almacena datos e índices de aquellas tablas con arquitectura

•

Key cache. Este tipo de caché se utiliza para almacenar información de los índices

•

Memory pool. Este caché lo utiliza MySQL fundamentalmente para almacenar

de almacenamiento InnoDB.

•

de las tablas tipo MyISAM.

información del diccionario de datos. Solo se benefician de este tipo de caché las tablas tipo InnoDB. Query cache. Este tipo de caché almacena consultas y resultados de consulta realizada sobre cualquier tabla independientemente de la arquitectura de almacenamiento de la misma.

La query cache de MySQL es un elemento opcional que el administrador puede o no tenerla activada. Si está activada el servidor primero consulta la query cache para localizar tanto la consulta lanzada como los resultados de la consulta sin necesidad de hacer ningún acceso a disco. Localizar los datos en la query cache supone, por lo tanto, una importante mejora en el rendimiento. También es importante saber que tipo de sucesos obligan a actualizar los datos de la query cache: •

Sentencias de consultas diferentes. MySQL no consulta la query cache si una consulta no es exactamente igual a una ejecutada anteriormente. Para MySQL la consulta: SELECT * f r om mi t abl a;

Es diferente de la consulta: sel ect * f r om mi t abl a;

Página 21


•

•

•


Finalización de transacciones. Cuando se lanza un commi t (en tablas tipo InnoDB) MySQL anula todos los datos de la query cache que hacen referencia a cualquiera de las tablas que han sido afectadas por la transacción. Modificaciones en los datos o en las estructuras de tablas y/o índices. En tablas con alta carga transaccional esto sucede constantemente. Además no solo se refrescan las posibles filas afectadas por las modificaciones, es necesario refrescar todas las filas de la query cache que hacen referencia a la tabla modificada, incluso las posibles filas que no han sido modificadas. Generación de tablas temporales. Si la consulta realizada se ve obligada a generar tablas temporales para obtener el resultado final en estos casos MySQL no puede hacer uso de la query cache.

Teniendo en cuenta los puntos anteriores se evidencia que activar la Query cache es tanto más efectivo desde el punto de vista del rendimiento cuanto más estables son las tablas afectadas por las consultas.

Activación de la Query cache. Para activar la query cache basta con reservar un espacio de memoria para alojarla. Esto se hace dando un valor a la variable query_cach e si ze , bien usando el comando SET GLOBAL o mejor desde el fichero de configuración my.cnf (o my.ini en Windows). quer y_cache_si ze=8M

También es conveniente indicar mediante la variable quer y_cach e_t ype cómo las consultas van a acceder a la query cache. Hay tres posibilidades. •

•

quer y_ cache_t ype=0/ OFF . Significa que ninguna consulta accede a

la query cache. Está reservada la memoria para la query cache pero ninguna consulta la utiliza. quer y_ cache_t ype=1/ ON. Significa que el acceso a la query cache es implícito para todas las consultas, a no ser que explícitamente se indique en la consulta que no se desea usar la query cache, es decir colocando la opción SQL_NO_CACHE en la sentencia SELECT correspondiente. SELECT SQL_ NO_CACHE * FROM mi t abl a;

Esta opción es la habitual y la que viene por defecto en la configuración del servidor MySQL. Es la más adecuada en bases de datos con más tablas estáticas que dinámicas. •

quer y_ cache_t ype=2/ DEMAND. Significa que el acceso a la query cache debe ser

explícito para que se produzca, es decir, solo se beneficiarán del uso de la query cache las sentencias que lleven la opción SQL_CACHE. SELECT SQL_ CACHE * FROM mi t abl a;

Esta opción es la adecuada en bases de datos con un elevado número de tablas muy dinámicas sobre las que se hacen frecuentes consultas.

Página 22



Monitorizar y optimizar la Query cache. Para poder acceder al estado de la query cache en nuestro servidor MySQL lanzamos la siguiente sentencia: mysql > show st at us l i ke ' %qcache%' ; +-- -- -- -- -- -- -- -- -- -- -- -- -+-- -- -- -- -+ | Var i abl e_name | Val ue | +-- -- -- -- -- -- -- -- -- -- -- -- -+-- -- -- -- -+ | Qcache_f r ee_bl ocks | 36 | | Qcache_f r ee_memor y | 1379888 | | Qcache_hi t s | 9570 | Qcache_i nser t s | 4312 | Qcache_ l owmem_pr unes | 14 | Qcache_not _cache d | 0 | Qcache_queri es_i n_cach e | 1073 | Qcache_t ot al _bl ocks | 2289 +-- -- -- -- -- -- -- -- -- -- -- -- -+-- -- -- --

| | | | | | -+

El valor Qcache_hi t s indica el número de consultas que ha suministrado la query cache. Un importante dato lo suministra Qcache_l owmem_ pr unes, que cuenta las consultas que han debido ser eliminadas de la query cache para liberar espacio que permita añadir nuevas consultas. Si este valor es habitualmente alto indica que la memoria asignada a la caché es escasa. Los valores del ejemplo anterior muestran un estado de la query cache bastante óptimo, el valor de Qcache_hi t s es bastante elevado mientras que Qcache_l owmem_pr unes es pequeño y por lo tanto no significativo. En el siguiente ejemplo de datos se observa un escenario distinto donde la situación se ha degradado bastante: +-- -- -- -- -- -- -- -- -- -- -- -- -+-- -- -- -- -+ | Var i abl e_name | Val ue | +-- -- -- -- -- -- -- -- -- -- -- -- -+-- -- -- -- -+ | Qcache_f r ee_bl ocks | 215 | | Qcache_f r ee_memor y | 1822323 | | Qcache_hi t s | 7543 | | Qcache_i nser t s | 19226 | | Qcache_ l owmem_pr unes | 6211 | | Qcache_not _c ached | 2810 | | Qcache_queri es_i n_cach e | 398 | | Qcache_t ot al _bl ocks | 1603 | +-- -- -- -- -- -- -- -- -- -- -- -- -+-- -- -- -- -+

Se observa como ha descendido el ratio de hits sobre el total de las consultas que han pasado por la query cache al tiempo que ha aumentado considerablemente el número de consultas eliminadas por falta de espacio. Quizá el administrador en esta situación debería plantearse la necesidad de asignar más espacio a la query cache al tiempo que debería tratar de determinar que consultas, probablemente, no se van a beneficiar de la query cache para añadir la opción SQL_ NO_ CACHE en sus sentencias SELECT.

Página 23



4.4.4 Descripción y uso de la Caché de claves (Key Cache) de MySQL. En la arquitectura de tablas MyISAM se emplea un mecanismo para almacenar en caché los bloques de índices más utilizados. Esta caché es accesible concurrentemente por distintas conexiones de clientes (distintos threads). Existe la posibilidad de crear varias cachés de claves distintas en lugar de la única caché de claves por defecto que configura MySQL. Se puede crear, por ejemplo, una caché de claves para soportar los índices de las tablas con elevada carga transaccional y alto contenido dinámico y otro caché de claves distinto para las tablas más estáticas. Separar las cachés de claves significa que distintos usuarios o aplicaciones no se interferirán con otros en el uso concurrente del espacio de caché. MySQL usa la caché de claves como todo mecanismo de caché. Cuando necesita acceder a un bloque de índice lo intenta localizar en la caché de claves antes de acceder a disco. Si lo localiza lo lee, o escribe sobre él, dependiendo de la operación que fuera a realizar. Si no lo encuentra el servidor escoge un bloque de la caché conteniendo índices de otra tabla y los sustituye copiando sobre ellos los bloques de índice de la tabla que ha tenido que cargar del disco. Tan pronto como el bloque de índice está en la caché de claves los datos de los índices ya pueden ser accedidos. La estrategia que utiliza MySQL para sustituir los bloques es la llamada por sus siglas LRU (Last Recently Used), es decir mantiene una estructura de lista en la que entra por la cola el último bloque accedido, de tal manera que cuando necesita sustituir un bloque siempre selecciona el que indica la cabeza de la lista. Los bloques de índices pueden ser modificados dentro de la caché de claves. Cuando un bloque es modificado ya no se permite el acceso concurrente a ese bloque. El resto de usuarios que necesiten usar ese bloque deben esperar hasta que el bloque modificado sea escrito en disco.

Múltiples cachés de claves. Como ya se ha comentado MySQL viene por defecto con una sola caché de claves, pero el administrador tiene la opción de asignar distintos índices de tablas a distintas cachés de claves para reducir conflictos de concurrencia a los mismos segmentos de datos entre distintos threads. Lo más recomendable en servidores de datos de mucha carga y con muchos accesos de clientes es establecer tres cachés de claves: Una primera cache (hot_cache) reservada para los índices de las tablas •

•

•

MyISAM que generan muchas consultas pero muy poca modificación. No debe ocupar más del 20% del espacio total reservado para las cachés de claves. Una segunda caché (cold_cache) reservada para las tablas MyISAM que sufren intensas modificaciones en sus datos. Tampoco debe superar más de 20% del espacio total reservado para las cachés de claves. Una tercera que será la caché de claves por defecto, que se usará para alojar los índices del resto de las tablas de tipo MyISAM de la base de datos.

Página 24



Una de las formas de habilitar múltiples caches de claves es indicándoselo al servidor en el fichero my.ini (o my.cnf). En el siguiente ejemplo se ve el extracto del fichero my.ini donde se crean las tres cachés comentadas anteriormente: # Si ze of t he Key Buf f er , used t o cache i ndex bl ocks f or MyI SAM t abl es. # Do not set i t l ar ger t han 30% of your avai l abl e memor y, as some memor y # i s al so r equi r ed by t he OS t o cache r ows. Even i f you' r e not usi ng # MyI SAM t abl es, you shoul d st i l l set i t t o 8- 64M as i t wi l l al so be # used f or i nt er nal t empor ar y di sk t abl es. key_buf f er _si ze=6M hot_cache.key_buffer_size = 2M cold_cache.key_buffer_size = 2M

La sentencia CACHE I NDEX es la que permite asignar los índices de distintas tablas a distintas cachés de claves. Se van a asignar tres tablas de la base de datos curso a la caché que hemos nombrado como hot_cache en el ejemplo: mysql > CACHE I NDEX cl i ent es, r eser vas, vi aj es

I N hot _cach e;

+-- -- -- -- -- -- -- -- +-- -- -- -- -- -- -- -- -- -- +-- -- -- -- -- +-- -- -- -- -- + | Tabl e | Op | Msg_t ype | Msg_t ext | +-- -- -- -- -- -- -- -- +-- -- -- -- -- -- -- -- -- -- +-- -- -- -- -- +-- -- -- -- -- + | cur so. cl i ent es | assi gn_t o_keycache | st at us | OK | | cur so. r eser vas | assi gn_t o_keycache | st at us | OK | | cur so. vi aj es | assi gn_t o_keycache | st at us | OK | +-- -- -- -- -- -- -- -- +-- -- -- -- -- -- -- -- -- -- +-- -- -- -- -- +-- -- -- -- -- + 3 r ows i n set ( 0. 01 sec)

Si se hace la asociación de las tablas con las distintas cachés lanzando el comando CACHE I NDEX desde la consola lo que sucede es que, si reinicia el servidor, se pierde esta

asociación. Lo ideal para que la asociación se mantenga es crear un script de sql (llamado por ejemplo mi s_ caches. sql ) donde se incluyen las sentencias CACHE I NDEX anteriores. Script mi s_cac hes. sql CACHE I NDEX cur so. cl i ent es, cur so. r eser vas, cur so. vi aj es CACHE I NDEX bd2. t abl a1, bd2. t abl a2 I N col d_cache;

I N hot_ cache;

Lo que se hace es incluir en el fichero de opciones my.ini la opción i ni t_ fi cargar el script anterior al arrancar el servidor.

l e que permite

…. key_buf f er _si ze=6M hot_c ache. key_buf f er_s i ze = 2M col d_ca che. key_buf f er _si ze = 2M init_file=/path_a_la_carpeta_del_script/mis_caches.sql

4.4.5 Mejoras en los join, sort, y escaneo completo de tablas MySQL maneja unas variables que permiten asignar más recursos de memoria para realizar escaneos completos de tablas, para realizar joins sobre columnas no indexadas y para realizar ordenaciones en cláusulas GROUP BYo ORDER BY . Dichas variables son Página 25



r ead_buf f er _si ze , j oi n_buf f er _si ze y sor t _buf f er_si ze respectivamente. Asignar

más valor a estas variables puede cambiar de forma sustancial los resultados, sobre todo cuando se esta manejando gran cantidad de información. Normalmente lo más aconsejable es mantener para estas variables un valor medio/bajo para las sesiones por defecto y modificar el valor de estas variables solo para aquellas sesiones que vayan a realizar alguna operación de este tipo que requiera condiciones especiales de memoria (mediante el uso de SET SESSI ON). De esta manera no se desperdician grandes cantidades de memoria que, a la larga, puedan perjudicar el rendimiento global de todo el sistema. mysql > set sessi on sor t _buf f er _s i ze=20786400; Quer y OK, 0 r ows af f ect ed ( 0. 00 sec)

En el siguiente ejemplo comentado se ve como puede variar el rendimiento de una operación modificando el valor de una de estas variables. Lo que se pretende es volcar la información de una tabla sobre otra de idéntica estructura pero realizando la inserción de los registros ordenados por una determinada columna. Esto supone realizar un sort masivo de datos y se puede observar como, cambiando el valor de la variable sort _buf f er_si ze , podemos disminuir considerablemente el tiempo de ejecución de la sentencia. 1. Tenemos una tabla llamada ej empl osor t que tiene la siguiente estructura: CREATE TABLE pr uebasor t ( i d I NT UNSI GNED NOT NULL, f echa DATE NOT NULL, i d_cl i ent e I NT NOT NULL, cant i dad DECI MAL( 5, 2) NOT NULL, descr i pci on var char ( 100) ) ENGI NE = MYI SAM;

2. A esta tabla le insertamos, para poder realizar el ejemplo, cinco millones de registros. (Ver el apéndice “Uso de la herramienta DataGen para insertar múltiples filas en tablas de prueba “). 3. Creamos una nueva tabla llamada pr uebasor t 2 con estructura de datos idéntica a pr uebasor t . CREATE TABLE pr uebasor t 2 LI KE pr uebasor t ;

4. El siguiente paso es volcar la información de la tabla pruebasort a la tabla pruebasort2 pero realizando las inserciones ordenadas por la columna cantidad. mysql > I NSERT I NTO pr uebasor t 2 SELECT * FROM pr uebasor t ORDER BY cant i dad; Quer y OK, 5000000 r ows af f ect ed ( 15 mi n 35. 28 sec) Recor ds: 5000000 Dupl i cat es: 0 War ni ngs: 0

Se han necesitado más de 15 minutos para completar la operación. 5. Es interesante ver ahora las variables de status que afectan a la ordenación para analizar como se ha realizado el algoritmo de sort.

Página 26



mysql > show st at us l i ke ' %sor t %' ; +-- -- -- -- -- -- -- -- -- -+-- -- -- -- -+ | Var i abl e_name | Val ue | +-- -- -- -- -- -- -- -- -- -+-- -- -- -- -+ | Sor t _mer ge_passes | 498 | | Sor t _r ange | 0 | | Sor t _r ows | 5000000 | | Sor t _sca n | 1 | +-- -- -- -- -- -- -- -- -- -+-- -- -- -- -+

Lo que llama la atención es que se han necesitado 498 procesos merge para completar el sort (es una cifra considerablemente alta). Cuando esta cifra es elevada es cuando el administrador debe considerar el incrementar la variable sor t _buf f er _si ze . 6. Se reinicia el servidor mysql d para inicializar los valores de estatus. 7. Se elimina y vuelve a crear la tabla para repetir el proceso de nuevo. dr op t abl e pr uebasor t 2; CREATE TABLE pr uebasor t 2 LI KE pr uebasor t ;

8. Se cambia el tamaño de la variable sor t _buf f er_si

ze incrementándolo 100 veces.

mysql >set sessi on sor t _buf f er _s i ze=20786400;

9. Lanzamos de nuevo el I NSERT con los cinco millones de registros ordenados. mysql > I NSERT I NTO pr uebasor t 2 SELECT * FROM pr uebasor t ORDER BY cant i dad; Quer y OK, 5000000 r ows af f ect ed ( 5 mi n 47. 49 sec) Recor ds: 5000000 Dupl i cat es: 0 War ni ngs: 0

El tiempo se ha divido prácticamente por tres, ha pasado de más de 15 minutos a más de 5 minutos. 10. Por último, un vistazo a las variables de estatus para ver como se ha realizado el sort: mysql > show st at us l i ke ' %sor t %' ; +-- -- -- -- -- -- -- -- -- -+-- -- -- -- -+ | Var i abl e_name | Val ue | +-- -- -- -- -- -- -- -- -- -+-- -- -- -- -+ | Sor t _mer ge_passes | 5 | | Sor t _r ange | 0 | | Sor t _r ows | 5000000 | | Sor t _sca n | 1 | +-- -- -- -- -- -- -- -- -- -+-- -- -- -- -+

Aquí es donde se ve el cambio más drástico, que es el paso de 498 procesos merge a 5 procesos merge.

Página 27



4. 5 Herramientas relacionadas con el rendimiento. 4.5.1 Optimización de expresiones y funciones mediante la función BENCHMARK(). Esta función es útil para verificar el tiempo que emplea MySQL en calcular una determinada función o expresión cuando puede ser utilizada de forma masiva. La sintaxis es: BENCHMARK( num_veces,

expresión_funcion) ;

El uso de esta función se ve muy bien con un ejemplo sencillo. Vamos a calcular el tiempo empleado en usar cien veces la función SYSDATE() . mysql > sel ect be nchmar k( 100, sysdat e( ) ) ; +--- --- --- --- --- --- --- --- --+ | benchmar k( 100, sysd at e( ) ) | +--- --- --- --- --- --- --- --- --+ | 0| +--- --- --- --- --- --- --- --- --+ 1 r ow i n set ( 0. 00 sec)

Se observa un tiempo despreciable. Ahora se llama cien mil veces a la misma función. m--ysql---> sel benchm ar k( + --- ect --- ----- ----- 100000, --- --+ sysda t e( ) ) ; | benchmar k( 100000, sysd at e( ) ) | +--- --- --- --- --- --- --- --- --- --+ | 0| +--- --- --- --- --- --- --- --- --- --+ 1 r ow i n set ( 0. 38 sec)

El tiempo empleado ha sido de 0.38 segundos, y por ultimo un millón de veces. mysql > sel ect benchmar k( 1000000, sysdat e( ) ) ; +--- --- --- --- --- --- --- --- --- --- + | benchmar k( 1000000, sysda t e( ) ) | +--- --- --- --- --- --- --- --- --- --- + | 0| +--- --- --- --- --- --- --- --- --- --- + 1 r ow i n set ( 2. 60 sec)

Genera un tiempo de 2,60 segundos. En este caso se aprecia con SYSDATE() que incrementando el número de veces que se ejecuta aumenta el tiempo de ejecución de forma lineal. La función BENCHMARK() puede ser muy útil para encontrar problemas o cuellos de botella en consultas que se ejecutan muchas veces y tienen expresiones complejas en su interior. Es aconsejable ejecutar BENCHMARK() algunas veces a distintas horas del día e interpretar el resultado en el sentido del nivel de carga que tiene la máquina del servidor.

Página 28



4.5.2 Test de rendimiento (Benchmarking). MySQL incorpora como parte de su distribución una serie de test de rendimiento del sistema de base de datos (MySQL Benchmark Suite). Los test de rendimiento son muy útiles cuando se quieren comparar varios servidores de bases de datos o cuando se está estudiando realizar un cambio en la configuración de nuestro servidor de base de datos. En el primer caso se lanzan los test de rendimiento en los distintos servidores y se comparan los resultados; en el segundo caso se lanzan los test antes del cambio en la configuración, se efectúa el cambio, y se vuelven a lanzar los test. Para lanzar los test de rendimiento incorporados en la MySQL Benchmark Suite es necesario contar con un intérprete de Perl que tenga instalado el paquete DBI para el acceso a las bases de datos y además el driver del tipo de base de datos que queremos probar. Es decir, que estos test no solo sirven para probar bases de datos MySQL sino cualquier otro tipo como PostgreSQL, Solid, etc., siempre que dispongamos del driver correspondiente. Los sistemas operativos Windows no vienen con intérprete de Perl, es necesario instalarlo previamente antes de lanzar los test de rendimiento. Para la instalación de un interprete de Perl en Windows consultar el Apéndice 2: Instalar interprete de Perl para Windows. ActivePerl.

Los test de prueba están localizados en una carpeta de nombre bench ( o sql - bench) que se encuentra bajo la carpeta de instalación de MySQL y son los siguientes: compare-results copy-db crash-me print-limit-table run-all-tests server-cfg test-alter-table test-ATIS test-big-tables test-connect test-create test-insert test-select test-wisconsin

Ejecutar todos ellos en bloque supone lanzar el script run-all-tests, siempre teniendo muy en cuenta que el tiempo de lanzar todos los test en bloque puede ser bastante elevado (entre ½ hora y 1 hora dependiendo del procesador). Veamos un ejemplo de ejecución del test que mide el rendimiento de consultas sobre tablas (test-select). Desde la consola de comandos nos situamos en la carpeta donde se encuentran los test: C: \ Document s and Set t i ngs\ Admi ni st r ador >cd c: \ wamp\ mysql \ bench

Lanzamos el test correspondiente. Página 29



C: \ wamp\ mysql \ bench>per l t est - sel ect - - user =r oot

La salida generada por t est-

sel ect

se visualiza en la ventana y es la siguiente:

Test i ng ser ver ' MySQL 5. 0. 18 nt ' at 2006- 09- 07 10: 01: 31 Test i ng t he speed of sel ect i ng on keys t hat consi st of many par t s The t est - t abl e has 10000 r ows and t he t est i s done wi t h 500 r anges. Cr eat i ng t abl e I nser t i ng 10000 r ows Ti me t o i nser t ( 10000) : 377 wal l cl ock secs ( 0. 93 usr cusr 0. 00 csys = 1. 24 CPU)

0. 31 sys + 0. 00

Test i f t he dat abase has a quer y cache Ti me f or sel ect _cache ( 10000) : 2 wal l cl ock secs ( 1. 15 usr 0. 00 cusr 0. 00 csys = 1. 47 CPU) Ti me f or sel ect _cache2 ( 10000) : 90 wal l cl ock secs ( 1. 43 usr 0. 00 cusr 0. 00 csys = 1. 71 CPU)

0. 32 sys + 0. 28 sys +

Test i ng bi g sel ect s on t he t abl e Ti me f or sel ect _bi g ( 70: 17207) : 1 wal l cl ock secs ( 0. 10 usr 0. 07 sys + 0. 00 cusr 0. 00 csys = 0. 17 CPU) Ti me f or sel ect _r ange ( 410: 1057904) : 11 wal l cl ock secs ( 7. 48 usr 2. 80 sys + 0. 00 cusr 0. 00 cs ys = 10. 29 CPU) Ti me f or mi n_max_on_key ( 70000) : 13 wal l cl ock secs ( 7. 98 usr 1. 44 sys + 0. 00cusr 0. 00 csys = 9. 42 CPU) Ti me f or count _on_key ( 50000) : 9 wal l cl ock secs ( 5. 83 usr 0. 98 sys + 0. 00 cusr 0. 00 csys = 6. 81 CPU) Ti me f or count _gr oup_ on_key_ par t s ( 1000: 100000) : 1 wal l cl ock secs ( 0. 71 usr 0. 34 sys + 0. 00 cusr 0. 00 csys = 1. 05 CPU) Test i ng count ( di st i nct ) on t he t abl e Ti me f or count _di st i nct _key_ pr ef i x ( 1000: 1000) : 1 wal l cl ock secs ( 0. 13 usr 0. 01 sys + 0. 00 cusr 0. 00 csys = 0. 14 CPU) Ti me f or count _di st i nct ( 1000: 1000) : 0 wal l cl ock secs ( 0. 13 usr 0. 00 sys + 0. 00 cusr 0. 00 cs ys = 0. 13 CPU) Ti me f or count _di st i nct _2 ( 1000: 1000) : 0 wal l cl ock secs ( 0. 12 usr 0. 05 sys + 0. 00 cusr 0. 00 cs ys = 0. 17 CPU) Ti me f or count _di st i nct _gr oup_ on_key ( 1000: 6000) : 0 wal l cl ock secs ( 0. 15 usr 0. 03 sys + 0. 00 cusr 0. 00 csys = 0. 18 CPU) Ti me f or count _di st i nct _gr oup_ on_key_ par t s ( 1000: 100000) : 1 wal l cl ock secs ( 0. 78 usr 0. 30 sys + 0. 00 cusr 0. 00 csys = 1. 08 CPU) Ti me f or count _di st i nct _gr oup ( 1000: 100000) : 2 wal l cl ock secs ( 0. 80 usr 0. 25sys + 0. 00 cusr 0. 00 csys = 1. 05 CPU) Ti me f or count _di st i nct _bi g ( 100: 1000000) : 9 wal l cl ock secs ( 6. 62 usr 2. 49 sys + 0. 00 cusr 0. 00 csys = 9. 11 CPU) Tot al t i me: 518 wal l cl ock secs ( 34. 35 usr 9. 68 sys + 0. 00 cusr 0. 00 cs ys = 44. 03 CPU)

Es interesante observar las acciones que ha ido realizando el test y el tiempo medido en pulsos de reloj del sistema que ha empleado para cada una de las acciones, además de establecer, al final, el tiempo total empleado. Un dato que destaca es el tiempo empleado en crear la tabla e insertar las 10000 filas (377 wallclock secs), esto se debe a que el fichero de configuración my.ini, en el caso del ejemplo, tiene como opción por defecto crear las tablas en arquitectura InnoDB, donde las inserciones son muchísimo más lentas que las tablas tipo MyISAM debido fundamentalmente a las verificaciones de integridad referencial que

Página 30



necesitan hacer las tablas InnoDB y que las tablas MyISAM ignoran. Es muy importante conocer el tipo de tablas por defecto que genera nuestro servidor de base de datos antes de lanzar los test de rendimiento. Un test un poco especial es el llamado crash-me que consiste en probar todos las características, capacidades y límites del sistema de base de datos. Cuando se lance este test es conveniente que el ordenador no esté realizando ningún otro tipo de actividad. La salida generada por crash-me es bastante extensa como para visualizarla desde consola, conviene lanzar el test con la opción conf i g- f i l e=n_f i cher o que permite almacenar la información generada. C: \ mysql \ bench>per l cr ash- me - - user =r oot - - conf i g- f i l e=t est 01- 09- 2006. t xt

No se va a visualizar en este documento toda la salida generada por el test crash-me porque es muy extensa, pero si, por ejemplo, podemos ver en un extracto, una vez editado el fichero del ejemplo t est 01- 09- 2006. t xt , como el test ha calculado que el tamaño máximo de un dato tipo var char es de 1048543 bytes de longitud. …. max_var char _ si ze=1048543 # max var char ( ) si ze ###We ar e t r yi ng ( exampl e wi t h N=5) : ###cr eat e t abl e cr ash_q ( q var char ( 5) ) ###i nser t i nt o cr ash_q va l ues ( ' aaaaa' ) ###sel ect * f r om cr ash_q ### 524287:OK 786431:OK 917503:OK 983039:OK 1015807:OK 1032191:OK 1040383: OK 1044479: OK 1046527: OK 1047551: OK 1048063: OK 1048319: OK 1048447: OK 1048511: OK 1048543: OK 1048559: FAI L 1048546: FAI L 1048544: FAI L ….

Para poder ver todas las opciones posibles a la hora de lanzar un test concreto usamos la opcion - - hel p. C: \ wamp\ mysql \ bench>per l t est - sel ect - - hel p

Página 31



Anexo 1. Uso de la herramienta DataGen para insertar múltiples filas en tablas de prueba. Esta herramienta es muy útil cuando se necesita crear un juego de tablas con múltiples filas para pruebas de optimización, tuning, rendimiento etc. en distintos servidores de bases de datos. DataGen se conecta vía ODBC a cualquier base de datos y a partir de unos sencillos asistentes permite realizar inserciones múltiples (de hasta cientos de miles de registros) a partir de expresiones aritméticas, valores aleatorios, valores extraídos de ficheros ASCII. Vamos a ver la utilidad de DataGen mediante un sencillo ejemplo. Paso 1. Instalar el controlador ODBC de MySQL. En primer lugar y si no lo tenemos ya instalado tendremos que instalar el controlador ODBC de MySQL para Windows. Este controlador permitirá al software cliente que se ejecuta en un equipo Windows conectarse a cualquier servidor de datos MySQL, el software cliente en este caso va a ser DataGen pero también sirve para cualquier otro software cliente como Access, Excel, etc. El software del controlador ODBC (mysql-connector-odbc-3.xx.xx-win32.msi) lo encontramos en la web de MySQL en la dirección: http://dev.mysql.com/downloads/connector/odbc (xx.xx depende de la última versión disponible en la Web de MySQL). Una vez bajado el conector se instala sin ninguna dificultad siguiendo las instrucciones por defecto que indica el asistente de instalación.

Página 32



Al finalizar los pasos de este asistente ya tenemos un conector ODBC para MySQL instalado. Lo podemos comprobar si abrimos el listado de conectores ODBC que tiene nuestro sistema operativo siguiendo la secuencia de menús: InicioPanel de ControlHerramientas AdministrativasOrígenes de datos (ODBC)

Paso 2. Crear una conexión ODBC a una base de datos concreta de nuestro servidor MySQL. Para crear una conexión ODBC que permita conectar cualquier programa cliente con la base de datos curso de mi servidor de base de datos MySQL se debe realizar lo siguiente: •

•

•

Se sigue con la aplicación Origenes de datos (ODBC) abierta y se selecciona la pestaña DNS de usuario y se pulsa el botón Agregar… Seleccionamos a continuación el driver MySQL ODBC 3.5X Driver que acabamos de instalar. Aparece a continuación la siguiente ventana de dialogo:

Página 33


•


Los campos necesarios a rellenar son los siguientes: 







Data Source Name: es el nombre que queramos dar a la conexión.

Este nombre es el que tendremos que hacer referencia desde el software de cliente (DataGen) para identificar la conexión. Server: Nombre o dirección IP del servidor de base de datos MySQL, en el ejemplo es el propio equipo. User y password: El login de acceso de MySQL. En el ejemplo es el usuario r oot sin password. Database: Si los anteriores datos han sido correctamente introducidos aquí ya debe aparecer el listado de bases de datos accesibles por el usuario de MySQL. En el ejemplo seleccionamos curso que ya estaba previamente creada en MySQL (mediante la sentencia cr eat e dat abase cur so ).

Paso 3. Crear desde MySQL la estructura de la tabla a la que se va posteriormente a conectar DataGen para insertarle los múltiples registros. Se lanza desde MySQL la siguiente sentencia que nos creará la tabla necesaria para el ejemplo. mysql > cr eat e t abl e pr uebadat agen ( cl ave i nt aut o_i ncr ement pr i mary ke y, descri pci on var char ( 50) , f echa dat e, val or i nt ) engi ne=myi sam;

Página 34



Nota: La tabla puede ser tanto MyI SAMcomo I nnoDB. DataGen funciona muchísimo más rápido si la tabla es MyI SAM, al eliminar todas las verificaciones de integridad. Paso 4. Obtener e instalar DataGen. El programa DataGen lo podemos bajar de la dirección: http://www.daansystems.com/datagen/ La instalación es inmediata ejecutando el fichero datagen_setup.exe. Paso 5. Ejecutar DataGen y preparar la inserción múltiple de registros. Una vez creada la estructura de la tabla ejecutamos el programa DataGen. Lo primero que tenemos que hacer es indicarle al programa que debemos utilizar la conexión ODBC creada en el paso 2. Pulsamos el botón para que aparezca la ventana que permite seleccionar el vínculo de datos.

La opción que debemos seleccionar es la que está resaltada en la imagen superior “Microsoft OLE DB Provider for ODBC Drivers”.

Al pulsar “Siguiente” la siguiente ventana nos lista los nombres de los DSN de usuario de los srcenes de datos ODBC . Debemos seleccionar el creado en el paso 2 con el nombre mi_conexion_mysql. Antes de pulsar Aceptar probamos la conexión con el botón Probar Conexión.

Página 35



En la ventana principal de la aplicación Datagen se puede ver, si la conexión con el servidor MySQL ha sido satisfactoria, el listado de tablas de la base de datos curso . Seleccionamos para nuestro ejemplo la tabla pr uebadat agen y visualizamos los campos que la componen. Debemos decirle a la aplicación cómo rellenamos cada una de las columnas de la tabla. Existen diversas posibilidades y vamos a ver como se han aplicado en nuestro ejemplo. Tal como muestra la siguiente ventana el campo clave [Integer], al estar definido como de tipo aut o_i ncr ement , va a ser el propio servidor MySQL el encargado de introducir sus valores, por lo tanto DataGen no va a ocuparse de esta columna y dejamos desmarcada la opción Use Datasource asociada a este campo.

Página 36



El siguiente campo descripción [varchar](50) se va a rellenar a partir de un fichero ASCI I que contiene nombres calles inglesas. (El programa algunos de estos listados que pueden serdemuy útiles para rellenar camposDataGen de texto incorpora de forma aleatoria).

Página 37



El campo fecha [dbdate] tal como se ve a continuación se va a rellenar con valores tipo fecha generados aleatoriamente.

valor [integer] se va a rellenar seleccionando valores de una columna Por último campo también de eltipo entero pero de la tabla libros que se encuentra en la misma base de datos curso, en concreto es la columna numpaginas.

Página 38



Una vez definidos como se van a rellenar las columnas de la tabla fijamos el número de filas que deseamos (en nuestro ejemplo 5000) y pulsamos el botón Go!. Finalmente chequeamos la tabla como comprobación de que todo ha ido bien. C: \ Document s and Set t i ngs\ Admi ni st r ador >mysql - ur oot Wel come t o t he MySQL moni t or . Commands end wi t h ; or \ g. Your MySQL connect i on i d i s 10 t o ser ver ver si on: 5. 0. 18- nt Type ' hel p; ' or ' \ h' f or hel p. Type ' \ c' t o cl ear t he buf f er . m ysql > usechanged cur so Dat abase mysql > sel ect count ( *) f r om pr uebadat agen; +- - - - - - - - - - + | count ( *) | +- - - - - - - - - - + | 5000 | +- - - - - - - - - - + 1 r ow i n set ( 0. 00 sec) mysql > sel ect * f r om pr uebadat agen l i mi t 10; +-- -- -- -+-- -- -- -- -- -- -- -- -- +-- -- -- -- -- -- +-- -- -- -+ | cl ave | desc r i pci on | f echa | val or | +-- -- -- -+-- -- -- -- -- -- -- -- -- +-- -- -- -- -- -- +-- -- -- -+ | 10001 | Mcconnel l Rd | 1979- 01- 21 | 288 | | 10002 | Cont ent ment St | 2020- 07- 20 | 540 | | 10003 | I val ea Rd | 1982- 04- 12 | 582 | | 10004 | Dunbar Ci r | 1990- 11- 30 | 653 | | 10005 | Sewel l Rd | 2036- 09- 04 | 865 | 10006 || Tr Coret a de Ci r Sout h || 20332000- 0412- 25 12 || || 10007 ai l t Ri | 10008 | Ti mber wood Rd | 2012- 01- 11 | | 10009 | Ki ng Ranch Rd | 1997- 04- 20 | | 10010 | Mae Ln | 1976- 11- 21 | +-- -- -- -+-- -- -- -- -- -- -- -- -- +-- -- -- -- -- -- +-10 r ows i n set ( 0. 01 sec)

530 || 456 843 | 954 | 434 | -- -- -+

mysql >

Página 39



Anexo2. Instalar interprete de Perl para Windows (ActivePerl). Para poder realizar los test de rendimiento (benchmarking) que vienen incluidos en la distribución de MySQL es necesario contar con un intérprete de Perl en nuestro sistema que tenga además instalado el paquete DBD para MySQL. Los sistemas Windows no vienen con intérprete de Perl y es necesario realizar la instalación previa antes de poder lanzar los test de rendimiento. Los pasos a dar son los siguientes: Acceder a la web http://www.activestate.com/Products/ActivePerl/ para bajarse el producto y proceder a su instalación. (Para Windows lo más cómodo es bajarse el archivo con extensión .msi). Se siguen en la instalación todas las opciones que vienen por defecto. Desde el menú Inicio ejecutamos el programa Perl Package Manager (PPM). PPM es un gestor de paquetes similar a los gestores de paquetes .rpm o .deb del entorno linux. Tenemos que comprobar que aparecen dentro de la lista de paquetes, como paquetes instalados, los dos siguientes: - DBI - DBD-mysql Tal como se ve en la siguiente imagen:

En el caso de que no estuviera alguno de los dos instalados es necesario visualizar todos los paquetes disponibles [Ctrl 1], localizar en el listado el paquete concreto, por ejemplo DBD-mysql, marcarlo para su instalación pulsando la tecla [+] y por último proceder a

Página 40



la instalación [Ctrl Enter]. Lo que hace PPM es conectarse al repositorio de paquetes vía internet e instalarlo y configurarlo en el equipo. Nota: A partir de la versión de ActivePerl 5.8.8.819 el programa PPM viene con interfaz gráfico. Las versiones anteriores de ActivePerl traen el programa PPM en modo consola de comandos.

Nuestro equipo ya está en condiciones de probar los test de rendimiento incluidos en la MySQL Benchmark Suite.

Página 41



Anexo3. MySQL Administrator. Introducción a MySQL Administrator. MySQL Administrador es una interfaz gráfica para llevar a cabo las principales operaciones administrativas, como configurar, monitorizar y poner en marcha y detener los servidores de MySQL, crear usuarios y conexiones, llevar a cabo copias de seguridad, y varias otras tareas administrativas. La mayoría de las tareas pueden ser llevadas a cabo usando un entorno de línea de consola como mysqladmin, o mysql, pero MySQL Administrador tiene las siguientes ventajas: Su interfaz gráfica lo hace más intuitivo. Provee una mejor visión general de los ajustes que son cruciales para el rendimiento, la fiabilidad, y seguridad de los servidores MySQL. Muestra indicadores de rendimiento gráficamente, haciendo más fácil determinar que ajustes y modificaciones se deben realizar en el servidor para afinar el rendimiento. • •

•

El administrador de MySQL está diseñado para trabajar a partir de la versión 4.0 o superior. Entre las tareas que MySQL Administrator puede realizar se incluyen: • • • • • • • •

La configuración de las opciones de inicio de los servidores. Inicio y detención de servidores. Monitorización de conexiones al servidor. Administración de usuarios. Monitorización del estado del servidor, incluyendo estadísticas de uso. Visualización de los logs de servidor. Gestión de copias de seguridad y recuperaciones. Visualización de catálogos de datos.

Instalación de MySQL Administrator. La descarga del programa se hace desde la página web de MySQL, en http://dev.mysql.com/downloads/administrator . Se debe seleccionar el fichero a bajar dependiendo del SO sobre el que se vaya a realizar la instalación.

Instalación en Windows Para instalar las herramientas GUI de MYSQL en Windows lo más cómodo es bajar el fichero mysql-gui-tools-version-win32.msi, donde versión indica la versión del producto (por ejemplo, 5.0r4). Una vez en el disco duro ejecutar el fichero y seguir los pasos que marca el instalador. Por defecto ya se crean las entradas en el menú Inicio de Windows. Página 42



Instalación en Linux. El archivo de tar.qz genérico permite que se instale las herramientass de GUI de MySQL en la mayoría de las distribuciones de Linux. El archivo a bajar se llama mysql-gui-toolsversio-.linux-i386.tar.gz, donde versión indica la versión del producto (por ejemplo, 5.0r4). Para instalar las herramientas de GUI de MySQL se lanza desde la shell el siguiente comando: shel l > t ar - - di r ectory= / opt - xzvf mysql - gui - t ool s- ver si on. t ar . gz

Se instalan los archivos binarios de aplicación en la carpeta / opt / mysql - gui - t ool s- ver r si ón

Establecer una conexión con el servidor. Al arrancar el programa aparece el siguiente cuadro de conexión:

En el que hay especificar los datos del servidor, nombre de usuario, contraseña y puerto de la base de datos a la cual se desea conectar. Una vez introducidos de forma correcta, aparece la pantalla principal del programa donde se visualiza por defecto la información más relevante sobre el servidor de MySQL y el cliente que ha establecido la conexión:

Página 43



Algunas utilidades accesibles desde la ventana principal del programa son: -

Service control: inicio y detención de servidores (solo accesible si se ha conectado

-

Startup variables: configuración del servidor y las variables de inicio (solo accesible

-

User Administration: para la gestión de usuarios y permisos. Server conections: visualiza y gestiona las conexiones abiertas con el servidor de

-

Health: información sobre la carga del servidor. Server Logs: el historial de logs del servidor. Replication Status: con información de los sistemas replicados. Backup: para hacer una copia de seguridad de las bases de datos. Restore: para restaurar las copias de seguridad. Catalogs: para mostrar las bases de datos, visualizar, crear y editar las tablas.

con un servidor MySQL en la máquina local).

si se ha conectado con un servidor MySQL en la máquina local). bases de datos.

MySQL Administrador ofrece la posibilidad de arrancar saltándose el cuadro de conexión para abrir directamente el modo “Configuración de servicio”. Este tipo de inicio es útil en los dos siguientes casos: •

Cuando se quiere iniciar el servidor MySQL desde dentro de MySQL Administrator. Evidentemente no podemos conectar con el servidor si no está levantado por lo tanto tenemos que saltarnos el inicio a través del cuadro de conexión normal e iniciar en el modo “Configuración de servicio”. Es evidente

Página 44



que en este caso solo podemos arrancar servidores de datos en el mismo host donde esta ejecutándose MySQL Administrador. •

Cuando se tienen múltiples servidores ejecutándose en el mimo host. Si arrancamos a través del cuadro de conexión normal solo podemos configurar el servicio del servidor al que nos hemos conectado. Si arrancamos en modo “Configuración de servicio” podemos configurar los servicios de todos los servidores que MySQL Administrador detecte en el host.

La forma de arrancar en modo “Configuración de servicio” es la siguiente. Se apreta la tecla CTRL en la ventana de dialogo de conexión. El boton Cancel se convierte en Skip mientras la tecla está apretada. Pulsando el botón Skip nos saltamos el cuadro de conexión y aparece la ventana que permite arrancar, detener y configurar los servicios asociados a los servidores de bases de datos MySQL.

Muchas de las opciones de configuración de la base de datos son muy sencillas de entender y de utilizar, por lo menos todas las opciones más básicas. En conjunto con MySQL Query Browse, nos permite gestionar cualquier aspecto de una base de datos MySQL. Este documento no trata de dar una visión exhaustiva de MySQL Administrador solo pretende destacar con un poco más de detalle un par tareas que posiblemente son de las que MySQL Administrador puede ayudar más a los usuarios que realizan labores de administración. Dichas tareas se centran en: Administración de usuarios. Como generar gráficos a medida para monitorizar la evolución de las variables de sistema (system variables) y el estado del sistema ( status variables). Página 45



MySQL Administrator para administrar usuarios. Los usuarios de MySQL se gestionan a través de la base de datos llamada mysql , concretamente con la tabla user . Sin embargo, el manejo de esa tabla es relativamente complicado para un usuario que no sea del todo experto, por lo que será mucho más cómodo usar la aplicación MySQL Administrator para realizar la gestión de usuarios.

Entre las distintas opciones de MySQL Administrator tenemos una sección para la gestión de usuarios, sus permisos, host de acceso, etc. La podemos ver y seleccionar en la barra de opciones de la izquierda.

Inicialmente solo hay un usuario creado, en muchas de las configuraciones típicas, que es el usuario root. La lista de usuarios creados aparece en la parte de abajo de la barra de la izquierda. Podemos seleccionar cualquier usuario para editar sus propiedades. Las propiedades de usuarios se dividen en tres pestañas: - User information: con los datos de login (nombre de usuario y contraseña de acceso) y otros datos personales del usuario, que es opcional completar. Página 46



- Schema Privileges: con los permisos asociados a ese usuario en las distintas bases de datos de MySQL. - Resources: con los recursos disponibles para ese usuario. En la parte de debajo de la ventana encontraremos tres botones, para crear un nuevo usuario, para aplicar los cambios realizados en los formularios de edición de un usuario y para descartarlos. Si queremos crear un usuario podemos pulsar el botón de New User y aparecerá la ventana de propiedades del usuario, en la pestaña de User Information, para que introduzcamos el nombre para este usuario y la contraseña que vamos a asignar. Podemos entrar en otras pestañas para terminar de introducir los datos del usuario. La pestaña de Schema Privileges es especialmente interesante, ya que nos permite seleccionar los permisos de este usuario para cada base de datos de nuestro sistema. La ventana de Schema Privileges nos permite seleccionar una base de datos y entonces accedemos a una lista con todos los privilegios posibles para permitir o denegar. Inicialmente, para un usuario nuevo, todos los permisos están denegados, así que tendremos que seleccionar los que deseamos otorgar. Para ello simplemente seleccionamos entre los avaliable privileges (permisos disponibles) y los pasamos a assigned privileges (permisos asignados). En la siguiente imagen se observa como el usuario pedro tiene sobre la base de datos concesi onar i o asignados determinados privilegios (la mayor parte) y aún tiene unos cuantos disponibles que el administrador ha decidido no asignarle por el momento. El usuario pedro podrá realizar tareas de administración si previamente el usuario root le ha concedido todos los derechos sobre la base de datos mysql del servidor.

Página 47



Vamos a ver como con MySQL Administrador podemos controlar los host desde los que se pueden conectar los distintos usuarios. Inicialmente cualquier usuario creado se puede conectar desde cualquier host o computador posible. Para

Usuario Pedro desde cualquier host

Si esta situación no nos conviene, podemos definir nuevos host pulsando con el botón derecho del ratón sobre el usuario al que queremos añadir ordenadores de acceso. Entonces nos sale un formulario para indicar el nombre del host que queremos añadir a ese usuario, o su dirección IP. En cualquier momento, si hacemos doble clic sobre un usuario y nos sale debajo una lista de hosts, es que ese usuario se puede conectar desde cualquier de esos hosts y además, podremos asignar permisos distintos al usuario cuando se conecte desde cada ordenador posible. De modo que un usuario si se conecta, por ejemplo desde localhost, puede tener más permisos que si se conecta desde otros ordenadores de la red local o de Internet.

En la imagen anterior se entiende que el usuario pedro se puede conectar desde localhost (el ordenador local) y desde otro equipo llamado con la dirección IP 192.168.1.101. Pero es muy importante tener cuidado con esta lista de hosts, porque a lo mejor todavía estamos permitiendo que el usuario prueba se pueda conectar desde cualquier host y es posible que no deseemos que eso se permita, sino que solo se deje acceder si se accede desde esos host especificados. Para darnos cuenta de este detalle podemos ver en la ventana de propiedades del usuario el nombre del usuario que sale, para saber los hosts que están permitidos.

Página 48



Lo vemos con una imagen:

Usuario Pedro desde 192.168.1.101

En esta imagen nos hemos situado en el host 192.168.1.101 del usuario pedro. Vemos que en la parte de arriba, en la parte de propiedades del usuario aparece [email protected]. Eso quiere decir que está definido el usuario pedro siempre que se conecte desde 192.168.1.101 y que estamos en la ventana de propiedades de ese usuario.

Página 49



Ahora veamos la siguiente imagen:

Usuario Pedro desde cualquier host

Nos hemos situado encima del usuario nos fijamos en en la parte de arriba de las propiedades del usuario veremos como pedro. apareceSi"pedro", donde la imagen anterior aparecía “[email protected]”. Por lo tanto en esta situación el usuario pedro, como no está acompañado de ningún nombre de host, significa que puede accederse desde cualquier equipo. Si queremos evitar que el usuario pedro pueda acceder desde cualquier equipo, simplemente pulsamos con el botón derecho en el nombre de usuario y seleccionamos la opción que pone "Remove Host From Which The User Can Connect". Entonces, si hemos pulsado sobre el nombre del usuario nos quitará el acceso del usuario desde cualquier host. Si hacemos la misma acción sobre cualquier nombre de host, como localhost, nos quitará la posibilidad de que el usuario se pueda conectar desde ese ordenador.

Página 50



Si nos fijamos ahora de nuevo en la pantalla se aprecia el cambio realizado:

Usuario Pedro desde 192.168.1.101

Apretando con el botón derecho en cualquier usuario o host nos dará una serie de opciones para el mantenimiento de ese usuario o host que no hemos nombrado, como añadir un usuario, borrarlo o duplicarlo.

Página 51



MySQL Administrator para crear gráficos customizados de monitorización del sistema. MySQL Adminsitrator presenta una opción de monitorización de variables de estado y variables de sistema mediante gráficos temporales que son muy útiles para medir la evolución de estas variables en tiempo real y en modo gráfico. En la configuración por defecto aparecen dos pestañas con una serie de gráficos predefinidos. La pestaña Connectión Health muestra información sobre las conexiones de los usuarios y el tráfico y la pestaña Memory Health que indica el uso de la Query Cache y de la Key Cache como muestra la siguiente imagen:

En concreto el gráfico Query Cache Hitrate es un buen indicador del uso de la Query Cache de MySQL. Para ver como se han creado estos gráficos predefinidos de MySQL Administrator basta con poner el ratón sobre el grafico y con el botón derecho seleccionamos la opción Edit Graph del menú contextual. Aparece una ventana que permite editar los parámetros de configuración del gráfico tales como la formula empleada para dibujar la grafica, el tipo de unidad de medida, el valor mínimo y máximo a alcanzar, etc. La formula se basa en una expresión sobre los valores recogidos en la variables de estado de MySQL o bien en el valor relativo de estos respecto a la última lectura dependiendo de lo especificado en la expresión [ var i abl e] o ^[ var i abl e] :

Página 52



El grafico, por lo tanto, responde a la formula recogida en la caja de texto Value Formula.

Página 53



Vamos a proceder a crear un grafico customizado desde el principio. Suponemos que al administrador de la base de datos le interesa monitorizar y registrar el número de sentencias de consulta (tipo SELECT) por un lado y tipo DML (I NSERT, UPDATE y DELETE) por otro lado, que realizan los usuarios conectados a la base de datos. El primer paso consiste en crear una nueva página o pestaña en la sección Health. Para ello basta con pulsar el botón derecho del ratón en el menú contextual y seleccionar la opción Add a Page.

Ponemos como título o Page Caption “Sentencias SQL” y a continuación como Page Description:

Como segundo paso es necesario crear algún grupo. Todos los gráficos están organizados en torno a grupos. Seleccionamos la opción Add a Group y creamos dos grupos tal como aparece en esta imagen:

Página 54



Como último paso y ubicando el ratón dentro de cada grupo seleccionamos Add a Graph para añadir los dos gráficos. La ventana del primer gráfico la rellenamos con los valores que se ven en esta imagen:

La formula en este ejemplo es bien sencilla, únicamente visualizar el valor de la variable de estado com_sel ect que cuenta el número de Selects distintas que se han realizado en la base de datos. Si la Select se repite se incrementa el valor de Qcache_hi t s pero no el de com_s el ect .

Página 55



Para el segundo grafico que contabiliza las sentencias de actualización la formula será la siguiente:

Finalmente una vez creados ambos gráficos deberíamos visualizar algo parecido a esta ventana:

Página 56

Manual SQL Avanzado

Recommend Documents