Одна таблица 400 ГБ, один запрос - нужны идеи настройки (SQL2005)

У меня есть одна большая таблица, которую я бы хотел оптимизировать. Я использую сервер MS-SQL 2005. Я попытаюсь описать, как он используется, и если у кого-то есть предложения, я бы очень его оценил.

Таблица составляет около 400 ГБ, каждый день вставляется 100 миллионов строк и 1 миллион строк. Таблица содержит 8 столбцов, 1 столбца данных и 7 столбцов, используемых для поиска/упорядочения.

k1 k2 k3 k4 k5 k6 k7 d1

где

k1: varchar(3), primary key - clustered index, 10 possible values
 k2: bigint, primary key - clustered index, total rows/10 possible values
 k3: int, 10 possible values
 k4: money, 100 possible values
 k5: bool
 k6: bool
 k7: DateTime

Выполняется только один запрос выбора, который выглядит следующим образом:

SELECT TOP(g) d1 FROM table WITH(NOLOCK)
 WHERE k1 = a
 AND k3 = c
 AND k4 = d
 AND k5 = e
 AND k6 = f
 ORDER BY k7

где g = около 1 миллиона Этот запрос мы запускали около 10 раз в день (часто во время вставки) и занимает около 5-30 минут.

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

Будет ли отдельный индекс для каждого столбца хорошим выбором? Я думаю, что один индекс займет около 5-8 ГБ. Сервер БД имеет общий объем оперативной памяти 8 ГБ.

Пожалуйста, не говорите, что лучше всего экспериментировать. Это сродни: "Я не знаю, работайте над собой":)

Любые полезные советы!

EDIT by doofledorfer -

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

- doofledorfer

EDIT: Комментарии к сообщениям на сегодняшний день теперь размещены ниже вместе с планом запроса - Г-н Флиббл

Вероятно, вы связаны с интерфейсом ввода/вывода

Да, это не связано с ЦП. Высокий уровень доступа к диску. Кажется, что используется вся оперативная память. Используется ли оно мудро или нет, пока не видно.

Вы говорите, что не можете разделить данные, потому что используются все данные: НЕВОЗМОЖНО

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

Почему вы выбрали эти типы Вероятно, VARCHAR должен был быть INT, поскольку это может быть только несколько значений. Остальные достаточно разумны, деньги представляют собой денежную ценность в реальной жизни, а bigint - это идентификатор, а bools - onny, offy type things:)

По какой-либо причине мы могли бы получить представление инструкции insert, или TSQL или объемный файл

TSQL. Его в основном INSERT INTO таблицы VALUES (k1, k2, k3, k4, k5, k6, d1). Единственное, что интересно, это то, что много дубликатов вставок предпринимаются, и ограничение k1 и k2 PK используется для предотвращения дублирования данных, поступающих в базу данных. Я верил во время разработки (и теперь), что это было так же быстро, как любой, чтобы уничтожить дублированные данные.

Можете ли вы сказать, как часто ваша вставка происходит Каждые 10 минут или около того вставки запускаются (ADO.NET), возможно, 10K за раз и занимают несколько минут. По моим оценкам, в настоящее время полные дневные вставки занимают 40% времени в день.

Поле DateTime содержит дату вставки Нет. На самом деле есть другой столбец DateTime, но он не извлекается ни в одном запросе SELECT, поэтому я просто не упомянул об этом ради простоты.

Как вы пришли к этому Еще один человек думает.

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

Мне не интересны только последние данные. Запрос может выбрать некоторые из самых первых данных, которые были вставлены в таблицу, вплоть до данных, вставленных минут назад. Но поскольку данные отфильтрованы, это не означает, что все данные в БД запрашиваются в этом запросе.

если имеется только один "вставщик" и только один "читатель", вы можете переключиться на специализированный тип (hashmap/list/deque/stack) или что-то более развитое на языке программирования.

Наверное, я буду придерживаться MSSQL. Он еще не сломался, только немного медленно.

liggett78, вы предлагаете кластеризованный индекс для столбцов k1, k4, k5, k6, k3 или некластеризованный индекс в этих столбцах?

Теперь мой главный вопрос: я должен расширить текущий кластеризованный индекс, чтобы также содержать k4 (это col со следующими наиболее вероятными значениями) или просто добавить некластеризованный индекс в k4.

Добавил бы все k1-k6 в кластеризованный индекс? Затем для отдельного некластеризованного индекса в столбце DateTime для ORDER BY? Правильно ли я полагаю, что это не приведет к существенному увеличению размера БД, а повлияет только на время вставки. Может ли кто-нибудь оценить эффект, который это будет иметь на вставках?

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

Следующий план был запущен с индексом (не кластеризованным) в столбце DATE.

