## 1. 创建包含大字段的表
CREATE TABLE fgedudb.fgedu_documents (
id INT PRIMARY KEY,
title VARCHAR(255),
content LONGTEXT,
attachment LONGBLOB,
created_at DATETIME
);

## 2. 创建大字段索引
— 创建前缀索引
CREATE INDEX idx_content_prefix ON fgedudb.fgedu_documents (content(100));

— 创建全文索引
CREATE FULLTEXT INDEX idx_content_fulltext ON fgedudb.fgedu_documents (content);

## 3. 插入大字段数据
— 插入文本数据
INSERT INTO fgedudb.fgedu_documents (id, title, content, created_at)
VALUES (1, ‘Document 1’, ‘This is a long document content…’, NOW());

— 插入二进制数据
INSERT INTO fgedudb.fgedu_documents (id, title, attachment, created_at)
VALUES (2, ‘Document 2’, LOAD_FILE(‘/tidb/data/attachment.pdf’), NOW());

## 4. 查询大字段数据
— 普通查询
SELECT id, title, content FROM fgedudb.fgedu_documents WHERE id = 1;

— 延迟加载查询
SELECT id, title FROM fgedudb.fgedu_documents WHERE id = 1;

— 部分读取查询
SELECT id, title, SUBSTRING(content, 1, 100) AS content_summary FROM fgedudb.fgedu_documents WHERE id = 1;

## 5. 更新大字段数据
UPDATE fgedudb.fgedu_documents SET content = ‘Updated content…’ WHERE id = 1;

## 6. 删除大字段数据
DELETE FROM fgedudb.fgedu_documents WHERE id = 1;

## 1. 配置TiKV存储参数
$ cat > tikv.toml << EOF [storage] enable-compression = true compression-algorithm = "lz4" compression-level = "default" [raftstore] sync-log = false EOF ## 2. 配置TiDB参数 $ cat > tidb.toml << EOF [performance] enable-tiflash-read = true [storage] enable-async-commit = true enable-pipelined-transaction = true EOF ## 3. 重启集群 $ tiup cluster restart fgedu-tidb-cluster ## 4. 验证配置 $ mysql -h 192.168.1.100 -P 4000 -u root -p SHOW VARIABLES LIKE 'tidb_enable_async_commit'; SHOW VARIABLES LIKE 'tidb_enable_pipelined_transaction';

## 1. 使用延迟加载
— 只查询非大字段列
SELECT id, title, created_at FROM fgedudb.fgedu_documents WHERE title LIKE ‘%document%’;

— 按需查询大字段
SELECT id, title, content FROM fgedudb.fgedu_documents WHERE id = 1;

## 2. 使用部分读取
— 读取大字段的前100个字符
SELECT id, title, SUBSTRING(content, 1, 100) AS content_summary FROM fgedudb.fgedu_documents;

— 读取大字段的指定范围
SELECT id, title, SUBSTRING(content, 100, 200) AS content_part FROM fgedudb.fgedu_documents WHERE id = 1;

## 3. 使用索引优化
— 使用前缀索引
SELECT id, title FROM fgedudb.fgedu_documents WHERE content LIKE ‘This%’;

— 使用全文索引
SELECT id, title FROM fgedudb.fgedu_documents WHERE MATCH(content) AGAINST(‘long document’);

## 4. 使用缓存
— 启用查询缓存
SET GLOBAL query_cache_size = 104857600;
SET GLOBAL query_cache_type = ON;

— 执行查询
SELECT id, title, content FROM fgedudb.fgedu_documents WHERE id = 1;

## 5. 批量查询
— 批量查询多个大字段
SELECT id, title, content FROM fgedudb.fgedu_documents WHERE id IN (1, 2, 3);

## 1. 批量写入
— 批量插入大字段数据
INSERT INTO fgedudb.fgedu_documents (id, title, content, created_at)
VALUES
(1, ‘Document 1’, ‘Content 1…’, NOW()),
(2, ‘Document 2’, ‘Content 2…’, NOW()),
(3, ‘Document 3’, ‘Content 3…’, NOW());

## 2. 异步写入
— 启用异步提交
SET SESSION tidb_enable_async_commit = ON;

— 执行写入操作
INSERT INTO fgedudb.fgedu_documents (id, title, content, created_at)
VALUES (4, ‘Document 4’, ‘Content 4…’, NOW());

## 3. 使用写入缓冲区
— 配置写入缓冲区
$ cat > tikv.toml << EOF [storage] write-buffer-size = "1GB" max-write-buffer-number = 2 EOF ## 4. 优化并发写入 -- 调整并发控制参数 $ cat > tidb.toml << EOF [txn] concurrent-commit = true EOF ## 5. 压缩写入 -- 启用压缩 $ cat > tikv.toml << EOF [storage] enable-compression = true compression-algorithm = "lz4" EOF

## 1. 使用Dumpling导出大字段数据
$ tiup dumpling -h 192.168.1.100 -P 4000 -u root -p password -B fgedudb -t 4 -o /tidb/backup

## 2. 使用Lightning导入大字段数据
$ tiup lightning –backend tidb –host 192.168.1.101 –port 4000 –user root –password password –db fgedudb –table fgedu_documents /tidb/backup

## 3. 使用BR备份恢复大字段数据
— 备份
$ tiup br backup full –pd 192.168.1.100:2379 –storage “local:///tidb/backup”

— 恢复
$ tiup br restore full –pd 192.168.1.101:2379 –storage “local:///tidb/backup”

