Блог

Главная - Вопросы по программированию - Выполнение запроса MySQL занимает много времени

Выполнение запроса MySQL занимает много времени

#mysql #sql #database #query-optimization #inner-join

#mysql #sql #База данных #оптимизация запроса #внутреннее объединение

Вопрос:

Я написал запрос, который объединяет три разные таблицы. Приблизительные описания таблиц приведены ниже:

 Table 1: account_data
columns: id(pk), property_id(fk), account_id(fk), value, status
No Of Data: around 30 milions

Table 2: account
columns: id(pk), service_id(fk), status
No Of Data: around 5 milions

Table 3: property
columns: id(pk), create_analytics(index), status, uri
No Of Data: few hundreds

Запрос выглядит следующим образом:

 SELECT ad.value, p.uri, count(ad.id)
FROM account_data ad 
     INNER JOIN property p ON ad.property_id = p.id AND (p.status = 1) 
     INNER JOIN account ac ON ad.account_id = ac.id AND (ac.status = 1) 
     WHERE (p.create_analytics = '1' AND ac.service_id = ?) AND (ad.status = 1) 
GROUP BY ad.property_id, ad.value

В начале запрос занимал слишком много времени. после создания индекса в столбце ‘create_analytics’ он заметно снизился.

Но все равно запрос выполняется слишком долго (более 3 минут). Я попытался создать индекс для группы по столбцам (property_id и value), создав индексы для столбцов ‘status’, но ни один из них не показал улучшения.

Просто интересно, есть ли какой-либо другой способ переписать этот запрос, чтобы сделать его быстрее? Или я что-то пропустил, где могло бы помочь создание индекса, порядок изменения?

С нетерпением ждем всех ваших мыслей / предложений по решению этой проблемы. Заранее спасибо.

Комментарии:

1. Попробуйте использовать explain, чтобы выяснить, где отсутствует индекс.

Ответ №1:

Кроме того, если ваш текущий create_analytics в настоящее время является VARCHAR , попробуйте изменить его на CHAR или, что еще лучше, если у вас ограниченное количество строк в этом поле, измените его на ENUM .

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

Убедитесь, что все ваши соединения одного типа, я предполагаю, что ваши идентификаторы — INT, убедитесь, что вы где-то не перепутали BIGINTs.

Наконец, попробуйте переместить creating_analytics в часть JOIN, чтобы вы предоставили меньший результирующий набор для предложения WHERE.

Комментарии:

1. На самом деле большинство pks являются BIGINT , только за исключением ‘property’ , который имеет тип INT. Должно ли это быть большой проблемой?

2. Если property.id является INT, также сделайте FK property_id значением INT.

3. Теперь, когда я рассматриваю идею Strawberry … вам обязательно нужны внешние ключи? Потому что, если вы этого не сделаете, вы можете изменить хранилище на MyISAM и разделить таблицы по их текущему FK.

4. На самом деле мне придется сохранить внешние ключи, потому что я использую ORM, который требует этого для поддержания отношений. Таким образом, необходимо поддерживать в innodb с помощью fk по умолчанию. Я попробую настроить соединения, давайте посмотрим. Спасибо за ваши ценные предложения!

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

Ответ №2:

Удалите имеющиеся у вас индексы (кроме PKS) и вместо этого попробуйте добавить (составные) индексы к следующему: 

 account_data (property_id,status)

property (status,create_analytics)

account (status,service_id)

Комментарии:

1. Спасибо. Я попытался воспользоваться вашим предложением, и, похоже, они улучшили производительность на 10-15 секунд. Просто интересно, почему вы не упомянули о столбце ‘value’. Есть ли какая-то конкретная причина, по которой я не должен включать это в индекс?

2. Он может добавить составные индексы, верно, но он не должен удалять FKS. Вы не сможете обеспечить структурную целостность без них, а status не имеет одинакового значения для разных таблиц, чтобы использовать составной FK.

3. @Rana value не имеет отношения ни к какой другой таблице и не используется ни в каком анализе диапазона, поэтому я не вижу смысла в его индексации (но, эй, я не эксперт, когда дело доходит до оптимизации)

4. Я думал, что это часть group by, поэтому может быть полезно. Но и с этим особых улучшений не произошло.

Ответ №3:

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

Из http://www.fromdual.com/mysql-materialized-views;

Обновление материализованных представлений

Материализованные представления могут обновляться разными видами. Они могут быть обновлены:

  1. никогда (только один раз в начале, только для статических данных)
  2. по требованию (например, один раз в день, например, после ночной загрузки)
  3. немедленно (после каждого оператора)

Обновление может быть выполнено следующими способами:

  1. полностью (медленно, заполняется с нуля)
  2. отложено (быстро, с помощью таблицы журналов)

Путем сохранения информации об изменениях в таблице журнала. Также могут быть созданы некоторые моментальные снимки или состояния с задержкой по времени:

  1. обновить до актуальности
  2. обновить полностью