招聘58同城找工作,网站做优化必须要ftp吗,广东省农业农村厅官网,郑州一建招聘目录 IO性能分析数据库性能优化漏斗法则1、减少数据访问#xff08;减少磁盘访问#xff09;(1) 正确的创建并使用索引索引生效场景索引失效场景判断索引是否生效--执行计划 2、返回更少数据#xff08;减少网络传输或磁盘访问#xff09;(1) 数据分页处理(减少行数)客户端… 目录 IO性能分析数据库性能优化漏斗法则1、减少数据访问减少磁盘访问(1) 正确的创建并使用索引索引生效场景索引失效场景判断索引是否生效--执行计划 2、返回更少数据减少网络传输或磁盘访问(1) 数据分页处理(减少行数)客户端分页服务器分页数据库分页 (2) 只返回需要的字段(减少列数) 3、减少交互次数减少网络传输batch 操作in list 操作设置Fetch Size优化业务逻辑 4、减少服务器CPU开销减少CPU及内存开销使用绑定变量合理使用排序大量复杂运算在客户端处理 5、利用更多资源增加资源客户端多进程并行访问数据库并行处理 参考 在基于数据库进行业务功能的开发时如何保证数据库访问的性能是区分普通程序员和高级程序员的分水岭。这里系统的梳理下如何在程序员视角下实现数据库访问性能优化。 本文是面向程序员的数据库访问性能优化法则一文的精简版有兴趣的同学可以参考下原文。
IO性能分析
计算机各硬件性能指标参考如下 上图中主要有两个性能指标 (1) 延时响应时间表示硬件的突发处理能力 (2) 带宽吞吐量代表硬件持续处理能力。 从上图可以看出计算机系统硬件性能从高到代依次为 CPU–Cache(L1-L2-L3)–内存–SSD硬盘–网络–硬盘 目前个人PC已经从硬盘替换成SSD硬盘但是后端服务器还是以硬盘偏多(SSD硬盘有读写次数限制要根据业务需要合理选择)。本文的内容不涉及SSD相关应用系统。
数据库性能优化漏斗法则
根据数据库知识可以列出每种硬件主要操作内容 (1) CPU及内存缓存数据访问、比较、排序、事务检测、SQL解析、函数或逻辑运算等 (2) 网络结果数据传输、SQL请求、远程数据库访问dblink等 (3) 硬盘数据访问、数据写入、日志记录、大数据量排序、大表连接等。 根据计算机硬件的基本性能指标及其在数据库中主要操作内容可以整理出如下图所示的性能基本优化法则 这个优化法则归纳为5个层次 1、减少数据访问减少磁盘访问 2、返回更少数据减少网络传输或磁盘访问 3、减少交互次数减少网络传输 4、减少服务器CPU开销减少CPU及内存开销 5、利用更多资源增加资源 由于每一层优化法则都是解决其对应硬件的性能问题所以带来的性能提升比例也不一样。传统数据库系统设计是也是尽可能对低速设备提供优化方法因此针对低速设备问题的可优化手段也更多优化成本也更低。任何一个SQL的性能优化都应该按这个规则由上到下来诊断问题并提出解决方案而不应该首先想到的是增加资源解决问题。 以下是每个优化法则层级对应优化效果及成本经验参考 接下来针对5种优化法则列举常用的优化手段并结合实例分析。
1、减少数据访问减少磁盘访问
(1) 正确的创建并使用索引
数据库存储的数据最终是以文件的形式存储在磁盘中。在使用这些数据的时候需要要把磁盘中的数据读到内存中。而磁盘 IO 是非常高昂的操作。一种有效的解决方案是提供一种稳定的数据结构能够满足只需要查询很少的数据就可定位到期望的数据。也即每次查询数据仅需要进行少部分的磁盘 IO 操作。这种数据结构就是索引。索引Index是帮助数据库高效获取数据的数据结构。 如果将数据库比作书那么索引就相当于目录。 数据库索引的原理非常简单但在复杂的表中真正能正确使用索引的人不多。
索引生效场景
索引在SQL中使用具体如下 (a) 在where子句中查询引擎会根据where子句中涉及的字段优先选择索引查询数据。 (b) 在order by子句当使用order by将查询结果按照某个字段排序时如果该字段没有建立索引那么执行计划会将查询出的所有数据使用外部排序但是如果对该字段建立索引后那么由于索引本身是有序的因此直接按照索引的顺序和映射关系逐条取出数据即可。而且如果是分页的那么只用取出索引表某个范围内的索引对应的数据而不用取出所有数据进行排序再返回某个范围内的数据。 © 在join子句中对join语句匹配关系on涉及的字段建立索引能够提高效率。 (d) 在select子句中如果select中的查询字段存在于覆盖索引中那么无需读取记录即可返回。
索引失效场景
在使用索引的时候要注意索引失效的场景尽量避免索引失效的场景。可能导致索引失效的常见场景有 (a) like语句以%开头会导致索引失效。模糊查询时使用%且将其放在开头会导致查询优化器不得不使用全表查询从而导致索引失效。如果是XXX%则可以正常使用索引。 (b) 索引列参与计算会导致索引失效(如执行算数运算或使用函数或存在类型转换)。当索引列参与计算时因为存在中间值所以会导致索引失效。常见的计算场景有类型转换、算数运算、使用函数等场景。 © 查询条件中有or如果存在or相关的字段没有索引会导致语句索引失效。如果查询条件中有or需要确保or相关的字段都要有索引否则会导致索引失效。 (d) 违背最左匹配原则会导致索引失效。如果是一个多码索引(也称联合索引、组合索引)其索引匹配遵循最左匹配规则如果违背会导致索引失效。 (e) 反向查询可能不会使用索引(如not in、not exist)如果在查询的时候使用了反向查询相关的语句要注意确认下索引是否生效。
判断索引是否生效–执行计划
简单SQL可以根据索引使用语法规则判断索引是否生效但是复杂的SQL不好办判断SQL的响应时间是一种策略但是这会受到数据量、主机负载及缓存等因素的影响有时数据全在缓存里可能全表访问的时间比索引访问时间还少。如何判断一个SQL语句是否使用到了索引呢其实可以通过该sql的执行计划来判断。关于MySQL执行计划的文章可以参考这篇WIKI。本文重点介绍下索引生效和失效的场景。
2、返回更少数据减少网络传输或磁盘访问
(1) 数据分页处理(减少行数)
客户端分页
将数据从应用服务器获取到本地应用程序后在客户端通过本地代码进行分页处理。这种处理方式编码简单可以减少客户端与应用服务器网络交互次数。但是首次交互时间较长且占用客户端内存(如果不控制数据总量可能会导致OOM)。
服务器分页
将数据从数据库获取到本地应用程序后在应用服务器内部再进行数据筛选。这种处理方式编码简单只需要一次SQL交互总数据与分页数据差不多时性能较好。但是总数据量较多时性能较差。
数据库分页
采用数据库SQL分页需要两次SQL完成 (1) 一个SQL计算总数量 (2) 一个SQL返回分页后的数据。 这种处理方式性能好是主流的数据分页处理方式。但是不同数据库的语法可能不同且需要两次SQL交互。
(2) 只返回需要的字段(减少列数)
在数据库开发规范中强制要求避免使用select * 语句。因为select * 会返回该表的所有字段。对于宽表来说查询所有字段是一种灾难。使用select特定字段可以: 减少数据在网络上传输开销减少服务器数据处理开销减少客户端内存占用。而且如果访问的所有字段刚好在一个索引里面则可以使用覆盖索引访问提高性能。 此外如果表中有大字段或内容较多的字段如备注信息、文件内容等等那在查询表时一定要注意这方面的问题否则可能会带来严重的性能问题。如果表经常要查询并且请求大内容字段的概率很低我们可以采用分表处理将一个大表分拆成两个一对一的关系表将不常用的大内容字段放在一张单独的表中。
3、减少交互次数减少网络传输
batch 操作
对数据写入操作尽量使用batch操作的接口采用batch操作一般不会减少很多数据库服务器的物理IO但是会大大减少客户端与服务端的交互次数从而减少了多次发起的网络延时开销同时也会降低数据库的CPU开销。
in list 操作
很多时候需要按一些ID查询数据库记录我们可以采用一个ID一个请求发给数据库如下所示
for :var in ids[] do beginselect * from mytable where id:var;
end;其实这里可以做一个小的优化如下所示用ID INLIST的这种方式写SQL
select * from mytable where id in(:id1,id2,...,idn);通过这样处理可以大大减少SQL请求的数量从而提高性能。但是需要注意的是在in语句里面一次放多少个值还需要考虑数据库的能力对MySQL来说建议in中元素的个数小于100。如果超过100个可能会引起执行计划的不稳定性及增加数据库CPU及内存成本这个需要专业DBA评估。
设置Fetch Size
当采用select从数据库查询数据时数据默认并不是一条一条返回给客户端的也不是一次全部返回客户端的而是根据客户端fetch_size参数处理每次只返回fetch_size条记录当客户端游标遍历到尾部时再从服务端取数据直到最后全部传送完成。所以如果我们要从服务端一次取大量数据时可以加大fetch_size这样可以减少结果数据传输的交互次数及服务器数据准备时间提高性能。 iBatis的SqlMapping配置文件可以对每个SQL语句指定fetchsize大小如下所示
select idgetAllProduct resultMapHashMap fetchSize1000select * from employee
/select注意fetchsize不能设置太大如果一次取出的数据大于JVM的内存会导致内存溢出所以建议不要超过1000太大了也没什么性能提高反而可能会增加内存溢出的危险。
优化业务逻辑
要通过优化业务逻辑来提高性能是比较困难的这需要程序员对所访问的数据及业务流程非常清楚。
4、减少服务器CPU开销减少CPU及内存开销
使用绑定变量
绑定变量是指SQL中对变化的值采用变量参数的形式提交而不是在SQL中直接拼写对应的值。非绑定变量写法Select * from employee where id1234567。绑定变量写法Select * from employee where id?。 SQL中预处理操作就是为处理绑定变量提供的对像绑定变量有以下优点 (1) 防止SQL注入 (2) 提高SQL可读性 (3) 提高SQL解析性能不使用绑定变更我们一般称为硬解析使用绑定变量我们称为软解析。
合理使用排序
现在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
大量复杂运算在客户端处理
什么是复杂运算一般情况下一秒钟CPU只能做10万次以内的运算。如含小数的对数及指数运算、三角函数、3DES及BASE64数据加密算法等等。如果有大量这类函数运算尽量放在客户端处理一般CPU每秒中也只能处理1万-10万次这样的函数运算放在数据库内不利于高并发处理。
5、利用更多资源增加资源
客户端多进程并行访问
多进程并行访问是指在客户端创建多个进程(线程)每个进程建立一个与数据库的连接然后同时向数据库提交访问请求。当数据库主机资源有空闲时我们可以采用客户端多进程并行访问的方法来提高性能。如果数据库主机已经很忙时采用多进程并行访问性能不会提高反而可能会更慢。所以使用这种方式最好与DBA或系统管理员进行沟通后再决定是否采用。
数据库并行处理
数据库并行处理是指客户端一条SQL的请求数据库内部自动分解成多个进程并行处理。注意并不是所有的数据库都支持并行处理并不是所有的SQL都可以使用并行处理。并行处理的优点是使用多进程处理充分利用数据库主机资源CPU,IO提高性能。但是并行处理也有以下缺点单个会话占用大量资源影响其它会话所以只适合在主机负载低时期使用只能采用直接IO访问不能利用缓存数据所以执行前会触发将脏缓存数据写入磁盘操作。 需要注意的是 (1) 并行处理在OLTP类系统中慎用使用不当会导致一个会话把主机资源全部占用而正常事务得不到及时响应所以一般只是用于数据仓库平台。 (2) 一般对于百万级记录以下的小表采用并行访问性能并不能提高反而可能会让性能更差。
参考
https://blog.csdn.net/yzsind/article/details/6059209 面向程序员的数据库访问性能优化法则 https://blog.csdn.net/solihawk/article/details/120756584 数据库系列之MySQL中的执行计划
