测试组合索引

最新推荐文章于 2022-06-29 20:47:24 发布
最新推荐文章于 2022-06-29 20:47:24 发布
阅读量71 收藏
点赞数
文章标签： 数据库 java php
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_33881041/article/details/85124869
版权

测试背景：生产数据库的会员表和产品表中存在三个CLOB字段(这些字段主要用来存会员和产品的描述信息)，随着数据量的增长，全表扫描的时候，I/O等待严重，于是想到分表！

提出问题：这两张大表，加起来的索引总共有35个，其中会员表的索引数为17个，产品表的索引数为18个(35个索引中不包含LOB字段的索引)，这些索引大部分为组合索引，且存在函数索引(函数索引均为组合索引的前导列进行desc排序)

索引优化测试：在测试库中进行优化测试，原始会员表用test代替，用户schema用hr代替，数据库版本为11.2.0.3 32bit on widows2003 enterprise edition

一:建表,添加主键约束和组合索引1

 
 
  1. SQL> create table t1 as select * from test;  
  2.  
  3. SQL> alter table t1 add constraint pk_t1_id primary key (id);  
  4. Table altered.  
  5.  
  6. SQL> create index idx_t1_mul1 on t1(status,registersource);  
  7. Index created. 
 
 
二：收集表统计信息,对status字段的空值进行赋值，status字段为会员的状态，1为正常状态，其他值均为异常状态，所以值的分布倾斜
 
 
 
 
  1. SQL> update t1 set status=0 where status is null;  
  2. 4 rows updated.  
  3.  
  4. SQL> commit;  
  5. Commit complete.  
  6.  
  7. SQL> exec dbms_stats.gather_table_stats(‘HR’,'T1',CASCADE=>true);  
  8. PL/SQL procedure successfully completed.  
  9.  
  10. SQL> select status,count(*) from t1 group by status;  
  11.  
  12.     STATUS   COUNT(*)  
  13. ---------- ----------  
  14.          1     595612  
  15.          2       1230  
  16.          4         10  
  17.          5          1  
  18.          3       2746  
  19.          0      26825  
  20. 6 rows selected. 
 
 
三：测试证明结果集比较小的情况下，where字句中包含前导列的时候就会使用组合索引
 
 
 
 
  1. SQL> select id,version from t1 where status=0;  
  2.  
  3. Execution Plan  
  4. ----------------------------------------------------------  
  5. Plan hash value: 1745128362  
  6. -------------------------------------------------------------------------------------------  
  7. | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|Time    |  
  8. -------------------------------------------------------------------------------------------  
  9. |   0 | SELECT STATEMENT            |             | 26939 |   289K|  6051   (1)|00:01:13 |  
  10. |   1 |  TABLE ACCESS BY INDEX ROWID| T1          | 26939 |   289K|  6051   (1)|00:01:13 |  
  11. |*  2 |   INDEX RANGE SCAN          | IDX_T1_MUL1 | 26939 |       |    93   (0)|00:00:02 |  
  12. -------------------------------------------------------------------------------------------  
  13. Predicate Information (identified by operation id):  
  14. ---------------------------------------------------  
  15.    2 - access("STATUS"=0) 
 
 
四：建组合索引2(组合索引2的字段同组合索引1一样，只是前导列对调)，对registersource字段的空值进行赋值，收集表的统计信息；(该字段用来存储用户的注册来源，值分布也存在倾斜的情况)
 
 
 
 
  1. SQL> create index idx_t1_mul2 on t1(registersource,status);  
  2. Index created.  
  3.  
  4. SQL> select registersource,count(*) from t1 group by registersource;  
  5.  
  6. REGIST   COUNT(*)  
  7. ------ ----------  
  8. 1             156  
  9.                 4  
  10. 3            1689  
  11. 6               4  
  12. 0           23487  
  13. 2          601084  
  14. 6 rows selected.  
  15.  
  16. SQL> update t1 set registersource=6 where registersource is null;  
  17. 4 rows updated  
  18.  
  19. SQL>commit；  
  20. Commit complete.  
  21.  
  22. SQL> exec dbms_stats.gather_table_stats(‘HR’,'T1',CASCADE=>true);  
  23. PL/SQL procedure successfully completed. 
 
 
五：只查询registersource字段且结果集小的时候，执行计划采用组合索引2 
 
 
 
 
  1. SQL> select  * from t1 where registersource='1'; //这里registersource字段为varchar2类型，故而加引号  
  2.  
  3. Execution Plan  
  4. ----------------------------------------------------------  
  5. Plan hash value: 2744963562  
  6. -------------------------------------------------------------------------------------------  
  7. | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|Time     |  
  8. -------------------------------------------------------------------------------------------  
  9. |   0 | SELECT STATEMENT            |             |   227 |   287K|    49   (0)|00:00:01 |  
  10. |   1 |  TABLE ACCESS BY INDEX ROWID| T1          |   227 |   287K|    49   (0)|00:00:01 |  
  11. |*  2 |   INDEX RANGE SCAN          | IDX_T1_MUL2 |   227 |       |     3   (0)|00:00:01 |  
  12. ------------------------------------------------------------------------------------------  
  13. Predicate Information (identified by operation id):  
  14. ---------------------------------------------------  
  15.    2 - access("REGISTERSOURCE"='1') 
 
 
