MariaDB / Indices y claves primarias en particiones

Bienvenidos sean a este post, hoy veremos que sucede con los indices y las claves primarias en las particiones.

Con las tablas particionadas las claves primarias y las claves unicas estan sujetas a una limitacion importante, cada clave unica (incluyendo las primarias) deben incluir todas las columnas que son necesarias para el calculo de la expresion de particionamiento, esto significa que:

• Las claves primarias deben incluir columnas de la expresion de particionamiento
• Solo un numero limitado de claves unicas son permitidas

Vamos a suponer un ejemplo donde tenemos que crear una tabla que contiene datos sobre todos los empleados de una compañia, para crear una tabla sin particionar deberiamos usar una instruccion similar a esta:

CREATE TABLE employee (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(200) NOT NULL,
  vat_id VARCHAR(15),
  hire_date DATE NOT NULL,
  PRIMARY KEY (id),
  UNIQUE unq_email (email),
  UNIQUE unq_vat (vat_id),
  INDEX idx_hire (hire_date)
)
  ENGINE = InnoDB;

Vamos a suponer que nuestra tabla se volvio increiblemente grande y necesitamos particionarla, y queremos que la particion adecuada de cada fila sea determinada por YEAR(hire_date), esto implica que debemos modificar la tabla de dos formas.

Primero, necesitaremos que la clave primaria incluya a hire_date porque es la columna utilizada por la expresion de particionamiento, sin embargo esta columna no es suficiente como clave primaria dado que no es un valor unico porque muchos empleados pueden ingresar en el mismo dia, por lo que nuestra clave primaria se transformara de la siguiente manera:

PRIMARY KEY (id, hire_date)

Observen que ahora la clave primaria es mucho mas larga que la definida en el ejemplo inicial, porque desde que todos los indices en una tabla InnoDB incluyen a la clave primaria hara que todos los indices sean mas grandes.

Segundo, un indice unico puede ser util o no para los queries pero es util como una restriccion porque fuerza a las columnas email y vat_id a ser valores unicos, sin embargo en tablas particionadas las claves unicas deben tener todas las columnas usadas por la expresion de particionados, esto lo podemos hacer de tres formas:

• Podemos agregar a hire_date dentro de los indices unicos pero no nos garantizaran la unicidad de estos campos
• Podemos agregar las columnas unicas a la expresion pero se volvera mucho mas lento y frenara los ingresos y actualizaciones
• La solucion mas comun es simplemente evitando los indices unicos, lo cual es lo que haremos en nuestro caso

Con la particion de tipo KEY, de la cual hablaremos en otro post, la clave primaria es obligatoria, sin embargo es posible particionar una tabla sin una clave primaria usando otro tipo de particionamiento pero una tabla sin claves puede ser probablemente muy lento, recuerden que las claves externas no son permitidas en las tablas particionadas y no hay restricciones para indices no unicos normales.

En resumen, hoy hemos visto como trabajan las claves primarias y claves unicas en las tablas particionadas, como son afectadas, como podemos trabajar para pasar una tabla comun a una tabla particionada, espero les haya sido util 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.