你点开本文我就当你已经知道Mysql基础了，看完本文你将速通：函数，分组与统计，多表查询，子查询，索引，事务，间隙锁。

由于篇幅问题，窗口函数，悲观/乐观锁，处理 JSON 数据，复杂逻辑处理(if else)，性能分析，存储过程与触发器等等高级功能会放到下一篇文章。全部看完你就瞬间变成Mysql高手

先用下面的py模拟一堆数据进去

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
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
import pymysql
from faker import Faker
import random
from datetime import datetime

# ================= 配置区域 =================
DB_CONFIG = {
    "host": "localhost",
    "user": "root",        
    "password": "123456", # ⚠️ 记得改你的密码
    "port": 3306,
    "charset": "utf8mb4"
}
DB_NAME = "mall_db_simple" # 依然使用纯净库

# 数据量设置
NUM_USERS = 200     # 200个用户
NUM_PRODUCTS = 50   # 50种商品
NUM_ORDERS = 100   # 100个订单 (生成的 order_items 会有 2000~4000 条)
# ===========================================

fake = Faker("zh_CN")

def get_connection(use_db=True):
    conf = DB_CONFIG.copy()
    if use_db:
        conf["db"] = DB_NAME
    return pymysql.connect(**conf)

def init_db():
    print(f"🔄 初始化纯净版数据库: {DB_NAME}...")
    conn = get_connection(use_db=False)
    cursor = conn.cursor()

    cursor.execute(f"CREATE DATABASE IF NOT EXISTS {DB_NAME} DEFAULT CHARSET utf8mb4;")
    cursor.execute(f"USE {DB_NAME};")

    # 清理旧表
    cursor.execute("DROP TABLE IF EXISTS order_items;")
    cursor.execute("DROP TABLE IF EXISTS orders;")
    cursor.execute("DROP TABLE IF EXISTS products;")
    cursor.execute("DROP TABLE IF EXISTS users;")

    # === 表结构保持“素颜”（只有主键） ===

    sql_users = """
    CREATE TABLE users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50),
        email VARCHAR(100),
        phone VARCHAR(20),
        city VARCHAR(50),
        created_at DATETIME
    );
    """

    sql_products = """
    CREATE TABLE products (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100),
        category VARCHAR(50),
        price DECIMAL(10, 2),
        stock INT,
        status TINYINT
    );
    """

    sql_orders = """
    CREATE TABLE orders (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        order_no VARCHAR(50),
        total_amount DECIMAL(12, 2), -- 这个金额是经过计算算出来的
        status TINYINT,
        created_at DATETIME
    );
    """

    sql_order_items = """
    CREATE TABLE order_items (
        id INT AUTO_INCREMENT PRIMARY KEY,
        order_id INT NOT NULL,
        product_id INT NOT NULL,
        quantity INT,
        price DECIMAL(10, 2)
    );
    """

    cursor.execute(sql_users)
    cursor.execute(sql_products)
    cursor.execute(sql_orders)
    cursor.execute(sql_order_items)
    conn.commit()
    conn.close()
    print("✅ 表结构重置完成！")

def generate_data():
    conn = get_connection()
    cursor = conn.cursor()

    # --- 1. 生成用户 ---
    print("👤 正在生成用户...")
    users_data = []
    for _ in range(NUM_USERS):
        users_data.append((fake.name(), fake.email(), fake.phone_number(), fake.city(), fake.date_time()))
    cursor.executemany("INSERT INTO users (username, email, phone, city, created_at) VALUES (%s, %s, %s, %s, %s)", users_data)

    # --- 2. 生成商品 ---
    print("📦 正在生成商品...")
    products_data = []
    # 搞点真实的分类
    categories = ['数码', '零食', '书籍', '服装', '家居']
    for _ in range(NUM_PRODUCTS):
        cat = random.choice(categories)
        price = random.randint(10, 2000) # 价格在10块到2000块之间
        products_data.append((f"{cat}-{fake.word()}", cat, price, 999, 1))
    cursor.executemany("INSERT INTO products (name, category, price, stock, status) VALUES (%s, %s, %s, %s, %s)", products_data)
    conn.commit()

    # 准备缓存数据，方便下面生成订单用
    cursor.execute("SELECT id FROM users")
    user_ids = [r[0] for r in cursor.fetchall()]

    cursor.execute("SELECT id, price FROM products")
    # 变成字典 {id: price}，方便查价格
    products_map = {r[0]: float(r[1]) for r in cursor.fetchall()} 
    product_ids_list = list(products_map.keys())

    # --- 3. 生成复杂的订单 ---
    print("📝 正在生成复杂订单 (多商品/多数量)...")
    batch_orders = []
    batch_items = []

    for i in range(1, NUM_ORDERS + 1):
        # 3.1 随机选人、时间、状态
        u_id = random.choice(user_ids)
        created_at = fake.date_time_between(start_date="-1y", end_date="now")
        order_no = f"ORD{created_at.strftime('%Y%m%d')}{i:05d}"
        status = random.choice([0, 1, 2, 3])

        # 3.2 决定这个订单买几种商品 (1 到 5 种)
        items_count = random.randint(1, 5)

        # 从商品库里随机抽 items_count 个商品ID (不重复)
        selected_pids = random.sample(product_ids_list, items_count)

        current_order_total = 0.0

        # 3.3 遍历生成的商品，算钱，生成明细
        for pid in selected_pids:
            # 决定买几个 (1 到 3 个)
            qty = random.randint(1, 3)
            price = products_map[pid]

            # 累加总金额
            current_order_total += price * qty

            # 添加到 items 列表 (注意这里用了 i 作为 order_id)
            batch_items.append((i, pid, qty, price))

        # 3.4 添加到 orders 列表
        batch_orders.append((u_id, order_no, current_order_total, status, created_at))

        # 每500个提交一次，防止列表太大撑爆内存
        if len(batch_orders) >= 500:
            cursor.executemany("INSERT INTO orders (user_id, order_no, total_amount, status, created_at) VALUES (%s, %s, %s, %s, %s)", batch_orders)
            cursor.executemany("INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (%s, %s, %s, %s)", batch_items)
            conn.commit()
            batch_orders = []
            batch_items = []
            print(f"   ...已处理 {i} 个订单")

    # 处理剩下的
    if batch_orders:
        cursor.executemany("INSERT INTO orders (user_id, order_no, total_amount, status, created_at) VALUES (%s, %s, %s, %s, %s)", batch_orders)
        cursor.executemany("INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (%s, %s, %s, %s)", batch_items)
        conn.commit()

    conn.close()
    print(f"\n🎉 搞定！生成了 {NUM_ORDERS} 个订单，包含多种商品组合。")

