In-depth understanding of how the MySQL index optimizer works

This article brings you relevant knowledge about mysql, which mainly introduces the relevant content about the working principle of the index optimizer, including the composition of MySQL Server, the MySQL optimizer selects indexes Principle and SQL cost analysis, and finally summarize the entire query process through select query. Let’s take a look at it together. I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

##1. How the MySQL optimizer selects indexes

Let’s take a look at this table. The SUB_ODR_ID field has created two related indexes. Based on what we learned before, we create a PRIMARY KEY (

ID) auto-incrementing primary key index, (LOG_ID, SUB_ODR_ID) are set as joint index and unique index, and two indexes are set for CREATE_TIME and UPDATE_TIME respectively.

CREATE TABLE `***`  (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `LOG_ID` varchar(32) NOT NULL COMMENT '交易流水号',
  `ODR_ID` varchar(32) NOT NULL COMMENT '父单号',
  `SUB_ODR_ID` varchar(32) NOT NULL COMMENT '子单号',
  `CREATE_TIME` datetime(0) NOT NULL COMMENT '创建时间',
  `CREATE_BY` varchar(32) NOT NULL COMMENT ' 创建人',
  `UPDATE_TIME` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
  `UPDATE_BY` varchar(32) NOT NULL COMMENT '更新人',
  PRIMARY KEY (`ID`) USING BTREE,
  UNIQUE INDEX `UNQ_LOG_SUBODR_ID`(`LOG_ID`, `SUB_ODR_ID`) USING BTREE,
  INDEX `IDX_ODR_ID`(`ODR_ID`) USING BTREE,
  INDEX `IDX_SUB_ID`(`SUB_ODR_ID`) USING BTREE,
  INDEX `IDX_CREATE_TIME`(`CREATE_TIME`) USING BTREE,
  INDEX `IDX_UPDATE_TIME`(`UPDATE_TIME`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 SET = utf8 COLLATE = utf8_general_ci COMMENT = '分摊业务明细表' ROW_FORMAT = Dynamic;

In the query field SUB_ODR_ID, three related indexes can theoretically be used: UNQ_LOG_SUBODR_ID, IDX_SUB_ID. How does the MySQL optimizer choose from these three indexes?

In relational databases, B-trees are just data structures used for storage.

How to use it depends on the optimizer of the database. The optimizer determines the selection of a specific index, known as the execution plan. Optimizer selection is based on cost, with the lower the cost, the higher the preference index.

1. MySQL database composition

MySQL database is composed of Server (server) layer and Engine (engine) layer.

The Serve layer has a SQL analyzer, SQL optimizer and SQL executor, which are responsible for the specific execution process of SQL statements.

The Engine layer is responsible for storing specific data, such as the most commonly used InnoDB storage engine, and the TempTable engine used to store temporary result sets in memory.

The SQL optimizer will analyze all possible execution plans and choose the lowest-cost execution. This optimizer is called CBO (cost-based optimizer).

#2. MySQL database cost calculation

In MySQL, the calculation cost of a SQL statement is easy to understand, that is, access Database (database pages, disk) Processes data.

CPU cost, which represents the calculation cost, such as comparison of index key values, comparison of record values ​​and sorting of result sets. These operations are completed at the server layer

IO cost, which represents the cost of engine-level IO. MySQL 8.0 can separately calculate the cost of reading memory IO and disk IO by distinguishing whether the table data is in memory.

Cost  = Server Cost + Engine Cost  = CPU Cost + IO Cost

MySQL optimizer believes that if a piece of SQL needs to create a disk-based temporary table, then the cost at this time is the largest, which is 20 times that of a memory-based temporary table. The cost of comparing index key values ​​and records is very low, but if there are many records to be compared, the cost can be very high.

The MySQL optimizer believes that the cost of reading from disk is 4 times the cost of memory (the cost is not static and will vary depending on the hardware).

2. MySQL query cost

Check the value of each cost and the working principle of the MySQL optimizer. We execute the following SQL statement and analyze the execution process. MySQL index The selection is based on SQL execution cost

EXPLAIN FORMAT=json 
select * from test.fork_business_detail f where f.sub_odr_id = ''

read_cost represents the cost of reading from the InnoDB storage engine;

eval_cost represents the CPU cost of the server layer;

prefix_cost represents the total cost of SQL ;

data_read_per_join represents the total number of bytes in the read record.

{
	"query_block": {
		"cost_info": {
			"query_cost": "1.20"
		},
		"table": {
			"access_type": "ref",
			"possible_keys": [
				"IDX_SUB_ID"
			],
			"key": "IDX_SUB_ID",
			"used_key_parts": [
				"SUB_ODR_ID"
			],
			"key_length": "98",
			"ref": [
				"const"
			],
			"cost_info": {
				"read_cost": "1.00",
				"eval_cost": "0.20",
				"prefix_cost": "1.20",
				"data_read_per_join": "1K"
			},
			"used_columns": [
				"ID",
				"LOG_ID",
				"ODR_ID",
				"SUB_ODR_ID",
				"CREATE_TIME",
				"CREATE_BY",
				"UPDATE_TIME",
				"UPDATE_BY"
			]
		}
	}
}

3. SELECT execution process

How to improve MySQL query performance? First, you need to understand the entire process of SQL processing by the query optimizer. The execution process of SELECT SQL is taken as an example, as shown in the following figure:

The client sends a SELECT query to the server; the server first checks the query cache. If the cache is hit, the results stored in the cache will be returned immediately. Otherwise, enter the next stage;

The server performs SQL parsing and preprocessing, and the query optimizer generates a corresponding execution plan; MySQL calls the API of the storage engine to execute the query based on the execution plan generated by the optimizer; the results will be returned to client and put into the query cache at the same time.

Recommended learning:

mysql video tutorial

The above is the detailed content of In-depth understanding of how the MySQL index optimizer works. For more information, please follow other related articles on the PHP Chinese website!