当前位置: 首页 > news >正文

兵马未动,粮草先行-InnoDB统计数据是如何收集的

我们前面介绍查询成本的时候经常用到一些统计数据,比如通过SHOW TABLE STATUS可以看到关于表的统计数据,通过SHOW INDEX可以看到关于索引的统计数据,那么这些统计数据是怎么来的呢?它们是以什么方式收集的呢?本章将聚焦于InnoDB存储引擎的统计数据收集策略。

InnoDB提供了两种存储统计数据的方式:

  • 永久性的统计数据
  • 非永久性的统计数据

设计MySQL的大佬们给我们提供了系统变量innodb_stats_persistent来控制到底采用哪种方式去存储统计数据。

基于磁盘的永久性统计数据

当我们选择把某个表以及该表索引的统计数据存放到磁盘上时,实际上是把这些统计数据存储到了两个表里:

mysql> SHOW TABLES FROM mysql LIKE 'innodb%';
+---------------------------+
| Tables_in_mysql (innodb%) |
+---------------------------+
| innodb_index_stats        |
| innodb_table_stats        |
+---------------------------+
2 rows in set (0.01 sec)

可以看到,这两个表都位于mysql系统数据库下面,其中:

  • innodb_table_stats存储了关于表的统计数据,每一条记录对应着一个表的统计数据。
  • innodb_index_stats存储了关于索引的统计数据,每一条记录对应着一个索引的一个统计项的统计数据。

innodb_table_stats

在这里插入图片描述可以看到我们熟悉的single_table表的统计信息就对应着mysql.innodb_table_stats的第三条记录。几个重要统计信息项的值如下:

n_rows 的值是9693,表明single_table表中大约有9693条记录,注意这个数据是估计值。
clustered_index_size 的值是97,表明single_table表的聚簇索引占用97个页面,这个值是也是一个估计值。
sum_of_other_index_sizes 的值是175,表明single_table表的其他索引一共占用175个页面,这个值是也是一个估计值。

n_rows统计项的收集

为什么老强调n_rows这个统计项的值是估计值呢?现在就来揭晓答案。InnoDB统计一个表中有多少行记录的套路是这样的:
按照一定算法(并不是纯粹随机的)选取几个叶子节点页面,计算每个页面中主键值记录数量,然后计算平均一个页面中主键值的记录数量乘以全部叶子节点的数量就算是该表的n_rows值。
innodb_stats_persistent_sample_pages的系统变量来控制使用永久性的统计数据时,计算统计数据时采样的页面数量。

clustered_index_size和sum_of_other_index_sizes统计项的收集

  • 从数据字典里找到表的各个索引对应的根页面位置。
    系统表SYS_INDEXES里存储了各个索引对应的根页面信息。

  • 从根页面的Page Header里找到叶子节点段和非叶子节点段对应的Segment Header。
    在每个索引的根页面的Page Header部分都有两个字段:

    PAGE_BTR_SEG_LEAF:表示B+树叶子段的Segment Header信息。
    PAGE_BTR_SEG_TOP:表示B+树非叶子段的Segment Header信息。

  • 从叶子节点段和非叶子节点段的Segment Header中找到这两个段对应的INODE Entry结构。
    在这里插入图片描述
    -从对应的INODE Entry结构中可以找到该段对应所有零散的页面地址以及FREE、NOT_FULL、FULL链表的基节点。
    在这里插入图片描述

  • 直接统计零散的页面有多少个,然后从那三个链表的List Length字段中读出该段占用的区的大小,每个区占用64个页,所以就可以统计出整个段占用的页面。
    在这里插入图片描述

  • 分别计算聚簇索引的叶子结点段和非叶子节点段占用的页面数,它们的和就是clustered_index_size 的值,按照同样的套路把其余索引占用的页面数都算出来,加起来之后就是sum_of_other_index_sizes的值。

这里需要大家注意一个问题,我们说一个段的数据在非常多时(超过32个页面),会以区为单位来申请空间,这里头的问题是以区为单位申请空间中有一些页可能并没有使用,但是在统计clustered_index_size和sum_of_other_index_sizes时都把它们算进去了,所以说聚簇索引和其他的索引占用的页面数可能比这两个值要小一些。

innodb_index_stats

