把数据转换为在内存中Tree(树形结构)。_拜托,别再问我数据库性能优化了!

2023-10-19 04:50

本文主要是介绍把数据转换为在内存中Tree(树形结构)。_拜托,别再问我数据库性能优化了!,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

4723e7c69a9aed6710373e3fe4620e87.png

一、前言

在谈论数据库的时候,经常能够听到“QPS”、“TPS”等词汇,其实吞吐量不过是数据库性能的呈现,对于数据库性能的本质,我更倾向于将其描述为响应时间量,即完成某次查询所需要的时间量。

两者的关系不难察觉,响应时间量减少,意味着每秒查询次数增多,所以数据库的性能优化,就是如何降低响应时间量。

既然目标是减少响应时间,那就需要清楚时间究竟花在了哪里。

在《让SQL飞:InnoDB下的锁策略》一文中,我有画过MySQL的架构图,不过这次讨论的是数据库的性能优化,对于SQL的执行步骤,需要将之前的图做一些修改。

3353934024b94457a0b714ad9f9f256b.png

二、优化(数据结构)

物理设计是良好性能的基础,存储方式对于数据库性能的提高至关重要。

1. 更小的与合适的

更小的数据类型意味着占用更少的磁盘、内存和缓存。同时,InnoDB是行存储模式,再以页模式从磁盘中读取数据,当页大小固定(InnoDB默认16k),行数据更小,则每页能够包含更多的行数,所以每次IO访问的行数增加,IO次数减少,性能随之提高。

不仅如此,由于InnoDB下数据在文件中特定的存储形式,当执行写操作时,有可能会导致页分裂与页合并,行数据的增大更是促进了这种情况发生的几率。悲观更新的结果就是索引树上加写锁,锁竞争意味着阻塞,不论是自增主键下的行删除影响了聚簇索引导致索引树重建,还是普通索引下的写操作,都不可能避免这种情况的出现。(单击跳转至《InnoDB中的页合并与分裂》)

稍微有些经验的设计者在做年龄存储时,不可能将age字段设置为int类型,因为他知道更小的数据结构能带来哪些好处。

但更小的并不总是更好的,我们有时需要为将来做些考虑。timestamp比datetime能节省一半的存储空间(timestamp4字节,datetime8字节),但timestamp能表示的范围却要比datetime小很多。

你也许听过一些前辈们的告诫,在存储小数时避开double的选择,它可能计算出错误的结果,但真的需要decimal吗?除非的确是要求如此准确的精度,譬如做财务记录,很多时候 ,double或许是一种不错的选择,要清楚,cpu是直接支持浮点计算的,double的性能远在decimal之上。

做字符串存储时记得char与varchar空间使用的区别,但是别忘了碎片问题,额外开销往往成为性能的瓶颈。

2. 更少的列与冗余的列

微服务将不同的业务拆成不同的模块,在表结构设计中,也需要根据不同的业务将字段拆到不同的表中。

减少单个表中的列有利于表的维护,从内存中向InnoDB拷贝数据时,需要通过行缓冲将编码过的列转换成行数据结构,其代价是非常高昂的,回表操作同样如此,没错,资源是用来消耗的,但不是用来浪费的。

另外,当有更新操作时,更少的列意味着更小的锁粒度,没有人将tb_user与tb_wallet合成一张表,尽管有时我们需要联表来完成某些逻辑。

不过有时我们可能的确需要做一些反范式的设计,即增加一些冗余的列,来避免联表以及减少聚合操作,但利弊相依,代价就是需要更多的存储空间与额外的维护开销,尤其是在频繁更新的数据库表中更要谨慎地权衡两者是否值得这样做。

3. 缓存表与汇总表

缓存表和汇总表与冗余的列有异曲同工之妙,只不过实现在单独的表中。

缓存表中存储与其它的表逻辑冗余的数据,将大表中活跃的数据单独拿出来构建成一张小表,通过减少单个表的数据量来提高查询性能,即所谓的热数据分离,尤其是在大表中的某些小部分数据被频繁访问时更能体现其带来的好处。

汇总表中存储与其它的表逻辑不同的衍生数据,通过减少聚合次数来提高查询性能,同样,这些数据也经常被访问。

但两者都必须面对数据的同步问题,对于允许最终一致性的场景来说,建立缓存表或者汇总表,无疑是一个好主意,但是如果需要实时更新,那就得多花一些心思来斟酌一二了。

4. 拆分与扩展

表的拆分与缓存表的原理十分相似,将大表拆成多个小表来降低单表的数据量,从而提高查询性能。

