MySQL深入浅出

MySQL其实没那么神秘，也就是一个应用程序，他像其他程序一样共用电脑的CPU内存以及硬盘，但是他主要的作用就是提供了一套很适用于软件数据的数据存储结构存储方案，以及完善的增删改查机制，和一些高级功能能适配很多场景的开发

数据库设计的三大范式

第一范式：原子性

存储的数据应该具有不可再分性

第二范式：唯一性

非主键字段的值必须完全依赖主键

一般这种情况需要做分表，下图为User表但是包含了课程的信息，非主键字段(课表信息的字段)并未全依赖主键字段，这是冗余的应该拆分出来

第三范式：独立性

消除传递依赖(非主键值不依赖于另一个非主键值)

非主键字段之间产生依赖

修改

E-R图

E-R图，也称为实体关系图，用于显示实体集之间的关系。它提供了一种表示实体类型、属性和连接的方法；用来描述现实世界的概念模型。ER模型是数据库的设计或蓝图，将来可以作为数据库来实现。

包括：实体、属性、关系

MySQL知识小站

MySQL中的聚簇索引、非聚簇索引、联合索引和唯一索引_mysql唯一索引是聚簇索引吗-CSDN博客

设计库模板

1
2
3
4
5
6
7

CREATE TABLE `product`  (
  `id` int(11) NOT NULL,
  `product_no` varchar(20)  DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `price` decimal(10, 2) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

在这个数据类型中：

• 10 表示数字的总位数（包括小数点前的部分和小数点后的部分）。
• 2 表示小数点后的位数。

开篇第一问

一个场景题：一个Mysql单表，列是 （唯一id，订单类型，订单时间，金额），一天有三千万条数据，现在要将昨天的数据打印出来，怎么设计索引？具体怎么操作？然后现在要根据订单类型计算每种订单类型的金额总和，怎么操作？

提示：根据时间分，提交多线程；随后追问怎么计算金额总和

对于给定的场景，我们需要设计一个有效的索引策略，以便能够快速检索昨天的数据，并且还要计算每种订单类型的金额总和。

设计索引

考虑到查询需求主要是基于订单时间（假设为order_time字段）和订单类型（假设为order_type字段），我们可以设计一个复合索引来优化查询。

1

CREATE INDEX idx_order_time_type ON your_table_name(order_time, order_type);

这个索引首先按照order_time排序，对于相同的时间，再按照order_type排序。这样设计有几个好处：

1. 范围查询优化：当我们需要查询某个时间段内的数据时（例如昨天的数据），数据库可以利用索引快速定位到这个时间范围。
2. 覆盖索引：如果查询只需要order_time和order_type字段，那么MySQL可以直接使用索引来满足查询，而无需回表查找。
3. 排序优化：如果查询需要对结果进行排序，并且排序顺序与索引顺序一致，那么排序操作将非常高效。

打印昨天的数据

要打印昨天的数据，我们可以使用以下SQL语句：

1
2
3
4
5
6

#CURDATE() - INTERVAL 1 DAY
#CURDATE()函数返回当前日期（不包含时间部分）。
#- INTERVAL 1 DAY从当前日期中减去一天，得到昨天的日期。
SELECT *
FROM your_table_name
WHERE order_time BETWEEN CURDATE() - INTERVAL 1 DAY AND CURDATE()

这里使用了BETWEEN语句来指定时间范围，从昨天的午夜开始到今天的午夜前一秒结束。确保order_time字段是日期时间类型（如DATETIME或TIMESTAMP），以便能够正确地进行比较。

根据订单类型计算金额总和

要计算每种订单类型的金额总和，我们可以使用GROUP BY语句和聚合函数SUM：

1
2
3
4

SELECT order_type, SUM(amount) AS total_amount
FROM your_table_name
WHERE order_time BETWEEN CURDATE() - INTERVAL 1 DAY AND CURDATE() - INTERVAL 1 SECOND
GROUP BY order_type;

这个查询会返回每种订单类型及其对应的金额总和。注意，这里我们同样使用了之前定义的时间范围来限制结果集只包含昨天的数据。

使用多线程

如果数据量非常大，并且服务器有足够的资源，可以考虑使用多线程来并行处理数据。然而，对于MySQL查询来说，通常不需要显式地创建多线程来处理，因为MySQL服务器内部已经为查询优化和执行做了大量的工作。如果确实需要利用多线程来提高性能，那么应该考虑在应用层实现，比如将时间范围划分成多个子区间，每个线程处理一个子区间的数据。

追问：怎么计算金额总和

在上面的SQL语句中，我们已经展示了如何计算金额总和。通过SUM(amount)，我们可以得到每种订单类型的总金额。这个操作是在SQL查询中直接完成的，MySQL会扫描符合条件的行，并对每一行的amount字段进行求和，然后按照order_type进行分组。

如果数据量非常大，计算过程可能会比较慢，这时可以考虑以下几个优化策略：

1. 确保索引有效：如上所述，确保order_time和order_type字段上有合适的索引，以加速数据的检索和分组。
2. 硬件和配置优化：根据服务器的硬件配置（如CPU、内存、磁盘I/O等）调整MySQL的配置参数，以优化查询性能。
3. 分区表：如果表非常大，并且经常需要按时间范围查询数据，可以考虑使用MySQL的分区表功能。通过将表按照时间范围进行分区，可以提高查询性能。
4. 定期汇总：如果订单数据的变更不频繁，可以考虑定期（如每天或每小时）计算并存储订单类型的金额总和，这样就不需要实时计算，而是直接查询预先计算好的结果。

记住，在实际应用中，最佳的做法通常取决于具体的数据量、查询频率、硬件资源以及业务需求。因此，在实施任何优化策略之前，最好先进行性能测试和分析，以确定最有效的方案。

应用层实现

在应用层使用多线程优化数据检索和计算操作，通常涉及将任务划分为多个子任务，并并行执行这些子任务以加快整体处理速度。对于数据库查询和计算订单类型金额总和的场景，可以通过以下步骤实现多线程优化：

1. 任务分解：

   • 首先，确定哪些部分的任务可以并行执行。在数据库查询的场景中，如果查询的数据量非常大，并且数据库服务器能够支持并发查询，那么可以将查询拆分成多个子查询，每个子查询负责检索一部分数据。
   • 对于计算订单类型金额总和的任务，如果每种订单类型的计算是独立的，那么也可以将这些计算任务分配给不同的线程并行执行。

2. 创建线程或线程池：

   • 根据需要创建足够数量的线程或线程池来执行子任务。使用线程池可以避免频繁地创建和销毁线程，从而提高性能。
   • 在Java中，可以使用ExecutorService和ThreadPoolExecutor来创建和管理线程池。

3. 分配任务：

   • 将子任务分配给线程池中的线程执行。这通常涉及到将查询条件或计算参数传递给线程。

4. 执行数据库查询：

   • 如果查询任务被拆分成多个子查询，每个线程将执行其分配的子查询，并获取结果集。
   • 确保数据库连接是线程安全的，或者为每个线程创建独立的数据库连接。

5. 计算金额总和：

   • 对于计算订单类型金额总和的任务，每个线程将处理其分配的订单类型，并计算相应的金额总和。
   • 可以使用线程安全的集合（如ConcurrentHashMap）来存储每个订单类型的计算结果，以便后续合并。

6. 合并结果：

   • 当所有线程完成其任务后，需要合并它们的结果。对于数据库查询，这通常意味着将各个子查询的结果集合并成一个完整的结果集。
   • 对于金额总和的计算，需要将各个线程计算的结果相加，得到每种订单类型的总金额。

7. 关闭线程池：

   • 一旦所有任务完成并且结果已经合并，关闭线程池以释放资源。

以下是一个简化的Java代码示例，展示了如何使用线程池并行执行数据库查询任务：

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
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76

import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;

// 假设有一个DatabaseClient类用于执行数据库查询
class DatabaseClient {
    public List<Order> queryOrdersByTimeRange(Date startTime, Date endTime) {
        // 执行数据库查询并返回结果
        return ...; // 返回订单列表
    }
}

// 假设Order类代表订单，包含订单类型和金额等字段
class Order {
    String orderType;
    double amount;
    // ... 其他字段和构造函数等
}

public class MultiThreadedExample {
    public static void main(String[] args) {
        ExecutorService executorService = Executors.newFixedThreadPool(4); // 创建一个固定大小的线程池
        DatabaseClient databaseClient = new DatabaseClient();
        List<Future<List<Order>>> futures = new ArrayList<>();

        // 假设我们按时间范围拆分查询任务
        Date startTime = ...; // 昨天的开始时间
        Date endTime = ...;   // 昨天的结束时间（或今天的开始时间）
        int taskCount = 4;    // 假设我们将查询拆分成4个任务
        long interval = (endTime.getTime() - startTime.getTime()) / taskCount;
		//！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
        // 好好学学别人如何多线程处理带返回值的任务！❀
        for (int i = 0; i < taskCount; i++) {
            Date taskStartTime = new Date(startTime.getTime() + i * interval);
            Date taskEndTime = new Date(Math.min(startTime.getTime() + (i + 1) * interval, endTime.getTime()));
            Future<List<Order>> future = executorService.submit(() -> {
                return databaseClient.queryOrdersByTimeRange(taskStartTime, taskEndTime);
            });
            futures.add(future);
        }

        // 等待所有任务完成并合并结果
        List<Order> allOrders = new ArrayList<>();
        for (Future<List<Order>> future : futures) {
            try {
                allOrders.addAll(future.get()); // 获取每个任务的结果并添加到总列表中，addAll()方法
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        // 关闭线程池
        executorService.shutdown();

        // 现在可以对allOrders进行处理，比如计算每种订单类型的金额总和等
         // 处理allOrders，计算每种订单类型的金额总和  
        Map<String, Double> totalAmountByOrderType = new HashMap<>();  
        for (Order order : allOrders) {  
            String orderType = order.getOrderType();  
            double amount = order.getAmount();  
  
            // 如果订单类型已经存在于map中，则累加金额；否则，初始化金额为当前订单的金额 ❀
            //！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
            //这一步getOrDefault()方法恰到好处，查询是否存在键，存在返回值，不存在初始化值为0
            totalAmountByOrderType.put(orderType, totalAmountByOrderType.getOrDefault(orderType, 0.0) + amount);  
        }  
  
        // 打印每种订单类型的金额总和：MAP的遍历方法！❀
        // ！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
        for (Map.Entry<String, Double> entry : totalAmountByOrderType.entrySet()) {  
            System.out.println("Order Type: " + entry.getKey() + ", Total Amount: " + entry.getValue());  
        }  
    }
}

索引查询 ❀

当你说“根据自己创建索引查询”，我理解为你想了解如何根据自己的需求在数据库中创建索引，并如何使用这些索引进行查询。以下是一个简单的例子，说明如何在关系型数据库中（以MySQL为例）创建索引并使用它进行查询。

创建索引

假设我们有一个名为users的表，其中包含id、name和email三个字段。如果我们经常根据email字段进行查询，那么为了提高查询效率，我们可以为email字段创建一个索引。

1. 使用CREATE INDEX语句创建索引：

1

CREATE INDEX idx_email ON users(email);

这条命令会在users表的email字段上创建一个名为idx_email的索引。

使用索引进行查询

一旦索引创建成功，你就可以在查询中使用它来提高性能。例如，如果你想根据email字段查找用户，你可以这样写查询：

1

SELECT * FROM users WHERE email = 'example@example.com';

MySQL优化器会自动决定是否使用idx_email索引来加速这个查询。如果它认为使用索引比全表扫描更快，它就会使用索引。

注意事项

• 选择性：索引的选择性越高，其效果通常越好。选择性是指不重复的索引值与总记录数的比率。例如，一个只有true和false两个值的字段可能不是一个好的索引候选，因为它的选择性很低。
• 维护成本：每次插入、更新或删除记录时，索引都需要被更新。因此，过多的索引会增加写操作的开销。
• 覆盖索引：如果一个查询只需要访问索引中的信息，而不需要回表访问数据行，那么这个查询就被称为“覆盖索引”查询。这种查询通常非常快。
• 避免过度索引：只为那些经常出现在WHERE子句、JOIN条件或ORDER BY子句中的列创建索引。

覆盖索引：

索引就是你需要查询的列话，就不需要在磁盘加载其他数据块了，直接在索引库找到数据返回，减少IO次数，效率非常高！

是指一个索引包含了查询所需的所有列，而无需访问表的实际数据页。当数据库系统执行查询时，通常需要从磁盘中读取数据页到内存中才能进行处理。而如果使用了覆盖索引，由于索引已经包含了查询所需的所有列的值，数据库系统可以直接通过索引来获取这些值，而不需要额外地读取数据页。这样可以减少磁盘I/O的次数和数据在内存中的占用，提高查询的效率。

最后，为了确定你的索引是否有效地提高了查询性能，你可以使用数据库的查询执行计划工具（如MySQL的EXPLAIN语句）来查看查询是如何使用索引的。

MySQL 执行流程是怎样的？

先来一个上帝视角图，下面就是 MySQL 执行一条 SQL 查询语句的流程，也从图中可以看到 MySQL 内部架构里的各个功能模块。

可以看到， MySQL 的架构共分为两层：Server 层和存储引擎层，

• Server 层负责建立连接、分析和执行 SQL。MySQL 大多数的核心功能模块都在这实现，主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等。）都在 Server 层实现。
• 存储引擎层负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。我们常说的索引数据结构，就是由存储引擎层实现的，不同的存储引擎支持的索引类型也不相同，比如 InnoDB 支持索引类型是 B+树 ，且是默认使用，也就是说在数据表中创建的主键索引和二级索引默认使用的是 B+ 树索引。

执行 SQL

解析器进行语法分析，确保SQL语句格式正确并且符合MySQL的语法规范

经过解析器后，接着就要进入执行 SQL 查询语句的流程了，每条SELECT 查询语句流程主要可以分为下面这三个阶段：

• prepare 阶段，也就是预处理阶段；
• optimize 阶段，也就是优化阶段；
• execute 阶段，也就是执行阶段；

预处理器

查询表是否存在！

优化器

1

explain select id from tb_user where id > 0 and nick_name = "小鱼同学";

优化器会在多种索引中使用效率最高的索引 !

很显然这条查询语句是覆盖索引，直接在二级索引就能查找到结果（因为二级索引的 B+ 树的叶子节点的数据存储的是主键值），就没必要在主键索引查找了，因为查询主键索引的 B+ 树的成本会比查询二级索引的 B+ 的成本大，优化器基于查询成本的考虑，会选择查询代价小的普通索引。

执行器

InnoDB作为存储引擎会调用read_record 方法来根据不同策略来搜素：

1. 当搜索条件包含主键索引时，read_record(const,id)方法逐步查询

1 2 3 4 5 6 7 8 9 10 11 12 13 14

int state = 0; while(state!=-1){ state = read_record(const,id); } Integet read_record(Enum enum,Long id){ //存储引擎通过主键索引的 B+ 树结构定位到 id = 1的第一条记录 if(isResultTrue(id)){ //将数据返回给客户端 ..... return -1; } return 0; }

2. 当不包含索引时，read_record(all)方法循环查询，查询到匹配的将一个一个返回到客户端

经历完优化器后，就确定了执行方案，接下来 MySQL 就真正开始执行语句了，这个工作是由「执行器」完成的。在执行的过程中，执行器就会和存储引擎交互了，交互是以记录为单位的。

执行器和存储引擎的交互过程：

• 主键索引查询
• 全表扫描
• 索引下推

主键索引查询

以本文开头查询语句为例，看看执行器是怎么工作的。

1

select * from product where id = 1;

这条查询语句的查询条件用到了主键索引，而且是等值查询，同时主键 id 是唯一，不会有 id 相同的记录，所以优化器决定选用访问类型为 const 进行查询，也就是使用主键索引查询一条记录，那么执行器与存储引擎的执行流程是这样的：

• 执行器第一次查询，会调用 read_first_record 函数指针指向的函数，因为优化器选择的访问类型为 const，这个函数指针被指向为 InnoDB 引擎索引查询的接口，把条件 id = 1 交给存储引擎，让存储引擎定位符合条件的第一条记录。
• 存储引擎通过主键索引的 B+ 树结构定位到 id = 1的第一条记录，如果记录是不存在的，就会向执行器上报记录找不到的错误，然后查询结束。如果记录是存在的，就会将记录返回给执行器；
• 执行器从存储引擎读到记录后，接着判断记录是否符合查询条件，如果符合则发送给客户端，如果不符合则跳过该记录。
• 执行器查询的过程是一个 while 循环，所以还会再查一次，但是这次因为不是第一次查询了，所以会调用 read_record 函数指针指向的函数，因为优化器选择的访问类型为 const，这个函数指针被指向为一个永远返回 - 1 的函数，所以当调用该函数的时候，执行器就退出循环，也就是结束查询了。

至此，这个语句就执行完成了。

全表扫描

举个全表扫描的例子：

1

select * from product where name = 'iphone';

这条查询语句的查询条件没有用到索引，所以优化器决定选用访问类型为 ALL 进行查询，也就是全表扫描的方式查询，那么这时执行器与存储引擎的执行流程是这样的：

• 执行器第一次查询，会调用 read_first_record 函数指针指向的函数，因为优化器选择的访问类型为 all，这个函数指针被指向为 InnoDB 引擎全扫描的接口，让存储引擎读取表中的第一条记录；
• 执行器会判断读到的这条记录的 name 是不是 iphone，如果不是则跳过；如果是则将记录发给客户的（是的没错，Server 层每从存储引擎读到一条记录就会发送给客户端，之所以客户端显示的时候是直接显示所有记录的，是因为客户端是等查询语句查询完成后，才会显示出所有的记录）。
• 执行器查询的过程是一个 while 循环，所以还会再查一次，会调用 read_record 函数指针指向的函数，因为优化器选择的访问类型为 all，read_record 函数指针指向的还是 InnoDB 引擎全扫描的接口，所以接着向存储引擎层要求继续读刚才那条记录的下一条记录，存储引擎把下一条记录取出后就将其返回给执行器（Server层），执行器继续判断条件，不符合查询条件即跳过该记录，否则发送到客户端；
• 一直重复上述过程，直到存储引擎把表中的所有记录读完，然后向执行器（Server层） 返回了读取完毕的信息；
• 执行器收到存储引擎报告的查询完毕的信息，退出循环，停止查询。

索引下推

减少IO开销

索引下推是一种查询优化技术，它允许数据库引擎在索引扫描期间直接过滤掉不符合条件的记录，而不是先返回所有的索引项，再在应用程序级别或数据库的更高层进行过滤。这意味着更多的过滤工作是在索引层面完成的，减少了不必要的数据传输，提高了查询效率

索引下推能够减少二级索引在查询时的回表操作，提高查询的效率，因为它将 Server 层部分负责的事情，交给存储引擎层去处理了。

举一个具体的例子，方便大家理解，这里一张用户表如下，我对 age 和 reward 字段建立了联合索引（age，reward）：

现在有下面这条查询语句：

1

select * from t_user  where age > 20 and reward = 100000;

联合索引当遇到范围查询 (>、<) 就会停止匹配，也就是 age 字段能用到联合索引，但是 reward 字段则无法利用到索引。具体原因这里可以看这篇：索引常见面试题(opens new window)

那么，不使用索引下推（MySQL 5.6 之前的版本）时，执行器与存储引擎的执行流程是这样的：

• Server 层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到 age > 20 的第一条记录；
• 存储引擎根据二级索引的 B+ 树快速定位到这条记录后，获取主键值，然后进行回表操作，将完整的记录返回给 Server 层；
• Server 层在判断该记录的 reward 是否等于 100000，如果成立则将其发送给客户端；否则跳过该记录；
• 接着，继续向存储引擎索要下一条记录，存储引擎在二级索引定位到记录后，获取主键值，然后回表操作，将完整的记录返回给 Server 层；
• 如此往复，直到存储引擎把表中的所有记录读完。

可以看到，没有索引下推的时候，每查询到一条二级索引记录，都要进行回表操作，然后将记录返回给 Server，接着 Server 再判断该记录的 reward 是否等于 100000。

而使用索引下推后，判断记录的 reward 是否等于 100000 的工作交给了存储引擎层，过程如下 ：

• Server 层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到 age > 20 的第一条记录；
• 存储引擎定位到二级索引后，先不执行回表操作，而是先判断一下该索引中包含的列（reward列）的条件（reward 是否等于 100000）是否成立。如果条件不成立，则直接跳过该二级索引。如果成立，则执行回表操作，将完成记录返回给 Server 层。
• Server 层在判断其他的查询条件（本次查询没有其他条件）是否成立，如果成立则将其发送给客户端；否则跳过该记录，然后向存储引擎索要下一条记录。
• 如此往复，直到存储引擎把表中的所有记录读完。

可以看到，使用了索引下推后，虽然 reward 列无法使用到联合索引，但是因为它包含在联合索引（age，reward）里，所以直接在存储引擎过滤出满足 reward = 100000 的记录后，才去执行回表操作获取整个记录。相比于没有使用索引下推，节省了很多回表操作。

当你发现执行计划里的 Extr 部分显示了 “Using index condition”，说明使用了索引下推。

总结

执行一条 SQL 查询语句，期间发生了什么？

• 连接器：建立连接，管理连接、校验用户身份；
• 查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
• 解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
• 执行 SQL：执行 SQL 共有三个阶段：
  • 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
  • 优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；
  • 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

SQL知识加油站

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
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85

-- 查询字段不为null的数据（is null作用相反）
select name,age ,score from student where age is not null

-- 模糊查询姓李的人，%代表任意多少字符，_代表一个字符
select name,score from student where name like '李%'

-- 多条件查询：or、and、not
select name,score from student where name like '%李%' and not score > 500

-- 去重查询，多条件去重就会根据组合来去重：distinct
select distinct class_id ,exam_num from student 

-- 排序规则：order by desc、asc
select name,age,score from student order by score desc

-- 分页截取数据：limit
select name,age from student order by age limit 1,3

 -- 多条件筛选：case when(条件) then 干什么，用于查询赋值给自设字段
select
  name,
  case when(age>60) then '老同学' when(age<=60 and age>20) then '年轻'
  when(age<=20 or age is null) then '小同学' end as age_level
from
  student
order by name

-- 时间函数：date()当前日期、datetime()当前日期和时间、time()时间
select name,datetime() as current_date from student

-- 字符串处理，将英文大小写：upper(name)、lower(name)
select id,name,upper(name) as upper_name from student where name = '热dog'

-- 聚合函数：sum、avg、max、min
select sum(score) as total_score,avg(score) as avg_score,
max(score) as max_score,min(score) as min_score  from student

-- 分组聚合：group by
select class_id,avg(score) as avg_score from student group by class_id

-- 分组聚合：多条件分组 group by
select
  class_id,
  exam_num,
  count(*) as total_num
from
  student group by class_id,exam_num
  
 -- 分组之后判断：having （不同于where，where是分组之前判断）
select
  class_id,
  sum(score) as total_score
from
  student
group by
  class_id
having
  sum(score) > 150
  
 -- 关联查询：outer join （不用带on，outer join之后的表内容将会以笛卡尔积返回）
select
  s.name student_name,
  s.age student_age,
  s.class_id class_id,
  c.name class_name
from
  student s,
outer join class c;

 -- 关联查询：inner join (只返回两方的交集)
select
  stu.name as student_name,stu.age as student_age,stu.class_id,c.name as class_name,c.level as class_level
from
  student stu
inner join class c on stu.class_id = c.id;

-- 关联查询：left join (返回from表的所有值，join表的匹配值)
select 
  s.name student_name,s.age student_age,s.class_id,c.name class_name,c.level class_level
from 
  student s
left JOIN  class c on s.class_id = c.id;

聚合函数

sum、avg、max、min、count

根据from的表单数量来计算，如果有分组语句就按分组计算

聚合函数优先级＜where：聚合函数是写在SELECT子句中；根据前面的内容知道，是先执行WHERE子句筛选出符合条件的记录，然后再由SELECT获取结果集！！！所以，一般是可以先使用WHERE子句做筛选，然后使用聚合函数在筛选后的数据上做统计的

1
2

select sum(score) as total_score,avg(score) as avg_score,
max(score) as max_score,min(score) as min_score  from student

1
2
3
4
5
6
7
8
9
10
11
12
13
14

SELECT SUM(count) AS nums FROM access_log; -- count为列
-- 统计最近五年的每一年的nums总数 按年降序
-- 统计每一年的nums总数
-- -- 统计最近五年的每一年的nums总数 按年降序
SELECT
	sum( nums ),
	years 
FROM
	age 
GROUP BY
	years 
ORDER BY
	years DESC 
	LIMIT 5

HAVING

对分组之后的数据进行过程

• Having用于分组后对数据的过滤
• Where用于分组之前的数据过滤

SQL之母 - 免费SQL自学网站 by 程序员鱼皮 (yupi.icu)

1
2
3
4
5
6
7
8
9
10
11
12

 -- 请在此处输入 SQL
select
  class_id,
  sum(score) as total_score
from
  student
where
  class_id > 2
group by
  class_id
having
  sum(score) > 150

BETWEEN

要查询两个日期之间的数据，你可以使用SQL的BETWEEN操作符，配合日期字段来实现。假设你有一个名为your_table的表，其中有一个日期字段date_column，你可以使用以下SQL查询来获取两个日期之间的数据：

1
2
3

SELECT *
FROM your_table
WHERE date_column BETWEEN 'start_date' AND 'end_date';

在上面的查询中，你需要将your_table替换为你的实际表名，date_column替换为你的实际日期字段名。同时，将start_date和end_date替换为你要查询的起始日期和结束日期。

请确保你提供的日期格式与数据库中存储的日期格式相匹配。通常，日期格式可以是'YYYY-MM-DD'或数据库特定的其他格式。

另外，如果你想要包含起始日期和结束日期在内的数据，上面的查询已经足够了。如果你只想包含起始日期之前的数据，而不包括结束日期，你可以稍微修改查询如下：

1
2
3

SELECT *
FROM your_table
WHERE date_column >= 'start_date' AND date_column < 'end_date';

在这个查询中，我们使用>=操作符来包含起始日期，并使用<操作符来排除结束日期。这样，你将获得从起始日期开始到结束日期之前的数据。

NOT

作用与<>相同，常接在where后面使用

1. NOT简单使用

1
2

SELECT * FROM employees WHERE age <> 30;
SELECT * FROM employees WHERE NOT (age = 30);

2. NOT IN

1

SELECT * FROM orders WHERE order_id NOT IN (10248, 10249);

3. NOT Between

1

SELECT * FROM employees WHERE age NOT BETWEEN 25 AND 35;

4. NOT EXISTS

1

SELECT * FROM customers WHERE NOT EXISTS (SELECT NULL FROM orders WHERE orders.customer_id = customers.customer_id);

EXISTS

通常连接子查询

EXISTS用于检查子查询是否返回至少一行数据。当子查询返回至少一行数据时，EXISTS返回TRUE；如果没有返回任何行，则返回FALSE

1
2
3
4
5
6
7
8
9

-- 当用户至少有一个订单的时候返回true，这个时候因为Not的缘故true会变成fasle
-- 以此达到查询没有订单的用户的目的
SELECT *
FROM user
WHERE NOT EXISTS (
    SELECT 1
    FROM orders
    WHERE `user`.id_u = orders.id_u
);

1
2
3
4
5
6
7

SELECT *
FROM customers
WHERE EXISTS (
    SELECT 1
    FROM orders
    WHERE orders.customer_id = customers.customer_id AND orders.order_date > '2024-01-01'
);

LIMIT

1
2

-- 分页截取数据：limit 这里的0,3表示从偏移量为0（即第一条记录）开始取三条记录
select name,age from student order by age limit 0,3

ALL

ALL关键字通常用于子查询中，用来比较一个值与子查询结果集中的所有值。它与SOME或没有任何量词的效果不同。当使用ALL时，外部查询的条件必须满足子查询的所有结果，而不仅仅是其中一个。

1
2
3
4
5
6
7

-- 找出同事中薪水最高的那个人
SELECT name, salary
FROM salaries s1
WHERE s1.salary > ALL (
    SELECT salary
    FROM salaries s2
);

SOME/ANY

SOME关键字用于子查询时，意味着外部查询的条件只需要满足子查询结果中的至少一个值即可。这与ALL关键字形成对比，后者要求外部查询的条件必须满足子查询中的所有结果。

1
2
3
4
5
6
7
8

-- 找到那些订购数量至少大于等于某个特定产品的任何一次订购数量的订单
SELECT order_id, product_name, quantity
FROM orders o1
WHERE o1.quantity >= SOME (
    SELECT quantity
    FROM orders o2
    WHERE o2.product_name = '特定产品'
);

<>

不等于

DISTINCT

DISTINCT关键字用于消除查询结果中的重复记录，只返回唯一记录

1

SELECT DISTINCT name FROM employees;

ASC DESC

1
2

-- 排序规则：order by desc、asc
select name,age,score from student order by score desc

关联查询

inner join

在 SQL 中，INNER JOIN 是一种常见的关联查询方式，它根据两个表之间的关联条件，将满足条件的行组合在一起。

注意，INNER JOIN 只返回两个表中满足关联条件的交集部分，即在两个表中都存在的匹配行。

1
2
3
4
5
6
7
8
9

select
  s.name student_name,
  s.age student_age,
  s.class_id class_id,
  c.name class_name,
  c.level class_level
from
  student s
  join class c on s.class_id = c.id;

cross join

在 SQL 中，关联查询是一种用于联合多个数据表中的数据的查询方式。

其中，CROSS JOIN 是一种简单的关联查询，不需要任何条件来匹配行，它直接将左表的 每一行 与右表的 每一行 进行组合，返回的结果是两个表的笛卡尔积。

1
2
3

select s.name student_name,s.age student_age,s.class_id, c.name class_name
from student s
cross JOIN class c;

outer join

where的优先级低于left join

先连表查出合适的数据再用where过滤

1 2 3 4

SELECT * FROM `users` u left join user_roles us on u.user_id = us.user_id left join roles r on us.role_id = r.role_id WHERE r.role_name = "user";

在 SQL 中，OUTER JOIN 是一种关联查询方式，它根据指定的关联条件，将两个表中满足条件的行组合在一起，并 包含没有匹配的行

在 OUTER JOIN 中，包括 LEFT OUTER JOIN 和 RIGHT OUTER JOIN 两种类型，它们分别表示查询左表和右表的所有行（即使没有被匹配），再加上满足条件的交集部分。

1
2
3

select s.name student_name,s.age student_age,s.class_id,c.name class_name,c.level class_level
from student s
left JOIN  class c on s.class_id = c.id;

RBAC多表查询场景：

1
2
3
4
5
6

-- 根据userid查询到用户的权限信息
SELECT DISTINCT permissions.perm_name FROM `users` 
LEFT JOIN user_roles ON users.user_id = user_roles.user_id
LEFT JOIN role_permissions ON role_permissions.role_id = user_roles.role_id
LEFT JOIN permissions ON permissions.perm_id = role_permissions.perm_id
WHERE users.user_id = 1;

SQL练习

1.找出1班学生的成绩高于2班学生

1
2
3

select * from student s1
left join stduent s2 on s1.score > s2.score
where s1.class = "1班" and s2.class = "2班";

Mysql数据是怎么存储的

行（row）

每一条数据都是按行保存的

页（page）

16kb InnoDB 的数据是按「页」为单位来读写的，当需要读一条记录的时候，并不是将这个行记录从磁盘读出来，而是以页为单位，将其整体读入内存。

页类型：数据页、索引页、undo 日志页、溢出页

区（extent）

B+树每个节点以区划分

在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了

段（segment）

表空间是由各个段（segment）组成的，段是由多个区（extent）组成的。段一般分为数据段、索引段和回滚段等。

• 索引段：存放 B + 树的非叶子节点的区的集合；
• 数据段：存放 B + 树的叶子节点的区的集合；
• 回滚段：存放的是回滚数据的区的集合，之前讲事务隔离 (opens new window)的时候就介绍到了 MVCC 利用了回滚段实现了多版本查询数据。

索引

唯一索引

唯一索引（Unique Index）: 可以是主键索引，也可以是辅助索引。如果它是辅助索引，则它强制列值的唯一性，但可以允许 NULL 值。

二级索引

区别于主键索引，单个列的索引，比如name，也称为普通索引

这种索引允许同一索引列中存在重复的值。也可以为null

在电商平台的用户行为数据分析中，可以根据用户ID和商品类别建立二级索引，以便快速查询某个用户在某个商品类别下的行为数据

（非）聚簇索引

• 聚簇索引：也叫主键索引，是将索引和数据放在一起，聚簇索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；找到索引也就找到了数据。一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。
• 非聚簇索引：是指二级索引，也叫辅助索引。通过非聚簇索引可以查到记录对应的主键值，再使用主键的值通过聚簇索引找到要查找的数据。

Innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值。

覆盖索引

1	select id from product where product_no = '0002';

这种在二级索引的 B+Tree 就能查询到结果的过程就叫作「覆盖索引」，也就是只需要查一个 B+Tree 就能找到数据。

意思是说仅仅根据索引建立的b+树就能查到数据，无需进行回表操作，叫覆盖索引

联合索引

当数据库表中同时建立id索引和name二级索引，数据库索引库会发生什么变化：

当然是对name字段生成一个索引库，如果说叶子节点包含完整的用户记录，索引库多了，将会占用磁盘很大空间，那我们该怎么办呢？

联想一下：id本身建立了索引并叶子节点携带所有数据，查询id是不是非常快，那么是不思路豁然开朗了！我们就可以选择重新建立name索引库，这次索引库叶子节点只包含name和id，数据量大大降低，根据name所查到的id，再去id索引库查询最终数据，因为name并不是一个主键索引，所以可能会有多个name对应结果，这样就需要回表，知道查询到对应的id数据为止！

所以谈论这么多，联合索引又是什么呢？

相当于同时创建的两个二级索引，但是这个二级索引遵循最左原则：

1	CREATE INDEX idx_customer_order_date ON orders(name, phone);

同样的是根据两个索引加上主键索引id创建索引库，即叶子节点不携带数据，这样的话会首先根据左边的name索引先排序，所以在叶子节点中会出现相同的节点，这时才会根据phone排序；所以说相同值的name节点下可能会保存不同的phone；所以如果直接搜索phone索引是乱序的就不会起作用

全文索引

只能在文本类型 CHAR,VARCHAR,TEXT 类型字段上创建全文索引。字段长度比较大时，如果创建普 通索引，在进行 like 模糊查询时效率比较低，这时可以创建全文索引。 MyISAM 和 InnoDB中都可以 使用全文索引。

B+Tree vs B Tree

B+树：

有序性、范围查询友好、空间利用率更好：同样大小的磁盘可以容纳更多节点

B+Tree 只在叶子节点存储数据，而 B 树 的非叶子节点也要存储数据，所以 B+Tree 的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。

另外，B+Tree 叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点。

B+Tree的叶子节点采用双链表连接的原因主要有以下几点：

1. 有序性：B+树的叶子节点中存储的是数据，而不是关键字。因此，叶子节点之间必须有一种有序的方式来存储数据。双链表作为一种有序的数据结构，正好满足了这一要求。通过双链表连接，叶子节点可以按照顺序进行排列，使得数据的存储和检索更加有序和高效。
2. 范围查询友好：B+树中每一层的节点与相邻节点有指针相互连接（双向），这样在类似于<,>的范围查询中提高查询速率
3. 稳定性：B+树的内节点不存储Key对应的data，只有叶子节点存储了完整的数据信息。这样的设计使得B+树在查询数据时，每次都必须到达叶子节点才能获取数据。虽然这看似增加了查询的复杂度，但实际上却提高了查询的稳定性。因为无论查询哪个数据，查询路径的长度都是相同的（即树的高度），不会出现因为数据在非叶子节点而导致查询路径长度不同的情况。
4. 空间利用率：B+树的中间节点不存指针，这样同样大小的磁盘页可以容纳更多的节点元素，使得树的高度相对较小。在数据量相同的情况下，B+树比B树更加“矮胖”，查找起来就更快。

综上所述，B+Tree的叶子节点采用双链表连接主要是因为双链表具有有序性、范围查询友好、稳定性高和空间利用率好等特点，这些特点使得B+树在数据库索引等应用场景中具有优越的性能。

BufferPool

Log文件

读懂explain语句

1. id: 这是选择列表的编号。数值为 1 表示这是一个单一的操作，没有子查询或联合查询等复杂结构。

2. select_type: 显示了查询的类型。SIMPLE 表示这是一个简单的 SELECT 语句，没有子查询或 UNION。

3. table: 显示所查询的表名。在这里是 user 表。

4. type: 这一列显示了访问类型，它描述了 MySQL 如何访问数据。const 表示 MySQL 使用主键或唯一索引查找单行记录。

5. possible_keys: 列出可能使用的索引。这里的 PRIMARY 表示可能使用主键索引来优化查询。

6. key: 显示实际使用的索引。这里的 PRIMARY 意味着确实使用了主键索引。

7. key_len: 显示了使用的索引长度（以字节为单位）。这里的 4 可能意味着主键是一个整型字段。

8. ref: 显示了使用哪个字段或常量来查找行。const 指的是使用常量值进行查找。

9. rows: 估计需要检查的行数。这里的 1 表明 MySQL 预计只需要检查一行数据。

10. filtered: 这个百分比表示经过 WHERE 子句过滤后剩余的行数比例。这里的 100.00 表示没有应用额外的过滤条件，或者说过滤条件已经被索引覆盖。

综合以上信息，这个 EXPLAIN 输出表明 MySQL 计划使用 user 表的主键索引来查找一个确定的行。由于类型是 const，可以推断查询中包含了一个等于某常量值的主键条件，使得 MySQL 能够直接定位到该行而无需扫描整个表。这通常是一个非常高效的查询策略。

如何创建索引

创建普通索引

1
2

CREATE INDEX idx_orders_customer_orderdate
ON orders (customer_id); -- orders为表名，customer_id为字段

创建联合索引

1
2

CREATE INDEX idx_orders_customer_orderdate
ON orders (customer_id, order_date); -- orders为表名，customer_id, order_date为字段

对函数创建索引

1

alter table user add key idx_name_length ((length(name)));

索引篇

普通索引可以为NULL吗

可以，null作为特殊值处理，不会聚集在一起，并且不会影响唯一性，不能用等号搜寻

在数据库中，普通索引（也称为非唯一索引或B树索引）是可以包含空值（NULL）的。一个字段如果允许NULL值，并且在其上创建了索引，那么这个索引将能够存储和检索NULL值。

但是，有一些重要的点需要注意：

1. 如果一个索引字段允许NULL值，那么NULL值会被当作是独立于所有其他值的特殊值来处理。这意味着即使有多个记录的索引字段为NULL，它们也会被视为不同的值，而不会被聚集在一起。
2. 一个索引字段中NULL值的数量不会影响索引的唯一性。即使在一个唯一索引中，多个NULL值也不会引发唯一性冲突，因为数据库系统将NULL视为不可比较的值。
3. 在进行查询时，如果使用IS NULL或IS NOT NULL来查找记录，索引将非常高效。但如果使用等号=来查找NULL值（例如WHERE column = NULL），这是无效的语法，因为NULL不能用等号来比较。

说说索引有哪些？

• 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
• 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
• 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
• 按「字段个数」分类：单列索引、联合索引。

innoDB中页内查询

innoDB总体根据B+tree以页为单位建立索引库，读写也是以页为单位的；在页的内部包含重要的两个部分：页目录、行记录；页目录将行记录分组记录了索引，以及每组主键最大值，用于二分查找快速定位数据。而在行记录中是以主键大小为顺序连接的链表，时间复杂度为O(n)，innodb为了防止每组链表数目过大，避免产生过多的O(n)低效查询，规定每个组不高于8行数据，以增加适用二分快速查找的页目录索引来尽可能提高页内查询速率

B+ 树是如何进行查询的？

B+树非叶子节点是以页为单位存储的，页内包含的是按照主键大小排序好的，当查询某个数据，将会从根节点根据索引开始搜索，在页内使用二分法查找索引，判断搜索的索引的大小在两个索引行之间就定位最小索引对应的下个索引页，最后定位到叶子节点中，使用二分法快速定位记录所在的分组（槽号），最后在分组内采取遍历的方式找到数据

总结

• InnoDB 的数据是按「数据页」为单位来读写的，默认数据页大小为 16 KB。每个数据页之间通过双向链表的形式组织起来，物理上不连续，但是逻辑上连续。
• 数据页内包含用户记录，每个记录之间用单向链表的方式组织起来，为了加快在数据页内高效查询记录，设计了一个页目录，页目录存储各个槽（分组），且主键值是有序的，于是可以通过二分查找法的方式进行检索从而提高效率。
• 为了高效查询记录所在的数据页，InnoDB 采用 b+ 树作为索引，每个节点都是一个数据页。
• 如果叶子节点存储的是实际数据的就是聚簇索引，一个表只能有一个聚簇索引；如果叶子节点存储的不是实际数据，而是主键值则就是二级索引，一个表中可以有多个二级索引。
• 在使用二级索引进行查找数据时，如果查询的数据能在二级索引找到，那么就是「索引覆盖」操作，如果查询的数据不在二级索引里，就需要先在二级索引找到主键值，需要去聚簇索引中获得数据行，这个过程就叫作「回表」

索引失效情况！

索引之所以会失效需要从他自身结构和排序规则出发去思考！根据什么东西建立索引但是却不按照这个来查必会索引失效

1. 索引列采用左模糊匹配或左右模糊匹配

是因为根据文字列建索引是按照文字内部先后顺序进行排序，比如“林某”，先排林再排某，相当于整体是对第一个字建了索引，和联合索引类似，所以直接当第一个字模糊查询时就只能全表查询了

2. 根据对索引使用函数

在这种情况下，索引并不是按照索引函数值而建立索引的，所以一定会失效

1

EXPLAIN  select * from user where LENGTH(name)=5;

但是可以选择对函数建立索引，以达到索引不失效的作用

1
2

alter table user add key idx_name_length ((length(name)));
EXPLAIN  select * from user where LENGTH(name)=5;

3. 对索引进行表达式计算

   在这里mysql完全可以对表达式转化而直接对主键进行搜索，但是mysql偷懒了！

   所以这里为什么为失效是因为：索引是更具id建立的而不是id+1这样的表达式，而mysql不愿意在搜索层面去简化运算，而导致全表扫描

1

explain select * from user where id_u + 1 = 10;

4. 对索引隐式类型转换

对一个列创建二级索引，查询时参数类型和该索引本身类型不一样将会导致索引失效

.png)

1

select * from t_user where phone = 1300000001;

.png)

使用该段sql测试mysql得到：mysql会把字符转化为数字来搜索

1

select "10" > 9 -- 结果为1

5. 联合索引中不遵循最左匹配原则

1
2

CREATE INDEX idx_orders_customer_orderdate
ON orders (a, b,c); -- orders为表名，a, b , c为字段

联合索引创建时首要是根据最左索引开始排序，然后按创建索引顺序继续根据其他索引字段排序，所以联合索引查询必须包含最左索引列

在5.6版本之前，联合查询时如果包括了同时最左索引列以及其他的联合索引的索引列查询条件，首先会按照最左索引匹配然后回表查询主键索引数据中其他的条件是否符合。而在5.6之后，就直接在Server层判断之后再决定是否进行回表，这样大大的降低了回表次数的操作是索引下推

6. OR查询条件中包含非索引字段的条件

因为OR是二者选其一，所以两个条件必须都得到遍历，而非索引字段条件必须执行全表扫描，这也导致索引失效

1

select * from t_user where id = 1 or age = 18;

MySQL 使用 like “%x“，索引一定会失效吗？

不一定，当表中只包含该普通索引和主键索引时，mysql将会对该普通索引进行全表扫描，这时候索引就生效了

为什么他不走聚簇索引去搜索呢？mysql优化器认为当搜索索引列能达到索引覆盖的功能，就可以使用该索引，这是因为 MySQL 优化器认为直接遍历二级索引树要比遍历聚簇索引树的成本要小的多，因此 MySQL 选择了「全扫描二级索引树」的方式查询数据

.png)

COUNT()函数性能比较

COUNT()函数其实是对每一条数据走括号里面的表达式，如果不为NULL则COUNT加一，从而达到记数的作用

.png)

