README.md · 谭仕杰/MySQL学习笔记

# MySQL学习笔记

## 介绍
MySQL学习笔记，包括SQL基本语法、MySQL基础知识，包括存储引擎、索引、视图、锁、优化、存储结构等MySQL主要的内容。

## MySQL基础

- 数据库：保存有组织的数据库的容器；

数据库管理系统（DBMS）：数据库软件，通过DBMS来创建和操纵数据库；

- 关系型数据库（RDBMS，Relational Database Management System）就是一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系（一对一、一对多、多对多）；常见的关系型数据库有**MySQL**、PostgreSQL、Oracle、SQL Server、SQLite；

- **连接器**：与数据库建立连接；

- **连接池**

- 背景：传统的数据库连接方式是，用户每次请求都要向数据库获取连接，而数据库连接的创建和关闭需要一定的开销。**频繁的建立、关闭数据库，会极大的降低系统的性能，增大系统的开销**，甚至成为系统的瓶颈。

- MySQL连接池是一个数据库连接管理器，它允许应用程序在需要时从预定义的一组连接中获取数据库连接，而不是为每个请求创建一个新的连接。这有助于**减少应用程序的资源占用，提高数据库性能和可伸缩性**。
  - 连接池管理连接的生命周期，并确保连接在不再需要时被正确回收，主要包括以下组件：
    - 连接池管理器：负责连接的创建，回收和管理。
    - 连接池配置：
      1. **最大连接数**：连接池中允许的最大连接数；
      2. **连接等待超时时间**：连接池中连接请求的等待时间；
      3. **连接验证：**连接池可以通过连接验证来确保连接池中的连接仍然有效。

- **InnoDB**存储引擎

![InnoDB存储引擎](images/69eb3fc1f8e44bacb841fe8917594f25.png)

- MySQL语句

```mysql
  use database; // 选择数据库
  
  # 检索
  select distinct username from users; # 检索不同的行
  select distinct username from users 
  limit 5; # 返回不多于5行
  select distinct username from users 
  limit 5,3; # 返回从行5开始的3行
  
  # 排序
  select username 
  from users order by username; # 按username的字母顺序升序排序
  select username 
  from users order by username desc; # 降序
  select id,username,email 
  from users order by id,username; # 对id相同的行使用username排序
  
  # 过滤数据
  select username from users where userid < 5 or userid > 10;
  select username from users where userid between 3 and 5;
  select username from users where username is null # 空值检查
  select username from users 
  where userid < 5 and username = 'xxx';
  select username from users where id in(10,12);
  
  # 使用通配符过滤
  
  # 百分号( % )通配符：表示任意字符出现任意次数
  select id,uername from users 
  where username like 'li%'; #检索以li起头的词
  # 下划线( _ )通配符：表示任意字符出现一次，即只匹配一个字符
  select id,uername from users 
  where username like '_i ming'; #检索Xi ming 格式的词
  
  # 使用正则表达式搜索
  select id from users 
  where regexp '.000'; # .表示任意字符
  select id from users 
  where regexp '1000 | 2000';
  
  # 拼接字段
  select Concat(information,'(',age,')') from users; # 输出实例：ming(20)
  # 使用别名
  select username as customer_name from users;
  
  # 汇总数据
  # AVG() COUNT() MAX() MIN() SUM()
  select AVG(price) as avg_price from products;
  select COUNT(*) as num_pro from products; # 对表中的所有行的数目计数（包括NULL）
                                           # COUNT(COLUMN)：对特定的列中具有值的行进行计数，忽略NULL
                                           # SUM(COLUMN)：返回指定列值的和
  
  # 插入数据
  insert into users(username,email) 
  values('mike','mike@test.com'); 
  
  insert into users(username,email) 
  values('mike','mike@test.com'), 
  values('jack','jack@test.com'); # 列名和次序相同时，可以同时插入多行
  
  # 更新数据
  update users
  set username = 'xxx',email = 'xx@xxx.com'
  where id = 1000;
  
  # 删除数据
  delete from users
  where id = 1000;
  
  # !!! 如果不带where字句，则会删除或更新所有行
  ```

- **创建和操纵表**

```mysql
  # 创建表
  create table users
  (
      id            int        not NULL auto_increment, # 自动增量
      username      char(50)   not NULL,
      age           int        not NULL default 18, #使用默认值
      email         char(50)   NULL,
      passward      char(50)   NULL,
      primary key(id) # 定义主键
  )engine=InnoDB;
  
  # 更新表
  alter table users
  add phone_number char(20); # 表中插入新的列，常用来添加外键
  
  alter table users
  drop column phone_number;# 删除表中的列
  
  # 删除表
  drop table users;
  ```

