MySQL存储引擎和数据类型

一 了解MySQL存储引擎

    什么是MySQL存储引擎？

        MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。   

        例如，如果你在研究大量的临时数据，你也许需要使用内存MySQL存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者，你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)。   

        这些不同的技术以及配套的相关功能在 MySQL中被称作存储引擎，存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为（表类型）。 MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。   

        存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式。

   Oracle，SqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。

    关系数据库表是用于存储和组织信息的数据结构，可以将表理解为由行和列组成的表格，类似于Excel的电子表格的形式。有的表简单，有的表复杂，有的表根本不用来存储任何长期的数据，有的表读取时非常快，但是插入数据时去很差；而我们在实际开发过程中，就可能需要各种各样的表，不同的表，就意味着存储不同类型的数据，数据的处理上也会存在着差异，那么。对于MySQL来说，它提供了很多种类型的存储引擎（或者说不通的表类型），我们可以根据对数据处理的需求，选择不同的存储引擎，从而最大限度的利用MySQL强大的功能。

      使用命令查看MySQL支持的引擎：show engines;

                  查看当前默认使用的存储引擎命令: show variables like '%storage_engine%';

            MyISAM

                           MyISAM自版本3.23开始成为MySQL的默认存储引擎（windows中默认为InnoDB）。对应初学者来说，MyISAM表是独立于操作系统的，这说明可以轻松将其从Windows服务器移植到Linux服务器。

        MyISAM不支持事务，不支持外键，支持全文索引，处理速度快。对事务完整性没有要求或者以SELECT、INSERT为主的应用基本都可以使用这个引擎来创建表。

        MyISAM存储引擎的表存储成3个文件，文件名与表名相同，扩展名分别为：frm，MYD，MYI。

• frm文件：存储表的结构。

• myd文件：存储数据。

• myi文件：存储存储索引。

    支持3种不同的存储格式：

静态(固定长度)表

动态表

压缩表

　　其中静态表是默认的存储格式。静态表中的字段都是非变长字段，这样每个记录都是固定长度的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多。静态表在数据存储时会根据列定义的宽度定义补足空格，但是在访问的时候并不会得到这些空格，这些空格在返回给应用之前已经去掉。同时需要注意：在某些情况下可能需要返回字段后的空格，而使用这种格式时后面到空格会被自动处理掉。 　　

动态表包含变长字段，记录不是固定长度的，这样存储的优点是占用空间较少，但是频繁到更新删除记录会产生碎片，需要定期执行OPTIMIZE TABLE语句或myisamchk -r命令来改善性能，并且出现故障的时候恢复相对比较困难。 　　

压缩表由myisamchk工具创建，占据非常小的空间，因为每条记录都是被单独压缩的，所以只有非常小的访问开支。 

    InnoDB

InnoDB是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。我的电脑上安装的MySQL 5.6.13版，InnoDB就是作为默认的存储引擎。InnoDB还引入了行级锁定和外键约束，在以下场合下，使用InnoDB是最理想的选择：

1.更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。

2.事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。

3.自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。

4.外键约束。MySQL支持外键的存储引擎只有InnoDB。

5.支持自动增加列AUTO_INCREMENT属性。

一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB是不错的选择。

    MEMORY

使用MySQL Memory存储引擎的出发点是速度。为得到最快的响应时间，采用的逻辑存储介质是系统内存。虽然在内存中存储表数据确实会提供很高的性能，但当mysqld守护进程崩溃时，所有的Memory数据都会丢失。获得速度的同时也带来了一些缺陷。它要求存储在Memory数据表里的数据使用的是长度不变的格式，这意味着不能使用BLOB和TEXT这样的长度可变的数据类型，VARCHAR是一种长度可变的类型，但因为它在MySQL内部当做长度固定不变的CHAR类型，所以可以使用。

一般在以下几种情况下使用Memory存储引擎：

1.可以忽略。目标数据较小，而且被非常频繁地访问。在内存中存放数据，所以会造成内存的使用，可以通过参数max_heap_table_size控制Memory表的大小，设置此参数，就可以限制Memory表的最大大小。

2.暂时。如果数据是临时的，而且要求必须立即可用，那么就可以存放在内存表中。