if __name__ == "__main__":
    init_db()
    generate_data()

函数

Mysql提供了很多类似于编程语言的函数，进来一堆行，吐出一个值

函数名 类型 作用 应用场景
COUNT() 聚合 数数 统计今天有多少人注册：COUNT(id)
SUM() 聚合 求和 算出今天的总营业额：SUM(total_amount)
AVG() 聚合 平均值 算出客单价（平均每单多少钱）：AVG(total_amount)
MAX() 聚合 最大值 找出最贵的商品价格：MAX(price)
MIN() 聚合 最小值 找出库存最少的商品：MIN(stock)
GROUP_CONCAT() 聚合 拼接结果 把一个订单买的所有商品名拼成一行显示："薯片,可乐,辣条"
NOW() 时间 当前时间 插入订单时，created_at 字段用的就是它
DATE_FORMAT() 时间 格式化 前端要显示 “2026年01月04日”，而不是数据库那串原本的英文格式
DATEDIFF() 时间 算天数 统计用户 “注册了多少天”：DATEDIFF(NOW(), created_at)
CONCAT() 字符串 拼字符串 模糊搜索时拼 like 条件，或者把 姓+名 拼在一起
SUBSTR() 字符串 截取 手机号脱敏，只显示后四位
LENGTH() 字符串 算长度 检查用户简介是不是太长了
IFNULL() 逻辑 是不是NULL? 如果头像 URL 是 NULL，给个默认图：IFNULL(avatar, 'default.png')
CASE WHEN 逻辑 SQL里的if-else 把状态 0/1 转换成中文 “未支付”/“已支付”

下面的内容会使用到这些常用的函数，如果有用到的函数没有在这张表上显示的话，可以前往MySQL 函数 | 菜鸟教程 查看函数功能

分组和统计

GROUP BY 语句根据一个或多个列对结果集进行分组。

1
2
3
4
5
6
7
8
SELECT 
    分组字段, 
    聚合函数(统计字段)
FROM 表名
WHERE 过滤条件              -- (可选) 在分组前过滤原始行
GROUP BY 分组字段           -- 核心：按什么来分组
HAVING 聚合后的过滤条件      -- (可选) 在分组后过滤统计结果
ORDER BY 排序字段;          -- (可选) 排序

使用GROUP BY分组

举个简单的例子，比如我想查看mall_db_simple数据库里，products表里有多少种商品类型

1
select category from products group by category;

这样就会输出

1
2
3
4
5
6
7
8
9
10
+----------+
| category |
+----------+
| 书籍     |
| 家居     |
| 数码     |
| 服装     |
| 零食     |
+----------+
5 rows in set (0.0005 sec)

配合函数使用

如果我还想看每个分类有多少个，使用上面提到的COUNT()函数来获取个数

1
select category,count(category) from products group by category;

这样就会输出

1
2
3
4
5
6
7
8
9
10
+----------+-----------------+
| category | count(category) |
+----------+-----------------+
| 书籍     |               4 |
| 家居     |              14 |
| 数码     |               8 |
| 服装     |              15 |
| 零食     |               9 |
+----------+-----------------+
5 rows in set (0.0009 sec)

返回的的表个数列会使用sql语句里写的内容，可以使用 AS 关键字来给这个列取个名字

1
select category,count(category) as category_count from products group by category;

这样列 count(category) 就会被重命名为 category_count

1
2
3
4
5
6
7
8
9
10
+----------+----------------+
| category | category_count |
+----------+----------------+
| 书籍     |              4 |
| 家居     |             14 |
| 数码     |              8 |
| 服装     |             15 |
| 零食     |              9 |
+----------+----------------+
5 rows in set (0.0003 sec)

使用HAVING对分组之后的结果再过滤

比如我要列出个数小于10的商品，看看我商城网站上还有哪些商品比较缺

1
select category,count(category) as category_count from products group by category having category_count < 10;

这样就只会输出商品类别小于10的商品种类

1
2
3
4
5
6
7
8
+----------+----------------+
| category | category_count |
+----------+----------------+
| 书籍     |              4 |
| 数码     |              8 |
| 零食     |              9 |
+----------+----------------+
3 rows in set (0.0042 sec)

HAVING和WHERE的区别？

WHERE：在分组之前过滤。比如：我只统计“已支付”订单的金额。

HAVING：在分组之后过滤统计结果。比如：我只看“消费总额超过1000元”的大客户。

上面那个指令，如果把having移动到where作为条件就会

1
select category,count(category) as category_count from products where category_count < 10 group by category ;

报错

1
ERROR: 1054 (42S22): Unknown column 'category_count' in 'where clause'

WHERE会找不到分组后的这一个列

多表查询

在那个py生成的数据库里，为了不让数据冗余，我把数据拆分到了不同的表里（用户一张表、订单一张表）。多表查询就是通过一个 “关联字段”，把这些表像拼图一样临时拼起来。就是多表查询

使用JOIN进行多表查询

在 MySQL 中，最常用的多表查询方式是 JOIN。

查询类型 名字 效果 业务场景
INNER JOIN 内连接 只显示两边都能对上的数据 查出那些“确实下过单”的用户和订单
LEFT JOIN 左连接 左表全保留，右表没对上的显示 NULL 查出“所有用户”，包括那些还没买过东西的
RIGHT JOIN 右连接 右表全保留（很少用，通常用 LEFT JOIN 代替） -

使用内连接

比如我要找到每个订单的买家和买家的手机号

1
select users.username,users.phone,orders.order_no,orders.total_amount from users inner join orders on users.id = orders.user_id order by users.username;

也可以使用AS来方便写sql语句，两者执行结果是一样的。我加了ORDER BY来用名字排序

1
2
3
4
5
6
7
8
SELECT 
    u.username, 
    u.phone, 
    o.order_no, 
    o.total_amount
FROM users AS u 
INNER JOIN orders AS o ON u.id = o.user_id
ORDER BY u.username;

这样就会输出

