MySQL之创建高性能的索引(三)

news/2024/7/25 21:50:14/文章来源:https://blog.csdn.net/Cover_sky/article/details/139250003

创建高性能的索引

哈希索引

哈希索引(hash index)基于哈希表实现,只有精确匹配索引所有的列的查询才有效。对于每一行数据,存储引擎都会对所有的索引列计算一个哈希码(hash code).哈希码是一个较小的值,并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中,同时哈希表中保存指向每个数据行的指针。在MySQL中,只有Memory引擎显式支持哈希索引。这也是Memory引擎表的默认索引类型,Memory引擎同时也支持B-Tree索引。值得一提的是,Memory引擎是支持非唯一哈希索引的,这在数据库世界里面是比较与众不同的。如果多个列的哈希值相同,索引会以链表的方式存放多个记录指针到同一个哈希条目中.下面来看一个例子

mysql> CREATE TABLE testhash(-> fname VARCHAR(50) NOT NULL,-> lname VARCHAR(50) NOT NULL,-> KEY USING HASH(fname)-> ) ENGINE=MEMORY;

表种包含如下数据:

mysql> SELECT * FROM testhash;
+-------+-----------+
| fname | lname     |
+-------+-----------+
| Arjen | Lentz     |
| Baron | Schwartz  |
| Peter | Zaitsev   |
| Vadim | Tkachenko |
+-------+-----------+

假设索引使用假想的哈希函数f(),它返回下面的值(都是示例数据,非真实数据):

f('Arjen')=2323
f('Baron')=7437
f('Peter')=8784
f('Vadim')=2458

则哈希索引的数据结构如下:

(Slot)(Value)
2323          指向第1行的指针
2458          指向第4行的指针
7437          指向第2行的指针
8784          指向第3行的指针

注意每隔槽的编号是顺序的,但是数据行不是。先在来看如下查询

mysql> SELECT lname FROM testhash WHERE fname ='Peter';

MySQL先计算’Peter’的哈希值,并使用该值寻找对应的记录指针。因为f(‘Peter’)=8784,所以MySQL在索引中查找8784,可以找到指向第3行的指针,最后异步是比较第三行的值是否为’Peter’,以确保就是要查找的行。因为索引自身只需要存储对应的哈希值,所以索引的结构十分紧凑,这也让哈希索引查找的速度非常快。然而,哈希索引也有它的限制:

  • 1.哈希索引只包含哈希值和行指针,而不存储字段值,所以不能使用索引中的值来避免读取行。不过,访问内存中的行的速度很快,所以大部分情况下这一点对性能的影响并不明显
  • 2.哈希索引数据并不是按照索引值顺序存储的,所以也就无法用于排序
  • 3.哈希索引也不支持部分索引列匹配查找,因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如,在数据列(A,B)上建立哈希索引,如果查询只有数据列A,则无法使用该索引
  • 4.哈希索引只支持等值比较查询,包括=、IN()、<=>(注意<>和<=>是不同的操作)。也不支持任何范围查询,例如WHERE price >100
  • 5.访问哈希索引的数据非常快,除非有很多哈希冲突(不同的索引列值却有相同的哈希值)。当出现哈希冲突的时候,存储引擎必须遍历链表中所有的行指针,主键进行比较,直到找出所有符合条件的行
  • 6.如果哈希冲突很多的化,一些索引维护操作的代价也会很高。例如,如果在某个选择性很低(哈希冲突很多)的列上建立哈希索引,那么当从表中删除一行时,存储引擎需要遍历对哈希值的链表中的每一行,找到并删除对应行的引用,冲突越多,代价越大。

因为这些限制,哈希索引只适用于某些特定的场合。而一旦适合哈希索引,则它带来的的性能提升将非常显著。举个例子,在数据仓库应用中有一种经典的"星型"schema。需要关联很多表,哈希索引就非常适合查找表的需求。除了Memory引擎外,NDB集群引擎也支持唯一哈希索引,且在NDB集群引擎中作用非常特殊。
InnoDB引擎有一个特殊的功能叫作"自适应哈希索引(Adaptive Hash Index)"。当InnoDB注意到某些索引值被使用得非常频繁时,它会在内存中基于B-Tree之上再创建一个哈希索引,这样就让B-Tree索引也具有哈希索引的一些优点,比如快速的哈希查找。这是一个完全自动的、内部的行为,用户无法控制或者配置,不过有必要,完全可以关闭该功能。

创建自定义哈希索引

