Antes de la optimización: Aprendizaje del diseño de la estructura de tablas

Selección de un modelo de tabla

Los tipos más comunes de modelos de tablas en un almacén de datos son los modelos en estrella y en copo de nieve. Tenga en cuenta los requisitos de servicio y rendimiento al elegir un modelo para sus tablas.

• En el modelo en estrella, una tabla de hechos central contiene los datos principales de la base de datos y varias tablas de dimensiones proporcionan información de atributos descriptivos para la tabla de hechos. La clave principal de una tabla de dimensiones asocia una clave externa en una tabla de hechos, como se muestra en Figura 1.
  • Todos los hechos deben tener la misma granularidad.
  • Las dimensiones diferentes no están asociadas.
  Figura 1 Modelo en estrella
  
• El modelo en copo de nieve se desarrolla en función del modelo en estrella. En este modelo, cada dimensión puede asociarse con múltiples dimensiones y dividirse en tablas de diferentes granularidades en función del nivel de dimensión, como se muestra en Figura 2.
  • Las tablas de dimensiones se pueden asociar según sea necesario y los datos almacenados en ellas se reducen.
  • Este modelo tiene más tablas de dimensiones que mantener que el esquema en estrella.
  Figura 2 Modelo en copo de nieve
  

Esta práctica verifica el rendimiento utilizando el modelo Store Sales (SS) de TPC-DS. El modelo utiliza el modelo en copo de nieve. Figura 3 ilustra su estructura.

Figura 3 TPC-DS store sales ER-Diagram

Para obtener más información sobre la tabla de hechos store_sales y las tablas de dimensiones del modelo, consulte el documento oficial de TPC-DS en https://www.tpc.org/tpc_documents_current_versions/current_specifications5.asp.

Selección de un modo de almacenamiento

La selección de un modelo para el almacenamiento de tablas es el primer paso de la definición de tablas. Seleccione un modelo de almacenamiento adecuado para su servicio según la tabla siguiente.

Generalmente, si una tabla contiene muchas columnas (llamada tabla ancha) y su consulta implica solo unas pocas columnas, se recomienda el almacenamiento de columnas. Si una tabla contiene solo unas pocas columnas y una consulta implica la mayoría de las columnas, se recomienda el almacenamiento de filas.

El almacenamiento en filas/columnas de una tabla se especifica mediante el atributo orientation en la definición de la tabla. El valor row indica una tabla de almacenamiento en filas y column indica una tabla de almacenamiento en columnas. El valor predeterminado es row.

Compresión de tabla

La compresión de tablas se puede habilitar cuando se crea una tabla. La compresión de tablas permite almacenar los datos de la tabla en formato comprimido para reducir el uso de memoria.

En escenarios donde la E/S es grande (se leen y escriben muchos datos) y la CPU es suficiente (se calculan pocos datos), seleccione una alta relación de compresión. En escenarios donde la E/S es pequeña y la CPU es insuficiente, seleccione una baja relación de compresión. Según este principio, se recomienda seleccionar diferentes relaciones de compresión y probar y comparar los resultados para seleccionar la relación de compresión óptima según sea necesario.

En DWS, solo se pueden comprimir las tablas de almacenamiento en columnas. Las tablas de almacenamiento en filas no se pueden comprimir. La relación de compresión se especifica mediante el parámetro COMPRESSION.

Para las tablas de almacenamiento en columnas, su valor puede ser LOW (valor predeterminado), MIDDLE o HIGH.

Los escenarios de servicio aplicables a cada nivel de compresión se describen en la siguiente tabla.

Selección de un modo de distribución

DWS admite los siguientes modos de distribución: replicación, hash y Round-robin.

Round-robin es compatible con el clúster 8.1.2 y versiones posteriores.

Selección de una clave de distribución

Si se utiliza el modo de distribución hash, se debe especificar una clave de distribución para la tabla de usuario. Si se inserta un registro, el sistema realiza el cálculo hash basado en los valores de la columna de distribución y, a continuación, almacena los datos en el DN relacionado.