六：倾斜组合测试
 
 
 
 
  1. 1：小小  
  2. SQL> select * from t1 where status=0 and registersource=0;  
  3.  
  4. Execution Plan  
  5. ----------------------------------------------------------  
  6. Plan hash value: 1745128362  
  7. -------------------------------------------------------------------------------------------  
  8. | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|Time    |  
  9. -------------------------------------------------------------------------------------------  
  10. |   0 | SELECT STATEMENT            |             |  1057 |  1337K|   280   (0)|00:00:04 |  
  11. |   1 |  TABLE ACCESS BY INDEX ROWID| T1          |  1057 |  1337K|   280   (0)|00:00:04 |  
  12. |*  2 |   INDEX RANGE SCAN          | IDX_T1_MUL1 |  1057 |       |    68   (0)|00:00:01 |  
  13. -------------------------------------------------------------------------------------------  
  14. Predicate Information (identified by operation id):  
  15. ---------------------------------------------------  
  16.    2 - access("STATUS"=0)  
  17.        filter(TO_NUMBER("REGISTERSOURCE")=0)  
  18.  
  19. SQL> select * from t1 where registersource=0 and status=0;  
  20.  
  21. Execution Plan  
  22. ----------------------------------------------------------  
  23. Plan hash value: 1745128362  
  24. -------------------------------------------------------------------------------------------  
  25. | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|Time    |  
  26. -------------------------------------------------------------------------------------------  
  27. |   0 | SELECT STATEMENT            |             |  1057 |  1337K|   280   (0)|00:00:04 |  
  28. |   1 |  TABLE ACCESS BY INDEX ROWID| T1          |  1057 |  1337K|   280   (0)|00:00:04 |  
  29. |*  2 |   INDEX RANGE SCAN          | IDX_T1_MUL1 |  1057 |       |    68   (0)|00:00:01 |  
  30. -------------------------------------------------------------------------------------------  
  31. Predicate Information (identified by operation id):  
  32. ---------------------------------------------------  
  33.    2 - access("STATUS"=0)  
  34.        filter(TO_NUMBER("REGISTERSOURCE")=0)  
  35.  
  36. 2:小大  
  37. SQL> select * from t1 where status=0 and registersource=2;  
  38.  
  39. Execution Plan  
  40. ----------------------------------------------------------  
  41. Plan hash value: 1745128362  
  42. -------------------------------------------------------------------------------------------  
  43. | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|Time    |  
  44. -------------------------------------------------------------------------------------------  
  45. |   0 | SELECT STATEMENT            |             | 28531 |    35M|  5776   (1)|00:01:10 |  
  46. |   1 |  TABLE ACCESS BY INDEX ROWID| T1          | 28531 |    35M|  5776   (1)|00:01:10 |  
  47. |*  2 |   INDEX RANGE SCAN          | IDX_T1_MUL1 | 28531 |       |    68   (0)|00:00:01 |  
  48. -------------------------------------------------------------------------------------------  
  49. Predicate Information (identified by operation id):  
  50. ---------------------------------------------------  
  51.    2 - access("STATUS"=0)  
  52.        filter(TO_NUMBER("REGISTERSOURCE")=2)  
  53.  
  54. SQL> select * from t1 where  registersource=2 and status=0;  
  55.  
  56. Execution Plan  
  57. ----------------------------------------------------------  
  58. Plan hash value: 1745128362  
  59. -------------------------------------------------------------------------------------------  
  60. | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time   |  
  61. -------------------------------------------------------------------------------------------  
  62. |   0 | SELECT STATEMENT            |             | 28531 |    35M|  5776   (1)|00:01:10 |  
  63. |   1 |  TABLE ACCESS BY INDEX ROWID| T1          | 28531 |    35M|  5776   (1)|00:01:10 |  
  64. |*  2 |   INDEX RANGE SCAN          | IDX_T1_MUL1 | 28531 |       |    68   (0)|00:00:01 |  
  65. -------------------------------------------------------------------------------------------  
  66. Predicate Information (identified by operation id):  
  67. ---------------------------------------------------  
  68.    2 - access("STATUS"=0)  
  69.        filter(TO_NUMBER("REGISTERSOURCE")=2)  
  70.  
  71. 3:大小  
  72. SQL> select * from t1 where status=1 and registersource=0;  
  73.  
  74. Execution Plan  
  75. ----------------------------------------------------------  
  76. Plan hash value: 1106331959  
  77. -------------------------------------------------------------------------------------------  
  78. | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|Time    |  
  79. -------------------------------------------------------------------------------------------  
  80. |   0 | SELECT STATEMENT            |             | 21105 |    26M|  4659   (1)|00:00:56 |  
  81. |   1 |  TABLE ACCESS BY INDEX ROWID| T1          | 21105 |    26M|  4659   (1)|00:00:56 |  
  82. |*  2 |   INDEX SKIP SCAN           | IDX_T1_MUL2 | 21105 |       |   437   (0)|00:00:06 |  
  83. ------------------------------------------------------------------------------------------  
  84. Predicate Information (identified by operation id):  
  85. ---------------------------------------------------  
  86.    2 - access("STATUS"=1)  
  87.        filter(TO_NUMBER("REGISTERSOURCE")=0 AND "STATUS"=1)  
  88.  
  89. SQL> select * from t1 where  registersource=0 and status=1;  
  90.  
  91. Execution Plan  
  92. ----------------------------------------------------------  
  93. Plan hash value: 1106331959  
  94. -------------------------------------------------------------------------------------------  
  95. | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|Time    |  
  96. -------------------------------------------------------------------------------------------  
  97. |   0 | SELECT STATEMENT            |             | 21105 |    26M|  4659   (1)|00:00:56 |  
  98. |   1 |  TABLE ACCESS BY INDEX ROWID| T1          | 21105 |    26M|  4659   (1)|00:00:56 |  
  99. |*  2 |   INDEX SKIP SCAN           | IDX_T1_MUL2 | 21105 |       |   437   (0)|00:00:06 |  
  100. -------------------------------------------------------------------------------------------  
  101. Predicate Information (identified by operation id):  
  102. ---------------------------------------------------  
  103.    2 - access("STATUS"=1)  
  104.        filter(TO_NUMBER("REGISTERSOURCE")=0 AND "STATUS"=1)  
  105.  
  106. 4:大大  
  107. SQL> select * from t1 where status=1 and registersource=2;  
  108.  
  109. Execution Plan  
  110. ----------------------------------------------------------  
  111. Plan hash value: 3617692013  
  112. --------------------------------------------------------------------------  
  113. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  114. --------------------------------------------------------------------------  
  115. |   0 | SELECT STATEMENT  |      |   569K|   703M| 32570   (1)| 00:06:31 |  
  116. |*  1 |  TABLE ACCESS FULL| T1   |   569K|   703M| 32570   (1)| 00:06:31 |  
  117. --------------------------------------------------------------------------  
  118.  
  119. Predicate Information (identified by operation id):  
  120. ---------------------------------------------------  
  121.    1 - filter("STATUS"=1 AND TO_NUMBER("REGISTERSOURCE")=2)  
  122.  
  123. SQL> select * from t1 where  registersource=2 and status=1;  
  124. Execution Plan  
  125. ----------------------------------------------------------  
  126. Plan hash value: 3617692013  
  127. --------------------------------------------------------------------------  
  128. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  129. --------------------------------------------------------------------------  
  130. |   0 | SELECT STATEMENT  |      |   569K|   703M| 32570   (1)| 00:06:31 |  
  131. |*  1 |  TABLE ACCESS FULL| T1   |   569K|   703M| 32570   (1)| 00:06:31 |  
  132. --------------------------------------------------------------------------  
  133.  
  134. Predicate Information (identified by operation id):  
  135. ---------------------------------------------------  
  136.    1 - filter("STATUS"=1 AND TO_NUMBER("REGISTERSOURCE")=2) 
 
 
