前言
上一篇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的存储引擎和数据类型