EDIT: Не уверен, что вы можете увидеть XML ниже, так что вот ссылка на него: http://conormccarthy.com/box/queryplan.sqlplan.txt

<!--?xml version="1.0" encoding="utf-16"?-->
<showplanxml xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" version="1.0" build="9.00.1399.06" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
 <batchsequence>
 <batch>
 <statements>
 <stmtsimple statementcompid="1" statementestrows="11111" statementid="1" statementoptmlevel="FULL" statementsubtreecost="625.754" statementtext="SELECT TOP(11111) d1 FROM hands WITH (NOLOCK) 
 WHERE k4 = '10' 
 AND k6 = 1 
 AND k5 = 1 
 AND k1 = 'IPN' 
 AND k3 BETWEEN 2 AND 10 
 ORDER BY k7 DESC
" statementtype="SELECT">
 <statementsetoptions ansi_nulls="false" ansi_padding="false" ansi_warnings="false" arithabort="true" concat_null_yields_null="false" numeric_roundabort="false" quoted_identifier="false">
 <queryplan degreeofparallelism="1" cachedplansize="36">
 <missingindexes>
 <missingindexgroup impact="81.7837">
 <missingindex database="[MYDB]" schema="[dbo]" table="[Hands]">
 <columngroup usage="EQUALITY">
 <column name="[k1]" columnid="1">
 <column name="[k4]" columnid="7">
 <column name="[k5]" columnid="9">
 <column name="[k6]" columnid="10">
 </column></column></column></column></columngroup>
 <columngroup usage="INEQUALITY">
 <column name="[k3]" columnid="6">
 </column></columngroup>
 <columngroup usage="INCLUDE">
 <column name="[d1]" columnid="3">
 <column name="[k7]" columnid="4">
 </column></column></columngroup>
 </missingindex>
 </missingindexgroup>
 </missingindexes>
 <relop avgrowsize="75" estimatecpu="0.0011111" estimateio="0" estimaterebinds="0" estimaterewinds="0" estimaterows="11111" logicalop="Top" nodeid="0" parallel="false" physicalop="Top" estimatedtotalsubtreecost="625.754">
 <outputlist>
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="d1">
 </columnreference></outputlist>
 <runtimeinformation>
 <runtimecountersperthread thread="0" actualrows="11111" actualendofscans="1" actualexecutions="1">
 </runtimecountersperthread></runtimeinformation>
 <top rowcount="false" ispercent="false" withties="false">
 <topexpression>
 <scalaroperator scalarstring="(11111)">
 <const constvalue="(11111)">
 </const></scalaroperator>
 </topexpression>
 <relop avgrowsize="83" estimatecpu="135.557" estimateio="0" estimaterebinds="0" estimaterewinds="0" estimaterows="11111" logicalop="Filter" nodeid="1" parallel="false" physicalop="Filter" estimatedtotalsubtreecost="625.753">
 <outputlist>
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="d1">
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="k7">
 </columnreference></columnreference></outputlist>
 <runtimeinformation>
 <runtimecountersperthread thread="0" actualrows="11111" actualendofscans="0" actualexecutions="1">
 </runtimecountersperthread></runtimeinformation>
 <filter startupexpression="false">
 <relop avgrowsize="96" estimatecpu="318.331" estimateio="0" estimaterebinds="0" estimaterewinds="0" estimaterows="195691" logicalop="Inner Join" nodeid="2" parallel="false" physicalop="Nested Loops" estimatedtotalsubtreecost="625.404">
 <outputlist>
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="d1">
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="k7">
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="k3">
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="k4">
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="k5">
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="k6">
 </columnreference></columnreference></columnreference></columnreference></columnreference></columnreference></outputlist>
 <runtimeinformation>
 <runtimecountersperthread thread="0" actualrows="341958" actualendofscans="0" actualexecutions="1">
 </runtimecountersperthread></runtimeinformation>
 <nestedloops optimized="false" withorderedprefetch="true">
 <outerreferences>
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="k1">
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="HandId">
 <columnreference column="Expr1003">
 </columnreference></columnreference></columnreference></outerreferences>
 <relop avgrowsize="32" estimatecpu="330.366" estimateio="790.88" estimaterebinds="0" estimaterewinds="0" estimaterows="195691" logicalop="Index Scan" nodeid="4" parallel="false" physicalop="Index Scan" estimatedtotalsubtreecost="2.88444">
 <outputlist>
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="k1">
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="HandId">
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="k7">
 </columnreference></columnreference></columnreference></outputlist>
 <runtimeinformation>
 <runtimecountersperthread thread="0" actualrows="341958" actualendofscans="0" actualexecutions="1">
 </runtimecountersperthread></runtimeinformation>
 <indexscan ordered="true" scandirection="BACKWARD" forcedindex="false" noexpandhint="false">
 <definedvalues>
 <definedvalue>
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="k1">
 </columnreference></definedvalue>
 <definedvalue>
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="HandId">
 </columnreference></definedvalue>
 <definedvalue>
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="k7">
 </columnreference></definedvalue>
 </definedvalues>
 <object database="[MYDB]" schema="[dbo]" table="[Hands]" index="[ix_dateplayed]">
 <predicate>
 <scalaroperator scalarstring="[MYDB].[dbo].[Hands].[k1]=N'IPN'">
 <compare compareop="EQ">
 <scalaroperator>
 <identifier>
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="k1">
 </columnreference></identifier>
 </scalaroperator>
 <scalaroperator>
 <const constvalue="N'IPN'">
 </const></scalaroperator>
 </compare>
 </scalaroperator>
 </predicate>
 
 
 <relop avgrowsize="88" estimatecpu="0.0001581" estimateio="0.003125" estimaterebinds="195691" estimaterewinds="0" estimaterows="1" logicalop="Clustered Index Seek" nodeid="6" parallel="false" physicalop="Clustered Index Seek" estimatedtotalsubtreecost="621.331">
 <outputlist>
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="d1">
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="k3">
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="k4">
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="k5">
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="k6">
 </columnreference></columnreference></columnreference></columnreference></columnreference></outputlist>
 <runtimeinformation>
 <runtimecountersperthread thread="0" actualrows="341958" actualendofscans="0" actualexecutions="341958">
 </runtimecountersperthread></runtimeinformation>
 <indexscan lookup="true" ordered="true" scandirection="FORWARD" forcedindex="false" noexpandhint="false">
 <definedvalues>
 <definedvalue>
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="d1">
 </columnreference></definedvalue>
 <definedvalue>
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="k3">
 </columnreference></definedvalue>
 <definedvalue>
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="k4">
 </columnreference></definedvalue>
 <definedvalue>
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="k5">
 </columnreference></definedvalue>
 <definedvalue>
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="k6">
 </columnreference></definedvalue>
 </definedvalues>
 <object database="[MYDB]" schema="[dbo]" table="[Hands]" index="[PK_Hands]" tablereferenceid="-1">
 <seekpredicates>
 <seekpredicate>
 <prefix scantype="EQ">
 <rangecolumns>
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="k1">
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="HandId">
 </columnreference></columnreference></rangecolumns>
 <rangeexpressions>
 <scalaroperator scalarstring="[MYDB].[dbo].[Hands].[k1]">
 <identifier>
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="k1">
 </columnreference></identifier>
 </scalaroperator>
 <scalaroperator scalarstring="[MYDB].[dbo].[Hands].[HandId]">
 <identifier>
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="HandId">
 </columnreference></identifier>
 </scalaroperator>
 </rangeexpressions>
 </prefix>
 </seekpredicate>
 </seekpredicates>
 
 
 
 
 <predicate>
 <scalaroperator scalarstring="[MYDB].[dbo].[Hands].[k4]=($10.0000) AND [MYDB].[dbo].[Hands].[k6]=(1) AND [MYDB].[dbo].[Hands].[k5]=(1) AND [MYDB].[dbo].[Hands].[k3]>=(2) AND [MYDB].[dbo].[Hands].[k3]<=(10)">
 <logical operation="AND">
 <scalaroperator>
 <compare compareop="EQ">
 <scalaroperator>
 <identifier>
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="k4">
 </columnreference></identifier>
 </scalaroperator>
 <scalaroperator>
 <const constvalue="($10.0000)">
 </const></scalaroperator>
 </compare>
 </scalaroperator>
 <scalaroperator>
 <compare compareop="EQ">
 <scalaroperator>
 <identifier>
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="k6">
 </columnreference></identifier>
 </scalaroperator>
 <scalaroperator>
 <const constvalue="(1)">
 </const></scalaroperator>
 </compare>
 </scalaroperator>
 <scalaroperator>
 <compare compareop="EQ">
 <scalaroperator>
 <identifier>
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="k5">
 </columnreference></identifier>
 </scalaroperator>
 <scalaroperator>
 <const constvalue="(1)">
 </const></scalaroperator>
 </compare>
 </scalaroperator>
 <scalaroperator>
 <compare compareop="GE">
 <scalaroperator>
 <identifier>
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="k3">
 </columnreference></identifier>
 </scalaroperator>
 <scalaroperator>
 <const constvalue="(2)">
 </const></scalaroperator>
 </compare>
 </scalaroperator>
 <scalaroperator>
 <compare compareop="LE">
 <scalaroperator>
 <identifier>
 <columnreference database="[MYDB]" schema="[dbo]" table="[Hands]" column="k3">
 </columnreference></identifier>
 </scalaroperator>
 <scalaroperator>
 <const constvalue="(10)">
 </const></scalaroperator>
 </compare>
 </scalaroperator>
 </logical>
 </scalaroperator>
 </predicate>
 
 
 
 
 
 
 
 
 
