SQL优化添加索引/联合索引避免使用select * ,使用覆盖索引避免回表联表查询不能过多，使用小表驱动大表进行查

添加索引/联合索引
避免使用select * ,使用覆盖索引避免回表
联表查询不能过多，使用小表驱动大表进行查询
避免索引失效
第三次优化：减少数据量：-
数据接近千万级，需要分表，比如按照用户id取模分表。
第五次优化：强制索引
关联字段冗余、直接放到一张表就是单表查询了。深分页问题

索引相关：添加索引，联合索引，避免索引失效，使用覆盖索引避免回表，强制使用索引避免使用select *
联表查询：小表驱动大表，避免太多表进行关联字段冗余
深度分页：考虑使用id > 某个值
分表：数据量太大，可以考虑分表，比如对用户id进行取模

间隙锁

在 MySQL 中，间隙锁（Gap Lock）的范围是左开右开，即(a, b) 。这意味着间隙锁不会包括边界值 a 和 b，它只锁住 a 和 b 之间的区间。具体来说：

1. 间隙锁的定义

间隙锁的主要目的是防止幻读（Phantom Read），保证事务的隔离性（通常是 REPEATABLE READ 隔离级别下）。
锁定的范围是一个“间隙”，不包括间隙两端的记录值。

2. 间隙锁的特性

范围： 左开右开，即 (a, b)。
作用： 防止其他事务在该间隙内插入新的数据。
适用场景： 仅在**行锁（Record Lock）**不足以解决问题时才使用。例如，范围查询时会同时使用行锁和间隙锁。

3. 示例分析

假设表结构如下：

CREATE TABLE test (
    id INT PRIMARY KEY,
    value INT
);
INSERT INTO test (id, value) VALUES (10, 20), (20, 30), (30, 40);

场景 1: 范围查询加锁

事务 A 执行以下语句：

SELECT * FROM test WHERE id > 10 FOR UPDATE;

此时加锁情况为：

锁住 id = 20 和 id = 30 的行（行锁）。
对间隙 (10, 20) 和 (20, 30) 加间隙锁，防止其他事务在这两个间隙内插入新记录。

场景 2: 插入操作受限

在事务 A 未提交的情况下，事务 B 尝试以下插入：

INSERT INTO test (id, value) VALUES (15, 25);

此操作会被阻塞，因为间隙 (10, 20) 被事务 A 锁住。

4. 总结

间隙锁范围是左开右开 (a, b) 。
间隙锁不锁定边界值 a 和 b，但会锁住整个间隙，防止其他事务插入数据。
通常用于范围查询，配合行锁解决幻读问题。

如果需要进一步深入了解间隙锁的应用场景或优化方法，可以提供更多上下文，我可以帮您分析！

我更新什么样的字段会加间隙锁

在 MySQL 中，**间隙锁（Gap Lock）**主要在特定的情况下被触发，用于防止幻读。更新字段时是否加间隙锁，取决于以下几个因素：

1. 触发间隙锁的场景

间隙锁通常在以下情况下出现：

(1) 涉及范围查询的更新

如果 UPDATE 或 DELETE 语句中包含范围条件（如 <, >, BETWEEN, LIKE 等），MySQL 会加间隙锁来防止幻读。例如：

UPDATE test SET value = 50 WHERE id > 10;

间隙锁范围： 对 id > 10 的范围内的间隙加锁。
目的： 防止其他事务插入满足 id > 10 的新行。

(2) 更新不存在的记录

如果更新的条件没有匹配到任何记录，MySQL 会对目标范围加间隙锁。例如：

UPDATE test SET value = 50 WHERE id = 15;

如果 id = 15 不存在，会对间隙 (10, 20) 加锁，防止插入新记录。

(3) 主键/唯一索引以外的字段更新

如果 UPDATE 查询条件涉及非主键字段，或者不依赖于唯一索引，MySQL 可能会使用间隙锁。例如：

UPDATE test SET value = 50 WHERE value > 25;

如果 value 字段没有索引，MySQL 会扫描全表，并可能对扫描过程中涉及的间隙加锁。

2. 不会触发间隙锁的情况

(1) 精确匹配主键或唯一索引

如果更新语句的条件是精确匹配主键或唯一索引，不会加间隙锁。例如：

UPDATE test SET value = 50 WHERE id = 10;

锁类型： 只对 id = 10 加行锁，不涉及间隙锁。

(2) `READ COMMITTED` 隔离级别

在 READ COMMITTED 隔离级别下，MySQL 默认不会使用间隙锁，只使用行锁。

3. 举例分析

假设表结构如下：

CREATE TABLE test (
    id INT PRIMARY KEY,
    value INT,
    INDEX(value)
);
INSERT INTO test (id, value) VALUES (10, 20), (20, 30), (30, 40);

场景 1: 范围更新

UPDATE test SET value = 50 WHERE id > 15;

加锁范围：
- 行锁：id = 20 和 id = 30。
- 间隙锁：(15, 20) 和 (30, ∞)。

场景 2: 更新不存在的记录

UPDATE test SET value = 50 WHERE id = 25;

加锁范围：
- 行锁：无。
- 间隙锁：(20, 30)。

场景 3: 非唯一索引字段更新

UPDATE test SET value = 50 WHERE value > 25;

加锁范围：
- 行锁：value = 30 和 value = 40 对应的行。
- 间隙锁：(25, 30) 和 (40, ∞)。

