El sistema de base de datos es el núcleo del sistema de información de gestión. El procesamiento de transacciones en línea (OLTP) basado en bases de datos y el procesamiento analítico en línea (OLAP) son una de las aplicaciones informáticas más importantes en bancos, empresas, gobiernos y otros departamentos. Basado en ejemplos de aplicación y combinado con la teoría de bases de datos, este artículo presenta la aplicación de la tecnología de optimización de consultas en sistemas reales. A juzgar por los ejemplos de aplicación de la mayoría de los sistemas, las operaciones de consulta representan la mayor proporción de varias operaciones de bases de datos, y la declaración SELECT en la que se basa la operación de consulta es la declaración más cara entre las declaraciones SQL. Por ejemplo, si la cantidad de datos se acumula hasta un cierto nivel, como la información de la tabla de la base de datos de la cuenta de un banco que se acumula en millones o incluso decenas de millones de registros, un escaneo completo de la tabla a menudo lleva decenas de minutos o incluso horas. Si adopta una mejor estrategia de consulta que un escaneo completo de la tabla, a menudo puede reducir el tiempo de consulta a unos pocos minutos, lo que demuestra la importancia de la tecnología de optimización de consultas.
Durante la implementación del proyecto de la aplicación, el autor descubrió que al desarrollar aplicaciones de bases de datos utilizando algunas herramientas de desarrollo de bases de datos front-end (como PowerBuilder, Delphi, etc.), muchos programadores solo se centran en la belleza de la interfaz de usuario y no pagan. Se presta atención a la eficiencia de las declaraciones de consulta, lo que genera todos los problemas. El sistema de aplicación desarrollado es ineficiente y provoca un grave desperdicio de recursos. Por lo tanto, es muy importante cómo diseñar declaraciones de consulta eficientes y razonables. Basado en ejemplos de aplicación y combinado con la teoría de bases de datos, este artículo presenta la aplicación de la tecnología de optimización de consultas en sistemas reales.
Analiza el problema
Muchos programadores creen que la optimización de consultas es tarea del DBMS (sistema de gestión de bases de datos) y tiene poco que ver con las declaraciones SQL escritas por los programadores. Un buen plan de consultas a menudo puede mejorar el rendimiento del programa decenas de veces. El plan de consulta es una colección de declaraciones SQL enviadas por el usuario y el plan de consulta es una colección de declaraciones generadas después de la optimización. El proceso de procesamiento del plan de consulta DBMS es el siguiente: después de completar la verificación léxica y sintáctica de la declaración de la consulta, la declaración se envía al optimizador de consultas DBMS. Una vez que el optimizador completa la optimización algebraica y la optimización de la ruta de acceso, el módulo precompilado procesa el. declaración y generar un plan de consulta, luego enviarlo al sistema para su procesamiento y ejecución en el momento apropiado, y finalmente devolver los resultados de la ejecución al usuario. En versiones superiores de productos de bases de datos reales (como Oracle, Sybase, etc.), se utilizan métodos de optimización basados en costos. Esta optimización puede estimar el costo de diferentes planes de consulta en función de la información obtenida de la tabla del diccionario del sistema y luego seleccionar. Una mejor planificación. Aunque los productos de bases de datos actuales son cada vez mejores en la optimización de consultas, las declaraciones SQL enviadas por los usuarios son la base para la optimización del sistema. Es difícil imaginar que un plan de consulta originalmente deficiente se vuelva eficiente después de la optimización del sistema. Las declaraciones que escriben los usuarios son cruciales. No discutiremos la optimización de consultas realizada por el sistema por ahora. Lo siguiente se centra en soluciones para mejorar los planes de consultas de los usuarios.
resolver problemas
A continuación se toma el sistema de base de datos relacional Informix como ejemplo para presentar métodos para mejorar los planes de consulta de los usuarios.
1. Uso razonable de índices
El índice es una estructura de datos importante en la base de datos y su propósito fundamental es mejorar la eficiencia de las consultas. La mayoría de los productos de bases de datos utilizan ahora la estructura de índice ISAM propuesta por primera vez por IBM. El uso de índices debe ser adecuado, y los principios para su uso son los siguientes:
●Cree índices en columnas que se conectan con frecuencia pero que no están designadas como claves externas, mientras que el optimizador genera automáticamente índices para campos conectados con poca frecuencia.
● Crear índices en columnas que se ordenan o agrupan con frecuencia (es decir, operaciones de agrupación u ordenamiento por).
●Cree búsquedas en columnas con muchos valores diferentes que se utilizan a menudo en expresiones condicionales. No cree índices en columnas con pocos valores diferentes. Por ejemplo, solo hay dos valores diferentes en la columna "Sexo" de la tabla de empleados, "Hombre" y "Mujer", por lo que no es necesario crear un índice. Si crea un índice, no solo no mejorará la eficiencia de las consultas, sino que reducirá seriamente la velocidad de actualización.
●Si hay varias columnas para ordenar, puede crear un índice compuesto en estas columnas.
●Utilice herramientas del sistema. Por ejemplo, la base de datos Informix tiene una herramienta tbcheck que puede comprobar índices sospechosos. En algunos servidores de bases de datos, el índice puede no ser válido o la eficiencia de lectura puede verse reducida debido a operaciones frecuentes. Si una consulta que utiliza el índice se ralentiza sin motivo aparente, puede intentar utilizar la herramienta tbcheck para comprobar la integridad del índice. y repararlo si es necesario. Además, cuando una tabla de base de datos actualiza una gran cantidad de datos, eliminar y reconstruir el índice puede mejorar la velocidad de la consulta.
2. Evite o simplifique la clasificación
Se debe simplificar o evitar la clasificación repetida de mesas grandes. El optimizador evita el paso de clasificación cuando puede usar un índice para producir automáticamente resultados en el orden adecuado. Aquí hay algunos factores que influyen:
●El índice no incluye una o varias columnas para ordenar;
●El orden de las columnas en la cláusula agrupar por u ordenar por es diferente del orden del índice;
●Las columnas ordenadas provienen de tablas diferentes.
Para evitar una clasificación innecesaria, es necesario agregar índices correctamente y fusionar tablas de bases de datos de manera razonable (aunque a veces puede afectar la normalización de las tablas, la mejora en la eficiencia vale la pena). Si la clasificación es inevitable, debe intentar simplificarla, como reducir el rango de columnas para la clasificación, etc.
3. Elimine el acceso secuencial a datos de filas de tablas grandes
En consultas anidadas, el acceso secuencial a las tablas puede tener un impacto fatal en la eficiencia de las consultas. Por ejemplo, utilizando una estrategia de acceso secuencial, si una consulta con tres niveles anidados consulta 1000 filas en cada nivel, entonces esta consulta consultará mil millones de filas de datos. La principal forma de evitar esto es indexar las columnas unidas. Por ejemplo, dos tablas: tabla de estudiantes (número de estudiante, nombre, edad...) y tabla de selección de cursos (número de estudiante, número de curso, calificaciones). Si se van a conectar dos tablas, se debe crear un índice en el campo de conexión "número de estudiante".
También puede utilizar uniones para evitar el acceso secuencial. Aunque hay índices en todas las columnas de verificación, algunas formas de cláusulas donde obligan al optimizador a utilizar acceso secuencial. La siguiente consulta forzará operaciones secuenciales en la tabla de pedidos: SELECCIONAR * DE pedidos DONDE (núm_cliente=104 Y núm_pedido>1001) O núm_pedido=1008
Aunque hay índices en núm_cliente y núm_pedido, el optimizador todavía usa la ruta de acceso secuencial para escanear toda la tabla en la declaración anterior. Debido a que esta declaración recupera una colección separada de filas, se debe cambiar a la siguiente declaración:
SELECCIONE * DE pedidos DONDE núm_cliente=104 Y núm_pedido>1001
UNIÓN
SELECCIONE * DE pedidos DONDE núm_pedido=1008
Esto permite utilizar la ruta del índice para procesar consultas.
4. Evite subconsultas correlacionadas
Si aparece una etiqueta de columna tanto en la consulta principal como en la consulta en la cláusula donde, es probable que se deba volver a consultar la subconsulta cuando cambie el valor de la columna en la consulta principal. Cuantos más niveles anidados tenga una consulta, menor será su eficiencia, por lo que se deben evitar las subconsultas tanto como sea posible. Si una subconsulta es inevitable, filtre tantas filas como sea posible en la subconsulta.
5. Evite las expresiones regulares difíciles
Las palabras clave MATCHES y LIKE admiten coincidencias con comodines, técnicamente denominadas expresiones regulares. Pero este tipo de emparejamiento requiere mucho tiempo. Por ejemplo: SELECCIONAR * DEL cliente DONDE código postal COMO “98_ _ _”
Incluso si se crea un índice en el campo del código postal, en este caso se sigue utilizando el escaneo secuencial. Si cambia la declaración a SELECT * FROM customer WHERE zipcode > "98000", el índice se utilizará para consultar al ejecutar la consulta, lo que obviamente mejorará enormemente la velocidad.
Además, evite las subcadenas que no comiencen. Por ejemplo, la declaración: SELECT * FROM customer WHERE zipcode[2, 3]>"80" usa una subcadena que no comienza en la cláusula donde, por lo que esta declaración no usa el índice.
6. Utilice tablas temporales para acelerar las consultas.
Ordenar un subconjunto de la tabla y crear una tabla temporal a veces puede acelerar las consultas. Ayuda a evitar múltiples operaciones de clasificación y, por lo demás, simplifica el trabajo del optimizador. Por ejemplo: SELECT cust.name, rcVBles.balance,...otras columnas
SELECCIONE el nombre del cliente, el saldo de rcVBles,... otras columnas
DE clientes, rcvbles
DONDE cust.customer_id = rcvlbes.customer_id
Y rcvblls.saldo>0
Y código postal cliente>"98000"
ORDENAR POR nombre.cliente
Si esta consulta se va a ejecutar varias veces en lugar de solo una vez, puede buscar todos los clientes no pagados en un archivo temporal y ordenarlos por nombre de cliente: SELECT cust.name, rcvbles.balance,...otras columnas
SELECCIONE nombre.cliente,rcvbles.saldo,...otras columnas
DE clientes, rcvbles
DONDE cust.customer_id = rcvlbes.customer_id
Y rcvblls.saldo>0
ORDENAR POR nombre.cliente
EN TEMP cliente_con_equilibrio
Luego consulte en la tabla temporal de la siguiente manera: SELECT * FROM cust_with_balance
DONDE código postal>"98000"
Hay menos filas en la tabla temporal que en la tabla principal y el orden físico es el orden requerido, lo que reduce la E/S del disco, por lo que la carga de trabajo de consultas se puede reducir considerablemente.
Nota: Una vez creada la tabla temporal, no reflejará la modificación de la tabla principal. Cuando los datos de la tabla principal se modifican con frecuencia, tenga cuidado de no perderlos.
7. Utilice la clasificación para reemplazar el acceso no secuencial.
El acceso al disco no secuencial es la operación más lenta y está representado por el movimiento hacia adelante y hacia atrás del brazo de acceso al disco. Las declaraciones SQL ocultan esta situación, lo que nos facilita escribir consultas que requieren acceso a una gran cantidad de páginas no secuenciales al escribir aplicaciones. A veces, utilizar las capacidades de clasificación de la base de datos en lugar del acceso no secuencial puede mejorar las consultas.
-