Форум пользователей MySQL

Suguby · 07.03.2014 16:13:36

Доброго времени суток, уважаемые!

Есть табличка на 8 млн записей

CREATE TABLE `visits` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

...

  `timestamp` datetime NOT NULL,

  `source_id` bigint(20) DEFAULT NULL,

  `start_page_id` bigint(20) DEFAULT NULL,

  `end_page_id` bigint(20) DEFAULT NULL,

  `date` date DEFAULT NULL,

...

  `cookie2` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`id`),

...

  KEY `visits_a34b03a6` (`source_id`),

  KEY `visits_67129887` (`start_page_id`),

  KEY `visits_10b15c48` (`end_page_id`),

  KEY `visits_3e678750` (`cookie2`),

  KEY `d_pd` (`date`,`project_domain_id`),

  KEY `c2_ep` (`cookie2`,`end_page_id`),

  KEY `date_cid` (`date`,`counter_id`),

...

)

Нужно оптимизировать запрос

SELECT 

  v1.id AS v1_id,

  v2.id AS v2_id,

  v1.hit_count AS v1_hit_count,

  v2.hit_count AS v2_hit_count,

  v1.timestamp AS v1_timestamp,

  v2.timestamp AS v2_timestamp,

  v2.duration AS v2_duration,

  v1.depth AS v1_depth,

  v2.depth AS v2_depth,

  v2.end_page_id AS v2_end_page_id 

FROM

  visits v1 

  JOIN visits AS v2 

    ON (

      v1.id != v2.id 

      AND v1.cookie2 = v2.cookie2 

      AND v2.source_id = v1.end_page_id

    ) 

WHERE v2.date = '2014-03-02' 

  AND v1.timestamp < v2.timestamp 

  AND v1.timestamp >= SUBTIME(v2.timestamp, '00:30:00') 

ORDER BY v1.timestamp

Какие бы я индексы (составные) не создавал, оптимизатор выбирает тот, где дата на первом месте... Это понятно, v2 сразу можно отфильтровать по дате, а вот потом "... Using temporary; Using filesort"

"id"    "select_type"    "table"    "type"    "possible_keys"    "key"    "key_len"    "ref"    "rows"    "Extra"

"1"    "SIMPLE"    "v2"    "ref"    "visits_a34b03a6,visits_3e678750,d_pd,c2_ep,date_cid"    "d_pd"    "4"    "const"    "83900"    "Using where; Using temporary; Using filesort"

"1"    "SIMPLE"    "v1"    "ref"    "visits_10b15c48,visits_3e678750,c2_ep"    "visits_3e678750"    "768"    "backend_db.v2.cookie2"    "1"    "Using index condition; Using where"

Неужели никак не заставить его для связок типа

v1.id != v2.id  AND v1.cookie2 = v2.cookie2  AND v2.source_id = v1.end_page_id

использовать индексы? Почему?

rgbeast · 09.03.2014 17:02:17

Запрос очень тяжелый, надо его упрощать. Для != индекс не используется, но это условие лишее, так как v1.timestamp < v2.timestamp. Если SUBTIME заменить на его результат, посчитанный зарание, то есть шанс использовать индекс по v2.date,v2.timestamp, а для JOIN - индекс (v1.cookie2, v1.end_page_id)

Suguby · 11.03.2014 11:31:20

rgbeast написал:
Запрос очень тяжелый, надо его упрощать. Для != индекс не используется, но это условие лишее, так как v1.timestamp < v2.timestamp. Если SUBTIME заменить на его результат, посчитанный зарание, то есть шанс использовать индекс по v2.date,v2.timestamp, а для JOIN - индекс (v1.cookie2, v1.end_page_id)

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

А как выбирать составной индекс? я просто делаю (А,Б,В) (А,В,Б) (Б,А,В) (Б,В,А) и т.д. и смотрю что оптимизатор выбрал... но обычно это делаешь на разработческой машине, где база пожиже... вот это определение селективности подойдет http://oracle-patches.ru/component/cont … cle/35/341 ?

Suguby · 11.03.2014 11:33:58

вот еще метод http://habrahabr.ru/post/61845/

rgbeast · 11.03.2014 11:52:05

Селективность индексов (A,B) и (B,A) одинакова, если они обя использованы полностью. Поэтому обычно индекс создают исходя из возможности использовать наиболее длинную его часть. Важно учитывать, что любое неравенство или range-условие может быть только последней используемой частью индекса.

Suguby · 12.03.2014 11:54:48

Целый день читал книгу "Шварц Б., Зайцев П., Ткаченко В. и др. - MySQL. Оптимизация производительности (2-е издание) - 2010" узнал много нового и интересного

Потом убрал условие v1.id != v2.id, сделал сортировку по v2.timestamp (клиент позволяет), создал индекс по v2.date+v2.timestamp - время выполнения запроса радикально увеличилось - с полутора часов до минут на тестовом индекс по v1.cookie2+v1.end_page_id создан, но оптимизатор его не использует.

Форум пользователей MySQL

#1 07.03.2014 16:13:36

Использование индексов в условиях WHERE и JOIN

#2 09.03.2014 17:02:17

Re: Использование индексов в условиях WHERE и JOIN

#3 11.03.2014 11:31:20

Re: Использование индексов в условиях WHERE и JOIN

rgbeast написал:

#4 11.03.2014 11:33:58

Re: Использование индексов в условиях WHERE и JOIN

#5 11.03.2014 11:52:05

Re: Использование индексов в условиях WHERE и JOIN

#6 12.03.2014 11:54:48

Re: Использование индексов в условиях WHERE и JOIN

Board footer