4. 如何避免不必要的间隙锁

(1) 使用精确查询条件

通过主键或唯一索引进行精确匹配查询，避免不必要的范围扫描。

UPDATE test SET value = 50 WHERE id = 10;

(2) 降低隔离级别

将事务隔离级别从 REPEATABLE READ 降为 READ COMMITTED，MySQL 不会加间隙锁，但仍然能避免大多数锁冲突。

(3) 优化索引

确保查询条件使用索引，避免全表扫描引发的间隙锁。

总结

更新操作会加间隙锁的情况通常出现在 范围查询 或 非精确匹配 时，尤其是在 REPEATABLE READ 隔离级别下。
间隙锁的范围为 左开右开，锁定的是满足条件的记录之间的“间隙”。
如果想减少间隙锁的影响，可以通过索引优化、调整隔离级别或修改查询条件来实现。

如果需要具体的优化建议，可以提供更详细的查询场景！

会阻塞读吗

MySQL 的间隙锁（Gap Lock）不会阻塞普通的读操作，但会在某些特定情况下影响读取，具体情况如下：

1. 不会阻塞普通的 `SELECT` 查询

在默认的 REPEATABLE READ 或 READ COMMITTED 隔离级别下，普通的 SELECT 查询（无锁查询）不会被间隙锁阻塞。
例如：
```
SELECT * FROM test WHERE id > 10;
```
即使间隙 (10, 20) 被加了间隙锁，普通的读取操作依然可以正常进行。

2. 会阻塞带锁的读取操作

当读取操作显式加锁（例如使用 FOR UPDATE 或 LOCK IN SHARE MODE），可能会因为间隙锁而被阻塞。具体情况如下：

(1) `SELECT ... FOR UPDATE`

如果另一个事务对间隙加了锁（如更新或删除操作触发间隙锁），当前事务尝试使用 SELECT ... FOR UPDATE 时会被阻塞。例如：

事务 A：

UPDATE test SET value = 50 WHERE id > 10;

事务 A 加了间隙锁 (10, 20)。

事务 B：

SELECT * FROM test WHERE id = 15 FOR UPDATE;

事务 B 会被阻塞，因为 id = 15 在间隙 (10, 20) 中。

(2) `SELECT ... LOCK IN SHARE MODE`

类似于 FOR UPDATE，共享锁也会因为间隙锁而被阻塞。例如：

事务 A：

DELETE FROM test WHERE id > 10;

事务 A 加了间隙锁 (10, 20)。

事务 B：

SELECT * FROM test WHERE id = 15 LOCK IN SHARE MODE;

事务 B 会被阻塞。

3. 特殊情况：幻读相关问题

间隙锁的设计目的是为了解决**幻读（Phantom Read）**问题，主要在 REPEATABLE READ 隔离级别下生效。由于间隙锁阻止了新记录的插入，所以：

幻读被避免： 如果一个事务已经查询了某个范围，另一个事务不能在该范围内插入新数据，保证了数据一致性。
正常读取不受影响： 间隙锁只会对写操作和带锁的读取操作产生影响，普通读取仍然可以执行。

4. 总结

不会阻塞： 普通的 SELECT 查询不受间隙锁影响。
会阻塞： 带锁的读取（FOR UPDATE 或 LOCK IN SHARE MODE）会因间隙锁而被阻塞。
解决办法：
- 如果间隙锁导致阻塞，可以：
  - 降低隔离级别为 READ COMMITTED。
  - 优化查询条件，避免触发间隙锁。
  - 在合适的场景使用 NOWAIT 或 SKIP LOCKED。

如果有具体的阻塞场景，可以提供更多细节，我可以进一步分析！

UPDATE test SET value = 50 WHERE id > 10; SELECT * FROM test WHERE id = 5 FOR UPDATE;会加锁吗

让我们分析一下这两条语句的加锁行为，以及是否会产生阻塞。

1. 事务 A 执行的语句

UPDATE test SET value = 50 WHERE id > 10;

加锁行为：
1. 行锁 (Record Lock)：
  - 锁住 id > 10 的所有匹配行，如 id = 20, id = 30, 等。
2. 间隙锁 (Gap Lock)：
  - 锁住所有满足 id > 10 的范围之间的间隙，比如 (10, 20), (20, 30) 等。
  - 防止在这些间隙中插入新行。
3. 插入意向锁 (Insert Intent Lock)：
  - 防止在 (10, ∞) 范围内插入新记录。

2. 事务 B 执行的语句

SELECT * FROM test WHERE id = 5 FOR UPDATE;

加锁行为：
- 由于使用了 FOR UPDATE，MySQL 会试图对查询结果加行锁。
- 如果 id = 5 存在，事务 B 会对这条记录加行锁。

3. 是否加锁、阻塞？

间隙锁和行锁是否冲突：
- 事务 A 加锁的范围是 id > 10 的记录和间隙，而事务 B 查询的 id = 5 不在事务 A 加锁的范围内。
- 因此，事务 B 不会被阻塞，可以正常执行。
结果：
- 事务 A 加的锁： (10, ∞) 范围内的行和间隙。
- 事务 B 查询的范围： id = 5，与事务 A 无重叠。
- 结论： 事务 B 的 SELECT ... FOR UPDATE 可以直接获取行锁，不会阻塞。

总结

