建立组合索引的字段顺序优化

本文探讨了组合索引的字段顺序优化策略,包括将最常用字段置于首位、优先考虑高离散值字段及等值条件字段前置的重要性。通过实例演示,展示了不同顺序对查询效率的影响。

建立组合索引的字段顺序优化

简介

组合索引我们经常用到,建立组合索引大家也都会,但是如何考虑建立组合索引的顺序是一个值得推敲的事情。

正文

1. 尽量把最常用的字段放在最前面

对于我们需要创建的组合索引,如果同时又经常单独使用其中某个字段作为查询条件,这样的字段是要求放在组合索引前面的。

因为这种场景下,能直接使用组合索引做范围扫描,否则,如果该字段放在后面,可能走索引跳跃扫描,全索引扫描,甚至全表扫描。

举例:
  1. 首先创建表

    create table t_userserviceinfo_test as select * from T_USERSERVICEINFO nologging;
    
  2. 创建索引,把常用字段 phonenumber 作为组合索引的前导列

    create index ix_userserviceinfo_test_1 on t_userserviceinfo_test(phonenumber,servstaus) tablespace ringidx; 
    
  3. 按号码查询,查看执行计划,走了该索引的范围扫描,很快就查到了结果。

反例:
  1. 删除上面的索引

    drop index ix_userserviceinfo_test_1;
    
  2. 创建新的索引,把 phonenumber 不作为前导列

    create index ix_userserviceinfo_test_1 on t_userserviceinfo_test(servstaus,phonenumber) tablespace ringidx; 
    
  3. 同样按号码查询,查看执行计划,走了该索引的跳跃扫描,效果不好。

当然 Oracle考虑执行COST,可能就不会走这个索引了,导致全表扫描。

2. 尽量把离散值较高的字段往前放
   	1. 条件中有单独使用这个字段,那么使用该索引有很好的效果
   	2. 放置误用索引,如果离骚之较少的字段放前面,同时条件中仅包含该字段,那么 Oracle 可能会选择该索引,但是其实选择该索引,选择率很低。
3. 查询时,有的列是非等值条件,有点是等值条件,则等值条件字段放在前面
   	1. 等值条件字段放在前面,在查找的时候,找到的索引块都是有效数据。
   2. 如果非等值字段放在前面,那么需要进行索引跳跃扫描,或者范围扫描,这是就扫描了很多无效的索引。
举例:
  1. 现需要根据状态和时间查找数据

     select * from t_userserviceinfo_test t where servstatus = 1 and t.upstatusstime > sysdate -100;
    
  2. 简历两个索引,分别把状态和时间字段顺序颠倒:

    create index ix_userserv_test_1 on t_userserviceinfo_test(servstaus,upstatustime);
    
    create index ix_userserv_test_2 on t_userserviceinfo_test(upstatustime,servstaus);
    
  3. 使用第一个索引查找

    select /* +index(ix_userserv_test_1) */  *
    from t_userserviceinfo_test t where t.servstaus = 1 and t.upstatustime > sysdate -100;
    

    得到执行分析如下,看到一致读数量为 334

    数据的查找过程是:首先从 servstaus = 1,upstatustime = sysdate -100 开始,找到第一条满足 servstaus = 1,upstatustime > sysdate -100 的数据,然后在索引树叶子节点顺序查找,直到找到第一条不满足条件的数据(servstaus = 2),退出查找,这个过程中查找到的索引都是有效索引。

    1. 使用第二个索引查找:
    select /* +index(ix_userserv_test_2) */  *
    from t_userserviceinfo_test t where t.servstaus = 1 and t.upstatustime > sysdate -100;
    

    看到一致读是前面的十倍,效果很不好。

    数据超找过程是:根据 upstatustime > sysdate -100 走的范围索引扫描,同时通过 servstaus = 1 过滤数据,存在大量的无用查找。

总结:

建立组合索引要考虑自身以及其他场景的使用情况,不要随意指定顺序。

