Mysql各种存储引擎特性

x33g5p2x  于2021-03-14 发布在 Mysql  
字(5.1k)|赞(0)|评价(0)|浏览(253)

1. 常见存储引擎

面重点介绍几种常用的存储引擎, 并对比各个存储引擎之间的区别, 如下表所示 :

特点InnoDBMyISAMMEMORYMERGENDB
存储限制64TB没有
事务安全==支持==
锁机制==行锁(适合高并发)====表锁==表锁表锁行锁
B树索引支持支持支持支持支持
哈希索引支持
全文索引支持(5.6版本之后)支持
集群索引支持
数据索引支持支持支持
索引缓存支持支持支持支持支持
数据可压缩支持
空间使用N/A
内存使用中等
批量插入速度
支持外键==支持==

下面我们将重点介绍最长使用的两种存储引擎: InnoDB、MyISAM , 另外两种 MEMORY、MERGE , 了解即可。

2. InnoDB

InnoDB存储引擎是Mysql的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎,InnoDB写的处理效率差一些,并且会占用更多的磁盘空间以保留数据和索引。

InnoDB存储引擎不同于其他存储引擎的特点 :

2.1事务控制

mysql> create table goods_innodb(
    ->     id int NOT NULL AUTO_INCREMENT,
    ->     name varchar(20) NOT NULL,
    ->     primary key(id)
    -> )ENGINE=innodb DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into goods_innodb(id,name)values(null,'iphone 20');
Query OK, 1 row affected (0.00 sec)

mysql> select * from goods_innodb;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | iphone 20 |
+----+-----------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

测试发现在InnoDB中是存在事务的

2.2 外键约束

MySQL支持外键的存储引擎只有InnoDB , 在创建外键的时候, 要求父表必须有对应的索引 , 子表在创建外键的时候, 也会自动的创建对应的索引。

​ 下面两张表中 , country_innodb是父表 , country_id为主键索引,city_innodb表是子表,country_id字段为外键,对应于country_innodb表的主键country_id 。

