面向程序员的数据库拜访性能优化法则（上）-外链论坛

tw4ld6 发表于 2024-7-27 01:06:58

面向程序员的数据库拜访性能优化法则（上）

尤其说明：
1、本文只是面对数据库应用研发的程序员，不适合专业DBA，DBA在数据库性能优化方面需要认识更加多的知识；
2、本文许多示例及概念是基于Oracle数据库描述，针对其它关系型数据库亦能够参考，但许多观点不适合于KV数据库或内存数据库或是基于SSD技术的数据库；
3、本文未深入数据库优化中最核心的执行计划分析技术。
读者对像：
研发人员：倘若你是做数据库研发，那本文的内容非常适合，由于本文是从程序员的方向来谈数据库性能优化。
架构师：倘若你已然是数据库应用的架构师，那本文的知识你应该清楚90%，否则你可能是一个爱好折腾的架构师。
DBA（数据库管理员）：大型数据库优化的知识非常繁杂，本文只是从程序员的方向来谈性能优化，DBA除了需要认识这些知识外，还需要深入数据库的内部体系架构来处理问题。
引言
在网上有非常多文案介绍数据库优化知识，然则大部份文案只是对某个一个方面进行说明，而针对咱们程序员来讲这种介绍并不可很好的把握优化知识，由于非常多介绍只是对有些特定的场景优化的，因此反而有时会产生误导或让程序员感觉不明白其中的奥妙而对数据库优化感觉很奥秘。
非常多程序员总是问怎样学习数据库优化，有无好的教材之类的问题。在书店亦看到了许都数据库优化的专业书籍，然则感觉更大都是面向DBA或是PL/SQL研发方面的知识，个人感觉不太适合普通程序员。而想要做到数据库优化的能手，不是花几周，几个月就能达到的，这并不是由于数据库优化有多高深，而是由于要做好优化一方面需要有非常好的技术功底，对操作系统、存储硬件网络、数据库原理等方面有比较扎实的基本知识，另一方面是需要花海量时间对特定的数据库进行实践测试与总结。
做为一个程序员，咱们亦许不清楚线上正式的服务器硬件配置，咱们不可能像DBA那样专业的对数据库进行各样实践测试与总结，但咱们都应该非常认识咱们SQL的业务规律，咱们清楚SQL中拜访表及字段的数据状况，咱们其实只关心咱们的SQL是不是能尽快返回结果。那程序员怎样利用已知的知识进行数据库优化？怎样能快速定位SQL性能问题并找到正确的优化方向？
面对这些问题，笔者总结了有些面向程序员的基本优化法则，本文将结合实例来坦述数据库研发的优化知识。
1、数据库拜访优化法则简介

      要正确的优化SQL，咱们需要快速定位能性的瓶颈点，亦便是说快速找到咱们SQL重点的开销在哪里？而大都数状况性能最慢的设备会是瓶颈点，如下载时网络速度可能会是瓶颈点，本地复制文件时硬盘可能会是瓶颈点，为何这些通常的工作咱们能快速确认瓶颈点呢，由于咱们对这些慢速设备的性能数据有有些基本的认识，如网络带宽是2Mbps，硬盘是每分钟7200转等等。因此呢，为了快速找到SQL的性能瓶颈点，咱们亦需要认识咱们计算机系统的硬件基本性能指标，下图展示的当前主流计算机性能指标数据。<img src="http://mmbiz.qpic.cn/mmbiz_jpg/Hk1ceA7cQUDIUmSeQFFicDeH5MUC37Mybw8jB9VHL4RG9KiczmcKlvWcjo44libZEDePcSfhg9vUst6FiayxIuS87w/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
从图上能够看到基本上每种设备都有两个指标：
延时（响应时间）：暗示硬件的突发处理能力；
带宽（吞吐量）：表率硬件连续处理能力。
从上图能够看出，计算机系统硬件性能从高到代依次为：
CPU——Cache(L1-L2-L3)——内存——SSD硬盘——网络——硬盘
因为SSD硬盘还处在快速发展周期，因此本文的内容不触及SSD关联应用系统。
按照数据库知识，咱们能够列出每种硬件重点的工作内容：
CPU及内存：缓存数据拜访、比较、排序、事务检测、SQL解析、函数或规律运算；
网络：结果数据传输、SQL请求、远程数据库拜访（dblink）；
硬盘：数据拜访、数据写入、日志记录、大数据量排序、大表连接。
按照当前计算机硬件的基本性能指标及其在数据库中重点操作内容，能够整理出如下图所示的性能基本优化法则：<img src="http://mmbiz.qpic.cn/mmbiz_jpg/Hk1ceA7cQUDIUmSeQFFicDeH5MUC37MybXKZsfsVgw8ck1EXbDIVnkyQuuwEoRDyphtU6M7X3Fn1V11yCticc8jA/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
这个优化法则归纳为5个层次：
    1、减少许据拜访（减少磁盘拜访）
    2、返回更少许据（减少网络传输或磁盘拜访）
    3、减少交互次数（减少网络传输）
    4、减少服务器CPU开销（减少CPU及内存开销）
    5、利用更加多资源（增多资源）