1
2
3
4
5
6
7
8
9
+----------+-------------+------------------+--------------+
| username | phone       | order_no         | total_amount |
+----------+-------------+------------------+--------------+
| 丁宁     | 13473076571 | ORD2025092300097 |     13104.00 |
| 丁宁     | 13473076571 | ORD2025040100039 |      8057.00 |
| 丁宁     | 13473076571 | ORD2025040800086 |       129.00 |
| 何宁     | 14796476245 | ORD2025030500020 |     13788.00 |

....省略若干

使用左连接

比如我要搞一个富豪榜，按照消费额度大小搞一个不漏掉没有下过单的人的全排名。

使用下面的sql语句

1
2
3
4
5
6
SELECT 
    u.username, 
    SUM(o.total_amount) as money
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id
ORDER BY money DESC;

这样执行就会报错

1
ERROR: 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'mall_db_simple.u.username'; this is incompatible with sql_mode=only_full_group_by

报错信息里的 only_full_group_by 就是：“既然你用了聚合函数，那你必须告诉我，那些没被聚合的字段该按什么规则排列？”

u.username：这个字段，它想把表里所有的用户名都列出来（假设有 100 行）。

SUM(o.total_amount)：这是一个聚合函数，它的目的是把所有的钱加起来，只吐出一个值（1 行）。

让数据库在第一列显示 100 个名字，但在第二列只显示 1 个总金额。

那这 1 个总金额，到底是对齐第一个人？还是最后一个人？还是平均分给所有人？数据库没法对齐

使用 GROUP BY 来按人分组，算出每个人的金额

1
2
3
4
5
6
7
SELECT 
    u.username, 
    SUM(o.total_amount) as money
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id
GROUP BY u.username
ORDER BY money DESC;

这样就能正常输出

1
2
3
4
5
6
7
8
9
10
11
12
+----------+----------+
| username | money    |
+----------+----------+
| 胡玉     | 22626.00 |
| 周龙     | 21825.00 |
| 丁宁     | 21290.00 |
...省略若干行...
| 吉桂芝   |     NULL |
| 艾静     |     NULL |
| 程帅     |     NULL |
+----------+----------+
193 rows in set (0.0031 sec)

没下过单的money列就会是NULL，我想把它改成0，使用IFNULL函数

1
2
3
4
5
6
7
SELECT 
    u.username, 
    IFNULL(SUM(o.total_amount),0) as money
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id
GROUP BY u.username
ORDER BY money DESC;

这样看着就舒服多了

1
2
3
4
5
6
7
8
9
10
11
12
+----------+----------+
| username | money    |
+----------+----------+
| 胡玉     | 22626.00 |
| 周龙     | 21825.00 |
| 丁宁     | 21290.00 |
...省略若干行...
| 吉桂芝   |     0.00 |
| 艾静     |     0.00 |
| 程帅     |     0.00 |
+----------+----------+
193 rows in set (0.0023 sec)

使用子查询进行多表查询

先查出一个结果，再把这个结果作为条件传给下一个查询

比如我想找出那个单笔订单花了最多钱的用户，就是分成两个查询

1.orders表里找到单笔金额最大的userid

2.根据找到的userid在users表里找到用户信息

1
SELECT * FROM users WHERE id = (SELECT user_id FROM orders ORDER BY total_amount DESC LIMIT 1);

这样就能输出

1
2
3
4
5
6
+-----+----------+-----------------+-------------+--------+---------------------+
| id  | username | email           | phone       | city   | created_at          |
+-----+----------+-----------------+-------------+--------+---------------------+
| 100 | 周龙     | vyu@example.net | 18958822331 | 阜新县 | 2001-07-10 10:29:42 |
+-----+----------+-----------------+-------------+--------+---------------------+
1 row in set (0.0005 sec)

如果要把上面的富豪榜改成子查询

1
2
3
4
5
6
7
SELECT 
    u.username, 
    (SELECT IFNULL(SUM(orders.total_amount), 0) 
     FROM orders
     WHERE orders.user_id = u.id) AS money
FROM users AS u  
ORDER BY money DESC;

效果是一样的

索引

如果把数据库比作一本《新华字典》，那“索引”就是字典前面的拼音检索表。

  • 没索引（全表扫描 / Full Table Scan）：
    假设要查“字”这个字，但字典没有目录。你只能从第一页开始，一页一页往后翻，直到翻到为止。如果字典有 1000 页，最坏情况你要翻 1000 次。
    • 效率：极低 O(n)。
  • 有索引（Index）：
    先看前面的拼音目录，找到 zi 在第 500 页。直接翻到第 500 页。
    • 效率：极高 O(log n)（因为 MySQL 底层用的是 B+树 结构，类似二分查找）。
      核心作用：以空间换时间。索引是一个独立的文件，占用硬盘空间，但能让查询速度提升百倍千倍。

其实那个python在创建数据表的时候，每个表创建了一个主键。

1
2
3
4
5
6
7
 MySQL  localhost:3306  mall_db_simple  SQL > show index from users;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users |          0 | PRIMARY  |            1 | id          | A         |     2000000 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.0011 sec)

而主键是被Mysql默认作为一个索引

主键索引有什么用？

定位一行数据

比如这样的一条sql

1
SELECT * FROM orders WHERE id = 10086;

这不是“查条件”，是“我知道你是谁，直接把你拎出来”。

不扫表，不比对，直接在 B+Tree 上精准定位

复杂度是 O(log n)，而不是 O(n)。

决定数据在磁盘上的物理顺序

在 InnoDB 里，表中的每一行，是按主键顺序排在一起存的

1
id INT AUTO_INCREMENT PRIMARY KEY

意味着，id 小的在前，id 大的在后，数据页顺着排

如果要执行这样的查询

1
SELECT * FROM orders WHERE id BETWEEN 100 AND 200;

数据就在磁盘上连着，一页一页顺着就读下去了

并且这样join的也快，只要 join 的是主键

普通索引的“最终落脚点”

在 InnoDB 里：普通索引 ≠ 指向行数据，而是普通索引 → 指向主键

普通索引节点里存的是：索引值 + 主键值

回表？

什么是回表

比如有这么一张表

1
2
3
4
5
6
7
8
users(
  id INT PRIMARY KEY,
  username VARCHAR(50),
  email VARCHAR(50),
  balance DECIMAL
)

INDEX idx_email(email)

执行这样的查询

1
SELECT * FROM users WHERE email = 'a@b.com';

实际在Mysql里是这样的：

先在idx_email里，根据 a@b.com 找到 id=100
这里只有email和id，没有balance和username

然后再用id找到这一行的数据，这一步就是回表

