SQL优化核心思想：或许你不知道的5条优化技巧

cfsky

　　点击关注 异步图书，置顶公众号
　　每天与你分享 IT好书 技术干货 职场知识

　　参与文末话题讨论，每日赠送异步图书。
　　——异步小编
　　随着系统的数据量逐年增加，并发量也成倍增长，SQL性能越来越成为IT系统设计和开发时首要考虑的问题之一。SQL性能问题已经逐步发展成为数据库性能的首要问题，80%的数据库性能问题都是因SQL而导致。面对日益增多的SQL性能问题，如何下手以及如何提前审核已经成为越来越多的程序员必须要考虑的问题。
　　今天将带来《SQL优化核心思想》，作者将8年专职SQL优化的经验和心得与大家一起分享，以揭开SQL优化的神秘面纱，让一线工程师在实际开发中不再寝食难安、谈虎色变，最终能够对SQL优化技能驾轻就熟.首先进入主题SQL优化必懂概念。
　　1.1　基数（CARDINALITY）
　　某个列唯一键（Distinct_Keys）的数量叫作基数。比如性别列，该列只有男女之分，所以这一列基数是2。主键列的基数等于表的总行数。基数的高低影响列的数据分布。
　　以测试表test为例，owner列和object_id列的基数分别如下所示。

　　TEST表的总行数为72 462，owner列的基数为29，说明owner列里面有大量重复值，object_id列的基数等于总行数，说明object_id列没有重复值，相当于主键。owner列的数据分布如下。

　　owner列的数据分布极不均衡，我们运行如下SQL。

　　SYS有30 808条数据，从72 462条数据里面查询30 808条数据，也就是说要返回表中42.5%的数据。

　　那么请思考，你认为以上查询应该使用索引吗？现在我们换一种查询语句。

　　SCOTT有7条数据，从72 462条数据里面查询7条数据，也就是说要返回表中0.009%的数据。

　　请思考，返回表中0.009%的数据应不应该走索引？
　　如果你还不懂索引，没关系，后面的章节我们会详细介绍。如果你回答不了上面的问题，我们先提醒一下。当查询结果是返回表中5%以内的数据时，应该走索引；当查询结果返回的是超过表中5%的数据时，应该走全表扫描。
　　当然了，返回表中5%以内的数据走索引，返回超过5%的数据就使用全表扫描，这个结论太绝对了，因为你还没掌握后面章节的知识，这里暂且记住5%这个界限就行。我们之所以在这里讲5%，是怕一些初学者不知道上面问题的答案而纠结。
　　现在有如下查询语句。

　　语句中，“:B1”是绑定变量，可以传入任意值，该查询可能走索引也可能走全表扫描。
　　现在得到一个结论：如果某个列基数很低，该列数据分布就会非常不均衡，由于该列数据分布不均衡，会导致SQL查询可能走索引，也可能走全表扫描。在做SQL优化的时候，如果怀疑列数据分布不均衡，我们可以使用select列，count(*) from表group by列order by 2 desc来查看列的数据分布。
　　如果SQL语句是单表访问，那么可能走索引，可能走全表扫描，也可能走物化视图扫描。在不考虑有物化视图的情况下，单表访问要么走索引，要么走全表扫描。现在，回忆一下走索引的条件：返回表中5%以内的数据走索引，超过5%的时候走全表扫描。相信大家读到这里，已经搞懂了单表访问的优化方法。
　　我们来看如下查询。

　　不管object_id传入任何值，都应该走索引。
　　我们再思考如下查询语句。

　　不管给object_name传入任何值，请问该查询应该走索引吗？
　　请你去查看object_name的数据分布。写到这里，其实有点想把本节名称改为“数据分布”。大家在以后的工作中一定要注意列的数据分布！
　　1.2　选择性（SELECTIVITY）
　　基数与总行数的比值再乘以100%就是某个列的选择性。
　　在进行SQL优化的时候，单独看列的基数是没有意义的，基数必须对比总行数才有实际意义，正是因为这个原因，我们才引出了选择性这个概念。
　　下面我们查看test表各个列的基数与选择性，为了查看选择性，必须先收集统计信息。关于统计信息，我们在第2章会详细介绍。下面的脚本用于收集test表的统计信息。

