 |
MySQL 9.0.0
Source Code Documentation
|
|
MySQL 9.0.0
Source Code Documentation
|
Hypergraph optimizer cost constants. More...
Go to the source code of this file.
Variables | |
| constexpr double | kUnitCostInMicroseconds = 0.434 |
| We define the cost unit for the MySQL hypergraph cost model as follows: A cost of 1.0 represents the average cost per row of a "SELECT * FROM t" table scan where t is an InnoDB table with ten integer columns and one million rows. More... | |
| constexpr double | kReadOneRowCost = 0.1 / kUnitCostInMicroseconds |
| Fixed cost of reading a row from the storage engine into the record buffer. More... | |
| constexpr double | kReadOneFieldCost = 0.02 / kUnitCostInMicroseconds |
| Cost of per field in the read set. More... | |
| constexpr double | kReadOneByteCost = 0.001 / kUnitCostInMicroseconds |
| Overhead per byte when reading a row. More... | |
| constexpr double | kApplyOneFilterCost = 0.025 / kUnitCostInMicroseconds |
| Cost of evaluating one filter on one row. More... | |
| constexpr double | kIndexLookupPageCost = 0.5 / kUnitCostInMicroseconds |
| The cost per page that is visited when performing an index lookup in an InnoDB B-tree. More... | |
| constexpr double | kIndexLookupFixedCost = 1.0 / kUnitCostInMicroseconds |
| Fixed cost of an index lookup when AHI is enabled (default). More... | |
| constexpr double | kIndexLookupDefaultCost = 1.0 / kUnitCostInMicroseconds |
| Default cost of an index lookup when we are missing information to compute a more accurate cost estimate. More... | |
| constexpr double | kSortOneRowCost = 0.15 / kUnitCostInMicroseconds |
| Fixed overhead per input row when sorting. More... | |
| constexpr double | kSortComparisonCost = 0.014 / kUnitCostInMicroseconds |
| Cost per comparison during sorting. More... | |
| constexpr double | kHashBuildOneRowCost = 0.65 / kUnitCostInMicroseconds |
| Hash join constants. More... | |
| constexpr double | kHashProbeOneRowCost = 0.09 / kUnitCostInMicroseconds |
| constexpr double | kHashReturnOneRowCost = 0.06 / kUnitCostInMicroseconds |
| constexpr double | kAggregateOneRowCost = 0.1 / kUnitCostInMicroseconds |
| In need of calibration. More... | |
| constexpr double | kStreamOneRowCost = 0.01 / kUnitCostInMicroseconds |
| constexpr double | kMaterializeOneRowCost = 0.1 / kUnitCostInMicroseconds |
| constexpr double | kWindowOneRowCost = 0.1 / kUnitCostInMicroseconds |
| constexpr double | kTempTableAggLookupCost = 0.1 / kUnitCostInMicroseconds |
Hypergraph optimizer cost constants.
This file contains cost constants that are used during optimization by the hypergraph optimizer. Ideally all (server) cost constants should be contained in this file, but some code paths might still lead to the old cost model (with constants in sql/opt_costconstants.h).
As we integrate more storage engines into the cost model we may add engine-specific constants. Eventually we might make some constants (or groups of related constants) user-configurable to provide users with the opportunity to customize the cost model to better reflect their actual costs.
The cost constants here have generally been calibrated in microseconds using regression analysis on a release build of the server. In order to avoid tying these constants to the execution time on a particular machine we define a cost unit in terms of a fundamental operation in MySQL (reading a row during a table scan,
For this batch of constants we include a particular measure of the unit cost in terms of microseconds. When adjusting the cost model in the future the following approach should be adopted:
|
constexpr |
In need of calibration.
|
constexpr |
Cost of evaluating one filter on one row.
Calibrated using simple integer filters, e.g. x < k, so it might be prudent to use a higher number, but then again, almost everything is calibrated on integers.
From calibration experiments we would prefer a cost model for filtering to consist of a fixed cost for filtering the row, together with a variable cost for the number of filter operations:
cost = kFilterOneRowCost + kApplyOneFilterCost * num_filter_evaluations
The expected number of filter evaluations for a row can be estimated. For example, the condition x < k1 AND x < k2 will require more filter evaluations if the selectivity of x < k1 is high, as then the second condition will also have to be evaluated. If we consider x < k1 OR x < k2, then a low selectivity of the first term will make it likely that the second term will have to be evaluated as well. Unfortunately the current cost model only provides partial support for these mechanisms, and does not support using a fixed filtering cost per row, so the constant has been adjusted to reflect this, pending a rewrite/refactoring of the filtering cost.
|
constexpr |
Hash join constants.
|
constexpr |
|
constexpr |
|
constexpr |
Default cost of an index lookup when we are missing information to compute a more accurate cost estimate.
Used e.g. with the MEMORY engine when computing the cost of index operations on a secondary non-covering index.
|
constexpr |
Fixed cost of an index lookup when AHI is enabled (default).
|
constexpr |
The cost per page that is visited when performing an index lookup in an InnoDB B-tree.
When the Adaptive Hash Index (AHI) is disabled the number of pages visited when performing an index lookup is equal to the height of the index since we traverse the tree from the root node to a leaf node, performing a binary search within each page. This constant has been calibrated with AHI disabled.
|
constexpr |
|
constexpr |
Overhead per byte when reading a row.
With a row-based format we have to process more data to extract the same number of fields when rows are larger, as measured by row length in bytes.
Note: This constant has been calibrated on tables with integer columns. We should therefore be careful about applying this cost to variable-length fields that are stored off-page. We use the length of the record buffer (TABLE_SHARE::rec_buff_length).
|
constexpr |
Cost of per field in the read set.
Used to account for the increase in cost when reading more fields from a row.
|
constexpr |
Fixed cost of reading a row from the storage engine into the record buffer.
Used in base table access paths such as TABLE_SCAN, INDEX_SCAN, INDEX_RANGE_SCAN.
|
constexpr |
Cost per comparison during sorting.
Calibrated using ORDER BY on a single INT column. The cost is of course higher if we sort on multiple columns, and of the data type is something more complex, but not so much higher that it is clear that it would be worth taking this into account in the cost model.
|
constexpr |
Fixed overhead per input row when sorting.
This represents the cost of reading a row into the sort buffer. The accuracy of the cost model could be further improved if we take into account the amount of data that is read into the sort buffer.
|
constexpr |
|
constexpr |
|
constexpr |
We define the cost unit for the MySQL hypergraph cost model as follows: A cost of 1.0 represents the average cost per row of a "SELECT * FROM t" table scan where t is an InnoDB table with ten integer columns and one million rows.
We assume that the InnoDB table is optimized (pages are full) and loaded into the buffer pool.
|
constexpr |
1.9.2