Вопрос по индексам MySQL?

Question

[Hint]

Большая таблица (миллионы записей). Строки — задания на обработку. При добавлении записи флаг «обработан» устанавливается в 0. Потом отдельный сервис обрабатывает записи и меняет флаг на 1. Записи не удаляются, флаг из состояния 1 никогда не переходит в состояние 0. По полю флага установлен индекс. Требуется запоминать время, когда файл был обработан (unix_timestamp в int).

Вопрос, можно ли убрать флаг из таблицы и делать выборку только по времени обработки (processed_time = 0)? Или же индекс по полю флага 0/1 будет работать эффективнее, чем по полю с int?

Answer 1

[Vitali Borovik]

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

Comments

[Skpd]

Мне тоже кажется идея с разделением на таблицы хорошей, яростно плюсую =)

[Hint]

Нет. Разделение на таблицы не подходят. Нужен уникальный id для каждой записи с инкрементом по которому можно ссылаться до и после обработки. Разбиение сильно усложнит логику.

Answer 2

[zuborg]

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

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

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

Надо тестить, в общем…

Answer 3

[Виталий Желтяков]

Индекс по полю с двумя значениями неэффективен изначально.

Comments

[Hint]

Почему? Если со значением 0 у меня около 10 миллионов записей, а со значением 1 около сотни. Мне нужно выбрать эти 100 со значением 1. Индекс будет неэффективен?

[Виталий Желтяков]

Индексы эффективны на уникальных полях или с ограниченным количеством строк (LIMIT).
У Вас же движок СУБД будет просматривать всю таблицу индексов — выигрыша в скорости практически никакой, но при этом время на изменение индекса практически 2 раза больше.

[Hint]

И не играет роли то, что с одним значением у меня 99.99% записей, а с другим 0.01% (и по нему я буду выбирать)?
В итоге, индекс по processed_time и выборка по нему (processed_time = 0) будет эффективнее (при том, что 99.99% записей будут содержать практически уникальные ненулевые значения, повышая cardinality)?

[Виталий Желтяков]

Откуда движку знать, что он нашел все 0.01%-х нужных строк? Он тупо просмотрит всю таблицу индексов. По скорости это ничем не отличатся от просмотра таблицы без этого индекса.

Тоже самое и с полем processed_time.

Эффект от использования индексов будет, если Вы ищите конкретные значения или используете LIMIT, точно зная, что «LIMIT <= подходящих_строк».

[Hint]

> Тоже самое и с полем processed_time.
Почему? Поле processed_time имеет столько уникальных значений, сколько строк в таблице. Я ищу по конкретному значению 0.

[Виталий Желтяков]

Под уникальным полем в SQL подразумевается поля без повторяющихся значений. У Вас же значение 0, как я понимаю, повторяется.

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

[Hint]

По-моему вы не правы. Взял таблицу с комментариями. В ней есть поле warn (int) с индексом (на момент теста всего 3 разных значения).

SELECT warn, COUNT(*) FROM `comments` GROUP BY warn

warn	COUNT(*)
-1	3792
0	529637
137888	3

SELECT * FROM `comments` WHERE warn = 0

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	comments	ALL	warn	NULL	NULL	NULL	533432	Usingwhere

SELECT * FROM `comments` WHERE warn = 137888

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	comments	ref	warn	warn	4	const	3732

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

[Виталий Желтяков]

И что ваш пример значит? Что 3 строки выбираются быстрее, чем 529637?

[Виталий Желтяков]

Самостоятельно провел тестирование. Я оказался не прав.

Результаты моего исследования ниже в комментариях.

Answer 4

[Григорий Перетяка]

Тут есть мнения с которыми я не совсем согласен. Давайте, разберем что такое индекс в упрощенной форме.

Если индекс на поле не установлен, то он проходит по всем записям и сравнивает, если флаг = 1, то выбрать запись. При миллионе записей будет миллион таких сравнений.

Если вы поставите индекс, то он сократит список до уникальных значений и он будет следующего вида:
0 — строки, где запись равна 0
1 — строки, где запись равна 1

То есть, сравнений будет всего 2.

