Задать вопрос

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

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

Вопрос, можно ли убрать флаг из таблицы и делать выборку только по времени обработки (processed_time = 0)? Или же индекс по полю флага 0/1 будет работать эффективнее, чем по полю с int?
  • Вопрос задан
  • 4289 просмотров
Подписаться 11 Оценить Комментировать
Пригласить эксперта
Ответы на вопрос 9
Наверное в случае такого количества данных я бы сделал отдельную таблицу (архив) куда бросал все обработанные записи, если они никогда не станут 0, смысл на них тратить время. И не нужны никакие индексы вообще.
Ответ написан
@zuborg
Тут сложно сказать однозначно, есть разные взаимокомпенсирующие эффекты.
С одной стороны, можно избавиться от ненужного больше поля с флагом — это плюс, с другой — придется добавить индекс по полю с таймстампом, которое занимает уже не один байт, как предыдущее — это минус (индекс будет большое занимать памяти). Сами индексы будут работать одинаково эффективно для указаной выборки.

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

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

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

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

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

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

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

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

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

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

Другая ситуация с NULL. IS NULL — всегда будет работать без сравнения вообще. Тут я бы так и сделал.
Ответ написан
@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)
Ответ написан
Комментировать
Провел тестирование:
— таблица с 1 000 000 строк, движок innobd;
— Структура полей id, flag (TINYINT), time(timestamp), data(char);
— У поля flag два возможных значения;
— 10 строк имеют отличное от всех значение поля flag;
— Кэширование запросов отключено.

Итог:
— Индексы действительно помогают отсеивать результаты даже, если значений мало. У меня получалось без индексов среднее время 1,89 сек., а с индексами — 0,015 сек.
— Использование NULL в качестве одного из значений дало прирост скорости 0,005 сек. на миллион записей без индексов, с индексами прирос слишком мал.
Ответ написан
Комментировать
Fastto
@Fastto
Мне кажется будет эффективен следующий вариант (если не ошибаюсь, подобное решение в тасках симфони):
не использовать ни флаг ни время для выборки, а просто вторым, обрабатывающим сервисом «запоминать» последний успешно обработанный идентификатор, а выборку делать
where ID > @yourLastCalcedID

Это будет наиболее эффективно, естественно, если обработка сервисом протекает последовательно, линейно ID за ID, без пропусков и ветвлений между элементами обработки
Ответ написан
Комментировать
@Artyushov
если у вас поле принимает ограниченное количество значений, (enumeration), то по нему будет построена битовая шкала. То есть для каждого значения будет храниться вектор из 0 и 1 длиной в количество записей. Думаю дальше понятно.
Таким образом можно сразу получить номера записей с данным значением поля.
Ответ написан
Комментировать
@hom9k
Скорее всего разницы никакой не будет, поскольку и в том и в другом случае количество записей с флаг =0 или processed_time = 0 одинаковое. Индекс на processed_time только позволит намного быстрее делать статистические запросы с количеством обработанных заданий во временных промежутках (нужно ли вам это?).
У индекса есть такое значение как cardinality (дословно с англ: «мощность»). И чем ближе значение этого свойства к общему количеству записей — тем быстрее работает поиск по индексу.
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Похожие вопросы
xRocket Москва
от 3 000 до 4 000 $
div. Ставрополь
от 50 000 до 120 000 ₽
Lachestry Таганрог
от 170 000 до 200 000 ₽