如果存储引擎不支持哈希索引,则可以模拟像InnoDB一样创建哈希索引,这可以享受一些哈希索引的便利,例如只需要很小的索引就可以为超长的键创建索引。思路很简单:在B-Tree基础上创建一个伪哈希索引。这和真正的哈希索引不是一回事,因为还是适用B-Tree进行查找,但是它适用哈希值而不是键本身进行索引查找。你需要做的事就是在查询的WHERE子句中手动指定适用哈希函数。下面是一个示例,例如需要存储大量的URL,并需要根据URL进行搜索查找。如果适用B-Tree来存储URL,存储的内容就会很大,因为URL本身都很长。正常情况下会有如下查询:

mysql> SELECT id FROM url WHERE url = "http://www.mysql.com";

若删除原来URL列上的索引,而新增一个被索引的url_crc列,适用CRC32做哈希,就可以适用下面的方式查询:

mysql>SELECT  id FROM url WHERE url="http://www.mysql.com" AND url_crc=CRC32("http://www.mysql.com");

这样做的性能会非常高,因为MySQL优化器会适用这个选择性很高而提及很小的基于url_crc列的索引来完成查找(在上面的案例中,索引值伪1560514994)即使有多个记录相同的索引值,查找仍然很快,只需要根据哈希值做快速的整数比较就能找到索引条目,然后一一比较返回对应的行。另外一种方式就是对完整的URL字符串做索引,那样会非常慢。

mysql> SELECT CRC32("http://www.mysql.com");
+-------------------------------+
| CRC32("http://www.mysql.com") |
+-------------------------------+
|                    1560514994 |
+-------------------------------+
1 row in set (0.10 sec)

这样实现的缺陷事需要维护哈希值。可以手动维护,也可以适用触发器实现。下面的案例演示了触发器如何在插入和更新时维护url_crc列,首先创建如下表:

CREATE TABLE pseudohash (
id INT UNSIGNED NOT NULL auto_increment,
url VARCHAR ( 255 ) NOT NULL,
url_crc INT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY ( id ));

然后创建触发器。先临时修改一下语句分隔符,这样就可以在触发器定义中适用分毫:

DELIMITER //
CREATE TRIGGER pseudohash_crc_ins BEFORE INSERT ON pseudohash FOR EACH ROW BEGIN SET NEW.url_crc=crc32(NEW.url);
END;
//CREATE TRIGGER pseudohash_crc_upd BEFORE UPDATE ON pseudohash FOR EACH ROW BEGIN SET NEW.url_crc=crc32(NEW.url);
END;
//DELIMITER;

剩下工作就是验证一下触发器如何维护哈希索引:

mysql>INSERT INTO pseudohash (url) VALUES ('http://www.mysql.com');
mysql> SELECT * FROM pseudohash;
+----+----------------------+------------+
| id | url                  | url_crc    |
+----+----------------------+------------+
|  1 | http://www.mysql.com | 1560514994 |
+----+----------------------+------------+
1 row in set (0.08 sec)
mysql> UPDATE pseudohash SET url = 'http://www.mysql.com/' WHERE id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> SELECT * FROM pseudohash;
+----+-----------------------+------------+
| id | url                   | url_crc    |
+----+-----------------------+------------+
|  1 | http://www.mysql.com/ | 1558250469 |
+----+-----------------------+------------+
1 row in set (0.07 sec)

SHA1()和MD5()