</object></indexscan></relop></object></indexscan></relop></nestedloops></relop></filter></relop></top></relop></queryplan></statementsetoptions></stmtsimple></statements></batch></batchsequence></showplanxml>
24 ответа

Как я намекнул в комментарии, я сделал это, когда одна таблица Oracle приблизилась к 8 ТБ, состоящая из более чем двух миллиардов строк, растущих со скоростью 40 миллионов строк в день. Однако в моем случае пользователи были двумя миллионами (и растущими) клиентами, обращающимися к этим данным через Интернет, 24x7, и буквально ЛЮБОЙ из строк подлежал доступу. О, и новые строки должны были быть добавлены в течение двух минут в режиме реального времени.

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

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

Вы говорите, что вы не можете разделить данные, потому что все данные используются: НЕВОЗМОЖНО! Невозможно, чтобы ваши пользователи просматривали страницы через миллион строк в день или всего сто миллионов строк. Итак, узнайте, как ваши пользователи НАСТОЯТЕЛЬНЫ используют данные - посмотрите на каждый запрос в этом случае.

Что еще более важно, мы не говорим, что вы должны УДАЛИТЬ данные, мы говорим, чтобы SPLIT данных. Клонирование структуры таблицы в несколько таблиц с одинаковым именем, возможно, основано на времени (возможно, один месяц на таблицу). Скопируйте данные в соответствующие таблицы и удалите исходную таблицу. Создайте представление, которое выполняет объединение над новыми таблицами с тем же именем, что и исходная таблица. Измените обработку вставки, чтобы настроить таргетинг на новейшую таблицу (при условии, что она подходит), и ваши запросы все равно будут работать против нового представления.

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

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

