【MySQL基础篇】一、数据库基础知识

原创

利刃大大

发布于 2025-01-26 12:01:43

83800

代码可运行

文章被收录于专栏：MySQLMySQL

运行总次数：0

代码可运行

文章目录

Ⅰ. 什么是数据库

1、普通文件的缺点

存储数据用文件就可以了，为什么还要弄个数据库❓❓❓

这就不得不提文件保存数据的以下几个缺点：

文件的安全性问题
文件不利于数据查询和管理
文件不利于存储海量数据
文件在程序中控制不方便

什么意思呢，比如说我们如果用普通文件作为数据库的话，那么就会有访问控制、数据加密、完整性保护、备份和恢复、病毒和恶意软件等安全性问题的存在；或者说此时有一大批数据，比如说有几万条 IP 地址，那么如果我们想筛选出以 192 开头的 IP 地址，对于普通文件来说我们就得用编程语言去读取文件，然后再依次判断每个 IP 地址是否符合要求，这显然是有点挫！

而上述问题，有人就帮我们解决了！专家们设计出更加利于管理数据的东西——数据库，它能更有效的管理数据。数据库的水平是衡量一个程序员水平的重要指标。

2、数据库的概念

数据库是一个 组织和存储数据的集合。它是一个 结构化 的数据存储系统，用于管理和访问大量相关数据的集合。数据库可以存储各种类型的数据，如文本、数字、图像、音频和视频等。

数据库的主要目的是 提供一种有效的方式来组织、存储和检索数据。它可以用于在各种应用程序和系统中存储和管理数据，例如企业资源规划（ERP）系统、客户关系管理（CRM）系统、电子商务网站和社交媒体平台等。

数据库通常由一个或多个表组成，每个表包含多个行和列。行表示数据的记录，列表示数据的属性或字段。通过使用结构化查询语言（SQL）或其他查询语言，可以对数据库进行查询、插入、更新和删除操作，以满足特定的数据需求。

数据库的优点包括数据的集中管理、数据的一致性和完整性、数据的安全性和可靠性，以及对大规模数据的高效处理能力。它们在许多领域中都得到广泛应用，包括企业管理、科学研究、金融服务、医疗保健和物流等。

3、主流数据库

SQL Sever：微软的产品，.Net程序员的最爱，适合中大型项目。
Oracle：甲骨文产品，适合大型项目，复杂的业务逻辑，并发一般来说不如 MySQL。
MySQL：世界上最受欢迎的数据库，属于甲骨文公司，并发性好，不适合做复杂的业务。主要用在电商，SNS，论坛。对简单的 SQL 处理效果好。
PostgreSQL：加州大学伯克利分校计算机系开发的关系型数据库，不管是私用，商用，还是学术研究使用，可以免费使用，修改和分发。
SQLite：是一款轻型的数据库，是遵守 ACID 的关系型数据库管理系统，它包含在一个相对小的 C 库中。它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。
H2：是一个用 Java 开发的嵌入式数据库，它本身只是一个类库，可以直接嵌入到应用项目中。

4、MySQL

MySQL 是一种 开源的关系型数据库管理系统（RDBMS），它是最流行和广泛使用的数据库之一。MySQL 由瑞典公司 MySQL AB 开发，并于 2000 年首次发布。现在 MySQL 由 Oracle 公司维护和支持。

MySQL使用结构化查询语言（SQL）作为其主要查询语言，它允许用户创建、修改和管理数据库中的数据。MySQL 支持多种操作系统，包括 Windows、Linux 和 macOS 等。

MySQL具有以下特点：

可靠性：MySQL被广泛用于大型企业和网站，因为它具有高度可靠性和稳定性。它可以处理大量的并发连接和高负载。
可扩展性：MySQL支持水平和垂直扩展，可以根据需求增加服务器的处理能力和存储容量。
安全性：MySQL提供了各种安全功能，如用户认证、访问控制和数据加密等，以保护数据库中的数据免受未经授权的访问和恶意攻击。
灵活性：MySQL支持多种存储引擎，如InnoDB、MyISAM和 Memory 等，每个存储引擎都有不同的特性和适用场景，使用户可以根据需求选择合适的存储引擎。
开源性：MySQL是开源软件，用户可以自由地使用、修改和分发它，这使得它成为许多开发者和组织的首选数据库解决方案。

MySQL广泛应用于各种应用程序和系统，包括 Web 应用程序、企业应用程序、电子商务网站和大数据分析等。它被许多知名公司和组织使用，如 Facebook、Twitter、YouTube 和亚马逊等。

下面的学习我们都是围绕 MySQL 进行展开！

Ⅱ. MySQL中客户端、服务端、数据库的关系

是不是很奇怪，为什么我们在安装 mysql 服务的时候，会有一个 mysqld 呢❓❓❓

