mysql存储引擎

mysql架构图

image.png

mysql存储引擎区别图

截屏2021-07-05 下午8.44.02.png

mysql中的数据用各种不同的技术存储在文件或者内存中。这些技术中的每一种都使用的不同的存储机制，索引技巧，锁定水平，并且最终提供广泛的不同的功能和能力，这种技术称之为存储引擎，mysql支持成千上万个存储引擎。其中目前应用最广泛的是InnoDB 与myISAM 两种。

存储引擎是针对表来定义的，所以是实现表管理用的。同一个数据库里不同的表可以采用不同的存储引擎。

• 查看一个表的存储引擎

mysql> SHOW CREATE TABLE 表名;
或者
mysql> SHOW TABLE STATUS FROM 数据库 WHERE name=表名 \G;

MariaDB [(none)]> SHOW TABLE STATUS\G;
*************************** 1. row ***************************
           Name: mytbl1 -------------------- 表名称
         Engine: InnoDB -------------------- 数据引擎
        Version: 10 ------------------------ 版本
     Row_format: Compact ------------------- 行格式为紧密
           Rows: 0 ------------------------- 行
 Avg_row_length: 0 ------------------------- 平均行长度
    Data_length: 29584 --------------------- 数据长度
Max_data_length: 0 ------------------------- 最大数据长度
   Index_length: 0 ------------------------- 索引长度
      Data_free: 0 ------------------------- 数据帧
 Auto_increment: NULL ---------------------- 自动递增
    Create_time: 2021-7-6 15:08:15 --------- 创建时间
    Update_time: NULL ---------------------- 更新时间
     Check_time: NULL ---------------------- 检验完整性的时间
      Collation: utf8_general_ci ----------- 字符集
       Checksum: NULL ---------------------- 检验码
 Create_options:  -------------------------- 创建时的选项
        Comment:  -------------------------- 表注释
1 row in set (0.00 sec)

目前常见的存储引擎

• myISAM (之前的存储引擎)
• InnoDB （现在99%都在用的引擎）

myISAM与InnoDB的区别

1. myISAM引擎特点

• 不支持事务
• 表级锁定
• 读写相互阻塞，写入不能读，读时不能写
• 只缓存索引
• 不支持外键约束
• 不支持聚簇索引
• 读取数据较快，占用资源少
• 不支持MVCC 高并发 (多版本并发控制机制)
• 崩溃恢复性较差
• mysql 5.5.5 前的默认数据引擎（show engines; 查看）

MyISAM 存储引擎适用场景

• 只读 （或者写较少）
• 表较小 （可以接受长时间修复操作）

MySAM 引擎文件

• tbl_name.frm 表格式定义
• tbl_name.MYD 数据文件
• tbl_name.MYI 索引文件

2. InnoDB引擎

InnoDB引擎的特点

• 行级锁
• 支持事务，适合处理大量短期事务
• 读写阻塞与事务隔离级别相关
• 可缓存数据和索引
• 支持聚簇索引
• 崩溃恢复性更好
• 支持MVCC高并发
• 从MySQL5.5后支持全文索引
• 从MySQL5.5.5开始为默认的数据引擎(看到如下图默认引擎)

image.png

InnoDB 数据存放结构图

image.png

row 为行，行存储是存放到页 （page）里。

page 为页，是数据库里的单位，可以称之为数据块,因为存储在磁盘中（通常存储在内存称之为页） ，datablock 默认大小为16kb

extent 为区，一个extent由64个page组成。extent的总大小为1M
segment 为段，由多个extent组成
tablespace 为表空间，由多个segment组成，表现为磁盘文件。

表空间存储选项

默认情况下InnoDB有一个共享表空间ibdata1，即所有数据都存放在这个表空间内,假如有100张表也是写到一起

show variables like 'innodb_data_file_path'\G;

image.png

image.png

也是有选项将共享表空间分开

• 查看当前innodb_file_per_table 的状态如果是OFF说明是关闭，ON是开启，开启既是将表空间分开存储。

> show variables like 'innodb_file_%';

image.png

• 更改innodb_file_per_table 为ON，需要编辑mysql配置文件/etc/my.cnf,开头加入innodb_file_per_table

image.png

• 改完重启数据库systemctl restart mariadb

如果使用此参数，需要注意的是每张表的表空间内存放的只是数据、索引和插入缓冲Bitmap页，其他类的数据，如回滚（undo）信息，插入缓冲索引页、系统事务信息，二次写缓冲等还是存放在原来的共享表空间内。

其他存储引擎

• Performance_Schema: Performance_Schema数据库使用
• Memory：将所有数据存储在RAM中，以便在需要快速查，支持hash索引，表级锁，常用于临时表
• CSV：将CSV文件（以逗号分隔字段的文本文件）作为MySQL表文件
• MRG_MYISAM：将多个MyISAM表合并成的虚拟表
• BLACKHOLE：类似于/dev/null，不真正存储数据
• FEDERATED: 用于访问其它远程MySQL服务器上表的存储引擎接口

MariaDB额外支持很多种存储引擎：
OQGraph、SphinxSE、TokuDB、Cassandra、CONNECT、SQUENCE、...

搜索引擎：
lucene, sphinx
lucene：Solr, ElasticSearch