Что такое хороший способ денормализации базы данных mysql?

Ответ 1

Я знаю больше о mssql о том, что mysql, но я не думаю, что количество объединений или количество строк, о которых вы говорите, должно вызывать слишком много проблем с правильными индексами. Проанализировали ли вы план запроса, не видели ли вы каких-либо?

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

Говоря об этом, как только вы будете удовлетворены своими указателями и исчерпали все другие возможности, нормализация может быть правильным ответом. Если у вас есть один или два запроса, которые являются проблемами, возможно, подходит ручной подход, тогда как какой-то инструмент хранилища данных может быть лучше для создания платформы для разработки кубов данных.

Здесь сайт, который я нашел, затрагивает тему:

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

Вот простой метод, который можно использовать для упрощения денормализации запросов, если вы просто делаете несколько за раз (и я не заменяю ваши таблицы OLTP, а просто создаю новую для целей отчетности). Скажем, у вас есть этот запрос в вашем приложении:

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

Вы можете создать денормализованную таблицу и заполнить почти одним и тем же запросом:

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

Обратите внимание, что подчеркивания соответствуют псевдонимам таблицы, которые вы используете

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

Затем, чтобы исправить ваше приложение, чтобы использовать новую денормализованную таблицу, переключите точки для подчеркивания.

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

Для огромных запросов это может сэкономить много времени и позволяет понять, откуда взялись данные, и вы можете повторно использовать уже имеющиеся запросы.

Помни, я только защищаю это как последнее средство. Готов поспорить с несколькими индексами, которые помогут вам. И когда вы де-нормализуете, не забывайте учитывать дополнительное пространство на ваших дисках и выясните, когда вы запустите запрос, чтобы заполнить новые таблицы. Вероятно, это должно быть ночью или когда активность низкая. И данные в этой таблице, конечно, никогда не будут точно обновлены.

[Еще одно редактирование] Не забывайте, что новые таблицы, которые вы создаете, нужно также индексировать! Хорошая часть состоит в том, что вы можете индексировать ваше сердечное содержимое и не беспокоиться о конкуренции блокировки обновлений, так как помимо вашей объемной вставки таблица будет видеть только избранные.

Ответ 2

В соответствии с некоторыми другими комментариями я бы определенно посмотрел на вашу индексацию.

Одна вещь, которую я обнаружил в начале этого года в наших базах данных MySQL, - это мощь составных индексов. Например, если вы сообщаете о номерах заказов по диапазонам дат, может помочь составной индекс в столбце заказа и столбцах даты заказа. Я считаю, что MySQL может использовать только один индекс для запроса, поэтому, если бы у вас были только отдельные индексы для номера заказа и даты заказа, ему пришлось бы решить только один из них. Использование команды EXPLAIN может помочь в этом.

Чтобы дать представление о производительности с хорошими индексами (включая многочисленные составные индексы), я могу запускать запросы, соединяющие 3 таблицы в нашей базе данных и получающие почти мгновенные результаты в большинстве случаев. Для более сложных отчетов большинство запросов выполняются менее чем за 10 секунд. Эти 3 таблицы имеют 33 миллиона, 110 миллионов и 140 миллионов строк соответственно. Обратите внимание, что мы также уже немного их нормализовали, чтобы ускорить наш самый распространенный запрос в базе данных.

Дополнительная информация о ваших таблицах и типах запросов к отчетности может дать дополнительные рекомендации.

Ответ 3

Я знаю, что это немного тангенциально, но вы пробовали посмотреть, есть ли больше индексов, которые вы можете добавить?

У меня нет большого фона БД, но я недавно работаю с базами данных, и я обнаружил, что многие запросы можно улучшить, просто добавив индексы.

Мы используем DB2, и есть команда под названием db2expln и db2advis, первая будет указывать, используются ли таблицы для сканирования индексов, а вторая рекомендует индексы, которые вы можете добавить для повышения производительности. Я уверен, что MySQL имеет похожие инструменты...

В любом случае, если это то, о чем вы еще не подумали, оно многое помогало мне... но если вы уже пошли по этому маршруту, я думаю, это не то, что вы ищете.

Другая возможность - это "материализованное представление" (или, как их называют, в DB2), которое позволяет указать таблицу, которая по существу построена из нескольких таблиц. Таким образом, вместо того, чтобы нормализовать фактические столбцы, вы можете предоставить этому представлению доступ к данным... но я не знаю, оказывает ли это серьезное влияние на производительность на вставки/обновления/удаления (но если оно "материализовано", то оно должен помочь с выборами, поскольку значения физически хранятся отдельно).

Ответ 4

MySQL 5 поддерживает представления, что может быть полезно в этом сценарии. Похоже, что вы уже много оптимизировали, но если вы не можете использовать MySQL EXPLAIN, чтобы увидеть, какие индексы на самом деле и что замедляет ваши запросы.

Что касается нормализации данных (независимо от того, используете ли вы представления или просто дублируете данные более эффективным образом), я думаю, что начинать с самых медленных запросов и прокладывать себе путь - это хороший подход.

Ответ 5

Для MySQL мне нравится этот разговор: Real World Web: производительность и масштабируемость, версия MySQL. Это содержит много разных советов для получения большей скорости из MySQL.

Ответ 6

Вы также можете захотеть выбрать во временную таблицу и затем выполнить запросы на эту временную таблицу. Это позволит избежать необходимости воссоединиться с вашими таблицами для каждого отдельного запроса (при условии, что вы можете использовать временную таблицу для многочисленных запросов, конечно). Это в основном дает денормализованные данные, но если вы выполняете только избранные вызовы, нет никакой обеспокоенности в отношении согласованности данных.

Ответ 7

В дополнение к моему предыдущему ответу, другой подход, который мы приняли в некоторых ситуациях, заключается в хранении ключевых данных отчетности в отдельных сводных таблицах. Есть определенные запросы отчетности, которые будут только замедляться даже после денормализации и оптимизации, и мы обнаружили, что создание таблицы и сохранение текущих итогов или итоговой информации в течение месяца по мере того, как оно входило, делало отчетность конца месяца намного быстрее.

Мы обнаружили, что этот подход легко реализовать, поскольку он не сломал ничего, что уже работало, - это просто дополнительные вставки базы данных в определенные моменты.

Ответ 8

Я играл с составными индексами и видел некоторые реальные преимущества... возможно, я настрою некоторые тесты, чтобы увидеть, может ли это спасти меня здесь... хотя бы немного дольше.