Оптимизировать запрос из одной таблицы.

[camka]

Оптимизировать запрос из одной таблицы.

Когда-то давно уже задавался этим вопросом в этом же форуме, но идеального решения не нашел. Да еще со временем значитально выросло количество данных в базе. И проблема всплыла снова.

Запрос:
[sql]
select query, sum(hits) as hits
from stat_fastsearch
where
engine in(1,2,3,4,5,6,7,8,9,10,11,12)
and id between 20040501 and 20050501
group by query
order by hits desc
limit 1000;
[/sql]

где stat_fastsearch - это таблица ежедневной статистики поиска фраз на разный страницах сайта.
[sql]
CREATE TABLE `stat_fastsearch` (
`id` int(11) NOT NULL default '0',
`query` int(11) NOT NULL default '0',
`engine` int(11) NOT NULL default '0',
`hits` int(10) unsigned default NULL,
KEY `id` (`id`),
KEY `engine_id` (`engine`,`id`),
KEY `query` (`query`),
) TYPE=MyISAM
[/sql]

id - это не первичный ключ, а, как ни странно, дата по которой собирается статистика
query - это идентификатор искомой фразы, ссылающийся на другую таблицу
engine - идентификатор страницы, на которой искали фразу
hits - количество раз, когда искалась данная фраза в указанный день на определенной странице.

Вышеописанным запросом выбирается топ самых частоискомых фраз для выбранных страниц за заданный период времени.

Строк в таблице с пол миллиона. Проблема в группировке и сортировке. Запрос выполняется ок. 10 секунд. Обычно используется индекс engine_id, когда фильтруется по условиям в where, но когда в выборку входят все страницы, может использоваться и индекс id. Хотелось бы как-то избавиться от using temporary и using filesort. Пробовал всевозможные комбинации индексов, но как-то значительно оптимизировать скорость не удалось.

 

[chira]

сколько весит заархивированный dump для таблицы stat_fastsearch?
можешь ли его выложить по ссылке какой-нибудь
хотелось бы поэкспериментировать ...

 

[camka]

тащи

 

[chira]

самый быстрый получился с таким индексом
create index qeih on stat_fastsearch(query,engine,id,hits);
остальные индексы все удалить ...

 

[camka]

В твоем варианте наибольшей производительности запрос достигает только в случае, когда выборка идет по всем (большинству) сайтов и за весь период времени. Если же делать запрос по одному сайту и за один день, он, при том же времени отработки абсолютно неоптимален, поскольку, при использовании индекса engine_id скорость отработки была бы _намного_ быстрее. И еще меня ввело в замешательство желание использовать mySql'ем индекса query, пока я его насильно не удалил, причем, он даже игнорировал строгое указания индекса, который надо использовать: use index (qeih).

Да, и до сих пор присутствуют using temporary и using filesort, в купе с using index и using where

 

[chira]

В твоем варианте наибольшей производительности запрос достигает только в случае, когда выборка идет по всем (большинству) сайтов и за весь период времени. Если же делать запрос по одному сайту и за один день, он, при том же времени отработки абсолютно неоптимален, поскольку, при использовании индекса engine_id скорость отработки была бы _намного_ быстрее.

всё правильно

от using temporary и using filesort ты вряд ли избавишся, потому, что ты сортируешь по вычисляемым значениям.

ты не хочешь создать суммарные таблицы за месяц, за год ...

попробуй
сделать 2 индекса qeih и (engine,id)
эффективность индексов проявляется, если твоя выборка составляет ~5% от общего числа записей

какие у тебя наиболее частые запросы? на них и ориентируйся ...

 

[camka]

А если сначала группировать по query и использовать фильтры на engine и id? Запихать все это во временную таблицу, а уже из неё получать 1000 нужных записей, сортируя по hits. Так мы избаляемся от filesort и using temporary (правда строим свою временную таблицу) в обоих этих запросах. Проблема в using where, то есть скан идет в любом случае.
[sql] desc select query, sum(hits) as hits from stat_fastsearch where engine in (1,2,4,5,6,7,8,9) and id between 20041111 and 20050101 group by query;
[/sql]

+-----------------+-------+---------------+------+---------+------+--------+--------------------------+
| table           | type  | possible_keys | key  | key_len | ref  | rows   | Extra                    |
+-----------------+-------+---------------+------+---------+------+--------+--------------------------+
| stat_fastsearch | index | id,engine_id  | qeih |      17 | NULL | 563374 | Using where; Using index |
+-----------------+-------+---------------+------+---------+------+--------+--------------------------+
1 row in set (0.07 sec)

я надеялся, что это можно поправить при помощи этого:

http://dev.mysql.com/doc/mysql/en/loose-index-scan.html

(
конкретно вот этого

The following queries provide several examples that fall into this category, assuming there is an index idx(c1, c2, c3) on table t1(c1,c2,c3,c4):
....
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;
)

, но, как видете, using where не убралось.

 

[chira]

если создаёшь временную таблицу, то это тоже самое, что Using temporary ...
здесь выигрыша никакого

а чем тебя пугает Using where?

 

[camka]

Using where пугает тем, что сканировани происходит по всем записям таблицы. Идеальным было бы решение, где один индекс используется и для фильтрации по engine и id, уменьшая тем самым количество сканируемых рядов, и одновременно для группировки, удаляя using temporary и using filesort. А на практике получается, что реально срабатывает только одно из двух.

То бишь, если сразу видно, что границы выборки по engine и id относительно малы и использование индекса даст ощутимый прирост в производительности оптимально было бы использовать сначала этот фильтр в индексе а потом группировку, также через индекс без using xxx.

P.S. Пока тестировал на своем сервере, оказалось, что размер таблицы на продакшн сервере вырос до полтора миллиона записей.

 

[chira]

пока у тебя будет order by по вычисляемому полю SUM(hits) тебе не избавиться от temporary и filesort

предложенный мной индекс содержит значения всех полей и поэтому MySQL-у не нужно дополнительно читать данные из таблицы.
как ты заметил, он хорошь при большой выборке.
недостаток, что MySQL проходиться просматривать вcе записи.
для малых выборок это будет занимать большее время, чем испрользование индекса engine_id

как насчёт создания сумарной таблицы?

 

[camka]

Автор оригинала: chira
пока у тебя будет order by по вычисляемому полю SUM(hits) тебе не избавиться от temporary и filesort

Не смотря на то, что так написано в мануале, на самом деле это не так!

mysql> alter table stat_fastsearch add index qh(query, hits);
Query OK, 1664177 rows affected (1 min 25.90 sec)
Records: 1664177  Duplicates: 0  Warnings: 0

mysql> desc select query , sum(hits) from stat_fastsearch group by query;
+-----------------+-------+---------------+------+---------+------+---------+-------------+
| table           | type  | possible_keys | key  | key_len | ref  | rows    | Extra       |
+-----------------+-------+---------------+------+---------+------+---------+-------------+
| stat_fastsearch | index | NULL          | qh   |       9 | NULL | 1664177 | Using index |
+-----------------+-------+---------------+------+---------+------+---------+-------------+
1 row in set (0.06 sec)

Смотри второй комментарий тут

.....

Ой, неверно прочитал. Мне показалось group by и sum(hits). Дак сортировать можно потом во временной таблице, предварительно запихав в нее сгруппированную информацию.

Тогда бы получлось, что с уменьшением границ выборки скорость бы _заметно_ увеличивалась.

А насчет сводных таблиц - надо обдумать. Придётся пускать крон ежедневно или же при первом запросе проверять, имеются ли сводные таблицы, и, если нет, то создавать их.

-~{}~ 05.05.05 16:53:

вернее, не таблици, а соответствующие записи

 

[Falc]

Позволю себе в мешатся в вашу дискусию.

Как я понимаю основная проблема - это тяжелые выборки за большой период и по большому кол-ву страниц. Поэтому, как сказал chira, нужно создать большой индекс по всем используемым в запросе полям, чтобы при тяжелых запросах шло обращение только к индексам, это ускорит такие запросы примерно в 2 раза. А также нужно создать простые индексы по тем полям по которым наиболее частые запросы.

Как вариант можно создать следующие индексы:
id,engine,query,hits
engine

Хотя если большинство запросов идут за весь период то скорее более эфективной окажется следующая комбинация:
engine,id,query,hits
id

По поводу temporary и filesort.
Если в запросе order by и group by идут по разным полям (выражениям), то MySQL будет создавать временую таблицу ( using temporary ). Ну а filesort используется в том случае если сортируемых данных больше чем может вместится в буфере сортировки, учитывая что у тебя кол-во записей перевалило за милион, то буфер сортировки увеличивать особого смысла нету.

Но на самом деле возня с этим запросом даст не значительный выигрыш в скорости. Основное тут решение - это уменьшение кол-во обрабатываемых данных. Тут есть 2 пути:
- удалять старые данные
- хранить промежуточные итоги
Оба этих питу ограничивают функциональность, но зато позваляют существенно повысить производительность.