|
MySQL.RU - Webboard
 Вернуться
 До-о-олгая выборка (Георгий) 18/08/2004 - 13:19:38
 Re: До-о-олгая выборка (простохуй) 18/08/2004 - 14:20:23
Re: До-о-олгая выборка (Георгий) 18/08/2004 - 15:56:24
Re: До-о-олгая выборка (простохуй) 18/08/2004 - 16:17:45
> Original message text:
> From: Георгий - 18/08/2004 - 13:19:38
> Subject:До-о-олгая выборка
> -----------------
> Столкнулся я с проблемой, решить которую мне пока, увы, не удалось. Был бы чрезвычайно благодарен за подсказки в каких направлениях копать...
>
> В тексте сообщения приводятся куски кода, позволяющие сгенерировать тестовые записи. Если вдруг кому не лень будет...
>
> Ситуация:
>
> Имеются некие записи (например, статьи). Каждая запись принадлежит одной или более рубрик. Рубрик мало (несколько десятков), записей много (сотня-другая тысяч). Запись имеет поле "дата создания". Некоторые рубрики имеют очень мало записей, другие, наоборот, много.
>
> Задача:
>
> Запихать записи и рубрики в базу и написать запросы, которые бы возвращали несколько десятков последних по дате создания записей для указанной рубрики. То бишь - получить для каждой рубрики список последних статей.
>
> Единственный пришедший мне в голову вариант, как это организовать в базе:
>
> Таблица записей:
>
> CREATE TABLE a_item (
> id int(10) NOT NULL default '0',
> date datetime NOT NULL default '0000-00-00 00:00:00',
> title char(255) NOT NULL default '',
> UNIQUE KEY id (id),
> KEY main (id,date)
> ) TYPE=InnoDB;
>
> Таблица рубрик:
>
> CREATE TABLE a_rubric (
> id int(10) NOT NULL auto_increment,
> title char(255) NOT NULL default '',
> PRIMARY KEY (id)
> ) TYPE=InnoDB;
>
> Связывающая их таблица:
>
> CREATE TABLE a_itemrubric (
> idItem int(10) NOT NULL default '0',
> idRubric int(10) NOT NULL default '0',
> KEY idItem (idItem),
> KEY idRubric (idRubric)
> ) TYPE=InnoDB;
>
> Если есть мысли как сформировать таблицы по-другому - буду рад их выслушать.
>
> Заполняем таблицу рубрик:
>
> #!/usr/bin/perl
> use strict;
> use DBI;
>
> my $pdb = DBI->connect ( "DBI:mysql:db_name", 'login', 'password' );
> my $i; for ( $i = 1; $i <= 20; $i++ ) {
> $pdb->do( "INSERT INTO a_rubric VALUES($i,'Заголовок номер $i')" );
> }
> $pdb->disconnect;
>
>
> Заполняем таблицу записей и связывающую таблицу. Не забываем о том, что для одних рубрик записей мало, а для других - много.
>
> ------------
> #!/usr/bin/perl
> use strict;
> use DBI;
>
> my $pdb = DBI->connect ( "DBI:mysql:db_name", 'login', 'password' );
>
> my $idItem; for ( $idItem = 1; $idItem <= 100000; $idItem++ ) {
> print "$idItem\n" if ( ( $idItem % 500 ) == 0 );
> # Случайная дата
> my $day = sprintf "%02d", int (rand(27.1) + 1);
> my $mon = sprintf "%02d", int (rand(11.1) + 1);
> my $yea = sprintf "%04d", int (rand(2.1) + 2002);
> my $hou = sprintf "%02d", int (rand(23.1));
> my $min = sprintf "%02d", int (rand(59.1));
> my $sec = sprintf "%02d", int (rand(59.1));
> my $date = "${yea}-${mon}-${day} ${hou}:${min}:${sec}";
>
> $pdb->do( "INSERT INTO a_item VALUES('$idItem', '$date','Заголовок номер $idItem')" );
>
> # Для каждой записи - от одной до 4х рубрик
> # Если разобраться - количество записей для рубрик получается действительно
> # неравномерным. Разбираться необязательно :)
> my $nRubric = int ( rand(3.1) + 1); # Количество рубрик (от 1 до 4х)
> my $j; for ( $j = 0; $j < $nRubric; $j++ ) {
> my $rnd = int rand ( 4.1 ); # Случайное число от 1 до 4х
> my $idRubric = $j*5 + $rnd + 1; # Число от 1 до 20
> $pdb->do( "INSERT INTO a_itemrubric VALUES('$idItem', '$idRubric')" );
> }
>
> }
> $pdb->disconnect;
> ------------
>
> Замечательно. Для рубрики номер 20 - 60 записей, для рубрики номер 1 - без малого 25000.
>
> Первый запрос для выборки, пришедший мне в голову ('1' - тут задаем идентификатор рубрики):
>
> SELECT i.id,i.date,i.title
> FROM a_item i, a_rubric r, a_itemrubric ir
> WHERE
> r.id=1
> AND ir.idRubric=r.id
> AND ir.idItem=i.id
> ORDER BY i.date DESC
> LIMIT 20
>
> Для рубрики 20 время выполнения запроса - 0.02 секунды. Для рубрики 1 - около 2х секунд. Т.е. при большом количестве записей для данной рубрики время выборки недопустимо велико.
>
> EXPLAIN показывает необходимость filesort, который, похоже, и замедляет процесс. Если убрать ORDER BY - filesort исчезает. Т.е., если я не ошибаюсь, налицо описанная в документации особенность - неспособность MySQL обрабатывать ORDER BY по индексу, если индекс - не по полям первой связываемой таблицы.
>
> table type possible_keys key key_len ref rows Extra
> r const PRIMARY PRIMARY 4 const 1 Using temporary; Using filesort
> ir ref idItem,idRubric idRubric 4 const 46278
> i eq_ref id,main id 4 ir.idItem 1
>
>
> Второй запрос. На первое место выводим таблицу a_item, чтоб ORDER BY мог обработаться по индексу:
>
> SELECT i.id,i.date,i.title
> FROM a_item i
> LEFT JOIN a_itemrubric ir ON i.id=ir.idItem
> LEFT JOIN a_rubric r ON ir.idRubric=r.id
> WHERE
> r.id=20
> ORDER BY i.date DESC
> LIMIT 20
>
> Получаем время запроса порядка 0.8 секунды для первой рубрики (т.е. примерно в два раза быстрее). И... 15 секунд для рубрики 20 (с малым числом записей).
>
> Как видим, несмотря ни на что, filesort сохранился.
>
> table type possible_keys key key_len ref rows Extra
> i ALL NULL NULL NULL NULL 98109 Using filesort
> ir ref idItem idItem 4 i.id 1
> r eq_ref PRIMARY PRIMARY 4 ir.idRubric 1 where used; Using index
>
> Если для a_item добавить ключ на поля date,id время запроса сокращается до 6 секунд.
>
> SELECT i.id, i.date, i.title
> FROM a_item i
> USE INDEX ( main )
> LEFT JOIN a_itemrubric ir ON i.id = ir.idItem
> LEFT JOIN a_rubric r ON ir.idRubric = r.id
> WHERE r.id = 20
> ORDER BY i.date DESC
> LIMIT 20
>
> При этом от filesort мы, правда, избавились, но сильно легче, увы, не стало.
>
> EXPLAIN говорит следующее (ключ был создан как PRIMARY):
>
> table type possible_keys key key_len ref rows Extra
> i index NULL PRIMARY 12 NULL 93315
> ir ref idItem idItem 4 i.id 1
> r eq_ref PRIMARY PRIMARY 4 ir.idRubric 1 where used; Using index
>
> Попытка поменять таблицы местами и начинать присоединение с rubric ни к чему хорошему не приводит - упираемся в тот же filesort - см. первый запрос, который без joinов. Время запроса для рубрик с большим количеством записей - от 4х секунд.
>
> SELECT i.id, i.title, r.id, r.title
> from a_rubric r
> left join a_itemrubric ir ON r.id=ir.idRubric
> LEFT JOIN a_item i ON ir.idItem=i.id
> WHERE
> r.id=1
> ORDER BY i.date DESC;
>
> table type possible_keys key key_len ref rows Extra
> r const PRIMARY PRIMARY 4 const 1 Using temporary; Using filesort
> ir ref idRubric idRubric 4 const 372
> i eq_ref id,main id 4 ir.idItem 1
>
> На этом фантазия пока иссякла.
>
From: простохуй - 18/08/2004 - 14:20:23
Subject:До-о-олгая выборка
-----------------
в таком случае имеет смысл применить сознательную денормализацию. раз у нас узкое место order by date то давайте эту date перенесем в таблицу отношений рубрика-статья, ну и попутно выкинем из запроса всякое упоминание о таблице рубрик потому как в данном случае она нам нахрен не нужна (хотя там и eq_ref => 1, но все же).
получаем:
SELECT i.id,ir.date,i.title FROM a_item i,a_itemrubric ir WHERE ir.idRubric=1 AND ir.idItem=i.id order by ir.date desc LIMIT 20;
20 rows in set (0.14 sec)
SELECT i.id,ir.date,i.title FROM a_item i,a_itemrubric ir WHERE ir.idRubric=20 AND ir.idItem=i.id order by ir.date desc LIMIT 20;
20 rows in set (0.01 sec)
ранее было:
SELECT i.id,i.date,i.title FROM a_item i,a_itemrubric ir WHERE ir.idRubric=1 AND ir.idItem=i.id order by i.date desc LIMIT 20;
20 rows in set (0.51 sec)
SELECT i.id,i.date,i.title FROM a_item i,a_itemrubric ir WHERE ir.idRubric=20 AND ir.idItem=i.id order by i.date desc LIMIT 20;
20 rows in set (0.01 sec)
смотрим чтоже изменилось?
explain SELECT i.id,ir.date,i.title FROM a_item i,a_itemrubric ir WHERE ir.idRubric=1 AND ir.idItem=i.id order by ir.date desc LIMIT 20;
+-------+--------+-----------------+----------+---------+-----------+-------+----------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+--------+-----------------+----------+---------+-----------+-------+----------------------------+
| ir | ref | idItem,idRubric | idRubric | 4 | const | 24491 | where used; Using filesort |
| i | eq_ref | id,main | id | 4 | ir.idItem | 1 | |
+-------+--------+-----------------+----------+---------+-----------+-------+----------------------------+
explain SELECT i.id,i.date,i.title FROM a_item i,a_itemrubric ir WHERE ir.idRubric=1 AND ir.idItem=i.id order by i.date desc LIMIT 20;
+-------+--------+-----------------+----------+---------+-----------+-------+---------------------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+--------+-----------------+----------+---------+-----------+-------+---------------------------------------------+
| ir | ref | idItem,idRubric | idRubric | 4 | const | 24491 | where used; Using temporary; Using filesort |
| i | eq_ref | id,main | id | 4 | ir.idItem | 1 | |
+-------+--------+-----------------+----------+---------+-----------+-------+---------------------------------------------+
как видим файлсорт никуда не исчез, но зато при сортировке более не используется временная таблица, отсюда и выигрыш в 4 раза.
избавляться от файлсорта мне уже лениво, а может оно и ни к чему -- на 25 тыс. строк (по эксплайну) результат вполне себе нормальный.
[Это сообщение - спам!]
Последние сообщения из форума
Уважаемые посетители форума MySQL.RU!
Убедительная просьба, прежде чем задавать свой вопрос в этом форуме, обратите внимание на разделы:
- ответы на наиболее часто задаваемые вопросы - FAQ
- раздел документация
- раздел поиск по сообщениям форума и документации
Также, старайтесь наиболее подробно указывать свою ситуацию (версию операционной системы, версию MySQL,
версию программного обеспечения, по которому возникает вопрос, текст возникающих ошибок, и др.)
Помните, чем конкретнее Вы опишете ситуацию, тем больше шансов получить реальную помощь.
16701
|
|
