【数据库】MySQL数据库存储引擎、数据库管理和数据库账号管理
文章目录
一、数据库存储引擎
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系统数据库:
-
information_schema:
- information_schema 存储了有关MySQL服务器的元数据,包括数据库、表、列、索引、用户权限等信息。这个数据库可以被用于查询和了解MySQL服务器的结构和配置。
-
mysql:
- mysql 数据库存储了关于MySQL用户和权限的信息。在这个数据库中的表,如
user
、db
、tables_priv
等,包含了关于用户、数据库和表级别的权限信息。
- mysql 数据库存储了关于MySQL用户和权限的信息。在这个数据库中的表,如
-
performance_schema:
performance_schema
提供了关于MySQL服务器性能的信息。这个数据库包含了许多表,记录了例如锁、查询性能、线程活动等方面的统计信息。
-
sys:
- sys 数据库是一个由MySQL提供的用于收集和展示性能信息的插件。它使用
performance_schema
中的数据,并提供了一组存储过程和视图,用于更方便地查看和分析性能统计信息。
- sys 数据库是一个由MySQL提供的用于收集和展示性能信息的插件。它使用
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 三大范式
数据库范式是一种规范化数据库设计的方法,通过分解表结构并消除冗余数据,以提高数据库的一致性和性能。关系数据库理论中提出了三大范式,它们是:
-
第一范式(1NF):列不可再分
- 数据表中的所有列都是原子性的,不可再分。每一列都包含一个单一的、不可再分的值。
- 保证每个单元格中只包含一个原子值,而不是多个值或者是集合。
-
第二范式(2NF):主键约束
- 数据表必须符合1NF,且表中的所有非主键列都完全依赖于主键(候选键)。
- 也就是说,每个非主键列必须完全依赖于整个主键,而不是只依赖于主键的一部分。
-
第三范式(3NF):外键约束
- 数据表必须符合2NF,且表中的所有列都不传递依赖于主键。
- 换句话说,表中的每个非主键列都不依赖于其他非主键列。
这三个范式是逐步递进的,第一范式是基础,第二范式在此基础上消除了部分依赖,第三范式在第二范式的基础上消除了传递依赖。范式的遵循有助于提高数据库的规范性、减少冗余数据,并提高数据的一致性和可维护性。
3.2 数据基本类型
优化原则
- 更小通常更快
- 更小通常更好更小的磁盘空间、内存、CPU缓存
- 更少的CPU周期
- 简单就好
- 整型比字符串操作代价更小
- 用时间类型表示日期 (date、datetime等)而不用字符串
- 尽量不用null
- NULL为列的默认值,但除非确实需要,应尽量避免使用NU
- 尽量指定列为NOT NULL,特别是需要建索引的列
- 查询中包括有NULL的类,MySQL更难优化
- 索引统计更复杂
- 不利于使用索引
- 值比较更复杂
- 用整型值而不是字符串表示IP地址
- INET ATON()
- INET NTOA()
基本数据类型
-
整数类型:
- INT(整数): 通常用于存储整数值,可表示范围在-231到231-1之间的整数。
- SMALLINT: 存储小范围整数,范围在-215到215-1之间。
- TINYINT: 存储非常小范围的整数,范围在-128到127之间。
- BIGINT: 用于存储大范围整数,可表示范围在-263到263-1之间的整数。
-
浮点数类型:
- FLOAT: 存储单精度浮点数,通常用于表示近似值,有约6-9位小数精度。
- DOUBLE: 存储双精度浮点数,提供更高的精度,有约15-17位小数精度。
-
定点数类型:
- DECIMAL(DEC): 用于存储精确的小数,通过指定精度和小数位数来确保准确性。
-
字符串类型:
- CHAR: 存储固定长度的字符串,对于长度固定的数据比如国家代码,这是一个合适的选择。
- VARCHAR: 存储可变长度的字符串,对于长度可变的数据,比如用户的地址,这是更常见的选择。
- TEXT: 用于存储大文本数据,比如文章或评论。tinytext, smalltext,mediumtext,text,longtext
-
日期与时间类型:
- DATE: 存储日期,格式为’YYYY-MM-DD’。
- TIME: 存储时间,格式为’HH:MM:SS’。
- DATETIME: 存储日期和时间,格式为’YYYY-MM-DD HH:MM:SS’。
- TIMESTAMP: 存储从1970年1月1日以来的秒数。
-
布尔类型:
- BOOLEAN(BOOL): 存储布尔值,通常表示真或假。
-
二进制类型:
- BLOB(Binary Large Object): 存储二进制数据,通常用于存储图像、音频、视频等。 TINYBLOB BLOB MEDIUMBLOB LONGBLOB
-
枚举类型:
- ENUM: 用于存储从预定义的值列表中选择的一个值。例如,性别可以用 ENUM(‘Male’, ‘Female’) 表示。
三、数据库账号管理
MySQL 8.0 版本的账号管理操作基本上延续了之前版本的基本语法,但引入了一些新的特性。以下是一些 MySQL 8.0 中的数据库用户账号管理操作:
-
创建账号:
- 使用
CREATE USER
语句创建一个新的数据库用户。
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
'username'
是要创建的用户名,'host'
是允许访问的主机,'password'
是用户的密码。
- 使用
-
授予权限:
- 使用
GRANT
语句给用户授予数据库权限。
GRANT privileges ON database.table TO 'username'@'host';
privileges
表示授予的权限,比如SELECT
,INSERT
,UPDATE
,DELETE
等;database.table
表示具体的数据库和表;'username'@'host'
是目标用户和允许访问的主机。
- 使用
-
修改账号权限:
- 使用
GRANT
或REVOKE
语句修改用户的权限。
GRANT privileges ON database.table TO 'username'@'host'; REVOKE privileges ON database.table FROM 'username'@'host';
GRANT
授予权限,REVOKE
撤销权限。
- 使用
-
删除账号:
- 使用
DROP USER
语句删除一个数据库用户。
DROP USER 'username'@'host';
- 删除用户后,相应的权限也会被移除。
- 使用
-
修改密码:
- 使用
ALTER USER
语句修改用户密码。
ALTER USER 'username'@'host' IDENTIFIED BY 'newpassword';
'newpassword'
是新的密码。
- 使用
-
查看用户权限:
- 使用
SHOW GRANTS
语句查看用户的权限。
SHOW GRANTS FOR 'username'@'host';
- 使用
-
账号锁定和解锁:
- 使用
ALTER USER
语句可以锁定或解锁用户账号。
ALTER USER 'username'@'host' ACCOUNT LOCK; ALTER USER 'username'@'host' ACCOUNT UNLOCK;
- 使用
-
账号过期:
- 使用
ALTER USER
语句可以设置账号的过期时间。
ALTER USER 'username'@'host' PASSWORD EXPIRE;
- 这会使用户下次登录时要求更改密码。
- 使用
这些是 MySQL 8.0 中一些常见的数据库用户账号管理操作。在执行这些操作时,请务必根据实际情况谨慎操作,尤其是在生产环境中。
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符,请联系我的编程经验分享网邮箱:veading@qq.com进行投诉反馈,一经查实,立即删除!