表的拆分没有冗余数据,但设计者需要面对的问题却更加复杂。当拆分出来的小表中存储的数据量呈现爆发增长而不得不再次拆分时。即便不需要提供不间断服务,在做数据迁移时也是一件痛苦的事情,不论采用一致性hash算法还是主从复制完再做处理,都不是一件容易的事情。

三、优化(索引)

索引是存储引擎用于快速找到记录的一种数据结构,同时,由于InnoDB中索引特殊的实现方式,在索引列做排序或者聚合操作时也能够得到非常快的响应,另外,索引可以让查询锁定更少的数据行,锁冲突的减少意味着阻塞减少,性能随之提高。(单击跳转至《让SQL飞:RR级别下的GAP锁范围》)

对于索引优化的讨论离不开索引的实现方式,InnoDB索引采用B+Tree实现,B+Tree是B-Tree的变种,B-Tree能解决的问题,B+Tree也能解决,同时,B+Tree有如下特点:

>多路搜索:单个支节点存储多个关键字,降低树的高度,搜索更快

>左闭合区间:更好地支持自增主键

>叶子结点存储数据:支节点不包含数据域,意味着支节点大小一定时,较B-Tree能存储更多的关键字;由于每次访问都需要找到叶子节点,效率更稳定

>叶子结点由链指针相连:扫表能力

00042afb57e78396e45b62a8ff064161.png

1. 聚簇索引和二级索引

InnoDB的聚簇索引实际上就是在B+Tree的叶子节点中存储了完整的数据行,就是通常所说的主键上的索引。

InnoDB的数据库表必定且仅包含一个聚簇索引,当没有定义主键时,InnoDB会选择一个唯一的非空索引来代替,倘若不存在这样的索引,InnoDB会隐式定义一个主键来做聚簇索引。

定义聚簇索引时要求主键自增,自增的主键在做顺序插入时并发性能最好。uuid等随机字符串不适合在InnoDB中充当主键,随机插入将导致大量的页分裂出现,数据库性能急剧下降,这也是研发者在做分库分表时宁愿多花一些开销来做全局自增主键而不使用uuid的原因。

另外,主键的数据类型要求尽可能的小,同一张表中的所有二级索引都包含了主键值,更大的主键类型将使得二级索引占用更多的资源。

二级索引就是普通索引,B+Tree的叶子节点中仅包含主键,当需要获取完整的数据行时需要回表通过主键来查询,这就是该名字的由来,下面具体说明二级索引的优化。

2. 单列索引和多列索引

多个单列索引与一个多列索引完全不同,尽管在MySQL5.0版本中引入了索引合并,使得多个列在执行and操作时也能够利用到这些索引,但索引合并会耗费大量的cpu和内存资源,同时更说明建立的索引很糟糕。

当explain出现type=index_merge的时候,就需要考虑建立多列索引了。

建立多列索引通常依赖于where条件、同时需要考虑order by排序和group by聚合。

最左匹配原则告诉我们编写查询语句时的诸多限制,多列索引的顺序是如此重要。通常,我们按照选择性给多列索引排序,以便过滤掉更多的数据,但在使用过程中我们或许要考虑地更多,对于那些选择性较低但执行频率很高的列,是否需要适当的调整该索引列的顺序值得商榷。

在MySQL5.6版本的多列索引中,引入了一个叫索引下推的内部优化。对于多列索引中违反最左匹配而不能使用索引的列,在回表时一并将这些条件传到存储引擎,以便减少存储引擎的IO量。

3. 自定义哈希索引

InnoDB中尽管有自适应哈希索引,但它仅仅是作为InnoDB的一个内部优化,存储于内存,且不可控。

不过我们似乎可以从中得到一些启发,假设现在需要存储大量的长字符串,且经常需要对这些字符串做等值匹配。如果在此字段上直接建立B+Tree索引,那索引占用的内存空间将会是巨大的,这个时候我们可以考虑来创建自定义哈希索引解决这个问题,具体过程如下:

a>假设在表tb_net中address是该字段的名称,首先需要做的就是增加一个额外的列address_hash,通过hash函数计算出address的哈希值,将其作为address_hash的内容,address_hash=crc16(address)

b>匹配数据时,条件有两个,select address from tb_net where address_hash=crc16(${address}) and address=${address}

随着数据量增长,哈希冲突必然随之提高,需要时可以考虑更换哈希算法来降低哈希冲突。但是记住,哈希算法选择计算结果是数字类型的,而像MD5等,不仅计算出的哈希值长度大,且字符串在InnoDB中的计算效率远不如数字类型。