其实 mysql 是数据库服务的客户端，而 mysqld 才是数据库服务的服务器端！

也就说，MySQL 的本质其实就是一种 C/S 模式的一种网络服务！

我们还需要有其它的一些共识：

mysql 是一套给我们 提供数据存取服务的网络程序
数据库一般指的是在磁盘或者内存中存储的特定组织结构的数据，也可以成为在磁盘或者内存中存储的一套 数据库方案！
也就是说，磁盘上存储的文件，不是普通的文件，而是特定结构的数据文件！

通过下图就能看出三者之间的关系：

一般我们不仅仅称磁盘那部分为数据库，还有人称整个系统都是数据库，求同存异！

所以在今后学习数据库的时候，为了统一性，这里来规定一下我们自己的说法：

mysql：表示数据库客户端。
mysqld：表示数据库服务端。
数据库：表示磁盘上存储的一套数据库文件。

Ⅲ. 见一见数据库

实践得真知，下面我们给出一个样例：

使用 mysql 建立一个数据库叫做 helloworld，然后在该数据库中建立一张表结构 student，在该表中插入一些数据，最后就是对比一下 mysql 在 linux 中是如何体现的！

1、数据库文件存放的位置

首先我们得先知道我们的 mysql 数据库文件都存放在哪里，这可以在配置文件中看到：

我们打开该路径看看：

可以很明显的看到这些目录，和我们使用服务端指令 show databases 中看到的数据库文件是一样的，也就是说，数据库文件本质就是一个目录！

2、创建数据库

mysql> create database helloworld;

此时可以看到如下效果：

3、使用数据库

在操作库之前，首先就需要选择操作哪个库：

mysql> use helloworld;

4、创建数据库表结构

mysql> create table student(
    -> name varchar(32),
    -> age int,
    -> gender varchar(2)
    -> );

具体的类型我们后面会讲！

可以得到结论，所谓的 在数据库内创建表，本质就是在数据库文件目录下创建对应的文件即可！

5、表中插入数据

mysql> insert into student (name, age, gender) values ('lirendada', 20, '男');

6、查询表中数据

mysql> select * from student;
+-----------+------+--------+
| name      | age  | gender |
+-----------+------+--------+
| lirendada |   20 | 男     |
+-----------+------+--------+
1 row in set (0.00 sec)

7、数据的存储逻辑

数据库文件虽然是二进制文件，但是它是有结构组织的，当我们想要打印结果的时候，其会按照行列式结构打印出来：

💥小总结

建立数据库，本质就是 linux 下创建的一个目录。
在数据库中创建一张表，本质就是在 linux 中的某个数据库目录下对应创建的文件。
数据库本质也是文件，只不过这些文件并不由程序员直接操作，而是由数据库服务帮我们进行操作。

Ⅳ. 服务器、数据库、表之间的关系

所谓安装数据库服务器，其实就是在机器上安装了一个数据库管理系统程序（守护进程，一直在后台运行着），这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。
为保存应用中实体的数据，一般会在数据库中创建多个表，以保存程序中实体的数据。

数据库服务器、数据库和表的关系如下：

Ⅴ. MySQL体系架构

1、体系架构总览

从架构图我们可以看出 MySQL 的架构自顶向下大致可以分为 连接层、数据库服务层、存储引擎层、系统文件层 四大部分。接下来，我们就来简单说说每个部分的组成信息。

2、连接层

连接层位于整个 MySQL 体系架构的最上层，主要担任客户端连接器的角色。提供与 MySQL 服务器建立连接的能力，几乎支持所有主流的服务端语言，例如：Java、C、C++、Python 等，各语言都是通过各自的 API 接口与 MySQL 建立连接。

也就是我们使用的 mysql 客户端，也可能是一些图形化的数据库客户端等等！

3、数据库服务层

数据库服务层是整个数据库服务器的核心，主要包括了系统管理和控制工具、连接池、SQL接口、解析器、查询优化器和缓存等部分。

① `Connection Pool`连接池

主要是负责存储和管理客户端与数据库的链接，连接池里的一个线程负责管理一个客户端到数据库的连接信息。自从引入了连接池以后，官方报道：到连接数达到 128 后，使用连接池与没有连接池的性能是提升了 n 倍（反正就是性能大大的提升了！）。

连接建立完成后，就可以执行 SQL 语句了。执行逻辑就会来到缓存模块。

② `Caches`缓存

MySQL的缓存是由一系列的小缓存组成的。例如：MySQL的表缓存，记录缓存，MySQL中的权限缓存，引擎缓存等。

当 MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 键值对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。

如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。你可以看到，如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。