测试结果表明，cbo将根据结果集的大小来决定使用最优的执行计划
 
 
七：组合索引和其他字段的配合使用(测试结果说明where子句中包含组合索引的前导列，且结果集小的情况下，执行计划会选择走组合索引)
 
 
 
 
  1. SQL> select * from t1 where status=0 and createddate < sysdate;  
  2. Execution Plan  
  3. ----------------------------------------------------------  
  4. Plan hash value: 1745128362  
  5. -------------------------------------------------------------------------------------------  
  6. | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  
  7. -------------------------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT            |             | 29698 |    36M|  6009   (1)|00:01:13 |  
  9. |*  1 |  TABLE ACCESS BY INDEX ROWID| T1          | 29698 |    36M|  6009   (1)|00:01:13 |  
  10. |*  2 |   INDEX RANGE SCAN          | IDX_T1_MUL1 | 29698 |       |    68   (0)|00:00:01 |  
  11. -------------------------------------------------------------------------------------------  
  12. Predicate Information (identified by operation id):  
  13. ---------------------------------------------------  
  14.  
  15.    1 - filter("CREATEDDATE"<SYSDATE@!)  
  16.    2 - access("STATUS"=0)  
  17.  
  18. SQL> select * from t1 where registersource='0' and createddate < sysdate;  
  19.  
  20. Execution Plan  
  21. ----------------------------------------------------------  
  22. Plan hash value: 2744963562  
  23. -------------------------------------------------------------------------------------------  
  24. | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  
  25. -------------------------------------------------------------------------------------------  
  26. |   0 | SELECT STATEMENT            |             | 22302 |    27M|  4514   (1)|00:00:55 |  
  27. |*  1 |  TABLE ACCESS BY INDEX ROWID| T1          | 22302 |    27M|  4514   (1)|00:00:55 |  
  28. |*  2 |   INDEX RANGE SCAN          | IDX_T1_MUL2 | 22302 |       |    52   (0)|00:00:01 |  
  29. -------------------------------------------------------------------------------------------  
  30. Predicate Information (identified by operation id):  
  31. ---------------------------------------------------  
  32.    1 - filter("CREATEDDATE"<SYSDATE@!)  
  33.    2 - access("REGISTERSOURCE"='0') 
 
 
