滚雪球学MySQL[8.3讲]：数据库中的JSON与全文检索详解：从数据存储到全文索引的高效使用

news/2024/10/4 6:57:20/

全文目录：

    • 前言
    • 8.3 JSON与全文检索
      • 1. JSON数据类型的使用
        • 1.1 JSON 数据类型概述
        • 1.2 JSON 数据的插入与查询
        • 1.3 JSON 常用函数与操作
        • 1.4 JSON使用的优缺点与性能考虑
      • 2. 全文索引与全文检索
      • 3. 拓展与实际应用
        • 3.1 JSON与全文检索的结合
        • 3.2 全文检索在生产环境中的应用
      • 4. 实战项目：在线博客系统中的全文检索
    • 小结
    • 下期预告：实战项目——在线博客系统

前言

在上一期中，我们探讨了事件调度，并讨论了如何通过自动调度器在数据库中高效执行定时任务。通过事件调度，管理员可以优化数据库的运行流程并减少人为干预的操作。然而，随着数据的多样化和复杂度的增加，传统的关系型数据库结构面临着更大的挑战。为了处理复杂的半结构化数据，数据库需要引入更灵活的解决方案，例如使用JSON数据类型。同时，在大量文本数据的应用场景中，快速检索大规模文本信息成为关键问题，全文索引提供了一种高效的解决方案。

本期内容将深入探讨数据库中的JSON数据类型及其使用，帮助您了解如何灵活存储和查询复杂的JSON数据。同时，我们将探讨全文检索的原理和应用，展示如何通过全文索引高效地进行大规模文本搜索，并结合实际案例展示其在生产环境中的应用。

最后，我们还将简要预告下期内容实战项目：在线博客系统，展示如何在实战项目中运用这些技术。

8.3 JSON与全文检索

1. JSON数据类型的使用

JSON（JavaScript Object Notation）作为一种轻量级的数据交换格式，因其简洁易读、灵活结构而被广泛用于现代Web应用和数据传输中。随着需求的变化，许多数据库（如MySQL、PostgreSQL等）都引入了对JSON数据类型的原生支持，使得存储和处理半结构化数据变得更加简单。

1.1 JSON 数据类型概述

在关系型数据库中，传统的数据结构通常要求字段类型固定，而JSON格式则允许以嵌套、灵活的方式存储对象和数组。这样，开发者可以根据实际需求动态调整数据结构，而不需要频繁更改表结构。例如，用户的偏好、产品的动态属性、复杂的配置信息等都可以通过JSON格式灵活存储。

在MySQL中，我们可以使用JSON类型定义字段，并通过标准的SQL查询对其进行操作。例如，以下示例展示了一个包含用户偏好数据的表：

CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(100),preferences JSON
);

在preferences字段中，我们可以存储类似以下的JSON数据：

json">{"theme": "dark","notifications": {"email": true,"sms": false},"language": "en"
}

这种灵活性使得开发者能够轻松处理复杂的嵌套数据结构。

1.2 JSON 数据的插入与查询

将JSON数据插入到数据库中非常简单，我们可以直接在SQL查询中嵌入JSON对象。例如：

INSERT INTO users (name, preferences)
VALUES ('Alice', '{"theme": "dark", "notifications": {"email": true, "sms": false}, "language": "en"}');

要查询JSON字段中的特定值，MySQL提供了强大的函数支持。例如，JSON_EXTRACT()函数允许提取JSON字段中的某个属性：

SELECT name, JSON_EXTRACT(preferences, '$.theme') AS theme
FROM users
WHERE JSON_EXTRACT(preferences, '$.language') = 'en';

此查询将提取用户偏好的语言为en的所有用户，并显示他们的主题设置。

1.3 JSON 常用函数与操作

为了支持JSON数据的操作，MySQL等数据库引入了多个函数用于处理JSON字段。以下是常用的一些操作：

  • JSON_SET()：用于更新JSON字段的值。例如，修改某用户的通知设置：

    UPDATE users
SET preferences = JSON_SET(preferences, '$.notifications.sms', true)
WHERE name = 'Alice';

  • JSON_ARRAY_APPEND()：用于向JSON数组添加元素。例如，向用户的兴趣列表中追加一个新的兴趣：

    UPDATE users
SET preferences = JSON_ARRAY_APPEND(preferences, '$.interests', 'reading')
WHERE name = 'Alice';

  • JSON_REMOVE()：用于删除JSON字段中的某个属性。例如，删除用户的语言设置：

    UPDATE users
SET preferences = JSON_REMOVE(preferences, '$.language')
WHERE name = 'Alice';

这些操作使得开发者可以在不改变数据库表结构的情况下，灵活管理嵌套的复杂数据。

1.4 JSON使用的优缺点与性能考虑