从普通索引 → 回到主键索引 → 拿整行数据

什么时候会发生回表

像这样

1
SELECT * FROM users WHERE email = 'a@b.com';

用了普通索引，但是要列里所有数据，索引里没有这么多列就会回表

但是如果是这样

1
2
3
SELECT email FROM users WHERE email = 'a@b.com';
或者
SELECT email, id FROM users WHERE email = 'a@b.com';

这样就不会回表，因为要找的东西在索引里就有了

索引的使用

还是那个py，把用户改成两百万个，运行Py后执行下面的查询

1
SELECT * FROM users WHERE username LIKE "哈基";

运行后是找不到这个数据的，这个正常，主要看找数据花了多久？

1
2
MySQL  localhost:3306  mall_db_simple  SQL > SELECT * FROM users WHERE username LIKE "哈基";
Empty set (0.2233 sec)

0.22秒，看起来不多，纯粹是因为我的电脑硬件（CPU、内存、SSD）太强了，把“笨重”的 SQL 硬扛下来了。如果是要面对大量并发查询，每次全表扫描就太慢了

users 表里现在只有主键索引（PRIMARY），但查询条件是 WHERE username LIKE ...。因为 username 没有索引，MySQL 其实做了一次“全表扫描”。

执行下面的SQL看看是怎么查询的

1
EXPLAIN SELECT * FROM users WHERE username LIKE "哈基";

运行输出:

1
2
3
4
5
6
7
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2000000 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.0010 sec)
Note (code 1003): /* select#1 */ select `mall_db_simple`.`users`.`id` AS `id`,`mall_db_simple`.`users`.`username` AS `username`,`mall_db_simple`.`users`.`email` AS `email`,`mall_db_simple`.`users`.`phone` AS `phone`,`mall_db_simple`.`users`.`city` AS `city`,`mall_db_simple`.`users`.`created_at` AS `created_at` from `mall_db_simple`.`users` where (`mall_db_simple`.`users`.`username` like '哈基')

可以看到type列是ALL(代表全表扫描，最烂的情况) ，rows列是2000000遍历了两百万行

创建和使用索引

创建索引的SQL语句是

1
CREATE INDEX 索引名 ON 表名(字段名);

在这里就是

1
CREATE INDEX idx_username ON users(username);

执行后再次运行上面那个查询，可以看到是瞬间给出结果了

1
2
MySQL  localhost:3306  mall_db_simple  SQL > SELECT * FROM users WHERE username LIKE "哈基";
Empty set (0.0004 sec)

只用了0.0004秒，现在再看看Mysql是怎么查询的

1
2
3
4
5
6
7
8
MySQL  localhost:3306  mall_db_simple  SQL > EXPLAIN SELECT * FROM users WHERE username LIKE "哈基";
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | users | NULL       | range | idx_username  | idx_username | 203     | NULL |    1 |      100 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.0004 sec)
Note (code 1003): /* select#1 */ select `mall_db_simple`.`users`.`id` AS `id`,`mall_db_simple`.`users`.`username` AS `username`,`mall_db_simple`.`users`.`email` AS `email`,`mall_db_simple`.`users`.`phone` AS `phone`,`mall_db_simple`.`users`.`city` AS `city`,`mall_db_simple`.`users`.`created_at` AS `created_at` from `mall_db_simple`.`users` where (`mall_db_simple`.`users`.`username` like '哈基')

看rows列变成了1，就是说明只需要扫 1 条索引记录，就能命中

删除索引

1
DROP INDEX 索引名 ON 表名;

如果索引建错了，或者没用上，要删掉（因为索引会拖慢写入速度）。就用上面的命令。我不演示了

索引的副作用

既然这么好，为啥不给所有字段都加？给 username, phone, email, address 全加上索引，岂不是起飞？

  1. 拖慢写入（INSERT/UPDATE/DELETE）：
    • 往书里写一行内容（INSERT），不仅要往正文里写，还得去改目录（维护索引树）。索引越多，改目录越慢。
    • 对于写多读少的系统（比如日志系统），索引要慎用。
  2. 占用磁盘空间：
    • 索引也是文件，也是要存硬盘的。数据量大时，索引可能比数据本身还大。

LIKE的坑

在刚刚的SQL里使用了LIKE但是没有加百分号。也就是说其实还是使用的精确匹配

  1. 精确匹配/前缀匹配（索引生效 ）

    1
    EXPLAIN SELECT * FROM users WHERE username LIKE "哈基%";

    结果：type 是 range 或 ref。索引生效了！因为目录是按拼音排的，MySQL 知道“哈”在前面的几页。

    1
    2
    3
    4
    5
    6
    7
    +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
    | id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
    +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
    |  1 | SIMPLE      | users | NULL       | range | idx_username  | idx_username | 203     | NULL |    1 |      100 | Using index condition |
    +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
    1 row in set, 1 warning (0.0009 sec)
    Note (code 1003): /* select#1 */ select `mall_db_simple`.`users`.`id` AS `id`,`mall_db_simple`.`users`.`username` AS `username`,`mall_db_simple`.`users`.`email` AS `email`,`mall_db_simple`.`users`.`phone` AS `phone`,`mall_db_simple`.`users`.`city` AS `city`,`mall_db_simple`.`users`.`created_at` AS `created_at` from `mall_db_simple`.`users` where (`mall_db_simple`.`users`.`username` like '哈基%')

  2. 左模糊匹配（索引失效 ）

    1
    2
    -- 找名字里以“米”结尾的人（比如哈基米、小米）
    EXPLAIN SELECT * FROM users WHERE username LIKE "%米";

    结果：type 又是 ALL 了！

    查字典的时候，如果只知道这就字“结尾是米”，没法用拼音目录查，只能把整本字典翻一遍。

    1
    2
    3
    4
    5
    6
    7
    +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
    |  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2000000 |    11.11 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
    1 row in set, 1 warning (0.0003 sec)
    Note (code 1003): /* select#1 */ select `mall_db_simple`.`users`.`id` AS `id`,`mall_db_simple`.`users`.`username` AS `username`,`mall_db_simple`.`users`.`email` AS `email`,`mall_db_simple`.`users`.`phone` AS `phone`,`mall_db_simple`.`users`.`city` AS `city`,`mall_db_simple`.`users`.`created_at` AS `created_at` from `mall_db_simple`.`users` where (`mall_db_simple`.`users`.`username` like '%米')

