MySQL表类型和存储引擎
一、基本介绍
从事务安全性的角度,可以把存储引擎分为两大类:
事务安全: BDB和innodb;
事务非安全性: myisam 和 memory
二、存储引擎的比较图
看你的mysql当前默认的存储引擎:
mysql> show variables like '%storage_engine%';
select version();
在配置文件my.cnf中的 [mysqld] 下面加入 default-storage-engine = 存储引擎
三、各个存储引擎的特点说明
MyISAM不支持事务、也不支持外键,但其访问速度快,对事务完整性没有要求或以select,insert为主【使用myisam】;产生碎片处理. optimize table 表名;
MyISAM的表可能会损坏,check table 表名 检查myisam表的健康;
Repair table 表名 修复一个损坏的myisam表。表损坏可能导致数据库异常重新启动,需尽快修复并尽可能确认损坏原因;
每个MyISAM在磁盘上存储成3个文件,其文件名都和表名相同,但扩展名分别是:
.frm(存储表定义);
.MYD(MYData,存储数据);
.MYI(MYIndex,存储索引);
数据文件和索引文件可以防止在不同的目录,平局分布IO,获得更快的速度。
数据文件和索引文件可以在创建表结构的时候通过 DATA DIRECTORY 和 INDEX DIRECTORY 来指定,必须是绝对路径,并具有访问权限,例如:
CREATE TABLE test (
id int(11),
title varchar(20)
) ENGINE = MyISAM DATA DIRECTORY = '/data/db1' INDEX DIRECTORY = '/data/db2';
举例说明:
InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全,支持外键,(delete,update)操作;但是比起MyISAM存储引擎,InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引;
Innodb通过设置 Innodb_file_per_table 参数,设置innodb为独立表空间模式,这样每个数据库的每个表都会生成一个独立的ibd文件,用于存储表的数据和索引,一定程度上减轻innodb表的空间回收问题。另外,在删除大量数据后,innodb可通过alter table xxx engine=innodb,但不修改引擎的方式来回收不用的空间。
1.自动增长列
Alter table *** auto_increment = n; 强制设置自动增长列的初始值,默认从1开始;注意,强制设置的默认值保留在内存中,重启后丢失,需重新设置;
LAST_INSERT_ID() 查询当前线程最后插入记录使用的值;如果一次插入多条记录,返回的是第一条记录使用的自增长值;
2.外键约束(MySQL中支持外键的只有InnoDB引擎)
创建外键时,要求父表必须有对应的索引,子表在创建外键时也会自动创建对应的索引;
创建索引时,可以指定在删除、更新父表时,对子表进行的相应操作,包括 RESTRICT、CASCADE、SET NULL 和 NO ACTION。
其中RESTRICT(默认)和NO ACTION相同,指限制在子表有关联记录的情况下父表不能更新;
CASCADE表示父表在更新或者删除时,更新或者删除子表对应记录;
SET NULL表示父表在更新或者删除的时候,子表的对应字段被SET NULL;
最后两种方式需谨慎,可能会因为错误的操作导致数据丢失;
某个表被其他表创建了外键参照,那么该表的对应索引或主键禁止被删除;
Set foreign key checks=0;暂时关闭外键检查
创建外键语句:
Constraint ‘外键名’ foreign key (子表字段) references 父表名(字段) ON delete restrict ON update cascade; // 主表删除记录时,子表有记录则不允许删除;主表更新记录时,子表有对应记录,则子表更新;
Alter table add foreign key (字段) references 主表(列);
3.存储方式
两种方式存储表和索引
使用共享表空间存储,创建的表的表结构保存在.frm文件中,数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中,可以是多个文件。
使用多表空间存储,表结构仍保存在.frm文件中,但每个表的数据和索引单独保存在.Idb中。如果是分区表,则每个分区对应单独的.Idb文件,文件名是“表名+分区名”,可以在创建分区的时候指定每个分区的数据文件的位置,来将表的IO均匀分布在多个磁盘上;
MEMORY存储引擎使用存在内存中的内容来创建表。 每个MEMORY表只实际对应一个磁盘文件.frm。MEMORY类型的表访问非常得快,因为它的数据是放在内存(RAM)中的,并且默认使用HASH索引,大小受max_heap_table_size约束。但是一旦服务关闭,表中的数据就会丢失掉, 表的结构还在;
特点是: 数据都在内存,访问速度快, 但是你的数据库一旦重启,数据丢失了.- 所有我们的memory 存储引擎适合存放 频繁变化的小数据,比如用户的在线状态.
举例说明:
MERGE是一组MyISAM表的组合,这些MyISAM表必须结构完全相同,MERGE表本身并没有数据。INSERT_METHOD子句定义插入的表,有3个不同的值,FIRST或LAST值使得merge插入操作被相应地作用在第一或最后一个myisam表上,不定义这个子句或者定义为NO,表示不能对这个MERGE表执行插入操作
Create table payment_all(
Country_id smallint,
Amout decimal(15,2),
Payment_date datetime,
)engine=merge union=(payment_2017,payment_2018) INSERT_METHOD=LAST;
第三方存储引擎,如高写性高压缩的TokuDB,使用日志数据,历史数据等…
四、如何选择适当的存储引擎
五、修改表的存储引擎指令