MySQL数据类型以及存储引擎

最新推荐文章于 2022-12-23 15:01:20 发布

kangswx

最新推荐文章于 2022-12-23 15:01:20 发布

阅读量172

点赞数

分类专栏： MySQL 文章标签： MySQL介绍 MySQL数据类型 MySQL存储引擎 MySQL性能优化 MySQL表空间

本文链接：https://blog.csdn.net/kangswx/article/details/94475067

版权

MySQL 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

影响MySql性能的主要因素：
1. 数据库结构设计；
2. 数据库存储引擎；
3. SQL语句的书写；
4. 数据库参数配置；
5. 服务器操作系统与硬件。
数据库设计三大范式：
1. 第一范式：数据库所有字段都只有单一属性；单一属性是由基本数据类型构成的；数据库的表都是二维的(行与列)(表中的字段不能被拆分)；
2. 第二范式：在满足第一范式的前提下，表中必须有一个主键(一列或多列)，其他字段可由主键确定，第二范式的目的是通过拆表减少数据冗余。
3. 第三范式：在满足第二范式的前提下，要求字段直接依赖与主键，不允许间接依赖，目的在于拆分实体(完善从表)。
反范式设计：在进行表设计的时候，增加一定的冗余数据
反范式设计的优点
1. 在单表查询中易于优化、易于管理；
2. SQL语句简单，有利于程序开发，团队协作。
反范式设计的缺点
1. 存在数据冗余，写操作时需要额外更新从表数据；
2. 不合理的反范式设计，会让表变得臃肿不堪。
实体关系分析：实体关系是指系统事物之间的联系，分析实体关系的时候，需要双向分析，实体的关系决定了数据库中表的关系。
实体关系的种类：一对一，一对多，多对多
表关系的设计原则：
1. 一对一：通过主键关联；
2. 一对多：在多的一方设置外键；
3. 多对多：增加中间表，持有双方外键。
自然主键：事物属性中自然唯一标识
代理主键：与业务无关的、无意义的数字序列值(自增id)
在表涉及的时候，优先是由代理主键，不推荐使用自然主键
数据类型选择：
字段类型优先级：数字类型，日期类型与二进制类型，字符串类型

整数类型：

列类型	存储空间	无符号取值范围	有符号取值范围
tinyint	1字节	0~255	-128~127
smallint	2字节	0~65535	-32768~32767
mediumint	3字节	0~16777215	-8388608~8388607
int	4字节	0~4294967295	-2147483~2147483647
bigint	8字节	0~18446744073709551615	-9223372036854775808~9223372036854775807

实数类型：

列类型存储空间是否精确类型
FLOAT 4字节否
DOUBLE 8字节否
DECIMAL 9字节是
DECIMAL(18, 9)，表示18位有效数字，小数占9位，整数占9位，小数部分占4个字节，整数部分占4个字节，小数点占1个字节，总共占9个字节，财务数据一定要使用DECIMAL。
varchar类型：变体长度，根据实际内容保存数据。
varchar使用注意点：
1. 使用最小的符合需求的长度；
2. varchar(255)以下使用额外一个字节保存长度；
3. varchar(255)以上使用额外两个字节保存长度；
4. varchar(5)与varchar(200)内存占用不同(在磁盘中根据实际的长度进行保存，在内存中会分配指定大小的空间，在内存中时定长的)；
5. varchar在变更长度时会出现锁表
varchar的使用场景：
1. 适合存储长度波动大的数据(如博客文章)；
2. 字符串很少被更新的场景(修改的过程中会重新计算字符串的长度)；
3. 适合保存多字节字符；
char类型：属于定长数据，最大长度255，在保存的时候会自动删除末尾的空格，检索效率比varchar高，写的效率也比varchar高。
char的使用场景：
1. 适合存储长度波动不大的数据，如MD5摘要；
2. 适合存储短字符串；
3. 适合存储经常更新的数据；
DATETIME日期类型：日期时间类型，占用8个字节，与失去无关，可保存到毫秒，可保存时间范围大。
PS:不要使用字符串存储时间类型，无法使用MySQL函数进行处理，占用空间会大的多。
TIMESTAMP时间戳：占用4个字节，时间范围 1970-01-01 到 2038-01-19，精确到了秒，采用整型进行存储(特殊的整型)，依赖于时区，会自动更新TIMESTAMP的值。
MySQL的utf8和标准的utf8，只支持3字节，像表情符号等4字节的数据是无法保存的，在MySQL中建库的时候，字符集最好使用utf8mb4
MySQL体系结构
1. 客户端：JDBC、ODBC
2. 服务层：连接管理器(管理客户端请求链接)、查询缓存、查询解析器(解析SQL文本，提取必要的组件)、查询优化器(对SQL语句执行过程优化)
3. 存储引擎层：MyISAM、InnoDB…
MySQL存储引擎：存储引擎只针对表，数据库中允许出现不同的引擎，每一个表只能有一种存储引擎。
MySQL存储引擎：

