使用存储过程处理数据与在检索后将其输入函数相比是否有性能提升？

Question

Vinay

Asked: 2013-04-30 08:51:20 +0800 CST2013-04-30 08:51:20 +0800 CST 2013-04-30 08:51:20 +0800 CST

优化查询

772

谁能帮我调整这个查询.....

explain extended 

 SELECT DISTINCT a.msisdn FROM `std_msc_opr_wise` a  
LEFT OUTER JOIN 
 (SELECT msisdn 
  FROM as_treat_pre_usage_30days 
  GROUP BY msisdn 
  HAVING SUM(std_total_og_mou)>0) b ON a.msisdn=b.msisdn
WHERE 
 b.msisdn IS NULL 
 AND a.call_dt BETWEEN '2013-03-27' AND '2013-04-28';

    +----+-------------+---------------------------+-------+---------------+---------+---------+------+----------+----------+-----------------------------------+
    | id | select_type | table                     | type  | possible_keys | key     | key_len | ref  | rows     | filtered | Extra                             |
    +----+-------------+---------------------------+-------+---------------+---------+---------+------+----------+----------+-----------------------------------+
    |  1 | PRIMARY     | a                         | ALL   | ix_cd         | NULL    | NULL    | NULL | 73985537 |    50.00 | Using where; Using temporary      |
    |  1 | PRIMARY     | <derived2>                | ALL   | NULL          | NULL    | NULL    | NULL |  1121664 |   100.00 | Using where; Not exists; Distinct |
    |  2 | DERIVED     | as_treat_pre_usage_30days | index | NULL          | PRIMARY | 14      | NULL |  3033621 |   100.00 |                                   |
    +----+-------------+---------------------------+-------+---------------+---------+---------+------+----------+----------+-----------------------------------+
    3 rows in set, 1 warning (1.70 sec)

________________________________________________________________________

explain extended 

    SELECT DISTINCT a.msisdn FROM `std_msc_opr_wise` a FORCE INDEX(ix_cd) LEFT OUTER JOIN
    (SELECT msisdn FROM as_treat_pre_usage_30days GROUP BY msisdn HAVING SUM(std_total_og_mou)>0 )b
    ON a.msisdn=b.msisdn
    WHERE b.msisdn IS NULL AND a.call_dt BETWEEN '2013-03-27' AND '2013-04-28'; 

    +----+-------------+---------------------------+-------+---------------+---------+---------+------+----------+----------+-----------------------------------+
    | id | select_type | table                     | type  | possible_keys | key     | key_len | ref  | rows     | filtered | Extra                             |
    +----+-------------+---------------------------+-------+---------------+---------+---------+------+----------+----------+-----------------------------------+
    |  1 | PRIMARY     | a                         | range | ix_cd         | ix_cd   | 4       | NULL | 36992756 |   100.00 | Using where; Using temporary      |
    |  1 | PRIMARY     | <derived2>                | ALL   | NULL          | NULL    | NULL    | NULL |  1121664 |   100.00 | Using where; Not exists; Distinct |
    |  2 | DERIVED     | as_treat_pre_usage_30days | index | NULL          | PRIMARY | 14      | NULL |  3033621 |   100.00 |                                   |
    +----+-------------+---------------------------+-------+---------------+---------+---------+------+----------+----------+-----------------------------------+

    ____________________________________________________

TABLE SCHEMA