　　下面的脚本用于查看test表中每个列的基数与选择性。

　　请思考：什么样的列必须建立索引呢？
　　有人说基数高的列，有人说在where条件中的列。这些答案并不完美。基数高究竟是多高？没有和总行数对比，始终不知道有多高。比如某个列的基数有几万行，但是总行数有几十亿行，那么这个列的基数还高吗？这就是要引出选择性的根本原因。
　　当一个列选择性大于20%，说明该列的数据分布就比较均衡了。测试表test中object_name、object_id的选择性均大于20%，其中object_name列的选择性为61.05%。现在我们查看该列数据分布（为了方便展示，只输出前10行数据的分布情况）。

　　由上面的查询结果我们可知，object_name列的数据分布非常均衡。我们查询以下SQL。

　　不管object_name传入任何值，最多返回30行数据。
　　什么样的列必须要创建索引呢？当一个列出现在where条件中，该列没有创建索引并且选择性大于20%，那么该列就必须创建索引，从而提升SQL查询性能。当然了，如果表只有几百条数据，那我们就不用创建索引了。
　　下面抛出SQL优化核心思想第一个观点：只有大表才会产生性能问题。
　　也许有人会说：“我有个表很小，只有几百条，但是该表经常进行DML，会产生热点块，也会出性能问题。”对此我们并不想过多地讨论此问题，这属于应用程序设计问题，不属于SQL优化的范畴。
　　下面我们将通过实验为大家分享本书第一个全自动优化脚本。
　　抓出必须创建索引的列（请读者对该脚本适当修改，以便用于生产环境）。
　　首先，该列必须出现在where条件中，怎么抓出表的哪个列出现在where条件中呢？有两种方法，一种是可以通过V$SQL_PLAN抓取，另一种是通过下面的脚本抓取。
　　先执行下面的存储过程，刷新数据库监控信息。

　　运行完上面的命令之后，再运行下面的查询语句就可以查询出哪个表的哪个列出现在where条件中。

　　下面是实验步骤。
　　我们首先运行一个查询语句，让owner与object_id列出现在where条件中。

　　其次刷新数据库监控信息。

　　然后我们查看test表有哪些列出现在where条件中。

　　接下来我们查询出选择性大于等于20%的列。

　　最后，确保这些列没有创建索引。

　　把上面的脚本组合起来，我们就可以得到全自动的优化脚本了。

　　1.3　直方图（HISTOGRAM）
　　前面提到，当某个列基数很低，该列数据分布就会不均衡。数据分布不均衡会导致在查询该列的时候，要么走全表扫描，要么走索引扫描，这个时候很容易走错执行计划。
　　如果没有对基数低的列收集直方图统计信息，基于成本的优化器（CBO）会认为该列数据分布是均衡的。
　　下面我们还是以测试表test为例，用实验讲解直方图。

　　首先我们对测试表test收集统计信息，在收集统计信息的时候，不收集列的直方图，语句for all columns>

　　Histogram为none表示没有收集直方图。

　　owner列基数很低，现在我们对owner列进行查询。

　　请注意看粗体字部分，查询owner＝'SCOTT'返回了7条数据，但是CBO在计算Rows的时候认为owner='SCOTT'返回2 499条数据，Rows估算得不是特别准确。从72 462条数据里面查询出7条数据，应该走索引，所以现在我们对owner列创建索引。

　　我们再来查询一下。

　　注意粗字体部分，查询owner='SYS'返回了30 808条数据。从72 462条数据里面返回30 808条数据能走索引吗？很明显应该走全表扫描。也就是说该执行计划是错误的。
　　为什么查询owner='SYS'的执行计划会用错呢？因为owner这个列基数很低，只有29，而表的总行数是72 462。前文着重强调过，当列没有收集直方图统计信息的时候，CBO会认为该列数据分布是均衡的。正是因为CBO认为owner列数据分布是均衡的，不管owner等于任何值，CBO估算的Rows永远都是2 499。而这2 499是怎么来的呢？答案如下。

