SQLinfo.ru - Все о MySQL

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

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

Вы не зашли.

#1 25.09.2007 12:58:57

Mikasto
Завсегдатай
Зарегистрирован: 25.09.2007
Сообщений: 35

Как оптимизировать запрос?

SELECT
    c.id card,
    b.name b_name,
    b.document_url b_url,
    ug.id_package,
    o.id,
    o.title,
    o.description,
    o.telephone,
    o.publish_site,
    o.date_create,
    o.city adress,
    s.domain site
FROM links l
    JOIN `usage` u ON l.`id_usage` = u.`id`
    JOIN `organizations` o ON l.`id_organization` = o.id
    JOIN `cards` c ON o.id = c.`id_organization`
    JOIN `usage_groups` ug ON o.id = ug.`id_organization`
    JOIN `businesses` b ON b.id = l.id_business
    JOIN `sites` s ON o.id = s.id_organization
    LEFT JOIN `businesses` b1 ON l.`id_business` = b1.`id`
    LEFT JOIN `businesses` b2 ON b2.`id` = b1.`id_parent`
    LEFT JOIN `businesses` b3 ON b3.`id` = b2.`id_parent`
    LEFT JOIN `businesses` b4 ON b4.`id` = b3.`id_parent`
WHERE o.publish = 'Y'
     AND 189 IN (
                l.`id_business`,
                b1.`id_parent`,
                b2.`id_parent`,
                b3.`id_parent`,
                b4.`id_parent`
                )            
GROUP BY ug.`id_organization`
ORDER BY ug.id_package DESC, o.date_create
LIMIT 100, 10

CREATE TABLE `organizations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_creator` int(10) unsigned DEFAULT NULL,
  `id_region` int(10) unsigned DEFAULT NULL,
  `id_business` int(10) unsigned DEFAULT NULL,
  `title` varchar(255) NOT NULL DEFAULT '',
  `inn` varchar(20) DEFAULT NULL,
  `kpp` varchar(20) DEFAULT NULL,
  `bank` varchar(255) DEFAULT NULL,
  `bank_bik` varchar(20) DEFAULT NULL,
  `bank_c_account` varchar(20) DEFAULT NULL,
  `bank_account` varchar(20) DEFAULT NULL,
  `date_create` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_disable` datetime DEFAULT NULL,
  `disable_reason` varchar(255) DEFAULT NULL,
  `description` text,
  `city` varchar(255) DEFAULT NULL,
  `publish` char(1) DEFAULT NULL,
  `publish_site` char(1) DEFAULT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `telephone` varchar(60) DEFAULT NULL,
  `fax` varchar(20) DEFAULT NULL,
  `site` varchar(255) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_organizations_region` (`id_region`),
  KEY `FK_organizations_business` (`id_business`),
  KEY `FK_organizations_creator` (`id_creator`),
  KEY `publish` (`publish`),
  KEY `date_create` (`date_create`),
  CONSTRAINT `FK_organizations_business` FOREIGN KEY (`id_business`) REFERENCES `businesses` (`id`),
  CONSTRAINT `FK_organizations_creator` FOREIGN KEY (`id_creator`) REFERENCES `users` (`id`),
  CONSTRAINT `FK_organizations_region` FOREIGN KEY (`id_region`) REFERENCES `regions` (`id`)
) ENGINE=InnoDB DEFAULT;

CREATE TABLE `usage_groups` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_package` int(10) unsigned NOT NULL DEFAULT '0',
  `id_organization` int(10) unsigned NOT NULL DEFAULT '0',
  `date_create` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `note` varchar(255) DEFAULT NULL,
  `card` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_usage_group_package` (`id_package`),
  KEY `FK_usage_group_organiuzation` (`id_organization`),
  KEY `id_package` (`id_package`,`id_organization`),
  CONSTRAINT `FK_usage_group_organization` FOREIGN KEY (`id_organization`) REFERENCES `organizations` (`id`),
  CONSTRAINT `FK_usage_group_package` FOREIGN KEY (`id_package`) REFERENCES `packages` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cards` (
  `id` int(8) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `id_series` int(10) unsigned NOT NULL DEFAULT '0',
  `id_organization` int(10) unsigned DEFAULT NULL,
  `id_package` int(10) unsigned NOT NULL DEFAULT '0',
  `date_pay` datetime DEFAULT NULL,
  `id_region` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_cards_series` (`id_series`),
  KEY `FK_cards_organization` (`id_organization`),
  KEY `FK_cards_package` (`id_package`),
  KEY `id_region` (`id_region`),
  CONSTRAINT `FK_cards_organization` FOREIGN KEY (`id_organization`) REFERENCES `organizations` (`id`),
  CONSTRAINT `FK_cards_package` FOREIGN KEY (`id_package`) REFERENCES `packages` (`id`),
  CONSTRAINT `FK_cards_series` FOREIGN KEY (`id_series`) REFERENCES `series` (`id`)
) ENGINE=InnoDB DEFAULT;