尽管JSON数据类型带来了极大的灵活性，但在使用时也需要谨慎对待。以下是一些最佳实践和注意事项：

  • 结构化与规范化：虽然JSON格式允许灵活存储，但在实际使用中，保持数据结构的一致性有助于提高查询和维护的效率。
  • 查询性能：在处理大规模JSON数据时，直接从JSON字段中提取数据可能导致查询性能下降。为提高性能，可以使用虚拟列（generated columns）或为JSON字段的常用属性建立索引。

2. 全文索引与全文检索

在处理大规模文本数据时，传统的查询方式往往难以满足复杂的文本搜索需求。为了支持高效的文本检索，许多数据库引入了全文索引，并提供了基于关键字的全文检索功能。

2.1 全文索引概述

全文索引是为文本字段设计的一种特殊索引，能够将文本分解为单词并创建倒排索引，从而支持快速的关键字搜索。与传统索引不同，全文索引关注的是文本中的单词出现频率和位置，而不是字段的精确匹配。

在MySQL中，可以为VARCHAR或TEXT类型的字段创建全文索引。例如，假设我们有一个存储博客文章的表：

CREATE TABLE posts (id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(255),content TEXT,FULLTEXT(title, content)
);

此时，MySQL会为title和content字段创建全文索引，以支持高效的全文搜索。

2.2 全文检索的使用

创建了全文索引后，MySQL允许我们使用MATCH和AGAINST关键字进行全文检索。MATCH指定要搜索的字段，AGAINST指定搜索的关键词：

SELECT * FROM posts
WHERE MATCH(title, content) AGAINST('database optimization' IN NATURAL LANGUAGE MODE);

此查询会返回所有包含关键词database optimization的博客文章。在自然语言模式下，MySQL会根据关键词的出现频率、重要性等因素对结果进行评分，返回最相关的结果。

2.3 全文检索模式

MySQL支持不同的全文检索模式，每种模式适用于不同的搜索场景。常见的模式包括：

  • 自然语言模式（Natural Language Mode）：适用于大多数普通文本搜索，系统根据文本分词和词频对搜索结果进行排序。

  • 布尔模式（Boolean Mode）：允许用户定义更复杂的搜索逻辑，可以使用操作符来控制查询行为。例如，使用+表示必须包含，-表示不能包含：

    SELECT * FROM posts
WHERE MATCH(title, content) AGAINST('+database -mysql' IN BOOLEAN MODE);

    这条查询会返回包含database但不包含mysql的文章。

  • 查询扩展模式（Query Expansion Mode）：在自然语言模式的基础上，通过扩展搜索范围，返回更多相关的搜索结果。这种模式适用于模糊搜索场景。

2.4 全文索引优化与性能调优

在处理大量文本数据时，全文索引的性能优化至关重要。以下是一些优化建议：

  • 限制索引字段：仅为需要检索的字段创建全文索引，避免不必要的性能消耗。
  • 调整分词规则：不同的语言和应用场景对分词的要求不同，MySQL允许开发者根据需求配置自定义的分词器，提升全文检索的精度。
  • 缩小搜索范围：尽量减少全文检索的文本量。例如，可以通过WHERE子句先过滤出符合条件的记录，再进行全文检索，从而提高查询效率。

3. 拓展与实际应用

3.1 JSON与全文检索的结合

在某些复杂的应用场景中，我们可能同时需要使用JSON数据存储复杂结构化信息，并结合全文检索实现高效的文本搜索。例如，在电商系统中，我们可以通过JSON存储产品的动态属性，并通过全文索引实现对产品描述的搜索。

这种结合应用可以大大提升系统的灵活性，同时支持复杂的搜索需求。以一个简单的电商产品搜索为例，我们可以将产品信息和评论存储在JSON

字段中：

CREATE TABLE products (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(255),details JSON,description TEXT,FULLTEXT(description)
);

接着，我们可以根据用户输入的关键词，在产品描述中进行全文检索，并结合产品的JSON属性筛选结果。

3.2 全文检索在生产环境中的应用

全文检索在内容管理系统（CMS）、电子商务平台、知识库和社交网络等应用中得到了广泛应用。在这些场景中，用户通常需要通过关键词快速找到相关的文章、产品或其他信息。通过合理配置全文索引和优化查询，我们可以确保全文检索的高效性，并显著提升用户体验。

例如，社交网络平台可能需要支持对用户发布内容的快速检索。在这种场景下，全文索引能够有效提高关键词匹配速度，确保用户能够快速找到与其搜索相关的内容。

4. 实战项目：在线博客系统中的全文检索

假设我们正在开发一个在线博客系统，用户可以通过关键词在博客文章中进行搜索。为此，我们可以为博客文章创建全文索引，并实现简单的搜索功能：

CREATE TABLE articles (id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(255),body TEXT,FULLTEXT(title, body)
);

用户在搜索框中输入关键词时，系统将执行如下查询，返回相关的文章：

SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('database optimization' IN NATURAL LANGUAGE MODE);

