SQL优化（二）（联合索引的使用）

最新推荐文章于 2021-11-08 07:57:36 发布
最新推荐文章于 2021-11-08 07:57:36 发布
阅读量703 收藏
点赞数
分类专栏： 性能优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/dbadoudou/article/details/8314995
版权

SQL优化（二）（联合索引的使用）

问题1

1、从AWR中发现一条SQL性能较差，这条简单的SQL返回值仅仅一行缺消耗7000+cost觉不能忍受，决定优化！

SQL> select count(*) from user_doudou where aclass=‘10001’ and email='14040928@qq.com';

 

  COUNT(*)

----------

         1

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2038320426

 

----------------------------------------------------------------------------------

| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |             |     1 |    40 |  7709   (2)| 00:01:33 |

|   1 |  SORT AGGREGATE    |             |     1 |    40 |            |          |

|*  2 |   TABLE ACCESS FULL| USER_DOUDOU |   260 | 10400 |  7709   (2)| 00:01:33 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("ACLASS"=10001 AND "EMAIL"='14040928@qq.com')

 

Note

-----

   - dynamic sampling used for this statement

 

 

Statistics

----------------------------------------------------------

        238  recursive calls

          0  db block gets

      34644  consistent gets

      33152  physical reads

          0  redo size

        515  bytes sent via SQL*Net to client

        469  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          5  sorts (memory)

          0  sorts (disk)

          1  rows processed

【由SQL的执行计划：

1、  执行了全表扫描：TABLE ACCESS FULL几乎占了所有的COST

2、  如果建立索引，应给指定access；最简单指定access的方法就是把where谓语中的选择性高的列作为索引列（也可以从filter条件中选取列作为索引，一个原则选择度越高越好）】

 

2、建立索引

         2-1、查看数据总行、消耗cost、表基本结构

SQL> select count(*) from user_doudou;

 

  COUNT(*)

----------

   2419271

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2038320426

 

--------------------------------------------------------------------------

| Id  | Operation          | Name        | Rows  | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |             |     1 |  7675   (1)| 00:01:33 |

|   1 |  SORT AGGREGATE    |             |     1 |            |          |

|   2 |   TABLE ACCESS FULL| USER_DOUDOU |  2604K|  7675   (1)| 00:01:33 |

--------------------------------------------------------------------------

SQL> set line 100

SQL> desc user_doudou

 Name                                                  Null?    Type

 ----------------------------------------------------- -------- ------------------------------------

 ACLASS                                                NOT NULL NUMBER

 SERIALNO                                              NOT NULL VARCHAR2(16)

 PASSWORD                                                       VARCHAR2(20)

 EMAIL                                                 NOT NULL VARCHAR2(50)

 STUDENTNO                                                      VARCHAR2(20)

 STUDENTNAME                                                    VARCHAR2(50)

 UNIVERSITY                                                     VARCHAR2(5)

 SENDTYPE                                                       CHAR(1)

 STATE                                                 NOT NULL CHAR(1)

 SKILLDATE                                                      VARCHAR2(4)

 INDATE                                                         DATE

 IMPORTNO                                                       NUMBER

 OVERFLAG                                                       CHAR(1)

 SENDCOUNT                                                      NUMBER(2)

 STATE_OLD                                                      VARCHAR2(2)

 EMAILSTATUS                                                    VARCHAR2(1)

 REFUSED                                                        VARCHAR2(1)

 INVALID                                                        VARCHAR2(1)

 LOG_TIME                                                       DATE

 

         2-2、建立模拟环境并查看数据总行及消耗cost，并查看我们需要调优SQL的cost消耗

SQL> create index idx_doudou_serialno on user_doudou (serialno) online compute statistics;

 

Index created.

SQL> select count(*) from user_doudou;

 

  COUNT(*)

----------

   2419271

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 863670098

 

-------------------------------------------------------------------------------------

| Id  | Operation             | Name                | Rows  | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |                     |     1 |  1471   (3)| 00:00:18 |

|   1 |  SORT AGGREGATE       |                     |     1 |            |          |

|   2 |   INDEX FAST FULL SCAN| IDX_DOUDOU_SERIALNO |  2604K|  1471   (3)| 00:00:18 |

-------------------------------------------------------------------------------------

 

Note

-----

   - dynamic sampling used for this statement

 

Statistics

----------------------------------------------------------

          5  recursive calls

          0  db block gets

       6523  consistent gets

       6691  physical reads

          0  redo size

        518  bytes sent via SQL*Net to client

        469  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

         2-3、生产库上的索引在这条SQL没有被使用，还是全表扫描

