La cantidad de datos almacenada dentro de la base de datos tiene un impacto importante sobre su rendimiento. Generalmente se admite que una sentencia llegue a ser lenta con datos adicionales dentro de la base de datos. Pero ¿cuál sería el impacto sobre el rendimiento si el volumen de datos se multiplica por dos? ¿Y cómo se puede mejorar este ratio? Existen preguntas clave cuando se habla de escalabilidad de base de datos.
Por ejemplo, ¿cómo se analiza el tiempo de respuesta de la siguiente sentencia cuando se usan dos índices diferentes? Las definiciones de los índices permanecerán desconocidas por el momento; se revelarán durante el transcurso del debate.
SELECT count(*)
FROM scale_data
WHERE section = ?
AND id2 = ?La columna SECTION tiene un propósito especial en esta sentencia: controla el volumen de datos. Cuanto mayor llegue a ser el numero de SECTION, más registros seleccionará la sentencia. La Figura 3.1 muestra el tiempo de respuesta para una SECTION con pocos registros.
Figura 3.1 Comparación de rendimiento
Existen diferencias considerables de rendimiento entre las dos variantes de los índices. Ambos tiempos de respuesta son tan sólo de una décima de segundo; la sentencia más lenta es probablemente lo suficientemente rápida en muchos casos. Sin embargo, la gráfica del rendimiento solamente muestra un punto de prueba. Hablar de escalabilidad significa ver el impacto sobre el rendimiento cuando cambian parámetros ambientales; como el volumen de datos.
Importante
La escalabilidad muestra la dependencia del rendimiento con factores como el volumen de datos.
Un valor de rendimiento es solamente un único punto de datos sobre la gráfica de escalabilidad.
La Figura 3.2 muestra el tiempo de respuesta con el número de registros de SECTION; esto es, para un volumen de datos creciente.
Figura 3.2 Escalabilidad con el volumen de datos
La gráfica muestra un tiempo de respuesta creciente para ambos índices. Del lado derecho de la gráfica, cuando el volumen de datos es cientos de veces más alto, la sentencia más rápida necesita el doble de tiempo que inicialmente, mientras que el tiempo de respuesta de la sentencia más lenta se incrementa en un factor de 20 para llegar a ser de más de un segundo.
Sugerencia
Apéndice C, “Example Schema” tiene los scripts para repetir las pruebas en una base de datos Oracle, PostgreSQL o SQL Server.
El tiempo de respuesta de una sentencia SQL depende de muchos factores. El volumen de datos es uno de ellos. Aunque la sentencia sea bastante rápida bajo algunas condiciones de prueba, eso no significa que vaya a ser también bastante rápida en producción. Este es especialmente el caso en ambientes de desarrollo que tienen solamente una parte de los datos del sistema de producción.
No resulta sorprendente que las sentencias lleguen a ser más lentas cuando el volumen de datos crece. Pero la diferencia sorprendente entre los dos índices es algo más inesperado. ¿Cuál es la razón de este diferente crecimiento?
Debería ser fácil encontrar la razón comparando los dos planes de ejecución.
- Db2 (LUW)
------------------------------------------------------------- ID | Operation | Rows | Cost 1 | RETURN | | 208 2 | GRPBY (COMPLETE) | 1 of 4456 ( .02%) | 208 3 | IXSCAN SCALE_SLOW | 4456 of 135449700 ( .00%) | 208Explain Plan ------------------------------------------------------------- ID | Operation | Rows | Cost 1 | RETURN | | 296 2 | GRPBY (COMPLETE) | 1 of 4456 ( .02%) | 296 3 | IXSCAN SCALE_FAST | 4456 of 135449700 ( .00%) | 296- MySQL
+------+------------+---------+-------+------+-----------------------+ | type | key | key_len | ref | rows | Extra | +------+------------+---------+-------+------+-----------------------+ | ref | scale_slow | 6 | const | 1 | Using index condition | +------+------------+---------+-------+------+-----------------------++------+------------+---------+-------------+------+-------+ | type | key | key_len | ref | rows | Extra | +------+------------+---------+-------------+------+-------+ | ref | scale_fast | 12 | const,const | 1 | | +------+------------+---------+-------------+------+-------+- Oracle
------------------------------------------------------ | Id | Operation | Name | Rows | Cost | ------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 972 | | 1 | SORT AGGREGATE | | 1 | | |* 2 | INDEX RANGE SCAN| SCALE_SLOW | 3000 | 972 | ------------------------------------------------------------------------------------------------------------ | Id Operation | Name | Rows | Cost | ------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 13 | | 1 | SORT AGGREGATE | | 1 | | |* 2 | INDEX RANGE SCAN| SCALE_FAST | 3000 | 13 | ------------------------------------------------------- SQL Server
El plan de ejecución a continuación usa el índice
scale_slow, mientras que el siguiente plan usascale_fast. Observa que ambos usan una operación de tipo "Index Seek". Pero, eso no da ninguna pista del porque el primero es más lento que el otro.
Sin embargo, con
STATISTICS PROFILE ONse puede notar una diferencia:|--Compute Scalar |--Stream Aggregate(Count(*)) |--Index Seek(OBJECT:scale_slow), SEEK:(scale_data.section=2), WHERE:(scale_data.id2=1234) ORDERED FORWARD)|--Compute Scalar |--Stream Aggregate(Count(*)) |--Index Seek(OBJECT:(scale_data.scale_fast), SEEK:(scale_data.section=1) AND scale_data.id2=1234) ORDERED FORWARD)
Los planes de ejecución son casi idénticos, con la excepción de que usan un índice diferente. A pesar de que los valores del costo reflejan la diferencia de velocidad, la razón no es visible en el plan de ejecución.
Pareciera que estamos confrontados con “un índice lento”; la sentencia es lenta aunque usa un índice. Sin embargo, a estas alturas ya no se debería creer en el mito del “índice quebrado”. En lugar de eso, deben recordarse los dos ingredientes que producen una búsqueda (“lookup”) del índice lento: (1) el acceso de la tabla; y (2) escanear un rango amplio del índice.
Ni el plan de ejecución muestra una operación de tipo TABLE ACCESS BY INDEX ROWID ni una de las ejecuciones debe escanear un rango del índice más amplio que la otra. Entonces, ¿dónde se muestra el rango del índice escaneado dentro del plan de ejecución? Por supuesto, ¡en la información de los predicados!
Sugerencia
Presta atención a la información de los predicados.
La información de los predicados no es un detalle innecesario que se puede omitir como se hizo previamente. Un plan de ejecución sin la información de los predicados es incompleto. Eso significa que no se puede ver la razón de la diferencia de rendimiento entre los planes mostrados previamente. Si ve los planes de ejecución completos, se podrán detectar las diferencias.
- Db2 (LUW)
Explain Plan ------------------------------------------------------------- ID | Operation | Rows | Cost 1 | RETURN | | 208 2 | GRPBY (COMPLETE) | 1 of 4456 ( .02%) | 208 3 | IXSCAN SCALE_SLOW | 4456 of 135449700 ( .00%) | 208 Predicate Information 3 - START (Q1.SECTION = ?) STOP (Q1.SECTION = ?) SARG (Q1.ID2 = ?)Explain Plan ------------------------------------------------------------- ID | Operation | Rows | Cost 1 | RETURN | | 296 2 | GRPBY (COMPLETE) | 1 of 4456 ( .02%) | 296 3 | IXSCAN SCALE_FAST | 4456 of 135449700 ( .00%) | 296 Predicate Information 3 - START (Q1.SECTION = ?) START (Q1.ID2 = ?) STOP (Q1.SECTION = ?) STOP (Q1.ID2 = ?)También se observan los valores del costo: aunque el segundo índice es más eficiente, el primero tiene el costo más bajo, lo que provoca que el optimizador escoja el peor en ambos casos.
- MySQL
+------+------------+---------+-------+------+-----------------------+ | type | key | key_len | ref | rows | Extra | +------+------------+---------+-------+------+-----------------------+ | ref | scale_slow | 6 | const | 1 | Using index condition | +------+------------+---------+-------+------+-----------------------++------+------------+---------+-------------+------+-------+ | type | key | key_len | ref | rows | Extra | +------+------------+---------+-------------+------+-------+ | ref | scale_fast | 12 | const,const | 1 | | +------+------------+---------+-------------+------+-------+- Oracle
------------------------------------------------------ | Id | Operation | Name | Rows | Cost | ------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 972 | | 1 | SORT AGGREGATE | | 1 | | |* 2 | INDEX RANGE SCAN| SCALE_SLOW | 3000 | 972 | ------------------------------------------------------ Predicate Information (identified by operation id): 2 - access("SECTION"=TO_NUMBER(:A)) filter("ID2"=TO_NUMBER(:B))------------------------------------------------------ | Id Operation | Name | Rows | Cost | ------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 13 | | 1 | SORT AGGREGATE | | 1 | | |* 2 | INDEX RANGE SCAN| SCALE_FAST | 3000 | 13 | ------------------------------------------------------ Predicate Information (identified by operation id): 2 - access("SECTION"=TO_NUMBER(:A) AND "ID2"=TO_NUMBER(:B))- SQL Server
Para ver la diferencia en la gráfica del plan de ejecución, se necesita mover el cursor sobre la operación
Index Seeky verificar los "Predicates" frente a los "Seek Predicates".|--Compute Scalar |--Stream Aggregate(Count(*)) |--Index Seek(OBJECT:scale_slow), SEEK:(scale_data.section=2), WHERE:(scale_data.id2=1234) ORDERED FORWARD)|--Compute Scalar |--Stream Aggregate(Count(*)) |--Index Seek(OBJECT:(scale_data.scale_fast), SEEK:(scale_data.section=1) AND scale_data.id2=1234) ORDERED FORWARD)Los filtros
WHEREen el primer plan de ejecución indican el uso de índice (predicados de filtro) los cuales no restringen el rango escaneado del índice. El segundo plan de ejecución muestra ambos predicados debajo deSEEK, que es el término para los predicados de acceso.
La diferencia ahora es evidente: solamente la condición sobre SECTION es un predicado de acceso cuando se usa el índice SCALE_SLOW. La base de datos lee todos los registros desde la “SECTION” y descarta los que no coinciden con el predicado de filtro ID2. El tiempo de respuesta crece con el número de registros dentro de “SECTION”. Con el índice SCALE_FAST, la base de datos usa todas las condiciones como predicados de acceso. El tiempo de respuesta crece con el número de registros seleccionados.
Importante
Los predicados de filtro son como recursos de artillería sin explotar: pueden explotar en cualquier momento.
La última pieza que hace falta en nuestro rompecabezas es la definición de los índices. ¿Se pueden reconstruir las definiciones de los índices desde los planes de ejecución?
La definición del índice SCALE_SLOW debe empezar con la columna SECTION; si no, no podría usarse como predicado de acceso. La condición sobre ID2 no es un predicado de acceso; por lo tanto no puede seguir SECTION en la definición del índice. Eso significa que el índice SCALE_SLOW debe tener mínimo tres columnas, donde SECTION es la primera y ID2 no es la segunda. Es exactamente como está la definición del índice usado para esta prueba:
CREATE INDEX scale_slow ON scale_data (section, id1, id2)La base de datos no puede usar ID2 como predicado de acceso debido a que la columna ID1 está en segunda posición.
La definición del índice SCALE_FAST debe tener las columnas SECTION y ID2 en las dos primeras posiciones porque ambas se usaron como predicados de acceso. Sin embargo, no se puede decir nada acerca de su orden. El índice que se ha usado para la prueba empieza con la columna SECTION y tiene una columna adicional ID1 en la tercera posición:
CREATE INDEX scale_fast ON scale_data (section, id2, id1)La columna ID1 se agregó solamente para que este índice tenga el mismo tamaño que SCALE_SLOW. Si no, se podría tener la impresión de que el tamaño causa la diferencia.
Si te gusta mi manera de explicar, te encantará mi libro.
Ver también
Predicados de filtros del índice explicados: condiciones "mayor que", "menor que" y "between"
Encontrar los predicados de filtros del índice en la base de datos Oracle, PostgreSQL y SQL Server.
Indexar los filtros LIKE: los predicados de acceso y de filtro del índice en una sola expresión
Big-O notation: el punto de vista de las matemáticas para la escalabilidad
