KWAN的解忧杂货铺

# 一.基础认识

# 1.什么是 MySQL

MySQL 是最流行的开源 SQL 数据库管理系统，由 Oracle 公司开发、分发和支持。MySQL 网站 (opens new window)

MySQL 是一种关系型数据库管理系统，其全称为“My Structured Query Language”。

  1. MySQL 是一个数据库管理系统;
  2. MySQL 数据库是关系型数据库;
  3. MySQL 是一种开源软件，可以在多个操作系统下使用；
  4. MySQL 数据库服务器非常快速、可靠、可扩展且易于使用;
  5. MySQL 支持多种存储引擎，如 MyISAM 和 InnoDB 等；
  6. MySQL 具有强大的安全性和可靠性，可以通过 SSL 等加密方式进行数据传输。

# 2.内部结构和便携性

  • 用 C 和 C++ 编写。

  • 适用于许多不同的平台。

  • 采用多层服务器设计，模块独立。

  • 提供事务性和非事务性存储引擎。

  • 使用非常快的 B 树磁盘表 ( MyISAM) 和索引压缩。

  • 实现内存中的哈希表，用作临时表。

  • 使用高度优化的类库实现 SQL 函数，该类库应该尽可能快。

# 3.MySQL8.0 新增功能

MySQL 8.0 是 MySQL 数据库管理系统的一个重要版本，它在 2018 年 4 月发布。以下是 MySQL 8.0 中的一些主要新增功能和改进：

  1. 支持窗口函数：引入了窗口函数（Window Functions），允许在查询中进行更复杂的数据分析和聚合操作。
  2. CTE（Common Table Expressions）：引入了公共表表达式，允许在查询中创建临时结果集，并在后续查询中引用它们。
  3. 新增降序索引，可以支持降序的索引。
  4. 增强的安全性：引入了更多的安全功能，如更强大的密码验证策略，支持 TLSv1.3 加密协议等。
  5. 更好的 JSON 支持：增加了更多的 JSON 函数和操作符，使得在存储和查询 JSON 数据更加方便和高效。
  6. 调优器改进：优化了查询优化器，使得一些查询在 MySQL 8.0 上执行更加高效。
  7. 新的数据类型：新增了几种数据类型，例如 GEOMETRY 类型的空间数据支持，以及更好的 UUID 支持。
  8. InnoDB 存储引擎改进：提供了更好的性能和可伸缩性，包括通过数据字典提高元数据的效率。
  9. 新的字符集和排序规则：增加了新的 Unicode 字符集和排序规则，提供更好的全球化支持。
  10. 自适应哈希索引：InnoDB 引擎中的自适应哈希索引可以提高特定查询的性能。
  11. Group Replication：引入了 MySQL Group Replication，提供了原生的多主复制特性，使得多个 MySQL 实例可以组成一个高可用性和容错性的集群。

# 4.数据库与实例

数据库与实例:

  • 数据库：物理操作系统文件或其他形式文件类型的集合。在 MySQL 数据库中，数据库文件可以是 frm、MYD、MYI、ibd 结尾的文件。当使用 NDB 引擎时，数据库的文件可能不是操作系统上的文件，而是存放于内存之中的文件，但是定义仍然不变。
  • 实例：MySQL 数据库由后台线程以及一个共享内存区组成。共享内存可以被运行的后台线程所共享。需要牢记的是，数据库实例才是真正用于操作数据库文件的。

从概念上来说，数据库是文件的集合，是依照某种数据模型组织起来并存放于二级存储器中的数据集合合；

数据库实例是程序，是位于用户与操作系统之间的一层数据管理软件，用户对数据库数据的任何操作，包括数据库定义、数据查询、数据维护、数据库运行控制等都是在数据库实例下进行的，应用程序只有通过数据库实例才能和数据库打交道。

# 5.数据库三大范式

数据库三大范式是关系数据库设计中的规范化原则，旨在减少数据冗余和提高数据的一致性。这些范式被命名为第一范式（1NF）、第二范式（2NF）和第三范式（3NF）。以下是它们的简要介绍：

  1. 第一范式（1NF）：
    • 数据表中的每个列都应该包含原子性的值，即每个列不能包含多个值或重复的值。
    • 每个表必须有一个主键来唯一标识每一行。
  2. 第二范式（2NF）：
    • 在 1NF 的基础上，要求每个非主键列完全依赖于整个主键，而不是部分依赖。
    • 如果一个表的主键由多个列组成，则每个非主键列必须依赖于所有主键列，而不是只依赖于部分主键列。
  3. 第三范式（3NF）：
    • 在 2NF 的基础上，要求每个非主键列之间没有传递依赖关系。
    • 换句话说，如果一个非主键列依赖于另一个非主键列，那么这两个非主键列都应该成为一个新的表，并且非主键列与主键之间建立关联。

