MySQ 8.0 推出直方图,性能大大提升!

MySQL 8.0 技术详解 MySQL 8.0 简介 MySQL 5.7 到 8.0,Oracle 官方跳跃了 Major Version 版本号,随之而来的就是在 MySQL 8.0 上做了许多重大更新,在往企业级数据库的路上大步前行,全新 Data Dictionary 设计,支持 Atomic DDL,全新的版本升级策略,安全和账号管理加强,InnoDB 功能增强等,目前小版本已经 release 到 8.0.16,... 阅读详情

点击上方“芋道源码”,选择“设为星标”

管她前浪,还是后浪?

能浪的浪,才是好浪!

每天 10:33 更新文章,每天掉亿点点头发...

源码精品专栏

 

来源:www.dbapub.cn/2020/09/01

/MySQL8.0直方图/

32ab2dcb1a715422471ecb6bf5289111.jpeg


查询优化器负责将SQL查询转换为尽可能高效的执行计划,但随着数据环境不断变化,查询优化器可能无法找到最佳的执行计划,导致SQL效率低下。造成这种情况的原因是优化器对查询的数据了解的不够充足,例如:每个表有多少行数据,每列中有多少不同的值,每列的数据分布情况。

因此MySQL8.0.3推出了直方图(histogram)功能,直方图是列的数据分布的近似值,其向优化器提供更多的统计信息。比如字段NULL的个数,每个不同值的百分比,最大/最小值等。

MySQL的直方图分为:等宽直方图和等高直方图,MySQL会自动分配使用哪种类型的直方图,无法干预 等宽直方图:每个bucket保存一个值以及这个值的累计频率

  • 等高直方图:每个bucket保存不同值的个数,上下限以及累计频率

直方图同时也存在一定的限制条件:

  • 不支持几何类型以及json类型的列

  • 不支持加密表和临时表

  • 无法为单列唯一索引的字段生成直方图

创建和删除直方图

创建语法

ANALYZE TABLE tbl_name UPDATE HISTOGRAM ON col_name [, col_name] WITH N BUCKETS;

创建直方图时能够同时为多个列创建直方图,但必须指定bucket数量,范围在1-1024之间,默认100。对于bucket数量应该综合考虑其有多少不同值、数据的倾斜度、精度等,建议从较低的值开始,不符合再依次增加。

删除语法

ANALYZE TABLE tbl_name DROP HISTOGRAM ON col_name [, col_name];

基于 Spring Boot + MyBatis Plus + Vue & Element 实现的后台管理系统 + 用户小程序,支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能。

项目地址:https://github.com/YunaiV/ruoyi-vue-pro

直方图信息

MySQL通过字典表column_statistics来保存直方图的定义,每行记录对应一个字段的直方图,已JSON格式保存。

root@employees 13:49:  select json_pretty(histogram) from information_schema.column_statistics where table_name='employees' and column_name='first_name';;
{
  "buckets": [
    [
      "base64:type254:QWFtZXI=",
      "base64:type254:QWRlbA==",
      0.010176045588684237,
      13
    ],
  "data-type": "string",
  "null-values": 0.0,
  "collation-id": 255,
  "last-updated": "2020-09-09 05:47:32.548874",
  "sampling-rate": 0.163495700259278,
  "histogram-type": "equi-height",
  "number-of-buckets-specified": 100
}

MySQL为employees的first_name字段分配了等高直方图,默认为100个bucket。

当生成直方图时,MySQL会将所有数据都加载到内存中,并在内存中执行所有工作。如果在大表上生成直方图,可能会将几百M的数据读取到内存中的风险,因此我们可以通过参数hitogram_generation_max_mem_size来控制生成直方图最大允许的内存量,当指定内存满足不了所有数据集时就会采用采样的方式。

root@employees 14:12:  select histogram->>'$."sampling-rate"' from information_schema.column_statistics where table_name='employees' and column_name='first_name';;
+---------------------------------+
| histogram->>'$."sampling-rate"' |
+---------------------------------+
| 0.163495700259278               |
+---------------------------------+

从MySQL8.0.19开始,存储引擎自身提供了存储在表中数据的采样实现,存储引擎不支持时,MySQL使用默认采样需要全表扫描,这样对于大表来说成本太高,采样实现避免了全表扫描提高采样性能。

通过INNODB_METRICS计数器可以监视数据页的采样情况,这需要提前开启计数器

root@employees 14:26:  SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE 'sampled%'\G
*************************** 1. row ***************************
 NAME: sampled_pages_read
COUNT: 430
*************************** 2. row ***************************
 NAME: sampled_pages_skipped
COUNT: 456
2 rows in set (0.04 sec)

采样率的计算公式为:sampled_page_read/(sampled_pages_read + sampled_pages_skipped)

基于微服务的思想,构建在 B2C 电商场景下的项目实战。核心技术栈,是 Spring Boot + Dubbo 。未来,会重构成 Spring Cloud Alibaba 。

项目地址:https://github.com/YunaiV/onemall

优化案例

复制一张表出来,源表不添加直方图,新表添加直方图

root@employees 14:32:  create table employees_like like employees;
Query OK, 0 rows affected (0.03 sec)

root@employees 14:33:  insert into employees_like select * from employees;
Query OK, 300024 rows affected (3.59 sec)
Records: 300024  Duplicates: 0  Warnings: 0

root@employees 14:33:  ANALYZE TABLE employees_like update HISTOGRAM on birth_date,first_name;
+--------------------------+-----------+----------+-------------------------------------------------------+
| Table                    | Op        | Msg_type | Msg_text                                              |
+--------------------------+-----------+----------+-------------------------------------------------------+
| employees.employees_like | histogram | status   | Histogram statistics created for column 'birth_date'. |
| employees.employees_like | histogram | status   | Histogram statistics created for column 'first_name'. |
+--------------------------+-----------+----------+-------------------------------------------------------+

分别在两张表上查看SQL的执行计划

root@employees 14:43:  explain format=json select count(*) from employees where (birth_date between '1953-05-01' and '1954-05-01') and first_name like 'A%';
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "30214.45"
    },
    "table": {
      "table_name": "employees",
      "access_type": "ALL",
      "rows_examined_per_scan": 299822,
      "rows_produced_per_join": 3700,
      "filtered": "1.23",
      "cost_info": {
        "read_cost": "29844.37",
        "eval_cost": "370.08",
        "prefix_cost": "30214.45",
        "data_read_per_join": "520K"
      },
      "used_columns": [
        "birth_date",
        "first_name"
      ],
      "attached_condition": "((`employees`.`employees`.`birth_date` between '1953-05-01' and '1954-05-01') and (`employees`.`employees`.`first_name` like 'A%'))"
    }
  }
}

root@employees 14:45:  explain format=json select count(*) from employees where (birth_date between '1953-05-01' and '1954-05-01') and first_name like 'A%';
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "18744.56"
    },
    "table": {
      "table_name": "employees",
      "access_type": "range",
      "possible_keys": [
        "idx_birth",
        "idx_first"
      ],
      "key": "idx_first",
      "used_key_parts": [
        "first_name"
      ],
      "key_length": "58",
      "rows_examined_per_scan": 41654,
      "rows_produced_per_join": 6221,
      "filtered": "14.94",
      "index_condition": "(`employees`.`employees`.`first_name` like 'A%')",
      "cost_info": {
        "read_cost": "18122.38",
        "eval_cost": "622.18",
        "prefix_cost": "18744.56",
        "data_read_per_join": "874K"
      },
      "used_columns": [
        "birth_date",
        "first_name"
      ],
      "attached_condition": "(`employees`.`employees`.`birth_date` between '1953-05-01' and '1954-05-01')"
    }
  }
}

可以看出Cost值从30214.45降到了18744.56,扫描行数从299822降到了41654,性能有所提升。

参考资料:

https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html#analyze-table-histogram-statistics-analysis

https://mysqlserverteam.com/histogram-statistics-in-mysql/



欢迎加入我的知识星球,一起探讨架构,交流源码。加入方式,长按下方二维码噢:

dedf70b650f1c9cb7985d3596ef1933f.png

已在知识星球更新源码解析如下:

1551c833ff6b6813574ccadf39625398.jpeg

daa56235a7a519eec277b4db427ea8a0.jpeg

bec1def8bb9ec1e238f5a401435682b4.jpeg

98d5158632f0ba10bb0703a224c13ad7.jpeg

最近更新《芋道 SpringBoot 2.X 入门》系列,已经 101 余篇,覆盖了 MyBatis、Redis、MongoDB、ES、分库分表、读写分离、SpringMVC、Webflux、权限、WebSocket、Dubbo、RabbitMQ、RocketMQ、Kafka、性能测试等等内容。

提供近 3W 行代码的 SpringBoot 示例,以及超 4W 行代码的电商微服务项目。

获取方式:点“在看”,关注公众号并回复 666 领取,更多内容陆续奉上。

文章有帮助的话,在看,转发吧。
谢谢支持哟 (*^__^*)
MySQL 8.0.15数据库安装与功能实战详解 你想过没有,如果一个数据页只写了前半部分就断电了怎么办?磁盘上的页就变成了“残废”,根本没法恢复!为此,InnoDB设计了双写机制先把脏页完整写入系统表空间的一个连续区域(即doublewrite buffer)成功后再复制到各自的.ibd文件中这样即使第二步失败,也能从doublewrite buffer中找回完整的原始页。查看是否开启:结果应为ON!除非你在使用ZFS这类支持原子写入的文件系统,否则千万别关! 阅读详情