Ожидайте, что не сможете использовать уникальные индексы: они не масштабируются не более чем на один-два миллиона строк. Вам также может потребоваться изменить некоторые другие тактики/рекомендации. На сто миллионов строк и 400 ГБ вы вошли в другую область обработки.

Кроме того, используйте другие предложения - проанализируйте фактическую производительность, используя множество инструментов, уже доступных в SQL Server и ОС. Примените многие известные методы настройки, которые легко доступны в Интернете или в книгах.

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


Помогите мне лучше узнать о таблице. если ваш PK - k1, k2, вам не нужно будет выбирать какой-либо другой столбец, чтобы получить совершенно уникальную запись.

Вы хотите сказать, что k1 - 7 - это ПК? Если да, объявите его как таковой, и это будет кластеризованный индекс. Производительность запроса должна значительно улучшиться.

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

Изменить: я понимаю, что я не одинок в своем подозрении, что лучшее место для оптимизации - это ваш физический дизайн таблицы. У вас есть контроль над этим? Не зная, что хранится в каждом столбце, я не могу предложить очень конкретные идеи, но следует использовать очень общий подход: Положите K1,3,4,5 и 6 (k2, по-видимому, непосредственно связаны со значениями в вашей таблице) в собственной таблице с единственным уникальным int как PK. Затем создайте связь FK с этой таблицей. Вы PK на главной таблице затем включили это поле, k2 и k7. Теперь ваш запрос будет оптимизатором, который выполнит довольно недорогой поиск в вашей новой таблице, вернет одну запись и затем выполнит поиск индекса в вашей основной таблице только с помощью ПК.


OK

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

  • K1:10 различных значений
  • K3: 100 различных значений
  • k4: 10 различных значений
  • k5: 2 разных значения
  • k6: 2 разных значения

Если мы создадим компасный ключ из k1, k3, k4, k5 и k6, это означает, что у ключа будет только 40 000 различных комбинаций (10 * 100 * 10 * 2 * 2). Это означает, что если у нас будет 100 000 000 делений записей на 40 000, статистически у нас будет подмножество из 2500 различных записей, в которых последовательный поиск будет использоваться для завершения других ограничений предложения WHERE.

Если мы экстраполируем этот результат и сравниваем его с текущим временем выполнения (30 минут) с ключом (k1), который генерирует статистически подмножество в 10 миллионов разных записей, мы получаем:

10 000 000 rec * X sec = 30 * 60 с * 2,500 rec

= > X с = 0,45 с

Неплохо, да? Еще лучше. Как насчет того, удалим ли мы k5 и k6 из compund index? Статистически мы будем иметь подмножество в 10 000 различных записей, где будет выполняться последовательный поиск. Теоретически, сколько времени это займет? давайте посмотрим:

10,000,000 rec * X sec = 30 * 60 * 10 000 rec

= > X сек = 1,8 с

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

Надеюсь, что это поможет,


Похоже, вам нужны только самые ранние записи "g"? Может быть, только самые последние записи "g"?

В основном вы хотите, чтобы ваш запрос читал только самые последние/самые старые записи. Вы не хотите запрашивать все 400GB? Если это так, вы можете рассмотреть возможность архивирования большей части 400 ГБ или сохранение последних вставленных записей в "текущей" таблице, которую вы можете запросить. Вы можете сохранять записи в текущей таблице текущими через двойные вставки или через триггер на столе (вздрагивать). Но основная предпосылка заключается в том, что вы выполняете свой запрос с минимальной таблицы, насколько это возможно. Это в основном разбиение таблиц с бедными людьми.


Вот что я сделал бы:

  • Не создавайте отдельные индексы для каждого столбца. Вы будете тратить пространство, и они не помогут вам (если вообще)
  • Оставьте свой первичный ключ отдельно, но создайте кластерный индекс в столбце даты, так как это то, что вы используете в ORDER BY. Таким образом, механизм базы данных начнет сканирование кластерного ключа, сравните столбцы с вашими поставленными значениями и строки вывода, которые удовлетворяют условиям.
  • Для этого вам не нужны никакие другие индексы. Я считаю, что даже 100 значений из 100 миллионов для k4 будут считаться плохой избирательностью оптимизатором (хотя вы можете попробовать хотя бы).
  • если вы выбираете на основе некоторых диапазонов дат, например. только данные за последний месяц, неделю, год и т.д., вы можете захотеть разделить свою большую таблицу на "меньшие" на основе столбца даты. Эти столбцы с 10 знаками будут хорошими кандидатами для ключей разделов.

Кстати, вы указываете всю PK в запросе - предполагая AND'ing в WHERE - который выберет ровно 1 строку.


Прежде всего, потратьте день на SQL Profiler, работающий в фоновом режиме. В конце дня сохраните данные трассировки в файл и попросите мастера оптимизации залить его и оцените текущий индекс. Это должно сказать вам, если изменение индексированных полей, порядок сортировки и т.д. Может дать вам значительный выигрыш. Не позволяйте мастеру вносить изменения. Если процентное увеличение производительности выглядит значительным ( > 30% ИМХО), продолжайте и внесите изменения самостоятельно.

Ваш индекс должен быть на большой стороне. Вы можете запланировать задание (ночевка, пару раз в неделю), чтобы сделать следующее:

  • Переместить данные за определенный возраст в таблицу истории
  • дефрагментировать индекс
  • пересчитать показатели

Это ускорит работу после того, как вы настроите индексы.


Почему вы кластерировали первичный ключ? Какие столбцы могут быть NULL? Каковы длины VARCHAR? Что теперь дает вам запрос?

Вы помешаете нам, указав бессмысленные имена столбцов.

Даже если кластеризованный индекс является правильным, более селективное поле должно быть первым.

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


Используйте SQL Profiler для определения того, какие индексы создаются, он предназначен для разработки этой информации для вас и предлагает улучшенные профили выполнения.

У вас есть внешние ключи на k3, k4?

Попробуйте превратить k1, k2 в int и сделать их внешними ключами, он будет использовать намного меньше места для хранения, я бы подумал, и я думаю, что это должно быть быстрее (хотя, возможно, я ошибаюсь, я думаю, что SQL Сервер кэширует эти значения). Более того, вам легче обновить значение. Вы просто меняете имя строки внешнего ключа - вам не нужно обновлять 100 миллионов первичных ключей или что-то еще.

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

В:

SELECT TOP(g) d1 
FROM table WITH(NOLOCK) 
WHERE k1 = a WHERE k2 = b WHERE k3 = c WHERE k4 = d WHERE k5 = e WHERE k6 = f 
ORDER BY k7

Что, я полагаю, должен быть

SELECT TOP(g) d1 
FROM table WITH(NOLOCK) 
WHERE k1 = a AND k2 = b AND k3 = c AND k4 = d AND k5 = e AND k6 = f 
ORDER BY k7

Скорее всего, будет некоторый набор данных, который немедленно сократит набор записей вниз, скажем, 10 миллионов строк, до 10 000.

например.

SELECT TOP(g) d1 
FROM (SELECT * 
 FROM table k1=a AND k2=a WITH(NOLOCK)) 
WHERE AND k3 = c AND k4 = d AND k5 = e AND k6 = f 
ORDER BY k7

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

Администраторы баз данных, вероятно, имеют больше и лучших решений!


