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

Ⅰ. 什么是数据库

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具有以下特点：

1. 可靠性：MySQL被广泛用于大型企业和网站，因为它具有高度可靠性和稳定性。它可以处理大量的并发连接和高负载。
2. 可扩展性：MySQL支持水平和垂直扩展，可以根据需求增加服务器的处理能力和存储容量。
3. 安全性：MySQL提供了各种安全功能，如用户认证、访问控制和数据加密等，以保护数据库中的数据免受未经授权的访问和恶意攻击。
4. 灵活性：MySQL支持多种存储引擎，如InnoDB、MyISAM和 Memory 等，每个存储引擎都有不同的特性和适用场景，使用户可以根据需求选择合适的存储引擎。
5. 开源性：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、数据的存储逻辑

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

💥小总结

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

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

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

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

Ⅴ. 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 中的一些规则对 “解析树” 做进一步的语法验证，确认其是否合法。

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

​ 步骤差不多如下所示：

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