一个大的日志表，如何通过分区来提升查询效率

bosstwobread 2018-01-19 12:05:04
RT！这个日志表现在有几百万条数据，之前已经做了按照月份来分表了，现在数据量太大，才没几天这个月的数据已经几百万，听过分区能解决这个问题，请教高人指点，说明下思路即可，具体语句我会去网上查
另外目前界面打开默认是根据显示今天以前的所有日志（这个月的），当然也有其它一些查询条件
...全文
1239 26 打赏 收藏 转发到动态 举报
写回复
用AI写文章
26 条回复
切换为时间正序
请发表友善的回复…
发表回复
kiss阳光 2018-01-26
  • 打赏
  • 举报
 回复
如果是带条件看的话可以考虑写个存储过程提前取出需要的数据，在取出的表的相应字段加上索引，这样可以提高查询的效率。
kingkingzhu 2018-01-25
  • 打赏
  • 举报
 回复
1.你这里的with语句应该没什么含义 2.多一层嵌套，性能肯定是有点影响了，但是你上面的0.1秒和8秒，差的有点多，看不出问题 3.你反正是要排序分页的，考虑下row_number() over( order by CREATE_TIME)
minsic78 2018-01-25
  • 打赏
  • 举报
 回复
1、你的执行计划没有拿好，少了要加的提示，否则能看到资源消耗瓶颈在哪里。 2、上面已经说到了，如果create_time上有索引，而且这个字段为非空，那么你的排序分页结果也会很快——至少翻前几页会很快。
bosstwobread 2018-01-25
  • 打赏
  • 举报
 回复
想了个通过修改后台代码的办法解决了下，现在可以做到0.2秒左右了，就这样吧
bosstwobread 2018-01-24
  • 打赏
  • 举报
 回复
引用 20 楼 minsic78 的回复:
另外，搞个执行计划过来吧，sqlplus执行如下脚本： 
set linesize 200
set pagesize 500
with DAILYRECORD_MONTH as (select t.* from DAILYRECORD_2018_01 t where 1=1 order by CREATE_TIME desc )
select /*+gather_plan_statistics*/t.* from(
Select t.*,ROWNUM rn From DAILYRECORD_MONTH t Where ROWNUM <=28
)t where t.rn >14;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
看了执行计划也看不出什么问题来，你知道为什么，如下语句就很快0.1秒就出来 

with DAILYRECORD_MONTH as (select t.* from platform_system_dailyrecord.DAILYRECORD_2018_01 t order by CREATE_TIME desc)
Select * From DAILYRECORD_MONTH t Where ROWNUM <=20
但是这样就很慢吗？如何能解决这个问题我应该就有办法了，如下要8秒左右 

with DAILYRECORD_MONTH as (select t.* from platform_system_dailyrecord.DAILYRECORD_2018_01 t order by CREATE_TIME desc)
select * from(
Select * From DAILYRECORD_MONTH t Where ROWNUM <=20
)t
我实在是想不通，两者就只差了外面套一层查询，为什么差距这么大
bosstwobread 2018-01-24
  • 打赏
  • 举报
 回复
引用 20 楼 minsic78 的回复:
另外，搞个执行计划过来吧，sqlplus执行如下脚本：

set linesize 200

set pagesize 500

with DAILYRECORD_MONTH as (select t.* from DAILYRECORD_2018_01 t where 1=1 order by CREATE_TIME desc )

select /*+gather_plan_statistics*/t.* from(

Select t.*,ROWNUM rn From DAILYRECORD_MONTH t Where ROWNUM <=28

)t where t.rn >14;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

bosstwobread 2018-01-23
  • 打赏
  • 举报
 回复
引用 17 楼 minsic78 的回复:
另外，where条件中没有主题帖中提到的时间条件？
时间只能是页面上选择月份，不同月份对应不同表，不能选择具体日期范围
minsic78 2018-01-23
  • 打赏
  • 举报
 回复
另外，搞个执行计划过来吧，sqlplus执行如下脚本： 
set linesize 200
set pagesize 500
with DAILYRECORD_MONTH as (select t.* from DAILYRECORD_2018_01 t where 1=1 order by CREATE_TIME desc )
select /*+gather_plan_statistics*/t.* from(
Select t.*,ROWNUM rn From DAILYRECORD_MONTH t Where ROWNUM <=28
)t where t.rn >14;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
minsic78 2018-01-23
  • 打赏
  • 举报
 回复
引用 18 楼 bosstwobread 的回复:
[quote=引用 17 楼 minsic78 的回复:] 另外，where条件中没有主题帖中提到的时间条件？
时间只能是页面上选择月份，不同月份对应不同表，不能选择具体日期范围[/quote] with子查询去掉吧，不去掉时间恐怕是降不下来的
minsic78 2018-01-21
  • 打赏
  • 举报
 回复
另外，where条件中没有主题帖中提到的时间条件？
minsic78 2018-01-21
  • 打赏
  • 举报
 回复