InnoDB、MyISAM、XtraDB、CSV、Memory(内存表存储引擎)、Archive、Federated
InnoDB存储引擎：最常用的存储引擎，是MySQL 5.5.8之后的默认存储引擎，支持事务处理，有良好的并发性，采用"表空间"保存文件，
InnoDB存储特性，InnoDB表空间有两种形式：
1. 使用系统表空间(ibdataN)(早期使用)，所有的数据都放在一个文件中;
2. 独立表空间：tablename.ibd(推荐)。
设置innodb_file_per_table决定表空间模式。
建表指定存储引擎(5.5.8版本之后不指定存储引擎的话，默认为INNODB)
```
CREATE TABLE innodb_test1 (id INT, NAME VARCHAR(16)) ENGINE INNODB;
```
在MySQL的安装目录 /data/数据库名称下会生成两个文件：
1. innodb_test1.frm —保存的是表的定义数据，字段名称、字段类型、约束等
2. innodb_test1.ibd —保存的是表中存储的数据(每个表中会单独创建一个表空间)

列类型	存储空间	是否精确类型
FLOAT	4字节	否
DOUBLE	8字节	否
DECIMAL	9字节	是

设置MySQL中所有的表都是用系统的表空间

SHOW VARIABLES LIKE 'innodb_file_per_table';  --查询全局变量，是否使用系统表空间，on表示不使用，off表示使用
SET GLOBAL innodb_file_per_table = 'off'; --设置所有的表使用系统表空间,所有数据放在一个文件下面，不利于管理，会产生IO瓶颈，系统表空间很难回收存储空间，
SET GLOBAL innodb_file_per_table = 'on';  --设置每个表使用单独的表空间(系统默认)

独立表空间使用 optimizw table 命令回收存储空间。
在MySQL 5.6之后默认使用独立表空间进行存储。
InnoDB事务特性：InnoDB支持事务，默认使用行级锁，具有良好的高并发特性
MySQL的锁：

职责分类粒度分类
共享锁 – 读锁行级锁
独占锁(排他锁) – 写锁表级锁

职责分类	粒度分类
共享锁 – 读锁	行级锁
独占锁(排他锁) – 写锁	表级锁

在MySQL中开启一个事务

BEGIN;   --开启一个事务
UPDATE innodb_test1 SET NAME = '张三a' WHERE id = 1;  --执行需要执行的sql语句
COMMIT;  --提交当前事务

注意：在InnoDB中只有利用索引的更新、删除操作，才使用行级锁；不能使用索引的写操作使用表级索。
所以在实际的开发中，如果遇到写操作，一定要确保update/delete语句的条件要能够使用索引，否则就会锁表，程序不具备并发性。
InnoDB适用场景：适用于绝大多数场景，MySQL 5.7 之后也支持全文索引与空间函数(地理运算)；
注意：MySQL 5.5 之前，默认的存储引擎为MyISAM。
MyISAM存储引擎特点：
1. 不支持事务(提交与回滚)，在执行回滚的时候，MyISAM存储引擎表中的操作不会被撤销；
2. 支持全文检索，支持text支持前缀索引；
3. 支持数据压缩；
4. 紧密存储，顺序读的性能很好；
5. 表级锁，混合读写性能不佳，并发性差(不适合高并发程序)
MyISAM应用场景：
1. 非事务应用，例如：保存日志(大多为追加写操作，极少进行更新操作)；
2. 只读类应用，报表数据，字典数据(大量读、少量写)；
3. 空间类应用，开发GIS系统(5.7版本之前)；
4. 系统临时表，SQL查询，分组的临时表引擎(查询时，系统自动生成的零时表)。

