SQL基础

查询语句

SELECT

# SELECT
SELECT * FROM table;
SELECT column1, column2 FROM table;
SELECT Now();
SELECT Now() FROM dual; # dual 表示虚拟表，可以忽略
SELECT (1 + 2) AS total;
SELECT COUNT(*) FROM table;

WHERE

# WHERE
SELECT columns_list FROM table_name WHERE query_condition;
SELECT * FROM actor WHERE last_name = 'ALLEN' AND age IN (18, 19);
SELECT * FROM actor WHERE age BETWEEN 16 AND 18;

AND / OR

# AND 运算符的优先级高于 OR 运算符
SELECT 1 OR 0 AND 0; # 1

# AND 语句中，0 的优先级大于 NULL，NULL 的优先级大于 1
# OR 语句中，1 的优先级大于 NULL，NULL 的优先级大于 0

IN / NOT IN

# IN
expression IN (value1, value2, ...);
SELECT NULL IN (1, 2), NULL IN (1, 2, NULL); # NULL NULL
SELECT 1 IN (1, NULL), 2 IN (1, NULL); # 1 NULL

# NOT IN
expression NOT IN (value1, value2, ...);
SELECT 1 NOT IN (1, 2), 3 NOT IN (1, 2); # 0 1
SELECT NULL NOT IN (1, 2), NULL NOT IN (1, 2, NULL); # NULL NULL
SELECT 1 NOT IN (1, NULL), 2 NOT IN (1, NULL); # 0 NULL

BETWEEN

# BETWEEN
expression BETWEEN min AND max;
expression >= min AND expression <= max
expression NOT BETWEEN min AND max;
expression < min OR expression > max

LIKE

# LIKE
# pattern 为字符串，支持通配符 % 和 _
expression LIKE pattern;
expression NOT LIKE pattern;

# a% 匹配以字符 a 开头的任意长度的字符串
# %a 匹配以字符 a 结尾的任意长度的字符串
# %a% 匹配包含字符 a 的任意长度的字符串
# %a%b% 匹配同时包含字符 a 和 b 且 a 在 b 前面的任意长度的字符串
# a_ 匹配以字符 a 开头长度为 2 字符串
# _a 匹配以字符 a 结尾长度为 2 字符串
# 如果 LIKE 运算符的两个操作数中任意一个为 NULL 时，返回 NULL

IS NULL

# IS NULL
expression IS NULL;
expression IS NOT NULL;
SELECT (NULL IN (NULL)) IS NULL; # 1
SELECT 0 IS NOT NULL; # 1

EXISTS

# EXISTS
# 如果子查询 subquery 返回了至少一个数据行，EXISTS 的计算结果为 TRUE，否则为 FALSE
# 一旦子查询找到一个匹配的行，EXISTS 运算就会返回，可用于提高查询性能
# 子查询中 SELECT 的列不影响 EXISTS 的结果
SELECT column_name FROM table_name WHERE EXISTS(subquery);
# 等价于
SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);
# 如果 t2 有数据（至少一行），返回 t1 中所有行的 column1
# 如果 t2 为空表，返回空结果集

ORDER BY

# ORDER BY
# ASC 代表升序，DESC 代表降序，默认值是 ASC
# 指定多个列时，首先按照前面的字段排序，其次按照后面的字段排序
SELECT column1, column2, ...
FROM table_name
[WHERE clause]
ORDER BY
   column1 [ASC|DESC],
   column2 [ASC|DESC],
   ...;

# 使用 CASE 实现自定义顺序
SELECT film_id, title, rating
FROM film
ORDER BY CASE rating
    WHEN 'G' THEN 1
    WHEN 'PG' THEN 2
    WHEN 'PG-13' THEN 3
    WHEN 'R' THEN 4
    WHEN 'NC-17' THEN 5
END;

# 使用 FIELD() 函数实现自定义顺序
SELECT *
FROM film
ORDER BY FIELD(rating, 'G', 'PG', 'PG-13', 'R', 'NC-17');

# 使用 ORDER BY 子句升序 ASC 排序时， NULL 值排在非 NULL 值的前面
# 使用 ORDER BY 子句降序 DESC 排序时， NULL 值排在非 NULL 值的后面

LIMIT OFFSET

# LIMIT OFFSET
LIMIT [offset,] row_count;
# 等价于
LIMIT row_count OFFSET offset;

# offset 为在原始结果集的基础上跳过的行数，row_count 为执行要返回的最大行数
LIMIT 2 5 # 在原始结果集中，跳过 2 个记录行，并从 第 3 个记录行开始，最多返回 5 个记录行

SELECT select_expression, ...
FROM table_name
ORDER BY sort_expression, ...
LIMIT [offset,] row_count;

# 搜索 film 表的 10-20 行数据
SELECT film_id, title FROM film LIMIT 10, 10;

DISTINCT

# DISTINCT
# 在 SELECT 语句中使用 DISTINCT 关键字会返回一个没有重复记录行的结果集
SELECT DISTINCT columns_list FROM table_name；

# 使用 DISTINCT 删除重复的数据，返回唯一的姓氏列表（排除姓氏重复的记录）
SELECT DISTINCT last_name FROM actor;

# 使用 DISTINCT 删除重复的数据，返回唯一的姓名列表（排除所有字段都重复的记录）
SELECT DISTINCT last_name, first_name FROM actor;

# 验证
SELECT last_name, first_name, COUNT(*)
FROM actor
GROUP BY last_name , first_name
HAVING COUNT(*) > 1;

# DISTINCT 认为所有的 NULL 值都是相同的
# 返回一行 NULL 记录
SELECT DISTINCT *
FROM (
    SELECT NULL
    UNION ALL
    SELECT NULL
    UNION ALL
    SELECT NULL
  ) t;

比较运算符汇总

比较运算符	说明	举例
=	等于	age = 18
<>	不等于	age <> 18
!=	不等于	age != 18
>	大于，通常用于比较数字或者日期	age > 18
>=	大于等于，通常用于比较数字或者日期	age >= 18
<	小于，通常用于比较数字或者日期	age < 18
<=	小于等于，通常用于比较数字或者日期	age <= 18
IN	判断值是否在一个集合中	age IN (18, 19)
NOT IN	判断值是否不在一个集合中	age NOT IN (18, 19)
BETWEEN	判断值是否介于两个数中间	age BETWEEN 16 AND 18
LIKE	模糊匹配	name LIKE ‘A%‘
IS NULL	是否为 NULL	name IS NULL
IS NOT NULL	是否不为 NULL	name IS NOT NULL

AND	1	NULL
1	1	NULL
0	0	0
NULL	NULL	NULL

OR	1	0	NULL
1	1	1	1
0	1	0	NULL
NULL	1	NULL	NULL

ORDER BY工作原理

MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

全字段排序

将查询所需的所有字段（city, name, age）一起放入 sort_buffer 排序，排完直接返回。

**缺点：**字段较多导致数据量超过 sort_buffer_size 时，sort_buffer 装不下，可能溢出到磁盘，建立 tmp_files，用归并排序处理多个 tmp_files，性能差。

RowID排序

city varchar(16)   -- 16 字节
name varchar(16)   -- 16 字节
age  int(11)       -- 11 只是显示宽度，不影响存储，int 类型固定占 4 字节

city、name、age 这三个字段的定义总长度是 36，把 max_length_for_sort_data 设置为 16 后，MySQL 认为单行过大，会采用 RowID 算法来排序。

只将 排序字段（name）+ 主键 id 放入 sort_buffer，排完后再回表查询其他字段返回。

**缺点：**多一次回表的磁盘 I/O。

联合索引（包含需要排序的所有字段的索引）

MySQL 之所以需要生成临时表，并且在临时表上做排序操作，其原因是原来的数据都是无序的。

alter table t add index city_user(city, name);

从索引 (city,name) 找到第一个满足 city=‘杭州’条件的主键 id。
到主键 id 索引取出整行，取 name、city、age 三个字段的值，作为结果集的一部分直接返回。
从索引 (city,name) 取下一个记录主键 id。
重复步骤 2、3，直到查到第 1000 条记录，或者是不满足 city=‘杭州’条件时循环结束。

覆盖索引（联合索引的一种用法，包含需要查询的所有字段的索引）

alter table t add index city_user(city, name, age);

从索引 (city,name,age) 找到第一个满足 city=‘杭州’条件的记录，取出其中的 city、name 和 age 这三个字段的值，作为结果集的一部分直接返回。
从索引 (city,name,age) 取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回。
重复执行步骤 2，直到查到第 1000 条记录，或者是不满足 city=‘杭州’条件时循环结束。

方案对比

方案	是否需要排序	是否需要回表	性能
全字段排序	是	否	一般
rowid 排序	是	是	较差
联合索引	否	是	好
联合索引 + 覆盖索引	否	否	最优

多表查询

JOIN

# 内部联接（交集）（INNER JOIN）：返回两个集合满足条件的交集
# 将第一个表的每一行与第二个表的每一行进行比较，如果满足给定的连接条件，则将两个表的行组合在一起作为结果集中的一行
SELECT
  student.*,
  student_score.*
FROM
  student
  INNER JOIN student_score
  ON student.student_id = student_score.student_id;
  # 等价于
  # student, student_score
  # WHERE student.student_id = student_score.student_id;

# 左连接（左集）（LEFT JOIN）：以左表的数据行为基础，根据连接匹配右表的每一行
# 匹配成功则将左表和右表的行组合成新的数据行返回
# 匹配不成功则将左表的行和 NULL 值组合成新的数据行返回
SELECT
  student.*,
  student_score.*
FROM
  student
  LEFT JOIN student_score
  ON student.student_id = student_score.student_id;
  # 等价于
  # LEFT JOIN student_score USING(student_id);

# 右连接（右集，与左连接相反）（RIGHT JOIN）：以右表的数据行为基础，根据连接匹配左表的每一行
# 匹配成功则将右表和左表的行组合成新的数据行返回
# 匹配不成功则将右表的行和 NULL 值组合成新的数据行返回
SELECT
  student.*,
  student_score.*
FROM
  student
  RIGHT JOIN student_score
  ON student.student_id = student_score.student_id;

# 交叉连接（并集）（CROSS JOIN）：返回两个集合的笛卡尔积
# 一个有 m 行的表和另一个有 n 行的表，返回 m * n 行数据
SELECT
  student.*,
  student_score.*
FROM
  student CROSS JOIN student_score;
  # 等价于
  # student, student_score; # 隐式交叉连接

UNION

# UNION
# UNION 双目操作符，需要两个 SELECT 语句作为操作数
# UNION 中的 SELECT 语句中的列数、列顺序必须相同
# UNION 运算包括 UNION DISTINCT 和 UNION ALL 两种算法，DISTINCT 可省略
# UNION DISTINCT：过滤结果集中重复的记录
# UNION ALL：返回结果集中的所有记录
SELECT statement
UNION [DISTINCT | ALL]
SELECT statement

# 想自定义列名，只需要为第一个结果集的列设定一个别名即可
SELECT 2 AS c
UNION
SELECT 1;

增删改语句

INSERT

# INSERT
# 插入单行
INSERT INTO table_name (column_1, column_2, ...)
VALUES (value_1, value_2, ...);

# 插入多行
INSERT INTO table_name (column_1, column_2, ...)
VALUES (value_11, value_12, ...),
       (value_21, value_22, ...)
       ...;

# 插入时未指定的字段（该字段未设置默认值）为 NULL

# max_allowed_packet 配置了服务器和客户端任何单个消息大小的上限，并同样适用于 SELECT 语句
SHOW VARIABLES LIKE 'max_allowed_packet';

# TODO: 修饰符

DELETE

# DELETE
DELETE FROM table_name
[WHERE clause]
[ORDER BY ...]
[LIMIT row_count]

# 删除操作不可恢复，所以删除前最好先备份
CREATE TABLE actor_copy AS (SELECT * FROM actor);

DELETE FROM actor_copy WHERE actor_id = 1;

# 大多数情况下， DELETE 语句中的 LIMIT 子句都应该和 ORDER BY 子句一起使用
DELETE FROM actor_copy
ORDER BY actor_id DESC
LIMIT 10;

# 清空表
DELETE FROM actor_copy;

# 性能更好的清空表命令
TRUNCATE actor_copy;

# 多表删除
DELETE t1, t2
FROM t1 INNER JOIN t2
WHERE t1.id = t2.id;

# 删除 t2 表中对应 id 不存在的 t1 表数据
DELETE t1
FROM
  t1 LEFT JOIN t2 ON t1.id = t2.id
WHERE t2.id IS NULL;

# TODO: 修饰符

UPDATE

# UPDATE
# 更新一行或多行数据
UPDATE [IGNORE] table_name
SET
    column_name1 = value1,
    column_name2 = value2,
    ...
[WHERE clause];

# 修改单列
UPDATE customer
SET email = 'NEW.MARY.SMITH@sakilacustomer.org'
WHERE customer_id = 1;

# 修改多列
UPDATE customer
SET first_name = 'Tim',
    last_name = 'Duncan',
    email = 'Tim.Duncan@sakilacustomer.org'
WHERE customer_id = 1;

# 使用表达式更新（使用 REPLACE 方法）
UPDATE customer
SET email = REPLACE(email, 'sakilacustomer.org', 'sjkjc.com');

# 使用子查询更新（为没有绑定商店的客户绑定一个随机商店）
UPDATE customer
SET store_id = (
    SELECT store_id
    FROM store
    ORDER BY RAND()
    LIMIT 1
  )
WHERE store_id IS NULL;

# TODO: 修饰符

汇总、分组数据

聚合函数

聚合函数忽略了 NULL 值，

函数	含义
MAX / MIN	最大/最小值
AVG	平均值
SUM	求和
COUNT(*)	总行数（含NULL）
COUNT(列名)	非NULL行数
COUNT(DISTINCT 列名)	去重后的计数

案例：

date_range	total_sales	total_payments	what_we_expect (the difference)
1st_half_of_2019
2nd_half_of_2019
Total

USE sql_invoicing;

    SELECT 
        '1st_half_of_2019' AS date_range,
        SUM(invoice_total) AS total_sales,
        SUM(payment_total) AS total_payments,
        SUM(invoice_total - payment_total) AS what_we_expect
    FROM invoices
    WHERE invoice_date BETWEEN '2019-01-01' AND '2019-06-30'

UNION

    SELECT 
        '2st_half_of_2019' AS date_range,
        SUM(invoice_total) AS total_sales,
        SUM(payment_total) AS total_payments,
        SUM(invoice_total - payment_total) AS what_we_expect
    FROM invoices
    WHERE invoice_date BETWEEN '2019-07-01' AND '2019-12-31'

UNION

    SELECT 
        'Total' AS date_range,
        SUM(invoice_total) AS total_sales,
        SUM(payment_total) AS total_payments,
        SUM(invoice_total - payment_total) AS what_we_expect
    FROM invoices
    WHERE invoice_date BETWEEN '2019-01-01' AND '2019-12-31'

GROUP BY

# GROUP BY
SELECT last_name, COUNT(*)
FROM actor
GROUP BY last_name
ORDER BY COUNT(*) DESC;

# 输出
+--------------+----------+
| last_name    | COUNT(*) |
+--------------+----------+
| KILMER       |        5 |
| NOLTE        |        4 |
| TEMPLE       |        4 |
| AKROYD       |        3 |
| ALLEN        |        3 |
| BERRY        |        3 |
...
| WRAY         |        1 |
+--------------+----------+
121 rows in set (0.00 sec)

首先使用 GROUP BY 子句按照 last_name 字段对数据进行分组。
然后使用聚合函数 COUNT(*) 汇总每个姓氏的行数。
最后使用 ORDER BY 子句按照 COUNT(*) 降序排列。

HAVING

HAVING 与 WHERE 的本质区别

WHERE 是对 FROM JOIN 里原表中的列进行 事前筛选，所以WHERE可以对没选择的列进行筛选，但必须用原表列名而不能用SELECT中确定的列别名。

HAVING 对 SELECT 查询后（通常是分组并聚合查询后）的结果列进行 事后筛选，若 SELECT 里起了别名的字段则必须用别名进行筛选，且不能对SELECT里未选择的字段进行筛选。

# HAVING
USE sql_invoicing;

SELECT 
    client_id,
    SUM(invoice_total) AS total_sales,
    COUNT(*/invoice_total/invoice_date) AS number_of_invoices
FROM invoices
GROUP BY client_id
HAVING total_sales > 500 AND number_of_invoices > 5
# total_sales 和 number_of_invoices 均为 SELECT 里的列别名

子查询

在 MySQL 8.0 中，优化器会自动优化 IN 子查询，优化为最佳的 JOIN 执行计划。
当一个子查询位于 FORM 子句中时，这个子查询被称为派生表，派生表必须使用别名，因为 MySQL 规定，任何 FORM 子句中的表必须具有一个名字。
若看到 DEPENDENT SUBQUERY 依赖子查询的执行计划，需要先进行 SQL 重写优化，一般是重写为派生表进行表连接。

# 依赖子查询
SELECT *
FROM orders
WHERE (o_clerk, o_orderdate) IN (
    SELECT o_clerk, MAX(o_orderdate)
    FROM orders
    GROUP BY o_clerk);

# 优化方案
SELECT * FROM orders o1,
(
SELECT o_clerk, MAX(o_orderdate)
FROM orders
GROUP BY o_clerk
) o2
WHERE o1.o_clerk = o2.o_clerk
AND o1.o_orderdate = o2.orderdate;

数据类型

VARCHAR

VARCHAR(max_length)

max_length 是一个数值，它指示了此列的最大字符数，注意不是字节数，默认值是 255。
MySQL 存储 VARCHAR 数值时，会将最前的 1 或者 2 个字节存储为实际字符串内容的长度。
如果列的值少于 2 ^ 8 - 1 = 255 个字节，则长度前缀为 1 个字节，否则为 2 个字节。
VARCHAR 允许的最大长度为 2 ^ 16 - 1 = 65535 个字节。
在 MySQL 中，除了 TEXT 和 BLOB 列，行大小限制为 65535 个字节。
当插入的内容超过 VARCHAR 列定义的长度时。
1. 如果超过的部分只包含空格，则多余的空格会被截断。
2. 如果超过的部分不只是空格，则给出错误提示。
千万不要直接在数据库表中直接存储密码，一旦有恶意用户进入到系统，则面临用户数据泄露的极大风险，一个真正好的密码存储设计，应该是：动态盐 + 非固定加密算法（动态盐，每次用户注册时业务产生不同的盐值，并存储在数据库中。若做得再精细一点，可以动态盐值 + 用户注册日期合并为一个更为动态的盐值）。

CHAR

CHAR(length)

length 是一个数值，它指示了此列的字符数，注意不是字节数。
length 可以是 0 到 255 之间的任何值。
如果我们不指定此值，则默认值是 1，也就是说 CHAR 等同于 CHAR(1)。
如果写入 CHAR 列中的字符串的长度小于指定的字符长度，MySQL 会在源字符串后填充空格一直到指定的长度，读取 CHAR 列中的值时，MySQL 会忽略字符串尾部的空格。
CHAR 数据类型采用固定的长度进行存储，因此 CHAR 的性能要比 VARCHAR 更好。
MySQL 不会自动截断字符串，而是会返回错误。

INT

类型	字节数	最小值	最大值	最大值(无符号)
TINYINT	1	-2^7	2^7-1	2^8-1
SMALLINT	2	-2^15	2^15-1	2^16-1
MEDIUMINT	3	-2^23	2^23-1	2^24-1
INT	4	-2^31	2^31-1	2^32-1
BIGINT	8	-2^63	2^63-1	2^64-1

# INT
INT [UNSIGNED]
INT(display_width) ZEROFILL

# 例：
# v3 INT(3) ZEROFILL
# v3 003

# 表的主键列使用 INT 数据类型列结合 AUTO_INCREMENT 属性作为主键
CREATE TABLE test_int_pk(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name char(30) NOT NULL,
    age INT NOT NULL
);

UNSIGNED 属性标识了此数据类型为无符号整数。
向一个无符号整数列插入负数时， MySQL 会返回错误提示。
display_width 是 INT 的显示宽度属性。
ZEROFILL 属性表示如果没有达到 display_width 指定的显示宽度需要在数值的左侧填充 0。
指定了 ZEROFILL 属性后，MySQL 会自动添加 UNSIGNED 属性到列中。
MySQL 要求 unsigned 数值相减之后依然为 unsigned，否则就会报错，需要对数据库参数 sql_mode 设置为 NO_UNSIGNED_SUBTRACTION，允许相减的结果为 signed，这样才能得到最终想要的结果。
用自增整型做主键，一律使用 BIGINT，而不是 INT，当达到 INT 上限后，再次进行自增插入时，会报重复错误，MySQL 数据库并不会自动将其重置为 1。
在数据库设计中，非常强调定长存储，因为定长存储的性能更好。

DECIMAL

# DECIMAL
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

# 例：
# balance DECIMAL(14, 4) ZEROFILL
# 0000001500.0400

DECIMAL 数据类型是定点数数据类型，用来存储精确的数值，比如账务金额等。
M 是总的位数，不包含小数点和正负号。
D 是小数部分的位数，如果 D 为 0 则表示没有小数部分。当 D 省略时，默认值为 0。
UNSIGNED 属性表示数值是无符号的。无符号的数字不能是负数。
ZEROFILL 属性表示当整数部分位数不足时，整数的左侧用 0 填充。带有 ZEROFILL 的列将自动具有 UNSIGNED 属性。这和 INT 数据类型一样。

BIT

# BIT
BIT(M)
# 数值添加方式为：b/B/0b 为前导，后面添加二进制
# b'01'
# B'01'
# 0b01
# INSERT INTO order_state (order_id, state)
# VALUES (1, 3), (2, b'011'), (3, B'011'), (4, 0b011);

