1. MySQL存储引擎
存储引擎就是存储数据,建立索引,更新查询数据等等技术的实现方式 。存储引擎是基于表的,而不是基于库的。所以存储引擎也可被称为表类型。Oracle,SqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎,可以根据需要使用相应引擎,或者编写存储引擎。MySQL5.0支持的存储引擎包含 : InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等,其中InnoDB和BDB提供事务安全表,其他存储引擎是非事务安全表。创建新表时如果不指定存储引擎,那么系统就会使用默认的存储引擎,MySQL5.5之前的默认存储引擎是MyISAM,5.5之后就改为了InnoDB。
1.1 查看数据库支持的存储引擎
show engines;
1.2 查看数据库默认的存储引擎
show variables like '%storage_engine%' ;
1.3 几种常用存储引擎对比
1.3.1 InnoDB
InnoDB存储引擎是Mysql的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎,InnoDB写的处理效率差一些,并且会占用更多的磁盘空间以保留数据和索引。
InnoDB存储引擎不同于其他存储引擎的特点 :
事务控制;
外键约束:CONSTRAINT `外键名` FOREIGN KEY(主表外键列) REFERENCES 从表(从表关联列) ON DELETE RESTRICT ON UPDATE CASCADE;
补充:在创建索引时, 可以指定在删除、更新父表时,对子表进行的相应操作,包括 RESTRICT、CASCADE、SET NULL和 NO ACTION。RESTRICT和NO ACTION相同, 是指限制在子表有关联记录的情况下,父表不能更新;CASCADE表示父表在更新或者删除时,更新或者删除子表对应的记录;SET NULL 则表示父表在更新或者删除的时候,子表的对应字段被SET NULL 。
存储方式 :InnoDB 存储表和索引有以下两种方式 : ①. 使用共享表空间存储, 这种方式创建的表的表结构保存在.frm文件中, 数据和索引保存在innodb_data_home_dir 和 innodb_data_fifile_path定义的表空间中,可以是多个文件。②. 使用多表空间存储, 这种方式创建的表的表结构仍然存在 .frm 文件中,但是每个表的数据和索引单独保存在.ibd 中。
补充:查看文件存储路径:show global variables like "%datadir%";
1.3.2 MyISAM
MyISAM 不支持事务、也不支持外键,其优势是访问的速度快,对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表 。有以下两个比较重要的特点:
不支持事务;
文件存储方式:每个MyISAM在磁盘上存储成3个文件,其文件名都和表名相同,但拓展名分别是 :.frm (存储表定义); .MYD(MYData , 存储数据); .MYI(MYIndex , 存储索引);
1.3.3 MEMORY
Memory存储引擎将表的数据存放在内存中。每个MEMORY表实际对应一个磁盘文件,格式是.frm ,该文件中只存储表的结构,而其数据文件,都是存储在内存中,这样有利于数据的快速处理,提高整个表的效率。MEMORY类型的表访问非常地快,因为他的数据是存放在内存中的,并且默认使用HASH索引 , 但是服务一旦关闭,表中的数据就会丢失。
1.3.4 MERGE
MERGE存储引擎是一组MyISAM表的组合,这些MyISAM表必须结构完全相同,MERGE表本身并没有存储数据,对MERGE类型的表可以进行查询、更新、删除操作,这些操作实际上是对内部的MyISAM表进行的。
对于MERGE类型表的插入操作,是通过INSERT_METHOD子句定义插入的表,可以有3个不同的值,使用FIRST 或LAST 值使得插入操作被相应地作用在第一或者最后一个表上,不定义这个子句或者定义为NO,表示不能对这个MERGE表执行插入操作。
create table order_1990(order_id int ,order_money double ( 10 , 2 ),order_address varchar ( 50 ),primary key (order_id))engine = myisam default charset=utf8;create table order_1991(order_id int ,order_money double ( 10 , 2 ),order_address varchar ( 50 ),primary key (order_id))engine = myisam default charset=utf8;create table order_all(order_id int ,order_money double ( 10 , 2 ),order_address varchar ( 50 ),primary key (order_id))engine = merge union = (order_1990,order_1991) INSERT_METHOD=LAST default charset=utf8;
2). 分别向两张表中插入记录
insert into order_1990 values ( 1 , 100.0 , ' 北京 ' );insert into order_1990 values ( 2 , 100.0 , ' 上海 ' );insert into order_1991 values ( 10 , 200.0 , ' 北京 ' );insert into order_1991 values ( 11 , 200.0 , ' 上海 ' );
3). 查询3张表中的数据。
order_1990中的数据 :
1.4 存储引擎的选择
在选择存储引擎时,应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统,还可以根据实际情况选择多种存储引擎进行组合。以下是几种常用的存储引擎的使用环境。