当前位置: 首页>后端>正文

select in索引 select or 索引

1、优化器

为SQL选择一个最优的执行计划的工具分为RBO和CBO。两种优化器的区别在于:RBO不会计算cost,但是CBO会计算cost。

2、统计信息

表中的数据量与数据分布等信息。查看统计信息,是在sys下的视图中查看。

|-- num_rows
        table -> user_tables --|-- blocks
                               |-- last_analyzed

                               |-- num_rows
        index -> user_indexs --|-- leaf_blocks
                               |-- distinct_keys
                               |-- last_analyzed

                               |-- num_rows
        row -> user_columns ---|-- low_values
                               |-- hight_values
                               |-- last_analyzed

如果没有统计信息,CBO会动态采样,取一定比例的数据分析得到一个统计信息。

3、表连接

实际上应该用row source来代替表。表连接并不是整张表连接。典型的表连接包括:嵌套循环(nested loop)、哈希连接(hash join)、排序合并连接(sort merge join)。驱动表(在表连接中先存取的表)。表连接一次只能连接两个表,即使有多个表的连接,也只能两个两个地连接,所以,两个表的连接很重要,表的连接顺序也很重要。

4、nested loops(嵌套循环)

eg:
        select /*+ use_nl(ab) full(a) full(b) leading(b)*/ *
        from t_userinfo a,t_userserviceinfo b
        where a.phonenumber = b.phonenumber
        leading(b)以b为驱动表

    (1)分析该查询语句的索引:
        这种查询就会采用nested loops(嵌套循环),就像java中的a.forEach(ele -> b.forEach())
        执行计划中的第一个表为驱动表,既:外层循环。
        /*+ use_nl(ab) full(a) full(b) leading(b)*/ 是查询语句中的提示。
        注意:在选择驱动表的时候,以小表为驱动表,小表不是真的小表,而是row source小的表。并且,大表上要有索引。
    
    (2)提示:
            “提示”是用来提示优化器采用什么样的执行计划。
            当出现以下情况的时候,就需要使用提示了:
                a、没有统计信息
                b、临时表上不能收集统计信息
                c、CBO选择的执行计划不好

    (3)提示的分类:
        a、指定连接顺序:
                leading -> 指定驱动表:no_unnest(以主查询为驱动)、unnest(以子查询为驱动)
                ordered -> 指定顺序
        b、指定连接方式:
                use_nl -> 嵌套循环
                use_hash -> 哈希连接
        c、指定访问路径:
                full -> 全表扫描
                index -> 索引扫描
        d、nl_sj,nl_aj,hash_sj,hash_aj
                用于子查询,sj->in/exists
                           aj->not in/not exists

    (4)如何选择驱动表:
        a、当两个表都是全表扫描时,以小表为驱动表
        b、内表的查询列一定要能命中索引
        c、当b表上有固定查询条件,可以缩小b表与a表的连接的row source时,以b表为驱动表。
           所以,应该以参与连接的row source比较小的表为驱动表

    (5)带子查询的nested loops:
        nl-sj -> 以主查询为驱动
        in/exist -> 以主查询为驱动
        no_unnest -> 与nl_sj一样,但是当10G以后,需要使用no_unnest

    (6)多表 nested loops:
        a、在多表连接的时候,实际上是两个表两个表地连接。当使用多表连接的时候,可以使用order(a b c)来指定连接顺序,
            两个表连接以后的row source越少,就应该越靠前。
        b、带外连接的nested loop都是以左表为驱动表。
        c、多表连接默认情况下,是根据from后面的表的顺序从右向左开始连接,但是,如果RBO认为有更好的
           执行计划,也会改变。

5、hash join

