MySQL || 数据类型与存储引擎

数据类型

MySQL的数据类型大致分为5种，整数类型、浮点数类型、定点数类型、日期与时间类型、字符串类型、二进制类型

1. 数值数据类型
   整数类型包括TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT
   浮点数类型包括FLOAT和DOUBLE
   定点数类型包括DECIMAL
2. 日期和时间类型
   YEAR,TIME,DATE,DATETIME,TIMESTAMP
3. 字符串类型
   CHAR,VARCHAR,BINARY,VARBINARY,BLOB,TEXT,ENUM,SET
4. 二进制类型
   BIT,BINARY,VARBINARY,TINYBLOB,BLOB,MEDIUMBLOB,LONGBLOB

1. 数值数据类型

整数类型

类型名称	说明	取值范围	存储需求
TINYINT	很小的整数	-128〜127	1个字节，0 〜255
SMALLINT	小的整数	-32768〜32767	2个宇节，0〜65535
MEDIUMINT	中等大小的整数	-8388608〜8388607	3个字节，0〜16777215
INT (INTEGHR)	普通大小的整数	-2147483648〜2147483647 4个字节， 0〜4294967295
BIGINT	大整数 -9223372036854775808〜9223372036854775807	8个字节0〜18446744073709551615

TINYINT需要1个字节（8bit）存储，则无符号数最大值为2⁸-1=255,有符号数最大值为2⁷-1=127，（一位用来存储符号位）

小数类型

• MySQL使用浮点数和定点数表示小数
  浮点类型有两种，单精度浮点数float，双精度浮点数double；定点数类型只有一种，decimal
• 浮点类型和定点类型都可以用(M,D)来表示，M称为精度，表示总共的位数，D称为标度，表示小数的位数
  浮点数类型取值范围M（1 ~ 255）和D（1 ~ 30），DECIMAL默认D值是0，M值是10

类型名称	说明	存储需求
FLOAT	单精度浮点数	4 个字节
DOUBLE	双精度浮点数	8 个字节
DECIMAL (M, D)，DEC	压缩的“严格”定点数	M+2 个字节

2. 日期和时间类型

类型名称	日期格式	日期范围	存储需求
YEAR	YYYY	1901 ~ 2155	1 个字节
TIME	HH:MM:SS	-838:59:59 ~ 838:59:59	3 个字节
DATE	YYYY-MM-DD	1000-01-01 ~ 9999-12-3	3 个字节
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	8 个字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC	4 个字节

3. 字符串类型

类型名称	说明	存储需求
CHAR(M)	固定长度非二进制字符串	M 字节，1<=M<=255
VARCHAR(M)	变长非二进制字符串	L+1字节，在此，L< = M和 1<=M<=255
TINYTEXT	非常小的非二进制字符串	L+1字节，在此，L<2^8
TEXT	小的非二进制字符串	L+2字节，在此，L<2^16
MEDIUMTEXT	中等大小的非二进制字符串	L+3字节，在此，L<2^24
LONGTEXT	大的非二进制字符串	L+4字节，在此，L<2^32
ENUM	枚举类型，只能有一个枚举字符串值	1或2个字节，取决于枚举值的数目 (最大值为65535)
SET	一个设置，字符串对象可以有零个或 多个SET成员	1、2、3、4或8个字节，取决于集合 成员的数量（最多64个成员）

4. 二进制类型

类型名称	说明	存储需求
BIT(M)	位字段类型	大约 (M+7)/8 字节
BINARY(M)	固定长度二进制字符串	M 字节
VARBINARY (M)	可变长度二进制字符串	M+1 字节
TINYBLOB (M)	非常小的BLOB	L+1 字节，在此，L<2^8
BLOB (M)	小 BLOB	L+2 字节，在此，L<2^16
MEDIUMBLOB (M)	中等大小的BLOB	L+3 字节，在此，L<2^24
LONGBLOB (M)	非常大的BLOB	L+4 字节，在此，L<2^32

存储引擎

• 数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建，查找，更新和删除数据操作。存储引擎就是表的类型，数据库的存储引擎决定表在计算机中的存储方式。
• MySQL插件式存储引擎是MySQL数据库服务器中的组件，代表了下面物理层提供的抽象逻辑接口，也是数据库执行实际I/O操作的地方，这类特殊应用需求包括数据仓储，事务处理，高可用性等，还能利用独立于任何存储引擎的一组接口和服务。
• handler类定义了存储引擎提供的接口和功能，所有的存储引擎从基类handler继承，所以都能提供同样的功能，所编写的存储引擎只有满足了handler要求后，才能顺利的插入到运行的MySQL服务器中。

handlerton和handler

• handlerton类
  handlerton类为所有存储引擎提供标准的接口，由sql目录中handler.h文件定义并且在文件handler.cc中实现。

