Breve análisis de las tablas temporales y derivadas de MySQL

Acerca de las tablas derivadas

Cuando la consulta principal contiene tablas derivadas, o cuando la cláusula SELECT contiene una palabra clave UNION, o cuando la cláusula SELECT contiene una cláusula ORDER BY de un campo (para una cláusula GROUP BY de otro campo), MySQL necesita crear automáticamente tablas temporales para almacenar los resultados temporales de la consulta. Estas tablas temporales son creadas y mantenidas automáticamente por MySQL y se convierten en tablas temporales creadas automáticamente. Para las tablas temporales creadas automáticamente, debido a que las tablas temporales en memoria tienen un rendimiento más优越, MySQL siempre utiliza primero las tablas temporales en memoria. Cuando las tablas temporales en memoria se vuelven demasiado grandes, cuando alcanzan un cierto umbral, las tablas temporales en memoria se convierten en tablas temporales en almacenamiento externo. Es decir, las tablas temporales en almacenamiento externo son una extensión del espacio de almacenamiento de las tablas temporales en memoria. El umbral para la conversión de las tablas temporales en memoria a tablas temporales en almacenamiento externo se determina por el valor más pequeño de las variables de sistema max_heap_table_size y tmp_table_size.

Las tablas derivadas se utilizan generalmente en la cláusula FROM. Por ejemplo:

select * from (select * from table) as t;

Acerca de las tablas temporales

Cuando trabaja en tablas muy grandes, es posible que necesite ejecutar muchas consultas para obtener un pequeño subconjunto de grandes cantidades de datos, no ejecutar estas consultas en toda la tabla, sino que permitir que MySQL encuentre cada vez los pocos registros necesarios, seleccionarlos en una tabla temporal puede ser más rápido, y luego ejecutar consultas en estas tablas.

Es fácil crear una tabla temporal, simplemente agregue la palabra clave TEMPORARY al comando CREATE TABLE normal:

CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
)

La tabla temporal existirá mientras esté conectado a MySQL. Al desconectar, MySQL eliminará automáticamente la tabla y liberará el espacio utilizado. Por supuesto, también puede eliminar la tabla y liberar el espacio mientras aún esté conectado.

DROP TABLE tmp_table

Si al crear la tabla temporal llamada tmp_table ya existe una tabla con ese nombre en la base de datos, será necesario ocultar (esconder) la tabla no temporal tmp_table.

Si declara que la tabla temporal es una tabla HEAP, MySQL también permite especificar que se cree en la memoria:

CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
) TYPE = HEAP

Dado que las tablas HEAP se almacenan en la memoria, las consultas que ejecuta puede ser más rápidas que las tablas temporales en disco. Sin embargo, las tablas HEAP son diferentes de las tablas comunes y tienen sus propias limitaciones. Consulte el manual de referencia de MySQL para obtener más información.

Como se sugirió anteriormente, debe probar la tabla temporal para ver si realmente es más rápida que ejecutar consultas en una gran base de datos. Si los datos están bien indexados, la tabla temporal puede no ser tan rápida.

1. La tabla temporal se borrara automáticamente los datos en la conexión de MySQL después de cerrar, pero esto solo se aplica a las tablas creadas con la siguiente sentencia:

Definir campo:

CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
)

2) directamente importar los resultados de la consulta a la tabla temporal

CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name

2. Además, MySQL también permite que cree tablas temporales directamente en la memoria, ya que está en la memoria, la velocidad será muy rápida, la sintaxis es la siguiente:

CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
) TYPE = HEAP

3A partir del análisis anterior, se puede ver que los datos de la tabla temporal se borraran, si cierra la conexión se borraran automáticamente, pero es imposible que cada vez que ejecute una sql se conecte a la base de datos (si es así, se producirán los problemas que preocupa, si no, no hay problemas), porque solo al cerrar la conexión de la base de datos se borraran los datos, si emite múltiples sql en una conexión de base de datos, el sistema no borrara automáticamente los datos de la tabla temporal.

Declaración: Este artículo se ha obtenido de la red, pertenece al propietario original, el contenido se ha contribuido y cargado de manera autónoma por los usuarios de Internet, este sitio web no posee los derechos de propiedad, no se ha realizado un procesamiento editorial humano y no asume ninguna responsabilidad legal relacionada. Si encuentra contenido sospechoso de infracción de derechos de autor, por favor envíe un correo electrónico a: notice#w proporcionando evidencia relevante.3Declaración: El contenido de este artículo se obtiene de la red, pertenece al propietario original, el contenido se contribuye y carga de manera autónoma por los usuarios de Internet, este sitio web no posee los derechos de propiedad, no se ha realizado un procesamiento editorial humano y no asume ninguna responsabilidad legal relacionada. Si encuentra contenido sospechoso de infracción de derechos de autor, por favor envíe un correo electrónico a: notice#w proporcionando evidencia relevante, una vez que se verifique, este sitio eliminará inmediatamente el contenido sospechoso de infracción.