CREATE_TIME上有索引吗？CREATE_TIME字段是否会非空字段？
bosstwobread 2018-01-20
  • 打赏
  • 举报
 回复
引用 11 楼 minsic78 的回复:
[quote=引用 9 楼 bosstwobread 的回复:] [quote=引用 8 楼 minsic78 的回复:] 几天百万，说明你一天的数据量是几十万的量级，这种走索引的效率已经比较差了。 我前面也说到，你登录或者说界面打开的查询就要涉及到几十万数据的查询，分区也解决不了你的问题，考虑到一下子显示几十万数据给前台也没什么意义，你这里少的是将这10几万SQL分页显示（分页得到稳定的较好性能的前提是已经按照天分区）
我们是B/S结构的，分页肯定是做的，不然几十万数据肯定不行的，浏览器一页几百条就够呛了，我先试试按天分区看[/quote] 如果你的其他条件多变且复杂，而且分页还有order by之类的东西，那么会需要更多的优化措施[/quote] 做了日期分区了，效率提升了十来倍，从原来1分多种到现在5秒左右，应该还能优化到一秒以内，感觉SQL语句有点问题 楼里大家为什么总是说不分页，直接显示几十万数据的问题，这个是基本的，没人会这么做的，我也是无语了
minsic78 2018-01-20
  • 打赏
  • 举报
 回复
上SQL和执行计划吧~
bosstwobread 2018-01-20
  • 打赏
  • 举报
 回复
建表的语句真实表名是DAILYRECORD_2018_01 上面贴错成DAILYRECORD_2017_10了
bosstwobread 2018-01-20
  • 打赏
  • 举报
 回复
贴上来大家帮忙再看看能否再优化下： 建表语句： 

-- Create table
create table DAILYRECORD_2017_10
(
  DAILYRECORD_ID   NVARCHAR2(36) not null,
  SYSTEM_NAME      NVARCHAR2(64),
  SYSTEM_VERSION   NVARCHAR2(64),
  MODULE_NAME      NVARCHAR2(64),
  REQUEST_URL      NVARCHAR2(2000),
  REQUEST_TYPE     NVARCHAR2(32),
  REQUEST_IP       NVARCHAR2(32),
  REQUEST_USERNAME NVARCHAR2(64),
  REQUEST_PARAMS   NVARCHAR2(2000),
  REQUEST_TIMESPAN INTEGER,
  RESPONSE_FLAG    INTEGER,
  REMARK           NVARCHAR2(1024),
  CREATE_TIME      DATE,
  USER_MODULE      NVARCHAR2(255),
  OPERATION_NAME   NVARCHAR2(255),
  OPERATION_TYPE   INTEGER,
  USER_LOG_FLAG    INTEGER,
  DESCRIPTION      NVARCHAR2(1024)
)
partition by range (CREATE_TIME)
interval (numtodsinterval(1,'DAY'))
(
   partition p0101 values less than (to_date('2018-01-02','yyyy-mm-dd'))
)
tablespace PLATFORM_SYSTEM_DAILYRECORD
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table DAILYRECORD_2017_10
  add constraint DAILYRECORD_2017_10_PK_ID primary key (DAILYRECORD_ID)
  using index 
  tablespace PLATFORM_SYSTEM_DAILYRECORD
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes 
create index DAILYRECORD_2017_10_IN_CT on DAILYRECORD_2017_10 (CREATE_TIME)
  tablespace PLATFORM_SYSTEM_DAILYRECORD
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Grant/Revoke object privileges 
grant select on DAILYRECORD_2017_10 to NO_PAY_USER;
查询的SQL： 

with DAILYRECORD_MONTH as (select t.* from DAILYRECORD_2018_01 t where 1=1 order by CREATE_TIME desc )
select t.* from(
Select t.*,ROWNUM rn From DAILYRECORD_MONTH t Where ROWNUM <=28
)t where t.rn >14
结果基本是5、6秒的时间，但单单执行如下语句是0.01秒之内的，所以感觉还能改下SQL再优化下，ORACLE的查询原理不是很了解，试了一些方式效果也不理想 

with DAILYRECORD_MONTH as (select t.* from DAILYRECORD_2018_01 t where 1=1 order by CREATE_TIME desc )
Select t.*,ROWNUM rn From DAILYRECORD_MONTH t Where ROWNUM <=55
minsic78 2018-01-19
  • 打赏
  • 举报
 回复
引用 9 楼 bosstwobread 的回复:
[quote=引用 8 楼 minsic78 的回复:] 几天百万，说明你一天的数据量是几十万的量级，这种走索引的效率已经比较差了。 我前面也说到，你登录或者说界面打开的查询就要涉及到几十万数据的查询，分区也解决不了你的问题，考虑到一下子显示几十万数据给前台也没什么意义，你这里少的是将这10几万SQL分页显示（分页得到稳定的较好性能的前提是已经按照天分区）
我们是B/S结构的，分页肯定是做的，不然几十万数据肯定不行的，浏览器一页几百条就够呛了，我先试试按天分区看[/quote] 如果你的其他条件多变且复杂，而且分页还有order by之类的东西，那么会需要更多的优化措施
zcs_zzh 2018-01-19
  • 打赏
  • 举报
 回复
