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

revis0r · 30.08.2011 13:44:34

Добрый день.
Знаю, подобные вопросы уже поднимались, но для себя ответа так и не нашел.
Есть таблица, в которой в данный момент около 7 млн записей. Позже она будет быстро расти.

Структура таблицы:

CREATE TABLE `pages` (

 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

 `domain_id` bigint(20) unsigned NOT NULL DEFAULT '0',

 `title` varchar(256) DEFAULT NULL,

 `url` varchar(2048) NOT NULL DEFAULT '/',

 `pr` int(11) DEFAULT NULL,

 `index_date` int(11) NOT NULL DEFAULT '0',

 `depth_level` int(11) DEFAULT NULL,

 `ya_direct` varchar(100) DEFAULT NULL,

 `google_adsense` varchar(100) DEFAULT NULL,

 `begun` varchar(100) DEFAULT NULL,

 `clear_chars` int(11) DEFAULT NULL COMMENT 'Количество символов на странице',

 `location` bigint(20) DEFAULT NULL,

 `index` tinyint(1) NOT NULL DEFAULT '1',

 `rcode` int(11) DEFAULT NULL,

 `ctype` varchar(45) DEFAULT NULL,

 `noindex_reason` int(11) DEFAULT '0',

 PRIMARY KEY (`id`),

 KEY `domain_id` (`domain_id`),

 KEY `location` (`location`),

 KEY `url` (`url`(767)),

 KEY `index_date` (`index_date`),

 KEY `id` (`index`,`index_date`,`domain_id`,`url`(767),`id`),

 CONSTRAINT `p_domain_id1` FOREIGN KEY (`domain_id`) REFERENCES `domains` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE=InnoDB AUTO_INCREMENT=6794387 DEFAULT CHARSET=cp1251

Нужно выполнить вот такой запрос:

SELECT  `domain_id` ,  `url` ,  `id` 

FROM  `pages` 

WHERE  `index` =1

GROUP BY  `domain_id` 

ORDER BY  `index_date` ASC

LIMIT 100

Сейчас он выполняется около полутора минут. Если убрать group by или order by, то тысячные доли секунды.
Вот что показывает explain:

mysql> explain SELECT  `domain_id` ,  `url` ,  `id` 

    -> FROM  `pages` 

    -> WHERE  `index` =1

    -> GROUP BY  `domain_id` 

    -> ORDER BY  `index_date` ASC

    -> LIMIT 100;

+----+-------------+-------+-------+---------------+------------+---------+------+------+------------------------------+

| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra                        |

+----+-------------+-------+-------+---------------+------------+---------+------+------+------------------------------+

|  1 | SIMPLE      | pages | index | id            | index_date | 4       | NULL |  719 | Using where; Using temporary | 

+----+-------------+-------+-------+---------------+------------+---------+------+------+------------------------------+

Второй день бьюсь, никак не могу подобрать составной ключ.
Версия mysql: 6.0.6-alpha. Надеюсь дело не в альфе, а в мозгах

rgbeast · 30.08.2011 20:38:15

Логика запроса не очень понятна. После группировки мы имеем группы по domain_id, в каждой группе имеются представители с разными index_date. Какой смысл имеет сортировка по index_date?

paulus · 31.08.2011 10:07:03

Но если, вообще говоря, включить режим «капитан-отвечает-только-на-заданный-вопрос»,
то должно хватить индекса на (`index`, `domain_id`, `index_date`).

revis0r · 31.08.2011 11:43:11

Смысл здесь такой. Мне нужно выбрать страницы, которые давно не индексировались - имеют наименьший index_date. (пробовал и GROUP by `domain_id` ORDER by MIN(`index_date`)) Т.е. по index_date выстраивается очередь из страниц. От каждого домена нужно по одной странице.

Вообще запрос нужен намного сложнее, что-то вроде такого:

SELECT 

    `di`.`ip` AS  `remote_ip` ,  `li`.`ip` AS  `local_ip` ,

    `d`.`domain` ,  `p`.`url` ,  `p`.`id` AS  `page_id` , 

    `nla`.`network_id` ,  `li`.`id` AS  `local_ip_id`, `d`.`id` AS `domain_id`

FROM  `domains` AS  `d` 

INNER JOIN  `pages` AS  `p` ON  `d`.`id` =  `p`.`domain_id` 

INNER JOIN  `domains_ip` AS  `di` ON  `di`.`domain_id` =  `d`.`id` 

INNER JOIN  `network_last_access` AS  `nla` ON  `nla`.`network_id` =  `di`.`network_id` 

INNER JOIN  `local_ip` AS  `li` ON  `nla`.`local_ip_id` =  `li`.`id` 

WHERE 

`d`.`index` =1

AND  `nla`.`last_access` <9999

AND  `p`.`index` =1

GROUP by `domain_id`

ORDER BY  `p`.`index_date` ASC 

LIMIT 100

Смысл такой - выбрать страницы, которые стоят первые в очереди + это должны быть страницы доменов, для которых уже получены IP-адреса + эти IP-адреса из подсетей, к которым давно не обращались с данного локального IP-адреса.
Но его нужно переписать, потому что выполняется он около 3-х минут.

Но если, вообще говоря, включить режим «капитан-отвечает-только-на-заданный-вопрос»,
то должно хватить индекса на (`index`, `domain_id`, `index_date`).

Не хватило, выполняется 70 секунд. EXPLAIN говорит то же самое.
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE pages index id index_date 4 NULL 286 Using where; Using temporary

П.С. Но спасибо за ответы )