SELECT * FROM order_state WHERE state = 3;
SELECT * FROM order_state WHERE state = (3);
SELECT * FROM order_state WHERE state = b'011';
SELECT * FROM order_state WHERE state = B'011';
SELECT * FROM order_state WHERE state = 0b011;

BIT(M) 允许存储 M 位二进制值，M 的取值范围是 1 到 64，默认值为 1，BIT 等效于 BIT(1)。
如果不指定 M，默认值为 1，BIT 等效于 BIT(1)。
BIN() 转为二进制，OCT() 转为八进制，HEX() 转为十六进制，state+0 转为十进制。
如果使用 BIT 列作为 WHERE 子句中的过滤条件，可以直接使用其对应的十进制或者 BIT 字面值。

DATE

# DATE
column_name DATE;

SELECT NOW(); # 获取当前日期和时间
SELECT CURDATE(); # 获取当前日期
SELECT DATE(); # 获取日期部分
SELECT DATE_FORMAT(); # 格式化输出日期
SELECT DATEDIFF(); # 计算两个日期之间的天数
SELECT DATE_ADD(); # 在给定日期上增加给定的时间间隔
SELECT DATE_SUB(); # 在给定日期上减少给定的时间间隔
SELECT DAY(); # 返回日期中天
SELECT MONTH(); # 返回月份
SELECT QUARTER(); # 返回季节
SELECT YEAR(); # 返回年份
SELECT WEEK(); # 函数返回给定日期是一年周的第几周
SELECT WEEKDAY(); # 函数返回工作日索引
SELECT WEEKOFYEAR(); # 函数返回日历周

# 使用日期格式模式 %m/%d/%Y 格式化当前日期
SELECT DATE_FORMAT(CURDATE(), '%m/%d/%Y') today;

# 从日期中减去一个时间间隔
SELECT CURDATE() `今天`,
    DATE_SUB(CURDATE(), INTERVAL 1 DAY) '一天前',
    DATE_SUB(CURDATE(), INTERVAL 1 WEEK) '一周前',
    DATE_SUB(CURDATE(), INTERVAL 1 MONTH) '一月前',
    DATE_SUB(CURDATE(), INTERVAL 1 YEAR) '一年前';

DATE 使用 yyyy-mm-dd 格式来存储日期值，范围从 1000-01-01 到 9999-12-31。
00-69 范围内的年份值将转换为 2000-2069，70-99 范围内的年份值将转换为 1970-1999。

DATETIME

# DATETIME
# YYYY-MM-DD HH:MM:SS
SET @dt =  NOW();

SELECT
    YEAR(@dt),
    QUARTER(@dt),
    MONTH(@dt),
    WEEK(@dt),
    DAY(@dt),
    HOUR(@dt),
    MINUTE(@dt),
    SECOND(@dt);

默认情况下， DATETIME 值范围从** 1000-01-01 00:00:00** 到** 9999-12-31 23:59:59**。
TIMESTAMP 值范围从 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC。
DATETIME 默认占用 8 个字节，TIMESTAMP 默认占用 4 个字节，DATETIME(6) 依然占用 8 个字节，TIMESTAMP(6) 占用 7 个字节；
更改时区时， TIMESTAMP 以 UTC 存储日期和时间值，该列的值会根据新时区进行调整，所以使用 TIMESTAMP 必须显式地设置时区。
推荐日期类型使用 DATETIME，而不是 TIMESTAMP 和 INT 类型。

YEAR

# YEAR
column_name YEAR

YEAR 数据类型占用 1 个字节，YEAR 值的范围为从 1901 到 2155, 还有 0000。
4 位数字的年份值，从 1901 to 2155。
4 位数字的年份值的字符串形式，从 ‘1901’ 到 ‘2155’。
2 位数字的年份值，从 0 到 99，并按如下规则转换为 4 位数年份：
1. 1 到 69 转换为 2001 到 2069。
2. 70 到 99 转换为 1970 到 1999。
3. 0 转换为 0000。
2 位数字的年份值的字符串形式，从 ‘0’ 到 ‘99’，并按如下规则转换为 4 位数年份：
1. ‘0’ 到 ‘69’ 转换为 2000 到 2069。
2. ‘70’ 到 ‘99’ 转换为 1970 到 1999。

ENUM

# ENUM
ENUM ('v1', 'v2', ..., 'vn')

# 创建
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    state ENUM('Unpaid', 'Paid', 'Shipped', 'Completed') NOT NULL
);

# 查询
SELECT * FROM orders WHERE state = 'Paid';
SELECT * FROM orders WHERE state = 2;
SELECT * FROM orders ORDER BY state;

# 获取完整枚举列表
SELECT column_type
FROM information_schema.COLUMNS
WHERE TABLE_NAME = 'orders'
    AND COLUMN_NAME = 'state';

ENUM 是一个字符串的列表，它定义了一个列中允许的值，列的值只能是创建列时定义的允许值列表中的的一个。
按照列定义时的顺序，Unpaid, Paid, Shipped 和 Completed 的索引分别为 1, 2, 3, 4。
因为定义了 state NOT NULL，当插入一个新行而不指定state 列的值时，MySQL 将使用第一个枚举成员作为默认值。
更改枚举成员需要使用 ALTER TABLE 语句重建整个表，这在资源和时间方面都是昂贵的。
获取完整的枚举列表很复杂，因为需要访问 information_schema 数据库。

TEXT 和 BLOB

SHOW CREATE TABLE; # 展示表和字段创建时的类型

在超出 65536 个字符的情况下，可以考虑使用更大的字符类型 TEXT 或 BLOB，两者最大存储长度为 4G，其区别是 BLOB 没有字符集属性，纯属二进制存储。
常见的字符集有 GBK、UTF8，通常推荐把默认字符集设置为 UTF8，推荐把 MySQL 的默认字符集设置为 UTF8MB4，否则某些 emoji 表情字符无法在 UTF8 字符集下存储。
包括 MySQL 8.0 版本在内，字符集默认设置成 UTF8MB4，8.0 版本之前默认的字符集为 Latin1。
排序规则（Collation）是比较和排序字符串的一种规则，每个字符集都会有默认的排序规则。

JSON

# JSON_EXTRACT 用来从 JSON 数据中提取所需要的字段内容
SELECT
    userId,
    JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.cellphone")) cellphone,
    JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.wxchat")) wxchat
FROM UserLogin;
# 等价于
SELECT
    userId,
    loginInfo->>"$.cellphone" cellphone,
    loginInfo->>"$.wxchat" wxchat
FROM UserLogin;

本质上，JSON 是一种新的类型，有自己的存储格式，还能在每个对应的字段上创建索引，做特定的优化，这是传统字段串无法实现的。
JSON 类型的另一个好处是无须预定义字段，字段可以无限扩展。
JSON 类型比较适合存储一些修改较少、相对静态的数据。
当 JSON 数据量非常大，用户希望对 JSON 数据进行有效检索时，可以利用 MySQL 的函数索引功能对 JSON 中的某个字段进行索引。
MySQL 8.0.17 版本开始支持 Multi-Valued Indexes，用于在 JSON 数组上创建索引，并通过函数 member of、json_contains、json_overlaps 来快速检索索引数据。

事务

# MySQL 的 autocommit 默认开启，每条单独语句自动包装为事务
# 多语句必须手动加 START TRANSACTION

START TRANSACTION;
  # SQL 语句...
COMMIT; # 提交

START TRANSACTION;
  # SQL 语句...
ROLLBACK; # 回滚（撤销）

开启事务命令	一致性视图创建时机	事务ID分配时机
begin/start transaction	执行第一个快照读语句时创建的（第一个 Select 语句）	第一条操作语句执行的时候启动事务（分配事务id）
start transaction with consistent snapshot	执行 start transaction with consistent snapshot 时创建的	事务启动的瞬间

事务ACID

特性	含义	InnoDB 实现机制
原子性 Atomicity	全成功或全回滚，无中间态	undo log（回滚日志）
一致性 Consistency	操作前后数据符合约束，总量不变	由 A + I + D 共同保证
隔离性 Isolation	并发事务互不干扰	MVCC + Undo 版本链
持久性 Durability	提交后永久生效，宕机不丢失	redo log（重做日志）

原子性（Atomicity）

一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节，而且事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性（Consistency）

事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。

隔离性（Isolation）

数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致，因为多个事务同时使用相同的数据时，不会相互干扰，每个事务都有一个完整的数据空间，对其他并发事务是隔离的。

持久性（Durability）

事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

并行事务引发的问题

丢失更新

两个事务同时改同一行，后者覆盖前者。

脏读

读取了另一个事务尚未提交的数据，而该事务最终回滚。

不可重复读

同一事务内两次读同一行，得到不同结果（期间被其他事务修改并提交）。

幻读

同一事务内两次查询，第二次多出了/少了几行（期间被其他事务插入/删除）。

幻读与不可重复读的区别

不可重复读表示同一行数据前后状态不一致，幻读表示多行数据前后数量不一致。

事务隔离级别

# 实践建议：保持默认的 REPEATABLE READ，仅当幻读不可接受时才升级为 SERIALIZABLE
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

隔离级别 / 是否能防止	脏读	丢失更新	不可重复读	幻读	性能
READ UNCOMMITTED 读未提交	否	否	否	否	最高
READ COMMITTED 读已提交	是	否	否	否	高
REPEATABLE READ 可重复读（MySQL 默认）	是	是	是	否	中
SERIALIZABLE 串行化	是	是	是	是	最低

读未提交（read uncommitted）

存在所有事物并行可能引发的问题。

可以读到其他事务未提交的变更，指一个事务还没提交时，它做的变更就能被其他事务看到。

读已提交（read committed）

解决了脏读问题。

仅可以读到其他事务已经提交了的变更，指一个事务提交之后，它做的变更才能被其他事务看到。

可重复读（repeatable read）

解决了脏读、丢失更新、不可重复读问题，很大程度上避免了幻读问题。

事务中重复读取同一条数据时前后始终保证一致，指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别。

串行化（serializable）

解决了脏读、丢失更新、不可重复读、幻读问题。

对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

案例

在不同隔离级别下，事务 A 执行过程中查询到的余额可能会不同：

在读未提交隔离级别下，事务 B 修改余额后，虽然没有提交事务，但是此时的余额已经可以被事务 A 看见了，于是事务 A 中余额 V1 查询的值是 200 万，余额 V2、V3 自然也是 200 万了。
在读提交隔离级别下，事务 B 修改余额后，因为没有提交事务，所以事务 A 中余额 V1 的值还是 100 万，等事务 B 提交完后，最新的余额数据才能被事务 A 看见，因此额 V2、V3 都是 200 万。
在可重复读隔离级别下，事务 A 只能看见启动事务时的数据，所以余额 V1、余额 V2 的值都是 100 万，当事务 A 提交事务后，就能看见最新的余额数据了，所以余额 V3 的值是 200 万。
在串行化隔离级别下，事务 B 在执行将余额 100 万修改为 200 万时，由于此前事务 A 执行了读操作，这样就发生了读写冲突，于是就会被锁住，直到事务 A 提交后，事务 B 才可以继续执行，所以从 A 的角度看，余额 V1、V2 的值是 100 万，余额 V3 的值是 200万。

隔离级别的实现方式

隔离级别	实现方式
读未提交	直接读最新数据，不加任何控制
读提交	MVCC（每次 SELECT 生成新 Read View）即 Multi Version Concurrency Control
可重复读	MVCC（启动事务时生成一次 Read View）即 Multi Version Concurrency Control
串行化	读写锁（读加共享锁，写加独占锁，完全阻塞）

Read View 在 MVCC 里如何工作

聚簇索引记录的版本链（Undo Log）

聚簇索引（聚集索引）定义：

如果为 InnoDB 表指定了主键，默认以主键作为聚簇索引，一个表中最多只能有一个聚集索引。
如果为 InnoDB 表定义了主键，MySQL 使用主键作为聚集索引。
如果表中没有主键，MySQL 将使用第一个所有键列都是 NOT NULL 的 UNIQUE 索引作为聚集索引。
如果 InnoDB 表没有主键或合适的 UNIQUE 索引，MySQL 会在内部生成一个隐藏的聚集索引，名称为 GEN_CLUST_INDEX，该索引建立在一个包含 ROW ID 值的列上。
除聚集索引外的所有索引均为非聚集索引或二级索引。
在 InnoDB 中，二级索引中的每条记录都包含该行的主键列以及非聚集索引中指定的列，MySQL 使用此主键值在聚集索引中进行行查找。

每条聚簇索引记录都有两个隐藏列（trx_id 和 roll_pointer）：

trx_id 为最后修改该记录的事务 ID，当一个事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里。
roll_pointer 指向 undo log 中的上一个版本记录，每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo 日志中，然后这个隐藏列是个指针，指向每一个旧版本记录，于是就可以通过它找到修改前的记录。
每次有事务修改一条记录，就会：
1. 把旧版本写入 undo log。
2. 新版本的 roll_pointer 指向旧版本。
3. 多个历史版本串成一条版本链（最新版本 trx_id=52 - 旧版本 trx_id=51 - 更旧版本 trx_id=50…）。

数据快照（Read View）

Read View 是事务读数据时的”一张快照”，记录了快照创建瞬间数据库的事务状态，包含四个字段：

m_ids：当前所有活跃（已启动但未提交）的事务 ID 列表，min_trx_id 和 max_trx_id 之间的事务如果在 m_ids 中，表示还未提交（不可见）。
min_trx_id：m_ids 中最小的事务 ID，min_trx_id 之前的事务为全部已提交（可见）。
max_trx_id：下一个新事务将被分配的 ID（= 当前最大 ID + 1），max_trx_id 之后的事务为快照创建后才出现（不可见）。
creator_trx_id：创建本 Read View 的事务 ID。

事务访问记录时的各种场景

如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值，表示这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见。
如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值，表示这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见。
如果记录的 trx_id 值在 Read View 的 min_trx_id 和 max_trx_id 之间，需要判断 trx_id 是否在 m_ids 列表中：
1. 如果记录的 trx_id 在 m_ids 列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。
2. 如果记录的 trx_id 不在 m_ids 列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。
遇到不可见版本时，沿着 roll_pointer 顺着版本链往下找旧版本，直到找到第一个可见的版本为止。

这种通过版本链来控制并发事务访问同一个记录时的行为就叫 MVCC（Multi Version Concurrency Control 多版本并发控制）。

一句话总结

读提交和可重复读两个隔离级别本质上是通过事务创建的 Read View 数据快照与聚集索引包含的 Undo Log 交互完成的，两个隔离级别的区别是事务创建 Read View 的时机不同，读提交每次 SELECT 生成新 Read View，可重复读每次启动事务时生成一次 Read View。

trx_id >= max_trx_id 时，记录对当前事务不可见。

trx_id 在 m_ids 列表中时，记录对当前事务不可见。

trx_id < max_trx_id 且不在 m_ids 列表中时记录对当前事务可见。

如果遇到不可见版本，事务沿 roll_pointer（Undo Log）查找，直到获取到最后一个可见版本。

可重复读如何工作

事务 B 的 Read View 启动时创建一次，固定不变。

假设事务 A （事务 id 为 51）启动后，紧接着事务 B （事务 id 为 52）也启动了，则事务 B 的 Read View 为：creator_trx_id = 52，m_ids = [51, 52]，min_trx_id = 51，max_trx_id = 53，接下来：

**事务 B 读（A 未修改）：**记录 trx_id=50 < min_trx_id=51 可见，读到 100 万。
**事务 A 将余额改为200万（未提交）：**版本链：200 万 trx_id=51 - 100 万 trx_id=50。
**事务 B 读（A 未提交）：**最新版本 trx_id=51，在 m_ids 中，即不可见，沿版本链找旧版本 trx_id=50 < 51 可见，读到 100 万。
事务 A 提交。
**事务 B 读（A 已提交）：**Read View 不变，m_ids 还是 [51,52]，trx_id=51 仍在 m_ids 中，即不可见，沿版本链找旧版本 trx_id=50 可见 - 仍读到 100 万。

**结论：**整个事务 B 期间，无论事务 A 是否提交，始终读到 100 万，即可重复读。

读提交如何工作

事务 B 每次 SELECT 都重新创建 Read View。

场景同上，假设事务 A（事务 id 为 51）启动后，紧接着事务 B（事务 id 为 52）也启动了，则事务 B 的 Read View 为：creator_trx_id = 52，m_ids = [51, 52]，min_trx_id = 51，max_trx_id = 53，接下来：

**事务 B 第 1 次读（A 未修改）：**新 Read View 为：m_ids=[51,52]，trx_id=50 < min_trx_id=51，即可见 - 读到 100 万。
事务 A 将余额改为 200 万（未提交）。
**事务 B 第 2 次读（A 未提交）：**新 Read View：m_ids=[51,52]（A 仍活跃），trx_id=51 在 m_ids 中，即不可见，沿版本链找旧版本 trx_id=50，即可见 - 读到 100 万。
事务 A 提交（51 从活跃列表消失）。
**事务 B 第 3 次读（A 已提交）：**新 Read View：m_ids=[52]（A 已不在活跃列表），trx_id=51 < min_trx_id=52，即可见 - 读到 200 万，导致前后不一致（不可重复读问题）。

**结论：**事务 A 提交后，事务 B 立刻能看到新数据，即不可重复读（这正是 RC 级别的特征）。

为什么读提交性能比可重复读更好

读提交每次 SELECT 生成新 Read View，可重复读每次启动事务时生成一次 Read View，看起来可重复读隔离级别生成 Read View 的频率更低，为什么读提交的性能反而比可重复读更高？

拉开两者性能差距的核心原因有三个：

Read View 的生成成本极低，包括拷贝当前活跃事务 ID 列表（一个整数数组），并记录几个 ID 边界值，这个操作极其轻量，耗时在微秒级别，几乎可以忽略。
**长时间维护版本链导致的性能下降：**可重复读要求事务在整个生命周期内看到一致的快照，只要一个事务还没提交，MySQL 就必须保留从该事务启动时刻起所有被修改过的行的历史版本，因为其他并发事务可能需要通过版本链回溯到那个时刻的旧数据。读提交则不同，每次 SELECT 都拿到”当下已提交”的最新版本，旧的 undo log 版本可以被尽快清理（purge），版本链短，读取效率高。
1. 假设一个可重复读事务运行了10分钟，期间有大量写操作。
2. 这10分钟内所有相关行的旧版本 undo log 都不能被清理。
3. 版本链越来越长，而链表的查询时间复杂度为 O(n)。
4. 其他事务读数据时，需要沿版本链遍历更多节点才能找到可见版本。
5. 读性能下降，内存/磁盘压力上升。
**间隙锁（Gap Lock）的额外开销：**可重复读为了防止幻读，在执行当前读（SELECT … FOR UPDATE、UPDATE、DELETE 等）时，会加 next-key lock（记录锁 + 间隙锁），间隙锁会锁住一个范围区间（而不仅仅是某一行），导致锁的粒度更粗，其他事务对该范围的插入操作都会被阻塞，同时，死锁概率更高，两个事务的间隙锁范围一旦交叉，就容易互相等待。读提交不需要防止幻读，因此不使用间隙锁，只加行级记录锁。锁的粒度更小，并发冲突更少，吞吐量更高。

因素	读提交（RC）	可重复读（RR）	性能影响
Read View 生成次数	每次 SELECT 生成	事务启动时生成一次	几乎无影响
Undo Log 版本链长度	短（旧版本可快速清理）	长（需保留到事务结束）	影响巨大
锁机制	仅行锁	行锁 + 间隙锁	影响显著
死锁概率	低	较高	影响并发吞吐

MySQL 可重复读隔离级别能完全解决幻读吗

MySQL 的两种读类型与对应防幻读方案

读类型	典型语句	防幻读机制
快照读	SELECT …	MVCC（多版本并发控制）
当前读	SELECT … FOR UPDATE / DELETE	Next-Key Lock（间隙锁 + 记录锁）

快照读原理：事务开启后第一次查询时生成 Read View，后续所有查询都基于这个快照，中途其他事务插入的数据对本事务不可见。

当前读原理：执行时对查询范围加 Next-Key Lock，其他事务无法在该范围内插入新记录，从而阻断幻读（事务 A 执行了这面这条锁定读语句后，就在对表中的记录加上 id 范围为 (2, +∞] 的 next-key lock（next-key lock 是间隙锁+记录锁的组合））。

幻读未被解决的两个场景

**场景一：**快照读 - UPDATE - 快照读

事务 A 快照读，查 id=5，结果为空（Read View 已建立）。
事务 B 插入 id=5 并提交。
事务 A UPDATE id=5（当前读，写入成功，该行 trx_id 变为事务 A 的 ID）。
事务 A 再快照读 id=5，能看到这条记录了 - 幻读发生。

**根因：**UPDATE 操作使该行”归属”于事务 A，绕过了 Read View 的可见性判断。

**场景二：**快照读 - 他人插入 - 当前读

事务 A 先做快照读，得到 3 条记录。
事务 B 插入符合事务 A 筛选条件的新记录并提交。
事务 A 再做当前读（FOR UPDATE），得到 4 条记录 - 幻读发生。

**根因：**快照读时未加锁，没能阻止事务 B 插入。

两种场景都是事务 A 快照读 - 事务 B 插入记录 - 事务 A 当前读，差别在于场景一事务 B 插入的数据不符合事务 A 的筛选条件，事务 A 多做了一步更新该数据的当前读操作，场景二是事务 B 直接插入了符合事务 A 筛选条件的记录，两种场景都会导致幻读。

结论

MySQL InnoDB 可重复读隔离级别大幅减少了幻读，但未彻底消除。

**最佳实践：**事务开启后，立即执行 SELECT … FOR UPDATE 加 Next-Key Lock，将锁前置，阻止其他事务插入，从而规避上述残留场景。

死锁