(1)nested loop 与 hash join的比较:
        a、nested loop之适合小数据量
        b、hash join适合大数据量
    
    (2)hash join的连接过程:
        a、生产hash表,以驱动表数据生成。为了使hash表小一点,要以小表为驱动表。
        b、生成hash表以后,扫描探测表(探测表是另外一张表),与hash表匹配,匹配上就将数据存入结果集。
        c、hash连接结束,就可以得到结果
    
    (3)默认优化器会使用 index fast full scan 而不是table full scan

    (4)update技巧
        sql语句:
            update t_useinfo a
            set a.username = (select b.oprinitinfo from t_userservice_info b where a.phone=b.phone);
            分析sql语句:
                a、这里会使用nested loop(嵌套循环)
                b、视图update
                    update(
                            select a.username,b.opinfo from a,b where a.phone=b.phone
                            )
                    set username=opinfo;
                c、PL/SQL更新
                    begin
                        for item in(
                                    select a.rowid,b.opinfo from a,b where a.phone=b.phone
                                    )
                        loop
                            update a set username=item.opinfo where rowid=item.rowid;
                        end loop;
                    end;

    (5)hash join总结
        a、适用于大数据,连接条件必须是等值连接
        b、选择小表为驱动表
        c、设置较大的GPA,可以加快hash join
        d、注意顺序,中间结果集尽量小
        e、hash join与nested loop比较,实际上是用空间换时间,当内存不足的时候,就会报错

    (6)分区表
        physical上分离,logical上一体。这样可以加速查询

6、oracle 索引

(1)B+树索引
        B+树索引的数据结果是一个树,树的叶子节点是表中的数据行,叶子节点的父节点叫“索引叶子节点块”,在索引叶子节点块中会保留相邻的索引叶子节点的地址。
        在B+树中,索引节点是有序的,而这种有序是针对每一层而言的。B+树中,每一层可以有200个节点块,每一个节点块中,可以对应400个下一级节点块。但是,叶子节点块是无序的(叶子节点块中存的是数据行)。

    (2)查看表的统计信息
        可以分析表的一些基本数据。注意:数据被delete,数据占用的块不会被释放,但是,新insert的数据会优先占用这些空间。
        
    (3)查看表索引的统计信息
        可以分析表的索引的一些信息。

    (4)关于索引的一些注意点
        a、空字段值是不会被加入索引的,因此,查某个字段为空是不能在索引中查到的
        b、不要在长字段上创建索引,这会导致B+树层高太大,影响索引扫描,使索引扫描时间增加。
            分析:
                1)为什么在长字段上创建索引会导致B+树层高太大:因为每一个索引叶子节点块能够存的数
                   据量是有限的。索引字段越长,索引叶子节点块中的索引数量越少,叶子节点块的数量就
                   越多,B+树层高就越大。
                2)为什么B+树层高越大,索引扫描越慢:因为B+树层高越大,需要扫描的叶子节点块的数量
                   就越多,所以,扫描时间越大。
        c、分配索引空间和表空间时,两者因该是差不多的。
        d、索引键是可以重复的,也是可以不重复的(设置索引为unique),但是不能为空。
        e、索引的重复度越高,查询越慢:
            B+树的高度决定了从根节点到叶子节点查询的节点数量。重复度决定了在叶子节点中查询的节点数
            量。所以,B+树高度越高,查询的节点树越多。重复度越高,查询的节点数越多。

    (5)索引的查询
        索引扫描的数度是由以下两个参数决定的:层高、索引重复度
        层高:层高由“索引块数”、“索引字段长度”这两个参数决定。因为块的大小是固定的,所以,索引字段    
              的长度可以决定索引的块数。因为每一层索引块数固定,所以,索引块数可以决定层高。
        索引重复度:索引重复度描述了同一个索引占用的索引块的数量。索引重复度越高,在同一层扫描的索
              引块树越多,索引扫描的效率越低。

7、数据库性能调优