EXPLAIN:

id    select_type    table    type        key    key_len    ref    rows    Extra
1    SIMPLE    c    index        FK_cards_organization    5        105400    Using index; Using temporary; Using filesort
1    SIMPLE    o    eq_ref        PRIMARY    4    test.c.id_organization    1    Using where
1    SIMPLE    ug    ref        FK_usage_group_organiuzation    4    test.o.id    1    Using where
1    SIMPLE    l    ref        FK_links_organizations_id    4    test.c.id_organization    1   
1    SIMPLE    b1    eq_ref        PRIMARY    4    test.l.id_business    1   
1    SIMPLE    b2    eq_ref        PRIMARY    4    test.b1.id_parent    1   
1    SIMPLE    b3    eq_ref        PRIMARY    4    test.b2.id_parent    1   
1    SIMPLE    b4    eq_ref        PRIMARY    4    test.b3.id_parent    1    Using where
1    SIMPLE    s    ref        FK_sites_organization    5    test.o.id    1    Using where
1    SIMPLE    u    eq_ref        PRIMARY    4    test.l.id_usage    1    Using index
1    SIMPLE    b    eq_ref        PRIMARY    4    test.l.id_business    1   

Время выполнения 0.20 сек. Хочется избавиться от filesort и temporary.

Неактивен

 

#2 25.09.2007 13:14:45

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Как оптимизировать запрос?

Какая жуть smile

1. Насколько я вижу, b1 тождественно равно b в этом запросе => -1 таблица.
2. Время выполнения не такое уж и плохое для такого запроса, Вы просто хотите
избавиться от страшных слов, или боитесь, что упретесь в производительность
на 10 миллионах карточек?
3. Боюсь, если Вы хотите сделать это быстрее - прийдется перепродумать структуру.
Кусок типа "189 in (...)" никогда не будет использовать индекс.

Неактивен

 

#3 25.09.2007 13:51:54

Mikasto
Завсегдатай
Зарегистрирован: 25.09.2007
Сообщений: 35

Re: Как оптимизировать запрос?

paulus написал:

Какая жуть smile

1. Насколько я вижу, b1 тождественно равно b в этом запросе => -1 таблица.

Убрал b1.
На скорость никак не повлияло, или малозаметно. Хотя она и правда лишняя.

paulus написал:

2. Время выполнения не такое уж и плохое для такого запроса, Вы просто хотите
избавиться от страшных слов, или боитесь, что упретесь в производительность
на 10 миллионах карточек?

Размер запроса  и страшные слова не волнуют - главное скорость!

paulus написал:

3. Боюсь, если Вы хотите сделать это быстрее - прийдется перепродумать структуру.
Кусок типа "189 in (...)" никогда не будет использовать индекс.

По-моему они все таки используются (обратите внимание на eq_ref):
id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    b    eq_ref    PRIMARY    PRIMARY    4    test.l.id_business    1   
1    SIMPLE    b2    eq_ref    PRIMARY    PRIMARY    4    test.b.id_parent    1   
1    SIMPLE    b3    eq_ref    PRIMARY    PRIMARY    4    test.b2.id_parent    1   
1    SIMPLE    b4    eq_ref    PRIMARY    PRIMARY    4    test.b3.id_parent    1    Using where


Перенес позиции таблиц в запросе - ug на 1е место, и создал индекс для с (id_organization, id)
SELECT
    c.id card,
    b.name b_name,
    b.document_url b_url,
    ug.id_package,
    o.id,
    o.title,
    o.description,
    o.telephone,
    o.publish_site,
    o.date_create,
    o.city adress,
    s.domain site
FROM `usage_groups` ug   
    JOIN `organizations` o ON ug.`id_organization` = o.id
    JOIN `cards` c ON ug.`id_organization` = c.`id_organization`
    JOIN links l ON o.id = l.`id_organization`
    JOIN `usage` u ON l.`id_usage` = u.`id`
    JOIN `businesses` b ON b.id = l.id_business
    JOIN `sites` s ON o.id = s.id_organization
    LEFT JOIN `businesses` b2 ON b2.`id` = b.`id_parent`
    LEFT JOIN `businesses` b3 ON b3.`id` = b2.`id_parent`
    LEFT JOIN `businesses` b4 ON b4.`id` = b3.`id_parent`