相关推荐

第十九届全国大学生智能车竞赛越野信标 硬件篇

全国大学生智能汽车竞赛,越野信标组

2301_77568650的博客 4118

ruoyi框架:使用Echarts工具、Ajax技术实现【饼图】和【柱状图】的功能

ruoyi框架:使用Echarts工具、Ajax技术实现【饼图】和【柱状图】的功能 当我点击【饼状图】的功能键时,就会跳转到带有饼状图的界面 第一步在显示界面添加功能按钮【饼状图】 <a class="btn btn-warning" onclick="$.operate.echartsFull()" shiro:hasPermission="system:score:echarts"> <i class="fa fa-download"&gt

噗嗤 7216

告别PS串口焦虑:深入解析AXI Uartlite IP核如何将ZYNQ PL任意引脚变成串口(从Vivado约束到驱动文件全解析)

本文深入解析AXI Uartlite IP核如何将ZYNQ PL任意引脚变成串口,从Vivado约束到驱动文件全解析。通过AXI Uartlite IP核,开发者可以突破PS端UART数量限制,实现高效串口通信。文章详细介绍了IP核架构、Vivado工程配置、驱动开发及调试技巧,帮助开发者快速掌握这一技术。

weixin_33736649的博客 469

MySQL8.0新特性:直方图

MySQL8.0新特性:直方图查询优化器负责将SQL查询转换为尽可能高效的执行计划,但随着数据环境不断变化,查询优化器可能无法找到最佳的执行计划,导致SQL效率低下。造成这种情况的原因是优...

欢迎关注公众号:【码农突围】,回复9999,可以获取一份LeetCode刷题笔记 423

基于IWRL6432AOP的低功耗毫米波雷达设计:从硬件选型到算法调优实战

毫米波雷达作为一种非接触式物理层感知技术,通过发射和接收高频电磁波实现对目标距离、速度及微动特征的探测。其核心原理在于分析反射波的频率和相位变化,从而在复杂环境(如黑暗、雨雾)中实现稳定感知,且不涉及光学成像,天然具备隐私保护优势。在工程实践中,低功耗与小型化是技术落地的关键挑战,这直接关系到其在电池供电的物联网设备中的可行性。天线封装技术通过将天线集成于芯片内部,极大简化了射频前端设计,降低了硬件成本和开发门槛。本文以TI的IWRL6432AOP芯片为例,深入剖析其在智能恒温器等场景中实现精准人员存在检测

weixin_29189003的博客 283

Win11下VSCode配置Python虚拟环境:从venv原理到高效开发实战

Python虚拟环境是解决项目依赖冲突、保障环境隔离性的核心技术,其核心原理是通过创建独立的Python解释器与包目录,实现不同项目间的依赖隔离。在工程实践中,虚拟环境能确保开发、测试与部署环境的一致性,提升代码的可复现性与团队协作效率。对于Windows 11平台下的Python开发者,结合轻量强大的VSCode编辑器,可以构建一套标准化的开发工作流。本文以Python内置的venv模块为例,详解如何在VSCode中创建、激活与管理虚拟环境,并配置自动化终端激活、代码格式化(如autopep8)与调试功能

weixin_33693070的博客 428

wampserver3.2.0_MySQL 8.0 技术详解

MySQL 8.0 简介MySQL 5.7 到 8.0,Oracle 官方跳跃了 Major Version 版本号,随之而来的就是在 MySQL 8.0 上做了许多重大更新,在往企业级数据库的路上大步前行,全新 Data Dictionary 设计,支持 Atomic DDL,全新的版本升级策略,安全和账号管理加强,InnoDB 功能增强等,目前小版本已经 release 到 8.0.16,新的...

weixin_39535701的博客 393

UOS系统手动添加1920x1080分辨率:xrandr命令详解与持久化配置

本文详细介绍了在UOS系统中使用xrandr命令手动添加1920x1080分辨率的方法。针对系统设置中缺失该选项的常见问题,文章提供了从识别显示设备、使用cvt生成模式、添加并切换分辨率,到通过修改.profile文件实现持久化配置的完整步骤。同时涵盖了虚拟机环境、设备名识别及排错等进阶内容,帮助用户彻底解决UOS分辨率配置难题。

weixin_29300931的博客 501

mysql8更新更慢_mysql8.0插入慢的问题解决方案(一) | MySQL 技术论坛

mysql8.0 的安装就不说了,上网搜索一大堆的教程,再写到博文里面就有点多余了哦咱们来说说 5.6 升级到 8.0 之后插入数据慢的问题写一个存储过程循环往表里面插入 3000 条数据先来看 5.6 的表现:1.4s 还是可以接受的吧再来看 8.0 的表现:9.07s 比 5.6 慢了好多好多 难以接受吧并且我的还报错了具体错误:[Err] 1055 - Expression #1 of OR...