COUNT(字段)

全表扫描！

COUNT(主键)

1. 当不包含二级索引时计数会走主键索引（MySQL优化器所为）

2. 当包含二级索引会走二级索引技术，速度更快

COUNT(1)

和COUNT(主键)采取相同的索引搜索方式

但是相对于他COUNT(1)免去了括号字段读取步骤，因而COUNT(1)性能 > COUNT(主键)

COUNT(*)

COUNT(*) == COUNT(0),所以与COUNT(1)查询速度一致

事务篇

事务的几大特性

• 原子性（Atomicity）：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节，而且事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样，就好比买一件商品，购买成功时，则给商家付了钱，商品到手；购买失败时，则商品在商家手中，消费者的钱也没花出去。
• 一致性（Consistency）：是指事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。比如，用户 A 和用户 B 在银行分别有 800 元和 600 元，总共 1400 元，用户 A 给用户 B 转账 200 元，分为两个步骤，从 A 的账户扣除 200 元和对 B 的账户增加 200 元。一致性就是要求上述步骤操作后，最后的结果是用户 A 还有 600 元，用户 B 有 800 元，总共 1400 元，而不会出现用户 A 扣除了 200 元，但用户 B 未增加的情况（该情况，用户 A 和 B 均为 600 元，总共 1200 元）。
• 隔离性（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致，因为多个事务同时使用相同的数据时，不会相互干扰，每个事务都有一个完整的数据空间，对其他并发事务是隔离的。也就是说，消费者购买商品这个事务，是不影响其他消费者购买的。
• 持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

