Hosting guebs

Apéndice H. Restricciones en características de MySQL

Tabla de contenidos

H.1. Restricciones en procedimientos almacenados y disparadores
H.2. Restricciones en cursores del lado del servidor
H.3. Restricciones en subconsultas
H.4. Restricciones en vistas

Se expone a continuación las restricciones en el uso de algunas características de MySQL, como las subconsultas o las vistas.

H.1. Restricciones en procedimientos almacenados y disparadores

Algunas de las restricciones expuestas aquí se refieren a todas las rutinas almacenadas; esto es, procedimientos almacenados y funciones almacenadas. Algunas de las restricciones sólo se refieren a funciones almacenadas, y no a los procedimientos almacenados.

Todas las restricciones para las funciones almacenadas se refieren también a los disparadores(triggers).

Las rutinas almacenadas no pueden contener sentencias SQL arbitrarias. Las siguientes sentencias no están permitidas dentro de una rutina almacenada:

  • CHECK TABLES

  • LOCK TABLES, UNLOCK TABLES

  • FLUSH

  • LOAD DATA, LOAD TABLE

  • Sentencias SQL preparadas(PREPARE, EXECUTE, DEALLOCATE). Implicación: No puedo usar SQL dinámico dentro de una rutina almacenada(donde construya dinámicamente sentencias como cadenas de caracteres y después ejecutarlas).

Además, en funciones almacenadas (pero no para procedimientos almacenados), no están permitidas las siguientes sentencias:

  • Sentencias que hacen commits o rollbacks explícitos o implícitos.

  • Sentencias que devuelvan un resultado. Esto incluye sentencias SELECT que no tienen una cláusula INTO y la sentencia SHOW. Una función puede procesar un resultado tanto con SELECT … INTO como con el uso de un cursor y de la sentencia FETCH.

El uso de una rutina almacenada puede causar problemas de replicación. Este asunto se expone con profundidad en Sección 19.3, “Registro binario de procedimientos almacenados y disparadores”.

INFORMATION_SCHEMA todavía no tiene una tabla PARAMETERS, así que aplicaciones que, en tiempo de ejecución, necesiten adquirir información de los parámetros de la rutina, deben usar técnicas como tratar la salida de la sentencia SHOW CREATE.

No existen facilidades para el depurado de rutinas almacenadas.

Las rutinas almacenadas usan cursores materializados, no cursores nativos. (El resultado se genera y guarda en el lado del servidor, y después se devuelve línea por línea a medida que el cliente lo extrae.)

La sentencia CALL no se puede preparar. Esto es cierto tanto para las sentencias preparadas del lado del servidor como para las sentencias SQL preparadas.

H.2. Restricciones en cursores del lado del servidor

Los cursores del lado del servidor están implementados a partir de MySQL 5.0.2 a través de la función de la API de C mysql_stmt_attr_set(). Un cursor del lado del servidor permite a un resultado ser generado del lado del servidor, pero no transmitido al cliente excepto para aquellos registros que el cliente solicite. Por ejemplo, si un cliente ejecuta una consulta pero de ésta sólo le interesa el primer registro, los registros sobrantes no son transferidos.

Los cursores son de sólo lectura; no puede usar un cursor para actualizar registros.

UPDATE WHERE CURRENT OF y DELETE WHERE CURRENT OF no están implementadas, porque los cursores actualizables no están soportados.

Los cursores son no-mantenibles (no se mantienen abiertos después de una ejecución (commit)). Cursors are non-holdable (not held open after a commit).

Los cursores son insensibles.

Los cursores no pueden navegarse.

Los cursores no son nombrados. El proceso de la sentencia actua como el ID del cursor.

Puede tener sólo un cursor por sentencia preparada. Si necesita varios cursores, debe preparar varias sentencias.

No se puede utilizar un cursor para una sentencia que genera un resultado si la sentencia no es soportada en modo preparado. Esto incluye sentencias como CHECK TABLES, HANDLER READ y SHOW BINLOG EVENTS.