Seleccione una clave de distribución hash basada en los siguientes principios:

1. Los valores de la clave de distribución deben ser discretos para que los datos puedan distribuirse uniformemente en cada DN. Puede seleccionar la clave principal de la tabla como clave de distribución. Por ejemplo, para una tabla de información de persona, elija la columna número de identificación como clave de distribución.
2. No seleccione la columna donde existe un filtro constante. Por ejemplo, si existe una restricción constante (por ejemplo, zqdh= '000001') en la columna zqdh en algunas consultas de la tabla dwcjk, no se recomienda utilizar zqdh como clave de distribución.
3. Una vez que se cumplan los principios anteriores, puede seleccionar las condiciones de unión como claves de distribución, de modo que las tareas de unión se puedan enviar a los DN para su ejecución, lo que reduce la cantidad de datos transferidos entre los DN.

   En una tabla hash, una clave de distribución incorrecta puede causar sesgo de datos o un rendimiento de E/S deficiente en determinados DN. Por lo tanto, debe comprobar la tabla para asegurarse de que los datos estén distribuidos uniformemente en cada DN. Puede ejecutar las siguientes sentencias SQL para comprobar si hay sesgo de datos:

   1 2 3 4 5

   SELECT xc_node_id, count(1) FROM tablename GROUP BY xc_node_id ORDER BY xc_node_id desc;

   xc_node_id corresponde a un DN. Por lo general, una diferencia superior al 5% entre la cantidad de datos en los diferentes DN se considera un sesgo de datos. Si la diferencia es mayor al 10%, elija otra clave de distribución.

4. No se recomienda agregar una columna como clave de distribución, especialmente agregar una nueva columna y usar el valor SEQUENCE para completar la columna. (Las secuencias pueden causar cuellos de botella de rendimiento y costos de mantenimiento innecesarios).

Uso de tablas particionadas

La partición se refiere a dividir lo que lógicamente es una tabla grande en piezas físicas más pequeñas basadas en esquemas específicos. La tabla basada en la lógica se llama una tabla particionada, y una pieza física se llama una partición. Los datos se almacenan en estas piezas físicas más pequeñas, a saber, particiones, en lugar de la tabla particionada lógica más grande. Una tabla particionada tiene las siguientes ventajas sobre una tabla ordinaria:

1. Alto rendimiento de la consulta: el sistema consulta solo las particiones en cuestión en lugar de toda la tabla, lo que mejora la eficiencia de la consulta.
2. Alta disponibilidad: Si una partición es defectuosa, los datos en las otras particiones todavía están disponibles.
3. Mantenimiento fácil: solo necesita corregir la partición defectuosa.

Las tablas particionadas admitidas por DWS incluyen tablas particionadas por rango y tablas particionadas por lista. (Las tablas particionadas por lista solo se admiten en el clúster 8.1.3).

Uso de agrupación parcial

La clave de clúster parcial (Partial Cluster Key) es una tecnología de almacenamiento orientado a columnas. Puede minimizar o maximizar los índices dispersos para filtrar rápidamente las tablas base. La clave de clúster parcial puede especificar varias columnas, pero se recomienda especificar no más de dos columnas. Utilice los siguientes principios para especificar columnas:

1. Las columnas seleccionadas deben estar restringidas por expresiones simples en las tablas base. Estas restricciones suelen representarse mediante Col, Op y Const. Col especifica el nombre de la columna, Op especifica los operadores (incluidos =, >, >=, <= y <) y Const especifica las constantes.
2. Seleccione las columnas que se seleccionan con frecuencia (para filtrar muchos más datos no deseados) en expresiones simples.
3. Enumere las columnas seleccionadas con menos frecuencia en la parte superior.
4. Enumere las columnas del tipo enumerado en la parte superior.

Selección de un tipo de datos

