[日期:2017-09-25] | [字体:大 中 小] |
和大部分的数据库不同,MySQL中有一个存储引擎的概念,用户可以根据数据存储的需求来选择不同的存储引擎。本次博客就来介绍一下MySQL中的存储引擎。MySQL版本 5.7.19。
MySQL的存储引擎可以看做是插件式的,用户可以根据自己的需求来选择不同的存储引擎,比如是否支持事务,如何去索引数据等。5.5之前默认使用的存储引擎是MyISAM,5.5之后改为了InnoDB。
查看当前数据库的默认存储引擎,默认是InnoDB:
- mysql> show variables like '%storage_engine%';
- +----------------------------------+--------+
- | Variable_name | Value |
- +----------------------------------+--------+
- | default_storage_engine | InnoDB |
- | default_tmp_storage_engine | InnoDB |
- | disabled_storage_engines | |
- | internal_tmp_disk_storage_engine | InnoDB |
- +----------------------------------+--------+
- 4 rows in set, 1 warning (0.00 sec)
查看当前数据库支持的存储引擎:
- mysql> show engines;
- +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
- | Engine | Support | Comment | Transactions | XA | Savepoints |
- +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
- | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
- | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
- | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
- | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
- | MyISAM | YES | MyISAM storage engine | NO | NO | NO |
- | CSV | YES | CSV storage engine | NO | NO | NO |
- | ARCHIVE | YES | Archive storage engine | NO | NO | NO |
- | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
- | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
- +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
- 9 rows in set (0.00 sec)
在创建表的时候,可以指定存储引擎,如果不指定就为默认的:
- mysql> create table t1(id int,name varchar(20)) engine=MyISAM;
- Query OK, 0 rows affected (0.01 sec)
- mysql> show create table t1 \G;
- *************************** 1. row ***************************
- Table: t1
- Create Table: CREATE TABLE `t1` (
- `id` int(11) DEFAULT NULL,
- `name` varchar(20) DEFAULT NULL
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8
- 1 row in set (0.00 sec)
可以使用ALTER TABLE语句修改引擎:
- mysql> alter table t1 engine=innodb;
- Query OK, 0 rows affected (0.07 sec)
- Records: 0 Duplicates: 0 Warnings: 0
- mysql> show create table t1 \G;
- *************************** 1. row ***************************
- Table: t1
- Create Table: CREATE TABLE `t1` (
- `id` int(11) DEFAULT NULL,
- `name` varchar(20) DEFAULT NULL
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8
- 1 row in set (0.00 sec)
一、MyISAM
MyISAM在5.5之前是默认的存储引擎,优缺点如下:
- 优点:
- 1.访问的速度快,对事务完整性没有要求或者以SELECT会在INSERT为主的应用可以使用此引擎;
- 缺点:
- 1.不支持事务,不支持外键;
- 2.MyISAM类型的表可能会损坏,损坏后表将不能访问,可以用工具去检查(CHECK TABLE)和修复(REPAIR TABLE)。。
- 每个MyISAM在磁盘上存储称3个文件,其文件名都和表名相同,但是扩展名如下:
每个MyISAM在磁盘上存储称3个文件,其文件名都和表名相同,但是扩展名如下:
- .frm(存储表定于)
- .MYD(存储数据)
- .MYI(存储索引)
MyISAM支持3种不同的存储格式:
- 固定长度表:每个字段都是固定的长度,容易存储和缓存,缺点是占用空间,不足字段长度会补足空格,出现故障容易恢复;
- 动态表:包含可变长字段,占用空间少,但是频繁的更新和删除或产生碎片,可以使用OPTIMIZE TABLE改善性能,出现故障不容易恢复;
- 压缩表:是用myisampack创建,占用的空间非常小。
二、InnoDB
InnoDB在5.5之后就是默认的存储引擎了,优缺点如下:
- 优点:
- 具有提交、回滚和崩溃恢复的事务能力;
- 缺点:
- 处理效率差一些,会占用更多的磁盘空间以保留数据和索引。
特点:
- .支持自增长的列,比如id自己增加
- .外键约束,MySQL支持外键约束的只有Innodb,在创建外键的时候,要求父表必须有对应的索引,子表在创建外键的时候也会自动创建对应的索引。
InnoDB存储表和索引有以下两种方式:
- .使用共享表空间,这种方式创建的表结构保存在.frm文件中,数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中,可以是多个文件
- .使用多表空间存储,这种方式创建的表的表结构依然保存在.frm文件中,但是每个表的数据和索引单独保存在.ibd中,如果是个分区表,则每个分区对应单独的.ibd文件,文件名为"表名+分区名",可以在创建分区的时候指定每个分区的数据位置,以此来讲表的IO均匀分布在多个磁盘上。
注意:即便在多表空间的存储方式下,共享空间仍然是必须的,InnoDB把内部数据词典和在线重做日志放在这个文件里。
3.MEMORY
MEMORY存储引擎使用存在于内存中的内容来创建表的,优缺点如下:
- 优点:访问速度快,因为它的数据是放在内存中的,并且默认使用HASH索引的;
- 缺点:一旦服务关闭,表中的数据就会丢失。
创建MEMORY的表:
- mysql> create table t2 engine=memory select sid,sname from student where sid <6;
- Query OK, 5 rows affected (0.01 sec)
- Records: 5 Duplicates: 0 Warnings: 0
- mysql> select * from t2;
- +-----+--------+
- | sid | sname |
- +-----+--------+
- | 1 | 李杰 |
- | 2 | 钢蛋 |
- | 3 | 张三 |
- | 4 | 张一 |
- | 5 | 张二 |
- +-----+--------+
- 5 rows in set (0.00 sec)
- mysql> show table status like 't2' \G;
- *************************** 1. row ***************************
- Name: t2
- Engine: MEMORY
- Version: 10
- Row_format: Fixed
- Rows: 5
- Avg_row_length: 101
- Data_length: 126984
- Max_data_length: 16293219
- Index_length: 0
- Data_free: 0
- Auto_increment: NULL
- Create_time: 2017-09-24 23:57:59
- Update_time: NULL
- Check_time: NULL
- Collation: utf8_general_ci
- Checksum: NULL
- Create_options:
- Comment:
- 1 row in set (0.00 sec)
在给MEMORY创建索引的时候可以指定是HASH索引还是BTREE索引:
- mysql> create index mem_hash using btree on t2(sname);
- Query OK, 5 rows affected (0.01 sec)
- Records: 5 Duplicates: 0 Warnings: 0
- mysql> show index from t2 \G;
- *************************** 1. row ***************************
- Table: t2
- Non_unique: 1
- Key_name: mem_hash
- Seq_in_index: 1
- Column_name: sname
- Collation: A
- Cardinality: NULL
- Sub_part: NULL
- Packed: NULL
- Null:
- Index_type: BTREE
- Comment:
- Index_comment:
- 1 row in set (0.00 sec)
如果要释放表,直接删除表的内容是用DELETE FROM或者TRUNCATE TABLE,删除表使用DROP TABLE即可。
4.MERGE
MERGE存储引擎是一组MyISAM表的组合,这些MyISAM的表必须结构是完全的相同的,MERGE表在磁盘上保留两个文件,文件以表的名字开始,一个.frm文件存储表定义,另一个是.MRG文件包含了组合表的信息。
因为MERGE表时多个表组成的,对于MERGE表的插入操作,要通过设置INSERT_METHOD来定义:
- INSERT_METHOD=FIRST #当在MERGE中插入的时候,只插在第一个表上;
- INSERT_METHOD=LAST #当在MERGE中插入的时候,只插在最后一个表上;
- INSERT_METHOD=NO #此MERGE表不允许被插入。
当DROP MERGE表的时候,不会对组合表中的表有任何影响。
比如来创建一个MERGE表:
(1)创建m1,m2,m3表且m3表时前面两个表的MERGE表:
- mysql> create table m1(id int,name varchar(20)) engine=myisam;
- Query OK, 0 rows affected (0.01 sec)
- mysql> create table m2(id int,name varchar(20)) engine=myisam;
- Query OK, 0 rows affected (0.01 sec)
- mysql> create table m3(id int,name varchar(20)) engine=merge union(m1,m2) insert_method=last;
- Query OK, 0 rows affected (0.01 sec)
(2)在m1和m2中插入数据,并查看:
- mysql> insert into m1(id,name) values(1,'frank');
- Query OK, 1 row affected (0.00 sec)
- mysql> insert into m2(id,name) values(1,'rose');
- Query OK, 1 row affected (0.00 sec)
- mysql> select * from m1;
- +------+-------+
- | id | name |
- +------+-------+
- | 1 | frank |
- +------+-------+
- 1 row in set (0.00 sec)
- mysql> select * from m2;
- +------+------+
- | id | name |
- +------+------+
- | 1 | rose |
- +------+------+
- 1 row in set (0.00 sec)
- mysql> select * from m3;
- +------+-------+
- | id | name |
- +------+-------+
- | 1 | frank |
- | 1 | rose |
- +------+-------+
- 2 rows in set (0.00 sec)
当在m1和m2上插入数据的时候,merge表中也会同步增加行,下面在m3中插入行:
- mysql> insert into m3(id,name) values(2,'alex');
- Query OK, 1 row affected (0.00 sec)
- mysql> select * from m1;
- +------+-------+
- | id | name |
- +------+-------+
- | 1 | frank |
- +------+-------+
- 1 row in set (0.00 sec)
- mysql> select * from m2;
- +------+------+
- | id | name |
- +------+------+
- | 1 | rose |
- | 2 | alex |
- +------+------+
- 2 rows in set (0.00 sec)
- mysql> select * from m3;
- +------+-------+
- | id | name |
- +------+-------+
- | 1 | frank |
- | 1 | rose |
- | 2 | alex |
- +------+-------+
- 3 rows in set (0.00 sec)
m3中成功被添加,m1中并没有被添加新行,因为这里的insert_method为last,所以只有最后的表才会被更新。
除了MySQL自带的存储引擎,还有一些第三方的存储引擎,比如Infobright、TokuDB等。
选择建议:
《深入浅出MySQL》PDF文字版(全)PDF 下载 http://www.linuxidc.com/Linux/2016-05/130922.htm
来源: http://www.linuxidc.com/Linux/2017-09/147054.htm