Если вы объедините поля, то в индексе получится 999 900 уникальных временных значений и одно — 0, соответствующие 100 записям то есть будет 999 901 сравнение.

(если поле вообще уникальное, то выигрыш в том, что он остановит поиск после первого найденного элемента)

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

Насколько вообще существенно сравнение интежеров даже помноженное на миллион я не берусь судить, как и об увеличении обращений к жесткому диску. Но это будет однозначно дольше, вопрос только насколько, тут уже надо тестировать.

Другая ситуация с NULL. IS NULL — всегда будет работать без сравнения вообще. Тут я бы так и сделал.

Comments

[Hint]

Спасибо. А то я уже начал думать, что мое представление об индексах был в корне неверным.
В итоге, использование processed_time с возможным значением NULL и поиск по IS NULL — лучшее решение?

[Григорий Перетяка]

Сложно сказать что лучшее. Есть ли выборки по полю с unix_timestamp, насколько часты вставки/апдейты по отношению к селекстам, другие нюансы? Я отписался только потому, что там товарищ слишком уверенно не совсем правильную информацию давал. А так сходу сказать как лучше — это пальцем в небо.

Я бы, наверное, сделал с NULL, потому, что так лаконичнее выглядит и вполне производительное решение, на небольших таблица, вроде вашей, вряд ли будет тормозить. Но если бы было так как у вас сейчас, то не переделывал бы. Текущий вариант вполне жизнеспособен и, возможно, даже правильнее.

Answer 5

[Vampiro]

Я бы для оптимизации этой таблички сделал разбивку на PARTITIONS
либо по датам,
CREATE TABLE demo (col1 INT, tasks VARCHAR(255), col3 DATE)
PARTITION BY KEY(col3)
PARTITIONS 4;
либо по полю обработки
PARTITION BY LIST (id) (
PARTITION r0 VALUES IN (0),
PARTITION r1 VALUES IN (1),

Индекс по 0/1 быстрый, поскольку нужные вам записи хранятся первыми же в HASH (а я искренне надеюсь, что вы сделали HASH индекс, а не BTREE)

Answer 6

[Виталий Желтяков]

Провел тестирование:
— таблица с 1 000 000 строк, движок innobd;
— Структура полей id, flag (TINYINT), time(timestamp), data(char);
— У поля flag два возможных значения;
— 10 строк имеют отличное от всех значение поля flag;
— Кэширование запросов отключено.

Итог:
— Индексы действительно помогают отсеивать результаты даже, если значений мало. У меня получалось без индексов среднее время 1,89 сек., а с индексами — 0,015 сек.
— Использование NULL в качестве одного из значений дало прирост скорости 0,005 сек. на миллион записей без индексов, с индексами прирос слишком мал.

Answer 7

[Fastto]

Мне кажется будет эффективен следующий вариант (если не ошибаюсь, подобное решение в тасках симфони):
не использовать ни флаг ни время для выборки, а просто вторым, обрабатывающим сервисом «запоминать» последний успешно обработанный идентификатор, а выборку делать
where ID > @yourLastCalcedID

Это будет наиболее эффективно, естественно, если обработка сервисом протекает последовательно, линейно ID за ID, без пропусков и ветвлений между элементами обработки

Answer 8

[Artyushov]

если у вас поле принимает ограниченное количество значений, (enumeration), то по нему будет построена битовая шкала. То есть для каждого значения будет храниться вектор из 0 и 1 длиной в количество записей. Думаю дальше понятно.
Таким образом можно сразу получить номера записей с данным значением поля.

Answer 9

[hom9k]

Скорее всего разницы никакой не будет, поскольку и в том и в другом случае количество записей с флаг =0 или processed_time = 0 одинаковое. Индекс на processed_time только позволит намного быстрее делать статистические запросы с количеством обработанных заданий во временных промежутках (нужно ли вам это?).
У индекса есть такое значение как cardinality (дословно с англ: «мощность»). И чем ближе значение этого свойства к общему количеству записей — тем быстрее работает поиск по индексу.

Comments

[Hint]

Статистических запросов не будет. Будет только выбор всех записей со значением processed = 0 (или processed_time = 0) с последующей установкой processed в 1 (или processed_time в time()).