创建使用MyISAM引擎的表：

CREATE TABLE myisam_test1 (id INT, NAME VARCHAR(16)) ENGINE MYISAM;

使用MyISAM引擎的表在硬盘中存储的时候会生成3个文件，MyISAM没有表空间

myisam_test1.frm —存储表的结构以及相关声明性的信息

myisam_test1.MYD —数据文件

myisam_test1.MYI —数据文件
Memory存储引擎：数据存储在内存中，而不是硬盘中。
Memory特点：
1. 不支持事务；
2. 内存读写，临时存储，重启MySQL后，表中的数据会被清空；
3. 超高的读写效率，比MyISAM高一个量级；
4. 表级锁，并发性差，适合读多写少的数据；
Memory应用场景：
1. 读多写少的静态数据，例如省市县的对应表(不适合频繁更新的操作)；
2. 充当缓存使用，保存高频访问的静态数据，并且可以使用SQL语句；
3. 系统临时表，在执行大SQL的时候，MySQL自动创建的零时表(在边界值以内的时候，临时表的数据以Memory的形式存储在内存中，超过边界值时以MyISAM的形式存储在硬盘中)；
在执行大SQL的时候，MySQL自动创建的零时表的空间大小在边界值以内的时候，临时表的数据以Memory的形式存储在内存中，超过边界值时以MyISAM的形式存储在硬盘中。
Memory的关键参数：
1. max_heap_table_size ---- 设置内存表大小(字节)；
2. tmp_table_size — 设置内存临时表最大值(字节)，值要小于或等于max_heap_table_size 的值；

创建MEMORY存储引擎的表：

CREATE TABLE memory_test1 (id INT, NAME VARCHAR(16)) ENGINE MEMORY;

MEMORY的表在硬盘中只有一个frm文件，数据文件不保存在硬盘中。
MEMORY表中的数据只要服务器不宕掉，数据一直在，但是一旦重启服务器，表中的数据将被全部清空。

MEMORY表大小默认为16M，查看和修改MEMORY的大小：

SHOW VARIABLES LIKE '%HEAP%';
SET GLOBAL max_heap_table_size = 16777216;  --SET GLOBAL修改的参数，只对当前实例有效，服务器重启后会回复默认值，如果想要永久有效，则需要修改配置文件/etc/my.conf
SET GLOBAL tmp_table_size = 16777216;  --设置临时表的大小

CSV存储引擎：存文本保存，不支持事务，不支持索引
CSV的应用场景：
1. 数据交换/数据迁移
2. 不依赖MySQL环境

创建CSV表

CREATE TABLE csv_test1 (id INT NOT NULL, NAME VARCHAR(16) NOT NULL) ENGINE CSV;

CSV在硬盘中存储的文件为：

csv_test1.CSM —csv的管理文件(二进制文件)

csv_test1.csv

csv_test1.frm

kangswx

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据类型以及存储引擎

影响MySql性能的主要因素：数据库结构设计；数据库存储引擎；SQL语句的书写；数据库参数配置；服务器操作系统与硬件。数据库设计三大范式：第一范式：数据库所有字段都只有单一属性；单一属性是由基本数据类型构成的；数据库的表都是二维的(行与列)(表中的字段不能被拆分)；第二范式：在满足第一范式的前提下，表中必须有一个主键(一列或多列)，其他字段可由主键确定，第二范式的目的...
复制链接

扫一扫

专栏目录