测试组合索引-白红宇

测试组合索引

阅读量：5988 次

发布时间：2019-06-20

本文共 22614 字，大约阅读时间需要 75 分钟。

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

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

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

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

SQL> create table t1 as select * from test;

SQL> alter table t1 add constraint pk_t1_id primary key (id);

Table altered.

SQL> create index idx_t1_mul1 on t1(status,registersource);

Index created.

二：收集表统计信息,对status字段的空值进行赋值，status字段为会员的状态，1为正常状态，其他值均为异常状态，所以值的分布倾斜

SQL> update t1 set status=0 where status is null;

4 rows updated.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(‘HR’,'T1',CASCADE=>true);

PL/SQL procedure successfully completed.

SQL> select status,count(*) from t1 group by status;

STATUS COUNT(*)

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

1 595612

2 1230

4 10

3 2746

0 26825

6 rows selected.

三：测试证明结果集比较小的情况下，where字句中包含前导列的时候就会使用组合索引

SQL> select id,version from t1 where status=0;

Execution Plan

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

Plan hash value: 1745128362

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

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

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

| 0 | SELECT STATEMENT | | 26939 | 289K| 6051 (1)|00:01:13 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 26939 | 289K| 6051 (1)|00:01:13 |

|* 2 | INDEX RANGE SCAN | IDX_T1_MUL1 | 26939 | | 93 (0)|00:00:02 |

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

Predicate Information (identified by operation id):

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

2 - access("STATUS"=0)

四：建组合索引2(组合索引2的字段同组合索引1一样，只是前导列对调)，对registersource字段的空值进行赋值，收集表的统计信息；(该字段用来存储用户的注册来源，值分布也存在倾斜的情况)

SQL> create index idx_t1_mul2 on t1(registersource,status);

Index created.

SQL> select registersource,count(*) from t1 group by registersource;

REGIST COUNT(*)

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

1 156

3 1689

0 23487

2 601084

6 rows selected.

SQL> update t1 set registersource=6 where registersource is null;

4 rows updated

SQL>commit；

Commit complete.

SQL> exec dbms_stats.gather_table_stats(‘HR’,'T1',CASCADE=>true);

PL/SQL procedure successfully completed.

五：只查询registersource字段且结果集小的时候，执行计划采用组合索引2

SQL> select * from t1 where registersource='1'; //这里registersource字段为varchar2类型，故而加引号

Execution Plan

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

Plan hash value: 2744963562

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

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

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

| 0 | SELECT STATEMENT | | 227 | 287K| 49 (0)|00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 227 | 287K| 49 (0)|00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_T1_MUL2 | 227 | | 3 (0)|00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("REGISTERSOURCE"='1')

六：倾斜组合测试

1：小小

SQL> select * from t1 where status=0 and registersource=0;

Execution Plan

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

Plan hash value: 1745128362

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

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

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

| 0 | SELECT STATEMENT | | 1057 | 1337K| 280 (0)|00:00:04 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1057 | 1337K| 280 (0)|00:00:04 |

|* 2 | INDEX RANGE SCAN | IDX_T1_MUL1 | 1057 | | 68 (0)|00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("STATUS"=0)

filter(TO_NUMBER("REGISTERSOURCE")=0)

SQL> select * from t1 where registersource=0 and status=0;

Execution Plan

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

Plan hash value: 1745128362

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

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

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

| 0 | SELECT STATEMENT | | 1057 | 1337K| 280 (0)|00:00:04 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1057 | 1337K| 280 (0)|00:00:04 |

|* 2 | INDEX RANGE SCAN | IDX_T1_MUL1 | 1057 | | 68 (0)|00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("STATUS"=0)

filter(TO_NUMBER("REGISTERSOURCE")=0)

2:小大

SQL> select * from t1 where status=0 and registersource=2;

Execution Plan

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

Plan hash value: 1745128362

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

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

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

| 0 | SELECT STATEMENT | | 28531 | 35M| 5776 (1)|00:01:10 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 28531 | 35M| 5776 (1)|00:01:10 |

|* 2 | INDEX RANGE SCAN | IDX_T1_MUL1 | 28531 | | 68 (0)|00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("STATUS"=0)

filter(TO_NUMBER("REGISTERSOURCE")=2)

SQL> select * from t1 where registersource=2 and status=0;

Execution Plan

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

Plan hash value: 1745128362

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

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

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

