【MySQL】深入理解MySQL索引优化器工作原理

本文将解读MySQL数据库查询优化器&＃xff08;CBO&＃xff09;的工作原理。简单介绍了MySQL Server的组成&＃xff0c;MySQL优化器选择索引额原理以及SQL成本分析&＃xff0c;最后通过 select 查询总结整个查询过程。

下面我们来看这张表&＃xff0c;SUB_ODR_ID字段创建了相关的 2 个索引&＃xff0c;根据我们前面所学我们建立一个PRIMARY KEY (ID)自增主键索引&＃xff0c;(LOG_ID, SUB_ODR_ID)设置为联合索引、唯一索引&＃xff0c;两个时间CREATE_TIME、UPDATE_TIME分别设置两个索引。

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


在查询字段 SUB_ODR_ID 中&＃xff0c;理论上可以使用三个相关的索引&＃xff1a;UNQ_LOG_SUBODR_ID、IDX_SUB_ID&＃xff0c;MySQL优化器如何从这三个索引中进行选择&＃xff1f;

在关系数据库中&＃xff0c;B&＃43;树只是用于存储的数据结构。

如何使用它取决于数据库的优化器。优化器确定特定索引的选择&＃xff0c;即执行计划。优化器的选择基于成本&＃xff0c;成本越低&＃xff0c;首选指数越高。

1、MySQL数据库组成

MySQL数据库由Server&＃xff08;服务器&＃xff09;层和Engine&＃xff08;引擎&＃xff09;层组成。

Serve层有SQL分析器、SQL优化器和SQL执行器&＃xff0c;负责SQL语句的具体执行过程。

Engine层负责存储特定数据&＃xff0c;例如最常用的InnoDB存储引擎&＃xff0c;以及用于在内存中存储临时结果集的TempTable引擎。

SQL优化器将分析所有可能的执行计划&＃xff0c;并选择成本最低的执行。这个优化器被称为CBO&＃xff08;基于成本的优化器&＃xff09;。

​

2、MySQL数据库成本计算

在 MySQL中&＃xff0c;一条 SQL 的计算成本计算&＃xff0c;很好理解&＃xff0c;就是访问数据库&＃xff08;数据库页、磁盘&＃xff09;&＃43;处理数据。

CPU成本&＃xff0c;表示计算成本&＃xff0c;例如索引键值的比较、记录值的比较和结果集的排序。这些操作都在服务器层完成

IO成本&＃xff0c;表示引擎级IO的成本&＃xff0c;MySQL 8.0可以通过区分表的数据是否在内存中来分别计算读取内存IO和磁盘IO的成本。

Cost  &＃61; Server Cost &＃43; Engine Cost  &＃61; CPU Cost &＃43; IO Cost



MySQL优化器认为&＃xff0c;如果一段SQL需要创建一个基于磁盘的临时表&＃xff0c;那么此时的成本是最大的&＃xff0c;是基于内存的临时表的20倍。比较索引键值和记录的成本很低&＃xff0c;但如果要比较的记录很多&＃xff0c;成本就会非常大。

MySQL 优化器认为&＃xff0c;从磁盘读取的开销是内存开销的 4 倍&＃xff08;成本不是一成不变的会根据硬件变化&＃xff09;。

查看各成本的值&＃xff0c;MySQL优化器的工作原理&＃xff0c;我们执行下面这行SQL语句&＃xff0c;分析执行过程&＃xff0c;MySQL 索引选择是基于 SQL 执行成本

EXPLAIN FORMAT&＃61;json
select * from test.fork_business_detail f where f.sub_odr_id &＃61; &＃39;&＃39;



read_cost表示从InnoDB存储引擎读取的成本&＃xff1b;

eval_cost表示服务器层的CPU成本&＃xff1b;

prefix_cost表示SQL的总成本&＃xff1b;

data_read_per_join 表示读取记录中的字节总数。

{
"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"
]
}
}
}


三、SELECT 执行过程

如何提高MySQL的查询性能&＃xff1f;首先&＃xff0c;您需要了解查询优化器进行SQL处理的整个过程。SELECT SQL 的执行过程为例&＃xff0c;如下图所示&＃xff1a;

​

客户端向服务器发送SELECT查询&＃xff1b;服务器首先检查查询缓存。如果缓存被命中&＃xff0c;存储在缓存中的结果将立即返回。否则&＃xff0c;进入下一阶段&＃xff1b;

服务器执行SQL解析、预处理&＃xff0c;查询优化器生成相应的执行计划&＃xff1b;MySQL根据优化器生成的执行计划调用存储引擎的API执行查询&＃xff1b;结果将返回到客户端&＃xff0c;并同时放入查询缓存。

本文将解读MySQL数据库查询优化器&＃xff08;CBO&＃xff09;的工作原理。简单介绍了MySQL Server的组成&＃xff0c;MySQL优化器选择索引额原理以及SQL成本分析&＃xff0c;最后通过 select 查询总结整个查询过程。