3.相对无关。存储在Memory表中的数据如果突然丢失，不会对应用服务产生实质的负面影响。

MERGE

MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同，尽管其使用不如其它引擎突出，但是在某些情况下非常有用。说白了，Merge表就是几个相同MyISAM表的聚合器；Merge表中并没有数据，对Merge类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行操作。Merge存储引擎的使用场景。

对于服务器日志这种信息，一般常用的存储策略是将数据分成很多表，每个名称与特定的时间端相关。例如：可以用12个相同的表来存储服务器日志数据，每个表用对应各个月份的名字来命名。当有必要基于所有12个日志表的数据来生成报表，这意味着需要编写并更新多表查询，以反映这些表中的信息。与其编写这些可能出现错误的查询，不如将这些表合并起来使用一条查询，之后再删除Merge表，而不影响原来的数据，删除Merge表只是删除Merge表的定义，对内部的表没有任何影响。

ARCHIVE

Archive是归档的意思，在归档之后很多的高级功能就不再支持了，仅仅支持最基本的插入和查询两种功能。在MySQL 5.5版以前，Archive是不支持索引，但是在MySQL 5.5以后的版本中就开始支持索引了。Archive拥有很好的压缩机制，它使用zlib压缩库，在记录被请求时会实时压缩，所以它经常被用来当做仓库使用。

如何选择合适的存储引擎？选择标准可以分为：

（1）是否需要支持事务；

（2）是否需要使用热备；

（3）崩溃恢复：能否接受崩溃；

（4）是否需要外键支持；

二 数据类型

1 日期和时间数据类型

date:存储日期信息，以标准格式YYYY-MM-DD存储，可以使用数字或字符串来插入，范围从1000-01-01到9999-12-31

datetime:存储日期和时间信息的组合，以标准格式YYYY-MM-DD HH:MM:SS存储，可以使用数字或字符串来插入，范围从1000-01-01 00:00:00到9999-12-31 23:59:59

time:存储时间信息，支持范围大，范围从-838:59:59到838:59:59

timestamp:混合日期和时间值，时间戳，以标准格式YYYY-MM-DD HH:MM:SS存储，范围从1970-01-01 00:00:00到2037-12-31 23:59:59

year:存储年份信息，范围从1901到2155

2 数值数据类型

bool和boolean:存储0和1

bigint:最大的整数范围，有符号数范围(-9 223 372 036 854 775 808，9 223 372 036 854 775 807)，无符号数范围(0，18 446 744 073 709 551 615)

int:第二大的整数范围，有符号数范围(-2 147 483 648，2 147 483 647)，无符号数范围(0，4 294 967 295)

mediumint:第三大的整数范围，有符号数范围(-8 388 608，8 388 607)，无符号数范围(0，16 777 215)

smallint:第四大的整数范围，有符号数范围(-32 768，32 767)，无符号数范围(0，65 535)

tinyint:最小的整数范围，有符号数范围(-128，127)，无符号数范围(0，255)

decimal:存储为字符串的浮点数

double:双精度浮点数

float:单精度浮点数

3 字符串数据类型

char:固定长度字符串，0-255字节

varchar:可变长度字符串，0-65535 字节

longblob:最大的二进制文本数据，0-4 294 967 295字节

longtext:最大文本数据，0-4 294 967 295字节

mediumblob:第二大的二进制文本数据，0-16 777 215字节

mediumtext:第二大文本数据，0-16 777 215字节

blob:第三大的二进制文本数据，0-65535 字节

text:第三大文本数据，0-65535 字节

tinyblob:最小的二进制文本数据，0-255字节

tinytext:最小文本数据，0-255字节

enum:其值来自表创建时在列规定中显式枚举的一列值。最多可以有65,535个元素。

set:可以有零或多个值，其值来自表创建时规定的允许的一列值。指定多个SET成员的SET列值时各成员之间用逗号隔开。最多可以设置64个值。

数据类型属性：

auto-increment:自动递增，适用于整数类型

binary:以区分大小写的方式排序（根据ASCII）

default:默认值

index:索引

national:使用默认字符集

not null:列不允许包含NULL值

null:列可包含NULL值

primary key:主键

unique:唯一值

zerofill:零填充，使所有剩余字段空间用0填充