在这里插入图片描述

  • 先查看index_name列,这个列说明该记录是哪个索引的统计信息,从结果中我们可以看出来,PRIMARY索引(也就是主键)占了3条记录,idx_key_part索引占了6条记录。

  • 针对 index_name 列相同的记录,stat_name表示针对该索引的统计项名称,stat_value展示的是该索引在该统计项上的值,stat_description指的是来描述该统计项的含义的。我们来具体看一下一个索引都有哪些统计项:

    n_leaf_pages:表示该索引的叶子节点占用多少页面。

    size:表示该索引共占用多少页面。

    n_diff_pfxNN:表示对应的索引列不重复的值有多少。其中的NN长得有点儿怪呀,什么意思呢?

      其实NN可以被替换为01、02、03... 这样的数字。比如对于idx_key_part来说:n_diff_pfx01表示的是统计key_part1这单单一个列不重复的值有多少。n_diff_pfx02表示的是统计key_part1、key_part2这两个列组合起来不重复的值有多少。n_diff_pfx03表示的是统计key_part1、key_part2、key_part3这三个列组合起来不重复的值有多少。n_diff_pfx04表示的是统计key_part1、key_part2、key_part3、id这四个列组合起来不重复的值有多少。
    
  • 在计算某些索引列中包含多少不重复值时,需要对一些叶子节点页面进行采样,size列就表明了采样的页面数量是多少。

基于内存的非永久性统计数据

当我们把系统变量innodb_stats_persistent的值设置为OFF时,之后创建的表的统计数据默认就都是非永久性的了,或者我们直接在创建表或修改表时设置STATS_PERSISTENT属性的值为0,那么该表的统计数据就是非永久性的了。

innodb_stats_method的使用

设计MySQL的大佬蛮贴心的,他们提供了一个名为 innodb_stats_method 的系统变量,相当于在计算某个索引列不重复值的数量时如何对待NULL值这个锅甩给了用户,这个系统变量有三个候选值:

  • nulls_equal:认为所有NULL值都是相等的。这个值也是innodb_stats_method的默认值。
    如果某个索引列中NULL值特别多的话,这种统计方式会让优化器认为某个列中平均一个值重复次数特别多,所以倾向于不使用索引进行访问。

  • nulls_unequal:认为所有NULL值都是不相等的。
    如果某个索引列中NULL值特别多的话,这种统计方式会让优化器认为某个列中平均一个值重复次数特别少,所以倾向于使用索引进行访问。

  • nulls_ignored:直接把NULL值忽略掉。

反正这个锅是甩给用户了,当你选定了innodb_stats_method值之后,优化器即使选择了不是最优的执行计划,那也跟设计MySQL的大佬们没关系了~ 当然对于用户的我们来说,最好不在索引列中存放NULL值才是正解。

相关文章:

兵马未动,粮草先行-InnoDB统计数据是如何收集的

我们前面介绍查询成本的时候经常用到一些统计数据,比如通过SHOW TABLE STATUS可以看到关于表的统计数据,通过SHOW INDEX可以看到关于索引的统计数据,那么这些统计数据是怎么来的呢?它们是以什么方式收集的呢?本章将聚焦…...

oracle服务器意外宕机数据库启动失败故障处理记录

客户反馈由于服务器意外宕机,导致数据库业务不能正常运行,经过一番努力后通过redo日志恢复成功! 故障描述:ORA-00600: 内部错误代码, 参数: [krctcr_4], [1179922061], [1179942042], [], [], [], [], [], [], [], [], [] 现将主要…...

学习笔记——MathType公式编号:右编号和随章节变化

1.如何在word文档中插入带有编号的公式? 步骤:(前提是已经安装mathtype) 2.MathType公式编号怎么随章节变化? 想要编号级数也随标题级数进行自动变化,则需要插入或修改文档的“分隔符” 步骤:...

如何使用 SSH 连接并管理你的 WordPress 网站

在当今数字化的世界里,网站的管理与维护至关重要。对于使用 WordPress 搭建网站的用户而言,掌握基本的 SSH(安全壳)命令能够极大地简化网站管理工作。本指南将向你介绍 SSH 的基本知识,并教你如何通过 SSH 连接和管理你…...

力扣60. 排列序列

描述 力扣60. 排列序列 给出集合 [1,2,3,…,n],其所有元素共有 n! 种排列。 按大小顺序列出所有排列情况,并一一标记,当 n 3 时, 所有排列如下: “123” “132” “213” “231” “312” “321” 给定 n 和 k,返回…...

Mac如何实现最简单的随时监测实时运行状态的方法

Mac book有着不同于Windows的设计逻辑与交互设计,使得Mac book有着非常棒的使用体验,但是在Mac电脑的使用时间过长时,电脑也会出现响应速度变慢或应用程序崩溃的情况,当发生的时候却不知道什么原因导致的,想要查询电脑…...

时间管理应用(可复制源码)

创建一个简单的时间管理应用程序&#xff0c;结合 Pomodoro 技术使用 HTML、CSS 和 JavaScript 1. HTML 创建一个基本的 HTML 文件 (index.html)&#xff1a; <!DOCTYPE html> <html lang"zh"> <head> <meta charset"UTF-8"&…...

SQL server 列转行

