MySql学习笔记（一）- 表类型有哪些，怎么用？

天天在用mysql，你说sql有什么学的，但是面试官说你学的都是皮毛。怎么样？确实萌新自问发现确实咋就是会写个sql，好点的话还能写个几十行让别人看都看不懂，甚至为自己写了一手好sql而感叹软件开发太简单了，飘飘然还想爬一波泰山说一句会当凌绝顶一览众山小的豪言壮志。

要说的就是mysql的一些比较重要的东西，也是对自己学习的一种挖掘。所以，说的不对的地方还望大家指正。读过我文章的朋友都知道我写的比较简洁，希望写博客的过程能够梳理自己的思维方式，也希望能够帮到别人，少走弯路。

我们都知道数据库是一个数据树，就是将数据用树的结构存储，就二叉树来说可以筛选50%的数据，所以树同一节点的分支愈多则树的深度就会越少，树深越小的话检索的速度就能提升上去，毕竟B树的检索瓶颈往往位于读磁盘上。所以说一张表就是一棵树，那么这个树的类型有哪些？这也就是我们标题所示的表类型。

那么具体的表类型有哪些？搞开发的人都知道InnoDB，除此之外还知道myISAM是一种适合做全文检索的树，其他的就触及到盲区了。那么我就逐个的过一遍吧！

mysql的基本表类型有MyISAM、InnoDB、DBD、MEMORY、MERGE、EXAMPLE、NDB CLUSTER、ARCHIVE、CSV、BLACKHOLE、FEDERATED等。其中InnoDB和BDB提供了事务。其他都不是事务安全的。如果创建表的时候不指定表类型，默认为InnoDB，这是mysql5.5之后约定的规范。当然您可以通过修改ini文件来指定默认的表类型。

使用命令：show engines可以查看当前数据库支持的表类型，从下图可以看出，我这个数据库不支持BDB，也就是说事务型的库也之后InnoDB了。

其实我们可以在创建表的时候指定表类型，比如

use pymysql;
create table mysqlLearn(
id int not null primary key,
name varchar(64),
age int
) engine MyISAM


#或者使用alter语句进行修改
alter table mysqlLearn engine=innodb

各种表类型的对比

通过上图的对比，我们发现InnoDB其实是当下最好的表类型了。但是他的数据上限是64TB

MyISAM

mysisam不支持事务，也没有外键关系，他的优势是访问速度快。所以说如果对事务完整性要求比较低或者说以insert和select为主的库可以采用myisam。

myisam库会带有几个文件，分别为

.sdi   表结构定义
.MYD 数据
.MYI  索引

当myisam表出现损坏的时候可以采用check table 表名进行检测

并使用repair table 表名 进行修复

check table mysqllearn;
repair table mysqllearn;

要注意的是myisam在存储的时候会对数据进行压缩，比如会将数据末尾的空格去掉。这一点需要注意。

InnoDB

InnoDB提供了提交、回滚和数据崩溃的恢复机制。但也是这点让其在效率上差于myisam，并且其在磁盘上的占用空间也大于myisam。innnodb提供了自增主键，修改自增主键的开始基数的时候可以使用alter table 表名 auto_increment=n;对于innoDB来说自增列必须是主键，如果是组合索引，必须是组合索引的第一列。但是对于myisma来说自增列可以是组合索引的其他列。具体是按照组合列排序之后进行递增。在主外键关系中，innodb提供了cascade、set null、restrict等策略。

MEMORY

是一种使用内存的内容来创建表。每个memory对应一个磁盘文件。memory的表访问极快，并且使用的是hash索引，但是如果服务关闭表中的数据就会消失。

create table thistory (
id varchar(64) not null primary key,
close double
) engine MEMORY ;


insert into thistory select date as id,close as close from sh600567 limit 100 ;


select * from thistory;

MERGE

是一组myisam表的组合，但是这些myisam表的结构完全相同，MERGE表本身没有数据，对MERGE表可以进行查询更新删除操作，这些表其实是对myisam的操作。对merge的插入实际上是通过insert_method子句定义插入的表。使用first和last可以使得插入在第一或者最后的表上，不定义或者定义为no表示不能插入。对merge的drop，只是删除merge的定义，对内部的表没有任何影响。

如下所示：

create table mysqlLearn1(
id int not null primary key,
name varchar(64),
age int
) engine MyISAM;


create table mergeTest(
id int not null primary key,
name varchar(64),
age int
)engine=merge union=(mysqlLearn,mysqlLearn1) insert_method=last;


insert into mysqllearn1 value(1,'123',1);
select * from mergeTest;
insert into mergetest value(10,'1',2);
select * from mysqllearn1;

如何选择合适的表类型

对事务要求低、主要以读和插入为主的业务可以采用myisam，比如一些展示类的数据。如果对事务要求比较高并且要有数据的安全性则可以选用innoDB，Membry是一种存在内存中的表类型，因为membory对数据大小有限制，所以不能大范围的缓存数据。适合一些小表。因为membory是内存表，所以重启服务之后数据就没有了。在mergeDB上其实是一种逻辑表，相当于将myisam联合到了一起，其优点是可以改善单表限制。然后将数据存到不同的磁盘上，还可以改善读写性能。