una tercera sencillos programa primera paso para normalizar normalizacion normales normal formas forma ejemplos desnormalizacion datos como mysql database denormalization

mysql - tercera - programa para normalizar base de datos



¿Cuál es una buena forma de desnormalizar una base de datos mysql? (8)

Tengo una gran base de datos de pedidos normalizados que se está volviendo muy lenta para consultar los informes. Muchas de las consultas que uso en los informes se unen a cinco o seis tablas y se deben examinar decenas o cientos de miles de líneas.

Hay muchas consultas y la mayoría se han optimizado tanto como sea posible para reducir la carga del servidor y aumentar la velocidad. Creo que es hora de comenzar a guardar una copia de los datos en un formato desnormalizado.

Alguna idea sobre un enfoque? ¿Debería comenzar con algunas de mis peores consultas e ir desde allí?

Sé que esto es un poco tangencial, pero ¿has intentado ver si hay más índices que puedas agregar?

No tengo mucha experiencia en DB, pero últimamente estoy trabajando con bases de datos, y he descubierto que muchas de las consultas se pueden mejorar simplemente agregando índices.

Estamos utilizando DB2, y hay un comando llamado db2expln y db2advis, el primero indicará si se están utilizando exploraciones de tabla frente a escaneos de índice, y el segundo recomendará índices que puede agregar para mejorar el rendimiento. Estoy seguro de que MySQL tiene herramientas similares ...

De todos modos, si esto es algo que no has considerado todavía, me ha estado ayudando mucho ... pero si ya te has ido por esta ruta, entonces supongo que no es lo que estás buscando.

Otra posibilidad es una "vista materializada" (o como lo llaman en DB2), que le permite especificar una tabla que se compone esencialmente de partes de varias tablas. Por lo tanto, en lugar de normalizar las columnas reales, podría proporcionar esta vista para acceder a los datos ... pero no sé si esto tiene un impacto severo en el rendimiento de las inserciones / actualizaciones / eliminaciones (pero si se "materializa", entonces debería ayudar con selecciones ya que los valores se almacenan físicamente por separado).

Además de mi respuesta anterior, otro enfoque que hemos tomado en algunas situaciones es almacenar los datos clave de informes en tablas de resumen separadas. Hay ciertas consultas de informes que van a ser lentas incluso después de la desnormalización y optimizaciones, y descubrimos que la creación de una tabla y el almacenamiento de los totales acumulados o la información resumida a lo largo del mes a medida que llegaba también hacía que el informe finalizara mucho más rápido.

Encontramos que este enfoque es fácil de implementar, ya que no rompió nada de lo que ya estaba funcionando; solo se trata de inserciones de bases de datos adicionales en ciertos puntos.

Conozco más sobre mssql que mysql, pero no creo que el número de uniones o el número de filas de las que está hablando deba causar demasiados problemas con los índices correctos en su lugar. ¿Has analizado el plan de consulta para ver si te falta alguno?

http://dev.mysql.com/doc/refman/5.0/en/explain.html

Dicho esto, una vez que esté satisfecho con sus índices y haya agotado todos los demás medios, la des-normalización podría ser la respuesta correcta. Si solo tiene una o dos consultas que son problemas, probablemente sea apropiado un enfoque manual, mientras que algún tipo de herramienta de almacenamiento de datos podría ser mejor para crear una plataforma para desarrollar cubos de datos.

Aquí hay un sitio que encontré que toca el tema:

http://www.meansandends.com/mysql-data-warehouse/?link_body%2Fbody=%7Bincl%3AAggregation%7D

Aquí hay una técnica simple que puede usar para simplificar las consultas de desnormalización, si solo está haciendo algunas a la vez (y no reemplazaré sus tablas OLTP, simplemente crearé una nueva para los informes). Supongamos que tiene esta consulta en su aplicación:

select a.name, b.address from tbla a join tblb b on b.fk_a_id = a.id where a.id=1

Podrías crear una tabla desnormalizada y poblar con casi la misma consulta:

create table tbl_ab (a_id, a_name, b_address); -- (types elided)

Observe que los guiones bajos coinciden con los alias de tabla que usa

insert tbl_ab select a.id, a.name, b.address from tbla a join tblb b on b.fk_a_id = a.id -- no where clause because you want everything

Luego, para arreglar su aplicación y usar la nueva tabla desnormalizada, cambie los puntos por guiones bajos.

select a_name as name, b_address as address from tbl_ab where a_id = 1;

Para grandes consultas, esto puede ahorrar mucho tiempo y deja en claro de dónde provienen los datos, y puede volver a utilizar las consultas que ya tiene.

Recuerda, solo defiendo esto como el último recurso. Apuesto a que hay algunos índices que te pueden ayudar. Y cuando se desincronice, no se olvide de tener en cuenta el espacio adicional en sus discos, y descubra cuándo ejecutará la consulta para rellenar las nuevas tablas. Esto probablemente debería ser por la noche o cuando la actividad sea baja. Y los datos en esa tabla, por supuesto, nunca estarán exactamente actualizados.

[Sin embargo, otra edición] ¡No olvide que las nuevas tablas que crea deben ser indexadas también! Lo bueno es que puede indexar al contenido de su corazón y no preocuparse por la contención del bloqueo de actualización, ya que, aparte de su inserción masiva, la tabla solo verá selecciones.

En línea con algunos de los otros comentarios, sin duda echaré un vistazo a su indexación.

Una cosa que descubrí a principios de este año en nuestras bases de datos MySQL fue el poder de los índices compuestos. Por ejemplo, si está informando los números de orden sobre los rangos de fechas, un índice compuesto en las columnas de número de pedido y fecha de pedido podría ayudar. Creo que MySQL solo puede usar un índice para la consulta, por lo que si solo tuviera índices separados sobre el número de orden y la fecha del pedido, tendría que decidir usar solo uno de ellos. Usar el comando EXPLAIN puede ayudar a determinar esto.

Para dar una indicación del rendimiento con buenos índices (incluidos numerosos índices compuestos), puedo ejecutar consultas uniendo 3 tablas en nuestra base de datos y obtener resultados casi instantáneos en la mayoría de los casos. Para informes más complejos, la mayoría de las consultas se ejecutan en menos de 10 segundos. Estas 3 tablas tienen 33 millones, 110 millones y 140 millones de filas respectivamente. Tenga en cuenta que también hemos normalizado esto ligeramente para acelerar nuestra consulta más común en la base de datos.

Para obtener más sugerencias, puede obtener más información sobre sus tablas y los tipos de consultas de informes.

MySQL 5 admite vistas , lo que puede ser útil en este escenario. Parece que ya ha hecho muchas optimizaciones, pero si no, puede utilizar la sintaxis EXPLAIN de MySQL para ver qué índices se están usando realmente y qué está ralentizando sus consultas.

En lo que respecta a la normalización de datos (ya sea que esté usando vistas o simplemente duplicando datos de una manera más eficiente), creo que comenzar con las consultas más lentas y seguir trabajando es un buen enfoque a seguir.

También puede considerar seleccionar en una tabla temporal y luego realizar consultas en esa tabla temporal. Esto evitaría la necesidad de volver a unirse a sus tablas para cada consulta que emita (suponiendo que puede usar la tabla temporal para numerosas consultas, por supuesto). Básicamente, esto le proporciona datos desnormalizados, pero si solo está haciendo llamadas selectas, no existe preocupación por la consistencia de los datos.

He estado jugando con índices compuestos y he visto algunos beneficios reales ... tal vez voy a configurar algunas pruebas para ver si eso me puede salvar aquí ... al menos por un poco más de tiempo.