通过这种方式，系统可以根据用户输入的关键词快速检索博客文章，并根据相关性进行排序，提升搜索体验。

小结

本期内容我们详细探讨了JSON数据类型和全文检索的使用方法。通过使用JSON数据类型，开发者可以灵活地存储半结构化数据，而全文索引则提供了高效的文本搜索解决方案。结合实际应用场景，这两种技术可以帮助开发者构建功能强大、性能优越的数据库系统。

下期预告：实战项目——在线博客系统

在接下来的实战项目中，我们将结合前几期学习的数据库技术，构建一个完整的在线博客系统。该系统将涵盖用户管理、文章发布、评论系统和全文检索功能，帮助大家深入理解如何在实际项目中应用数据库的各类技术。敬请期待！


http://www.ppmy.cn/news/1533779.html

相关文章

Electron 安装以及搭建一个工程

Electron 安装以及搭建一个工程

安装Node.js 在使用Electron进行开发之前，需要安装 Node.js。 官方建议使用最新的LTS版本。 检查 Node.js 是否正确安装： # 查看node版本 node -v # 查看npm版本 npm -v注意 开发者需要在开发环境安装 Node.js 才能编写 Electron 项目，但是…
阅读更多...
QT系统学习篇（3）- Qt开发常用算法及控件原理

QT系统学习篇（3）- Qt开发常用算法及控件原理

一、Qt中 Qt框架中和模块提供一些算法和常用函数 比如 double d1-59.6,d292.5;double d3qAbs(d1);double d4qMax(d1,d2);qDebug()<<"d3"<<d3<<"d4"<<d4<<endl;二、Qt窗口及控件原理设计 理解Qt窗口的生成、属性 1、Qt窗口…
阅读更多...
Windows 环境搭建 CUDA 和 cuDNN 详细教程

Windows 环境搭建 CUDA 和 cuDNN 详细教程

CUDA CUDA&#xff08;Compute Unified Device Architecture&#xff09;是由NVIDIA公司推出的一个并行计算平台和编程模型&#xff0c;它允许开发者使用NVIDIA GPU进行通用计算&#xff08;即GPGPU&#xff09;&#xff0c;从而加速各种计算密集型任务。CUDA提供了一套基于C/C…
阅读更多...
Linux·进程概念(下)

Linux·进程概念(下)

1. 进程优先级 优先级就是获得某种资源的先后顺序&#xff0c;因为CPU资源是有限的&#xff0c;因此各个进程之间要去争取CPU的资源。 那么针对Linux操作系统下的PCB中&#xff0c;也就是task_struct结构体中&#xff0c;使用了int类型的变量记录了每个进程的优先级属性&#x…
阅读更多...
使用 lstm + crf 实现NER

使用 lstm + crf 实现NER

条件随机场CRF 前言 CRF是给定一组输入随机变量条件下另一组输出随机变量的条件概率分布的模型。特点&#xff1a;假设输出随机变量构成马尔卡夫随机场。CRF可以用于不同的预测问题。但是主要讨论线性链条件随机场&#xff0c;这时问题变成了由输入序列对输出序列的判别模型&…
阅读更多...
[RabbitMQ] RabbitMQ介绍,安装与快速上手

[RabbitMQ] RabbitMQ介绍,安装与快速上手

&#x1f338;个人主页:https://blog.csdn.net/2301_80050796?spm1000.2115.3001.5343 &#x1f3f5;️热门专栏: &#x1f9ca; Java基本语法(97平均质量分)https://blog.csdn.net/2301_80050796/category_12615970.html?spm1001.2014.3001.5482 &#x1f355; Collection与…
阅读更多...
高斯消元 笔记

高斯消元 笔记

高斯消元 高斯消元&#xff1a;解线性方程组 有 n n n 个未知数&#xff1a; x 1 , x 2 , . . . , x n x_1,x_2,...,x_n x1​,x2​,...,xn​ a 1 1 x 1 1 a 1 2 x 1 2 . . . a 1 n x 1 n b 1 a_{1_1}x_{1_1}a_{1_2}x_{1_2}...a_{1_n}x_{1_n}b_1 a11​​x11​​a12​​x1…
阅读更多...
开源模型应用落地-qwen2.5-7b-instruct-LoRA微调-LLaMA-Factory-单机单卡-V100（十八）

开源模型应用落地-qwen2.5-7b-instruct-LoRA微调-LLaMA-Factory-单机单卡-V100（十八）

一、前言 本篇文章将使用LLaMA-Factory去高效微调（命令和界面方式）QWen2.5系列模型，通过阅读本文，您将能够更好地掌握这些关键技术，理解其中的关键技术要点，并应用于自己的项目中。 QWen2系列模型微调: 开源模型应用落地-qwen2-7b-instruct-LoRA微调-LLaMA-Factory-单机单…
阅读更多...
最新文章

Copyright @ 2022~2023