通过遵循这些范式，可以保持数据库结构的合理性和一致性，避免冗余数据和数据更新异常，提高数据库的性能和可维护性。需要注意的是，在实际设计数据库时，有时需要根据具体的业务需求和查询优化等因素来权衡是否严格遵循三大范式。有时会允许部分冗余数据以提高查询性能或简化查询操作。

# 6.什么是视图?

在 MySQL 数据库中，视图(View)是一个命名的虚表，它由一个 SQL 查询来定义，可以当做表使用。与持久表(permanent table)不同的是，视图中的数据没有实际的物理存储。起到安全层的作用，不用关心原表的全部字段。

可更新视图:视图是可以被更新的.根据视图更新基本表。

物化视图:存在物理存储。

# 7.常见约束

在 MySQL 中，常见的约束用于限制表中数据的有效性和完整性。以下是 MySQL 中常见的约束类型：

  1. 主键约束（Primary Key Constraint）：用于唯一标识表中的每一行。主键字段的值必须是唯一的，且不能为 NULL。一个表只能有一个主键。
  2. 唯一约束（Unique Constraint）：确保一个字段或一组字段的值在表中是唯一的，但允许字段为空值（NULL）。
  3. 外键约束（Foreign Key Constraint）：用于确保表中的数据与另一个表中的数据保持引用完整性。外键约束通常用于创建表之间的关系。
  4. 非空约束（NOT NULL Constraint）：确保字段不允许为空值（NULL）。
  5. 默认约束（Default Constraint）：指定字段的默认值，如果在插入数据时没有提供该字段的值，则使用默认值。
  6. 检查约束（Check Constraint）：用于指定数据必须满足的条件。只有满足条件的数据才能被插入或更新到表中。

约束和索引的区别，约束更是是一个逻辑的概念，用来保证数据的完整性，而索引是一个数据结构，既有逻辑上的概念，在数据库中还代表着物理存储的方式。

# 二.进阶认识

# 1.MySQL 体系结构

从下图可以看出， MySQL 主要由下面几部分构成：

  • 连接器： 身份认证和权限相关(登录 MySQL 的时候)。
  • 查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
  • 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
  • 优化器： 按照 MySQL 认为最优的方案去执行。
  • 执行器： 执行语句，然后从存储引擎返回数据。 执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。
  • 插件式存储引擎 ： 主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎。

image-20231022233026591

# 2.其他存储引擎

  • innodb:支持行锁，支持事务
  • myisam:不支持行锁，支持表锁，不支持事务
  • NDB:集群存储引擎，将数据全部放在内存中，不是磁盘中
  • Memory:数据存放在内存，适合纬度表，使用 hash 索引
  • Archive:只支持 insert 和 update 操作.高速的插入和压缩
  • Maria:myisam 的后续版本

# 3.数据库语言

① 数据查询语言（Data Query Language，DQL）：

  • DQL 主要用于数据的查询，其基本结构是使用 SELECT 子句，
  • FROM 子句和 WHERE 子句的组合来查询一条或多条数据。

② 数据操作语言（Data Manipulation Language，DML）：

  • DML 主要用于对数据库中的数据进行增加、修改和删除的操作，其主要包括：
  • INSERT：增加数据
  • UPDATE：修改数据
  • DELETE：删除数据

③ 数据定义语言（Data Definition Language，DDL）：

  • DDL 主要用针对是数据库对象（表、索引、视图、>触发器、存储过程、函数、表空间等）进行创建、修改和删除操作。其主要包括：
  • CREATE：创建数据库对象
  • ALERT：修改数据库对象
  • DROP：删除数据库对象

④ 数据控制语言（Data Control Language，DCL）：

  • DCL 用来授予或回收访问数据库的权限，其主要包括：
  • GRANT：授予用户某种权限
  • REVOKE：回收授予的某种权限

⑤ TCL 用于数据库的事务管理。其主要包括：

  • START TRANSACTION：开启事务
  • COMMIT：提交事务
  • ROLLBACK：回滚事务
  • SET TRANSACTION：设置事务的属性

