MariaDB / Podando particion

Bienvenidos sean a este post, hoy hablaremos sobre una mecanica para las particiones.

En mariadb se nos permite hacer la «poda» de las particiones en las de tipo RANGE y LIST pero no con RANGE COLUMNS o LIST COLUMNS, cuando una instruccion referencia las columnas utilizadas por la expresion de particionamiento el optimizador es capaz de determinar si una o mas particiones pueden ser excluidas del plan del query, a menudo excluye todas las particiones pero una (en algunas casos) puede detectar un «Impossible WHERE» y evitar la total ejecucion del query, el optimizador examina la opcion WHERE en las siguientes instrucciones para determinar si la poda de particion es posible:

• SELECT
• INSERT … SELECT
• REPLACE … SELECT
• DELETE
• UPDATE

Para las instrucciones INSERT el optimizador examina los valores insertados, en cambio para REPLACE se examina tanto el WHERE como los valores ingresados, el optimizador al examinar la opcion WHERE puede usar los siguientes operadores para la poda:

• =
• !=
• <
• >
• <=
• >=
• BETWEEN
• IN

Para los casos donde tenemos particiones que contienen filas para las cuales las expresiones de particionamiento devuelven valores NULL podemos usar tambien a los operadores IS NULL e IS NOT NULL.

Si queremos obtener un plan de ejecucion de la instruccion podemos utilizar a EXPLAIN, del cual hablamos en este post, y en su momento lo vimos a nivel tablas pero hoy para ver las particiones debemos usar la opcion PARTITIONS la cual nos agregara la columna PARTITIONS a la salida de EXPLAIN, en esta encontraremos una lista de particiones que usaremos para ejecutar la instruccion, como podran darse cuenta esta extension fue agregada especialmente para verificar si y como se aplica la poda de particiones, para entender el concepto vamos a realizar un ejemplo.

Para este ejemplo vamos a crear una tabla, en caso de haber estado trabajando en los posts anteriores deben eliminar la tabla articulos porque la volveremos a crear pero con otro tipo de particionamiento:

MariaDB [base1]> create table articulos (
    -> id integer unsigned not null auto_increment,
    -> fecha date not null,
    -> autor varchar(100),
    -> idioma tinyint unsigned,
    -> texto text,
    -> primary key (id, fecha)
    -> )
    -> engine = InnoDB
    -> partition by range(id) (
    -> partition p0 values less than (5000),
    -> partition p1 values less than (10000),
    -> partition p2 values less than (15000),
    -> partition p3 values less than (20000));

En este caso el particionamiento lo hacemos por id a diferencia de como lo estabamos haciendo hasta ahora por año, esto lo hacemos asi porque los queries basados en la columna ID sacan mucho provecho a la poda de particion, veamos un ejemplo:

MariaDB [base1]> explain partitions
    -> select * from articulos where id > 5000\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: articulos
   partitions: p1,p2,p3
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.015 sec)

MariaDB [base1]>

En este caso le dijimos que nos traiga toda la informacion para el caso donde id sea mayor a 5000, si observamos vemos que en la columna de particiones nos devolvio todas menos p0 que es la unica menor a la cifra solicitada, vamos a hacer otro query:

MariaDB [base1]> explain partitions
    -> select * from articulos where id between 8000 and 11000\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: articulos
   partitions: p1,p2
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 2
        Extra: Using where
1 row in set (0.001 sec)

MariaDB [base1]>

En este query le pedimos que evalue un rango entre 8000 y 11000, para lo cual nos informa que solamente se utilizaran dos particiones, veamos otro ejemplo:

MariaDB [base1]> explain partitions
    -> select * from articulos where id = 11000\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: articulos
   partitions: p2
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: 
1 row in set (0.000 sec)

MariaDB [base1]>

En esta salida podemos ver que solamente nos comenta una particion porque es la unica que puede cubrir dicho valor para ID, veamos un ejemplo particular:

MariaDB [base1]> explain partitions
    -> select * from articulos where id=999999999 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Impossible WHERE noticed after reading const tables
1 row in set (0.001 sec)

MariaDB [base1]>

Aqui podemos ver el caso de Impossible WHERE dado que nuestras particiones no pueden cubrir un valor de id tan alto pero tambien podemos tenerlo por realizar una mala utilizacion de logica:

MariaDB [base1]> explain partitions
    -> select * from articulos where id=18000 and id < 5000 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Impossible WHERE
1 row in set (0.001 sec)

MariaDB [base1]>

En este caso por un mal uso del AND nos devolvio un Impossible Where pero no notifica el motivo como en el caso anterior pero sabemos que no funcionara, por ultimo vamos a ver el siguiente query:

MariaDB [base1]> explain partitions
    -> select * from articulos where id=18000 or id < 5000 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: articulos
   partitions: p0,p3
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 2
        Extra: Using where
1 row in set (0.000 sec)

MariaDB [base1]>

En este caso al aplicar bien la logica anterior, dado que usamos un OR que verifica si id tiene el primer valor o es menor al siguiente, para esta situacion nos notifica que trabajara en dos particiones ante cualquier coincidencia.

En resumen, hoy hemos visto que es la poda de particiones, que es, para que sirve, hemos visto como se puede observar la misma por medio de EXPLAIN, espero les haya sido de utilidad sigueme en tumblr, Twitter o Facebook para recibir una notificacion cada vez que subo un nuevo post en este blog, nos vemos en el proximo post.