几个索引优化的原则

2019独角兽企业重金招聘Python工程师标准>>>

一.Index Slice，索引扫描片段，slice中的每个索引页会被顺序扫描，索引页对应的Table页，会被同步顺序读取，

所以索引片的大小很大程度的影响使用该索引的成本。

 

二.Matching Column and Screen Column

在Where 从句中，满足以下条件的，可以称作一个Matching Column

1.有明确谓词的索引列可以称作一个Matching Column

2.前面的条件总没有range 谓词

例如: 索引有4列构成，A,B,C,D

where 从句为 A=:A,B>:B,C=:C

 

A,B 都是Matching Column , C 虽然不是Matching Column ,但其对哪些索引值要回表仍有贡献，所以称为Screen Column

如果where变成A=:A,B=:B,C=:C 则三个都是Matching Column

如果where变成B=:B,C=:C 则两个都不是Matching Column,Index Slice 将是整个Index

 

三.Covering Index, 当Select 的字段都在索引中时，该索引称为该查询的Covering Index, 该查询称为Covering Select

 

Index Filter Factor,Number of Result rows/All rows,当有多个Match Column 时，compound predicate filter factor 相当于每个栏位的值相乘

但当两个column strong correlation 时，近似可以用相乘，但实际的filter factor 与具有偏小的filter factor 的match column 相当，即这种联合

的match column 并没有降低filter factor

 

filter factor 直接决定了Index slice 的大小，值越小Index slice 越小，Performance 越好

 

四.Three Stars Index

First Star

Where 条件中"=" 条件能够走上Index, 则这个索引可以为 First Star

 

Second Star

避免排序，若SQL 有排序动作，如果排序字段刚好是索引字段，且在索引中的位置与First Star不冲突，则为Second Star，即避免排序

如SQL：

SELECT CNO, FNAME

FROM CUST

WHERE LNAME = :LNAME

AND

CITY = :CITY

ORDER BY FNAME

 

Index (LNAME,CITY,FNAME) 可以避免FNAME 的排序，如果Order by 是复合字段，则排序字段顺序不能变，但在First Star 中用的字段可以去掉

如Order by LNAME,FNAME

 

此时如果LNAME 是范围查询，Index(LNAME,CITY,FNAME) 仍然可以保证FNAME 的有序性，因为其前面的Column City Predicate is "="

 

Third Star

避免回表，Select 中查询的字段在Index 都有

 

一般设计的索引很难做到Three Stars，其中First Star 是最重要的，要优选保证(注意Filter Factor)

 

在性能上，一个8字段的索引优于两个4字段的索引，所以当查询语句类型增加时，可以优先考虑在现有索引上增加字段

 

在索引的设计上，需要关注多索引造成的DML 性能下降问题，多索引会引起额外的随机读操作，带来额外的磁盘压力和性能影响

在当前SSD 的架构下，随机读已经不在是一个瓶颈

 

多索引会造成空间浪费，不过这点现在不是问题，磁盘空间不是主要问题，空间换性能本身就是Index 的初衷

 

Index 要尽量避免随机读，和 Fetch 动作，文章认为每个顺序读(由于有预读等特性，耗时 0.01ms),每个随机读耗时 10ms(这在当前SSD 的磁盘特性下已经大大提高)，每个Fetch 耗时0.1ms(取决于最终返回的数据量)

 

在使用Stop Key 的情况下，应尽量避免排序，这样可以不用取得全部数据，即可返回给应用

 

五.要特别注意index pitfall(陷阱)，当Screen Column 出现的很少时，可能会出现全索引扫描，如 ColA=:A,ColB>:B,ColC=:C, ColC 是Screen Column 如果:C 出现的很少，可能会对A所在的Index 扫描大量块甚至全扫描

 

六.当两表关联时，如果某个表有local predicate, 一般会作为驱动表(Out Table)使用

如果做外关联(Out Join，语法中带"+" 的那种)，不带"+" 的一边必须做为驱动表，inner join(内关联，即常规关联)，要求数据集较小的表作为驱动表，数据集较大的表作为inner table，而且在关联字段上要有索引

 

Hash Join ， 首先将较小的数据集在Join Column 上计算Hash Value ,然后缓存在内存中，然后用较大的数据集逐条在计算Hash Value 后探测小数据集，可以认为是将大表作为了驱动表，主要是考虑小表要缓存，提高单次探测效率

 

索引要Fat(能够尽量多的提供扫描需要的栏位，最大程度避免回表)，索引扫描片段要thin(最大程度的起到过滤)(preferably a fat index that provides a very thin index

slice)

 

通过数据冗余和解决部分表关联问题，但也引入新的问题，如数据一致性问题(冗余数据必须在两个表内做更新(Trigger 可以用于解决这个问题))，额外的更新带来的空间消耗，IO消耗问题

 

转载于:https://my.oschina.net/zimingforever/blog/207854