Shulou(Shulou.com)06/01 Report--

In the last issue of "Statistical record Table | omni-directional understanding of mysql system library", we introduced in detail the statistical information record table in mysql system library. In this issue, we will bring you the fifth article in the series, "Optimizer cost record Table | omni-directional understanding of mysql system library". Please follow us to start the systematic learning journey of mysql system library. 01 Optimizer cost Model Overview in order to generate an execution plan, the optimizer uses a cost-based model to estimate various operating costs. The optimizer has a set of editable default "cost constants" (these values are stored in the server_cost and engine_cost tables under the mysql system database) that can be used to adjust decisions for execution plans. * server_cost: the constant value of the optimizer cost estimation used in the server routine operation. * engine_cost: estimate the constant value of the optimizer cost used for the operation of a specific storage engine. When the server starts, the cost model table is read into memory and the in-memory values are used when the execution plan is generated. Any non-NULL cost estimation constant values specified in the table take precedence. Any remaining NULL constant values are converted to built-in default constant values when used. The cost constant value allows dynamic modification during server operation (by modifying the server_cost and engine_cost tables. After the modification is completed, you need to reload the FLUSH OPTIMIZER_COSTS statement). If you find that the modification is incorrect or need to be reset, you can directly set the response cost constant value to NULL. The effect of a change to the value of a cost constant is similar to that of a global variable, which takes effect only for new connections that have been modified, but not for connections that have been established before the modification (regardless of whether or not the FLUSH OPTIMIZER_COSTS statement has been executed). The cost constant data in the server_cost and engine_ cost tables apply only to the current instance, and their changes are not replicated synchronized. These two tables are described in detail below. 02 Optimizer cost record Table details 2.1. Engine_cost

