MySQL удаляет строки в отсортированном порядке на очень большой таблице
У меня есть таблица T MyISAM со следующей схемой:
f1 (целое число без знака, не ноль) f2 (целое число без знака, не ноль)
Эта таблица имеет индекс f2, и в настоящее время она содержит 320 миллионов строк, и ожидается, что она будет расти со скоростью около 200000 строк один раз в неделю. Я выполняю следующий запрос к этой таблице:
ВЫБЕРИТЕ DISTINCT T.f1 ОТ T ГДЕ f2 = @ Var LIMIT?, 30
@Var - это переменная, передаваемая хранимой процедуре, которая выполняет этот запрос, и переменная LIMIT изменяется в зависимости от отображаемого номера страницы (начиная с 0 и т. Д.)
Скорость поиска очень хорошая (учитывая, что таблица очень большая), но строки отображаются в том порядке, в котором они были записаны в таблицу (т.е. не в порядке f1). Я хотел бы иметь возможность включить пункт "ORDER BY f1 DESC" в вышеупомянутый запрос, однако, выполнение этого без INDEX было бы самоубийством! (иногда может быть более миллиона строк, удовлетворяющих запросу, и упорядочение их без индекса, вероятно, приведет к остановке сервера)
Мой вопрос... какой индекс (ы) должен присутствовать для удовлетворения запроса, который я выполняю, а также для упорядочения строк в результате? Если запрос и сортировка не могут быть выполнены с помощью индексов, я думал о выполнении команды ALTER TABLE T ORDER BY f1 DESC после обновления (и в то время как пользователи все еще могут запрашивать данные). В этом случае на моей машине для разработки оператор alter занял около 50 минут, что не так уж и плохо. Очевидно, что на LIVE-машине мне нужно было бы столько же свободного дискового пространства, сколько и размер исходной таблицы... Какие еще соображения мне нужно принять?
Заранее спасибо Тим
1 ответ
Я не уверен, что ваше предположение о том, что для предложения ORDER BY потребуется указатель на f1, действительно верно. Я создал такую таблицу и побежал
объяснить SELECT DISTINCT T.f1 как результат ОТ проверки строки T ГДЕ f2=10 упорядочить по результату LIMIT 0,30
И я получил это:
id | select_type | стол | тип | возможные_ключи | ключ | key_len | ref | строки | дополнительный 1 | ПРОСТО | T | ref | idx_f2 | idx_f2 | 4 | конст | 3 | Используя где; Используя временные; Использование сортировки файлов
Теперь тот факт, что сервер будет использовать временную таблицу и сортировку файлов, не намекает на особенно быстрый или эффективный способ сделать это. Однако там нет ничего, что говорило бы о необходимости индекса на f1. Не обращайте внимания на тот факт, что в моем случае в наборе результатов будет только 3 строки (я не мог позволить себе создать таблицу с 320 миллионами строк).
Теперь: если я добавлю индекс к таблице в столбце f1, результат объяснения не изменится вовсе, а это значит, что индекс у вас есть или нет, не имеет значения.
Причина этого заключается в том, что сервер сначала извлекает все строки, которые удовлетворяют условию where (используя индекс по f2), а затем упорядочивает их, используя временный файл. При извлечении строк индекс на f1 не помогает, а на этапе упорядочения его нет.
Учитывая, что ваш результирующий набор никогда не превышает 30 строк, упорядочение во временном файле вообще не займет времени. Попробуйте сами.
РЕДАКТИРОВАТЬ Забудьте это последнее предложение, это была чепуха. Я только что понял, что предложение LIMIT применяется ПОСЛЕ сортировки. Итак: Да, сортировка займет некоторое время. Если ваш запрос действительно возвращает только один числовой столбец, он должен быть достаточно быстрым. И остается одна истина: индекс на f1 не имеет никакого значения. Плюс: AFAIK, после того как все строки получены, таблица не блокируется для любого другого доступа. И поскольку это не изменится, это не повлияет на других пользователей, независимо от того, используете вы предложение ORDER BY или нет.