本文讨论了一个关于数据库查询优化的过程，涉及表fcx01和fcp01的联查。通过修改SQL语句、调整索引和连接方式，显著提升了查询效率。从最初的24秒执行时间逐步优化至14秒，通过删除冗余索引、创建复合索引和调整表连接顺序等方法，减少了查询成本和执行时间。
表结构和索引如下：
create table "informix".fcx01
(
afcx0100 char(32) not null ,
afcp0100 char(32),
orgid varchar(32),
afcx0101 varchar(20),
afcx0102 varchar(32),
afcx0103 integer,
afcx0104 decimal(4,1),
afcx0105 decimal(4,1),
afcx0106 datetime year to fraction(5),
afcx0107 char(1),
afcx0108 varchar(27),
afcx0109 varchar(32),
afcx0110 varchar(30),
afcx0111 varchar(36),
afcx0112 varchar(32),
afcx0113 char(1),
afcx0114 varchar(2),
afcx0115 lvarchar(400),
afcx0118 varchar(72),
afcx0119 datetime year to fraction(5),
afcx0120 varchar(72),
afcx0121 datetime year to fraction(5),
del_state char(1),
create_user varchar(32),
create_time datetime year to fraction(5),
update_user varchar(32),
update_time datetime year to fraction(5),
del_user varchar(32),
del_time datetime year to year,
afcx0122 char(1),
afcx0123 datetime year to second,
afcx0124 varchar(36),
afcx0125 varchar(200),
afcx0126 varchar(200),
afcx0127 "informix".boolean,
afcx0128 decimal(4,1),
olddata varchar(100),
ac43_id varchar(100),
aab034 varchar(100),
afcx0129 varchar(32),
primary key (afcx0100) constraint "informix".fcx01_pk
) extent size 64 next size 64 lock mode row;
revoke all on "informix".fcx01 from "public" as "informix";
create index "informix".fcx01_aab034 on "informix".fcx01 (aab034) using btree in reliefdbs;
create index "informix".fcx01_ac43_id on "informix".fcx01 (ac43_id) using btree in reliefdbs;
create index "informix".fcx01_afcp0100 on "informix".fcx01 (afcp0100) using btree in reliefdbs;
create index "informix".fcx01_afcx0113 on "informix".fcx01 (afcx0113) using btree in reliefdbs;
create index "informix".fcx01_orgid_idx on "informix".fcx01 (orgid, afcx0114,del_state) using btree in reliefdbs;
create index "informix".index_fcx01_afcx0101 on "informix".fcx01 (afcx0101) using btree in reliefdbs;
create table "informix".fcp01
(
afcp0100 char(32) not null ,
afcp0101 varchar(200),
afcp0102 varchar(72),
afcp0103 char(1),
afcp0104 char(2),
afcp0105 char(1),
afcp0106 varchar(60),
afcp0107 date,
afcp0108 char(2),
afcp0109 varchar(80),
afcp0110 char(1),
afcp0111 varchar(32),
afcp0112 lvarchar(1000),
afcp0113 varchar(100),
afcp0114 char(1),
axcp0002 varchar(18),
axcp0004 varchar(72),
del_state char(1),
create_user varchar(32),
create_time datetime year to fraction(5),
update_user varchar(100),
update_time datetime year to fraction(5),
del_user varchar(32),
del_time datetime year to year,
afcx0100 char(32),
afcp0115 char(1),
olddata varchar(100),
history_id varchar(100),
primary key (afcp0100) constraint "informix".fcp01_pk
) extent size 64 next size 64 lock mode row;
revoke all on "informix".fcp01 from "public" as "informix";
create index "informix".fcp01_afcp0106_idx on "informix".fcp01 (afcp0106) using btree in reliefdbs;
create index "informix".fcp01_afcp0111 on "informix".fcp01 (afcp0111) using btree in reliefdbs;
create index "informix".fcp01_del_state_idx on "informix".fcp01 (del_state) using btree in reliefdbs;
create index "informix".fcp01_history_id on "informix".fcp01 (history_id) using btree in reliefdbs;
查询SQL如下
SELECT count(*)
FROM fcx01 x
INNER JOIN fcp01 b ON b.del_state = '1' AND x.afcp0100 = b.afcp0100
WHERE x.del_state = '1' AND x.afcx0114 = '1' AND x.ORGID = '1100000000005009727';
执行时间24S。 两个表的数据量为9634575 6870811
优化内容如下：
修改查询SQL为
SELECT count(*)
FROM fcx01 x ,fcp01 b
where b.del_state = '1' AND x.afcp0100 = b.afcp0100
and x.del_state = '1' AND x.afcx0114 = '1' AND x.ORGID = '1100000000005009727';
索引处理规则：
1、删除多余的索引；
2、创建两个复合索引,分别包含两个表的对应字段（区分度大的在前）。发现性能更差了。初步分析为需要全索引扫描，性能不咋好。大概需要4分多钟才能计算出来。
3、把fcx01的索引修改为（ORGID，afcp0100，del_state，afcx0114），性能有所提升，需要2分钟+才能计算出来。
4、发现nestloop的连接方式，调整两个表的顺序，以改善关联表的连接方式，发现SQL执行时间可以到14秒，基本上大达到目标要求。
各个过程的执行计划如下
QUERY: (OPTIMIZATION TIMESTAMP: 02-05-2021 13:48:03)
------
SELECT count(*)
FROM fcx01 x ,fcp01 b
where b.del_state = '1' AND x.afcp0100 = b.afcp0100
and x.del_state = '1' AND x.afcx0114 = '1' AND x.ORGID = '1100000000005009727'
Estimated Cost: 786404
Estimated # of Rows Returned: 1
1) informix.x: INDEX PATH
Filters: (informix.x.del_state = '1' AND informix.x.afcx0114 = '1' )
(1) Index Name: informix.fcx01_orgid_idx
Index Keys: afcp0100 orgid (Key-First) (Serial, fragments: ALL)
Index Key Filters: (informix.x.orgid = '1100000000005009727' )
2) informix.b: INDEX PATH
Filters: informix.b.del_state = '1'
(1) Index Name: informix. 275_965
Index Keys: afcp0100 (Serial, fragments: ALL)
Lower Index Filter: informix.x.afcp0100 = informix.b.afcp0100
NESTED LOOP JOIN
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 x
t2 b
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 28619 84797 2657918 00:25.01 679334
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 28485 6870811 28485 00:19.38 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 28485 84654 00:44.40 786405
type rows_prod est_rows rows_cons time
-------------------------------------------------
group 0 1 28485 00:00.00
QUERY: (OPTIMIZATION TIMESTAMP: 02-05-2021 13:55:17)
------
SELECT count(*)
FROM fcx01 x ,fcp01 b
where b.del_state = '1' AND x.afcp0100 = b.afcp0100
and x.del_state = '1' AND x.afcx0114 = '1' AND x.ORGID = '1100000000005009727'
Estimated Cost: 756474
Estimated # of Rows Returned: 1
1) informix.x: INDEX PATH
(1) Index Name: informix.fcx01_orgid_idx
Index Keys: afcp0100 orgid afcx0114 del_state (Key-First) (Serial, fragments: ALL)
Index Key Filters: (informix.x.orgid = '1100000000005009727' ) AND
(informix.x.del_state = '1' ) AND
(informix.x.afcx0114 = '1' )
2) informix.b: INDEX PATH
(1) Index Name: informix.fcp01_afcp0100
Index Keys: afcp0100 del_state (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: (informix.x.afcp0100 = informix.b.afcp0100 AND informix.b.del_state = '1' )
NESTED LOOP JOIN
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 x
t2 fcp01
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 45474 84797 4651606 04:32.28 679419
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 45338 6870811 45338 00:03.59 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 45338 84653 04:35.90 756474
type rows_prod est_rows rows_cons time
-------------------------------------------------
group 0 1 45338 00:00.00
QUERY: (OPTIMIZATION TIMESTAMP: 02-05-2021 14:07:47)
------
SELECT count(*)
FROM fcx01 x ,fcp01 b
where b.del_state = '1' AND x.afcp0100 = b.afcp0100
and x.del_state = '1' AND x.afcx0114 = '1' AND x.ORGID = '1100000000005009727'
Estimated Cost: 202758
Estimated # of Rows Returned: 1
1) informix.x: INDEX PATH (SKIP SCAN)
Filters: (informix.x.del_state = '1' AND informix.x.afcx0114 = '1' )
(1) Index Name: informix.fcx01_orgid_idx
Index Keys: orgid afcp0100 afcx0114 del_state (Serial, fragments: ALL)
Lower Index Filter: informix.x.orgid = '1100000000005009727'
2) informix.b: INDEX PATH
(1) Index Name: informix.fcp01_afcp0100
Index Keys: afcp0100 del_state (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: (informix.x.afcp0100 = informix.b.afcp0100 AND informix.b.del_state = '1' )
NESTED LOOP JOIN
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 x
t2 fcp01
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 85061 84797 85219 00:12.86 125703
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 84926 6870811 84926 02:17.55 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 84926 84653 02:30.46 202758
type rows_prod est_rows rows_cons time
-------------------------------------------------
group 1 1 84926 02:30.52
QUERY: (OPTIMIZATION TIMESTAMP: 02-05-2021 14:12:01)
------
SELECT count(*)
FROM fcp01 b,fcx01 x
where b.del_state = '1' AND x.afcp0100 = b.afcp0100
and x.del_state = '1' AND x.afcx0114 = '1' AND x.ORGID = '1100000000005009727'
Estimated Cost: 202758
Estimated # of Rows Returned: 1
1) informix.x: INDEX PATH (SKIP SCAN)
Filters: (informix.x.del_state = '1' AND informix.x.afcx0114 = '1' )
(1) Index Name: informix.fcx01_orgid_idx
Index Keys: orgid afcp0100 afcx0114 del_state (Serial, fragments: ALL)
Lower Index Filter: informix.x.orgid = '1100000000005009727'
2) informix.b: INDEX PATH
(1) Index Name: informix.fcp01_afcp0100
Index Keys: afcp0100 del_state (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: (informix.x.afcp0100 = informix.b.afcp0100 AND informix.b.del_state = '1' )
NESTED LOOP JOIN
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 x
t2 fcp01
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 85061 84797 85219 00:07.49 125703
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 84926 6870811 84926 00:05.55 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 84926 84653 00:13.08 202758
type rows_prod est_rows rows_cons time
-------------------------------------------------
group 1 1 84926 00:13.13
QUERY: (OPTIMIZATION TIMESTAMP: 02-05-2021 14:31:29)
------
SELECT count(*)
FROM fcp01 b,fcx01 x
where b.del_state = '1' AND x.afcp0100 = b.afcp0100
and x.del_state = '1' AND x.afcx0114 = '1' AND x.ORGID = '1100000000005009727'
Estimated Cost: 202758
Estimated # of Rows Returned: 1
1) informix.x: INDEX PATH (SKIP SCAN)
Filters: (informix.x.del_state = '1' AND informix.x.afcx0114 = '1' )
(1) Index Name: informix.fcx01_orgid_idx
Index Keys: orgid afcp0100 afcx0114 del_state (Serial, fragments: ALL)
Lower Index Filter: informix.x.orgid = '1100000000005009727'
2) informix.b: INDEX PATH
(1) Index Name: informix.fcp01_afcp0100
Index Keys: afcp0100 del_state (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: (informix.x.afcp0100 = informix.b.afcp0100 AND informix.b.del_state = '1' )
NESTED LOOP JOIN
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 x
t2 fcp01
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 85061 84797 85219 00:07.62 125703
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 84926 6870811 84926 00:05.61 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 84926 84653 00:13.27 202758
type rows_prod est_rows rows_cons time
-------------------------------------------------
group 1 1 84926 00:13.32
397
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
