systemadmin.es > DBA > Índices en tablas particionadas

Índices en tablas particionadas

En MySQL 5.1 se introdujo la posibilidad de particionar tablas, en algunos casos puede causar más problemas que soluciones. Vamos a ver a continuación un ejemplo de tabla que para las consultas que indicaremos no tiene sentido particionarla.

En la documentación de MySQL hay un listado de limitaciones del particionamiento de MySQL 5.1. A continuación veremos aplicados algunas de las limitaciones:

Primero de todo supondremos la siguiente tabla:

CREATE TABLE `usuarios` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `usuario` varchar(64) NOT NULL,
  `password` varchar(64) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `usuario` (`usuario`)
) ENGINE=MyISAM AUTO_INCREMENT=1645135 DEFAULT CHARSET=utf8;

Con un total de 1.645.134 registros de ejemplo:

mysql> select count(*) from usuarios;
+----------+
| count(*) |
+----------+
|  1645134 |
+----------+
1 row in set (0.00 sec)

El patrón de acceso que vamos a suponer es del siguiente tipo de queries:

SELECT usuario, password FROM usuarios where usuario='ejemplo';

La primera limitación que nos encontramos es que no podemos crear particiones usando tipos de datos no numéricos:

mysql> alter table usuarios_part partition by hash(usuario) partitions 8;
ERROR 1491 (HY000): The PARTITION function returns the wrong type

Y tampoco podremos usar una función como LENGTH() para transformar un string en un valor numérico:

mysql> alter table usuarios_part partition by hash(LENGTH(usuario)) partitions 8;
ERROR 1564 (HY000): This partition function is not allowed

Podemos intentar particionar por id, que ya es un valor numérico, pero nos encontraremos con otra limitación de MySQL, los índices no pueden ser globales, sino locales a la partición:

mysql> alter table usuarios_part partition by hash(id) partitions 8;
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

Podemos verlo mirando los ficheros que genera una tabla sin particionar:

-rw-rw----  1 mysql mysql      8630 Feb 10 09:36 usuarios.frm
-rw-rw----  1 mysql mysql  34396808 Feb 10 09:37 usuarios.MYD
-rw-rw----  1 mysql mysql  53809152 Feb 10 09:37 usuarios.MYI

Y compararlos con los ficheros en una tabla particionada:

-rw-rw----  1 mysql mysql      8630 Feb 10 09:55 usuarios_part.frm
-rw-rw----  1 mysql mysql        48 Feb 10 09:55 usuarios_part.par
-rw-rw----  1 mysql mysql   4300132 Feb 10 09:56 usuarios_part#P#p0.MYD
-rw-rw----  1 mysql mysql   2114560 Feb 10 09:56 usuarios_part#P#p0.MYI
-rw-rw----  1 mysql mysql   4299516 Feb 10 09:56 usuarios_part#P#p1.MYD
-rw-rw----  1 mysql mysql   2114560 Feb 10 09:56 usuarios_part#P#p1.MYI
-rw-rw----  1 mysql mysql   4298944 Feb 10 09:56 usuarios_part#P#p2.MYD
-rw-rw----  1 mysql mysql   2114560 Feb 10 09:56 usuarios_part#P#p2.MYI
-rw-rw----  1 mysql mysql   4299772 Feb 10 09:56 usuarios_part#P#p3.MYD
-rw-rw----  1 mysql mysql   2114560 Feb 10 09:56 usuarios_part#P#p3.MYI
-rw-rw----  1 mysql mysql   4299488 Feb 10 09:56 usuarios_part#P#p4.MYD
-rw-rw----  1 mysql mysql   2114560 Feb 10 09:56 usuarios_part#P#p4.MYI
-rw-rw----  1 mysql mysql   4299812 Feb 10 09:56 usuarios_part#P#p5.MYD
-rw-rw----  1 mysql mysql   2114560 Feb 10 09:56 usuarios_part#P#p5.MYI
-rw-rw----  1 mysql mysql   4299280 Feb 10 09:56 usuarios_part#P#p6.MYD
-rw-rw----  1 mysql mysql   2114560 Feb 10 09:56 usuarios_part#P#p6.MYI
-rw-rw----  1 mysql mysql   4299864 Feb 10 09:56 usuarios_part#P#p7.MYD
-rw-rw----  1 mysql mysql   2114560 Feb 10 09:56 usuarios_part#P#p7.MYI

Podemos apreciar que cada partición tiene su porpio fichero de datos y de índices según este patrón: tabla#P#p<Numero de partición>.MYI (índices)y tabla#P#p<Numero de partición>.MYD (datos)

Procederemos entonces a eliminar dicho índice UNIQUE:

mysql> alter table usuarios_part drop key usuario;
Query OK, 1645134 rows affected (6.99 sec)
Records: 1645134  Duplicates: 0  Warnings: 0

A continuación podremos crear las particiones:

mysql> alter table usuarios_part partition by hash(id) partitions 8;
Query OK, 1645134 rows affected (4.92 sec)
Records: 1645134  Duplicates: 0  Warnings: 0

Podemos a continuación comparar con una tabla sin particionar:

mysql> select * from usuarios where usuario='therealme';
+---------+-----------+----------+
| id      | usuario   | password |
+---------+-----------+----------+
| 1318467 | therealme |          |
+---------+-----------+----------+
1 row in set (0.00 sec)

Y su EXPLAIN:

mysql> explain select * from usuarios where usuario='therealme';
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | usuarios | const | usuario       | usuario | 194     | const |    1 |       |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

Con la tabla particionada en este punto:

mysql> select * from usuarios_part where usuario='therealme';
+---------+-----------+----------+
| id      | usuario   | password |
+---------+-----------+----------+
| 1318467 | therealme |          |
+---------+-----------+----------+
1 row in set (0.36 sec)

Hasta este punto podemos ver como la diferencia de tiempo es considerable. Podemos ver como el EXPLAIN nos indica la falta del índice:

mysql> explain select * from usuarios_part where usuario='therealme';
+----+-------------+---------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table         | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+---------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | usuarios_part | ALL  | NULL          | NULL | NULL    | NULL | 1645134 | Using where |
+----+-------------+---------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

Como no se puede crear un índice global, podemos obtar por incluir el id en el índice:

mysql> alter table usuarios_part add unique (usuario,id);
Query OK, 1645134 rows affected (39.06 sec)
Records: 1645134  Duplicates: 0  Warnings: 0

MySQL permite la utilización de prefijos de los índices existentes, por lo que podemos crear el índice (usuario,id) y utilizarlo como si fuera solo de (usuario). Esto tiene un inconveniente, realmente no estamos creando una restricción de UNIQUE en el campo “usuario”, ya que al agregar el id autoincremental el conjunto siempre será único:

mysql> insert into usuarios_part(usuario) values ('therealme');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from usuarios_part where usuario='therealme';
+---------+-----------+----------+
| id      | usuario   | password |
+---------+-----------+----------+
| 1318467 | therealme |          |
| 1645135 | therealme |          |
+---------+-----------+----------+
2 rows in set (0.42 sec)

Por la cantidad de registros, en la query en tiempo vamos a notar poca diferencia con la versión sin particionar:

mysql> select * from usuarios_part where usuario='therealme';
+---------+-----------+----------+
| id      | usuario   | password |
+---------+-----------+----------+
| 1318467 | therealme |          |
+---------+-----------+----------+
1 row in set (0.00 sec)

Pero en el EXPLAIN podemos notar la diferencia:

mysql> explain select * from usuarios_part where usuario='therealme';
+----+-------------+---------------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table         | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+---------------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | usuarios_part | ref  | usuario       | usuario | 194     | const |    8 | Using where |
+----+-------------+---------------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Debemos fijarnos en el type del EXPLAIN de las dos queries:

  • En el caso de la tabla no particionada el type es const: Por lo que indica que seleccionamos un solo valor, por lo que es el mejor caso (exceptuando system que no aplicaría en este caso)
  • En el caso de la tabla particionada el type es ref: Esto indica que deberá recorrer todo el índice en búsqueda de todos los valores

En el caso de una tabla sin particionar el índice se trata de un solo fichero, pero en el caso de una tabla particionada deberemos recorrer todos los índices de todas las particiones, por lo que no sólo va a ser peor por passar de un const a un ref, sino que no se trata de un índice sino tantos como particiones.

Evidentemente el particionamiento es bueno para algunos casos pero no cada caso debe ser analizado para ver si aplica. Por el contrario, muchas veces parece que la gente se lo toma como: “Con el particonamiento se solucionaran todos los problemas de rendimiento” o “Si la tabla es grande se debe particionar“. Cada cosa tiene su lugar, por lo que se debe planificar bien un cambio de estas características, nunca se debe hacer por hacer.

2 comments to “Índices en tablas particionadas”

  1. Otra solución, sobre a que creo que se tiene más control, es particionar desde el código, y tomando las decisiones desde él, haciendo que la “lógica” que transladas a MySQL sea mínima, teniendo tablas sencillas y pudiendo volver a partir el cluster cuando quieras.

  2. Si se puede hacer desde código mucho mejor, claro. Pero cuando tiene sentido el particionado de tablas es cuando por un lado quieres hacer queries por conjuntos (partición) pero sin perder la possibilidad de hacerlas globales (toda la tabla). Si alguien se plantea hacer particiones pero no quiere hacer queries que afecten a toda la tabla, se debería plantear porque no lo quiere en tablas separadas, por decir algo, tabla_00 .. tabla_NM.

    Utilidades como las del maatkit te reconocen este patrón de tablas, por lo que no te perjudica al analizar logs.

Deja un comentario:

XHTML - Tags permitidos:<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>