MySQL engine层到server层字段过滤优化

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介:

1.1 问题描述

  执行计划的不同肯定会带来效率的不同,但是在本例中执行计划完全一致,都是全表扫描,不同的只有字段个数而已。其次,测试中都使用了where条件进行过滤(Using where),过滤后没有数据返回,常说的where过滤实际上是在server层,当然某些情况下使用ICP会提前在Innodb层过滤数据,这里不考虑ICP。
  对于大数据量访问来讲可能涉及到物理IO,首次访问和随后的访问因为Innodb buffer的关系,效率不同是正常,需要多测试几次。
_
_
_
  通过上面的测试,可以发现随着字段的不断减少,效率越来越高,并且主要的区别都在sending data下面。简单的说Innodb数据的获取和Innodb数据到server层数据的传递都包含在其中。

2.2 理论依据

https://dev.mysql.com/doc/dev/mysql-server/latest/

全表访问数据的流程

  这里将简单描述一下这种全表扫描的流程,实际上其中有一个核心接口就是row_search_mvcc,它大概包含了如下功能:

  • 通过预取缓存获取数据
  • 打开事务
  • 定位索引位置(包含使用AHI快速定位)
  • 是否开启readview
  • 通过持久化游标不断访问下一条数据
  • 加Innodb表锁、加Innodb行锁
  • 可见性判断
  • 根据主键回表(可能回表需要加行锁)
  • ICP优化
  • SEMI update优化

  下面对MySQL处理字段多少时的优化流程做出介绍:

1、通过select字段构建read_set(server 层)

  首先需要构建一个叫做read_set的位图,来表示访问的字段位置及数量。

2、初次访问定位的时候还会构建一个模板(mysql_row_templ_t)(innodb 层)

  本模板主要用于当Innodb层数据到server层做转换的时候使用,其中记录了使用的字段数量、字段的字符集、字段的类型等等。

3、初次定位数据,定位游标到主键索引的第一行记录,为全表扫描做好准备(innodb层)

  对于这种全表扫描的执行方式,定位数据就变得简单了,只需要找到主键索引的第一条数据就好了。对于全表扫描的初次定位调用函数为btr_cur_open_at_index_side_fun。

  btr_cur_open_at_index_side_func的功能就是通过B+树结构,定位叶子结点的开头第一个块,然后调用函数page_cur_set_before_first,将游标放到了所有记录的开头,目的只有一个为全表扫描做好准备。

4、获取Innodb层的第一条数据(Innodb层)

  拿到了游标过后就可以获取数据了。但是这里获取的数据只是一个指针,言外之意可以理解为整行数据,其格式也是原始的Innodb数据,其中还包含了一些伪列比如(rollback ptr和trx id)。这里实际上和访问的字段个数无关。

5、将第一行记录转换为MySQL格式(Innodb层)

  这一步完成后可以认为记录已经返回给了server层,这里就是实际的数据拷贝了,并不是指针,整个过程放到了函数row_sel_store_mysql_rec中。

  前面的模板(mysql_row_templ_t)也会在这里发挥它的作用,这是一个字段过滤的过程,先来看一个循环
for (i = 0; i < prebuilt->n_template; i++),其中prebuilt->n_template就是字段模板的个数,通过read_set的过滤,对于不需要的字段是不会建立模板的。因此这里的模板数量是和访问的字段个数一样的。

  然后在这个循环下面会调用row_sel_store_mysql_field_func然后调用row_sel_field_store_in_mysql_format_func将字段一个一个转换为MySQL的格式。其中一种类型的转换如下:

    case DATA_INT:
        /* Convert integer data from Innobase to a little-endian
        format, sign bit restored to normal */

        ptr = dest + len;

        for (;;) {
            ptr--;
            *ptr = *data;//值拷贝 内存拷贝
            if (ptr == dest) {
                break;
            }
            data++;
        }

  可以发现这是一种实际的转换,也就是需要花费内存空间的。查询的字段越多那么着这里转换的过程越长,并且这里都是实际的内存拷贝,最终这行数据会存储到row_search_mvcc的形参buffer中返回给server层。

6、对第一条数据进行where过滤(server层)

  拿到数据后当然还不能作为最终的结果返回给用户,需要在server层做一个过滤操作,这个条件比较位于函数evaluate_join_record的开头。

  如果和条件不匹配将会返回False。这里比较会最终调用Item_func的各种方法,如果等于则是Item_func_eq。

7、访问下一条数据(server 层)

  上面已经展示了访问第一条数据的大体流程,接下面需要做的就是继续访问下去,如下:

移动游标到下一行
访问数据
根据模板转换数据返回给server层
根据where条件过滤

  整个过程会持续到全部主键索引数据访问完成。

  并且row_search_mvcc的流程肯定也会有变化。但是实际的获取数据转换过程和过滤过程并没有改变。注意这些步骤除了步骤1,基本都处于sending data下面。

  到这里已经大概知道全表扫描的访问数据的流程了,就来看看一下在全表扫描流程中字段的多少到底有哪些异同点:

不同点

  • 构建的read_set不同,字段越多read_set中为‘1’的位数越多
  • 建立的模板不同,字段越多模板数量越多
  • 每行数据转换为MySQL格式的时候不同,字段越多模板越多,那么循环转换每个字段的循环次数也就越多,并且这是每行都要处理的。返回给server层的行内存消耗越大。

相同点

  • 访问的行数一致
  • 访问的流程一致
  • where过滤的方式一致

  在整个不同点中,认为最耗时的部分应该是每行数据转换为MySQL格式的消耗最大,因为每行每个字段都需要做这样的转换,这也刚好是除以sending data状态下面。线上大于10个字段的表比比皆是,如果只需要访问其中的少量字段,最好还是写实际的字段而不是‘*’,来规避这个问题。

总结
  本文中以全表扫描为列进行了解释,但是实际上任何情况下都应该缩减访问字段的数量,应该只访问需要的字段。

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
5天前
|
关系型数据库 MySQL Linux
MySQL原理简介—6.简单的生产优化案例
本文介绍了数据库和存储系统的几个主题: 1. **MySQL日志的顺序写和数据文件的随机读指标**:解释了磁盘随机读和顺序写的原理及对数据库性能的影响。 2. **Linux存储系统软件层原理及IO调度优化原理**:解析了Linux存储系统的分层架构,包括VFS、Page Cache、IO调度等,并推荐使用deadline算法优化IO调度。 3. **数据库服务器使用的RAID存储架构**:介绍了RAID技术的基本概念及其如何通过多磁盘阵列提高存储容量和数据冗余性。 4. **数据库Too many connections故障定位**:分析了MySQL连接数限制问题的原因及解决方法。
|
8天前
|
SQL 关系型数据库 MySQL
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
|
1天前
|
存储 缓存 关系型数据库
MySQL底层概述—5.InnoDB参数优化
本文介绍了MySQL数据库中与内存、日志和IO线程相关的参数优化,旨在提升数据库性能。主要内容包括: 1. 内存相关参数优化:缓冲池内存大小配置、配置多个Buffer Pool实例、Chunk大小配置、InnoDB缓存性能评估、Page管理相关参数、Change Buffer相关参数优化。 2. 日志相关参数优化:日志缓冲区配置、日志文件参数优化。 3. IO线程相关参数优化: 查询缓存参数、脏页刷盘参数、LRU链表参数、脏页刷盘相关参数。
MySQL底层概述—5.InnoDB参数优化
|
1天前
|
SQL 关系型数据库 MySQL
MySQL底层概述—7.优化原则及慢查询
本文主要介绍了:Explain概述、Explain详解、索引优化数据准备、索引优化原则详解、慢查询设置与测试、慢查询SQL优化思路
MySQL底层概述—7.优化原则及慢查询
|
3天前
|
关系型数据库 MySQL 数据库
从MySQL优化到脑力健康:技术人与效率的双重提升
聊到效率这个事,大家应该都挺有感触的吧。 不管是技术优化还是个人状态调整,怎么能更快、更省力地完成事情,都是我们每天要琢磨的事。
50 23
|
3天前
|
SQL 关系型数据库 MySQL
MySQL原理简介—11.优化案例介绍
本文介绍了四个SQL性能优化案例,涵盖不同场景下的问题分析与解决方案: 1. 禁止或改写SQL避免自动半连接优化。 2. 指定索引避免按聚簇索引全表扫描大表。 3. 按聚簇索引扫描小表减少回表次数。 4. 避免产生长事务长时间执行。
|
20天前
|
监控 关系型数据库 MySQL
Aurora MySQL负载突增应对策略与优化方案
通过以上策略,企业可以有效应对 Aurora MySQL 的负载突增,确保数据库在高负载情况下依然保持高性能和稳定性。这些优化方案涵盖了从架构设计到具体配置和监控的各个方面,能够全面提升数据库的响应速度和处理能力。在实际应用中,应根据具体的业务需求和负载特征,灵活调整和应用这些优化策略。
49 22
|
20天前
|
关系型数据库 MySQL 数据库连接
数据库连接工具连接mysql提示:“Host ‘172.23.0.1‘ is not allowed to connect to this MySQL server“
docker-compose部署mysql8服务后,连接时提示不允许连接问题解决
|
7天前
|
关系型数据库 MySQL 数据库
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤,希望对您有所帮助。
76 42
|
25天前
|
缓存 关系型数据库 MySQL
【深入了解MySQL】优化查询性能与数据库设计的深度总结
本文详细介绍了MySQL查询优化和数据库设计技巧,涵盖基础优化、高级技巧及性能监控。
186 0