InnoDB 引擎通过什么技术来保证事务的这四个特性的呢？

• 持久性是通过 redo log （重做日志）来保证的；
• 原子性是通过 undo log（回滚日志） 来保证的；
• 隔离性是通过 MVCC（多版本并发控制） 或锁机制来保证的；
• 一致性则是通过持久性+原子性+隔离性来保证；

事务隔离级有哪些

并发执行会出现的现象：

• 脏读：读到其他事务未提交的数据；
• 不可重复读：前后读取数据不一样
• 幻读：前后读取行数不一致

.png)

.png)

MySQL如何解决可重复读

• 针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
• 针对当前读（select … for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select … for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

MVCC实现读提交和可重复读（ReadView）

行数据trx_id在ReadView 数据可见的情况，否则将会根据当前数据行的roll_pointer往下寻找满足条件的数据

• 【最小min_trx_id之后】
• 【在min_trx_id与max_trx_id之间 且不在活跃事务列表 m_ids之中】
• 【事务id == trx_id】

• 可重复读：启动事务时生成一个ReadView
• 读提交：在每次查询时创建一个新的ReadView

• select * from t_test where id > 100 （快照读）
• select * from t_test where id > 100 for update（当前读）

可重复读（快照读）如何解决幻读

如何解决？

在事务开启之后，数据行发生改变，这个时候改变的数据行trx_id一定是大于该ReadView事务id的，如此该事务对这新增行不可见

