【数据库】MySQL数据库存储引擎、数据库管理和数据库账号管理

一 常用的数据引擎

数据库存储引擎是数据库底层软件组织，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引机制、锁定水平等功能。不同的存储引擎，都有其特定的功能及特定应用场景

可以进入Navicat中查看存储引擎

存储引擎查看命令： SHOW ENGINES

字段说明： default 为默认存储引擎。 YES表示可以使用。NO表示不能使用

1.1 InnoDB存储引擎

InnoDB是事务性数据库首选引擎，也叫默认存储引擎。InnoDB从Mysql5.5.5开始就成为数据默认的存储引擎，是MySQL8.0之后最重要，使用最广泛的存储引擎。支持事务安全（ACID），支持行锁定和外键

InnoDB主要的特性：

存储限制：64TG

速度：删除与修改效率更高

事务支持：支持ACID事务，这意味着它可以提供高可靠性和数据完整性

行级锁定：使用行级锁定来允许多个事务并发访问数据，这有助于提高并发性能。

外键约束：支持外键约束，这有助于保持数据的一致性和完整性。

MVCC（多版本并发控制）：InnoDB使用MVCC来支持高并发访问，同时减少锁的竞争。

聚集索引：InnoDB采用聚集索引的架构，这意味着数据实际上是存储在主键索引中。这有助于提高某些查询的性能，但也意味着你不能更改一个已经存在的表的主键。

数据压缩：InnoDB支持数据压缩，这有助于节省存储空间并提高某些查询的性能。

崩溃恢复：有一个日志文件，可以用来恢复崩溃后可能丢失的数据。

支持多种存储引擎：虽然InnoDB是MySQL的默认存储引擎，但MySQL也支持其他存储引擎，如MyISAM和Memory。这意味着你可以根据特定的使用情况选择最合适的存储引擎。

支持多种隔离级别：InnoDB支持SQL标准的四种隔离级别（READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE）。

自动提交：在InnoDB中，如果没有明确指定，事务会自动提交。

1.2 MyISAM存储引擎

MyISAM基于ISAM存储引擎，并对其进行扩展。在 Web,数据仓储和其他应用环境使用的存储引擎较多。MyISAM拥有较高的插入、查询速度，但不支持事务和外键

不支持事务
存储限制：256TG
表级锁定：在MySQL中，MyISAM存储引擎中，当发生数据更新时，会锁定整个表，以防止其他会话对该表中数据的同时修改所致的混乱。这样做可以使得操作简单，但是会减少并发量
读写相互阻塞：在MyISAM类型表中，即不可以在向数据表中写入数据的同时另一个会话也向该表写入数据，也不允许其他的会话读取该表中的数据。只允许多个会话同时读取该数据表中的数据
只会缓存索引，不会缓存数据：缓存，是指数据库在访问磁盘数据时，将更多的数据读取进入内存，这样可以使得当访问这些数据时，直接从内存中读取而不是再次访问硬盘。MyISAM可以通过key_buffer_size缓存索引，以减少磁盘I/O,提升访问性能。MyISAM数据表并不会缓存数据。
读取速度较快，占用资源较少；
不支持外键约束；
支持全文索引；

数据字典，系统参数
不需要事务支持的场景
读取操作比较多，写入操作较少（很少修改经常查询的数据）
数据并发较低的场景；
硬件条件比较差的场景；
在配置数据库读写分离场景下，从库可以使用MyISAM索引

1.3 Memory存储引擎

MySQL中Memory存储引擎是一个置于内存中的表，其采用的存储介质是内存。响应速度很快。但是当MySQL守护进程崩溃的时候数据会丢失。Memory存储引擎是存储的数据类型是长度不变的类型，blob/text类的数据类型不可用

存储瞬时非关键数据；
存储限制：取决于RAM（随机存储器）’
不支持事务
内存存储，可实现快速访问和低延迟
只读或读取是主要数据访问模式：每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为frm类型。该文件中只存储表的结构。而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。值得注意的是，服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要了，可以释放内存，甚至删除不需要的表。
支持数据类型有限：不支持Text和Blob数据类型，对于字符串类型的数据，只支持固定长度的行，VARCHAR会被自动存储为CHAR类型。
支持表级锁：在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈查询中存在临时表且表中有BLOB、TEXT类型的字段，那么在这个临时表会自动转化为MyISAM类型的表。性能会急剧降低
默认索引使用Hash索引
内存表特别大时，自动转换为MyISAM类型实体表

1.4 ARCHIVE存储引擎

ARCHIVE存储引擎主要用于存储大量的归档数据，如历史数据或安全审计信息
ARCHIVE 存储引擎是 MySQL 数据库中的一种特殊的存储引擎，它主要用于在需要大量存储数据，但对查询性能要求不高的场景。以下是关于 ARCHIVE 存储引擎的一些重要特点和使用注意事项：

数据压缩：ARCHIVE 存储引擎以高度压缩的方式存储数据，因此非常适合存储大量历史性数据，例如日志文件或归档数据。数据压缩可以显著减小存储空间的占用。

只支持INSERT和SELECT操作：ARCHIVE 存储引擎仅支持 INSERT 和 SELECT 操作，不支持 UPDATE 和 DELETE 操作。这是因为 ARCHIVE 存储引擎的设计目标是为了长时间保存大量历史数据，而不是频繁更新或删除数据。

不支持索引：ARCHIVE 存储引擎不支持索引，这意味着在使用该存储引擎的表上不能创建索引。因此，对于需要频繁查询的场景，不适合选择 ARCHIVE 存储引擎。

表级锁定：ARCHIVE 存储引擎使用表级锁定，而不是行级锁定。这可能导致在高并发环境中的写入冲突。