如果采用这种方式,记住不要适用SHA1()和MD5(0作为哈希函数。因为这两个函数计算出来的哈希值是非常长的字符串,会浪费大量空间,比较时也会更慢。SHA1()和MD5()是强加密函数,设计目标是最大限度消除冲突,但这里并需要这样高的要求。简单哈希函数的冲突在一个可以接受的范围,同时又能够提供好的性能。如果数据表非常大,CRC32()会出现大量的哈希冲突,则可以考虑自己实现一个简单的64位哈希函数。这个自定义函数要返回整数,而不是字符串。一个简单的办法可以适用MD5()函数返回值的一部分来作为自定义哈希函数。这可能比自己写一个哈希算法的性能要差,不过这样实现最简单:

mysql> SELECT CONV(RIGHT(MD5('http://www.mysql.com/'), 16), 16, 10) AS HASH64;
+---------------------+
| HASH64              |
+---------------------+
| 9761173720318281581 |
+---------------------+

处理哈希冲突,当适用哈希索引进行查询的时候,必须在WHERE子句中包含常量值:

mysql> SELECT id FROM url WHERE url_crc=CRC32('http://mysql.com') AND url='http://www.mysql.com';

一旦出现哈希冲突,另一个字符串的哈希值也恰是1560514994,则下面的查询时无法正确工作的。

mysql> SELECT id FROM url WHERE url_crc=CRC32('http://www.mysql.com');

因为所谓的"生日悖论",出现哈希冲突的概率的增长速度可能比想象的要快得多。CRC32()返回的是32位的整数,当索引有93 000条时出现的冲突的概率是1%。例如将/usr/share/dict/words中的词导入数据表并进行CRC32()计算,最后会有98 569行,这就已经出现一次哈希冲突了,冲突让下面的插叙年返回了多条记录

mysql>SELECT word,crc FROM words WHERE crc=CRC32('gnu');

正确的刑法应该如下:

mysql>SELECT word, crc FROM words WHERE crc=CRC32('gnu') AND word = 'gnu';

要避免冲突问题,必须在WHERE条件中带入哈希值和对应列值。如果不是想查询具体值,例如只是统计记录数(不精确的),则可以不带如列值,直接适用CRC32()的哈希值查询即可。还可以适用如FNV64()函数作为哈希函数,这是移植自Percona Server的函数,可以以插件的方式在任何MySQL版本中适用,哈希值64位,速度快,且冲突比CRC32()要少很多

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

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

相关文章

链表经典题目—相交链表和链表倒数第k个节点

&#x1f389;&#x1f389;&#x1f389;欢迎莅临我的博客空间&#xff0c;我是池央&#xff0c;一个对C和数据结构怀有无限热忱的探索者。&#x1f64c; &#x1f338;&#x1f338;&#x1f338;这里是我分享C/C编程、数据结构应用的乐园✨ &#x1f388;&#x1f388;&…

Java中Spring MVC 来如何接收表单数据

目录 一、Java语言介绍 二、Spring MVC 框架介绍 三、什么是表单 四、Spring MVC 来如何接收表单数据 一、Java语言介绍 Java是一种广泛使用的面向对象的编程语言&#xff0c;由Sun Microsystems公司的James Gosling等人开发。它最初于1995年发布&#xff0c;被设计为具有…

字符串和字符串函数(1)

前言&#xff1a; 字符串在C语言中比较特别&#xff0c;没有单另的字符串类型&#xff0c;想要初始化字符串必须用字符变量的数组初始化&#xff0c;但是在C语言标准库函数中提供了大量能对字符串进行修改的函数&#xff0c;比如说可以实现字符串的的拷贝&#xff0c;字符串的追…

【C++初阶】—— 类和对象 (上)

&#x1f4dd;个人主页&#x1f339;&#xff1a;EterNity_TiMe_ ⏩收录专栏⏪&#xff1a;C “ 登神长阶 ” &#x1f339;&#x1f339;期待您的关注 &#x1f339;&#x1f339; 类和对象 1. 初步认识C2. 类的引入3. 类的定义声明和定义全部放在类体中声明和定义分开存放 4.…

4月冰箱行业线上市场销售数据分析

家电行业内卷现象严重&#xff0c;企业之间在价格、营销和服务上进行激烈竞争&#xff0c;这种竞争态势可能导致整体家电市场需求承压&#xff0c;这需要品牌方做好一定的心理准备。尽管如此&#xff0c;消费者对于冰箱的需求还是以更新换代为主导&#xff0c;行业后市仍有较大…

深圳比创达电子EMC|EMC电磁兼容性行业:挑战与机遇并存

随着电子技术的迅猛发展&#xff0c;电磁兼容性&#xff08;EMC&#xff09;已成为各行各业不可忽视的关键问题。EMC是指设备或系统在其电磁环境中能正常工作且不对该环境中任何事物构成不能承受的电磁骚扰的能力。 一、EMC电磁兼容性行业的现状 EMC电磁兼容性行业作为电子技…

非关系型数据库NOSQL

文章目录 1. NOSQL 概述2. 相关理论基础2.1 一致性2.2 分区2.3 存储分布2.4 查询模型 3. NOSQL 数据库的种类3.1 文档存储3.2 键值存储3.3 列存储3.3 图存储 4. NOSQL 应用案例和新技术4.1 HBase 数据库4.2 云数据库 GeminiDB 非关系型的数据库 NOSQL (Not Only SQL)是对不同于…

C语言动态顺序表结构的创建、初始化结构、尾插、尾删、头插、头删、指定位置插入、指定位置删除、找指定数值下标等的介绍

文章目录 前言一、 结构创建二、 初始化结构三、 打印动态顺序表四、 销毁动态顺序表五、 尾插六、尾删七、 头插八、 头删九、指定位置插入十、指定位置删除十一、找指定数值下标总结 前言 C语言动态顺序表结构的创建、初始化结构、尾插、尾删、头插、头删、指定位置插入、指…

芯片设计公司外协ERP数字化运营:科技与管理的融合

随着信息技术的快速发展&#xff0c;ERP(企业资源计划)系统已经成为现代企业管理不可或缺的一部分。在芯片设计行业&#xff0c;由于产品的复杂性、技术的高要求以及市场的快速变化&#xff0c;外协ERP数字化运营显得尤为重要。 芯片设计公司的外协ERP数字化运营&#xff0c;主…

Docker CIG使用

Docker CIG是什么 CIG为&#xff1a;CAdvisor监控收集、InfluxDB存储数据、Granfana图表展示 这个组合是一个常见的监控 Docker 容器的解决方案,它包括以下三个组件: cAdvisor (Container Advisor): cAdvisor 是一个开源的容器资源监控和性能分析工具。它能够收集有关正在运行的…

云原生Kubernetes: K8S 1.26版本 部署KubeSphere

目录 一、实验 1.环境 2.K8S 1.26版本部署HELM 3.K8S 1.26版本 部署KubeSphere 4.安装KubeSphere DevOps 二、问题 1.如何安装Zadig 2.扩展插件Zadig安装失败 3.calico 如何实现不同node通信 4.如何清除docker占用的磁盘空间 5.如何强制删除资源 6.namespace删除不…

Java进阶学习笔记26——包装类

包装类&#xff1a; 包装类就是把基本类型的数据包装成对象。 看下API文档&#xff1a; deprecated&#xff1a;极力反对、不赞成的意思。 marked for removal&#xff1a;标识为去除的意思。 自动装箱&#xff1a;基本数据类型可以自动转换成包装类。 自动拆箱&#xff1a;…

Ubuntu20.04安装VINS_Mono 和 VINS_Fusion

文章目录 一、问题描述二、依赖环境1. Eigen 安装2. glog 安装3. gflags 安装4. ceres 安装 三、VINS-Mono 安装1. git 下载并安装2. OpenCV 版本冲突3. 运行 四、VINS—Fusion 安装1. git 下载并安装2. OpenCV 版本冲突3. 运行 五、日常bug1. 动静态库链接冲突 一、问题描述 …

AIGC行业的发展前景与市场需求

简介&#xff1a;探讨当前时机是否适合进入AIGC行业&#xff0c;考虑行业发展阶段和市场需求。 方向一&#xff1a;行业前景 AIGC&#xff08;人工智能生成内容&#xff09;行业是近年来随着人工智能技术的快速发展而兴起的一个新兴领域&#xff0c;它涉及到使用人工智能技术来…

11.Redis之zset类型

1.zset类型基本介绍 有序描述的是&#xff1a;升序/降序 Set 集合 1.唯一 2. 无序 孙行者,行者孙, 者行孙 >同一只猴~~ List有序的 孙行者,行者孙, 者行孙 >不同的猴~~ zset 中的 member 仍然要求是唯一的!!(score 则可以重复) 排序的规则是啥? 给 zset 中的 member 同…

通过域名接口申请免费的ssl多域名证书

来此加密已顺利接入阿里云的域名接口&#xff0c;用户只需一键调用&#xff0c;便可轻松完成域名验证&#xff0c;从而更高效地申请证书。接下来&#xff0c;让我们详细解读一下整个操作过程。 来此加密官网 免费申请SSL证书 免费SSL多域名证书&#xff0c;泛域名证书。 首先&a…

Jmeter元件及基本作用域

&#x1f680;从今天开始学习性能测试工具——Jmeter&#xff0c;小梦也是先学习了下Jmeter的元件概念以及其基本的作用域&#xff0c;整理了下笔记&#xff0c;希望不管是从事开发领域还是测试领域的朋友们&#xff0c;我们一起学习下Jmeter工具&#xff0c;提升工作中的技能&…

leetcode栈和队列的相关题、有效的括号、用队列实现栈、用栈实现队列、设计循环队列等介绍

文章目录 前言一、有效的括号二、用队列实现栈三、 用栈实现队列四、设计循环队列总结 前言 leetcode栈和队列的相关题、有效的括号、用队列实现栈、用栈实现队列、设计循环队列等介绍 一、有效的括号 leetcode有效的括号 // 动态增长的栈 typedef char STDataType; typedef…

第十七届全国大学生信息安全竞赛创新实践能力赛初赛部分复现

Misc 神秘文件 1.根据提示信息&#xff0c;均需要从ppt中提取信息 2.在ppt的属性中发现一串密文和key&#xff0c;解密之后得到第一部分&#xff0c;根据提示Bifid chipher&#xff0c;为双歧密码解密&#xff0c;使用Bifid Cipher Decode解码 3.在第五张幻灯片&#xff0c;…

微服务实践k8sdapr开发部署调用

前置条件 安装docker与dapr: 手把手教你学Dapr - 3. 使用Dapr运行第一个.Net程序安装k8s dapr 自托管模式运行 新建一个webapi无权限项目 launchSettings.json中applicationUrl端口改成5001,如下: "applicationUrl": "http://localhost:5001" //Wea…