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

MySQL系列—8.存储结构

news 来源：原创 2024/9/20 11:56:31

1.系统表空间 ibdata

系统表空间由参数innodb_data_file_path定义路径、初始化大小、自动扩展策略

如： innodb_data_file_path=/dayta/mysql/ibdata1:100M:autoextend

存放：

Change Buffer(insert buffer)

Lock System

Data Dictionary

Foreign key constaint system tables

User data (innodb_file_per_table=0，不使用独立表空间的时候，用户数据会存储共享表空 间中，有可能会存放在系统表空间。)

2.通用表空间 .ibd

类似于Oracle的表空间概念，多个Table放在同一个表空间中。

mysql> create tablespace tbs add datafile 'tbs.ibd';
Query OK, 0 rows affected (0.01 sec)mysql>  select * from information_schema.innodb_tablespaces;
+------------+------------------+-------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+-----------------+----------------+---------------+------------+--------+
| SPACE      | NAME             | FLAG  | ROW_FORMAT           | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | AUTOEXTEND_SIZE | SERVER_VERSION | SPACE_VERSION | ENCRYPTION | STATE  |
+------------+------------------+-------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+-----------------+----------------+---------------+------------+--------+
| 4294967294 | mysql            | 18432 | Any                  |     16384 |             0 | General    |          4096 |  27262976 |       27262976 |               0 | 8.0.39         |             1 | N          | normal |
| 4294967293 | innodb_temporary |  4096 | Compact or Redundant |     16384 |             0 | System     |          4096 |  12582912 |       12582912 |               0 | 8.0.39         |             1 | N          | normal |
| 4294967279 | innodb_undo_001  |     0 | Undo                 |     16384 |             0 | Undo       |          4096 |  16777216 |       16777216 |               0 | 8.0.39         |             1 | N          | active |
| 4294967278 | innodb_undo_002  |     0 | Undo                 |     16384 |             0 | Undo       |          4096 |  16777216 |       16777216 |               0 | 8.0.39         |             1 | N          | active |
|          1 | sys/sys_config   | 16417 | Dynamic              |     16384 |             0 | Single     |          4096 |    114688 |         114688 |               0 | 8.0.39         |             1 | N          | normal |
|          2 | tbs              | 18432 | Any                  |     16384 |             0 | General    |          4096 |    114688 |         114688 |               0 | 8.0.39         |             1 | N          | normal |
+------------+------------------+-------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+-----------------+----------------+---------------+------------+--------+
6 rows in set (0.00 sec)

