阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL之存储引擎(表类型)的选择

207次阅读
没有评论

共计 6451 个字符,预计需要花费 17 分钟才能阅读完成。

和大部分的数据库不同,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 之后就是默认的存储引擎了,优缺点如下:

优点:具有提交、回滚和崩溃恢复的事务能力;缺点:处理效率差一些,会占用更多的磁盘空间以保留数据和索引。

特点:

1. 支持自增长的列,比如 id 自己增加
2. 外键约束,MySQL 支持外键约束的只有 Innodb,在创建外键的时候,要求父表必须有对应的索引,子表在创建外键的时候也会自动创建对应的索引。

InnoDB 存储表和索引有以下两种方式:

1. 使用共享表空间,这种方式创建的表结构保存在.frm 文件中,数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path 定义的表空间中,可以是多个文件
2. 使用多表空间存储,这种方式创建的表的表结构依然保存在.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 之存储引擎(表类型)的选择

 选择建议:

1.MyISAM:如果应用是以读操作或者和插入操作为主,只有很少的更新和删除操作,并对事务的完整性和并发要求不是很高,可以选择,比如 Web 和数据仓库;

2.InnoDB:用于事务处理的应用,支持外键。对于类型计费系统或者财务系统等对数据准确性较高的可以使用 InnoDB。
 
参考数据:《深入浅出 MySQL》推荐~

《深入浅出 MySQL》PDF 文字版(全)PDF 下载  http://www.linuxidc.com/Linux/2016-05/130922.htm

本文永久更新链接地址:http://www.linuxidc.com/Linux/2017-09/147054.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计6451字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中