Какие столбцы обычно дают хорошие показатели?

Question

В продолжение " Что такое индексы и как я могу использовать их для оптимизации запросов в моей базе данных?", Где я пытаюсь узнать об индексах, какие столбцы являются хорошими кандидатами в индексы? Специально для базы данных MS SQL?

После некоторого поиска в Google все, что я прочитал, говорит о том, что столбцы, которые обычно увеличиваются и являются уникальными, дают хороший индекс (такие вещи, как auto_increment в MySQL), я понимаю это, но я использую MS SQL и использую GUID для первичных ключей, поэтому кажется, что индексы не пойдут на пользу GUID столбцам...

Answer 1

Индексы могут играть важную роль в оптимизации запросов и быстром поиске результатов по таблицам. Так что это самый важный шаг, чтобы выбрать, какие столбцы будут индексироваться. Есть два основных места, где мы можем рассмотреть индексацию: столбцы, на которые есть ссылка в предложении WHERE, и столбцы, используемые в предложениях JOIN. Короче говоря, такие столбцы должны быть проиндексированы, по которым вы должны искать определенные записи. Предположим, у нас есть таблица с именем покупателей, где запрос SELECT использует индексы, как показано ниже:

SELECT
 buyer_id /* no need to index */
FROM buyers
WHERE first_name='Tariq' /* consider to use index */
AND last_name='Iqbal'   /* consider to use index */

Так как в разделе SELECT есть ссылка на customer_id, MySQL не будет использовать его для ограничения выбранных строк. Следовательно, нет большой необходимости индексировать его. Ниже приведен еще один пример, немного отличающийся от приведенного выше:

SELECT
 buyers.buyer_id, /* no need to index */
 country.name    /* no need to index */
FROM buyers LEFT JOIN country
ON buyers.country_id=country.country_id /* consider to use index */
WHERE
 first_name='Tariq' /* consider to use index */
AND
 last_name='Iqbal' /* consider to use index */

В соответствии с вышеупомянутыми запросами first_name, столбцы last_name могут быть проиндексированы, поскольку они расположены в предложении WHERE. Также для индексации можно рассмотреть дополнительное поле country_id из таблицы стран, поскольку оно содержится в предложении JOIN. Таким образом, индексация может быть рассмотрена для каждого поля в предложении WHERE или предложении JOIN.

Следующий список также предлагает несколько советов, которые вы всегда должны учитывать при создании индексов в своих таблицах:

• Индексируйте только те столбцы, которые требуются в предложениях WHERE и ORDER BY. Индексирование столбцов в изобилии приведет к некоторым недостаткам.
• Попробуйте воспользоваться функцией "Префикс индекса" или "Индекс нескольких столбцов" в MySQL. Если вы создаете индекс, такой как INDEX(first_name, last_name), не создавайте INDEX(first_name). Однако "Префикс индекса" или "Индекс нескольких столбцов" рекомендуется не во всех случаях поиска.
• Используйте атрибут NOT NULL для тех столбцов, в которых вы рассматриваете индексирование, чтобы значения NULL никогда не сохранялись.
• Используйте параметр --log-long-format для регистрации запросов, которые не используют индексы. Таким образом, вы можете проверить этот файл журнала и настроить ваши запросы соответственно.
• Оператор EXPLAIN помогает вам понять, как MySQL будет выполнять запрос. Он показывает, как и в каком порядке объединяются таблицы. Это может быть очень полезно для определения того, как писать оптимизированные запросы и нужно ли индексировать столбцы.

Обновление (23 февраля 15):

Любой индекс (хороший / плохой) увеличивает время вставки и обновления.

В зависимости от ваших индексов (количество индексов и тип), результат поиска. Если ваше время поиска увеличится из-за индекса, то это плохой индекс.

Вероятно, в любой книге "Страница указателя" может иметь начальную страницу главы, начинается номер страницы темы, а также начинается страница подтемы. Некоторое разъяснение на странице указателя помогает, но более подробный указатель может сбить вас с толку или напугать. Индексы также имеют память.

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

Answer 2

Некоторые люди ответили на похожий вопрос: откуда вы знаете, что такое хороший индекс?

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

Также обратите внимание на тип создаваемого вами индекса - B-деревья хороши для большинства вещей и допускают запросы диапазонов, но хеш-индексы позволяют сразу перейти к сути (но не позволяют диапазоны). У других типов индексов есть и другие плюсы и минусы.

Удачи!

Answer 3

Все зависит от того, какие запросы вы ожидаете задать относительно таблиц. Если вы запросите все строки с определенным значением для столбца X, вам придется выполнить полное сканирование таблицы, если индекс не может быть использован.

Индексы будут полезны, если:

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

Они не будут полезны, если:

• Вы выбираете большой% (>10-20%) строк в таблице
• Дополнительное использование пространства является проблемой
• Вы хотите максимизировать производительность вставки. Каждый индекс в таблице снижает производительность вставки и обновления, поскольку они должны обновляться при каждом изменении данных.

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

Answer 4

В целом (я не использую mssql, поэтому не могу комментировать конкретно), первичные ключи дают хорошие индексы. Они уникальны и должны иметь указанное значение. (Кроме того, первичные ключи делают такие хорошие индексы, что обычно они создаются автоматически).

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

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

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

Answer 5

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

Это включает в себя: внешние ключи -

select * from tblOrder where status_id=:v_outstanding

Описательные поля -

select * from tblCust where Surname like "O'Brian%"

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

select * from tblOrder where paidYN='N'

Answer 6

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

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

Answer 7

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

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

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

Answer 8

Числовые типы данных, упорядоченные в порядке возрастания или убывания, являются хорошими показателями по нескольким причинам. Во-первых, числа обычно оцениваются быстрее, чем строки (varchar, char, nvarchar и т. Д.). Во-вторых, если ваши значения не упорядочены, может потребоваться перестановка строк и / или страниц для обновления индекса. Это дополнительные накладные расходы.

Если вы используете SQL Server 2005 и настроены на использование уникальных идентификаторов (руководств), и вам НЕ нужно, чтобы они имели случайный характер, проверьте последовательный тип уникального идентификатора.

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

Answer 9

Ваш первичный ключ всегда должен быть индексом. (Я был бы удивлен, если бы он не был автоматически проиндексирован MS SQL, на самом деле.) Вы также должны индексировать столбцы SELECT или же ORDER часто; Их целью является как быстрый поиск одного значения, так и быстрая сортировка.

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

Answer 10

Основное правило: столбцы, которые часто используются в предложениях WHERE, ORDER BY и GROUP BY, или столбцы, которые часто используются в объединениях. Имейте в виду, я имею в виду индексы, а не первичный ключ

Не давать "ванильный ответ", но это действительно зависит от того, как вы получаете доступ к данным

Answer 11

Это должно быть еще быстрее, если вы используете GUID. Предположим, у вас есть записи

1. 100
2. 200
3. 3000
4. ....

Если у вас есть индекс (бинарный поиск, вы можете найти физическое местоположение искомой записи за время O( lg n) вместо последовательного поиска за O(n). Это потому, что вы не знаете, какие записи у вас есть. в твоей таблице.

Answer 12

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

Взять пример База данных участников с первичным ключом Numnber социального обеспечения участников. Мы выбираем SS, потому что приложение priamry обращается к человеку таким образом, но вы также хотите создать функцию поиска, которая будет использовать имена членов и фамилию. Затем я бы предложил создать индекс по этим двум полям.

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