能完全解决吗？（不能！）

还有一种情况，开启事务A时查询行数据不存在，但是之间有个事务B插入了该数据，事务A会自动对这条行记录执行更新操作，并把该行的trx_id设置成当前事务id，这样再次查询时会查出这条记录

当前读如何解决幻读

如何解决？

事务A在读取范围中加间隙锁，其他事务的更改操作将会阻塞至事务A结束

能完全解决吗？（不能！）

事务A使用快照读查询，在这之后有事务B执行插入操作，如果事务A继续使用快照读将不会发生幻读，但这个时候事务A手欠采用当前读，完了肯定会查出来新的数据行，因为他这个加锁是发生在事务B插入操作之后的，并不会将其阻塞！这漏洞真的无孔不入啊！

MySQL 可重复读隔离级别并没有彻底解决幻读，只是很大程度上避免了幻读现象的发生。

锁机制

MySQL的锁主要分为读锁【共享】和写锁【独占】

全局锁

用于备份数据到磁盘，获取到全局锁时，只能执行读操作

1 2	flush tables with read lock -- 获取锁 unlock tables -- 释放锁

• innodb支持可重复读事务，在备份数据时开启事务无论什么时候读取数据都是一样的，就能保障备份数据和实际数据相同，即使不同可以通过快照去定位到最新数据
• myisam不支持可重复读事务的引擎，在备份数据库时就要使用全局锁的方法