八：函数索引测试，对orderno,membership.status,featured四个字段建组合索引3，同时orderno字段进行desc排序；对前面4个字段建立普通的组合索引4，这种做法意在测试是否有必要使用函数索引？
 
 
 
 
  1. SQL> create index idx_t1_mul3 on t1(orderno desc,membership,status,featured);  
  2. Index created.  
  3.  
  4. SQL> select * from t1 where membership=1 and status=0 order by orderno;  
  5. no rows selected  
  6.  
  7. Execution Plan  
  8. ----------------------------------------------------------  
  9. Plan hash value: 687259109  
  10. -------------------------------------------------------------------------------------------  
  11. | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|Time     |  
  12. -------------------------------------------------------------------------------------------  
  13. |   0 | SELECT STATEMENT            |             |  5940 |  7517K|  3009   (1)|00:00:37 |  
  14. |   1 |  TABLE ACCESS BY INDEX ROWID| T1          |  5940 |  7517K|  3009   (1)|00:00:37 |  
  15. |*  2 |   INDEX FULL SCAN DESCENDING| IDX_T1_MUL3 |  5940 |       |  1825   (1)|00:00:22 |  
  16. -------------------------------------------------------------------------------------------  
  17. Predicate Information (identified by operation id):  
  18. ---------------------------------------------------  
  19.  
  20.    2 - access("MEMBERSHIP"=1 AND "STATUS"=0)  
  21.        filter("STATUS"=0 AND "MEMBERSHIP"=1)  
  22.  
  23. SQL> select * from t1 where membership=1 and status=0 order by orderno desc;  
  24. no rows selected  
  25.  
  26. Execution Plan  
  27. ----------------------------------------------------------  
  28. Plan hash value: 607735922  
  29. -------------------------------------------------------------------------------------------  
  30. | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|Time     |  
  31. -------------------------------------------------------------------------------------------  
  32. |   0 | SELECT STATEMENT            |             |  5940 |  7517K|  3009   (1)|00:00:37 |  
  33. |   1 |  TABLE ACCESS BY INDEX ROWID| T1          |  5940 |  7517K|  3009   (1)|00:00:37 |  
  34. |*  2 |   INDEX FULL SCAN           | IDX_T1_MUL3 |  5940 |       |  1825   (1)|00:00:22 |  
  35. -------------------------------------------------------------------------------------------  
  36. Predicate Information (identified by operation id):  
  37. ---------------------------------------------------  
  38.  
  39.    2 - access("MEMBERSHIP"=1 AND "STATUS"=0)  
  40.        filter("STATUS"=0 AND "MEMBERSHIP"=1)  
  41.  
  42. SQL> drop index idx_t1_mul3;  
  43. Index dropped.  
  44.  
  45. SQL> create index idx_t1_mul4 on t1(orderno,membership,status,featured);  
  46. Index created.  
  47.  
  48. SQL> select * from t1 where membership=1 and status=0 order by orderno;  
  49. no rows selected  
  50.  
  51. Execution Plan  
  52. ----------------------------------------------------------  
  53. Plan hash value: 3155127807  
  54. -------------------------------------------------------------------------------------------  
  55. | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|Time     |  
  56. -------------------------------------------------------------------------------------------  
  57. |   0 | SELECT STATEMENT            |             |  5940 |  7517K|  1923   (1)|00:00:24 |  
  58. |   1 |  TABLE ACCESS BY INDEX ROWID| T1          |  5940 |  7517K|  1923   (1)|00:00:24 |  
  59. |*  2 |   INDEX SKIP SCAN           | IDX_T1_MUL4 |  5940 |       |   739   (0)|00:00:09 |  
  60. -------------------------------------------------------------------------------------------  
  61. Predicate Information (identified by operation id):  
  62. ---------------------------------------------------  
  63.    2 - access("MEMBERSHIP"=1 AND "STATUS"=0)  
  64.        filter("STATUS"=0 AND "MEMBERSHIP"=1)  
  65.  
  66. SQL> select * from t1 where membership=1 and status=0 order by orderno desc;  
  67. no rows selected  
  68.  
  69. Execution Plan  
  70. ----------------------------------------------------------  
  71. Plan hash value: 893632694  
  72. -------------------------------------------------------------------------------------------  
  73. | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|Time     |  
  74. -------------------------------------------------------------------------------------------  
  75. |   0 | SELECT STATEMENT            |             |  5940 |  7517K|  1923   (1)|00:00:24 |  
  76. |   1 |  TABLE ACCESS BY INDEX ROWID| T1          |  5940 |  7517K|  1923   (1)|00:00:24 |  
  77. |*  2 |   INDEX SKIP SCAN DESCENDING| IDX_T1_MUL4 |  5940 |       |   739   (0)|00:00:09 |  
  78. -------------------------------------------------------------------------------------------  
  79. Predicate Information (identified by operation id):  
  80. ---------------------------------------------------  
  81.    2 - access("MEMBERSHIP"=1 AND "STATUS"=0)  
  82.        filter("STATUS"=0 AND "MEMBERSHIP"=1) 
 
 