| 0 | SELECT STATEMENT | | 28531 | 35M| 5776 (1)|00:01:10 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 28531 | 35M| 5776 (1)|00:01:10 |

|* 2 | INDEX RANGE SCAN | IDX_T1_MUL1 | 28531 | | 68 (0)|00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("STATUS"=0)

filter(TO_NUMBER("REGISTERSOURCE")=2)

3:大小

SQL> select * from t1 where status=1 and registersource=0;

Execution Plan

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

Plan hash value: 1106331959

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

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

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

| 0 | SELECT STATEMENT | | 21105 | 26M| 4659 (1)|00:00:56 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 21105 | 26M| 4659 (1)|00:00:56 |

|* 2 | INDEX SKIP SCAN | IDX_T1_MUL2 | 21105 | | 437 (0)|00:00:06 |

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

Predicate Information (identified by operation id):

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

2 - access("STATUS"=1)

filter(TO_NUMBER("REGISTERSOURCE")=0 AND "STATUS"=1)

SQL> select * from t1 where registersource=0 and status=1;

Execution Plan

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

Plan hash value: 1106331959

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

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

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

| 0 | SELECT STATEMENT | | 21105 | 26M| 4659 (1)|00:00:56 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 21105 | 26M| 4659 (1)|00:00:56 |

|* 2 | INDEX SKIP SCAN | IDX_T1_MUL2 | 21105 | | 437 (0)|00:00:06 |

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

Predicate Information (identified by operation id):

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

2 - access("STATUS"=1)

filter(TO_NUMBER("REGISTERSOURCE")=0 AND "STATUS"=1)

4:大大

SQL> select * from t1 where status=1 and registersource=2;

Execution Plan

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

Plan hash value: 3617692013

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

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

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

| 0 | SELECT STATEMENT | | 569K| 703M| 32570 (1)| 00:06:31 |

|* 1 | TABLE ACCESS FULL| T1 | 569K| 703M| 32570 (1)| 00:06:31 |

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

Predicate Information (identified by operation id):

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

1 - filter("STATUS"=1 AND TO_NUMBER("REGISTERSOURCE")=2)

SQL> select * from t1 where registersource=2 and status=1;

Execution Plan

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

Plan hash value: 3617692013

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

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

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

| 0 | SELECT STATEMENT | | 569K| 703M| 32570 (1)| 00:06:31 |

|* 1 | TABLE ACCESS FULL| T1 | 569K| 703M| 32570 (1)| 00:06:31 |

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

Predicate Information (identified by operation id):

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

1 - filter("STATUS"=1 AND TO_NUMBER("REGISTERSOURCE")=2)

测试结果表明，cbo将根据结果集的大小来决定使用最优的执行计划

七：组合索引和其他字段的配合使用(测试结果说明where子句中包含组合索引的前导列，且结果集小的情况下，执行计划会选择走组合索引)

SQL> select * from t1 where status=0 and createddate < sysdate;

Execution Plan

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

Plan hash value: 1745128362

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

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

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

| 0 | SELECT STATEMENT | | 29698 | 36M| 6009 (1)|00:01:13 |

|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 29698 | 36M| 6009 (1)|00:01:13 |

|* 2 | INDEX RANGE SCAN | IDX_T1_MUL1 | 29698 | | 68 (0)|00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("CREATEDDATE"<SYSDATE@!)

2 - access("STATUS"=0)

SQL> select * from t1 where registersource='0' and createddate < sysdate;

Execution Plan

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

Plan hash value: 2744963562

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

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

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

| 0 | SELECT STATEMENT | | 22302 | 27M| 4514 (1)|00:00:55 |

|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 22302 | 27M| 4514 (1)|00:00:55 |

|* 2 | INDEX RANGE SCAN | IDX_T1_MUL2 | 22302 | | 52 (0)|00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("CREATEDDATE"<SYSDATE@!)

2 - access("REGISTERSOURCE"='0')

八：函数索引测试，对orderno,membership.status,featured四个字段建组合索引3，同时orderno字段进行desc排序；对前面4个字段建立普通的组合索引4，这种做法意在测试是否有必要使用函数索引？

SQL> create index idx_t1_mul3 on t1(orderno desc,membership,status,featured);

Index created.

SQL> select * from t1 where membership=1 and status=0 order by orderno;

no rows selected

Execution Plan

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

Plan hash value: 687259109

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

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

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