定义：两个事务互相持有对方需要的锁，永久等待对方释放（举例：事务 A 锁了顾客表第 1 行，等待订单表第 1 行，事务 B 锁了订单表第 1 行，等待顾客表第 1 行），MySQL 将报错并强制中断其中一个。

减少死锁的方法：

多个事务更新同一批记录时，保持相同的语句顺序。
让事务尽量短小，减少锁定时间。
大事务尽量安排在低峰期运行。

主键和外键

主键

主键就是能唯一标识表中每条记录的字段，主键要短，可唯一标识记录，且永不改变，字段，设为主键后自动变为不可为空。

外键

外键是子表里对父表主键的引用，通过外键建立表与表之间的关联，MySQL 会自动验证数据合法性（子表只能引用父表中真实存在的 id）。

外键约束

当父表中的主键被修改或删除时，规定子表中对应外键如何响应，需要设置约束以防止数据损坏/污染（不一致）。

选项	含义	适用场景
CASCADE	级联——随父表变化而变化	UPDATE 时常用
RESTRICT / NO ACTION	禁止操作——若有子记录，不允许修改/删除父记录	DELETE 时常用
SET NULL	置空——外键置为 NULL，产生”孤儿记录”	几乎不用

三大范式

范式	关键词	核心要求
1NF	单一值	单元格只存一个值，无重复列
2NF	单一功能	每张表只描述一个实体，字段全部属于该实体
3NF	独立	字段不能由表内其他字段计算/推导得出

数据规范化

重复/冗余数据的危害：

浪费存储空间。
修改时需要在多处同步改动，一旦漏改就会出现数据不一致。

规范化的目标：消除冗余，保证数据一致性与完整性。

第一范式：每个单元格只存单一值，不能有重复列

反例：courses 表里有个 tags 字段，存了 “frontend, backend” 这样的多值。

问题：查询困难，想改标签名要锁整张课程表。

解法：单独建一张 tags 表，再用一张链接表（course_tags）来描述课程与标签的多对多关系，两个外键 course_id 和 tag_id 构成了 course_tags 表的联合主键。

第二范式：每张表只描述一个实体，表中所有字段都必须完全属于该实体

反例：把 enrollment_date（注册日期）放进 courses 表——一门课被多名学生多次注册，注册日期不是课程本身的属性。

问题：同一属性在表中反复出现，造成冗余。

解法：把”不完全属于该实体”的字段独立成新表。

将 courses 表的 enrollment_date 字段移入 enrollments 表。
instructor（老师）在多门课里可能重复：单独建 instructors 表，用外键引用。

一句话判断：如果某个字段的值在多条记录里会重复，且它描述的不是本表实体本身，就该移出去。

第三范式：表中的字段不能由其他字段推导而来

反例：发票表里同时有发票额、已付款额、余额，余额 = 发票额 - 已付款额，是冗余字段。

问题：前两个字段更新后若忘记同步更新第三个，就出现矛盾数据（不知道该信哪个）。

解法：删掉可以推导出来的字段，需要时用计算得出即可。同理，有 first_name + last_name 就不需要 full_name。

实用建议：专注于消除重复数据

实际工作中只需记住一个原则：专注于消除重复数据。

判断方法：如果某列出现的是重复的”真实值”（如一遍遍重复的名字），而不是重复的”外键 id”，说明设计还不够规范化。

实操思路（以”用户有多个地址”为例）：

先从概念/逻辑模型出发，识别出两个实体：用户和地址，关系是一对多。
拆成两张表：用户表 + 地址表，地址表用外键引用用户。
重复性降到最低。
**重要前提：永远按业务需求设计。**如果一个用户只有一个地址，一张表就够了，强行拆两张表是过度设计。

创建表

创建和删除数据库

CREATE DATABASE IF NOT EXISTS school; # 已存在则跳过
DROP DATABASE IF EXISTS school; # 已存在则删除

创建表

USE sql_store2; # 选择数据库
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name  VARCHAR(50) NOT NULL,
    points      INT NOT NULL DEFAULT 0,
    email       VARCHAR(255) NOT NULL UNIQUE # 保证唯一性
);

# 常用列属性：
# PRIMARY KEY：主键
# NOT NULL：必填
# DEFAULT 值：默认值
# AUTO_INCREMENT：自动递增
# UNIQUE：值不可重复

修改表

ALTER TABLE customers
    # 增列，可指定位置
    ADD    last_name VARCHAR(50) NOT NULL AFTER first_name,
    # 改列（相当于重置，需写全属性）
    MODIFY first_name VARCHAR(60) DEFAULT '',
    # 删列
    DROP   points;

MODIFY 是重置而非局部修改，务必写全该列所有属性，否则会丢失原有设置（如 NOT NULL）。

修改表间关系

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date  DATE NOT NULL,
    FOREIGN KEY fk_orders_customers (customer_id)
        REFERENCES customers (customer_id)
        ON UPDATE CASCADE
        ON DELETE NO ACTION
);

关键规则：

**外键命名：**fk_子表名_父表名。
**删表/建表顺序：**有依赖关系时，删表先删子表，建表先建父表。

修改主键和外键约束

ALTER TABLE orders
    DROP FOREIGN KEY fk_orders_customers,
    ADD  FOREIGN KEY fk_orders_customers (customer_id)
         REFERENCES customers (customer_id)
         ON UPDATE CASCADE
         ON DELETE NO ACTION;

ALTER TABLE orders
    ADD  PRIMARY KEY (order_id),
    ADD  PRIMARY KEY (order_id, xxx, yyy), # 可设置多个字段作为联合主键
    DROP PRIMARY KEY; # 删除所有主键，无需指定名称

字符集与排序规则

核心概念：

**字符集：**数字与字符互转的规则字典（如 utf8 支持全球语言，latin1 只支持拉丁字母）。

**排序规则：**字符的排序方式（默认 utf8_general_ci，ci = 大小写不敏感），99% 的情况用默认 utf8 即可。

**特殊优化场景：**某列确定只存英文，可改为 latin1，存储空间缩减为原来的 1/3（3字节 - 1字节/字符）。

# 数据库级别
CREATE/ALTER DATABASE db_name CHARACTER SET latin1;

# 表级别
CREATE/ALTER TABLE t1 (...) CHARACTER SET latin1;

# 列级别（加在类型和属性之间）
ALTER TABLE customers
    MODIFY first_name VARCHAR(50) CHARACTER SET latin1 NOT NULL;

存储引擎

引擎	状态	特点
MyISAM	旧，已淘汰	不支持事务、外键
InnoDB	默认（5.5+）	支持事务、外键，应优先使用

# 更换引擎会重建整张表，代价极高，操作期间表不可访问，生产环境谨慎操作
ALTER TABLE customers ENGINE = InnoDB;

索引

索引用法

索引是一种数据结构，例如 B-Tree，它提高了从表中检索数据行的速度，需要额外的写入和存储来维护。

使用主键或唯一键创建表时，MySQL 会自动创建一个名为 PRIMARY 的索引，该索引称为聚集索引。

PRIMARY 索引是特殊的，因为索引本身与数据一起存储在同一个表中，聚集索引强制执行表中行的顺序。

PRIMARY 索引以外的其他索引称为二级索引或非聚集索引。

# UNIQUE 关键字表明此索引为唯一索引，它是可选的
# index_name 是索引的名字，一个表中不应该出现两个相同名字的索引
# USING 子句指定索引的类型，可选 BTREE，HASH
CREATE [UNIQUE] INDEX index_name
[USING {BTREE | HASH}]
ON table_name (column_list)
[algorithm_option | lock_option];

# algorithm_option 指定删除索引的算法，默认为 DEFAULT 即 INSTANT
# DML 为数据库操作语言（增删改等操作）
ALGORITHM [=] {DEFAULT | INPLACE | COPY}
# INSTANT: 操作只修改数据字典中的元数据，在操作的执行阶段，可能会短暂地对表进行独占元数据锁定。表数据不受影响，使操作瞬间完成，允许并发 DML
# INPLACE: 操作避免复制表数据，但可能会就地重建表，在操作的准备和执行阶段，可能会短暂地对表进行独占元数据锁定，通常支持并发 DML
# COPY：对原表的副本进行操作，将原表中的表数据逐行复制到新表中，不允许并发 DML

# lock_option 指定删除索引的并发控制策略
LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
# DEFAULT：给定 ALGORITHM 子句（如果有）和 ALTER TABLE 操作的最大并发级别：如果支持，则允许并发读取和写入，如果不是，则允许并发读取（如果支持），如果不是，则强制执行独占访问
# NONE：如果支持，允许并发读取和写入，否则，会发生错误
# SHARED：如果支持，允许并发读取但阻止写入，即使存储引擎支持给定 ALGORITHM 子句（如果有）和 ALTER TABLE 操作的并发写入，写入也会被阻止，如果不支持并发读取，则会发生错误
# EXCLUSIVE：强制执行独占访问，即使存储引擎支持给定 ALGORITHM 子句（如果有）和 ALTER TABLE 操作的并发读/写，也会这样做

# 在 MySQL 内部，CREATE INDEX 语句被映射为 ALTER TABLE ... ADD INDEX ... 语句

# 创建名字索引
CREATE INDEX first_name ON actor(first_name);

# 查看索引是否创建成功
SHOW INDEXES FROM actor;

# algorithm_option 和 lock_option 用法和特性与创建索引一致
DROP INDEX index_name
ON table_name
[algorithm_option | lock_option];

# 在 MySQL 内部，DROP INDEX 语句被映射为 ALTER TABLE ... DROP INDEX ... 语句

# 阐述名字索引
DROP INDEX first_name ON actor;

# 显示索引
SHOW INDEXES FROM db_name.table_name;
# 等价于
SHOW INDEXES FROM table_name IN db_name;

# MySQL SHOW INDEXES 语句返回以下 15 个列：
# Table：表名
# Non_unique：是否唯一索引，如果不是，则为 1，否则为 0
# Key_name：索引的名称，主键索引的名称固定为 PRIMARY
# Seq_in_index：索引中的列序号，第一列序号从 1 开始
# Column_name：列名
# Collation：排序规则表示列在索引中的排序方式， A 表示升序、 B 表示降序或 NULL 表示未排序
# Cardinality：索引基数，它是索引中唯一值的估计数量，注意，这个数字是不精确的，只是一个估计值，基数越高，查询优化器使用索引进行查找的机会就越大
# Sub_part：索引前缀，如果整个列都被索引，则为 NULL，否则，它会在列被部分索引的情况下显示索引字符数
# Packed：指示键是如何打包的；如果不是，则为 NULL
# Null：如果该列可能包含 NULL 值为 YES，如果不包含则为空白
# Index_type：索引类型，可能的值： BTREE， HASH， RTREE，或 FULLTEXT 
# Comment：未在其自己的列中描述的有关索引的信息
# Index_comment：显示创建索引时使用 COMMENT 属性指定的索引的注释
# Visible：索引对查询优化器是否可见或不可见；如果可见为 YES ，否则为 NO
# Expression：如果索引使用表达式而不是列或列前缀值时有值，此时 column_name 列为 NULL

# 过滤索引类型
SHOW INDEXES FROM sakila.film WHERE Non_unique = 0;

# 唯一键也是一个常用的约束，用来保证表中的一列或几列的中的值是唯一的
# 主键列不能包含 NULL 值，而唯一键列可以包含 NULL 值
# 向数据表添加唯一索引
CREATE UNIQUE INDEX index_name
[USING {BTREE | HASH}]
ON table_name (column_list)
[algorithm_option | lock_option];

# 创建数据表时指定唯一索引
CREATE TABLE table_name(
    ...,
    column_name data_type UNIQUE,
    ...
);

# 创建数据表时指定多个列为唯一索引
CREATE TABLE table_name(
column_name1 column_definition,
column_name2 column_definition,
...,
[CONSTRAINT constraint_name]
    UNIQUE(column_name1,column_name2)
);

# 向表中指定了唯一索引的列插入第二条相同的数据时会报错
# 向表中指定了唯一索引的多个列插入第二条所有唯一索引包含的列的数据都相同的数据时会报错

# MySQL 查询优化器是 MySQL 数据库服务器的一个组件，它为 SQL 语句制定最佳执行计划
# MySQL 优化器通常根据索引基数进行决策。 有时候，虽然你创建了索引，但是你的 SQL 语句却不一定使用索引
# 使用 USE INDEX 语句建议查询优化器去使用指定的命名索引
SELECT column_list
FROM table_name
USE INDEX (index_list)
WHERE condition;

# EXPLAIN 返回值中的 key 列表示本次查询实际使用的索引
EXPLAIN
SELECT *
FROM customer
USE INDEX (idx_last_name_first_name)
WHERE last_name = 'BARBEE';

# 使用 FORCE INDEX 语句强制查询优化器去使用指定的命名索引
SELECT *
FROM table_name
FORCE INDEX (index_list)
WHERE condition;

索引类型

# 复合索引也称为组合索引或者多列索引，是多列上的索引
# 定义多列索引时，应将 WHERE 子句中常用的列放在索引列列表的开头，将不常用的列放在索引列列表的后面，否则 MySQL 优化器可能不会使用索引
CREATE INDEX index_name
ON table_name(column_1, column_2, column_3);

# 如果条件列不构成索引的最左前缀，则查询优化器无法使用索引执行查找
# 案例：
CREATE INDEX index_name
ON table_name(a, b, c);

# 以下情况会使用索引
WHERE a = v1 and b = v2 and c = v3;
WHERE a = v1 and b = v2;
WHERE a = v1;

# 以下情况不会使用索引
WHERE b = v2 and c = v3;
WHERE  c = v3;

# 聚集索引是一种特殊的索引，此索引中的键值的顺序决定了表中相应行的物理顺序
# 由于表中的数据只能按照一种顺序进行存储，因此一个表中最多只能有一个聚集索引

# 如果为 InnoDB 表定义了主键，MySQL 使用主键作为聚集索引
# 如果表中没有主键，MySQL 将使用第一个所有键列都是 NOT NULL 的 UNIQUE 索引作为聚集索引
# 如果 InnoDB 表没有主键或合适的 UNIQUE 索引，MySQL 会在内部生成一个隐藏的聚集索引，名称为 GEN_CLUST_INDEX，该索引建立在一个包含 ROW ID 值的列上

# 除聚集索引外的所有索引均为非聚集索引或二级索引
# 在 InnoDB 中，二级索引中的每条记录都包含该行的主键列以及非聚集索引中指定的列，MySQL 使用此主键值在聚集索引中进行行查找

# Cardinality：索引基数，它是索引中唯一值的估计数量，这个数字是不精确的，只是一个估计值，基数越高，查询优化器使用索引进行查找的机会就越大
# 极端情况下，索引基数为 1 时，优化器不会考虑使用该索引，因为无法提升查询性能

# 查询表中的索引信息
SHOW INDEXES FROM film;

# 隐藏索引是实际存在的，但是对 MySQL 查询优化器不可见的索引
# 即使使用 FORCE INDEX，优化器也不会使用隐藏索引
# 在删除一个索引前，可以先将索引隐藏，如果这不影响性能，再去真正的删除索引
# 隐藏索引对 MySQL 查询优化器是不可见的，但是它是真实存在的，并且对写入操作保持最新

# 创建隐藏索引
CREATE INDEX index_name
ON table_name(c1, c2, ...) INVISIBLE;

# 修改索引可见性
# 不能将主键列上的索引设置为隐藏索引，否则 MySQL 会给出一个错误
ALTER TABLE table_name
ALTER INDEX index_name [VISIBLE | INVISIBLE];

# MySQL 查询优化器默认不使用隐藏索引，但是可以通过系统变量 optimizer_switch 中的 use_invisible_indexes 修改这一行为
SELECT @@optimizer_switch;
SET SESSION optimizer_switch="use_invisible_indexes=on";

# 向表中添加一个前缀索引
CREATE INDEX index_name
ON table_name (column_name(length));

# 创建表时定义一个前缀索引
CREATE TABLE table_name(
  column_list,
  INDEX(column_name(length))
);

# 非二进制字符类型，length 指定的是字符数
# 二进制字符类型，length 指定的是字节数

# CHAR，VARCHAR 和 TEXT 等非二进制字符类型，length 指定用于前缀索引的字符数
# BINARY，VARBINARY 和 BLOB 等二进制字符类型，length 指定用于前缀索引的字节数

# 案例：查询名字前两个字母为"GE"的所有演员
CREATE INDEX idx_first_name_prefix_2
ON actor(first_name(2));

EXPLAIN
SELECT *
FROM actor
WHERE first_name LIKE 'GE%';

# 确定前缀长度时，应该尽量让字符串前缀保持唯一性，唯一性越强，则索引的效率越高

# 可以在创建索引的时候指定索引的顺序，默认情况下，索引按照 ASC 升序存储
# 向数据表添加索引
CREATE [UNIQUE] INDEX index_name
[USING {BTREE | HASH}]
ON table_name (column_name [ASC | DESC], ...)
[algorithm_option | lock_option];

# 创建表时指定索引
CREATE TABLE table_name(
  column_list
  INDEX index_name (column_name [ASC | DESC], ...)
);

索引数据结构

索引是什么

索引是提升查询速度的一种数据结构，之所以能提升查询速度，在于它在插入时对数据进行了排序（缺点是影响插入或者更新的性能）。

为什么选 B+ 树

二叉树、红黑树、跳表、哈希索引等结构适合内存场景，海量数据 + 磁盘存储场景下，B+ 树效率最高。

B+ 树核心特点：

基于磁盘的平衡树，树高通常只有 3-4 层。
可存放千万到亿级排序数据。
查询/插入一条记录只需 3-4 次 I/O（SSD 下约 0.003~0.004 秒）。
特殊情况：高度为 1 的 B+ 树索引，只有一个页，该页既是根节点也是叶子节点。

结构组成： 根节点 - 中间节点 - 叶子节点（叶子节点存放所有排序后的真实数据）。

所有 B+ 树都是从高度为 1 的树开始，然后根据数据的插入，慢慢增加树的高度，高度为 1 的 B+ 树索引中，存放的记录都已经排序好了，若要在一个叶子节点内再进行查询，只进行二叉查找，就能快速定位数据。

随着插入 B+ 树索引的记录变多，1个页（16K）无法存放这么多数据，所以会发生 B+ 树的分裂，B+ 树的高度变为 2，当 B+ 树的高度大于等于 2 时，根节点和中间节点存放的是索引键对，由（索引键、指针）组成。

索引键就是排序的列，而指针是指向下一层的地址，在 MySQL 的 InnoDB 存储引擎中占用 6 个字节。

B+ 树的容量是怎么算的

以 InnoDB 默认页大小 16KB 为例：

非叶子节点（根节点/中间节点）存放的是索引键 + 指针，不存真实数据，每个键值对由两部分组成：

组成	大小	说明
索引键（key）	8 字节	主键类型为 BIGINT，占 8 字节
子节点指针	6 字节	InnoDB 页指针固定 6 字节
合计	14 字节	每个键值对大小

每个节点能存的键值对数 = 页大小 / 每个键值对大小 = 16,384 / 14 ≈ 1170。

层高	存储量估算	原理
高度 1	仅叶子节点，约 32 条记录（每条 500B）	16K / 500B ≈ 32
高度 2	~35,200 条	根节点 1100 个键 × 叶子 32 条
高度 3	~3,800 万条	1100 × 1100 × 32
高度 4	~50 亿条	需要考虑碎片空间，一般记为 60% 利用率

**结论：**高度 4 的 B+ 树能存约 50 亿条数据，查询只需 4 次 I/O。

优化 B+ 树索引的插入性能

**核心问题：**自增 ID 插入是顺序追加，性能好，UUID/随机值插入会导致页分裂，性能差。

**建议：**主键使用自增整型（如 BIGINT AUTO_INCREMENT），避免用 UUID 作主键。

**数据顺序（或逆序）插入：**B+ 树索引的维护代价非常小，叶子节点都是从左往右进行插入，比较典型的是自增 ID 的插入、时间的插入（若在自增 ID 上创建索引，时间列上创建索引，则 B+ 树插入通常是比较快的）。

**数据无序插入：**B+ 树为了维护排序，需要对页进行分裂、旋转等开销较大的操作，另外，即便对于固态硬盘，随机写的性能也不如顺序写，所以磁盘性能也会收到较大影响。比较典型的是用户昵称，每个用户注册时，昵称是随意取的，若在昵称上创建索引，插入是无序的，索引维护需要的开销会比较大。

对于 B+ 树索引，在 MySQL 数据库设计中，仅要求主键的索引设计为顺序，比如使用自增，或使用函数 UUID_TO_BIN 排序的 UUID，而不用无序值做主键。

UUID 由于是无序值，所以在插入时性能比起顺序值自增 ID 和排序 UUID，性能上差距比较明显。

在表结构设计时，主键的设计一定要尽可能地使用顺序值，这样才能保证在海量并发业务场景下的性能。

MySQL 中 B+ 树索引的设计与管理

# 单列索引
ALTER TABLE t ADD INDEX idx_a (a);
# 联合索引（推荐）
ALTER TABLE t ADD INDEX idx_a_b (a, b);

# 看 type 字段：ref/range/const 代表用了索引，ALL 代表全表扫描
EXPLAIN SELECT * FROM User WHERE id = 1;

索引数量没有硬限制（“不能超过5个”是谣言），按业务查询需要创建即可。

清理无用索引

SELECT * FROM sys.schema_unused_indexes
WHERE object_schema != 'performance_schema';

# 先设为不可见，观察业务影响，确认无影响再删除
ALTER TABLE t1 ALTER INDEX idx_name INVISIBLE;
ALTER TABLE t1 ALTER INDEX idx_name VISIBLE; # 恢复

MySQL 为什么用 B+ 树做索引

核心问题：索引存在磁盘上

