mysql 临时索引_mysql 借用临时加索引的方式，使原来几分钟以上才能查出来的数据，优化到2秒钟以内...

借用临时加索引的方式，使原来几分钟以上才能查出来的数据，优化到2秒钟以内

CREATE DEFINER=`root`@`%` PROCEDURE `sp_tbpm_lyh`(IN year1 INT,IN year2 INT,IN bmonthe INT,IN emonthe INT,IN endnum INT)

BEGIN

DROP TABLE IFEXISTS tmp_table3;

DROP TABLE IF EXISTS tmp_table4;

CREATE TEMPORARY TABLE `tmp_table3` (

`taxpayer_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '',

`taxpayer_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '',

`collection_items` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '',

`industry_category` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '',

`bid_industry` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '',

`middle_industry` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '',

`town` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '',

`industry` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '',

`tax_duration_end` date NULL DEFAULT NULL COMMENT '',

`amount_paid_in` double(10, 2) NULL DEFAULT NULL ,

`swbnlj` double(10, 2) NULL DEFAULT NULL COMMENT '',

`register_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '',

INDEX `taxpayer_name_citems`(`taxpayer_name`, `collection_items`) USING BTREE,

INDEX `tax_duration_end`(`tax_duration_end`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = '' ROW_FORMAT = Compact;

CREATE TEMPORARY TABLE `tmp_table4` (

`taxpayer_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '',

`collection_items` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '',

`swsnlj` double(10, 2) NULL DEFAULT NULL ,

INDEX `taxpayer_name_citems`(`taxpayer_name`, `collection_items`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;

insert into tmp_table3(taxpayer_name,

industry_category,

bid_industry,

middle_industry,

industry,

tax_duration_end,

amount_paid_in,

swbnlj,

collection_items,

town,

taxpayer_code,

register_type ) SELECT

sw.taxpayer_name,

sw.industry_category,

sw.bid_industry,

sw.middle_industry,

sw.industry,

sw.tax_duration_end,

sw.amount_paid_in,

IFNULL( sum( sw.amount_paid_in ), 0.0 ) AS 'swbnlj',

sw.collection_items,

sw.town,

sw.taxpayer_code,

sw.register_type

FROM

sw_net_storage_info sw

WHERE

1 = 1

AND YEAR ( sw.tax_duration_end ) =year1

GROUP BY

sw.taxpayer_name,

sw.collection_items ;

insert into tmp_table4(taxpayer_name,swsnlj,collection_items) SELECT

sw.taxpayer_name,

IFNULL( sum( sw.amount_paid_in ), 0.0 ) AS 'swsnlj',

sw.collection_items

FROM

sw_net_storage_info sw

WHERE

1 = 1

AND YEAR ( sw.tax_duration_end ) =year2- 1

GROUP BY

sw.taxpayer_name,

sw.collection_items;

SELECT

b.*

FROM

(

SELECT

t.*,

CASE

WHEN @rowtotal = t.tbzj THEN

@rownum

WHEN @rowtotal := t.tbzj THEN

@rownum := @rownum + 1

WHEN @rowtotal = 0 THEN

@rownum := @rownum + 1

END AS rownum

FROM

( SELECT @rownum := 0, @rowtotal := NULL ) r,

(

SELECT

swtb.taxpayer_name,

swtb.swbnlj,

swtb.tbzj,

swtb.swsnlj,

sum( CASE WHEN dl.YEAR =year1 AND dl.MONTH BETWEEN bmonthe AND emonthe THEN dl.kWh ELSE 0.0 END ) AS 'bnkwh',

sum( CASE WHEN dl.YEAR =year2- 1 AND dl.MONTH BETWEEN bmonthe AND emonthe THEN dl.kWh ELSE 0.0 END ) AS 'snkwh'

FROM

(

SELECT

swbn.taxpayer_name,

swbn.industry_category,

swbn.bid_industry,

swbn.middle_industry,

swbn.industry,

swbn.tax_duration_end,

IFNULL( sum( swbn.swbnlj ), 0.0 ) AS swbnlj,

IFNULL( sum( swbn.swbnlj ), 0.0 ) - IFNULL( sum( swsn.swsnlj ), 0.0 ) AS tbzj,

swbn.collection_items,

swbn.town,

swbn.taxpayer_code,

swbn.register_type,

IFNULL( sum( swsn.swsnlj ), 0.0 ) AS swsnlj

FROM

tmp_table3 AS swbn

LEFT OUTER JOIN tmp_table4 AS swsn ON swbn.taxpayer_name = swsn.taxpayer_name

AND swbn.collection_items = swsn.collection_items

WHERE

1 = 1

AND MONTH ( swbn.tax_duration_end ) >=bmonthe

AND MONTH ( swbn.tax_duration_end ) <=emonthe

GROUP BY

swbn.taxpayer_name

) AS swtb

LEFT OUTER JOIN dl_use_info dl ON swtb.taxpayer_name = dl.register_name

WHERE

swtb.tbzj >= 0

GROUP BY

swtb.taxpayer_name

ORDER BY

swtb.tbzj DESC

) AS t

) AS b

WHERE

1 = 1

AND b.rownum <=endnum;

END