Вопроc к знатокам MySQL

Есть у меня табличка, в которой порядка 10 миллионов записей.

Код:

CREATE TABLE `queue` (   `id` int(12) NOT NULL auto_increment,   `taskid` smallint(5) NOT NULL,   `thread` tinyint(2) NOT NULL,   `expires` int(12) NOT NULL,   PRIMARY KEY  (`id`),   KEY `taskid` (`taskid`),   KEY `thread` (`thread`) ) ENGINE=MyISAM ;

Выборка осуществляется следующим образом:

Код:

SELECT * FROM `queue` WHERE taskid='$taskid' AND thread='$thread' AND expires<UNIX_TIMESTAMP();

Ну с taskid и thread понятно, они константы, поэтому сделать их ключевыми полями вполне логично. А вот expires, проверяется на диапазон значений. Поможет ли чем-то, если сделать его также ключевым? Или наоборот, это замедлит работу с таблицей? Ведь общеизвестно, что ключевые поля замедляют INSERT.

P.S. Скорость выполнения запросов на этой таблице критична. Всем +5.

Последний раз редактировалось: alex.raven (10/05/10 в 22:04), всего редактировалось 1 раз

Сделай один ключ на 2 поля сразу (taskid, thread).
Последнее условие /expires/ все равно будет перебором искать, даже если ты сделаешь поле expires тоже индексом. Имхо, только так. Оптимальный вариант.

На счет скорости - дело твоё, конечно, но 10М это маленькая таблица, тем более с такими полями. Не заморачивайся. Нормально всё будет с инсертами.

Да, для проверки, как используются индексы, не забудь использовать "explain"

Вообще с такими вопросами лучше на sql.ru иди. Тут тебе по многостолбцовым индексам насоветуют )

SELECT BENCHMARK (1000000, 'query');

Вообще 10 миллионов подобных записей - всего 300 мегабайт. Вполне влезет в память на нынешних серверах. Если настроить mysql - то можно в heap хранить это все дело, скорость доступа вообще совсем другого уровня будет. Я так понимаю по смыслу задачи, там персистентность данных не совсем обязательна?

Еще лучше было бы написать своего простенького демона на C специализированного, на которого всю задачу свалить по управлению очередью, который бы структуры все в памяти держал, еще быстрее в разы будет. Там можно просто стандартные библиотеки было бы использовать типа того же boost для организации очередей.

Или вообще писать не на php, а на нормальном языке

..ой, что-то понесло Остапа

Dr.Syshalt писал:

Вообще 10 миллионов подобных записей - всего 300 мегабайт. Вполне влезет в память на нынешних серверах. Если настроить mysql - то можно в heap хранить это все дело, скорость доступа вообще совсем другого уровня будет. Я так понимаю по смыслу задачи, там персистентность данных не совсем обязательна?

Да, кстати - отличная идея. На серваке оперативки 2 гб, так что должно хватить. Таблица пересоздаётся каждый день, так что да, персистентность данных не обязательна.

Dr.Syshalt писал:

Еще лучше было бы написать своего простенького демона на C специализированного, на которого всю задачу свалить по управлению очередью, который бы структуры все в памяти держал, еще быстрее в разы будет. Там можно просто стандартные библиотеки было бы использовать типа того же boost для организации очередей. Или вообще писать не на php, а на нормальном языке ..ой, что-то понесло Остапа

Ну кстати, к быстродействию PHP в данном случае претензий нет, данные обрабатываются скриптом, который выполняется 10-ю тредами (каждый тред выбирает данные, предназначенные для него, для этого threadid и служит).

По полям taskid и thread - где больше вариантов значений ?

есть вопросики
1. совершенно случайно, taskid+thread не является уникальным полем заменяющим primary key ?
2. expires - меняется со временем или задается единожды при вставке?
3. > А вот expires, проверяется на диапазон значений.
а разве это мешает использовать индекс? "...where field between 1024 and 3000..." замечательно работает, не говоря уже об упрощениях на <=, => ...

имхо стоит попробовать и так и сяк, если еще не опробовано

zuborg писал:

По полям taskid и thread - где больше вариантов значений ?

Код:

`taskid` smallint(5) NOT NULL, `thread` tinyint(2) NOT NULL,

Догадайся с трех раз

кстати, если все запросы к этой таблице только типа:
taskid='$taskid' AND thread='$thread'
то поля вообще можно скелить в одно ;)

Heavy писал:

есть вопросики 1. совершенно случайно, taskid+thread не является уникальным полем заменяющим primary key ?

нет, значения не уникальны, taskid для всей таблицы как правило, одинаково (оно увеличивается на 1 при создании следующей очереди). threadid изменяется от 1 до 10.

Heavy писал:

2. expires - меняется со временем или задается единожды при вставке?

только один раз - при вставке, записи, у которых expires<UNIX_TIMESTAMP() после обработки удаляются из таблицы. если в таблице `queue` больше не остаётся записей, то выполняется TRUNCATE TABLE `queue`, чтобы дефрагментировать таблицу и установить автоинкремент в 1.

3. > А вот expires, проверяется на диапазон значений.
а разве это мешает использовать индекс? "...where field between 1024 and 3000..." замечательно работает, не говоря уже об упрощениях на <=, => ...
имхо стоит попробовать и так и сяк, если еще не опробовано [/quote]

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

Heavy писал:

кстати, если все запросы к этой таблице только типа: taskid='$taskid' AND thread='$thread' то поля вообще можно скелить в одно ;)

ну да, так по идее быстрее будет обрабатываться, но в некоторых скриптах есть к примеру,

Код:

DELETE FROM `queue` WHERE taskid='$taskid'

alex.raven писал:

нет, значения не уникальны, taskid для всей таблицы как правило, одинаково (оно увеличивается на 1 при создании следующей очереди). threadid изменяется от 1 до 10.