____________________________________________________
CREATE TABLE `as_treat_pre_usage_30days` (
  `msisdn` varchar(12) NOT NULL COMMENT 'Subscriber number',
  `local_a2a_og_mou` bigint(20) DEFAULT NULL COMMENT 'Local A2A OG MOU of the subs',
  `local_a2o_og_mou` bigint(20) DEFAULT NULL COMMENT 'Local A2O OG MOU of the subs',
  `std_total_og_mou` bigint(20) DEFAULT NULL COMMENT 'STD OG MOU of the subs',
  `total_og_mou` bigint(20) DEFAULT NULL COMMENT 'Total OG MOU of the subs',
  PRIMARY KEY (`msisdn`),
  KEY `ix_std` (`std_total_og_mou`)
) ENGINE=InnoDB
____________________________________________________
CREATE TABLE `std_msc_opr_wise` (
  `msisdn` varchar(12) DEFAULT NULL COMMENT 'Mobile Number of the Subscriber',
  `call_dt` date DEFAULT NULL,
  `destination_type` varchar(5) DEFAULT NULL COMMENT 'Destination Type - A2A or A2O',
  `duration` int(6) DEFAULT NULL COMMENT 'Call Duration in Seconds',
  KEY `ix_ms` (`msisdn`),
  KEY `ix_cd` (`call_dt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (to_days(call_dt))
(PARTITION p20130120 VALUES LESS THAN (735253) ENGINE = InnoDB,
 PARTITION p20130121 VALUES LESS THAN (735254) ENGINE = InnoDB,
 PARTITION p20130122 VALUES LESS THAN (735255) ENGINE = InnoDB,
 PARTITION p20130123 VALUES LESS THAN (735256) ENGINE = InnoDB,
 PARTITION p20130124 VALUES LESS THAN (735257) ENGINE = InnoDB,
 PARTITION p20130126 VALUES LESS THAN (735259) ENGINE = InnoDB,
 PARTITION p20130127 VALUES LESS THAN (735260) ENGINE = InnoDB,
 PARTITION p20130128 VALUES LESS THAN (735261) ENGINE = InnoDB,
 PARTITION p20130129 VALUES LESS THAN (735262) ENGINE = InnoDB,
 PARTITION p20130130 VALUES LESS THAN (735263) ENGINE = InnoDB,
 PARTITION p20130131 VALUES LESS THAN (735264) ENGINE = InnoDB,
 PARTITION p20130201 VALUES LESS THAN (735265) ENGINE = InnoDB,
 PARTITION p20130202 VALUES LESS THAN (735266) ENGINE = InnoDB,
 PARTITION p20130203 VALUES LESS THAN (735267) ENGINE = InnoDB,
 PARTITION p20130204 VALUES LESS THAN (735268) ENGINE = InnoDB,
 PARTITION p20130205 VALUES LESS THAN (735269) ENGINE = InnoDB,
 PARTITION p20130206 VALUES LESS THAN (735270) ENGINE = InnoDB,
 PARTITION p20130207 VALUES LESS THAN (735271) ENGINE = InnoDB,
 PARTITION p20130208 VALUES LESS THAN (735272) ENGINE = InnoDB,
 PARTITION p20130209 VALUES LESS THAN (735273) ENGINE = InnoDB,
 PARTITION p20130210 VALUES LESS THAN (735274) ENGINE = InnoDB,
 PARTITION p20130211 VALUES LESS THAN (735275) ENGINE = InnoDB,
 PARTITION p20130212 VALUES LESS THAN (735276) ENGINE = InnoDB,
 PARTITION p20130213 VALUES LESS THAN (735277) ENGINE = InnoDB,
 PARTITION p20130214 VALUES LESS THAN (735278) ENGINE = InnoDB,
 PARTITION p20130215 VALUES LESS THAN (735279) ENGINE = InnoDB,
 PARTITION p20130216 VALUES LESS THAN (735280) ENGINE = InnoDB,
 PARTITION p20130217 VALUES LESS THAN (735281) ENGINE = InnoDB,
 PARTITION p20130218 VALUES LESS THAN (735282) ENGINE = InnoDB,
 PARTITION p20130219 VALUES LESS THAN (735283) ENGINE = InnoDB,
 PARTITION p20130220 VALUES LESS THAN (735284) ENGINE = InnoDB,
 PARTITION p20130221 VALUES LESS THAN (735285) ENGINE = InnoDB,
 PARTITION p20130222 VALUES LESS THAN (735286) ENGINE = InnoDB,
 PARTITION p20130223 VALUES LESS THAN (735287) ENGINE = InnoDB,
 PARTITION p20130224 VALUES LESS THAN (735288) ENGINE = InnoDB,
 PARTITION p20130225 VALUES LESS THAN (735289) ENGINE = InnoDB,
 PARTITION p20130226 VALUES LESS THAN (735290) ENGINE = InnoDB,
 PARTITION p20130227 VALUES LESS THAN (735291) ENGINE = InnoDB,
 PARTITION p20130228 VALUES LESS THAN (735292) ENGINE = InnoDB,
 PARTITION p20130301 VALUES LESS THAN (735293) ENGINE = InnoDB,
 PARTITION p20130302 VALUES LESS THAN (735294) ENGINE = InnoDB,
 PARTITION p20130303 VALUES LESS THAN (735295) ENGINE = InnoDB,
 PARTITION p20130304 VALUES LESS THAN (735296) ENGINE = InnoDB,
 PARTITION p20130305 VALUES LESS THAN (735297) ENGINE = InnoDB,
 PARTITION p20130306 VALUES LESS THAN (735298) ENGINE = InnoDB,
 PARTITION p20130307 VALUES LESS THAN (735299) ENGINE = InnoDB,
 PARTITION p20130308 VALUES LESS THAN (735300) ENGINE = InnoDB,
 PARTITION p20130309 VALUES LESS THAN (735301) ENGINE = InnoDB,
 PARTITION p20130310 VALUES LESS THAN (735302) ENGINE = InnoDB,
 PARTITION p20130311 VALUES LESS THAN (735303) ENGINE = InnoDB,
 PARTITION p20130312 VALUES LESS THAN (735304) ENGINE = InnoDB,
 PARTITION p20130313 VALUES LESS THAN (735305) ENGINE = InnoDB,
 PARTITION p20130314 VALUES LESS THAN (735306) ENGINE = InnoDB,
 PARTITION p20130315 VALUES LESS THAN (735307) ENGINE = InnoDB,
 PARTITION p20130316 VALUES LESS THAN (735308) ENGINE = InnoDB,
 PARTITION p20130317 VALUES LESS THAN (735309) ENGINE = InnoDB,
 PARTITION p20130318 VALUES LESS THAN (735310) ENGINE = InnoDB,
 PARTITION p20130319 VALUES LESS THAN (735311) ENGINE = InnoDB,
 PARTITION p20130320 VALUES LESS THAN (735312) ENGINE = InnoDB,
 PARTITION p20130321 VALUES LESS THAN (735313) ENGINE = InnoDB,
 PARTITION p20130322 VALUES LESS THAN (735314) ENGINE = InnoDB,
 PARTITION p20130323 VALUES LESS THAN (735315) ENGINE = InnoDB,
 PARTITION p20130324 VALUES LESS THAN (735316) ENGINE = InnoDB,
 PARTITION p20130325 VALUES LESS THAN (735317) ENGINE = InnoDB,
 PARTITION p20130326 VALUES LESS THAN (735318) ENGINE = InnoDB,
 PARTITION p20130327 VALUES LESS THAN (735319) ENGINE = InnoDB,
 PARTITION p20130328 VALUES LESS THAN (735320) ENGINE = InnoDB,
 PARTITION p20130329 VALUES LESS THAN (735321) ENGINE = InnoDB,
 PARTITION p20130330 VALUES LESS THAN (735322) ENGINE = InnoDB,
 PARTITION p20130331 VALUES LESS THAN (735323) ENGINE = InnoDB,
 PARTITION p20130401 VALUES LESS THAN (735324) ENGINE = InnoDB,
 PARTITION p20130402 VALUES LESS THAN (735325) ENGINE = InnoDB,
 PARTITION p20130403 VALUES LESS THAN (735326) ENGINE = InnoDB,
 PARTITION p20130404 VALUES LESS THAN (735327) ENGINE = InnoDB,
 PARTITION p20130405 VALUES LESS THAN (735328) ENGINE = InnoDB,
 PARTITION p20130406 VALUES LESS THAN (735329) ENGINE = InnoDB,
 PARTITION p20130407 VALUES LESS THAN (735330) ENGINE = InnoDB,
 PARTITION p20130408 VALUES LESS THAN (735331) ENGINE = InnoDB,
 PARTITION p20130409 VALUES LESS THAN (735332) ENGINE = InnoDB,
 PARTITION p20130410 VALUES LESS THAN (735333) ENGINE = InnoDB,
 PARTITION p20130411 VALUES LESS THAN (735334) ENGINE = InnoDB,
 PARTITION p20130413 VALUES LESS THAN (735336) ENGINE = InnoDB,
 PARTITION p20130414 VALUES LESS THAN (735337) ENGINE = InnoDB,
 PARTITION p20130415 VALUES LESS THAN (735338) ENGINE = InnoDB,
 PARTITION p20130416 VALUES LESS THAN (735339) ENGINE = InnoDB,
 PARTITION p20130417 VALUES LESS THAN (735340) ENGINE = InnoDB,
 PARTITION p20130418 VALUES LESS THAN (735341) ENGINE = InnoDB,
 PARTITION p20130419 VALUES LESS THAN (735342) ENGINE = InnoDB,
 PARTITION p20130420 VALUES LESS THAN (735343) ENGINE = InnoDB,
 PARTITION p20130421 VALUES LESS THAN (735344) ENGINE = InnoDB,
 PARTITION p20130422 VALUES LESS THAN (735345) ENGINE = InnoDB,
 PARTITION p20130423 VALUES LESS THAN (735346) ENGINE = InnoDB,
 PARTITION p20130424 VALUES LESS THAN (735347) ENGINE = InnoDB,
 PARTITION p20130425 VALUES LESS THAN (735348) ENGINE = InnoDB,
 PARTITION p20130426 VALUES LESS THAN (735349) ENGINE = InnoDB,
 PARTITION p20130427 VALUES LESS THAN (735350) ENGINE = InnoDB,
 PARTITION p20130428 VALUES LESS THAN (735351) ENGINE = InnoDB,
 PARTITION p20130429 VALUES LESS THAN (735352) ENGINE = InnoDB,
 PARTITION p20130430 VALUES LESS THAN (735353) ENGINE = InnoDB,
 PARTITION p20130501 VALUES LESS THAN (735354) ENGINE = InnoDB) *

1 个回答

Voted

druzin · Answer 1 · 2013-04-30T09:02:21+08:00

Best Answer

druzin

2013-04-30T09:02:21+08:002013-04-30T09:02:21+08:00

你当然可以试试这个；

SELECT DISTINCT msisdn 
FROM std_msc_opr_wise  
WHERE 
 call_dt BETWEEN '2013-03-27' AND '2013-04-28'
 AND msisdn NOT IN (
   select msisdn
   from as_treat_pre_usage_30days
   group by msisdn
   having SUM(std_total_og_mou)>0
   )

1

优化查询

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

授予用户对所有表的访问权限

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

优化查询

1 个回答

相关问题