SQL优化基础知识

原创已于 2022-06-07 21:19:22 修改 · 701 阅读

CC 4.0 BY-SA版权

文章标签：

于 2020-12-03 21:24:15 首次发布

6 篇文章

订阅专栏

从4到1，成本是逐渐增大的，因此数据库的优化上，SQL语句优化是很重要的一个方面。

针对SQL的优化有以下基本概念需要掌握：

例如性别这列，有男女两个不同的唯一键，其基数为2。

基数的高低反映出该例的数据分布情况。

如果某个列基数很低，该例数据分布就会非常不平衡，由于该列数据分布不均衡，会导致SQL索引可能走全表扫描，也可能走索引。

如果SQL语句是单表访问，那么可能走索引，也可能走全表扫描，还有可能走物化视图扫描。走索引的条件：返回表中5%的数据以内的时候走索引，超过5%以上走全表扫描(根本原因在回表，下面有讲到回表)。

数据量大的情况下，基数小，会走全表扫描。
数据量大的情况下，基数大，选择数据超过百分之20，会走全表扫描。

什么样的列必须建立索引呢？

在进行SQL优化的时候，但看基数是没有意义的，必须对比总行数此案有实际意义，因而引入了选择性。

选择性大于20%，说明该列的数据比较均衡。当一个列出现在where语句中且选择性大于20%，在该列上创建索引能够提升SQL查询性能。

SQL优化核心思想：只有大表才会产生性能问题。

因此在大表建索引是优化方式之一，可以使用V$SQL_PLAN或者自动化脚本抓取表的哪一列出现在where语句中，用于建索引。

直方图是一种统计信息图，它使用高低不等的纵向条纹或线段表示数据分布情况。

如果没有对基数低的列收集直方图统计信息，基于成本的优化器(CBO)会认为该列数据分布是均衡的。

在做SQL优化的时候，经常需要做的工作就是帮助CBO计算出比较准确的Rows，收集直方图统计信息是一种有效的方式。如果CBO每次计算都可以得到精确的Rows，那么我们就只需要关心业务逻辑、表设计、SQL写法已经如何建立索引了，不需要担心SQL会走错执行计划。

可以使用自动化脚本抓出必须建立直方图的列。

在这里插入图片描述

当对一个列建立索引后，索引会包含该列的键值以及对应行所在的rowid。通过索引中记录的rowid访问表中的数据就叫回表。回表一般是单块读，回表次数太多会严重影响SQL性能，如果回表次数过多，就不应该走索引扫描了，应该走全表扫描。

为什么5%的数据以内的时候走索引，超过5%以上走全表扫描？

根本原因在回表。在回表无法避免的条件下，走索引如果返回数据量太多，必然导致回表次数太多，从而导致性能严重下降。

集群因子用于判断索引回表需要消耗的物理I/O次数。

集群因子介于表的块数和表行数之间。

如果集群因子与块数接近，说明数据基本上是有序的，而且其顺序基本与索引一样。这样在进行索引范围或者索引全扫描的时候，回表只需要读取少量的数据块就能完成。

如果集群因子与表记录数接近，说明表的数据和索引顺序差异很大，在进行索引范围扫描或者索引全扫描的时候，回表会读取更多的数据块。

在进行SQL优化的时候，往往会建立合适的组合索引消除回表，或者建立组合索引尽量减少回表次数。

一对一、一对多、多对多。

项	含义	SQL示例
`full table scan`	全表扫描(多块读)：从表中读取所有行
`index fast full scan`	索引快速全扫描(多块读)：不需要通过rowid获取其他数据	`select id from taxidata where id = '1'`
`table access by rowid`	直接通过rowid定位行，即回表
`index unique scan`	索引唯一扫描	`select * from taxidata where id = '1'`
`index range scan`	索引局部扫描：范围定位行 `select * from taxidata where id < '5'`
`index full scan`	索引全扫描：遍历索引扫描	`select id from taxidata`