MySQL 数据持久化在磁盘，磁盘比内存慢万倍以上，索引查询本质是磁盘 I/O，I/O 次数越少，查询越快，适合索引的数据结构至少要满足**“能在尽可能少的磁盘的 I/O 操作中完成查询工作，而且要能高效地查询某一个记录，也要能高效地执行范围查找”**两个要求，树的高度直接决定 I/O 次数，所以目标是：矮而宽的树。

数据结构的演进路径

**有序数组 + 二分查找：**查询快 O(logn)，但插入慢（需整体后移），不适合磁盘。

**二叉查找树（BST）：**二叉查找树的特点是一个节点的左子树的所有节点都小于这个节点，右子树的所有节点都大于这个节点，属于天然二分结构，插入灵活，但存在致命缺陷 - 顺序插入时退化为链表，查询变 O(n)，树高不可控。
自平衡二叉树（AVL 树 / 红黑树）：主要是在二叉查找树的基础上增加了一些条件约束，即每个节点的左子树和右子树的高度差不能超过 1，通过旋转保证树高 O(logn)，解决退化问题，但本质仍是二叉树，节点多时树依然很高，I/O 次数多。
B 树（多叉树）：假设 M = 3，那么就是一棵 3 阶的 B 树，特点就是每个节点最多有 2 个（M-1个）数据和最多有 3 个（M个）子节点，超过这些要求的话，就会分裂节点，树变”矮胖”，减少 I/O，但仍存在问题 - 每个节点同时存索引+数据记录，非目标节点的记录数据白白占用内存和 I/O，范围查询需中序遍历，效率低。
**B+ 树（MySQL 的选择）：**B 树的升级版，解决了上述所有问题。
1. 叶子节点（最底部的节点）才会存放实际数据（索引+记录），非叶子节点只会存放索引。
2. 所有索引都会在叶子节点出现，叶子节点之间构成一个有序链表。
3. 非叶子节点的索引也会同时存在于子节点中，并且是在子节点中所有索引的最大（或最小）。
4. 非叶子节点中有多少个子节点，就有多少个索引。

B+ 树的核心设计

特性	说明
非叶子节点只存索引	不存实际数据，单个节点能放更多索引 - 树更矮，I/O 更少
叶子节点存全量数据	所有索引+记录集中在底层叶子节点
叶子节点用链表串联	支持高效范围查询，直接顺序遍历链表即可
冗余索引	非叶子节点的索引在叶子节点也存在，删除时无需复杂树变形

B+ 树 vs B 树的三个关键优势

**单点查询：**B+ 树非叶子节点不含数据，数据量更少，同阶下树更矮胖，I/O 更少，性能更稳定。
**增删效率：**B+ 树有大量冗余节点，删除/插入最多影响一条路径，变形简单，B 树删除节点可能触发复杂的树重组。
**范围查询：**B+ 树叶子节点通过链表相连，找到起点后直接遍历链表即可，而 B 树只能靠中序遍历，涉及多次随机 I/O。

一句话总结：B+ 树就是 B 树 + “数据下沉到叶子” + “叶子节点串链表”，正是这两点改动，带来了更少的磁盘 I/O 和更高效的范围查询。

InnoDB 的 B+ 树实现细节

叶子节点间用双向链表连接（支持向左/向右遍历）。
**每个节点是一个数据页，默认 16KB，**因为目录项记录非常小（只有主键值 + 页号），所以单个 16KB 的页可以存放大量目录项。
两种索引类型：
1. **聚簇索引：**叶子节点存完整用户记录，每表有且仅有一个。
2. 二级索引：叶子节点只存主键值，可以有多个，查到主键后再回表查聚簇索引。

B+ 树数据页保存的内容	说明
File Header	文件头，包含指向上一页和下一页的双向指针（构成双向链表）
Page Header	页的状态信息
Infimum + Supremum	页内最小和最大的虚拟记录
User Records	实际存放的内容（非叶子节点放目录项，叶子节点放用户记录）
Free Space	页内剩余空闲空间
Page Directory（页目录）	存储各分组最后一条记录的偏移地址（槽），用于页内二分查找
File Trailer	校验页的完整性

	非叶子节点页	叶子节点页
存放内容	索引键 + 子页页号（目录项）	索引键 + 完整用户记录
作用	导航/寻址	存储真实数据
节点间连接	通过 File Header 的双向指针连接	同上，构成有序双向链表
单页容量	可容纳大量目录项（因为记录小）	容量取决于单行数据大小

总结

B+ 树通过”非叶子节点只存索引”让树更矮（减少 I/O），通过”叶子节点链表”支持高效范围查询，通过”冗余节点（非叶子节点中的索引键，在叶子节点中也一定重复存在一份）“简化增删操作，三点合力使其成为数据库索引的最优解。

索引存储

两种数据存储模式对比

**堆表（Heap Table）：**数据与索引分开存储，数据无序堆放，索引叶子节点保存的是数据在堆中的物理地址。

**问题：**数据位置变了，所有索引中的地址都要更新，性能差，OLTP 不友好。

**代表：**Oracle、SQL Server、PostgreSQL（早期/默认）。

PostgreSQL 因只支持堆表，对 OLTP 业务高并发场景存在先天局限性（本质是空间换时间）。

**索引组织表（Index Organized Table，IOT）：**数据按主键顺序直接存储在主键索引（B+ 树）中，叶子节点存储完整行记录。

**核心公式：**数据即索引，索引即数据。

**代表：**MySQL InnoDB（必选），主键索引也叫聚集索引（Clustered Index）。

二级索引（Secondary Index）

除主键索引外，所有其他索引都是二级索引（也叫非聚集索引）。

关键特点：二级索引的叶子节点存储的是索引键值 + 主键值，不含完整行数据。

回表（Bookmark Lookup）：通过二级索引找到主键值后，还需再查一次主键索引才能拿到完整记录，相当于两次 B+ 树查询。

索引组织表这样的二级索引设计有一个非常大的好处：若记录发生了修改，则其他索引无须进行维护，除非记录的主键发生了修改。

SELECT id FROM idx_name WHERE name = ?; # 第一步：查二级索引
SELECT * FROM User WHERE id = _id; # 第二步：回表

**唯一索引：**加了唯一约束的二级索引，可保证业务字段的唯一性（如用户名昵称）。

**二级索引与数据变更的联动：插入一条记录，本质是一个事务同时写主键索引表和所有二级索引表，**任何一步失败则全部回滚。

堆表中的索引都是二级索引，哪怕是主键索引也是二级索引，也就是说它没有聚集索引，每次索引查询都要回表，同时，堆表中的记录全部存放在数据文件中，并且无序存放，这对互联网海量并发的 OLTP 业务来说，堆表的实现的确“过时”了。

二级索引的性能评估

不同字段上的二级索引，性能开销差异很大，核心看插入是否顺序。

在真实业务的表结构设计中，必须对每个核心业务表创建一个列 last_modify_date，标识每条记录的修改时间。

索引	数据特征	性能评价
idx_name（用户名）	随机字符串，完全无序	插入成本高，每次随机 I/O
idx_register_date（注册时间）	时间戳，基本顺序	插入成本低，顺序追加
idx_last_modify_date（修改时间）	插入顺序，但频繁更新	插入成本低，但更新导致额外开销

主键设计原则（影响所有二级索引性能）：

**比较顺序：**对聚集索引插入友好（如自增 ID、顺序 UUID）。
**尽量紧凑：**主键越小，每个二级索引叶子节点占的空间越少，推荐 16 字节顺序 UUID。

函数索引（Functional Index）

索引键是一个函数表达式而不是原始列值。

**用途一：**修复”函数写在左边导致索引失效”的问题。

反例（索引失效）

# 索引 idx_register_date 对 DATE_FORMAT(...) 无效
SELECT * FROM User WHERE DATE_FORMAT(register_date, '%Y-%m') = '2021-01'

**原因：**索引对 register_date 排序，不对 DATE_FORMAT(register_date) 排序，数据库规范要求函数写在等式右边。

**快速解法：**创建函数索引，让索引直接对函数表达式结果排序。

ALTER TABLE User ADD INDEX idx_func_register_date((DATE_FORMAT(register_date,'%Y-%m')));

**最佳解法：**让开发修改 SQL 写法（范围查询代替函数）。

**用途二：**配合虚拟列（Generated Column）。

虚拟列从已有字段计算而来，不占实际存储空间，但可以建索引。

CREATE TABLE UserLogin (
    loginInfo JSON,
    cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone"), # 虚拟列
    UNIQUE KEY idx_cellphone(cellphone) # 本质是函数索引
);

# 使用虚拟列的两种方式（函数调用和直接使用字段都可以）
WHERE loginInfo->>"$.cellphone" = '13918888888'
WHERE cellphone = '13918888888'

SQL 可以直接 WHERE cellphone = ?，简洁且高效，适合对 JSON 字段中的嵌套属性建索引。

总结一句话

MySQL InnoDB 索引组织表的本质是：表的物理存储就是主键索引本身，所有其他索引（二级索引）都只是”指向主键”的辅助结构，查数据必须经过主键这一道门。

理解了这一点，就理解了为什么**主键设计（顺序性、紧凑性）**对整个数据库性能至关重要。

联合索引

什么是组合索引

由多列共同构成的 B+ 树索引（Compound Index），本质结构不变，只是排序键从 1 列变为多列。

关键认知：(a, b) 和 (b, a) 是两个完全不同的索引——排序规则不同，适用场景不同。

组合索引的使用规则（最左前缀原则）

核心原理：(a, b) 索引先按 a 排序，a 相同时再按 b 排序，单独查 b 时，无法利用该排序结构。

SQL 场景	能否用索引 (a, b)
WHERE a = ?	是
WHERE a = ? AND b = ?	是（WHERE 中 a/b 顺序无关）
WHERE a = ? ORDER BY b DESC	是（a 等值 + b 有序）
WHERE b = ?	否（跳过了 a，无法利用排序）
WHERE b = ? ORDER BY a DESC	否（同理）

避免额外排序

**场景：**用户查询自己的订单，并按时间倒序展示。

SELECT * FROM orders WHERE o_custkey = 147601 ORDER BY o_orderdate DESC

问题：原有索引 ORDERS_FK1(o_custkey) 只按 custkey 排序，查出数据后，还需要额外一次 filesort（EXPLAIN 中 Extra: Using filesort），在高并发下严重拖累性能。

解决：创建组合索引，把 custkey + orderdate 一起排序。

ALTER TABLE orders ADD INDEX idx_custkey_orderdate(o_custkey, o_orderdate);

效果：新索引已按 (custkey, orderdate) 排好序，查询时直接按序返回，消除额外排序，EXPLAIN 中 Sort 步骤消失，cost 从高降低。

覆盖索引（避免回表）

**回表：**走二级索引查到主键后，还需要再去主键索引取完整行数据的过程，代价高。

**覆盖索引（Covering Index）：**查询所需的所有列，都包含在组合索引的叶子节点中，直接返回，无需回表。

**场景：**查询用户订单的 custkey、orderdate、totalprice 三列。

SELECT o_custkey, o_orderdate, o_totalprice FROM orders WHERE o_custkey = 147601;

演进过程：

用 idx_custkey_orderdate(o_custkey, o_orderdate) - 叶子节点无 totalprice，还需回表，cost = 6.65。
改建 idx_custkey_orderdate_totalprice(o_custkey, o_orderdate, o_totalprice) - 叶子节点包含全部所需列，无需回表，EXPLAIN 显示 Extra: Using index，cost 降至 2.94。

总结

组合索引的三大核心优势：

覆盖多个查询条件：(a, b) 同时支持 WHERE a = ? 和 WHERE a = ? AND b = ?。
**避免额外排序：**WHERE a = ? ORDER BY b 类查询，利用索引本身的有序性消除 filesort
**实现索引覆盖，避免回表：**将查询所需列全部放入索引，直接命中叶子节点返回数据，性能可提升 10 倍

设计原则：根据业务查询模式，把高频的等值查询列放前面（利用最左前缀），把排序列放后面，再考虑是否需要把 SELECT 列也纳入索引以实现覆盖查询。

索引失效

索引的存储结构

MySQL 默认引擎 InnoDB 用 B+ 树存索引，分两类。

类型	叶子节点存储内容
聚簇索引（主键）	完整数据行
二级索引（普通字段）	主键值（需回表再查数据）

**覆盖索引：**查询字段恰好就在二级索引的叶子节点中，无需回表，性能最优。

六种索引失效场景

**LIKE 左模糊匹配：B+ 树按索引值有序排列，只支持前缀比较。**左模糊不知道从哪里开始找，只能全表扫描。

# 失效（%在左）
WHERE name LIKE '%林'
WHERE name LIKE '%林%'

# 有效（%在右）
WHERE name LIKE '林%'

**对索引列使用函数：**索引存的是字段原始值，不是函数计算后的值，无法命中。

# 失效
WHERE LENGTH(name) = 6;

# 解决：MySQL 8.0+ 可建函数索引
ALTER TABLE t_user ADD KEY idx_name_length ((LENGTH(name)));

**对索引列做表达式计算：**同上，索引存原始值，表达式结果无法直接匹配。

# 失效
WHERE id + 1 = 10;

# 有效（把计算移到右侧）
WHERE id = 10 - 1;

隐式类型转换：MySQL 遇到字符串与数字比较时，自动将字符串转为数字（相当于 CAST(字段 AS int)。

# phone 是 VARCHAR，传入整数 - 失效
WHERE phone = 1300000001;

# id 是 INT，传入字符串 - 仍有效
WHERE id = '1';

# 若索引列是字符串、参数是数字 - 对索引列调用了 CAST - 等同于对索引用函数 - 失效
# 若索引列是数字、参数是字符串 - 对参数做 CAST - 索引列未受影响 - 正常走索引

**联合索引违反最左匹配原则：**联合索引数据按第一列排序，第一列相同才按第二列排序，跳列则无序，无法查找。

# 假设存在联合索引(a, b, c)
# 有效
WHERE a = 1;
WHERE a = 1 AND b = 2;
WHERE a = 1 AND b = 2 AND c = 3;

# 失效（跳过了最左列 a）
WHERE b = 2;
WHERE c = 3;
WHERE b = 2 AND c = 3;

# 特殊情况（索引截断）
# 启用索引下推（ICP），c 的过滤下推到存储引擎层，减少回表，性能更好
WHERE a = 1 AND c = 3;

# 索引下推的大概原理是：截断的字段不会在 Server 层进行条件判断，而是会被下推到存储引擎层进行条件判断（因为 c 字段的值是在 (a, b, c) 联合索引里的），然后过滤出符合条件的数据后再返回给 Server 层。
# 由于在引擎层就过滤掉大量的数据，无需再回表读取数据来进行判断，减少回表次数，从而提升性能。

OR 条件含非索引列：OR 意味着两个条件都必须覆盖，只要有一个条件列没有索引，就必须全表扫描。

# 失效（age 不是索引）
WHERE id = 1 OR age = 18;

# 解决：给 age 建索引
# 此时走 index merge（分别扫两个索引，再合并结果集）

总结

**通用规则：**凡是让 MySQL 无法直接用 B+ 树有序性定位的操作，索引就会失效。

失效场景	根本原因
LIKE 左模糊	B+ 树无法前缀定位
对索引用函数	索引存原始值，函数值未建索引
对索引做表达式	同上
隐式类型转换	等价于对索引列套 CAST 函数
联合索引跳列	B+ 树跳列后数据无序
OR 含非索引列	非索引列必须全表扫，OR 连带失效

索引选择

MySQL 没有使用我创建的索引，根本原因是索引中的数据出了问题，而非 MySQL 选错了索引，MySQL 优化器永远根据成本选择执行计划。

MySQL 如何选择索引（CBO 机制）

**CBO（Cost Based Optimizer，基于成本的优化器）**是 MySQL 的优化器类型，它分析所有可能的执行计划，选择成本最低的那个。

MySQL 数据库由 Server 层和 Engine 层组成：

Server 层有 SQL 分析器、SQL优化器、SQL 执行器，用于负责 SQL 语句的具体执行过程。
Engine 层负责存储具体的数据，如最常使用的 InnoDB 存储引擎，还有用于在内存中存储临时结果集的 TempTable 引擎。

Cost = Server Cost + Engine Cost = CPU Cost + IO Cost

**server_cost 表：**记录 Server 层操作的 CPU 成本。
1. disk_temptable_create_cost：创建磁盘临时表的成本，默认为20。
2. **disk_temptable_row_cost：**磁盘临时表中每条记录的成本，默认为0.5。
3. **memory_temptable_create_cost：**创建内存临时表的成本：默认为1。
4. **memory_temptable_row_cost：**内存临时表中每条记录的成本，默认为0.1。
5. **row_evaluate_cost：**记录间的比较成本，默认为0.1。
6. key_compare_cost：索引键值比较的成本，默认为0.05，成本最小。
7. 磁盘临时表 = 内存临时表成本的 20 倍。
**engine_cost 表：**记录存储引擎层的 IO 成本。
1. io_block_read_cost（读磁盘一页）= 1。
2. memory_block_read_cost（读内存一页）= 0.25。
3. MySQL 优化器认为磁盘 IO = 内存 IO 的 4 倍（可按需修改，如果使用 HDD 硬盘可改为 12.5）。

可通过 EXPLAIN FORMAT=json 查看每条 SQL 的详细成本分解（IO Cost + CPU Cost）。

案例：未能使用创建的索引

两条结构相同的 SQL，仅查询时间范围不同，一条用了全表扫描，另一条用了索引。

SQL	时间范围	执行方式	成本
SQL 1	1994-01-01 ~ 1994-12-31（全年）	全表扫描	592,267
SQL 2	1994-02-01 ~ 1994-12-31（少1月）	索引范围扫描	更低

**根本原因：**回表成本。

二级索引查询需要回表（先找二级索引，再去主键索引取完整数据），当查询范围很大、回表记录数极多时，回表的总 IO 成本反而高于直接全表扫描。

SQL 1 强制使用索引的成本为 844,351 > 全表扫描 592,267，所以优化器选全表。

**结论：**这不是 Bug，是正确的优化决策，同一类型 SQL 因参数不同，执行计划就可以不同。

案例：索引建在低选择性字段上

**背景：**o_orderstatus 字段仅有 3 个值（F=已完成 49%，O=进行中 48.5%，P=支付中 2.5%），属于低选择性字段。

**问题：**MySQL 不知道数据分布，默认认为 P 占 1/3（约 33%）的数据，回表量大，放弃索引选全表。

**实际上：**P 只占 2.5%，用索引查 P 效率极高，应该走索引。

ANALYZE TABLE orders UPDATE HISTOGRAM ON o_orderstatus;

创建后，MySQL 知道 P 仅占 2.5%，查询 WHERE o_orderstatus = ‘P’ 时正确选择索引。

结论：

低选择性字段（如性别）通常不需要索引；
低选择性 + 数据严重倾斜 + 只查少量数据，可以建索引，但需配合直方图校准优化器。

总结

MySQL 不是”选错了索引”，而是它的成本模型告诉它另一种方式更便宜——理解 CBO，才能真正理解索引行为。

知识点	结论
MySQL 优化器类型	CBO，基于成本
查看执行成本工具	EXPLAIN / EXPLAIN FORMAT=json/tree
索引何时失效	回表成本 > 全表扫描成本
高选择性字段	建索引，效果好
低选择性字段	通常不建索引
低选择性但数据倾斜	可建索引 + 创建直方图辅助优化器判断

索引应用

索引的利与弊

优势：提升查询速度、确保数据唯一性、加速排序/分组/连表、支持高效范围查询、提升整体吞吐量。

劣势：占用额外磁盘空间，增删改时需维护索引结构，写入性能下降。

**核心结论：**索引并非越多越好，需合理建立。

各类索引的核心缺陷

索引类型	核心问题
主键索引	主键无序（如 UUID）会频繁触发树结构调整，建议用自增 ID
联合索引	遵循最左前缀原则，查询条件缺少最左字段则失效
前缀索引	节省空间，但无法支持 ORDER BY、GROUP BY、覆盖扫描
全文索引	索引文件大、不能自动更新、对中文支持较差
唯一索引	查询快（找到即停），但插入慢（需校验唯一性）
哈希索引	查询极快（一次哈希计算），但无序，无法排序/分组

建立索引的原则

应该建立索引的情况（8条正向原则）：

频繁用作查询条件的字段。
主外键与连表字段（必须建）。
区分度高的字段（避免性别这类低区分度字段）。
值较长的字段改用前缀索引。
联合索引遵循最左前缀，查询频率高的字段放首位。
经常用于范围查询、排序、分组的字段。
确认不需排序的唯一字段，可改用 Hash 结构。
优先使用联合索引替代多个单值索引（一个联合索引顶多个）。

不应该建立索引的情况（7条警戒线）：

频繁增删改的字段（维护成本高）。
大量重复值的字段（如性别）。
经常参与函数计算的字段。
单表索引数量尽量不超过 5 个（一般控制在 3 个）。
联合索引未认真考虑字段优先级。
表数据量极少时。
字段值无序时（尤其是主键，会导致页分裂）。

**页分裂（Page Split）：**B+ 树要求所有节点中的数据必须保持有序，当使用 UUID 等无序主键的情况下，比如先后插入：cc…、aa…、ff…、bb… 由于 B+ 树必须保持有序，插入 bb 时发现它应该排在 aa 和 cc 之间，但 aa 和 cc 所在的页已经满了，此时就必须发生页分裂（将当前满页的数据从中间劈开，分成两页 - 把新数据插入到合适的位置 - 更新上层节点的索引指针）。

回表问题

**回表：**基于辅助索引查询时，先通过辅助索引找到主键 ID，再通过主键索引获取完整行数据，产生两次查询。

**消除回表的方法：**明确写出所需列（而非 SELECT *），若所需列均在联合索引中，则直接命中索引覆盖，无需回表。

索引失效的九种场景