　　现在大家也知道了，执行计划里面的Rows是假的。执行计划中的Rows是根据统计信息以及一些数学公式计算出来的。很多DBA到现在还不知道执行计划中Rows是假的这个真相，真是令人遗憾。
　　在做SQL优化的时候，经常需要做的工作就是帮助CBO计算出比较准确的Rows。注意：我们说的是比较准确的Rows。CBO是无法得到精确的Rows的，因为对表收集统计信息的时候，统计信息一般都不会按照100%的标准采样收集，即使按照100%的标准采样收集了表的统计信息，表中的数据也随时在发生变更。另外计算Rows的数学公式目前也是有缺陷的，CBO永远不可能计算得到精确的Rows。
　　如果CBO每次都能计算得到精确的Rows，那么相信我们这个时候只需要关心业务逻辑、表设计、SQL写法以及如何建立索引了，再也不用担心SQL会走错执行计划了。
　　Oracle12c的新功能SQL Plan Directives在一定程度上解决了Rows估算不准而引发的SQL性能问题。关于SQL Plan Directives，本书不做过多讨论。
　　为了让CBO选择正确的执行计划，我们需要对owner列收集直方图信息，从而告知CBO该列数据分布不均衡，让CBO在计算Rows的时候参考直方图统计。现在我们对owner列收集直方图。

　　查看一下owner列的直方图信息。

　　现在我们再来查询上面的SQL，看执行计划是否还会走错并且验证Rows是否还会算错。

　　对owner列收集完直方图之后，CBO估算的Rows就基本准确了，一旦Rows估算对了，那么执行计划也就不会出错了。
　　大家是不是很好奇，为什么收集完直方图之后，Rows计算得那么精确，收集直方图究竟完成了什么操作呢？对owner列收集直方图其实就相当于运行了以下SQL。

　　直方图信息就是以上SQL的查询结果，这些查询结果会保存在数据字典中。这样当我们查询owner为任意值的时候，CBO总会算出正确的Rows，因为直方图已经知道每个值有多少行数据。
　　如果SQL使用了绑定变量，绑定变量的列收集了直方图，那么该SQL就会引起绑定变量窥探。绑定变量窥探是一个老生常谈的问题，这里不多做讨论。Oracle11g引入了自适应游标共享（Adaptive Cursor Sharing），基本上解决了绑定变量窥探问题，但是自适应游标共享也会引起一些新问题，对此也不做过多讨论。
　　当我们遇到一个SQL有绑定变量怎么办？其实很简单，我们只需要运行以下语句。

　　如果列数据分布均衡，基本上SQL不会出现问题；如果列数据分布不均衡，我们需要对列收集直方图统计。
　　关于直方图，其实还有非常多的话题，比如直方图的种类、直方图的桶数等，本书在此不做过多讨论。在我们看来，读者只需要知道直方图是用来帮助CBO在对基数很低、数据分布不均衡的列进行Rows估算的时候，可以得到更精确的Rows就够了。
　　什么样的列需要收集直方图呢？当列出现在where条件中，列的选择性小于1%并且该列没有收集过直方图，这样的列就应该收集直方图。注意：千万不能对没有出现在where条件中的列收集直方图。对没有出现在where条件中的列收集直方图完全是做无用功，浪费数据库资源。
　　下面我们为大家分享本书第二个全自动化优化脚本。
　　抓出必须创建直方图的列（大家可以对该脚本进行适当修改，以便用于生产环境）。

　　1.4　回表（TABLE ACCESS BY INDEX ROWID）
　　当对一个列创建索引之后，索引会包含该列的键值以及键值对应行所在的rowid。通过索引中记录的rowid访问表中的数据就叫回表。回表一般是单块读，回表次数太多会严重影响SQL性能，如果回表次数太多，就不应该走索引扫描了，应该直接走全表扫描。
　　在进行SQL优化的时候，一定要注意回表次数！特别是要注意回表的物理I/O次数！
　　大家还记得1.3节中错误的执行计划吗？