表级锁

表锁

包括【共享读锁】+【独占写锁】

1
2
3
4
5

-- 表级别的共享锁，也就是读锁；读锁可以多线程持有
lock tables t_student read;

-- 表级别的独占锁，也就是写锁；
lock tables t_stuent write;

元数据锁（MDL）

说一个实例你就明白了：假设有一个SELECT查询正在执行（元数据读锁），此时另一个事务尝试执行ALTER TABLE或DROP TABLE等操作（元数据写锁）。在这种情况下，第二个事务会被阻塞，直到第一个事务完成并释放元数据锁。

当我们对数据库表进行操作时，会自动给这个表加上 MDL，随事务的生命周期而产生和消亡

• 对一张表进行 CRUD 操作时，加的是 MDL 读锁；
• 对一张表做结构变更操作的时候，加的是 MDL 写锁；

意向锁

意向锁是表级锁但与行级锁相关，在对行记录加【独占锁】或者【共享锁】时需要先加个意向锁，可以避免再对每一行记录查询是否占有锁，而只查看该表是否包含意向锁达到记录快速加锁的的目的

意向锁的目的是为了快速判断表里是否有记录被加锁

AUTO-INC 锁

保证自增字段插入时ID正确自增，该锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放。

MySQL5.1之后：引入轻量级的锁，给 AUTO_INCREMENT字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁

行级锁

普通的select语句是依靠MVCC做并发控制的，不会加锁，而想对行加锁可以采用以下语句，

1
2
3
4

-- 对读取的记录加共享锁
select ... lock in share mode;
-- 对读取的记录加独占锁
select ... for update;

锁是伴随事务而产生消亡的，所以上述语句必须加上事务 begin、start transaction 或者 set autocommit = 0。

分类：

• Record Lock，记录锁，也就是仅仅把一条记录锁上；
• Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
• Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

Record Lock

记录锁：锁住一条记录

1
2
3

BEGIN;
SELECT * FROM `user` WHERE id_u = 5 FOR UPDATE;
COMMIT;

Gap Lock

间隙锁：专门为解决可重复读隔离级别下的幻读问题而设计

间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的。

当你在一个事务中对某个范围的数据行使用了间隙锁时，这个事务本身是可以继续查询、修改甚至删除这些数据行的。这是因为间隙锁锁定的是数据行之间的“间隙”而非数据行本身。

使用场景：

本质是防止幻读

1. 操作一个不存在的索引

如果不存在id为7的数据，将会锁住索引(6，8)区间，防止插入ID为7的数据

1

SELECT * FROM table_name WHERE id = 7 FOR UPDATE;

2. 范围查询

InnoDB会锁定索引值为5和15的行，并且还会锁定这两个值之间的所有间隙（即索引值大于5小于15的区域）。这样可以防止其他事务在此区域内插入新行