场景	说明
OR 连接条件	即使字段有索引也会失效
LIKE ‘%xxx’（%开头）	改为 ‘xxx%’ 可用索引
字符串字段不加引号	隐式类型转换导致失效
索引字段参与计算（如 id-1=1）	包括 +、-、*、/ 等运算
索引字段用于函数（如 SUBSTRING(name,…)）	函数计算应放在 = 右侧
违背最左前缀原则	联合索引缺少最左字段
不同字段值互相对比（如 name = sex）	字段间比较不走索引
反向范围查询（NOT IN、!=、IS NOT NULL）	正向范围（BETWEEN、IN）可走索引
走索引扫描行数超过全表 30%	MySQL 自动放弃索引，转为全表扫描

当走索引扫描的行数超过表行数的30%时，MySQL会默认放弃索引查询，转而使用全表扫描的方式检索数据，因此这种情况下走索引的随机磁盘IO，反而不一定有全表的顺序磁盘IO快。

MySQL 的几个索引优化机制

索引覆盖（Index Covering）：查询所需的列全部包含在某索引中，无需回表即可直接返回数据。
1. **关键：**SELECT 后写明具体列，而非 *。
2. 即使违背最左前缀原则，只要查询列都在索引中，也能触发覆盖扫描。
**索引下推（Index Condition Pushdown，ICP）：**将 Server 层的过滤条件下推到存储引擎层，在回表前先过滤掉不符合条件的索引节点，减少回表次数。
1. **示例：**WHERE name LIKE ‘竹%’ AND sex=‘男’，原本需回表2次，开启 ICP 后只需回表1次。
2. 默认开启，可通过 optimizer_switch 控制。
MRR（Multi-Range Read）机制：解决辅助索引回表时产生的离散 IO 问题。
1. 将回表所需的主键 ID 先收集到缓冲区（read_rnd_buffer）。
2. 对 ID 排序后，按顺序 IO 统一回表查询聚簇索引。
3. 减少随机磁盘 IO，提升范围查询的整体效率。
4. 默认开启，可通过 optimizer_switch 控制。
**索引跳跃式扫描（Index Skip Scan）：**允许联合索引跳过第一个字段也能使用索引。
1. 优化器自动对第一字段去重，拼接成多条包含第一字段的查询后做 UNION ALL。
2. 本质是”优化器帮你补全了最左字段”。
3. **限制：**多表联查、GROUP BY、DISTINCT 等情况下无法触发，仅适合第一字段区分度低的场景。

使用索引的核心准则（速记）

避免 OR，改用多条 SQL 或子查询。
模糊查询 % 不放开头，大规模模糊查询用全文索引或 ES。
字符串查询必须加引号，防止隐式转换。
计算逻辑放客户端，不要让索引字段做运算。
函数调用放 = 右侧。
多条件查询必须包含联合索引最左字段。
字段对比查询改为连表或临时表。
避免反向范围条件（NOT IN、!= 等）。
查询时明确列名，利用索引覆盖消除回表。

**为了更好的利用索引，索引列最好设置为 NOT NULL 约束。**有两个原因：

**第一原因：**索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，因为可为 NULL 的列会使索引、索引统计和值比较都更复杂，比如进行索引统计时，count 会省略值为NULL 的行。

**第二原因：**NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，因为 InnoDB 存储记录的时候，如果表中存在允许为 NULL 的字段，那么行格式 (opens new window)中至少会用 1 字节空间存储 NULL 值列表。

索引面试题

https://xiaolincoding.com/mysql/index/index_interview.html

数据管理

启动和停止 MySQL 进程。
管理 MySQL 用户。

MySQL底层原理

架构

整体架构

MySQL 架构分为两层

Server 层：负责连接、解析、优化、执行，主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等）都在 Server 层实现（与存储引擎无关的逻辑全在这里）。
存储引擎层：负责数据的实际存储与读取（不同的存储引擎共用一个 Server 层，默认 InnoDB，InnoDB 支持索引类型是 B+树，且也是默认使用的）。

SQL 执行过程

连接器

# -h 指定 MySQL 服务得 IP 地址，如果是连接本地的 MySQL服务，可以不用这个参数；
# -u 指定用户名，管理员角色名为 root；
# -p 指定密码，如果命令行中不填写密码（为了密码安全，建议不要在命令行写密码），就需要在交互对话里面输入密码
mysql -h$ip -u$user -p;

# TCP 三次握手
客户端  -SYN->  MySQL服务端
客户端  <-SYN+ACK-  MySQL服务端
客户端  -ACK->  MySQL服务端

# 查看 MySQL 被多少客户端连接
show processlist;

# 查询空闲连接的最大存活时间，默认值：28800 秒（即 8 小时）
# 超时后服务端自动断开，客户端不会立刻感知，直到下次发送请求时才会收到报错
SHOW VARIABLES LIKE 'wait_timeout';

# 手动断开指定连接
KILL CONNECTION + <id>;

# 查询连接数量上限，默认值为 151
SHOW VARIABLES LIKE 'max_connections';

基于 TCP（Transmission Control Protocol，传输控制协议）三次握手建立连接。
校验用户名/密码。
读取并缓存该用户权限。
权限在连接建立时读取一次，中途改权限不影响已有连接。
如果一个用户已经建立了连接，即使管理员中途修改了该用户的权限，也不会影响已经存在连接的权限，修改完成后，只有再新建的连接才会使用新的权限设置。

	短连接	长连接
流程	每次执行完 SQL 就断开	多次 SQL 共用一个连接
优点	资源释放及时	减少 TCP 握手/挥手开销
缺点	频繁建连，性能差	内存占用累积，可能导致 MySQL 被系统杀掉

推荐使用长连接，但需要解决内存占用问题：

**方案一：定期主动断开长连接，**让内存随连接断开而释放。
方案二：客户端主动重置连接，调用 mysql_reset_connection() 方法重置连接状态（MySQL 5.7+），这个接口的特点是：
1. 不需要重新建立 TCP 连接。
2. 不需要重新鉴权。
3. 但会把连接恢复到”刚刚建立完”的干净状态，从而释放执行大操作后积累的内存。

查询缓存（MySQL 8.0 已删除）

解析器（词法 + 语法分析）

解析器的职责是：把你写的一串 SQL 字符串，变成 MySQL 内部能理解和处理的结构化数据（语法树）。

整个过程分为两步：词法分析 - 语法分析。

词法分析（Lexical Analysis）：把 SQL 字符串拆解成一个个有意义的词（Token）。MySQL 会逐字符扫描你输入的 SQL，识别出每个词属于哪种类型：是 MySQL 的关键字（保留字），还是用户自定义的非关键字（表名、字段名等）。

Token	类型
SELECT	关键字（Keyword）
username	非关键字（标识符）
FROM	关键字（Keyword）
userinfo	非关键字（标识符）

语法分析（Syntax Analysis）：根据词法分析的结果，按照 MySQL 的语法规则，验证 SQL 是否合法，并构建”语法树”。MySQL 拿着拆好的 Token 列表，对照语法规则逐一检查，判断这条 SQL 是否符合语法。但解析器只负责语法（关键字是否合法、SQL 结构是否符合语法规则等），不负责语义（表是否存在、字段是否存在等）。

执行 SQL（三个子阶段）

预处理阶段：
1. 语义检查：解析器只管语法对不对，预处理器负责检查语法树里的内容”有没有意义”，包括判断表是否存在、字段是否存在等。
2. 符号展开：*** **是一个通配符，预处理器会把它展开为该表的所有具体列名，方便后续模块直接使用。
优化阶段：为 SQL 制定代价最小的执行计划。预处理完成后，MySQL 知道了 SQL 要做什么，但怎么做最高效，这是优化器的工作。假设 product 表有主键索引（id）和普通索引（name），执行：

SELECT id FROM product WHERE id > 1 AND name LIKE 'i%';

这条 SQL 两个索引都可以用，但优化器会选普通索引（name），原因是：

1. 查询结果只需要 id 字段。
2. 普通索引（二级索引）的叶子节点里就存着主键值 id
3. 直接在普通索引上就能拿到结果，不需要回到主键索引再查一次
4. 这就是**覆盖索引优化**——查询成本更低。
5. 查看优化器的选择，**关注输出结果中的两个关键列**。

EXPLAIN SELECT * FROM product WHERE id = 1;

EXPLAIN 列名	含义
key	实际选用的索引（NULL = 全表扫描）
type	访问类型（const > ref > range > ALL，从快到慢）
Extra	附加信息（如 Using index = 覆盖索引，Using index condition = 索引下推）

**执行阶段：按照执行计划，调用存储引擎接口，逐条读取记录，返回给客户端。**执行器是 Server 层与存储引擎层的”中间人”，交互以记录为单位，通过一个 while 循环不断向存储引擎要数据。
1. 方式一：主键索引查询，精确定位，查到即止，效率最高。

SELECT * FROM product WHERE id = 1;

# 访问类型：`const`（主键等值查询，唯一记录）

执行器                          				    InnoDB 存储引擎
   │                                    				  │
   │  ① 第一次查询：把 id=1 交给存储引擎  						│
   │  ──────────────────────────────────► 				│
   │                                      				│  B+ 树定位 id=1
   │  ② 找到记录则返回，找不到报错      						  │
   │  ◄────────────────────────────────── 				│
   │                                      				│
   │  ③ 执行器判断记录是否符合条件，符合则发给客户端			│
   │                                     				  │
   │  ④ 第二次查询（while循环）       	  	  			│
   │  ──────────────────────────────────► 				│
   │     因为是 const 类型，函数指针指向  	 		 		  │
   │     "永远返回-1"的函数，直接退出循环  						│

2. **方式二：全表扫描，逐条扫描全表，每条都判断，效率最低。**客户端看起来是"一次性显示所有结果"，实际上 Server 层是一条一条发给客户端的，客户端等全部收完再统一展示。

SELECT * FROM product WHERE name = 'iphone';  -- name 无索引

# 访问类型：`ALL`（全表扫描）

执行器                              InnoDB 存储引擎
   │                                      │
   │  ① 第一次查询：读取第一条记录      		  │
   │  ──────────────────────────────────► │
   │  ◄────────────────────────────────── │
   │  执行器判断 name 是否='iphone'     	  │
   │     是 -> 发送给客户端              	  │
   │     否 -> 跳过                    	  │
   │                                      │
   │  ② while循环：继续读下一条记录    	    │
   │  ──────────────────────────────────► │
   │  ◄────────────────────────────────── │
   │  … 重复，直到存储引擎返回"读完了"   		  │
   │                                      │
   │  ③ 执行器退出循环，查询结束         	  │

3. **方式三：索引下推。**

重点：索引下推

**核心是减少回表次数。**先理解什么是回表：使用二级索引（普通索引）查询时，叶子节点只存主键值，如果需要获取完整记录，必须拿主键值再去主键索引查一次，这个过程叫”回表”。回表有额外的 I/O 开销。

# 场景： t_user 表对 (age, reward) 建立联合索引
SELECT * FROM t_user WHERE age > 20 AND reward = 100000;

# 由于联合索引遇到范围查询（`>`）就停止匹配，`age` 能用联合索引，`reward` 不能继续用联合索引过滤，但 `reward` 列的值存在联合索引的叶子节点中。

# 不使用索引下推（MySQL 5.6 之前）：
存储引擎（每条记录的处理）               Server 层
        │                                 │
        │  ① 二级索引定位 age>20 的记录 	  │
        │  ② 立刻回表，取完整记录      	 	  │
        │  ─────────────────────────► 	  │
        │                             	  │  ③ 判断 reward=100000？
        │                            	    │     是 -> 发客户端
        │                          	      │     否 -> 丢弃（但回表已做！）
        │  重复以上，直到遍历完      		    │

# 问题：每命中一条二级索引，就回表一次，即使最终 `reward` 不满足条件，这次回表也白做了。

# 使用索引下推（MySQL 5.6+）：
存储引擎（每条记录的处理）        		  Server 层
        │                               	│
        │  ① 二级索引定位 age>20 的记录 		│
        │  ② 先不回表！                 	  │
        │     在索引中判断 reward=100000？	│
        │     否 -> 直接跳过（省掉回表） 			│
        │     是 -> 才执行回表，取完整记录	  │
        │  ─────────────────────────►  	  │
        │                                 │  ③ 判断其他条件（本例无）
        │                              	  │     发送给客户端

# 优化效果：把"筛选 reward"的工作从 Server 层下推到存储引擎层，大量不满足`reward`条件的记录直接在存储引擎内跳过，避免了无效的回表操作。

# 如何识别是否用了索引下推？执行计划中`Extra`列显示`Using index condition`。

引擎分类

MyISAM 和 InnoDB 对比

维度	MyISAM	InnoDB
磁盘文件	.frm（结构） .MYD（数据） .MYI（索引）	.frm（结构） .ibd（数据+索引）
索引类型	只支持非聚簇索引（数据与索引分离）	支持聚簇索引（数据与索引合一）
事务支持	无 undo-log，不支持事务/回滚	有 undo-log，支持完整事务
故障恢复	无 redo-log，宕机数据可能永久丢失	有 redo-log，事务提交后数据永不丢
锁粒度	只有表锁（无聚簇索引，无法实现行锁）	支持行锁 + 表锁
并发性能	低（写时整表锁定，读写互斥）	高（行锁 + MVCC 多版本并发控制，读写可并行）
内存利用	依赖 MySQL Server 缓存，内存开发度低	Buffer Pool + 异步 IO，几乎将内存开发到极致

为什么 MyISAM 无法实现行锁？

MyISAM 有多棵独立的 B+ 树索引，不同索引无法汇聚到同一行上加锁，会产生”一行数据被多个锁覆盖”的冲突。InnoDB 有聚簇索引兜底，所有次级索引最终指向聚簇索引键，只需锁住聚簇索引中那一行即可。

MyISAM 的适用场景

**只读从库（读写分离架构中的 Slave）：**无外部写入，索引查询无需回表，查速快。

**无需事务的静态数据表：**如字典表、文章表、帖子表。

InnoDB 替代 MyISAM 的根本原因

MyISAM 不支持事务、行锁、故障恢复，在并发业务场景下可靠性和性能均不足。InnoDB 全部补齐了这些短板，且内存利用率更高。**MyISAM 是为单机、低并发、简单读写而生；InnoDB 是为高并发、事务安全、可靠存储而生。**现代业务默认用 InnoDB，特殊场景（只读从库）可考虑 MyISAM。

InnoDB存储引擎

记录的存储格式

数据保存位置

InnoDB 引擎下，每张表的数据存储在独立文件 表名.ibd 中（MySQL 5.6.6+ 默认行为）。

表空间的四层结构

层级	大小	说明
段 Segment	不固定	分为数据段、索引段、回滚段
区 Extent	1MB = 64页	保证 B+ 树中相邻的页是物理连续的，避免随机 I/O
页 Page	16KB	读写的最小单元，行数据存在数据页里
行 Row	不固定	具体的记录，按行格式存储

**索引段：**存放 B + 树的非叶子节点的区的集合。

**数据段：**存放 B + 树的叶子节点的区的集合。

**回滚段：**存放的是回滚数据的区的集合，之前讲事务隔离 (opens new window)的时候就介绍到了 MVCC 利用了回滚段实现了多版本查询数据。

InnoDB 行格式

MySQL 有 4 种行格式：Redundant（废弃）、Compact、Dynamic（5.7+ 默认）、Compressed，其中，Dynamic/Compressed 基于 Compact 改进，所以搞懂 Compact 即可。

Compact 行结构 = 额外信息 + 真实数据。

额外信息：

变长字段长度列表：
1. 记录 varchar、text 等变长字段的实际字节数。
2. 按列顺序逆序存放（优化 CPU Cache 命中率）。
3. 字段最大 ≤255 字节用 1 字节表示长度；>255 字节用 2 字节。
4. 表中无变长字段时，此列表不存在。
5. 变长字段长度列表中不需要保存值为 NULL 的变长字段的长度。
NULL 值列表：
1. 用二进制位标记哪些列是 NULL**（1=NULL，0=非NULL）**，逆序排列。
2. 至少占 1 字节（不足 8 位高位补 0）。
3. 所有字段都是 NOT NULL 时，此列表不存在。
4. 这样设计的好处是可以让 NULL 值不占用真实数据区的空间。
记录头信息：
1. delete_mask：删除标记（DELETE 只是标记为 1，不真正删除）。
2. next_record：指向下一条记录（记录间用链表组织）。
3. record_type：记录类型（普通记录/B+树节点/最大最小记录）。

真实数据：

隐藏字段	大小	说明
row_id	6字节	无主键且无唯一约束时才存在
trx_id	6字节	写入该记录的事务ID，必须存在
roll_pointer	7字节	用于实现 Undo Log 的链表，指向上一版本数据，支持 MVCC，必须存在

NULL 会占用空间吗

不占真实数据空间，但会在 NULL 值列表中占用 bit 位（至少 1 字节）。把字段设为 NOT NULL 可节省这 1 字节。

VARCHAR(n)的 n 最大是多少

MySQL 规定除了 TEXT、BLOB 这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节，但这包含了变长字段长度列表和 NULL 值列表的开销。

VARCHAR(n) 字段类型的 n 代表的是最多存储的字符数量，而不是字节大小。

**以单字段、ascii 字符集、允许 NULL 为例：**65535 - 2（变长长度列表）- 1（NULL列表）= 65532。

**UTF-8 下：**65532 / 3 = 21844（一个字符最多占 3 字节）。

以上是针对该表仅存在一列，且该列为主键，类型为 VARCHAR 的情况下的极限场景。

行溢出怎么处理

一页只有 16KB，但一行可能超过 16KB。溢出时：

Compact：真实数据处保留部分数据 + 20 字节指针指向溢出页。

**Dynamic/Compressed：**真实数据处只存 20 字节指针，数据全部放溢出页。

总结

InnoDB 以页为单位读写数据，行记录按 Compact 格式存储，由（变长字段长度列表 + NULL值列表 + 记录头）+（真实数据 + 3个隐藏字段）组成，这个结构决定了 NULL 存储方式、varchar 上限和行溢出的处理逻辑。

char 和 varchar

现代 MySQL 中两者性能差距已很小，空间效率才是主要考量。

**CHAR：**固定长度，存入时自动补空格，取出时自动去除。

**VARCHAR：**可变长度，按实际内容存储，额外用 1-2 字节记录长度。

维度	CHAR	VARCHAR
存储空间	永远占满定义的长度	实际内容长度 + 1~2字节
读取性能	略快（无需算长度）	略慢（需处理长度信息）
适用场景	长度固定的短字符串	长度变化的字符串

从数据页角度看 B+ 树（InnoDB 存储原理）

数据存储的基本单位：数据页

InnoDB 不以”行”为单位读写，而是以**数据页（16KB）**为最小 I/O 单位。

在 File Header 中有两个指针，分别指向上一个数据页和下一个数据页，多个数据页之间用这种双向链表连接，逻辑连续，物理不必连续。

单个数据页内如何快速查找记录

页内记录按主键顺序组成单向链表，但链表遍历慢，所以引入了页目录：

所有记录按主键排序后，分成若干组，每组 4~~8 条（首组1条，尾组1~~8条），每个记录组最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字段（图中粉红色字段）。
页目录用来存储每组最后一条记录的地址偏移量，每组的地址偏移量也称为槽（slot）。
因为记录是按照主键值从小到大排序的，所以查找时先对槽做二分查找定位到所在组，再在组内顺序遍历（最多几条），效率极高。

以上面那张图举个例子，5 个槽的编号分别为 0，1，2，3，4，我想查找主键为 11 的用户记录：

先二分得出槽中间位是 (0+4)/2=2 ，2号槽里最大的记录为 8。
因为 11 > 8，所以需要从 2 号槽后继续搜索记录。
再使用二分搜索出 2 号和 4 槽的中间位是 (2+4)/2= 3，3 号槽里最大的记录为 12。
因为 11 < 12，所以主键为 11 的记录在 3 号槽里。
这里有个问题：槽对应的值都是这个组的主键最大的记录，如何找到组里最小的记录？比如槽 3 对应最大主键是 12 的记录，那如何找到最小记录 9。
解决办法是：通过槽 3 找到槽 2 对应的记录，也就是主键为 8 的记录。主键为 8 的记录的下一条记录就是槽 3 当中主键最小的 9 记录，然后开始向下搜索 2 次，定位到主键为 11 的记录，取出该条记录的信息即为我们想要查找的内容。

多个数据页如何组织：B+ 树

当数据量大，需要多个数据页时，用** B+ 树**管理：

B+ 树中的每个节点都是一个数据页。
非叶子节点：只存索引目录项（键值 + 页号），不存实际数据，用于导航。
叶子节点：存放完整的用户记录，所有叶子节点按照索引键大小排序，通过双向链表相连（利于范围查询）。

设计为”矮胖”树，目的是减少磁盘 I/O 次数。

B+ 树如何实现快速查找主键为 6 的记录，以上图为例：

从根节点开始，通过二分法快速定位到符合页内范围包含查询值的页，因为查询的主键值为 6，在[1, 7)范围之间，所以到页 30 中查找更详细的目录项。
在非叶子节点（页30）中，继续通过二分法快速定位到符合页内范围包含查询值的页，主键值大于 5，所以就到叶子节点（页16）查找记录。
在叶子节点（页16）中，通过槽查找记录时，使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，再遍历槽内的所有记录，找到主键为 6 的记录。

查找流程（以主键=6为例）：根节点 - 二分定位页 - 非叶子节点 - 二分定位页 - 叶子节点 - 页内槽二分 - 组内遍历 - 找到记录。

两种索引类型

因为表的数据都是存放在聚簇索引的叶子节点里，所以 InnoDB 存储引擎一定会为表创建一个聚簇索引，且由于数据在物理上只会保存一份，所以聚簇索引只能有一个。

	聚簇索引	二级索引（非聚簇）
叶子节点存什么	完整数据行	主键值
每表数量	只能有 1 个	可以有多个
索引键选择	主键 > 非 NULL 唯一列 > 隐式自增 id	任意列

