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

2024-01-03 15:06:16

一、数据库存储引擎

1.1 概述

数据库存储引擎是数据库管理系统中的一个模块,负责管理数据在磁盘上的存储和检索。它定义了数据在物理存储介质上的组织结构,包括表、索引等。存储引擎的工作涉及到将逻辑数据模型转化为物理存储结构,以提高数据的访问效率。

不同的数据库系统支持不同的存储引擎,而每种引擎都有其独特的特点、优势和适用场景。一些常见的数据库存储引擎包括InnoDB、MyISAM、SQLite的默认存储引擎等四大引擎。它们可能使用不同的数据结构、索引算法和缓存机制,因此在选择存储引擎时需要考虑到应用的性能需求、并发处理能力、事务支持等因素。

1.2 InnoDB

  • 特点:
    • 提供了强大的事务支持和行级锁定。
    • 支持外键约束、ACID事务特性,适用于高并发和需要数据一致性的应用。
    • 存储限制:64 TB
  • 适用场景: 适合事务性应用,如电子商务、金融系统等。由于其可靠性和并发处理能力,通常作为MySQL的默认存储引擎。

1.3 MyISAM

  • 特点:
    • 不支持事务和行级锁定
    • 具有较快的读取速度和较小的存储空间。(表锁)
    • 支持全文索引。
    • 存储限制:256 TB
  • 适用场景: 适合读密集型的应用,如博客、新闻网站等。在对数据的读取频率远远大于写入的情况下,MyISAM表现较为出色。

1.4 Memory(HEAP)

  • 特点:
    • 将表数据存储在内存中,提供了非常快速的数据访问,但在数据库关闭时数据将丢失。
    • 不支持事务。
    • 存储限制:取决于RAM(随机储存器)
  • 适用场景: 适用于对速度要求极高、数据量不大且能够丢失的场景,如临时表、缓存表等。

1.5 Archive

  • 特点:
    • 针对只有少量读写操作的大量历史数据(只能插入和查询),使用压缩算法存储数据。
    • 不支持事务和索引。
  • 适用场景: 适合归档和存储大量历史数据的场景,对于只需偶尔查询的数据而言,Archive表可以提供较高的存储效率。

二、数据库管理

2.1 元数据库

记录MySQL系统自身数据的数据库,即系统数据库

2.2 分类

在MySQL中,有一些系统数据库用于存储系统元数据和执行特定的功能。以下是一些常见的MySQL系统数据库:

  1. information_schema:

    • information_schema 存储了有关MySQL服务器的元数据,包括数据库、表、列、索引、用户权限等信息。这个数据库可以被用于查询和了解MySQL服务器的结构和配置。
  2. mysql:

    • mysql 数据库存储了关于MySQL用户和权限的信息。在这个数据库中的表,如userdbtables_priv等,包含了关于用户、数据库和表级别的权限信息。
  3. performance_schema:

    • performance_schema 提供了关于MySQL服务器性能的信息。这个数据库包含了许多表,记录了例如锁、查询性能、线程活动等方面的统计信息。
  4. sys:

    • sys 数据库是一个由MySQL提供的用于收集和展示性能信息的插件。它使用performance_schema中的数据,并提供了一组存储过程和视图,用于更方便地查看和分析性能统计信息。

2.3 CURD

-- 创建数据库
CREATE DATABASE if not exists my_database  CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 查询所有数据库
SHOW DATABASES;

-- 使用数据库
use DATABASES;

-- 修改数据库字符集
ALTER DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 删除数据库
DROP DATABASE my_database;

2.4 权限相关

权限信息存储在 mysql 系统库的 user、db、tables_priv、columns_priv、procs_priv 这几个系统表中。

  • user 表:存放用户账户信息以及全局级别(所有数据库)权限。
  • db 表:存放数据库级别的权限,决定了来自哪些主机的哪些用户可以访问此数据库。
  • tables_priv 表:存放表级别的权限,决定了来自哪些主机的哪些用户可以访问数据库的这个表。
  • columns_priv 表:存放列级别的权限,决定了来自哪些主机的哪些用户可以访问数据库表的这个字段。

三、数据表管理

3.1 三大范式

数据库范式是一种规范化数据库设计的方法,通过分解表结构并消除冗余数据,以提高数据库的一致性和性能。关系数据库理论中提出了三大范式,它们是:

  1. 第一范式(1NF):列不可再分

    • 数据表中的所有列都是原子性的,不可再分。每一列都包含一个单一的、不可再分的值。
    • 保证每个单元格中只包含一个原子值,而不是多个值或者是集合。
  2. 第二范式(2NF):主键约束

    • 数据表必须符合1NF,且表中的所有非主键列都完全依赖于主键(候选键)。
    • 也就是说,每个非主键列必须完全依赖于整个主键,而不是只依赖于主键的一部分。
  3. 第三范式(3NF):外键约束

    • 数据表必须符合2NF,且表中的所有列都不传递依赖于主键。
    • 换句话说,表中的每个非主键列都不依赖于其他非主键列。

这三个范式是逐步递进的,第一范式是基础,第二范式在此基础上消除了部分依赖,第三范式在第二范式的基础上消除了传递依赖。范式的遵循有助于提高数据库的规范性、减少冗余数据,并提高数据的一致性和可维护性。

