Вопросы о реляционных субд, на которые никогда не хватает времени08.02.2017 10:03

Вступление

Но выяснять подробные ответы на них дорого по времени, ибо отправляют в чудесную страну многотомных мануалов, лингвистики & статистики и толстых книг.

Прошу помощи у сообщества в искоренении своего и чужого невежества.
Примечание: ответы на часть вопросов были освещены в этой статье, но раскрыты не полностью.

Свои эмпирические предположения спрячу под кат.

Вопросы

1. Что происходит в субд при размере таблицы+кэш+индексы << оперативной памяти?
   Предположения и новые вопросы

   Логика и некоторый опыт подсказывают, что если у базы в настройках указан размер меньше оперативной памяти, и в системе есть достаточно памяти для её размещения — база должна уложиться целиком в оперативку, но, т.к. ACID требует согласованность и долговечность — должна происходить фиксация на диск, хотя бы через подсистему логов. Так ли это на самом деле? Если не так- можно ли настроить субд таким образом, чтобы чтение всегда осуществлялось из оперативной памяти?
   
   
   
2. Если размер таблицы >> оперативной памяти, а индекса по ней нет (или мы в него не попадаем) как менеджер памяти определяет, какое количество места может занять под выборку из таблицы?
   Предположения

   Количество требуемого места можно попробовать найти статистически, по распределению значений внутри колонок (в подсистеме статистики субд обычно есть такие данные.).
   Количество доступной памяти — можно посчитать по средней нагрузке за последнее время, и найти ожидаемое количество свободного места.
   
   
   
3. Как и куда осуществляется поочередная выборка записей таблицы (и откуда, память/диск) для фильтрации? Какие при этом произойдут блокировки (для бд с версионированием записей и без)?
   Предположения

   • Скорее всего, есть фасад единого менеджера памяти, который в состоянии выдернуть значение из кэша/индекса, и если его там нет — взять с диска.
   • Наверняка, он имеет очередь обработки запросов на получение строк. Наверняка, порядок архитектуры: оптимизатор запросов, определяющий откуда выбираем значение — таблица/индекс, менеджер, определяющий где индекс/таблица — в памяти или на диске, кэш — есть ли там строки индекса/таблицы с заданным идентификатором, подтягивание потоком в память индекса/таблицы, прогон нужной части индекса/таблицы через оперативку с выборкой нужных значений.
     
   • Куда — скорее всего, в отдельный пул памяти процесса, если места нет — ос или сами мапим его на диск

   
   
   
   
4. Что произойдет, если размер подходящей выборки >> оперативной памяти? Какие базы умеют отдавать данные потоком входе выборки, а какие — только по полной готовности выборки? Можно ли это поведение настроить?
   Предположения и новые вопросы

   • Блокировки будут, определенно, зависеть от настроек требуемой согласованности бд (wiki, Уровень_изолированности_транзакций).
     
   • Вопрос, на который у меня всё-таки нет легкого ответа — могут ли строки меняться при чтении большого объема, несмотря на поток чтения? Будет ли это подпадать под ситуацию «грязного чтения»? Что произойдет, если другая транзакция пытается изменить строку, которую мы сейчас читаем? Является ли чтение транзакцией, которая может блокировать другие транзакции? При версионировании строк всё проще — читаем строки с версией = последняя закоммиченная на момент старта чтения
   • Теоретически, должна класться на диск средствами бд/ос

   
   
   
   
5. Как оптимизатор запросов формально доказывает эквивалентность условий в предложении ON для JOIN-запроса, и предложении WHERE этого запроса? Пример под катом.
   Пример и предположения

   • В pg неоднократно замечал отсутствие разницы в производительности и плане запросов

     select * from a 
     inner join b 
     on a.id = b.id 
     where a.n > N and b.n 

     и 
     

     select * from (select * from a where a.n > N)  as a
     inner join (select * from b where b.n 

     

   • Даже для более сложных запросов, где условия были несколько «выше» в дереве построения запросов pg неким образом доказывал эквивалентность и выбирал на join не весь объем данных, а только подходящий под выборку.
   • Т.е. есть некий мехнизм распространения условий выборки по дереву запросов. MS-SQl 2008 вел себя хуже и показывал существенную разницу во времени прохождения запросов. Почему?
     Какими методами из логики/программирования можно доказать эквивалентность on и where?
     Когда и где это работает?
     

   

   

6. Как происходит работа с индексом по более чем одному полю? Как происходит работа с b-tree индекcом, если он не влазит целиком в оперативную память? Через виртуальную память ос? Или есть способ загружать индекс частично?
   С чем связан вопрос

   Мне не удаётся понять, как организовано b-tree, когда колонок в индексе > 1 и они разных типов. Логичное предположение — ключом дерева является некоторая хэш функция от колонок, но тогда явно требуется соблюдение условия, что 
   H (a, b) > H (a) & H (a, b) > H (b) & H (a, b) < H(a +1, b) & H(a, b+1) < H(a+1)
   я догадываюсь, что с этим может помочь перцептивный хэш с некоторыми «разрядами», где разряд соответствует колонке, но меня смущает длина хэша.
   Другим вариантом мне видится не делать хэш, а явно сравнивать значения полей индекса при загрузке/выгрузке из него. Но тогда не ясно, как сработает

   select * from A 
   where  A.b > 1 A.c < 3
   

   если индекс объявлен как 
   
   Как мы будем обходить дерево не имея ограничения по первому уровню?
   Будет ли планировщик использовать данный индекс?
   Связанные вопросы — имеет ли значение порядок перечисления полей индекса в условии WHERE?
   Сможем ли мы попасть в индекс, если часть полей перечислено в предложении ON подзапроса, а часть в WHERE более общего запроса, т.е. работает ли перераспределение условий вместе с оценкой попали/не попали в индекс?
   Как на это можно повлиять?
   
   
   Более специализированные вопросы, на которые также тяжело дается ответ.
   1. При каких условиях стоит делать таблицу партиционированной?
   2. Когда партиций слишком много?
   3. На каком размере стоит задуматься о шардировании?
   4. Когда нормализовывать, а когда денормализовывать схему данных?
   5. На какой длине строки и почему именно на такой многие бд выносят её в отдельное хранилище?
   6. Почему в pg использование CTE в запросах значительно быстрее реализации с временной таблицей? Так ли это? Если так — есть ли исключения?
   
   

   Пояснения
   

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

   Спасибо за понимание.