Analysis of InnoDB execution process in MySQL

The concepts in the figure can be referred to https://blog.csdn.net/qq_36364521/article/details/121727899 Medium mysql The links below understand

The overall architecture ：
1、 Link layer
2、server layer
3、 Storage engine layer

adopt sql Query configuration information ：
There are two kinds 1、variables 2、status
eg：1、show variables like ‘%query_cache%’ cache Open or not
2、show global status like ‘%Thread%’ How many links are there

Optimizer ：
explain sentence
explain format = json See more , At the same time get cost_info
eg: explain format = json select * from business_a.monitor_app_a
Get the results ：
{
“query_block”: {
“select_id”: 1,
“cost_info”: {
“query_cost”: “5.00”
},
“table”: {
“table_name”: “monitor_app_a”,
“access_type”: “ALL”,
“rows_examined_per_scan”: 20,
“rows_produced_per_join”: 20,
“filtered”: “100.00”,
“cost_info”: {
“read_cost”: “1.00”,
“eval_cost”: “4.00”,
“prefix_cost”: “5.00”,
“data_read_per_join”: “104K”
},
“used_columns”: [
“id”,
“app_name”,
“aone_app_id”,
“status”,
“is_delete”,
“create_time”,
“create_user”,
“update_time”,
“update_user”,
“domain_url”
]
}
}
}

If you want to print each sql The plan implemented can ：
1、 Turn on
set optimizer_trace = ‘enabled=on’;
2、 perform sql
A little
3、select * from information_schema.OPTIMIZER_TRACE;
see ：https://blog.csdn.net/weixin_34192816/article/details/86082371
After execution, we get ： Several stages of data ：1、sql Get ready 2、 Optimization stage 3、 Execution phase

mysql Mainstream storage ：
MyISAM、innoDB、Memory、Archive、NDB

innoDB The adaptive hash Indexes
1.innodb Its index structure is B+tree, and hash The index is innodb Features and functions provided by the storage engine
2.innodb The storage engine monitors the index table itself , If an index is monitored, it is often used , So it's considered thermal data , Then create an internal hash Indexes （ Applicable only “=” Query for , Yes “in”“<=>” These range queries do not apply ）
3. Created hash After index , If this index is queried next time , Then go straight through hash The algorithm deduces the address of the record , You can find the data directly at once , Than repeat B+tree The efficiency of querying nodes three or four times in the index is much higher

buffer pool solve ： Pre read failure , Buffer contamination