Каков ваш подход к оптимизации больших таблиц (+ 1M строк) на SQL Server?

Я импортирую данные бразильского фондового рынка в базу данных SQL Server. Сейчас у меня есть таблица с ценовой информацией от трех видов активов: акции, опционы и форварды. Я все еще в 2006 году, и в таблице более полумиллиона записей. У меня есть более 12 лет данных для импорта, поэтому таблица будет превышать миллион записей.

Теперь мой первый подход к оптимизации заключался в том, чтобы сохранить минимальные размеры данных, поэтому я уменьшил размер строки до 60 байт со следующими столбцами:

[Stock] [int] NOT NULL
[Date] [smalldatetime] NOT NULL
[Open] [smallmoney] NOT NULL
[High] [smallmoney] NOT NULL
[Low] [smallmoney] NOT NULL
[Close] [smallmoney] NOT NULL
[Trades] [int] NOT NULL
[Quantity] [bigint] NOT NULL
[Volume] [money] NOT NULL

Теперь второй подход для оптимизации заключался в создании кластерного индекса. Фактически первичный индекс автоматически сжимается, и я сделал его составным индексом с полями "Сток" и "Дата". Это уникально, я не могу иметь две данные котировки для одного и того же запаса в тот же день.

Сложенный индекс гарантирует, что котировки из одного и того же запаса остаются вместе, а , вероятно, упорядочены по дате. Является ли эта вторая информация верной?

Прямо сейчас, когда полмиллиона записей занимают 200 мс, чтобы выбрать 700 цитаты из определенного актива. Я считаю, что это число будет расти по мере роста таблицы.

Теперь для третьего подхода я думаю, что, возможно, разбивая таблицу на три таблицы, каждая для определенного рынка (акции, опционы и форварды). Вероятно, это уменьшит размер таблицы на 1/3. Теперь, поможет ли этот подход или это не имеет большого значения? Прямо сейчас таблица имеет размер 50 МБ, поэтому она может полностью помещаться в ОЗУ без особых проблем.

Другим подходом будет использование функции разделения SQL Server. Я не знаю много об этом, но я думаю, что это обычно используется, когда таблицы большие, и вы можете охватывать несколько дисков, чтобы уменьшить латентность ввода-вывода, верно? Разделение будет полезным в этом случае? Я считаю, что могу разбить новейшие значения (последние годы) и самые старые значения в разных таблицах. Вероятность поиска новейших данных выше, а с небольшим разделом она, вероятно, будет быстрее, правильно?

Какими будут другие хорошие подходы, чтобы сделать это максимально возможным? В основном выбор использования таблицы будет заключаться в поиске определенного диапазона записей из определенного актива, таких как последние 3 месяца актива X. Будут и другие способы использования, но это будет наиболее распространенным, возможно, выполнено более чем на 3 тыс. пользователей одновременно.

Ответ 1

На 1 миллион записей я бы не счел это особенно большой таблицей, требующей необычных методов оптимизации, таких как разбиение таблицы, денормализация и т.д. Но эти решения придут, когда вы попробуете все нормальные средства, t влияет на вашу способность использовать стандартные методы запросов.

Теперь второй подход для оптимизации заключался в создании кластерного индекса. Фактически первичный индекс автоматически сжимается, и я сделал его составным индексом с полями "Сток" и "Дата". Это уникально, я не могу иметь две данные котировки для одного и того же запаса в тот же день.

Сложенный индекс гарантирует, что котировки из одного и того же запаса остаются вместе и, вероятно, упорядочены по дате. Является ли эта вторая информация верной?

Это логически верно - кластеризованный индекс определяет логический порядок записей на диске, и это все, о чем вы должны беспокоиться. SQL Server может отказаться от накладных расходов на сортировку в физическом блоке, но он все равно будет вести себя так, как если бы он это сделал, поэтому он не значителен. Запрос на один запас, вероятно, будет на 1 или 2 страницах в любом случае; и оптимизатор не сильно выигрывает от неупорядоченных данных в пределах чтения страницы.

Сейчас, когда полмиллиона записей занимают около 200 мс для выбора 700 котировок из определенного актива. Я считаю, что это число будет расти по мере роста таблицы.

Не обязательно значительно. Не существует линейной зависимости между размером таблицы и скоростью запроса. Есть, как правило, гораздо больше соображений, которые важнее. Я бы не стал беспокоиться об этом в том диапазоне, который вы описываете. Это причина, о которой вы беспокоитесь? 200 мс, казалось бы, были бы замечательными, настолько, чтобы вы дошли до того, что ваши таблицы загружены, и вы можете начать реалистичное тестирование и получить гораздо лучшее представление о реальной производительности.

Теперь для третьего подхода я думаю, что, возможно, разбивая таблицу на три таблицы, каждая для определенного рынка (акции, опционы и форварды). Вероятно, это уменьшит размер таблицы на 1/3. Теперь, поможет ли этот подход или это не имеет большого значения? Прямо сейчас таблица имеет размер 50 МБ, поэтому она может полностью помещаться в ОЗУ без особых проблем.

Нет! Такая оптимизация настолько преждевременна, что она, вероятно, мертворожденная.

Другим подходом будет использование функции разделения SQL Server.

Тот же комментарий. Вы сможете долго придерживаться строго логичного, полностью нормализованного дизайна схемы.

Какими будут другие хорошие подходы, чтобы сделать это максимально возможным?

Лучший первый шаг - кластеризация на складе. Скорость вставки не имеет никакого значения, пока вы не посмотрите на несколько записей, вставленных в секунду - я ничего не вижу поблизости от этой активности. Это должно приблизить вас к максимальной эффективности, поскольку оно будет эффективно читать каждую запись, связанную с запасом, и это, по-видимому, ваш самый распространенный индекс. Любая дальнейшая оптимизация должна выполняться на основе тестирования.