**回表：**用二级索引查到主键后，再去聚簇索引取完整数据 - 查了两棵 B+ 树。

**索引覆盖：**查询所需数据直接在二级索引中就能拿到（如只查主键值），只查一棵树，性能更好。

如果某个字段既不是聚集索引，又不是二级索引，匹配时 MySQL 只能从聚簇索引的最左叶子节点开始，逐页、逐行扫描，即全表扫描（Full Table Scan），时间复杂度 O(n)。

总结

InnoDB 以 16KB 数据页为单位存储，页内用槽 + 二分查找定位记录，跨页用 B+ 树导航。

叶子节点存完整数据的是聚簇索引，存主键值的是二级索引，二级索引查非主键字段时需要”回表”，查主键字段时可”索引覆盖”直接返回。

buffer pool

为什么需要 Buffer Pool

MySQL 数据存在磁盘，每次读写磁盘性能极差。InnoDB 引入 Buffer Pool（内存缓冲池）作为缓存层。

**读：**先查 Buffer Pool，命中直接返回，未命中才读磁盘。

写：只改内存中的页（标记为”脏页”），由后台线程批量刷盘。

在 MySQL 启动的时候，InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页。此时这些缓存页都是空闲的，之后随着程序的运行，才会有磁盘上的页被缓存到 Buffer Pool 中。

Buffer Pool 基本结构

查询一条记录，InnoDB 会把整个页（16KB）都加载进来，不是只加载一条记录。

属性	说明
大小	默认 128MB，建议设为物理内存的 60%~80%，参数：innodb_buffer_pool_size
基本单位	页（Page），默认 16KB，与磁盘交互的最小单位
管理单元	每个缓存页都有一个对应的控制块（存页号、地址等元信息）

为了更好的管理这些在 Buffer Pool 中的缓存页，InnoDB 为每一个缓存页都创建了一个控制块，控制块信息包括缓存页的表空间、页号、缓存页地址、链表节点等等，控制块也是占有内存空间的，它是放在 Buffer Pool 的最前面，接着才是缓存页。

三种链表管理缓存页

Free List（空闲链表）：
1. 存放空闲缓存页的控制块。
2. 需要加载新页时，从 Free List 取一个空闲页，用完从链表移除。
3. Free 链表上除了有控制块，还有一个头节点，该头节点包含链表的头节点地址，尾节点地址，以及当前链表中节点的数量等信息。

Flush List（脏页链表）：
1. 存放被修改过、尚未写入磁盘的页。
2. 后台线程遍历此链表，将脏页批量刷盘。
3. Flush 链表上除了有控制块，还有一个头节点，该头节点包含链表的头节点地址，尾节点地址，以及当前链表中节点的数量等信息。

LRU（Least recently used） List（冷热数据链表）：
1. 链表头部的节点是最近使用的，而链表末尾的节点是最久没被使用的，当空间不够时，就淘汰最久没被使用的节点，从而腾出空间。
2. 简单 LRU（Least recently used）算法：
  1. 当访问的页在 Buffer Pool 里，就直接把该页对应的 LRU 链表节点移动到链表的头部。
  2. 当访问的页不在 Buffer Pool 里，除了要把页放入到 LRU 链表的头部，还要淘汰 LRU 链表末尾的节点。
3. 同时管理干净页和脏页。
4. 目标：让热数据留在内存、冷数据被淘汰。

总结：
1. Free Page（空闲页），表示此页未被使用，位于 Free 链表。
2. Clean Page（干净页），表示此页已被使用，但是页面未发生修改，位于LRU 链表。
3. Dirty Page（脏页），表示此页已被使用且已经被修改，其数据和磁盘上的数据已经不一致。当脏页上的数据写入磁盘后，内存数据和磁盘数据一致，那么该页就变成了干净页。
4. 脏页同时存在于 LRU 链表和 Flush 链表。

LRU 算法的问题与优化

问题 1：预读失效：MySQL 会提前加载相邻页（预读），但这些页可能从未被真正访问，却占据了 LRU 头部，挤掉真正的热数据。

**优化 1：LRU 分区（解决预读失效）：**将 LRU 链表分为两段：

Young 区（前 63%）：热数据。
Old 区（后 37%）：冷数据，参数 innodb_old_blocks_pct 控制比例。
规则：预读的页只进 Old 区头部，只有被真正访问后才升入 Young 区头部。如果预读页一直没被访问，最终从 Old 区末尾淘汰，不影响热数据。

**问题 2：Buffer Pool 污染：**全表扫描（如 SELECT * FROM t WHERE name LIKE ‘%xx%‘）会把大量冷数据依次插入 LRU 头部，将所有热数据全部挤出。

**优化2：Old 区停留时间门槛（解决 Buffer Pool 污染）：**进入 Young 区的条件必须同时满足：

页被访问了。
该页在 Old 区停留时间 > 1秒（innodb_old_blocks_time 参数控制），这样，全表扫描中每个页虽然被访问了，但由于停留时间极短（毫秒级），无法升入 Young 区，热数据得以保留。

**额外优化：**Young 区前 1/4 的页被访问后不移动到头部，避免频繁操作链表。

脏页何时刷入磁盘

触发刷盘的四个时机：

redo log 写满时 - 强制触发刷盘。
Buffer Pool 空间不足 - 需要将一部分数据页淘汰掉，如果淘汰的是脏页，先将脏页同步到磁盘。
MySQL 空闲时 - 后台线程定期刷。
MySQL 正常关闭时 - 全部刷盘。

崩溃不丢数据靠 redo log（WAL - Write Ahead Log 机制：先写日志，再写磁盘）。

为什么不直接把数据写到磁盘

既然每次修改数据都需要先写redo log，需要写到磁盘，为什么不直接把数据写到磁盘呢？

写入逻辑不同：
1. **直接写数据到磁盘 = 随机写：**数据页分散在磁盘的不同位置。比如你修改了第 100 页和第 5000 页的数据，磁盘磁头需要先跳到第 100 页写，再跳到第 5000 页写——这是随机 I/O，磁头寻道耗时极长，性能很差。
2. **写 redo log = 顺序写：**redo log 是一个追加写的日志文件，每次都写在末尾，磁头不需要来回寻道——这是顺序 I/O，速度比随机写快几十倍甚至上百倍。
写入量不同：
1. **直接写数据到磁盘：**数据页是 16KB，即使只改了页里的一个字段，也要把整个 16KB 写入磁盘。
2. 写 redo log：redo log 只记录”在哪个页、哪个偏移量、改了什么值”，通常只有几十字节，所以 redo log 的写入量也远小于直接写数据页。

WAL：用一次快速的顺序写（redo log），换掉一次缓慢的随机写（数据页），脏页刷盘的时机可以异步、批量进行，性能大幅提升。这就是数据库领域经典的 WAL（Write Ahead Logging）预写日志思想。

写缓冲 change buffer（普通索引 vs 唯一索引）

普通索引 vs 唯一索引：在业务已经保证数据不重复的前提下，两种索引逻辑等价，但性能有差异，该怎么选？

查询性能：差异微乎其微

**普通索引：**找到 k=5 后，继续往后扫描，直到遇到第一个 k≠5 的记录才停。

**唯一索引：**找到 k=5 后，立即停止。

**差距：多一次”判断并向后找”。因为 InnoDB 以数据页（默认 16KB）**为单位读取，k=5 的记录和紧邻记录通常在同一页内，多做一次判断的代价可以忽略不计。

更新性能：差异显著（关键在 change buffer）

**merge 操作：**将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

**change buffer 是什么：**当更新的目标数据页不在内存中时，InnoDB 不立即读盘，而是把操作记录到 change buffer（可持久化到磁盘）。下次有查询需要读这个页时，再将数据页读入内存并 merge，从而减少随机读 IO。

普通索引：

目标页在内存中：找到对应的位置，插入值，语句结束。

目标页不在内存中：将更新记录在 change buffer，语句结束。

唯一索引：

目标页在内存中：找到对应的位置，判断是否有冲突，插入值，语句结束。

目标页不在内存中：将数据页读入内存，判断是否有冲突，插入值，语句结束。

**为什么唯一索引不能用 change buffer：**唯一索引在写入时必须先判断是否违反唯一性约束，这必须把数据页读入内存才能检查。既然页已经在内存里，直接更新就好，change buffer 就没有意义了。

change buffer 使用场景

场景	是否适合用普通索引的 change buffer 机制
写多读少（账单、日志类系统）	最适合，收益最大
写完立即查询	反而增加维护代价（写入即触发 merge，相当于多了一步）
机械硬盘的历史归档库	特别适合，随机读 IO 代价高，节省显著

change buffer vs redo log（容易混淆）

redo log：把随机写磁盘变成顺序写（节省的是写 IO）。

change buffer：把随机读磁盘延迟到合并时才读（节省的是读 IO）。

最终结论

优先选普通索引。

**查询性能：**两者几乎无差别。

**更新性能：**普通索引配合 change buffer，在写多读少、机械硬盘场景下优势明显。

**例外：**若业务无法保证数据唯一性（需数据库兜底），则必须用唯一索引，此时本文讨论的优化意义仅在排查性能问题时参考。

锁

锁粒度

根据加锁范围，MySQL 锁分三层：全局锁 / 表级锁 / 行级锁。

全局锁

锁住整个数据库，使其进入只读状态，会话断开时，全局锁会被自动释放。

# 使用全局锁
FLUSH TABLES WITH READ LOCK;


# 释放全局锁
UNLOCK TABLES;

**唯一场景：**全库逻辑备份（防止备份期间数据不一致）。

**缺点：**备份期间业务完全不可写。

**替代方案：**InnoDB 支持事务，可用 mysqldump —single-transaction，利用 **MVCC + 可重复读，**在不加全局锁的情况下完成一致性备份，MyISAM 因不支持事务，只能用全局锁。

表级锁（4种）

表锁（最粗粒度）：
1. **读锁（LOCK TABLES t READ）：表级别的共享锁，**本线程和其他线程都只能读，写操作会发生阻塞，同时本线程不能访问其他表。
2. **写锁（LOCK TABLES t WRITE）：表级别的独占锁，**只有本线程可读写，其他线程完全阻塞。
3. InnoDB 中一般不用，粒度太粗，影响并发。
元数据锁（MDL Meta Data Lock，自动加）：
1. 执行 CRUD - 自动加 MDL 读锁。
2. 执行表结构变更（ALTER）- 自动加 MDL 写锁。
3. 目的：防止 CRUD 期间表结构被修改。
4. 释放时机：事务提交后才释放（不是语句结束）。
5. 危险场景：长事务持有 MDL 读锁 - ALTER 申请写锁被阻塞 - 后续所有查询也被阻塞（写锁优先级高于读锁，形成队列堵死）- 线程爆满。
  1. 首先，线程 A 先启用了事务（但是一直不提交），然后执行一条 select 语句，此时就先对该表加上 MDL 读锁。
  2. 然后，线程 B 也执行了同样的 select 语句，此时并不会阻塞，因为「读读」并不冲突。
  3. 接着，线程 C 修改了表字段，此时由于线程 A 的事务并没有提交，也就是 MDL 读锁还在占用着，这时线程 C 就无法申请到 MDL 写锁，就会被阻塞，那么在线程 C 阻塞后，后续有对该表的 select 语句，就都会被阻塞。
  4. 如果此时有大量该表的 select 语句的请求到来，就会有大量的线程被阻塞住，这时数据库的线程很快就会爆满了。
  5. 对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了 MDL 读锁，如果可以考虑 kill 掉这个长事务，然后再做表结构的变更。
意向锁（表级，自动加）：
1. 目的：让”加表锁”时无需遍历每行记录，直接检查表上是否有意向锁即可，提升效率。
2. 对行加共享锁前，先在表上加意向共享锁（IS）。
3. 对行加独占锁前，先在表上加意向独占锁（IX）。
4. 意向锁之间不互斥，只与表锁冲突。
5. 表锁和行锁是满足读读共享、读写互斥、写写互斥的。
6. 普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。
AUTO-INC 锁：
1. 保证自增主键连续递增，插入时加锁，插入语句执行完立即释放（不等事务提交）。
2. 优化：MySQL 提供轻量级自增锁，申请到自增值后立即释放，无需等语句结束，并发更高。
3. 通过 innodb_autoinc_lock_mode 控制：
  1. **= 0：**传统 AUTO-INC 锁。
  2. **= 1：**混合模式（普通 insert 申请后马上释放，批量 insert 语句结束后才释放）。
  3. **= 2：**全部轻量锁，性能最高，但需配合 binlog_format=row 才能保证主从一致。
  4. 当 innodb_autoinc_lock_mode = 2 是性能最高的方式，但是当搭配 binlog 的日志格式是 statement 一起使用的时候，在主从复制的场景中会发生数据不一致的问题。

行级锁（3种 + 1特殊）

仅 InnoDB 支持，普通 SELECT 不加行锁（走 MVCC 快照读）。

这种查询会加锁的语句称为锁定读，下面这两条语句必须在一个事务中，因为当事务提交了，锁就会被释放，所以在使用这两条语句的时候，要加上 begin、start transaction 或者 set autocommit = 0。

X 与 S，X 与 X 锁之间都不兼容，S 与 S 锁之间可以兼容。

SELECT ... LOCK IN SHARE MODE; # S锁
SELECT ... FOR UPDATE; # X锁

Record Lock（记录锁）：
1. 锁住单条记录。
2. **S锁：**读读共享，读写互斥。
3. **X锁：**读写、写写均互斥。
4. 当一个事务对一条记录加了 S 型记录锁后，其他事务也可以继续对该记录加 S 型记录锁（S 型与 S 锁兼容），但是不可以对该记录加 X 型记录锁（S 型与 X 锁不兼容）。
5. 当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁（S 型与 X 锁不兼容），也不可以对该记录加 X 型记录锁（X 型与 X 锁不兼容）。
Gap Lock（间隙锁）：
1. 锁住两条记录之间的空隙，不锁记录本身，如 (3, 5)，该锁将阻止其他事务插入 id=4 这条记录。
2. 目的：防止幻读（阻止在间隙内插入新记录）。
3. 间隙锁之间相互兼容（两个事务可同时持有同一间隙锁）。
4. 存在于可重复读和串行化隔离级别。
Next-Key Lock（邻键锁）= 记录锁 + 间隙锁：
1. **锁住范围 + 记录本身，**比如，一个事务持有了范围为 (1, 10] 的 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，就会被阻塞。
2. 既防止幻读，又保护当前记录。
3. X 型 Next-Key Lock 之间互斥（因为包含记录锁）。
4. InnoDB 默认的行锁类型。
插入意向锁（特殊间隙锁）：
1. 插入意向锁是”插入操作遇到间隙锁时，阻塞等待”期间产生的锁，表示”我想插入某个点”。
2. 本质是行级别的特殊间隙锁，不是意向锁。
3. 关键：插入意向锁与间隙锁互斥（不能在一个间隙锁区间内同时有插入意向锁）。
4. MySQL 加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁。

总结

锁类型	粒度	是否自动	核心作用
全局锁	整库	手动	全库备份
表锁	整表	手动	并发控制（粗）
MDL 锁	整表	自动	保护表结构
意向锁	表（行的前置）	自动	加表锁时快速判断
AUTO-INC 锁	表	自动	保证自增连续
记录锁	单行	手动/自动	保护记录
间隙锁	区间	自动	防幻读
邻键锁	区间+记录	自动（默认）	防幻读+保护记录
插入意向锁	点（等待态）	自动	标识等待插入

MySQL 行级锁加锁规则

https://xiaolincoding.com/mysql/lock/how_to_lock.html

哪些语句会加行级锁

语句类型	是否加锁	锁类型
普通 SELECT	否（快照读，MVCC）	/
SELECT … LOCK IN SHARE MODE	是	S 锁（共享锁）
SELECT … FOR UPDATE（当前读）	是	X 锁（独占锁）
UPDATE / DELETE	是	X 锁（独占锁）

**S 锁：**读读兼容，读写互斥。

**X 锁：**写写互斥，读写互斥。

行级锁的三种类型

类型	作用范围	防止
Record Lock（记录锁）	锁住一条具体记录	其他事务修改/删除该记录
Gap Lock（间隙锁）	锁一个区间，不含边界记录，如 (1, 5)	其他事务在间隙内插入新记录
Next-Key Lock（邻键锁）	记录锁 + 间隙锁，前开后闭，如 (1, 5]	修改/删除该记录 + 间隙内插入

基本单位是 Next-Key Lock，但在能使用记录锁或者间隙锁就能避免幻读现象的场景下， Next-Key Lock 就会退化成记录锁或间隙锁。

**目的：**够用就好，不过度加锁。

核心加锁规则

唯一索引（仅针对主键）等值查询：
1. 记录存在时 - next-key lock 退化为 X 型记录锁。
2. **原因：**唯一性保证没有重复记录，记录锁已足够防幻读。
3. 记录不存在时 - 在第一条大于查询值的记录上，next-key lock 退化为间隙锁。
4. **原因：**记录不存在无法加记录锁，只需封堵插入间隙即可。
**唯一索引（仅针对主键）范围查询：**扫描到的每条记录都加 next-key lock，但在边界位置按以下规则退化，尾部还会对 supremum pseudo-record（最大哨兵记录）加 (max, +∞] 的 next-key lock。
1. 针对 **> **的范围查询的情况。
  1. 最开始要找的第一行是 id = 20，由于查询该记录不是一个等值查询（不是大于等于条件查询），所以对该主键索引加的是范围为 (15, 20] 的 next-key 锁。
  2. 由于是范围查找，就会继续往后找存在的记录，虽然我们看见表中最后一条记录是 id = 20 的记录，但是实际在 Innodb 存储引擎中，会用一个特殊的记录来标识最后一条记录，该特殊的记录的名字叫 supremum pseudo-record，所以扫描第二行的时候，也就扫描到了这个特殊记录的时候，会对该主键索引加的是范围为 (20, +∞] 的 next-key 锁。
  3. 停止扫描。

2. 针对 **>=** 的范围查询的情况。
    1. 最开始要找的第一行是 id = 15，由于查询该记录是一个等值查询（等于 15），所以该主键索引的 next-key 锁会退化成记录锁，也就是仅锁住 id = 15 这一行记录。
    2. 由于是范围查找，就会继续往后找存在的记录，扫描到的第二行是 id = 20，于是对该主键索引加的是范围为 (15, 20] 的 next-key 锁。
    3. 接着扫描到第三行的时候，扫描到了特殊记录（ supremum pseudo-record），于是对该主键索引加的是范围为 (20, +∞] 的 next-key 锁。
    4. 停止扫描。

3. 针对 **< 或 <=** 的范围查询，查询条件值的记录**不存在**表中的情况。
    1. 最开始要找的第一行是 id = 1，于是对该主键索引加的是范围为 (-∞, 1] 的 next-key 锁。
    2. 由于是范围查找，就会继续往后找存在的记录，扫描到的第二行是 id = 5，所以对该主键索引加的是范围为 (1, 5] 的 next-key 锁。
    3. 由于扫描到的第二行记录（id = 5），满足 id < 6 条件，而且也没有达到终止扫描的条件，接着会继续扫描。
    4. 扫描到的第三行是 id = 10，该记录不满足 id < 6 条件的记录，所以 id = 10 这一行记录的锁会退化成间隙锁，于是对该主键索引加的是范围为 (5, 10) 的间隙锁。
    5. 由于扫描到的第三行记录（id = 10），不满足 id < 6 条件，达到了终止扫描的条件，于是停止扫描。

4. 针对 **< 或 <=** 的范围查询，查询条件值的记录**存在**表中的情况。
    1. 最开始要找的第一行是 id = 1，于是对该记录加的是范围为 (-∞, 1] 的 next-key 锁。
    2. 由于是范围查找，就会继续往后找存在的记录，扫描到的第二行是 id = 5，于是对该记录加的是范围为 (1, 5] 的 next-key 锁。
    3. 由于主键索引具有唯一性，不会存在两个 id = 5 的记录，所以不会再继续扫描，于是停止扫描。

5. 针对 **<** 的范围查询时，查询条件值的记录**存在**表中的情况。
    1. 最开始要找的第一行是 id = 1，于是对该记录加的是范围为 (-∞, 1] 的 next-key 锁.
    2. 由于是范围查找，就会继续往后找存在的记录，扫描到的第二行是 id = 5，该记录是第一条不满足 id < 5 条件的记录，于是该记录的锁会退化为间隙锁，锁范围是 (1,5)。
    3. 由于找到了第一条不满足 id < 5 条件的记录，于是停止扫描。

**非唯一索引等值查询：**涉及两套索引同时加锁（二级索引 + 主键索引）。
1. 记录存在 - 扫描直到第一条不符合条件的记录：
  1. 符合条件的二级索引记录：加 next-key lock。
  2. 第一条不符合的二级索引记录：next-key lock 退化为间隙锁。
  3. 符合条件的主键索引：加记录锁。
2. 记录不存在 - 第一条不符合的二级索引记录：
  1. next-key lock 退化为间隙锁，主键索引不加锁。
**非唯一索引范围查询：**与唯一索引的关键区别是，next-key lock 永远不退化（无论等值边界是否存在），所有扫描到的二级索引记录均加 next-key lock，同时对符合条件的主键加记录锁。
**无索引的查询：**全表扫描 - 每一行都加 next-key lock = 相当于锁全表。

一句话记住核心逻辑

能用更小的锁防止幻读，就退化为更小的锁，防幻读的本质是防插入，记录锁防不了插入，间隙锁才能防插入——这就是各种”退化”规则背后唯一的驱动力。

实践警示

线上执行 UPDATE、DELETE、SELECT … FOR UPDATE 时，务必确认走了索引，否则全表每行都加 next-key lock，等同于锁表，极易造成大面积阻塞。

MySQL 大表加索引的锁机制

两个核心锁概念

