前言

上一篇mysql基础学习，简单总结了mysql数据库设计的几个步骤，以及每个步骤要做的事情，但是针对最为关键的物理设计并没有过多提及，因为物理设计部分涉及数据库存储引擎的选择，数据表中字段类型的选择，这些内容较多，这里单独开一篇博客来进行总结

物理设计要做的事情

数据库物理设计阶段，其实就是根据所选择的关系型数据库的特点，对逻辑模型进行存储结构设计。在mysql中，物理设计中有如下几个事情要完成

1、定义数据库、表及字段的命名规范

2、选择合适的存储引擎

3、为表中的字段选择合适的数据类型

4、建立数据库结构

说白了，就是将逻辑模型，落地成数据库表。这里最为关键的就是存储引擎的选择和表中字段数据类型的选择。至于所谓的命名规范，这里不做提及。

mysql的存储引擎

存储引擎是作用于具体的数据表的，不是某个数据库。mysql其实支持同一数据库中不同的表，采用不同的存储引擎，但是一般不建议这么操作。

MyISAM

特点

mysql5.5之前版本默认的存储引擎，也是mysql大部分系统表和临时表所使用的存储引擎，这里说的临时表，不是我们手动建立的临时表，而是在排序，分组等操作中，当数量超过一定大小之后，由查询优化器建立的临时表。

MyISAM存储引擎的数据表存储的文件，由MYD和MYI组成，MYD存储的是表中的数据内容，而MYI是存储表的索引。

MyISAM使用的是表级锁，不是行级锁，对表中数据修改的时候，会对表增加表级排他锁，读取表中的数据的时候，需要增加共享锁。

MyISAM不支持事务，

MyISAM支持全文索引，支持对text和blob字符，支持前500个字符的前文索引，并且支持数据压缩（对于数据不怎么变化的表，压缩是一个不错的选择），压缩之后的表，只能读取，无法被修改。

适用场景

可适用于非事务的应用，只读类型的应用，空间类（比如存储GPS数据）的应用。

Innodb

MySQL5.5之后的默认存储引擎。

InnoDB引擎的数据表，存储数据根据innodb_file_pre_table变量的值不同而有所差异，如果innodb_file_pre_table为ON ，则InnoDB存储引擎的数据表数据会存储在独立的表空间中，一般为tablename.ibd文件，如果innodb_file_pre_table为OFF，则数据会存储在系统表空间中，一般存储于ibdataX文件（X为数字）中，这个时候所有的InnoDB引擎的数据表中的数据，都会存储于这种文件中。使用InnoDB存储引擎的数据表，强烈建议使用独立的表空间来存储数据。

InnoDB支持事务，为了能够实现事务的ACID属性，InnoDB采用了Redo log和Undo log日志类型。

Redo log主要用于实现日志的持久性，在数据库中一般存储文件名为ib_logfileX（X为数字），mysql中有两个参数来配置Redo log的文件buffer大小和文件个数，innodb_log_buffer_size，这个参数就是mysql中配置的Redo log的缓冲区大小，基本每隔1秒，mysql就会将缓冲区中的内容刷新到磁盘，因此一般不用配置的太大。innodb_log_files_in_group决定了ib_logfilex的文件个数。

Undo log主要用于帮助未提交事务进行回滚，和实现mvcc多版本并发控制，Redo log存储的是已提交的事务，而Undo log存储的是未提交的事务。

InnoDB支持行级锁。行级锁可以最大程度的支持并发，行级锁是在存储引擎层实现的。数据库中的锁主要用户管理共享资源的并发访问，也用于实现事务的隔离性。

InnoDB提供了状态检查工具，可以通过show engine innodb status 命令来查看当前的引擎状态。

适用场景

可以使用与大多数的线上应用场景，无论是否有事务支持，在mysql5.7之后，innodb也支持全文索引和空间数据了。

CSV

特点

这种存储引擎数据存储格式就是CSV文件格式，数据是以文本方式存储在文件中，这点不同于MyISAM存储引擎和InnoDB存储引擎，其数据文件可以在数据库运行的时候，单独拷贝出来进行操作。

以CSV格式进行数据存储。所有列必须都是非空的。

CSV存储引擎不支持索引。不适合大表，不适合在线处理。

支持对数据文件直接编辑，这点也是其他存储引擎做不到的。