Трудно дать вам очень содержательный ответ. Рассматривали ли вы затраты на ввод-вывод диска? Где вы храните файлы базы данных - возможно, это задержка ввода-вывода? Здесь очень много переменных, которые могут повлиять на производительность. Возможно, настало время, когда ваш пользовательский интерфейс или какой-либо экран отображают данные, возможно, это время, которое занимает Сеть?

Возможно, самый простой способ - вы увидите, что наибольшая прибыль будет заключаться в разделении таблицы - если вы находитесь на Enterprise Edition SQL Server 2005.

Опять же, не имея доступа к фактическим планам запросов, статистика perfmon очень трудно сказать вам, в чем именно проблема. Ваш вопрос просто не дает нам достаточно, чтобы продолжать - и все просто догадка.


Разделить и распараллелить - проверьте план запроса, если он не показывает, что запрос распараллелен, а затем выясните, почему это не так. Возможно, вам придется разбить запрос на несколько шагов, а затем привести результаты вместе.

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

Не предполагайте, что SQL Server будет использовать все ваши ядра. Как правило, вам нужно правильно спроектировать свой запрос, чтобы можно было использовать несколько ядер. Проверьте свойства первого node в плане запроса, чтобы увидеть DOP (степень параллелизма). Если его 1 вы теряете ядра...


Это звучит весело.

Несколько вопросов:

  • Почему вы выбрали эти типы? varchar, money, bigint, int, bool? есть ли причина или просто желаю добавить какую-то хорошую забаву?
  • По какой-либо причине мы могли бы получить представление инструкции insert или TSQL или bulkinsert?
  • Можете ли вы сказать, как часто происходит ваша вставка (является ли она объемной или случайной?)
  • Поле DateTime содержит дату вставки?
  • Как вы пришли к этому? (мышление одного человека в день или команда из 20 человек, работающих как сумасшедшие в течение последних трех месяцев?)

Мне кажется несколько фактов:

  • Вы вставляете миллион строк каждый день
  • Вам нужен только последний миллион данных

Несколько замечаний пришли ко мне:

  • Если вас интересуют только последние данные, удаление/архивирование бесполезных данных может иметь смысл (начинайте с нуля каждое утро).
  • Если есть только один "вставщик" и только один "читатель", вы можете захотеть переключиться на специализированный тип (hashmap/list/deque/stack) или что-то более развитое на языке программирования.


Рассматривали ли вы создание столбца суррогатной идентификации (тип bigint) и использование этого как кластеризованный индекс? Затем создайте свой первичный ключ как некластеризованный уникальный индекс.

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

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

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

После того, как вы внедрили это, рассмотрите (ака, попробуйте и оцените;-) добавив некластеризованный индекс в столбец k7.


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

Кажется, что ваш кластеризованный индекс построен следующим образом:

create clustered index IX_Clustered on Table(k1 ASC, k2 ASC)

Однако ваши другие столбцы k * содержат только 40 000 возможных перестановок.

10 (k1) * 10 (k3) * 100 (k4) * 2 (k5) * 2 (k6) = 40 000

Вы должны вытащить уникальные комбинации этих 4 ключей в отдельную таблицу и дать каждому из них уникальный int (первичный ключ "newPK" ).

Извините псевдокод, пожалуйста:

create table SurrogateKey(
 newPK int -- /*primary key*/
, k1, k3, k4, k5, k6
)
constraint: newPK is primary key, clustered
constraint: k1, k3, k4, k5, k6 is unique

В этой таблице будет только 40 000 строк и очень быстро найти первичный ключ, newPK. Затем вы можете найти одно целое в своей большой таблице.

Ваша существующая таблица должна быть изменена, чтобы иметь следующие столбцы:

  • newPK
  • k2 (что действительно не является ключом, возможно, просто порядковым номером)
  • d1
  • k7 datetime

Учитывая вышеизложенное, вы можете изменить свой кластеризованный индекс на:

create clustered index IX_Clustered on Table(newPK ASC)

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

declare @pk int
select @pk = newPK 
from SurrogateKey
where
 k1 = @k1
 and k3 = @k3
 and k4 = @k4
 and k5 = @k5
 and k6 = @k6
select top(g1) d1, k2, k7
from Table with(read uncommitted)
where newPK = @pk
order by k7

Ваш оператор вставки должен быть изменен для запроса/вставки таблицы SurrogateKey.


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


Вы можете попробовать:

alter table MyTable
 add constraint PK_MyTable
 primary key nonclustered (k1, k2)
create clustered index IX_MyTable
 on MyTable(k4, k1, k3, k5, k6, k7)
 --decreasing order of cardinality of the filter columns

Это обеспечит, чтобы ваши дубликаты вставили ошибку.

