【MySQL】表学习笔记

二二得四

已于 2022-04-27 23:26:50 修改

阅读量435

点赞数

分类专栏： MySQL 文章标签： mysql

于 2022-04-26 18:23:24 首次发布

本文链接：https://blog.csdn.net/weixin_42573967/article/details/124433460

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

MySQL表

一、数据类型
二、表碎片产生原因
三、碎片计算方法及整理过程
- 3.1清除碎片的两种方法：

数据库其实就是一个有好多表的大集合。
在创建表的时候，首先要保证几点原则：

禁止使用中文做字段名；
禁止使用字符型做主键；
禁止无主建或是唯一索引的表出现。

一、数据类型

MySQL主要的数据类型可以分为整型、浮点型、字符类型和日期时间类型。MySQL允许我们指定数值字段中的值是否有正负之分或者用零填补。
整型
在这里插入图片描述
为什么非要选择id做主键呢？
主键字段要选择那种不经常修改的，尽量要与业务无关的，没有什么具体含义的。因为InnoDB表是索引组织表，需要保证索引结构不经常翻转，避免造成性能的消耗。

int(4)和int(10)有区别吗？
Int(n)括号里面的数字n无论写成多少，都是占4个字节的空间，最多能存10位数。n不是代表能存多少位数，只是显示宽度。所以int(4)和int(10)没有区别。但如果定义了zerofill,int(4)中的4就有意义了，假如我们写入一个数字1，它会补充完成写成0001，int(10)则为0000000001。

1. 浮点型

在这里插入图片描述
总结：DECIMAL(M,D)中的D值是小数部分的位数，若插入的值未指定小数部分或者小数部分不足D位，则自动补到D位小数，若插入的值小数部分超过了D,则会发生截断，截取前D位小数，并四舍五入。M值是整数部分加小数部分的总长度，即插入的数字整数部分不能超过M一D位，否则不能成功插入，会报超出范围的错误。

2.时间类型

在这里插入图片描述

3. 字符串类型

在这里插入图片描述

1.3.1Char和Varchar的区别？

Char类型用于定长字符串，并且大小范围为0~255。如果字符数没有达到定义的位数，会在后面用空格补全存入数据库中；如果超过指定长度大小，会被截断。
.
Varchar是变长长度，长度范围为0~65535，存储时，如果字符没有达到定义的位数，不会在后面补空格；如果超过指定长度，也会被截断。
.
Varchar类型可以根据实际内容动态改变存储值的长度，在不能确定字段需要多少字符时，使用Varchar类型可以大大地节约磁盘空间，提高存储效率。
.
使用Varchar时，和输入的字符数有关，会多一到两个字节来记录字节长度，当数据位占用的字节数小于255时，用1个字节来记录长度，数据位占用字节数大于255时，用2个字节来记录长度，还有一位用来记录是否为null值。

在这里插入图片描述

1.3.2字节数

MySQL每一行的最大字节数为65535.
使用UTF8字符集，每个字符最多占3个字节，最大长度不能超过(65535-1-2)/3=21844。
如果使用GBK字符集，每个字符最多占2个字节，最大长度不能超过(65535-1-2)/2=36766。
IPv4这样的字段，选择什么数据类型存储合适呢？推荐使用int类型来存储iP字段。可int不是存整数的嘛，怎么可以存字符串呢？这里就需要使用到inet_aton和inet_ntoa两个函数。
在这里插入图片描述

1.3.3字符集

字符集就是一套文字符号及其编码的集合。

MySQL数据库字符集包括字符集和校对规则两个概念。其中，字符集用来定义MySQL数据字符串的存储方式，而校对规则则是定义比较字符串的方式。

常用的字符集有GBK、Latinl、UTF8、UTF8mb4。GBK占2个字节，通用性没有UTF8好。UTF8占3个字节，UTF8mb4是UTF8的超集，占4个字节。

1.3.4避免中文乱码？

连接终端的字符集必须是UTF8
操作系统的字符集必须是UTF8
MySQL数据库的字符集必须是UTF8,或者可以通过show variables like"char"来查看数据库字符集的配置，只需要在配置文件中的mysqld下加入character-set-server=utf8mb4就可以了。

二、表碎片产生原因

我们有时会在表中删除一些大量的无用数据，但发现数据文件的大小并没有减小，这是因为删除后在数据文件中遗留了大量的数据碎片所导致的。
因为使用delete删除数据的时候，MySQL并不会把数据文件真实删除，而只是将数据文件的标识位删除，也没有整理数据文件，因此不会彻底释放表空间。换句话说，每当我们从表中删除数据时，这段被删除数据的空间就会被留出来，如果又赶上某段时间内对该表进行大量的delete操作，那么这部分被删除数据的空间就会越来越大。当有新数据写入时，MySQL会再次利用这些被删除的区域，但也无法彻底占用。
delete删除操作会产生数据碎片，这些碎片会占用硬盘空间。这种额外的破碎的存储空间在读取效率方面比正常占用的空间要低很多。所以我们需要对表进行优化，对表进行碎片整理工作。

三、碎片计算方法及整理过程

了解碎片计算方法，通过show table status like"%table name%"命令来查看

碎片大小=数据总大小-实际表空间文件大小。数据总大小=data length+index_length=56033280。
实际表空间文件大小=rows×avg_row_length=25720506。
碎片大小=(56033280-25720506)/1024/1024，大约等于28.9MB。

3.1清除碎片的两种方法：

(1)alter table table name engine=innodb.
这条语句的作用就是重新整理一遍全表数据，整理之后的数据连续性好，全表扫描变快，表空间文件也变小了，节约了磁盘上空间，清除了碎片。
缺点是需要先给整表加个写锁，需要经历比较长的时间，例子中表的数据量才10万条SQL就耗时近7s,在业务高峰期不建议使用。
(2)备份原表数据，然后删掉，重新导入到新表中（与原表结构一样）。