事务 A 的 UPDATE 会对 id > 10 的范围加行锁和间隙锁，但不会影响 id = 5。
事务 B 的 SELECT ... FOR UPDATE 查询 id = 5 不在事务 A 的锁范围内，因此可以正常执行。
结论：不会产生阻塞。

如果有更复杂的查询条件或锁冲突场景，可以进一步分析！

复杂sql整理：

以下是几个复杂的 SQL 示例及其答案，涵盖多表查询、递归查询、窗口函数等场景：

例子 1：多表关联和条件过滤

问题：

有三张表：

Orders（订单表）：OrderID, CustomerID, OrderDate
Customers（客户表）：CustomerID, CustomerName, Country
OrderDetails（订单详情表）：OrderID, ProductID, Quantity, Price

查询每个国家的客户总消费金额（TotalAmount = Quantity * Price），并按金额降序排序。

SQL：

SELECT 
    c.Country,
    SUM(od.Quantity * od.Price) AS TotalAmount
FROM 
    Customers c
JOIN 
    Orders o ON c.CustomerID = o.CustomerID
JOIN 
    OrderDetails od ON o.OrderID = od.OrderID
GROUP BY 
    c.Country
ORDER BY 
    TotalAmount DESC;

答案：

假设数据如下：

Customers：

CustomerID CustomerName Country
1 Alice USA
2 Bob Canada
Orders：

OrderID CustomerID OrderDate
101 1 2024-01-01
102 2 2024-01-02
OrderDetails：

OrderID ProductID Quantity Price
101 A 2 50
102 B 3 30

CustomerID	CustomerName	Country
1	Alice	USA
2	Bob	Canada

OrderID	CustomerID	OrderDate
101	1	2024-01-01
102	2	2024-01-02

OrderID	ProductID	Quantity	Price
101	A	2	50
102	B	3	30

结果：

Country	TotalAmount
USA	100
Canada	90

例子 2：递归查询（层级结构）

问题：

有一个员工表 Employees，字段如下：

EmployeeID: 员工编号
Name: 员工姓名
ManagerID: 上级编号（为 NULL 表示顶层经理）

查询某个经理（例如 ID = 1）管理的所有下属（包括间接下属）。

SQL：

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT 
        EmployeeID, Name, ManagerID
    FROM 
        Employees
    WHERE 
        ManagerID = 1 -- 顶层经理 ID

    UNION ALL

    SELECT 
        e.EmployeeID, e.Name, e.ManagerID
    FROM 
        Employees e
    INNER JOIN 
        EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

答案：

假设数据如下：

EmployeeID	Name	ManagerID
1	Alice	NULL
2	Bob	1
3	Charlie	2
4	David	2

结果：

EmployeeID	Name	ManagerID
2	Bob	1
3	Charlie	2
4	David	2

例子 3：窗口函数

问题：

有一张 Sales 表：

SalesID: 销售编号
SalesPerson: 销售员
Amount: 销售金额

计算每个销售员的累计销售额（按销售编号排序）。

SQL：

SELECT 
    SalesPerson,
    SalesID,
    Amount,
    SUM(Amount) OVER (PARTITION BY SalesPerson ORDER BY SalesID) AS CumulativeAmount
FROM 
    Sales;

答案：

假设数据如下：

SalesID	SalesPerson	Amount
1	Alice	100
2	Bob	200
3	Alice	150
4	Bob	300

结果：

SalesPerson	SalesID	Amount	CumulativeAmount
Alice	1	100	100
Alice	3	150	250
Bob	2	200	200
Bob	4	300	500

例子 4：子查询和排名

问题：

有一个学生表 Students：

StudentID: 学生编号
Name: 姓名
Score: 成绩

查询每个学生的排名（按成绩从高到低排序，成绩相同时并列）。

SQL：

SELECT 
    Name,
    Score,
    RANK() OVER (ORDER BY Score DESC) AS Rank
FROM 
    Students;

答案：

假设数据如下：

StudentID	Name	Score
1	Alice	90
2	Bob	95
3	Charlie	90
4	David	85

结果：

Name	Score	Rank
Bob	95	1
Alice	90	2
Charlie	90	2
David	85	4

例子 5：复杂条件查询

问题：

在 Products 表中：

ProductID: 产品编号
Category: 分类
Price: 价格

查询价格高于分类平均价格的产品。

SQL：

SELECT 
    ProductID,
    Category,
    Price
FROM 
    Products p1
WHERE 
    Price > (
        SELECT 
            AVG(Price)
        FROM 
            Products p2
        WHERE 
            p1.Category = p2.Category
    );

答案：

假设数据如下：

ProductID	Category	Price
1	A	100
2	A	200
3	B	150
4	B	100

结果：

ProductID	Category	Price
2	A	200
3	B	150

这些例子展示了 SQL 的各种复杂用法，包括多表关联、递归查询、窗口函数、子查询和条件过滤等常见场景。

以下是使用 `GROUP BY` 和 `HAVING` 的例子，包括典型的过滤操作和业务场景。

例子 1：统计并过滤总数

问题：

有一张 Orders 表，包含以下字段：

CustomerID: 客户编号
OrderID: 订单编号
OrderAmount: 订单金额

查询每位客户的总订单金额，且只保留总金额大于 500 的客户。

SQL：

SELECT 
    CustomerID, 
    SUM(OrderAmount) AS TotalAmount
FROM 
    Orders
GROUP BY 
    CustomerID
