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

vaspet · 08.04.2009 16:27:25

Запрос:

SELECT

    A.*

FROM

    Artikel AS A

INNER JOIN

    P2A ON A.ID = P2A.artikel_id

INNER JOIN

    mlp ON mlp.Masteridlink = P2A.product_id

LEFT JOIN

    mlm ON A.ID = mlm.IDlink

WHERE 

    (mlp.Produktidlink BETWEEN 10100000000 AND 10200000000)

    AND

       (

    mlm.Molink IS NULL

    )

GROUP BY

    A.ID

ORDER BY

    A.JJMNr;

Таблицы (только затронутые поля):

CREATE TABLE  `artikel` (

  `ID` int(10) NOT NULL,

  PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;

# 130,000 строк

CREATE TABLE  `p2a` (

  `product_id` int(10) unsigned NOT NULL,

  `artikel_id` int(10) unsigned NOT NULL,

  KEY `p_id` (`product_id`),

  KEY `a_id` (`artikel_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

# 600,000 строк

CREATE TABLE  `mlp` (

  `Masteridlink` int(10) unsigned NOT NULL,

  `Produktidlink` bigint(20) unsigned NOT NULL,

  KEY `pde` (`Produktidlink`),

  KEY `masteridlink` (`Masteridlink`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;

# 7,500 строк

CREATE TABLE  `mlm` (

  `IDlink` int(10) DEFAULT NULL,

  `Molink` int(10) DEFAULT NULL,

  KEY `IDlink1` (`IDlink`),

  KEY `Molink1` (`Molink`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 1,000,000 строк

Время исполнения >50 сек!
Выбираерся например 420 записей.

Explain:

id    select_type    table    type    possible_keys        key        key_len        ref                    rows    Extra

   SIMPLE        mlp        range    pde,masteridlink    pde        8                                508        Using where; Using temporary; Using filesort

   SIMPLE        P2A        ref        p_id,a_id            p_id    4            mlp.Masteridlink    26    

   SIMPLE        A        eq_ref    PRIMARY                PRIMARY    4            P2A.artikel_id        1        Using where

   SIMPLE        mlm        ref        IDlink1                IDlink1    5            A.ID                23        Using where

Отредактированно vaspet (08.04.2009 16:30:31)

paulus · 08.04.2009 17:00:28

Оптимизация подразумевает обычно не тупое добавление индексов (благо, у Вас они
висят на каждом поле), но и подход к способу хранения данных (и пониманию, что хранится
в таблицах).

Возможно, на существующих данных можно переписать запрос так, чтобы он логически
был правилен, но при этом запрос бы выполнялся меньше. С текущими названиями таблиц, я могу
только гадать, что такое, например, mlp.

Конструкции вида «нет id в этой таблице или id есть, но поля не заполнены» почти всегда приводят
к тому, что индекс на этой таблице не используется.

Как первое приближение, попробуйте избавиться от mlm в этом запросе в плане «незаполненности
полей» (кусок в WHERE): нет смысла хранить пустые строки, если они не заполнены, а запрос это
усложняет.

Если переструктурировать данные тяжело, то можете вытаскивать не A.*, а необходимые A.ID
(тогда сортировка filesort будет происходить над меньшим количеством данных => будет происходить
быстрее), а потом к готовому результату присоединить данные для нужных ID:
SELECT A.* FROM A JOIN (SELECT A.ID FROM ... ) B USING (ID) ORDER BY JJMnr;

vaspet · 08.04.2009 17:28:57

paulus написал:
Конструкции вида «нет id в этой таблице или id есть, но поля не заполнены» почти всегда приводят
к тому, что индекс на этой таблице не используется.

Смысл запроса как раз найти те А (Artikel), которые не привязаны к объектам (Molink) из mlm // == A.ID NOT IN (SELECT IDlink FROM mlm)
Но при этом лежашие в Контайнерах между 10100000000 и 10200000000

Таблицы
p2a (Artikel to Product n:n)
mlp (Product to Container n:n)
mlm (Artikel to Object n:n)

paulus написал:
Если переструктурировать данные тяжело, то можете вытаскивать не A.*, а необходимые A.ID
(тогда сортировка filesort будет происходить над меньшим количеством данных => будет происходить
быстрее), а потом к готовому результату присоединить данные для нужных ID:
SELECT A.* FROM A JOIN (SELECT A.ID FROM ... ) B USING (ID) ORDER BY JJMnr;

Насколько я понимаю сортировка произойдет после выборки?
А там уже 400 строк, которые надо отсортировать. Поправте меня, если я ошибаюсь.

П.С.
Интересный результат дало разбиение таблицы mlm:

CREATE TABLE `mlm` (

  `IDlink` int(11) NOT NULL default '0',

  `Molink` int(11) NOT NULL default '0',

  KEY `IDlink` (`IDlink`),

  KEY `Molink` (`Molink`)

)

PARTITION BY RANGE( Molink ) (

    PARTITION p0 VALUES LESS THAN (20000),

    PARTITION p1 VALUES LESS THAN (30000),

    PARTITION p2 VALUES LESS THAN (40000),

    PARTITION p3 VALUES LESS THAN (50000),

    PARTITION p4 VALUES LESS THAN (60000),

    PARTITION p5 VALUES LESS THAN MAXVALUE

);

Время выполнения упало до 5 сек. // буду еще проверять возможно кэш "помешал"

Отредактированно vaspet (08.04.2009 17:29:30)

vaspet · 08.04.2009 17:49:37

не совсем так:

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

до 15 сек
и Таблицы были переделанны в MyISAM

Отредактированно vaspet (08.04.2009 17:50:14)

paulus · 08.04.2009 19:16:34

«Которые не привязаны к объектам» — там NULL на другом поле, вроде, должен быть — на поле связи.
Зачем другие поля при этом доставать?

Если такой прирост дало преобразование в MyISAM, то, может, у Вас в InnoDB просто мало памяти было?

vaspet · 08.04.2009 19:31:46

paulus написал:
«Которые не привязаны к объектам» — там NULL на другом поле, вроде, должен быть — на поле связи.
Зачем другие поля при этом доставать?

это хорошее замечание, спасиба

paulus написал:
Если такой прирост дало преобразование в MyISAM, то, может, у Вас в InnoDB просто мало памяти было?

Мало памяти в InnoDB? ето какой параметр крутить надо? не совсем понял.

paulus · 09.04.2009 00:59:16

innodb_buffer_pool_size — это память, которую InnoDB использует для работы.
Чем больше — тем лучше. На 32битах больше 1.5Гигабайт лучше не ставить, иначе
можете вылезти из адресного пространства.

vaspet · 09.04.2009 16:30:57

Всем спасибо за помощь.
Все вместе + танцы с бубном дало прирост производительности.
Теперь - 3,7сек.

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

#1 08.04.2009 16:27:25

Типичная проблемма - медлинная выборка. Помогите оптимизировать.

#2 08.04.2009 17:00:28

Re: Типичная проблемма - медлинная выборка. Помогите оптимизировать.

#3 08.04.2009 17:28:57

Re: Типичная проблемма - медлинная выборка. Помогите оптимизировать.

paulus написал:

paulus написал:

#4 08.04.2009 17:49:37

Re: Типичная проблемма - медлинная выборка. Помогите оптимизировать.

сам и написал:

#5 08.04.2009 19:16:34

Re: Типичная проблемма - медлинная выборка. Помогите оптимизировать.

#6 08.04.2009 19:31:46

Re: Типичная проблемма - медлинная выборка. Помогите оптимизировать.

paulus написал:

paulus написал:

#7 09.04.2009 00:59:16

Re: Типичная проблемма - медлинная выборка. Помогите оптимизировать.

#8 09.04.2009 16:30:57

Re: Типичная проблемма - медлинная выборка. Помогите оптимизировать.

Board footer