🎏 但是大多数情况下建议不要使用查询缓存，为什么呢❓❓❓ 因为查询缓存往往弊大于利。查询缓存的失效非常频繁，只要有对一个表的某一条数据更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如：一个系统配置表，那这张表上的查询才适合使用查询缓存。好在 MySQL 也提供了这种“按需使用”的方式。你可以将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。

注意：MySQL 8.0 版本直接将查询缓存的整块功能删掉了，标志着 MySQL 8.0 开始彻底没有缓存这个功能了。

③ `Parser`解析树

主要负责对请求的 SQL 解析成一棵 “解析树”，然后根据 MySQL 中的一些规则对 “解析树” 做进一步的语法验证，确认其是否合法。

注意，如果没有命中查询缓存，才会开始真正执行语句！

步骤差不多如下所示：

首先，MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析。
分析器先会做 “词法分析” 。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么、代表什么。
做完了词法分析以后，就要做 “语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

如果我们在拼写 SQL 语句的时候漏了某个字母，或者某个关键字写错了，就会收到 "You have an error in your SQL syntax" 的错误提醒，如下所示：

一般语法错误会提示第一个出现错误的位置，所以你要关注的是紧接 “use near” 的内容。（仅供参考，有时候这个提示也不是非常靠谱）

经过分析器对 SQL 进行了分析，并且没有报错。那么此时就进入优化器中，对 SQL 进行优化。

④ `Optimizer`优化器

在 MySQL 中，如果 “解析树” 通过了解析器的语法检查，此时就会由优化器将其转化为执行计划，然后与存储引擎进行交互，通过存储引擎与底层的数据文件进行交互。

简单地说，优化器就是在我们的数据库表中，如果存在多个索引的时候，自动决定使用哪个索引；或者当一个语句有多表关联的时候，自动决定各个表的连接顺序。

首先，肯定是要判断权限，就是有没有权限执行这条 SQL。工作中可能会对某些客户端进行权限控制。比如说：生产环境中，对于大部分开发人员都只开查询权限，没有增删改权限（部分小公司除外）。
如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

⑤ `SQL Interface`接口

主要负责接收客户端发送过来的各种 SQL 命令，并将 SQL 命令发送到其他部分，并接收其他部分返回的结果数据，将结果数据返回给客户端。

⑥ `Services & Utilities`系统管理和控制工具

提供数据库系统的管理和控制功能，例如对数据库中的数据进行**备份和恢复，保证整个数据库的安全性，提供安全管理，对整个数据库的集群进行协调和管理**等。

4、存储引擎层

存储引擎的概念是 MySQL 里面才有的，不是所有的关系型数据库都有存储引擎这个概念 。

存储引擎就是数据库管理系统如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。也就是说，其实所有的 底层工作就是由存储引擎帮我们干的，而上层的其它服务都只是对存储引擎进行选择和调用！

因为 在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（Table Type，即存储和操作此表的类型）。

MySQL 中的存储引擎层主要负责数据的写入和读取，与底层的文件进行交互。值得一提的是，MySQL 中的存储引擎是插件式的，服务器中的查询执行引擎通过相关的接口与存储引擎进行通信，同时，接口屏蔽了不同存储引擎之间的差异。

MySQL5.5 版本以前，默认使用的存储引擎是 MyISAM 。
MySQL5.5 版本以后，默认使用的存储引擎是 InnoDB 。

下面对部分相对使用多的引擎进行一个对比：

在实际项目中，大多数使用 InnoDB，然后是 MyISAM，至于其他存储引擎使用的非常至少。

两个主要原因：第一个原因是 MyISAM 是表级锁定，限制了数据库读/写的性能；另外一个原因是 MyISAM 不支持事务，基于以上两点，InnoDB 引擎可以到行。

MyISAM 与 InnoDB 引擎的区别：

事务支持：InnoDB 引擎支持事务处理，可以使用 ACID（原子性、一致性、隔离性和持久性）属性来确保数据的完整性和一致性。而 MyISAM 引擎不支持事务处理。
锁定级别：InnoDB 引擎支持行级锁定，这意味着在并发访问时，只锁定需要修改的行，而不是整个表。这提高了并发性能。而 MyISAM 引擎只支持表级锁定，这意味着在并发访问时，需要锁定整个表，可能导致性能瓶颈。
外键支持：InnoDB 引擎支持外键约束，可以确保数据的完整性和一致性。而 MyISAM 引擎不支持外键约束。
全文索引：InnoDB 引擎从 MySQL 5.6 版本开始支持全文索引。而 MyISAM 引擎一直支持全文索引。
崩溃恢复：InnoDB 引擎具有崩溃恢复能力，可以在数据库崩溃后自动恢复数据。而 MyISAM 引擎在崩溃后可能需要手动修复表。
性能特点：对于大量的读操作和稳定的查询，MyISAM 引擎通常具有更好的性能。而对于大量的写操作、事务处理和并发访问，InnoDB 引擎通常具有更好的性能。

如何在两种存储引擎中进行选择❓❓❓

① 是否有事务操作？有，InnoDB。

② 是否存储并发修改？有，InnoDB。

③ 是否追求快速查询，且数据修改较少？是，MyISAM。

④ 是否使用全文索引？如果不引用第三方框架，可以选择 MyISAM，但是可以选用第三方框架和 InnDB 效率会更高

如何查看存储引擎

我们可以直接通过下面指令查看当前 mysql 中的存储引擎：

show engines

5、系统文件存储层

系统文件存储层主要是 负责将数据库的数据和日志存储在系统的文件中，同时完成与存储引擎的之间的打交道，是文件的物理存储层。其存储的文件主要有：日志文件、数据文件、配置文件、MySQL 的进行 pid 文件和 socket 文件等。

① 数据文件

db.opt 文件：记录当前数据库的默认使用的字符集和校验规则。
frm文件：存储数据表的结构信息，主要是数据表相关的元数据信息，包括数据表的表结构定义信息，每张表都会有一个frm文件。
- 值得注意的是：MySQL8 版本中的 innodb 存储引擎的表没有 frm 文件。
MYD文件：MyISAM 存储引擎专用的文件格式，主要存放 MyISAM 存储引擎数据表中的数据，每张 MyISAM 存储引擎表对应一个 .MYD 文件。
MYI文件：MyISAM 存储引擎专用的文件格式，主要存放与 MyISAM 存储引擎数据表相关的索引信息，每张 MyISAM 存储引擎表对应一个 .MYI 文件。
ibd文件：存放 Innodb 存储引擎的数据文件和索引文件，主要存放的是独享表空间的数据和索引，每张表对应一个 .ibd 文件。
ibdata文件：存放 Innodb 存储引擎的数据文件和索引文件，主要存放的是共享表空间的数据和索引，所有表共用一个（或者多个）.ibdata 文件，可以根据配置来指定共用的 .ibdata 文件个数。
ibdata1 文件：系统表空间数据文件，主要存储 MySQL 的数据表元数据、Undo 日志等信息。
ib_logfile0 和 ib_logfile1 文件：数据库中的 Redo log 文件，主要用于 MySQL 实现事务的持久性。如果在某个时间点 MySQL 发生了故障，此时如果有脏页没有写入到数据库的 ibd 文件中，在重启 MySQL 的时候，MySQL 会根据 Redo Log 信息进行重做，将写入 Redo Log 并且尚未写入数据表的数据进行持久化操作。

② 日志文件

错误日志
- 主要存储的是 MySQL 运行过程中产生的错误信息。 show variables like '%log_error%';
通用查询日志
- 主要记录 MySQL 运行过程中的一般查询信息。 show variables like '%general%';
二进制日志
- 主要记录对 MySQL 数据库执行的插入、修改和删除操作，并且也会记录 SQL 语句执行的时间、执行的时长，但是二进制日志不记录 select、show 等不修改数据库的 SQL。主要用于恢复数据库的数据和实现 MySQL 主从复制。
- 查看二进制日志是否开启： show variables like '%log_bin%';
- 查看二进制日志的参数： show variables like '%binlog%'
- 查看日志文件： show binary logs;
慢查询日志
- 慢查询主要记录的是执行时间超过指定时间的 SQL 语句，这个时间默认是 10 秒。
- 查看是否开启慢查询日志 show variables like '%slow_query%';
- 查看慢查询设置的时长： show variables like '%long_query_time%'

③ 配置文件

用于存在 MySQL 所有的配置信息，在 Unix/Linux 环境中是 my.cnf 文件，在 Windows 环境中是 my.ini 文件。

④ pid文件

pid 文件是 mysqld 应用程序在 Linux 和 Unix 操作系统下才有的的一个进程文件，和许多其他 Linux 或者 Unix 服务端程序一样，该文件 放着自己的进程 id。

⑤ socket文件

socket 文件也是 Linux/Unix 操作系统下才有的，用户在 Linux/Unix 操作系统下客户端连接可以不通过 TCP/IP 网络而直接使用 Unix socket 来连接 MySQL 数据库。

Ⅵ. MySQL执行流程

Ⅶ. SQL分类

DDL【data definition language】数据定义语言，用来 维护存储数据的结构，如创建数据库、创建表结构等，代表指令如 create、drop、alter 等。
DML【data manipulation language】数据操纵语言，用来 对数据进行操作，如增删改等。代表指令如 insert、delete、update 等。
- DML 中又单独分了一个 DQL，数据查询语言，用来对数据进行查询，代表指令如 select。
DCL【Data Control Language】数据控制语言，主要 负责权限管理和事务。代表指令如 grant、revoke、commit 等。

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

mysql

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

mysql