Puede utilizar tipos de datos con las siguientes características para mejorar la eficiencia:

1. Tipos de datos que mejoran la eficiencia de la ejecución

   Por lo general, el cálculo de números enteros (incluidos los cálculos de comparación comunes, como =, >, <, ≥, ≤, y ≠ y GROUP BY) es más eficiente que el de cadenas y números de punto flotante. Por ejemplo, si necesita realizar una consulta de punto en una tabla de almacenamiento por columnas cuya columna NUMERIC se utiliza como criterio de filtro, la consulta tardará más de 10 segundos. Si cambia el tipo de datos de NUMERIC a INT, la consulta tardará solo unos 1.8 segundos.

2. Selección de tipos de datos con una longitud corta

   Los tipos de datos de longitud corta reducen tanto el tamaño del archivo de datos como la memoria utilizada para el cómputo, lo que mejora el rendimiento de E/S y de cómputo. Por ejemplo, utilice SMALLINT en lugar de INT e INT en lugar de BIGINT.

3. Mismo tipo de datos para una unión

   Se recomienda utilizar el mismo tipo de datos para una unión. Para unir columnas con diferentes tipos de datos, la base de datos debe convertirlos al mismo tipo, lo que genera sobrecargas de rendimiento adicionales.

Uso de índices

• El objetivo de crear índices es acelerar las consultas. Por lo tanto, asegúrese de que los índices se puedan utilizar en algunas consultas. Si ninguna sentencia de consulta utiliza un índice, este no tiene sentido. Elimine dicho índice.
• No cree índices secundarios innecesarios. Los índices secundarios útiles pueden acelerar las consultas. Sin embargo, el espacio ocupado por los índices aumenta con la cantidad de índices. Cada vez que se agrega un índice, se debe agregar un par clave-valor adicional cuando se inserta un dato. Por lo tanto, cuantos más índices haya, más lenta será la velocidad de escritura y mayor será el uso del espacio. Además, demasiados índices afectan el tiempo de ejecución del optimizador, y los índices inapropiados confunden al optimizador. Tener más índices no necesariamente conduce a mejores resultados.
• Cree índices adecuados en función de las características del servicio. En principio, es necesario crear índices para las columnas requeridas en una consulta para mejorar el rendimiento. Los índices se pueden crear en los siguientes escenarios:
  • Para las columnas con alta diferenciación, los índices pueden reducir significativamente el número de filas después del filtrado. Por ejemplo, se recomienda crear un índice en la columna de número de identificación, pero no en la columna de género.
  • Si hay múltiples condiciones de consulta, puede seleccionar un índice de combinación. Tenga en cuenta que la columna de la condición equivalente debe colocarse antes del índice de combinación. Por ejemplo, si su consulta es SELECT * FROM t where c1 = 10 and c2 = 100 and c3 > 10;, cree un índice compuesto Index cidx (c1, c2, c3) para optimizar el escaneo.
• Cuando se utiliza una columna de índice como condición de consulta, no realice cálculos, funciones ni conversiones de tipo en la columna de índice. De lo contrario, el optimizador no puede utilizar el índice.
• Asegúrese de que la columna de índice contenga la columna de consulta. No ejecute siempre la sentencia SELECT * para consultar todas las columnas.
• Los índices no se utilizan cuando se utilizan != or NOT IN en las condiciones de consulta.
• Al utilizar LIKE, si la condición comienza con el comodín %, no se podrá utilizar el índice.
• Si hay varios índices disponibles para una condición de consulta, pero sabe cuál es el índice óptimo, se recomienda utilizar la sugerencia del optimizador para obligar al optimizador a utilizar el índice. Esto evita que el optimizador seleccione un índice incorrecto debido a estadísticas inexactas u otros problemas.
• Cuando se utiliza la expresión IN como condición de consulta, el número de condiciones coincidentes no debe ser demasiado grande. De lo contrario, la eficiencia de ejecución es baja.