• handler类
  handler类继承自sql_alloc，sql_alloc类定义了MySQL的各种内存分配方法，所以handler类的内存分配方法也都是继承自sql_alloc类。handler类具体实现存储引擎的数据处理方法，他与存储引擎之间依赖handlerton标准进行通信。

• MySQL支持的存储引擎有InnoDB，MyISAM,Memory,Merge,Archive,CSV,BLACKHOLE,可以使用SHOW ENGINES;语句查看系统所支持的引擎类型

存储引擎	描述
ARCHIVE	用于数据存档的引擎，数据被插入后就不能在修改了，且不支持索引。
CSV	在存储数据时，会以逗号作为数据项之间的分隔符。
BLACKHOLE	会丢弃写操作，该操作会返回空内容。
FEDERATED	将数据存储在远程数据库中，用来访问远程表的存储引擎。
InnoDB	具备外键支持功能的事务处理引擎
MEMORY	置于内存的表
MERGE	用来管理由多个 MyISAM 表构成的表集合
MyISAM	主要的非事务处理存储引擎
NDB	MySQL 集群专用存储引擎

• MySQL中的表，在磁盘上均有一个.frm作为扩展名的文件与之对应。

常用的存储引擎

MyISAM

MyISAM将数据存放在本地磁盘，当在创建表后，MySQL将在配置的数据目录下生成3个文件，索引文件和数据文件是分离的，索引文件仅保存数据记录的地址

• .MYD：存放数据

• .MYI：存放索引

• .frm：表结构

• 数据文件.MYD
  支持三种不同的存储格式-固定的、动态的、压缩的，固定格式与动态格式根据正适用的列的类型自动选择，压缩格式只能使用myisampack工具创建

1. 静态表（默认）：字段都是非变长的，存储非常迅速，容易缓存，出现故障容易恢复，占用空间通常比动态表多
2. 动态表：占用空间相对较少，但是频繁的更新删除数据会产生碎片，出现故障的时候恢复困难
3. 压缩比哦啊，使用myisampack工具创建，占用非常小的磁盘空间，每个记录都是被单独压缩的，因此只需要非常小的访问开支

• 索引文件.MYI
  MYI文件包含两部分-头部信息和索引值
  头部信息记录了该表的索引选项、索引文件大小和索引定义
  索引值是按页面page形式存储的，每一页中只存储来自于同一个索引的值
  1.每个索引包含索引列的所有内容，包括列的前置空格和后置空格
  2.行大小固定的表，其索引指针大小也是固定的。行大小动态变化的表，其索引指针指向数据行在MYD的偏移量。
  3.索引页面的正常大小是1024字节
• MyISAM引擎的索引结构为B+Tree，B+Tree的数据域存储的内容为实际数据的地址，索引和实际数据是分开的，索引指向实际的数据。
• 没有提供对数据库事务的支持，是表级锁，当插入或更新数据时，需要锁定整个表，效率变低

InnoDB

• InnoDB的数据文件本身就是索引文件

• InnoDB提供给MySQL具有可回滚和防崩溃能力的事务型存储引擎，使用的是行级锁，支持外键功能，可以将InnoDB类型的表与其他表类型混合起来查询。

• 架构

在第一层，Handle
APl的存在，使得InnoDB能够顺利插入到MySQL服务器中。同时InnoDB还为应用系统提供了API，在这种情况下，用户可以直接将InnoDB存储引擎嵌入到他的应用系统之中。
第二层是事务层。在InnoDB中，所有的行为都发生在事务中。如果我们在my.cnf中配置了auto-commit这一属性，那么我们执行的每个SQL语句都是一个单独的事务。SQL关键字COMMIT、ROLLBACK等被查询解析子系统解析为InnoDB特有查询语句。
第三层是锁功能层。该层完成锁功能和事务管理的功能(如回滚、提交等操作)。InnoDB采用行级的读写锁。
第四层是缓存管理。缓存管理层的主要目标是高效地将数据存放在内存之中。该层的功能由目录 buf中的源文件实现
第五层是存储空间I0管理。该层的目标是为文件读写提供接口并维护表空间和日志空间的大小。该层的代码主要分布在fil目录下。InnoDB为了提供高效的磁盘访问，采用和RAIDO相似的条带化方法将逻辑块数据分布到不同的几个物理文件中。

• 使用场景： 由于其支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）

MyISAM与InnoDB的区别

1. MyISAM的索引文件和数据文件是分离的，索引文件仅保存数据记录的地址，InnoDB的数据文件本身就是索引文件，B+Tree的数据域存储的就是实际的数据，这种索引就是聚集索引。
2. InnoDB要求表必须有主键，没有显示指定，MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型，MyISAM可以没有主键
3. 与MyISAM索引不同的是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。