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

老雪姑娘_7485

老雪姑娘_7485

2022-11-09

1763人浏览

转载

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于索引优化器工作原理的相关内容,其中包括了mysql server的组成,mysql优化器选择索引额原理以及sql成本分析,最后通过 select 查询总结整个查询过程,下面一起来看一下,希望对大家有帮助。

推荐学习:mysql视频教程

一、MySQL 优化器是如何选择索引的

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

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;

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

在关系数据库中,B+树只是用于存储的数据结构。

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

1、MySQL数据库组成

MySQL数据库由Server(服务器)层和Engine(引擎)层组成。

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

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

SQL优化器将分析所有可能的执行计划,并选择成本最低的执行。这个优化器被称为CBO(基于成本的优化器)。

2、MySQL数据库成本计算

在 MySQL中,一条 SQL 的计算成本计算,很好理解,就是访问数据库(数据库页、磁盘)+处理数据。

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

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

Drizzle ORM TypeScript SQL 数据库工具包
Drizzle ORM TypeScript SQL 数据库工具包

Drizzle ORM 是一个轻量级的 TypeScript ORM,提供类型安全的 SQL 模式声明、关系型和类 SQL 查询构建器,以及自动生成迁移。其压缩后仅 7.4kb,且无依赖项,支持 PostgreSQL、MySQL 和 SQLite,包括 Neon、Turso 和 Cloudflare D1 等无服务器数据库。

下载
Cost  = Server Cost + Engine Cost  = CPU Cost + IO Cost

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

MySQL 优化器认为,从磁盘读取的开销是内存开销的 4 倍(成本不是一成不变的会根据硬件变化)。

二、MySQL查询成本

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

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

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

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

prefix_cost表示SQL的总成本;

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的查询性能?首先,您需要了解查询优化器进行SQL处理的整个过程。SELECT SQL 的执行过程为例,如下图所示: 

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

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

推荐学习:mysql视频教程

相关文章

PHP速学视频免费教程(入门到精通)
PHP速学视频免费教程(入门到精通)

PHP怎么学习?PHP怎么入门?PHP在哪学?PHP怎么学才快?不用担心,这里为大家提供了PHP速学教程(入门到精通),有需要的小伙伴保存下载就能学习啦!

下载

相关标签:

mysql 数据库

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

更多
GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

2026.11.17

20

15

GoFrame框架安装与gf命令行工具
GoFrame框架安装与gf命令行工具

本专题围绕GoFrame框架的安装与工具链配置展开,讲清Go版本要求、gf命令行的安装方式、代理镜像与依赖准备,说明项目初始化、热编译、交叉编译打包的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.17

60

15

GoFrame框架零基础入门
GoFrame框架零基础入门

本专题整理GoFrame框架的入门知识,从模块化设计理念、环境准备到跑通第一个Web服务,讲清目录分层、配置加载、日志与中间件的用法,并对比Gin、Go-Zero的差异,帮助Go开发者建立整体认知。

2026.11.17

60

15

SQLAlchemy 一对多关系怎么建立
SQLAlchemy 一对多关系怎么建立

SQLAlchemy 一对多关系怎么建立?本专题整理父子表外键、relationship 双向映射、关联查询、级联删除和加载优化等相关文章,帮助你理解并实现常见的 ORM 关系建模。

2026.11.17

20

10

SQLAlchemy 如何增删改查
SQLAlchemy 如何增删改查

SQLAlchemy 如何增删改查?本专题整理 ORM 新增记录、条件查询、更新和删除、Session 事务提交与批量处理等实用文章,帮助你掌握 Python 数据库操作流程。

2026.11.17

60

11

SQLAlchemy ORM 模型怎么写
SQLAlchemy ORM 模型怎么写

SQLAlchemy ORM 模型怎么写?本专题整理模型类定义、Mapped 字段映射、主键与约束、表结构创建和常见报错的相关文章,帮助你从基础配置到实际建模逐步掌握 SQLAlchemy。

2026.11.17

40

16

Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

2026.11.12

180

15

Go-Zero安装与goctl环境配置教程
Go-Zero安装与goctl环境配置教程

本专题围绕Go-Zero的安装与工具链配置展开,讲清Go版本要求、goctl安装方式、代理镜像、protoc依赖与环境变量设置,说明多版本共存、升级回退与CI流水线安装的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.12

140

15

Go-Zero零基础入门教程
Go-Zero零基础入门教程

本专题整理Go-Zero的入门知识,从框架定位、环境准备到跑通第一个API服务,讲清目录分层、配置加载、日志与中间件的基本用法,并对比Gin、Kratos的差异,帮助Go开发者建立整体认知,判断它是否适合当前项目。

2026.11.12

160

15

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程