mysql默认缓存 mysql5.7默认存储引擎

转载

编程梦想编织者 2023-10-05 17:42:18

文章标签 mysql默认缓存 mysql 数据库存储引擎数据 文章分类 MySQL 数据库

1.概述
插件式存储引擎时MySQL数据库最重要的特性之一，用户可以根据自己的需求选择如何存储索引数据、是否使用事务等。
MySQL5.7支持的存储引擎包括InnoDB、MyISAM、MEMORY、CSV、ARCHIVE、MERGE、NDB等，其中InnoDB和NDB提供事务安全表，其他的存储引擎都是非实物安全表。
建立新表时，如果不指定存储引擎，则会使用默认存储引擎。MySQL在5.5之前的默认存储引擎时MyISAM，5.5之后改为了InnoDB。
（1）查看默认存储引擎

mysql> show variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)

（2）查看支持哪些存储引擎
展示部分支持引擎：

mysql> show engines \G
# 展示部分支持引擎
*************************** 1. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************

（3）指定创建表的引擎

create table test(
	id int primary key Auto_increment, 
	name varchar
)engine=InnoDB default charset=utf8;

（4）更改表的引擎

alter table test engine = myisam;

修改表的存储引擎会锁表并复制数据，对于线上环境的表进行这个操作非常危险，一般不建议。
2.常用存储引擎的对比

mysql默认缓存 mysql5.7默认存储引擎_mysql

2.1 MyISAM

MyISAM是MySQL5.5之前版本默认的存储引擎。MyISAM既不支持事务，也不支持外键。在一些对事务完整性没有要求，以及select、insert为主要的应用中，相对InnoDB的访问速度，有明显优势。

（1）每个MyISAM在磁盘上存储成3个文件，文件名都和表明相同，但扩展名分别如下：

.frm（存储表定义）
.MYD（MYData，存储数据）
.MYI（MYIndex，存储索引）

数据文件和索引文件可以放在不同的目录，平均分布IO。
（2）MyISAM类型的表可能会损坏，因此提供了修复工具，可以用CHECK TABLE语句来检查MyISAM表的健康，用REPAIR TABLE语句来修复。
（3）MyISAM的表还支持3种不同的存储格式，分别为：

静态（固定长度）表
动态表
压缩表

静态表是默认的存储格式，每个记录都是固定长度的。这种存储方式的特点是占用空间相对动态表多，但是存储非常迅速，容易缓存，而且出现故障容易修复。此外，如果保存的内容后面带有空格，在返回结果的时候会被去除掉。
动态表的记录不是固定长度的，占用的空间相对较少，但是频繁的更新和删除会产生碎片，需要定期执行OPTIMIZE TABLE语句或myisamchk -r来改善性能，而且在初心故障时恢复相对比较困难。
压缩表由myisampack工具创建，占用非常小的磁盘空间。每条记录都是单独压缩，所以只有非常小的访问开支。
2.2 InnoDB
InnoDB作为MySQL5.5之后的默认存储引擎，提供了事务安全保障，同时提供了更小的锁粒度和更强的并发能力，拥有自己独立的缓存和日志。
相比MyISAM存储引擎，InnoDB会占用更多的磁盘空间以保留数据和索引。
（1）自动增长列
InnoDB表的自动增长列可以手工输入，但是插入的值如果是空，则实际插入的将是自动增长后的值。也可以通过‘alter table table_name auto_increment=n’进行更改，但是在MySQL8.0之前，这个值存在内存中，如果出先MySQL服务重启，可能会导致冲突。
（2）外键约束
MySQL支持外键的常用存储引擎只有InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建的时候也会自动创建对应的索引。
对于InnoDB类型的表，外键的信息可以通过使用show create table 或者 show table status命令进行显示。
（3）主键和索引
不同于其他的存储引擎，InnoDB的数据文件本身就是以聚簇索引的形式保存的，这个聚簇索引也被称为主索引，并且也是InnoDB表的主键，InnoDB表的每行数据都保存在主索引的叶子节点上。因此，所有的InnoDB表都必须包含索引，如果建表的时候，没有显式指定主键，那么InnoDB存储引擎会自动创建一个长度为6个字节的long类型隐藏字段作为主键。

