服务端2026年5月27日 18:26
MariaDB 如何进行索引优化？有哪些索引类型和优化策略？## MariaDB 有哪些索引类型？各自的适用场景是什么？
MariaDB 支持多种索引类型，理解它们的区别是做优化决策的基础。
**B-Tree 索引**是 MariaDB 的默认索引结构，绝大多数场景下使用的都是它。B-Tree 采用平衡多路搜索树结构，叶子节点通过双向链表连接，天然支持等值查询、范围查询和排序操作。当你执行 `WHERE id BETWEEN 100 AND 200` 或 `ORDER BY created_at DESC` 时，B-Tree 可以高效地利用索引的有序性完成扫描，而不需要额外的文件排序（filesort）。
**哈希索引**仅支持等值匹配查询，不支持范围查询和排序。它的查询时间复杂度接近 O(1)，在精确查找场景下比 B-Tree 更快。MariaDB 中哈希索引主要用于 MEMORY 存储引擎的表，InnoDB 的自适应哈希索引（Adaptive Hash Index）是引擎内部自动维护的，不需要手动创建。面试中如果被问到"哈希索引为什么不能用于范围查询"，核心原因是哈希值之间没有大小关系，无法利用有序性做区间扫描。
**全文索引（FULLTEXT）**专门用于文本内容的模糊搜索，底层基于倒排索引实现。相比于 `LIKE '%关键词%'` 会导致全表扫描，全文索引可以快速定位包含目标词的记录。MariaDB 支持 `MATCH ... AGAINST` 语法，提供自然语言模式和布尔模式两种查询方式。需要注意的是，全文索引对中文分词的支持有限，通常需要借助 ngram 解析器或 Mroonga 引擎来处理中文场景。
**空间索引（SPATIAL）**用于地理空间数据类型的索引，底层基于 R-Tree 结构。适合处理点、线、多边形等 GIS 数据的空间关系查询，比如"查找某坐标 5 公里范围内的门店"。空间索引仅支持 InnoDB 和 MyISAM 引擎，且索引列必须声明为 `NOT NULL`。
**聚簇索引**不是一个独立的索引类型，而是 InnoDB 的数据组织方式。InnoDB 的主键索引就是聚簇索引——叶子节点直接存储完整的行数据，而非主键索引（二级索引）的叶子节点存储的是主键值。这意味着通过二级索引查找数据时，需要先查到主键值，再回表查询完整行数据，这个过程叫做"回表"。理解聚簇索引和回表机制，是掌握覆盖索引优化前提。
## 创建索引时应该遵循哪些设计原则？
索引不是越多越好。每多一个索引，INSERT/UPDATE/DELETE 就多一份维护成本，同时占用额外的磁盘空间。设计索引时需要把握几个关键原则。
**优先对高选择性列建索引。** 选择性指的是列中不同值的数量与总行数的比值。选择性越高，索引过滤效果越好。例如用户表的 email 列选择性接近 1，几乎每条记录值都不同，索引过滤效率极高；而性别列只有两三个值，选择性极低，索引对查询的帮助微乎其微，优化器大概率会选择全表扫描。一个经验阈值是：当某值占比超过全表的 20% 时，优化器通常放弃使用索引。
**复合索引要遵循最左前缀原则。** 对于索引 `idx_abc(a, b, c)`，查询条件用到了 a、(a,b)、(a,b,c) 都能命中索引，但只用 b 或 c 则无法使用。实际设计中，应该把等值查询的列放在前面，范围查询的列放在后面。例如 `WHERE status = 1 AND created_at > '2024-01-01'`，应建索引 `(status, created_at)` 而非 `(created_at, status)`，因为等值过滤在前可以大幅缩小范围查询的扫描区间。
**利用覆盖索引减少回表。** 如果查询需要的所有列都包含在索引中，InnoDB 直接从索引返回数据，无需回表读取行记录。例如 `SELECT id, name FROM users WHERE name = 'John'`，如果 name 列上有索引且索引包含了 id（InnoDB 二级索引自动包含主键），这就是一次覆盖索引扫描。在 EXPLAIN 输出中，覆盖索引的 Extra 列会显示 `Using index`。
**避免在索引列上使用函数或表达式。** `WHERE YEAR(created_at) = 2024` 会导致索引失效，因为 MariaDB 需要对每一行计算函数值后才能比较。正确的写法是 `WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'`，这样优化器可以利用 B-Tree 的有序性做范围扫描。
## 如何使用 EXPLAIN 分析查询的索引使用情况？
EXPLAIN 是索引优化的核心工具，它展示优化器为查询选择的执行计划。
```sql
EXPLAIN SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 1 AND o.created_at > '2024-01-01';
```
重点关注以下几个字段：
- **type**：访问类型，从好到差依次为 `system > const > eq_ref > ref > range > index > ALL`。出现 `ALL` 意味着全表扫描，需要重点优化。`ref` 表示使用索引等值匹配，`range` 表示索引范围扫描。
- **key**：实际使用的索引名称。如果显示 NULL，说明没有可用索引。
- **rows**：预估扫描行数。这个值越小越好，但它是基于统计信息的估算值，不一定精确。
- **Extra**：额外信息。`Using index` 表示覆盖索引，`Using filesort` 表示需要额外排序，`Using temporary` 表示使用了临时表，后两者通常意味着性能瓶颈。
一个实用的工作流是：先跑 EXPLAIN 看执行计划，发现 type 为 ALL 或 rows 过大时，针对性地添加或调整索引，再反复验证。
## 什么是索引失效？哪些常见写法会导致索引失效？
索引失效指的是查询条件虽然涉及了索引列，但优化器最终选择不使用索引而做全表扫描。以下几种写法是常见的索引失效陷阱。
**对索引列使用函数或运算：** `WHERE name LIKE '%John'`（左模糊）、`WHERE YEAR(date_col) = 2024`、`WHERE id + 1 = 100`，这些写法破坏了 B-Tree 的有序性，优化器无法利用索引定位。
**隐式类型转换：** 当列是 VARCHAR 类型，查询条件写成 `WHERE phone = 13800001111`（数字类型），MariaDB 会将列值转为数字再做比较，这相当于对列施加了隐式函数，导致索引失效。正确写法是 `WHERE phone = '13800001111'`。
**OR 条件连接不同索引列：** `WHERE name = 'John' OR age = 25`，如果 name 和 age 各有独立索引，MariaDB 在某些情况下可以使用 Index Merge 优化，但效果往往不如预期，不如改写为 UNION ALL 两个子查询。
**NOT IN、NOT EXISTS、!=、<>：** 这些否定条件可能导致索引失效，尤其是结果集占比较大时。但并非绝对——如果否定条件过滤性很强（排除的值很少），优化器仍可能选择索引。
**索引列 IS NULL：** 在 MariaDB 中，B-Tree 索引是包含 NULL 值的，`WHERE col IS NULL` 可以使用索引。这一点与 Oracle 等数据库不同，面试中注意区分。
## 如何通过覆盖索引和索引下推提升查询性能？
**覆盖索引**在前文已经提到，核心思路是让查询所需的所有列都在索引中，从而避免回表。实际应用中，可以通过 SELECT 指定列或将常用查询列加入复合索引来实现。
```sql
-- 订单列表查询，只需要 id、status、created_at
SELECT id, status, created_at FROM orders WHERE user_id = 100;
-- 建立覆盖索引 (user_id, status, created_at)
-- InnoDB 二级索引自动包含主键 id，因此这三列 + id 都在索引中
ALTER TABLE orders ADD INDEX idx_user_status_created(user_id, status, created_at);
```
**索引下推（Index Condition Pushdown，ICP）** 是 MariaDB 5.6+ 引入的优化。传统流程中，二级索引查到主键后必须回表才能判断 WHERE 中的其他条件；启用 ICP 后，存储引擎在索引扫描阶段就根据 WHERE 条件过滤，减少回表次数。
```sql
-- 假设有索引 (last_name, first_name)
SELECT * FROM users WHERE last_name = 'Smith' AND first_name LIKE '%ohn';
-- 没有 ICP：先通过 last_name 索引查到所有 Smith 的主键，逐个回表再过滤 first_name
-- 有 ICP：在索引扫描时直接对 first_name 做 LIKE 判断，不满足的跳过，减少回表
```
在 EXPLAIN 的 Extra 列中，ICP 会显示 `Using index condition`。ICP 的适用条件是：查询使用了复合索引，且 WHERE 中有索引前列的等值条件加上后续列的条件过滤。
## 如何监控和维护索引的健康状态？
索引不是建完就一劳永逸的，随着数据增删改，索引可能出现碎片化、统计信息过期等问题，需要定期维护。
```sql
-- 查看表的索引信息
SHOW INDEX FROM users;
-- 查看索引统计信息
SELECT INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, CARDINALITY
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'users';
-- 更新表的统计信息（不锁表）
ANALYZE TABLE users;
-- 重建表以消除碎片（会锁表）
OPTIMIZE TABLE users;
```
**识别无用索引：** 可以通过 `sys.schema_unused_indexes` 视图（MariaDB 10.6+）或开启 `userstat` 插件来追踪索引使用情况。长期未使用的索引应该清理，减少写入开销。
**监控索引碎片：** 频繁的增删改会导致索引页出现空洞，降低索引扫描效率。`OPTIMIZE TABLE` 会重建表和索引，消除碎片，但操作期间会锁表，建议在低峰期执行。对于大表，可以考虑使用 `ALTER TABLE ... ENGINE=InnoDB` 的方式在线重建。
**统计信息维护：** 优化器依赖统计信息（cardinality、rows 等）来选择执行计划。如果统计信息严重失真，可能导致优化器选错索引。定期执行 `ANALYZE TABLE` 可以刷新统计信息，且在 MariaDB 10.4+ 中该操作是在线进行的，不会阻塞读写。
## MariaDB 索引优化有哪些常见的实战案例？
**案例一：分页查询优化**
深分页是典型的性能杀手。`SELECT * FROM orders ORDER BY id LIMIT 100000, 10` 需要先扫描 100010 行再丢弃前 100000 行。
```sql
-- 方案一：游标分页（推荐）
-- 前端记录上一页最后一条的 id，下一页查询时带上
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;
-- 方案二：延迟关联
-- 先通过子查询在索引上定位 id，再回表取数据
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 10) t
ON o.id = t.id;
```
**案例二：多条件组合查询**
`SELECT * FROM products WHERE category_id = 5 AND status = 1 AND price BETWEEN 100 AND 500 ORDER BY sales_count DESC LIMIT 20;`
索引设计：`(category_id, status, sales_count)`。前两列做等值过滤缩小范围，第三列利用索引有序性避免 filesort。price 列的范围查询放在最后处理。
**案例三：大表 JOIN 优化**
```sql
SELECT o.id, u.name FROM orders o JOIN users u ON o.user_id = u.id
WHERE o.status = 2 AND u.region = 'CN';
```
确保 JOIN 条件列（user_id、id）有索引，同时 orders 表在 status 上建索引、users 表在 region 上建索引，使驱动表的过滤结果尽可能小，减少循环 JOIN 的次数。
掌握以上索引类型、设计原则、分析工具和实战技巧，基本能应对 MariaDB 索引优化的大部分面试问题和线上场景。核心思路始终是：用 EXPLAIN 验证，让索引覆盖查询，减少回表和全表扫描。标签
MariaDB
MariaDB 是一个开源数据库服务器，为 MySQL 提供直接替换功能。
服务端2026年5月27日 18:24
MariaDB 的 JSON 函数怎么用？有哪些常见坑？MariaDB 从 10.2 开始提供了一套 JSON 函数，能直接在 SQL 里创建、查询、修改和校验 JSON 数据。JSON 列本质是 LONGTEXT 加 CHECK 约束，不是 MySQL 那种二进制格式，这一点在迁移时容易踩坑。
## 追问
### JSON_EXTRACT、JSON_VALUE、JSON_QUERY 有什么区别？
三个都是取值，但返回类型不同：
| 函数 | 返回值 | 示例 |
|------|--------|------|
| JSON_EXTRACT | 原始 JSON 片段（带引号） | `"John"` |
| JSON_VALUE | 标量值（去引号） | `John` |
| JSON_QUERY | 对象或数组 | `{"city":"NY"}` |
日常取字符串值用 `->` 操作符（JSON_EXTRACT 的语法糖），取标量用 JSON_VALUE，取嵌套对象用 JSON_QUERY。
### JSON_SET、JSON_INSERT、JSON_REPLACE 有什么区别？
- **JSON_SET**：存在则更新，不存在则插入——万能选手
- **JSON_INSERT**：只在路径不存在时插入，已有值不动
- **JSON_REPLACE**：只在路径已存在时替换，没找到就跳过
记住一句话：不确定用 SET，只想加新字段用 INSERT，只想改旧字段用 REPLACE。
### JSON 列怎么加索引？
JSON 列不能直接建普通索引。两种方式：
1. **生成列 + 索引**（推荐）：
```sql
ALTER TABLE products
ADD COLUMN brand VARCHAR(50)
GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.brand'))) STORED,
ADD INDEX idx_brand(brand);
```
2. **函数索引**（MariaDB 10.3+）：
```sql
CREATE INDEX idx_brand ON products((CAST(attributes->'$.brand' AS CHAR(50))));
```
### MariaDB 的 JSON 和 MySQL 的 JSON 有什么区别？
这是迁移时最容易翻车的地方：
| 对比项 | MariaDB | MySQL |
|--------|---------|-------|
| 存储格式 | 原文 LONGTEXT | 二进制 JSON |
| JSON 类型 | LONGTEXT 的别名 | 独立数据类型 |
| 自动校验 | 需要 CHECK 约束 | 内置校验 |
| 部分更新 | 不支持 | 支持二进制增量更新 |
MariaDB 存原文的好处是可以直接用文本函数处理，坏处是每次修改整个字段重写，大 JSON 字段更新性能差。
### 用 JSON 列存数据有什么坑？
1. **没有 schema 约束**：同列不同行结构可以完全不同，查出来才知道长什么样，排查问题靠蒙
2. **查询性能**：每次取值都要解析 JSON，高频查询字段务必抽成普通列加索引
3. **更新代价**：改一个字段整个 JSON 重写，大文档更新慢
4. **CHECK 约束别忘了加**：
```sql
CREATE TABLE products (
id INT PRIMARY KEY,
attrs JSON,
CONSTRAINT chk_json CHECK (JSON_VALID(attrs))
);
```
## 写段代码
```sql
-- 建表 + 插入 + 查询一条龙
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
attrs JSON CHECK (JSON_VALID(attrs))
);
INSERT INTO products (name, attrs) VALUES
('Laptop', '{"brand":"Dell","ram":"16GB"}');
-- 查品牌、改内存
SELECT name, attrs->'$.brand' AS brand FROM products;
UPDATE products SET attrs = JSON_SET(attrs, '$.ram', '32GB') WHERE id = 1;
```服务端2026年5月27日 17:38
如何优化MariaDB查询性能？查询性能是数据库系统的生命线。一条低效的SQL可能拖垮整个应用，而一次精准的优化能让响应时间从秒级降到毫秒级。这篇文章从诊断、索引、写法、配置四个层面，给出经过生产验证的优化方法。
## 用 EXPLAIN 定位性能瓶颈
优化之前，先要找到问题。EXPLAIN 是最直接的诊断工具：
```sql
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';
```
输出中有四个字段值得重点关注：
- **type** — 访问类型，从差到优依次为 ALL → index → range → ref → eq_ref → const。出现 ALL 意味着全表扫描，必须优化
- **key** — 实际使用的索引。如果为 NULL，说明索引未被命中
- **rows** — 预估扫描行数。数字越大，查询越慢
- **Extra** — Using filesort 表示额外排序，Using temporary 表示使用了临时表，两者都应尽量避免
一个简单的判断标准：type 不是 ALL 且 Extra 没有 Using filesort/Using temporary，查询基本合格。
## 索引：最有效的加速手段
### 建立合适的复合索引
单列索引在多条件查询时往往不够用。复合索引遵循最左前缀原则，把区分度高的列放前面：
```sql
-- 假设查询条件为 WHERE user_id = ? AND status = ?
-- user_id 区分度远高于 status，放前面
CREATE INDEX idx_user_status ON orders(user_id, status);
```
### 用覆盖索引避免回表
当查询的列全部包含在索引中时，引擎无需回表读取数据行，性能提升显著：
```sql
-- 索引 idx_user_status(user_id, status) 无法覆盖此查询（需要 amount 列）
SELECT user_id, status, amount FROM orders WHERE user_id = 100;
-- 建立覆盖索引后，直接从索引读取所有数据
CREATE INDEX idx_user_status_amount ON orders(user_id, status, amount);
```
### 避免索引失效的常见写法
以下写法会导致索引无法命中：
- 对索引列使用函数：`WHERE YEAR(created_at) = 2025` 改为 `WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'`
- 隐式类型转换：`WHERE varchar_col = 123` 改为 `WHERE varchar_col = '123'`
- 前缀模糊查询：`WHERE name LIKE '%John'` 改为 `WHERE name LIKE 'John%'`
- 使用 OR 连接不同索引列：改用 UNION ALL 拆分
## 查询写法的优化技巧
### 只查需要的列
SELECT * 是性能杀手。它强制读取所有列的数据，增加 I/O 和内存开销，还可能破坏覆盖索引：
```sql
-- 不推荐
SELECT * FROM users WHERE id = 1;
-- 推荐：只查业务需要的列
SELECT id, name, email FROM users WHERE id = 1;
```
### 用 JOIN 替代子查询
MariaDB 优化器对子查询的处理不如 JOIN 高效，特别是 IN 子查询：
```sql
-- 不推荐
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);
-- 推荐
SELECT u.id, u.name, u.email FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.amount > 1000;
```
### UNION ALL 替代 UNION
UNION 会对结果去重，需要额外的排序操作。如果确定结果集无重复，用 UNION ALL 省掉去重开销：
```sql
-- 不需要去重时
SELECT name FROM customers WHERE region = 'east'
UNION ALL
SELECT name FROM suppliers WHERE region = 'east';
```
### 深分页的两种优化方案
OFFSET 值很大时，数据库需要扫描并跳过前面的所有行：
```sql
-- 传统写法：跳过 10 万行，极其缓慢
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;
-- 方案一：游标分页（要求排序字段连续且有索引）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;
-- 方案二：延迟关联（先查主键再回表，减少扫描列数）
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 10) tmp
ON o.id = tmp.id;
```
### JOIN 优化
- 被驱动表的连接列必须有索引
- 小结果集驱动大表，减少循环次数
- 当优化器选错连接顺序时，用 STRAIGHT_JOIN 强制指定：
```sql
SELECT * FROM small_table s
STRAIGHT_JOIN large_table l ON s.id = l.small_id;
```
## 配置层面的调优
### InnoDB 缓冲池
这是影响 InnoDB 性能最重要的参数，建议设为物理内存的 50%-70%：
```ini
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
```
### 排序和连接缓冲
```ini
sort_buffer_size = 4M -- 每个连接的排序缓冲
join_buffer_size = 4M -- 每个无索引连接的缓冲
read_rnd_buffer_size = 4M -- MRR 读取缓冲
```
### 临时表大小
```ini
tmp_table_size = 256M
max_heap_table_size = 256M
```
超过此大小的临时表会写到磁盘，导致性能骤降。
### 关于查询缓存
注意：MariaDB 10.6 起默认禁用查询缓存，后续版本已移除该功能。如果使用 10.6+，不要配置 query_cache_size，而是关注应用层缓存（如 Redis）。
## 监控慢查询
开启慢查询日志，定期分析并优化：
```sql
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 超过 1 秒记录
SET GLOBAL log_queries_not_using_indexes = ON; -- 记录未使用索引的查询
```
结合 pt-query-digest 工具分析慢查询日志，找出最需要优化的 SQL：
```bash
pt-query-digest /var/lib/mysql/slow.log
```
## 优化决策路径
面对一个慢查询，按以下顺序排查：
1. 先用 EXPLAIN 查看执行计划，确认是否走了索引
2. 如果走了索引仍然慢，考虑建立覆盖索引或调整索引列顺序
3. 如果索引没有问题，检查查询写法是否有优化空间（避免 SELECT *、子查询改 JOIN、深分页优化）
4. 如果单条 SQL 已最优，考虑配置调优（缓冲池、排序缓冲、临时表大小）
5. 配置也调不动了，考虑架构层面优化（读写分离、分库分表、引入缓存）
每个阶段都有明确的检查点和动作，避免盲目调参。服务端2026年5月27日 15:06
MariaDB 的事务隔离级别如何工作？怎样根据业务场景选择合适的隔离级别？## 事务隔离级别要解决什么问题
多个事务并发执行时，如果不加任何隔离措施，会出现三类数据不一致的问题：
- **脏读（Dirty Read）**：事务 A 读到了事务 B 尚未提交的数据。如果事务 B 回滚，事务 A 拿到的就是根本不存在的"脏数据"。
- **不可重复读（Non-Repeatable Read）**：事务 A 两次读取同一行数据，中间事务 B 修改并提交了这行，导致两次读到的值不同。
- **幻读（Phantom Read）**：事务 A 两次执行相同的范围查询，中间事务 B 插入了新行并提交，导致第二次查询多出了"幻影行"。
这三类问题逐层递进：脏读是读到了未提交的修改，不可重复读是已提交的修改导致同一行前后不一致，幻读是已提交的新增导致行数变化。SQL 标准据此定义了四种隔离级别，每种级别禁止一部分问题。
## 四种隔离级别
### READ UNCOMMITTED（读未提交）
最低隔离级别，允许事务读取其他事务未提交的修改。在这个级别下，脏读、不可重复读、幻读都可能发生。实际业务中几乎不会使用——读到未提交的数据意味着可能基于错误数据做出决策，风险极高。
### READ COMMITTED（读已提交）
只允许读取已经提交的数据，杜绝了脏读。但同一事务内两次读取同一行，可能因为其他事务的提交而得到不同结果，所以不可重复读和幻读仍然存在。
Oracle 和 PostgreSQL 默认使用这个级别。如果你的业务对同一事务内数据一致性要求不高（比如报表查询、大多数 Web 应用的读操作），READ COMMITTED 是一个性能和正确性的折中选择。
### REPEATABLE READ（可重复读）
保证同一事务内多次读取同一行的结果一致，杜绝了脏读和不可重复读。按照 SQL 标准，幻读在这个级别仍然可能发生。但 MariaDB/MySQL 的 InnoDB 引擎通过 MVCC 和 Gap Lock 机制，在 REPEATABLE READ 下也避免了幻读——这比 SQL 标准更严格。
MariaDB 和 MySQL 的默认隔离级别就是 REPEATABLE READ。大多数 OLTP 场景不需要改动它。
### SERIALIZABLE（串行化）
最高隔离级别，所有事务按顺序串行执行，完全杜绝脏读、不可重复读和幻读。实现方式是对所有读取的行加共享锁，其他事务无法修改这些行直到锁释放。
性能代价极大——并发度几乎归零。只在对数据一致性有极端要求的场景下使用，比如金融对账、审计等。
## 隔离级别与并发问题的对应关系
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| READ UNCOMMITTED | 可能 | 可能 | 可能 |
| READ COMMITTED | 不会 | 可能 | 可能 |
| REPEATABLE READ | 不会 | 不会 | 可能（SQL 标准）/ 不会（MariaDB InnoDB） |
| SERIALIZABLE | 不会 | 不会 | 不会 |
## MVCC 是怎么工作的
MVCC（Multi-Version Concurrency Control，多版本并发控制）是 InnoDB 实现 REPEATABLE READ 和 READ COMMITTED 的核心机制。它的基本思路是：每行数据保留多个版本，读操作访问的是某个历史快照，写操作创建新版本，读写互不阻塞。
InnoDB 在每行记录后添加两个隐藏列：
- **DB_TRX_ID**：最后修改该行的事务 ID。
- **DB_ROLL_PTR**：回滚指针，指向 undo log 中该行的前一个版本。
每个事务开始时会获得一个递增的事务 ID。读取数据时，InnoDB 根据当前事务 ID 和 undo log 链构建一个一致性视图（Read View），只返回对当前事务可见的版本。
MVCC 在两个隔离级别下的行为差异：
- **REPEATABLE READ**：事务第一次读取时创建 Read View，整个事务期间复用同一个 View，所以同一行数据多次读取结果一致。
- **READ COMMITTED**：每次 SELECT 都创建新的 Read View，所以能看到其他事务已提交的最新数据。
这就是为什么 READ COMMITTED 下会出现不可重复读，而 REPEATABLE READ 不会——Read View 的创建时机不同。
## Gap Lock 与 Next-Key Lock
MVCC 解决了快照读（普通 SELECT）的幻读问题，但当前读（SELECT ... FOR UPDATE、UPDATE、DELETE 等加锁读）怎么办？InnoDB 的答案是 Gap Lock 和 Next-Key Lock。
- **Record Lock**：锁定索引上的单条记录。
- **Gap Lock**：锁定两条记录之间的间隙，阻止其他事务在该间隙中插入新行。
- **Next-Key Lock**：Record Lock + Gap Lock 的组合，锁定一条记录及其前面的间隙。这是 InnoDB 在 REPEATABLE READ 下的默认加锁方式。
举个例子：表中有 id = 1、5、10 三条记录。对 id = 5 加 Next-Key Lock 时，实际锁住的范围是 (1, 5]，即 id 大于 1 且小于等于 5 的区间。其他事务无法在这个范围内插入新行（比如 id = 3），从而防止了幻读。
在 READ COMMITTED 下，Gap Lock 被禁用（外键约束检查和唯一键冲突检查除外），只使用 Record Lock。这意味着其他事务可以在已锁定记录的间隙中自由插入，并发度更高，但可能出现幻读。
## InnoDB 与 MyISAM 的关键区别
讨论事务隔离级别的前提是存储引擎支持事务。MariaDB 同时支持 InnoDB 和 MyISAM，但两者在事务能力上有本质区别：
- **InnoDB**：支持完整的 ACID 事务、行级锁、MVCC、外键约束和崩溃恢复。事务隔离级别的所有讨论都基于 InnoDB。
- **MyISAM**：不支持事务、不支持行级锁（只有表级锁）、没有 MVCC、没有崩溃恢复。在 MyISAM 表上执行 `START TRANSACTION` 不会有任何效果，`ROLLBACK` 也不会回滚任何修改。
如果你的表使用 MyISAM 引擎，事务隔离级别的设置毫无意义。检查方法：
```sql
SELECT ENGINE FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'your_table';
```
如果是 MyISAM，需要先转为 InnoDB：
```sql
ALTER TABLE your_table ENGINE = InnoDB;
```
MariaDB 5.5 起默认存储引擎已经是 InnoDB，新建的表无需额外指定。
## 如何设置隔离级别
### 查看当前隔离级别
```sql
-- 查看全局默认隔离级别
SELECT @@GLOBAL.transaction_isolation;
-- 查看当前会话隔离级别
SELECT @@SESSION.transaction_isolation;
-- 兼容写法（MariaDB 中仍可用）
SELECT @@tx_isolation;
```
### 设置隔离级别
```sql
-- 仅影响下一个事务
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 影响当前会话的所有后续事务
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 影响所有新会话的默认隔离级别（需要 SUPER 权限）
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
```
注意：事务已经开始后不能修改隔离级别，否则会报错 `ERROR 1568 (25001): Transaction characteristics can't be changed while a transaction is in progress`。
### 在配置文件中设置
在 `my.cnf` 中设置全局默认：
```ini
[mysqld]
transaction-isolation = READ-COMMITTED
```
重启后生效。
## MariaDB 与 MySQL 的差异
MariaDB 是 MySQL 的分支，事务隔离机制基本一致，但有几个值得注意的差异：
- **tx_isolation vs transaction_isolation**：MySQL 8.0.3 移除了 `tx_isolation` 别名，只使用 `transaction_isolation`；MariaDB 两者都支持。
- **WITH CONSISTENT SNAPSHOT**：MariaDB 的 `START TRANSACTION WITH CONSISTENT SNAPSHOT` 兼容所有隔离级别，MySQL 8.0 前只支持 REPEATABLE READ。
- **Gap Lock 行为**：两者在 REPEATABLE READ 下的 Gap Lock 策略相同，但具体死锁场景可能因版本不同而有差异。
- **默认二进制日志**：MySQL 8.0 默认开启 binlog，MariaDB 默认关闭。binlog 的开启与否会影响事务的提交流程和性能。
- **Aria 引擎**：MariaDB 用 Aria 替代 MyISAM 作为非事务型引擎的选择，Aria 支持崩溃安全特性。
## 怎么选择隔离级别
选择隔离级别本质上是正确性和并发性能之间的权衡：
- **大多数 Web 应用**：保持默认的 REPEATABLE READ 即可。InnoDB 的 MVCC 让读操作不加锁，性能开销可控。
- **高并发短事务场景**（如秒杀、库存扣减）：考虑降级到 READ COMMITTED。Gap Lock 在高并发下容易导致死锁，去掉 Gap Lock 可以减少锁冲突。代价是需要业务层处理不可重复读。
- **报表和数据分析**：READ COMMITTED 通常够用。报表查询对同一事务内的一致性要求不高，但需要看到最新提交的数据。
- **金融对账和审计**：SERIALIZABLE 或者在应用层加分布式锁。数据一致性优先，性能可以妥协。
- **READ UNCOMMITTED**：几乎没有任何合理的使用场景。即使你不在乎一致性，它也不会比 READ COMMITTED 快多少——InnoDB 在 RC 级别下读操作同样不加锁。
一个常见的调优方向：把 REPEATABLE READ 降为 READ COMMITTED，减少 Gap Lock 带来的死锁问题。Drupal 官方就推荐使用 READ COMMITTED 替代默认的 REPEATABLE READ 来避免死锁。如果你的业务逻辑中大量使用范围查询和插入操作混合的场景，值得做这个调整。
从性能角度看，隔离级别从低到高，锁持有时间递增、锁范围递增、并发度递减。REPEATABLE READ 的 Read View 在事务期间一直持有，长事务会占用大量 undo log 空间；READ COMMITTED 每次 SELECT 创建新 Read View，undo log 压力更小。所以控制事务长度比选择隔离级别本身更重要——无论用哪个级别，都应该让事务尽可能短。服务端2026年5月27日 10:48
MariaDB 窗口函数怎么用？排名、累计和同比计算详解窗口函数是 SQL 里做数据分析最好用的工具——不用窗口函数，计算排名、累计、同比这些需求得写各种子查询和自连接，代码又长又慢。MariaDB 从 10.2 开始支持窗口函数，基本覆盖了 SQL 标准的核心功能。
## 语法结构
每个窗口函数都遵循同一套语法：
```sql
函数名(表达式) OVER (
PARTITION BY 分组字段
ORDER BY 排序字段
ROWS/RANGE 窗口范围
)
```
OVER 子句定义了"窗口"——函数在这个范围内计算。PARTITION BY 把数据分组，每组独立计算；ORDER BY 决定组内排序；ROWS/RANGE 进一步约束参与计算的行范围。
## 排名函数：ROW_NUMBER、RANK、DENSE_RANK
三个排名函数的区别在处理并列值时的行为：
- **ROW_NUMBER**：严格递增，1-2-3-4，不管值是否相同
- **RANK**：并列同名，跳号，1-1-3-4
- **DENSE_RANK**：并列同名，不跳号，1-1-2-3
典型场景——每个部门薪资前三名：
```sql
SELECT * FROM (
SELECT name, department, salary,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
FROM employees
) t WHERE rnk <= 3;
```
用 DENSE_RANK 而不是 RANK，因为如果前三名有并列，RANK 会跳号，导致实际返回的记录少于 3 条。
## 聚合函数做累计和移动平均
窗口函数让 SUM/AVG/COUNT 不再只是"一组一个数"，而是逐行累计：
```sql
-- 累计销售额
SELECT order_date, amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;
-- 7 天移动平均
SELECT order_date, amount,
AVG(amount) OVER (ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d
FROM orders;
```
ROWS BETWEEN ... AND ... 定义了参与计算的行范围。6 PRECEDING AND CURRENT ROW 表示当前行和前 6 行，总共 7 行做平均。
## LAG 和 LEAD：访问前后行
LAG 取前 N 行的值，LEAD 取后 N 行的值。算环比增长率靠它们：
```sql
SELECT month, revenue,
LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
ROUND((revenue - LAG(revenue, 1) OVER (ORDER BY month))
/ LAG(revenue, 1) OVER (ORDER BY month) * 100, 2) AS growth_pct
FROM monthly_sales;
```
LAG 的第二个参数是偏移量，第三个参数是默认值（缺省返回 NULL）。算同比就改成 `LAG(revenue, 12)`，往前取 12 个月。
## FIRST_VALUE 和 LAST_VALUE 的坑
FIRST_VALUE 取分组内第一个值，LAST_VALUE 取最后一个值。但 LAST_VALUE 有个常见陷阱——默认窗口范围是 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，不是整个分区。所以如果你想取部门最低薪资，必须显式指定窗口范围：
```sql
-- 正确写法：指定完整窗口范围
SELECT name, department, salary,
LAST_VALUE(salary) OVER (
PARTITION BY department ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS lowest_in_dept
FROM employees;
```
不加 ROWS BETWEEN ... UNBOUNDED FOLLOWING，LAST_VALUE 每行返回的值都不一样——因为窗口只到当前行为止。
## ROWS 和 RANGE 的区别
- **ROWS**：按物理行号计算，窗口大小固定
- **RANGE**：按逻辑值范围计算，同一排序值的行作为一个整体
```sql
-- ROWS：固定 3 行窗口
SUM(amount) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
-- RANGE：同一天的行一起算
SUM(amount) OVER (ORDER BY date RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW)
```
RANGE 适合按时间窗口聚合，同一时间点的所有行会被包含在同一个窗口内。ROWS 更精确，适合固定行数的滑动窗口。服务端2月21日 15:51
MariaDB 性能调优有哪些关键参数和优化策略？MariaDB 的性能调优需要从多个维度进行优化，以下是主要的调优策略：
## 1. 配置参数优化
```ini
# my.cnf 配置文件
# 连接配置
max_connections = 500
max_connect_errors = 100000
wait_timeout = 28800
interactive_timeout = 28800
# InnoDB 配置
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
# MyISAM 配置
key_buffer_size = 256M
myisam_sort_buffer_size = 64M
# 查询缓存（MariaDB 10.3+ 已移除）
# query_cache_size = 64M
# query_cache_type = 1
# 临时表配置
tmp_table_size = 256M
max_heap_table_size = 256M
# 排序和连接配置
sort_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
join_buffer_size = 2M
# 线程配置
thread_cache_size = 16
thread_stack = 256K
# 日志配置
slow_query_log = 1
long_query_time = 2
log_queries_not_using_indexes = 1
```
## 2. 内存优化
```sql
-- 查看 InnoDB 缓冲池使用情况
SHOW STATUS LIKE 'Innodb_buffer_pool%';
-- 查看连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';
-- 查看内存使用
SHOW STATUS LIKE 'Memory%';
```
## 3. 硬件优化
1. **CPU**：多核处理器，建议 8 核以上
2. **内存**：建议 16GB 以上，InnoDB 缓冲池占用 70-80%
3. **磁盘**：使用 SSD，配置 RAID 10
4. **网络**：千兆以上网络带宽
## 4. 表结构优化
```sql
-- 使用合适的数据类型
-- 不推荐：VARCHAR(255) 用于状态字段
-- 推荐：TINYINT 或 ENUM
-- 规范化与反规范化权衡
-- 读取频繁：适当反规范化
-- 写入频繁：保持规范化
-- 分区表
ALTER TABLE orders PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026)
);
```
## 5. 查询优化
```sql
-- 使用 EXPLAIN 分析查询
EXPLAIN SELECT * FROM orders WHERE user_id = 1;
-- 创建合适的索引
CREATE INDEX idx_user_id_created ON orders(user_id, created_at);
-- 避免全表扫描
SELECT * FROM large_table WHERE indexed_column = 'value';
-- 使用批量操作
INSERT INTO users (name, email) VALUES
('John', 'john@example.com'),
('Jane', 'jane@example.com');
```
## 6. 监控和诊断
```sql
-- 查看慢查询
SELECT * FROM mysql.slow_log
ORDER BY query_time DESC LIMIT 10;
-- 查看表状态
SHOW TABLE STATUS FROM database_name;
-- 查看索引使用情况
SELECT * FROM information_schema.statistics
WHERE table_schema = 'database_name';
-- 查看进程列表
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
```
## 7. 性能测试工具
```bash
# 使用 sysbench 进行性能测试
sysbench oltp_read_write \
--mysql-host=localhost \
--mysql-port=3306 \
--mysql-user=root \
--mysql-password=password \
--mysql-db=test \
--tables=10 \
--table-size=100000 \
--threads=16 \
--time=300 \
--report-interval=10 \
run
```
## 8. 定期维护
```sql
-- 分析表
ANALYZE TABLE table_name;
-- 优化表
OPTIMIZE TABLE table_name;
-- 检查表
CHECK TABLE table_name;
-- 修复表
REPAIR TABLE table_name;
```
通过系统性的性能调优，可以显著提升 MariaDB 的整体性能和稳定性。服务端2月21日 15:51
MariaDB 如何进行安全配置？有哪些安全最佳实践？MariaDB 的安全配置是保护数据库安全的重要环节，以下是主要的安全配置措施：
## 1. 用户权限管理
```sql
-- 创建用户并设置密码
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password';
-- 授予最小必要权限
GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'app_user'@'localhost';
-- 撤销权限
REVOKE DELETE ON database_name.* FROM 'app_user'@'localhost';
-- 删除用户
DROP USER 'app_user'@'localhost';
-- 查看用户权限
SHOW GRANTS FOR 'app_user'@'localhost';
-- 刷新权限
FLUSH PRIVILEGES;
```
## 2. 配置文件安全
```ini
# my.cnf 安全配置
# 禁止远程 root 登录
skip-networking
# 或
bind-address = 127.0.0.1
# 禁用本地文件加载
local-infile = 0
# 限制最大连接数
max_connections = 100
# 启用 SSL
require-secure-transport = ON
ssl-ca = /path/to/ca-cert.pem
ssl-cert = /path/to/server-cert.pem
ssl-key = /path/to/server-key.pem
# 设置默认认证插件
default-authentication-plugin = mysql_native_password
```
## 3. 密码策略
```sql
-- 安装密码验证插件
INSTALL PLUGIN simple_password_check SONAME 'simple_password_check.so';
-- 配置密码策略
SET GLOBAL simple_password_check_minimal_length = 12;
SET GLOBAL simple_password_check_minimal_digit_count = 2;
SET GLOBAL simple_password_check_minimal_special_char_count = 1;
SET GLOBAL simple_password_check_minimal_uppercase_char_count = 1;
-- 强制密码过期
ALTER USER 'app_user'@'localhost' PASSWORD EXPIRE;
ALTER USER 'app_user'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
```
## 4. 网络安全
```bash
# 配置防火墙
# 只允许特定 IP 访问
iptables -A INPUT -p tcp -s 192.168.1.0/24 --dport 3306 -j ACCEPT
iptables -A INPUT -p tcp --dport 3306 -j DROP
# 使用 SSH 隧道
ssh -L 3306:localhost:3306 user@remote_server
```
## 5. 数据加密
```sql
-- 启用 InnoDB 表加密
-- my.cnf 配置
innodb_encrypt_tables = ON
innodb_encrypt_log = ON
innodb_encryption_threads = 4
innodb_encryption_rotate_key_age = 1
-- 创建加密表
CREATE TABLE sensitive_data (
id INT PRIMARY KEY,
data VARCHAR(255),
ENCRYPTION='Y'
) ENGINE=InnoDB ENCRYPTED=YES;
-- 使用加密函数
SELECT AES_ENCRYPT('sensitive_data', 'encryption_key');
SELECT AES_DECRYPT(encrypted_data, 'encryption_key');
```
## 6. 审计日志
```sql
-- 启用审计日志
-- my.cnf 配置
plugin_load_add = server_audit
server_audit_events = CONNECT,QUERY,TABLE
server_audit_logging = ON
server_audit_file_path = /var/log/mariadb/audit.log
server_audit_file_rotate_size = 100M
server_audit_file_rotations = 9
-- 查看审计日志
SELECT * FROM information_schema.server_audit;
```
## 7. 定期安全检查
```sql
-- 查看所有用户
SELECT user, host FROM mysql.user;
-- 查看空密码用户
SELECT user, host FROM mysql.user WHERE authentication_string = '';
-- 查看具有所有权限的用户
SELECT user, host FROM mysql.user WHERE Grant_priv = 'Y';
-- 查看匿名用户
SELECT user, host FROM mysql.user WHERE user = '';
```
## 8. 备份安全
```bash
# 加密备份文件
mysqldump -u root -p database_name | gzip | openssl enc -aes-256-cbc -salt -out backup.sql.gz.enc
# 解密备份文件
openssl enc -d -aes-256-cbc -in backup.sql.gz.enc | gunzip | mysql -u root -p database_name
```
## 9. 安全最佳实践
1. **最小权限原则**：只授予必要的权限
2. **定期更新**：及时安装安全补丁
3. **强密码策略**：使用复杂密码并定期更换
4. **网络隔离**：限制数据库的网络访问
5. **加密传输**：使用 SSL/TLS 加密连接
6. **审计监控**：启用审计日志并定期审查
7. **备份保护**：加密备份文件并安全存储
8. **定期检查**：定期进行安全审计和漏洞扫描
通过以上安全配置措施，可以显著提升 MariaDB 的安全性，保护数据免受未授权访问和攻击。服务端2月21日 15:51
MariaDB 如何进行备份和恢复？有哪些备份策略和工具？MariaDB 的备份与恢复是保障数据安全的重要环节，以下是主要的备份和恢复方法：
## 1. 逻辑备份（mysqldump）
**全量备份**：
```bash
# 备份所有数据库
mysqldump -u root -p --all-databases > all_databases.sql
# 备份指定数据库
mysqldump -u root -p database_name > database_name.sql
# 备份指定表
mysqldump -u root -p database_name table_name > table_name.sql
# 备份并压缩
mysqldump -u root -p database_name | gzip > database_name.sql.gz
```
**增量备份**：
```bash
# 启用二进制日志
# my.cnf 配置
log-bin = mysql-bin
binlog-format = ROW
# 备份二进制日志
mysqlbinlog mysql-bin.000001 > binlog_backup.sql
```
**恢复数据**：
```bash
# 恢复完整备份
mysql -u root -p < all_databases.sql
# 恢复指定数据库
mysql -u root -p database_name < database_name.sql
# 恢复压缩备份
gunzip < database_name.sql.gz | mysql -u root -p database_name
# 应用二进制日志
mysqlbinlog mysql-bin.000001 | mysql -u root -p
```
## 2. 物理备份（Mariabackup）
**全量备份**：
```bash
# 创建备份
mariabackup --backup --target-dir=/backup/full \
--user=root --password=password
# 准备备份
mariabackup --prepare --target-dir=/backup/full
# 恢复备份
mariabackup --copy-back --target-dir=/backup/full
```
**增量备份**：
```bash
# 创建全量备份
mariabackup --backup --target-dir=/backup/full \
--user=root --password=password
# 创建增量备份
mariabackup --backup --target-dir=/backup/inc1 \
--incremental-basedir=/backup/full --user=root --password=password
# 准备备份
mariabackup --prepare --target-dir=/backup/full
mariabackup --prepare --target-dir=/backup/full \
--incremental-dir=/backup/inc1
```
## 3. 快照备份
```bash
# 使用 LVM 快照
lvcreate -L 10G -s -n mysql_snapshot /dev/vg0/mysql
mount /dev/vg0/mysql_snapshot /mnt/backup
rsync -av /mnt/backup/ /backup/mysql/
umount /mnt/backup
lvremove /dev/vg0/mysql_snapshot
```
## 4. 自动化备份脚本
```bash
#!/bin/bash
# backup.sh
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backup/mariadb"
MYSQL_USER="root"
MYSQL_PASSWORD="password"
# 创建备份目录
mkdir -p $BACKUP_DIR
# 全量备份
mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD --all-databases \
--single-transaction --quick --lock-tables=false \
| gzip > $BACKUP_DIR/all_$DATE.sql.gz
# 保留最近7天的备份
find $BACKUP_DIR -name "all_*.sql.gz" -mtime +7 -delete
echo "Backup completed: all_$DATE.sql.gz"
```
## 5. 备份策略建议
1. **全量备份**：每天凌晨执行
2. **增量备份**：每小时执行
3. **二进制日志**：实时保留
4. **异地备份**：定期同步到远程服务器
5. **备份验证**：定期测试恢复流程
## 6. 恢复注意事项
1. 恢复前先停止 MariaDB 服务
2. 确保有足够的磁盘空间
3. 恢复后验证数据完整性
4. 记录恢复过程和时间点
5. 在测试环境先验证恢复流程
通过合理的备份策略和恢复流程，可以最大程度保障 MariaDB 数据的安全性和可靠性。服务端2月21日 15:51
MariaDB 的分区表有哪些类型？如何创建和管理分区表？MariaDB 的分区表（Partitioning）是将大表分割成更小、更易管理的部分的技术，可以显著提升查询性能和管理效率。
## 1. 分区类型
### RANGE 分区
```sql
-- 按日期范围分区
CREATE TABLE orders (
id INT PRIMARY KEY,
order_date DATE,
customer_id INT,
amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
-- 按数值范围分区
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50),
age INT
) PARTITION BY RANGE (age) (
PARTITION p0 VALUES LESS THAN (18),
PARTITION p1 VALUES LESS THAN (30),
PARTITION p2 VALUES LESS THAN (50),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
```
### LIST 分区
```sql
-- 按离散值列表分区
CREATE TABLE orders (
id INT PRIMARY KEY,
order_date DATE,
region VARCHAR(20),
amount DECIMAL(10,2)
) PARTITION BY LIST COLUMNS(region) (
PARTITION p_east VALUES IN ('New York', 'Boston', 'Philadelphia'),
PARTITION p_west VALUES IN ('Los Angeles', 'San Francisco', 'Seattle'),
PARTITION p_midwest VALUES IN ('Chicago', 'Detroit', 'Cleveland'),
PARTITION p_south VALUES IN ('Atlanta', 'Miami', 'Dallas')
);
```
### HASH 分区
```sql
-- 哈希分区（均匀分布数据）
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
category_id INT,
price DECIMAL(10,2)
) PARTITION BY HASH(id) PARTITIONS 4;
-- 线性哈希分区（更快但分布可能不均匀）
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
amount DECIMAL(10,2)
) PARTITION BY LINEAR HASH(customer_id) PARTITIONS 8;
```
### KEY 分区
```sql
-- KEY 分区（类似 HASH，使用主键或唯一键）
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100)
) PARTITION BY KEY(id) PARTITIONS 4;
```
## 2. 分区管理
### 添加分区
```sql
-- RANGE 分区添加新分区
ALTER TABLE orders ADD PARTITION (
PARTITION p2025 VALUES LESS THAN (2026)
);
-- LIST 分区添加新分区
ALTER TABLE orders ADD PARTITION (
PARTITION p_other VALUES IN ('Other', 'Unknown')
);
-- HASH/KEY 分区增加分区数量
ALTER TABLE products REORGANIZE PARTITION (
PARTITION p0, PARTITION p1, PARTITION p2, PARTITION p3
) INTO (
PARTITION p0, PARTITION p1, PARTITION p2,
PARTITION p3, PARTITION p4, PARTITION p5
);
```
### 删除分区
```sql
-- 删除分区（同时删除数据）
ALTER TABLE orders DROP PARTITION p2022;
-- 删除所有分区（转换为普通表）
ALTER TABLE orders REMOVE PARTITIONING;
```
### 合并分区
```sql
-- 合并 RANGE 分区
ALTER TABLE orders REORGANIZE PARTITION p2022, p2023 INTO (
PARTITION p2022_2023 VALUES LESS THAN (2024)
);
```
## 3. 分区查询
```sql
-- 查看分区信息
SELECT
PARTITION_NAME,
PARTITION_METHOD,
PARTITION_EXPRESSION,
PARTITION_DESCRIPTION,
TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = 'orders';
-- 查询特定分区
SELECT * FROM orders PARTITION (p2023);
-- 使用分区裁剪优化查询
SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';
```
## 4. 分区维护
```sql
-- 检查分区
ALTER TABLE orders CHECK PARTITION p2023;
-- 优化分区
ALTER TABLE orders OPTIMIZE PARTITION p2023;
-- 分析分区
ALTER TABLE orders ANALYZE PARTITION p2023;
-- 修复分区
ALTER TABLE orders REPAIR PARTITION p2023;
```
## 5. 分区索引
```sql
-- 创建本地索引（每个分区独立索引）
CREATE INDEX idx_customer_id ON orders(customer_id);
-- 创建全局索引（MariaDB 10.3+）
CREATE UNIQUE INDEX idx_global ON orders(id);
```
## 6. 分区使用场景
### 时间序列数据
```sql
CREATE TABLE logs (
id BIGINT PRIMARY KEY,
log_time TIMESTAMP,
message TEXT,
level VARCHAR(10)
) PARTITION BY RANGE (UNIX_TIMESTAMP(log_time)) (
PARTITION p2024_q1 VALUES LESS THAN (UNIX_TIMESTAMP('2024-04-01')),
PARTITION p2024_q2 VALUES LESS THAN (UNIX_TIMESTAMP('2024-07-01')),
PARTITION p2024_q3 VALUES LESS THAN (UNIX_TIMESTAMP('2024-10-01')),
PARTITION p2024_q4 VALUES LESS THAN (UNIX_TIMESTAMP('2025-01-01'))
);
```
### 大表归档
```sql
-- 定期归档旧数据
ALTER TABLE orders DROP PARTITION p2022;
```
## 7. 分区注意事项
1. **分区键选择**：选择查询中常用的列作为分区键
2. **分区数量**：不宜过多，建议 10-100 个分区
3. **主键约束**：主键必须包含分区键
4. **唯一索引**：唯一索引必须包含分区键
5. **数据分布**：确保数据在各分区间均匀分布
6. **维护成本**：分区表需要额外的维护操作
分区表是处理大数据量的有效手段，合理使用可以显著提升查询性能和管理效率。服务端2月21日 15:51
MariaDB 和 MySQL 有什么区别？MariaDB 是一个开源的关系型数据库管理系统，由 MySQL 的原始开发者创建，作为 MySQL 的直接替代品。它保持了与 MySQL 的高度兼容性，同时提供了更多的存储引擎、性能优化和新功能。
主要区别包括：
1. **存储引擎**：MariaDB 提供了更多存储引擎选择，如 Aria、ColumnStore、Spider、RocksDB 等，而 MySQL 主要使用 InnoDB 和 MyISAM。
2. **性能优化**：MariaDB 在查询优化、索引处理、缓存机制等方面进行了改进，通常在相同硬件上性能优于 MySQL。
3. **功能特性**：
- MariaDB 支持窗口函数（MySQL 8.0+ 才支持）
- 更丰富的 JSON 功能支持
- 支持动态列（Dynamic Columns）
- 更好的复制和集群功能
4. **开源策略**：MariaDB 采用完全开源的 GPL 许可证，而 MySQL 在 Oracle 收购后采用双重许可证。
5. **更新频率**：MariaDB 发布新版本更频繁，包含更多创新功能。
6. **兼容性**：MariaDB 10.x 之前版本与 MySQL 5.x 高度兼容，但后续版本逐渐分化。
在实际应用中，选择 MariaDB 还是 MySQL 主要取决于具体需求、团队技术栈和对开源策略的偏好。