1

SELECT * FROM table_name WHERE id BETWEEN 5 AND 15 FOR UPDATE;

3. 边界值查询

当查询条件涉及到索引的第一个或最后一个值时，InnoDB同样会使用间隙锁

1

SELECT * FROM table_name WHERE id < 5 FOR UPDATE;

InnoDB将锁定索引值小于5的所有记录，并且还会锁定索引值为5之前的间隙，以防止其他事务在索引值5之前插入新行。

Next-key Lock

next-key lock = Record Lock + Gap Lock

next-key lock 即能保护该记录（阻止其他事务对该记录修改），又能阻止其他事务将新记录插入到被保护记录前面的间隙中。

插入意向锁

它并不是意向锁，它是一种特殊的间隙锁，属于行级别锁

作用就是想在某一个加了锁的范围中执行插入操作，用于标识线程等待的状态

MySQL是如何加行级锁的

加锁的对象是索引，加锁的基本单位是 next-key lock，它是由记录锁和间隙锁组合而成的

唯一索引等值查询

• 存在：对的当前记录加记录锁
• 不存在：对大于该索引的最小索引，采取左右开区间，保证最小的间隙锁范围，加间隙锁

为什么不存在的要加间隙锁呢，因为锁是对索引加的，不存在的索引因而无法锁住该记录

唯一索引范围查询

update没加索引导致的问题

导致全表扫描，致使整个表加上了锁，对所有事务造成了堵塞

1

update table set score = 100 where name = "小明"

如何避免呢？

• 将sql_safe_updates设置为1(对是否加索引做了硬性要求)
• 避免索引失效再次导致全表扫描，可以加force index([index_name])告诉优化器使用哪个索引

如何会发生死锁

当两个事务查询，并查询条件中的数据不存在，则会加入间隙锁，间隙锁可以共有，而插入意向锁是与间隙锁相排斥的，于是共有情况下执行插入操作，导致两个事务都只能等待对方释放间隙锁，从而导致死锁发生！

如何预防死锁

• 开启主动死锁检测（将参数 innodb_deadlock_detect 设置为 on）
• 设置事务等待时间（innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒）

BufferPool

因为对磁盘进行IO操作十分费时间，所以引入了BufferPool，BufferPool的默认大小为128MB。因为MySQL一次读取操作是根据页来读取的，定位到页之后就会把缓存页加载到BufferPool中，再去定位到行记录

缓存页

控制块

保存的是【缓存页的表空间、页号、缓存页地址、链表节点】

如何管理空闲页

定义了一个Free链表，保存的是空闲缓存页的控制块的链表。每当需要从磁盘中加载一个页到 Buffer Pool 中时，就从 Free链表中取一个空闲的缓存页，并把控制块信息填上，然后把该缓存页对应的控制块从 Free 链表中移除

如何提高命中率

优化LRU（Least Recently Used）

