3.3 MySQL 存储引擎
MySQL架构
Client : 提供连接MySQL服务器功能的常用工具集 Server : MySQL实例，真正提供数据存储和数据处理功能的MySQL服务器进程 mysqld: MySQL服务器守护程序，在后台运行。它管理着客户端请求。mysqld是一个多线程的进程，允许多个会话连接，端口监听连接，管理MySQL实例 MySQL memory allocation: MySQL的要求的内存空间是动态的，比如 innodb_buffer_pool_size (from 5.7.5), key_buffer_size。每个会话都有独一无二的执行计划，我们只能共享同一会话域内的数据集。 SESSION 为每个客户端连接分配一个会话，动态分配和回收。用于查询处理，每个会话同时具备一个缓冲区。每个会话是作为一个线程执行的 Parser 检测SQL语句语法，为每条SQL语句生成SQL_ID，用户认证也发生在这个阶段 Optimizer 创造一个有效率的执行计划（根据具体的存储引擎）。它将会重写查询语句。比如：InnoDB有共享缓冲区，所以，优化器会首先从预先缓存的数据中提取。使用 table statistics optimizer将会为SQL查询生成一个执行计划。用户权限检查也发生在这个阶段。 Metadata cache 缓存对象元信息和统计信息 Query cache 共享在内存中的完全一样的查询语句。如果完全相同的查询在缓存命中，MySQL服务器会直接从缓存中去检索结果。缓存是会话间共享的，所以为一个客户生成的结果集也能为另一个客户所用。查询缓存基于SQL_ID。将SELECT语句写入视图就是查询缓存最好的例子。 key cache 缓存表索引。MySQL keys是索引。如果索引数据量小，它将缓存索引结构和叶子节点（存储索引数据）。如果索引很大，它只会缓存索引结构，通常供
MySQL 主要的两种存储引擎
MyISAM存储引擎特点 不支持事务 表级锁定 读写相互阻塞，写入不能读，读时不能写 只缓存索引 不支持外键约束 不支持聚簇索引 读取数据较快，占用资源较少 不支持MVCC（多版本并发控制机制）高并发 崩溃恢复性较差 MySQL5.5.5前默认的数据库引擎 MyISAM存储引擎适用场景 只读（或者写较少） 表较小（可以接受长时间进行修复操作） MyISAM引擎文件 tbl_name.frm 表格式定义 tbl_name.MYD 数据文件 tbl_name.MYI 索引文件
InnoDB引擎特点 行级锁 支持事务，适合处理大量短期事务 读写阻塞与事务隔离级别相关 可缓存数据和索引 支持聚簇索引 崩溃恢复性更好 支持MVCC高并发 从MySQL5.5后支持全文索引 从MySQL5.5.5开始为默认的数据库引擎 InnoDB数据库文件 所有InnoDB表的数据和索引放置于同一个表空间中 数据文件：ibdata1, ibdata2,存放在datadir定义的目录下 表格式定义：tb_name.frm,存放在datadir定义的每个数据库对应的目录下 每个表单独使用一个表空间存储表的数据和索引 两类文件放在对应每个数据库独立目录中 数据文件(存储数据和索引)：tb_name.ibd 表格式定义：tb_name.frm
其它存储引擎
Performance_Schema：Performance_Schema数据库使用
Memory ：将所有数据存储在RAM中，以便在需要快速查找参考和其他类似数据的环境中进行快速访问。适用存放临时数据。引擎以前被称为HEAP引擎
MRG_MyISAM：使MySQL DBA或开发人员能够对一系列相同的MyISAM表进行逻辑分组，并将它们作为一个对象引用。适用于VLDB(Very Large Data Base)环境，如数据仓库
Archive ：为存储和检索大量很少参考的存档或安全审核信息，只支持SELECT和INSERT操作；支持行级锁和专用缓存区
Federated联合：用于访问其它远程MySQL服务器一个代理，它通过创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，而后完成数据存取，提供链接单独MySQL服务器的能力，以便从多个物理服务器创建一个逻辑数据库。非常适合分布式或数据集市环境
BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性
Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性
CSV：CSV存储引擎使用逗号分隔值格式将数据存储在文本文件中。可以使用CSV引擎以CSV格式导入和导出其他软件和应用程序之间的数据交换
BLACKHOLE ：黑洞存储引擎接受但不存储数据，检索总是返回一个空集。该功能可用于分布式数据库设计，数据自动复制，但不是本地存储
example：“stub”引擎，它什么都不做。可以使用此引擎创建表，但不能将数据存储在其中或从中检索。目的是作为例子来说明如何开始编写新的存储引擎
管理存储引擎
查看mysql支持的存储引擎
show engines; 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> 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 (0.00 sec)
设置默认的存储引擎
vim /etc/my.cnf [mysqld] default_storage_engine= InnoDB
查看库中所有表使用的存储引擎
mysql> show table status from centos; +-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | 员工信息表 | InnoDB | 10 | Dynamic | 2 | 8192 | 16384 | 0 | 0 | 0 | NULL | 2021-03-08 15:51:34 | 2021-03-08 16:21:32 | NULL | utf8_general_ci | NULL | | | | 工资表3 | InnoDB | 10 | Dynamic | 1 | 16384 | 16384 | 0 | 16384 | 0 | NULL | 2021-03-08 17:54:29 | 2021-03-08 16:21:32 | NULL | utf8_general_ci | NULL | | | | user | InnoDB | 10 | Dynamic | 21 | 780 | 16384 | 0 | 16384 | 0 | NULL | 2021-03-08 16:36:25 | 2021-03-08 16:41:39 | NULL | utf8_general_ci | NULL | | | +-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ 3 rows in set (0.01 sec)
查看库中指定表的存储引擎
mysql> show table status like 'user'; +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | user | InnoDB | 10 | Dynamic | 21 | 780 | 16384 | 0 | 16384 | 0 | NULL | 2021-03-08 16:36:25 | 2021-03-08 16:41:39 | NULL | utf8_general_ci | NULL | | | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ 1 row in set (0.00 sec)
mysql> show create table user; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | user | CREATE TABLE `user` ( `name` char(50) DEFAULT NULL, `password` char(1) DEFAULT NULL, `uid` smallint(6) DEFAULT NULL, `gid` smallint(6) DEFAULT NULL, `describ` char(200) DEFAULT NULL, `homedir` char(200) DEFAULT NULL, `shell` char(100) DEFAULT NULL, KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
设置表的存储引擎：
mysql> create table cc(name varchar(10)) engine=innodb; Query OK, 0 rows affected (0.00 sec) mysql> create table ccm(name varchar(10)) engine=myisam; Query OK, 0 rows affected (0.00 sec) mysql> show table status like "ccm"\G *************************** 1. row *************************** Name: ccm Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 0 Avg_row_length: 0 Data_length: 0 Max_data_length: 281474976710655 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2021-03-10 06:09:58 Update_time: 2021-03-10 06:09:58 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.01 sec) mysql> ALTER TABLE ccm ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
MySQL中的系统数据库
mysql数据库 是mysql的核心数据库，类似于Sql Server中的master库，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息 performance_schema数据库 MySQL 5.5开始新增的数据库，主要用于收集数据库服务器性能参数,库里表的存储引擎均为PERFORMANCE_SCHEMA，用户不能创建存储引擎为PERFORMANCE_SCHEMA的表 information_schema数据库 MySQL 5.0之后产生的，一个虚拟数据库，物理上并不存在information_schema数据库类似与“数据字典”，提供了访问数据库元数据的方式，即数据的数据。比如数据库名或表名，列类型，访问权限（更加细化的访问方式） sys数据库 MySQL5.7之后新增加的数据库，库中所有数据源来自performance_schema。目标是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了解DB的运行情况
浙公网安备 33010602011771号