mysql - usar - ¿Es mejor crear un índice antes de llenar una tabla con datos o después de que los datos estén en su lugar?

usar indices en consulta sql (5)

Crear un índice después de la inserción de datos es una forma más eficiente (incluso a menudo es recomendable soltar el índice antes de la importación por lotes y después de la importación recrearlo).

Ejemplo Syntetic (PostgreSQL 9.1, máquina de desarrollo lenta, un millón de filas):

CREATE TABLE test1(id serial, x integer); INSERT INTO test1(id, x) SELECT x.id, x.id*100 FROM generate_series(1,1000000) AS x(id); -- Time: 7816.561 ms CREATE INDEX test1_x ON test1 (x); -- Time: 4183.614 ms

Insertar y luego crear índice - alrededor de 12 segundos

CREATE TABLE test2(id serial, x integer); CREATE INDEX test2_x ON test2 (x); -- Time: 2.315 ms INSERT INTO test2(id, x) SELECT x.id, x.id*100 FROM generate_series(1,1000000) AS x(id); -- Time: 25399.460 ms

Crear índice y luego insertar: alrededor de 25.5 segundos (más de dos veces más lento)

Tengo una tabla de aproximadamente 100M filas que voy a copiar para alterar, agregando un índice. No estoy tan preocupado con el tiempo que lleva crear la nueva tabla, pero ¿el índice creado será más eficiente si modifico la tabla antes de insertar cualquier dato o inserte los datos primero y luego agrego el índice?

Esto no importa en este problema porque:

Si agrega datos primero a la tabla y luego agrega un índice. Su tiempo de generación de índice será O(n*log(N)) más largo (donde n es una fila agregada). Debido a que el tiempo de gestación del árbol es O(N*log(N)) entonces si divide esto en datos viejos y datos nuevos, obtiene O((X+n)*log(N)) esto puede simplemente convertirse a O(X*log(N) + n*log(N)) y en este formato simplemente puede ver lo que esperará adicional.
Si agrega un índice y luego pone datos. Cada fila (tiene n nuevas filas) se vuelve más largo inserte el tiempo adicional O(log(N)) necesario para regenerar la estructura del árbol después de agregarle un nuevo elemento (columna de índice de la nueva fila, porque el índice ya existe y la nueva fila agregado, entonces el índice debe regenerarse a la estructura balanceada, este costo O(log(P)) donde P es una potencia de índice [elementos en índice] ). Usted tiene n filas nuevas, entonces finalmente tiene n * O(log(N)) luego O(n*log(N)) resumen de tiempo adicional.

Los índices creados después son mucho más rápidos en la mayoría de los casos. Ejemplo: 20 millones de filas con texto completo en varchar (255) - (Nombre de la empresa) Indice en su lugar mientras se importan las filas - una coincidencia contra tomar hasta 20 segundos en el peor de los casos. Suelte el índice y vuelva a crear: haga coincidir entre tomar menos de 1 segundo cada vez

No estoy seguro de que realmente importe para la eficiencia del índice, ya que en ambos casos está insertando datos nuevos en el índice. El servidor no sabría cuán desequilibrado estaría un índice hasta después de su construcción, básicamente. Speedwise, obviamente, hacer las inserciones sin el índice.

Probablemente sea mejor crear el índice después de agregar las filas. No solo será más rápido, sino que el equilibrio del árbol probablemente sea mejor.

Editar "equilibrar" probablemente no sea la mejor elección de términos aquí. En el caso de un b-tree, está equilibrado por definición. Pero eso no significa que el b-tree tenga el diseño óptimo. La distribución del nodo hijo dentro de los padres puede ser desigual (lo que lleva a un mayor costo en futuras actualizaciones) y la profundidad del árbol puede llegar a ser más profunda de lo necesario si el balanceo no se realiza cuidadosamente durante las actualizaciones. Si el índice se crea después de agregar las filas, es más probable que tenga una mejor distribución. Además, las páginas de índice en el disco pueden tener menos fragmentación una vez que se genera el índice. Un poco más de información aquí