8.9.5 The Optimizer Cost Model — MySQL 5.7

8.9.5 Модель стоимости оптимизатора

Для генерации планов выполнения оптимизатор использует модель стоимости, основанную на оценках стоимости различных операций, происходящих во время выполнения запроса. Оптимизатор имеет набор предварительно скомпилированных значений по умолчанию “констант стоимости” для принятия решений относительно планов выполнения.

Оптимизатор также имеет базу данных оценок стоимости, используемых во время построения плана выполнения. Эти оценки хранятся в таблицах server_cost и engine_cost в системной базе данных mysql и могут быть настроены в любое время. Цель этих таблиц — сделать возможным легкое изменение оценок стоимости, используемых оптимизатором при попытке создания планов выполнения запросов.

Общие принципы работы модели стоимости

Настраиваемая модель стоимости оптимизатора работает следующим образом:

Сервер считывает таблицы модели стоимости в память при запуске и использует значения в памяти во время выполнения. Любая не-NULL оценка стоимости, указанная в таблицах, имеет приоритет над соответствующей предварительно скомпилированной константой стоимости. Любая NULL оценка указывает оптимизатору использовать предварительно скомпилированное значение по умолчанию.
Во время выполнения сервер может повторно прочитать таблицы стоимости. Это происходит при динамической загрузке движка хранения или при выполнении оператора FLUSH OPTIMIZER_COSTS.
Таблицы стоимости позволяют администраторам сервера легко регулировать оценки стоимости, изменяя записи в таблицах. Также легко вернуться к значениям по умолчанию, установив стоимость записи в NULL. Оптимизатор использует значения стоимости в памяти, поэтому изменения в таблицах должны сопровождаться оператором FLUSH OPTIMIZER_COSTS, чтобы они вступили в силу.
Оценки стоимости в памяти, действующие в момент начала сеанса клиента, применяются в течение всего сеанса до его окончания. В частности, если сервер повторно считывает таблицы стоимости, любые изменённые оценки применяются только к сеансам, начатым после этого. Существующие сеансы остаются без изменений.
Таблицы стоимости специфичны для данного экземпляра сервера. Сервер не дублирует изменения в таблицах стоимости на репликах.

База данных модели стоимости

База данных модели стоимости оптимизатора состоит из двух таблиц в системной базе данных mysql, содержащих информацию об оценках стоимости операций, происходящих во время выполнения запроса:

server_cost: Оценки стоимости оптимизатора для общих операций сервера
engine_cost: Оценки стоимости оптимизатора для операций, специфичных для определённых движков хранения

Таблица server_cost содержит следующие столбцы:

cost_name

Имя оценки стоимости, используемой в модели стоимости. Название не чувствительно к регистру. Если сервер не распознаёт имя стоимости при чтении этой таблицы, он записывает предупреждение в журнал ошибок.
cost_value

Значение оценки стоимости. Если значение не NULL, сервер использует его как стоимость. В противном случае используется значение по умолчанию (скомпилированное значение). Администраторы базы данных могут изменить оценку стоимости, обновив этот столбец. Если сервер обнаруживает, что значение стоимости некорректно (неположительно) при чтении этой таблицы, он записывает предупреждение в журнал ошибок.

Чтобы переопределить оценку стоимости по умолчанию (для записи, указывающей NULL), установите стоимость на не-NULL значение. Чтобы вернуться к значению по умолчанию, установите значение в NULL. Затем выполните оператор FLUSH OPTIMIZER_COSTS, чтобы сообщить серверу перечитать таблицы стоимости.
last_update

Время последнего обновления строки.
comment

Описание, связанное с оценкой стоимости. Администраторы БД могут использовать этот столбец для предоставления информации о причинах, по которым строка оценки стоимости хранит определённое значение.

Первичный ключ для таблицы server_cost — столбец cost_name, поэтому невозможно создать несколько записей для любой оценки стоимости.

Сервер распознаёт следующие значения для таблицы cost_name:

disk_temptable_create_cost (по умолчанию 40.0), disk_temptable_row_cost (по умолчанию 1.0)

Оценки стоимости для временно созданных временных таблиц, хранящихся в движке хранения на диске (либо InnoDB, либо MyISAM). Увеличение этих значений повышает оценку стоимости использования внутренних временных таблиц и заставляет оптимизатор отдавать предпочтение планам запросов с меньшим использованием таких таблиц. Для получения информации об этих таблицах обратитесь к Разделу 8.4.4 «Использование внутренних временных таблиц в MySQL».

Более высокие значения по умолчанию для этих параметров диска по сравнению с значениями по умолчанию для соответствующих параметров памяти (memory_temptable_create_cost, memory_temptable_row_cost) отражают большую стоимость обработки таблиц на диске.
key_compare_cost (по умолчанию 0.1)

Стоимость сравнения ключей записей. Увеличение этого значения делает план запроса, сравнивающий множество ключей, более дорогим. Например, план запроса, выполняющий filesort, становится относительно более дорогим по сравнению с планом запроса, который избегает сортировки, используя индекс.
memory_temptable_create_cost (по умолчанию 2.0), memory_temptable_row_cost (по умолчанию 0.2)

Оценки стоимости для временно созданных временных таблиц, хранящихся в движке хранения MEMORY. Увеличение этих значений повышает оценку стоимости использования внутренних временных таблиц и заставляет оптимизатор отдавать предпочтение планам запросов с меньшим использованием таких таблиц. Для получения информации об этих таблицах обратитесь к Разделу 8.4.4 «Использование внутренних временных таблиц в MySQL».

Более низкие значения по умолчанию для этих параметров памяти по сравнению со значениями по умолчанию для соответствующих параметров диска (disk_temptable_create_cost, disk_temptable_row_cost) отражают меньшую стоимость обработки таблиц в памяти.
row_evaluate_cost (по умолчанию 0.2)

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

Таблица engine_cost содержит следующие столбцы:

engine_name

Имя движка хранения, к которому относится эта оценка стоимости. Название не чувствительно к регистру. Если значение default, оно относится ко всем движкам хранения, у которых нет собственной записи с именем. Если сервер не распознаёт имя движка при чтении этой таблицы, он записывает предупреждение в журнал ошибок.
device_type

Тип устройства, к которому относится эта оценка стоимости. Столбец предназначен для задания различных оценок стоимости для разных типов устройств хранения, таких как жёсткие диски и твердотельные накопители. В настоящее время эта информация не используется, и единственное допустимое значение — 0.
cost_name

То же, что и в таблице server_cost.
cost_value

То же, что и в таблице server_cost.
last_update

То же, что и в таблице server_cost.
comment

То же, что и в таблице server_cost.

Первичный ключ для таблицы engine_cost — кортеж, состоящий из столбцов (cost_name, engine_name, device_type), поэтому невозможно создать несколько записей для любой комбинации значений в этих столбцах.

Сервер распознаёт следующие значения для таблицы engine_cost:

io_block_read_cost (по умолчанию 1.0)

Стоимость чтения индекса или блока данных с диска. Увеличение этого значения делает план запроса, читающего множество блоков диска, более дорогим по сравнению с планом запроса, читающим меньше блоков. Например, сканирование таблицы становится относительно более дорогим по сравнению со сканированием диапазона, которое считывает меньше блоков.
memory_block_read_cost (по умолчанию 1.0)

Аналогично io_block_read_cost, но представляет стоимость чтения индекса или блока данных из буфера памяти базы данных.

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

Внесение изменений в базу данных модели стоимости

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

Изменения параметров io_block_read_cost и memory_block_read_cost наиболее вероятны для получения полезных результатов. Эти значения параметров позволяют моделям стоимости для методов доступа к данным учитывать затраты на чтение информации из различных источников; то есть, затраты на чтение информации с диска по сравнению с чтением информации, уже находящейся в буфере памяти. Например, при прочих равных условиях, установка io_block_read_cost на значение, большее, чем memory_block_read_cost, заставляет оптимизатор отдавать предпочтение планам запросов, которые читают информацию, уже находящуюся в памяти, по сравнению с планами, которые должны читать с диска.

Этот пример показывает, как изменить значение по умолчанию для io_block_read_cost:

UPDATE mysql.engine_cost
  SET cost_value = 2.0
  WHERE cost_name = 'io_block_read_cost';
FLUSH OPTIMIZER_COSTS;

Этот пример показывает, как изменить значение io_block_read_cost только для движка хранения InnoDB:

INSERT INTO mysql.engine_cost
  VALUES ('InnoDB', 0, 'io_block_read_cost', 3.0,
  CURRENT_TIMESTAMP, 'Using a slower disk for InnoDB');
FLUSH OPTIMIZER_COSTS;