(1)数据库性能的影响因子:
        IO、CPU、网络。如果数据库做的计算越多,则,CPU使用率越高,网络使用率越低。如果数据库做的计
        算越少,则,CPU使用率越低,网络使用率越高。
        所以,要合理地安排数据库的计算,尽量使IO、CPU、网络的使用率相当。
    (2)减少数据块的读取
    (3)数据块是数据行存储的位置,一个数据块可以存多少行的数据,由行大小来决定。数据块大小由磁盘文
        件系统决定,也可以设置。磁盘格式化时,会将磁盘划分为指定大小的块。

8、数据的访问路径

(1)执行计划的三个方面:
        a、访问路径:全表扫描、各种索引扫描
        b、连接方式:单表查询不考虑
        c、连接顺序:单表查询不考虑
    (2)autotrace工具
        查看执行计划、访问量多少数据块
    (3)delete不会降低表的高水位
        即使数据块是空块,全表扫描也会扫描
    (4)执行计划是从下向上看的
    (5)select /*+full(a)*/ count(*) from user a;
        /*+full(a)*/ : 是告诉优化器对a表执行全表扫描,a为表user的别名
    (6)index unique scan
        因为是唯一索引,所以,查询的索引块数量为层高。
        table access by index rowid,通过行号(rowid)访问表
        主要分析:consistent gets 和 physical reads的数量
        select /*+index(a indexName)*/ field1 from table a where field2='a' and field3='b';
        /*+index(a indexName)*/:指定命中索引a为table别名,index为field2与field3的复合索引

9、index range scan

索引范围扫描:对于组合索引,key为多个字段的拼接。索引重复度高或范围查询
    
    注意:不能命中 index range scan
    
    (1)全模糊查询
        username like '%abc%' / '%abc%'不能比对,因为不知道'abc'是第几位
    (2)没有使用索引中的第一个字段(前导列)
    (3)使用“不等号”或“not in”
    (4)查询列上有表达式或函数
    (5)index range scan的性能分析:
        通过分析该查询访问的块来衡量性能,块越少,性能越好。查询的块 = 索引块 + 数据块
    
    注意:查询条件可以分为:定位条件、过滤条件
        如果定位条件先起作用,则,访问的块数就会很少,然后用过滤条件过滤

10、如何确定哪个索引好

标准:访问的数据块越少越好,访问的数据块 = 索引块 + 表块
    
    (1)叶子节点的访问量对性能影响巨大。查询条件和索引的重复率对该因素影响巨大

    (2)因为表数据是不连续的,所以,通过叶子节点来访问表是需要来回地从叶子节点获得rowid。这也是为
        什么,当命中不了索引时,全表扫描比强制使用index快。自增字段在入表时是相对接近的,因为数据
        入表时间是相近的。
    (3)索引“聚集因子”:它描述了数据在表中的聚集情况。但是,聚集因子的计算因为出现物理读的可能性很
        大,所以,开销也很大。
    (4)一般,如果索引命中超过5%,就不用index range scan

11、index full scan

索引全扫描:扫描所有的叶子节点
    特点:
        (1)有序,叶子节点是有序的
        (2)只能单块读(全表扫描可以多块读),叶子节点之间是指针相连,只能沿指针遍历
        (3)效率比全表扫描低

    使用场景:
        (1)排序取Top N
            全表扫描排序因为需要将数据读入内存,所以,非常消耗性能。但是,index full scan(索引全
            扫描)只能扫描Top N就返回

12、index fast full scan

索引快速全扫描:根据索引在磁盘的物理序扫描,不会顺着叶子节点指针扫描
    
    特点:
        并行、无序、可多块读、只适用于CBO(CBO是一种优化器)

13、index skip scan

定义:跳过索引前导列的索引扫描

14、索引设计原则

(1)分区表不创建全局索引,否则,当delete分区时,索引会失效
    
    (2)不要创建无用的索引,否则,会降低DML的性能
        
    (3)t_1_r_1 与 t_1 是一样功能的索引

    (4)key不要太长,否则,B+树会很高

    (5)重复度低的应该靠前

    (6)索引和表要建在不同的表空间中,可以提高IO性能

 


https://www.xamrdz.com/backend/3m51928840.html

相关文章: