У меня есть таблица MyISAM T со следующей схемой:
f1 (целое число без знака не равно нулю) f2 (целое число без знака не равно нулю)
Эта таблица имеет индекс на f2, и в настоящее время она содержит 320 миллионов строк и, как ожидается, будет расти примерно на 200 000 строк один раз в неделю. Я выполняю следующий запрос по этой таблице:
ВЫБЕРИТЕ 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-машине мне потребуется столько же свободного места на диске, сколько размер исходной таблицы ... Есть ли какие-то другие соображения, которые мне нужно принять?
Заранее спасибо, Тим
Я не уверен, что ваше предположение о том, что для предложения ORDER BY потребуется индекс для f1, действительно верно. Я создал такую таблицу и запустил
explain SELECT DISTINCT T.f1 as result FROM rowtest T WHERE f2=10 order by result LIMIT 0,30
И я получил в ответ это:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra 1 | SIMPLE | T | ref | idx_f2 | idx_f2 | 4 | const | 3 | Using where; Using temporary; Using filesort
Теперь тот факт, что сервер будет использовать временную таблицу и файловую сортировку, не намекает на особенно быстрый или эффективный способ сделать это. Однако там нет ничего, что говорило бы, что вам нужен индекс на f1. Игнорируйте тот факт, что в моем случае в наборе результатов будет только 3 строки (я не мог позволить себе создать таблицу с 320 миллионами строк).
Теперь: если я добавлю индекс в таблицу в столбце f1, результат объяснения не изменится вообще, а это значит, есть ли у вас индекс или нет, не имеет значения.
Причина этого заключается в том, что сервер сначала извлекает все строки, удовлетворяющие условию where (используя индекс на f2), а затем упорядочивает их, используя временный файл. При извлечении строк индекс на f1 не помогает, а на этапе упорядочивания его нет.
Учитывая, что ваш результирующий набор никогда не превышает 30 строк, упорядочивание во временном файле вообще не займет времени. Попробуйте сами.
РЕДАКТИРОВАТЬ Забудьте последнее предложение, это ерунда. Я только что понял, что применяется предложение LIMIT ПОСЛЕ происходит сортировка. Итак: Да, сортировка займет некоторое время. Однако, если ваш запрос действительно возвращает только один числовой столбец, он должен быть довольно быстрым. И остается одна правда: индекс на f1 не имеет никакого значения. Плюс: AFAIK, как только все строки были извлечены, таблица не заблокирована для любого другого доступа. И поскольку это не меняется, это не влияет на других пользователей, независимо от того, используете вы предложение ORDER BY или нет.