MYSQL 学习笔记（五）存储引擎

插件是存储引擎室MYSQL数据库的最重要的特性之一，用户可根据需要选择如何存储和索引数据、是否使用事物等。Mysql默认支持多种存储引擎，以适用不同领域数据库应用需要。

创建表时如果不指定存储引擎，那么系统使用默认的存储引擎，mysql5.5之前默认的存储引擎室MyISAM

5.5之后改为了InnoDB.如果需要修改默认的存储引擎，

可以在参数文件中设置default-table-type.查看当前默认的存储引擎，可使用以下命令：

Mysql> show variables like ‘table_type’;

创建表的时候可以通过增加ENGINE关键字设置新建表的存储引擎：

Create table aimme ( i bjgint(20) NOT NULL AUTO_INCREMENT, PRIMARY KEY(i))engine = MYISAM DEFAULT CHARSET =GBK ;

也可使用alter table语句，将一个已经存在的表修改成其他的存储引擎。

Alter table aimme engine=innodb;

Mysql>show create table aimme \g;

Mysql常用存储引擎对比：

MYISAM是mysql5.5以前的默认存储引擎，不支持十五，也不支持外键，其优势是访问速度快，对事务完整性没有要求或者以select insert 为主的应用基本上都可以使用这个殷勤来创建表。

每个，myisam 在磁盘上存储成三个文件，其文件名和表名都相同，但扩展名分别是

.frm  (存储表的定义)

MYD(MYData,存储数据)；

.MYI(MYIndex ， 存储索引)

数据文件和索引文件可以放置在不同的目录，平均分布IO，获得更快的速度。要指定数据文件和索引文件的路径，需要在创建标的时候通过 DATA DIRECTORY 和 INDEX DIRECTORY 语句指定，文件路径需要的是绝对路径，并具有访问权限

MYISAM类型的表可能会被损坏，原因是多种多样的，损坏后的表可能不能被访问，会提示需要修复或返回错误的结果。MYISAM类型的表修复步骤会在后面详细写。

MySAM的表还支持3中不同的存储格式，分别是：

1. 静态（固定长度）表；--默认的存储格式，静态表中的字段都是非变长字段，这样每个记录都是固定长度的，这种存储方式的有点事存储非常迅速，容易缓存，出现故障易恢复；缺点是占用的空间通常比动态表多。静态表的数据存储是会按照列的宽度定义补足空格，这些空格在返回给应用之前就已经去掉。
2. 动态表—包含变长字段，纪录长度不是固定不变的，这样的存储优点是占用的空间相对较少，但是频繁的更新和删除记录会产生碎片，需要执行OPTIMIZE TABLE 或myisamchk-r 命令来改善性能，并且在出现故障时回复相对比较困难。

压缩表—由myisampack 工具创建，占据非常小的磁盘空间。

InnoDB

存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比myisam存储引擎，innoDB写的处理效率差一点，并且会占用更多的磁盘空间仪表刘数据和索引。

特点：

1. 自动增长序列：可手工插入，但是插入的值如果是空或者0，则实际存储的是自动增长后的值

Create table aimme( 
I smallint not null auto_increment,
Name varchar(20),primary key(i)
)engine = innodb;
Insert into aimme values(1,’1’),(0,’2’),(null,’3’);

可以通过“alter table aimme AUTO_INCREMENT=N” 语句强制设置自动增长序列的初始值，但是该强制的默认值是保留在内存中的，如果该值在使用之前数据库重新启动，那么这个强制的默认值就会丢失，就需要在数据库启动以后重新设置。

可以使用LAST_INSERT_ID() 查询当前线程最后插入记录使用的值。如果一次插入多条记录，那么返回的是第一条记录使用的自动增长值。

Select LAST_INSERT_ID();

对于innoDB表，自动增长序列必须是索引，如果是组合索引，也必须是组合索引的第一列，但是对于myisam表，自动增长序列可以使组合索引的其他列。

Create table aimme( 
D1 smallint not null auto_increment,
D2 smallint not null ,

Name varchar(20),primary key(i),
Index(d2,d2)
)engine = innodb;
Insert into aimme (d2,name) values(1,’1’),(2,’2’),(4,’3’),(3,’3’), (1,’1’),(2,’2’),(4,’3’),(3,’3’), (1,’1’),(2,’2’),(4,’3’),(3,’3’);
Selelct *feom aimme;

1. 外键约束

Mysql 支持外键约束的存储引擎只有innoDB，在创建外键的时候，要求父表必须有对应的索引，字表在创建外键的时候也会自动的创建对应的索引

下例country是父表-country_id 是主键索引，city是子表，country_id是外键，对应与country表的主键country_id;

Create table country(
Country_id smallint unsigned not null auto_increment,
Country varchar(50) not null,
Last_update TIMESTAM NOT NULL DEFAULT CURRENT_TIMSTAMP OM UPDATE CURRENT_TIMSTAMP,PRIMARY KEY(Coutry_id)
)engine – InnoDB default charset=utf-8;
Create table city(
City_id smallint unsigned not null auto_increment,
Country_id smallint unsigned not null,
City  varchar(50) not null,
Last_update TIMESTAM NOT NULL DEFAULT CURRENT_TIMSTAMP OM UPDATE CURRENT_TIMSTAMP,PRIMARY KEY(City_id)
Constraint ‘fk_city_country’ foreign key(country_id) references country(country_id) 
on delete restrict  
on update cascade
)engine – InnoDB default charset=utf-8;

在创建索引时，可以指定再删除、更新父表时，对子表进行相应的操作。

操作包括以下三种：

1. NO ACTION 和RESTRICT 相同，是指限制在子表有关联记录的情况下父表不能更新；
2. CASCADE 表示父表在更新或删除时，更新或者删除子表对应记录；
3. SET NULL表示父表在更新或删除的时候，子表的对应字段被SET NULL.选择后两种方式的时候需谨慎，可能会因为错误的操作导致数据的丢失

上例中 on delete restrict  on update cascade 表示子表有关联数据，主表不允许删除数据，主表更新时，子表更新对应记录。