Как работает поиск в SQL бд на техническом уровне?

Так или иначе, БД все равно нужно проверить каждое поле на запрос?
Наверное это будет в разы быстрее, чем массивы с индексами, т.к массив сам по себе находится уже в ОЗУ, а БД это готовый вариант, к которому можно обратиться.

Извини, что не особо помог, я в БД мало силен, но я вижу, что никто особо не отвечает, решил подискутировать вместе

собственно, в любой субд есть опция показать "план выполнения" (execution plan) - там видно, когда идет прямой просмотр таблиц, а когда подключаются индексы.

как устроены индексы, например, в MS SQL, можно глянуть вот тут, в качестве отправной точки:
https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver16
(вариант для postgresql: https://www.postgresql.org/docs/current/internals.html , пункты 67 и далее)
дальше придется почитать про B-деревья, хэш-таблицы и т.п.

Все зависит от конкретного случая организации хранения данных. Сейчас существуют системы, для которых перебрать 100М записей безо всяких индексов - как два пальца об асфальт. Например, таким образом ищут зависимости в генетических данных - там никакие индексы не спасут, только сбор гигантской статистики.
Тот самый конкретный и твой случай всегда можно посмотреть, заказав у базы план исполнения запроса, как советует глубокоуважаемая г-жа Шниц. Более того, во многих системах на этот план можно оказывать существенное влияние при помощи комментариев-подсказок, или даже составлять его самостоятельно полностью.