此外,可以看出,建立伪哈希索引类似于数据结构优化中的冗余列,所以也必须考虑维护该哈希列的代价。

4. 前缀索引

前缀索引与自定义哈希索引的目标相同,前缀索引通过索引字符串的开始部分来节约索引空间,但选择性随之降低,性能是否提高取决于索引的长度是否合理,需要在消耗资源与选择性之间做一个平衡。

前缀索引不支持排序、聚合,这点需要特别注意。

5. 覆盖索引

如果一个索引包含所有需要查询的字段,该索引就叫做覆盖索引,所以某些查询只需要使用索引就能够完成,而不需要回表。

这里举个例子,假设有个表tb_user,里面有字段name、age、phone,现在需要根据name排序,然后将name和age读取出来。name字段建立索引支持排序,但不可避免需要回表,如果我们建立的索引是index_name_age,就可以在索引中拿到所有需要的数据,这是典型的空间与时间权衡。

四、优化(查询语句)

查询语句的优化涉及到整个语句的执行步骤,没有一个放之四海而皆准的原则。

人们都知道select * from tb_*将导致回表,但在工作中很多人依然这样做,因为他们觉得代码复用带来的好处更多。

联表操作时需要小表驱动大表,目的是为了减少loop次数,但是倘若排序字段在大表里,恐怕小表驱动大表就不一定合适了。

在选择exist和in实现相同的功能时,经常根据索引来判断哪种方式更有效率,但不要忘了loop次数与hash join带来的开销,两者同样关键。

尽管没有通用的法则,但是从这几点来考虑肯定没有错:

1. 索引是否被使用,例如条件左侧有没有表达式

2. 回表次数能否减少,例如延迟关联

3. IO消耗能否降低,例如只返回需要的列

五、优化(配置)

倘若对你来说提升性能真的如此必要,即便牺牲一些安全也无所谓。

1. innodb_autoinc_lock_mode = 2

该参数为自增锁模式,可取3个值:

0:语句结束释放,语句内的自增键连续

1:普通insert立即释放,批量insert语句结束释放

2:立即释放

当设置成2的时候,由于锁占有时间最少,所以在插入时并发性能最高,但语句内的自增键不能保证连续。

2. binlog_format = statement

该参数为binlog的存储格式,可取3个值:

statement:每一条修改SQL的语句都会被记录

row:每一条数据的修改都会被记录

mixed:根据不同的情况选择以上两种

当设置成statement的时候,binlog相对于其它两种格式占用的空间最少,但是主从复制时可能出现问题,例如insert语句中包含一些特殊的函数。

3. sync_binlog = 0

该参数为binlog的持久化方式,可取3个值:

0:定时写入os buffer,调用sync()

1:commit时写入os buffer,调用sync()

2:commit时写入os buffer,定时调用sync()

当设置成0的时候,commit时log仅存在于binlog buffer中,由于不必每次提交时做文件系统交换,所以这种配置下的性能最好,但是假如系统崩溃,将丢失掉这段时间内的日志,导致数据不完整。

4. innodb_flush_log_at_trx_commit = 0

该参数为redolog持久化方式,参数配置与sync_binlog相同,有自己的redolog buffer。

在InnoDB中,事务的持久性由redolog实现,所以系统一旦崩溃,持久性将不能得到保证;另外,undolog的持久化由redolog实现,事务的原子性不能保证。

73f431b68f8aa23ffc586a044e596d11.png

六、结束语

不考虑业务场景而单独讨论数据库优化没有意义,很多时候都是在做权衡取舍,读写分离下需要考虑延迟问题,第三方缓存组件下需要考虑数据一致性问题。

但这些都不是糟糕设计的理由。

原创不易,点个赞再走呗!

Thanks♪(・ω・)ノ

--------------------------------

公众号:以镒称铢

出自<孙子兵法 · 军形>,故胜兵若以镒称铢,败兵若以铢称镒。胜者之战民也,若决积水于千仞之溪者,形也。

长按下图二维码关注,你将发现一个不一样的世界,君子引而不发,跃如也~

812595b247791fdbbe34909157fa7e16.png

这篇关于把数据转换为在内存中Tree(树形结构)。_拜托,别再问我数据库性能优化了!的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/237424

相关文章

Vue3 的 shallowRef 和 shallowReactive:优化性能