在 SQL Server 中&#xff0c;将列转换为行的操作通常被称为“透视”&#xff08;Pivot&#xff09;的逆操作&#xff0c;即“反透视”&#xff08;Unpivot&#xff09;。SQL Server 提供了 UNPIVOT 关键字来实现这一功能。假设你有一个表 EmployeeDetails&#xff0c;其中包含…...

aws申请ssl证书的方法【该证书仅供aws】

这里先声明&#xff0c;过程是对的&#xff0c;最终没有达到目的。 原本想着申请ssl证书替代&#xff0c;结果发现aws证书只能给自己的服务器用 但是整套申请证书以及下载&#xff0c;以及使用aws控制台的过程可以参考借鉴。 起因&#xff1a; 腾讯云的ssl证书越来越没法用了…...

Linux中目录配置标准的FHS

文件系统层次结构标准&#xff08;Filesystem Hierarchy Standard, FHS&#xff09;定义了Linux和其他类Unix操作系统中文件和目录的标准布局。FHS的目标是确保在不同的Linux发行版之间具有一致的文件系统结构&#xff0c;从而使软件能够在不同的系统上容易地安装和运行。 FHS…...

目标检测YOLO实战应用案例100讲-基于深度学习的人眼视线检测

目录 知识储备 视觉深度的测定 基本知识 视觉检测中的关键技术 单眼感知景深 内部摄像机距离的效果 Face ID 与3D传感技术 什么是Face ID? 3D传感技术原理 主动测距法 被动测距法 基于深度学习的人眼视线检测代码 数据集读取与预处理 卷积神经网络模型构建 模型…...

SpringCloud篇(微服务)

目录 一、认识微服务 1. 单体架构 2. 分布式架构 3. 微服务 3.1. 特点 3.2. 优点 3.3 缺点 二、微服务设计、拆分原则 1. AKF 拆分原则 2. Y轴&#xff08;功能&#xff09;关注应用中功能划分&#xff0c;基于不同的业务拆分 3. X轴&#xff08;水平扩展&#xff09…...

[每日一练]过去30天的用户活动

#该题目来源于力扣&#xff1a; 1142. 过去30天的用户活动 II - 力扣&#xff08;LeetCode&#xff09; Activity 表&#xff1a;------------------------ | Column Name | Type | ------------------------ | user_id | int | | session_id | int | …...

华为2288HV2服务器安装BCLinux8U6无法显示完整安装界面的问题处理

本文记录了华为2288HV2服务器安装BCLinux8U6无法显示完整安装界面&#xff0c;在安装过程中配置选择时&#xff0c;右侧安装按钮不可见&#xff0c;导致安装无法继续的问题处理过程。 一、问题现象 华为2288HV2服务器安装BCLinux8U6时无法显示完整的安装界面&#xff0c;问题…...

【python】OpenCV—findContours(4.6)

文章目录 1、功能描述2、代码实现3、效果展示4、完整代码5、涉及到的库函数cv2.inRange 6、参考 1、功能描述 给出一张仅含有手指的图片&#xff0c;判断图片中有多少根手指 2、代码实现 导入库函数&#xff0c;图像预处理 import numpy as np import cv2 as cv img cv.im…...

【C++】——多态

一.多态的概念 1.多态 多态(polymorphism)的概念&#xff1a;通俗的来说&#xff0c;就是多种形态。多态分为静态多态(编译时多态)和动态多态(运行时多态)&#xff0c;而我们讲的多态大部分都是动态多态。 静态多态主要就是我们前面了解过的函数模板和函数重载&#xff0c;它…...

Web前端开发--HTML语言

文章目录 前言1.介绍2.组成3.基本框架4.常见标签4.1双标签4.1.1.标题标签4.2.2段落标签4.1.3文本格式化标签4.1.4超链接标签4.1.5视频标签4.1.6 音频标签 4.2单标签4.2.1换行标签和水平线标签4.2.2 图像标签 5.表单控件结语 前言 生活中处处都有网站&#xff0c;无论你是学习爬…...

AI驱动的网络空间智能对抗;无人集群系统,多体协同算法创新和故障智能预警

目录 AI驱动的网络空间智能对抗 认知与认知域安全 认知攻击-杀伤链 PPDR主动安全框架 短视频内容分析 不良视频鉴别:人工+智能 舆情监测 非介入式监测 大模型对新闻内容审查与播报 无人集群系统,多体协同算法创新和故障智能预警 一、无人集群系统概述 二、多体协…...

推荐一款SSD硬盘优化器:Auslogics SSD Optimizer Pro

SSD Optimizer Pro 是一款专为优化固态硬盘 (SSD) 性能而设计的专业工具&#xff0c;旨在最大化 SSD 的效率&#xff0c;延长硬盘使用寿命。凭借简便的操作界面和强大的优化功能&#xff0c;SSD Optimizer Pro 可以让用户充分利用 SSD 的优势&#xff0c;从而获得更高的系统性能…...

