php mysql亿级数据查询_【原创】一个亿级数据库优化过程_MySQL

bitsCN.com

第一部分 棉花数据库问题和分析

1.问题sql

数据库的版本是9i,问题sql有两个：

Sql1：SELECT

c_lotno

FROM b_ctn_normal

WHERE d_prodatetime BETWEEN to_date('2011-07-01', 'yyyy-mm-dd HH24:MI:SS') AND

to_date('2012-07-03', 'yyyy-mm-dd HH24:MI:SS')

AND n_madein = 65

AND rownum < 31

Sql2:SELECT count(c_bale)

FROM b_ctn_normal

WHERE d_prodatetimeBETWEEN to_date('2011-07-01', 'yyyy-mm-dd HH24:MI:SS') AND

to_date('2012-07-03', 'yyyy-mm-dd HH24:MI:SS')

这俩sql其实非常简单，就是一个按照时间的分页查询，一个查询时间范围内的总数据量。

但是这个表的数据量很大，41803656条数据，单表容量超过21G。因此查询非常慢，仅仅查询30条数据就需要耗费十几分钟。甚至查不出结果。

2 表概况

表b_ctn_normal是一个分区表,按照D_VERIFYDATETIME进行了range分区，分区的策略为2010年前每年一个分区,2010年后每月一个分区.该表的数据量为41803656条。partition by range (D_VERIFYDATETIME)

partition PART_20080101 values less than (TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

partition PART_20090101 values less than (TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))—后续的省略

另外该表上建了大量的索引，见表1：

3.表存在的问题

以下是索引的概况统计信息。

NDEX_NAMEDISTINCT_KEYSNUM_ROWSSAMPLE_SIZE

I_CTN_NORMAL_99441805079.06306318459008

I_NORMAL_MADEIN11741804897.35461089544621

I_CTN_NORMAL_668041767143.70964549580744

I_CTN_NORMAL_778641473125.09630439479665

I_CTN_NORMAL_2236641875654.44383739937607

I_CTN_NORMAL_1188941867424.931405911007070

I_CTN_NORMAL_55195740169648.6955449058949

I_NORMAL_UPLOADTIME11725341866396.719388910087608

I_CTN_NORMAL_3338447241842227.869689610621842

I_NORMAL_D_COLORGRADETIME148586341727490.27348619119757

GLOBAL_INDEX_D_VERIFYDATETIME21162573418042569592128

PRIMARY1_ID41804473418044739540784

UNI_NORMAL_C_BALE14184180941841809.17815877023237

【表1】索引概况

l 表不是很宽，但是竟然建了13个索引，而且8个索引可选性很差，每个索引都占据不少段空间，极大的浪费了存储空间。

l 索引没有分区，千万级别的数据量，本身查找索引就很耗时，因此应当对索引分区其高索引检索性能。

l 表的索引和表应该建立在不同的表空间分开存放,同时表的分区在不同的表空间存放。

l 分区的记录不均匀，分区不合理

分区的统计信息显示，大量的数据集中在了PART_20100101和PART_20090101分区，分区很不合理，大大削弱了分区表的作用。应该对分区进行细粒度的划分，均匀分布数据。TABLE_NAMEPARTITION_NAMENUM_ROWSSAMPLE_SIZE

B_CTN_NORMALPART_20100101155804002883811

B_CTN_NORMALPART_20090101130074832420607

B_CTN_NORMALPART_201012013809673709735

B_CTN_NORMALPART_201101012656138494675

B_CTN_NORMALPART_201011012641196492471

B_CTN_NORMALPART_201102011169697217919

B_CTN_NORMALPART_201002011106187205854

B_CTN_NORMALPART_20110401662618123426

B_CTN_NORMALPART_2011030127119050600

B_CTN_NORMALPART_2010050120517337933

B_CTN_NORMALPART_2010040119422335804

B_CTN_NORMALPART_2010060115419528641

B_CTN_NORMALPART_2011050113708525587

B_CTN_NORMALPART_2010030110574719575

B_CTN_NORMALPART_201010016442411960

B_CTN_NORMALPART_20100701337436206

B_CTN_NORMALPART_201008014044725

B_CTN_NORMALPART_20100901283283

B_CTN_NORMALPART_201110018080

B_CTN_NORMALPART_200801015353

B_CTN_NORMALPART_201112012121

B_CTN_NORMAL