Добавление индексов часто предлагается здесь для устранения проблем с производительностью.
(Я говорю только о чтении и запросе, мы все знаем, что индексы могут замедлять запись).
Я пробовал это средство много раз, на протяжении многих лет, как на DB2, так и на MSSQL, и результат был неизменно разочаровывающим.
Я обнаружил, что независимо от того, насколько "очевидно", что индекс улучшит ситуацию, оказалось, что оптимизатор запросов был более умным, и мой хитрый индекс почти всегда делал хуже.
Я должен указать, что мой опыт связан главным образом с маленькими таблицами (< 100 000 рядов).
Может ли кто-нибудь дать некоторые приблизительные рекомендации по выбору индексирования?
Правильным ответом будет список рекомендаций, например:
В идеале ответ даст несколько поучительных примеров.
Индексы вроде как химиотерапия... слишком много, и это убивает вас... слишком мало, и вы умираете... делайте это неправильно, и вы умрете. Вы должны знать, сколько, как часто, и какой вид, чтобы это не убило вас.
Ваше оборудование, платформа, среда, загрузка все играют определенную роль. Поэтому, чтобы ответить на ваши вопросы.
Да, возможно, иногда.
Как правило, первичные ключи и внешние ключи необходимо индексировать. Обычно первичный ключ индексируется, просто определяя их как таковые, но FK не находятся в каждой базе данных (они определенно не находятся в SQL Server, я не могу говорить о других dbs). Вы будете использовать их в соединениях, поэтому для их определения важна производительность.
Теперь, если у вас есть поля, которые вы часто используете в предложениях, они также могут использовать индексы, предоставляя несколько вещей:
Сначала поле должно иметь диапазон значения. Поле бит или поле с только 2 или 3 значения будут почти никогда используйте индекс.
Вторые запросы, которые вы пишете, должны быть защищены. То есть они должны быть разработаны для использования индексов. Я подозреваю, что если вы никогда не получите улучшения в производительности от того, что похоже на вероятных кандидатов на индексы, то у вас, вероятно, есть вопросы, которые не могут быть отклонены. Например, возьмите "WHERE Name like" % Smith "как предложение where. Не зная первых символов, оптимизатор не может использовать индекс.
Маленькие таблицы редко выигрывают от индексов. Если оптимизатор может хранить все это в памяти, то это часто бывает быстрее. Если вы работаете с многомиллионными таблицами записей, вы увидите, что индексы имеют решающее значение.
Индексация может быть очень сложной, и если вас интересует предмет, я предлагаю вам получить хорошую книгу о производительности, настраивающую вашу конкретную базу данных и подробно изучая ее.
Индекс, который никогда не использовался, является пустой тратой дискового пространства, а также добавлением времени вставки/обновления/удаления. Вероятно, лучше всего сначала определить индекс кластеризации, а затем определить
дополнительные индексы, поскольку вы обнаруживаете, что пишете предложения WHERE.
Одна общая ошибка индекса, которую я вижу, - это люди, которые задаются вопросом, почему выбор на col2 (или col3) занимает так много времени, когда индекс определяется как col1 ASC, col2 ASC, col3 ASC. Если у вас есть индекс с несколькими столбцами, предложение WHERE должно использовать первый столбец в индексе или первый и второй столбцы в индексе и т.д.
Если вам нужно получить доступ к данным по col2, вам понадобится дополнительный индекс, который определяется как col2 ASC.
С небольшими таблицами доменов иногда быстрее выполнять сканирование таблицы, чем чтение строк из таблицы с использованием индекса. Это зависит от скорости вашего компьютера базы данных и скорости сети.
Always use clustered indexes.
На самом деле вы не можете не использовать их. Данные в таблице будут выложены на диске в каком-то определенном порядке в любом случае, его нельзя сохранить как кучу или что-то еще. У вас есть шанс указать, как именно эти данные будут выложены. Зачем гореть?
Когда у вас есть таблица, в которой добавляются новые записи, и вы заметили, что некоторое значение в этих записях всегда растет (например, номер вопроса StackOverflow), сделайте из него кластеризованный индекс. Затем новые данные не будут вставлены в середину, но в основном будут добавлены к файлу на диске, который является относительно дешевой операцией.
В основном, когда БД собирает данные, а живые индексы должны идти и развиваться с этим потоком. Там может быть действительно хороший индекс на таблице, но после того, как он вырос за пределами записей XXX, тот же самый индекс в той же таблице бесполезен, и в этом случае он должен быть реорганизован.
Чтобы оптимизированная и быстрая БД была единственным способом отслеживать ее все время и реорганизовывать ее за время, когда записываются записи.
Пример реальной жизни, который я получил некоторое время назад, был супер быстрый запрос, ограниченный некоторым временным диапазоном (created_at между A и B) и супер медленным запросом, где временной диапазон был другим. Тот же запрос, одна и та же база данных, одно приложение и только одно различие во временном диапазоне.
Вам нужны индексы. Только с индексами вы можете получить доступ к данным достаточно быстро.
Сделать это как можно короче:
like и sql-функции могут заставить СУБД не использовать индексы.Наконец: просмотрите планы выполнения, чтобы решить, как оптимизировать запросы.
Вы добавите индексы только для одного критического запроса. В этом случае вы точно добавите индексы, которые необходимы в запросе (многоколоночные индексы).
Если ожидается, что таблица станет целью соединения, лучше всего иметь кластеризованный индекс в этой таблице, чтобы соединения могли выполняться последовательно через страницы данных. Столбцы в кластерном индексе будут (в некоторых системах БД) включены во все остальные индексы в этой таблице, так как это значения, которые индексы будут использовать для ссылки на данные таблицы. Чтобы другие индексы не становились слишком большими, столбцы в кластерном индексе должны быть как можно более узкими, поэтому лучше всего использовать только числовые, а не характерные типы данных в кластерном индексе. В общем случае меньшее количество столбцов лучше, чем больше столбцов, но обратите внимание, что три столбца int (по 12 байт на строку) намного лучше, чем один столбец nvarchar(32) (возможно, 64 байта на строку).
Если кластеризованный индекс является узким, то несколько дополнительных индексов не должны негативно влиять на производительность даже на очень больших таблицах.
Кажется, вы смешиваете два понятия здесь.
Добавление индексов * обычно может только ускорить чтение, очень редко (почти никогда) медленнее. Добавление индекса никогда не заставляет оптимизатор запросов использовать его. Он будет использовать его только в том случае, если он считает, что он может извлечь из этого выгоду, и, как правило, он очень сообразителен в отношении этих решений.
Для вставок/обновлений, конечно, каждый индекс болит производительность немного больше... Но на другом конце спектра, например, для базы данных только для чтения (например, для базы данных адресов USPS, которая распространяется ежемесячно) при оперативном использовании не было бы никаких вставок/обновлений, поэтому единственным негативным воздействием дополнительных индексов будет дисковое пространство, которое они занимают.
Это совсем другое, что указывает, что оптимизатор запросов ИСПОЛЬЗУЕТ индекс, фактически переопределяя то, что он сделал бы на нем... Это потенциально может сделать запрос медленнее.
EDIT: Отредактировано, чтобы исключить возможность неправильного толкования чрезмерно буквальными читателями.