适用场景

非常适合作为数据交换的中间表，在不同系统之间可以用作CSV来进行数据交换。

Archive

特点

Archive引擎会缓存所有的写操作。以zlib对表数据进行压缩，磁盘I/O较小，数据存储在ARZ为后缀的文件中。

只支持insert和select操作，支持行级锁和专用的缓冲区，可以满足高并发的要求。

不支持事务，只允许在自增id列上建立索引。

适用场景

比较适合用于日志和数据采集类的应用，因为不支持对数据的删除和修改操作，同时存储所用空间小。

Memory

特点

也称为HEAP存储引擎，所有数据都是存储在内存中的，一旦mysql数据库服务器重启，则数据全部丢失，但是表结构还是存在的（因为表结构是保存在磁盘中）

支持Hash索引和BTree索引，默认为Hash索引。

所有字段的存储都为固定长度，比如，定义varchar(10)的字段，也会转换为char(10)

不支持BLOB和TEXT等大字段类型

使用表级锁，不支持行级锁

表的最大大小，由参数max_heap_table_size参数决定，这个参数的修改对存量表不生效。

适用场景

用于查找表，或者映射表，比如邮编和地址。

用于保存数据分析中产生的中间表

适用于缓存周期性聚合数据的结果表

Federated

特点

提供了访问远程mysql服务器上表的方法，本地不存储数据，数据会放到远程服务器上，本地需要保存远程表的结构信息和连接信息

mysql默认是禁止该存储引擎的。

使用场景

用于偶尔的统计分析以及手工查询的场景。

如何选择存储引擎

大部分情况可以直接选择innodb存储引擎，除非真的有innodb不支持的场景，才选择其他存储引擎。

mysql的数据类型

在mysql数据库设计的第二步，就是为每一个表字段选择合适的数据类型，mysql中数据类型大体上分为整数类型、实数类型、时间类型、字符串类型

整数类型

mysql中每一种整数类型，以及适用的数据大小范围，都如下表所示

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

实数类型

存储小数，需要用到实数类型

列类型	存储空间	是否精确类型
FLOAT	4字节	否
DOUBLE	8个字节	否
DECIMAL	每4个字节存9个小数，小数点占1个字节	是

所谓的不精确，是指小数部分存储的并不很精确，关于decimal举一个如下的实例

123456789.123456789=decimal(18,9) ##占用9字节(小数点前9位数，占用4个字节，小数点后也是四个字节，小数点1个字节，共9个字节)

时间类型

mysql中常用的时间类型有5种

列类型	存储空间	格式	范围
DATE	3字节	YYYY-MM-DD	从'1000-01-01'到'9999-12-31'
TIME	3~6字节	HH:MM:SS.[微妙值]	从'-838:59:59'到'838:59:59'
YEAR	1字节	YYYY	从'1901'到'2155'
DATETIME	5~8字节	YYYY-MM-DD HH:MM:SS[.微秒值]	从'1000-01-01 00:00:00'到'9999-12-31 23:59:59'
TIMESTAMP	4~7字节	YYYY-MM-DD HH:MM:SS[.微秒值]	从'1970-01-01 00:00:01' UTC到'2038-01-19 03:14:07' UTC

关于上表中的微秒值，由于微秒最长会有6位，不同长度的微秒值占用的存储空间不同

微秒位数	存储空间
0	0字节
1,2	1字节
3,4	2字节
5,6	3字节

我们通常定义指定长度的time类型，就是可以根据指定的长度不同，存储的微秒位数不同

比如：

实例1	time	18:00:00
实例2	time(2)	18:00:00:78
实例3	time(4)	18:00:00:7812
实例4	time(6)	18:00:00:781216

字符串类型

类型	范围	说明
char(M)	M=1~255个字符	固定长度
varchar(M)	一行中所有varchar类型的列所占用的字节数不能超过65535个字节。UTF8mb4 varchar(10)=40字节	存储可变长度的M个字符
tinytext	最大长度255个字节	可变长度
text	最大长度65535个字节	可变长度
mediumtext	最大长度16777215个字节	可变长度
longtext	最大长度4294967295个字节	可变长度
enum	集合最大数目为65535	只能插入列表中的值

总结

简单梳理了一下mysql的存储引擎和数据类型