深入理解Mysql数据存储(mysql数据存储过程怎么写?)
nanshan 2024-10-13 03:52 16 浏览 0 评论
欢迎关注我的微信公众号【Mflyyou】获取持续更新。
github.com/zhangpanqin/MFlyYou 收集技术文章及我的系列文章,欢迎 Star。
前言
本文内容
- Mysql 数据文件说明
- Mysql 数据逻辑存储架构
- Mysql 表空间,主要是系统表空间和独立表空间
- Mysql 数据类型 时区对 datetime 和 timestamp 影响,java 中 LocalDatetime 保存时,时间和预期不符的原因分析和解决办法 varchar(n) 和 char(n) 保存时,n 能取多少,n 的含义。一行数据中 varchar 能存多少个 整型、小数
本文内容基于 Mysql 8.0.21 ,系统为 Centos 7。
Mysql 架构说明
客户端链接 Mysql 的 Server 层,Server 层会对 sql 进行语法解析和优化并生成执行计划,然后调用存储引擎提供的接口获取要查询的数据。
存储引擎层从计算机文件系统上读取对应的文件中的数据返回给 Server 层,Server 层再将数据返回给客户端。
存储引擎不了解的话,使用 InnoDB 就行,这也是比较常用的。
Redis 为什么会比 Mysql 快,很大的原因是 Redis 的数据都在内存中,再加上比较好的数据结构,查询的速度当然不是一个量级的。但同时 Redis 不会存储那么多的数据量,几个 T 的内存还是挺贵的。
Mysql 将数据储存在硬盘上,为了提高查询速度,比较好的做法是将索引数据和一部分热数据(经常访问的数据)放到内存中(Mysql 的 Buffer Poll)。
当检索数据的时候,Mysql 通过索引查找,就可以知道数据在磁盘哪块了,从硬盘对应位置读取对应的数据到内存中返回给客户端。
如果查询的时候没有走索引就需要扫描整个表数据文件,因为内存比硬盘小,会不停的从硬盘读取表中的一部分数据到内存,然后在内存中筛选出符合要求的数据,再去硬盘读取一部分数据做筛选直到整个表数据读取一遍。如果你有 20 g 数据,你想一下需要读取多长时间。
Mysql 8.0 相对 Mysql 7.0 性能上有很大提升,条件允许建议使用 Mysql 8.0。
Mysql 数据存储
连接 Mysql
# -h 指定 mysqld 的服务地址
# -P 指定连接端口
# -u 执行用户(生产环境不建议使用 root 用户连接,合理使用权限管理。每个库使用不同的账号密码)
# -p 输入密码
mysql -hlocalhost -P3306 -uroot -p
查看 Mysql 数据文件的目录
-- 连接之后输入以下命令,查看数据储存在哪里了
-- /var/lib/mysql/ Centos 7.0 存储位置
show variables like '%datadir%';
系统表空间
系统表空间是所有表共享的,它保存了数据表结构,事务信息 等
SHOW VARIABLES LIKE 'innodb_data_file_path%'
-- ibdata1:12M:autoextend
独立表空间
独立表空间是指每张表单独用一个文件储存每张表对应的数据和索引,文件扩展名为 ibd。
每个数据库会有一个对应目录用于保存当前数据库中的数据文件
在 /var/lib/mysql/ 目录下
drwxr-x--- 8 zhangpanqin admin 256B 2 11 2020 leetcode
drwxr-x--- 8 zhangpanqin admin 256B 10 18 2019 mysql
-rw-r----- 1 zhangpanqin admin 36M 11 29 18:31 mysql.ibd
当我们在某个数据库中创建一张表时,除了在系统表空间生成元数据和表结构,也会在对应的数据库目录下,新建一个 tablename.ibd 文件。
/usr/local/var/mysql/leetcode
-rw-r----- 1 zhangpanqin admin 112K 2 11 2020 department.ibd
-rw-r----- 1 zhangpanqin admin 112K 2 11 2020 employee.ibd
-rw-r----- 1 zhangpanqin admin 112K 2 11 2020 logs.ibd
-rw-r----- 1 zhangpanqin admin 112K 2 11 2020 person.ibd
-rw-r----- 1 zhangpanqin admin 112K 2 14 2020 scores.ibd
-rw-r----- 1 zhangpanqin admin 112K 2 11 2020 weather.ibd
Mysql 8.0 是默认开启独立表空间的,默认每张表使用一个文件进行保存数据和索引
-- 查看是否开启独立表空间配置
mysql> show variables like '%innodb_file_per_table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
InnoDB 逻辑储存结构
表空间
在 InnoDB 存储引擎下,表相关的所有数据(比如业务数据和索引数据)都储存在表空间(tablespace)中。每张表都有一个自己的文件(.ibd)去储存相关数据(开启独立表空间设置)。
表空间又可以细分为 segment,extent,page,row。
段
表空间又包含多个段(segment),常见的数据段有:
- Leaf node segment 数据段,存储当前表中的数据
- Non-Leaf node segment 索引段,存储当前表中的索引
区
段包含很多个区,每个区始终为 1MB 。区由多个连续连续的页组成,页的大小通常是 16KB,所以一个区可以有 64 (1024/16=64)个连续页。
页
页是 InnoDB 与磁盘交互的最小单位。从磁盘上读取数据,一次性是读取一页数据。将内存中的数据落盘到硬盘上,也是操作一页数据。
比如我们修改了 id=3 某行数据,数据持久化的时候,是需要将这行所在的页全部落盘在硬盘上。
update test_table set a=2 where id =3;
页也有类型,数据页,索引页等等。
-- 查看页的大小,默认是 16KB =16*1024bit
mysql> show variables like '%innodb_page_size%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
行
每页存放一行一行的数据。
mysql> SHOW TABLE STATUS LIKE "test_data_type"\G;
*************************** 1. row ***************************
Name: test_data_type
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 22
Avg_row_length: 14894
Data_length: 327680
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 243
Create_time: 2020-09-21 01:55:51
Update_time: 2020-09-21 02:06:59
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
Row_format 定义了一行数据在数据页中怎么保存。
VARCHAR(M) 和 TEXT 类型的字段为变长字段,变长字段占用多少字节,记录在 变长字段长度列表
记录头信息中,记录着当前行的类型和下一条记录位置等信息。
行数据中,除了我们表结构中自己定义的字段,还有 Mysql 添加的元数据字段。比如 行 id(当没有主键数据的时候添加),事务 id (主要用于 MVCC)和 回滚指针等。
一页可以存 16KB 数据,但是 VARCAHR(m),可以存 65535 字节。这些变长数据大于一页需要怎么存呢。这个现象也叫做行溢出。
行溢出的数据会单独存在一页中,在真实数据中对应的列中记录一个指针指向溢出的数据。
以上内容了解即可,只是为了理解原理及辅助表设计。
数据类型
表设计的时候一定要选取合适的数据类型,能用数字就不要用字符串,一是减少存储时空间的浪费,二是减少查询时内存的浪费。
整型
小数
M 表示小数的有效数字,D 表示小数点后的有效数字。
FLOAT(4, 1) 不能存 4000.1 会报错误。
日期和时间
TIMESTAMP(fsp) 中的 fsp 是指秒的精度(x.xxx xxx),fsp 取值 0,1,2,3,4,5,6。
TIME、DATETIME、TIMESTAMP 这几种类型支持小数秒。
DATETIME(0) 精确到秒,没有小数位。
DATETIME(3) 精确到毫秒,有三位小数。
日期和时间存储时区的设置有关,一定要搞清楚原理。
TIMESTAMP 的显示和数据库系统设置的时区有关。
TIMESTAMP 底层实际存储的是毫秒值,显示的时间是根据设置的时区(time_zone)转换为时间显示的。
还有 Java 1.8 新增的 LocalDateTime 需要怎么转换 Mysql 中的时间呢。
-- 查看 mysql 的时区设置
SHOW VARIABLES LIKE "%time_zone%";
mysql> SHOW VARIABLES LIKE "%time_zone%";
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CET |
| time_zone | +08:00 |
+------------------+--------+
system_time_zone 是 Mysql 启动的时候获取计算机系统所在的时区。只要计算机的时间准确就没有问题。
time_zone 设置的是连接 mysql 的会话期间,时间 (java.util.Date)转换为字符串时的 TimeZone。time_zone 这个值可以被 jdbc 连接中的 serverTimezone=Asia/Shanghai 覆盖。
我们在东八区,希望时间都转换为东八区时间。
[mysqld]
# 将时间转换为东八区的时间
default-time-zone = '+08:00'
CREATE TABLE `test_data_type` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`test_data_time` datetime DEFAULT NULL,
`test_timestamp` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=243
DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
-- 实际保存数据的时候需要将日期转换为字符串,拼接成这样的 sql
INSERT INTO test_data_type (test_data_time,test_timestamp) VALUES
('2020-12-12 12:12:12','2020-12-12 12:12:12');
比如我们将 java 中的 LocalDateTime 存为 datetime 类型。
@Data
@TableName(value = "test_data_type")
public class TestDataType {
@TableId(value = "id", type = IdType.AUTO)
private Long id;
@TableField(value = "test_data_time")
private LocalDateTime testDataTime;
@TableField(value = "test_timestamp")
private LocalDateTime testTimestamp;
}
当我们保存数据的时候,需要根据配置的 time_zone,将 LocalDateTime 转为 String,再替换到 sql 中的 ?
INSERT INTO test_data_type (test_data_time,test_timestamp) VALUES (?,?);
// NativeProtocol.configureTimezone 可以看到这个逻辑
@Test
public void run33() {
// 首先获取到在服务器设置的 time_zone,如果没有设置的话,默认取 mysql 服务器所在时区
String configuredTimeZoneOnServer = this.serverSession.getServerVariable("time_zone");
// jdbc 链接中设置的参数
String canonicalTimezone = getStringProperty("serverTimezone");
if(canonicalTimezone==null||canonicalTimezone.length()<0){
canonicalTimezone=configuredTimeZoneOnServer;
}
// jdbc url 参数中配置的 serverTimezone 和 time_zone 都是为了获取 TimeZone,serverTimezone 的优先级更高
final TimeZone timeZone = TimeZone.getTimeZone(canonicalTimezone);
// Timestamp 继承了 java.util.Date
// 这里会将获得 LocalDateTime 获取其年月日时分秒上的值
final Timestamp timestamp = Timestamp.valueOf(LocalDateTime.now());
final SimpleDateFormat simpleDateFormat = new SimpleDateFormat();
simpleDateFormat.applyPattern("yyyy.MM.dd HH:mm:ss");
simpleDateFormat.setTimeZone(timeZone);
// 然后将这个 time 替换 ?
String time= simpleDateFormat.format(timestamp);
}
时区设置结论
当我们保存数据的创建时间的时候,只需获取当前时间就行。当前时区与东八区的时差,mysql 配置的 time_zone和 serverTimezone 转换成字符串时会自动加上。
final Date createTime = new Date();
// LocalDateTime 一定不要自己补时差,不然时间会对不上
final LocalDateTime createTime2 = LocalDateTime.now();
// 下面这个用法是错误的。这样数据库中保存的时间比实际时间多了八个小时
final LocalDateTime errorCreateTime = LocalDateTime.now(ZoneId.of("UTC+8"));
字符串
varchar
varchar (M) 中 M 指的是字符数。Mysql 限制在一行数据中,所有 varchar 列的总字节数不能超过 65535 字节。
-- utf8mb4 实际会占用 1-3 字节
CREATE TABLE `test_varchar` (
`test_name` varchar(65535) CHARACTER SET utf8mb4 NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4;
执行上述 sql 报错为:
1074 - Column length too big for column 'test_name' (max = 16383);
use BLOB or TEXT instead, Time: 0.000000s
验证所有 varchar 列总数据不能超过 65535
CREATE TABLE `test_varchar` (
`test_name1` varchar(7000) CHARACTER SET utf8mb4 NOT NULL,
`test_name2` varchar(7000) CHARACTER SET utf8mb4 NOT NULL,
`test_name3` varchar(7000) CHARACTER SET utf8mb4 NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4;
执行上述 sql 报错信息为
1118 - Row size too large. The maximum row size for the used table type,
not counting BLOBs, is 65535. This includes storage overhead, check the manual.
You have to change some columns to TEXT or BLOBs, Time: 0.002000s
varchar (M) 实际占用字节数,除了数据的占用,还有数据字节数大小的记录(1-2 字节)。
varchar(255) 存储的 abc 的时候占用 4 个字节,但是这个数据加载到内存的时候是占用定义的时候指定的字节数 (255*3 utf8 编码)所以这个数值不要随便填写
char
char(M) M 也是指的字符数,列采用的字符集不同,char 类型数据占用大小也不一样。char 类型的数据没有达到指定字符数,数据库会自动补充空格,返回数据的时候再去掉空格。
当一个字符串太长的时候一定要采取 text 类型的数据,text 类型的数据存储的时候会作为行溢出数据存储,就没有 65535 大小的限制。
// 可以看到占用
https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html
本文由 张攀钦的博客 http://www.mflyyou.cn/ 创作。 可自由转载、引用,但需署名作者且注明文章出处。
欢迎关注我的微信公众号【Mflyyou】获取持续更新。
github.com/zhangpanqin/MFlyYou 收集技术文章及我的系列文章,欢迎 Star。
相关推荐
- 雷军1994年写的老代码曝光,被称像诗一样优雅
-
大数据文摘授权转载自程序员的那些事雷军的代码像诗一样优雅↓↓↓有些网友在评论中质疑,说雷军代码不会是“屎”一样优雅吧。说这话的网友,也许是开玩笑的,也许是真没看过雷军写过的代码。在2011年的时候,我...
- 原创经验分享:低级bug耗费12小时Fix
-
调试某程序非常简单的程序,简单到认为不可能存在缺陷,但该BUG处理时间超过12小时:程序属于后台进程,监控系统每隔15秒检查外设IO状态,IO异常后发出报警或复位外设,外设都在linux下有/sys/...
- SpringBoot实现的简单停车位管理系统附带导入和演示教程视频
-
这一次为大家带来的是简单的停车位管理系统,基于SpringBoot+Thymeleaf+Mybatis框架,这个系统相对来说比较简单,很容易学习并快速上手,因为逻辑很清晰,没有太复杂的代码逻辑,所以学...
- 一个开箱即用的代码生成器(代码自动生成工具开源)
-
今天给大家推荐一个好用的代码生成器,名为renren-generator,该项目附带前端页面,可以很方便的选择我们所需要生成代码的表。首先我们通过git工具克隆下来代码(地址见文末),导入idea。...
- 【免费开源】JeecgBoot单点登录源码全部开源了
-
JeecgBoot单点登录源码全部开源了,有需要的朋友可以来薅羊毛了。一、JeecgBoot介绍JeecgBoot是一款企业级的低代码平台!前后端分离架构SpringBoot2.x,SpringCl...
- SpringBoot+JWT+Shiro+Mybatis实现Restful快速开发后端脚手架
-
作者:lywJee来源:cnblogs.com/lywJ/p/11252064.html一、背景前后端分离已经成为互联网项目开发标准,它会为以后的大型分布式架构打下基础。SpringBoot使编码配置...
- 为什么越来越多的人选择使用idea软件
-
IDEA软件是什么?IDEA软件是干什么的?为什么越来越多的人选择使用IDEA软件?IDEA软件,全称IntelliJIDEA,它是由JetBrains公司开发开发的一款功能强大的集成开发环境(ID...
- 开题报告大学生互助系统(附源码)java毕设
-
本系统(程序+源码)带文档lw万字以上文末可获取一份本项目的java源码和数据库参考。系统程序文件列表开题报告内容选题背景随着互联网技术的飞速发展,大学生群体对信息共享与互助的需求日益增长。关于大...
- SpringBoot项目快速开发框架JeecgBoot——项目简介及系统架构!
-
项目简介及系统架构JeecgBoot是一款基于SpringBoot的开发平台,它采用前后端分离架构,集成的框架有SpringBoot2.x、SpringCloud、AntDesignof...
- 新手配电脑13代CPU怎么选择(新手配电脑13代cpu怎么选择好)
-
Intel第13代酷睿i3、i5、i7、i9系列处理器的核心参数、性能差异及适用群体的详细说明(以桌面端为例):一、13代酷睿全系参数对比(桌面端主流型号)参数i3-13100i5-13600Ki7-...
- 加速 SpringBoot 应用开发,官方热部署神器真带劲
-
平时使用SpringBoot开发应用时,修改代码后需要重新启动才能生效。如果你的应用足够大的话,启动可能需要好几分钟。有没有什么办法可以加速启动过程,让我们开发应用代码更高效呢?今天给大家推荐一款Sp...
- 基于微信小程序的移动端物流系统-计算机毕业设计源码+LW文档
-
摘要随着Internet的发展,人们的日常生活已经离不开网络。未来人们的生活与工作将变得越来越数字化,网络化和电子化。网上管理,它将是直接管理移动端物流系统app的最新形式。本论文是以构建移动端物流系...
- springboot教务管理系统+微信小程序云开发附带源码
-
今天给大家分享的程序是基于springboot的管理,前端是小程序,系统非常的nice,不管是学习还是毕设都非常的靠谱。本系统主要分为pc端后台管理和微信小程序端,pc端有三个角色:管理员、学生、教师...
- SpringBoot全家桶:23篇博客加23个可运行项目让你对它了如指掌
-
SpringBoot现在已经成为Java开发领域的一颗璀璨明珠,它本身是包容万象的,可以跟各种技术集成。本项目对目前Web开发中常用的各个技术,通过和SpringBoot的集成,并且对各种技术通...
- Maven+JSP+Servlet+C3P0+Mysql实现的音乐库管理系统
-
本系统基于Maven+JSP+Servlet+C3P0+Mysql实现的音乐库管理系统。简单实现了充值、购买歌曲、poi数据导入导出、歌曲上传下载、歌曲播放、用户注册登录注销等功能。难度等级:简单技术...
你 发表评论:
欢迎- 一周热门
-
-
UOS服务器操作系统防火墙设置(uos20关闭防火墙)
-
极空间如何无损移机,新Z4 Pro又有哪些升级?极空间Z4 Pro深度体验
-
如何修复用户配置文件服务在 WINDOWS 上登录失败的问题
-
手机如何设置与显示准确时间的详细指南
-
如何在安装前及安装后修改黑群晖的Mac地址和Sn系列号
-
日本海上自卫队的军衔制度(日本海上自卫队的军衔制度是什么)
-
NAS:DS video/DS file/DS photo等群晖移动端APP远程访问的教程
-
爱折腾的特斯拉车主必看!手把手教你TESLAMATE的备份和恢复
-
10个免费文件中转服务站,分享文件简单方便,你知道几个?
-
FANUC 0i-TF数据备份方法(fanuc系统备份教程)
-
- 最近发表
- 标签列表
-
- linux 查询端口号 (58)
- docker映射容器目录到宿主机 (66)
- 杀端口 (60)
- yum更换阿里源 (62)
- internet explorer 增强的安全配置已启用 (65)
- linux自动挂载 (56)
- 禁用selinux (55)
- sysv-rc-conf (69)
- ubuntu防火墙状态查看 (64)
- windows server 2022激活密钥 (56)
- 无法与服务器建立安全连接是什么意思 (74)
- 443/80端口被占用怎么解决 (56)
- ping无法访问目标主机怎么解决 (58)
- fdatasync (59)
- 405 not allowed (56)
- 免备案虚拟主机zxhost (55)
- linux根据pid查看进程 (60)
- dhcp工具 (62)
- mysql 1045 (57)
- 宝塔远程工具 (56)
- ssh服务器拒绝了密码 请再试一次 (56)
- ubuntu卸载docker (56)
- linux查看nginx状态 (63)
- tomcat 乱码 (76)
- 2008r2激活序列号 (65)