在存在大字段组合索引的情况下,可从以下几个方面进行 SQL 优化: ### 索引优化 - **合理创建索引**:为常用的查询字段(尤其是筛选条件字段)创建合适的索引。由于存在组合索引,要遵循最左匹配原则,组合索引的前导列一定是使用最频繁的列,使关键查询形成索引覆盖。例如,若组合索引为 (col1, col2, col3),查询条件为 `WHERE col1 = 'value1' AND col2 = 'value2'` 时可有效利用该组合索引。同时,因为存在大字段,要避免在大字段上创建索引,因为大字段索引会占用大量空间,且维护成本高 [^1][^3][^4]。 - **选择合适的索引类型**:可以使用聚集索引(Clustered Index)和非聚集索引(Non - clustered Index)来优化查询性能。聚集索引适用于排序、范围查询等,而非聚集索引适用于单一列或组合列的查询。但要注意避免过多索引,过多的索引会增加更新、插入和删除操作的成本,需要平衡索引的数量和性能,尤其是在有大字段的情况下,过多索引会进一步加重存储和维护负担 [^1]。 ### 避免索引失效 - **全值匹配**:保证查询条件与组合索引中的列全值匹配,这样能最大程度利用索引。 - **遵循最左匹配原则**:最左匹配原则只适用于使用组合索引的情况。当查询语句中有多个条件,且这些条件可以利用索引进行匹配时,要按照组合索引的列顺序使用条件。例如组合索引为 (col1, col2, col3),查询条件 `WHERE col1 = 'value1' AND col2 = 'value2'` 能利用索引,而 `WHERE col2 = 'value2' AND col3 = 'value3'` 可能无法完全利用该组合索引 [^4]。 - **不在索引列上做操作**:避免在索引列上进行函数运算、类型转换等操作,否则会导致索引失效。例如,`WHERE DATE(col1) = '2024-01-01'` 会使索引失效,应改为 `WHERE col1 >= '2024-01-01 00:00:00' AND col1 < '2024-01-02 00:00:00'`。同时,当查询字段为 `*` 时索引可能会失效,尽量明确指定查询字段;当查询字段为 `count()` 或者是索引字段索引不会失效 [^2]。 - **范围条件右边的索引失效**:在组合索引中,范围条件(如 `>`、`<`、`BETWEEN` 等)右边的索引会失效。例如组合索引为 (col1, col2, col3),查询条件 `WHERE col1 = 'value1' AND col2 > 'value2' AND col3 = 'value3'`,`col3` 索引会失效 [^2]。 - **避免其他导致索引失效的情况**:如 `mysql` 在使用不等于 (`!=` 或者 `<>`)、`is not null`、`like` 以通配符开头 (`%qw`)、字符串不加引号、使用 `or` 连接等情况时索引会失效,尽量避免这些情况 [^2]。 ### 其他优化策略 - **尽量使用覆盖索引**:覆盖索引是指查询的列刚好是索引的列,这样可以直接从索引中获取数据,无需回表查询,提高查询效率 [^2]。 - **排序与分组优化**:使用 `order by` 时可能会出现 `Using filesort`,使用 `group by` 时可能会出现 `Using temporary`,可通过合理设计索引和查询语句来优化。例如,确保 `order by` 和 `group by` 的列在组合索引中,减少额外的排序和临时表操作 [^2]。 - **大数据量分页优化**:对于大数据量的分页查询,可采用子查询优化使用 `id` 限定方案等方法,减少查询的数据量,提高响应时间 [^2]。 - **小表驱动大表**:在表关联查询、`in` 和 `exists` 查询时,尽量使用小表驱动大表,减少关联的数据量,提高查询效率 [^2]。 ### 示例代码 以下是一个简单的示例,展示如何创建组合索引和使用查询: ```sql -- 创建表 CREATE TABLE test_table ( col1 INT, col2 VARCHAR(50), col3 DATE, large_field TEXT ); -- 创建组合索引 CREATE INDEX idx_composite ON test_table (col1, col2, col3); -- 全值匹配查询 SELECT col1, col2, col3 FROM test_table WHERE col1 = 1 AND col2 = 'value2' AND col3 = '2024-01-01'; -- 遵循最左匹配原则的查询 SELECT col1, col2 FROM test_table WHERE col1 = 1 AND col2 = 'value2'; ```
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值