paulus · 01.09.2011 23:16:21

Ну не правда. Было 700 строк, стало 300. Это больше, чем в два раза выигрыш
Хотя, конечно, поисковик так не напишешь.

Я правильно понимаю, что это не столько таблица страниц, сколько журнал таблицы
страниц. И каждый раз, когда страница индексируется, она добавляется в этот жур-
нал?

Попробуйте сначала написать запрос так, чтобы он содержал осмысленные значения.
Например, запрос с группировкой требует агрегатных функций над полями, не участ-
вующими в группировке (иначе выбирается *какое-то* значение, но не факт, что то,
которое нужно). Я имею в виду в первую очередь index_date.

Ну и, разумеется, начинайте думать над денормализацией. Когда работаете с таким
журналом, в любом случае нужно будет делать где-то денормализованную табличку.

revis0r · 05.09.2011 21:49:18

Ну не особо ощущается этот выигрыш

paulus написал:
Я правильно понимаю, что это не столько таблица страниц, сколько журнал таблицы
страниц. И каждый раз, когда страница индексируется, она добавляется в этот жур-
нал?

Именно так..

paulus написал:
Ну и, разумеется, начинайте думать над денормализацией. Когда работаете с таким
журналом, в любом случае нужно будет делать где-то денормализованную табличку.

Да... Тут вообще по-хорошему нужна не одна СУБД и не я, а нормальный администратор БД, чтобы помог правильно настроить все
Пока выкрутился 2-мя временными таблицами, чтобы запросы попроще и побыстрее были. Но как-то криво...

paulus написал:
Попробуйте сначала написать запрос так, чтобы он содержал осмысленные значения.
Например, запрос с группировкой требует агрегатных функций над полями, не участ-
вующими в группировке (иначе выбирается *какое-то* значение, но не факт, что то,
которое нужно). Я имею в виду в первую очередь index_date.

А можно подробнее? Что значит осмысленные значения? Первый запрос уже не так важен, появился новый
http://pix.am/oPqh/
Но легче будет с первым разобраться. Чтобы EXPLAIN наконец сказал "using index". Все-таки не понимаю как ключи подбираются.
http://mysqldba.blogspot.com/2008/06/ho … y-and.html
Здесь нашел мою ситуацию, попробовал аналогично подобрать ключ, не получилось..

Спасибо.

Отредактированно revis0r (06.09.2011 02:30:52)

paulus · 06.09.2011 22:05:24

Осмысленные значения — это очень просто. Представьте, что у Вас есть
корзина с фруктами. Группировка выбирает тип фрукта (например, груша,
слива, виноград). Неосмысленный запрос: «выбери тип фрукта и его размер
из этой корзины». Осмысленный запрос: «выбери тип фрукта и максималь-
ный его размер из этой корзины».

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

#1 30.08.2011 13:44:34

Оптимизация запроса с ORDER by и GROUP by

#2 30.08.2011 20:38:15

Re: Оптимизация запроса с ORDER by и GROUP by

#3 31.08.2011 10:07:03

Re: Оптимизация запроса с ORDER by и GROUP by

#4 31.08.2011 11:43:11

Re: Оптимизация запроса с ORDER by и GROUP by

#5 01.09.2011 23:16:21

Re: Оптимизация запроса с ORDER by и GROUP by

#6 05.09.2011 21:49:18

Re: Оптимизация запроса с ORDER by и GROUP by

paulus написал:

paulus написал:

paulus написал:

#7 06.09.2011 22:05:24

Re: Оптимизация запроса с ORDER by и GROUP by

Board footer