【MySQL】深入理解MySQL索引优化器原理(MySQL专栏启动)

这篇具有很好参考价值的文章主要介绍了【MySQL】深入理解MySQL索引优化器原理(MySQL专栏启动)。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

mysqleval_cost是什么,《MySQL从入门到精通与实战》,mysql,数据库,java,开发语言

📫作者简介:小明java问道之路,专注于研究 Java/ Liunx内核/ C++及汇编/计算机底层原理/源码,就职于大型金融公司后端高级工程师,擅长交易领域的高安全/可用/并发/性能的架构设计与演进、系统优化与稳定性建设。

 

📫 热衷分享,喜欢原创~ 关注我会给你带来一些不一样的认知和成长。

 

🏆 InfoQ签约作者、CSDN专家博主/后端领域优质创作者/内容合伙人、阿里云专家/签约博主、51CTO专家 🏆

 

🔥如果此文还不错的话,还请👍关注、点赞、收藏三连支持👍一下博主~ 

本文目录

本文目录

本文导读

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

1、MySQL数据库组成

2、MySQL数据库成本计算

二、MySQL查询成本

三、SELECT 执行过程

总结


本文导读

本文将解读MySQL数据库查询优化器(CBO)的工作原理。简单介绍了MySQL Server的组成,MySQL优化器选择索引额原理以及SQL成本分析,最后通过 select 查询总结整个查询过程。文章来源地址https://www.toymoban.com/news/detail-791933.html

一、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(基于成本的优化器)。

mysqleval_cost是什么,《MySQL从入门到精通与实战》,mysql,数据库,java,开发语言

2、MySQL数据库成本计算

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

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

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

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 的执行过程为例,如下图所示: 

mysqleval_cost是什么,《MySQL从入门到精通与实战》,mysql,数据库,java,开发语言

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

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

总结

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

到了这里,关于【MySQL】深入理解MySQL索引优化器原理(MySQL专栏启动)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 【MySQL】MVCC详解与MVCC实现原理(MySQL专栏启动)

    📫作者简介: 小明java问道之路,专注于研究 Java/ Liunx内核/ C++及汇编/计算机底层原理/源码,就职于大型金融公司后端高级工程师,擅长交易领域的高安全/可用/并发/性能的架构设计与演进、系统优化与稳定性建设。 📫 热衷分享,喜欢原创~ 关注我会给你带来一些不一样的

    2023年04月09日
    浏览(36)
  • Mysql性能调优——1.深入理解Mysql索引数据结构和算法

    本系列所说的Mysql性能调优,主要是针对开发者在实际环境中的sql调优,代码层面上的优化。不涉及到mysql底层代码的调优。 我们知道,一个mysql数据表,数据量小的时候,可能简单的查询耗时不会太久,性能也可以接受。但当数据量大的时候,查询速度会很缓慢。这时候我们

    2024年02月09日
    浏览(37)
  • 【MySQL】左连接右连接内连接与Hash连接、子查询原理与实战(MySQL专栏启动)

    📫作者简介: 小明java问道之路,专注于研究 Java/ Liunx内核/ C++及汇编/计算机底层原理/源码,就职于大型金融公司后端高级工程师,擅长交易领域的高安全/可用/并发/性能的架构设计与演进、系统优化与稳定性建设。    📫 热衷分享,喜欢原创~ 关注我会给你带来一些不一

    2024年02月02日
    浏览(38)
  • 理解Mysql索引原理及特性

    作为开发人员,碰到了执行时间较长的sql时,基本上大家都会说”加个索引吧”。但是索引是什么东西,索引有哪些特性,下面和大家简单讨论一下。 索引就好比书本的目录,提高数据库表数据访问速度的数据库对象。当我们的请求打过来之后,如果有目录,就会快速的定位

    2024年02月05日
    浏览(41)
  • git文件管理与索引,深入理解工作原理,java面试手册升级版

    git add 命令的意义是将暂存一个文件。以Git文件分类而言,如果一个文件是未追踪的,那么 git add 会将文件的状态转化为 已追踪状态 。如果git add 作用一个目录 ,那么该目录下的 所有文件都会被递归为已追踪状态暂存起来 。接着之前的例子,继续进行讲解。 $ git status On b

    2024年04月12日
    浏览(47)
  • MySQL(二)索引原理以及优化

    MySQL(一)基本架构、SQL语句操作、试图 MySQL(二)索引原理以及优化 MySQL(三)SQL优化、Buffer pool、Change buffer MySQL(四)事务原理及分析 MySQL(五)缓存策略 MySQL(六)主从复制 数据库三范式 MySQL数据库是用来保存海量数据的,但是海量数据涉及到一个快速查找问题,怎么

    2024年02月16日
    浏览(49)
  • 深入解析MySQL视图、索引、数据导入导出:优化查询和提高效率

    目录 1. 视图(View): 什么是视图? 为什么要使用视图? 视图的优缺点 1) 定制用户数据,聚焦特定的数据 2) 简化数据操作 3) 提高数据的安全性 4) 共享所需数据 5) 更改数据格式 6) 重用 SQL 语句 示例操作 没使用前 使用后 2. 索引(Index): 什么是索引? 为什么要使用索引?

    2024年02月13日
    浏览(64)
  • 【MySQL】MySQL索引优化——从原理分析到实践对比

    目录 使用TRACE分析MySQL优化 开启TRACE TRACE 结果集 ORDER BY GROUP BY 优化 优化方式 分页优化 不同场景的优化方式 JOIN关联优化 算法介绍 优化方式 COUNT优化 优化方式 某些情况下,MySQL是否走索引是不确定的=[,,_,,]:3,那、我就想确定。。。咋办? 首先,在 FROM 表名 后加上 FORCE IN

    2023年04月16日
    浏览(49)
  • MySQL 数据存储和优化------MySQL索引原理和优化 ---- (架构---索引---事务---锁---集群---性能---分库分表---实战---运维)持续更新

    Mysql架构体系全系列文章主目录(进不去说明还没写完) https://blog.csdn.net/grd_java/article/details/123033016 本文只是整个系列笔记的第二章:MySQL索引原理和优化,只解释索引相关概念。 索引可以提高查询效率,影响where查询和order by排序,它可以从多方面进行分类,但是实际创建时

    2024年02月02日
    浏览(53)
  • 【Elasticsearch专栏 01】深入探索:Elasticsearch的正向索引和倒排索引是什么

    首先,要明确的是,Elasticsearch本质上只使用倒排索引来实现高效的搜索和查询功能。正向索引虽然在某些数据库和搜索系统中被提及,但在Elasticsearch的上下文中并不是一个核心概念。下面我详细解释倒排索引,并简要提及正向索引以提供对比。 倒排索引是Elasticsearch中用于实

    2024年02月22日
    浏览(50)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包