⾯试常见问题--数据库优化百万数据怎么优化
⼀、访问优化的五个法则
在实际开发，我们主要是需要对SQL语句进⾏优化，我们需要快速定位能性的瓶颈点，也就是说快速到我们SQL主要的开销在哪⾥？根据⽊桶原理可以知道，最慢的设备往往是性能瓶颈。例如：互联⽹运⽤中的带宽，本地数据复制时的硬盘的访问速度。
根据当前计算机硬件的基本性能指标及其在数据库中主要操作内容，可以整理出如下五条性能基本优化法则：
（1）减少数据访问（减少磁盘访问）
（2）返回更少数据（减少⽹络传输或磁盘访问）
（3）减少交互次数（减少⽹络传输）
（4）减少服务器CPU开销（减少CPU及内存开销）
（5）利⽤更多资源（增加资源）
由于每⼀层优化法则都是解决其对应硬件的性能问题，所以带来的性能提升⽐例也不⼀样。传统数据库系统设计是也是尽可能对低速设备提供优化⽅法，因此针对低速设备问题的可优化⼿段也更多，优化成本也更低。我们任何⼀个SQL的性能优化都应该按这个规则由上到下来诊断问题并提出解决⽅案，⽽不应该⾸先想到的是增加资源解决问题。
以下是每个优化法则层级对应优化效果及成本经验参考：
优化法则性能提升效果优化成本
减少数据访问1~1000低
返回更少数据1~100低
减少交互次数1~20低
减少服务器CPU开销1~5低
利⽤更多资源@~10⾼
⼆、数据库访问优化法则详解
2.1、减少数据访问
（1）正确创建索引
索引有哪些种类？
常见的索引有B-TREE索引、位图索引、全⽂索引，位图索引⼀般⽤于数据仓库应⽤，全⽂索引由于使⽤较少，这⾥不深⼊介绍。B-TREE 索引包括很多扩展类型，如组合索引、反向索引、函数索引等等，以下是B-TREE索引的简单介绍：
B-TREE索引也称为平衡树索引(Balance Tree)，它是⼀种按字段排好序的树形⽬录结构，主要⽤于提升查询性能和唯⼀约束⽀持。B-TREE 索引的内容包括根节点、分⽀节点、叶⼦节点。
我们⼀般在什么字段上建索引？
这是⼀个⾮常复杂的话题，需要对业务及数据充分分析后再能得出结果。主键及外键通常都要有索引，其它需要建索引的字段应满⾜以下条件：
1、字段出现在查询条件中，并且查询条件可以使⽤索引；
2、语句执⾏频率⾼，⼀天会有⼏千次以上；
3、通过字段条件可筛选的记录集很⼩，那数据筛选⽐例是多少才适合？
这个没有固定值，需要根据表数据量来评估，以下是经验公式，可⽤于快速评估：
⼩表(记录数⼩于10000⾏的表)：筛选⽐例<10%；
⼤表：(筛选返回记录数)<(表总记录数*单条记录长度)/10000/16
单条记录长度≈字段平均内容长度之和+字段数*2
索引对DML(INSERT,UPDATE,DELETE)附加的开销有多少？
这个没有固定的⽐例，与每个表记录的⼤⼩及索引字段⼤⼩密切相关，以下是⼀个普通表数据，仅供参考：
索引对于Insert性能降低56%
索引对于Update性能降低47%
索引对于Delete性能降低29%
因此对于写IO压⼒⽐较⼤的系统，表的索引需要仔细评估必要性，另外索引也会占⽤⼀定的存储空间。
（2）只通过索引访问数据
有些时候，我们只是访问表中的⼏个字段，并且字段内容较少，我们可以为这⼏个字段单独建⽴⼀个组合索引，这样就可以直接只通过访问索引就能得到数据，⼀般索引占⽤的磁盘空间⽐表⼩很多，所以这种⽅式可以⼤⼤减少磁盘IO开销。
（3）优化SQL执⾏计划
SQL执⾏计划是关系型数据库最核⼼的技术之⼀，它表⽰SQL执⾏时的数据访问。由于业务需求越来越复杂，表数据量也越来越⼤，程序员越来越懒惰，SQL也需要⽀持⾮常复杂的业务逻辑，但SQL的性能还需要提⾼，因此，优秀的关系型数据库除了需要⽀持复杂的SQL语法及更多函数外，还需要有⼀套优秀的算法库来提⾼SQL性能。
2.2、返回更少的数据
减少数据的返回也是优化的重要⼿段，主要有两种⽅法分页和只返回需要的字段。
（1）分页
分页总共包括三种分页⽅式：客户端分页、服务端分页、数据库分页。
客服端分页：将数据从应⽤服务器全部下载到本地应⽤程序或浏览器，在应⽤程序或浏览器内部通过本地代码进⾏分页处理。
优点：编码简单，减少客户端与应⽤服务器⽹络交互次数sql语句优化方式
缺点：⾸次交互时间长，占⽤客户端内存
适应场景：客户端与应⽤服务器⽹络延时较⼤，但要求后续操作流畅，如⼿机GPRS，超远程访问（跨国）等等。
服务端分页：将数据从数据库服务器全部下载到应⽤服务器，在应⽤服务器内部再进⾏数据筛选。
优点：编码简单，只需要⼀次SQL交互，总数据与分页数据差不多时性能较好。
缺点：总数据量较多时性能较差。
适应场景：数据库系统不⽀持分页处理，数据量较⼩并且可控。
数据库分页：采⽤数据库SQL分页需要两次SQL完成，⼀个SQL计算总数量，⼀个SQL返回分页后的
数据。
优点：性能好
缺点：编码复杂，各种数据库语法不同，需要两次SQL交互。
ps：数据库⼀般采⽤rownum来进⾏分页。常⽤分页⽅式如下
rownum分页：
rowid分页语法：
（2）只返回需要的字段
通过去除不必要的返回字段可以提⾼性能。
优点：
1、减少数据在⽹络上传输开销
2、减少服务器数据处理开销
3、减少客户端内存占⽤
4、字段变更时提前发现问题，减少程序BUG
5、如果访问的所有字段刚好在⼀个索引⾥⾯，则可以使⽤纯索引访问提⾼性能。
缺点：增加编码⼯作量
2.3、减少交互的次数
（1）batch 操作
数据库访问框架⼀般都提供了批量提交的接⼝，jdbc⽀持batch的提交处理⽅法，我们可以批量插⼊、
更新和删除数据。这样交互次数⼤⼤减少。采⽤batch操作⼀般不会减少很多数据库服务器的物理IO，但是会⼤⼤减少客户端与服务端的交互次数，从⽽减少了多次发起的⽹络延时开销，同时也会降低数据库的CPU开销。
（2）in
很多时候我们需要按⼀些ID查询数据库记录，我们可以采⽤⼀个ID⼀个请求发给数据库，如下所⽰：
我们也可以做⼀个⼩的优化，如下所⽰，⽤ID INLIST的这种⽅式写SQL：
通过这样处理可以⼤⼤减少SQL请求的数量，从⽽提⾼性能。那如果有10000个ID，那是不是全部放在⼀条SQL⾥处理呢？答案肯定是否定的。⾸先⼤部份数据库都会有SQL长度和IN⾥个数的限制，如的IN⾥就不允许超过1000个值。
另外当前数据库⼀般都是采⽤基于成本的优化规则，当IN数量达到⼀定值时有可能改变SQL执⾏计划，从索引访问变成全表访问，这将使性能急剧变化。随着SQL中IN的⾥⾯的值个数增加，SQL的执⾏计划会更复杂，占⽤的内存将会变⼤，这将会增加服务器CPU及内存成本。
评估在IN⾥⾯⼀次放多少个值还需要考虑应⽤服务器本地内存的开销，有并发访问时要计算本地数据使⽤周期内的并发上限，否则可能会导致内存溢出。
综合考虑，⼀般IN⾥⾯的值个数超过20个以后性能基本没什么太⼤变化，也特别说明不要超过100，超过后可能会引起执⾏计划的不稳定性及增加数据库CPU及内存成本.
（3）设置Fetch Size
当我们采⽤select从数据库查询数据时，数据默认并不是⼀条⼀条返回给客户端的，也不是⼀次全部返回客户端的，⽽是根据客户端fetch _size参数处理，每次只返回fetch_size条记录，当客户端游标遍历到尾部时再从服务端取数据，直到最后全部传送完成。所以如果我们要从服务端⼀次取⼤量数据时，可以加⼤fetch_size，这样可以减少结果数据传输的交互次数及服务器数据准备时间，提⾼性能。
2.4、减少数据库服务器CPU运算
（1）使⽤绑定变量
中Preparestatement就是为处理绑定变量提供的对像，绑定变量有以下优点：
1、防⽌SQL注⼊
2、提⾼SQL可读性
3、提⾼SQL解析性能，不使⽤绑定变更我们⼀般称为硬解析，使⽤绑定变量我们称为软解析。
（2）合理使⽤排序
（3）减少⽐较操作
（4）⼤量复杂运算在客户端处理
2.5、利⽤更多的资源
（1）客户端多进程并⾏访问
多进程并⾏访问是指在客户端创建多个进程(线程)，每个进程建⽴⼀个与数据库的连接，然后同时向数据库提交访问请求。当数据库主机资源有空闲时，我们可以采⽤客户端多进程并⾏访问的⽅法来提⾼性能。如果数据库主机已经很忙时，采⽤多进程并⾏访问性能不会提⾼，反⽽可能会更慢。所以使⽤这种⽅式最好与DBA或系统管理员进⾏沟通后再决定是否采⽤。
（2）数据库并⾏处理

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。