如果我想倒着查也能瞬间给出结果？使用虚拟列功能

1
2
3
4
5
6
-- 1. 给表加一个虚拟列，内容是 username 的倒序
ALTER TABLE users ADD reverse_username VARCHAR(50) 
GENERATED ALWAYS AS (REVERSE(username));

-- 2. 给这个倒序列加索引
CREATE INDEX idx_reverse_username ON users(reverse_username);

如果使用的是Windows的phpstudy中的Mysql5.7，默认不会是InnoDB，在执行第二步就会报错

1
ERROR: 1478 (HY000): Table storage engine 'MyISAM' does not support the create option 'Index on virtual generated column'

这时候使用ALTER命令修改存储引擎为InnoDB

1
ALTER TABLE users ENGINE=InnoDB;

再执行第二步就正常了，现在看看左模糊匹配效果，不过得在username的倒序列里查找

1
2
3
4
5
6
7
8
9
10
MySQL  localhost:3306  mall_db_simple  SQL > SELECT * FROM users WHERE reverse_username LIKE "米%";
Empty set (0.0015 sec)
MySQL  localhost:3306  mall_db_simple  SQL > EXPLAIN SELECT * FROM users WHERE reverse_username LIKE "米%";
+----+-------------+-------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | range | idx_reverse_username | idx_reverse_username | 203     | NULL |    1 |      100 | Using where |
+----+-------------+-------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.0008 sec)
Note (code 1003): /* select#1 */ select `mall_db_simple`.`users`.`id` AS `id`,`mall_db_simple`.`users`.`username` AS `username`,`mall_db_simple`.`users`.`email` AS `email`,`mall_db_simple`.`users`.`phone` AS `phone`,`mall_db_simple`.`users`.`city` AS `city`,`mall_db_simple`.`users`.`created_at` AS `created_at`,`mall_db_simple`.`users`.`reverse_username` AS `reverse_username` from `mall_db_simple`.`users` where (`mall_db_simple`.`users`.`reverse_username` like '米%')

也是秒出。也只用扫描一条记录

事务

事务是不支持MyISAM存储引擎的

事务是把一组SQL语句打包成一个整体，在这组SQL语句的执行过程中，要么全部成功，要么全部失败。这组SQL语句可以是一条也可以是多条。

事务的八股:

  • A (Atomicity) 原子性：
    • 事务中的所有操作就是一个不可分割的整体，像原子一样。这些操作，要么全部成功，要么全部失败。数据库会记录事务执行前的数据状态，一旦事务执行过程出现失败，就会回滚到原来的初始状态。
    • 原理：靠 Undo Log 实现（万一失败了，根据日志把数据改回去）。
  • C (Consistency) 一致性：
    • 事务执行前后，事务的完整性不会被破坏。事务执行完成之后，保证数据正确并且符合预期。
  • I (Isolation) 隔离性：
    • 数据库允许多个并发事务同时对数据进行修改和读写，隔离性可以保证多个事务并发执行，并且不相互干扰。
    • 原理：靠 锁 (Lock) 和 MVCC (多版本并发控制) 实现。
  • D (Durability) 持久性：
    • 只要 COMMIT 了，哪怕下一秒服务器爆炸，电线被挖断，数据也不能丢。
    • 原理：靠 Redo Log 实现（先写日志再写磁盘）。

事务指令

指令 作用
START TRANSACTION (或 BEGIN) 开启事务。告诉 MySQL：“从这一行开始，后面的操作先别真写入硬盘，先记在小本本上。”
COMMIT 提交。告诉 MySQL：“刚才的操作都确认无误，全部生效，写入硬盘！”
ROLLBACK 回滚。告诉 MySQL：“出事了！刚才的操作全部作废，撤销到开启事务之前的状态！”
savepoint 设置一个存档。告诉Mysql在这里保存一个状态。如果回滚的话可以选择回滚到这里

注意：事务是有适用范围的

  • 能回滚的（DML - Data Manipulation Language）：
    • INSERT（增）
    • UPDATE（改）
    • DELETE（删数据）
  • 不能回滚且会强制提交的（DDL - Data Definition Language）：
    • CREATE（建表/库）
    • DROP（删表/库）
    • ALTER（改字段）
    • TRUNCATE（清空表）

比如下面这个例子:

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
MySQL  localhost:3306  mall_db_simple  SQL > start transaction;
Query OK, 0 rows affected (0.0001 sec)
MySQL  localhost:3306  mall_db_simple  ★  SQL > show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mall_db_simple     |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.0008 sec)
MySQL  localhost:3306  mall_db_simple  ★  SQL > drop database mall_db_simple;
Query OK, 4 rows affected (0.0082 sec)
MySQL  localhost:3306  mall_db_simple  SQL > rollback;
Query OK, 0 rows affected (0.0002 sec)
MySQL  localhost:3306  mall_db_simple  SQL > show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.0006 sec)

Mysql在执行DDL指令前，不会管前面有没有start transaction;都会先执行一个COMMIT，然后再会执行DDL指令。执行完DDL后再会执行一个COMMIT。后续的rollback滚不回来

DELETE和TRUNCATE的区别

  • 想清空一张表，且希望可以回滚：
    • 用 DELETE FROM users;
    • 这是 DML，速度慢（一行行删），但可以 ROLLBACK。
  • 想清空一张表，不留后路：
    • 用 TRUNCATE TABLE users;
    • 这是 DDL，速度快（直接把表文件扔了重新建一个），但ROLLBACK不回来。

开始事务之前

库存是怎么被下单操作搞乱的？

先看看id为1的商品有多少个库存

1
SELECT id, stock FROM products WHERE id = 1;

执行得到结果剩余100个

1
2
3
4
5
6
7
MySQL  localhost:3306  mall_db_simple  SQL > SELECT id, stock FROM products WHERE id = 1;
+----+-------+
| id | stock |
+----+-------+
|  1 |   100 |
+----+-------+
1 row in set (0.0003 sec)

此时一位正常用户进来买了一个id为1的商品

1
UPDATE products SET stock = stock - 1 WHERE id = 1;

这时候库存还剩99个

1
2
3
4
5
6
7
MySQL  localhost:3306  mall_db_simple  SQL > SELECT id, stock FROM products WHERE id = 1;
+----+-------+
| id | stock |
+----+-------+
|  1 |    99 |
+----+-------+
1 row in set (0.0004 sec) sec)

然后一位测试进来买了-1个id为1的商品