mysql默认缓存 mysql5.7默认存储引擎_数据_02

一般来说，主键应该按照下列原则来选择：

a. 唯一性和非空约束

b. 优先考虑查询时最常使用的字段或自增字段

c. 字段值基本不会被修改

d. 使用尽可能短的字段

在InnoDB表上，除了主键之外的其他索引都叫做辅助索引或二级索引，二级索引指向主索引，并通过主索引获取最终数据。因此，主键是否合理，会对所有索引的效率产生影响。

（4）存储方式

InnoDB存储表和索引由一下两种方式：

a.共享表空间存储：表结构保存在.frm文件中，数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中，可以时多个文件

b.多表空间存储：表结构保存在.frm文件中，但是每个表的数据和索引单独保存在.ibd中。如果是分区表，则每个分区对于单独的.ibd文件，文件名为“表名+分区名”

使用共享表空间时，随着数据的不断增长，表空间的管理维护会变的越来越困难，因此一般都建议使用多表空间。

使用哪种表空间是由innodb_file_per_table这个参数决定的，如果这个参数为ON:多表空间，存储的表名为(表名.ibd)，如果参数为OFF:共享表空间(系统的共享表空间)，存储的表名为(ibdataX(X为数字)))

命令：show variables like 'innodb_file_per_table'; 查看mysql数据库的存放位置: show global variables like "%datadir%";

接下来我们创建一个表来看一下：

create table myinnodb(id int,c1 varchar(100)) engine='innodb';

看一下文件系统是如何存储的，进入到数据库存放的位置，ls -lh myinnodb* 可以看到有myinnodb.frm和myinnodb.ibd两个文件，frm文件时记录表结构的，ibd就是innodb表实际存储的位置；
接着把innodb_file_per_table参数设置为off,命令为set global innodb_file_per_table=off;show variables like 'innodb_file_per_table'; 用这个命令检查是否关闭了
再创建一个表：

create  table myinnodb_g(id int,c1 varchar(100)) engine='innodb';

查看存储的位置，可以看到只有一个myinnodb_g.frm的文件，不存在ibd文件，也就是说它的数据存储在系统共享表的空间存储在ibdataX中；
InnoDB详细存储结构可以参考：
2.3 MEMORY
MEMORY存储引起使用存在于内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件，格式是.frm。MEMORY类型的表访问非常快，因为它的数据是放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。
每个MEMORY表中可以放置的数据量的大小，受到max_heap_table_size系统变量的约束，这个系统变量的初始值是16MB。此外，在定义MEMORY表的时候，可以通过MAX_ROWS子句来指定表的最大行数。
2.4 MERGE
MERGE存储引擎也被称为MRG_MyISAM，是一组MyISAM表的组合。这些MyISAM表的结构必须完全相同，MERGE表本身并没有数据。
MERGE表在磁盘上保留两个文件，文件名以表的名字开始，一个.frm文件存储表定义，另一个.MRG文件包含组合表的信息，包括MERGE表由哪些表组成，插入新的数据时的依据。
3. 如何选择合适的存储引擎
（1）MyISAM：MySQL5.5之前默认的存储引擎。如果应用是以读操作和插入操作为主，只有少数的更新和删除操作，并且对事务的完整性没有要求、没有并发写的要求，可以考虑选择MyISAM。OLTP场景一般建议不要使用MyISAM。
（2）InnoDB：MySQL5.5之后默认的存储引擎，用于事务处理应用程序，支持外键，对于大多数的应用系统，InnoDB都是合适的选择。如果应用对事务的完整性由比较高的要求，在并发条件下要求数据的一致性，数据操作除了查询和插入之外，还包括很多的更新、删除操作，则优先选择InnoDB。
（3）MEMORY：将所有的数据保存在RAM中，可快速的提供访问速度。但对表的大小有限制，以及数据库服务异常终止后无法恢复。
（4）MERGE：用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对对象引用他们。MERGE表的优点在于可以突破对单个MyISAM表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效地盖上MERGE表的访问效率，这对于数据仓储等VLDB环境十分合适。