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

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

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

Есть идеи о подходе? Должен ли я начать с пары моих худших запросов и продолжить?

15.08.2008 23:36:30
8 ОТВЕТОВ
РЕШЕНИЕ

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

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

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

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

11
16.08.2008 01:55:57

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

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

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

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

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

1
15.08.2008 23:58:13

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

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

2
16.08.2008 00:04:20

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

0
16.08.2008 00:20:37

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

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

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

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

1
16.08.2008 02:41:02

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

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

0
16.08.2008 02:53:00

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

0
17.08.2008 16:52:00

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

1
18.08.2008 15:03:36