Оптимизиция запроса select ... where ... order by price limit 200 из бд в 18 000 000 записей

[lexey2006.90]

База:
CREATE TABLE IF NOT EXISTS `gate_spo_price_egipt_table` (
`id` bigint(16) NOT NULL AUTO_INCREMENT,
`date_from` date NOT NULL,
`date_to` date NOT NULL,
`country` int(11) NOT NULL,
`city` int(11) NOT NULL,
`hotel` int(11) NOT NULL,
`operator` int(11) NOT NULL,
`star` int(11) NOT NULL,
`room` int(11) NOT NULL,
`pansion` int(11) NOT NULL,
`depature_from` int(11) NOT NULL,
`depature_to` int(11) NOT NULL,
`depature_date` date NOT NULL,
`days` int(11) NOT NULL,
`price` int(11) NOT NULL,
`status` tinyint(4) NOT NULL,
`chld` int(11) NOT NULL,
`age1` int(11) NOT NULL,
`age2` int(11) NOT NULL,
`age3` int(11) NOT NULL,
`age4` int(11) NOT NULL,
PRIMARY KEY (`id`,`depature_date`),
KEY `seach_gate` (`depature_date`,`room`,`days`,`status`,`operator`,`chld`,`hotel`),
KEY `seach_sort` (`price`,`depature_date`,`room`,`days`,`status`,`operator`,`chld`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
/*!50100 PARTITION BY RANGE (DAYOFYEAR(depature_date))
(PARTITION p0 VALUES LESS THAN (152) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (158) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (164) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (170) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (176) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (181) ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN (187) ENGINE = InnoDB,
PARTITION p7 VALUES LESS THAN (194) ENGINE = InnoDB,
PARTITION p8 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ AUTO_INCREMENT=40039238726 ;


запрос вида : select * from gate_spo_price_egipt_table use index (seach_key) where depature_date >= 2011-06-20 and depature_date <= 2011-06-30 and room = 23 order by price asc limit 200.
Выполняется 4 минуты, что неприемлимо. Конфигурацию сервера и субд менять слишком дорого.
Применяется:
1. Индекс
2. Партицирование по 6 дней (для быстрого поиска)

По поводу индексов: у меня возможен поиск по полям (указывается в форме):
`country` (уже разделил таблицу по странам - можно не указывать. на форме можно выбрать только 1 страну, так что соединение таблиц не происходит)
`city`
`hotel`
`operator`
`star`
`room`
`days`
`price` - поэтому и в индексе содержится почти все.
По `depature_date` (по 6 дней сделал партицирование).

 

[Gas]

1. "use index (seach_key)" - что-то я не вижу такого индекса
2. приведи explain запроса
3. мне кажется что "партицирование" сделано странно, 7 диапазонов по 6 дней и 2 партиции, охватывающие всё остальное. Не улавливаю смысл этого. Если в большинстве запросов используется дата, а по хорому в этом случае так и должно быть, то я бы сделал партиции по годам. 18 млн. записей - это за сколько лет? Если запросы как правило за текущий год, то ещё я бы попробовал добавить subpartitions для текущего года по месяцам или по 2 месяца.
4. мне кажется что от таких составных индексов как у тебя смысла может оказаться мало, так-как они пригодны для малого количества запросов ... (здесь я долго думал как бы объяснить простыми словами и решил дать ссылки Multiple-Column Indexes и ORDER BY Optimization

 

[lexey2006.90]

1. Прошу прощения, неправильно привел запрос - используется индекс seach_sort
2. mysql> explain select * from gate_spo_price_egipt use index (seach_sort) where depature_date >= 2011-06-20 and depature_date <= 2011-06-30 and room = 23 order by price asc limit 200;
+----+-------------+----------------------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------+------+---------------+------+---------+------+------+-----------------------------+
| 1 | SIMPLE | gate_spo_price_egipt | ALL | NULL | NULL | NULL | NULL | 0 | Using where; Using filesort |
+----+-------------+----------------------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set, 4 warnings (0.00 sec)

3. Партицирование тоже вставил из прошлой таблицы. (суть в том, что пропартицировано по 6 дней, 18 000 000 записей - на 4 месяца. и самый частый запрос по depature_date - плюс/минус 3 дня - поэтому и партицирование по 6 дней. а так я перепартицировал уже таблицу до конца года)

 

[lexey2006.90]

И еще вопрос - при создании партицирования, mysql заставил меня добавить depature_date в primary key - это нормально?

 

[prolis]

1. Неактуальные записи за прошлые годы перенести из таблицы поиска
2. Партиции по 6 дней точно убрать, дабы не перестараивать каждую неделю таблицы

 

[lexey2006.90]

Я сделал партицирование для ускорение поиска, и прописал 30 партиций до конца года, так что перестраивать ничего не придется

 

[Gas]

lexey2006.90
я правильно понимаю что 18M у тебя в рамках 4 месяцев, а не нескольких лет?
для приведённого тобой выше запроса нужен новый индекс, или (depature_date,room) или (room,depature_date), нужно на селективность данных смотреть. но это решение частного случая, чтоб какие-то более общие рекомендации давать, нужно знать что это заданные, какие запросы к ним идут, какие чаще, а какие реже, есть ли какое-то условие, которое присутствует во всех или большинстве запросов.

 

[lexey2006.90]

GAS - да, про 18 млн ты правильно понял. Запросы могут идти по
`city`
`hotel`
`operator`
`star`
`room`
`days`, по `price` идет сортировка.
На хабаре прочитал, что индекс не должен использоваться, если значения у поля всего 2-5. про
`operator`
`star`
`room` - это как раз так. все время присутствует значение поля только:
depature_date. (есть ли смыс делать индекс по нему - не знаю - ведь итак идет выборка только из партиции по этому полю, необходимо, наверно, только если значение depature_date попадает одновременно в 2 партиции. в большее кол-во попасть не может). чаще всего ищут по полям:
`days`
`city`
`room`
depature_date.

 

[lexey2006.90]

Chusha
1. Выборка предполагает до 10 000 записей.
2. Что даст изменение PRIMARY KEY ? (вообще - для партицирования mysql обязал меня добавить depature_date в primary key)
3. На хабаре было написано про партицирование: может замедлить только время записи в бд (партицирование вроде помогает делать выборку только из опред. партиции, а у меня как раз всегда попадает либо в 1, либо в 2 партиции)
4. Вы уже не первый, кто предлагает такое индекс, можете объяснить почему? (прочитайте, пожалуйста, прошлое сообщение к пользователю GAS, про индексы)
5. Когда нужно использовать force index?

 

[lexey2006.90]

И еще вопрос: для данного запроса - понятно, что нужен индекс только с 2-мя полями. Если у меня для выборки есть 6 полей - мне создавать 6 индексов, (с 1-им полем, с 2-мя, с 3-мя и т.д.)?

 

[Gas]

lexey2006.90
- а как часто данные добавляются изменяются в таблице и какими объёмами?
- пока не используй force index / use index, это уже тонкий тюнинг и нужно однозначно понимать что он даёт

мне создавать 6 индексов, (с 1-им полем, с 2-мя, с 3-мя и т.д.)?

нет, если данные меняются не часто, например раз в несколько дней проводится какой-то импорт, а всё остальное время идёт только селекты, то можно попробовать создать по одному индексу на каждое поле, по которому может идти выборка, в этом случае поиск в партиции по depature_date + какой-то доп.индекс (который уже сам mysql выберет на основе селективности) должны очень существенно скорость увеличить

На хабаре прочитал, что индекс не должен использоваться, если значения у поля всего 2-5.

в общем случае, если в поле всего несколько значений и распределение их +/- равномерное, то смысла в индексе на поле обычно нет, ну даст он фильтр из 10M записей до 1-2M, от этого не легче.

 

[lexey2006.90]

Разьве поиск с индексами комбинированными идут не намного быстрее? Если в поиске участвует 3 поля, и у меня есть 3 индекса по 1 полю - то mysql может выбрать из 3 одновременно?

 

[lexey2006.90]

`operator`
`star`
`room' - как раз содержат примерно по 5 различных значений.

Еще 1 проблема - мне везде пишут - тестирую с разными индексами - но даже при изменении таблицы и небольшого изменения запроса происходит кэширование данных. И невозможно отследить сколько выполняется запрос, например:

select * from gate_spo_price_egipt where depature_date >= 2011-06-20 and depature_date <= 2011-06-30 and room = 23 order by price asc limit 200; идет 1 минуту.
select * from gate_spo_price_egipt where depature_date >= 2011-07-20 and depature_date <= 2011-07-30 and room = 23 order by price asc limit 200; идет 0,09 сек., хотя записи разные.

 

[Gas]

Разьве поиск с индексами комбинированными идут не намного быстрее

быстрее, но далеко не всегда, почитай http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html и посмотри примеры, когда используется составной индекс, а когда нет.
но индексы ещё замедляют insert/update/delete операции, так-как их нужно перестраивать после них, и чем больше индексов, тем это дольше. По-этому я спрашивал тебя как часто изменяются данные в таблице, может действительно можно наплодить кучу составных индексов под разные случаи (естественно не бездумно), если в таблице очень редко происходят изменения.

происходит кэширование данных

- запускай при тесте "select SQL_NO_CACHE ...", чтоб в query cache не садилось, если он включен
- данные кешируются в innodb_pool_buffer и это нормально, в идеале нужно увеличить размер этого буфера, чтоб вся база туда влезала. Но это в идеале, посмотри какое там сейчас значение и возможно его стоит увеличить.

как раз содержат примерно по 5 различных значений.

тогда эти поля имеет смысл включать только в составной индекс, сами по себе индексы на эти поля, делать особого смысла может и нет.

 

[lexey2006.90]

mysql, как мне сказал сисадмин настроено идеально, он довольно опытный - я ему доверяю... щас попробую SQL_NO_CACHE...

 

[lexey2006.90]

Спасибо огромное за совет по SQL_NO_CACHE!!!!!!!!!!!!!!!

 

[lexey2006.90]

Тогда буду тестировать с разными индексами - напишу сюда результаты)

 

[lexey2006.90]

Какого вида подзапросы вы предлагаете? Щас попробую эффективность партицирования на базе с ним и без него элементарным запросом select * from ... where depature_date >= ... and depature_date <= ...;

 

[lexey2006.90]

Это очень мало. Запрос с подзапросом будет работать быстро. И простой запрос будет быстрым, если всё сделаете по правильном

там еще идет сортировка по цене - 10 000 записей отсортировать по цене - это быстро?

 

[lexey2006.90]

- запускай при тесте "select SQL_NO_CACHE ...", чтоб в query cache не садилось, если он включен

select SQL_NO_CACHE * from gate_spo_price_part where depature_date >= '2011-06-17' AND depature_date <= '2011-06-20'; - запрос все-равно кэшируется. (в первый раз выполняется 2 сек, потом 0,05)

Партицирование полезно далеко не всегда. В Вашем случая оно почти бесполезно, я так думаю.

При запросе выше на идентичных данных без партицир. и с партицир. (создано 2 разные таблицы). Без него 2 сек. С ним 0,5 сек