1
UPDATE products SET stock = stock - (-1) WHERE id = 1;

库存增加了！

1
2
3
4
5
6
7
8
9
10
11
MySQL  localhost:3306  mall_db_simple  SQL > UPDATE products SET stock = stock - (-1) WHERE id = 1;
Query OK, 1 row affected (0.0010 sec)

Rows matched: 1  Changed: 1  Warnings: 0
MySQL  localhost:3306  mall_db_simple  SQL > SELECT id, stock FROM products WHERE id = 1;
+----+-------+
| id | stock |
+----+-------+
|  1 |   100 |
+----+-------+
1 row in set (0.0004 sec)

然后这位测试买了9999个商品

1
UPDATE products SET stock = stock - 9999 WHERE id = 1;

然后他买到了9999个商品并且把库存干成负数了，商城超卖了

1
2
3
4
5
6
7
8
9
10
11
MySQL  localhost:3306  mall_db_simple  SQL > UPDATE products SET stock = stock - 9999 WHERE id = 1;
Query OK, 1 row affected (0.0010 sec)

Rows matched: 1  Changed: 1  Warnings: 0
MySQL  localhost:3306  mall_db_simple  SQL > SELECT id, stock FROM products WHERE id = 1;
+----+-------+
| id | stock |
+----+-------+
|  1 | -9899 |
+----+-------+
1 row in set (0.0004 sec)

解决方法

利用UPDATE 语句的原子性，把“判断”逻辑直接写在 SQL 里。

1
2
3
4
UPDATE products 
SET stock = stock - 购买数量
WHERE id = 1 
AND stock >= 购买数量;

这样的执行效果是

1
2
3
4
5
6
7
8
9
10
11
MySQL  localhost:3306  mall_db_simple  SQL > UPDATE products SET stock = stock - 9999 WHERE id = 1 AND stock >= 9999;
Query OK, 0 rows affected (0.0009 sec)

Rows matched: 0  Changed: 0  Warnings: 0
MySQL  localhost:3306  mall_db_simple  SQL > SELECT id, stock FROM products WHERE id = 1;
+----+-------+
| id | stock |
+----+-------+
|  1 |   100 |
+----+-------+
1 row in set (0.0003 sec)

效果：

  • 库存够：MySQL 找到这行数据，且条件满足，执行扣减。返回 Rows matched: 1, Changed: 1。后端看到影响行数为 1，判定购买成功。
  • 库存不够（比如库存是 100）：MySQL 扫这行数据，发现 stock >= 9991 不成立。直接放弃修改。返回 Rows matched: 0, Changed: 0。后端看到影响行数为 0，判定库存不足，下单失败。

同样的方法解决买了-1个商品导致库存增加的问题，再添加一个AND判断逻辑

1
2
3
4
5
UPDATE products 
SET stock = stock - 购买数量
WHERE id = 1 
AND stock >= 购买数量
AND 购买数量 > 0;

这样当购买数量为-1的时候

1
2
3
4
5
6
7
8
9
10
11
MySQL  localhost:3306  mall_db_simple  SQL > UPDATE products SET stock = stock - (-1) WHERE id = 1 AND stock >= (-1) AND (-1) > 0;
Query OK, 0 rows affected (0.0010 sec)

Rows matched: 0  Changed: 0  Warnings: 0
MySQL  localhost:3306  mall_db_simple  SQL > SELECT id, stock FROM products WHERE id = 1;
+----+-------+
| id | stock |
+----+-------+
|  1 |   100 |
+----+-------+
1 row in set (0.0004 sec)

同样，0行被match，0行被change

没用的解决方法

后端检测库存数量是否充足，再执行扣库存？

假设库存只有 1 个。
User A和User B同时点击购买。
后端代码（错误示范）：

1
2
3
4
5
6
7
8
9
# 1. 先查库存
current_stock = db.query("SELECT stock FROM products WHERE id=1")
# 2. 判断充足
if current_stock > 0:
 # 3. 扣库存
 db.exec("UPDATE products SET stock = stock - 1 WHERE id=1")
 return "购买成功"
else:
 return "库存不足"

超卖过程：

  1. 时刻 0.01s：A 查库存，是 1。
  2. 时刻 0.01s：B 查库存，也是 1（因为 A 还没扣呢）。
  3. 时刻 0.02s：A 判断 1 > 0，通过。
  4. 时刻 0.02s：B 判断 1 > 0，通过。
  5. 时刻 0.03s：A 扣减，库存变 0。
  6. 时刻 0.03s：B 扣减，库存变 -1。
    结果：卖出了 2 个手机，实际上仓库只有 1 个。超卖了。

如果要求库存不能为负数？

在MySQL 8.0.16+使用Check约束是可行的

1
2
3
ALTER TABLE products
ADD CONSTRAINT chk_stock_non_negative
CHECK (stock >= 0);

之后再尝试扣除超过库存的数的时候

1
UPDATE products SET stock = stock - 9999 WHERE id = 1;

就会直接报错

1
ERROR 3819 (HY000): Check constraint 'chk_stock_non_negative' is violated

但是要注意，CHECK 是兜底，不是主流程。因为报错成本高，不方便区分“库存不足 vs 其他错误”，而且在高并发下，异常比 affected_rows 慢

如果使用UNSIGNED？表面看起来可行，但不推荐

在某些版本 / SQL_MODE 下：要么报错，要么发生隐式类型转换，行为不稳定

UNSIGNED 解决的是“值域”，不是并发逻辑

总之在后端逻辑和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
MySQL  localhost:3306  mall_db_simple  SQL > SELECT id, stock FROM products WHERE id = 1;
+----+-------+
| id | stock |
+----+-------+
|  1 |    99 |
+----+-------+
1 row in set (0.0039 sec)
MySQL  localhost:3306  mall_db_simple  SQL > START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)
MySQL  localhost:3306  mall_db_simple  ★  SQL > UPDATE products SET stock = stock - 1 WHERE id = 1;
Query OK, 1 row affected (0.0015 sec)

Rows matched: 1  Changed: 1  Warnings: 0
MySQL  localhost:3306  mall_db_simple  ★  SQL > SELECT id, stock FROM products WHERE id = 1;
+----+-------+
| id | stock |
+----+-------+
|  1 |    98 |
+----+-------+
1 row in set (0.0003 sec)
MySQL  localhost:3306  mall_db_simple  ★  SQL > ROLLBACK;
Query OK, 0 rows affected (0.0072 sec)
MySQL  localhost:3306  mall_db_simple  SQL > SELECT id, stock FROM products WHERE id = 1;
+----+-------+
| id | stock |
+----+-------+
|  1 |    99 |
+----+-------+
1 row in set (0.0003 sec)