| 0 | SELECT STATEMENT | | 5940 | 7517K| 3009 (1)|00:00:37 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 5940 | 7517K| 3009 (1)|00:00:37 |

|* 2 | INDEX FULL SCAN DESCENDING| IDX_T1_MUL3 | 5940 | | 1825 (1)|00:00:22 |

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

Predicate Information (identified by operation id):

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

2 - access("MEMBERSHIP"=1 AND "STATUS"=0)

filter("STATUS"=0 AND "MEMBERSHIP"=1)

SQL> select * from t1 where membership=1 and status=0 order by orderno desc;

no rows selected

Execution Plan

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

Plan hash value: 607735922

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

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

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

| 0 | SELECT STATEMENT | | 5940 | 7517K| 3009 (1)|00:00:37 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 5940 | 7517K| 3009 (1)|00:00:37 |

|* 2 | INDEX FULL SCAN | IDX_T1_MUL3 | 5940 | | 1825 (1)|00:00:22 |

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

Predicate Information (identified by operation id):

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

2 - access("MEMBERSHIP"=1 AND "STATUS"=0)

filter("STATUS"=0 AND "MEMBERSHIP"=1)

SQL> drop index idx_t1_mul3;

Index dropped.

SQL> create index idx_t1_mul4 on t1(orderno,membership,status,featured);

Index created.

SQL> select * from t1 where membership=1 and status=0 order by orderno;

no rows selected

Execution Plan

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

Plan hash value: 3155127807

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

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

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

| 0 | SELECT STATEMENT | | 5940 | 7517K| 1923 (1)|00:00:24 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 5940 | 7517K| 1923 (1)|00:00:24 |

|* 2 | INDEX SKIP SCAN | IDX_T1_MUL4 | 5940 | | 739 (0)|00:00:09 |

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

Predicate Information (identified by operation id):

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

2 - access("MEMBERSHIP"=1 AND "STATUS"=0)

filter("STATUS"=0 AND "MEMBERSHIP"=1)

SQL> select * from t1 where membership=1 and status=0 order by orderno desc;

no rows selected

Execution Plan

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

Plan hash value: 893632694

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

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

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

| 0 | SELECT STATEMENT | | 5940 | 7517K| 1923 (1)|00:00:24 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 5940 | 7517K| 1923 (1)|00:00:24 |

|* 2 | INDEX SKIP SCAN DESCENDING| IDX_T1_MUL4 | 5940 | | 739 (0)|00:00:09 |

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

Predicate Information (identified by operation id):

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

2 - access("MEMBERSHIP"=1 AND "STATUS"=0)

filter("STATUS"=0 AND "MEMBERSHIP"=1)

从测试结果上看，不使用函数索引的效果较优，同时去掉这类的函数索引，会方便今后的shrink table操作

九：不使用前导列情况下，组合索引是否能正常使用？在前面测试函数索引的过程中，where条件中不含前导列，只是对结果集进行排序的时候使用前导列，执行计划选择组合索引，因而进行如下测试！

SQL> create index idx_t1_mul5 on t1(password,signinid);

Index created.

SQL> select * from (select signinid,count(*) from t1 group by signinid) where rownum<10;

SIGNINID COUNT(*)

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

000000yu 1

0000410265269 1

00006789 1

00009746 1

000113 1

0001mwm 1

0002081 1

000317 1

00032156688 1

9 rows selected.

SQL> select * from t1 where signinid='000000yu';

Execution Plan

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

Plan hash value: 2246799375

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

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

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

| 0 | SELECT STATEMENT | | 1 | 1296 | 3706 (1)|00:00:45 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1296 | 3706 (1)|00:00:45 |

|* 2 | INDEX SKIP SCAN | IDX_T1_MUL5 | 1 | | 3705 (1)|00:00:45 |

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

Predicate Information (identified by operation id):

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

2 - access("SIGNINID"='000000yu')

filter("SIGNINID"='000000yu')

SQL> select * from t1 where createddate < sysdate and signinid='000000yu';

Execution Plan

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

Plan hash value: 2246799375

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

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

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

| 0 | SELECT STATEMENT | | 1 | 1296 | 3706 (1)|00:00:45 |

|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1296 | 3706 (1)|00:00:45 |

|* 2 | INDEX SKIP SCAN | IDX_T1_MUL5 | 1 | | 3705 (1)|00:00:45 |

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

