Как рассчитать стоимость простого запроса на выборку для одной таблицы базы данных?

Question

Employee (ename, title, dname, address) - это строковые поля одинаковой длины.

Атрибут ename является ключом-кандидатом. Отношение содержит 10000 страниц. Есть 10 буферных страниц.

Запрос: SELECT E.title, E.ename ОТ сотрудника E WHERE E.title= 'Администратор'

Предположим, что только 10% кортежей сотрудников соответствуют условию выбора.

Предположим, что кластерный индекс дерева B+ для ename доступен (единственный индекс). Какова стоимость лучшего плана?

Как бы я рассчитал эту стоимость? и если в заголовке есть кластерный индекс B+ дерева, как бы я рассчитал это?

Другой запрос: ВЫБЕРИТЕ E.ename ОТ СОТРУДНИКА E ГДЕ E.title = 'Администратор' И E.dname= 'Финансы'

Предположим, что только 10% кортежей сотрудников соответствуют условию E.title = "Администратор", только 10% соответствуют E.dname= "Финансы" и только 5% удовлетворяют обоим условиям.

Предположим, что кластеризованный индекс дерева B+ (единственный индекс) доступен. Какова стоимость лучшего плана?

Эксперты! Пожалуйста помоги. Любые комментарии / предложения будут с благодарностью. Я хочу понять весь процесс. Я провел много исследований, и мне кажется, что я знаю, как рассчитать стоимость каждой операции. Что меня смущает, так это то, что они говорят, что отношение содержит 10 000 страниц, а не как много кортежей на каждой странице? из того, что я узнал, я считаю, что мы должны иметь общий размер отношения в терминах кортежей, я прав? Зачем?

СПАСИБО всем, кто нашел время, чтобы прочитать вопрос:-)

Answer 1

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

Если у вас есть соответствующий индекс, и этот индекс достаточно избирателен (10%, вероятно, будет "достаточно избирательным"), запрос будет два шага:

1. Сканируйте часть индекса, которая является отдельным BTree.
2. Для каждого ряда, получить PRIMARY KEY (при условии, что вы используете InnoDB) с этого BTree. С этим PK найдите строку в главном BTree, которая содержит PK и Данные.

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

Если не все блоки находятся в кеше (потому что mysqld только что запустился, или потому что индекс / данные слишком велики, чтобы оставаться в кеше), тогда вы "подсчитываете обращения к диску". Это связано с тем, что доминирующей частью "затрат" является ввод / вывод. Теперь вычислить стоимость довольно сложно, потому что нужно узнать, какой процент уже кеширован, будет ли запрос "разбивать" кэш, равномерно ли разбросаны 10%, или сгруппированы вместе, или что-то промежуточное.

Поскольку (для InnoDB) PK "кластеризован" с данными, поиск по PK действует иначе, чем поиск по вторичному ключу.

10K строк это "маленький". 10 буферных страниц - что вы имеете в виду? "все строковые поля одинаковой длины" - нереально и не очень хорошая идея CHAR вместо VARCHAR, В любом случае, длины строк имеют очень мало отношения к этому обсуждению.

WHERE E.title=‘Administrator’ AND E.dname=‘Finance’ - просит INDEX(title, dname) в любом порядке.

"Полезное правило": один блок (InnoDB) может содержать 100 строк (данных или индекса). (Это, конечно, может сильно отличаться. Но иногда это удобно для "подсчета попаданий на диск".)

В моей кулинарной книге мне легче сосредоточиться на разработке "лучшего" индекса, без вычисления "затрат".

Дополнительные заметки по запросам

"Предположим, что только 10% кортежей сотрудников соответствуют условию E.title =" Администратор ", только 10% соответствуют E.dname =" Финансы "и только 5% удовлетворяют обоим условиям". В случае MySQL, здесь более подробно:

Случай 1: INDEX(title) - аналогично первому запросу - сканирование диапазона индекса на 10%, затем исследование данных.
Случай 2: INDEX(dname) - То же самое.
Случай 3: оба индекса - существует небольшая вероятность использования "пересечения слиянием индексов" для двух индексных "сканирований диапазона", слияния двух наборов вместе, а затем получения данных для строк.
Дело 4 (лучшее): INDEX(title, dname) (или в обратном порядке): возврат к просмотру диапазона индекса, но только 5% элементов.

Предпочтительным движком MySQL является InnoDB. То, что я обсуждал, предполагает это, а не MyISAM. В InnoDB "Данные" хранятся в дереве B+, как и каждый вторичный индекс. Помните об этом сходстве, обдумывая, как выполняется запрос. Отметим также, что "конечные узлы" вторичного индекса содержат PK, тем самым обеспечивая механизм для поиска остальной части записи.