Это может также дать указание SQL Server фильтровать на (k1, k3, k4, k5, k6) и заказывать на (k7 asc) за один проход, разрешая SQL Server передавать результаты запроса без промежуточного этапа сортировки миллиона результатов. Когда SQL Server найдет первую строку, соответствующую (k1, k3, k4, k5, k6), следующие миллионы строк или около того будут соответствовать одному и тому же фильтру и уже будут отсортированы в порядке (k7 asc). Вся фильтрация и упорядочение будут выполняться вместе, основываясь на кластерном индексе.

Если страницы хранятся последовательно и при условии, что SQL Server знает, как оптимизировать, несколько дисков стремятся спуститься по индексу, чтобы найти первую соответствующую строку, за которой следует один большой последовательный диск, считанный десятью тысячами страниц. Это должно быть быстрее, чем просить SQL Server искать по всему месту, чтобы найти строки, а затем попросить SQL Server отсортировать их в tempdb!

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


Добавьте в него один индекс с столбцами k1-k6; это должно быть лучшим.

Кроме того, если вы запускаете sp_updatestats перед каждым запросом.


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

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

k7 также следует индексировать, так как это значительно увеличит скорость предложения orderby.

Вам также может понадобиться экспериментировать (я знаю, я знаю, вы сказали, что не экспериментируйте, но это может помочь...) с созданием индекса с несколькими столбцами в этом порядке: k4, k1, k2, k3. Это, опять же, должно уменьшить количество возможных строк, возвращенных как можно быстрее.


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

Однако, если бы это был я, я бы попробовал индекс на K3, K4 (в том порядке, в котором вы чаще всего запрашиваете) (у вас уже есть индексы K1 и K2) и отдельный индексированный на K7. Я не верю, что добавление булевых полей улучшит производительность индекса.

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


Я бы сказал, что 8 Гбайт недостаточно для ОЗУ для таблицы 400 ГБ. Сервер не имеет возможности хранить соответствующие данные в памяти, если только один индекс занимает 5-8 ГБ. Таким образом, есть много и много жестких дисков, которые делают запрос медленным.

По моему мнению, увеличение объема оперативной памяти и наличие базы данных на быстром RAID (возможно, разделенное на несколько RAID-массивов?) поможет больше всего.

РЕДАКТИРОВАТЬ. Чтобы убедиться в том, что ваше настоящее узкое место, запустите Windows Performance Monitor.


Спасибо всем за вашу помощь.

Я сделал 3 исправления ошибок в исходном сообщении.

1) ГДЕ должны быть ИД.

2) k4 должен был ДЕНЬГИ не VARCHAR. Кроме того, k1 имеет длину 3.

3) k2 не должен находиться в предложении WHERE. Как правильно указывает doofledorfer, нет смысла иметь какие-либо другие инструкции WHERE, кроме полного первичного ключа.

Вот ответы на ваши вопросы:

Почему вы кластерировали первичный ключ?

У меня создалось впечатление, что по умолчанию PK был установлен как кластерный индекс. Я не изменил его.

Какие столбцы могут быть NULL?

Отсутствует.

Каковы длины VARCHAR?

Я ошибся с типами столбцов. Единственный оставшийся VARCHAR имеет длину 3.

Что теперь дает вам запрос?

Опубликовано в следующем сообщении.

Помогите мне лучше узнать о таблице. если ваш PK - k1, k2, вам не нужно выбирать любой другой столбец, чтобы получить полностью уникальную запись. Это была ошибка. Часть k2 PK не находится в предложении WHERE.

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

База данных содержит ежедневные записи (столбец d1 TEXT) или данные. Людям нужен доступ к большим объемам этих данных для запуска собственных отчетов. Им нужно отфильтровать его по нескольким значениям и доставить сортировку по времени.

Похоже, вам нужны только самые ранние записи "g"? Может быть, только самые последние записи "g"?

Да, последнее. Но я определенно их. Я заранее не знаю дату начала.

У вас есть внешние ключи на k3, k4? Нет. Это единственная таблица int DB.

Комментарии:

Даже если кластеризованный индекс является правильным, более селективное поле должно быть первым.

Более селективный индекс не используется в предложении WHERE (post edit!). Итак, я полагаю, это не должно быть первым в этом случае?

Возможно, вы захотите переместить данные за определенный возраст в таблицу истории

В настоящее время все данные используются, поэтому обрезка не является вариантом.

Возможно, вы захотите дефрагментировать индекс

В настоящее время у меня их нет. Посмотрите на него, если этот поток окажется плодотворным.

Добавьте в него один индекс с столбцами k1-k6; это должно быть лучшим.

Может ли кто-нибудь еще прокомментировать это предложение? Liggett78 поместился в камеру, что это удвоит размер БД, не помогая из-за сортировки даты-столбца. Обратите внимание, что столбец DATE не находится в предложении WHERE, он используется только для упорядочивания данных.