SQL 语言共分为以下几大类：查询语言 DQL，控制语言 DCL，操纵语言 DML，定义语言 DDL。事务控制 TCL.

  • DQL（Data QUERY Languages）语句：即数据库定义语句，用来查询 SELECT 子句，FROM 子句，WHERE 子句组成的查询块，比如：select–from–where–grouop by–having–order by–limit
  • DDL（Data Definition Languages）语句：即数据库定义语句，用来创建数据库中的表、索引、视图、存储过程、触发器等，常用的语句关键字有：CREATE,ALTER,DROP,TRUNCATE,COMMENT,RENAME。增删改表的结构
  • DML（Data Manipulation Language）语句：即数据操纵语句，用来查询、添加、更新、删除等，常用的语句关键字有：SELECT,INSERT,UPDATE,DELETE,MERGE,CALL,EXPLAIN PLAN,LOCK TABLE,包括通用性的增删改查。增删改表的数据
  • DCL（Data Control Language）语句：即数据控制语句，用于授权/撤销数据库及其字段的权限（DCL is short name of Data Control Language which includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system.）。常用的语句关键字有：GRANT,REVOKE。
  • TCL（Transaction Control Language）语句：事务控制语句，用于控制事务，常用的语句关键字有：COMMIT,ROLLBACK,SAVEPOINT,SET TRANSACTION。

# 4.什么是异步 IO

为了提高磁盘操作性能，当前的数据库系统都采用异步 IO(Asynchronous Input/Output，AIO)的方式来处理磁盘操作。InnoDB 存储引擎亦是如此。与 AIO 对应的是 Sync IO，即每进行一次 IO 操作，需要等待此次操作结束才能继续接下来的操作。

异步IO:用户可以在发出一个 IO 请求后立即再发出另一个 IO 请求，当全部请求发送完毕后，等待所有 IO 操作的完成，这就是 AIO。

AIO 的另一个优势是可以进行 IO Merge 操作，也就是将多个 IO 合并为 1 个 IO，这样可以提高 IOPS 的性能。例如用户需要访问页的(space，page_no)为以下页，每个页的大小为 16KB，那么同步 IO 需要进行 3 次 IO 操作。而 AIO 会判断到这三个页是连续的(显然可以通过(space，page_no)得知。因此 AIO 底层会发送一个 IO 请求，从(8，6)开始，读取 48KB 的页。

(8，6)、(8，7)，(8，8)
1

# 5.SQL 运行顺序?

sql 语句执行过程

#编写过程
select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...
#解析过程
from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...
1
2
3
4

查询 sql 的关键字书写顺序

 select from where group by having order by limit
1

查询 sql 的关键字执行顺序

 from where group by having select order by limit
1

编写技巧 根据需求挑选关键字，按照书写顺序依次排列关键字 按照关键字的执行顺序填空

image-20231020114245836

# 6.JDBC 操作流程?

  1. 加载数据库驱动类
  2. 打开数据库连接
  3. 执行 sql 语句
  4. 处理返回结果
  5. 关闭资源
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

public class JDBCSample {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            // 1. 建立数据库连接
            String url = "jdbc:database_type://host:port/database_name";
            String user = "username";
            String password = "password";
            connection = DriverManager.getConnection(url, user, password);

            // 2. 创建SQL语句
            String sql = "SELECT * FROM my_table";

            // 3. 创建Statement对象并执行SQL语句
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);

            // 4. 处理结果集
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                System.out.println("ID: " + id + ", Name: " + name);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                // 5. 关闭资源
                if (resultSet != null) resultSet.close();
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

# 7.QPS 和 TPS

QPS（Queries Per Second）和 TPS（Transactions Per Second）是衡量系统性能的指标，用于描述系统每秒处理的查询或事务数量。虽然它们在很多情况下是类似的概念，但在某些情况下有一些细微的区别。

  1. QPS（Queries Per Second）：QPS 是指系统每秒处理的查询请求数量。这个指标通常用于描述数据库、Web 服务器、缓存服务器等系统的查询吞吐量。在数据库中，一个查询可以是一个 SELECT、UPDATE、INSERT 或 DELETE 操作。
  2. TPS（Transactions Per Second）：TPS 是指系统每秒处理的事务数量。这个指标通常用于描述事务性应用程序（如银行交易、在线支付等）的处理能力。在这种情况下，一个事务通常涉及多个查询和更新操作，因此 TPS 通常比 QPS 更小。

需要注意的是，QPS 和 TPS 都是衡量系统处理能力的指标，但它们不能独立地表示系统性能的好坏。实际上，系统的性能取决于很多因素，包括硬件配置、软件优化、网络延迟等等。

上次更新: 11/26/2024, 10:01:04 PM

02-MySQL基础命令 →