10.9.5 The Optimizer Cost Model — MySQL 8.4

10.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. Затем выполните FLUSH OPTIMIZER_COSTS, чтобы сообщить серверу о необходимости повторного чтения таблиц стоимости.
last_update

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

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

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

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

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

disk_temptable_create_cost, disk_temptable_row_cost

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

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

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

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

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

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

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

engine_name

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

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

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

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

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

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

Значение по умолчанию (скомпилированное) для оценки стоимости. Этот столбец — только для чтения, сгенерированный столбец, который сохраняет своё значение даже если связанная оценка стоимости изменяется. Для строк, добавленных в таблицу во время выполнения, значение этого столбца — NULL, за исключением случаев, когда строка имеет то же значение cost_name, что и одна из исходных строк, в этом случае столбец default_value имеет то же значение, что и у этой строки.

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

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

io_block_read_cost

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

Аналогично 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;