📄 SQL Tuning by Dan Tow (O'Reilly)

Не все предикаты одинаково эффективны с точки зрения запуска index range scan. Оператор неравенства (!=, <>) чаще всего не помогает установить, какой промежуток от индекса требуется получить, и поэтому не повышает скорость исполнения запроса. Если смешивать такие предикаты с предикатами, которые могут запустить определение нужного индексного промежутка, то, так как is NULL либо неравенство все равно требует полного скана, селективность не улучшится.

При этом главным является улучшить селективность на самой таблице, а не на ее индексе, так как за одно считывание СУБД получает около 300 промежутков индекса, но за одно считавание в таблице – всего одну строчку.

Шехтель, Птичник
#moire_experience

♠️ SQL Tuning by Dan Tow (O’Reilly)
#sql #databases #selectivity

Селективность условия выборки по промежутку индекса — это доля строк таблицы, которую проверяет СУБД, пока сканирует индекс. При этом у условия не всегда есть верхняя или нижняя граница. Например, предикат salary > 4000, если предполагать, что у нас висит индекс на “salary”, не ограничивает скан индекса сверху.
Даже если у нас есть высоко селективный предикат по проиндексированной колонке, всё равно может случиться так, что использование индекса будет неэффективным. Пример: использование в предикате функций. Функцию будет сложно преобразовать в условие для поиска по индексу. Большинство СУБД не будут даже пробовать. Исключением являются оракловские функционированные индексы, когда к значению уже заранее применена конкретная функция.

Другой пример, выключающий индекс из обработки, — неявное сравнение разных типов данных: CharacterColumn=94303. В Оракле такое выражение будет преобразовано к TO_NUMBER(CharacterColumn)=94303 и, предполагая, что на CharacterColumn висит индекс, он, тем не менее, использоваться не будет. Чтобы не было такой ерунды, надо приводить типы явно на стороне клиента.

🧶🧶 > 🧶 👍

🧶 = 🧵 👎

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

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

Ещё пример неудачной попытки использовать индекс:

Indexed_Char_Col LIKE '%ABC%'

Это не может помочь, так как не задает ни начало, ни конец индексового промежутка для скана, и надо искать по всем записям.

🕳🕳💽🗜📄 Andy Pavlo speaks about databases
#sql #databases #database_memory_mechanism #Andy_Pavlo #CMU

Продолжая начатую в первом посте тему о том, как работают БД, есть отличная серия видео, видимо, ещё не законченная:
https://www.youtube.com/playlist?list=PLSE8ODhjZXjbohkNBWQs_otTrBTrjyohi

У неё незаслуженно мало просмотров. Это курс от Carnegie Mellon University, и первую лекцию Энди читает, сидя прямо в ванной в отеле.

Вы узнаёте, что несмотря на то, что реляционная алгебра называет таблицы набором кортежей, в реальности это будут не кортежи, а bags, потому что механизм установления уникальности — дополнительный по отношению к хранению записей. Итак, в реальности корректно говорить об отношении не как о неупорядоченном наборе кортежей, но как о — эээ, я не знаю, как переводится bags :) Но суть в том, что это bags.

Также вы узнаёте, что "система следует ANSI SQL-стандарту" обычно сводится к тому, что она реализовала возможности стандарта ANSI-SQL-92, а не то, что можно было бы подумать.

Третье видео (которое я смотрю сейчас) — о том, как СУБД работают на низком уровне, как обращаются с памятью и почему не надо использовать системный mmap для управления памятью. DBMS почти всегда стараются сами управлять памятью и это всегда лучше, чем позволять это делать операционной системе. Операционная система не в курсе, что у вам там по поводу семантики ваших запросов, и видит только список вызовов на чтение и запись.

🛌 SQL Tuning by Dan Tow (O’Reilly)
#sql #databases #selectivity #indexes #database_indexes

А теперь —дальше про индексы.
Предикаты со знаком "не равно" типа != или <> по проиндексированной колонке не “включают" использование индекса, так как большинство СУБД заранее сочтёт такое условие малоселективным, а потому недостойным того, чтобы лезть в индекс.
Если условие не задаёт границ для индексного промежутка по крайней мере на первой колонке индекса (если он многоатрибутный), единственный способ использовать индекс вообще — это полное чтение всего индекса, каждой его записи каждого листа. СУБД обычно избегают такого, потому что это недешево с точки зрения производительности, к тому же полное чтение индекса обычно приводит к чтению также и большей части таблицы, что вместе получается дороже, чем просто сделать full table scan.

