【MySQL】常用存储引擎，数据库管理，数据表管理，数据库账户管理

目录

一 常用的数据引擎(4)

1.1 InnoDB存储引擎

1.2 MyISAM存储引擎

1.3 Memory存储引擎

1.4 ARCHIVE存储引擎

二 数据库管理

2.1 元数据库概念与分类

2.2 相关操作命令

三 数据表的管理

3.1 三大范式

3.2 数据类型

四 数据库账户管理

五 思维导图 

一 常用的数据引擎(4)

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

可以进入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拥有较高的插入、查询速度，但不支持事务和外键

MyISAM的主要特性：

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

MyISAM适用场景：

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

1.3 Memory存储引擎

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

Memory的主要特性：

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

应用场景：一般存储在视图和缓存的数据

1.4 ARCHIVE存储引擎

         ARCHIVE存储引擎主要用于存储大量的归档数据，如历史数据或安全审计信息

ARCHIVE的主要特性：

不支持事务：ARCHIVE不支持事务处理，这意味着它不能提供ACID事务的保证

不支持索引（自增ID列除外）

高压缩率：ARCHIVE存储引擎采用了较高的压缩比，可以大大减少存储空间的需求，使大量历史数据得以高效地存储。

只支持INSERT和SELECT操作：ARCHIVE不支持DELETE、UPDATE等其他操作，这有助于保持数据的一致性和完整性。

行级锁定机制：ARCHIVE使用行级锁定来允许多个事务并发访问数据，提高并发性能。

适用于历史数据存储：由于其高压缩率和只支持INSERT和SELECT操作的特性，ARCHIVE非常适合存储大量的历史数据或归档信息。

使用场景：只允许插入和查询，不允许删除和修改，压缩存储节约空间，如日志记录、审计跟踪等。

二 数据库管理

2.1 元数据库概念与分类

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

information_schema元数据库：信息数据库，保存mysql所维护的其他数据库信息，包含了关于    数据库实例中所有数据库、表、列、索引、约束等的结构信息

mysql元数据库：核心数据库，user，db，tables_priv，columns_priv这些表中，主要负责存储数据库的用户，权限设置，关键字等

user表：用户管理权限

db表：数据库层权限

tables_priv表：表层权限

columns_priv表：字段层权限

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` 

    

create user Bing identified by'123'

grant SELECT on his.t_book to Bing@'%';

grant UPDATE on his.t_book to Bing@'%';

revoke UPDATE on his.t_book from Bing@'%';

show grants for 'Bing'@'%';

show databases

图解命令意思：

五 思维导图