Попробуйте превратить k1, k2 в ints и делая их внешними ключами, он будет использовать много меньше хранения для одного, я бы думал, и я думаю, что это должно быть быстрее (хотя, возможно, я ошибаюсь, Я полагаю, SQL Server кэширует эти значения).

k2 - bigint (ошибка в исходном посте). Таким образом, изменение k1 в int (из VARCHAR (3)) является опцией. Неужели мы действительно думаем, что это будет иметь большое значение. И действительно ли люди думают, что разделение таблицы на k1, k2, d1 и k1, k2, k3, k4, k5, k7 и использование внешних ключей улучшит ситуацию?

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

например. SELECT TOP (g) d1 FROM (SELECT * FROM table WHERE k1 = a WITH (NOLOCK)) ГДЕ И k3 = c И k4 = d И k5 = e AND k6 = f ORDER BY k7

Очень интересно. Это действительно поможет? Похоже, что SQL Server был бы очень глупым, если бы он не сокращал данные аналогичным образом.

Возможно, это это время, которое занимает пользовательский интерфейс или отображать ли данные, возможно это время, затраченное на сеть?

Нет пользовательского интерфейса. Конечно, проблемы с сетью перемещаются, но меня беспокоит только время, затраченное на то, чтобы запрос начал возвращать результаты (я использую ADO.NET-считыватель данных) на данный момент - по одной штуке за раз:)

.. [видеть] наибольшее количество выигрышей... разделите таблицу

Будет ли кластеризованный индекс не иметь такого же эффекта?

Оставьте свой первичный ключ один, но создать кластеризованный индекс в вашей дате столбец, так как это то, что вы используете в СОРТИРОВАТЬ ПО. Таким образом, механизм базы данных начнет сканировать кластеризованный ключ, сравнить столбцы с предоставленными вами значения и выходные строки, которые удовлетворяют условия.

Звучит как звуковой план! Любые другие сторонники?

Подводя итог предложениям:

1) Создайте отдельные индексы для всех ключей: большинство людей не голосуют на этом?

2) Создайте отдельные индексы для клавиш с самыми разными значениями.

3) Создайте индекс нескольких столбцов в некоторых столбцах с первым столбцом с самыми разными значениями.

4) Бросьте RAM на него.


В плане запроса в основном показано следующее:

  • Первым оператором является кластерный индексный поиск с сравнениями на k1, handId?, d1, k3-k6
  • Второе - полное сканирование индекса на k1, handId? и k7
  • Третий - это, конечно, соединение для создания окончательного набора результатов
  • Сортировка ORDER BY
  • TOP n (Фильтр)

В плане предлагается индекс, который должен улучшить показатель на 81% - k1, k4, k5, k6, k3 + включить d1 и k7. Я не знаю, сколько времени потребуется для создания такого индекса и увидеть результаты, но, как я уже здесь прокомментировал, он будет эффективно удваивать размер вашей таблицы, просто потому, что почти каждый столбец присутствует в индексе. Также вставки будут медленнее.

Как было предложено многими людьми, разбиение является лучшей стратегией здесь, например. например, одна таблица имеет значения k3 от 1 до 3, другое - от 4 до 7, а третье - от 8 до 10. При разбиении на разделы SQL Server Enterprise с использованием ограничения CHECK в этом столбце оптимизатор запросов определит, какая таблица n для сканирования/поиска в зависимости от значения параметра для столбца.


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

Если вы не можете воспользоваться некоторыми конкретными знаниями ваших значений (возможно, k5 является только true, если k4 < 0 или что-то еще), вы в значительной степени смотрите на кластерное сканирование индекса. Можете также сделать это поле, которое вы заказываете.

Если посмотреть на небольшое количество различных значений в k3 - k6, вам, вероятно, нужно будет только прочитать < 1,5 миллиона строк, чтобы получить ваш топ 1 миллион. Это, вероятно, лучшее, что вы собираетесь сделать - тем более, что любой другой план потребует от вас заказать k7 в любом случае для оценки вашего предложения TOP.


что такое D1, он десятичный или длинный char, пожалуйста, вы можете это уточнить. Я бы рекомендовал создать кластеризованный индекс как (K7, k2, k1, k4), а затем создать дополнительный индекс на (k3) (создание индекса по двум значениям bool в основном бессмысленно, если распределение значений составляет около 30%/70% между значениями или если ваша таблица очень широкая, если d1).

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


Вот идея, что если вы создаете вторую таблицу со всеми значениями Lookup, а затем вместо where присоединяетесь к таблицам и делаете предложение where в новой таблице Lookup.

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

licensed under cc by-sa 3.0 with attribution.