求sql优化大神降临。

Question

sql索引数据优化优化sql

Answer 1

表结构发出来看一下，user_id是主键吗？

Answer 2

使用分析函数优化,提供两种写法，自行比较下执行效率吧
select u_id,status,pm_id from(
select u_id,status,pm_id,row_numer()over(partition by u_id order by pm_id desc) rn
from mass_user_mailbox
where mb.user_id = '100009'
)where rn=1select mb.u_id, 
max(status)keep(dense_rank last order by pm_id,rowid) status,
max(pm_id) pm_id
from mass_user_mailbox mb
where mb.user_id = '100009'
group by mb.u_id

Answer 3

我说咋看着这么别扭，row_number拼错了，少写了个字母

Answer 4

先谢谢了  sql方面我就了解一些平时用的， 你写的里面几个关键字我还真是第一次见..  我先看看   ， 其实我有个疑问：为什么他建的user_id的索引没起作用，是因为分组函数的关系吗？

Answer 5

c.u_id = d.u_id
   and c.pm_id = d.pm_id
你的条件是两个字段，单独用一个字段建立索引，对这样的查询起不到提速的作用可以考虑建立这两个字段的组合索引

Answer 6

但是 单对这段sql 
select mb.u_id, max(pm_id) pm_id
          from mass_user_mailbox mb
         where mb.user_id = '100009'
         group by mb.u_id
也就是里面的那层sql  我去查询的时候 f5 也是全表，user_id的索引是有的，不明白.............

Answer 7

应该user_id的筛选效果不太好吧
一般情况下使用索引的话，查询效率增加，但是代价是IO效率降低，
筛选效果不够好的话，查询效率的提升不足以弥补IO效率的降低，就会使用全表扫描
可以试试使用hint强制走索引，看看怎样耗时短一些

Answer 8

select b.*, u.nick_name, u.login_name, ua.logo
  from (select a.u_id,
               a.status,
               pm.message,
               pm.cdate,
               pm.id,
               pm.from_user,
               pm.to_user
          from (
                --替换你的start
                select mb.u_id,
                        max(status) keep(dense_rank last order by pm_id, rowid) status,
                        max(pm_id) pm_id
                  from mass_user_mailbox mb
                 where mb.user_id = '100009'
                 group by mb.u_id
                -- 替换你的end 
                ) a,
               mass_user_private_message pm
         where a.pm_id = pm.id
           and pm.message not like 'Hi，我是%'
        --      and trunc(pm.cdate) = to_date('time', 'yyyy-MM-dd')
         order by pm.cdate desc) b
  left join cdml_sensky_user u
    on b.u_id = u.id
  left join cdml_sensky_user_add ua
    on b.u_id = ua.id
 where 1 = 1;其实它总的sql是这样的，能帮瞅一眼嘛，之前因为数据大，查询直接崩了， 我没辙了，就让他只能查一天的数据，也就是注释的那，但还是很慢， 里面注释中的那块是引用你的，或者能说下优化的方向吗？  得学习这块了....

Answer 9

先谢谢了  sql方面我就了解一些平时用的， 你写的里面几个关键字我还真是第一次见..  我先看看   ， 其实我有个疑问：为什么他建的user_id的索引没起作用，是因为分组函数的关系吗？
select b.*, u.nick_name, u.login_name, ua.logo
  from (select a.u_id,
               a.status,
               pm.message,
               pm.cdate,
               pm.id,
               pm.from_user,
               pm.to_user
          from (
                --替换你的start
                select mb.u_id,
                        max(status) keep(dense_rank last order by pm_id, rowid) status,
                        max(pm_id) pm_id
                  from mass_user_mailbox mb
                 where mb.user_id = '100009'
                 group by mb.u_id
                -- 替换你的end 
                ) a,
               mass_user_private_message pm
         where a.pm_id = pm.id
           and pm.message not like 'Hi，我是%'
        --      and trunc(pm.cdate) = to_date('time', 'yyyy-MM-dd')
         order by pm.cdate desc) b
  left join cdml_sensky_user u
    on b.u_id = u.id
  left join cdml_sensky_user_add ua
    on b.u_id = ua.id
 where 1 = 1;其实它总的sql是这样的，能帮瞅一眼嘛，之前因为数据大，查询直接崩了， 我没辙了，就让他只能查一天的数据，也就是注释的那，但还是很慢， 里面注释中的那块是引用你的，或者能说下优化的方向吗？  得学习这块了....