因为每一层优化法则都是处理其对应硬件的性能问题，因此带来的性能提高比例亦不同样。传统数据库系统设计是亦是尽可能对低速设备供给优化办法，因此呢针对低速设备问题的可优化手段亦更加多，优化成本亦更低。咱们任何一个SQL的性能优化都应该按这个规则由上到下来诊断问题并提出处理方法，而不该该首要想到的是增多资源处理问题。
以下是每一个优化法则层级对应优化效果及成本经验参考：
优化法则
性能提高效果
优化成本
减少许据拜访
1~1000
低
返回更少许据
1~100
低
减少交互次数
1~20
低
减少服务器CPU开销
1~5
低
利用更加多资源
@~10
接下来，咱们针对5种优化法则列举常用的优化手段并结合实例分析。
2、Oracle数据库两个基本概念
数据块（Block）
数据块是数据库中数据在磁盘中存储的最小单位，亦是一次IO拜访的最小单位，一个数据块一般能够存储多条记录，数据块体积是DBA在创建数据库或表空间时指定，可指定为2K、4K、8K、16K或32K字节。下图是一个Oracle数据库典型的理学结构，一个数据库能够包含多个数据文件，一个数据文件内又包括多个数据块；
<img src="http://mmbiz.qpic.cn/mmbiz_jpg/Hk1ceA7cQUDIUmSeQFFicDeH5MUC37MybGyxRfj5oUjrEFT0mAqqMVJBdjThP9hLjFJX4z1cbxw28xGrurllrzw/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
ROWID
ROWID是每条记录在数据库中的独一标识，经过ROWID能够直接定位记录到对应的文件号及数据块位置。ROWID内容包含文件号、对像号、数据块号、记录槽号，如下图所示：
<img src="http://mmbiz.qpic.cn/mmbiz_jpg/Hk1ceA7cQUDIUmSeQFFicDeH5MUC37MybTGxOl7TZHAUvWyCCULN15RbLvhEYSnNRVatntiba8rWKEw0OKeL6qDg/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
3、数据库拜访优化法则详解

1 减少许据拜访
1.1 创建并运用正确的索引
数据库索引的原理非常简单，但在繁杂的表中真正能正确运用索引的人很少，即使是专业的DBA亦不必定能完全做到最优。
索引会大大增多表记录的DML(INSERT,UPDATE,DELETE)开销，正确的索引能够让性能提高100，1000倍以上，不恰当的索引亦可能会让性能下降100倍，因此呢在一个表中创建什么样的索引需要平衡各样业务需要。
索引平常问题：
索引有那些种类？
平常的索引有B-TREE索引、位图索引、全文索引，位图索引通常用于数据仓库应用，全文索引因为运用较少，这儿不深入介绍。B-TREE索引包含非常多扩展类型，如组合索引、反向索引、函数索引等等，以下是B-TREE索引的简单介绍：
B-TREE索引亦叫作为平衡树索引(Balance Tree)，它是一种按字段排好序的树形目录结构，重点用于提高查找性能和独一约束支持。B-TREE索引的内容包含根节点、分支节点、叶子节点。
叶子节点内容：索引字段内容+表记录ROWID
根节点，分支节点内容：当一个数据块中不可放下所有索引字段数据时，就会形成树形的根节点或分支节点，根节点与分支节点保留了索引树的次序及各层级间的引用关系。
一个普通的BTREE索引结构示意图如下所示：<img src="http://mmbiz.qpic.cn/mmbiz_jpg/Hk1ceA7cQUDIUmSeQFFicDeH5MUC37MybAXth6yy0ibAqja8O7SvCCxsWChdZMXw3dbvsibMtPzbzC9JOWicoHdR3w/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
倘若咱们把一个表的内容认为是一本字典，那索引就相当于字典的目录，如下图所示：<img src="http://mmbiz.qpic.cn/mmbiz_jpg/Hk1ceA7cQUDIUmSeQFFicDeH5MUC37MybwYzn2okGW0kAZ7uRDzibfwLBWhWvHXl81icqQT06Q4iaErKV4tmh0nYYQ/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"><img src="http://mmbiz.qpic.cn/mmbiz_jpg/Hk1ceA7cQUDIUmSeQFFicDeH5MUC37MybibL6xCabkOfUuBfI1ib7x0cNwtOosl3pp4lia4h235CYFmSiaHpKUzuVtg/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
图中是一个字典按部首+笔划数的目录，相当于给字典建了一个按部首+笔划的组合索引。
一个表中能够建多个索引，就如一本字典能够建多个目录同样（按拼音、笔划、部首等等）。
一个索引亦能够由多个字段构成，叫作为组合索引，如上图便是一个按部首+笔划的组合目录。
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沟通。
有时候咱们会运用多个字段的组合索引，倘若查找要求中第1个字段不可运用索引，那全部查找亦不可运用索引
如：咱们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。
那样按第1种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表。
（未完待续，关注公众号查看下篇）

情迷布拉格 发表于 2024-8-20 05:32:15

“沙发”（SF，第一个回帖的人）‌

7wu1wm0 发表于 2024-10-9 10:16:19

我深受你的启发，你的话语是我前进的动力。

m5k1umn 发表于 2024-10-22 09:35:48

外链发布社区 http://www.fok120.com/

nykek5i 发表于 2024-11-2 20:00:54

外链论坛的成功举办，是与各位领导、同仁们的关怀和支持分不开的。在此，我谨代表公司向关心和支持论坛的各界人士表示最衷心的感谢！

页: [1]

外链论坛's Archiver

面向程序员的数据库拜访性能优化法则（上）