optimizar - tipos de indices en mysql

Rendimiento de MySQL: tablas múltiples vs. índice en una sola tabla y particiones

Question

Me pregunto qué es más eficiente y más rápido en rendimiento:
Tener un índice en una tabla grande o varias tablas más pequeñas sin índices?

Dado que este es un problema bastante abstracto, permítanme hacerlo más práctico:
Tengo una tabla con estadísticas sobre usuarios (20,000 usuarios y aproximadamente 30 millones de filas en general). La tabla tiene alrededor de 10 columnas que incluyen user_id , actions , timestamps , etc.
Las aplicaciones más comunes son: Insertar datos por user_id y recuperar datos por ID_usuario (las SELECT nunca incluyen múltiples user_id''s ).

Ahora hasta ahora tengo un INDEX en el user_id y la consulta se ve algo así como

SELECT * FROM statistics WHERE user_id = 1

Ahora, con más y más filas, la mesa se vuelve más y más lenta. INSERT ralentizan porque el INDEX hace cada vez más grande; SELECT ralentizan, bueno, porque hay más filas para buscar.

Ahora me preguntaba por qué no tener una tabla de estadísticas para cada usuario y cambiar la sintaxis de la consulta por algo como esto:

SELECT * FROM statistics_1

donde 1 representa el user_id obviamente.
De esta forma, no se necesita ningún INDEX y hay mucha menos información en cada tabla, por lo que las INSERT y SELECT deberían ser mucho más rápidas.

Ahora mis preguntas de nuevo:
¿Hay desventajas en el mundo real para manejar tantas tablas (en mi caso 20,000) en lugar de usar una tabla con un INDEX ?
¿Mi enfoque realmente aceleraría las cosas o la búsqueda de la mesa eventualmente ralentizaría las cosas más que todo?

Answer 1

Crear 20,000 tablas es una mala idea. Necesitarás 40,000 tablas en poco tiempo, y luego más.

Llamé a este síndrome Metadata Tribbles en mi libro SQL Antipatterns . Verá que esto sucede cada vez que planea crear una "tabla por X" o una "columna por X".

Esto causa problemas reales de rendimiento cuando tienes decenas de miles de tablas. Cada tabla requiere que MySQL mantenga estructuras internas de datos, descriptores de archivos, un diccionario de datos, etc.

También hay consecuencias operativas prácticas. ¿Realmente desea crear un sistema que requiera la creación de una nueva tabla cada vez que se registre un nuevo usuario?

En cambio, te recomendaría que uses MySQL Partitioning .

Aquí hay un ejemplo de partición de la tabla:

CREATE TABLE statistics (
  id INT AUTO_INCREMENT NOT NULL,
  user_id INT NOT NULL,
  PRIMARY KEY (id, user_id)
) PARTITION BY HASH(user_id) PARTITIONS 101;

Esto le da la ventaja de definir una tabla lógica, a la vez que divide la tabla en muchas tablas físicas para un acceso más rápido cuando consulta un valor específico de la clave de partición.

Por ejemplo, cuando ejecuta una consulta como su ejemplo, MySQL accede solo a la partición correcta que contiene el user_id específico:

mysql> EXPLAIN PARTITIONS SELECT * FROM statistics WHERE user_id = 1/G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: statistics
   partitions: p1    <--- this shows it touches only one partition 
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 2
        Extra: Using where; Using index

El método de partición HASH significa que las filas se colocan en una partición por un módulo de la clave de partición entera. Esto significa que el mapa de muchos user_id a la misma partición, pero cada partición tendría solo 1 / Nth tantas filas en promedio (donde N es el número de particiones). Y define la tabla con un número constante de particiones, por lo que no tiene que expandirla cada vez que obtiene un nuevo usuario.

Puede elegir cualquier cantidad de particiones hasta 1024 (u 8192 en MySQL 5.6), pero algunas personas han informado problemas de rendimiento cuando alcanzan ese nivel.

Se recomienda utilizar un número primo de particiones. En caso de que los valores de su user_id sigan un patrón (como el uso solo de números pares), usar un número primo de particiones ayuda a distribuir los datos de manera más pareja.

Re sus preguntas en comentario:

¿Cómo podría determinar una cantidad razonable de particiones?