不支持事务：ARCHIVE 存储引擎不支持事务。因此，如果你的应用程序要求事务支持，不应选择 ARCHIVE 存储引擎。

适用场景：ARCHIVE 存储引擎适用于只追加数据、很少进行更新和删除操作，并且对查询性能要求不高的场景。典型的应用场景包括日志表、归档表等。

要在 MySQL 中使用 ARCHIVE 存储引擎，你可以在创建表时指定存储引擎，例如：
需要注意的是，使用 ARCHIVE 存储引擎时，你应该明确了解它的限制和适用场景，确保选择合适的存储引擎来满足你的需求。

二 数据库管理

2.1 元数据库概念与分类

简单来说就是在创建一个数据库的时候自身会创建本身会带的数据库，主要有三种数据库

mysql 数据库：这是存储 MySQL 管理系统自身数据的数据库。它包含用户帐户、权限和其他系统级元数据。在这个数据库中，有一些关键的系统表格，例如 user、db、tables_priv、columns_priv 等，用于存储用户信息、权限和数据库访问控制等。

information_schema 数据库：这个数据库是 MySQL 数据库服务器提供的元数据信息的一个虚拟数据库。它不存储实际数据，而是提供了关于数据库对象（如表、列、权限等）的元数据信息，供用户查询和检索。information_schema 数据库中的表格包含有关数据库服务器的信息，如 SCHEMATA、TABLES、COLUMNS、STATISTICS 等。

performance_schema 数据库：这是 MySQL 5.5及更新版本引入的数据库，用于提供关于数据库服务器性能的详细信息。performance_schema 数据库包含许多用于监视和分析数据库服务器性能的表格，可以帮助识别系统瓶颈、优化查询以及进行性能调优。

这三种数据库是 MySQL 数据库管理系统的核心组成部分，它们各自提供了重要的功能，用于管理、存储系统信息和监视性能。

2.2 相关操作命令

use 数据库名 (使用数据库)

create database 数据库名 （创建数据库）

show databases (查看所有数据库)

drop database 数据库名 （删除数据库）

三 数据表的管理

3.1 三大范式
第一范式：列不可再分（原子性）

属性不可分割，即每个属性都是不可分割的原子项。(实体的属性即表中的列)

第二范式：主键约束

满足第一范式；且不存在部分依赖，即非主属性必须完全依赖于主属性。(主属性即主键；完全依赖是针对于联合主键的情况，非主键列不能只依赖于主键的一部分)

第三范式：外键约束

满足第二范式；且不存在传递依赖，即非主属性不能与非主属性之间有依赖关系，非主属性必须直接依赖于主属性，不能间接依赖主属性。（A ->
B, B ->C, A -> C）

3.2 数据类型
整数：

tinyint 8位（-128~127）

smallint 16位（-32768~ 32767）

mediumint 24位 （-8388608~ 8388607）

int 32位 大约正负21亿

bigint 64位

实数(带有小数点)：

float 4个字节

double 8个字节

ecimal 最多允许65个数字

字符串：

CHAR:

定长字符串。长度范围是 0 到 255 个字符。 如果存储的字符串长度小于定义的长度，MySQL会用空格填充剩余的空间

VARCHAR:

变长字符串。长度范围是 0 到 65,535 个字符。 只存储实际需要的字符，不会用空格填充。
频繁修改且字符串变化长度大时，可能会出现页分裂

text&blob：

text存储字符数据：tinytext，smalltext，mediumtext，text，longtext

blob存储二进制数据: tinyblob，smallblob，mediumbolb，blob，longblob

日期时间：

datetime：精度秒 8个字节的存储空间 范围在1001年-9999年

timestamp: 1970.1.1后的秒数 占用4个字节空间 1970-2038年 时区有关

date： yyyy-MM-dd

time： HH:mm:ss

选择标识符：

① 用来进行关联操作

② 在其他表作为外键

③ 整形通常是标识列最好选择

④ 相关的表中使用相同的数据类型

⑤ 避免字符串作为标识列，不然insert与select慢影响效率

四 数据库账户管理

SELECT USER, Host FROM user：

这是一个查询语句，目的是从 MySQL 数据库的 user 表中选择 USER 和 Host 列的数据，显示了数据库中所有用户的用户名和主机信息。
CREATE USER niyin IDENTIFIED BY ‘123’：

这是一个创建用户的语句。它创建了一个名为 niyin 的用户，该用户的密码是 ‘123’。
GRANT SELECT ON db_oa.iswel TO niyin@‘%’：

这是一个授权语句，授予了用户 niyin 对数据库 db_oa 中的表 iswel 执行 SELECT 操作的权限。niyin@‘%’ 表示该授权适用于任何主机。
GRANT UPDATE ON db_oa.iswel TO niyin@‘%’：

这是另一个授权语句，授予了用户 niyin 对数据库 db_oa 中的表 iswel 执行 UPDATE 操作的权限。同样，niyin@‘%’ 表示该授权适用于任何主机。
REVOKE UPDATE ON db_oa.iswel FROM niyin@‘%’：

这是一个回收权限的语句，从用户 niyin 中撤销对数据库 db_oa 中表 iswel 的 UPDATE 权限。同样，niyin@‘%’ 表示该回收适用于任何主机。
SHOW GRANTS FOR ‘niyin’@‘%’：

这个语句用于显示用户 niyin 在任何主机上的授予权限。它将列出用户 niyin 的当前权限设置。
SHOW DATABASES：

这是一个显示当前 MySQL 服务器上所有数据库的语句。它将返回一个包含数据库列表的结果集。
请注意，SQL 语句需要在正确的上下文中执行，而且执行这些语句需要相应的权限。确保你对 MySQL 数据库有足够的权限执行这些操作。

​