• 读取的数据放在链表前面（固定链表长度，淘汰最久未使用的数据）
• 预读机制：把读取页的连续页加载到Old区域（靠近当前被访问数据的数据，在未来很大概率会被访问到）
• 防止BufferPool污染：[页被访问」且「 old 区域停留时间超过innodb_old_blocks_time 阈值（默认为1秒）」才会移动至young区域

TIP：BufferPool污染是索引失效时带来的全表搜索导致BufferPool全部的热点数据被淘汰带来的问题

日志篇

undolog

• 保障原子性，实现事务（删除，插入，更新）发生错误事回滚
• 配合MVCC机制实现读提交、可重复读事务机制

redolog

redo log 是物理日志，记录了某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新，每当执行一个事务就会产生这样的一条或者多条物理日志

• 保证持久性
• redolog写入磁盘是【顺序写】不需要定位到页，而写数据需要定位到页是【随机写】，顺序写（写入redolog）性能比随机写效率好很多，这样MySQL一条语句执行速率很快，然后在利用后台线程把数据真正刷入磁盘，类似于异步优化

undolog和redolog有什么区别？

• redolog记录了此次事务【完成之后】的状态，记录更新后的值
• undolog记录了事务【执行之前】的状态，记录更新前的值

redolog恢复已经提交的事务，undolog回滚未提交的事务

redo log 和 binlog 有什么区别？

redolog是存在于innoDB中的，在MySQL未引入innoDB之前是用binlog的

• 写入方式不同：binlog是全量文件，保存满了会创建新的文件用来保存；redolog则只会用一个文件保存

• 文件格式不同：binlog包含了STATEMENT、ROW、MIXED三种文件格式；而redolog保存的是记录的是在某个数据页做了什么修改

  STATEMENT：记录数据修改的指令； ROW：记录修改的数据；MIXED：根据不同的情况自动使用 ROW 模式和 STATEMENT 模式

  STATEMENT在记录使用某些函数（计算uuid、datetime()），这样恢复文件会导致恢复的结果和实际的结果不一样，此时可以使用ROW格式记录修改的数据弥补STATEMENT的缺点。

• 用途不同：binlog用于主从复制，备份恢复（数据库被删除了用binlog恢复）；redolog用于故障恢复

binlog

binlog主从复制

在事务提交的时候异步发送binlog日志，从库收到日志响应成功之后，再回放binlog文件

主从模型：同步，异步（默认），半同步（保障至少有一个同步成功，并加快反应速率）

主从分离之后：对主库主要进行写操作，用于同步从库；从库进行读操作；这样在MySQL写优先于读的情况下，写操作占用了主表的表锁，也不会影响读操作！

数据备份

CP全量备份

1. 对Mysql加表锁

• 登录

1	mysql -u root -p

• 加锁

1	flush tables with read lock;

2. 复制Mysql数据文件夹到备份文件夹

mysqldump

1. 保存数据库sql

1

mysqldump -uroot -p'123456' opencv users > E:\temp\opencv.sql

2. 恢复

1

mysql -uroot -p'123456' opencv < E:\temp\opencv.sql

xtrabackup

思考题

数据库三大范式

数据库设计的三范式超详细详解_数据库三范式-CSDN博客

满足3范式只要做到==一个表只存一种数据==基本就可以实现

• 第一范式：一个列不能再分（一个列不能包含多个属性）
• 第二范式：非主键字段必须完全依赖于主键（主键代表表一条数据的唯一标识，类似于学生表的其他字段中只包含学生信息，课程表字段中只包含课程信息）
• 第三范式：非主键字段不能依赖其他非主键字段（学生表中如果包含了学院合乎常理，但是还包括了学院号码，学院号码和学院项目依赖，则不可取）

innoDB对比MyISAM

• innoDB支持行级锁（粒度更加细），MyISAM只支持表级锁
• innoDB支持事务，并有MVCC机制做并发控制，MyISAM不支持事务
• innoDB支持外键，MyISAM不支持外键，索引和数据是分开的
• innoDB更适用于写入场景，以确保数据的一致性和完整性；而MyISAM更适用于读多写少场景，处理大量读场景很快
• InnoDB 在MySQL5.5之前不支持全文索引，而 MyISAM 支持全文索引，查询效率上 MyISAM 要高（全文索引增快文本检索效率，例如Like）
• innoDB保存数据是依靠聚簇索引的，聚簇索引叶子结点保存的是数据；而MyISAM采用的是非聚簇索引，叶子结点保存的是数据的物理地址，索引树是独立的，通过辅助键检索无需访问主键的索引树

myisam引擎是5.1版本之前的默认引擎，支持全文检索、压缩、空间函数等，但是不支持事务和行级锁，所以一般用于有大量查询少量插入的场景来使用，而且myisam不支持外键，并且索引和数据是分开存储的。

innodb是基于B+Tree索引建立的，和myisam相反它支持事务、外键，并且通过MVCC来支持高并发，索引和数据存储在一起

简单说一说drop、delete与truncate的区别

• drop命令用于删除整个表及其结构，通常用于永久性删除表。
• delete命令用于根据特定条件删除记录，而truncate操作则是清空表中的所有数据。

大表优化方案（数据量大）

1. 限定数据范围
2. 读/写分离（主表用来读，从表用来写）
3. 垂直分区，表结构将列划分为多张关联表

• 优点：降低数据量，减少IO次数
• 缺点：增加了Join查询次数

4. 水平分区，表结构不变，采用多张相同结构的表保存数据（水平分区尽量选用分库）

• 优点：水平拆分可以支撑非常大的数据量。
• 缺点：会带来 逻辑、部署、运维的各种复杂度，并且减低跨节点Join的性能

下面补充一下数据库分片的两种常见方案：

• 客户端代理： 分片逻辑在应用端，封装在JAR包中，通过修改或者封装层来实现。
• 中间件代理： 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。

批处理、库表设计（比如避免联表查询）、缓存、修改配置、选择合适的引擎或技术栈、分库分表、读写分离，甚至是升级物理机的配置来优化。

SQL优化

索引优化：

1. 某个字段查询次数多，可以使用覆盖索引，减少回表操作
2. 只为那些经常出现在WHERE子句、JOIN条件或ORDER BY子句中的列创建索引

过度的索引要求更多更复杂的b+树结构，会降低插入性能

3. 场景中经常依赖某个字段查询（例如一段时间，不同种类）就可以考虑在该字段建立索引

场景优化：

1. 禁用select *
2. 减少子查询，使用关联查询替代 (join)
3. 避免索引失效的场景（最左模糊查询，函数查询，联合索引中不遵循最左匹配原则，使用OR查询，避免使用NULL判断，避免使用!=）
4. 减少使用 IN 或者 NOT IN ，使用exists ，not exists或者关联查询语句替代 (?)
5. 避免长事务之后执行写操作，防止线程阻塞，因为写操作受阻塞，并且写操作是优先于读操作的，这会导致后面的读写线程都会阻塞
6. update，delete，select for update语句应对索引加条件，否则会锁住全表

分布式唯一ID如何解决

• UUID生成ID过长且无顺序，适用于生成文件名
• 数据库自增ID，两个数据库自增设置不同步长，有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。
• Redis分布式ID，性能好不依赖于数据库，但是引入了新组件
• 美团Leaf分布式ID生成方案（Leaf——美团点评分布式ID生成系统 - 美团技术团队 (meituan.com)）

NoSQL与关系型数据库区别

• 关系型数据库（RDBMS）：设计强调数据的结构化和规范化，通过定义表格、字段、主键和外键等来描述数据间的逻辑关系，从而实现数据的有效管理和查询
• 非关系型数据库（NoSQL）：是指那些不采用传统的关系模型来存储数据的数据库系统。这类数据库通常放弃了某些关系型数据库的特性（如固定的表格结构、严格的事务一致性），以换取更高的可扩展性和性能。

区别：

RDBMS：

• 结构化、规范化
• 严格的事务一致性
• 基于SQL规范的操作语句
• 通常支持垂直扩展

NoSQL：

• 不同的NoSQL数据库解决不同的实际问题
• 没有预定式的模式
• 高性能、可伸缩性（支持水平扩展）
• 缺点：没有强大的事务机制、追求高性能的同时牺牲一致性

PostgreSQL对比MySQL

介绍ORM框架

对象-关系映射

对象和关系数据是业务实体的两种表现形式，业务实体在内存中表现为对象，在数据库中表现为关系数据。内存中的对象之间存在关联和继承关系，而在数据库中，关系数据无法直接表达多对多关联和继承关系。因此，对象-关系映射(ORM)系统一般以中间件的形式存在，主要实现程序对象到关系数据库数据的映射。

• O：Object对象（java对象）
• R：Relation 关系（数据库）
• M：Mapping 映射 （对象和数据库的映射)

优点： 提高开发效率、使开发更加对象化

介绍一下数据分片

数据分片（Data Sharding）是一种将数据分散存储在多个节点上的技术，它将大型数据集分成较小的数据块，每个数据块被分配到不同的节点上进行存储和处理。数据分片的目的是提高系统的可扩展性和性能，避免单点故障，同时也可以提高数据的安全性和可靠性。

数据分片通常使用哈希函数来将数据块分配到不同的节点上，哈希函数将数据块的关键字映射到一个固定的节点或一组节点上，保证相同关键字的数据块被存储在同一个节点上。数据分片还可以使用其他的分片策略，如按时间、按地理位置等进行分片。

优点：

可扩展性、性能、安全性、避免单点故障

分片方法：

哈希、范围、取模、一致性哈希

一致性哈希（Consistent Hashing）是一种特殊的哈希算法，旨在解决分布式系统中数据分片（sharding）和负载均衡的问题。与传统的哈希算法相比，一致性哈希能够在节点（服务器或存储设备）动态增减时保持数据映射的最大稳定性，从而最小化数据迁移的成本

一致性哈希的核心思想是将数据空间映射成一个圆环（一个虚拟的哈希空间）。每个节点都被哈希成环上的一个点，而每个键也被哈希成环上的一个点。当一个键需要存储或查找时，就通过哈希函数计算出其位置，然后顺时针寻找最近的一个节点。

数据分片会不会影响数据的完整性？

数据分片本身不会影响数据的完整性，但在数据分片的过程中，如果处理不当，可能会导致数据的完整性受损。例如，如果数据被分成了多个片段，但在传输或存储过程中丢失了其中的某些片段，那么整个数据就会变得不完整。因此，在进行数据分片时，需要采取措施确保数据的完整性，例如采用冗余数据或错误检测和纠正技术。

视图是什么

本质：视图是存储在数据库中的查询的SQL语句

• 数据库中只存放了视图的定义，而并没有存放视图中的数据，这些数据存放在原来的表中。
• 作用：
  • 安全性：定义视图可以隐藏部分数据
  • 简易性：可使复杂的查询易于理解和使用，对复杂数据的查询封装为视图，只需对视图执行查询就能实现一键查询

数据库约束有哪些

主键、外键、唯一性（可以为null）、非空、默认值、检查约束

• 主键约束 (Primary Key): 用于唯一标识表中的每条记录。一个表只能有一个主键，且主键列不允许有重复值或NULL值。
• 外键约束 (Foreign Key): 用于建立和加强两个表之间的链接关系。外键通常引用另一个表的主键。
• 唯一性约束 (Unique): 保证一列或多列的组合值在表中是唯一的，但允许有NULL值。
• 检查约束 (Check): 用于限制列中可以输入的值。例如，可以设置年龄列只能接受大于0的值。
• 非空约束 (Not Null): 指定某列不能有NULL值。
• 默认值约束 (Default): 当没有为列指定值时，将自动插入到该列的默认值。

自适应Hash（AHI）

InnoDB默认支持

InnoDB 发现，有很多 SQL 存在这类很长的寻路，并且有很多 SQL 会命中相同的页面 (page)，

InnoDB 会在自己的内存缓冲区 (Buffer) 里，开辟一块区域，建立自适应哈希所有 AHI，以加速查询

当业务场景为下面几种情况时：

• 很多单行记录查询（例如 passport，用户中心等业务）

• 索引范围查询（此时 AHI 可以快速定位首行记录）

AHI 往往是有效的。当业务有大量 like 或者 join，AHI 的维护反而可能成为负担，降低系统效率，此时可以手动关闭 AHI 功能