SQLinfo.ru - Все о MySQL

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

Задавайте вопросы, мы ответим

Вы не зашли.

#1 07.03.2014 16:13:36

Suguby
Участник
Зарегистрирован: 26.12.2011
Сообщений: 21

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

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

Есть табличка на 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
использовать индексы? Почему?

Неактивен

 

#2 09.03.2014 17:02:17

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

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

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

Неактивен

 

#3 11.03.2014 11:31:20

Suguby
Участник
Зарегистрирован: 26.12.2011
Сообщений: 21

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

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 ?

Неактивен

 

#4 11.03.2014 11:33:58

Suguby
Участник
Зарегистрирован: 26.12.2011
Сообщений: 21

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

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

Неактивен

 

#5 11.03.2014 11:52:05

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

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

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

Неактивен

 

#6 12.03.2014 11:54:48

Suguby
Участник
Зарегистрирован: 26.12.2011
Сообщений: 21

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

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

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

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson