Кто нибудь объясните мне про индексы в БД, я не вижу в них никакого смысла?
Начну с того что понимание зачем индексы для БД у меня имеются, но я не понимаю в них смысла.
В ChatGPT+Copilot я задавал вопрос, а смысла в индексах, если ты все равно при разработке проекта не можешь знать, какие данные будут самыми запрашиваемые для редактирования или для запросов.
И тут я стал изучать статьи про индексы как русскоязычные, так и англоязычные и наткнулся на несколько авторов, которые говорят что индексы надо ставить уже после того, как твоя база клиентов выросла, запросы увеличились и ты теперь должен вручную это прописывать.
Потом я прочитал что в век быстрых компьютеров, оптоволокна, быстрого более 1000мбит интернета и прочего, в индексах смысл теряется, и они могут замедлить работу твоей базы данных.
Теперь у меня какой то, диссонанс случился, одни говорят про великую пользу индексах, другие про никакой пользы, третьи про то, что индексы надо ставить уже после того, как запросы увеличились, но если ты неправильно пропишешь индексы, то можешь попрощаться с быстрой оптимизацией баз данных.
Есть ли вообще ситуации, когда при WHERE, ORDER BY, JOIN, MIN, MAX, COUNT и прочих таких функциях базы данных, даже при огромной нагрузке на БД, база данных отлично справлялась и без всяких индексов?
Dmitry Roo, это всё понятно, но тут говорят и про ssd m2, быстрые процессоры и прочего.
Если у ssd m2 чтение и запись от 1200мб/сек, то и на бд это должно влиять.
Psychedelic Geek, да и хоть в памяти вся БД пусть будет, один чёрт поиск по индексу на порядки быстрее перебора при сколь-нибудь значимых объёмах данных.
Вопрос бы переформулировать. Не нужны ли индексы, а сколько надо индексов.
По своему опыту.
1 индекс на таблицу обязательно. Даже если в запросе нет условия по индексу, план выполнения строится оптимальнее, запросы работают быстрее.
А вот больше уже сильно зависит от используемой БД и самого использования. С pg например столкнулся с ситуацией, когда второй индекс сильно замедляет операции вставки и удаления. При этом этом селекты выполняются незначительно медленее. И если сравнивать организацию индексов в pg и mssql, то можно даже найти объяснения, почему то, что хорошо работало в одной БД, плохо работает в другой.
Vitsliputsli, в теории да. Но по опыту, в MS и PG быстрее, особенно если запрос возвращает множество записей. В oracle быстрее не будет, там вроде индекс в системных полях по дефолту присутствует.
Psychedelic Geek, надо пробовать, тестировать. Одной теорией тут не обойдёшься, и нет универсального решения. Сталкивался с ситуацией, когда запрос с индексом работал дольше, чем без индекса(если выборка по индексу возвращала 0 записей). MS есть кластерный индекс, в PG их нет, тоже свои нюансы использования из этого вытекают.
в теории да. Но по опыту, в MS и PG быстрее, особенно если запрос возвращает множество записей. В oracle быстрее не будет, там вроде индекс в системных полях по дефолту присутствует.
Здесь, как и чаще всего под индексом подразумевается отдельный от таблицы объект в базе данных для ускорения выборки. Вы же пишите про первичный ключ, это совсем другой вопрос, первичный ключ нужно создавать всегда, некоторые СУБД создают его автоматически, если пользователь этого не сделал, некоторые нет - разрешают "стрелять себе в ноги".
И, собственно любая нормальная таблица, т.е. с первичным ключом - это и есть кластеризованный индекс, хоть в PG, хоть в MS.
Vitsliputsli, первичный ключ - это сущность логической модели. Физически чаще всего это такой же индекс, с набором определенных свойств типа UNIQUE. В MS можно "изменить" свойства этого индекса, и он перестанет быть pk, но индекс останется. Так же в MS можно кластерным сделать любой индекс, но только один естественно, и не обязательно уникальное поле. А вот в PG можно кластеризовать в моменте таблицу по любому индексу, но он не станет из-за этого кластерным. Поэтому в PG партиционирование приходилось делать на относительно небольших таблицах, в несколько млн., которые MS или oracle переваривают без проблем.
multisu, про использование кластеризованного ключа в разных СУБД вы верно поправили, я был неправ.
Но я попрежнему не понимаю это:
1 индекс на таблицу обязательно. Даже если в запросе нет условия по индексу, план выполнения строится оптимальнее, запросы работают быстрее.
Или вы про джойн таблиц, одна из которых без кластеризованного индекса и без индекса на pk? Ну это просто ошибочное использование, а индекс в такой ситуации еще как используется.
А вот в PG можно кластеризовать в моменте таблицу по любому индексу, но он не станет из-за этого кластерным. Поэтому в PG партиционирование приходилось делать на относительно небольших таблицах, в несколько млн., которые MS или oracle переваривают без проблем.
Т.е. кластеризованный индекс деградирает меньше при увеличении колва строк?
Не думаю, что это связано с индексами, скорее с другими механизмами, например vacuum.
Индексы нужны, но не всегда и не всем.
При разработке вы действительно точно не можете спрогнозировать нагрузку на БД. Но точно и не нужно. Как правило разработчик знает свое приложение, и какие данные из БД ему нужны. Соответственно знает предикаты (блок WHERE), которые будут в тех или иных запросах. Далее, входе разработки, когда будет накоплена некоторая статистика по наиболее востребованным предикатам, можно будет уже продумать какие индексы нужны.
В ходе реальной эксплуатации приложения, даже просто у разных клиентов может возникнуть ситуация, когда у одного все хорошо, а у другого тормозит. Все зависит от профиля нагрузки и наполнения таблиц. Бывает, что таблица DML-нагружена (много изменений) и имеет много индексов - будут тормоза, связанные с дополнительной работой по поддержания индексов в актуальном состоянии. А если ещё и много ограничений целостности...
Второй вариант, когда у одного клиента таблица на 1к строк и ему хорошо без индексов, даже если таблица "горячая" по чтениям. БД просто держит её всегда в кэше.
У другого клиента эта же таблица содержит 100к записей и ему ну очень нужны какие то индексы.
Ситуация когда данные в таблице часто меняются и часто и много запрашиваются, как правило связанны с не верной архитектурой приложения. Тут нужно уже переделывать.
Ну как минимум чтение индекса при поиске обходится намного дешевле, чем чтение всей таблицы. Банально меньшее кол-во данных надо прочитать. Тем более индексы - упорядоченная структура, для поиска данных по какой-то колонке не надо читать весь индекс. Можно сравнить b-tree индекс с алфавитным указателем - чтобы найти в нём ссылку на какое-то слово, не надо читать весь алфавитный указатель, надо просто перейти к нужной букве.
Но да, не каждый индекс может быть полезен. Есть такое понятие, как селективность индекса. Условно, индекс по полю "пол" реально может не давать пользы, так как даже при использовании индекса так и так надо будет прочитать половину записей в таблице и может быть быстрее просто прочитать всю таблицу.
Рекомендую прочитать книги, например, я читал "Настройка производительности MySQL" Нихтера. Тогда всё станет ясно.
Если вся база целиком помещается в буферный кэш - да, данные могут читаться довольно быстро без индекса. Но обычно на это нет расчёта. Да и даже в этом случае высокоселективный индекс даст намного лучший результат.
Если вся база целиком помещается в буферный кэш - да, данные могут читаться довольно быстро без индекса. Но обычно на это нет расчёта.
обычно именно на это и расчет, если большинство читаемых данных не помещаются в память, то о какой-либо производительности можно забыть. В современном мире чтение с диска считается очень медленной операцией.
лучше хотя бы такой индекс, который откинет чтение пол таблицы, чем никакой
вряд ли, это очень низкая селективность, и скорее всего будет эффективнее прочитать всю таблицу, т.к. это последовательное чтение, чем через индекс выискивать каждый нужный id персонально.
Ответ очень прост. Смотришь сколько идёт ли поиск по полю, если медленно то ставишь индекс все.
Для определения быстро или медленно. Тоже легко если таблица миллион плюс записей будет то сразу да, если нет смотришь требования, запрос должен происходить максимально быстро то ставишь индекс(к примеру часто используется в подзапросе) , если не важно то не ставишь. Все весь алгоритм.
А вообще чтобы понять требуется индекс или нет смотришь сколь вообще идёт запрос в веб если он идёт в районе 1 секунды то все ок, если нет ищешь способ ускорить, сам простой способ проиндексирвать на поля по которым идёт поиск.
Так же индекс не имеет смысла ставить на поле, если ты перед этим будет фильтр, который уменьшить выборку до 10-20 значений.
К примеру у тебя в таблице, хранятся ид пользователя и его адрес. По ты точно знаешь, что только по адресу поиск не производиться, то можно фильтрануть по ид пользователя, а потомм по адресу, а адресов больше 100 99% не имеют. Тут индекс не нуден
Спасибо за Ваш ответ.
Да я часто думал о том, что если запрос идет медленно, то надо ускорять с индексами.
И при этом я видел совсем иную картину, как индексы наоборот тормозят всю работу БД.
Psychedelic Geek, они тормозят бд в 2 случаях, если в бд часто происходят удаления (в данном случае сильно заметно если индекс кластеризованыый) и если не происходит профилактических перестроения индекса периодически. А так ты кроме повышения потребления памяти не заметишь ничего.
Когда ты связываешь две таблицы по полю, то поле второй таблицы должно быть проиндексировано для мгновенного доступа, чтобы это поле могло быть отображено рядом. В FoxPro это наглядно видно. Поэтому сначала надо подумать, сделать архитектуру, а потом писать. Но так не всегда получается, и приходится добавлять индексы "на живую" под вновь появившуюся хотелку. В общем индексы нужны для быстрой связи между БД.
Спасибо за Ваш ответ.
Это именно я хотел и узнать, индексы на живую надо почти ставить, это значит риски влезать прямо либо в продакш на сервере, либо влезать на дев версию, и на живую все делать.
Таблица без индексов это как Array либо List объектов. Каждый новый индекс добавляет хранение этих объектов в Map (Dictionary) где key мапы это index key, а value это строка.
Так что просто при создании таблицы или нового запроса к ней нужно подумать хватает ли существующих индексов для быстрого выполнения запросов. Если нет, надо добавлять новый.
Для некоторых сложных индексов по нескольким полям иногда нужно замеры проводить. (По типу, если есть индекс с 3 полями, а ты ищешь по 4 полям, и не знаешь, что будет лучше, добавить новый с 4 полями, или продолжить использовать имеющийся с 3 полями)
Если очень кратко:
Вам нужно понять, что индексы это не такая уж элементарная вещь. Нельзя просто поставить на поле флажок "индексировать". Индексы это тонкая настройка, и чем сложнее выборки, тем она сложнее. Даже не рассматривая различные типы индексов, выбрать какие поля, в какой последовательности, с какой сортировкой и в каком индексе должны присутствовать бывает не так просто. Для этого нужно очень хорошо понимать и как устроены индексы, и как с ними будет работать оптимизатор и какова селективность конкретных данных. И тогда, скорость выборок можно увеличить в разы, но все это конечно не бесплатно.
Тем не менее, другой ваш вопрос, если нужно фильтровать и сортировать по всем полям таблицы, а полей очень много, здесь уже использование реляционных СУБД не оправдано и на помощь приходят другие инструменты, типа ElasticSearch.
Если заботитесь о стабильности, то индексы всегда назначаются при разработке соответствующих запросов. Если потом мы их и меняем, то потому что чтото не предусмотрели.
Индексы могут замедлить работу. Очевидно, что они замедляют вставки и апдейты, но при криворукости можно замедлить и выборки, ведь и оптимизатор тоже ошибается. Индекс это по сути еще одна таблица, что уже намекает что не всегда это будет быстрее, т.к. придется работать не с одной таблицей, а с двумя (не всегда с двумя, но опять же, в двух словах все не охватишь).
Можно попробовать порешать задачи на codeforces, сразу станет понятно, что перебор по массиву, даже не очень большому и двоичный поск по сортированному могут в сотни раз по времени отличатся хотя тут все данные в оперативке, а в реальной БД почти все на дисках, часть только горячих данных в кеше в оперативке.
Спасибо за Ваш ответ.
Придется решать, потому что индексы я видел очень редко, обычно их ставят когда проекты разрастаются уже до больших объемах.
Я чаще всего в компаниях работал с проектами с нуля, и индексы практически не прописывал.
Все за меня делал Database engineer либо человек по старше в начале моего пути.
Вот вам христоматийная задача - у вас есть телефонный справочник города вида ФИО - номер телефона.
В справочнике 1М+ записей.
Вам нужно найти номер телефона по имени.
Сколько вам потребуется времени на поиск по несортированному, неиндексированному списку в худшем случае?
Надеюсь, вопросов насчет необходимости индексов БД у вас не осталось.
Это когда ты знаешь, какие данные тебе нужно сортировать.
При маленькой базе данных, индексы никакого толка на скорость не влияют.
Индексы влияют на количество записей от 10 тысяч, а до 10 тысяч пару мсек разницы не сделают.
Тогда индексы надо ставить на все столбцы, если уж сортировать данные, то прям клепать индексы на все столбцы где нужно сортировать, считать и прочего.
Тут же говорится, что индекс в БД полезен только при больших объемах данных, на старте проекта или до тех пор пока клиентов прибавится и запросы увеличатся, индекс вообще может только затормозить работу бд.
Мое желание понять, ставить ли индексы при разработке проекта, когда данных то может и не быть практически, либо пихать их во все столбцы где нужно where, order by, count, join,min и max запросы делать.
Psychedelic Geek, в маленьких табличках, действительно, можно и без индексов обойтись.
С остальными можно, например, посмотреть статистику запросов.
У вас может быть, например, медленный запрос, но который выполняется раз в сутки - его, возможно, и не стоит оптимизировать, а могут быть например запросы, которые в секунду выполняются тысячи раз - для таких запросов оптимизация выполнения будет очень важна.
Тогда индексы надо ставить на все столбцы, если уж сортировать данные, то прям клепать индексы на все столбцы где нужно сортировать, считать и прочего.
да где такое написано? как можно было прийти к такой дичи?
Второй вариант хрестоматийной задачи. Тоже телефонный справочник: телефон, ФИО, адрес.
Рассмотри размер БД в таком случае и в случае, когда список улиц вынесен в отдельную таблицу.
да где такое написано? как можно было прийти к такой дичи?
Автор прочитал, что для быстрой сортировки по большой таблице нужен индекс по полю. Автору нужно сортировать по всем полям таблицы. Вывод - нужно вешать индексы на все поля. Если не так, то почему же никто не скажет как нужно?
Второй вариант хрестоматийной задачи. Тоже телефонный справочник: телефон, ФИО, адрес.
Рассмотри размер БД в таком случае и в случае, когда список улиц вынесен в отдельную таблицу.
понимание. Список улиц будет сильно меньше, а выбор улицы по индексу - логичнее
Да, в этом случае занимаемый объем на диске будет меньше, скорее всего...
Но индекс тут причем? Добавляя индекс мы наоборот увеличиваем занимаемый объем, и ведь цель другая.
если ты все равно при разработке проекта не можешь знать, какие данные будут самыми запрашиваемые для редактирования или для запросов.
Почему это не знаешь? Это вполне себе анализируется на этапе бизнес-требований. Конечно потом индексы можно и нужно пересматривать, но и в начале вполне это может быть понятно исходя из ТЗ.
Потом я прочитал что в век быстрых компьютеров, оптоволокна, быстрого более 1000мбит интернета и прочего, в индексах смысл теряется, и они могут замедлить работу твоей базы данных.
В век быстрых компьютеров еще и растет само по себе количество информации. И потребность оптимизации никуда не делась. К тому же - это бизнес - так что если добавив индекс можно сэкономить на инфраструктуре - то кнчн лучш едобавить индекс, а не вкидывать бабки в дорогое железо.
Ну если у автора "базы" максимум по десятку записей в таблице, и у баз единицы юзеров, то индексы ему и не нужны. В остальном вопрос настолько глупый, что я даже не знаю что сказать, я не смогу опуститься до такого уровня.
Похоже, вы совсем новичек, если считаете что оптоволокно здесь хоть что то ускорит.
С правильным индексом на приличной базе выполнение запроса ускоряется в сотни раз. Это все что нужно знать для начала
За более подробным ответом идти надо не на форумы, а в документацию используемой вами бд. Там будет подробно все расписано, а в профильных книгах будет информация как они строятся, и работают внутри.
Полный абзац!
Особенно порадовала последняя фраза вопрос, чтобы база справлялась при огромной нагрузке на БД. Автор вообще в курсе, что оптимизация и ставит целью снижение нагрузки на БД?
Автор вообще в курсе, что оптимизация и ставит целью снижение нагрузки на БД?
"Нагрузка на БД" это очень абстрактно.
После добавления индексы, у вас при insert и update по сути будет вставка не в 1 таблицу, а в 2, нагрузка на БД выросла? конечно, выросла.
Был у нас запрос который занимал 10 сек. После добавления правильного индекса он стал выполняться за 5мс. Скорость интернета здесь ни причем. Это время поиска информации в ДБ а не время передачи данных.