тогда threadid в конец индекса статической части запроса:
CREATE INDEX idx1 ON queue (thread, taskid, expires);

alex.raven писал:

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

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

alex.raven писал:

ну да, так по идее быстрее будет обрабатываться, но в некоторых скриптах есть к примеру, Код: DELETE FROM `queue` WHERE taskid='$taskid'

поэтому тебе и виднее, как лучше оптимизацию сделать, и опробовать есть возможность ;)
а конкретно в данном примере, если поле строится как taskid+thread, то условие будет WHERE taskid_thread between ='$taskid00' and between ='$taskid99' , но опять же пробовать нужно и сравнивать

p.s. не актуально, пропустил ваш ответ ранее

Цитата:

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

на больше-меньше-максимальное-минимальное он тоже работает ;)

Всем спасибо за ценные советы. В результате был применён метод, который я уже использовал несколько лет назад для другой крупной таблицы (~250 миллионов записей). Суть метода состоит в следующем.

Так как таблица queue динамическая (пересоздаётся раз в сутки), то:

1. Движок для этой таблицы был сменён на MEMORY.
2. Таблица `queue` пересоздаётся заново (DROP TABLE/CREATE TABLE) БЕЗ ИНДЕКСОВ (т.к индексы замедляют INSERT).
3. Делается INSERT (расширенный, блоками по 5000 записей) необходимых данных.
4. Добавляются индексы через ALTER TABLE `queue` ADD INDEX ( `thread` ), etc (довольно быстро).

Получаем очень быстро работающую таблицу.

На всякий случай (если потом столкнешься с подобной проблемой, но которая не решается через engine=MEMORY).

Посмотри вот эту ссылку http://stackoverflow.com/questions/1691451/which-is-the-best-way-to…ified-date (это по поводу условия expires<UNIX_TIMESTAMP())

Мне помогло решить мою проблему с медленными выборками между определенными датами (timestamp храню в INT).

condom007 писал:

На всякий случай (если потом столкнешься с подобной проблемой, но которая не решается через engine=MEMORY). Посмотри вот эту ссылку http://stackoverflow.com/questions/1691451/which-is-the-best-way-to…ified-date (это по поводу условия expires<UNIX_TIMESTAMP()) Мне помогло решить мою проблему с медленными выборками между определенными датами (timestamp храню в INT).

Спасибо, но там timestamp переводится в дату, а у меня выборка идёт с точностью до секунд.

alex.raven писал:

Спасибо, но там timestamp переводится в дату, а у меня выборка идёт с точностью до секунд.

Никто не мешает дату указывать не в формате YYYY-MM-DD, а в формате YYYY-MM-DD H:i:s

alex.raven писал:

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

Нет. Если ты указываешь тип индекса как BTREE

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

То MySQL для MEMORY использует T-Tree - а они хорошо ищут и на больше-меньше.

condom007 писал:

Никто не мешает дату указывать не в формате YYYY-MM-DD, а в формате YYYY-MM-DD H:i:s

тогда уже в YYYYMMDDHHiiss ;) но в случае ТС, пересоздающего несколько раз в сутки таблицу, имхо, год, месяц и день можно и откинуть, тем самым сократив поле до int(6), а если перейти в количество секунд, отчисляемых от начал дня, то и того меньше, но сомневаюсь, что это даст выйгрыш в занимаемом месте или скорости.

Кстати, alex.raven, а нет возможности создавать таблицу чаще, но с меньшим количеством строк? т.е. планировать ваши таски не "на сутки", а на "час", например - и таблица меньше будет и выборка возможно быстрее...

alex.raven, а у тебя индекс на поле expire стоит?

Что-то мне подсказывает, что задача, которую ты решаешь очень просто решается с помощью memcache или redis, где ты можешь создавать ключи с данными и указывать им expire период. Если ключ не проэкспайрился (это происходит прозрачно для тебя), то ты берешь оттуда данные, а если проэкспайрился, то ф-ия просто скажет о том, что ключ не найден.

Например, ключ называешь {task_id}.{thread}, пишешь туда 1 и ставишь expire на нужный период.

Почитай про memcache/redis - жизнь себе облегчишь мгновенно + забудешь про нагрузку и время запросов на мускуле (увы, лишь иногда и частично ).

condom007 писал:

Например, ключ называешь {task_id}.{thread}, пишешь туда 1 и ставишь expire на нужный период.

Он же писал, что ему нужно иногда только по taskid удалять.

Dr.Syshalt писал:

Он же писал, что ему нужно иногда только по taskid удалять.

Не досмотрел. Тогда определенно redis

Heavy писал:

Кстати, alex.raven, а нет возможности создавать таблицу чаще, но с меньшим количеством строк? т.е. планировать ваши таски не "на сутки", а на "час", например - и таблица меньше будет и выборка возможно быстрее...

к сожалению, это не сработает, т.к очередь может создаваться на 6, 12, 24 часа и до 60 дней (в зависимости от задач)

condom007 писал:

alex.raven, а у тебя индекс на поле expire стоит? Что-то мне подсказывает, что задача, которую ты решаешь очень просто решается с помощью memcache или redis, где ты можешь создавать ключи с данными и указывать им expire период.

memcache у нас используется в большинстве проектов, в том числе и в этом. к сожалению, по expires запись не только удаляется, но и обрабатывается, так что только делать выборку из базы по этому полю.

alex.raven писал:

к сожалению, это не сработает, т.к очередь может создаваться на 6, 12, 24 часа и до 60 дней (в зависимости от задач)

так хоть на год вперед , можно же по этой очереди делать оперативный срез в другую таблицу по планам на Н-часов вперед - все ж меньше работы мускулю будет ), только гемора программисту больше