记一次sql优化
目录
记一次sql优化
总数据
70w
原sql
-- 删除索引
drop index BUSINESSID_INDEX
-- 优化前
SELECT
COUNT(CASE WHEN BUREAU_TASK ='0' AND DECLARE_PERSON_ID = 'bfa800bd0000000014e1beec00000004' AND YWNO='02' THEN 1 END) AS correction ,
COUNT(CASE WHEN BUREAU_TASK ='0' AND DECLARE_PERSON_ID = 'bfa800bd0000000014e1beec00000004' AND YWNO='04' THEN 1 END) AS dismissed ,
COUNT(CASE WHEN BUREAU_TASK ='0' AND DECLARE_PERSON_ID = 'bfa800bd0000000014e1beec00000004' AND YWNO='00' THEN 1 END) AS preacceptance ,
COUNT(CASE WHEN BUREAU_TASK ='0' AND ONETHING_TODO_FLAG='0' AND UPDATETIME is not NULL AND YWNO ='00' AND REGIONAL_GENERAL =0 THEN 1 END) AS registerBox ,
COUNT(CASE WHEN BUREAU_TASK ='1' AND DECLARE_PERSON_ID = 'bfa800bd0000000014e1beec00000004' THEN 1 END) AS accepted ,
COUNT(CASE WHEN DECLARESOURCE IN(2,3) AND ONLINE_STATE ='1' and BUSINESSID in
('bfa80104ffffffffd9bac2a500000026','ac10678e000000007998874a0000001e','bfa80104ffffffffd9bb24020000001a','ac10678effffffffb1f95d91ffffffdf','ac10678effffffffb1ff00c3ffffffe1','ac10678effffffffe48daf5200000067','ac10678e000000000ddc91d10000028e','ac10678e000000000da825fd00000351','ac10678e0000000034286f4a000002d4','bfa80104fffffffff88011f400000063','ac10678e00000000422d60910000050d','ac10678e000000000dc41ad500000375','ac10678e000000000dc41a2200000376','ac10678e000000002435baf6ffffffa9','ac10678e000000002454508effffffbc','ac10678e0000000024571439ffffffc0','ac10678e000000002451c1d0ffffffba','ac10678e000000002d91098dffffffe8','ac10678e000000002d913abdffffffef','ac10678e00000000245493acffffffbe','ac10678e000000001044ba6c00000468','ac10678e0000000041e0655700000014','ac10678e0000000041debf480000000a','ac10678e0000000041dec18e00000012','ac10678e0000000041dec0c00000000d','ac10678e0000000041dec03d00000010','ac10678e0000000041dfe08800000019','ac10678e0000000041fc62520000002e','ac10678e000000000de1994e000002a0','ac10678e000000002d834d4bffffffdb','ac10678e0000000041e124df0000001c','ac10678effffffffa427828a00000060','bfa80104ffffffffd9bc6b0b00000029','bfa80104ffffffffd9bbcd5300000032','bfa80104ffffffffd9bd4bf800000037','bfa80104ffffffffd9bed67e00000039','bfa80104ffffffffd9c52f1f00000048','bfa80104ffffffffe00a9da1ffffff91','bfa80104ffffffffe00bdd1bffffff93','bfa80104ffffffffe017e4a2ffffffa2','bfa80104ffffffffdee542feffffff83','ac10678e0000000028722e2bffffffb2','ac10678e00000000287137eaffffffb1','bfa80104ffffffffd9c09c7f0000003f','bfa80104ffffffffd9c0e9d800000041','bfa80104ffffffffd9c1817000000043','bfa80104ffffffffdef537f3ffffff88','bfa80104ffffffffdef77a27ffffff8a','bfa80104ffffffffdef7a721ffffff8b','ac10678effffffffbd3126de0000008c','ac10678effffffffa41f6a540000005a','bfa80104ffffffffe4b9116600000016','bfa80104ffffffffe4ba317200000018','ac10678e000000002874093bffffffb6','bfa80104ffffffffe4b7728400000014','bfa80104ffffffffe016e805ffffffa1','ac10678e00000000287d91e4ffffffbc','bfa80104ffffffffe00cf499ffffff95','bfa80104ffffffffe00f7f0dffffff97','bfa80104ffffffffe013f52fffffff9c','bfa80104ffffffffdee5de86ffffff85','bfa80104ffffffffdef00113ffffff87','bfa80104ffffffffe0070e51ffffff90','ac10678e00000000287c433effffffba','ac10678e00000000287c9d3affffffbb','ac10678effffffff9e9de87d00000106','ac10678effffffffd633f7af00000009','bfa80104ffffffffd9c89e0c0000004c','bfa80104ffffffffd9c89e5f00000053','bfa80104ffffffffd9c7a9ea0000004a','bfa80104ffffffffd9cd6ea40000005d','bfa80104ffffffffe3e9705f00000005','bfa80104ffffffffe3ea264100000006','bfa80104ffffffffe3e9d03c00000007','bfa80104ffffffffe015c484ffffffa0','bfa80104ffffffffd9c434e800000046','bfa80104fffffffff951e5a40000005a','bfa80104ffffffffe014d1c5ffffff9e','ac10678e00000000287a8c90ffffffb9','ac10678e0000000028756d11ffffffb7','bfa80104ffffffffdefd16aeffffff8c','bfa80104ffffffffe012fccfffffff9b','ac10678effffffff9296cb6b0000027f','ac10678effffffffabfd782700000227','ac10678effffffffabfd73c800000225','ac10678effffffffabfd79130000022b','ac10678effffffffabfd79a600000229','ac10678effffffffabfd7a860000022d','ac10678effffffffabfd7af00000022f','ac10678effffffff8e69b20f000001e9','ac10678e00000000736f5de600000057','ac10678e00000000736f5e800000005e','ac10678e00000000736f5e2900000066','ac10678e00000000736f5fc20000006d','ac10678e00000000736f5f460000006f','ac10678e00000000736f60cb00000071','ac10678e00000000736f606600000077','ac10678e00000000736f60fc0000007d','ac10678e00000000736f6208ffffff82','ac10678e00000000736f638affffff84','ac10678e00000000736f630affffff86','ac10678e00000000736f6488ffffff88','ac10678e00000000736f6409ffffff8a','ac10678e00000000736f65a4ffffff90','ac10678e00000000736f6540ffffff96','ac10678effffffff8e6a1b58000001e0','ac10678effffffff8e6a1cd0000001e2','ac10678effffffff8e6a1da1000001e6','ac10678effffffff8e6a1c38000001e4','ac10678effffffff8e6c24ba00000304','ac10678effffffff8e6c2336000001fc','ac10678effffffffbf438a21000000b1','ac10678effffffffbf438ab3000000af','ac10678effffffffbf438b7d000000b3','ac10678effffffffbf438bcd000000b5','ac10678e00000000737301c200000134','ac10678e000000007373013d00000136','ac10678e00000000737302b100000138','ac10678e00000000737302220000013a','ac10678e00000000737303940000013c','ac10678e0000000007aa6941ffffffcd','ac10678e000000000690f28100000227','ac10678e000000000699407800000240','ac10678e0000000003ab52c100000005','ac10678e0000000003bf45ea00000047','ac10678e0000000003c72dd90000004e','ac10678e0000000003c72e3f00000053','ac10678e0000000041dd890c00000007','ac10678effffffffbae8c4ae0000015c','ac10678e0000000003d4c19500000065','ac10678e0000000007c16b7200000166','ac10678e0000000003c8ca7700000062','ac10678e0000000003b69f600000002f','ac10678e0000000006ad5c0800000183','ac10678e000000001f87264800000014','ac10678e0000000003afca8100000010','ac10678effffffffcd04d65d0000001b','ac10678effffffffe4197d2a000000ec','ac10678e00000000736f56e300000032','ac10678effffffffb81bd909000003eb','ac10678effffffffb81bdb7a000003f2','ac10678effffffffb5c93911ffffffe9','ac10678effffffffb5c93aaeffffffeb','ac10678effffffffb5c93a29ffffffed','ac10678e00000000736f6850ffffffb4','ac10678e00000000736f6939ffffffc6','ac10678e00000000736f6ac4ffffffc8','ac10678e00000000736f6be9ffffffcc','ac10678e00000000736f6a5dffffffca','ac10678e00000000736f6cb8ffffffd5','ac10678e00000000736f6c60ffffffdb','ac10678e00000000736f6d40ffffffed','ac10678e00000000736f6df7fffffff5','ac10678e00000000736f6f87fffffff7','bfa77f89ffffffffb69d489100000003','ac10678e00000000378b4f1000000466','ac10678efffffffffdb0c0bbffffff87','ac10678efffffffffdb0befaffffff85','ac10678effffffff8e6d2ff700000315','ac10678effffffff8e6d305d00000317','ac10678effffffff8e6cc48600000336','ac10678effffffffb5c93b9fffffffef','ac10678effffffffb5c93b14fffffff1','ac10678effffffffb5c93c8afffffff3','ac10678effffffffb5c0efc2ffffffce','ac10678effffffffb5c0ef3dffffffd0','ac10678effffffffb5c0f0a8ffffffd2','ac10678effffffffb5c0f013ffffffd4','ac10678effffffffb5c0f17cffffffd6','ac10678e00000000378cfe6000000470','ac10678effffffffb52eabbc00000069','ac10678e000000002c9814e600000027','ac10678efffffffffea45d1b00000177','ac10678effffffff8e6e07d600000343','ac10678effffffff8e6ce38600000341','ac10678effffffffb5a6f23fffffffaa','ac10678effffffff8e6bacaa00000312','ac10678effffffffba4052e8ffffffbe','ac10678effffffffb5cadb50fffffff7','ac10678e00000000378af75b0000046d','ac10678efffffffffea012c00000016b','ac10678efffffffffe9b6b8300000168','ac10678effffffff8e6d6e6e0000031a','ac10678effffffff8e6d6fd00000031c','ac10678effffffff8e6d6f3a0000031e','ac10678efffffffffea4c3150000008b','ac10678efffffffffea4c4d20000008d','ac10678efffffffffea4c57e0000008f','ac10678effffffff8e6c9b6800000321','ac10678effffffff8e6c9ccb00000323','ac10678effffffff8e6c9c2c00000325','ac10678effffffff8e6c9d9300000327','ac10678effffffff8e6c9cf700000329','ac10678effffffff8e6c9d630000032b','ac10678effffffff8e6c9ecd0000032d','ac10678effffffff8e6cc24400000330','ac10678effffffff8e6cc3b600000332','ac10678effffffff8e6cc31e00000334','ac10678effffffff8e6cc4ea00000338','ac10678effffffff8e6cc44d0000033a','ac10678effffffff8e6cc5b30000033c','ac10678effffffff8e6cc5190000033e','ac10678effffffffb5c2a5ddffffffdb','ac10678effffffffb6e0869a000002f4','ac10678effffffffb6e08632000002f6','ac10678effffffffb6e08798000002f8','ac10678effffffffb6e08704000002fa','ac10678effffffffbb7cdfacffffffb9','ac10678effffffffba4825fcffffffce','ac10678effffffffba80d7a5fffffff1','ac10678efffffffffe946ad100000156','ac10678efffffffffea0cab00000016e','ac10678efffffffffe94d30600000159','ac10678efffffffffe9602730000015c','ac10678efffffffffe9a739100000164','ac10678e00000000378e87c300000474','ac10678effffffffb5a1831fffffffa4','ac10678effffffffb5a184cfffffffa6','ac10678effffffffb81e197100000504','ac10678effffffffcbfdf9ff00000006','ac10678effffffffb6872b6500000209','ac10678effffffffb6872e2500000212','ac10678effffffff8e670669000001b7','ac10678effffffff8e6770d3000001be','ac10678effffffff8e6700d8000001c6','ac10678effffffff8e682171000001ca','ac10678effffffff8e685e2e000001d3','ac10678effffffff8e687a6a000001d7','ac10678effffffff8e6796a7000001da','ac10678effffffff8e61c23a000001aa','ac10678efffffffffdacf71c0000007d','ac10678effffffff8e6696ff000001c1','ac10678effffffff8e684490000001cf','ac10678effffffff8e67b5b8000001dd','ac10678effffffffb6ccd12d00000336','ac10678e00000000736f6287ffffff80','ac10678efffffffffdb0c068ffffff89','ac10678efffffffffdb0c114ffffff8b','ac10678effffffffa64ffcd200000237','ac10678e000000000ffed4bb00000290','ac10678e000000000ff690120000037b','ac10678e000000001b3d001000000291','ac10678e000000001b3e022d0000029a','ac10678e000000001b3e608c000002d6','ac10678e000000001b3e6105000002dc','ac10678e000000002f4dec39fffffff6','ac10678effffffffa15eadb8000009d3','ac10678effffffffa1600d4a000009ef','ac10678effffffffa15eae18000009dd','ac10678effffffffa16010f800000b08','ac10678effffffffa16013b400000b27','ac10678effffffffa160141c00000b36','ac10678effffffffa16448db00000b4b','ac10678effffffffa1644bb800000b65','ac10678effffffffa1644c1800000b70','ac10678effffffffa1644dd200000b77','ac10678effffffffa1644e8500000b7c','ac10678effffffffa1644e4800000a87','ac10678effffffffa1644f0500000a8c','ac10678effffffffa15eaa06000009bc','ac10678e000000002f4e5b7afffffff5','ac10678e000000002f4e9d4dfffffff7','ac10678effffffffa15eab6b000009ce','ac10678effffffffa15eaf73000009e2','ac10678effffffffa1600fa6000009f9','bfa781e9ffffffff95ea11a2000027f1','bfa781e9ffffffff95ea0b19000027e8','ac10678effffffffede02befffffdfe3','ac10678effffffffee1a393cffff854f','ac10678e000000007757782000000696','ac10678e000000007dcde015000000af','ac10678e000000007dcf6a32000000b2','ac10678efffffffff2dc38dfffffff86','ac10678e000000001bed58f4ffffc5f0','ac10678effffffffee1a3717ffff854c','ac10678effffffff8483e1830000083b','ac10678effffffffa15ea990000009ae','ac10678effffffffa160114f00000b16','ac10678effffffffa1644a0e00000b61','ac10678e0000000017b335830000021e','ac10678effffffffa1645a2b00000af4','ac10678e000000007dceeeca000000b5','ac10678efffffffff2d45b8900000074','ac10678efffffffff2d4a08f0000007c','ac10678effffffffede123bfffffe199','ac10678effffffffbe8ed15e00001d2e','ac10678effffffffa1644b6b00000b6a','ac10678effffffffa15091c2000007db','ac10678effffffff8cf8c3600000074b','ac10678effffffff8cf8c57f0000074f','ac10678effffffff8cf8c68c00000755','ac10678effffffffedfc9a2efffff5aa','ac10678effffffffbe8ed31f00001d34','ac10678e000000007e4aa7e800000019','ac10678effffffffb6431f720000409c','ac10678e00000000736f6613ffffffa6','ac10678effffffffa135d21700000280')THEN 1 END) AS onlinedeclare ,
COUNT(CASE WHEN ONETHING_TODO_FLAG ='1'AND YWNO ='00' AND BUREAU_TASK='0'THEN 1 END) AS onethingAccept ,
COUNT(CASE WHEN REGIONAL_GENERAL ='1'AND YWNO ='00' AND BUREAU_TASK='0' AND SCHEME_REVIEW_FLAG ='0' AND UPDATETIME IS NOT NULL THEN 1 END) AS documentsViewed ,
COUNT(CASE WHEN YWNO <>'00' AND BUREAU_TASK='1' AND BUSINESSNAME like '%燃气过户%' THEN 1 END) AS rqgh ,
COUNT(CASE WHEN ONETHING_TODO_FLAG='0' AND INSTANCEID in('bfa7810a0000000033aee28d00000027') THEN 1 END) AS businessTodo ,
COUNT(CASE WHEN ONETHING_TODO_FLAG='1' AND INSTANCEID in('bfa7810a0000000033aee28d00000027') THEN 1 END) AS onethingTodo ,
COUNT(CASE WHEN ONETHING_TODO_FLAG='1' AND INSTANCEID in('bfa7810a0000000033aee28d00000027')THEN 1 END) AS regionalTodo
FROM SPM_DECLAREINFO D
WHERE ISVALID ='1';
-- 以下sql为其他用
SELECT count(1) AS regionalTodo
FROM SPM_DECLAREINFO WHERE ONETHING_TODO_FLAG='1' AND INSTANCEID in('bfa7810a0000000033aee28d00000027')
select
COUNT(CASE WHEN ONETHING_TODO_FLAG='1' AND INSTANCEID in('bfa7810a0000000033aee28d00000027')THEN 1 END) AS regionalTodo
FROM SPM_DECLAREINFO D
select count(1) from SPM_DECLAREINFO
查询结果：
发现查询时间3秒多，全表扫描
优化后的sql
-- 先创建索引
create index BUSINESSID_INDEX on SPM_DECLAREINFO(BUSINESSID);
-- 优化后的语句
select * from
(select
COUNT(CASE WHEN BUREAU_TASK ='0' AND DECLARE_PERSON_ID = 'bfa800bd0000000014e1beec00000004' AND YWNO='02' THEN 1 END) AS correction ,
COUNT(CASE WHEN BUREAU_TASK ='0' AND DECLARE_PERSON_ID = 'bfa800bd0000000014e1beec00000004' AND YWNO='04' THEN 1 END) AS dismissed ,
COUNT(CASE WHEN BUREAU_TASK ='0' AND DECLARE_PERSON_ID = 'bfa800bd0000000014e1beec00000004' AND YWNO='00' THEN 1 END) AS preacceptance from SPM_DECLAREINFO
where BUREAU_TASK ='0' AND DECLARE_PERSON_ID = 'bfa800bd0000000014e1beec00000004' and ISVALID ='1'),
(select
COUNT(1) AS registerBox from SPM_DECLAREINFO
where BUREAU_TASK ='0' AND ONETHING_TODO_FLAG='0' AND UPDATETIME is not NULL AND YWNO ='00' AND REGIONAL_GENERAL=0 and ISVALID ='1'),
(select
COUNT(1) AS accepted from SPM_DECLAREINFO
where BUREAU_TASK ='1' AND DECLARE_PERSON_ID = 'bfa800bd0000000014e1beec00000004' and ISVALID ='1'),
(select count(1) AS onlinedeclare from SPM_DECLAREINFO where DECLARESOURCE IN(2,3) AND ONLINE_STATE ='1' and BUSINESSID in
('bfa80104ffffffffd9bac2a500000026','ac10678e000000007998874a0000001e','bfa80104ffffffffd9bb24020000001a','ac10678effffffffb1f95d91ffffffdf','ac10678effffffffb1ff00c3ffffffe1','ac10678effffffffe48daf5200000067','ac10678e000000000ddc91d10000028e','ac10678e000000000da825fd00000351','ac10678e0000000034286f4a000002d4','bfa80104fffffffff88011f400000063','ac10678e00000000422d60910000050d','ac10678e000000000dc41ad500000375','ac10678e000000000dc41a2200000376','ac10678e000000002435baf6ffffffa9','ac10678e000000002454508effffffbc','ac10678e0000000024571439ffffffc0','ac10678e000000002451c1d0ffffffba','ac10678e000000002d91098dffffffe8','ac10678e000000002d913abdffffffef','ac10678e00000000245493acffffffbe','ac10678e000000001044ba6c00000468','ac10678e0000000041e0655700000014','ac10678e0000000041debf480000000a','ac10678e0000000041dec18e00000012','ac10678e0000000041dec0c00000000d','ac10678e0000000041dec03d00000010','ac10678e0000000041dfe08800000019','ac10678e0000000041fc62520000002e','ac10678e000000000de1994e000002a0','ac10678e000000002d834d4bffffffdb','ac10678e0000000041e124df0000001c','ac10678effffffffa427828a00000060','bfa80104ffffffffd9bc6b0b00000029','bfa80104ffffffffd9bbcd5300000032','bfa80104ffffffffd9bd4bf800000037','bfa80104ffffffffd9bed67e00000039','bfa80104ffffffffd9c52f1f00000048','bfa80104ffffffffe00a9da1ffffff91','bfa80104ffffffffe00bdd1bffffff93','bfa80104ffffffffe017e4a2ffffffa2','bfa80104ffffffffdee542feffffff83','ac10678e0000000028722e2bffffffb2','ac10678e00000000287137eaffffffb1','bfa80104ffffffffd9c09c7f0000003f','bfa80104ffffffffd9c0e9d800000041','bfa80104ffffffffd9c1817000000043','bfa80104ffffffffdef537f3ffffff88','bfa80104ffffffffdef77a27ffffff8a','bfa80104ffffffffdef7a721ffffff8b','ac10678effffffffbd3126de0000008c','ac10678effffffffa41f6a540000005a','bfa80104ffffffffe4b9116600000016','bfa80104ffffffffe4ba317200000018','ac10678e000000002874093bffffffb6','bfa80104ffffffffe4b7728400000014','bfa80104ffffffffe016e805ffffffa1','ac10678e00000000287d91e4ffffffbc','bfa80104ffffffffe00cf499ffffff95','bfa80104ffffffffe00f7f0dffffff97','bfa80104ffffffffe013f52fffffff9c','bfa80104ffffffffdee5de86ffffff85','bfa80104ffffffffdef00113ffffff87','bfa80104ffffffffe0070e51ffffff90','ac10678e00000000287c433effffffba','ac10678e00000000287c9d3affffffbb','ac10678effffffff9e9de87d00000106','ac10678effffffffd633f7af00000009','bfa80104ffffffffd9c89e0c0000004c','bfa80104ffffffffd9c89e5f00000053','bfa80104ffffffffd9c7a9ea0000004a','bfa80104ffffffffd9cd6ea40000005d','bfa80104ffffffffe3e9705f00000005','bfa80104ffffffffe3ea264100000006','bfa80104ffffffffe3e9d03c00000007','bfa80104ffffffffe015c484ffffffa0','bfa80104ffffffffd9c434e800000046','bfa80104fffffffff951e5a40000005a','bfa80104ffffffffe014d1c5ffffff9e','ac10678e00000000287a8c90ffffffb9','ac10678e0000000028756d11ffffffb7','bfa80104ffffffffdefd16aeffffff8c','bfa80104ffffffffe012fccfffffff9b','ac10678effffffff9296cb6b0000027f','ac10678effffffffabfd782700000227','ac10678effffffffabfd73c800000225','ac10678effffffffabfd79130000022b','ac10678effffffffabfd79a600000229','ac10678effffffffabfd7a860000022d','ac10678effffffffabfd7af00000022f','ac10678effffffff8e69b20f000001e9','ac10678e00000000736f5de600000057','ac10678e00000000736f5e800000005e','ac10678e00000000736f5e2900000066','ac10678e00000000736f5fc20000006d','ac10678e00000000736f5f460000006f','ac10678e00000000736f60cb00000071','ac10678e00000000736f606600000077','ac10678e00000000736f60fc0000007d','ac10678e00000000736f6208ffffff82','ac10678e00000000736f638affffff84','ac10678e00000000736f630affffff86','ac10678e00000000736f6488ffffff88','ac10678e00000000736f6409ffffff8a','ac10678e00000000736f65a4ffffff90','ac10678e00000000736f6540ffffff96','ac10678effffffff8e6a1b58000001e0','ac10678effffffff8e6a1cd0000001e2','ac10678effffffff8e6a1da1000001e6','ac10678effffffff8e6a1c38000001e4','ac10678effffffff8e6c24ba00000304','ac10678effffffff8e6c2336000001fc','ac10678effffffffbf438a21000000b1','ac10678effffffffbf438ab3000000af','ac10678effffffffbf438b7d000000b3','ac10678effffffffbf438bcd000000b5','ac10678e00000000737301c200000134','ac10678e000000007373013d00000136','ac10678e00000000737302b100000138','ac10678e00000000737302220000013a','ac10678e00000000737303940000013c','ac10678e0000000007aa6941ffffffcd','ac10678e000000000690f28100000227','ac10678e000000000699407800000240','ac10678e0000000003ab52c100000005','ac10678e0000000003bf45ea00000047','ac10678e0000000003c72dd90000004e','ac10678e0000000003c72e3f00000053','ac10678e0000000041dd890c00000007','ac10678effffffffbae8c4ae0000015c','ac10678e0000000003d4c19500000065','ac10678e0000000007c16b7200000166','ac10678e0000000003c8ca7700000062','ac10678e0000000003b69f600000002f','ac10678e0000000006ad5c0800000183','ac10678e000000001f87264800000014','ac10678e0000000003afca8100000010','ac10678effffffffcd04d65d0000001b','ac10678effffffffe4197d2a000000ec','ac10678e00000000736f56e300000032','ac10678effffffffb81bd909000003eb','ac10678effffffffb81bdb7a000003f2','ac10678effffffffb5c93911ffffffe9','ac10678effffffffb5c93aaeffffffeb','ac10678effffffffb5c93a29ffffffed','ac10678e00000000736f6850ffffffb4','ac10678e00000000736f6939ffffffc6','ac10678e00000000736f6ac4ffffffc8','ac10678e00000000736f6be9ffffffcc','ac10678e00000000736f6a5dffffffca','ac10678e00000000736f6cb8ffffffd5','ac10678e00000000736f6c60ffffffdb','ac10678e00000000736f6d40ffffffed','ac10678e00000000736f6df7fffffff5','ac10678e00000000736f6f87fffffff7','bfa77f89ffffffffb69d489100000003','ac10678e00000000378b4f1000000466','ac10678efffffffffdb0c0bbffffff87','ac10678efffffffffdb0befaffffff85','ac10678effffffff8e6d2ff700000315','ac10678effffffff8e6d305d00000317','ac10678effffffff8e6cc48600000336','ac10678effffffffb5c93b9fffffffef','ac10678effffffffb5c93b14fffffff1','ac10678effffffffb5c93c8afffffff3','ac10678effffffffb5c0efc2ffffffce','ac10678effffffffb5c0ef3dffffffd0','ac10678effffffffb5c0f0a8ffffffd2','ac10678effffffffb5c0f013ffffffd4','ac10678effffffffb5c0f17cffffffd6','ac10678e00000000378cfe6000000470','ac10678effffffffb52eabbc00000069','ac10678e000000002c9814e600000027','ac10678efffffffffea45d1b00000177','ac10678effffffff8e6e07d600000343','ac10678effffffff8e6ce38600000341','ac10678effffffffb5a6f23fffffffaa','ac10678effffffff8e6bacaa00000312','ac10678effffffffba4052e8ffffffbe','ac10678effffffffb5cadb50fffffff7','ac10678e00000000378af75b0000046d','ac10678efffffffffea012c00000016b','ac10678efffffffffe9b6b8300000168','ac10678effffffff8e6d6e6e0000031a','ac10678effffffff8e6d6fd00000031c','ac10678effffffff8e6d6f3a0000031e','ac10678efffffffffea4c3150000008b','ac10678efffffffffea4c4d20000008d','ac10678efffffffffea4c57e0000008f','ac10678effffffff8e6c9b6800000321','ac10678effffffff8e6c9ccb00000323','ac10678effffffff8e6c9c2c00000325','ac10678effffffff8e6c9d9300000327','ac10678effffffff8e6c9cf700000329','ac10678effffffff8e6c9d630000032b','ac10678effffffff8e6c9ecd0000032d','ac10678effffffff8e6cc24400000330','ac10678effffffff8e6cc3b600000332','ac10678effffffff8e6cc31e00000334','ac10678effffffff8e6cc4ea00000338','ac10678effffffff8e6cc44d0000033a','ac10678effffffff8e6cc5b30000033c','ac10678effffffff8e6cc5190000033e','ac10678effffffffb5c2a5ddffffffdb','ac10678effffffffb6e0869a000002f4','ac10678effffffffb6e08632000002f6','ac10678effffffffb6e08798000002f8','ac10678effffffffb6e08704000002fa','ac10678effffffffbb7cdfacffffffb9','ac10678effffffffba4825fcffffffce','ac10678effffffffba80d7a5fffffff1','ac10678efffffffffe946ad100000156','ac10678efffffffffea0cab00000016e','ac10678efffffffffe94d30600000159','ac10678efffffffffe9602730000015c','ac10678efffffffffe9a739100000164','ac10678e00000000378e87c300000474','ac10678effffffffb5a1831fffffffa4','ac10678effffffffb5a184cfffffffa6','ac10678effffffffb81e197100000504','ac10678effffffffcbfdf9ff00000006','ac10678effffffffb6872b6500000209','ac10678effffffffb6872e2500000212','ac10678effffffff8e670669000001b7','ac10678effffffff8e6770d3000001be','ac10678effffffff8e6700d8000001c6','ac10678effffffff8e682171000001ca','ac10678effffffff8e685e2e000001d3','ac10678effffffff8e687a6a000001d7','ac10678effffffff8e6796a7000001da','ac10678effffffff8e61c23a000001aa','ac10678efffffffffdacf71c0000007d','ac10678effffffff8e6696ff000001c1','ac10678effffffff8e684490000001cf','ac10678effffffff8e67b5b8000001dd','ac10678effffffffb6ccd12d00000336','ac10678e00000000736f6287ffffff80','ac10678efffffffffdb0c068ffffff89','ac10678efffffffffdb0c114ffffff8b','ac10678effffffffa64ffcd200000237','ac10678e000000000ffed4bb00000290','ac10678e000000000ff690120000037b','ac10678e000000001b3d001000000291','ac10678e000000001b3e022d0000029a','ac10678e000000001b3e608c000002d6','ac10678e000000001b3e6105000002dc','ac10678e000000002f4dec39fffffff6','ac10678effffffffa15eadb8000009d3','ac10678effffffffa1600d4a000009ef','ac10678effffffffa15eae18000009dd','ac10678effffffffa16010f800000b08','ac10678effffffffa16013b400000b27','ac10678effffffffa160141c00000b36','ac10678effffffffa16448db00000b4b','ac10678effffffffa1644bb800000b65','ac10678effffffffa1644c1800000b70','ac10678effffffffa1644dd200000b77','ac10678effffffffa1644e8500000b7c','ac10678effffffffa1644e4800000a87','ac10678effffffffa1644f0500000a8c','ac10678effffffffa15eaa06000009bc','ac10678e000000002f4e5b7afffffff5','ac10678e000000002f4e9d4dfffffff7','ac10678effffffffa15eab6b000009ce','ac10678effffffffa15eaf73000009e2','ac10678effffffffa1600fa6000009f9','bfa781e9ffffffff95ea11a2000027f1','bfa781e9ffffffff95ea0b19000027e8','ac10678effffffffede02befffffdfe3','ac10678effffffffee1a393cffff854f','ac10678e000000007757782000000696','ac10678e000000007dcde015000000af','ac10678e000000007dcf6a32000000b2','ac10678efffffffff2dc38dfffffff86','ac10678e000000001bed58f4ffffc5f0','ac10678effffffffee1a3717ffff854c','ac10678effffffff8483e1830000083b','ac10678effffffffa15ea990000009ae','ac10678effffffffa160114f00000b16','ac10678effffffffa1644a0e00000b61','ac10678e0000000017b335830000021e','ac10678effffffffa1645a2b00000af4','ac10678e000000007dceeeca000000b5','ac10678efffffffff2d45b8900000074','ac10678efffffffff2d4a08f0000007c','ac10678effffffffede123bfffffe199','ac10678effffffffbe8ed15e00001d2e','ac10678effffffffa1644b6b00000b6a','ac10678effffffffa15091c2000007db','ac10678effffffff8cf8c3600000074b','ac10678effffffff8cf8c57f0000074f','ac10678effffffff8cf8c68c00000755','ac10678effffffffedfc9a2efffff5aa','ac10678effffffffbe8ed31f00001d34','ac10678e000000007e4aa7e800000019','ac10678effffffffb6431f720000409c','ac10678e00000000736f6613ffffffa6','ac10678effffffffa135d21700000280') and ISVALID ='1') ,
(select
COUNT(CASE WHEN ONETHING_TODO_FLAG ='1' THEN 1 END) AS onethingAccept ,
COUNT(CASE WHEN REGIONAL_GENERAL ='1'AND SCHEME_REVIEW_FLAG ='0' AND UPDATETIME IS NOT NULL THEN 1 END) AS documentsViewed
from SPM_DECLAREINFO
where YWNO ='00' AND BUREAU_TASK='0' and ISVALID ='1'),
(select
COUNT(1) AS rqgh
from SPM_DECLAREINFO
where YWNO <>'00' AND BUREAU_TASK='1' AND BUSINESSNAME like '%燃气过户%' and ISVALID ='1'),
(select
COUNT(CASE WHEN ONETHING_TODO_FLAG='0' THEN 1 END) AS businessTodo ,
COUNT(CASE WHEN ONETHING_TODO_FLAG='1' THEN 1 END) AS onethingTodo ,
COUNT(CASE WHEN ONETHING_TODO_FLAG='1' THEN 1 END) AS regionalTodo
from SPM_DECLAREINFO
where INSTANCEID in('bfa7810a0000000033aee28d00000027') and ISVALID ='1')
查询结果：
发现查询时间0.6秒，businessid的查询是索引扫描
我觉得这次优化sql结构是主要的，索引是次要的，于是把索引删了，执行了一下优化后的sql
查询结果：
确实是这样的！而且大约估计出来，该索引带来了0.1秒的性能提升！
后续个人觉得应该从加其他字段索引或者联合索引达到索引覆盖的目的提升性能，并且探究为什么后一种sql结构的性能提升巨大
