Mysql выбирает неудачный порядок соединения таблиц

fort_186
Дата: 30.01.2013 14:36:03
Добрый день. Проблема в том, что Mysql выбирает неудачный порядок соединения таблиц, через что ORDER BY не использует индекс, что влияет на производительность. Сам запрос
EXPLAIN
SELECT SQL_NO_CACHE
#STRAIGHT_JOIN
                o.offer_id,o.name,o.price,o.updated,o.description,
                `ogc`.general_category_id AS category_id , `gc`.name AS categoryName 
            FROM `offers` AS o
            INNER JOIN `shops` AS s ON (o.shop_id = s.shop_id)
            INNER JOIN `offers_general_categories` AS ogc ON ( o.offer_id = ogc.offer_id ) 
         INNER JOIN `general_categories` AS gc ON ( gc.general_category_id = ogc.general_category_id )
         WHERE o.deleted ='false'  AND s.display_status ='on'  AND o.shop_id IN (1,2)  
         AND o.price <= '10000'  AND o.price >= '1500'  
         ORDER BY  `updated` DESC
         LIMIT 10, 10


Результат EXPLAIN


*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: s
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 2
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: o
type: ref
possible_keys: PRIMARY,updating_shop_id,shop_id_price_deleted,shop_offers_shop,shop_id_deleted
key: shop_offers_shop
key_len: 4
ref: xml_debug.s.shop_id
rows: 2749
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: ogc
type: ref
possible_keys: general_category_id_offer_id,updating_offer_id,offer_id_offers_general_categories,general_category_id_offers_general_categories
key: general_category_id_offer_id
key_len: 8
ref: xml_debug.o.offer_id
rows: 1
Extra: Using index
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: gc
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: xml_debug.ogc.general_category_id
rows: 1
Extra:

Как видно что порядок JOIN изменился и MYSQL использует Using temporary; Using filesort. Такой запрос выполняется за 0.38 s
Если же использовать STRAIGHT_JOIN тогда результат совершенно другой.

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: o
type: index
possible_keys: PRIMARY,updating_shop_id,shop_id_price_deleted,shop_offers_shop,shop_id_deleted
key: updated
key_len: 4
ref: NULL
rows: 66
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: s
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: xml_debug.o.shop_id
rows: 1
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: ogc
type: ref
possible_keys: general_category_id_offer_id,updating_offer_id,offer_id_offers_general_categories,general_category_id_offers_general_categories
key: general_category_id_offer_id
key_len: 8
ref: xml_debug.o.offer_id
rows: 1
Extra: Using index
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: gc
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: xml_debug.ogc.general_category_id
rows: 1
Extra:
Время выполнения: 0.0033 s
В этом случае MYSQL использовал индекс updated для сортировки. Без Using temporary; Using filesort

Почему MYSQL так просчитывает, что можно предпринять?
Запрос используется для поиска, где параметры могут менятся и подключатся еще таблицы. Поэтому использование в запросе STRAIGHT_JOIN, только частичный случай.

Спасибо за внимание.
trew
Дата: 30.01.2013 15:31:13
fort_186,

В таком выражении, ниже, скобки не нужны
INNER JOIN `shops` AS s ON (o.shop_id = s.shop_id)

Можно попробовать так написать
INNER JOIN `shops` AS s ON o.shop_id = s.shop_id
AND o.shop_id IN (1,2)
AND o.deleted ='false'
... остальные условия
INNER JOIN `offers_general_categories` AS ogc ...

Т.е. перенести все условия из WHERE в INNER JOIN

Интересно следующее:
1. Количество записей в каждой таблице
2. Количетво записей в таблице с условием o.shop_id IN (1,2) т.е. понять селективность этого условия.
3. Количетво записей в таблице с условием o.deleted ='false' т.е. select * from таблица where deleted ='false'
4. Количетво записей в таблице с условием s.display_status ='on'
5. Количетво записей в таблице с условием o.price between '1500' AND '10000' И ещё не понятно price это текстовое поле?
Akina
Дата: 30.01.2013 15:32:51
Выполните ANALYZE TABLE для обновления статистики - может помочь... а вообще straight_join вполне обычная практика, если оптимизатор занесло не туда.
fort_186
Дата: 30.01.2013 16:38:19
trew,
Количество записей в каждой таблице
offers - 250,590
shops - 18
offers_general_categories - 62,706
general_categories - 1575

Количетво записей в таблице с условием o.shop_id IN (1,2) т.е. понять селективность этого условия.
203,075