　　执行计划中加粗部分（TABLE ACCESS BY INDEX ROWID）就是回表。索引返回多少行数据，回表就要回多少次，每次回表都是单块读（因为一个rowid对应一个数据块）。该SQL返回了30 808行数据，那么回表一共就需要30 808次。
　　请思考：上面执行计划的性能是耗费在索引扫描中还是耗费在回表中？
　　为了得到答案，请大家在SQLPLUS中进行实验。为了消除arraysize参数对逻辑读的影响，设置arraysize=5000。arraysize表示Oracle服务器每次传输多少行数据到客户端，默认为15。如果一个块有150行数据，那么这个块就会被读10次，因为每次只传输15行数据到客户端，逻辑读会被放大。设置了arraysize=5000之后，就不会发生一个块被读n次的问题了。

　　从上面的实验可见，索引扫描只耗费了74个逻辑读。

　　SQL在有回表的情况下，一共耗费了877个逻辑读，那么这877个逻辑读是怎么来的呢？
　　SQL返回的30 808条数据一共存储在796个数据块中，访问这796个数据块就需要消耗796个逻辑读，加上索引扫描的74个逻辑读，再加上7个逻辑读[其中7=ROUND（30808/5000）]，这样累计起来刚好就是877个逻辑读。
　　因此我们可以判断，该SQL的性能确实绝大部分损失在回表中！
　　更糟糕的是：假设30 808条数据都在不同的数据块中，表也没有被缓存在buffer cache中，那么回表一共需要耗费30 808个物理I/O，这太可怕了。
　　大家看到这里，是否能回答为什么返回表中5%以内的数据走索引、超过表中5%的数据走全表扫描？根本原因就在于回表。
　　在无法避免回表的情况下，走索引如果返回数据量太多，必然会导致回表次数太多，从而导致性能严重下降。
　　Oracle12c的新功能批量回表（TABLE ACCESS BY INDEX ROWID BATCHED）在一定程度上改善了单行回表（TABLE ACCESS BY INDEX ROWID）的性能。关于批量回表本书不做讨论。
　　什么样的SQL必须要回表？

　　这样的SQL就必须回表，所以我们必须严禁使用Select *。那什么样的SQL不需要回表？

　　这样的SQL就不需要回表。
　　当要查询的列也包含在索引中，这个时候就不需要回表了，所以我们往往会建立组合索引来消除回表，从而提升查询性能。
　　当一个SQL有多个过滤条件但是只在一个列或者部分列建立了索引，这个时候会发生回表再过滤（TABLE ACCESS BY INDEX ROWID前面有“*”），也需要创建组合索引，进而消除回表再过滤，从而提升查询性能。
　　关于如何创建组合索引，这问题太复杂了，我们在本书8.3节、9.1节以及第10章都会反复提及如何创建组合索引。
　　本文摘自《SQL优化核心思想》

　　《SQL优化核心思想》
　　罗炳森 黄超 钟侥 著
　　点击封面购买纸书
　　结构化查询语言（Structured Query Language，SQL）是一种功能强大的数据库语言。它基于关系代数运算，功能丰富、语言简洁、使用方便灵活，已成为关系数据库的标准语言。 本书旨在引导读者掌握SQL优化技能，以更好地提升数据库性能。本书基于Oracle进行编写，内容讲解由浅入深，适合各个层次的读者学习。
　　本书面向一线工程师、运维工程师、数据库管理员以及系统设计与开发人员，无论是初学者还是有一定基础的读者，都将从中获益。
　　今日互动

你对异步图书的哪类文章感兴趣？为什么？截止时间4月27日17时，留言+转发本活动到朋友圈，小编将抽奖选出5名读者赠送纸书2本和3张e读版100元异步社区代金券，（留言点赞最多的自动获得一张）。

　　推荐阅读
　　2018年4月新书书单
　　异步图书最全Python书单
　　一份程序员必备的算法书单
　　第一本Python神经网络编程图书

　　长按二维码，可以关注我们哟
　　每天与你分享IT好文。
　　在“异步图书”后台回复“关注”，即可免费获得2000门在线视频课程；推荐朋友关注根据提示获取赠书链接，免费得异步e读版图书一本。赶紧来参加哦！
　　点击阅读原文，直接购买《SQL优化核心思想》
　　阅读原文