两阶段锁协议（InnoDB 事务锁）
1. 锁在”需要时”加上，但不会立刻释放，必须等到事务结束才释放。
2. 结果：长事务 = 长时间持锁，阻塞其他操作。
MDL 锁（Metadata Lock）
1. 对表做增删改查（DML）时，自动加 MDL 读锁。
2. 对表做结构变更（DDL，如加索引、加字段）时，自动加 MDL 写锁。
3. 关键规则：读锁与写锁互斥，写锁优先级高于读锁。

事故是怎么发生的（锁冲突链）

**Session A：**开启事务，执行查询 - 持有 MDL 读锁，事务长时间未提交 - 长时间不释放锁。
**Session C：**执行 DDL（加索引） - 申请 MDL 写锁 - 被 A 的读锁阻塞 - 等待。
**Session D：**执行普通查询 - 申请 MDL 读锁 - 因写锁优先级高，被 C 阻塞 - 等待。

结论：一个未提交的长事务，会卡住 DDL（数据定义语言，这里表示加索引的过程），DDL 反过来又卡住所有后续读写请求，最终导致连接耗尽、服务不可用。

两种解决方案对比

两者都不能完全消除风险，只能尽量缩短持锁时间。

	Online DDL（MySQL 5.6+）	pt-osc（Percona 工具）
原理	原地修改，中间阶段降级为 MDL 读锁，允许正常读写	创建临时表 + 触发器同步数据 + rename
执行步骤	拿 MDL 写锁 - 降级为读锁 - 执行 DDL - 升级为写锁 - 释放	建临时表并变更结构 - 建触发器同步增量 - 拷贝存量数据 - rename - 删触发器
业务影响	步骤 1、4 时间极短，只要无锁冲突影响极小，步骤 3 期间这个表可以正常读写数据	rename/触发器创建时需要获取 MDL 写锁，同样可能等待
风险点	若存在长事务，升级 MDL 写锁时会堵塞	同左，另有死锁风险，过程耗时更长

根本原因与最佳实践

**根本原因：**长事务 + DDL 操作 + 并发请求，三者叠加必出问题。

避险要点：

**消灭慢 SQL / 长事务：**事务执行越快，MDL 读锁持有时间越短，DDL 被阻塞的概率越低。
**选择低峰期操作：**业务请求少时，锁冲突概率接近于零。
**监控工具：**用 SHOW PROCESSLIST 和 SELECT * FROM information_schema.INNODB_TRX 在执行前确认无长事务。

一句话总结

加索引本质是抢 MDL 写锁，只要存在未提交的长事务（持有 MDL 读锁），DDL 就会永久等待，并顺带阻塞所有后续读写，最终雪崩。解法不在于用哪个工具，而在于保证执行 DDL 时没有长事务。

MySQL 大表加索引中的锁知识

为什么 DDL 会锁表

****早期 MySQL（5.6.7 之前）DDL 执行时需要独占锁，期间阻塞写操作。

早期 DDL 两种方式的锁机制

两者共同点：都会锁表，都不允许 DML（增删改）。

方式	机制	锁行为
copy table	建临时表 - 逐行复制数据 - rename	全程禁写，允许读
inplace（仅索引）	建临时 frm 文件 - 构建索引 - rename	全程禁写，允许读

绕过锁表的三种方案

影子策略（手动操作）：
1. 核心思路：在新表上建索引，再做表交换。
2. **缺陷：**新旧表交换期间，老数据的修改/删除会丢失，数据一致性无法保障。
pt-online-schema-change（工具方案）：
1. **核心思路：**用三个触发器（INSERT / UPDATE / DELETE）实现新旧表的实时数据同步，解决了影子策略的数据丢失问题。
2. 全程数据变动都能同步，安全性更高。
Online DDL（MySQL 5.6.7+ 原生支持，5.7+ 成熟推荐）：
1. 核心思路：通过锁降级，把独占锁时间压缩到极短的瞬间。

Online DDL 锁机制详解（重点）

**完整流程概括：**获取 MDL 写锁 - 降级为 MDL 读锁 - 执行 DDL（允许 DML）- 升级为 MDL 写锁 - 释放锁。

Online DDL 在执行期间，会开辟一个 row log 缓冲区，专门用来记录 DDL 执行期间所有并发的 DML 变更，如果 DDL 期间 DML 量太大、row log 写满了，Online DDL 会报错回滚，并不会静默丢数据。

**Initialization（初始化）：**加 MDL 读锁（禁止其他线程改表结构，允许 DML）- 评估存储引擎能力，确定并发策略。
Execution（执行，最耗时）：
1. **Prepared：**短暂加 MDL 写锁（极短），完成后降为读锁。
2. **Executed：**真正执行 DDL，此时持有 MDL 读锁，允许 DML。
**Commit（提交）：**MDL 读锁升级为 MDL 写锁（独占，但耗时极短）- 替换旧表定义 - 释放 MDL 锁。最终一致性靠写锁保障，第三阶段升级写锁的瞬间，是为了保证 row log 应用完毕、新旧定义完成原子替换，这一刻才真正达到一致。所以写锁虽然存在，但只需极短时间。

**关键结论：**DDL 真正执行时是 MDL 读锁，不锁表，可以并发写，只有开始和结束瞬间需要写锁，业务影响极小。

Online DDL 并不是”无视 DML 的存在”，而是通过 row log 追写的方式，将并发 DML 的影响延迟收敛，最终在提交时保证索引与数据完全一致，这也是它能做到”不锁表”同时又保证数据正确性的根本原因。

核心概念总结

概念	说明
MDL 读锁	允许并发 DML，禁止修改表结构
MDL 写锁	独占，禁止一切并发读写
锁降级	Online DDL 将写锁降为读锁，是”在线”的关键
DML	INSERT / UPDATE / DELETE，业务的增删改操作
DDL	CREATE / ALTER / DROP，表结构变更操作

死锁

核心锁类型

锁类型	锁住的范围	作用
Record Lock（记录锁）	某一行记录本身	防止其他事务修改该行
Gap Lock（间隙锁）	两条记录之间的空隙	防止其他事务往这个区间插入新数据
Next-key Lock（临键锁）	间隙 + 右侧那条记录 = 前开后闭区间	同时防止幻读和记录修改
插入意向锁	插入点（一个具体位置）	并发插入时的协调锁，属于特殊间隙锁

关键兼容规则：

间隙锁之间互相兼容（多个事务可同时持有同一间隙的间隙锁）。
插入意向锁与间隙锁互相冲突（这是死锁的根源）。
X 型 next-key lock 之间冲突，但范围含 +∞ 的除外（因为 +∞ 不是真实记录）。

死锁是怎么发生的

**场景：**两个事务并发插入新订单（1007 和 1008），都先用 select … for update 检查订单是否存在。

步骤分解：

事务A 执行 select … where order_no=1007 for update - 因 1007 不存在，加了 (1006, +∞] 的 next-key lock（X型）。
事务B 执行 select … where order_no=1008 for update - 同样加了 (1006, +∞] 的 next-key lock（X型）- 两者兼容，互不阻塞**（因为包含 +∞，无真实记录冲突）**。
事务A 尝试 insert order_no=1007 - 需获取插入意向锁，但事务B持有该范围间隙锁 - 被阻塞，等B释放。
事务B 尝试 insert order_no=1008 - 需获取插入意向锁，但事务A持有该范围间隙锁 - 被阻塞，等A释放。

**结果：**A等B，B等A - 循环等待 - 死锁。

插入意向锁是一种特殊的间隙锁，只锁指定插入的一条记录，但与间隙锁、next-key lock 等锁不兼容，需要等待其他事务释放间隙锁后，插入意向锁才能从等待状态变为正常状态，解除阻塞并插入数据。

如果 LOCK_MODE 是 next-key 锁或者间隙锁，那么 LOCK_DATA 就表示锁的范围最右值，此次的事务 A 的 LOCK_DATA 是 supremum pseudo-record，表示的是 +∞。然后锁范围的最左值是 t_order 表中最后一个记录的 index_order 的值，也就是 1006。因此，next-key 锁的范围 (1006, +∞]。

**场景：**如果没有使用 select … for update 语句，而使用了单纯的 select 语句，如果是两个订单号一样的请求同时进来，就会出现两个重复的订单，则有可能出现幻读。

begin;
# 对读取的记录加共享锁
select ... lock in share mode;
commit; # 锁释放

begin;
# 对读取的记录加独占锁
select ... for update;
commit; # 锁释放

# 查看事务执行 SQL 过程中加了什么锁
select * from performance_schema.data_locks\G;

# 如果 LOCK_MODE 为 X，说明是 X 型的 next-key 锁
# 如果 LOCK_MODE 为 X, REC_NOT_GAP，说明是 X 型的记录锁
# 如果 LOCK_MODE 为 X, GAP，说明是 X 型的间隙锁

如果 update 语句的 where 条件没有用到索引列，那么就会全表扫描，在一行行扫描的过程中，不仅给行记录加上了行锁，还给行记录两边的空隙也加上了间隙锁，相当于锁住整个表，然后直到事务结束才会释放锁，可以将 MySQL 里的 sql_safe_updates 参数设置为 1，开启安全更新模式，如果 where 条件带上了索引列，但是优化器最终扫描选择的是全表，而不是索引的话，我们可以使用 force index([index_name]) 可以告诉优化器使用哪个索引，以此避免有几率锁全表带来的隐患。

INSERT 语句的加锁机制

**正常插入：**不加锁，使用隐式锁（依赖记录上的 trx_id 隐藏列保护）。

两种触发显式锁的场景：

插入位置有间隙锁 - 生成插入意向锁 INSERT_INTENTION，状态为等待 - Insert 被阻塞。
唯一键冲突：
1. 如果主键索引重复，事务会给已存在的主键值重复的聚簇索引记录添加 S 型记录锁。
2. 如果唯一二级索引重复，事务都会给已存在的值重复的二级索引记录添加 S 型 next-key 锁。

冲突类型	加锁行为
主键重复	对已存在记录加 S 型记录锁
唯一二级索引重复	对已存在记录加 S 型 next-key lock

特殊场景：两个事务插入同一条唯一索引记录时。

事务 A 先插入，记录受隐式锁保护（无实际锁结构）。
事务 B 插入相同记录时，触发唯一冲突 - 事务 A 的隐式锁升级为 X 型记录锁 - 事务 B 想加 S 型 next-key lock - 冲突 - 事务 B 阻塞。

如何避免死锁

**死锁的四个必要条件：**互斥、占有且等待、不可强占用、循环等待。

被动策略（死锁发生后处理）
1. 超时回滚：innodb_lock_wait_timeout（默认50秒），超时自动回滚某个事务。
2. 主动检测：innodb_deadlock_detect = on（默认开启），检测到死锁立即回滚其中一个事务。
主动预防（从业务角度）
1. **将 order_no 设为唯一索引：**直接利用数据库唯一性约束替代 select … for update 的幂等检查，从根本上消除产生死锁的锁竞争链路。

一句话总结

select … for update 在记录不存在时会加间隙锁，而间隙锁之间兼容但与插入意向锁冲突，当两个事务都持有同一间隙锁后再尝试插入，就形成了循环等待 - 死锁。

SQL 锁与死锁分析

实验场景

**表结构：**t_student，主键 id，其余为普通字段（无二级索引）。

**已有数据中关键记录：**id=20、id=30（之间无 id=25/26 的记录）。

**隔离级别：**可重复读（RR）。

两个事务按如下顺序执行，发生死锁。

三种行锁的核心区别

锁类型	LOCK_MODE 标识	锁的范围	作用
记录锁	X, REC_NOT_GAP	单条记录	锁住已存在的行
间隙锁	X, GAP	区间 (左, 右)，开区间	防止区间内插入新数据
Next-Key锁	X	区间 (左, 右]，左开右闭	记录锁 + 间隙锁的组合
插入意向锁	INSERT_INTENTION	一个点	INSERT 操作前申请的特殊间隙锁

逐步加锁分析

**T1：**事务 A 执行 UPDATE WHERE id=25（记录不存在）。

加表级意向独占锁（IX）。

加行级间隙锁，范围 (20, 30)。

**原因：**id=25 不存在，在 RR 可重复读隔离级别下为防止幻读，锁住该间隙。

**T2：**事务 B 执行 UPDATE WHERE id=26（记录不存在）。

同样加行级间隙锁，范围 (20, 30)。

与事务 A 的间隙锁不冲突——间隙锁之间天然兼容（共同目的是阻止插入，互不排斥）。

**T3：**事务 A 执行 INSERT id=25。

**插入前检查：**id=25 的下一条记录 id=30 上，是否有间隙锁。

发现事务 B 持有 (20, 30) 的间隙锁，id=25 在此范围内。

事务 A 生成插入意向锁，状态为等待（等事务 B 的间隙锁释放）。

插入意向锁 vs 间隙锁 - 冲突，事务 A 阻塞。

**T4：**事务 B 执行 INSERT id=26。

同理，发现事务 A 持有 (20, 30) 的间隙锁。

事务 B 生成插入意向锁，等待事务 A 的间隙锁释放。

但事务 A 也在等事务 B - 循环等待 - 死锁。

死锁的本质

**死锁的四个必要条件：**互斥、占有且等待、不可强占用、循环等待。

**互斥：**间隙锁阻止插入意向锁。

**占有且等待：**各自持有间隙锁，同时等待对方释放。

**不可强占：**锁不能被抢占。

**循环等待：**A 等 B 的间隙锁，B 等 A 的间隙锁。

根因一句话：两事务的间隙锁范围相同（可共存），但各自的 INSERT 操作又需要向对方的间隙锁范围内申请插入意向锁，形成循环依赖。

关键结论

间隙锁与间隙锁兼容（可同时存在）。
插入意向锁间隙锁冲突（INSERT 会被阻塞）。
UPDATE 不存在的记录在 RR 可重复读隔离级别下会产生间隙锁（防幻读副作用）。
死锁触发模式：两事务先各持间隙锁，再交叉向对方的间隙内 INSERT。

排查死锁

锁类型

**X锁（独占锁）：X 是 Exclusive 的缩写，**写操作（DELETE/UPDATE/INSERT）申请，互斥，任何其他锁都不能同时持有。

**S锁（共享锁）：**读操作申请，可与其他S锁共存，但与X锁互斥。

锁粒度

**记录锁（Record Lock）：**只锁命中的那一行。RR隔离级别 + 唯一索引等值查询 → 加记录锁，而非 Next-Key 锁。

间隙锁（Gap Lock）：锁住记录间的间隙，防止幻读。

插入意向锁（Insert Intention Lock）：INSERT 第二阶段申请，是一种特殊的间隙锁。

关键机制：INSERT 的两阶段加锁

INSERT 不是直接申请 X 锁，而是分两步。

**阶段1：**申请 S 锁 - 检查唯一索引是否重复（duplicate key check）。

**阶段2：**检查通过后，S 锁升级为 X 锁（Insert Intention）- 实际插入。

这是本文死锁的根本诱因。

常规死锁

步骤	事务1	事务2
1		begin
2		delete from test where a = 2; 执行成功，事务 2 占有 a=2 下的 X 锁，类型为记录锁。
3	begin
4	delete from test where a = 2; 事务 1 希望申请 a=2 下的 X 锁，但是由于事务 2 已经申请了一把 X 锁，两把 X 锁互斥，所以 X 锁申请进入锁请求队列。
5		insert into test (id, a) values (10, 2); 由于 a 字段建立了唯一索引，所以需要申请 S 锁以便检查duplicate key，由于插入的 a 的值还是 2，所以排在 X 锁后面。但是前面的 X 锁的申请只有在事务 2 commit 或者 rollback 之后才能成功，此时形成了循环等待，死锁产生。
结果	循环等待，产生死锁。	事务 1 被回滚（权重低）。

**循环链：**事务 1 等待事务 2 释放 X 锁，事务 2 等待 S 锁，S 锁排在事务 1 的 X 锁请求之后。

高并发下的死锁

步骤	事务1	事务2
1		begin
2		delete from test where a = 2; 执行成功，事务 2 占有 a=2 下的 X 锁，类型为记录锁。
3	begin
4		insert 第 1 阶段：insert into test (id, a) values (10, 2); 事务 2 申请 S 锁进行 duplicate key 检查，检查成功。
5	delete from test where a = 2; 事务 1 希望申请 a=2 下的 X 锁，但是由于事务 2 已经申请了一把 X 锁，两把 X 锁互斥，所以 X 锁申请进入锁请求队列。
6		insert 第 2 阶段：insert into test (id, a) values (10, 2); 事务 2 开始插入数据，S 锁升级为 X 锁，类型为 insert intention，X 锁进入队列排队，形成循环等待，死锁产生。
结果	循环等待，产生死锁。	事务 1 被回滚（权重低）。

**与场景一的核心差别：**事务 2 等待的是 X（INSERT INTENTION）锁而不是 S 锁。

如何读死锁日志

SHOW ENGINE INNODB STATUS;

日志分两段：

WAITING FOR THIS LOCK TO BE GRANTED - 事务1在等什么锁。
HOLDS THE LOCK(S) + WAITING FOR THIS LOCK TO BE GRANTED - 事务2持有什么锁、又在等什么锁。

通过两段日志还原出循环等待链，再逆向推断出死锁成因。

排查死锁的方法论（总结）

查看死锁日志（SHOW ENGINE INNODB STATUS）。
识别循环等待：谁持有什么锁？谁在等什么锁？
结合 SQL 分析加锁类型（X/S/记录锁/Gap锁/Insert Intention）。
逆向推断：是什么操作顺序导致了这个循环？
找到根因并修复（如调整操作顺序、减少事务范围等）。

避免死锁

开启锁监控

# 开启标准监控
SET GLOBAL innodb_status_output=ON;

# 开启锁监控
SET GLOBAL innodb_status_output_locks=ON;

# 记录所有死锁到错误日志
SET GLOBAL innodb_print_all_deadlocks=ON;

# 监控默认关闭，会影响性能且使日志膨胀，分析完及时关闭

# 只能看最近一次死锁
SHOW ENGINE INNODB STATUS;

读懂死锁日志

*** (1) TRANSACTION # 事务1的基本信息
*** (1) WAITING FOR ... # 事务1在等什么锁
*** (2) TRANSACTION # 事务2的基本信息
*** (2) HOLDS THE LOCK(S) # 事务2持有什么锁（即造成事务1等待的锁）
*** (2) WAITING FOR ... # 事务2在等什么锁
*** WE ROLL BACK TRANSACTION (2) # InnoDB自动回滚代价小的事务

关键字段解读：

**ACTIVE 0 sec inserting：**事务活跃时间与当前动作。
**11 lock struct(s)：**持有的锁结构总数（含表锁、行锁）。
**2 row lock(s)：**行锁数量。
**undo log entries 15：**已修改 15 条聚集索引，说明该事务不只执行了日志里那一条 SQL。

死锁诊断的局限性

死锁日志每个事务只显示一条 SQL，而实际事务往往包含多条 SQL。

靠日志单独逆推原因非常困难，必须结合：

应用程序代码（查看完整事务逻辑）。
binlog（只要不是 100% 必现，binlog 里能还原完整事务 SQL 序列）。

三种典型死锁场景

**案例一：**加锁顺序相反（最经典）。

事务A：锁 id=20 - 等 id=30。

事务B：锁 id=30 - 等 id=20。

结论：互相持有对方需要的锁 - 死锁。

**案例二：**间隙锁 + 插入意向锁冲突。

事务A/B 各执行了一条无效UPDATE（目标记录不存在），RR隔离级别下会在间隙加锁。

随后双方再INSERT，需要加插入意向锁，与对方的间隙锁冲突 - 死锁。

解法：删除无效的UPDATE语句；或将隔离级别改为 RC（无间隙锁）。

**案例三：**范围查询的加锁顺序不一致。

单条SQL也可能死锁：范围查询是逐行加锁的。

事务A（主键范围）加锁顺序：id = 15 - 18 - 20。

事务B（二级索引范围）加锁顺序：id = 18 - 20 - 15。

顺序不同导致互相等待 - 死锁，本质与案例一相同。

如何预防死锁

**统一访问顺序：**多个事务以相同顺序访问表和索引，批量处理时提前排序。
**降低隔离级别：**间隙锁是死锁常见根源，确认业务可接受时将 RR 改为 RC。
**合理建索引：**缺索引会导致全表加锁，大幅提升死锁概率。
**一次性锁定资源：**类似MyISAM的思想，事务开始时锁定所有需要的资源。
**拆分大事务：**大事务持锁时间长，冲突概率高。
**设置锁等待超时：**innodb_lock_wait_timeout，防止大量挂起事务拖垮数据库。

总结

死锁 = 多事务加锁顺序不一致 + 循环等待。

读懂日志是手段，理清加锁顺序是关键，统一访问顺序、消除无效锁（如间隙锁）是治本之道。

悲观锁与乐观锁

为什么需要锁

并发场景下，多个用户同时修改同一数据，会导致脏读、幻读、不可重复读，最终数据结果不确定，锁是保证并发数据准确性的机制。

两种锁的核心思想

	悲观锁 Pessimistic Locking	乐观锁 Optimistic Locking
假设	别人一定会修改数据	别人不会修改数据
策略	先加锁，再操作	先操作，提交时再校验
代价	阻塞其他事务	冲突时回滚重试
适用	写多、冲突频繁	读多、冲突少

悲观锁的实现

在对记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）。
如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。具体响应方式由开发者根据实际需要决定。
如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。
其间如果有其他事务对该记录做加锁的操作，都要等待当前事务解锁或直接抛出异常。

# 必须关闭自动提交才能实现悲观锁
SET autocommit = 0;

# 开始事务
begin; 

# 查询出商品库存信息，在这里加了独占行锁
# id 存在索引的情况下加的是行锁，如果没有索引会锁全表
select quantity from items where id=1 for update;

# 修改商品库存
update items set quantity=2 where id = 1;