SQL> select count(*) from user_doudou where aclass=‘10001’ and email='14040928@qq.com';

 

  COUNT(*)

----------

         1

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2038320426

 

----------------------------------------------------------------------------------

| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |             |     1 |    40 |  7709   (2)| 00:01:33 |

|   1 |  SORT AGGREGATE    |             |     1 |    40 |            |          |

|*  2 |   TABLE ACCESS FULL| USER_DOUDOU |   260 | 10400 |  7709   (2)| 00:01:33 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("ACLASS"=10001 AND "EMAIL"='14040928@qq.com')

 

Note

-----

   - dynamic sampling used for this statement

 

 

Statistics

----------------------------------------------------------

        238  recursive calls

          0  db block gets

      34644  consistent gets

      33152  physical reads

          0  redo size

        515  bytes sent via SQL*Net to client

        469  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          5  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

         2-4、根据我们的SQL谓语，及列的数据分布，决定建立联合索引

SQL> create index idx_doudou_aclass on user_doudou(aclass,email) online  compute statistics;--（建立索引并收集统计信息）

 

Index created.

 

         2-5、联合索引被建立之后，SQL的COST从7000+下降到3，这个结果说明效果还是不错的

SQL> select count(*) from user_doudou where aclass=‘10001’ and email='14040928@qq.com';

 

  COUNT(*)

----------

         1

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1829380857

 

---------------------------------------------------------------------------------------

| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                   |     1 |    40 |     3   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE   |                   |     1 |    40 |            |          |

|*  2 |   INDEX RANGE SCAN| IDX_DOUDOU_ACLASS |     1 |    40 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("ACLASS"=10001 AND "EMAIL"='14040928@qq.com')

 

Note

-----

   - dynamic sampling used for this statement

 

 

Statistics

----------------------------------------------------------

          9  recursive calls

          0  db block gets

         88  consistent gets

        373  physical reads

          0  redo size

        515  bytes sent via SQL*Net to client

        469  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

问题2

         如果我们没有使用联合索引，给谓语中的列都建立了单独索引。效果如何呢?

SQL> drop index   IDX_DOUDOU_ACLASS;

 

Index dropped.

 

SQL> create index idx_dou_class on user_doudou(aclass) online;

 

Index created.

 

SQL> create index idx_dou_email on user_doudou(email) online;

 

Index created.

 

SQL> select count(*) from user_doudou where aclass=‘10001’ and email='14040928@qq.com';

 

  COUNT(*)

----------

         1

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 202351326

 

----------------------------------------------------------------------------------------------

| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |               |     1 |    40 |   356   (0)| 00:00:05 |

|   1 |  SORT AGGREGATE              |               |     1 |    40 |            |          |

|*  2 |   TABLE ACCESS BY INDEX ROWID| USER_DOUDOU   |   260 | 10400 |   356   (0)| 00:00:05 |

|*  3 |    INDEX RANGE SCAN          | IDX_DOU_EMAIL |   382 |       |     4   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("ACLASS"=10001)

   3 - access("EMAIL"='14040928@qq.com')

 

Note

-----

   - dynamic sampling used for this statement

 

 

Statistics

----------------------------------------------------------

         75  recursive calls

          0  db block gets

        114  consistent gets

        314  physical reads

          0  redo size

        515  bytes sent via SQL*Net to client

        469  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 【单独给每个列建立索引的效果明显不如联合索引。但是这绝不是说联合索引就比单独索引性能好，这里还要考虑到数据分布和选择度】

         

问题3

         统计分析索引有必要么？下面小实验简略说明一下

SQL> select * from  user_doudou where aclass=‘10001’ and email='14040928@qq.com';

Execution Plan

----------------------------------------------------------

Plan hash value: 3373022615

 

---------------------------------------------------------------------------------------------

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |               |   260 | 44460 |   356   (0)| 00:00:05 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| USER_DOUDOU   |   260 | 44460 |   356   (0)| 00:00:05 |

|*  2 |   INDEX RANGE SCAN          | IDX_DOU_EMAIL |   382 |       |     4   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("ACLASS"=10001)

   2 - access("EMAIL"='14040928@qq.com')

 

Note

-----

   - dynamic sampling used for this statement

 

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          5  consistent gets

          0  physical reads

          0  redo size

       1796  bytes sent via SQL*Net to client

        469  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL> exec dbms_stats.gather_table_stats(user,'user_doudou',cascade=>true);

 

PL/SQL procedure successfully completed.

 

SQL> select * from  user_doudou where aclass=‘10001’ and email='14040928@qq.com';

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3373022615

 