Answer 10

select b.*, u.nick_name, u.login_name, ua.logo
  from (select a.u_id,
               a.status,
               pm.message,
               pm.cdate,
               pm.id,
               pm.from_user,
               pm.to_user
          from (
                --替换你的start
                select mb.u_id,
                        max(status) keep(dense_rank last order by pm_id, rowid) status,
                        max(pm_id) pm_id
                  from mass_user_mailbox mb
                 where mb.user_id = '100009'
                 group by mb.u_id
                -- 替换你的end 
                ) a,
               mass_user_private_message pm
         where a.pm_id = pm.id
           and pm.message not like 'Hi，我是%'
        --      and trunc(pm.cdate) = to_date('time', 'yyyy-MM-dd')
         order by pm.cdate desc) b
  left join cdml_sensky_user u
    on b.u_id = u.id
  left join cdml_sensky_user_add ua
    on b.u_id = ua.id
 where 1 = 1;其实它总的sql是这样的，能帮瞅一眼嘛，之前因为数据大，查询直接崩了， 我没辙了，就让他只能查一天的数据，也就是注释的那，但还是很慢， 里面注释中的那块是引用你的，或者能说下优化的方向吗？  得学习这块了....

Answer 11

1、内层查询的order by可以去掉
2、and trunc(pm.cdate) = to_date('time', 'yyyy-MM-dd')
这样通过函数转化后不会走cdate字段的索引的
可改成and pm.cdate>= to_date('time', 'yyyy-MM-dd') and pm.cdate< to_date('time', 'yyyy-MM-dd')+1
或是改成trunc(cdate)的函数索引
3、cdml_sensky_user 增加id的索引
4、cdml_sensky_user_add增加id的索引
5、not like很容易走全表扫描，根据业务看看有没有啥其他的方式过滤，或是看看不过滤对结果是否有影响
有时增加了条件以为数据量少了会快，但是如果因此不能走索引的话，有点得不偿失

Answer 12

强制使用索引后 果然效率更慢...    select mb.u_id,
                        max(status) keep(dense_rank last order by pm_id, rowid) status,
                        max(pm_id) pm_id
                  from mass_user_mailbox mb
                 where mb.user_id = '100009'
                 group by mb.u_id
这段还是达到了  Cost还是达到了15342， 你上面说的索引他们之前已经加上了， 这块还有没有空间在优化呢 ？

Answer 13

可以增加u_id、pm_id、status的联合索引试下

Answer 14

可以增加u_id、pm_id、status的联合索引试下加上走的还是全表 ， 不过我使用强制索引走我建的联合索引，发现走的还是全表，不知道是不是我建索引那出错了吗？

Answer 15

没有数据量，执行计划。单看语句，你可以试试建这两个索引，试试速度。
create index xxx01 on  mass_user_mailbox（user_id,u_id,pm_id）;
create index xxx02 on  mass_user_mailbox(u_id,pm_id,status);
速度决对有大提升，但以上不是最优解。

Answer 16

sql 语句：select b.*, u.nick_name, u.login_name, ua.logo
  from (select a.u_id,
               a.status,
               pm.message,
               pm.cdate,
               pm.id,
               pm.from_user,
               pm.to_user
          from (
                select mb.u_id,
                        max(status) keep(dense_rank last order by pm_id, rowid) status,
                        max(pm_id) pm_id
                  from mass_user_mailbox mb
                 where mb.user_id = '100009'
                 group by mb.u_id
               
               ) a,
               mass_user_private_message pm
         where a.pm_id = pm.id
           and pm.message not like 'Hi，我是%'
         order by pm.cdate desc) b
  left join cdml_sensky_user u
    on b.u_id = u.id
  left join cdml_sensky_user_add ua
    on b.u_id = ua.id
 where 1 = 1;执行计划：