Так, ура, разбираем пример. 🧼🧪

Пусть у нас есть таблица Persons с единственным индексом:
(Area_Code, Phone_Number, Last_Name, First_Name)

Мы составили запрос со следующим предикатом:
WHERE Area_Code=916 AND UPPER(First_Name)='IVA'

Только первое условие задаёт границы для скана индекса. Второе условие, на четвёртом атрибуте индекса, не может позволить нам сузить область поиска:
— Второй и третий атрибуты не использованы в предикате;
— Мы использовали функцию UPPER вокруг значения атрибута, это всё усложнило, и БД такая meh, чёчё.

К счастью, это всё фигня, и, на самом деле, СУБД, скорее всего, догадается проверить второе условие до того, как трогать таблицу. Так как индекс содержит нужный атрибут, БД проверит условие, используя данные из индекса. Т.е., в данном случае БД использует Area_Code, чтобы задать промежуток для чтения индеса, а затем, по мере чтения каждой записи внутри промежутка, БД будет отбрасывать записи с не интересующим пользователя атрибутом (!='IVA').
В результате мы, скорее всего, получим всего одно-два обращения чтения из самой таблицы.

Быть тележкой.

⚙️
#sql #databases #joins #nested_loop_join

Самый простой способ джойнить — это nested-loops join.
🗄→🗄→🗄

Исполнение начинается с запроса к изначальной таблице — т.н. driving table (на русском, кажется, это называется управляющая таблица) — с использованием тех условий, которые относятся именно и только к ней. Отделив нужные сроки в управляющей таблице, БД передает их в следующий “бокс”. В нём БД построчно ищет соответствующие строки во второй таблице, затем применяет предикаты второй таблицы для отсеивания ненужных записей. И так далее.
Матчинг строк первой таблицы со второй в случае nested-loop join’а обычно происходит с помощью поиска в индексе по тому ключу, по которому происходил джойн.

Розмари Трокель
#moire_experience

Парадиз Александры Вертинской
#moire_experience

#sql #postgresql
PostgreSQL (как и, наверное, другие СУБД) пользуется троичной логикой для разрешения логических предикатов (True, False, undefined). Поэтому для любых предикатов результаты не будут включать в себя строки, значения которых были NULL, так как для них логический результат будет неопределен, а возвращаются лишь строки, разрешающиеся в True.

#sql #postgresql
Чтобы явно указывать при сортировке, куда ставить NULL-значения — в конец или начало, можно приписывать nulls last или nulls first.

select * from customers order by last_name desc nulls last

По дефолту включено asc и nulls last. Для desc по дефолту — nulls first.

#sql #postgresql
В общем случае ключевые слова и команды обрабатываются в следующем порядке:
1) from — идём в нужную таблицу;
2) where — применяем предикаты для отбора по условию;
3) group by
4) having — фильтруем сагреггированные результаты;
5) select — отбираем нужные атрибуты;
6) order by — сортируем результат;
7) limit — и обрубаем нужное количество.

Ну это, конечно, упрощение, но именно из-за этого порядка мы не можем обращаться к алиасам выборки в предикатах, но можем в order by, так как он идёт после обработки select.

#sql #postgresql
В order by тоже можно использовать case when.
Например, мы хотим выводить список имеющихся стран, отсортированный по алфавиту, но первыми выводить всегда US, UK, France.

Это можно сделать так:

select country
from country
order by
  case country
    when 'US' then 0
    when 'UK' then 1
    when 'France' then 2
    else 3
  end, country;

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

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

#sql #postgresql

Как с помощью функции random() возвращать по пять рандомных клиентов (при каждом вызове разных):

select first_name, last_name, email
from customer
order by random()
limit 5;

«Стать лесом, видящим сны <...> про своих обитателей». (с) Роман Михайлов, «Изнанка крысы»

#pandas
pandas_profiling — пишут, что очень медленный пакет, но надо не забыть попробовать. Зато сразу все графички про данные.

https://towardsdatascience.com/exploring-your-data-with-just-1-line-of-python-4b35ce21a82d