1
自己参加落总（罗炳森）SQL优化班时整理的学习笔记（全干货）
25墨值下载
一般我们优化数据库一般我们优化数据库
1. 架构上去优化1. 架构上去优化
2. db上优化2. db上优化
80% ----sql的优化（如果数据库不跑SQL，肯定就不会出性能问题）80% ----sql的优化（如果数据库不跑SQL，肯定就不会出性能问题）
20% -----参数的优化、配置，硬件20% -----参数的优化、配置，硬件
优SQL优化，知道思路，立马搞定优SQL优化，知道思路，立马搞定
基数(Cardinality) 也就是列唯一键(Distinct_keys)的数量，比如性别，该列只有男女之分，所基数(Cardinality) 也就是列唯一键(Distinct_keys)的数量，比如性别，该列只有男女之分，所
以这一列基数是2。主键列的基数等于行数。以这一列基数是2。主键列的基数等于行数。
非主键列的基数怎么算？非主键列的基数怎么算？
select count(distinct column_name) from tableselect count(distinct column_name) from table
选择性(Selectivity) : 列唯一键(Distinct_Keys)与行数(Num_Rows)的比值选择性(Selectivity) : 列唯一键(Distinct_Keys)与行数(Num_Rows)的比值
跟 走不走 索引 有木有 关系？ 有一点关系跟 走不走 索引 有木有 关系？ 有一点关系
一般来说基数越多，返回的数据就越少。这样的列也更适合去建索引。所以正常情况下 基数越多一般来说基数越多，返回的数据就越少。这样的列也更适合去建索引。所以正常情况下 基数越多
，越适合建立索引.列的基数太低，就并不适合建立索引。，越适合建立索引.列的基数太低，就并不适合建立索引。
基数和选择性 这两个概念非常重要，在OLTP系统中，基数/选择性高的列，适合建立B-Tree索引，基数和选择性 这两个概念非常重要，在OLTP系统中，基数/选择性高的列，适合建立B-Tree索引，
选择性低的列不适合建立索引。在OLAP环境中，基数低的列根据需求，可能会建立bitmap索引选择性低的列不适合建立索引。在OLAP环境中，基数低的列根据需求，可能会建立bitmap索引
OLTP千万不能建位图索引。如1000W行的表，999W男，1W女。在性别列上创建了位图索引后，如比OLTP千万不能建位图索引。如1000W行的表，999W男，1W女。在性别列上创建了位图索引后，如比
一个性别为男的列进行DML，会导致999W性别为男的列都被锁定。如果有多个人同时DML性能男的一个性别为男的列进行DML，会导致999W性别为男的列都被锁定。如果有多个人同时DML性能男的
列，数据库直接死了，不多说。列，数据库直接死了，不多说。
我们在进行SQL调优的时候，可以用下面SQL去查看列真实的基数和选择性我们在进行SQL调优的时候，可以用下面SQL去查看列真实的基数和选择性
select count(distinct column_name),select count(distinct column_name),
count(*) total_rows,count(*) total_rows,
count(distinct column_name) / count(*) * 100 selectivitycount(distinct column_name) / count(*) * 100 selectivity
from table_namefrom table_name
作业：作业：
1、写个脚本，基数低的列，建立了索引 -----选出来1、写个脚本，基数低的列，建立了索引 -----选出来
select column_nameselect column_name
from (select c.column_name,from (select c.column_name,
round(c.num_distinct / t.num_rows * 100, 2) selectivityround(c.num_distinct / t.num_rows * 100, 2) selectivity
from dba_tab_col_statistics c, dba_tables tfrom dba_tab_col_statistics c, dba_tables t
where c.table_name = t.table_namewhere c.table_name = t.table_name
1-第一天 2014/11/8
and c.num_distinct <> 0and c.num_distinct <> 0
and t.num_rows <> 0)and t.num_rows <> 0)
where selectivity < 10where selectivity < 10
intersectintersect
select column_name from dba_ind_columnsselect column_name from dba_ind_columns
2、 去 抓 没有被使用的索引， 可以通过 v$sql_plan_statistics_all 来查询2、 去 抓 没有被使用的索引， 可以通过 v$sql_plan_statistics_all 来查询
3、此类索引在 基数很低的列建立3、此类索引在 基数很低的列建立
索引不能随便乱建，所以可以建立一个日志表并随时作出记录，以后可以随时去查询每个索引的创索引不能随便乱建，所以可以建立一个日志表并随时作出记录，以后可以随时去查询每个索引的创
建原因，方便故障排除。建原因，方便故障排除。
如： 表名字，索引名字，创建人，创建时间，创建原因如： 表名字，索引名字，创建人，创建时间，创建原因
dba 最好自己 创建一个账号,专门存储索引信息、大sql信息dba 最好自己 创建一个账号,专门存储索引信息、大sql信息
*其他任何关于数据库的操作等都可以记录下来。以后日志查询。很重要。*其他任何关于数据库的操作等都可以记录下来。以后日志查询。很重要。
直方图直方图
直方图 当某列数据分布不均衡，为了让CBO能生成最佳的执行计划，我们可能需要对表收集直方直方图 当某列数据分布不均衡，为了让CBO能生成最佳的执行计划，我们可能需要对表收集直方
图，直方图最大的桶数(Bucket)是254。收集直方图是一个很耗时的过程，如无必要，千万别去收图，直方图最大的桶数(Bucket)是254。收集直方图是一个很耗时的过程，如无必要，千万别去收
集直方图。集直方图。
一种是频率直方图(FREQUENCY HISTOGRAM)，当列中Distinct_keys 较少(小于254)，如果不手工一种是频率直方图(FREQUENCY HISTOGRAM)，当列中Distinct_keys 较少(小于254)，如果不手工
指定直方图桶数(BUCKET)，Oracle就会自动的创建频率直方图，并且桶数(BUCKET)等于指定直方图桶数(BUCKET)，Oracle就会自动的创建频率直方图，并且桶数(BUCKET)等于
Distinct_KeysDistinct_Keys
另外一种是高度平衡直方图(HEIGHT BALANCED)，当列中Distinct_keys大于254，如果不手工指另外一种是高度平衡直方图(HEIGHT BALANCED)，当列中Distinct_keys大于254，如果不手工指
定直方图桶数(BUCKET)，Oracle就会自动的创建高度平衡直方图。定直方图桶数(BUCKET)，Oracle就会自动的创建高度平衡直方图。
直方图的桶数据记录的就是某个列分别有多少数据 ---记录到桶直方图的桶数据记录的就是某个列分别有多少数据 ---记录到桶
频率直方图是准确的，高度平衡直方图是不准确的频率直方图是准确的，高度平衡直方图是不准确的
没有收集直方图的时候CBO算ROWS是平均算的：ROWS = num_rows/基数没有收集直方图的时候CBO算ROWS是平均算的：ROWS = num_rows/基数
不收集直方图，优化器认为你的列分布永远是均衡的不收集直方图，优化器认为你的列分布永远是均衡的
直方图 最终的目的是？？？ 让CBO能够准确的算出ROWS直方图 最终的目的是？？？ 让CBO能够准确的算出ROWS
做优化，很重要的一点就是保证ROWS算准确做优化，很重要的一点就是保证ROWS算准确
1-第一天 2014/11/8
of 185
25墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论