- **子查询**

- **查询**：任何sql语句都是查询，但术语一般指select；

- 子查询：即嵌套在其他查询中的查询；

```mysql
    select username from users 
    where id in( select id from users
                where username like 'li%'
    ); # 由内向外执行
    ```

- 联结表

- **外键**（foreign key）：外键为某个表中的一列，它包含了另一个表的主键值；
  - 如果数据从存储在多个表中，使用**联结**检索数据

```mysql
  # users 和 products两个表
  select username,prod_name,prod_price
  from users,products
  where users.prod_id = products.prod_id  # 为避免歧义，使用完全限定列名
  order by username,prod_name;
  ```

- 视图

- 视图是一种虚拟的表格，它**并不真实存在于数据库中**，但可以像真实的表格一样被查询、更新和操作。

- 特点：

- 视图是虚拟的，不实际存在于数据库中；

- 隐藏底层数据表中的某些列或行，从而简化复杂的SQL操作；
    - 加强数据的安全性，防止用户对敏感数据进行误操作；

- 使用存储过程

- 存储过程：为以后使用而保存的一条或多条MySQL语句的集合。
  - 特点：
    - 可重复使用；
    - 提高性能；
    - 安全性高；
    - 支持事务；
  - 简单来讲，就是数据库 SQL 语言层面的代码封装与重用；

- 使用游标

- 游标（Cursor）是一种数据库对象，用于在SQL语句执行过程中，对结果集进行逐行处理。游标可以看作是一个指向结果集中某一行的指针，程序可以使用游标来逐行读取或更新结果集中的数据。
  - MySQL游标**只能用于存储过程**；

```mysql
  -- 定义存储过程
  CREATE PROCEDURE count_product_sales()
  BEGIN
      -- 定义游标
      DECLARE cur CURSOR FOR SELECT product_id FROM product;
      -- 定义变量
      DECLARE product_id INT;
      DECLARE sales_count INT;
      -- 打开游标
      OPEN cur;
      -- 循环读取数据
      WHILE TRUE DO
          -- 读取数据
          FETCH cur INTO product_id;
          -- 判断是否读取完毕
          IF (SQLSTATE = '02000') THEN
              LEAVE WHILE;
          END IF;
          -- 计算销售数量
          SELECT COUNT(*) INTO sales_count FROM order WHERE product_id = cur_product_id;
          -- 更新产品表格
          UPDATE product SET sales_count = sales_count + sales_count WHERE id = cur_product_id;
      END WHILE;
      -- 关闭游标
      CLOSE cur;
      -- 销毁游标
      DEALLOCATE cur;
  END
  ```

- 管理事务处理

- **事务处理**：是一种机制，用来维护数据的完整性，它保证成段的MySQL操作要么完全执行，要么完全不执行。（要么成功，要么不成功）

- 关键词：

- 事务（transaction）：指一组SQL语句；
    - 回退（rollback）：撤销指定SQL语句的过程；
    - 提交（commit）：指将未存储的SQL语句结果写入数据库表；
    - 保留点（savepoint）：事务处理中设置的临时占位符；

- 使用`rollback`

```mysql
    start transaction;
    delete from users;
    rollback; # 回退 start transaction 之后的所有语句
    ```

显然，`rollback`只能在一个事务内使用；

可以回退`insert`，`update`, `delete`语句，不能回退`select` 语句（也没意义）。

- 使用`commit`

- 一般的MySQL语句都是针对数据库表执行和操作的，提交操作是自动的，即**隐含提交**；
    - 事务处理块中必须进行明确的提交，使用`commit`

```mysql
    start transaction;
    delete from users where id = 1;
    delete from articles where id = 1;
    commit; # 如果以上某条语句失败，则commit语句失效
    ```

- **隐含事务关闭**：当`rollback` 和`commit`执行后，事务会自动关闭（将来的操作变为隐含提交）

- 使用`savepoint`

```mysql
    start transaction;
    delete from users where id = 1;
    savepoint delete1;
    delete from articles where id = 1;
    rollback to delete1;
    ```

## MySQL存储引擎

- **InnoDB存储引擎**简介：