Predicate Information (identified by operation id):

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

1 - filter("CREATEDDATE"<SYSDATE@!)

2 - access("SIGNINID"='000000yu')

filter("SIGNINID"='000000yu')

从测试结果上看，11.2.0.3版本的数据库，只要查询语句中包含组合索引的字段，且结果集较小的情况下，执行计划会选择组合索引！

十：在10.2.0.1版本的oracle上进行同样的测试，发现where子句中不出现组合索引的前导列的时候，执行计划不会选择组合索引！

(第9和第10的测试有点疑问，后期将继续测试，ref )

SQL> create user test identified by test;

User created.

SQL> grant connect,resource,select_catalog_role to test;

Grant succeeded.

SQL> conn test/test

Connected.

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> desc t1;

Name Null? Type

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

OWNER VARCHAR2(30)

OBJECT_NAME VARCHAR2(128)

SUBOBJECT_NAME VARCHAR2(30)

OBJECT_ID NUMBER

DATA_OBJECT_ID NUMBER

OBJECT_TYPE VARCHAR2(19)

CREATED DATE

LAST_DDL_TIME DATE

TIMESTAMP VARCHAR2(19)

STATUS VARCHAR2(7)

TEMPORARY VARCHAR2(1)

GENERATED VARCHAR2(1)

SECONDARY VARCHAR2(1)

SQL> create index i_t1_mul1 on t1(object_id,object_type);

Index created.

SQL> set autot traceonly exp

SQL> select * from t1 where object_id < 100;

Execution Plan

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

Plan hash value: 1186876071

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

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

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

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

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 98 | 17346 | 4 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | I_T1_MUL1 | 98 | | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("OBJECT_ID"<100)

Note

-----

- dynamic sampling used for this statement

SQL> select * from t1 where object_type='INDEX';

Execution Plan

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

Plan hash value: 3617692013

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

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

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

| 0 | SELECT STATEMENT | | 531 | 93987 | 159 (2)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T1 | 531 | 93987 | 159 (2)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

1 - filter("OBJECT_TYPE"='INDEX')

Note

-----

- dynamic sampling used for this statement

SQL> set autot off

SQL> select count(*) from t1;

COUNT(*)

----------

50380

SQL> select object_type,count(*) from t1 group by object_type;

OBJECT_TYPE COUNT(*)

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

CONSUMER GROUP 5

INDEX PARTITION 276

SEQUENCE 143

QUEUE 27

SCHEDULE 1

TABLE PARTITION 128

RULE 4

JAVA DATA 306

PROCEDURE 85

OPERATOR 57

LOB PARTITION 1

OBJECT_TYPE COUNT(*)

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

WINDOW 2

LOB 566

PACKAGE 848

PACKAGE BODY 791

LIBRARY 150

RULE SET 19

PROGRAM 12

TYPE BODY 173

CONTEXT 5

JAVA RESOURCE 770

XML SCHEMA 26

OBJECT_TYPE COUNT(*)

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

TRIGGER 171

JOB CLASS 2

UNDEFINED 6

DIRECTORY 9

DIMENSION 5

MATERIALIZED VIEW 2

TABLE 1636

INDEX 1800

SYNONYM 20026

VIEW 3671

FUNCTION 270

OBJECT_TYPE COUNT(*)

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

WINDOW GROUP 1

JAVA CLASS 16417

INDEXTYPE 10

CLUSTER 10

TYPE 1926

RESOURCE PLAN 3

EVALUATION CONTEXT 14

JOB 6

41 rows selected.

SQL> set autot traceonly exp

SQL> select /*+index(i_t1_mul1)*/ * from t1 where object_type='INDEX';

Execution Plan

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

Plan hash value: 3617692013

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

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

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

| 0 | SELECT STATEMENT | | 531 | 93987 | 159 (2)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T1 | 531 | 93987 | 159 (2)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

1 - filter("OBJECT_TYPE"='INDEX')

Note

-----

- 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的场景在并发严重的情况下，有可能会导致出现热点快，因而需要根据需求来适当调整

转载地址：http://iujlx.baihongyu.com/

你可能感兴趣的文章

buildroot的make menuconfig配置

ThreadStart和ParameterizedThreadStart区别

3.Scripting.FileSystemObject对象

查看>>

Java classloader机制测试命令

查看>>

Vue.filter()方法创建全局过滤器