HAVING 
    SUM(OrderAmount) > 500;

数据示例：

CustomerID	OrderID	OrderAmount
1	101	200
1	102	400
2	103	100
3	104	300
3	105	250

输出结果：

CustomerID	TotalAmount
1	600
3	550

例子 2：多条件过滤

问题：

在一张 Sales 表中，包含以下字段：

SalesPersonID: 销售员编号
Region: 销售地区
SalesAmount: 销售金额

查询每个地区的总销售金额，保留总金额大于 1000 且总金额小于 5000 的地区。

SQL：

SELECT 
    Region, 
    SUM(SalesAmount) AS TotalSales
FROM 
    Sales
GROUP BY 
    Region
HAVING 
    SUM(SalesAmount) > 1000 AND SUM(SalesAmount) < 5000;

数据示例：

SalesPersonID	Region	SalesAmount
1	North	1500
2	North	2000
3	South	300
4	East	4000
5	East	1500

输出结果：

Region	TotalSales
North	3500

例子 3：使用聚合函数和过滤

问题：

有一个 Products 表，包含以下字段：

Category: 产品类别
ProductName: 产品名称
Price: 产品价格

查询每个类别中，平均价格大于 50 的类别，并统计该类别的产品总数。

SQL：

SELECT 
    Category, 
    COUNT(ProductName) AS ProductCount, 
    AVG(Price) AS AvgPrice
FROM 
    Products
GROUP BY 
    Category
HAVING 
    AVG(Price) > 50;

数据示例：

Category	ProductName	Price
A	Product1	60
A	Product2	40
B	Product3	70
B	Product4	80
C	Product5	30

输出结果：

Category	ProductCount	AvgPrice
B	2	75.0

例子 4：使用 `GROUP BY` 和复杂过滤条件

问题：

有一个 Transactions 表，包含以下字段：

AccountID: 账户编号
TransactionID: 交易编号
TransactionAmount: 交易金额

查询每个账户的总交易次数和交易总金额，保留交易次数大于 3 且交易总金额大于 1000 的账户。

SQL：

SELECT 
    AccountID, 
    COUNT(TransactionID) AS TransactionCount, 
    SUM(TransactionAmount) AS TotalAmount
FROM 
    Transactions
GROUP BY 
    AccountID
HAVING 
    COUNT(TransactionID) > 3 AND SUM(TransactionAmount) > 1000;

数据示例：

AccountID	TransactionID	TransactionAmount
1	101	300
1	102	400
1	103	500
1	104	200
2	105	100
2	106	150
3	107	1200

输出结果：

AccountID	TransactionCount	TotalAmount
1	4	1400

总结

在这些例子中：

GROUP BY 用于分组，通常与聚合函数（如 SUM、AVG、COUNT）结合使用。
HAVING 用于对分组后的结果进行过滤（区别于 WHERE，它是在分组前过滤原始数据）。
常用场景 包括按类别、用户或其他分组维度计算总量、平均值、次数等，并筛选满足特定条件的结果。

使用HAVING 需要注意的是

在 SQL 中，HAVING 子句中的条件必须使用聚合函数或分组后的列的别名，而不能直接使用 SELECT 子句中的别名。也就是说，你不能直接在 HAVING 子句中使用 TransactionCount 和 TotalAmount 这样的别名。

因此，你的 SQL 需要改写为如下形式：

sql
复制代码
SELECT 
    AccountID, 
    COUNT(TransactionID) AS TransactionCount, 
    SUM(TransactionAmount) AS TotalAmount
FROM 
    Transactions
GROUP BY 
    AccountID
HAVING 
    COUNT(TransactionID) > 3 AND SUM(TransactionAmount) > 1000;

详细解释

HAVING 的限制：
- HAVING 子句用于过滤分组后的结果，必须基于聚合函数（如 COUNT、SUM、AVG 等）或分组列。
- SQL 不允许直接在 HAVING 子句中使用 SELECT 中定义的别名（如 TransactionCount 和 TotalAmount）。
工作原理：
- SQL 的执行顺序是先执行 GROUP BY 和聚合计算，再应用 HAVING 过滤条件。
- SELECT 子句中的别名在 HAVING 之前并未生效，因此无法直接引用。
正确使用聚合函数：
- 在 HAVING 子句中，必须写出完整的聚合函数表达式，如 COUNT(TransactionID) 和 SUM(TransactionAmount)。

可以这样写：
SELECT AccountID, COUNT(TransactionID) AS TransactionCount, SUM(TransactionAmount) AS TotalAmount FROM Transactions GROUP BY AccountID HAVING AccountID > 3;

ORDER BY 可以使用select中的字段

SUM(Quantity * Price) 这种语法是完全有效的。在 SQL 中，你可以在 SUM 函数中直接使用乘法运算符 * 来计算每个记录的乘积，然后对这些乘积进行求和。

SQL优化，就是指将一条SQL写的更加简洁，让SQL的执行速度更快，易读性与维护性更好。当然 优化一定要建立在不违背业务需求的情况下进行

第五次优化：强制索引

当 MySQL 中的 IN 子句用于查询千万级数据时，如果未正确设计和使用索引，可能导致索引失效，从而影响查询性能。

通常情况下，MySQL 的优化器会根据查询条件选择最优的执行计划，包括选择合适的索引。然而，对于大数据量的 IN 子句查询，MySQL 可能无法有效使用索引，从而导致全表扫描或索引失效。