大家对 Vue3 的 ref 和 reactive 都很熟悉,那么对 shallowRef 和 shallowReactive 是否了解呢? 在编程和数据结构中,“shallow”(浅层)通常指对数据结构的最外层进行操作,而不递归地处理其内部或嵌套的数据。这种处理方式关注的是数据结构的第一层属性或元素,而忽略更深层次的嵌套内容。 1. 浅层与深层的对比 1.1 浅层(Shallow) 定义

Spring Security基于数据库验证流程详解

Spring Security 校验流程图 相关解释说明(认真看哦) AbstractAuthenticationProcessingFilter 抽象类 /*** 调用 #requiresAuthentication(HttpServletRequest, HttpServletResponse) 决定是否需要进行验证操作。* 如果需要验证,则会调用 #attemptAuthentica

大模型研发全揭秘:客服工单数据标注的完整攻略

在人工智能(AI)领域,数据标注是模型训练过程中至关重要的一步。无论你是新手还是有经验的从业者,掌握数据标注的技术细节和常见问题的解决方案都能为你的AI项目增添不少价值。在电信运营商的客服系统中,工单数据是客户问题和解决方案的重要记录。通过对这些工单数据进行有效标注,不仅能够帮助提升客服自动化系统的智能化水平,还能优化客户服务流程,提高客户满意度。本文将详细介绍如何在电信运营商客服工单的背景下进行

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展,我们的业务数据越来越多,单纯使用 MySQL 已经不能满足我们的数据查询需求,例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后,随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步,我们目前的解决方案是建立数据中间表。把需要检索的业务数据,统一放到一张M

关于数据埋点,你需要了解这些基本知识

产品汪每天都在和数据打交道,你知道数据来自哪里吗? 移动app端内的用户行为数据大多来自埋点,了解一些埋点知识,能和数据分析师、技术侃大山,参与到前期的数据采集,更重要是让最终的埋点数据能为我所用,否则可怜巴巴等上几个月是常有的事。   埋点类型 根据埋点方式,可以区分为: 手动埋点半自动埋点全自动埋点 秉承“任何事物都有两面性”的道理:自动程度高的,能解决通用统计,便于统一化管理,但个性化定

性能测试介绍

性能测试是一种测试方法,旨在评估系统、应用程序或组件在现实场景中的性能表现和可靠性。它通常用于衡量系统在不同负载条件下的响应时间、吞吐量、资源利用率、稳定性和可扩展性等关键指标。 为什么要进行性能测试 通过性能测试,可以确定系统是否能够满足预期的性能要求,找出性能瓶颈和潜在的问题,并进行优化和调整。 发现性能瓶颈:性能测试可以帮助发现系统的性能瓶颈,即系统在高负载或高并发情况下可能出现的问题

使用SecondaryNameNode恢复NameNode的数据

1)需求: NameNode进程挂了并且存储的数据也丢失了,如何恢复NameNode 此种方式恢复的数据可能存在小部分数据的丢失。 2)故障模拟 (1)kill -9 NameNode进程 [lytfly@hadoop102 current]$ kill -9 19886 (2)删除NameNode存储的数据(/opt/module/hadoop-3.1.4/data/tmp/dfs/na

异构存储(冷热数据分离)

异构存储主要解决不同的数据,存储在不同类型的硬盘中,达到最佳性能的问题。 异构存储Shell操作 (1)查看当前有哪些存储策略可以用 [lytfly@hadoop102 hadoop-3.1.4]$ hdfs storagepolicies -listPolicies (2)为指定路径(数据存储目录)设置指定的存储策略 hdfs storagepolicies -setStoragePo

HDFS—存储优化(纠删码)

纠删码原理 HDFS 默认情况下,一个文件有3个副本,这样提高了数据的可靠性,但也带来了2倍的冗余开销。 Hadoop3.x 引入了纠删码,采用计算的方式,可以节省约50%左右的存储空间。 此种方式节约了空间,但是会增加 cpu 的计算。 纠删码策略是给具体一个路径设置。所有往此路径下存储的文件,都会执行此策略。 默认只开启对 RS-6-3-1024k

Hadoop集群数据均衡之磁盘间数据均衡

生产环境,由于硬盘空间不足,往往需要增加一块硬盘。刚加载的硬盘没有数据时,可以执行磁盘数据均衡命令。(Hadoop3.x新特性) plan后面带的节点的名字必须是已经存在的,并且是需要均衡的节点。 如果节点不存在,会报如下错误: 如果节点只有一个硬盘的话,不会创建均衡计划: (1)生成均衡计划 hdfs diskbalancer -plan hadoop102 (2)执行均衡计划 hd