Количетво записей в таблице с условием o.deleted ='false' т.е. select * from таблица where deleted ='false'
115,047

Количетво записей в таблице с условием s.display_status ='on'
16
--видно, что селективность индекса со значением s.display_status ='on' низка.
Так как всегда записей с 'on' будет больше чем в 'off', следовательно удаляю этот индекс.

Количетво записей в таблице с условием o.price between '1500' AND '10000' И ещё не понятно price это текстовое поле?
94,186
тип поля price - float

После удаления индекса s.display_status и удаления скобок с INNER JOIN ON ... , результат не изменился
При правке индексов выполнял Optimize table и ANALYZE TABLE
javajdbc
Дата: 30.01.2013 18:46:15
fort_186,

покажите точный состав всех индексов на таблице Оффер.
Подозрение что индексы не правильно сделаны и не работают.
fort_186
Дата: 31.01.2013 11:34:13
javajdbc,

Тип движка innodb.
Индексы offers:
internal_id_shop_id ( internal_id, shop_id )
updated( updated )
shop_id_deleted_price( shop_id, deleted, price )
shop_offers_shop( shop_id )
updating_shop_id(shop_id, updating)
shop_id_deleted( deleted,shop_id )

Поле deleted ENUM( true,false )
Поле updating ENUM( true,false )

Запросы:
1)UPDADE offers set updating = 'true' WHERE shop_id = {} AND deleted = 'false'
2)UPDADE offers set updating = 'false' AND deleted ='true' WHERE shop_id = {} AND updating = 'true'

3)select o. перечисление полей from offers AS o
INNER JOIN `shops` AS s ON o.shop_id = s.shop_id
WHERE
o.deleted ='false' AND s.display_status ='on' AND o.shop_id ={}
ORDER BY `updated` DESC
LIMIT 0,10

4)select o. перечисление полей from offers AS o
INNER JOIN `shops` AS s ON o.shop_id = s.shop_id
WHERE
o.deleted ='false' AND s.display_status ='on' AND o.price < 1000
ORDER BY `updated` DESC
LIMIT 0,10

Последние 2 запроса как вариант 2 возможных. Т.е Условие o.deleted ='false' будет всегда присуцтвовать c этим значением .
o.shop_id может быть или точечным или перечислением или отсуцтвовать
o.price - может быть : интервал, сравнение, больше, меньше или отсуцтвовать


Ниже распечатан полный листинг индексов.


*************************** 1. row ***************************
Table: offers
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: offer_id
Collation: A
Cardinality: 290611
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 2. row ***************************
Table: offers
Non_unique: 1
Key_name: updated
Seq_in_index: 1
Column_name: updated
Collation: A
Cardinality: 8303
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 3. row ***************************
Table: offers
Non_unique: 1
Key_name: shop_id_deleted_price
Seq_in_index: 1
Column_name: shop_id
Collation: A
Cardinality: 21
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 4. row ***************************
Table: offers
Non_unique: 1
Key_name: shop_id_deleted_price
Seq_in_index: 2
Column_name: deleted
Collation: A
Cardinality: 21
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
*************************** 5. row ***************************
Table: offers
Non_unique: 1
Key_name: shop_id_deleted_price
Seq_in_index: 3
Column_name: price
Collation: A
Cardinality: 48435
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
*************************** 6. row ***************************
Table: offers
Non_unique: 1
Key_name: shop_offers_shop
Seq_in_index: 1
Column_name: shop_id
Collation: A
Cardinality: 12
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 7. row ***************************
Table: offers
Non_unique: 1
Key_name: updating_shop_id
Seq_in_index: 1
Column_name: shop_id
Collation: A
Cardinality: 12
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 8. row ***************************
Table: offers
Non_unique: 1
Key_name: updating_shop_id
Seq_in_index: 2
Column_name: updating
Collation: A
Cardinality: 12
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 9. row ***************************
Table: offers
Non_unique: 1
Key_name: shop_id_deleted
Seq_in_index: 1
Column_name: deleted
Collation: A
Cardinality: 12
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
*************************** 10. row ***************************
Table: offers
Non_unique: 1
Key_name: shop_id_deleted
Seq_in_index: 2
Column_name: shop_id
Collation: A
Cardinality: 125
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
javajdbc
Дата: 31.01.2013 18:32:59
fort_186,

1. есть такой прием как "контролируемая денормализация".
Если с.дисплай_статус -- это единственое условие по таблице шоп,
то его можно вынести в таблицу офферс.
У этого метода есть некоторые недостатки (надо апдейтить это поле
везде когда оно меняется в таблице шоп, но это очень редко происходит, так?).
Используйте его ТОЛЬКО если ничего другово не помогает.

2. можно попробовать "умнее" пре-парсить --
подготовить 2-3 разных обших случаев СКЛ
и использовать тот или иной в зависимости, например,
дали цену или нет. В частности, на лету включать
СТРАЙТ жоинт если ваш "умный" пре-парсер
будет запрограмирован.

3. Убрать ненужные индексы которые отвлекают
оптимизатор.

4. как только появилась узкая ценовая рамка,
возможно, надо использовать индекс по цене.
У вас НЕТ индекса где цена стоит на первом месте.
Создайте отдельный индекс по цене.

5. индекс shop_id_deleted_price -- который вроде должен
хорошо сработать --- имеет кардиналиты 21, такойже как и
простой индекс по щоп_ид -- почему?
Это у вас такие "плоские" тестовые данные, или
неправильная статистика?
попробуйте пересоздать
fort_186
Дата: 01.02.2013 17:54:21
javajdbc,

Похоже что по пунктам 1-4 так и сделаю, если ничего не изменится. Спасибо. Индекс по цене конечно добавлю.
с.дисплай_статус редко будет обновлятся.

5.
индекс shop_id_deleted_price -- который вроде должен хорошо сработать --- имеет кардиналиты 21, такойже как и
простой индекс по щоп_ид -- почему?
Это у вас такие "плоские" тестовые данные, или неправильная статистика?
попробуйте пересоздать

Запускаю подряд ANALYZE table offers и кажный раз отображаются разная статистика.

Тут я несколько в раздумии как лучше создать индекс и на что опиратся. Может Вы лучше видите ситуацию.
Текущие индексы:
updated( updated )
shop_id_deleted_price( shop_id, deleted, price )
shop_offers_shop( shop_id )
updating_shop_id(shop_id, updating)
shop_id_deleted( deleted,shop_id )

Условие o.deleted ='false' будет всегда присуцтвовать c этим значением.
А shop_id может быть или точечным или IN перечисление

На сколько я понимаю, в случае запроса select * from offers as o where o.deleted = 'false' and o.shop_id IN (...) and o.price>0
индекс shop_id_deleted_price( shop_id, deleted, price ) может быть только использован только по первой колонке (shop_id), поскольку задано shop_id IN
?

Можеть быть лучше вместо shop_id_deleted_price( shop_id, deleted, price ) и shop_id_deleted( deleted,shop_id ) создать один
deleted_shop_id_price( deleted, shop_id, price ).
и в случае того же запроса может быть использован индекс по колонках deleted и shop_id , что будет эффективней.
javajdbc
Дата: 01.02.2013 19:55:32
fort_186,

вы задаете хорошие вопросы. Для хорошего ответа надо
рассказать теорию (а лучше читайте документацию,
там есть отдел "как работают индексы").
Вкратце (админам: может мини-ФАК сделать?):

1. в конкретном селекте может работать тольк один индекс
(а также каждый подселект может использовать свой индекс)

2. индекс может работать на филтрацию или на сортировку

3. индекс работает (если работает) начиная с первого поля в индексе.
Если в филтре (where) нет первого поля индекса
то индекс не используется для филтрации

4. индекс для филтрации имеет смысл использовать
если он отсекает <20% записей. Оптимизатор
пытается это предсказать по статистике.
Иногда ошибается, иногда статистика неверная.
иногда данные меравномерные

5. Для разных СКЛ-ах с разныму where блоками
нужны разные индексы. Кроме того, даже разные
передаваемые занчения для филтрации могут
менят-ь использование индекса

В вашем случае:
.... price > 0 никак не (должен) зацепить индекс по прайс
а вот
....price > 20000$ уже может

6. практика -- критерий правильности теории.
Экспериментируйте, очень желательно
на копии реальных данных или создавайте блиазкие
к реальности тестовые данные.

7. про теста используйте SQL_NO_CACHE,
после создания индекса делайте ОПТИМИЗЕ/АНАЛИЗЕ ТАБЛЕ
делайте 2-3 прогона и усредняйте скорость.

8. если хотите бсудить, выдавайте:
-- СКЛв формтированом виде (таг SRC)
-- скорпсть в милисекундах
-- ресультат EXPLAIN