查询sql如下，由于in的数据量不是很稀疏，实际查询强制索引和普通索引效果一致

sql
 代码解读
复制代码
-- 第五个版本，强制索引 
SELECT a.*,sum(b.total_amount) as total from users a left join orders b force index (idx_orders_user_id_total_amount)  on a.user_id = b.user_id
where b.user_id in (1033,1034,1035,1036,1037,1038)
group by a.user_id;
-- 第五个版本，不走强制索引 
SELECT a.*,sum(b.total_amount) as total from users a left join orders b  on a.user_id = b.user_id
where b.user_id in (1033,1034,1035,1036,1037,1038)
group by a.user_id;

查询时间都是零点几秒。

笔者在实际业务中是遇到过这种场景的，业务sql更加复杂。这里由于临时创建的订单用户表没复现。

当你发现explain都是命中索引的，但是查询依然很慢。这个强制索引可以试试。

作者：r0ad
链接：juejin.cn/post/736098…
来源：稀土掘金
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

场景：大表删除，更新

线上有一张数据量级为几千万的表，需要新增一个字段，该如何处理呢

在 MySQL 中对数据量级为几千万的大表新增字段时，直接执行 ALTER TABLE 可能会导致锁表、业务中断等问题，因此需要采取谨慎的措施。以下是处理方案：

1. 使用在线DDL工具

推荐使用成熟的在线DDL工具，如 pt-online-schema-change 或 gh-ost，它们可以在不锁表的情况下进行表结构变更。

pt-online-schema-change 示例

pt-online-schema-change --alter "ADD COLUMN new_column INT DEFAULT 0" --user=root --password=yourpassword --host=localhost --execute D=my_database,t=my_table

原理：工具会创建一张新表，将原表数据逐行复制到新表（含新增字段），最后替换原表。
优点：
- 对业务影响极小。
- 支持实时更新。

gh-ost 示例

gh-ost --user="root" --password="yourpassword" --host="localhost" \
--database="my_database" --table="my_table" \
--alter="ADD COLUMN new_column INT DEFAULT 0" \
--allow-on-master --execute

优点：
- 支持主从复制环境。
- 更少的资源占用，几乎无锁表风险。

2. 分步骤执行

如果不方便使用工具，可以手动分步骤减少对业务的影响：

步骤1：在线新增字段但不加默认值

直接执行新增字段操作时不要设置 DEFAULT 值，因为这会触发对整表数据的写入。

ALTER TABLE my_table ADD COLUMN new_column INT;

优点：不更新已有数据，速度较快，锁表时间短。

步骤2：分批更新字段值

通过分批更新的方式填充字段，避免对整个表一次性操作。

UPDATE my_table SET new_column = 0 WHERE id BETWEEN 1 AND 10000;

然后循环执行直到更新完成。

步骤3：添加默认值

字段更新完成后，再修改字段属性，增加默认值。

ALTER TABLE my_table MODIFY COLUMN new_column INT DEFAULT 0;

3. 利用分区表（如果表支持分区）

如果表设计时已经采用分区，可通过操作分区实现快速变更字段。

新增分区表结构，包含新字段。
将数据分批迁移到新表。
替换原始表。

4. 创建中间表

在某些情况下，直接对大表操作风险较高，可以考虑通过创建中间表来完成字段新增。

步骤1：创建中间表

创建一个包含新字段的中间表：

CREATE TABLE my_table_new LIKE my_table;
ALTER TABLE my_table_new ADD COLUMN new_column INT DEFAULT 0;

步骤2：迁移数据

将数据从原表分批插入到新表：

INSERT INTO my_table_new (col1, col2, ..., new_column)
SELECT col1, col2, ..., 0 FROM my_table;

步骤3：替换表

RENAME TABLE my_table TO my_table_old, my_table_new TO my_table;

5. 调整MySQL配置

在执行大表操作之前，可以调整MySQL的部分配置，优化性能：

禁用自动提交：
```
SET autocommit = 0;
```

延迟索引更新：在操作前禁用索引，完成后重建：

ALTER TABLE my_table DISABLE KEYS;
-- 执行操作
ALTER TABLE my_table ENABLE KEYS;

调整临时表空间和缓冲区：提高 innodb_buffer_pool_size 和 tmp_table_size，避免操作期间使用磁盘。

6. 选择低峰期操作

无论采用哪种方案，都建议在业务低峰期操作，减少对用户的影响。

总结

工具优先：首选使用在线DDL工具（如 pt-online-schema-change、gh-ost）。
分步骤处理：如果工具不可用，考虑分步骤执行新增字段和数据填充操作。
备份与监控：在操作前做好备份，并实时监控操作对数据库性能的影响。

这些方法可以显著降低新增字段操作对业务的影响。

线上有一张数据量级为几千万的表，需要删除数据，该如何处理呢

在 MySQL 中对一张数据量级为几千万的大表删除数据时，直接执行 DELETE 操作可能会导致以下问题：

锁表：可能阻塞其他查询和操作。
事务日志爆满：导致性能下降甚至系统崩溃。
性能问题：单次操作时间过长，影响线上业务。

因此需要采取一些优化策略，以下是详细处理方案：

1. 分批删除

通过限制每次删除的数据量，避免长时间锁表和性能下降。

示例

DELETE FROM my_table WHERE condition LIMIT 1000;

通过脚本或程序循环执行，直到删除完成。例如：