---------------------------------------------------------------------------------------------

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |               |     1 |    98 |     4   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| USER_DOUDOU   |     1 |    98 |     4   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_DOU_EMAIL |     1 |       |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("ACLASS"=10001)

   2 - access("EMAIL"='14040928@qq.com')

 

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          5  consistent gets

          0  physical reads

          0  redo size

       1796  bytes sent via SQL*Net to client

        469  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

1         rows processed

【统计收集索引信息前后是差别的相差了近4倍左右，统计收集索引信息的效果随环境不同而变化，但是绝对是有必要的】

 

 

总结：

1、  索引建立：1、选择度高的列上建立索引

                                2、在where谓语中，选择建立索引的列

                                 3、有类似有index skip scan的条件，建议建立联合索引

 2、统计收集索引信息是有必要的，建议使用dbms_stats包收集

lovehewenyu
关注
专栏目录
（三）深入理解SQL优化与掌握索引使用
h363659487的博客
01-15 145
SQL优化和索引分析
联合索引优化多条件查询
热门推荐
黄复贵的博客
07-19 1万+
联合索引是由多个字段组成的组合索引。若经常需要使用多个字段的多条件查询（WHERE col1 = … AND col2 = … AND col3 = …），可以考虑使用联合索引。现在数据表myIndex中i_testID是主键列，其他列无任何索引：多条件查找名字为xiaoming，城市为beijing，年龄为21的人：返回了一行数据，从执行计划中看到，查询没有使用任何索引，进行了全表扫描，磁盘IO大
sql优化中出现的索引合并问题(多条件查询时使用单索引还是联合索引)
12-14
年前从深圳这边的公司离职了，然后回西安上周五新入职的新公司。然后入职的第一个任务居然是优化SQL。 有如下两条SQL， select sum(silvers_num) from tb_video_cost_log where anchor_id = 205690298267862016 and consume_scene = 1; select sum(silvers_num) from tb_video_cost_log where anchor_id = 205690298267862016  and consume_scene > 0 and consume_scene < 2; 其中t
sql优化之联合索引优化
RichardDragon的博客
03-22 2066
一、问题描述： 项目有一个多条件查询的sql语句，因数据量比较大，查询非常慢，就考虑对条件查询的字段加入联合索引。加入索引， 之后对所有条件字段包含在内的sql语句进行explain，确实走了联合索引。然后，减少依次减少条件字段，再进行explain， 发现当去掉联合索引中的第一位字段条件时，sql不走联合索引，仅仅使用了id的主键。（多次测试发现的） 就得出结...
mysql第二个索引_MySQL高级第二章——索引优化分析
weixin_39870238的博客
02-19 159
一、SQL性能下降原因1.等待时间长？执行时间长？可能原因：查询语句写的不行索引失效(单值索引、复合索引)CREATE INDEX index_user_name ON user(name);(底层做了一个排序)CREATE INDEX index_user_nameEmail ON user(name,email);查询关联join太多太乱服务器调优参数的设置...二、常见join连接查询由上文...
联合主键下的聚簇索引
AF_0228的博客
09-09 1126
懂的越多，不懂的越多 上次提到，mysql创建表结构时，如果使用的不是单一主键，而是联合主键，那么主键对应的索引会如何建立哪？没有实践，就没有发言权，今天就来进行一番彻底的比对实验把！ create table test1(id1 int Not Null,id2 int Not Null, Primary Key (id1, id2),val int); create table test2(id1 int Not Null,id2 int Not Null, Primary Key (id2, id.
sql复合索引使用和注意事项
mysevenyear的博客
05-31 1万+
复合索引的优点和注意事项 概念:     单一索引是指索引列为一列的情况,即新建索引的语句只实施在一列上;     用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引);     复合索引在数据库操作期间所需的开销更小,可以代替多个单一索引;     同时有两个概念叫做窄索引和宽索引,窄索引是指索引列为1-2列的索引,宽索引也就是索引列超过2列的索引;     设计索引的一个重要原则就是能...
通过联合索引优化MYSQL慢查询
12-14
通过联合索引优化MYSQL慢查询 一、慢SQL分析 慢SQL如下 SELECT SUM(t.amount) as amount FROM (SELECT game_id,room_id,is_robot,amount,record_time FROM t_statements_logs WHERE game_id = 30 AND is_robot = 0 ...
MySQL不走联合索引_SQL优化,索引原则,及mysql不走索引的原因分析
weixin_39783915的博客
01-18 902
SQL语句优化1 企业SQL优化思路1、把一个大的不使用索引的SQL语句按照功能进行拆分2、长的SQL语句无法使用索引，能不能变成2条短的SQL语句让它分别使用上索引。3、对SQL语句功能的拆分和修改4、减少“烂”SQL由运维(DBA)和开发交流(确认)，共同确定如何改，最终由DBA执行5、制定开发流程2 不适合走索引的场景1、唯一值少的列上不适合建立索引或者建立索引效率低。例如：性别列2、小表可...
SQL性能优化策略之联合索引优化方法
华章IT官方博客
11-08 690
导读：SQL优化是优化工作中经常会涉及的问题，之前给大家介绍了SQL性能优化策略之索引优化方法。本文以实际案例为大家介绍联合索引优化方法。案例：一条很简单的SQL语句明明选择了索引扫描，但...
SQL联合索引
dycparty的博客
04-04 2502
1):查询条件中出现联合索引第一列,或者全部,则能利用联合索引. 2):条件列中只要条件相连在一起,以本文例子来说就是: last_name=’1′ and first_name=’1′ 与 first_name=’1′ and last_name=’1′ ,无论前后,都会利用上联合索引. 3):查询条件中没有出现联合索引的第一列,而出现联合索引的第二列,或者第
SQL索引一步到位
weixin_30239339的博客
01-04 3691
　　　　　　　　SQL索引一步到位（此文章为“数据库性能优化二：数据库表优化”附属文章之一） 　　SQL索引在数据库优化中占有一个非常大的比例，一个好的索引的设计，可以让你的效率提高几十甚至几百倍，在这里将带你一步步揭开他的神秘面纱。 　　1.1什么是索引？ 　　SQL索引有两种，聚集索引和非聚集索引，索引主要目的是提高了SQLServer系统的性能，加快数据的查询速度...
oracle索引管理-online创建和重建索引
oradbm的博客
07-30 1364
Create unique index idx1_col on table_NAME(COLUMN1，COLUMN2，….) LOCAL ONLINE; SELECT 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' REBUILD ONLINE;' EXEC_STATEMENT, STATUS FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE' union all...
SQL联合索引 与 单一列的索引
绝对秋香
10-21 509
背景:目前WEB的普及太快,很多网站都会因为大流量的数据而发生服务器习惯性死机,一个查询语句只能适用于一定的网络环境.没有优化的查询当遇上大数据量时就不适用了.   联合索引使用结论:    1):查询条件中出现联合索引第一列,或者全部,则能利用联合索引.    2):条件列中只要条件相连在一起,以本文例子来说就是:    last_name=’1′ and first_name...
【mysql知识点整理】 --- SQL优化策略整理
nrsc
03-04 880
文章目录数据准备 + 理论基础策略1 --- 尽量全职匹配策略2 --- 最佳左前缀法则策略3 --- 不要在索引列上做任何操作策略4 --- 范围条件放最后策略5 --- 覆盖索引尽量用策略6 --- 不等于要甚用策略7 --- is null（使用）、is not null（不使用）有影响策略8 --- Like查询要当心策略9 --- 字符类型加引号策略10 --- OR改UNION效率高 ...
sql优化，关于联合索引某个中间字段使用>（大于）号，导致后面索引失效的理解
weixin_42935902的博客
07-21 8636
大于号导致索引失效的原因? mysql先用=号去查数据，找到最后的叶子节点，然后往后推，找大于的所有数据 如果在字段在中间使用大于号，没有通过索引走到底层的叶子节点，那么取出来的数据没有精确到某一个数据上，只是在某个范围里面。形成一个临时表，然后根据后面的条件在这个临时表里面取值。而后面的条件不能使用索引，所以后面的字段自然是索引失效。因为后面的字段使用索引的前提条件是，前面的字段精确到了某一个节...
sql 设置联合索引，及联合唯一索引怎么写
jerry的博客
08-29 1万+
alter table 表名 add unique index 索引名(字段1,字段2,字段3) 如果设置联合索引 alter table 表名 add index 索引名(字段1,字段2,字段3)
mysql 联合索引怎么用_一个案例彻底弄懂如何正确使用 mysql inndb 联合索引
weixin_30865253的博客
01-19 585
原标题：一个案例彻底弄懂如何正确使用 mysql inndb 联合索引有一个业务是查询最新审核的5条数据查看当时的监控情况 cpu 使用率是超过了100%，show processlist看到很多类似的查询都是处于create sort index的状态。查看该表的结构 索引有一个audit_time在左边的联合索引，没有关于status的索引。分析上面的sql执行的逻辑：从联合索引里找到所有小于...
Oracle SQL性能优化：索引使用与优化策略
除了索引的使用，还有其他优化方法，如SQL语句排序优化，选择合适的联合查询顺序，以及调整SQL子查询。对于函数索引，例如在处理日期时，如果经常需要查询特定年份，可以创建基于to_char函数的索引，以提高查询效率...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值