Ответ 2

Миллион записей действительно не такой большой. Похоже, что слишком много времени для поиска - это столбец, который вы ищете, с индексированным?

Как всегда, первый порт вызова должен быть оценщиком профилировщика SQL и плана запроса. Спросите SQL Server, что он будет делать с запросами, которые вас интересуют. Я считаю, вы даже можете попросить его предложить такие изменения, как дополнительные индексы.

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

Ответ 3

Сначала проверьте план выполнения этого запроса. Убедитесь, что ваши индексы используются. Я нашел это. Миллионов записей не много. Чтобы дать некоторую перспективу, у нас была таблица инвентаря с 30 миллионами строк в ней, и весь наш запрос, который объединил тонны таблиц и провел множество расчетов, мог работать под 200 MS. Мы обнаружили, что на четырехпроцессорном 64-битном сервере мы могли бы иметь значительно больше записей, поэтому мы никогда не беспокоились о частичном подключении.

Вы можете использовать SQL Profier для просмотра плана выполнения или просто запустить запрос из SQL Management Studio или Query Analyzer.

Ответ 4

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

Моя рекомендация - это перестроить индексы для этой таблицы, сделать составную для столбцов, которые вам понадобятся больше всего. Теперь, когда у вас есть только несколько записей, играющих с разными индексами, в противном случае вам будет очень неприятно попробовать новые вещи, как только у вас будут все исторические данные в таблице.

После того как вы просмотрите свой запрос, сделайте оценщик плана запроса своим другом и проверьте, использует ли движок нужный индекс.

Я только что прочитал последнее сообщение, это единственное, чего я не получаю, вы запрашиваете таблицу, пока вставляете данные? в то же время?. Зачем? путем вставки, вы имеете в виду одну запись или сотни тысяч? Как вы вставляете? один за другим?

Но опять же ключом к этому являются индексы, не путайте с разделением и тем не менее.. специально с миллионными записями, вот и ничего, у меня есть таблицы с 150 миллионными записями, а возврат 40 тыс. конкретных записей принимает двигатель 1500 мс...

Ответ 5

Я работаю в школьном округе, и мы должны отслеживать посещаемость для каждого ученика. Это как мы делаем наши деньги. Моя таблица, которая содержит ежедневный показатель посещаемости для каждого учащегося, в настоящее время составляет 38,9 миллиона записей. Я могу очень быстро вытащить одну студенческую посещаемость. В этой таблице мы сохраняем 4 индекса (включая первичный ключ). Наш кластеризованный индекс - это студент/дата, которая хранит все записи студентов, заказанные им. Мы приняли удар по вставкам в эту таблицу в отношении того, что в том случае, если старая запись для ученика вставлена, но это представляет опасность для наших целей.

Что касается выбора скорости, я бы, безусловно, воспользовался кешированием в ваших обстоятельствах.

Ответ 6

Вы упомянули, что ваш первичный ключ - это составная часть (Stock, Date) и кластеризация. Это означает, что таблица организована по Акциям, а затем по дате. Всякий раз, когда вы вставляете новую строку, она должна вставлять ее в середину таблицы, и это может привести к вытеснению других строк на другие страницы (разбиение страниц).

Я бы рекомендовал попробовать перенести первичный ключ на (дата, запас) и добавить некластеризованный индекс на Stock, чтобы облегчить быстрый поиск определенного товара. Это позволит всегда вставлять вставки в конце таблицы (при условии, что вы вставляете их в порядке), и не повлияет на остальную часть таблицы и меньшую вероятность разбиения страниц.

Ответ 7

План выполнения показывает его с помощью кластеризованного индекса довольно хорошо, но я забыл очень важный факт, я все еще вставляю данные! Вставка, вероятно, слишком часто блокирует таблицу. Есть ли способ увидеть это узкое место?

План выполнения, похоже, ничего не говорит о проблемах с блокировкой.

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

Ответ 8

другим решением было бы создать историческую таблицу для каждого года и поместить все эти таблицы в историческую базу данных, заполнить все это, а затем создать для них соответствующие индексы. Как только вы закончите с этим, вам больше не придется прикасаться к ним. Зачем вам нужно вставлять данные? Чтобы запросить все эти таблицы, вы просто "объедините все": p

Таблица текущего года должна сильно отличаться от этих исторических таблиц. Насколько я понял, вы планируете вставлять записи на ходу? Я планировал бы что-то другое, например, делать объемную вставку или что-то подобное время от времени в течение дня. Конечно, все это зависит от того, что вы хотите сделать.

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

Ответ 9

Фактически первичный индекс автоматически сжимается, и я сделал его составным индексом с полями "Сток" и "Дата". Это уникально, я не могу иметь двух данных кавычек для одного и того же запаса в тот же день.

Сложенный индекс гарантирует, что котировки из одного и того же запаса остаются вместе и, вероятно, упорядочены по дате. Является ли эта вторая информация верной?

Индексы в SQL Server всегда сортируются по порядку столбцов в индексе. Таким образом, индекс на [акции, дата] сначала сортируется по акциям, затем в наличии на дату. Индекс по [дате, акции] сначала сортирует по дате, затем в дату на складе.

При выполнении запроса вы всегда должны включать первый столбец (индексы) индекса в части WHERE, иначе индекс не может быть эффективно использован.

Для вашей конкретной проблемы: если запрос диапазона дат для акций является наиболее распространенным, введите первичный ключ в [дату, запас], поэтому данные будут храниться последовательно по дате на диске, и вам следует получить быстрый доступ. При необходимости создайте другие индексы. Обновите обновление индекса/статистику после вставки большого количества новых данных.