weixin_42122881的博客 3757

从时序精度到视觉盛宴:PY32F030驱动WS2812的工程艺术与避坑指南

本文深入探讨了基于PY32F030微控制器驱动WS2812智能LED的完整解决方案。通过精确的时序控制、硬件PWM结合DMA传输技术,实现了稳定可靠的驱动效果,并提供了电源管理、信号完整性和常见问题排查等实用避坑指南,帮助开发者轻松应对严格的纳秒级时序挑战。

wind6的博客 121

088、多摄像头跨镜目标追踪 ID 频繁切换?ReID 特征 + 轨迹关联 + 时间约束方案

文章摘要:本文针对多摄像头跨镜目标追踪中ID频繁切换的问题,提出了一种结合ReID特征、轨迹关联和时间约束的综合解决方案。作者分析了ID切换的三个主要原因:ReID特征不稳定、轨迹关联依赖最近邻匹配以及忽略时间约束。通过改进ReID特征提取(多层特征融合)、引入轨迹预测(卡尔曼滤波)和添加时间窗口约束,成功将ID切换率从35%降至3%以下。文章详细分享了实现细节和踩坑经验,为跨摄像头目标追踪提供了实用优化思路。

ai技术增长的博客 170

MySQL 8.0/8.4/9.0 核心区别:面试官必问(版本选型 + 升级理由)

MySQL 8.0/8.4/9.0的选型和升级,核心不是“追新”,而是“匹配业务”——2026年,8.4作为LTS版本,是生产环境的最优解;8.0适合存量系统过渡;9.0仅适合技术预研,切勿用于核心生产。开发工程师:写SQL时,无需关注版本差异(8.0/8.4完全兼容),重点了解8.4的性能优化点,面试时能说出核心区别和选型理由即可;若涉及AI场景,可了解9.0的向量类型,但不建议在项目中落地。运维/DBA:核心生产系统优先升级到8.4,享受长期支持和运维优化;

黑牛儿的博客 2008

14 | TensorFlow2对阿拉伯语手写字符数据集进行识别

该数据集由 60 名参与者书写的16,800 个字符组成,年龄范围在 19 至 40 岁之间,90% 的参与者是右手。每个参与者在两种形式上写下每个字符(从“alef”到“yeh”)十次,如图 7(a)和 7(b)所示。表格以 300 dpi 的分辨率扫描。使用 Matlab 2016a 自动分割每个块以确定每个块的坐标。该数据库分为两组:训练集(每类 13,440 个字符到 480 个图像)和测试集(每类 3,360 个字符到 120 个图像)。数据标签为1到28个类别。

微信号:RunsenLiu 2881

MySQL8.0.18新特性之bash join探究

【引言】 今年9月14日的OOW19大会,大会内容《python and mysql 8.0 document store》topic中提到MySQL即将在8.0.18中支持hash join;Hash Join引入MySQL将给SQL的性能带来显著提升。 查了下,Oracle数据库在1996年7.3版本中就已经推出了hash join功能。 2019年10月14日,oracle正式发布MySQL...

db_murphy的博客 727

OV9734 DATASHEET,完整规格书

OV9734完整规格书,包括电气参数,尺寸规格,寄存器配置表等

RU DOS版、EFI版32bit/64bit BIOS常用工具

DOS版、EFI版的RU工具,可用来查看IO、MEM、PCI、SMBIOS等资源。亲测在EFI SHELL下可用。

mysql 8介绍,MySQL 8.0 简介

MySQL 5.7 到 8.0,Oracle 官方跳跃了 Major Version 版本号,随之而来的就是在 MySQL 8.0 上做了许多重大更新,在往企业级数据库的路上大步前行,全新 Data Dictionary 设计,支持 Atomic DDL,全新的版本升级策略,安全和账号管理加强,InnoDB 功能增强等,目前小版本已经 release 到 8.0.16,新的功能仍然在持续推出。My...

weixin_30106425的博客 1万+

复分析 阿尔福斯(Ahlfors) 第三版答案 英文

答案分为十章,有部分习题没有答案,全英文复分析阿尔福斯(Ahlfors) 第三版答案

MySQL 8.0 可以操作 JSON 了,牛逼

往期热门文章:1、免费!真的免费! 2、MySQL 用 limit 为什么会影响性能? 3、List中remove()方法的陷阱,被坑惨了! 4、7 种 Spring Boot 注入 Be...

Java之间 312

Camera2Demo

在Camera2 的基础上进行封装,使得Camera2一堆初始化逻辑与Activity分离,降低耦合度,完成预览拍照并保存的操作

上一篇: 重磅:腾讯正式开源 Spring Cloud Tencent,功能真心强大!!
下一篇: 教你优雅的实现 SpringBoot 并行任务
公众号-芋道源码
博客等级 码龄10年 1640粉丝 377原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值