mysql> create table t2 (id int) tablespace tbs;
Query OK, 0 rows affected (0.01 sec)
mysql> show create table t2;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (`id` int DEFAULT NULL
) /*!50100 TABLESPACE `tbs` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

3.独立表空间

每个table都有各自的.ibd文件

删除大表的一个技巧(释放文件名)

为数据文件建立硬链接，然后删除原数据文件名，待到空闲时间再清理链接的文件数据

innodb_file_per_table

为1时（默认），启用独立表空间

为0时，启用共享表空间，用户数据存储在系统表空间ibdata*文件中，或者自定义的通用表空间general tablespace中。

为1时，启用独立表空间，每个表有各自的.ibd文件

4.Undo 表空间

实例初始化时，默认创建两个Undo表空间，最大支持127个Undo表空间。

innodb_rollback_segments：于定义每个undo表空间中的回滚段rollback segment的 数量，默认是128个

8.0.14后，可以在线手动创建新的Undo表空间

create undo tablespace undo_name add datafile 'undo_name.ibu';

一个回滚段rollback segment默认最大只有128 个，实例初始化后默认2个undo表空间：128*2个undo tbs= 256个并发事务。所以高并发需求 时需要注意增加undo表空间。

5.临时表空间

innodb_temp_data_file_path：定义路径、大小等，初始值12MB

实例关闭后，临时表文件会被删除，实例启动后，临时表文件（ibtmp1）重新创建

用户自己的临时表放在#innodb_temp/*.ibt，session会话退出后，临时段自动回收。

8.0以前， 有个严重问题，就是ibtmp1，用户执行过程中产生大量临时存在ibtmp1

文件被撑爆后也无法回缩的。

改进后只有innodb内部线程自己生成的临时表才会放在ibtmp1文件里。

用户运行过程中产生的临时表都会放在innodb_temp目录中

innodb_temp_tablespaces_dir：设置会话级用户临时表空间存储路径

会话级临时表空间初始共有10个，会随着用户连接数及创建临时表的情况按需增加，实例重 启后会删除这些文件。每个session最多分配两个临时表空间，一个用于存储用户主动创建的临时表(create temporary table)，另一个用于存储用户执行SQL过程中生成的内部(磁盘)临时表，session断开后，会话级用户临时表空间会直接释放，不用再担心撑爆磁盘。

查看会话级临时表空间的使用情况：

select * from information_schema.innodb_session_temp_tablespaces;

查看全局临时表空间的情况：

 select * from files where tablespace_name like '%temp%'\G

创建session级别临时表，通过实验来观察临时表的分配、查看及回收：

mysql> create temporary table k1 like bigints;
Query OK, 0 rows affected (0.04 sec)
mysql> desc k1;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| dtl | varchar(200) | YES | | NULL | NULL |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)
mysql> insert into k1 select * from bigints;
Query OK, 2621440 rows affected (25.30 sec)
Records: 2621440 Duplicates: 0 Warnings: 0
--换一个session查询
mysql> select * from information_schema.innodb_temp_table_info;
+----------+--------------+--------+------------+
| TABLE_ID | NAME | N_COLS | SPACE |
+----------+--------------+--------+------------+
| 1089 | #sql4c_19_13 | 5 | 4294501264 |
+----------+--------------+--------+------------+
1 row in set (0.00 sec)
mysql> select * from information_schema.innodb_session_temp_tablespaces;
+----+------------+----------------------------+-----------+----------+-----
| ID | SPACE | PATH | SIZE | STATE | PURPOSE |
+----+------------+----------------------------+-----------+----------+-----
| 13 | 4294501265 | ./#innodb_temp/temp_9.ibt | 98304 | ACTIVE | INTRINSI
| 29 | 4294501263 | ./#innodb_temp/temp_7.ibt | 134217728 | ACTIVE | USER
| 0 | 4294501257 | ./#innodb_temp/temp_1.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501258 | ./#innodb_temp/temp_2.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501259 | ./#innodb_temp/temp_3.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501260 | ./#innodb_temp/temp_4.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501261 | ./#innodb_temp/temp_5.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501262 | ./#innodb_temp/temp_6.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501264 | ./#innodb_temp/temp_8.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501266 | ./#innodb_temp/temp_10.ibt | 81920 | INACTIVE | NONE
+----+------------+----------------------------+-----------+----------+-----
10 rows in set (0.00 sec)
[17:34:49] root@ms85:#innodb_temp # ll -h
total 129M
-rw-r----- 1 mysql mysql 80K Jul 7 11:39 temp_10.ibt
-rw-r----- 1 mysql mysql 80K Jul 7 11:39 temp_1.ibt
-rw-r----- 1 mysql mysql 80K Jul 7 11:39 temp_2.ibt
-rw-r----- 1 mysql mysql 80K Jul 7 11:39 temp_3.ibt
-rw-r----- 1 mysql mysql 80K Jul 7 11:39 temp_4.ibt
-rw-r----- 1 mysql mysql 80K Jul 7 11:39 temp_5.ibt
-rw-r----- 1 mysql mysql 80K Jul 7 11:39 temp_6.ibt
-rw-r----- 1 mysql mysql 128M Jul 7 17:35 temp_7.ibt
-rw-r----- 1 mysql mysql 80K Jul 7 17:31 temp_8.ibt
-rw-r----- 1 mysql mysql 96K Jul 7 17:23 temp_9.ibt
--断开前一个session
mysql> exit;
Bye
--再次查询，发现临时表已经被释放
mysql> select * from information_schema.innodb_temp_table_info;
Empty set (0.00 sec)
mysql> select * from information_schema.innodb_session_temp_tablespaces;
+----+------------+----------------------------+-------+----------+---------
| ID | SPACE | PATH | SIZE | STATE | PURPOSE |
+----+------------+----------------------------+-------+----------+---------
| 12 | 4294501266 | ./#innodb_temp/temp_10.ibt | 81920 | ACTIVE | INTRINSIC
| 13 | 4294501265 | ./#innodb_temp/temp_9.ibt | 98304 | ACTIVE | INTRINSIC
| 0 | 4294501257 | ./#innodb_temp/temp_1.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501258 | ./#innodb_temp/temp_2.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501259 | ./#innodb_temp/temp_3.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501260 | ./#innodb_temp/temp_4.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501261 | ./#innodb_temp/temp_5.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501262 | ./#innodb_temp/temp_6.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501264 | ./#innodb_temp/temp_8.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501263 | ./#innodb_temp/temp_7.ibt | 81920 | INACTIVE | NONE |
+----+------------+----------------------------+-------+----------+---------
10 rows in set (0.00 sec
[17:36:08] root@ms85:#innodb_temp # ll
total 336K
-rw-r----- 1 mysql mysql 80K Jul 7 11:39 temp_10.ibt
-rw-r----- 1 mysql mysql 80K Jul 7 11:39 temp_1.ibt
-rw-r----- 1 mysql mysql 80K Jul 7 11:39 temp_2.ibt
-rw-r----- 1 mysql mysql 80K Jul 7 11:39 temp_3.ibt
-rw-r----- 1 mysql mysql 80K Jul 7 11:39 temp_4.ibt
-rw-r----- 1 mysql mysql 80K Jul 7 11:39 temp_5.ibt
-rw-r----- 1 mysql mysql 80K Jul 7 11:39 temp_6.ibt
-rw-r----- 1 mysql mysql 80K Jul 7 17:36 temp_7.ibt
-rw-r----- 1 mysql mysql 80K Jul 7 17:31 temp_8.ibt
-rw-r----- 1 mysql mysql 96K Jul 7 17:23 temp_9.ibt

干掉占用临时表空间的会话:

mysql> select * from information_schema.innodb_session_temp_tablespaces;
+----+------------+----------------------------+-----------+----------+-----
| ID | SPACE | PATH | SIZE | STATE | PURPOSE |
+----+------------+----------------------------+-----------+----------+-----
| 13 | 4294501265 | ./#innodb_temp/temp_9.ibt | 98304 | ACTIVE | INTRINSI
| 30 | 4294501263 | ./#innodb_temp/temp_7.ibt | 125829120 | ACTIVE | USER
| 0 | 4294501257 | ./#innodb_temp/temp_1.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501258 | ./#innodb_temp/temp_2.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501259 | ./#innodb_temp/temp_3.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501260 | ./#innodb_temp/temp_4.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501261 | ./#innodb_temp/temp_5.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501262 | ./#innodb_temp/temp_6.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501264 | ./#innodb_temp/temp_8.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501266 | ./#innodb_temp/temp_10.ibt | 81920 | INACTIVE | NONE
+----+------------+----------------------------+-----------+----------+-----
10 rows in set (0.00 sec)
mysql> show processlist;
+----+-----------------+-----------+--------------------+---------+-------+-
| Id | User | Host | db | Command | Time | State | In
+----+-----------------+-----------+--------------------+---------+-------+-
| 4 | event_scheduler | localhost | NULL | Daemon | 21518 | Waiting o
| 13 | root | localhost | information_schema | Query | 0 | starting
| 30 | root | localhost | kk | Query | 22 | executing |
+----+-----------------+-----------+--------------------+---------+-------+-
3 rows in set (0.00 sec)
mysql> kill 30
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> show processlist;
+----+-----------------+-----------+--------------------+---------+-------+-
| Id | User | Host | db | Command | Time | State | In
+----+-----------------+-----------+--------------------+---------+-------+-
| 4 | event_scheduler | localhost | NULL | Daemon | 21531 | Waiting o
| 13 | root | localhost | information_schema | Query | 0 | starting
+----+-----------------+-----------+--------------------+---------+-------+-
2 rows in set (0.00 sec)
mysql> select * from information_schema.innodb_session_temp_tablespaces;
+----+------------+----------------------------+-------+----------+---------
| ID | SPACE | PATH | SIZE | STATE | PURPOSE |
+----+------------+----------------------------+-------+----------+---------
| 13 | 4294501265 | ./#innodb_temp/temp_9.ibt | 98304 | ACTIVE | INTRINSIC
| 0 | 4294501257 | ./#innodb_temp/temp_1.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501258 | ./#innodb_temp/temp_2.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501259 | ./#innodb_temp/temp_3.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501260 | ./#innodb_temp/temp_4.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501261 | ./#innodb_temp/temp_5.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501262 | ./#innodb_temp/temp_6.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501264 | ./#innodb_temp/temp_8.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501266 | ./#innodb_temp/temp_10.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501263 | ./#innodb_temp/temp_7.ibt | 81920 | INACTIVE | NONE |
+----+------------+----------------------------+-------+----------+---------
10 rows in set (0.00 sec)

相关文章：

  • 北京网站建设多少钱？
  • 辽宁网页制作哪家好_网站建设
  • 高端品牌网站建设_汉中网站制作
  • 中医世家龚洪海博士：用医术和真诚赢得患者的心
  • SPIRNGBOOT+VUE实现浏览器播放音频流并合成音频
  • 【开源大模型生态6】生态大咖与产品布局
  • 文本格式 .WAT
  • ueditor抓取图片
  • 2024.09.02 校招 实习 内推 面经
  • mysql快速定位cpu 占比过高的sql语句
  • UE5.3 新学到的一些性能测试合计(曼巴学习笔记)
  • 人工智能在行业中的应用
  • Java 创建图形用户界面（GUI）入门指南（Swing库 JFrame 类）概述
  • git分支的管理
  • 2024.09.04【读书笔记】|如何使用Tombo进行Nanopore Direct RNA-seq（DRS）分析
  • spring security 中的异常
  • 【Linux系统编程】TCP实现--socket
  • 数学建模笔记——熵权法(客观赋权法)
  • [NodeJS] 关于Buffer
  • 【MySQL经典案例分析】 Waiting for table metadata lock
  • chrome扩展demo1-小时钟
  • go语言学习初探（一）
  • java8-模拟hadoop
  • JavaScript的使用你知道几种？(上)
  • magento 货币换算
  • Python利用正则抓取网页内容保存到本地
  • Sequelize 中文文档 v4 - Getting started - 入门
  • Vue官网教程学习过程中值得记录的一些事情
  • 第三十一到第三十三天：我是精明的小卖家（一）
  • 关于Android中设置闹钟的相对比较完善的解决方案
  • 海量大数据大屏分析展示一步到位：DataWorks数据服务+MaxCompute Lightning对接DataV最佳实践...
  • 回顾 Swift 多平台移植进度 #2
  • 记录：CentOS7.2配置LNMP环境记录
  • 精彩代码 vue.js
  • 前端相关框架总和
  • 数据仓库的几种建模方法
  • 再次简单明了总结flex布局，一看就懂...
  • 京东物流联手山西图灵打造智能供应链，让阅读更有趣 ...
  • ​Base64转换成图片,android studio build乱码,找不到okio.ByteString接腾讯人脸识别
  • ​力扣解法汇总946-验证栈序列
  • ![CDATA[ ]] 是什么东东
  • # centos7下FFmpeg环境部署记录
  • （Arcgis）Python编程批量将HDF5文件转换为TIFF格式并应用地理转换和投影信息
  • （WSI分类）WSI分类文献小综述 2024
  • （草履虫都可以看懂的）PyQt子窗口向主窗口传递参数，主窗口接收子窗口信号、参数。
  • （二）windows配置JDK环境
  • （附源码）php新闻发布平台 毕业设计 141646
  • （附源码）springboot 校园学生兼职系统 毕业设计 742122
  • （附源码）springboot“微印象”在线打印预约系统 毕业设计 061642
  • （附源码）ssm码农论坛 毕业设计 231126
  • （四）TensorRT | 基于 GPU 端的 Python 推理
  • （算法）前K大的和
  • (转)Linux整合apache和tomcat构建Web服务器
  • （转）德国人的记事本
  • .NET 2.0中新增的一些TryGet,TryParse等方法
  • .NET 3.0 Framework已经被添加到WindowUpdate
  • .Net Core中的内存缓存实现——Redis及MemoryCache（2个可选）方案的实现
  • .NET 材料检测系统崩溃分析