数据量：1440多万索引
mass_user_mailbox：  1.pm_id    2.user_id.
mass_user_private_message   1. 主键id

Answer 17

sql 语句：select b.*, u.nick_name, u.login_name, ua.logo
  from (select a.u_id,
               a.status,
               pm.message,
               pm.cdate,
               pm.id,
               pm.from_user,
               pm.to_user
          from (
                select mb.u_id,
                        max(status) keep(dense_rank last order by pm_id, rowid) status,
                        max(pm_id) pm_id
                  from mass_user_mailbox mb
                 where mb.user_id = '100009'
                 group by mb.u_id
               
               ) a,
               mass_user_private_message pm
         where a.pm_id = pm.id
           and pm.message not like 'Hi，我是%'
         order by pm.cdate desc) b
  left join cdml_sensky_user u
    on b.u_id = u.id
  left join cdml_sensky_user_add ua
    on b.u_id = ua.id
 where 1 = 1;执行计划：
数据量：1440多万索引
mass_user_mailbox：  1.pm_id    2.user_id.
mass_user_private_message   1. 主键id
不知道你说的1440万是哪个表
你的语句等价于 select mb.u_id,
        mb.status,
        pm.message,
        pm.cdate,
        pm.id,
        pm.from_user,
        pm.to_user,
        u.nick_name,
        u.login_name,
        ua.logo
   from mass_user_mailbox         mb,
        mass_user_private_message pm,
        cdml_sensky_user          u,
        cdml_sensky_user_add      ua
  where mb.pm_id = pm.id
    and mb.u_id = u.id(+)
    and mb.u_id = ua.id(+)
    and pm.message not like 'Hi，我是%'
    and mb.user_id = '100009'
    and not exists (select 1
           from mass_user_mailbox x
          where x.u_id = mb.u_id
            and x.user_id = mb.user_id
            and x.pm_id > mb.pm_id)
order by pm.cdate desc
不知道user_id, pm_id的选择性如何
改成这种形式，然后创建一个组合索引：(user_id,u_id,pm_id)
看看效果

Answer 18

sql 语句：select b.*, u.nick_name, u.login_name, ua.logo
  from (select a.u_id,
               a.status,
               pm.message,
               pm.cdate,
               pm.id,
               pm.from_user,
               pm.to_user
          from (
                select mb.u_id,
                        max(status) keep(dense_rank last order by pm_id, rowid) status,
                        max(pm_id) pm_id
                  from mass_user_mailbox mb
                 where mb.user_id = '100009'
                 group by mb.u_id
               
               ) a,
               mass_user_private_message pm
         where a.pm_id = pm.id
           and pm.message not like 'Hi，我是%'
         order by pm.cdate desc) b
  left join cdml_sensky_user u
    on b.u_id = u.id
  left join cdml_sensky_user_add ua
    on b.u_id = ua.id
 where 1 = 1;执行计划：
数据量：1440多万索引
mass_user_mailbox：  1.pm_id    2.user_id.
mass_user_private_message   1. 主键id
不知道你说的1440万是哪个表
你的语句等价于 select mb.u_id,
        mb.status,
        pm.message,
        pm.cdate,
        pm.id,
        pm.from_user,
        pm.to_user,
        u.nick_name,
        u.login_name,
        ua.logo
   from mass_user_mailbox         mb,
        mass_user_private_message pm,
        cdml_sensky_user          u,
        cdml_sensky_user_add      ua
  where mb.pm_id = pm.id
    and mb.u_id = u.id(+)
    and mb.u_id = ua.id(+)
    and pm.message not like 'Hi，我是%'
    and mb.user_id = '100009'
    and not exists (select 1
           from mass_user_mailbox x
          where x.u_id = mb.u_id
            and x.user_id = mb.user_id
            and x.pm_id > mb.pm_id)
order by pm.cdate desc
不知道user_id, pm_id的选择性如何
改成这种形式，然后创建一个组合索引：(user_id,u_id,pm_id)
看看效果
建完联合索引，速度果然可观了 ， 巨感谢啊。  待我慢慢研究这段sql....