3.2 数据基本类型

优化原则

  • 更小通常更快
    • 更小通常更好更小的磁盘空间、内存、CPU缓存
    • 更少的CPU周期
  • 简单就好
    • 整型比字符串操作代价更小
    • 用时间类型表示日期 (date、datetime等)而不用字符串
  • 尽量不用null
    • NULL为列的默认值,但除非确实需要,应尽量避免使用NU
    • 尽量指定列为NOT NULL,特别是需要建索引的列
    • 查询中包括有NULL的类,MySQL更难优化
      • 索引统计更复杂
      • 不利于使用索引
      • 值比较更复杂
    • 用整型值而不是字符串表示IP地址
      • INET ATON()
      • INET NTOA()

基本数据类型

  1. 整数类型:

    • INT(整数): 通常用于存储整数值,可表示范围在-231到231-1之间的整数。
    • SMALLINT: 存储小范围整数,范围在-215到215-1之间。
    • TINYINT: 存储非常小范围的整数,范围在-128到127之间。
    • BIGINT: 用于存储大范围整数,可表示范围在-263到263-1之间的整数。
  2. 浮点数类型:

    • FLOAT: 存储单精度浮点数,通常用于表示近似值,有约6-9位小数精度。
    • DOUBLE: 存储双精度浮点数,提供更高的精度,有约15-17位小数精度。
  3. 定点数类型:

    • DECIMAL(DEC): 用于存储精确的小数,通过指定精度和小数位数来确保准确性。
  4. 字符串类型:

    • CHAR: 存储固定长度的字符串,对于长度固定的数据比如国家代码,这是一个合适的选择。
    • VARCHAR: 存储可变长度的字符串,对于长度可变的数据,比如用户的地址,这是更常见的选择。
    • TEXT: 用于存储大文本数据,比如文章或评论。tinytext, smalltext,mediumtext,text,longtext
  5. 日期与时间类型:

    • DATE: 存储日期,格式为’YYYY-MM-DD’。
    • TIME: 存储时间,格式为’HH:MM:SS’。
    • DATETIME: 存储日期和时间,格式为’YYYY-MM-DD HH:MM:SS’。
    • TIMESTAMP: 存储从1970年1月1日以来的秒数。
  6. 布尔类型:

    • BOOLEAN(BOOL): 存储布尔值,通常表示真或假。
  7. 二进制类型:

    • BLOB(Binary Large Object): 存储二进制数据,通常用于存储图像、音频、视频等。 TINYBLOB BLOB MEDIUMBLOB LONGBLOB
  8. 枚举类型:

    • ENUM: 用于存储从预定义的值列表中选择的一个值。例如,性别可以用 ENUM(‘Male’, ‘Female’) 表示。

三、数据库账号管理

MySQL 8.0 版本的账号管理操作基本上延续了之前版本的基本语法,但引入了一些新的特性。以下是一些 MySQL 8.0 中的数据库用户账号管理操作:

  1. 创建账号:

    • 使用 CREATE USER 语句创建一个新的数据库用户。
    CREATE USER 'username'@'host' IDENTIFIED BY 'password';
    
    • 'username'是要创建的用户名,'host'是允许访问的主机,'password'是用户的密码。
  2. 授予权限:

    • 使用 GRANT 语句给用户授予数据库权限。
    GRANT privileges ON database.table TO 'username'@'host';
    
    • privileges 表示授予的权限,比如 SELECT, INSERT, UPDATE, DELETE 等;database.table 表示具体的数据库和表;'username'@'host' 是目标用户和允许访问的主机。
  3. 修改账号权限:

    • 使用 GRANTREVOKE 语句修改用户的权限。
    GRANT privileges ON database.table TO 'username'@'host';
    REVOKE privileges ON database.table FROM 'username'@'host';
    
    • GRANT 授予权限,REVOKE 撤销权限。
  4. 删除账号:

    • 使用 DROP USER 语句删除一个数据库用户。
    DROP USER 'username'@'host';
    
    • 删除用户后,相应的权限也会被移除。
  5. 修改密码:

    • 使用 ALTER USER 语句修改用户密码。
    ALTER USER 'username'@'host' IDENTIFIED BY 'newpassword';
    
    • 'newpassword' 是新的密码。
  6. 查看用户权限:

    • 使用 SHOW GRANTS 语句查看用户的权限。
    SHOW GRANTS FOR 'username'@'host';
    
  7. 账号锁定和解锁:

    • 使用 ALTER USER 语句可以锁定或解锁用户账号。
    ALTER USER 'username'@'host' ACCOUNT LOCK;
    ALTER USER 'username'@'host' ACCOUNT UNLOCK;
    
  8. 账号过期:

    • 使用 ALTER USER 语句可以设置账号的过期时间。
    ALTER USER 'username'@'host' PASSWORD EXPIRE;
    
    • 这会使用户下次登录时要求更改密码。

这些是 MySQL 8.0 中一些常见的数据库用户账号管理操作。在执行这些操作时,请务必根据实际情况谨慎操作,尤其是在生产环境中。

文章来源:https://blog.csdn.net/TestXzing/article/details/135355419
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。