从测试结果上看，不使用函数索引的效果较优，同时去掉这类的函数索引，会方便今后的shrink table操作
 
 
九：不使用前导列情况下，组合索引是否能正常使用？在前面测试函数索引的过程中，where条件中不含前导列，只是对结果集进行排序的时候使用前导列，执行计划选择组合索引，因而进行如下测试！
 
 
 
 
  1. SQL> create index idx_t1_mul5 on t1(password,signinid);  
  2. Index created.  
  3.  
  4. SQL> select * from (select signinid,count(*) from t1 group by signinid) where rownum<10;  
  5.  
  6. SIGNINID                                                       COUNT(*)  
  7. ------------------------------------------------------------ ----------  
  8. 000000yu                                                              1  
  9. 0000410265269                                                         1  
  10. 00006789                                                              1  
  11. 00009746                                                              1  
  12. 000113                                                                1  
  13. 0001mwm                                                               1  
  14. 0002081                                                               1  
  15. 000317                                                                1  
  16. 00032156688                                                           1  
  17.  
  18. 9 rows selected.  
  19.  
  20. SQL> select * from t1 where signinid='000000yu';  
  21.  
  22. Execution Plan  
  23. ----------------------------------------------------------  
  24. Plan hash value: 2246799375  
  25. -------------------------------------------------------------------------------------------  
  26. | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|Time     |  
  27. -------------------------------------------------------------------------------------------  
  28. |   0 | SELECT STATEMENT            |             |     1 |  1296 |  3706   (1)|00:00:45 |  
  29. |   1 |  TABLE ACCESS BY INDEX ROWID| T1          |     1 |  1296 |  3706   (1)|00:00:45 |  
  30. |*  2 |   INDEX SKIP SCAN           | IDX_T1_MUL5 |     1 |       |  3705   (1)|00:00:45 |  
  31. -------------------------------------------------------------------------------------------  
  32. Predicate Information (identified by operation id):  
  33. ---------------------------------------------------  
  34.  
  35.    2 - access("SIGNINID"='000000yu')  
  36.        filter("SIGNINID"='000000yu')  
  37.  
  38. SQL> select * from t1 where createddate < sysdate and signinid='000000yu';  
  39.  
  40. Execution Plan  
  41. ----------------------------------------------------------  
  42. Plan hash value: 2246799375  
  43. -------------------------------------------------------------------------------------------  
  44. | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|Time     |  
  45. -------------------------------------------------------------------------------------------  
  46. |   0 | SELECT STATEMENT            |             |     1 |  1296 |  3706   (1)|00:00:45 |  
  47. |*  1 |  TABLE ACCESS BY INDEX ROWID| T1          |     1 |  1296 |  3706   (1)|00:00:45 |  
  48. |*  2 |   INDEX SKIP SCAN           | IDX_T1_MUL5 |     1 |       |  3705   (1)|00:00:45 |  
  49. -------------------------------------------------------------------------------------------  
  50. Predicate Information (identified by operation id):  
  51. ---------------------------------------------------  
  52.  
  53.    1 - filter("CREATEDDATE"<SYSDATE@!)  
  54.    2 - access("SIGNINID"='000000yu')  
  55.        filter("SIGNINID"='000000yu') 
 
 