可见，开始之前，id为1的商品库存99个。然后启动事务，将商品数量扣除1个后，再次查看商品数量已经变成98个了。然后使用回滚。商品数量没有变化

提交的使用

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
MySQL  localhost:3306  mall_db_simple  SQL > SELECT id, stock FROM products WHERE id = 1;
+----+-------+
| id | stock |
+----+-------+
|  1 |    99 |
+----+-------+
1 row in set (0.0025 sec)
MySQL  localhost:3306  mall_db_simple  SQL > begin;
Query OK, 0 rows affected (0.0002 sec)
MySQL  localhost:3306  mall_db_simple  ★  SQL > UPDATE products SET stock = stock - 2 WHERE id = 1;
Query OK, 1 row affected (0.0005 sec)

Rows matched: 1  Changed: 1  Warnings: 0
MySQL  localhost:3306  mall_db_simple  ★  SQL > SELECT id, stock FROM products WHERE id = 1;
+----+-------+
| id | stock |
+----+-------+
|  1 |    97 |
+----+-------+
1 row in set (0.0003 sec)
MySQL  localhost:3306  mall_db_simple  ★  SQL > commit;
Query OK, 0 rows affected (0.0045 sec)
MySQL  localhost:3306  mall_db_simple  SQL > SELECT id, stock FROM products WHERE id = 1;
+----+-------+
| id | stock |
+----+-------+
|  1 |    97 |
+----+-------+
1 row in set (0.0003 sec)

可见，开始之前，id为1的商品库存99个。然后启动事务，将商品数量扣除2个后，再次查看商品数量已经变成97个了。使用commit提交后，事务结束。商品库存扣除

保存点的使用

在同一事务中，保存点名称是唯一的。如果设置相同名称的保存点名称，后设置的保存点会覆盖先设置的

看下面的例子

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
MySQL  localhost:3306  mall_db_simple  SQL > begin;
Query OK, 0 rows affected (0.0002 sec)
MySQL  localhost:3306  mall_db_simple  ★  SQL > SELECT id, stock FROM products WHERE id = 1;
+----+-------+
| id | stock |
+----+-------+
|  1 |   100 |
+----+-------+
1 row in set (0.0003 sec)
MySQL  localhost:3306  mall_db_simple  ★  SQL > UPDATE products SET stock = stock - 10 WHERE id = 1;
Query OK, 1 row affected (0.0004 sec)

Rows matched: 1  Changed: 1  Warnings: 0
MySQL  localhost:3306  mall_db_simple  ★  SQL > SELECT id, stock FROM products WHERE id = 1;
+----+-------+
| id | stock |
+----+-------+
|  1 |    90 |
+----+-------+
1 row in set (0.0003 sec)
MySQL  localhost:3306  mall_db_simple  ★  SQL > savepoint koukuncun1;
Query OK, 0 rows affected (0.0002 sec)
MySQL  localhost:3306  mall_db_simple  ★  SQL > UPDATE products SET stock = stock - 20 WHERE id = 1;
Query OK, 1 row affected (0.0003 sec)

Rows matched: 1  Changed: 1  Warnings: 0
MySQL  localhost:3306  mall_db_simple  ★  SQL > SELECT id, stock FROM products WHERE id = 1;
+----+-------+
| id | stock |
+----+-------+
|  1 |    70 |
+----+-------+
1 row in set (0.0003 sec)
MySQL  localhost:3306  mall_db_simple  ★  SQL > savepoint koukuncun2;
Query OK, 0 rows affected (0.0002 sec)
MySQL  localhost:3306  mall_db_simple  ★  SQL > rollback to koukuncun1;
Query OK, 0 rows affected (0.0003 sec)
MySQL  localhost:3306  mall_db_simple  ★  SQL > SELECT id, stock FROM products WHERE id = 1;
+----+-------+
| id | stock |
+----+-------+
|  1 |    90 |
+----+-------+
1 row in set (0.0002 sec)
MySQL  localhost:3306  mall_db_simple  ★  SQL > rollback to koukuncun2;
ERROR: 1305 (42000): SAVEPOINT koukuncun2 does not exist
MySQL  localhost:3306  mall_db_simple  ★  SQL > release savepoint koukuncun1;
Query OK, 0 rows affected (0.0002 sec)
MySQL  localhost:3306  mall_db_simple  ★  SQL > UPDATE products SET stock = stock - 20 WHERE id = 1;
Query OK, 1 row affected (0.0004 sec)

Rows matched: 1  Changed: 1  Warnings: 0
MySQL  localhost:3306  mall_db_simple  ★  SQL > SELECT id, stock FROM products WHERE id = 1;
+----+-------+
| id | stock |
+----+-------+
|  1 |    70 |
+----+-------+
1 row in set (0.0003 sec)
MySQL  localhost:3306  mall_db_simple  ★  SQL > rollback to koukuncun1;
ERROR: 1305 (42000): SAVEPOINT koukuncun1 does not exist

一个会话里可以设置多个保存点。并且可以通过rollback to 保存点回到保存点。

但是，保存点不是变量。就比如上面的例子，创建了保存点koukuncun1后又创建了保存点koukuncun2。然后直接回滚到了koukuncun1。这时候就不能再回滚到koukuncun2了，因为koukuncun2对于koukuncun1来说是未来发生的。
也可以使用release savepoint 保存点指令来释放保存点。释放后的保存点同样不能再回滚。

自动提交？手动提交？

  1. 什么是自动提交（默认）？
    敲一行 SQL，回车，MySQL 立刻把它存进硬盘（Commit）。
  • 状态：autocommit = 1 (开启)
  • 场景：平时在命令行里敲 UPDATE ...，没敲 COMMIT 数据也改了，就是因为这个。
  1. 什么是手动提交？
    需要显式地告诉 MySQL：“我要开始记账了”和“我记完账了”。
  • 状态：autocommit = 0 (关闭)
  • 场景：
    • 方法 A（推荐，临时手动）：输入 START TRANSACTION。此时 autocommit 暂时失效，直到你敲 COMMIT 或 ROLLBACK。
    • 方法 B（全局手动，不推荐）：执行 SET autocommit = 0;。这很危险！如果忘了敲 COMMIT，做了一天的操作，关掉窗口后全都会丢。

