Какова стоимость индексации нескольких столбцов БД?

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

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

Когда вы обнаружите, что некоторые часто выполняемые запросы выполняют полное сканирование таблицы (или занимают слишком много времени по другим причинам), тогда, и только тогда, вы должны добавить дополнительные индексы. Оптимизация запроса, выполняемого один раз в месяц в полночь, не дает ничего хорошего, чтобы он мог завершиться в 12:05 вместо 12:07. Тем не менее, сокращение количества запросов, обращенных к клиенту, с 5 до 2 секунд - огромное улучшение (это все еще слишком медленно, запросы, ориентированные на клиента, должны быть меньше секунды, если это возможно).

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

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

Честно говоря, я не верю, что индексация трех столбцов в таблице заставит вас страдать, если вы не планируете хранить действительно огромное количество строк :-) - индексация довольно эффективна.

После вашего редактирования, в котором говорится:

I am indexing a point_value, the user_id, and an event_id, all required for client-facing purposes. For an instance such as scoring baseball runs by player id and game id. What would be the cost of inserting about 200 new records a day, after the table holds records for two seasons, say 72,000 runs, and after 5 seasons, maybe a quarter million records? Only for illustration, but I'm expecting to insert between 25 and 200 records a day.

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

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

Теперь понятно, что это в базе данных DB2, но я не могу представить, что IBM так намного лучше, чем люди MySQL, что MySQL может обрабатывать только менее 0,01% нагрузки DB2.

Индекс предназначен для ускорения извлечения данных, поэтому вопрос должен быть таким: «К каким данным мне нужно быстро получить доступ?». Без индекса некоторые запросы будут выполнять полное сканирование таблицы (просматривая каждую строку в таблице), чтобы найти нужные данные. При большом количестве записей это будет медленная и дорогостоящая операция. Если это для отчета, который вы запускаете раз в месяц, тогда, может быть, это нормально; если он предназначен для часто используемых данных, вам понадобится индекс, чтобы улучшить работу пользователей.

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

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

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

Ничего для запросов выбора, хотя обновления и особенно вставки будут на порядок медленнее - чего вы не заметите, пока не начнете вставлять МНОГО строк одновременно ...

Фактически, у предыдущего работодателя (однопользовательская, настольная система) мы фактически УБИРАЛИ индексы перед запуском нашей «процедуры импорта», которая сначала удаляла все записи, прежде чем вставлять огромное количество записей в ту же таблицу ...

Затем, когда мы закончили работу по вставке, мы воссоздали индексы ...

Мы бы сэкономили 90% времени для этой операции, отбросив индексы перед запуском операции и заново создав индексы после этого ...

Это была база данных Sybase, но те же числа применимы для любой базы данных ...

Так что будь осторожный с индексами, это ДАЛЕКО из "бесплатных" ...

Что сказал Пакс.

Что касается описываемых вами измерений, единственная серьезная проблема, которую я могу себе представить, - это «Какова стоимость неудачного индексирования нескольких столбцов БД?»

Only for illustration, but I'm expecting to insert between 25 and 200 records a day.

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

Я провел несколько простых тестов, используя свой настоящий проект и настоящую базу данных MySql.

Мои результаты: добавление среднего индекса (1-3 столбца в индексе) к таблице - замедляет вставку на 2,1%. Итак, если вы добавите 20 индексов, ваши вставки будут медленнее на 40-50%. Но ваш выбор будет в 10-100 раз быстрее.

Так можно ли добавлять много индексов? - Как много :) Я вам свои результаты привел - решать вам!