while [ condition ]; do
    mysql -uroot -p -e "DELETE FROM my_table WHERE condition LIMIT 1000;"
    sleep 1
done

优点

每次只删除少量数据，锁表时间短，对性能影响小。

注意

如果表有主键或索引，可以使用主键范围删除优化性能：
```
DELETE FROM my_table WHERE id BETWEEN 1 AND 10000;
```

2. 使用分区表

如果表是分区表，可以通过删除整个分区来快速清理数据。

示例

ALTER TABLE my_table DROP PARTITION partition_name;

如果分区是基于时间或其他规则设计的，可以直接删除过期分区。

优点

删除分区的速度远快于逐行删除。
避免全表扫描，极大降低对系统的影响。

注意

如果表未设计为分区表，则此方法不可行。

3. 使用中间表

对于复杂的删除操作，可以考虑通过创建新表来间接删除数据。

步骤

创建新表：

CREATE TABLE my_table_new LIKE my_table;

迁移需要保留的数据：

INSERT INTO my_table_new SELECT * FROM my_table WHERE condition_to_keep;

替换表：

RENAME TABLE my_table TO my_table_old, my_table_new TO my_table;

删除旧表：
```
DROP TABLE my_table_old;
```

优点

对线上业务几乎无影响。
避免锁表问题。

注意

适用于删除的大量数据与保留数据比例较低的场景。
操作前需确保有足够的磁盘空间存储新表。

4. 调整MySQL配置

在删除操作前，可以通过调整配置减少对性能的影响：

关闭自动提交：避免每次删除都写入日志，减少 I/O 开销。
```
SET autocommit = 0;
```

分批提交：在事务中分批删除并提交：

START TRANSACTION;
DELETE FROM my_table WHERE condition LIMIT 1000;
COMMIT;

禁用外键约束（如果存在）：

SET FOREIGN_KEY_CHECKS = 0;
DELETE FROM my_table WHERE condition;
SET FOREIGN_KEY_CHECKS = 1;

5. 使用 TRUNCATE 或 DELETE with PARTITION

如果需要删除全表数据或大部分数据，TRUNCATE 是高效的方式。

示例

TRUNCATE TABLE my_table;

适用场景：表中数据全部或大部分需要删除，并且不依赖外键。
优点：速度快，不记录事务日志。

6. 在线工具支持

使用在线DDL工具（如 pt-online-schema-change）辅助删除数据。工具通过创建临时表，逐行迁移有效数据的方式实现数据清理，确保对业务影响最小。

示例

pt-online-schema-change --alter "DELETE FROM my_table WHERE condition" --execute --user=root --password=yourpassword --host=localhost

优点

在线操作，无需锁表。
对性能影响极低。

7. 限制操作时间窗口

在业务低峰期执行删除操作，可以减少对用户的影响。结合分批删除和脚本控制时间：

示例

while [ $(date +%H) -lt 6 ]; do
    mysql -uroot -p -e "DELETE FROM my_table WHERE condition LIMIT 1000;"
    sleep 1
done

8. 监控和备份

在执行删除操作前，确保对表或数据库进行备份，防止数据丢失。
通过监控工具（如 MySQL Slow Query Log）观察操作对数据库的性能影响。

综合建议

如果业务允许，优先使用 分批删除 或 分区删除。
若数据清理涉及复杂逻辑，考虑 中间表迁移。
如果是一次性全表数据清理，使用 TRUNCATE 是最优方案。

这些方法能够有效解决大表数据删除的效率问题，同时最大限度降低对线上业务的影响。

上述的是行锁还是表锁

在 MySQL 中，大表的添加字段和删除数据操作是否是行锁还是表锁，取决于存储引擎（如 InnoDB 或 MyISAM）以及具体的 SQL 操作方式。以下是详细说明：

1. 添加字段（ALTER TABLE 操作）

锁类型：表锁。
原因：
1. MyISAM 存储引擎：
  - ALTER TABLE 操作会锁住整个表，阻止其他查询或写入，直到操作完成。
2. InnoDB 存储引擎：
  - 在某些情况下，ALTER TABLE 也会锁表，尤其是 MySQL 早期版本（< 5.6）。
  - 但是，现代版本（>= 5.6）支持在线 DDL（通过 ALGORITHM=INPLACE），某些字段的添加可以避免完全锁表。例如，添加非 NULL 字段且无默认值时，可以减少表锁的时间。
优化方式：
- 使用在线DDL工具（如 pt-online-schema-change 或 gh-ost），这些工具通过创建新表并逐行复制数据的方式，避免长时间表锁。
- 示例：
```
ALTER TABLE my_table ADD COLUMN new_column INT DEFAULT 0, ALGORITHM=INPLACE, LOCK=NONE;
```

2. 删除数据（DELETE 操作）

(1) InnoDB 存储引擎

InnoDB 默认使用行锁，但也可能升级为表锁，具体情况如下：

行锁

条件：如果有索引（主键或其他索引列），并且 WHERE 条件命中了索引，则 InnoDB 会使用行锁，仅锁住满足条件的行。

示例：

DELETE FROM my_table WHERE id = 123;  -- 行锁，仅锁住 id=123 的那一行。

表锁

触发条件：
- 删除操作未命中索引，导致全表扫描，此时会锁住整个表。
- 如果事务过大，行锁可能会因资源不足升级为表锁。

示例：

