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

Я знаю больше о 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;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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