create table country_innodb(
	country_id int NOT NULL AUTO_INCREMENT,
    country_name varchar(100) NOT NULL,
    primary key(country_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


create table city_innodb(
	city_id int NOT NULL AUTO_INCREMENT,
    city_name varchar(50) NOT NULL,
    country_id int NOT NULL,
    primary key(city_id),
    key idx_fk_country_id(country_id),
    CONSTRAINT `fk_city_country` FOREIGN KEY(country_id) REFERENCES country_innodb(country_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into country_innodb values(null,'China'),(null,'America'),(null,'Japan');
insert into city_innodb values(null,'Xian',1),(null,'NewYork',2),(null,'BeiJing',1);

在创建索引时, 可以指定在删除、更新父表时,对子表进行的相应操作,包括 RESTRICT、CASCADE、SET NULL 和 NO ACTION。

RESTRICT和NO ACTION相同, 是指限制在子表有关联记录的情况下, 父表不能更新;

CASCADE表示父表在更新或者删除时,更新或者删除子表对应的记录;

SET NULL 则表示父表在更新或者删除的时候,子表的对应字段被SET NULL 。

针对上面创建的两个表, 子表的外键指定是ON DELETE RESTRICT ON UPDATE CASCADE 方式的, 那么在主表删除记录的时候, 如果子表有对应记录, 则不允许删除, 主表在更新记录的时候, 如果子表有对应记录, 则子表对应更新 。

表中数据如下图所示 :

mysql> select * from country_innodb;
+------------+--------------+
| country_id | country_name |
+------------+--------------+
|          1 | China        |
|          2 | America      |
|          3 | Japan        |
+------------+--------------+
3 rows in set (0.00 sec)

mysql> select * from city_innodb;
+---------+-----------+------------+
| city_id | city_name | country_id |
+---------+-----------+------------+
|       1 | Xian      |          1 |
|       2 | NewYork   |          2 |
|       3 | BeiJing   |          1 |
+---------+-----------+------------+
3 rows in set (0.00 sec)

#删除主表数据时,如果关联表中有关联数据,则不能被删除
mysql> delete from  country_innodb where country_id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`demo_01`.`city_innodb`, CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country_innodb` (`country_id`) ON DELETE RESTRICT ON UPDATE CASCADE)

#更新主表数据时,如果关联表中有关联数据,则数据也会被更新
mysql> update country_innodb set country_id =10 where country_id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from city_innodb;
+---------+-----------+------------+
| city_id | city_name | country_id |
+---------+-----------+------------+
|       1 | Xian      |         10 |
|       2 | NewYork   |          2 |
|       3 | BeiJing   |         10 |
+---------+-----------+------------+
3 rows in set (0.00 sec)

2.3 存储位置

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

①. 使用共享表空间存储, 这种方式创建的表的表结构保存在.frm文件中, 数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定义的表空间中,可以是多个文件。

②. 使用多表空间存储, 这种方式创建的表的表结构仍然存在 .frm 文件中,但是每个表的数据和索引单独保存在 .ibd 中。

默认存储位置在Mysql/data/’数据库‘ 下

➜  mysql-8.0.14-macos10.14-x86_64 cd data 
➜  data ls
#innodb_temp       ca-key.pem         ib_logfile0        mysqld.local.err   server-key.pem
auto.cnf           ca.pem             ib_logfile1        mysqld.local.pid   sys
binlog.000001      client-cert.pem    ibdata1            performance_schema undo_001
binlog.000002      client-key.pem     ibtmp1             private_key.pem    undo_002
binlog.000003      demo_01            mysql              public_key.pem
binlog.index       ib_buffer_pool     mysql.ibd          server-cert.pem
➜  data cd demo_01 
➜  demo_01 ls
city.ibd           city_innodb.ibd    country.ibd        country_innodb.ibd goods_innodb.ibd

3. MyISAM

​ MyISAM 不支持事务、也不支持外键,其优势是访问的速度快,对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表 。有以下两个比较重要的特点:

3.1 不支持事务


create table goods_myisam(
	id int NOT NULL AUTO_INCREMENT,
	name varchar(20) NOT NULL,
    primary key(id)
)ENGINE=myisam DEFAULT CHARSET=utf8;

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into goods_myisam values(null,'diannao');
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from goods_myisam;
+----+---------+
| id | name    |
+----+---------+
|  1 | diannao |
+----+---------+
1 row in set (0.00 sec)

通过测试,我们发现,在MyISAM存储引擎中,是没有事务控制的 ;

3.2 文件存储方式

每个MyISAM在磁盘上存储成3个文件,其文件名都和表名相同,但拓展名分别是 :

.frm (存储表定义);

.MYD(MYData , 存储数据);

.MYI(MYIndex , 存储索引);

➜  demo_01 ls
city.ibd             country.ibd          goods_innodb.ibd     goods_myisam.MYI
city_innodb.ibd      country_innodb.ibd   goods_myisam.MYD     goods_myisam_343.sdi

4. MEMORY

Memory存储引擎将表的数据存放在内存中。每个MEMORY表实际对应一个磁盘文件,格式是.frm ,该文件中只存储表的结构,而其数据文件,都是存储在内存中,这样有利于数据的快速处理,提高整个表的效率。MEMORY 类型的表访问非常地快,因为他的数据是存放在内存中的,并且默认使用HASH索引 , 但是服务一旦关闭,表中的数据就会丢失。

5. c

​ MERGE存储引擎是一组MyISAM表的组合,这些MyISAM表必须结构完全相同,MERGE表本身并没有存储数据,对MERGE类型的表可以进行查询、更新、删除操作,这些操作实际上是对内部的MyISAM表进行的。

​ 对于MERGE类型表的插入操作,是通过INSERT_METHOD子句定义插入的表,可以有3个不同的值,使用FIRST 或 LAST 值使得插入操作被相应地作用在第一或者最后一个表上,不定义这个子句或者定义为NO,表示不能对这个MERGE表执行插入操作。

​ 可以对MERGE表进行DROP操作,但是这个操作只是删除MERGE表的定义,对内部的表是没有任何影响的。

相关文章