通过下面的命令查看当前状态

1
SELECT @@autocommit;

一般都是默认开启了自动提交

1
2
3
4
5
6
7
MySQL  localhost:3306  mall_db_simple  SQL > SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.0001 sec)

事务的隔离级别

MySQL 提供了 4 种级别，安全性从低到高，性能从高到低：

级别 英文名 绰号 特点 会发生什么？
1 Read Uncommitted 读未提交 裸奔 别人还没 Commit 的数据就能看见。（脏读、不可重复读、幻读）
2 Read Committed 读已提交 (RC) 半隔离 别人 Commit 了，才能看见。（幻读和不可重复读）
3 Repeatable Read 可重复读 (RR) 照骗 MySQL 默认 事务一开始，就拍个照。不管别人怎么改，看到的永远是开始时的样子。
4 Serializable 串行化 排队 只要我没搞完，别人连查都不准查。慢到令人发指。

默认可重复读的场景

场景：（窗口 A）在查账，（窗口 B）在改数据。

  1. 窗口 A：开启事务，先查一下库存。

    1
    2
    START TRANSACTION;
    SELECT stock FROM products WHERE id = 1;

  2. 窗口 B：开启事务，卖掉 50 个

    1
    UPDATE products SET stock = 50 WHERE id = 1;

  3. 窗口 A：再次查询库存。

    1
    SELECT stock FROM products WHERE id = 1;
    • 结果：还是 100！ (哪怕窗口 B 已经改成 50 了)
    • 原因：这就是 Repeatable Read。在开启事务的那一刻，MySQL 给他生成了一个“快照”。只要不结束事务，外面的世界天崩地裂，他看到的永远是 100。

  4. 窗口 A：结束查账。

    1
    2
    3
    COMMIT;
    SELECT stock FROM products WHERE id = 1;
    --结果：50

如果窗口A要获取实时数据，就使用

1
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

事务并发执行可能引发的问题

  1. 脏读 (Dirty Read)：
    • 现象：A 读到了 B 还没提交的数据。结果 B 回滚了，A 读到的是假数据。
    • 谁有这毛病：Read Uncommitted。
  2. 不可重复读 (Non-repeatable Read)：
    • 现象：A 第一次读是 100，B 改成 50 提交了，A 第二次读变成 50。A 懵了：“怎么一会儿一个样？”
    • 谁有这毛病：Read Committed 。
  3. 幻读 (Phantom Read)：
    • 现象：A 查所有订单，发现有 5 个。B 偷偷插入了一个新订单。A 准备修改这 5 个订单，结果发现数据库里有 6 个订单受影响。A 觉得出现了幻觉。

在大部分情况下保持默认 (Repeatable Read) 就行

MySQL 的默认设置已经非常优秀，能避免大多数并发坑。

只有在极少数对“实时性”要求极高，且为了减少死锁的场景下（比如 12306 抢票），才会去考虑改为 RC。不过上面的那些内容也是面试狗官爱问的。

间隙锁

间隙锁 (Gap Lock) 是 MySQL (InnoDB) 里的特性，也是只有在 RR（可重复读）隔离级别下才会生效的特殊锁机制。

不需要专门写 SQL 去调用它。只要隔离级别是默认的 Repeatable Read (RR)，并且执行了范围查询的加锁操作，MySQL 就会自动触发间隙锁。

如果没有间隙锁

场景：事务A要查 ID > 10 的所有订单。目前只有 ID=11, ID=13 两条。

动作：事务A执行 SELECT * FROM orders WHERE id > 10 FOR UPDATE;（锁住查到的数据）。

漏洞：如果没有间隙锁，只锁住了 11 和 13。这时候事务B偷偷插入了一条 ID=12 的数据。

结果：事务A还没提交事务，再查一次，发现突然多出来一个 12！见鬼了（幻读）。
间隙锁的作用：
事务A不仅把 11 和 13 锁住，还拉起了警戒线——“把 11 和 13 中间的空位、13 后面的空位全封死！谁也不准往里插数据！”

间隙锁的工作

首先先创建一个很简单的表

1
2
3
4
CREATE TABLE gap_test (
    id INT PRIMARY KEY
) ENGINE=InnoDB;
INSERT INTO gap_test VALUES (1), (5), (10);

在其中一个窗口启动事务查询里面的数据

1
2
3
4
5
6
7
8
9
MySQL  localhost:3306  mall_db_simple  SQL > START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)
MySQL  localhost:3306  mall_db_simple  ★  SQL > SELECT * FROM gap_test WHERE id > 1 AND id < 10 FOR UPDATE;
+----+
| id |
+----+
|  5 |
+----+
1 row in set (0.0004 sec)

这时候再到另一个窗口启动事务，并尝试往这些“空位”里插数据。

1
2
3
4
5
6
MySQL  localhost:3306  SQL > START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)
MySQL  localhost:3306  ★  SQL > use mall_db_simple;
Default schema set to `mall_db_simple`.
Fetching global names, object names from `mall_db_simple` for auto-completion... Press ^C to stop.
MySQL  localhost:3306  mall_db_simple  ★  SQL > INSERT INTO gap_test VALUES (2);

卡在这里不动了，因为在第一个窗口 MySQL 会自动加上间隙锁，锁住 (1, 5) 和 (5, 10) 这两个区间

间隙锁导致的死锁

  1. A 用户想注册名字叫“哈基米”，由于并发逻辑，他先用 SELECT * FROM users WHERE name = '哈基米' FOR UPDATE 查一下有没有。
    • 假设数据库里没这人。MySQL 加上了间隙锁（锁住了这一片不存在的区域）。
  2. B 用户同时也想注册“哈基米”，也执行了同样的 SQL。
    • 间隙锁和间隙锁之间是不冲突的！B 用户也加上了间隙锁。
  3. A 用户执行 INSERT ... '哈基米'。
    • 崩了：A 被 B 的间隙锁挡住了。
  4. B 用户执行 INSERT ... '哈基米'。
    • 崩了：B 被 A 的间隙锁挡住了。

MySQL 报错 “Deadlock found”，其中一个用户直接报错退出。

如果不需要极其严格的防幻读（大多数互联网业务都不需要），可以选择把隔离级别降级为 RC (Read Committed)。在 RC 级别下，间隙锁会自动关闭。这样能大大减少死锁，提升并发度。