- 特点：**支持事务，行锁设计，支持外键，支持非锁定读；**
  - 将数据存放在一个逻辑的表空间中，由InnoDB自身管理；将InnoDB存储引擎的表单独存放到一个独立的 `ibd `文件中。
  - InnoDB通过使用多版本并发控制（**MVCC**）来获得高并发性；
  - 实现了SQL标准的四种隔离级别，默认为`REPEATABLE`级别; 
  - 使用一种被称为`next-key locking` 的策略来避免幻读（`phantom`）现象的产生。
  - 提供了插入缓存（`insert buffer`），二次写（`double write`），自适应哈希索引（`adaptive hash index`），预读（`read ahead`）等高性能和高可用的功能。

- **InnoDB体系架构**

![InnoDB体系架构](images/2024250-20200625155335442-1744230825.png)

- **内存池**：

- 维护进程和线程需要访问的多个内部数据结构；
    - 缓存磁盘上的数据，在磁盘文件被修改之前进行缓存；
    - 重做日志（`redo log`）缓存；

- **后台线程：**InnoDB是**多线程**的模型，因此有多个不同的后台进程，负责处理不同任务。

- 主要作用：负责刷新内存池中的数据；将已修改的文件刷新到磁盘文件；保证在数据库发生异常的情况下InnoDB能恢复到正常的状态。
    - `Master Thread`：负责将缓冲池中的数据异步刷新到磁盘，保证数据一致性，包括脏页的刷新，合并插入缓存，UNDO页回收；
    - `IO Thread`：负责IO请求的回调处理；
    - `Purge Thread`：回收已经使用并分配的UNDO页；设置purge Thread可以减轻Master Thread的工作；
    - `Page Cleaner Thread`：脏页的刷新操作；

- 内存

- **缓冲池**：一块内存区域，磁盘读取较慢，因此数据库进行读取页操作时，如果该页在缓冲池中，则称该页被命中，直接读取该页；否则，读取磁盘上的页；**InnoDB存储引擎中，缓冲池中页的大小默认为16KB**；

**缓冲池的设计目的是为了协调CPU速度与磁盘速度之间的鸿沟**。

- 缓冲池的管理：**LRU**（Latest Recently Used）即最频繁使用的页在LRU列表的前端，而最少使用的在尾端；

- LRU列表中的页被修改之后，称该页为**脏页**（dirty page），即此时**缓冲池中的页和磁盘上的页的数据产生不一致**。

此时数据库会**通过`CHECKPOINT`机制将脏页刷新回磁盘**；

- **重做日志缓冲**：将重做日志信息按一定频率刷新到重做日志文件中；

- `CheckPoint`技术

- 为了避免数据丢失的情况发生，当前事务数据库系统普遍采用了**Write Ahead Log**的策略，即**当前事务提交时，先写重做日志，再修改页。** 当发生宕机而导致数据丢失时，通过重做日志来完成数据的恢复。

- **索引**

- 索引是一种用于快速查询和检索数据的**数据结构**，其本质可以看成是一种排序好的数据结构。

- 分类

- 按数据结构：哈希索引，B+树索引，B树索引；
    - 按底层存储方式：聚集索引，非聚集索引；
    - 按应用维度：主键索引，二级索引；

- 索引优缺点：

- 优点：加快数据检索速度；创建唯一索引可以保证数据唯一性；
    - 缺点：数据更新时，索引也需要更新；耗费一定空间；

- **哈希索引**

- 哈希表是键值对的集合，通过键(key)即可快速取出对应的值(value)，时间复杂度接近O(1)。

```java
      hash = hashfunc(key)
      index = hash % array_size
      ```

哈希算法有个 **Hash 冲突** 问题，也就是说多个不同的 key 最后得到的 index 相同。通常情况下，我们常用的解决办法是 **链地址法**。链地址法就是将哈希冲突数据存放在链表中。

- 为了减少 Hash 冲突的发生，一个好的哈希函数应该“**均匀地**”将数据分布在整个可能的哈希值集合中。

- **为什么 MySQL 没有使用其作为索引的数据结构呢？**主要是因为 Hash 索引**不支持顺序和范围查询**。

- **B树&B+树**

- **B 树& B+树两者有何异同呢？**
      - B 树的所有节点既存放键(key) 也存放数据(data)，而 B+树**只有叶子节点存放 key 和 data**，其他内节点只存放 key。
      - B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
      - B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的**顺序检索**很明显。

- **主键索引（primary key）**