使用索引在返回数量占表总记录3-5%效果才好，否则使用全表扫描更好。你这个查询要返回前一天所有的日志，几十万条，感觉没有什么意义，肉眼也看不过来，还是设计上有问题。
bosstwobread 2018-01-19
  • 打赏
  • 举报
 回复
引用 8 楼 minsic78 的回复:
几天百万，说明你一天的数据量是几十万的量级，这种走索引的效率已经比较差了。 我前面也说到，你登录或者说界面打开的查询就要涉及到几十万数据的查询，分区也解决不了你的问题，考虑到一下子显示几十万数据给前台也没什么意义，你这里少的是将这10几万SQL分页显示（分页得到稳定的较好性能的前提是已经按照天分区）
我们是B/S结构的，分页肯定是做的，不然几十万数据肯定不行的，浏览器一页几百条就够呛了，我先试试按天分区看
minsic78 2018-01-19
  • 打赏
  • 举报
 回复
几天百万，说明你一天的数据量是几十万的量级，这种走索引的效率已经比较差了。 我前面也说到，你登录或者说界面打开的查询就要涉及到几十万数据的查询，分区也解决不了你的问题，考虑到一下子显示几十万数据给前台也没什么意义，你这里少的是将这10几万SQL分页显示（分页得到稳定的较好性能的前提是已经按照天分区）
bosstwobread 2018-01-19
  • 打赏
  • 举报
 回复
引用 5 楼 minsic78 的回复:
[quote=引用 4 楼 zcs_zzh 的回复:] 解决性能的最终方案还是索引，根据查询条件创建合适的联合索引，另外可以考虑数据清理，如果1年或者N年前的数据没有价值了，可以备份到历史表中，然后删除掉，删除时用DROP分区的方式，不要DELETE。
索引能解决一切性能问题的话，IOT将是Oracle默认的建表类型了，而不是普通的堆表～[/quote] 历史数据清理，索引，按月分表我都做了，效果好像不明显，才几百万数据就这样，看来我们服务器配置很一般，我按官方的按天分区试试看，有效果再跟大家说
加载更多回复（6）
Oracle分区表(Partition Table)使用详解
本课程详细介绍了Oracle数据库的分区表机制、分区表应用场景、分区表与分区索引类型/创建/维护操作、分区裁剪等，结合大量的案例程序，对Oracle分区表的使用进行了详细的分析讲解。
分区表的误区：性能提升
很多同学会认为，分区表是把一张大表拆分成了多张小表，所以这样 MySQL 数据库的性能会有大幅提升。这是错误的认识！如果你寄希望于通过分区表提升性能，那么我不建议你使用分区，因为做不到。 分区表技术不是用于提升 MySQL 数据库的性能，而是方便数据的管理。 从表格中可以看到，B+ 树的高度为 4 能存放数十亿的数据，一次查询只需要占用 4 次 I/O，速度非常快。 但是当你使用分区之后，效果就不一样了，比如上面的表 t，我们根据时间拆成每年一张表，这时，虽然 B+ 树的高度从 4 降为了 3，但是这个提
MySQL分区表：优化大型数据库性能的关键
分区表是MySQL数据库中的一种高级功能，用于将表数据划分为多个分区，每个分区可以独立管理和操作。通过将表分割为多个分区，可以提高查询性能、简化数据维护和管理，并允许更高效地处理大量数据。分区表是由多个分区组成的逻辑表，每个分区存储特定范围或条件的数据。每个分区都可以单独管理和访问，就像独立的物理表一样。分区表具有相同的表结构，但数据在不同的分区中存储。MySQL分区表基于分区策略将数据划分为不同的分区。分区策略可以基于范围、列表、哈希或键值等条件进行定义。
数据库表分区真能加速数据库查询速度吗？
数据库表分区
大数据开发之Hive如何提高查询效率
今天分享一下Hive如何提升查询效率。Hive作为最大数据培训常用的数仓计算引擎，是我们必备的技能，但是很多人只是会写Hql，并不会优化，也不知道如何提升查询效率，今天分享8条军规： 1、开启FetchTask 一个简单的查询语句，是指一个没有函数、排序等功能的语句，当开启一个Fetch Task功能，就执行一个简单的查询语句不会生成MapRreduce作业，而是直接使用FetchTask，从hdfs文件系统中进行查询输出数据，从而提高效率。 设置的方式： Hive.fetch.task.c
基础和管理

17,377

社区成员

95,128

社区内容

发帖
与我相关
我的任务
社区描述
Oracle 基础和管理
社区管理员
  • 基础和管理社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章