WHERE o.publish = 'Y'
     AND 189 IN (
                l.`id_business`,
                b.`id_parent`,
                b2.`id_parent`,
                b3.`id_parent`,
                b4.`id_parent`
                )                            
GROUP BY ug.`id_organization`
ORDER BY ug.id_package DESC, o.date_create
LIMIT 100, 10

EXPLAIN стал лучше - вместо 106000 перебирается 1200 - используется меньшая таблица ug:
id    select_type    table    type    key    key_len    ref    rows    Extra
1    SIMPLE    o    ref        publish    4    const    1290    Using where; Using temporary; Using filesort
1    SIMPLE    ug    ref        FK_usage_group_organiuzation    4    test.o.id    1   
1    SIMPLE    l    ref        FK_links_organizations_id    4    test.o.id    1   
1    SIMPLE    b    eq_ref        PRIMARY    4    test.l.id_business    1   
1    SIMPLE    b2    eq_ref        PRIMARY    4    test.b.id_parent    1   
1    SIMPLE    b3    eq_ref        PRIMARY    4    test.b2.id_parent    1   
1    SIMPLE    b4    eq_ref        PRIMARY    4    test.b3.id_parent    1    Using where
1    SIMPLE    s    ref        FK_sites_organization    5    test.l.id_organization    1    Using where
1    SIMPLE    u    eq_ref        PRIMARY    4    test.l.id_usage    1    Using index
1    SIMPLE    c    ref        FK_cards_organization    5    test.ug.id_organization    7    Using where; Using index

Время выполнения уменьшилось на 0.05 сек, но filesort остался и все равно медленно?

Можно ли еще ускорить?

Неактивен

 

#4 25.09.2007 14:20:34

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Как оптимизировать запрос?

eq_ref - это хороший способ составления таблиц в JOIN. Он имеет отношение
к JOIN `usage` u ON l.`id_usage` = u.`id`, например. Но он не имеет отношения
к фильтрации данных, которые есть 189 in (...) и вследствие которого есть
"using where" справа от нескольких колонок.

Попробуйте переписать его в виде
WHERE b.id_parent = 189 OR ...

При этом после добавления на b ключика на (id_parent, id) должно летать.

Неактивен

 

#5 25.09.2007 14:48:09

Mikasto
Завсегдатай
Зарегистрирован: 25.09.2007
Сообщений: 35

Re: Как оптимизировать запрос?

paulus написал:

eq_ref - это хороший способ составления таблиц в JOIN. Он имеет отношение
к JOIN `usage` u ON l.`id_usage` = u.`id`, например. Но он не имеет отношения
к фильтрации данных, которые есть 189 in (...) и вследствие которого есть
"using where" справа от нескольких колонок.

Попробуйте переписать его в виде
WHERE b.id_parent = 189 OR ...

При этом после добавления на b ключика на (id_parent, id) должно летать.

Вы будете удивлены, но использование:
WHERE o.publish = 'Y'
     AND (
                          l.`id_business` = 189
        OR b.`id_parent` = 189
        OR b2.`id_parent` = 189
        OR b3.`id_parent` = 189
        OR b4.`id_parent` = 189
        )

никак не повлияло на скорость, а ключик на b (id_parent, id) никак не повлиял на скорость и даже никак не изменил EXPLAIN.
Хотя в теории я с вами согласен. Может быть новая версия MySQL стала использовать индексы на IN выражениях..

0.16 сек предел на свободном сервере, а на рабочем 1.00 - 5.00 сек sad

Есть ли смысл оптимизировать дальше? Или копаться в настройках сервера MySQL ?

Неактивен

 

#6 25.09.2007 14:50:46

Mikasto
Завсегдатай
Зарегистрирован: 25.09.2007
Сообщений: 35

Re: Как оптимизировать запрос?

Более того, если убрать все b - b4 и условия с ними связанные, то на производительсность это не отражается. Мне кажется ключ лежит в ORDER BY, который использует filesort.

Неактивен

 

#7 25.09.2007 14:53:48

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Как оптимизировать запрос?

Оптимизатор решил, что INDEX_MERGE в данном случае будет хуже FTS. Возможно,
он прав smile

Можете сделать табличку "предки бизнеса", где держать всех предков каждого
бизнеса (не только прямых родителей). Тогда будет один INNER JOIN, который
точно пробивает по индексу.

P.S. MySQL умеет использовать индексы в случае с IN, когда Вы пишете
column IN (value list), но не в обратном случае.