k8s-service、endpoints、pod之间是怎么进行网络互通的

k8s-service、endpoints、pod之间是怎么进行网络互通的 1、service2、endpoints3、service、endpoints、pod通信图4、不通服务pod内部间访问 1、service 在K8S中&#xff0c;Service是一种抽象&#xff0c;定义了一组Pod的逻辑集合和访问这些Pod的策略。首先&#xff0c;我们需…...

conda相比python好处

Conda 作为 Python 的环境和包管理工具&#xff0c;相比原生 Python 生态&#xff08;如 pip 虚拟环境&#xff09;有许多独特优势&#xff0c;尤其在多项目管理、依赖处理和跨平台兼容性等方面表现更优。以下是 Conda 的核心好处&#xff1a; 一、一站式环境管理&#xff1a…...

Java 语言特性(面试系列2)

一、SQL 基础 1. 复杂查询 &#xff08;1&#xff09;连接查询&#xff08;JOIN&#xff09; 内连接&#xff08;INNER JOIN&#xff09;&#xff1a;返回两表匹配的记录。 SELECT e.name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id d.dept_id; 左…...

Spark 之 入门讲解详细版(1)

1、简介 1.1 Spark简介 Spark是加州大学伯克利分校AMP实验室&#xff08;Algorithms, Machines, and People Lab&#xff09;开发通用内存并行计算框架。Spark在2013年6月进入Apache成为孵化项目&#xff0c;8个月后成为Apache顶级项目&#xff0c;速度之快足见过人之处&…...

rknn优化教程(二)

文章目录 1. 前述2. 三方库的封装2.1 xrepo中的库2.2 xrepo之外的库2.2.1 opencv2.2.2 rknnrt2.2.3 spdlog 3. rknn_engine库 1. 前述 OK&#xff0c;开始写第二篇的内容了。这篇博客主要能写一下&#xff1a; 如何给一些三方库按照xmake方式进行封装&#xff0c;供调用如何按…...

(二)TensorRT-LLM | 模型导出(v0.20.0rc3)

0. 概述 上一节 对安装和使用有个基本介绍。根据这个 issue 的描述&#xff0c;后续 TensorRT-LLM 团队可能更专注于更新和维护 pytorch backend。但 tensorrt backend 作为先前一直开发的工作&#xff0c;其中包含了大量可以学习的地方。本文主要看看它导出模型的部分&#x…...

剑指offer20_链表中环的入口节点

链表中环的入口节点 给定一个链表&#xff0c;若其中包含环&#xff0c;则输出环的入口节点。 若其中不包含环&#xff0c;则输出null。 数据范围 节点 val 值取值范围 [ 1 , 1000 ] [1,1000] [1,1000]。 节点 val 值各不相同。 链表长度 [ 0 , 500 ] [0,500] [0,500]。 …...

WordPress插件:AI多语言写作与智能配图、免费AI模型、SEO文章生成

厌倦手动写WordPress文章&#xff1f;AI自动生成&#xff0c;效率提升10倍&#xff01; 支持多语言、自动配图、定时发布&#xff0c;让内容创作更轻松&#xff01; AI内容生成 → 不想每天写文章&#xff1f;AI一键生成高质量内容&#xff01;多语言支持 → 跨境电商必备&am…...

在web-view 加载的本地及远程HTML中调用uniapp的API及网页和vue页面是如何通讯的?

uni-app 中 Web-view 与 Vue 页面的通讯机制详解 一、Web-view 简介 Web-view 是 uni-app 提供的一个重要组件&#xff0c;用于在原生应用中加载 HTML 页面&#xff1a; 支持加载本地 HTML 文件支持加载远程 HTML 页面实现 Web 与原生的双向通讯可用于嵌入第三方网页或 H5 应…...

Mysql中select查询语句的执行过程

目录 1、介绍 1.1、组件介绍 1.2、Sql执行顺序 2、执行流程 2.1. 连接与认证 2.2. 查询缓存 2.3. 语法解析&#xff08;Parser&#xff09; 2.4、执行sql 1. 预处理&#xff08;Preprocessor&#xff09; 2. 查询优化器&#xff08;Optimizer&#xff09; 3. 执行器…...

Python+ZeroMQ实战:智能车辆状态监控与模拟模式自动切换

目录 关键点 技术实现1 技术实现2 摘要&#xff1a; 本文将介绍如何利用Python和ZeroMQ消息队列构建一个智能车辆状态监控系统。系统能够根据时间策略自动切换驾驶模式&#xff08;自动驾驶、人工驾驶、远程驾驶、主动安全&#xff09;&#xff0c;并通过实时消息推送更新车…...