H.3. Restricciones en subconsultas

El siguiente bug es conocido y será reparado: Si se compara un valor NULL con una subconsulta usando ALL, ANY, o SOME, y la sentencia devuelve un resultado vacío, la comparación podría dar un resultado no estándar de NULL en vez de un TRUE o FALSE.

La sentencia de afuera de la subconsulta puede ser cualquiera de las siguientes: SELECT, INSERT, UPDATE, DELETE, SET, o DO.

Las operaciones de comparación de registros son soportadas sólo parcialmente:

  • En expr IN (subconsulta), expr puede ser una n-tupla (especificada vía sintaxis del constructor de registros) y la subconsulta puede devolver registros de n-tuplas.

  • En expr op {ALL|ANY|SOME} (subconsulta), expr debe ser un valor escalar y la subconsulta debe ser de una sola columna; no puede devolver registros con múltiples columnas.

En otras palabras, para una subconsulta que devuelve registros de n-tuplas, esto está soportado:

(val_1, ..., val_n) IN (subconsulta)

Pero esto no está soportado:

(val_1, ..., val_n) op {ALL|ANY|SOME} (subconsulta)

La razón por la que se soporta la comparación entre registros con IN pero no con los otros es que IN fue implementado reescribiéndolo como una secuencia de comparaciones = y operaciones AND. Esto mismo no puede realizarse con ALL, ANY, o SOME.

Los constructores de registros no están bien optimizados. Las siguientes dos expresiones son equivalentes, pero sólo la segunda puede ser optimizada:

(col1, col2, ...) = (val1, val2, ...)
col1 = val1 AND col2 = val2 AND ...

La optimización de subconsultas para los IN no es tan efectiva como para los =.

Un caso típico del pobre rendimiento del IN es cuando una subconsulta devuelve un número pequeño de registros, pero la consulta de afuera regresa un número grande de registros para ser comparados con el resultado de la subconsulta.

Las subconsultas en la cláusula FROM no pueden ser subconsultas correlacionadas. Éstas son materializadas (ejecutadas para producir un resultado) antes de que se evalúe la consulta exterior, así que no pueden ser evaluadas por registro de la consulta exterior.

En general, no puede modificar una tabla y seleccionar de la misma en una subconsulta. Por ejemplo, esta limitación se aplica a sentencias del siguiente tipo:

DELETE FROM t WHERE ... (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);
{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);

Excepción: La prohibición precedente no se aplica si se usa una subconsulta para la tabla modificada en la cláusula FROM. Ejemplo:

UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...) AS _t ...);

Aquí la prohibición no se aplica porque una subconsulta en la cláusula FROM se materializa como una tabla temporal, así que los registros relevantes en t ya han sido seleccionados cuando la actualización de t ha tenido lugar.

El optimizador es más maduro para joins que para subconsultas, así que en la mayor parte de los casos las sentencias que usan subconsultas pueden ser ejecutadas más eficientemente si se reescriben como joins.

Una excepción a esta norma es el caso en que una subconsulta IN puede ser reescrita como una join SELECT DISTINCT. Ejemplo:

SELECT col FROM t1 WHERE id_col IN (SELECT id_col2 FROM t2 WHERE condición);

La sentencia puede ser reescrita como sigue:

SELECT DISTINCT col FROM t1, t2 WHERE t1.id_col = t2.id_col AND condición;

Pero en este caso el join requiere una operación DISTINCT extra y no es más eficiente que la subconsulta.

Futura optimización posible: MySQL no reescribe el orden del join para la evaluación de subconsultas. En algunos casos, una subconsulta puede ser ejecutada más eficientemente si MySQL la reescribe como un join. Esto daría al optimizador mayor posibilidad de elegir entre varios planes de ejecución. Por ejemplo, podría decidir si leer primero una tabla o la otra.