Неактивен

 

#8 25.09.2007 15:05:11

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Как оптимизировать запрос?

А в случае без b причина может быть другой. Например, Вы достаете почти
все данные из таблицы. При этом у Вас сортировка заведомо происходит не
по индексу, т.к. сортируете Вы по двум разным таблицам.

Есть шанс использовать сортировку по ключу для первого прохода.

Можете попробовать поиграть с JOIN, вытаскивая их в заданном Вами порядке
(SELECT STRAIGHT_JOIN ...). Если вытаскивать сначала из ug и объединять
все остальные таблицы через eq_ref, то MySQL может использовать индекс по
ug (который, разумеется, должен быть).

Неактивен

 

#9 25.09.2007 15:18:04

Mikasto
Завсегдатай
Зарегистрирован: 25.09.2007
Сообщений: 35

Re: Как оптимизировать запрос?

Еще интересный факт - чистая выборка всех в данном дереве занимает 0.02 сек. Т.е. надо наверное выдвинуть ее наперед.
SELECT   
    b.name b_name
FROM
    `businesses` b
    LEFT JOIN `businesses` b2 ON b2.`id` = b.`id_parent`
    LEFT JOIN `businesses` b3 ON b3.`id` = b2.`id_parent`
    LEFT JOIN `businesses` b4 ON b4.`id` = b3.`id_parent`
WHERE  (
         b.id = 189
        OR b.`id_parent` = 189
        OR b2.`id_parent` = 189
        OR b3.`id_parent` = 189
        OR b4.`id_parent` = 189
        )
LIMIT 100, 10
10 rows fetched (0,02 sec)

Я плохо знаю как пользоваться STRAIGHT_JOIN hmm Буду пробовать..

Неактивен

 

#10 25.09.2007 15:21:36

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Как оптимизировать запрос?

Просто пишете STRAIGHT_JOIN после SELECT - и таблицы будут собираться
в том порядке, который указываете Вы, а не в том, который выбирает оптимизатор.
Иногда он может ошибаться...

Неактивен

 

#11 25.09.2007 15:36:09

Mikasto
Завсегдатай
Зарегистрирован: 25.09.2007
Сообщений: 35

Re: Как оптимизировать запрос?

paulus написал:

Просто пишете STRAIGHT_JOIN после SELECT - и таблицы будут собираться
в том порядке, который указываете Вы, а не в том, который выбирает оптимизатор.
Иногда он может ошибаться...

Выигрыш 0.04 секунды на лицо - спасибо.
Наверное это предел 0.11 сек для такого громоздкого запроса. Или еще остались способы? smile

Неактивен

 

#12 25.09.2007 15:40:21

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Как оптимизировать запрос?

Способ - переделать сами данные. Сделайте таблицу, все-таки, с предками. Один JOIN
будет работать с индексом при ограничении WHERE, сейчас у Вас все равно выбираются
все строки, а потом идет поиск по этой временной табличке.

Неактивен

 

#13 25.09.2007 15:45:51

Mikasto
Завсегдатай
Зарегистрирован: 25.09.2007
Сообщений: 35

Re: Как оптимизировать запрос?

paulus написал:

Способ - переделать сами данные. Сделайте таблицу, все-таки, с предками. Один JOIN
будет работать с индексом при ограничении WHERE, сейчас у Вас все равно выбираются
все строки, а потом идет поиск по этой временной табличке.

Это я к сожалению сделать не смогу - не в моей компетенции изменение структуры таблиц. С деревом завязано много другого.
STRAIGHT_JOIN - отличная штука!

Неактивен

 

#14 25.09.2007 15:59:38

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Как оптимизировать запрос?

Тогда посмотрите еще на значение tmp_table_size. Если оно достаточно большое,
то временные таблицы будут создаваться в памяти, что несколько ускорит Ваши
запросы (я имею в виду production-машинку).

Неактивен

 

#15 25.09.2007 16:45:35

Mikasto
Завсегдатай
Зарегистрирован: 25.09.2007
Сообщений: 35

Re: Как оптимизировать запрос?

paulus написал:

Тогда посмотрите еще на значение tmp_table_size. Если оно достаточно большое,
то временные таблицы будут создаваться в памяти, что несколько ускорит Ваши
запросы (я имею в виду production-машинку).

Всего на машинке 512М, а tmp_table_size=32М. Есть ли смысл увеличивать?

Неактивен

 

#16 25.09.2007 17:37:23

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Как оптимизировать запрос?

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

Неактивен

 

Board footer

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