This table provides the optimizer cost estimation constant values that the query needs to use for the operations of a particular storage engine. The following is the content of the information stored in the table. Root@localhost: mysql 01:01:47 > select * from engine_cost +-+ | engine_name | device_type | cost_name | cost_value | | last_update | comment | +-+ | default | 0 | Io_block_read_cost | NULL | 2017-07-01 14:31:32 | NULL | | default | 0 | memory_block_read_cost | NULL | 2017-07-01 14:31:32 | NULL | +- -+ 2 rows in set (0.00 sec) table field meaning. ENGINE_NAME: the name of the storage engine to which this cost estimation constant applies. Names are not case sensitive. If this value is the default, it applies to all storage engines. If Server does not recognize the engine name when reading this table, a warning is written to the error log (except for the default value default, which refers to illegal values). Device_type: the type of equipment to which this cost estimation constant applies. This column is designed to specify different cost estimation constants for different types of storage devices, for example, to specify different estimation constants for mechanical and solid state drives. This field is not currently used and the only valid value is 0. Cost_name: the same fields in the server_ cost table have the same meaning. Cost_value: the same fields in the server_ cost table have the same meaning. Last_update: the same fields in the server_ cost table have the same meaning. Comment: the same fields in the server_ cost table have the same meaning. The primary key of the engine_ cost table contains three columns (cost_name,engine_name,device_type), so the combined values of these three columns must be unique and cannot create multiple entries. The effective cost constant values recorded in this table are as follows: io_block_read_cost (default 1. 0): the cost of reading an index or block from disk. Compared with the query plan when you increase this value, a query plan that reads more blocks is considered more expensive than a query plan that reads fewer blocks. For example, table scans are considered expensive compared to range scans that read fewer blocks. Memory_block_read_cost (default 1. 0): similar to io_block_read_cost, it represents an estimated constant that reads an index or block from a memory buffer. If the io_block_read_cost and memory_block_read_cost values are different, the execution plan may notice a change in execution during two runs of the same query (for example, different execution plans or different execution times). For example, suppose the cost of memory access is lower than that of disk access. In this case, you may get different plans for executing the same query twice before and after reading the data into the buffer pool before the server starts. Changes to the io_block_read_cost and memory_block_read_cost parameters may benefit the query plan, for example, setting the io_block_read_ cost value to a value greater than memory_block_read_cost makes the optimizer prefer to walk through a query plan that queries data in memory. The example information for modifying io_block_read_cost is as follows: # update existing constant values UPDATE mysql.engine_cost SET cost_value = 2.0 WHERE cost_name = 'io_block_read_cost';FLUSH OPTIMIZER_COSTS;# inserts a single row of constant values INSERT INTO mysql.engine_cost VALUES (' InnoDB', 0, 'io_block_read_cost', 3.0, CURRENT_TIMESTAMP,' Using a slower disk for InnoDB') for the innodb engine; FLUSH OPTIMIZER_COSTS;2.2. Server _ cost this table provides the optimizer cost estimation constant values that are used to query server regular operations. The following is the content of the information stored in the table. Root@localhost: mysql 01:07:25 > select * from server_cost +-+ | cost_name | cost_value | last_update | comment | +- -+ | disk_temptable_create_cost | NULL | 2017-07-01 14:31:32 | NULL | | disk_temptable_row_cost | NULL | 2017-07-01 14:31:32 | NULL | | key_compare _ cost | NULL | 2017-07-01 14:31:32 | NULL | | memory_temptable_create_cost | NULL | 2017-07-01 14:31:32 | NULL | | memory_temptable_row_cost | NULL | 2017-07-01 14:31:32 | NULL | | row_evaluate_cost | NULL | 2017-07-01 14:31:32 | NULL | +- -+ 6 rows in set (0.00 sec) table field meaning. Cost_name: the name of the cost estimation variable used in the cost model. Names are not case sensitive. If Server does not recognize the cost name when reading this table, a warning is written to the error log. Cost_value: the value of the cost estimate variable. If the value is not NULL, Server will use it directly for costing. Otherwise, it uses the default estimate (the compiled default within the code). DBA can influence the cost estimate by updating this column of values. However, you need to make sure that you specify a valid value (mindful of the field data type in the table structure), and if Server finds that the cost value is invalid (incorrect) when reading this table, a warning is written to the error log. If you need to restore the default value, simply set this field to null, and then execute the FLUSH OPTIMIZER_COSTS statement to tell Server to reread the data in the table. Last_update: when the row record was last updated. Comment: descriptive information related to cost estimation variables. PS: the server_cost table has the primary key column cost_name, so it is not possible to set multiple values for a cost estimation variable. What is recorded in the table is the cost estimation constant identified by Server, as follows: disk_temptable_create_cost (default is 40.0), disk_temptable_row_cost (default is 1.0): cost estimation constant based on disk's internal temporary table (InnoDB or MyISAM). Increasing these values increases the cost estimate for using disk-based internal temporary tables, and the query optimizer tends to use it less when making cost estimates, which is larger than the default values of the corresponding memory-based internal temporary table parameters (memory_temptable_create_cost,memory_temptable_row_cost). Key_compare_cost (default 0. 1): compare the cost constants of index key value records. Increasing this value makes the query optimizer think that querying more index key values is expensive. Because, the query plan will try to avoid file sorting (index-based sorting). Memory_temptable_create_cost (default 2. 0), memory_temptable_row_cost (default 0. 2): the cost estimation constant based on the internal temporary table of the MEMORY storage engine. Increasing these values increases the cost estimate for using internal memory temporary tables, which makes the optimizer tend to use it less. Row_evaluate_cost (default is 0. 2): evaluates the cost constant of the record row. Table scans are more expensive than range scans that read fewer rows, and query plans tend to use less table scans. This is the end of this issue, and the reference links for this issue are as follows: https://dev.mysql.com/doc/refman/5.7/en/cost-model.html

Over the mountain, you can see a sea! Keep reading our series of articles on "Comprehensive understanding of the mysql system Library" to share, and you can learn it systematically. Thank you for your reading. I'll see you next time.

| | author profile |

Luo Xiaobo Walk senior database technology expert

IT has worked for many years and is mainly responsible for the database support and after-sale second-line support of MySQL products. Participated in the design and preparation of version release system, lightweight monitoring system, operation and maintenance management platform, database management platform, familiar with MySQL architecture, Innodb storage engine, like to specialize in open source technology, has done offline database topic sharing in public many times, and published many database-related research articles.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.