DELETE FROM my_table WHERE non_indexed_column = 'value';  -- 表锁，全表扫描。

优化方式：

分批删除：使用 LIMIT 分批减少锁住的行数。

DELETE FROM my_table WHERE condition LIMIT 1000;

确保索引可用：在删除条件的字段上建立索引，避免全表扫描。

(2) MyISAM 存储引擎

MyISAM 不支持行锁，所有写操作（如 DELETE）都会直接加表锁。

特点：

DELETE 操作锁住整个表，阻止其他线程读写操作。
操作完成后才释放表锁。

优化方式：

如果必须删除大量数据，考虑使用 CREATE TABLE 和数据迁移的方式，避免对业务的长时间影响。

总结

操作类型	存储引擎	锁类型	说明
添加字段	MyISAM	表锁	无法避免，操作期间阻塞所有其他查询和更新。
	InnoDB	表锁	使用在线DDL工具或 `ALGORITHM=INPLACE` 可减少锁表时间。
删除数据	MyISAM	表锁	写操作始终锁表。
	InnoDB	行锁	有索引命中条件时是行锁；若无索引则升级为表锁。

建议

尽量使用 InnoDB：支持行锁和更好的并发性能。
优化索引：确保 DELETE 和其他操作条件命中索引，避免锁表。
使用工具：在线DDL工具（如 pt-online-schema-change、gh-ost）是处理大表结构变更的首选方案。
分批操作：大规模删除数据时，分批完成，避免长时间锁定资源。

数据同步

双写，定时任务清洗，切换

以下是上述 SQL 练习题的答案与解析。假设我们使用的表名为 students 和 scores，根据每道题的要求编写具体的 SQL 语句。

基础题目

1. 查询班级总分最高的班级及其总分

SELECT class_id, SUM(score) AS total_score
FROM students
GROUP BY class_id
ORDER BY total_score DESC
LIMIT 1;

2. 查询每个班级中成绩最高的学生及其成绩

SELECT s.id, s.name, s.class_id, s.score
FROM students s
WHERE s.score = (
    SELECT MAX(score)
    FROM students
    WHERE class_id = s.class_id
);

3. 查询全校平均分及每个班级的平均分

SELECT
    (SELECT AVG(score) FROM students) AS overall_avg,
    class_id,
    AVG(score) AS class_avg
FROM students
GROUP BY class_id;

4. 查询成绩比班级平均分高的学生信息

SELECT s.id, s.name, s.class_id, s.score
FROM students s
WHERE s.score > (
    SELECT AVG(score)
    FROM students
    WHERE class_id = s.class_id
);

进阶题目

5. 查询每班的倒数第二名学生的成绩

SELECT s1.id, s1.name, s1.class_id, s1.score
FROM students s1
WHERE (
    SELECT COUNT(*)
    FROM students s2
    WHERE s2.class_id = s1.class_id AND s2.score > s1.score
) = 1;

6. 查询总分排在全校前三名的班级及其总分

SELECT class_id, SUM(score) AS total_score
FROM students
GROUP BY class_id
ORDER BY total_score DESC
LIMIT 3;

7. 查询连续两次成绩都高于 90 的学生

SELECT DISTINCT s1.student_id
FROM scores s1, scores s2
WHERE s1.student_id = s2.student_id
  AND s2.exam_id = s1.exam_id + 1
  AND s1.score > 90 AND s2.score > 90;

8. 统计每个班级中女生人数最多的班级

SELECT class_id, COUNT(*) AS female_count
FROM students
WHERE gender = 'F'
GROUP BY class_id
ORDER BY female_count DESC
LIMIT 1;

9. 查询所有班级的最高分、最低分和平均分

SELECT class_id,
       MAX(score) AS max_score,
       MIN(score) AS min_score,
       AVG(score) AS avg_score
FROM students
GROUP BY class_id;

综合题目

10. 查询每班成绩前 3 名学生的平均分

WITH ranked_students AS (
    SELECT s.id, s.class_id, s.score,
           ROW_NUMBER() OVER (PARTITION BY s.class_id ORDER BY s.score DESC) AS rank
    FROM students s
)
SELECT class_id, AVG(score) AS avg_top_3
FROM ranked_students
WHERE rank <= 3
GROUP BY class_id;

11. 查询总分超过班级平均分两倍的学生

WITH class_avg AS (
    SELECT class_id, AVG(score) AS avg_score
    FROM students
    GROUP BY class_id
)
SELECT s.id, s.name, s.class_id, s.score
FROM students s
JOIN class_avg ca ON s.class_id = ca.class_id
WHERE s.score > 2 * ca.avg_score;

12. 统计每个班级中通过的学生人数及通过率

SELECT class_id,
       COUNT(*) AS total_students,
       SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END) AS passed_students,
       SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS pass_rate
FROM students
GROUP BY class_id;

13. 查询每班最高分和第二高分相差最大的班级

WITH ranked_scores AS (
    SELECT s.class_id, s.score,
           ROW_NUMBER() OVER (PARTITION BY s.class_id ORDER BY s.score DESC) AS rank
    FROM students s
)
SELECT class_id,
       MAX(CASE WHEN rank = 1 THEN score ELSE NULL END) AS max_score,
       MAX(CASE WHEN rank = 2 THEN score ELSE NULL END) AS second_max_score,
       MAX(CASE WHEN rank = 1 THEN score ELSE NULL END) -
       MAX(CASE WHEN rank = 2 THEN score ELSE NULL END) AS score_diff