从测试结果上看，11.2.0.3版本的数据库，只要查询语句中包含组合索引的字段，且结果集较小的情况下，执行计划会选择组合索引！
 
 
十：在10.2.0.1版本的oracle上进行同样的测试，发现where子句中不出现组合索引的前导列的时候，执行计划不会选择组合索引！
 
 
(第9和第10的测试有点疑问，后期将继续测试，ref  http://t.askmaclean.com/viewthread.php?tid=1384&pid=7258&page=1&extra=page%3D1)
 
 
 
 
  1. SQL> create user test  identified by test;  
  2. User created.  
  3.  
  4. SQL> grant connect,resource,select_catalog_role to test;  
  5. Grant succeeded.  
  6.  
  7. SQL> conn test/test  
  8. Connected.  
  9.  
  10. SQL> create table t1 as select * from dba_objects;  
  11. Table created.  
  12.  
  13. SQL> create table t1 as select * from dba_objects;  
  14. Table created.  
  15.  
  16. SQL> desc t1;  
  17.  Name                                      Null?    Type  
  18.  ----------------------------------------- -------- ----------------------------  
  19.  OWNER                                              VARCHAR2(30)  
  20.  OBJECT_NAME                                        VARCHAR2(128)  
  21.  SUBOBJECT_NAME                                     VARCHAR2(30)  
  22.  OBJECT_ID                                          NUMBER  
  23.  DATA_OBJECT_ID                                     NUMBER  
  24.  OBJECT_TYPE                                        VARCHAR2(19)  
  25.  CREATED                                            DATE  
  26.  LAST_DDL_TIME                                      DATE  
  27.  TIMESTAMP                                          VARCHAR2(19)  
  28.  STATUS                                             VARCHAR2(7)  
  29.  TEMPORARY                                          VARCHAR2(1)  
  30.  GENERATED                                          VARCHAR2(1)  
  31.  SECONDARY                                          VARCHAR2(1)  
  32.  
  33. SQL> create index i_t1_mul1 on t1(object_id,object_type);  
  34. Index created.  
  35.  
  36. SQL> set autot traceonly exp  
  37. SQL> select * from t1 where object_id < 100;  
  38.  
  39. Execution Plan  
  40. ----------------------------------------------------------  
  41. Plan hash value: 1186876071  
  42. -----------------------------------------------------------------------------------------  
  43. | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |  
  44. -----------------------------------------------------------------------------------------  
  45. |   0 | SELECT STATEMENT            |           |    98 | 17346 |     4   (0)| 00:00:01 |  
  46. |   1 |  TABLE ACCESS BY INDEX ROWID| T1        |    98 | 17346 |     4   (0)| 00:00:01 |  
  47. |*  2 |   INDEX RANGE SCAN          | I_T1_MUL1 |    98 |       |     2   (0)| 00:00:01 |  
  48. -----------------------------------------------------------------------------------------  
  49. Predicate Information (identified by operation id):  
  50. ---------------------------------------------------  
  51.    2 - access("OBJECT_ID"<100)  
  52.  
  53. Note  
  54. -----  
  55.    - dynamic sampling used for this statement  
  56.  
  57.  
  58. SQL> select * from t1 where object_type='INDEX';  
  59.  
  60. Execution Plan  
  61. ----------------------------------------------------------  
  62. Plan hash value: 3617692013  
  63. --------------------------------------------------------------------------  
  64. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  65. --------------------------------------------------------------------------  
  66. |   0 | SELECT STATEMENT  |      |   531 | 93987 |   159   (2)| 00:00:02 |  
  67. |*  1 |  TABLE ACCESS FULL| T1   |   531 | 93987 |   159   (2)| 00:00:02 |  
  68. --------------------------------------------------------------------------  
  69. Predicate Information (identified by operation id):  
  70. ---------------------------------------------------  
  71.    1 - filter("OBJECT_TYPE"='INDEX')  
  72.  
  73. Note  
  74. -----  
  75.    - dynamic sampling used for this statement  
  76.  
  77.  
  78. SQL> set autot off  
  79. SQL> select count(*) from t1;  
  80.  
  81.   COUNT(*)  
  82. ----------  
  83.      50380  
  84.  
  85. SQL> select object_type,count(*) from t1  group by object_type;  
  86.  
  87. OBJECT_TYPE           COUNT(*)  
  88. ------------------- ----------  
  89. CONSUMER GROUP               5  
  90. INDEX PARTITION            276  
  91. SEQUENCE                   143  
  92. QUEUE                       27  
  93. SCHEDULE                     1  
  94. TABLE PARTITION            128  
  95. RULE                         4  
  96. JAVA DATA                  306  
  97. PROCEDURE                   85  
  98. OPERATOR                    57  
  99. LOB PARTITION                1  
  100.  
  101. OBJECT_TYPE           COUNT(*)  
  102. ------------------- ----------  
  103. WINDOW                       2  
  104. LOB                        566  
  105. PACKAGE                    848  
  106. PACKAGE BODY               791  
  107. LIBRARY                    150  
  108. RULE SET                    19  
  109. PROGRAM                     12  
  110. TYPE BODY                  173  
  111. CONTEXT                      5  
  112. JAVA RESOURCE              770  
  113. XML SCHEMA                  26  
  114.  
  115. OBJECT_TYPE           COUNT(*)  
  116. ------------------- ----------  
  117. TRIGGER                    171  
  118. JOB CLASS                    2  
  119. UNDEFINED                    6  
  120. DIRECTORY                    9  
  121. DIMENSION                    5  
  122. MATERIALIZED VIEW            2  
  123. TABLE                     1636  
  124. INDEX                     1800  
  125. SYNONYM                  20026  
  126. VIEW                      3671  
  127. FUNCTION                   270  
  128.  
  129. OBJECT_TYPE           COUNT(*)  
  130. ------------------- ----------  
  131. WINDOW GROUP                 1  
  132. JAVA CLASS               16417  
  133. INDEXTYPE                   10  
  134. CLUSTER                     10  
  135. TYPE                      1926  
  136. RESOURCE PLAN                3  
  137. EVALUATION CONTEXT          14  
  138. JOB                          6  
  139. 41 rows selected.  
  140.  
  141. SQL> set autot traceonly exp  
  142. SQL> select /*+index(i_t1_mul1)*/ * from t1 where object_type='INDEX';  
  143.  
  144. Execution Plan  
  145. ----------------------------------------------------------  
  146. Plan hash value: 3617692013  
  147. --------------------------------------------------------------------------  
  148. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  149. --------------------------------------------------------------------------  
  150. |   0 | SELECT STATEMENT  |      |   531 | 93987 |   159   (2)| 00:00:02 |  
  151. |*  1 |  TABLE ACCESS FULL| T1   |   531 | 93987 |   159   (2)| 00:00:02 |  
  152. --------------------------------------------------------------------------  
  153. Predicate Information (identified by operation id):  
  154. ---------------------------------------------------  
  155.    1 - filter("OBJECT_TYPE"='INDEX')  
  156.  
  157. Note  
  158. -----  
  159.    - dynamic sampling used for this statement 
 
 
综上所述：
 1：可以对线上数据库的组合索引做优化，对于倾斜字段做前导列的组合索引，可以使用非倾斜字段做前导列，或者建立相同的索引，前导列对调；
 
 
2：取消相应的函数索引
 
 
3：删除不必要的组合索引，对经常在where子句中出现的字段建组合索引(例如查询1中where子句出现A,B,C,D四个字段，查询2中where子句出现A,B,C三个字段，且这个查询语句经常出现，这种条件下，只需要对A,B,C,D字段建组合索引即可，无需建2个索引)
 
 
4：注意索引的热点块问题，前面3的场景在并发严重的情况下，有可能会导致出现热点快，因而需要根据需求来适当调整
 


                

        

        




    

  


            

                    
            

    

      

        

            

              
                
                  weixin_33881041
                
              
            

            

                关注
              
            

        

        

          
      

      









                



	

		

			

				
					
【MongoDB】--MongoDB的组合索引

				
			

			

				

					寻梦友的博客
				

				

					04-23
					
					604
					
				

			

		

		

			
				
本文章主要介绍Mongodb的组合索引的使用。

			
		

	



                

              
                



	

		

			

				
					
mysql 组合索引 排序_MySQL利用索引排序实践

				
			

			

				

					weixin_39776344的博客
				

				

					01-19
					
					576
					
				

			

		

		

			
				
要使用多列索引(联合索引)，需要满足最左原则，where条件必须和索引的顺序一致，如果只用到单列则必须是最左列。只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向(倒序或正序)都一样时，MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。也需要满足索引的最左前缀要求(例外：前导列为常量...

			
		

	



                


  
              

                


	

		

			

				
					
mysql  联合索引测试

				
			

			

				

					潇洒人生的博客
				

				

					02-26
					
					158
					
				

			

		

		

			
				
mysql联合索引

mysql> select * from sys_user;
+----+----------+-----+-----+-------+
| id | username | sex | age | phone |
+----+----------+-----+-----+-------+
| 1 | zs    | ls | 18 | 136  |
| 2 | l...

			
		

	





	

		

			

				
					
mysql，explain执行计划组合索引测试

					
热门推荐

				
			

			

				

					龙池小生
				

				

					09-10
					
					5万+
					
				

			

		

		

			
				
创建测试表，添加测试数据


CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `manager_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idex_...

			
		

	



		

			
		



	

		

			

				
					
MySQL 索引测试

				
			

			

				

					风信子
				

				

					06-29
					
					773
					
				

			

		

		

			
				
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

			
		

	





	

		

			

				
					
MySQL组合索引（多列索引）使用与优化

				
			

			

				

					大道至简，持之以恒！
				

				

					02-22
					
					1万+
					
				

			

		

		

			
				
目录

一、多列索引

二、测试案例及过程

1、创建一个测试数据库和数据表

2、添加两个单列索引

3、查询一条数据利用到两个列的索引

4、查看执行计划

5、然后删除以上索引,添加多列索引

6、再次查询

三、多列索引的使用顺序

1、怎么选择建立组合索引时，列的顺序

2、组合索引的使用规则



一、多列索引



我们经常听到一些人说"把WHERE条件里的列都加上索引"，其实这个建议非常错误。



在多个列上建立单独的索引大部分情况下并不能提高MySQL的查询性能。MySQ...

			
		

	





	

		

			

				
					
mysql测试索引

				
			

			

				

					头皮发麻
				

				

					09-20
					
					293
					
				

			

		

		

			
				
向数据库批量插入100万条数据
CREATE PROCEDURE unknown()
  BEGIN
    DECLARE num INT;
    SET num = 0;
    WHILE num < 1000000 DO
      set names latin1;
      insert INTO
			demo(name,age,hobby) values('小明',12,...

			
		

	





	

		

			

				
					
“卧槽”用好组合索引，性能提升10倍不止

				
			

			

				

					i6588662的博客
				

				

					03-11
					
					1250
					
				

			

		

		

			
				
相信各位在面试时，通常会被问到“什么是索引？” 而你肯定可以脱口而出：索引是提升查询速度的一种数据结构。而索引之所以能提升查询速度，在于它在插入时对数据进行了排序。

在实际业务中，我们会遇到很多复杂的场景，比如对多个列进行查询。这时，可能会要求用户创建多个列组成的索引，如列 a 和 b 创建的组合索引，但究竟是创建（a，b）的索引，还是（b，a）的索引，结果却是完全不同的。

今天，我们就来聊聊更贴近业务实战的组合索引，一起来感受一下组合索引的威力。（当然咯，文章中讲的索引指的是B+树索引，就是那个矮胖子

			
		

	





	

		

			

				
					
oracle 联合 唯一索引,oracle单列索引和组合索引性能测试

				
			

			

				

					weixin_39827625的博客
				

				

					04-03
					
					891
					
				

			

		

		

			
				
共有两张表t1,t2t1的表结构t2的表结构t1和t2通过insert into t1 values ('www','aaa','bbb');insert into t1 values ('www1','aaa1','bbb1');insert into t1 values ('www2','aaa2','bbb2');insert into t1 values ('www3','aaa3','b...

			
		

	





	

		

			

				
					
MySQL一个索引最多有多少个列？真实的测试例子

				
			

			

				

					09-11
				

			

		

		

			
				
在实际应用中，选择合适的索引类型和列组合是优化数据库性能的重要环节。  总的来说，MySQL的索引列数限制为16，这是为了平衡查询性能和存储效率。在设计数据库时，应当充分考虑这个限制，合理构建索引，以满足应用...

			
		

	





	

		

			

				
					
软件测试文章索引

				
			

			

				

					简言
				

				

					10-19
					
					919
					
				

			

		

		

			
				
软件测试的入门进阶所有文章索引，包含测试基础理论，测试计划的制定、如何做好设计及评审、测试执行过程中如何提交和管跟踪缺陷，如何进行接口、兼容、性能、埋点的测试和报告输出，并且如何做版本质量分析几个环节。


			
		

	





	

		

			

				
					
为什么你创建的数据库索引没有生效，索引失效的条件！

				
			

			

				

					徐刘根的博客
				

				

					03-15
					
					2万+
					
				

			

		

		

			
				
几乎所有的小伙伴都可以随口说几句关于创建索引的优缺点，也知道什么时候创建索引能够提高我们的查询性能，什么时候索引会更新，但是你有没有注意到，即使你设置了索引，有些时候索引他是不会生效的！这不仅考察了大家对索引的了解程度，还要让大家在使用的时候能够正确的使用。以下介绍了一些可能会造成索引失效的特殊情况，希望大家在平时开发和面试的时候能够注意到！



一、如何判断数据库索引是否生效

首先在接着探讨...

			
		

	





	

		

			

				
					
MySQL联合索引（abc）命中规则

				
			

			

				

					qq_34486648的博客
				

				

					02-23
					
					6260
					
				

			

		

		

			
				
1.建表
mysql创建一张表，表名：‘test_models’

id列为 主键，int类型 ，自增
a,b,c,d,e 全部是int（11）
为（a,b,c）添加一个联合索引 index_abc

执行语句：
CREATE TABLE `test_models` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
 `a` INT(11) DEFAULT NULL,
 `b` INT(11) DEFAULT NULL,
 `c` INT(11) DEFAULT NULL,
 

			
		

	





	

		

			

				
					
[2016-03]MySQL · 答疑解惑 · MySQL 锁问题最佳实践

				
			

			

				

					weixin_34362991的博客
				

				

					03-27
					
					118
					
				

			

		

		

			
				
前言
最近一段时间处理了较多锁的问题，包括锁等待导致业务连接堆积或超时，死锁导致业务失败等，这类问题对业务可能会造成严重的影响，没有处理经验的用户往往无从下手。下面将从整个数据库设计，开发，运维阶段介绍如何避免锁问题的发生，提供一些最佳实践供RDS的用户参考。
设计阶段
在数据库设计阶段，引擎选择和索引设计不当可能导致后期业务上线后出现较为严重的锁或者死...

			
		

	





	

		

			

				
					
杂谈--从基数评估来看问题1

				
			

			

				

					weixin_33694172的博客
				

				

					12-10
					
					89
					
				

			

		

		

			
				
前段时间遇到很多例统计信息更新不及时导致执行计划较差引发性能问题的情况，现在总结分析下，与各位分享。
问题描述：
订单表用于存放用户订单信息，其中有字段DT存放订单的创建时间，PID存放用户编号，根据业务需求，我们分别创建在列DT上索引IDX_DT，在PID上创建索引IDX_PID，我们通常会在业务低峰期进行索引维护和统计更新，在双11期间，订单暴增而统计信息没有得到及时更新，部分查询选用了不...

			
		

	





	

		

			

				
					
mysql联合索引测试

				
			

			

				

					luoqinglong的专栏
				

				

					05-11
					
					2116
					
				

			

		

		

			
				
mysql联合索引和查询条件的匹配原则：只有当联合索引的第一个字段在where条件中出现时，才会使用索引查询，不然就是全表扫描查询。表的索引为：KEY`Index_query`(`transorderid`,`userorderid`,`tradetype`,`orgcode`,`productid`)一、使用索引查询的情况：1、查询条件和索引字段全部匹配2、查询条件和索引字段部分匹配3、查...

			
		

	





	

		

			

				
					
组合索引位置的使用测试

				
			

			

				

					sunbocong的博客
				

				

					05-11
					
					505
					
				

			

		

		

			
				
组合索引位置的使用测试



1  内容

本文档记录组合索引的顺序跟相应组合索引的顺序的测试结果，目的是如何在实际的工作中如何有效的使用组合索引；



1.1  测试环境

数据库版本：oracle 11.0.2.3

表：BKPF



索引：BKPF~1

测试语句：



SELECT /*+ INDEX(BKPF "BKPF~1") */ 
BELNR XM_BELNR, BUDAT ...

			
		

	





	

		

			

				
					
mysql如何创建组合索引

					
最新发布

				
			

			

				

					07-27
				

			

		

		

			
				
要创建组合索引，你可以使用 `CREATE INDEX` 语句。以下是一个示例：  ```sql CREATE INDEX index_name ON table_name (column1, column2, ...); ```  在上面的语句中，你需要将 `index_name` 替换为你想要为索引...

			
		

	



              




          




        



    





    



      

      

        
      

      





	

		评论	
	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

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

              

            

          

      

      余额充值