- 一张数据表有只能有一个主键，并且主键不能为 null，不能重复。
    - 在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

- 二级索引（辅助索引）

- 二级索引的叶子节点存储的数据是主键，也就是说通过**二级索引可以定位主键的位置**。
    - 分类：
      - **唯一索引**(Unique Key)：唯一索引的属性列**不能出现重复的数据**，但是**允许数据为 NULL**，一张表**允许创建多个**唯一索引。 建立唯一索引的目的是为了该属性列的**数据的唯一性**。
      - 普通索引(Index)：普通索引的唯一作用就是为了**快速查询数据**，一张表**允许创建多个普通索引，并允许数据重复和 NULL。**
      - 前缀索引(Prefix)：只适用于字符串类型的数据。
      - 全文索引(Full Text)：为了检索大文本数据中的关键字的信息。

- 聚集索引

- 聚集索引即**索引结构和数据一起存放的索引**，并不是一种单独的索引类型。InnoDB 中的**主键索引**就属于聚簇索引。
    - 优缺点
      - 优点：查询速度快；对于主键的排序查找和范围查找速度非常快；叶子节点的数据即用户所需数据；
      - 缺点：如果索引数据不是有序的，需要在插入时进行排序；

- 非聚集索引

- 非聚集索引即**索引结构和数据分开存放**的索引，并不是一种单独的索引类型。**二级索引**就属于非聚簇索引。
    - 优缺点
      - 优点：由于叶子节点不存放数据，**更新代价比聚集索引要小** ；
      - 缺点：**需要二次查询**，先找到主键，再根据主键找到数据；

- **自适应哈希索引**

- 数据库本身创建并使用的；
    - **适合用于等值查询，对于范围查询无能为力；**

- 使用索引的一些tips：

- 选择合适的字段作为索引：
      - 尽量不为Null；
      - 被频繁查询的字段；
      - 频繁需要排序的字段，可以利用索引的排序，加快排序速度；
      - 避免使用频繁更新的字段，因为维护索引的成本也不小；

- **锁(Lock)**

- 锁机制为了支持**对共享资源进行并发访问，提供数据的一致性和完整性**；

- lock的对象是事务，用来锁定的是数据库中的对象，如表，页，行。并且一般lock的对象仅在事务`commit`或`rollback`后进行释放。

- **行级锁**

- **共享锁**（S Lock）：允许事务**读**一行数据；

- **排他锁**（X Lock）：允许事务**删除或更新**一行事务；

- 当一个事务锁定一个行时，其他事务也可以在该行上进行读取操作（**锁兼容**），但是不能进行修改操作（**锁不兼容**），直到持有锁的事务提交或回滚。

|      |   X    |   S    |
      | :--: | :----: | :----: |
      |  X   | 不兼容 | 不兼容 |
      |  S   | 不兼容 |  兼容  |

- **意向锁**

- 数据库的层次结构：数据库 $\rightarrow$ 表 $\rightarrow$ 页 $\rightarrow$ 记录(Row)      (粒度：粗 $\rightarrow$ 细)
    - 意向锁意味着事务希望在更细粒度上进行上锁；
    - **意向共享锁**（IS Lock）：事务想要获得一张表中某几行的共享锁；（多行）
    - **意向排他锁**（IX Lock）：事务想要获得一张表中某几行的排他锁；（多行）
    - **意向锁不会与行级的共享 / 排他锁互斥！！！**

- **一致性非锁定读**

- 如果读取的行正在执行删除或更新操作，这时**不需要等待行上X锁的释放**，而是读取行的一个**快照数据**（即存储在undo log 中的该行的历史数据）
    - 一个行记录可能有不止一个快照数据，一般称之为行多版本技术；由此带来的并发控制，称为**多版本并发控制(Multi Version Concurrency Control, MVCC)**

- **事务隔离级别**

- SQL 标准定义了四个隔离级别：

- **READ-UNCOMMITTED(读取未提交)** ： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
      - **READ-COMMITTED(读取已提交)** ： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
      - **REPEATABLE-READ(可重复读)**(默认配置) ： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
      - **SERIALIZABLE(可串行化)** ： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

|            | READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE |
      | :--------: | :--------------: | :------------: | :-------------: | :----------: |
      |    脏读    |                  |      解决      |      解决       |     解决     |
      | 不可重复读 |                  |                |      解决       |     解决     |
      |    幻读    |                  |                |                 |     解决     |