Ejemplo:

SELECT a FROM outer_table AS ot
WHERE a IN (SELECT a FROM inner_table AS it WHERE ot.b = it.b);

Para esra consulta, MySQL siempre busca outer_table primero y después ejecuta la subconsulta en inner_table para cada registro. Si outer_table tiene muchos registros e inner_table tiene pocos, la consulta probablemente no será tan rápida como pudiera ser.

La consulta anterior podría reescribirse así:

SELECT a FROM outer_table AS ot, inner_table AS it
WHERE ot.a = it.a AND ot.b = it.b;

En este caso podemos explorar la tabla pequeña (inner_table) y buscar registros en outer_table, lo cual sería más rápido si existiera un índice en (ot.a,ot.b).

Futura optimización posible: Una subconsulta correlacionada se evalúa por cada registro de la consulta externa. Una mejor solución sería que no se evaluara la subconsulta nuevamente si el valor del registro exterior fuese igual al de la línea anterior. Se usaría en este caso el resultado previo.

Futura optimización posible: Una subconsulta en la cláusula FROM se evalúa materializando el resultado dentro de una tabla temporal, y esta tabla no usa índices. Esto no permite el uso de índices en comparación con otras tablas en la consulta, aunque pudiera ser útil.

Futura optimización posible: Si una subconsulta en la cláusula FROM se asemeja a una vista a la que se puede aplicar el algoritmo MERGE, reescribir la consulta, aplicar el algoritmo MERGE, para que se puedan utilizar los índices. La siguiente sentencia contiene una subconsulta de este tipo:

SELECT * FROM (SELECT * FROM t1 WHERE t1.t1_col) AS _t1, t2 WHERE t2.t2_col;

La sentencia puede ser reescrita con un join como éste:

SELECT * FROM t1, t2 WHERE t1.t1_col AND t2.t2_col;

Este tipo de reescritura comportaría dos beneficios

  1. Evitar el uso de una tabla temporal, en la que no se puede utilizar índices. En la consulta reescrita, el optimizador puede usar índices en t1.

  2. Da al optimizador más libertad al elegir entre diferentes planes de ejecución. Por ejemplo, reescribiendo la consulta como un join permite al optimizador usar t1 o t2 primero.

Futura optimización posible: Para los IN, = ANY, <> ANY, = ALL, y <> ALL con subconsultas no correlacionadas, usar una tabla hash en memoria para un resultado o una tabla temporal con un índice para los resultados más grandes. Ejemplo:

SELECT a FROM big_table AS bt
WHERE non_key_field IN (SELECT non_key_field FROM table WHERE condicion)

En este caso, podríamos crear una tabla temporal:

CREATE TABLE t (key (non_key_field))
(SELECT non_key_field FROM table WHERE condicion)

Entonces, para cada renglón en big_table, hacer un ciclo en t basado en bt.non_key_field.

H.4. Restricciones en vistas

El procesamiento de vistas no está optimizado:

  • No es posible crear un ínidice en una vista.

  • Los índices pueden utilizarse para procesar vistas usando un algoritmo de combinación (MERGE). Sin embargo, una vista que se procesa con el algoritmo de tablas temporales (temptable) no es capaz de tomar ventaja de los índices que hacen referencia a las tablas que contiene (aunque los índices pueden ser usados durante la generación de las tablas temporales).

Las subconsultas no pueden utilizarse en la cláusula FROM de una vista. Esta limitación será removida en el futuro.

Existe un principio general por el que no se puede modificar una tabla y seleccionar de la misma en una subconsulta. Ver Sección H.3, “Restricciones en subconsultas”.

El mismo principio se aplica también si se hace una selección de una vista que hace una selección de una tabla, si la vista selecciona de la tabla dentro de una subconsulta, y la vista es evaluada usando el algoritmo de combinación (merge). Ejemplo:

CREATE VIEW v1 AS
SELECT * FROM t2 WHERE EXISTS (SELECT 1 FROM t1 WHERE t1.a = t2.a);

UPDATE t1, v2 SET t1.a = 1 WHERE t1.b = v2.b;

Si la vista se evalúa usando una tabla temporal, se puede seleccionar de la tabla en la subconsulta de la vista y modificarla en la consulta exterior. En este caso la vista se almacenará en una tabla temporal y por ello no se está realmente seleccionando de una tabla en una subconsulta y modificándola “al mismo tiempo”. (Esta es otra razón por la que tal vez sea deseable forzar a MySQL para que use el algoritmo de tablas (temptable) temporales especificando las palabras ALGORITHM = TEMPTABLE en la definición de la vista.)

Se puede usar DROP TABLE o ALTER TABLE para eliminar o modificar una tabla utilizada en la definición de una vista (lo cual invalida la vista) y no se obtendrá ninguna alerta de las operaciones de elimiar o modificar. Sin embargo, se obtiene un error más tarde, cuando se utiliza la vista.

Algunas sentencias “congelan” una definición de vista:

  • Si una sentencia preparada por PREPARE se refiere a una vista, los contenidos de la vista que se ven cada vez que se ejecuta la sentencia, serán los contenidos de la vista en el momento en el que la sentencia fue preparada. Esto es cierto incluso si la definición de la vista se cambia después de preparar la sentencia y antes de que ésta se ejecute. Ejemplo:

    CREATE VIEW v AS SELECT 1;
    PREPARE s FROM 'SELECT * FROM v';
    ALTER VIEW v AS SELECT 2;
    EXECUTE s;
    

    El resultado devuelto por la sentencia EXECUTE es 1, y no 2.

  • Si una sentencia en una rutina almacenada se refiere a una vista, los contenidos de la vista que ve la sentencia son sus contenidos de la primera ejecución de la sentencia. Esto significa por ejemplo que si se ejecuta una sentencia en un bucle, en todas las iteraciones de la sentencia se verá el mismo contenido de la vista, aunque la definición de la vista cambie durante el bucle. Ejemplo:

    CREATE VIEW v AS SELECT 1;
    delimiter //
    CREATE PROCEDURE p ()
    BEGIN
      DECLARE i INT DEFAULT 0;
      WHILE i < 5 DO
        SELECT * FROM v;
        SET i = i + 1;
        ALTER VIEW v AS SELECT 2;
      END WHILE;
    END;
    //
    delimiter ;
    CALL p();
    

    Cuando se llama al procedimiento p(), el SELECT devuelve siempre 1 dentro del bucle, aunque dentro del mismo cambie la definición de la vista.

Con respecto a las actualizaciones en vistas, el objetivo es que cualquier vista que sea teóricamente actualizable, tiene que serlo en la práctica. Esto incluye vistas que tienen UNION en su definición. Actualmente, no todas las vistas teóricamente actualizables se pueden actualizar. La implementación inicial de vistas fue deliberadamente escrita de esta forma para obtener en MySQL vistas utilizables y actualizables lo antes posible. Muchas vistas teóricamente actualizables pueden ser actualizadas actualmente, pero algunas limitaciones siguen existiendo:

  • Las vistas actualizables con subconsultas en cualquier lugar que no sea en la cláusula WHERE. Algunas vistas que tienen subconsultas en la lista SELECT podrían ser actualizables.

  • No se puede utilizar UPDATE para actualizar más de una tabla incluida en una vista que sea definida como un join.

  • No se puede usar una sentencia DELETE para actualizar una vista que está definida como un JOIN.


Ésta es una traducción del manual de referencia de MySQL, que puede encontrarse en dev.mysql.com. El manual de referencia original de MySQL está escrito en inglés, y esta traducción no necesariamente está tan actualizada como la versión original. Para cualquier sugerencia sobre la traducción y para señalar errores de cualquier tipo, no dude en dirigirse a mysql-es@vespito.com.