-
2020-03-23 11:41:10
点赞多大胆,就有多大产!有支持才有动力!将技术分享给每一个技术使用者和爱好者!
干货满满,摆好姿势,点赞发车!
前言
数据库优化是一个老生常谈的问题,刚入门的小白或者工作N年的光头对这个问题应该都不陌生,你要面试一个中高级工程师那么他就想"哥俩好"一样那么粘,面试官肯定会问这个问题,这篇文章我们就和它哥俩好!而且这个问题就是一个送分题,数据库的优化方案基本就是那些,答案也都是固定的,大家只要好好准备这个问题就不会住你,可以在面试中安排面试官,不然就被面试官安排!话不多说下边就针对数据库优化展开讲!
相关文章
面试开始
小伙子看你简历上写了Mysql,数据库优化了解吗?
摸摸头之后笑着说数据库优化不是很了解嘿嘿~~~,这时和蔼的面试官头上出现了一抹红!
如果这时你正好想到了我这篇文章,那么你就会说数据库优化方面我还是很有研究的,请您听我慢慢道来......
首先
面试官我想解释一下为什么做数据库优化(这个你心里知道就好了,面试的时候就不要说了)
- 系统的数据都从数据库上来,数据库的吞吐量和速度一定程度决定系统的并发和响应速度
- 系统运行与数据量成正比,数据读处理尤其是查询自然就慢
- Mysql数据库的数据最终在磁盘上持久化存储,读写不如Redis等这些内存数据库
其次
面试官大人我想说一下数据库优化一般从以下几个方面来:
- 数据库设计:数据表设计遵循三范式,使用合适的数据类型,使用合适的存储引擎
- 适当创建索引
- 数据库扩展:数据库的分表分库,读写分离等
- SQL语句优化等
接下来我们一一说明解释
数据库设计
数据库设计3范式
数据库设计范式如果要满足N范式必须要先满足N-1范式
第一范式1NF:字段原子性
第一范式简单的说就是表中的字段是最小不可再分的,我们下边举个例子,我们看到以下一张用户表。里边的字段是不可再分的,这样就符合第一范式的原子性,可能有些朋友会疑问,这个地址还可以分成省份、城市、区/县三个字段,是的!如果是一个电商项目它需要再分,那么就不符合第一范式,所以具体还是看项目的需求,没有固定标准,在项目需求中它的设计已不可再分那么就符合第一范式!
第二范式2NF: 消除对主键的部分依赖
2NF的使用是需要满足1NF为前提,在表中添加一个业务字段,而主键不用来做业务处理,比如我们的商品表有商品id,商品id为商品的主键,但是需要创建一个商品编号列来专门处理业务,因为id太敏感,我们处理业务都是用商品编号来处理,比如展示商品时展示编号等等!
第三范式3NF:在2NF的基础上添加外键
3NF的使用必须满足2NF,要求表中的每一列只与主键直接相关而不是间接相关,(表中的每一列只能依赖于主键),比如下面的例子,订单表中有客户相关信息,在分离出客户表之后,订单表中只需要有一个用户id即可(外键),而不能有其他的客户信息。因为其他的客户信息直接关联于用户id,而不是直接与订单id直接相关。如下图所示:
分离之后:
三大范式只是一般设计数据库的基本理念,可以建立冗余较小、结构合理的数据库。如果有特殊情况,当然要特殊对待,数据库设计最重要的是看需求跟性能,需求>性能>表结构。所以不能一味的去追求范式建立数据库!需求才是粑粑
数据类型
尽量使用可以正确存储数据的最小数据类型
更小的数据类型意味着更快,占用更少的磁盘,内存、缓存和处理时间
尽量使用整型表示字符串
因为字符集和校对规则,使处理字符比整型更复杂,比如:我们使用数据库内置的datetime类型存储时间而不是字符类型,我们使用整型存储ip而不是直接将ip字符串存到数据库中
尽可能使用not null
这个值是很烦人的,建字段时请尽量指定是否非空,NULL使得索引,统计,比较都变得更复杂,而且索引尽量不要创建到可以为null的字段上
字符串类型
VARCHAR是可变长字符串
比定长字符串(CHAR)更节省空间,仅使用必要的空间另外VARCHAR需要额外字节记录字符串长度(不同情况需要字节数不同)
CHAR类型是定长字符串
开发中基本很少用(一些公司甚至基本上不考虑这种类型了),注意:字符串长度定义不是字节数,是字符数
日期和时间类型
datetime
使用8字节存储空间,保存从1001年到9999年的秒数。与时区无关,默认情况下,Mysql以一种可排序的格式显示它的值,例如:"2018-10-14 22:30:08"
timestamp
只使用4字节存储,保存1970年1月1日午夜以来的秒数,依赖于系统时区,和UNIX时间戳相同,转换函数分别为FROM_UNIXTIME()和UNIX_TIMESTAMP(),可以设置根据当前时间戳更新,比如我们熟悉的update_time字段
整数类型
UNSIGNED
属性表示不允许负值,可以使得正数的上限提高一倍,比如tinyint+unsigned可以使原本的-128~127的范围变为0~255
tinyint
我们一般用它存储状态值而不要用int,如果是Boolean类型,那么tinyint(1)当值为1和0时,查询结果自动转为true和false,条件参数相应的也可以直接传入true和false即可
INT(11)
不会限制值的范围,只是规定了一些客户端工具用来显示的字符的个数,所以对于存储和计算来说INT(11)和INT(1)相同
IP地址
实际上是32位无符号整数,用INT存储,Mysql提供转换函数为INET_ATON()和INET_NTOA()
小数
decimal不会损失精度,存储空间会随数据的增大而增大。double占用固定空间,较大数的存储会损失精度,通常存金额用decimal(11,2),这表示整数部分和小数部分分别为9位和2位注意!,当然可以根据具体的金额大小选择长度,注意这时候对应的java中用BigDecimal类来处理运算时要仔细,因为加减法和比较跟平常不一样
存储引擎
介绍
数据库存储引擎是数据库底层组件,数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎还可以获得特定的功能。我们可以通过SHOW ENGINES;
InnoDB存储引擎
InnoDB越做越好从MySQL5.5版本之后,MySQL的默认内置存储引擎已经是InnoDB,主要特点有
- 容灾恢复性比较好
- 支持事务,默认事务隔离界别为可重复读
- 使用的锁粒度为行锁,可以支持更高的并发
- 支持外键
- 配合一些热备工具可以支持在线热备份
- 在InnoDB中存在着缓冲管理,通过缓冲池,将索引和数据全部缓存起来,加快查询的速度
- 对于InnoDB类型的表,其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。根据主键进行排序,数据和索引放在一块,都位于B+数的叶子节点上
MyISAM存储引擎
在5.5版本之前,MyISAM是MySQL的默认存储引擎,该存储引擎并发性差,不支持事务,所以使用场景比较少,主要特点有
- 不支持事务
- 不支持外键,如果强行增加外键,不会提示错误,只是外键不其作用
- 对数据的查询缓存只会缓存索引,不会像InnoDB一样缓存数据,而且是利用操作系统本身的缓存
- 默认的锁粒度为表级锁,所以并发度很差,加锁快,锁冲突较少,所以不太容易发生死锁
- 支持全文索引(MySQL5.6之后,InnoDB存储引擎也对全文索引做了支持),但是MySQL的全文索引基本不会使用,对于全文索引,现在有其他成熟的解决方案,比如:ElasticSearch,Solr,Sphinx等
- 数据库所在主机如果宕机,MyISAM的数据文件容易损坏,而且难恢复
MEMORY存储引擎
将数据存在内存中,和市场上的Redis,memcached等思想类似,为了提高数据的访问速度,主要特点有
- 支持的数据类型有限制,不支持TEXT和BLOB类型,对于字符串类型的数据,只支持固定长度的行,VARCHAR会被自动存储为CHAR类型
- 支持的锁粒度为表级锁。所以,在访问量比较大时,表级锁会成为MEMORY存储引擎的瓶颈
- 由于数据是存放在内存中,所以在服务器重启之后,所有数据都会丢失
- 查询的时候,如果有用到临时表,而且临时表中有BLOB,TEXT类型的字段,那么这个临时表就会转化为MyISAM类型的表,性能会急剧降低
ARCHIVE存储引擎
ARCHIVE存储引擎适合的场景有限,由于其支持压缩,故主要是用来做日志,流水等数据的归档,主要特点有
- 支持Zlib压缩,数据在插入表之前,会先被压缩
- 仅支持SELECT和INSERT操作,存入的数据就只能查询,不能做修改和删除;
- 只支持自增键上的索引,不支持其他索引
CSV存储引擎
数据中转试用,主要特点有
- 其数据格式为.csv格式的文本,可以直接编辑保存
- 导入导出比较方便,可以将某个表中的数据直接导出为csv,试用Excel办公软件打开
选择依据
如果没有特殊需求默认使用InnoDB引擎即可
MyISAM:以读写插入为主的应用程序,比如博客系统、新闻门户网站。
Innodb:更新(删除)操作频率也高,或者要保证数据的完整性;并发量高,支持事务和外键保证数据完整性。比如OA自动化办公系统
索引
已为客官备好,轻点哦《这小伙子把MySQL索引使用讲的真明白,真好,快来戳他》
索引数据结构在这在这《搞懂MySQL数据库索引数据结构这一篇足够从此不再萌萌哒》
MySQL读写分离
MySQL分表分库
一样点一下就成《手把手基于Mycat实现MySQL数据拆分》
SQL优化
这里列举出来一些用过的,看到的欢迎大家评论区补充讨论
1、查询尽量避免全表扫描,首先考虑在where、order by字段上添加索引
2、避免在where字段上使用NULL值,所以在设计表时尽量使用NOT NULL约束,有些数据会默认为NULL,可以设置默认值为0或者-1
3、避免在where子句中使用!=或<>操作符,Mysql只对<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE使用索引
4、避免在where中使用OR来连接条件,否则可能导致引擎放弃索引来执行全表扫描,可以使用UNION进行合并查询
select id from t where num = 30 union select id from t where num = 40;
5、尽量避免在where子句中进行函数或者表达式操作
6、最好不要使用select * from t,用具体的字段列表代替"*",不要返回用不到的任何字段
7、in 和 not in 也要慎用,否则会导致全表扫描,如
select id from t where num IN(1,2,3)如果是连续的值建议使用between and,select id from t where between 1 and 3;
8、select id from t where col like %a%;模糊查询左侧有%会导致全表检索,如果需要全文检索可以使用全文搜索引擎比如es,slor
9、limit offset rows关于分页查询,尽量保证不要出现大的offset,比如limit 10000,10相当于对已查询出来的行数弃掉前10000行后再取10行,完全可以加一些条件过滤一下(完成筛选),而不应该使用limit跳过已查询到的数据。这是一个==offset做无用功==的问题。对应实际工程中,要避免出现大页码的情况,尽量引导用户做条件过滤
关注本系列文章的朋友应该发现,这里的未完待续已经消失,我们的MySQL优化就告一段落,主要从数据库设计、索引、数据库拆分和SQL语句上进行优化,更多优化方案希望大家通过评论区留言!
路漫漫其修远兮,吾将上下而求索
更多相关内容 -
大型网站性能优化方案
2016-09-19 14:14:51大型网站性能优化的常规手段。包含CDN加速、负载均衡、页面优化等。原创。 -
《优化方案》2022高考生物二轮配套训练:模块综合检测.docx
2022-02-07 10:29:14《优化方案》2022高考生物二轮配套训练:模块综合检测.docx -
2022版《优化方案》高中数学人教A版必修四文档:第二章§3.1数乘向量 Word版含答案.docx
2022-02-07 11:09:222022版《优化方案》高中数学人教A版必修四文档:第二章§3.1数乘向量 Word版含答案.docx -
2022版《优化方案》高中政治人教版必修二配套练习:第一单元单元综合检测 Word版含答案.docx
2022-02-07 11:02:432022版《优化方案》高中政治人教版必修二配套练习:第一单元单元综合检测 Word版含答案.docx -
2022版《志鸿优化设计》高考生物二轮复习题型专项训练二坐标曲线类 Word版含答案.docx
2022-02-08 14:36:312022版《志鸿优化设计》高考生物二轮复习题型专项训练二坐标曲线类 Word版含答案.docx -
2021优化方案高中语文鲁人版必修5习题:第四单元单元综合检测(四) Word版含答案.docx
2022-02-05 01:12:392021优化方案高中语文鲁人版必修5习题:第四单元单元综合检测(四) Word版含答案.docx -
2022版优化方案高考数学(山东专用·理科)二轮复习小题分类练(五) Word版含答案.docx
2022-02-08 15:15:222022版优化方案高考数学(山东专用·理科)二轮复习小题分类练(五) Word版含答案.docx -
2022版《优化方案》高中政治人教版必修二配套练习:第四单元单元综合检测 Word版含答案.docx
2022-02-07 11:05:362022版《优化方案》高中政治人教版必修二配套练习:第四单元单元综合检测 Word版含答案.docx -
2022版《优化方案》高中政治人教版必修二配套练习:第二单元单元综合检测 Word版含答案.docx
2022-02-07 11:04:592022版《优化方案》高中政治人教版必修二配套练习:第二单元单元综合检测 Word版含答案.docx -
优化方案_高中同步测试卷_人教化学选修4:高中同步测试卷(七)Word版含答案x.docx
2021-12-18 16:05:37优化方案_高中同步测试卷_人教化学选修4:高中同步测试卷(七)Word版含答案x.docx -
Unity大场景数据加载及优化方案
2018-08-07 16:29:55前段时间,有几个虚拟仿真公司跟我请教关于大地形的加载优化问题,它们使用的引擎都是自己研发的,引擎对于开发者来说,大同小异,它们的基本构造是一样的,关键是在于解决问题的方法,正是基于这个前提写了这个课程...前段时间,有几个虚拟仿真公司跟我请教关于大地形的加载优化问题,它们使用的引擎都是自己研发的,引擎对于开发者来说,大同小异,它们的基本构造是一样的,关键是在于解决问题的方法,正是基于这个前提写了这个课程,希望给读者提供一些解决问题的思路。
目前,大地形动态加载已经成为当前游戏开发或者虚拟仿真领域必须要解决的问题,尤其在虚拟军事仿真领域,由于要涉及到大兵团虚拟演练作战,这对仿真真实性要求比较高,需要根据真实地形数据将其在终端硬件设备上绘制出来,或者是通过美术人员利用建模工具制作大地形,比较流行的做法是利用航拍拍摄城市或者山区地形生成高度图,利用现有的技术按照一定的比例还原城市和山区地形原貌,生成的大地形大部分是上千公里区域,面对这么庞大的数据,由于硬件的限制,需要程序做对地形的加载做一些优化操作,以解决运行效率问题。
该课程主要是提供了大地形动态数据的加载及优化方案,因为在虚拟仿真以及游戏开发中都会涉及到海量数据的加载,由于内存的限制,不可能直接将大地形一次性的加载到内存中,即使硬件满足了需求,除了地形还有其他道具的加载,这样会对内存带来严重的负载问题。解决大地形加载方案:首先想到的做法是将大地形进行分块加载,单单的分块加载并不能完全解决实际问题,比如在飞行仿真中,由于角色飞行速度快,会导致加载地形块不完整的情况出现,当然也会出现程序运行卡顿情况,还有一种情况,角色在场景中做移动操作,如果角色在块与块边界附近不停的旋转视角或者是来回移动,这样也会导致地形频繁的加载卸载,同样会出现地形加载问题,体验非常不好。另外,大地形上面的建筑物非常密集,它们也会随地形加载到内存中,这些建筑物也要进行分块以及遮挡处理的;除了地形和建筑物模型数据,贴图的加载和渲染也是需要解决的问题。大地形以及建筑物二者都会涉及到大量贴图的加载,贴图的加载也会吃掉大量内存的,如何把这么多贴图加载到内存中?以上种种问题,该课程给出了解决问题的答案。下面我们把本篇课程涉及的主要技术给读者介绍一下,后续章节会详细给读者讲解,下面先从大地形数据加载方案说起。大地形数据加载方案
大地形加载考虑到现有的内存机制, 不可能一次性将其加入到内存中,这个问题是显而易见的,其实在游戏开发中经常遇到,比如我们常见的进度条,加载进度条的目的就是等待程序加载场景,进度条只是一个蒙板遮罩而已。大地形的加载,别无他法,只能用分块,这个是大方向,因此作为程序来说,要做的事情是如何分块?块的大小是多少?这些具体的问题我们要根据需求划分,比如飞行模拟器块大小可能就要大一些,因为俯瞰的视角比较大,场景漫游块可以小一些等等,下面我们就以游戏的经典之作——魔兽世界地形加载方案为例给读者先介绍一下它的实现原理,魔兽世界这款游戏实现的就是无缝地图的拼接,所以非常具有参考价值,先看下图所示:
魔兽世界是如何实现无限地图的?其实它也是很多的场景块拼接而成的,我们通过编辑器分析魔兽世界的地形块的大小划分,魔兽世界场景我们称为MapWorld是由一系列MapTile组成,这些MapTile的大小是1600/3 ≈ 533.33m,而每个MapTile又是由 16x16 个MapChunk组成,由此可以计算出每个MapChunk≈33.33m。再就是每个MapChunk又由9x9+8x8个地形顶点高度,法线,若干贴图层(一般为4层)组成的地表纹理。魔兽世界地形的大小,在这里我们就不讨论了,但它划分块的思想我们是可以借鉴的。
继续分析魔兽世界的分块方法:它们是根据矩阵的方式进行划分的,在XZ平面上进行的,每个块都会包含一定的信息数据的,比如:在XZ(3,3)位置的MapTile,每个MapTile都包含了该tile内使用的贴图、模型实例等等。所谓模型实例也就是我们的道具,可以理解成相同模型在tile内不同摆放位置、大小、角度的信息,它们都是被保存在二进制文件中的,为了节省文件尺寸,模式实例是通过index模型方式保存的,同顶点索引类似,在每个MapTile里面还有贴图信息比如贴图的名字和UV信息等等。本篇课程的分块思维方式跟魔兽世界的类似,会在后面的章节中详细介绍,块分好了以后,下面就是实现原理了。
实现原理:在任何时刻,程序总是保存着玩家所在的及其周围的3x3个MapTile,随着玩家的移动,这些MapTile会被动态更新,新的MapTile被加载以替换被卸载的旧MapTile。为了提高调度效率,魔兽引入了Cache机制,Cache中保存着最多16个MapTile数据。需要加载新的MapTile时,首先会在Cache中查找;卸载的旧MapTile也不会被立刻删除,而是保存在Cache中以备再次调用。由于一段时间内玩家的活动范围通常不会有太大变化,这一Cache策略在应用中表现的非常出色,这是无缝地图的基本原理。地形的动态加载卸载我们会使用多线程去实现,我们会整两个线程:一个线程专门用于加载地形,另一个线程专门用于卸载或隐藏地形MapTile。让我们再来回忆一下游戏的经典之作,游戏场景效果如下所示:
本篇课程实现的方法可以使用两种方式处理块的加载显示问题,一种是利用对象池的方式,预先加载分块地形,根据视距进行检测判断显示那些地块以及隐藏那些地块,在这里并不删掉它们。这样只需要一个线程就可以。另一种方式是利用多线程,起一个线程专门用于移除卸载不在视线范围内的地块,这样可以提升效率,下面介绍使用多线程的加载方案。多线程实现大地形加载方案
多线程在PC端游戏中使用的比较多,比如可以起一个线程专门进行资源的加载,游戏服务器中同样也会有多线程的使用,下面给读者介绍多线程实现方案,多线程处理问题就是把所有的加载逻辑放到了新的进程中,和主线程做一些进程间的通信,接受主线程的加载建议,做按需加载,也会自主做一些提前预加载,放进分配的内存,就跟魔兽世界的处理方式一样,通过进程间的内存共享机制,把加载的地形数据,共享给主进程使用。主游戏进程,永远只要维护一个很小的内存即可,大量的内存数据,都在另一个进程中处理。这样就可以优化大地形块的加载,实现方式如下所示:
首先主线程会先加载九块地形,主线程只负责维护这九块地形,无论角色怎么移动,角色所在的整个区域永远是九块地形,如上图所示的,这九块可以直接使用主线程加载到内存中,剩下的16块我们通过另一个线程将其放到缓存中,角色的位置是在已经加载好的九块地形中间,也就是在A所在的位置。随着角色的移动,会有新的地形块加入进来,同时现有的地形块会被置换出去,这样一直显示九块地形,被置换的地形并不会马上卸载掉,会根据角色移动情况做预判,它会等主线程通知,按照一定的规则进行卸载地块和加载地块。其实这种实现方式就是我们通常所说的双缓存-多线程技术。实现的效果如下所示:
地形分块加载完事了后,下面就要考虑地形上面的纹理贴图问题了,地形的贴图资源也会占用大的内存,下面介绍如何加载海量贴图数据。
大地形海量图片的加载方案
大地形中的场景图片非常多,地形中的贴图至少会有四层,这么多贴图我们在加载时需要考虑的,我们分块时也需要考虑这些因素,另外场景中使用的LightMap烘培也是要考虑的问题,为了缓解内存压力,我们事先会将不同块中的地形材质以及建筑物材质进行打包,先介绍如何分块加载场景贴图?它实现方式如下所示:
该思路就是将场景中的贴图根据我们划分的块打成不同的图集,当然也可以将两个块中的贴图打成一个图集,图集大小对于PC端来说,最大是4096,在移动端最大是2048。这个也是为了避免内存频繁的加载卸载会导致很多内存碎片,不利于后面大内存的分配。在打图集之前我们需要做点事情就是需要将地形块中的纹理贴图与我们的打包图集之间建立一一对应关系,方便对号入座。因为我们打包的图集跟实际地形之间不会有任何关系,要确立二者之间的对应关系我们需要在它们中间再整一张索引文件表格,它是连接图集与实际地形纹理的桥梁,通过我们建的索引文件,我们可以找到实际地形中纹理与图集纹理之间的对应关系,我们建的索引表格是要加载到内存中的,而我们的图集是根据加载任务后期才加到内存中的,这就要求我们的索引文件尽可能的少,因为它们是常驻内存的,除了海量图片的加载,我们还需要处理密集建筑的加载。- 密集建筑的加载方案
密集的建筑加载,大家试想一下,如果把场景中所有的建筑一次性加载到内存中,内存瞬间就会占满,帧数瞬间下降,这也是为什么大家在游戏场景中移动时,遇到密集的建筑就会卡顿一下的原因。以前处理方式是使用LOD处理,被遮挡的物体使用简模,这样也会加大内存的负载效果,如果角色一直在建筑物之间来回穿梭,这样不同LOD模型就需要来回切换,对内存也是一个负担,效果不理想。这些问题对于程序员面来说必须解决的问题,如何解决呢?很多人想到了合并大Mesh,这种方法行不通的,大网格并不适合做裁剪操作,试想一下,我们合并的网格,如果摄像机只看其一小部分,因为它们是一个整体这样就需要把他们一起加载到内存中,而实际上我们并不需要这么多模型数据,在合并网格时,在这里也给读者一个建议,尽量把靠的很近的模型进行合并,避免上述问题发生。其实最有效解决方案还是划分块,这个划分块可以利用地形划分的思想进行,它是与地形块紧密相关的,每个地形块中的建筑物跟随地形块一起加载。如果块中的建筑非常密集,这种方法还不能够完全解决,还需要进一步的处理,就是要加入OC遮挡算法结合LOD算法,这样就可以完全解决我们当前的问题了,这也是本篇课程
要讲解的方法,再进一步的优化方法是可以将OC遮挡算法和LOD算法放到GPU中计算,这样效率还会提升,在Siggraph2015发表了一篇文章GPU-Driven Rendering Pipelines,它的思想就是使用GPU进行遮挡裁剪处理,主要分两个阶段,使用的是DX12图形API,如下图所示:
它的思想就是第一步先做一个初略的遮挡裁剪列表,而后在此基础上再根据视线距离或者射线检测做进一步的细化裁剪操作,这个思想跟我们的碰撞检测算法类似,引擎中碰撞检测算法也是基于这个原理实现的,给读者介绍一下:实际可用的碰撞检测算法,一般要分2个阶段:
第一阶段,broad phase 快速找出潜在的碰撞物体对列表,不在这个列表里的是绝对没可能碰撞的。broad phase确定了一批需要进一步检查的物体对。
第二阶段,narrow phase 准确找出发生碰撞的物体对列表。因为上一个阶段的部分物体对实际上是没有碰撞的,需要在这个阶段剔除。
broad phase其中有一个简单算法叫sweep and prune(SAP),本质上是利用了排序算法。第一步是初始化排序列表,列表中的元素是包围盒,可以用任意排序算法完成,例如快排;之后的排序就不是用快排了,而是用冒泡排序,为什么用冒泡排序更好呢?是因为一个默认的前提:物体的运动有时间相关性(temporal coherence),即当前帧和下一帧的位置是相近的,所以在冒泡排序过程中,发生的位置交换预期都很靠近。
其实算法中有很多类似的地方,这里我们也要互相借鉴它们解决问题的思想用于解决我们的问题。笔者以前做的是端游,端游中很多优化思想同样适用于移动端,移动端跟PC端比,就是一台配置比较低的电脑而已。接着我们的遮挡裁剪继续给读者介绍,论文作者也做了一个效率测试,以250’000物体,1G的网格为例,测试效果如下所示:
是不是很酷啊!在项目开发中完全可以用它解决问题,下面我们再谈谈使用GPU去优化我们的大地形场景。
GPU大地形渲染优化解决方案
我们的大地形首先会有自己的地表贴图,常用的地表贴图是四张纹理融合,最多可以有八张贴图融合,地形纹理渲染会涉及到LOD算法,远处的地形网格可以简化一些,对应的贴图也是最低的,这就是MipMap的使用。另外肯定有草有花以及其他大量相同的物件渲染,先说说草和花的绘制,他们在游戏中会非常的多,常用的做法是引擎提供的面片或者是十字交叉,或者三张图片交叉,然后将带有Alpha通道的贴图映射在上面,如下图所示效果:
CPU绘制这些草或者花在PC端是可以的,因为现在的电脑都是多核的,在手机端就会影响到效率问题了。使用CPU绘制,DrawCall会非常的多,而且草或者花还需要摆动,计算量很大的,这严重影响了运行效率,CPU有难,GPU可以帮忙,我们可以将草或者花的绘制放到GPU中执行,效果如下所示:
这些草的绘制都是在GPU中进行的,游戏中花的绘制原理跟草的绘制是类似的。它们的制作并不是美术建模的草或者花而是程序自定义生成Mesh,然后在Mesh上面加上贴图进行渲染处理的,从而降低DrawCall,GPU使用的是几何着色器,DX10图形API使用的就是几何着色器。对于其他相同物件的绘制,我们可以使用GPU Instancing优化处理,除了处理批量物体外,我们还会使用GPU进行材质渲染以及处理角色动画蒙皮等等。如下图模型材质渲染效果所示:
除了这些渲染外,我们还需要针对场景的后处理渲染,Bloom,Blur,SSAO,HDR等等常用后处理渲染。下面给读者展示一个体积光渲染,效果如下所示:
图片来源:https://zhuanlan.zhihu.com/p/39754801
这样渲染的效果让场景更加逼真形象,GPU能帮助我们处理很多事情,这里就不一一列举了。- 总结
本篇课程主要目的是解决大地形数据加载和密集建筑物加载优化问题,本篇课程主要会从以上几方面结合着案例给读者进行讲解,希望通过本篇课程的学习能够给读者提供一些解决问题的思路,做到举一反三。不同的项目需求是不同的,但是遇到的问题都差不多,随着硬件的提升,算法的改进,在大地形的效率运行方面还会有质的提升。
-
四年级数学下册数学好玩第三课优化作业pdf无答案北师大版
2021-09-09 00:02:22四年级数学下册数学好玩第三课优化作业pdf无答案北师大版 -
高中生物全国名校名卷优化重组专题5光合作用无答案PDF
2021-09-10 03:13:34高中生物全国名校名卷优化重组专题5光合作用无答案PDF -
leetcode答案-leetcode:我对leetcode算法问题的解决方案,主要使用JavaScript,有时使用Go
2021-06-30 02:10:58如果它被接受,它就在这里结束——除非它被标记为“最佳”或“修订”,否则不会在事后重新优化。 涉及指针/引用的问题在 JavaScript 中感觉很脏/很奇怪,所以我选择学习更多 Go 并用 Go 编写它们。 随着我解决更多 ... -
高中生物全国名校名卷优化重组专题13遗传的分子基础1无答案PDF
2021-09-10 03:28:14高中生物全国名校名卷优化重组专题13遗传的分子基础1无答案PDF -
高中生物全国名校名卷优化重组专题28现代生物科技专题3无答案PDF
2021-09-10 03:25:18高中生物全国名校名卷优化重组专题28现代生物科技专题3无答案PDF -
高中生物全国名校名卷优化重组专题14遗传的分子基础2无答案PDF
2021-09-10 03:21:25高中生物全国名校名卷优化重组专题14遗传的分子基础2无答案PDF -
高中生物全国名校名卷优化重组专题4酶ATP和细胞呼吸无答案PDF
2021-09-10 03:16:06高中生物全国名校名卷优化重组专题4酶ATP和细胞呼吸无答案PDF -
高中生物全国名校名卷优化重组专题10遗传的基本定律2无答案PDF
2021-09-10 03:09:13高中生物全国名校名卷优化重组专题10遗传的基本定律2无答案PDF -
高中生物全国名校名卷优化重组专题15生物的变异育种和进化无答案PDF
2021-09-10 03:17:03高中生物全国名校名卷优化重组专题15生物的变异育种和进化无答案PDF -
高中生物全国名校名卷优化重组专题3细胞质细胞核的结构和功能无答案PDF
2021-09-10 03:11:50高中生物全国名校名卷优化重组专题3细胞质细胞核的结构和功能无答案PDF -
SQL优化方案
2019-05-08 15:49:10转载至:http://blog.itpub.net/31555484/viewspace-2565387/ 作者1:惨绿少年 ... 作者2:喜欢拿铁的人 ... 在进行MySQL的优化之前,必须要了解的就是MySQL的查询过程,很多查询优化...转载至:http://blog.itpub.net/31555484/viewspace-2565387/
作者1:惨绿少年
https://www.cnblogs.com/clsn/p/8214048.html
作者2:喜欢拿铁的人
https://zhuanlan.zhihu.com/p/49888088
在进行MySQL的优化之前,必须要了解的就是MySQL的查询过程,很多查询优化工作实际上就是遵循一些原则,让MySQL的优化器能够按照预想的合理方式运行而已。
图-MySQL查询过程
1 优化的哲学
注:优化有风险,涉足需谨慎
a 优化可能带来的问题?
-
优化不总是对一个单纯的环境进行,还很可能是一个复杂的已投产的系统;
-
优化手段本来就有很大的风险,只不过你没能力意识到和预见到;
-
任何的技术可以解决一个问题,但必然存在带来一个问题的风险;
-
对于优化来说解决问题而带来的问题,控制在可接受的范围内才是有成果;
-
保持现状或出现更差的情况都是失败!
b 优化的需求?
-
稳定性和业务可持续性,通常比性能更重要;
-
优化不可避免涉及到变更,变更就有风险;
-
优化使性能变好,维持和变差是等概率事件;
-
切记优化,应该是各部门协同,共同参与的工作,任何单一部门都不能对数据库进行优化!
所以优化工作,是由业务需要驱使的!
c 优化由谁参与?
在进行数据库优化时,应由数据库管理员、业务部门代表、应用程序架构师、应用程序设计人员、应用程序开发人员、硬件及系统管理员、存储管理员等,业务相关人员共同参与。
2 优化思路
a 优化什么?
在数据库优化上有两个主要方面:即安全与性能。
-
安全->数据可持续性;
-
性能->数据的高性能访问。
b 优化的范围有哪些?
存储、主机和操作系统方面:
-
主机架构稳定性;
-
I/O规划及配置;
-
Swap交换分区;
-
OS内核参数和网络问题。
应用程序方面:
-
应用程序稳定性;
-
SQL语句性能;
-
串行访问资源;
-
性能欠佳会话管理;
-
这个应用适不适合用MySQL。
数据库优化方面:
-
内存;
-
数据库结构(物理&逻辑);
-
实例配置。
说明:不管是设计系统、定位问题还是优化,都可以按照这个顺序执行。
c 优化维度?
数据库优化维度有四个:
硬件、系统配置、数据库表结构、SQL及索引。
优化选择:
-
优化成本:硬件>系统配置>数据库表结构>SQL及索引。
-
优化效果:硬件<系统配置<数据库表结构<SQL及索引。
1 优化工具有啥?
a 数据库层面?
检查问题常用工具:
1)MySQL
2)msyqladmin:MySQL客户端,可进行管理操作
3)mysqlshow:功能强大的查看shell命令
4)show [SESSION | GLOBAL] variables:查看数据库参数信息
5)SHOW [SESSION | GLOBAL] STATUS:查看数据库的状态信息
6)information_schema:获取元数据的方法
7)SHOW ENGINE INNODB STATUS:Innodb引擎的所有状态
8)SHOW PROCESSLIST:查看当前所有连接session状态
9)explain:获取查询语句的执行计划
10)show index:查看表的索引信息
11)slow-log:记录慢查询语句
12)mysqldumpslow:分析slowlog文件的
不常用但好用的工具:
1)Zabbix:监控主机、系统、数据库(部署zabbix监控平台)
2)pt-query-digest:分析慢日志
3)MySQL slap:分析慢日志
4)sysbench:压力测试工具
5)MySQL profiling:统计数据库整体状态工具
6)Performance Schema:MySQL性能状态统计的数据
7)workbench:管理、备份、监控、分析、优化工具(比较费资源)
关于Zabbix参考:
http://www.cnblogs.com/clsn/p/7885990.html
b 数据库层面问题解决思路?
一般应急调优的思路:针对突然的业务办理卡顿,无法进行正常的业务处理,需要立马解决的场景。
1)show processlist;
2)explain select id ,name from stu where name='clsn'; # ALL id name age sex;
select id,name from stu where id=2-1 函数 结果集>30;show index from table;
3)通过执行计划判断,索引问题(有没有、合不合理)或者语句本身问题;
4)show status like '%lock%'; # 查询锁状态
kill SESSION_ID; # 杀掉有问题的session。
常规调优思路:针对业务周期性的卡顿,例如在每天10-11点业务特别慢,但是还能够使用,过了这段时间就好了。
1)查看slowlog,分析slowlog,分析出查询慢的语句;
2)按照一定优先级,一个一个排查所有慢语句;
3)分析top SQL,进行explain调试,查看语句执行时间;
4)调整索引或语句本身。
c 系统层面?
Cpu方面:
vmstat、sar top、htop、nmon、mpstat;
内存:
free、ps-aux;
IO设备(磁盘、网络):
iostat、ss、netstat、iptraf、iftop、lsof;
vmstat命令说明:
1)Procs:r显示有多少进程正在等待CPU时间。b显示处于不可中断的休眠的进程数量。在等待I/O。
2)Memory:swpd显示被交换到磁盘的数据块的数量。未被使用的数据块,用户缓冲数据块,用于操作系统的数据块的数量。
3)Swap:操作系统每秒从磁盘上交换到内存和从内存交换到磁盘的数据块的数量。s1和s0最好是0。
4)Io:每秒从设备中读入b1的写入到设备b0的数据块的数量。反映了磁盘I/O。
5)System:显示了每秒发生中断的数量(in)和上下文交换(cs)的数量。
6)Cpu:显示用于运行用户代码,系统代码,空闲,等待I/O的Cpu时间。
iostat命令说明:
实例命令:iostat -dk 1 5
iostat -d -k -x 5 (查看设备使用率(%util)和响应时间(await))
1)tps:该设备每秒的传输次数。“一次传输”意思是“一次I/O请求”。多个逻辑请求可能会被合并为“一次I/O请求”。
2)iops :硬件出厂的时候,厂家定义的一个每秒最大的IO次数
3)"一次传输"请求的大小是未知的。
4)kB_read/s:每秒从设备(drive expressed)读取的数据量;
5)KB_wrtn/s:每秒向设备(drive expressed)写入的数据量;
6)kB_read:读取的总数据量;
7)kB_wrtn:写入的总数量数据量;这些单位都为Kilobytes。
d 系统层面问题解决办法?
你认为到底负载高好,还是低好呢?在实际的生产中,一般认为Cpu只要不超过90%都没什么问题。
当然不排除下面这些特殊情况:
Cpu负载高,IO负载低:
1)内存不够;
2)磁盘性能差;
3)SQL问题--->去数据库层,进一步排查SQL 问题;
4)IO出问题了(磁盘到临界了、raid设计不好、raid降级、锁、在单位时间内tps过高);
5)tps过高:大量的小数据IO、大量的全表扫描。
IO负载高,Cpu负载低:
1)大量小的IO写操作:
autocommit,产生大量小IO;IO/PS,磁盘的一个定值,硬件出厂的时候,厂家定义的一个每秒最大的IO次数。
2)大量大的IO 写操作:SQL问题的几率比较大
IO和cpu负载都很高:
硬件不够了或SQL存在问题。
4 基础优化
a 优化思路?
定位问题点吮吸:硬件-->系统-->应用-->数据库-->架构(高可用、读写分离、分库分表)。
处理方向:明确优化目标、性能和安全的折中、防患未然。
b 硬件优化?
主机方面:
根据数据库类型,主机CPU选择、内存容量选择、磁盘选择:
1)平衡内存和磁盘资源;
2)随机的I/O和顺序的I/O;
3)主机 RAID卡的BBU(Battery Backup Unit)关闭。
CPU的选择:
CPU的两个关键因素:核数、主频
根据不同的业务类型进行选择:
1)CPU密集型:计算比较多,OLTP - 主频很高的cpu、核数还要多
2)IO密集型:查询比较,OLAP - 核数要多,主频不一定高的
内存的选择:
OLAP类型数据库,需要更多内存,和数据获取量级有关。
OLTP类型数据一般内存是Cpu核心数量的2倍到4倍,没有最佳实践。
存储方面:
1)根据存储数据种类的不同,选择不同的存储设备;
2)配置合理的RAID级别(raid5、raid10、热备盘);
3)对与操作系统来讲,不需要太特殊的选择,最好做好冗余(raid1)(ssd、sas、sata)。
4)raid卡:
主机raid卡选择:
实现操作系统磁盘的冗余(raid1);
平衡内存和磁盘资源;
随机的I/O和顺序的I/O;
主机raid卡的BBU(Battery Backup Unit)要关闭。
网络设备方面:
使用流量支持更高的网络设备(交换机、路由器、网线、网卡、HBA卡)
注意:以上这些规划应该在初始设计系统时就应该考虑好。
c 服务器硬件优化?
1)物理状态灯
2)自带管理设备:远程控制卡(FENCE设备:ipmi ilo idarc)、开关机、硬件监控。
3)第三方的监控软件、设备(snmp、agent)对物理设施进行监控。
4)存储设备:自带的监控平台。EMC2(hp收购了)、 日立(hds)、IBM低端OEM hds、高端存储是自己技术,华为存储。
d 系统优化?
Cpu:
基本不需要调整,在硬件选择方面下功夫即可。
内存:
基本不需要调整,在硬件选择方面下功夫即可。
SWAP:
MySQL尽量避免使用swap。
阿里云的服务器中默认swap为0。
IO :
raid、no lvm、ext4或xfs、ssd、IO调度策略。
Swap调整(不使用swap分区)
/proc/sys/vm/swappiness的内容改成0(临时),/etc/sysctl. conf上添加vm.swappiness=0(永久)
这个参数决定了Linux是倾向于使用swap,还是倾向于释放文件系统cache。在内存紧张的情况下,数值越低越倾向于释放文件系统cache。
当然,这个参数只能减少使用swap的概率,并不能避免Linux使用swap。
修改MySQL的配置参数innodb_flush_ method,开启O_DIRECT模式:
这种情况下,InnoDB的buffer pool会直接绕过文件系统cache来访问磁盘,但是redo log依旧会使用文件系统cache。
值得注意的是,Redo log是覆写模式的,即使使用了文件系统的cache,也不会占用太多。
IO调度策略:
#echo deadline>/sys/block/sda/queue/scheduler 临时修改为deadline
永久修改
vi /boot/grub/grub.conf
更改到如下内容:
kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet
e 系统参数调整?
Linux系统内核参数优化:
vim/etc/sysctl.conf
net.ipv4.ip_local_port_range = 1024 65535:# 用户端口范围
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.tcp_fin_timeout = 30
fs.file-max=65535:# 系统最大文件句柄,控制的是能打开文件最大数量
用户限制参数(MySQL可以不设置以下配置):
vim/etc/security/limits.conf
* soft nproc 65535
* hard nproc 65535
* soft nofile 65535
* hard nofile 65535
f 应用优化?
业务应用和数据库应用独立;
防火墙:iptables、selinux等其他无用服务(关闭):
chkconfig --level 23456 acpid off
chkconfig --level 23456 anacron off
chkconfig --level 23456 autofs off
chkconfig --level 23456 avahi-daemon off
chkconfig --level 23456 bluetooth off
chkconfig --level 23456 cups off
chkconfig --level 23456 firstboot off
chkconfig --level 23456 haldaemon off
chkconfig --level 23456 hplip off
chkconfig --level 23456 ip6tables off
chkconfig --level 23456 iptables off
chkconfig --level 23456 isdn off
chkconfig --level 23456 pcscd off
chkconfig --level 23456 sendmail off
chkconfig --level 23456 yum-updatesd off
安装图形界面的服务器不要启动图形界面runlevel 3。
另外,思考将来我们的业务是否真的需要MySQL,还是使用其他种类的数据库。用数据库的最高境界就是不用数据库。
5 数据库优化
SQL优化方向:执行计划、索引、SQL改写。
架构优化方向:高可用架构、高性能架构、分库分表。
a 数据库参数优化?
调整
实例整体(高级优化,扩展):
thread_concurrency:# 并发线程数量个数
sort_buffer_size:# 排序缓存
read_buffer_size:# 顺序读取缓存
read_rnd_buffer_size:# 随机读取缓存
key_buffer_size:# 索引缓存
thread_cache_size:# (1G—>8, 2G—>16, 3G—>32, >3G—>64)
连接层(基础优化)
设置合理的连接客户和连接方式:
max_connections # 最大连接数,看交易笔数设置
max_connect_errors # 最大错误连接数,能大则大
connect_timeout # 连接超时
max_user_connections # 最大用户连接数
skip-name-resolve # 跳过域名解析
wait_timeout # 等待超时
back_log # 可以在堆栈中的连接数量
SQL层(基础优化)
query_cache_size: 查询缓存 >>> OLAP类型数据库,需要重点加大此内存缓存,但是一般不会超过GB。
对于经常被修改的数据,缓存会立马失效。
我们可以实用内存数据库(redis、memecache),替代他的功能。
b 存储引擎层(innodb基础优化参数)?
default-storage-engine
innodb_buffer_pool_size # 没有固定大小,50%测试值,看看情况再微调。但是尽量设置不要超过物理内存70%
innodb_file_per_table=(1,0)
innodb_flush_log_at_trx_commit=(0,1,2) # 1是最安全的,0是性能最高,2折中
binlog_sync
Innodb_flush_method=(O_DIRECT, fdatasync)
innodb_log_buffer_size # 100M以下
innodb_log_file_size # 100M 以下
innodb_log_files_in_group # 5个成员以下,一般2-3个够用(iblogfile0-N)
innodb_max_dirty_pages_pct # 达到百分之75的时候刷写 内存脏页到磁盘。
log_bin
max_binlog_cache_size # 可以不设置
max_binlog_size # 可以不设置
innodb_additional_mem_pool_size #小于2G内存的机器,推荐值是20M。32G内存以上100M
谈谈项目中常用的MySQL优化方法,共19条,具体如下:
1、EXPLAIN
做MySQL优化,我们要善用EXPLAIN查看SQL执行计划。
下面来个简单的示例,标注(1、2、3、4、5)我们要重点关注的数据:
type列,连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。
key列,使用到的索引名。如果没有选择索引,值是NULL。可以采取强制索引方式。
key_len列,索引长度。
rows列,扫描行数。该值是个预估值。
extra列,详细说明。注意,常见的不太友好的值,如下:Using filesort,Using temporary。
2、SQL语句中IN包含的值不应过多
MySQL对于IN做了相应的优化,即将IN中的常量全部存储在一个数组里面,而且这个数组是排好序的。但是如果数值较多,产生的消耗也是比较大的。再例如:select id from t where num in(1,2,3) 对于连续的数值,能用between就不要用in了;再或者使用连接来替换。
3、SELECT语句务必指明字段名称
SELECT*增加很多不必要的消耗(CPU、IO、内存、网络带宽);增加了使用覆盖索引的可能性;当表结构发生改变时,前断也需要更新。所以要求直接在select后面接上字段名。
4、当只需要一条数据的时候,使用limit 1
这是为了使EXPLAIN中type列达到const类型
5、如果排序字段没有用到索引,就尽量少排序
6、如果限制条件中其他字段没有索引,尽量少用or
or两边的字段中,如果有一个不是索引字段,而其他条件也不是索引字段,会造成该查询不走索引的情况。很多时候使用union all或者是union(必要的时候)的方式来代替“or”会得到更好的效果。
7、尽量用union all代替union
union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的CPU运算,加大资源消耗及延迟。当然,union all的前提条件是两个结果集没有重复数据。
8、不使用ORDER BY RAND()
select id from `dynamic` order by rand() limit 1000;
上面的SQL语句,可优化为:
select id from `dynamic` t1 join (select rand() * (select max(id) from `dynamic`) as nid) t2 on t1.id > t2.nidlimit 1000;
9、区分in和exists、not in和not exists
select * from 表A where id in (select id from 表B)
上面SQL语句相当于
select * from 表A where exists(select * from 表B where 表B.id=表A.id)
区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询。所以IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。
关于not in和not exists,推荐使用not exists,不仅仅是效率问题,not in可能存在逻辑问题。如何高效的写出一个替代not exists的SQL语句?
原SQL语句:
select colname … from A表 where a.id not in (select b.id from B表)
高效的SQL语句:
select colname … from A表 Left join B表 on where a.id = b.id where b.id is null
取出的结果集如下图表示,A表不在B表中的数据:
10、使用合理的分页方式以提高分页的效率
select id,name from product limit 866613, 20
使用上述SQL语句做分页的时候,可能有人会发现,随着表数据量的增加,直接使用limit分页查询会越来越慢。
优化的方法如下:可以取前一页的最大行数的id,然后根据这个最大的id来限制下一页的起点。比如此列中,上一页最大的id是866612。SQL可以采用如下的写法:
select id,name from product where id> 866612 limit 20
11、分段查询
一些用户选择页面中,可能一些用户选择的时间范围过大,造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序,分段进行查询,循环遍历,将结果合并处理进行展示。
如下图这个SQL语句,扫描的行数成百万级以上的时候就可以使用分段查询:
12、避免在where子句中对字段进行null值判断
对于null的判断会导致引擎放弃使用索引而进行全表扫描。
13、不建议使用%前缀模糊查询
例如LIKE“%name”或者LIKE“%name%”,这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。
那如何查询%name%?
如下图所示,虽然给secret字段添加了索引,但在explain结果并没有使用:
那么如何解决这个问题呢,答案:使用全文索引。
在我们查询中经常会用到select id,fnum,fdst from dynamic_201606 where user_name like '%zhangsan%'; 。这样的语句,普通索引是无法满足查询需求的。庆幸的是在MySQL中,有全文索引来帮助我们。
创建全文索引的SQL语法是:
ALTER TABLE `dynamic_201606` ADD FULLTEXT INDEX `idx_user_name` (`user_name`);
使用全文索引的SQL语句是:
select id,fnum,fdst from dynamic_201606 where match(user_name) against('zhangsan' in boolean mode);
注意:在需要创建全文索引之前,请联系DBA确定能否创建。同时需要注意的是查询语句的写法与普通索引的区别。
14、避免在where子句中对字段进行表达式操作
比如:
select user_id,user_project from user_base where age*2=36;
中对字段就行了算术运算,这会造成引擎放弃使用索引,建议改成:
select user_id,user_project from user_base where age=36/2;
15、避免隐式类型转换
where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换,建议先确定where中的参数类型。
16、对于联合索引来说,要遵守最左前缀法则
举列来说索引含有字段id、name、school,可以直接用id字段,也可以id、name这样的顺序,但是name;school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序,常用的查询字段放在最前面。
17、必要时可以使用force index来强制查询走某个索引
有的时候MySQL优化器采取它认为合适的索引来检索SQL语句,但是可能它所采用的索引并不是我们想要的。这时就可以采用forceindex来强制优化器使用我们制定的索引。
18、注意范围查询语句
对于联合索引来说,如果存在范围查询,比如between、>、<等条件时,会造成后面的索引字段失效。
19、关于JOIN优化
LEFT JOIN A表为驱动表,INNER JOIN MySQL会自动找出那个数据少的表作用驱动表,RIGHT JOIN B表为驱动表。
注意:
1)MySQL中没有full join,可以用以下方式来解决:
select * from A left join B on B.name = A.namewhere B.name is nullunion allselect * from B;
2)尽量使用inner join,避免left join:
参与联合查询的表至少为2张表,一般都存在大小之分。如果连接方式是inner join,在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表,但是left join在驱动表的选择上遵循的是左边驱动右边的原则,即left join左边的表名为驱动表。
3)合理利用索引:
被驱动表的索引字段作为on的限制字段。
4)利用小表去驱动大表:
从原理图能够直观的看出如果能够减少驱动表的话,减少嵌套循环中的循环次数,以减少 IO总量及CPU运算的次数。
5)巧用STRAIGHT_JOIN:
inner join是由MySQL选择驱动表,但是有些特殊情况需要选择另个表作为驱动表,比如有group by、order by等「Using filesort」、「Using temporary」时。STRAIGHT_JOIN来强制连接顺序,在STRAIGHT_JOIN左边的表名就是驱动表,右边则是被驱动表。在使用STRAIGHT_JOIN有个前提条件是该查询是内连接,也就是inner join。其他链接不推荐使用STRAIGHT_JOIN,否则可能造成查询结果不准确。
这个方式有时能减少3倍的时间。
以上19条MySQL优化方法希望对大家有所帮助!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31555484/viewspace-2565387/,如需转载,请注明出处,否则将追究法律责任。
-
-
CDN HTTPS 解决方案及优化实践
2017-03-30 09:38:27阿里云 CDN 承担了支付宝 2017 年春节五福开奖的重要保障工作。五福开奖的峰值超过以往,而且...本次演讲将会介绍 CDN HTTPS 的解决方案,包括 HTTPS 的相关基础、CDN 中 HTTPS 的架构、以及相关的优化实践等方面内容。 -
高中生物全国名校名卷优化重组专题6光合作用和呼吸作用的关系无答案PDF
2021-09-10 03:29:50高中生物全国名校名卷优化重组专题6光合作用和呼吸作用的关系无答案PDF -
高中生物全国名校名卷优化重组专题12遗传定律伴性遗传常见题型综合无答案PDF
2021-09-10 03:26:10高中生物全国名校名卷优化重组专题12遗传定律伴性遗传常见题型综合无答案PDF -
数据库SQL优化大总结之 百万级数据库优化方案
2016-06-23 09:43:50网上关于SQL优化的教程很多,但是比较杂乱。近日有空整理了一下,写出来跟大家分享一下,其中有错误...1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 w
网上关于SQL优化的教程很多,但是比较杂乱。近日有空整理了一下,写出来跟大家分享一下,其中有错误和不足的地方,还请大家纠正补充。
这篇文章我花费了大量的时间查找资料、修改、排版,希望大家阅读之后,感觉好的话推荐给更多的人,让更多的人看到、纠正以及补充。
一、百万级数据库优化方案
1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num is null
最好不要给数据库留NULL,尽可能的使用 NOT NULL填充数据库.
备注、描述、评论之类的可以设置为 NULL,其他的,最好不要使用NULL。
不要以为 NULL 不需要空间,比如:char(100) 型,在字段建立时,空间就固定了, 不管是否插入值(NULL也包含在内),都是占用 100个字符的空间的,如果是varchar这样的变长字段, null 不占用空间。
可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:select id from t where num = 0
3.应尽量避免在 where 子句中使用 != 或 <> 操作符,否则将引擎放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描,如:select id from t where num=10 or Name = 'admin'
可以这样查询:
select id from t where num = 10 union all select id from t where Name = 'admin'
5.in 和 not in 也要慎用,否则会导致全表扫描,如:select id from t where num in(1,2,3)
对于连续的数值,能用 between 就不要用 in 了:
select id from t where num between 1 and 3
很多时候用 exists 代替 in 是一个好的选择:
select num from a where num in(select num from b)
用下面的语句替换:
select num from a where exists(select 1 from b where num=a.num)
6.下面的查询也将导致全表扫描:
select id from t where name like ‘%abc%’
若要提高效率,可以考虑全文检索。
7.如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然 而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:select id from t where num = @num
可以改为强制查询使用索引:
select id from t with(index(索引名)) where num = @num
.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where num/2 = 100
应改为:
select id from t where num = 100*2
9.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:select id from t where substring(name,1,3) = ’abc’ -–name以abc开头的id select id from t where datediff(day,createdate,’2005-11-30′) = 0 -–‘2005-11-30’ --生成的id
应改为:
select id from t where name like 'abc%' select id from t where createdate >= '2005-11-30' and createdate < '2005-12-1'
10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。
11.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。
12.不要写一些没有意义的查询,如需要生成一个空表结构:select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:
create table #t(…)13.Update 语句,如果只更改1、2个字段,不要Update全部字段,否则频繁调用会引起明显的性能消耗,同时带来大量日志。
14.对于多张大数据量(这里几百条就算大了)的表JOIN,要先分页再JOIN,否则逻辑读会很高,性能很差。
15.select count(*) from table;这样不带任何条件的count会引起全表扫描,并且没有任何业务意义,是一定要杜绝的。
16.索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有 必要。
17.应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。
18.尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。
19.尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。
20.任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。
21.尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。
22. 避免频繁创建和删除临时表,以减少系统表资源的消耗。临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件, 最好使用导出表。
23.在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。
24.如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。
25.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。
26.使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。
27.与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。
28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
29.尽量避免大事务操作,提高系统并发能力。
30.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。实际案例分析:拆分大的 DELETE 或INSERT 语句,批量提交SQL语句
如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询,你需要非常小心,要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的,表一锁住了,别的操作都进不来了。
Apache 会有很多的子进程或线程。所以,其工作起来相当有效率,而我们的服务器也不希望有太多的子进程,线程和数据库链接,这是极大的占服务器资源的事情,尤其是内存。
如果你把你的表锁上一段时间,比如30秒钟,那么对于一个有很高访问量的站点来说,这30秒所积累的访问进程/线程,数据库链接,打开的文件数,可能不仅仅会让你的WEB服务崩溃,还可能会让你的整台服务器马上挂了。
所以,如果你有一个大的处理,你一定把其拆分,使用 LIMIT oracle(rownum),sqlserver(top)条件是一个好的方法。下面是一个mysql示例:
while(1){ //每次只做1000条 mysql_query(“delete from logs where log_date <= ’2012-11-01’ limit 1000”); if(mysql_affected_rows() == 0){ //删除完成,退出! break; } //每次暂停一段时间,释放表让其他进程/线程访问。 usleep(50000) }
二、数据库访问性能优化
特别说明:
1、 本文只是面对数据库应用开发的程序员,不适合专业DBA,DBA在数据库性能优化方面需要了解更多的知识;
2、 本文许多示例及概念是基于Oracle数据库描述,对于其它关系型数据库也可以参考,但许多观点不适合于KV数据库或内存数据库或者是基于SSD技术的数据库;
3、 本文未深入数据库优化中最核心的执行计划分析技术。
读者对像:
开发人员:如果你是做数据库开发,那本文的内容非常适合,因为本文是从程序员的角度来谈数据库性能优化。
架构师:如果你已经是数据库应用的架构师,那本文的知识你应该清楚90%,否则你可能是一个喜欢折腾的架构师。
DBA(数据库管理员):大型数据库优化的知识非常复杂,本文只是从程序员的角度来谈性能优化,DBA除了需要了解这些知识外,还需要深入数据库的内部体系架构来解决问题。
在网上有很多文章介绍数据库优化知识,但是大部份文章只是对某个一个方面进行说明,而对于我们程序员来说这种介绍并不能很好的掌握优化知识,因为很多介绍只是对一些特定的场景优化的,所以反而有时会产生误导或让程序员感觉不明白其中的奥妙而对数据库优化感觉很神秘。
很多程序员总是问如何学习数据库优化,有没有好的教材之类的问题。在书店也看到了许多数据库优化的专业书籍,但是感觉更多是面向DBA或者是PL/SQL开发方面的知识,个人感觉不太适合普通程序员。而要想做到数据库优化的高手,不是花几周,几个月就能达到的,这并不是因为数据库优化有多高深,而是因为要做好优化一方面需要有非常好的技术功底,对操作系统、存储硬件网络、数据库原理等方面有比较扎实的基础知识,另一方面是需要花大量时间对特定的数据库进行实践测试与总结。
作为一个程序员,我们也许不清楚线上正式的服务器硬件配置,我们不可能像DBA那样专业的对数据库进行各种实践测试与总结,但我们都应该非常了解我们SQL的业务逻辑,我们清楚SQL中访问表及字段的数据情况,我们其实只关心我们的SQL是否能尽快返回结果。那程序员如何利用已知的知识进行数据库优化?如何能快速定位SQL性能问题并找到正确的优化方向?
面对这些问题,笔者总结了一些面向程序员的基本优化法则,本文将结合实例来坦述数据库开发的优化知识。
要正确的优化SQL,我们需要快速定位能性的瓶颈点,也就是说快速找到我们SQL主要的开销在哪里?而大多数情况性能最慢的设备会是瓶颈点,如下载时网络速度可能会是瓶颈点,本地复制文件时硬盘可能会是瓶颈点,为什么这些一般的工作我们能快速确认瓶颈点呢,因为我们对这些慢速设备的性能数据有一些基本的认识,如网络带宽是2Mbps,硬盘是每分钟7200转等等。因此,为了快速找到SQL的性能瓶颈点,我们也需要了解我们计算机系统的硬件基本性能指标,下图展示的当前主流计算机性能指标数据。
从图上可以看到基本上每种设备都有两个指标:
延时(响应时间):表示硬件的突发处理能力;
带宽(吞吐量):代表硬件持续处理能力。
从上图可以看出,计算机系统硬件性能从高到代依次为:
CPU——Cache(L1-L2-L3)——内存——SSD硬盘——网络——硬盘
由于SSD硬盘还处于快速发展阶段,所以本文的内容不涉及SSD相关应用系统。
根据数据库知识,我们可以列出每种硬件主要的工作内容:
CPU及内存:缓存数据访问、比较、排序、事务检测、SQL解析、函数或逻辑运算;
网络:结果数据传输、SQL请求、远程数据库访问(dblink);
硬盘:数据访问、数据写入、日志记录、大数据量排序、大表连接。
根据当前计算机硬件的基本性能指标及其在数据库中主要操作内容,可以整理出如下图所示的性能基本优化法则:
这个优化法则归纳为5个层次:
1、 减少数据访问(减少磁盘访问)
2、 返回更少数据(减少网络传输或磁盘访问)
3、 减少交互次数(减少网络传输)
4、 减少服务器CPU开销(减少CPU及内存开销)
5、 利用更多资源(增加资源)
由于每一层优化法则都是解决其对应硬件的性能问题,所以带来的性能提升比例也不一样。传统数据库系统设计是也是尽可能对低速设备提供优化方法,因此针对低速设备问题的可优化手段也更多,优化成本也更低。我们任何一个SQL的性能优化都应该按这个规则由上到下来诊断问题并提出解决方案,而不应该首先想到的是增加资源解决问题。
以下是每个优化法则层级对应优化效果及成本经验参考:
优化法则
性能提升效果
优化成本
减少数据访问
1~1000
低
返回更少数据
1~100
低
减少交互次数
1~20
低
减少服务器CPU开销
1~5
低
利用更多资源
@~10
高
接下来,我们针对5种优化法则列举常用的优化手段并结合实例分析。
接下来,我们针对5种优化法则列举常用的优化手段并结合实例分析。
二、oracle数据库两个基本概念
数据块是数据库中数据在磁盘中存储的最小单位,也是一次IO访问的最小单位,一个数据块通常可以存储多条记录,数据块大小是DBA在创建数据库或表空间时指定,可指定为2K、4K、8K、16K或32K字节。下图是一个Oracle数据库典型的物理结构,一个数据库可以包括多个数据文件,一个数据文件内又包含多个数据块;
ROWID是每条记录在数据库中的唯一标识,通过ROWID可以直接定位记录到对应的文件号及数据块位置。ROWID内容包括文件号、对像号、数据块号、记录槽号,如下图所示:
三、数据库访问优化法则详解
减少数据访问
创建并使用正确的索引
数据库索引的原理非常简单,但在复杂的表中真正能正确使用索引的人很少,即使是专业的DBA也不一定能完全做到最优。
索引会大大增加表记录的DML(INSERT,UPDATE,DELETE)开销,正确的索引可以让性能提升100,1000倍以上,不合理的索引也可能会让性能下降100倍,因此在一个表中创建什么样的索引需要平衡各种业务需求。
索引常见问题:
索引有哪些种类?
常见的索引有B-TREE索引、位图索引、全文索引,位图索引一般用于数据仓库应用,全文索引由于使用较少,这里不深入介绍。B-TREE索引包括很多扩展类型,如组合索引、反向索引、函数索引等等,以下是B-TREE索引的简单介绍:
B-TREE索引也称为平衡树索引(Balance Tree),它是一种按字段排好序的树形目录结构,主要用于提升查询性能和唯一约束支持。B-TREE索引的内容包括根节点、分支节点、叶子节点。
叶子节点内容:索引字段内容+表记录ROWID
根节点,分支节点内容:当一个数据块中不能放下所有索引字段数据时,就会形成树形的根节点或分支节点,根节点与分支节点保存了索引树的顺序及各层级间的引用关系。
一个普通的BTREE索引结构示意图如下所示:
如果我们把一个表的内容认为是一本字典,那索引就相当于字典的目录,如下图所示:
图中是一个字典按部首+笔划数的目录,相当于给字典建了一个按部首+笔划的组合索引。
一个表中可以建多个索引,就如一本字典可以建多个目录一样(按拼音、笔划、部首等等)。
一个索引也可以由多个字段组成,称为组合索引,如上图就是一个按部首+笔划的组合目录。
SQL什么条件会使用索引?
当字段上建有索引时,通常以下情况会使用索引:
INDEX_COLUMN = ?
INDEX_COLUMN > ?
INDEX_COLUMN >= ?
INDEX_COLUMN < ?
INDEX_COLUMN <= ?
INDEX_COLUMN between ? and ?
INDEX_COLUMN in (?,?,...,?)
INDEX_COLUMN like ?||'%'(后导模糊查询)
T1. INDEX_COLUMN=T2. COLUMN1(两个表通过索引字段关联)
SQL什么条件不会使用索引?
查询条件
不能使用索引原因
INDEX_COLUMN <> ?
INDEX_COLUMN not in (?,?,...,?)
不等于操作不能使用索引
function(INDEX_COLUMN) = ?
INDEX_COLUMN + 1 = ?
INDEX_COLUMN || 'a' = ?
经过普通运算或函数运算后的索引字段不能使用索引
INDEX_COLUMN like '%'||?
INDEX_COLUMN like '%'||?||'%'
含前导模糊查询的Like语法不能使用索引
INDEX_COLUMN is null
B-TREE索引里不保存字段为NULL值记录,因此IS NULL不能使用索引
NUMBER_INDEX_COLUMN='12345'
CHAR_INDEX_COLUMN=12345
Oracle在做数值比较时需要将两边的数据转换成同一种数据类型,如果两边数据类型不同时会对字段值隐式转换,相当于加了一层函数处理,所以不能使用索引。
a.INDEX_COLUMN=a.COLUMN_1
给索引查询的值应是已知数据,不能是未知字段值。
注:
经过函数运算字段的字段要使用可以使用函数索引,这种需求建议与DBA沟通。
有时候我们会使用多个字段的组合索引,如果查询条件中第一个字段不能使用索引,那整个查询也不能使用索引
如:我们company表建了一个id+name的组合索引,以下SQL是不能使用索引的
Select * from company where name=?
Oracle9i后引入了一种index skip scan的索引方式来解决类似的问题,但是通过index skip scan提高性能的条件比较特殊,使用不好反而性能会更差。
我们一般在什么字段上建索引?
这是一个非常复杂的话题,需要对业务及数据充分分析后再能得出结果。主键及外键通常都要有索引,其它需要建索引的字段应满足以下条件:
1、字段出现在查询条件中,并且查询条件可以使用索引;
2、语句执行频率高,一天会有几千次以上;
3、通过字段条件可筛选的记录集很小,那数据筛选比例是多少才适合?
这个没有固定值,需要根据表数据量来评估,以下是经验公式,可用于快速评估:
小表(记录数小于10000行的表):筛选比例<10%;
大表:(筛选返回记录数)<(表总记录数*单条记录长度)/10000/16
单条记录长度≈字段平均内容长度之和+字段数*2
以下是一些字段是否需要建B-TREE索引的经验分类:
字段类型
常见字段名
需要建索引的字段
主键
ID,PK
外键
PRODUCT_ID,COMPANY_ID,MEMBER_ID,ORDER_ID,TRADE_ID,PAY_ID
有对像或身份标识意义字段
HASH_CODE,USERNAME,IDCARD_NO,EMAIL,TEL_NO,IM_NO
索引慎用字段,需要进行数据分布及使用场景详细评估
日期
GMT_CREATE,GMT_MODIFIED
年月
YEAR,MONTH
状态标志
PRODUCT_STATUS,ORDER_STATUS,IS_DELETE,VIP_FLAG
类型
ORDER_TYPE,IMAGE_TYPE,GENDER,CURRENCY_TYPE
区域
COUNTRY,PROVINCE,CITY
操作人员
CREATOR,AUDITOR
数值
LEVEL,AMOUNT,SCORE
长字符
ADDRESS,COMPANY_NAME,SUMMARY,SUBJECT
不适合建索引的字段
描述备注
DESCRIPTION,REMARK,MEMO,DETAIL
大字段
FILE_CONTENT,EMAIL_CONTENT
如何知道SQL是否使用了正确的索引?
简单SQL可以根据索引使用语法规则判断,复杂的SQL不好办,判断SQL的响应时间是一种策略,但是这会受到数据量、主机负载及缓存等因素的影响,有时数据全在缓存里,可能全表访问的时间比索引访问时间还少。要准确知道索引是否正确使用,需要到数据库中查看SQL真实的执行计划,这个话题比较复杂,详见SQL执行计划专题介绍。
索引对DML(INSERT,UPDATE,DELETE)附加的开销有多少?
这个没有固定的比例,与每个表记录的大小及索引字段大小密切相关,以下是一个普通表测试数据,仅供参考:
索引对于Insert性能降低56%
索引对于Update性能降低47%
索引对于Delete性能降低29%
因此对于写IO压力比较大的系统,表的索引需要仔细评估必要性,另外索引也会占用一定的存储空间。
1.2、只通过索引访问数据
有些时候,我们只是访问表中的几个字段,并且字段内容较少,我们可以为这几个字段单独建立一个组合索引,这样就可以直接只通过访问索引就能得到数据,一般索引占用的磁盘空间比表小很多,所以这种方式可以大大减少磁盘IO开销。
如:select id,name from company where type='2';
如果这个SQL经常使用,我们可以在type,id,name上创建组合索引
create index my_comb_index on company(type,id,name);
有了这个组合索引后,SQL就可以直接通过my_comb_index索引返回数据,不需要访问company表。
还是拿字典举例:有一个需求,需要查询一本汉语字典中所有汉字的个数,如果我们的字典没有目录索引,那我们只能从字典内容里一个一个字计数,最后返回结果。如果我们有一个拼音目录,那就可以只访问拼音目录的汉字进行计数。如果一本字典有1000页,拼音目录有20页,那我们的数据访问成本相当于全表访问的50分之一。
切记,性能优化是无止境的,当性能可以满足需求时即可,不要过度优化。在实际数据库中我们不可能把每个SQL请求的字段都建在索引里,所以这种只通过索引访问数据的方法一般只用于核心应用,也就是那种对核心表访问量最高且查询字段数据量很少的查询。
1.3、优化SQL执行计划
SQL执行计划是关系型数据库最核心的技术之一,它表示SQL执行时的数据访问算法。由于业务需求越来越复杂,表数据量也越来越大,程序员越来越懒惰,SQL也需要支持非常复杂的业务逻辑,但SQL的性能还需要提高,因此,优秀的关系型数据库除了需要支持复杂的SQL语法及更多函数外,还需要有一套优秀的算法库来提高SQL性能。
目前ORACLE有SQL执行计划的算法约300种,而且一直在增加,所以SQL执行计划是一个非常复杂的课题,一个普通DBA能掌握50种就很不错了,就算是资深DBA也不可能把每个执行计划的算法描述清楚。虽然有这么多种算法,但并不表示我们无法优化执行计划,因为我们常用的SQL执行计划算法也就十几个,如果一个程序员能把这十几个算法搞清楚,那就掌握了80%的SQL执行计划调优知识。
由于篇幅的原因,SQL执行计划需要专题介绍,在这里就不多说了。
2、返回更少的数据
2.1、数据分页处理
一般数据分页方式有:
2.1.1、客户端(应用程序或浏览器)分页
将数据从应用服务器全部下载到本地应用程序或浏览器,在应用程序或浏览器内部通过本地代码进行分页处理
优点:编码简单,减少客户端与应用服务器网络交互次数
缺点:首次交互时间长,占用客户端内存
适应场景:客户端与应用服务器网络延时较大,但要求后续操作流畅,如手机GPRS,超远程访问(跨国)等等。
2.1.2、应用服务器分页
将数据从数据库服务器全部下载到应用服务器,在应用服务器内部再进行数据筛选。以下是一个应用服务器端Java程序分页的示例:
List list=executeQuery(“select * from employee order by id”);
Int count= list.size();
List subList= list.subList(10, 20);
优点:编码简单,只需要一次SQL交互,总数据与分页数据差不多时性能较好。
缺点:总数据量较多时性能较差。
适应场景:数据库系统不支持分页处理,数据量较小并且可控。
2.1.3、数据库SQL分页
采用数据库SQL分页需要两次SQL完成
一个SQL计算总数量
一个SQL返回分页后的数据
优点:性能好
缺点:编码复杂,各种数据库语法不同,需要两次SQL交互。
oracle数据库一般采用rownum来进行分页,常用分页语法有如下两种:
直接通过rownum分页:
select * from (
select a.*,rownum rn from
(select * from product a where company_id=? order by status) a
where rownum<=20)
where rn>10;
数据访问开销=索引IO+索引全部记录结果对应的表数据IO
采用rowid分页语法
优化原理是通过纯索引找出分页记录的ROWID,再通过ROWID回表返回数据,要求内层查询和排序字段全在索引里。
create index myindex on product(company_id,status);
select b.* from (
select * from (
select a.*,rownum rn from
(select rowid rid,status from product a where company_id=? order by status) a
where rownum<=20)
where rn>10) a, product b
where a.rid=b.rowid;
数据访问开销=索引IO+索引分页结果对应的表数据IO
实例:
一个公司产品有1000条记录,要分页取其中20个产品,假设访问公司索引需要50个IO,2条记录需要1个表数据IO。
那么按第一种ROWNUM分页写法,需要550(50+1000/2)个IO,按第二种ROWID分页写法,只需要60个IO(50+20/2);
2.2、只返回需要的字段
通过去除不必要的返回字段可以提高性能,例:
调整前:select * from product where company_id=?;
调整后:select id,name from product where company_id=?;
优点:
1、减少数据在网络上传输开销
2、减少服务器数据处理开销
3、减少客户端内存占用
4、字段变更时提前发现问题,减少程序BUG
5、如果访问的所有字段刚好在一个索引里面,则可以使用纯索引访问提高性能。
缺点:增加编码工作量
由于会增加一些编码工作量,所以一般需求通过开发规范来要求程序员这么做,否则等项目上线后再整改工作量更大。
如果你的查询表中有大字段或内容较多的字段,如备注信息、文件内容等等,那在查询表时一定要注意这方面的问题,否则可能会带来严重的性能问题。如果表经常要查询并且请求大内容字段的概率很低,我们可以采用分表处理,将一个大表分拆成两个一对一的关系表,将不常用的大内容字段放在一张单独的表中。如一张存储上传文件的表:
T_FILE(ID,FILE_NAME,FILE_SIZE,FILE_TYPE,FILE_CONTENT)
我们可以分拆成两张一对一的关系表:
T_FILE(ID,FILE_NAME,FILE_SIZE,FILE_TYPE)
T_FILECONTENT(ID, FILE_CONTENT)
通过这种分拆,可以大大提少T_FILE表的单条记录及总大小,这样在查询T_FILE时性能会更好,当需要查询FILE_CONTENT字段内容时再访问T_FILECONTENT表。
3、减少交互次数
3.1、batch DML
数据库访问框架一般都提供了批量提交的接口,jdbc支持batch的提交处理方法,当你一次性要往一个表中插入1000万条数据时,如果采用普通的executeUpdate处理,那么和服务器交互次数为1000万次,按每秒钟可以向数据库服务器提交10000次估算,要完成所有工作需要1000秒。如果采用批量提交模式,1000条提交一次,那么和服务器交互次数为1万次,交互次数大大减少。采用batch操作一般不会减少很多数据库服务器的物理IO,但是会大大减少客户端与服务端的交互次数,从而减少了多次发起的网络延时开销,同时也会降低数据库的CPU开销。
假设要向一个普通表插入1000万数据,每条记录大小为1K字节,表上没有任何索引,客户端与数据库服务器网络是100Mbps,以下是根据现在一般计算机能力估算的各种batch大小性能对比值:
单位:ms
No batch
Batch=10
Batch=100
Batch=1000
Batch=10000
服务器事务处理时间
0.1
0.1
0.1
0.1
0.1
服务器IO处理时间
0.02
0.2
2
20
200
网络交互发起时间
0.1
0.1
0.1
0.1
0.1
网络数据传输时间
0.01
0.1
1
10
100
小计
0.23
0.5
3.2
30.2
300.2
平均每条记录处理时间
0.23
0.05
0.032
0.0302
0.03002
从上可以看出,Insert操作加大Batch可以对性能提高近8倍性能,一般根据主键的Update或Delete操作也可能提高2-3倍性能,但不如Insert明显,因为Update及Delete操作可能有比较大的开销在物理IO访问。以上仅是理论计算值,实际情况需要根据具体环境测量。
3.2、In List
很多时候我们需要按一些ID查询数据库记录,我们可以采用一个ID一个请求发给数据库,如下所示:
for :var in ids[] do begin
select * from mytable where id=:var;
end;
我们也可以做一个小的优化, 如下所示,用ID INLIST的这种方式写SQL:
select * from mytable where id in(:id1,id2,...,idn);
通过这样处理可以大大减少SQL请求的数量,从而提高性能。那如果有10000个ID,那是不是全部放在一条SQL里处理呢?答案肯定是否定的。首先大部份数据库都会有SQL长度和IN里个数的限制,如ORACLE的IN里就不允许超过1000个值。
另外当前数据库一般都是采用基于成本的优化规则,当IN数量达到一定值时有可能改变SQL执行计划,从索引访问变成全表访问,这将使性能急剧变化。随着SQL中IN的里面的值个数增加,SQL的执行计划会更复杂,占用的内存将会变大,这将会增加服务器CPU及内存成本。
评估在IN里面一次放多少个值还需要考虑应用服务器本地内存的开销,有并发访问时要计算本地数据使用周期内的并发上限,否则可能会导致内存溢出。
综合考虑,一般IN里面的值个数超过20个以后性能基本没什么太大变化,也特别说明不要超过100,超过后可能会引起执行计划的不稳定性及增加数据库CPU及内存成本,这个需要专业DBA评估。
3.3、设置Fetch Size
当我们采用select从数据库查询数据时,数据默认并不是一条一条返回给客户端的,也不是一次全部返回客户端的,而是根据客户端fetch_size参数处理,每次只返回fetch_size条记录,当客户端游标遍历到尾部时再从服务端取数据,直到最后全部传送完成。所以如果我们要从服务端一次取大量数据时,可以加大fetch_size,这样可以减少结果数据传输的交互次数及服务器数据准备时间,提高性能。
以下是jdbc测试的代码,采用本地数据库,表缓存在数据库CACHE中,因此没有网络连接及磁盘IO开销,客户端只遍历游标,不做任何处理,这样更能体现fetch参数的影响:
String vsql ="select * from t_employee";
PreparedStatement pstmt = conn.prepareStatement(vsql,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
pstmt.setFetchSize(1000);
ResultSet rs = pstmt.executeQuery(vsql);
int cnt = rs.getMetaData().getColumnCount();
Object o;
while (rs.next()) {
for (int i = 1; i <= cnt; i++) {
o = rs.getObject(i);
}
}
测试示例中的employee表有100000条记录,每条记录平均长度135字节
以下是测试结果,对每种fetchsize测试5次再取平均值:
fetchsize
elapse_time(s)
1
20.516
2
11.34
4
6.894
8
4.65
16
3.584
32
2.865
64
2.656
128
2.44
256
2.765
512
3.075
1024
2.862
2048
2.722
4096
2.681
8192
2.715
Oracle jdbc fetchsize默认值为10,由上测试可以看出fetchsize对性能影响还是比较大的,但是当fetchsize大于100时就基本上没有影响了。fetchsize并不会存在一个最优的固定值,因为整体性能与记录集大小及硬件平台有关。根据测试结果建议当一次性要取大量数据时这个值设置为100左右,不要小于40。注意,fetchsize不能设置太大,如果一次取出的数据大于JVM的内存会导致内存溢出,所以建议不要超过1000,太大了也没什么性能提高,反而可能会增加内存溢出的危险。
注:图中fetchsize在128以后会有一些小的波动,这并不是测试误差,而是由于resultset填充到具体对像时间不同的原因,由于resultset已经到本地内存里了,所以估计是由于CPU的L1,L2 Cache命中率变化造成,由于变化不大,所以笔者也未深入分析原因。
iBatis的SqlMapping配置文件可以对每个SQL语句指定fetchsize大小,如下所示:
<select id="getAllProduct" resultMap="HashMap" fetchSize="1000">
select * from employee
</select>
3.4、使用存储过程
大型数据库一般都支持存储过程,合理的利用存储过程也可以提高系统性能。如你有一个业务需要将A表的数据做一些加工然后更新到B表中,但是又不可能一条SQL完成,这时你需要如下3步操作:
a:将A表数据全部取出到客户端;
b:计算出要更新的数据;
c:将计算结果更新到B表。
如果采用存储过程你可以将整个业务逻辑封装在存储过程里,然后在客户端直接调用存储过程处理,这样可以减少网络交互的成本。
当然,存储过程也并不是十全十美,存储过程有以下缺点:
a、不可移植性,每种数据库的内部编程语法都不太相同,当你的系统需要兼容多种数据库时最好不要用存储过程。
b、学习成本高,DBA一般都擅长写存储过程,但并不是每个程序员都能写好存储过程,除非你的团队有较多的开发人员熟悉写存储过程,否则后期系统维护会产生问题。
c、业务逻辑多处存在,采用存储过程后也就意味着你的系统有一些业务逻辑不是在应用程序里处理,这种架构会增加一些系统维护和调试成本。
d、存储过程和常用应用程序语言不一样,它支持的函数及语法有可能不能满足需求,有些逻辑就只能通过应用程序处理。
e、如果存储过程中有复杂运算的话,会增加一些数据库服务端的处理成本,对于集中式数据库可能会导致系统可扩展性问题。
f、为了提高性能,数据库会把存储过程代码编译成中间运行代码(类似于java的class文件),所以更像静态语言。当存储过程引用的对像(表、视图等等)结构改变后,存储过程需要重新编译才能生效,在24*7高并发应用场景,一般都是在线变更结构的,所以在变更的瞬间要同时编译存储过程,这可能会导致数据库瞬间压力上升引起故障(Oracle数据库就存在这样的问题)。
个人观点:普通业务逻辑尽量不要使用存储过程,定时性的ETL任务或报表统计函数可以根据团队资源情况采用存储过程处理。
3.5、优化业务逻辑
要通过优化业务逻辑来提高性能是比较困难的,这需要程序员对所访问的数据及业务流程非常清楚。
举一个案例:
某移动公司推出优惠套参,活动对像为VIP会员并且2010年1,2,3月平均话费20元以上的客户。
那我们的检测逻辑为:
select avg(money) as avg_money from bill where phone_no='13988888888' and date between '201001' and '201003';
select vip_flag from member where phone_no='13988888888';
if avg_money>20 and vip_flag=true then
begin
执行套参();
end;
如果我们修改业务逻辑为:
select avg(money) as avg_money from bill where phone_no='13988888888' and date between '201001' and '201003';
if avg_money>20 then
begin
select vip_flag from member where phone_no='13988888888';
if vip_flag=true then
begin
执行套参();
end;
end;
通过这样可以减少一些判断vip_flag的开销,平均话费20元以下的用户就不需要再检测是否VIP了。
如果程序员分析业务,VIP会员比例为1%,平均话费20元以上的用户比例为90%,那我们改成如下:
select vip_flag from member where phone_no='13988888888';
if vip_flag=true then
begin
select avg(money) as avg_money from bill where phone_no='13988888888' and date between '201001' and '201003';
if avg_money>20 then
begin
执行套参();
end;
end;
这样就只有1%的VIP会员才会做检测平均话费,最终大大减少了SQL的交互次数。
以上只是一个简单的示例,实际的业务总是比这复杂得多,所以一般只是高级程序员更容易做出优化的逻辑,但是我们需要有这样一种成本优化的意识。
3.6、使用ResultSet游标处理记录
现在大部分Java框架都是通过jdbc从数据库取出数据,然后装载到一个list里再处理,list里可能是业务Object,也可能是hashmap。
由于JVM内存一般都小于4G,所以不可能一次通过sql把大量数据装载到list里。为了完成功能,很多程序员喜欢采用分页的方法处理,如一次从数据库取1000条记录,通过多次循环搞定,保证不会引起JVM Out of memory问题。
以下是实现此功能的代码示例,t_employee表有10万条记录,设置分页大小为1000:
d1 = Calendar.getInstance().getTime();
vsql = "select count(*) cnt from t_employee";
pstmt = conn.prepareStatement(vsql);
ResultSet rs = pstmt.executeQuery();
Integer cnt = 0;
while (rs.next()) {
cnt = rs.getInt("cnt");
}
Integer lastid=0;
Integer pagesize=1000;
System.out.println("cnt:" + cnt);
String vsql = "select count(*) cnt from t_employee";
PreparedStatement pstmt = conn.prepareStatement(vsql);
ResultSet rs = pstmt.executeQuery();
Integer cnt = 0;
while (rs.next()) {
cnt = rs.getInt("cnt");
}
Integer lastid = 0;
Integer pagesize = 1000;
System.out.println("cnt:" + cnt);
for (int i = 0; i <= cnt / pagesize; i++) {
vsql = "select * from (select * from t_employee where id>? order by id) where rownum<=?";
pstmt = conn.prepareStatement(vsql);
pstmt.setFetchSize(1000);
pstmt.setInt(1, lastid);
pstmt.setInt(2, pagesize);
rs = pstmt.executeQuery();
int col_cnt = rs.getMetaData().getColumnCount();
Object o;
while (rs.next()) {
for (int j = 1; j <= col_cnt; j++) {
o = rs.getObject(j);
}
lastid = rs.getInt("id");
}
rs.close();
pstmt.close();
}
以上代码实际执行时间为6.516秒
很多持久层框架为了尽量让程序员使用方便,封装了jdbc通过statement执行数据返回到resultset的细节,导致程序员会想采用分页的方式处理问题。实际上如果我们采用jdbc原始的resultset游标处理记录,在resultset循环读取的过程中处理记录,这样就可以一次从数据库取出所有记录。显著提高性能。
这里需要注意的是,采用resultset游标处理记录时,应该将游标的打开方式设置为FORWARD_READONLY模式(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY),否则会把结果缓存在JVM里,造成JVM Out of memory问题。
代码示例:
String vsql ="select * from t_employee";
PreparedStatement pstmt = conn.prepareStatement(vsql,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
pstmt.setFetchSize(100);
ResultSet rs = pstmt.executeQuery(vsql);
int col_cnt = rs.getMetaData().getColumnCount();
Object o;
while (rs.next()) {
for (int j = 1; j <= col_cnt; j++) {
o = rs.getObject(j);
}
}
调整后的代码实际执行时间为3.156秒
从测试结果可以看出性能提高了1倍多,如果采用分页模式数据库每次还需发生磁盘IO的话那性能可以提高更多。
iBatis等持久层框架考虑到会有这种需求,所以也有相应的解决方案,在iBatis里我们不能采用queryForList的方法,而应用该采用queryWithRowHandler加回调事件的方式处理,如下所示:
MyRowHandler myrh=new MyRowHandler();
sqlmap.queryWithRowHandler("getAllEmployee", myrh);
class MyRowHandler implements RowHandler {
public void handleRow(Object o) {
//todo something
}
}
iBatis的queryWithRowHandler很好的封装了resultset遍历的事件处理,效果及性能与resultset遍历一样,也不会产生JVM内存溢出。
4、减少数据库服务器CPU运算
4.1、使用绑定变量
绑定变量是指SQL中对变化的值采用变量参数的形式提交,而不是在SQL中直接拼写对应的值。
非绑定变量写法:Select * from employee where id=1234567
绑定变量写法:
Select * from employee where id=?
Preparestatement.setInt(1,1234567)
Java中Preparestatement就是为处理绑定变量提供的对像,绑定变量有以下优点:
1、防止SQL注入
2、提高SQL可读性
3、提高SQL解析性能,不使用绑定变更我们一般称为硬解析,使用绑定变量我们称为软解析。
第1和第2点很好理解,做编码的人应该都清楚,这里不详细说明。关于第3点,到底能提高多少性能呢,下面举一个例子说明:
假设有这个这样的一个数据库主机:
2个4核CPU
100块磁盘,每个磁盘支持IOPS为160
业务应用的SQL如下:
select * from table where pk=?
这个SQL平均4个IO(3个索引IO+1个数据IO)
IO缓存命中率75%(索引全在内存中,数据需要访问磁盘)
SQL硬解析CPU消耗:1ms (常用经验值)
SQL软解析CPU消耗:0.02ms(常用经验值)
假设CPU每核性能是线性增长,访问内存Cache中的IO时间忽略,要求计算系统对如上应用采用硬解析与采用软解析支持的每秒最大并发数:
是否使用绑定变量
CPU支持最大并发数
磁盘IO支持最大并发数
不使用
2*4*1000=8000
100*160=16000
使用
2*4*1000/0.02=400000
100*160=16000
从以上计算可以看出,不使用绑定变量的系统当并发达到8000时会在CPU上产生瓶颈,当使用绑定变量的系统当并行达到16000时会在磁盘IO上产生瓶颈。所以如果你的系统CPU有瓶颈时请先检查是否存在大量的硬解析操作。
使用绑定变量为何会提高SQL解析性能,这个需要从数据库SQL执行原理说明,一条SQL在Oracle数据库中的执行过程如下图所示:
当一条SQL发送给数据库服务器后,系统首先会将SQL字符串进行hash运算,得到hash值后再从服务器内存里的SQL缓存区中进行检索,如果有相同的SQL字符,并且确认是同一逻辑的SQL语句,则从共享池缓存中取出SQL对应的执行计划,根据执行计划读取数据并返回结果给客户端。
如果在共享池中未发现相同的SQL则根据SQL逻辑生成一条新的执行计划并保存在SQL缓存区中,然后根据执行计划读取数据并返回结果给客户端。
为了更快的检索SQL是否在缓存区中,首先进行的是SQL字符串hash值对比,如果未找到则认为没有缓存,如果存在再进行下一步的准确对比,所以要命中SQL缓存区应保证SQL字符是完全一致,中间有大小写或空格都会认为是不同的SQL。
如果我们不采用绑定变量,采用字符串拼接的模式生成SQL,那么每条SQL都会产生执行计划,这样会导致共享池耗尽,缓存命中率也很低。
一些不使用绑定变量的场景:
a、数据仓库应用,这种应用一般并发不高,但是每个SQL执行时间很长,SQL解析的时间相比SQL执行时间比较小,绑定变量对性能提高不明显。数据仓库一般都是内部分析应用,所以也不太会发生SQL注入的安全问题。
b、数据分布不均匀的特殊逻辑,如产品表,记录有1亿,有一产品状态字段,上面建有索引,有审核中,审核通过,审核未通过3种状态,其中审核通过9500万,审核中1万,审核不通过499万。
要做这样一个查询:
select count(*) from product where status=?
采用绑定变量的话,那么只会有一个执行计划,如果走索引访问,那么对于审核中查询很快,对审核通过和审核不通过会很慢;如果不走索引,那么对于审核中与审核通过和审核不通过时间基本一样;
对于这种情况应该不使用绑定变量,而直接采用字符拼接的方式生成SQL,这样可以为每个SQL生成不同的执行计划,如下所示。
select count(*) from product where status='approved'; //不使用索引
select count(*) from product where status='tbd'; //不使用索引
select count(*) from product where status='auditing';//使用索引
4.2、合理使用排序
Oracle的排序算法一直在优化,但是总体时间复杂度约等于nLog(n)。普通OLTP系统排序操作一般都是在内存里进行的,对于数据库来说是一种CPU的消耗,曾在PC机做过测试,单核普通CPU在1秒钟可以完成100万条记录的全内存排序操作,所以说由于现在CPU的性能增强,对于普通的几十条或上百条记录排序对系统的影响也不会很大。但是当你的记录集增加到上万条以上时,你需要注意是否一定要这么做了,大记录集排序不仅增加了CPU开销,而且可能会由于内存不足发生硬盘排序的现象,当发生硬盘排序时性能会急剧下降,这种需求需要与DBA沟通再决定,取决于你的需求和数据,所以只有你自己最清楚,而不要被别人说排序很慢就吓倒。
以下列出了可能会发生排序操作的SQL语法:
Order by
Group by
Distinct
Exists子查询
Not Exists子查询
In子查询
Not In子查询
Union(并集),Union All也是一种并集操作,但是不会发生排序,如果你确认两个数据集不需要执行去除重复数据操作,那请使用Union All 代替Union。
Minus(差集)
Intersect(交集)
Create Index
Merge Join,这是一种两个表连接的内部算法,执行时会把两个表先排序好再连接,应用于两个大表连接的操作。如果你的两个表连接的条件都是等值运算,那可以采用Hash Join来提高性能,因为Hash Join使用Hash 运算来代替排序的操作。具体原理及设置参考SQL执行计划优化专题。
4.3、减少比较操作
我们SQL的业务逻辑经常会包含一些比较操作,如a=b,a<b之类的操作,对于这些比较操作数据库都体现得很好,但是如果有以下操作,我们需要保持警惕:
Like模糊查询,如下所示:
a like ‘%abc%’
Like模糊查询对于数据库来说不是很擅长,特别是你需要模糊检查的记录有上万条以上时,性能比较糟糕,这种情况一般可以采用专用Search或者采用全文索引方案来提高性能。
不能使用索引定位的大量In List,如下所示:
a in (:1,:2,:3,…,:n) ----n>20
如果这里的a字段不能通过索引比较,那数据库会将字段与in里面的每个值都进行比较运算,如果记录数有上万以上,会明显感觉到SQL的CPU开销加大,这个情况有两种解决方式:
a、 将in列表里面的数据放入一张中间小表,采用两个表Hash Join关联的方式处理;
b、 采用str2varList方法将字段串列表转换一个临时表处理,关于str2varList方法可以在网上直接查询,这里不详细介绍。
以上两种解决方案都需要与中间表Hash Join的方式才能提高性能,如果采用了Nested Loop的连接方式性能会更差。
如果发现我们的系统IO没问题但是CPU负载很高,就有可能是上面的原因,这种情况不太常见,如果遇到了最好能和DBA沟通并确认准确的原因。
4.4、大量复杂运算在客户端处理
什么是复杂运算,一般我认为是一秒钟CPU只能做10万次以内的运算。如含小数的对数及指数运算、三角函数、3DES及BASE64数据加密算法等等。
如果有大量这类函数运算,尽量放在客户端处理,一般CPU每秒中也只能处理1万-10万次这样的函数运算,放在数据库内不利于高并发处理。
5、利用更多的资源
5.1、客户端多进程并行访问
多进程并行访问是指在客户端创建多个进程(线程),每个进程建立一个与数据库的连接,然后同时向数据库提交访问请求。当数据库主机资源有空闲时,我们可以采用客户端多进程并行访问的方法来提高性能。如果数据库主机已经很忙时,采用多进程并行访问性能不会提高,反而可能会更慢。所以使用这种方式最好与DBA或系统管理员进行沟通后再决定是否采用。
例如:
我们有10000个产品ID,现在需要根据ID取出产品的详细信息,如果单线程访问,按每个IO要5ms计算,忽略主机CPU运算及网络传输时间,我们需要50s才能完成任务。如果采用5个并行访问,每个进程访问2000个ID,那么10s就有可能完成任务。
那是不是并行数越多越好呢,开1000个并行是否只要50ms就搞定,答案肯定是否定的,当并行数超过服务器主机资源的上限时性能就不会再提高,如果再增加反而会增加主机的进程间调度成本和进程冲突机率。
以下是一些如何设置并行数的基本建议:
如果瓶颈在服务器主机,但是主机还有空闲资源,那么最大并行数取主机CPU核数和主机提供数据服务的磁盘数两个参数中的最小值,同时要保证主机有资源做其它任务。
如果瓶颈在客户端处理,但是客户端还有空闲资源,那建议不要增加SQL的并行,而是用一个进程取回数据后在客户端起多个进程处理即可,进程数根据客户端CPU核数计算。
如果瓶颈在客户端网络,那建议做数据压缩或者增加多个客户端,采用map reduce的架构处理。
如果瓶颈在服务器网络,那需要增加服务器的网络带宽或者在服务端将数据压缩后再处理了。
5.2、数据库并行处理
数据库并行处理是指客户端一条SQL的请求,数据库内部自动分解成多个进程并行处理,如下图所示:
并不是所有的SQL都可以使用并行处理,一般只有对表或索引进行全部访问时才可以使用并行。数据库表默认是不打开并行访问,所以需要指定SQL并行的提示,如下所示:
select /*+parallel(a,4)*/ * from employee;
并行的优点:
使用多进程处理,充分利用数据库主机资源(CPU,IO),提高性能。
并行的缺点:
1、单个会话占用大量资源,影响其它会话,所以只适合在主机负载低时期使用;
2、只能采用直接IO访问,不能利用缓存数据,所以执行前会触发将脏缓存数据写入磁盘操作。
注:
1、并行处理在OLTP类系统中慎用,使用不当会导致一个会话把主机资源全部占用,而正常事务得不到及时响应,所以一般只是用于数据仓库平台。
2、一般对于百万级记录以下的小表采用并行访问性能并不能提高,反而可能会让性能更差。
参考文章:http://www.cnblogs.com/easypass/archive/2010/12/08/1900127.html
http://www.cnblogs.com/yunfeifei/p/3850440.html
-
mysql 百万级数据库优化方案
2018-06-21 14:26:45https://blog.csdn.net/Kaitiren/article/details/80307828一、百万级数据库优化方案1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。2.应尽量避免在 where 子句中对... -
数据库SQL优化大总结1之- 百万级数据库优化方案
2017-06-06 09:55:51一、百万级数据库优化方案 1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而... -
2D项目大量物品图标Draw Call优化方案
2022-03-07 10:16:061)2D项目大量物品图标Draw Call优化方案 2)UGUI SpriteAtlas的热更新问题 3)iOS平台突然有一帧UI渲染开销很高 4)iOS上频繁Crash,堆栈很奇怪 这是第288篇UWA技术知识分享的推送。今天我们继续为大家精选了...