Оптимизация запроса MySQL с внутренним соединением. Mysql оптимизация запроса

mysql - Оптимизация запросов MYSQL (необходимо увеличить скорость)

У меня есть таблица MySQL, в которой содержатся данные Google Analytics:

CREATE TABLE IF NOT EXISTS `analytics_data` ( `ga_profile_id` int(11) NOT NULL, `page` varchar(200) NOT NULL, `source` varchar(150) NOT NULL, `medium` varchar(50) NOT NULL, `keyword` varchar(200) NOT NULL, `bounces` int(11) NOT NULL, `entrances` int(11) NOT NULL, `exits` int(11) NOT NULL, `new_visits` int(11) NOT NULL, `page_views` int(11) NOT NULL, `unique_page_views` int(11) NOT NULL, `time_on_page` int(11) NOT NULL, `visits` int(11) NOT NULL, `date` date NOT NULL, KEY `ga_profile_id` (`ga_profile_id`,`source`,`medium`,`date`), ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

У меня есть запрос на вычисление суммы посетителей на основе идентификатора профиля аналитики google (ga_profile_id) за определенный период времени:

SELECT SUM( `visits` ), ( UNIX_TIMESTAMP( `date` ) - 21600 ) * 1000 AS date FROM `analytics_data` WHERE `date` >= '2011-05-09' AND `date` <= '2011-06-08' AND `ga_profile_id` = [...] GROUP BY `date`

У нас есть 4,5 миллиона записей или около того.

Данные индекса:

Type: BTREE Fields/Cardinality: ga_profile_id / 100 source / 10196 medium / 10196 date / 149893

EXPLAIN SELECT - id: 1 - select_type: ПРОСТОЕ - таблица: analytics_data - тип: ref - возможные_keys: ga_profile_id - ключ: ga_profile_id - ref: const - строки: 219555 - Дополнительно: использование где; Использование временных; Использование filesort

Среднее время выполнения: 1 секунда.

Мы находимся на виртуальном частном сервере, и большинство запросов выполняются в .0003 - 0,03 секунды. LONG-запросы (которые я собирался оптимизировать в какой-то момент), как правило, составляют 3 секунды.

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

Я смотрю на изменение MyISAM на память - любые идеи приветствуются.

qaru.site

performance - MySQL: оптимизация запроса JOIN

Создайте следующие индексы:

CREATE INDEX ix_big_1_2_a ON tab_big (id1, id2, id_a) CREATE UNIQUE INDEX ux_small_b_2_1 ON tab_small (id_b, id2, id1)

и попробуйте следующее:

SELECT DISTINCT a.id_a FROM tab_small b JOIN tab_big a ON (a.id1, a.id2) = (b.id1, b.id2) WHERE b.id_b = 2 AND a.id_a NOT IN ( SELECT id1 FROM tab_small b1 /* FORCE INDEX (PRIMARY) */ WHERE b1.id_b = 2 ) AND a.id_a NOT IN ( SELECT id2 FROM tab_small b2 /* FORCE INDEX (ux_small_b_2_1) */ WHERE b2.id_b = 2 )

который создает этот план запроса:

1, 'PRIMARY', 'b', 'ref', 'PRIMARY,ux_small_b_2_1', 'PRIMARY', '4', 'const', 1, 100.00, 'Using index; Using temporary' 1, 'PRIMARY', 'a', 'ref', 'ix_big_1_2', 'ix_big_1_2', '8', 'test.b.id1,test.b.id2', 2, 100.00, 'Using where' 3, 'DEPENDENT SUBQUERY', 'b2', 'ref', 'ux_small_b_2_1', 'ux_small_b_2_1', '8', 'const,func', 1, 100.00, 'Using index' 2, 'DEPENDENT SUBQUERY', 'b1', 'ref', 'PRIMARY', 'PRIMARY', '8', 'const,func', 1, 100.00, 'Using index'

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

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

Все было бы намного проще, если MySQL могли выполнять FULL OUTER JOIN с помощью MERGE, но это не так.

Update:

Судя по вашей статистике, этот запрос будет более эффективным:

SELECT id_a FROM ( SELECT DISTINCT id_a FROM tab_big ad ) a WHERE id_a NOT IN ( SELECT id1 FROM tab_small b1 FORCE INDEX (PRIMARY) WHERE b1.id_b = 2 ) AND id_a NOT IN ( SELECT id2 FROM tab_small b2 FORCE INDEX (ux_small_b_2_1) WHERE b2.id_b = 2 ) AND EXISTS ( SELECT NULL FROM tab_small be JOIN tab_big ae ON (ae.id1, ae.id2) = (be.id1, be.id2) WHERE be.id_b = 2 AND ae.id_a = a.id_a )

Он работает следующим образом:

Создает список DISTINCT id_a (который длиннее 100,000)
Фильтрует значения, присутствующие в подмножестве
Для каждого значения id_a он ищет подмножество для наличия (id_a, id1, id2). Это делается путем итерации подмножества. Поскольку вероятность найти это значение высока, скорее всего, поиск будет успешным в 10 строках или около того с начала подмножества, а EXISTS вернется в тот самый момент.

Это, скорее всего, нужно будет оценивать только около 1,000,000 записей или так.

Убедитесь, что используется следующий план:

1, 'PRIMARY', '<derived2>', 'ALL', '', '', '', '', 8192, 100.00, 'Using where' 5, 'DEPENDENT SUBQUERY', 'be', 'ref', 'PRIMARY,ux_small_b_2_1', 'PRIMARY', '4', 'const', 1, 100.00, 'Using index' 5, 'DEPENDENT SUBQUERY', 'ae', 'eq_ref', 'PRIMARY,ix_big_1_2', 'PRIMARY', '12', 'a.id_a,test.be.id1,test.be.id2', 1, 100.00, 'Using index' 4, 'DEPENDENT SUBQUERY', 'b2', 'ref', 'ux_small_b_2_1', 'ux_small_b_2_1', '8', 'const,func', 1, 100.00, 'Using index' 3, 'DEPENDENT SUBQUERY', 'b1', 'ref', 'PRIMARY', 'PRIMARY', '8', 'const,func', 1, 100.00, 'Using index' 2, 'DERIVED', 'ad', 'range', '', 'PRIMARY', '4', '', 10, 100.00, 'Using index for group-by'

самая важная часть - Using index for group-by в последней строке.

qaru.site

sql - Оптимизация запроса MySQL с внутренним соединением

Я обновил запрос, используя объединения, а не соединение в предложении WHERE. Кроме того, глядя на него, как разработчика, вы можете напрямую видеть взаимосвязь между таблицами. A-> B, A-> D и D-> C. Теперь, на таблице B, где вы хотите получить наивысший идентификатор, основанный на общем "ID = index_ID", а для RDD = 11305 не потребуется полный подзапрос. Однако это переместило "MAX()" в верхнюю часть предложения выбора поля. Я бы удостоверился, что у вас есть индекс на tblB on (index_id, rdd). Наконец, делая STRAIGHT_JOIN, вы можете принудительно выполнить заказ для запуска запроса на основе того, как конкретно указано.

- ИЗМЕНЕНИЕ ИЗ КОММЕНТАРИИ -

Кажется, вы получаете нули из tblB. Обычно это указывает на действительную запись tblA, но нет записи tblB по тому же идентификатору, у которого есть RDD = 11305. Тем не менее, похоже, что вас интересуют только те записи, которые связаны с 11305, поэтому я соответствующим образом корректирую запрос. Убедитесь, что у вас есть индекс на tblB на основе столбца "RDD" (по крайней мере, в первой позиции в случае индекса нескольких столбцов)

Как вы можете видеть в этом, я предварительно запрашиваю из таблицы B только для 11305 записей и предварительной группировки по index_ID (связанным с tblA). Это дает мне одну запись за индекс, где они будут существовать... Из этого результата я присоединяюсь к A, а затем снова возвращаюсь к B, но на основе этого наивысшего идентификатора соответствия, а затем D и C, как и раньше. Итак, СЕЙЧАС, вы можете получить любой столбец из любой из таблиц и получить надлежащую запись, о которой идет речь... В этом запросе не должно быть значений NULL.

Надеюсь, я уточнил, КАК Я собираю эти штуки для вас.

SELECT STRAIGHT_JOIN PreQuery.HighestPerIndexID tblA.id, tblA.AnotherAField, tblA.Etc, tblB.SomeOtherField, tblB.AnotherField, tblC.id, tblD.id FROM ( select PQ1.Index_ID, max( PQ1.ID ) as HighestPerIndexID from tblB PQ1 where PQ1.RDD = 11305 group by PQ1.Index_ID ) PreQuery JOIN tblA on PreQuery.Index_ID = tblA.ID join tblB on PreQuery.HighestPerIndexID = tblB.ID join tblD on tblA.s_Name = tblD.name join tblC on tblD.s_type = tblC.Name ORDER BY tblA.s_Name

qaru.site