В моей таблице (SQL Server 2008) есть 1 миллион + записей, когда я пытаюсь упорядочить записи по дате, она занимает 1 секунду, но когда я заказываю по ID (int), она занимает всего около 0,1 секунды.
Есть ли способ повысить эффективность? (Я уже добавил столбец datetime в индекс)
Заказ id, вероятно, использует сканирование с кластеризованным индексом, а упорядочение с помощью datetime использует сортировку или поиск по индексу.
Оба этих метода медленнее, чем сканирование с кластерным индексом.
Если ваша таблица кластеризована id, в основном это означает, что она уже отсортирована. Записи содержатся в B+Tree, который имеет связанный список, связывающий страницы в порядке id. Двигатель должен просто пересечь связанный список, чтобы получить записи, упорядоченные с помощью id.
Если id были вставлены в последовательном порядке, это означает, что физический порядок строк будет соответствовать логическому порядку, а сканирование кластерного индекса будет еще быстрее.
Если вы хотите, чтобы ваши записи упорядочивались с помощью datetime, существует два варианта:
datetime. Индекс хранится в отдельном пространстве диска, это означает, что движок должен перемещаться между индексными страницами и страницами таблицы во вложенном цикле. Это тоже медленнее.Чтобы улучшить упорядочение, вы можете создать отдельный индекс покрытия на datetime:
CREATE INDEX ix_mytable_datetime ON mytable (datetime) INCLUDE (field1, field2, …)
и включите все столбцы, которые вы используете в своем запросе, в этот индекс.
Этот индекс похож на теневую копию вашей таблицы, но с данными, отсортированными в другом порядке.
Это позволит избавиться от поиска ключей (поскольку индекс содержит все данные), что сделает упорядочение на datetime так же быстро, как на id.
Update:
Новое сообщение в блоге по этой проблеме:
В честь ORDER BY движок имеет две альтернативы:
Первая опция выполняется быстро, вторая - медленная. Проблема в том, что для использования индекс должен быть покрывающим индексом. Это означает, что он содержит все столбцы в списке проекции SELECT и все столбцы, используемые в предложениях WHERE (как минимум). Если индекс не покрывает, то для поиска значений требуемых столбцов движок должен искать кластерный индекс (т.е. Таблицу) для каждой строки. Этот постоянный поиск значений является дорогостоящим, и есть решающий момент, когда двигатель (по праву) решит более эффективно просто сканировать кластерный индекс и сортировать результат, фактически игнорируя ваш некластеризованный индекс. Для получения дополнительной информации см. Ответы на опрос точек опроса.
Рассмотрим следующие три запроса:
SELECT dateColumn FROM table ORDER BY dateColumn
SELECT * FROM table ORDER BY dateColumn
SELECT someColumn FROM table ORDER BY dateColumn
В первом случае будет использоваться некластеризованный индекс для dateColumn. Но второй не будет использовать индекс на dateColumn, скорее всего, выберем сканирование и сортировку вместо 1M строк. С другой стороны, третий запрос может извлечь выгоду из индекса на Table(dateColumn) INCLUDE (someColumn).
Этот раздел подробно рассматривается в MSDN, см. Основы проектирования индексов, Общие принципы проектирования указателей, Руководство по разработке некластеризованных указателей или Как сделать: Оптимизация индексов SQL.
В конечном счете, самым важным выбором дизайна вашей таблицы является кластеризованный индекс, который вы используете. Почти всегда первичный ключ (как правило, автоматически увеличиваемый идентификатор) остается как кластеризованный индекс, решение, которое приносит пользу только определенным нагрузкам OLTP.
И, наконец, довольно очевидный вопрос: почему в мире вы бы заказали 1 миллион строк? Вы не можете их отобразить, не так ли? Объяснение немного больше о вашем случае использования может помочь нам найти лучший ответ для вас.
Добавить время даты в новый индекс, добавив его в идентификатор, который все равно не поможет.
Может быть, есть индекс для вашего столбца int, но не для столбца datetime? Посмотрите на план выполнения.
возможно, если вы храните datatime как int, но при каждом хранении или получении данных потребуется преобразование времени. (общая техника, используемая для хранения персонала, такого как ip-адрес, и ускорения поиска)
вы должны проверить на своем сервере, как он хранит дату и время, b/c его сервер уже сохраняет его как int или bigint.. он ничего не изменит....
Если ваше поле datetime содержит множество различных значений, и эти значения редко меняются, определите кластерный индекс в поле datetime, это отсортирует фактические данные по значению даты и времени. См. http://msdn.microsoft.com/en-us/library/aa933131(SQL.80).aspx для использования кластеризованных индексов.
Это приведет к тому, что вы выполняете поиск по-прежнему медленнее, поскольку они будут отнесены к использованию некластеризованного индекса.
Вы добавили поле DateTime к индексу или к эксклюзивному индексу? Вы отфильтровываете свой выбор другим полем и DateTime или только этим?
У вас должен быть индекс со всеми полями, которые вы фильтруете, и предпочтительно в том же порядке, чтобы оптимизировать производительность.