Оптимизация SQL запросов: списки с meta-данными. Sql оптимизация запроса с большим количеством or

Оптимизация SQL запросов: списки с meta-данными

Оптимизация количества и качества SQL запросов к базе данных весьма актуальна, особенно если их много, а возможности сервера ограничены. Как таковой, здесь лучше стремиться к следующему:

Использовать наиболее простые запросы SQL. Т.е. без всяких подзапросов, с наименьшим числом объединений данных таблиц и условий, ну и т.п.
По возможности, объединять данные в одну таблицу, дабы уменьшить число SQL запросов к БД.
В результате SQL запроса выводить данные только тех полей, которые вам нужны.

И всем в том же духе. С одной стороны мы не используем возможности того же MySQL на полную катушку, а с другой щадим наш сервер. Однако, это все конечно звучит красиво, но иногда приходится решать сложные задачи, с множеством SQL запросов. Например, когда нам необходимо вывести список с так называемыми (мной) «meta-данными», т.е. данными из других таблиц. Это может быть: название рубрики, региона, тегов и т.п.

Чтобы было более-менее понятно, о чем я говорю, давайте рассмотрим небольшой пример. И так, у нас есть таблица db_items с данными записей, которые мы будем выводить в виде списка. К каждой записи необходимо присоединять соответствующие «meta-данные»: рубрику (db_cats) и несколько тегов (db_tags, db_tags_links). Здесь стоит отметить, что я буду рассматривать сразу два варианта подключения «meta-данных»:

Первый вариант будет продемонстрирован на примере «рубрик», когда в db_items есть поле с идентификатором рубрики (cat_id).
Второй вариант будет продемонстрирован на примере «тегов», когда к каждой из строк в db_items соответствует несколько тегов из db_tags. Для этого будет использоваться вспомогательная таблица связей db_tags_links состоящая из полей идентификаторов записи (item_id) и тега (tag_id).

Концепция решения

Но перед тем как приступить к практике, давайте рассмотрим саму концепцию предлагаемого решения. Как таковой, она сводится к использованию дополнительного SQL запроса к db_items с выборкой необходимых идентификаторов записей (item_id). Это позволит сделать разовые выборки необходимых тех же тегов с помещением их в массив, ключами которого будут идентификаторы записей (item_id), а значениями — массивы уже сгенерированных ссылок тегов, что и позволит избежать дополнительных SQL запросов по выборке соответствующих тегов каждой из записи. Дабы нить повествования не обрывалась, начнем со сложного, т.е. с тегов и рассмотрим соответствующий пример.

page 1 page 2

kurilka.co.ua

sql - как оптимизировать sql-запрос для ускорения работы с большими данными?

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

CREATE INDEX Tags_IX1 ON Tags (state, name)

Мы ожидаем, что вывод EXPLAIN для вашего запроса будет показывать этот индекс, используя "Использование индекса" в столбце "Дополнительно" и избегая дорогостоящей операции "Использование файлового управления".

Поскольку в предложении WHERE существует предикат равенства в state, а затем группа по операции в столбце name, MySQL может удовлетворить запрос из индекса без необходимости выполнять операцию сортировки и без какого-либо поиска на страницах в базовую таблицу.

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

Если бы мы создали такой индекс:

... ON Tags (name,state)

с name в качестве ведущего столбца, тогда мы могли бы повторно написать запрос, чтобы более эффективно использовать этот индекс:

SELECT t.name , SUM(IF(t.state='1',t.name IS NOT NULL,NULL) AS count FROM Tags t GROUP BY t.name ORDER BY count DESC LIMIT 7

РЕДАКТИРОВАТЬ

Другие ответы здесь рекомендуют добавить индекс в столбец state. Похоже, что state может иметь низкую мощность. То есть, есть только несколько значений этого столбца, и большой процент строк будет иметь значение '1'. В этом случае индекс только для state вряд ли даст лучшую производительность. Это потому, что использование этого индекса (если MySQL даже использует его) потребует поиска на базовые страницы данных для извлечения столбца name, а затем все строки должны быть отсортированы для удовлетворения GROUP BY.

Используйте EXPLAIN, Люк.

Ссылка: 8.8.1 Оптимизация запросов с помощью EXPLAIN https://dev.mysql.com/doc/refman/5.6/en/using-explain.html

СЛЕДОВАТЬ ЗА

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

Итак, вот тест.

SQL Fiddle Here: http://sqlfiddle.com/#!9/20e73/2

(Будьте терпеливы открыть ссылку SQL Fiddle... она заполняет таблицу строк million+, строит четыре индекса и запускает пятнадцать запросов, поэтому она вращается в течение дюжины секунд.)

Ниже приведены результаты работы MySQL 5.6 на моем локальном компьютере:

run no index (state,name) (name,state) (state) (name) ---- ----------- ------------ ------------ ----------- ----------- run1 2.410 sec 0.687 sec 1.076 sec 3.374 sec 3.924 sec run2 2.433 sec 0.659 sec 1.074 sec 3.267 sec 3.958 sec run3 2.851 sec 0.717 sec 1.024 sec 3.423 sec 4.222 sec

Самый быстрый был индекс с несколькими столбцами (state,name)
Вторым самым быстрым был индекс с несколькими столбцами (name,state)

Третьим самым быстрым было полное сканирование таблицы
Четвертый, медленнее, чем сканирование таблицы, является индексом (state)
И на последнем месте индекс на столбце just (name)

В результате выполнения SQL Fiddle результаты были схожи:

none (s,n) (n,s) (n) (s) ---- ------ ------ ------ ------ ------ run1 701ms 193ms 286ms 1462ms 959ms run2 707ms 191ms 282ms 1170ms 957ms run3 702ms 190ms 283ms 1157ms 914ms

Результаты тестов показывают, что победитель является многоколоночным индексом (state,name).

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

qaru.site

performance - Оптимизация SQL-запроса DISTINCT с условиями OR

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

Ваш запрос эквивалентен:

SELECT DISTINCT business_key FROM Memory WHERE NOT (concept = 'case' AND attrib = 'status' AND value = 'closed')

и к этому (что, вероятно, даст тот же план выполнения):

SELECT business_key FROM Memory WHERE NOT (concept = 'case' AND attrib = 'status' AND value = 'closed') GROUP BY business_key

Поскольку 4 столбца, которые должны быть помещены в индекс, все VARCHAR(255), длина индекса будет довольно большой. MyISAM не позволит более 1000 байт и InnoDB не более 3072.

Одним из решений является сокращение длины последней части, при этом длина индекса меньше 1000: 255+255+255+230 = 995:

(concept, attrib, value, business_key(220))

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

Другим вариантом является уменьшение длины всех или некоторых из этих четырех столбцов, если это соответствует данным, которые вы ожидаете сохранить там. Нет необходимости объявлять длину 255 если вы ожидаете, что в столбце должно быть не более 100.

Другой вариант, который вы можете рассмотреть, заключается в том, чтобы поместить эти 4 столбца в 4 отдельные таблицы ссылок. (Или просто столбцы с повторными данными. Кажется, что business_key будет иметь повторяющиеся данные, но не так много. Таким образом, будет не очень полезно создать справочную таблицу для этого столбца.)

Пример: Поместите значения concept в новую таблицу с чем-то вроде:

CREATE TABLE Concept_Ref ( concept_id INT AUTO_INCREMENT , concept VARCHAR(255) , PRIMARY KEY concept_id , UNIQUE INDEX concept_idx (concept) ) ; INSERT INTO Concept_Ref ( concept ) SELECT DISTINCT concept FROM Memory ;

а затем измените таблицу Memory:

ALTER TABLE Memory ADD COLUMN concept_id INT ;

сделайте это (один раз):

UPDATE Memory m JOIN Concept_Ref c ON c.concept = m.concept SET m.concept_id = c.concept_id

а затем отбросить столбец Memory.concept:

ALTER TABLE Memory DROP COLUMN concept ;

Вы также можете добавить ссылки FOREIGN KEY если вы меняете свои таблицы из MyISAM в InnoDB.

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

Конечно, для запроса потребуется 4 JOINs. И любые INSERT, UPDATE или DELETE для этой таблицы должны быть изменены и тщательно разработаны.

Но в целом, я думаю, у вас будет более высокая производительность. С дизайном, который у вас есть сейчас, кажется, что значения типа 'case', 'status' и 'closed' повторяются много раз.

qaru.site

Оптимизация запроса при работе с большими таблицами

Вопрос: Оптимизация запроса.

Привет. Нужна помощь в оптимизации запрос.

Запрос:

SELECT creation_date, grouped_type, user_id, max(creation_time),array_agg(id), array_agg(table_owner_id), array_agg(user_id), array_agg(app_id), array_agg(creation_time), array_agg(type), array_agg(subtype), array_agg(looked), array_agg(parameters) FROM table WHERE table_owner_id='owner' GROUP BY 1,2,3 ORDER BY 1 DESC, 4 DESCLIMIT 5 OFFSET 0;

Индексы:"table_pkey" PRIMARY KEY, btree (id)"table_date_asc_grouped_type_user_id" btree (creation_date, grouped_type, user_id)"table_date_desc_grouped_type_user_id" btree (creation_date DESC, grouped_type, user_id)"table_feed_owner_idx" btree (table_owner_id)

В таком виде индексы не используются (об этом говорит explain analyze) и запрос работает достаточно медленно (таблица 300к записей, время 2-4 сек).Если убрать max(creation_time) и ORDER BY 4 DESC - в этом случае работает индексы table_date_asc_grouped_type_user_id & table_date_desc_grouped_type_user_id (время выполнения - меньше 100 мс.), но результат группировки конечно же "ломается".

Возможно ли каким то образом оптимизировать запрос (может добавить какие то вспомогательные колонки или еще что) с сохранением функционала?

Ответ: index (table_owner_id,creation_date,creation_time)

+ идея ивана, в редакции примерно такой {псевдокод}:

WITH s5 AS ( SELECT {group list} FROM t WHERE table_owner_id = 'owner'::text ORDER BY creation_date DESC,creation_time DESC LIMIT 5 ) SELECT s5.{group list},s5.creation_time ,L.{aggregate_list} FROM s5

forundex.ru

Оптимизация запроса с большим объемом данных

Вопрос: Проблемы с оптимизацией запроса

Добрый день, разбираюсь почему запрос долго выбирает данные (4 сек), столкнулся со странной проблемойсам запрос SELECT top 40 [tbl_Problem].[StatusID] AS [StatusID], [tbl_ProblemStatus].[Name] AS [StatusName], [tbl_ProblemStatus].[IsFinish] AS [IsFinish], [tbl_Problem].[AuthorID] AS [AuthorID], [Author].[Name] AS [AuthorName], [tbl_Problem].[CIServiceID] AS [CIServiceID], [tbl_CIService].[Name] AS [CIServiceName], [tbl_Problem].[ConfigurationItemID] AS [ConfigurationItemID], [tbl_ConfigurationItem].[Name] AS [ConfigurationItemName], [tbl_Problem].[AffectsCIID] AS [AffectsCIID], [AffectsCI].[Name] AS [AffectsCIName], [tbl_Problem].[Solution] AS [Solution], [tbl_Problem].[PriorityID] AS [PriorityID], [tbl_ProblemPriority].[Name] AS [PriorityName], [tbl_Problem].[UrgencyID] AS [UrgencyID], [tbl_ProblemUrgency].[Name] AS [UrgencyName], [tbl_Problem].[OwnerID] AS [OwnerID], [Owner].[Name] AS [OwnerName], [tbl_Problem].[ConciseDescription] AS [ConciseDescription], [tbl_Problem].[RegistrationDate] AS [RegistrationDate], [tbl_Problem].[Number] AS [Number], [tbl_Problem].[ProblemTypeID] AS [ProblemTypeID], [tbl_ProblemType].[Name] AS [ProblemTypeName], [tbl_Problem].[FullDescription] AS [FullDescription], [tbl_Problem].[ImpactID] AS [ImpactID], [tbl_ProblemImpact].[Name] AS [ImpactName], [tbl_Problem].[ImpactDegree] AS [ImpactDegree], [tbl_Problem].[ID] AS [ID] FROM [dbo].[tbl_Problem] AS [tbl_Problem] LEFT OUTER JOIN [dbo].[tbl_ConfigurationItem] AS [tbl_ConfigurationItem] ON [tbl_ConfigurationItem].[ID] = [tbl_Problem].[ConfigurationItemID] LEFT OUTER JOIN [dbo].[tbl_ConfigurationItem] AS [AffectsCI] ON [AffectsCI].[ID] = [tbl_Problem].[AffectsCIID] LEFT OUTER JOIN [dbo].[tbl_Contact] AS [Author] ON [Author].[ID] = [tbl_Problem].[AuthorID] LEFT OUTER JOIN [dbo].[tbl_CIService] AS [tbl_CIService] ON [tbl_CIService].[ID] = [tbl_Problem].[CIServiceID] LEFT OUTER JOIN [dbo].[tbl_ProblemPriority] AS [tbl_ProblemPriority] ON [tbl_ProblemPriority].[ID] = [tbl_Problem].[PriorityID] LEFT OUTER JOIN [dbo].[tbl_ProblemUrgency] AS [tbl_ProblemUrgency] ON [tbl_ProblemUrgency].[ID] = [tbl_Problem].[UrgencyID] LEFT OUTER JOIN [dbo].[tbl_Contact] AS [Owner] ON [Owner].[ID] = [tbl_Problem].[OwnerID] LEFT OUTER JOIN [dbo].[tbl_ProblemType] AS [tbl_ProblemType] ON [tbl_ProblemType].[ID] = [tbl_Problem].[ProblemTypeID] LEFT OUTER JOIN [dbo].[tbl_ProblemImpact] AS [tbl_ProblemImpact] ON [tbl_ProblemImpact].[ID] = [tbl_Problem].[ImpactID] LEFT OUTER JOIN [dbo].[tbl_ProblemStatus] AS [tbl_ProblemStatus] ON [tbl_ProblemStatus].[ID] = [tbl_Problem].[StatusID] WHERE ( tbl_Problem.AffectsCIID in (select ID from fn_GetPathForConfigurationItem('FDC83724-F48B-4590-80CA-96F8F158347F')) OR [tbl_Problem].[OrganizationID] = 'C8C54382-6B10-4095-B3CC-FFAF1C11BD9A' OR [tbl_Problem].[OrganizationID] = '7D9BDA55-6194-4B6F-BE8C-31EEE13F8AB2' ) AND [tbl_ProblemStatus].[IsFinish] = 0 время выполнения - 4 сек, выдает 3 строчкиНо если в условии изменить [tbl_ProblemStatus].[IsFinish] = 0 на [tbl_ProblemStatus].[IsFinish] = 1выбирается за 0,2 секунды.в таблице tbl_ProblemStatus 6 строк с isfinish =0 и 3 с isfinish = 1Выборка с условием [tbl_ProblemStatus].[IsFinish] = 1 - 1300 строк, но выбирается за 0,2 секундыВыборка с условием [tbl_ProblemStatus].[IsFinish] = 0 - 3 строки, но выбирается за4 секундыПомогите понять в чем может быть проблема, уже весь мозг сломал

forundex.ru