！！！**从左到右，隔离级别越高，并发性能越差**；

- InnoDB 实现的 REPEATABLE-READ 隔离级别其实是可以**解决幻读**问题发生的，主要有下面两种情况：

- **快照读** ：由 MVCC 机制来保证不出现幻读。
      - **当前读** ： 使用 Next-Key Lock 进行加锁来保证不出现幻读，Next-Key Lock 是行锁（Record Lock）和间隙锁（Gap Lock）的结合，行锁只能锁住已经存在的行，为了避免插入新行，需要依赖间隙锁。

- **死锁**：

- **多个事务在执行过程中，因争夺资源而造成的一种互相等待的现象**；
    - 解决死锁最简单的方式：**设置超时**；

- **乐观锁和悲观锁**

- 乐观锁和悲观锁都是并发控制的技术，用于在多个线程或进程同时访问共享资源时保证数据的一致性和完整性。
    - **悲观锁**认为在并发访问情况下，数据很可能会被其他线程修改，所以在**访问共享资源之前先获取锁，防止其他线程同时修改，从而保证数据的一致性**。悲观锁的实现方式包括数据库中的行级锁、表级锁、读写锁等。
    - **乐观锁**则认为在并发访问情况下，数据很少被其他线程修改，所以**不需要在访问共享资源之前获取锁，而是在更新数据时检查数据是否被其他线程修改过**，如果没有则更新，否则放弃更新。乐观锁的实现方式包括版本号控制、时间戳控制等。
    - **悲观锁在高并发场景下会导致性能瓶颈，因为获取锁需要等待其他线程释放锁**，而乐观锁则可以避免这种等待，但是**在数据竞争激烈的情况下，乐观锁的重试机制会导致性能下降。**

- **事务**

- **四大特性（ACID）**
    - **原子性（Atomicity）**
      - 事务中的所有操作需要像原子一样不可分割，要么全部执行成功，要么全部失败；
      - 实现：**undo log** 机制；
    - **一致性（Consistency）**
      - 数据库从一种状态转变为另一种一致的状态；
      - 如果某个操作失败了，系统可以自动撤销事务；
    - **隔离性（Isolation）**
      - 多个事务执行时彼此隔离，不可以互相干扰；
      - 写+写隔离：**锁机制**；
      - 写+读隔离：**MVCC**；
    - **持久性（Durability）**
      - 事务一旦提交，其结果的就是永久性的；
  - **三大问题**
    - **脏读**：事务A读取(`SELECT`)到了事务B未提交的数据；
    - **不可重复读**：事务A执行过程中，对同一个数据进行了两次读取(`SELECT`)，在这两次读取之间，事务B对该数据进行了更新(`UPDATE`)并完整提交，导致事务A两次读取到的数据不一致；
    - **幻读**：事务A执行过程中，对同一个集合数据进行两次读取(`COUNT`)，在这两次读取之间，事务B在该集合中增加(`INSERT`)或删除(`DELETE`)了部分数据，导致事务A两次读取到的行数据不一致；
  - **事务的实现**
    - **Redo Log**
      - `redo log`（重做日志）是`InnoDB`存储引擎独有的，它让`MySQL`拥有了崩溃恢复能力。
      - 比如 `MySQL` 实例挂了或宕机了，重启时，`InnoDB`存储引擎会使用`redo log`恢复数据，保证数据的**持久性**与完整性。
    - **Undo Log**
      - 我们知道如果想要保证事务的原子性，就需要在异常发生时，对已经执行的操作进行**回滚**，在 MySQL 中，恢复机制是通过**回滚日志（undo log）**实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后再执行相关的操作。如果执行过程中遇到异常的话，我们直接利用 **回滚日志** 中的信息将数据回滚到修改之前的样子即可！并且，回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库的时候，数据库还能够通过查询回滚日志来回滚将之前未完成的事务。
      - 简单来说，有两个作用：
        - 当事务回滚时用于将数据恢复到修改前的样子
        - 另一个作用是 `MVCC` ，当读取记录时，若该记录被其他事务占用或当前版本对该事务不可见，则可以通过 `undo log` 读取之前的版本数据，以此实现非锁定读；

谭仕杰/MySQL学习笔记

简介

发行版

贡献者

近期动态

谭仕杰/MySQL学习笔记 .gitee-modal { width: 500px !important; }

简介

发行版

贡献者

近期动态

搜索帮助

谭仕杰/MySQL学习笔记