FROM ranked_scores
GROUP BY class_id
ORDER BY score_diff DESC
LIMIT 1;

14. 查询连续两次考试成绩均提升的学生

SELECT DISTINCT s1.student_id
FROM scores s1, scores s2
WHERE s1.student_id = s2.student_id
  AND s2.exam_id = s1.exam_id + 1
  AND s2.score > s1.score;

挑战题

15. 模拟分页查询：每页显示 5 条成绩最高的学生信息

SELECT id, name, class_id, score
FROM students
ORDER BY score DESC
LIMIT 5 OFFSET 5; -- 假设查询第 2 页

16. 查询学生成绩分布：每 10 分段统计人数

SELECT FLOOR(score / 10) * 10 AS score_range,
       COUNT(*) AS student_count
FROM students
GROUP BY FLOOR(score / 10)
ORDER BY score_range;

17. 查询每班平均分最高的前三门课程

WITH course_avg AS (
    SELECT class_id, course_id, AVG(score) AS avg_score
    FROM scores
    GROUP BY class_id, course_id
),
ranked_courses AS (
    SELECT class_id, course_id, avg_score,
           ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY avg_score DESC) AS rank
    FROM course_avg
)
SELECT class_id, course_id, avg_score
FROM ranked_courses
WHERE rank <= 3;

18. 查询全校前 5 名学生中各班级的人数

WITH top_students AS (
    SELECT class_id
    FROM students
    ORDER BY score DESC
    LIMIT 5
)
SELECT class_id, COUNT(*) AS student_count
FROM top_students
GROUP BY class_id;

19. 动态累计：查询每班成绩前 N 名的总分

WITH ranked_students AS (
    SELECT id, class_id, score,
           ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY score DESC) AS rank
    FROM students
)
SELECT class_id, SUM(score) AS top_n_total
FROM ranked_students
WHERE rank <= 3 -- N = 3
GROUP BY class_id;

20. 查询每班成绩与全校成绩差异最大的一名学生

WITH overall_avg AS (
    SELECT AVG(score) AS avg_score
    FROM students
),
score_diff AS (
    SELECT s.id, s.name, s.class_id, s.score,
           ABS(s.score - oa.avg_score) AS diff
    FROM students s
    CROSS JOIN overall_avg oa
)
SELECT id, name, class_id, score, diff
FROM score_diff
WHERE diff = (
    SELECT MAX(diff)
    FROM score_diff
);

总结

这些答案覆盖了 SQL 的各个方面，包括 GROUP BY、子查询、自关联、窗口函数等。可以将这些题目拆解练习，加深对 SQL 的理解。如果有不清楚的地方，可以随时提问！

SQL优化

间隙锁

1. 间隙锁的定义

2. 间隙锁的特性

3. 示例分析

场景 1: 范围查询加锁

场景 2: 插入操作受限

4. 总结

我更新什么样的字段会加间隙锁

1. 触发间隙锁的场景

(1) 涉及范围查询的更新

(2) 更新不存在的记录

(3) 主键/唯一索引以外的字段更新

2. 不会触发间隙锁的情况

(1) 精确匹配主键或唯一索引

(2) READ COMMITTED 隔离级别

3. 举例分析

场景 1: 范围更新

场景 2: 更新不存在的记录

场景 3: 非唯一索引字段更新

4. 如何避免不必要的间隙锁

(1) 使用精确查询条件

(2) 降低隔离级别

(3) 优化索引

总结

会阻塞读吗

1. 不会阻塞普通的 SELECT 查询

2. 会阻塞带锁的读取操作

(1) SELECT ... FOR UPDATE

(2) SELECT ... LOCK IN SHARE MODE

3. 特殊情况：幻读相关问题

4. 总结

UPDATE test SET value = 50 WHERE id > 10; SELECT * FROM test WHERE id = 5 FOR UPDATE;会加锁吗

1. 事务 A 执行的语句

2. 事务 B 执行的语句

3. 是否加锁、阻塞？

总结

复杂sql整理：

以下是几个复杂的 SQL 示例及其答案，涵盖多表查询、递归查询、窗口函数等场景：

例子 1：多表关联和条件过滤

问题：

SQL：

答案：

例子 2：递归查询（层级结构）

问题：

SQL：

答案：

例子 3：窗口函数

问题：

SQL：

答案：

例子 4：子查询和排名

问题：

SQL：

答案：

例子 5：复杂条件查询

问题：

SQL：

答案：

以下是使用 GROUP BY 和 HAVING 的例子，包括典型的过滤操作和业务场景。

例子 1：统计并过滤总数

问题：

SQL：

数据示例：

输出结果：

例子 2：多条件过滤

问题：

SQL：

数据示例：

输出结果：

例子 3：使用聚合函数和过滤

问题：

SQL：

数据示例：

输出结果：

例子 4：使用 GROUP BY 和复杂过滤条件

问题：

SQL：

数据示例：

输出结果：

(2) `READ COMMITTED` 隔离级别

1. 不会阻塞普通的 `SELECT` 查询

(1) `SELECT ... FOR UPDATE`

(2) `SELECT ... LOCK IN SHARE MODE`

以下是使用 `GROUP BY` 和 `HAVING` 的例子，包括典型的过滤操作和业务场景。

例子 4：使用 `GROUP BY` 和复杂过滤条件