## 4. 增量迁移大字段数据
— 使用CDC同步大字段数据
$ tiup cdc cli changefeed create –pd 192.168.1.100:2379 –sink-uri “tidb://root:password@192.168.1.101:4000/fgedudb” –changefeed-id “fgedu-cdc”

## 5. 迁移优化
— 调整迁移参数
$ tiup dumpling -h 192.168.1.100 -P 4000 -u root -p password -B fgedudb -t 8 -F 256MB -o /tidb/backup

— 调整Lightning参数
$ tiup lightning –backend tidb –host 192.168.1.101 –port 4000 –user root –password password –db fgedudb –table fgedu_documents –batch-size 1024 –region-concurrency 16 /tidb/backup

## 案例1：存储文档内容

### 步骤1：创建文档表
CREATE TABLE fgedudb.fgedu_documents (
id INT PRIMARY KEY,
title VARCHAR(255),
content LONGTEXT,
author VARCHAR(100),
created_at DATETIME
);

### 步骤2：插入文档数据
INSERT INTO fgedudb.fgedu_documents (id, title, content, author, created_at)
VALUES (1, ‘技术文档’, ‘这是一份详细的技术文档…’, ‘fgedu’, NOW());

### 步骤3：查询文档数据
— 普通查询
SELECT id, title, author FROM fgedudb.fgedu_documents;

— 查看文档内容
SELECT id, title, content FROM fgedudb.fgedu_documents WHERE id = 1;

## 案例2：存储图片数据

### 步骤1：创建图片表
CREATE TABLE fgedudb.fgedu_images (
id INT PRIMARY KEY,
name VARCHAR(255),
image LONGBLOB,
type VARCHAR(50),
size INT,
created_at DATETIME
);

### 步骤2：插入图片数据
INSERT INTO fgedudb.fgedu_images (id, name, image, type, size, created_at)
VALUES (1, ‘logo.png’, LOAD_FILE(‘/tidb/data/logo.png’), ‘image/png’, 102400, NOW());

### 步骤3：查询图片数据
— 普通查询
SELECT id, name, type, size FROM fgedudb.fgedu_images;

## 案例3：存储音频视频数据

### 步骤1：创建媒体表
CREATE TABLE fgedudb.fgedu_media (
id INT PRIMARY KEY,
name VARCHAR(255),
media LONGBLOB,
type VARCHAR(50),
size BIGINT,
created_at DATETIME
);

### 步骤2：插入媒体数据
INSERT INTO fgedudb.fgedu_media (id, name, media, type, size, created_at)
VALUES (1, ‘video.mp4’, LOAD_FILE(‘/tidb/data/video.mp4’), ‘video/mp4’, 104857600, NOW());

### 步骤3：查询媒体数据
— 普通查询
SELECT id, name, type, size FROM fgedudb.fgedu_media;

## 案例1：大字段查询优化

### 优化前
SELECT * FROM fgedudb.fgedu_documents WHERE title LIKE ‘%技术%’;

— 执行时间：5秒

### 优化后
— 创建索引
CREATE INDEX idx_title ON fgedudb.fgedu_documents (title);

— 只查询必要字段
SELECT id, title, author, created_at FROM fgedudb.fgedu_documents WHERE title LIKE ‘%技术%’;

— 执行时间：0.5秒

## 案例2：大字段写入优化

### 优化前
— 单条插入
INSERT INTO fgedudb.fgedu_documents (id, title, content, author, created_at)
VALUES (1, ‘Document 1’, ‘Content 1…’, ‘fgedu’, NOW());

— 执行时间：1秒

### 优化后
— 批量插入
INSERT INTO fgedudb.fgedu_documents (id, title, content, author, created_at)
VALUES
(1, ‘Document 1’, ‘Content 1…’, ‘fgedu’, NOW()),
(2, ‘Document 2’, ‘Content 2…’, ‘fgedu’, NOW()),
(3, ‘Document 3’, ‘Content 3…’, ‘fgedu’, NOW());

— 执行时间：1.5秒（3条数据）

## 案例3：大字段存储优化

### 优化前
— 未压缩存储
CREATE TABLE fgedudb.fgedu_documents (
id INT PRIMARY KEY,
content LONGTEXT
);

— 存储空间：10GB

### 优化后
— 启用压缩
$ cat > tikv.toml << EOF [storage] enable-compression = true compression-algorithm = "lz4" EOF -- 存储空间：3GB

## 案例1：使用Dumpling和Lightning迁移大字段数据

### 步骤1：导出数据
$ tiup dumpling -h 192.168.1.100 -P 4000 -u root -p password -B fgedudb -t 8 -F 256MB -o /tidb/backup

### 步骤2：导入数据
$ tiup lightning –backend tidb –host 192.168.1.101 –port 4000 –user root –password password –db fgedudb –batch-size 1024 –region-concurrency 16 /tidb/backup

## 案例2：使用BR备份恢复大字段数据

### 步骤1：备份数据
$ tiup br backup full –pd 192.168.1.100:2379 –storage “local:///tidb/backup”

### 步骤2：恢复数据
$ tiup br restore full –pd 192.168.1.101:2379 –storage “local:///tidb/backup”

## 案例3：使用CDC同步大字段数据

### 步骤1：创建CDC同步任务
$ tiup cdc cli changefeed create –pd 192.168.1.100:2379 –sink-uri “tidb://root:password@192.168.1.101:4000/fgedudb” –changefeed-id “fgedu-cdc”

### 步骤2：监控同步状态
$ tiup cdc cli changefeed list –pd 192.168.1.100:2379
$ tiup cdc cli changefeed status –pd 192.168.1.100:2379 –changefeed-id “fgedu-cdc”