Para la partición HASH, si usa 101 particiones como las que muestro en el ejemplo anterior, entonces cualquier partición dada tiene aproximadamente el 1% de sus filas en promedio. Dijiste que tu tabla de estadísticas tiene 30 millones de filas, por lo que si utilizas esta partición, tendrías solo 300k filas por partición. Eso es mucho más fácil de leer para MySQL. Puede (y debe) usar también índices: cada partición tendrá su propio índice, y será solo el 1% del tamaño de todo el índice en toda la tabla sin particiones.

Entonces, la respuesta a cómo se puede determinar una cantidad razonable de particiones es: ¿qué tan grande es toda la tabla y qué tan grande quieres que sean las particiones en promedio?

¿No debería la cantidad de particiones crecer con el tiempo? Si es así: ¿cómo puedo automatizar eso?

La cantidad de particiones no necesariamente tiene que crecer si usa la partición HASH. Eventualmente puede tener 30 mil millones de filas en total, pero he encontrado que cuando el volumen de datos crece en órdenes de magnitud, eso exige una nueva arquitectura de todos modos. Si sus datos crecen tanto, es probable que necesite fragmentar en varios servidores y particionar en varias tablas.

Dicho esto, puede volver a particionar una tabla con ALTER TABLE:

ALTER TABLE statistics PARTITION BY HASH(user_id) PARTITIONS 401;

Esto tiene que reestructurar la tabla (como la mayoría de los cambios de ALTER TABLE), así que espere que tome un tiempo.

Es posible que desee controlar el tamaño de los datos y los índices en las particiones:

SELECT table_schema, table_name, table_rows, data_length, index_length
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE partition_method IS NOT NULL;

Al igual que con cualquier tabla, desea que el tamaño total de los índices activos quepa en su grupo de búferes, porque si MySQL tiene que intercambiar partes de los índices dentro y fuera del grupo de búferes durante las consultas SELECCIONAR, el rendimiento se resiente.

Si usa particiones RANGE o LIST, entonces agregar, soltar, fusionar y dividir particiones es mucho más común. Ver http://dev.mysql.com/doc/refman/5.6/en/partitioning-management-range-list.html

Los animo a que lean la sección del manual sobre particionamiento , y también miren esta agradable presentación: Boost Performance With MySQL 5.1 Partitions .

Answer 2

Hay poco que agregar a la respuesta de Bill Karwins. Pero una sugerencia es: compruebe si se necesitan todos los datos para el usuario con todo detalle durante todo el tiempo.

Si desea dar estadísticas de uso o número de visitas o esas cosas, generalmente no obtendrá una granularidad de acciones únicas y segundos para, por ejemplo, el año 2009 desde la vista de hoy. Por lo tanto, podría compilar tablas de agregación y una tabla de archivo (no el archivo del motor, por supuesto) para tener los datos recientes sobre la base de acciones y una descripción general de las acciones anteriores.

Las viejas acciones no cambian, creo.

Y todavía puede entrar en detalles de la agregación con un week_id en la tabla de archivo, por ejemplo.

Answer 3

Para pasar de 1 tabla a 1 tabla por usuario, puede usar la partición para alcanzar una cierta cantidad de tablas / tamaño de tabla en algún punto intermedio.

También puede mantener las estadísticas de los usuarios para tratar de mover a los usuarios "activos" en 1 tabla para reducir la cantidad de tablas a las que debe acceder a lo largo del tiempo.

En resumidas cuentas, hay mucho que puede hacer, pero en gran medida tiene que crear prototipos y pruebas y simplemente evaluar los impactos en el rendimiento de varios cambios que está realizando.

Answer 4

Probablemente dependa del tipo de consultas que planee realizar con frecuencia, y la mejor manera de saberlo con certeza es simplemente implementar un prototipo de ambas y realizar algunas pruebas de rendimiento.

Dicho esto, esperaría que una sola tabla (grande) con un índice lo haga mejor en general porque la mayoría de los sistemas DBMS están muy optimizados para lidiar con la situación exacta de encontrar e insertar datos en tablas grandes. Si tratas de hacer muchas tablas pequeñas con la esperanza de mejorar el rendimiento, eres como pelear contra el optimizador (que generalmente es mejor).

Además, tenga en cuenta que una mesa es probablemente más práctica para el futuro. ¿Qué sucede si quiere obtener estadísticas agregadas sobre todos los usuarios? Tener 20 000 tablas haría esto muy difícil e ineficiente de ejecutar. Vale la pena considerar la flexibilidad de estos esquemas también. Si divides tus mesas de esa manera, es posible que estés diseñando en una esquina para el futuro.