# 提交事务
commit;

事务 A 查询数据并加锁 - 立刻返回结果。
事务 B 尝试查同一行 - 阻塞等待。
事务 A 修改完成并提交 - 事务 B 才能继续。
若等待超时 - 报错 Lock wait timeout exceeded。

缺点： 额外开销大、并行性低、有死锁风险。

数据库中行锁、表锁、读锁（共享锁）、写锁（排他锁）均属悲观锁。

乐观锁的实现

# CAS
# 开始事务
begin; 

# 查询商品库存信息
select quantity from items where id =1 

# 使用乐观锁的 CAS 机制修改商品库存
# 当多个线程尝试使用CAS同时更新同一个变量时，只有其中一个线程能更新变量的值，而其它线程都失败，失败的线程并不会被挂起，而是被告知这次竞争中失败，并可以再次尝试
update items set quantity = 2 where id = 1 and quantity = 3;

# 提交事务
commit;

# ABA
# 开始事务
begin; 

# 查询商品库存信息
select version from items where id = 1

# 修改商品库存，带版本号更新避免出现 ABA 问题
update items set quantity = 2, version = 3 where id = 1 and version = 2;

# 提交事务
commit;

# 原子操作
# 开始事务
begin; 

# 查询商品库存信息，减小锁粒度，高并发优化
# 在一次原子操作中完成查+改操作，无需先 SELECT，吞吐量最高
UPDATE items SET quantity = quantity - 1 WHERE id = 1 AND quantity-1 > 0;

# 提交事务
commit;

用户 A、B 同时读取数据（version=0）。
用户 A 先更新成功，version 变为 1。
用户 B 再更新，条件 version=0 不匹配，影响行数为 0，更新失败提示重试。

CAS 与 ABA 问题

乐观锁的底层原理就是 CAS（Compare And Swap）：读取值 A - 准备更新为 B - 先检查当前值是否仍为 A - 是则更新，否则放弃。

CAS 由 CPU 指令保证原子性，因此乐观锁本质上是无锁编程。

**ABA 问题：**值从 A - B - A，CAS 误判为”未被修改”。

**解决：**用版本号，每次变更版本号递增，杜绝值回滚被误判。

如何选择

写操作多 / 冲突频繁：悲观锁（减少无效重试）。

读操作多 / 冲突罕见：乐观锁（减少锁开销，提升吞吐量）。

注意：乐观锁粒度若掌握不好，更新失败率会升高，反而拖累性能。

日志

执行一条 update 语句，期间发生了什么？

客户端先通过连接器建立连接，连接器自会判断用户身份。
MySQL 8.0 移除了查询缓存功能。
解析器会通过词法分析识别出关键字 update，表名等等，构建出语法树，接着还会做语法分析，判断输入的语句是否符合 MySQL 语法。
预处理器会判断表和字段是否存在。
优化器确定执行计划，因为 where 条件中的 id 是主键索引，所以决定要使用 id 这个索引。
执行器负责具体执行，找到这一行，然后更新。

三种日志定位

日志	所属层	核心作用	保障特性
undo log	InnoDB 引擎层	回滚 + MVCC	原子性（A）
redo log	InnoDB 引擎层	崩溃恢复	持久性（D）
binlog	Server 层	备份 + 主从复制	/

undo log（回滚日志）

**为什么需要 undo log（回滚日志）：**事务执行中途崩溃，需要能回到执行前的状态，保证原子性。

**记录什么：**操作的逆向信息。

INSERT - 记录主键值（回滚时删除）。
DELETE - 记录整行（回滚时插入）。
UPDATE - 记录旧值（回滚时还原）。

一条记录的每一次更新操作产生的 undo log 格式都有一个 roll_pointer 指针和一个 trx_id 事务id，通过 trx_id 可以知道该记录是被哪个事务修改的，通过 roll_pointer 指针可以将这些 undo log 串成一个链表，这个链表就被称为版本链。

两大作用：

事务回滚：ROLLBACK 时按逆操作恢复数据，保证原子性。
实现 MVCC：每条记录通过 trx_id + roll_pointer 串成版本链，配合 ReadView 实现快照读（“读提交”每次 SELECT 生成新 ReadView，“可重复读”只在事务开始时生成一次）。

读类型	典型语句	防幻读机制
快照读	SELECT …	MVCC（多版本并发控制）
当前读	SELECT … FOR UPDATE / DELETE	Next-Key Lock（间隙锁 + 记录锁）

Buffer Pool + redo log

为什么需要 Buffer Pool：磁盘 I/O 慢，先在内存缓存修改（脏页），再异步刷盘。

**问题：**内存掉电，脏页丢失。

解法：redo log + WAL（Write Ahead Logging）预写日志技术，先写日志，再在合适时机写磁盘。

Buffer Pool 中有 undo 页，对 undo 页的修改也都会记录到 redo log，redo log 会定时刷盘，提交事务时也会刷盘，数据页和 undo 页都是靠这个机制保证持久化的。

**redo log 是什么：**物理日志，记录”对某数据页某偏移量做了什么修改”。

为什么 redo log 比直接写磁盘快：redo log 是顺序追加写，数据写磁盘是随机写，顺序写效率高得多。

事务提交之前发生了崩溃，重启后会通过 undo log 回滚事务。

redo log 刷盘时机：

MySQL 正常关闭时。
当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时，会触发落盘。
InnoDB 的后台线程每隔 1 秒，将 redo log buffer 持久化到磁盘。
每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘（这个策略可由 innodb_flush_log_at_trx_commit 参数控制）。

刷盘时机（由 innodb_flush_log_at_trx_commit 控制）：

值	行为	安全性	性能
0	提交时留在 buffer，后台线程每秒刷	低（进程崩溃丢1秒数据）	高
1（默认）	提交时直接持久化到磁盘	高（零丢失）	低
2	提交时写到 OS Page Cache，每秒 fsync	中（OS崩溃丢1秒）	中

**redo log 文件管理：**循环写，固定大小，用 write_pos（当前写位置）和 checkpoint（可覆盖位置）维护环形空间，写满则阻塞等待脏页落盘后推进 checkpoint。

图中的 write pos 和 checkpoint 的移动都是顺时针方向。
write pos - checkpoint 之间的部分（图中的红色部分），用来记录新的更新操作。
check point - write pos 之间的部分（图中蓝色部分）为待落盘的脏数据页记录。
如果 write pos 追上了 checkpoint，就意味着 redo log 文件满了，这时 MySQL 不能再执行新的更新操作，也就是说 MySQL 会被阻塞（因此所以针对并发量大的系统，适当设置 redo log 的文件大小非常重要），此时会停下来将 Buffer Pool 中的脏页刷新到磁盘中，然后标记 redo log 哪些记录可以被擦除，接着对旧的 redo log 记录进行擦除，等擦除完旧记录腾出了空间，checkpoint 就会往后移动（图中顺时针），然后 MySQL 恢复正常运行，继续执行新的更新操作。

binlog（归档日志）

事务提交的时候，会将该事务执行过程中产生的所有 binlog 统一写入 binlog 文件，binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，比如 SELECT 和 SHOW 操作。

对比维度	binlog	redo log
所属层	Server 层（所有引擎通用）	InnoDB 引擎层
日志类型	逻辑日志（记录SQL或行变更）	物理日志（记录页修改）
写入方式	追加写（全量保留）	循环写（只保留未落盘部分）
用途	备份恢复、主从复制	崩溃恢复

备份恢复：

redo log 文件是循环写，是会边写边擦除日志的，只记录未被刷入磁盘的数据的物理日志，已经刷入磁盘的数据都会从 redo log 文件里擦除。

binlog 文件保存的是全量的日志，也就是保存了所有数据变更的情况，理论上只要记录在 binlog 上的数据，都可以恢复，所以如果不小心整个数据库的数据被删除了，得用 binlog 文件恢复数据。

主从复制：

**写入 Binlog：**主库写 binlog 日志，提交事务，并更新本地存储数据。
**同步 Binlog：**把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。
**回放 Binlog：**回放 binlog，并更新存储引擎中的数据。

事务执行过程中，先把日志写到 binlog cache（Server 层的 cache），事务提交的时候，再把 binlog cache 写到 binlog 文件中。

**三种格式：**STATEMENT（记SQL，有动态函数隐患）、ROW（记行变更，文件较大）、MIXED（自动选择）。

MySQL提供一个 sync_binlog 参数来控制数据库的 binlog 刷到磁盘上的频率：

sync_binlog = 0，表示每次提交事务都只 write，不 fsync，后续交由操作系统决定何时将数据持久化到磁盘。
sync_binlog = 1，表示每次提交事务都会 write，然后马上执行 fsync。
sync_binlog =N(N>1) ，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

两阶段提交（2PC）

问题根源：redo log 和 binlog 是两个独立的持久化操作，任一半途崩溃会导致主从数据不一致。

MySQL 为了避免出现两份日志之间的逻辑不一致的问题，使用了两阶段提交来解决，两阶段提交其实是分布式事务一致性协议，它可以保证多个逻辑操作要不全部成功，要不全部失败，不会出现半成功的状态。

**解法：**以 XID 为纽带，将 redo log 写入拆成两步。

**prepare 阶段：**写 redo log（状态=prepare），持久化到磁盘。
**commit 阶段：**写 binlog，持久化到磁盘，将 redo log 状态改为 commit。

判定标准：以 binlog 写成功作为事务提交成功的唯一标识。

**崩溃恢复规则：**重启后扫描处于 prepare 状态的 redo log，去 binlog 中查找对应 XID，找到则提交事务（binlog 已写，从库已同步），找不到则回滚事务。

binlog 组提交（Group Commit）优化

两阶段提交的性能瓶颈：每个事务需要两次 fsync（redo log + binlog），高并发下 I/O 压力大。

**组提交：**将 commit 阶段拆为三个队列，多个事务合并为一次刷盘。

**flush 阶段：**多事务 redo log 合并一次 fsync（write+fsync）。
**sync 阶段：**多事务 binlog 合并一次 fsync（等待或凑数量触发）。
**commit 阶段：**各事务依次完成引擎 commit。

可通过 binlog_group_commit_sync_delay（等待时长）和 binlog_group_commit_sync_no_delay_count（事务数阈值）调整组提交效果。

MySQL 磁盘 I/O 很高，有什么优化的方法

MySQL 日志：undo log、redo log、binlog 有什么用？

一条 UPDATE 的完整流程

执行器查找记录（Buffer Pool 或磁盘读入）。
写 undo log（记旧值，写入 Undo 页，同时记对应 redo log）。
更新 Buffer Pool 中的数据页（标记脏页）。
写 redo log buffer（WAL）。
执行完成，写 binlog cache。
事务提交：两阶段提交。
1. **prepare：**redo log 落盘。
2. **commit：**binlog 落盘 - redo log 置为 commit。

备份

性能调优

benchmark

explain执行计划

当在一条SQL前加上explain命令，执行这条SQL后会列出所有的执行方案，重点关注** type、key、extra**。

**id：**这是执行计划的ID值，这个值越大，表示执行的优先级越高。

**select_type：**当前查询语句的类型，有如下几个值：

**simple：**简单查询。

**primary：**复杂查询的外层查询。

**subquery：**包含在查询语句中的子查询。

**derived：**包含在FROM中的子查询。

**table：**表示当前这个执行计划是基于那张表执行的。

type：当前执行计划查询的类型，有几种情况：

**all：**表示走了全表查询，未命中索引或索引失效。

**system：**表示要查询的表中仅有一条数据。

**const：**表示当前SQL语句的查询条件中，可以命中索引查询。

**range：**表示当前查询操作是查某个区间。

**eq_ref：**表示目前在做多表关联查询。

**ref：**表示目前使用了普通索引查询。

**index：**表示目前SQL使用了辅助索引查询。

**possible_keys：**执行SQL时，优化器可能会选择的索引（最后执行不一定用）。

key：查询语句执行时，用到的索引名字。

**key_len：**这里表示索引字段使用的字节数。

**ref：**这里显示使用了那种查询的类型。

**rows：**当前查询语句可能会扫描多少行数据才能检索出结果。

Extra：这里是记录着额外的一些索引使用信息，有几种状态：

**using index：**表示目前使用了覆盖索引查询（稍后讲）。

**using where：**表示使用了where子句查询，通常表示没使用索引。

**using index condition：**表示查询条件使用到了联合索引的前面几个字段。

**using temporary：**表示使用了临时表处理查询结果。

**using filesort：**表示以索引字段之外的方式进行排序，效率较低。

**select tables optimized away：**表示在索引字段上使用了聚合函数。

SQL 语句优化

核心问题

SELECT * FROM t_player ORDER BY score DESC OFFSET k-1 LIMIT 1;

时间复杂度：O(N)

直觉上走索引应该是 O(logN)，但找第K大不是按值查找，不知道走树的哪条分支。

实际走的是 B+ 树底层的双向链表顺序遍历，所以是 O(N)。

为什么 OFFSET 大了之后慢得离谱

根本原因不只是遍历，而是无效回表：对 OFFSET 跳过的每一行都回表查聚簇索引，产生了大量随机 IO，跳过 10000 行 = 10000 次随机 IO，这才是真正的性能杀手。

三种优化方案

**业务上改为”下一页”逻辑：**将 OFFSET 改为基于上一页最后一条记录的条件查询，利用树索引直接定位，彻底避免回表，适合信息流、分页列表（抖音、微博的翻页逻辑）。

SELECT * FROM t_player WHERE score < prev_score ORDER BY score DESC LIMIT 20;

索引覆盖 + SQL 拆分：先只查主键 ID（不回表），再用 ID 查完整数据（只有 1 次 IO），500万数据量下，从10秒级降至10毫秒级。

SELECT * 
FROM t_player 
WHERE id IN (
    SELECT id 
    FROM t_player 
    ORDER BY score DESC 
    OFFSET 10000 
    LIMIT 1
);

**业务预判边界值：**适用于能预先知道边界值的特定场景，非通用方案。

为什么 MySQL 不主动优化这个问题

根本原因是 OFFSET 没有下推到存储引擎层，执行流程是：存储引擎查二级索引 - 回表取完整行 - 上层（SQL层）判断是否跳过 - 重复，存储引擎根本不知道 OFFSET 的存在，自然每行都回表。

MySQL 官方不修的原因：

限制场景太多，兼容各引擎成本高。
分层设计理念：OFFSET 属于 SQL 层的事，不该由存储引擎处理。

阿里云 / 腾讯云怎么做的

两家云厂商在自研 MySQL 版本中实现了 OFFSET 下推，在引擎层直接跳过无用行，不再回表。

**实测效果：**同场景从 25秒降至 329毫秒，性能提升 75 倍。

什么是索引下推（Index Condition Pushdown，ICP）

索引下推就是把部分 WHERE 条件的判断下推到存储引擎层，在回表之前就过滤掉不符合的行，从而减少无效回表次数，降低随机 IO 开销。

条件	说明
使用二级索引	主键索引不需要回表，ICP 无意义
WHERE 条件字段在索引上	只有索引包含的字段才能提前判断

连接池

MySQL性能优化（上）

https://juejin.cn/post/7163894728201601060

性能优化核心思维

**优化前提：**不要为了调优而调优，瓶颈出现时才介入，预防优于救火。

**核心约束：**CPU / 内存 / 磁盘任一达到瓶颈，系统整体崩溃，三者使用率最好控制在 80-85% 以下。

**优先级排序（收益从高到低）：**架构优化 > 结构优化 > SQL 优化 > 参数优化 > 连接层优化。

MySQL 五维调优方案

连接层优化：
1. 最佳最大连接数 = CPU 核心数 × 2 + 1（SSD磁盘 + 1）。
2. 连接数 ≠ 越大越好，线程数远超 CPU 核心数时，上下文切换开销远大于执行本身开销，性能反而下降。
3. 客户端连接池（Druid/HikariCP）控制连接数，带大等待队列兜底。
4. 大事务 / 慢 SQL 单独开一个连接池，避免阻塞普通请求。
5. **偶发高峰型业务：**常驻连接数设低，缩短空闲连接存活时间。
6. **分库分表场景：**按每节点硬件单独规划。
表/字段/索引结构优化：
1. 表结构三原则：
  1. 字段数量控制在 30 以内，遵循三范式，冷热字段拆分（减少内存浪费）。
  2. 高频连表字段可适当冗余（空间换时间）。
  3. 统计类业务建中间表，每日定时刷新（避免实时联查开销）。
  4. 读多写少的字典/权限表，选 MyISAM，其他用 InnoDB。
2. 字段类型三原则：
  1. 用最小够用的类型（tinyint / smallint 代替 int，char 代替短 varchar）。
  2. 索引字段必须 NOT NULL（NULL 值影响索引性能）。
  3. 用简单类型代替复杂类型（IP 用 int，状态用 enum）。
3. 索引结构四要点：
  1. 多单列索引合并为联合索引（节省空间 + 支持覆盖索引）。
  2. 长字段建前缀索引（索引键越小，B+Tree 越矮，查询越快）。
  3. 模糊查询字段建全文索引（LIKE 会使普通索引失效）。
  4. 无范围查询的字段可用 Hash 索引（O(1) 复杂度）。
参数优化：

参数	作用	建议值
innodb_buffer_pool_size	InnoDB 缓冲区	服务器内存 × 70% - 75%
innodb_buffer_pool_instances	缓冲区实例数（减少并发冲突）	每实例 ≥ 1GB，内存大时约 2GB/ 实例
sort_buffer_size / read_buffer_size / join_buffer_size	线程私有缓冲区（排序/读取/联查）	机器内存（GB）对应 MB 级别，上限 64MB
tmp_table_size / max_heap_table_size	临时表内存上限	调大到磁盘临时表占比 < 20%
wait_timeout / interactive_timeout	空闲连接超时	默认 8h - 调整为 30min-1h

**激进优化选项：**降低刷盘频率（牺牲安全性换性能），事务隔离级别从 RR 改为 RC（减少并发冲突）。

架构优化（收益最大）：
1. 读多写少：引入 Redis 缓存 + 主从读写分离。
2. 写多读少：引入 MQ 削峰 + 双主双写热备。
3. 数据量巨大：分库分表（垂直分库按业务划分，水平分表按数据量切片）。
4. 双主局限：每节点存完整数据，磁盘上限 + 木桶效应，超过 2 主时直接上分库分表。
SQL 优化：参考MySQL性能优化（下）。

一句话总结

架构决定上限，结构决定基线，参数微调锦上添花，SQL 优化日常必备。

没有瓶颈就不要动，出现瓶颈对症下药，永远不要等系统崩了才开始调优。

MySQL性能优化（下）

https://juejin.cn/post/7164652941159170078

编写 SQL 的基本功（拆解法）

核心思想： 面对复杂 SQL，不要一步到位，先拆解需求，再逐步实现并组合。

化繁为简：将复杂需求拆成若干小步，逐步推导。
先用定值代替子查询结果，验证通后再嵌套。
**把 SQL 想成”链式处理器”：**每道工序前尽量缩小数据量。

SQL 编写注意点（避坑清单）

问题	原因	对策
SELECT *	解析成本高、网络/内存开销大、回表无法索引覆盖	明确写出所需字段
关联表过多	数据量线性增长、索引不可控	控制在 5 张以内（阿里规范 ≤3 张）
大表驱动小表	Nest Loop Join 循环次数多	小表在前，大表在后
LIKE %xxx / %xxx%	索引失效，全表扫描	改用全文索引或 ES
IS NULL 判断	索引失效	字段设 NOT NULL，用空字符串代替
= 前对字段运算/函数	优化器不走索引	运算移到 = 右边
!=、NOT IN、OR	可能导致索引失效	用 UNION ALL 等替代
隐式/缩写写法	MySQL 底层需额外转换	写完整语法（如 AS、INNER JOIN）
循环单条 INSERT/UPDATE	频繁网络请求	改为批量操作（一条 SQL 多 values）
深分页 LIMIT 100000,10	扫描 10 万行再丢弃	有序连续字段改用 WHERE id>=N LIMIT 10; 业务层限制最大页数
大事务	长时间持锁，高并发阻塞	拆分为小事务或异步处理
一次返回海量数据	网络/内存压力大	分页/增量查询

其他技巧：

确定只有一条结果时加 LIMIT 1，找到即停。
联合索引查询必须遵循最左前缀原则。
复杂场景可用 FORCE INDEX 强制走指定索引。
临时表只对高频查询建立，避免频繁创建销毁。

性能标准（留给 SQL 的时间有多少）

**用户感知响应链路：**网络 - 负载均衡 - 服务处理 - 数据库操作 - 返回。

**C 端业务：**总响应 ≤3s，留给 SQL 的时间约 500ms。

**大流量门户：**甚至要求 SQL 执行在 10~50ms 以内。

EXPLAIN 分析工具（诊断核心）

执行 EXPLAIN SQL语句，重点关注以下字段：

**type（访问类型，最重要）性能从好到差：**NULL > system > const > eq_ref > ref > range > index > ALL，至少达到 range，不允许出现 index 或 ALL（全扫描）。
**key（实际使用的索引）为空：**未走索引，需建索引或改写 SQL。
rows（预估扫描行数）越小越好，过大说明扫描了大量无效数据。
filtered（过滤后数据占比）越大越好，接近100%表示几乎没有多余扫描。
**Extra（执行细节）性能从好到差：**Using index（索引覆盖）> NULL > Using where > Using filesort > Using temporary。

索引优化本质（两条路）

**改写SQL：**消除索引失效写法，让查询精准命中索引。

**建对索引：**在高频查询条件字段上建立合适的索引（单列/联合/覆盖索引）。

走了索引 ≠ 一定快，还需结合 type、rows、filtered 综合判断，确保没有扫描多余行。

总结

SQL优化 = 减少扫描数据量 + 提升索引命中率 + 避免索引失效写法，三者同时做好，基本可以写出一手高质量SQL。