Оптимизация таблицы содержащей около 2 миллионов записей

Большой offset зло. Сервак считывает ВЕСЬ оффсет, потом выплевывает limit.

Лучше использовать больше-меньше на индекс. К примеру, если у тебя это посты, то у них есть ид. Соответственно запрос не

select * from posts limit 30 offset 100500

select * from posts where post_id >= 100500 limit 30

Но это как вариант, каждый случай надо отдельно рассматривать.

Ага, а потом у тебя записи из области до post_id > 100500 удаляются и постраничный вывод превращается в гуано..

To TS: никогда не поверю, что выборка таблицы с хотя бы одним индексированным полем (которое к тому же primary key) на таком небольшом объеме данных может длиться так долго

Провёл эксперимент: таблица на 1 миллион записей. Первый запрос с limit 950000,30 выполнился за 4.5 секунды. Все последующие с любым limit ~0.5 секунды

CREATE TABLE `videos` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `site` varchar(255) NOT NULL,
  `site_id` bigint(20) unsigned NOT NULL,
  `site_url` varchar(255) NOT NULL,
  `title` text NOT NULL,
  `thumbnails` text NOT NULL,
  `duration_txt` varchar(32) NOT NULL,
  `duration` bigint(20) unsigned NOT NULL,
  `embed` text NOT NULL,
  `created_on` datetime NOT NULL,
  `status` enum('active','deleted') NOT NULL DEFAULT 'active',
  `views` bigint(20) unsigned NOT NULL DEFAULT '0',
  `rating` bigint(20) unsigned NOT NULL DEFAULT '0',
  `voted` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `site` (`site`),
  KEY `site_id` (`site_id`),
  KEY `site_url` (`site_url`),
  KEY `status` (`status`),
  KEY `created_on_id` (`created_on`,`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1797645 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED

Запрос `SELECT * FROM f_videos LIMIT 1782420, 30;` длится бесконечно долго. Что я делаю не так?

Что я делаю не так?

ENGINE=MyISAM

И Вы еще спрашиваешь?

К слову в постгресе, у меня по два мульена записей в сутки, ивсе это за год вполне себе выдается, даже на особокучерявых запросах. База уже больше 200гиг.

Ага, а потом у тебя записи из области до post_id > 100500 удаляются

Не удаляй, помечай как удаленные

MyISAM не стоит использовать. Плюс надо, как уже писали выше, избавляться от LIMIT. Ну и на конфиг дай посмотреть.

Вчитался, из всех индексов только 2 приемлемы - created_on_id и site_id. Архитектор наркоман, однозначно.

поле status - просто верх неадеквата, прямо таки на премию дарвина тянет.

Что не так с полема status? По поводу индексов я с ними экспериментировал поэтому много лишних. Скажите какие оставить. От лишних индексов запрос тормозится разве?

ENGINE=MyISAM

И Вы еще спрашиваешь?

Насколько я понимаю InnoDB ещё тормознее но поддерживает транзакции.

Насколько я понимаю InnoDB ещё тормознее

Ну что тут можно сказать?

Покажи show create table и explain на проблемный select. Есть три варианта: 1. В условии выборки (where) участвует поле, которого нет в индексе, поэтому большой offset по факту превращается в seq scan 2. Индекс банально не влезает в память 3. mysql - кривое глючное говно, а не база данных

судя по тому что я увидел, key_buffer уводит систему в жестокий своп. Я намекну - как думаешь, насколько операция varchar(255)==varchar(255) затратнее для процессора, чем операция int(11)==int(11) ? а уж enum(varchar,varchar) вместо enum(int,int), вообще не от мира сего. и даже не enum должен быть, а тупо int. Enum так или иначе тянет за собой дополнительную логику - он вообще не нужен. Ну и конфиг-то покажи плиз.

Что я делаю не так?

SELECT * FROM f_videos ORDER BY id LIMIT 1782420, 30

Покажи show create table

Оптимизация таблицы содержащей около 2 миллионов записей (комментарий)

mysql> EXPLAIN SELECT * FROM f_videos LIMIT 1782420, 30;
+----+-------------+----------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows    | Extra |
+----+-------------+----------+------+---------------+------+---------+------+---------+-------+
|  1 | SIMPLE      | f_videos | ALL  | NULL          | NULL | NULL    | NULL | 1782614 |       |
+----+-------------+----------+------+---------------+------+---------+------+---------+-------+
1 row in set (0.03 sec)

mysql> 

3. mysql - кривое глючное говно, а не база данных

В настоящее время занимаюсь конвертацией в PostgreSQL, о результатах сообщу.

ставлю на пункт 2.

Ну что тут можно сказать?

В твоем случае действительно лучше бы промолчать.

судя по тому что я увидел, key_buffer уводит систему в жестокий своп. Я намекну - как думаешь, насколько операция varchar(255)==varchar(255) затратнее для процессора, чем операция int(11)==int(11) ? а уж enum(varchar,varchar) вместо enum(int,int), вообще не от мира сего. и даже не enum должен быть, а тупо int. Enum так или иначе тянет за собой дополнительную логику - он вообще не нужен. Ну и конфиг-то покажи плиз.

Думал это предусмотрено и строки конвертируются в INT автоматически как в C. А так придётся запоминать какое число что значит.

[mysqld]
character-set-server        = utf8
user                        = mysql
port                        = 3306
socket                      = /var/run/mysqld/mysqld.sock
pid-file                    = /var/run/mysqld/mysqld.pid
log-error                   = /var/log/mysql/mysqld.err
basedir                     = /usr
datadir                     = /var/lib/mysql
skip-external-locking
key_buffer                  = 16M
max_allowed_packet          = 100M
table_cache                 = 64
sort_buffer_size            = 512K
net_buffer_length           = 8K
read_buffer_size            = 256K
read_rnd_buffer_size        = 512K
myisam_sort_buffer_size     = 8M
language                    = /usr/share/mysql/english

да хоть на оракл. 2кк записей - это даже не ясли для любой субд.

EXPLAIN SELECT * FROM f_videos LIMIT 1782420, 30

ORDER BY однозначно не хватает. В постгри будет точно так же.

key_buffer=256M net_buffer_length=64k read_buffer_size=1M read_rnd_buffer_size=1M

ну и на innodb-таки переходи.

4.2 на файрберде это адь, т.к. в myisam таблица в одном файле, прозреваю и там фокусы.

не надо ничего запоминать. Просто надо все, абсолютно все, кроме числовых примитивов выносить в соседние таблицы, а при выборке делать left outer join, и не тупить с индексами. В этом как бы и смысл реляционных баз. кроме того, это сократит физический размер базы конкретно у тебя раза в два.

Мой опыт говорит об обратном. Скорее всего проблема опять таки в логике.

а при выборке делать left outer join,

Точно? Не inner?

надо все, абсолютно все, кроме числовых примитивов выносить в соседние таблицы, а при выборке делать left outer join

Чтоб тебе в аду джойны руками делать. Нормализация нигде кроме учебников не живет. join = seek. О том, сколько seek'ов в секунду может сделать жесткий диск, спроси у гугла.

Ну да, давай мерятся длинной опыта 8) Ты еслиб не говорил _любой_ то у тебяб не сокращался опыт.

Убогий мускуль не в силах скешировать табличку в полсотни записей?

Убогий мускуль не в силах скешировать табличку в полсотни записей?

Чего ты собрался кешировать при связи 1 к 1, убогий?

Ну 2кк записей явно больше, чем какой бы то ни было вынесенный справочник, соответственно нечего тут оптимизировать.

У тебя траблы с пониманием текста.

join==seek? Все, выхожу из треда, тут профи явно лучше рассудят.

Ну ты веришь, что движек базы настолько умен? На практике у меня оракел показывал в аналогичных случаях проседание (тупая оптимизация запроса вложенным селектом сильно улучшала план), прозреваю, что мускуль будет стольже фееричен.

Я бы посоветовал тебе выходить заодно и из профессии, потому что понимание принципов работы базы данных у тебя где-то на уровне абстрактных секретарш, под которых SQL придумывался.

Ммм, хорошо сказано. Из того что я видел, вложенный селект - это почти всегда ухудшение производительности. Конкретно с мускулем вообще беда, он в статике (вьюхи) до сих пор вложенные селекты не понимает. Но вот с индексами работает именно так «умно».

Как скажешь, пойду устроюсь дворником.

Уточню.

select a.*, b.name
  from atable a
  join btable b on a.bid=b.id

-- оптимизируется в 
select a.*, 
       (select b.name from btable b where a.bid=b.id) as name
  from atable a
  

Очевидно если atable огромен, а btable - наносправочник

Если atable сильно больше btable, то такая оптимизация приведет только к потере эффективности. Это проход ALL по всему atable плюс количество запросов к btable == ALL, итого количество селектов овер 9000. Такая оптимизация видится немного не логичной, не?

Видится, это одно, а время выполнения запроса - другое. Речь идет именно о последнем.

Вот эти поля я бы вынес в отдельную таблицу -

  `title` text NOT NULL,
  `thumbnails` text NOT NULL,
  `embed` text NOT NULL,

Особенно если там большой объем данных.

key_buffer=256M net_buffer_length=64k read_buffer_size=1M read_rnd_buffer_size=1M

Помоло, завершается за ~10 секунд выборка.

ну и на innodb-таки переходи.

Сейчас этим занимаюсь, запустил alter table videos engine=innodb;

К слову в постгресе, у меня по два мульена записей в сутки, ивсе это за год вполне себе выдается, даже на особокучерявых запросах. База уже больше 200гиг.

Сконвертировал наконец то базу в PostgreSQL. И вот что получается:

b32=# \di
                     List of relations
 Schema |         Name         | Type  |  Owner   | Table  
--------+----------------------+-------+----------+--------
 public | videos_created_on    | index | postgres | videos
 public | videos_created_on_id | index | postgres | videos
 public | videos_pkey          | index | postgres | videos
(3 rows)

b32=# explain select * from videos order by id OFFSET 1505000 LIMIT 30;
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Limit  (cost=144603.26..144606.14 rows=30 width=509)
   ->  Index Scan using videos_pkey on videos  (cost=0.00..171276.94 rows=1782614 width=509)
(2 rows)

b32=# explain select * from videos order by created_on OFFSET 1505000 LIMIT 30;
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Limit  (cost=1964544.56..1964544.63 rows=30 width=509)
   ->  Sort  (cost=1960782.06..1965238.59 rows=1782614 width=509)
         Sort Key: created_on
         ->  Seq Scan on videos  (cost=0.00..142793.14 rows=1782614 width=509)
(4 rows)

b32=# explain select * from videos order by created_on, id OFFSET 1505000 LIMIT 30;
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Limit  (cost=1964544.56..1964544.63 rows=30 width=509)
   ->  Sort  (cost=1960782.06..1965238.59 rows=1782614 width=509)
         Sort Key: created_on, id
         ->  Seq Scan on videos  (cost=0.00..142793.14 rows=1782614 width=509)
(4 rows)

b32=# 

ORDER BY id с 'Index scan' выполняется довольно быстро. Однако ORDER BY created_on а также ORDER BY created_on DESC, id DESC (что собственно мне в идеале хотелось бы зависают надолго т.е. не дождался и зделал Ctrl+C). Несмотря на то что добавил индексы как для created_on так и для (created_on, id). В чём дело?

Sort Key: created_on

У тебя все сортируется, это ппц как долго, а потом уже выбирается

Так что делать? Почему не происходит то что описано здесь http://www.postgresql.org/docs/9.2/static/indexes-ordering.html и как оптимизировать? Я уже готов плюнуть на всё и написать свой простенький велосипед для данного частного случая.

возможно индексы чем то не по нраву планировщику надо смотреть как ты их создаешь и что за типы в таблице, вообще планировщик игнорирует индексы - когда запрашивается «все», некоторые субд также игнорируют индексы когда в select вместо перечисления полей используется * - но не помню грешит ли этим постгрес

SELECT id даёт такой же результат.

create index videos_created_on on videos (created_on);
create index videos_created_on_id on videos (created_on,id);

Конкретно какие шаги можно предпринять? С чего начать?

А покажи план выполнения

explain
  select * 
    from videos v
   where v.id>xxxx /*тут айдишник какой нить 1505000 записи после которой можно 30 отсчитать*/ 
   order by created_on, id LIMIT 30;

Если atable сильно больше btable, то такая оптимизация приведет только к потере эффективности. Это проход ALL по всему atable плюс количество запросов к btable == ALL, итого количество селектов овер 9000. Такая оптимизация видится немного не логичной, не?

Вполне логичная оптимизация, так как обычно используются всякие where, и вместо прохода по всему atable проходится только по нужным строкам.

Если ты будешь сортировать по created_on и без where - то работать будет в любом случае долго.
p.s. текстовые поля (которые text) вынеси в отдельную табицу - на больших таблицах дает хороший прирост производительности.