当前位置： 首页 > news >正文

微商分销系统开发/奉化云优化seo

news 文章来源：https://blog.csdn.net/robinson1988/article/details/129426002 2025/3/19 21:26:55
微商分销系统开发,奉化云优化seo,网站开发公司网站模板,如何为公司做网站最近某人社局核心数据库上了OB，经常出现性能问题 某人社与我司合作多年，非常信任我司在数据库的专业能力，邀请我司过去看看能否提供帮助 与OB驻场工程师合作，抓取了一天的TOP SQL，跑得慢的SQL有几十条(注意只是某一天的…

最近某人社局核心数据库上了OB，经常出现性能问题
某人社与我司合作多年，非常信任我司在数据库的专业能力，邀请我司过去看看能否提供帮助
与OB驻场工程师合作，抓取了一天的TOP SQL，跑得慢的SQL有几十条(注意只是某一天的TOP SQL)
大致分析了一下，有缺索引的，有执行计划走错的，有SQL写法有问题的，有字段类型设计错误的，也有表分区策略设计有问题的
这些问题在我看来都很简单就不贴在博客了，下面我要分享一个对于OB SQL优化很有启发意义的案例

下面SQL每天要运行20w次，平均每次执行0.5s到2秒，它耗费了整个OB集群20%的CPU资源
也许有人会说，不就才20w次吗，我见过运行几百万次，上千万次的SQL
这里我要说的是，请不要拿国产数据库与Oracle对比，Oracle发展了40年了，国产数据库才发展多少年，能替换O已经很厉害了
每天运行20w次的时段大致在早上9:30分到11:30分以及下午2：30到5:00，也就是工作日业务办理时间

SQL代码大致如下(只贴一条，还有很多类似SQL就不贴了)：

SELECT * FROM AC08  WHERE (AAC001 = ? AND AAE140= ? AND AAE792 IN (?,?) AND NOT EXISTS (SELECT ? FROM AC08 B WHERE AC08.AAZ686 = B.AAZ686 AND B.AAE792 IN (?)))

AC08是个超级大表，它有26亿条数据，根据AAC001 进行的HASH分区，执行计划如下：

=======================================================================
|ID|OPERATOR                 |NAME                     |EST. ROWS|COST|
-----------------------------------------------------------------------
|0 |NESTED-LOOP ANTI JOIN    |                         |0        |337 |
|1 | PX COORDINATOR          |                         |1        |302 |
|2 |  EXCHANGE OUT DISTR     |:EX10000                 |1        |294 |
|3 |   TABLE SCAN            |AC08(IDX_AC08EES_AAC001) |1        |294 |
|4 | PX COORDINATOR          |                         |1        |46  |
|5 |  EXCHANGE OUT DISTR     |:EX20000                 |1        |46  |
|6 |   SUBPLAN SCAN          |VIEW1                    |1        |46  |
|7 |    PX PARTITION ITERATOR|                         |1        |46  |
|8 |     TABLE SCAN          |B(IDX_AC08_AAZ686_AAE792)|1        |46  |
=======================================================================Outputs & filters: 
-------------------------------------0 - output([AC08.AAZ648], [AC08.AAZ223], [AC08.AAZ686], [AC08.AAC001], [AC08.AAZ159], [AC08.AAZ061], [AC08.AAB001], [AC08.AAB365], [AC08.AAE140], [AC08.AAE002], [AC08.AAE003], [AC08.AAA115], [AC08.AAE793], [AC08.AAE794], [AC08.AAA093], [AC08.AAC066], [AC08.AAC313], [AC08.AAC314], [AC08.AAE737], [AC08.AAB019], [AC08.AAB033], [AC08.AAE745], [AC08.AAC028], [AC08.AAC040], [AC08.AAB121], [AC08.AAE180], [AC08.AAE795], [AC08.AAE871], [AC08.AAE020], [AC08.AAE021], [AC08.AAE022], [AC08.AAE023], [AC08.AAE784], [AC08.AAE785], [AC08.AAE786], [AC08.AAE787], [AC08.AAE836], [AC08.AAE837], [AC08.AAE838], [AC08.AAE839], [AC08.AAE056], [AC08.AAE887], [AC08.AAE026], [AC08.AAE828], [AC08.AIC380], [AC08.AAE834], [AC08.AAE845], [AC08.AAE844], [AC08.AAB191], [AC08.AAE080], [AC08.AAE081], [AC08.AAE082], [AC08.AAE083], [AC08.AAE788], [AC08.AAE789], [AC08.AAE790], [AC08.AAE791], [AC08.AAE840], [AC08.AAE841], [AC08.AAE842], [AC08.AAE843], [AC08.AAE057], [AC08.AAE888], [AC08.AAE086], [AC08.AAE741], [AC08.AAE202], [AC08.AAA042], [AC08.AAA043], [AC08.AAA041], [AC08.AAA045], [AC08.AAZ616], [AC08.AAZ615], [AC08.AAE061], [AC08.AAE108], [AC08.AAB301], [AC08.AAB299], [AC08.AAE729], [AC08.AAE819], [AC08.AAE820], [AC08.AAE796], [AC08.AAE797], [AC08.AAE798], [AC08.AAE799], [AC08.AAE800], [AC08.AAZ625], [AC08.AAE792], [AC08.AAE150], [AC08.AAE066], [AC08.AAE748], [AC08.AAE822], [AC08.AAZ631], [AC08.AAZ650], [AC08.AAC323], [AC08.AAE013], [AC08.AAZ649], [AC08.AAE860], [AC08.AAE859], [AC08.AAE011], [AC08.AAZ692], [AC08.AAE036], [AC08.AAB034], [AC08.AAB360], [AC08.AAB359], [AC08.AAF018], [AC08.AAA431], [AC08.AAZ673], [AC08.AAA027], [AC08.AAA508], [AC08.AAA350]), filter(nil), conds(nil), nl_params_([AC08.AAZ686])1 - output([AC08.AAC001], [AC08.AAE140], [AC08.AAE792], [AC08.AAZ686], [AC08.AAZ648], [AC08.AAZ223], [AC08.AAZ159], [AC08.AAZ061], [AC08.AAB001], [AC08.AAB365], [AC08.AAE002], [AC08.AAE003], [AC08.AAA115], [AC08.AAE793], [AC08.AAE794], [AC08.AAA093], [AC08.AAC066], [AC08.AAC313], [AC08.AAC314], [AC08.AAE737], [AC08.AAB019], [AC08.AAB033], [AC08.AAE745], [AC08.AAC028], [AC08.AAC040], [AC08.AAB121], [AC08.AAE180], [AC08.AAE795], [AC08.AAE871], [AC08.AAE020], [AC08.AAE021], [AC08.AAE022], [AC08.AAE023], [AC08.AAE784], [AC08.AAE785], [AC08.AAE786], [AC08.AAE787], [AC08.AAE836], [AC08.AAE837], [AC08.AAE838], [AC08.AAE839], [AC08.AAE056], [AC08.AAE887], [AC08.AAE026], [AC08.AAE828], [AC08.AIC380], [AC08.AAE834], [AC08.AAE845], [AC08.AAE844], [AC08.AAB191], [AC08.AAE080], [AC08.AAE081], [AC08.AAE082], [AC08.AAE083], [AC08.AAE788], [AC08.AAE789], [AC08.AAE790], [AC08.AAE791], [AC08.AAE840], [AC08.AAE841], [AC08.AAE842], [AC08.AAE843], [AC08.AAE057], [AC08.AAE888], [AC08.AAE086], [AC08.AAE741], [AC08.AAE202], [AC08.AAA042], [AC08.AAA043], [AC08.AAA041], [AC08.AAA045], [AC08.AAZ616], [AC08.AAZ615], [AC08.AAE061], [AC08.AAE108], [AC08.AAB301], [AC08.AAB299], [AC08.AAE729], [AC08.AAE819], [AC08.AAE820], [AC08.AAE796], [AC08.AAE797], [AC08.AAE798], [AC08.AAE799], [AC08.AAE800], [AC08.AAZ625], [AC08.AAE150], [AC08.AAE066], [AC08.AAE748], [AC08.AAE822], [AC08.AAZ631], [AC08.AAZ650], [AC08.AAC323], [AC08.AAE013], [AC08.AAZ649], [AC08.AAE860], [AC08.AAE859], [AC08.AAE011], [AC08.AAZ692], [AC08.AAE036], [AC08.AAB034], [AC08.AAB360], [AC08.AAB359], [AC08.AAF018], [AC08.AAA431], [AC08.AAZ673], [AC08.AAA027], [AC08.AAA508], [AC08.AAA350]), filter(nil)2 - output([AC08.AAC001], [AC08.AAE140], [AC08.AAE792], [AC08.AAZ686], [AC08.AAZ648], [AC08.AAZ223], [AC08.AAZ159], [AC08.AAZ061], [AC08.AAB001], [AC08.AAB365], [AC08.AAE002], [AC08.AAE003], [AC08.AAA115], [AC08.AAE793], [AC08.AAE794], [AC08.AAA093], [AC08.AAC066], [AC08.AAC313], [AC08.AAC314], [AC08.AAE737], [AC08.AAB019], [AC08.AAB033], [AC08.AAE745], [AC08.AAC028], [AC08.AAC040], [AC08.AAB121], [AC08.AAE180], [AC08.AAE795], [AC08.AAE871], [AC08.AAE020], [AC08.AAE021], [AC08.AAE022], [AC08.AAE023], [AC08.AAE784], [AC08.AAE785], [AC08.AAE786], [AC08.AAE787], [AC08.AAE836], [AC08.AAE837], [AC08.AAE838], [AC08.AAE839], [AC08.AAE056], [AC08.AAE887], [AC08.AAE026], [AC08.AAE828], [AC08.AIC380], [AC08.AAE834], [AC08.AAE845], [AC08.AAE844], [AC08.AAB191], [AC08.AAE080], [AC08.AAE081], [AC08.AAE082], [AC08.AAE083], [AC08.AAE788], [AC08.AAE789], [AC08.AAE790], [AC08.AAE791], [AC08.AAE840], [AC08.AAE841], [AC08.AAE842], [AC08.AAE843], [AC08.AAE057], [AC08.AAE888], [AC08.AAE086], [AC08.AAE741], [AC08.AAE202], [AC08.AAA042], [AC08.AAA043], [AC08.AAA041], [AC08.AAA045], [AC08.AAZ616], [AC08.AAZ615], [AC08.AAE061], [AC08.AAE108], [AC08.AAB301], [AC08.AAB299], [AC08.AAE729], [AC08.AAE819], [AC08.AAE820], [AC08.AAE796], [AC08.AAE797], [AC08.AAE798], [AC08.AAE799], [AC08.AAE800], [AC08.AAZ625], [AC08.AAE150], [AC08.AAE066], [AC08.AAE748], [AC08.AAE822], [AC08.AAZ631], [AC08.AAZ650], [AC08.AAC323], [AC08.AAE013], [AC08.AAZ649], [AC08.AAE860], [AC08.AAE859], [AC08.AAE011], [AC08.AAZ692], [AC08.AAE036], [AC08.AAB034], [AC08.AAB360], [AC08.AAB359], [AC08.AAF018], [AC08.AAA431], [AC08.AAZ673], [AC08.AAA027], [AC08.AAA508], [AC08.AAA350]), filter(nil), is_single, dop=13 - output([AC08.AAC001], [AC08.AAE140], [AC08.AAE792], [AC08.AAZ686], [AC08.AAZ648], [AC08.AAZ223], [AC08.AAZ159], [AC08.AAZ061], [AC08.AAB001], [AC08.AAB365], [AC08.AAE002], [AC08.AAE003], [AC08.AAA115], [AC08.AAE793], [AC08.AAE794], [AC08.AAA093], [AC08.AAC066], [AC08.AAC313], [AC08.AAC314], [AC08.AAE737], [AC08.AAB019], [AC08.AAB033], [AC08.AAE745], [AC08.AAC028], [AC08.AAC040], [AC08.AAB121], [AC08.AAE180], [AC08.AAE795], [AC08.AAE871], [AC08.AAE020], [AC08.AAE021], [AC08.AAE022], [AC08.AAE023], [AC08.AAE784], [AC08.AAE785], [AC08.AAE786], [AC08.AAE787], [AC08.AAE836], [AC08.AAE837], [AC08.AAE838], [AC08.AAE839], [AC08.AAE056], [AC08.AAE887], [AC08.AAE026], [AC08.AAE828], [AC08.AIC380], [AC08.AAE834], [AC08.AAE845], [AC08.AAE844], [AC08.AAB191], [AC08.AAE080], [AC08.AAE081], [AC08.AAE082], [AC08.AAE083], [AC08.AAE788], [AC08.AAE789], [AC08.AAE790], [AC08.AAE791], [AC08.AAE840], [AC08.AAE841], [AC08.AAE842], [AC08.AAE843], [AC08.AAE057], [AC08.AAE888], [AC08.AAE086], [AC08.AAE741], [AC08.AAE202], [AC08.AAA042], [AC08.AAA043], [AC08.AAA041], [AC08.AAA045], [AC08.AAZ616], [AC08.AAZ615], [AC08.AAE061], [AC08.AAE108], [AC08.AAB301], [AC08.AAB299], [AC08.AAE729], [AC08.AAE819], [AC08.AAE820], [AC08.AAE796], [AC08.AAE797], [AC08.AAE798], [AC08.AAE799], [AC08.AAE800], [AC08.AAZ625], [AC08.AAE150], [AC08.AAE066], [AC08.AAE748], [AC08.AAE822], [AC08.AAZ631], [AC08.AAZ650], [AC08.AAC323], [AC08.AAE013], [AC08.AAZ649], [AC08.AAE860], [AC08.AAE859], [AC08.AAE011], [AC08.AAZ692], [AC08.AAE036], [AC08.AAB034], [AC08.AAB360], [AC08.AAB359], [AC08.AAF018], [AC08.AAA431], [AC08.AAZ673], [AC08.AAA027], [AC08.AAA508], [AC08.AAA350]), filter([cast(cast(AC08.AAE140, VARCHAR2(3 BYTE)), NUMBER(-1, -85)) = 120], [AC08.AAE792 IN (?, ?)]), access([AC08.AAC001], [AC08.AAE140], [AC08.AAE792], [AC08.AAZ686], [AC08.AAZ648], [AC08.AAZ223], [AC08.AAZ159], [AC08.AAZ061], [AC08.AAB001], [AC08.AAB365], [AC08.AAE002], [AC08.AAE003], [AC08.AAA115], [AC08.AAE793], [AC08.AAE794], [AC08.AAA093], [AC08.AAC066], [AC08.AAC313], [AC08.AAC314], [AC08.AAE737], [AC08.AAB019], [AC08.AAB033], [AC08.AAE745], [AC08.AAC028], [AC08.AAC040], [AC08.AAB121], [AC08.AAE180], [AC08.AAE795], [AC08.AAE871], [AC08.AAE020], [AC08.AAE021], [AC08.AAE022], [AC08.AAE023], [AC08.AAE784], [AC08.AAE785], [AC08.AAE786], [AC08.AAE787], [AC08.AAE836], [AC08.AAE837], [AC08.AAE838], [AC08.AAE839], [AC08.AAE056], [AC08.AAE887], [AC08.AAE026], [AC08.AAE828], [AC08.AIC380], [AC08.AAE834], [AC08.AAE845], [AC08.AAE844], [AC08.AAB191], [AC08.AAE080], [AC08.AAE081], [AC08.AAE082], [AC08.AAE083], [AC08.AAE788], [AC08.AAE789], [AC08.AAE790], [AC08.AAE791], [AC08.AAE840], [AC08.AAE841], [AC08.AAE842], [AC08.AAE843], [AC08.AAE057], [AC08.AAE888], [AC08.AAE086], [AC08.AAE741], [AC08.AAE202], [AC08.AAA042], [AC08.AAA043], [AC08.AAA041], [AC08.AAA045], [AC08.AAZ616], [AC08.AAZ615], [AC08.AAE061], [AC08.AAE108], [AC08.AAB301], [AC08.AAB299], [AC08.AAE729], [AC08.AAE819], [AC08.AAE820], [AC08.AAE796], [AC08.AAE797], [AC08.AAE798], [AC08.AAE799], [AC08.AAE800], [AC08.AAZ625], [AC08.AAE150], [AC08.AAE066], [AC08.AAE748], [AC08.AAE822], [AC08.AAZ631], [AC08.AAZ650], [AC08.AAC323], [AC08.AAE013], [AC08.AAZ649], [AC08.AAE860], [AC08.AAE859], [AC08.AAE011], [AC08.AAZ692], [AC08.AAE036], [AC08.AAB034], [AC08.AAB360], [AC08.AAB359], [AC08.AAF018], [AC08.AAA431], [AC08.AAZ673], [AC08.AAA027], [AC08.AAA508], [AC08.AAA350]), partitions(p0)4 - output([1]), filter(nil)5 - output([1]), filter(nil), dop=16 - output([1]), filter(nil), access([VIEW1.B.AAZ686])7 - output([B.AAZ686]), filter(nil)8 - output([B.AAZ686]), filter(nil), access([B.AAZ686]), partitions(p[0-127])

从执行计划上看，上面的SQL没有可以优化的地方了，访问路径是对的，表关联方式也是对的
对于一般的DBA来说，可能放弃治疗了
注意观察执行计划，ID = 8 access([B.AAZ686]), partitions(p[0-127]) 访问了所有的分区
OB是分布式数据库，会根据分区将数据打散到所有的数据节点
每次跑这个SQL都会访问所有的数据节点，如果并发较高，就会对整个OB带来压力
执行计划上没有优化的地方，那就从SQL写法和业务逻辑入手
喵了一眼SQL写法，也没问题，现在只能从业务逻辑入手了，查一下表和列的注释

AC08   养老保险人员实收明细表
AAC001 人员编号，分区KEY
AAE140 险种类型
AAE792 费用标志  
AAZ686 人员缴费ID

看到这里就知道怎么优化了，原始SQL语句中 NOT EXISTS 部分少加了个关联条件，我们再来看一下原始SQL：

SELECT * FROM AC08  WHERE (AAC001 = ? AND AAE140= ? AND AAE792 IN (?,?) AND NOT EXISTS (SELECT ? FROM AC08 B WHERE AC08.AAZ686 = B.AAZ686 AND B.AAE792 IN (?)))

应该把SQL改成

SELECT * FROM AC08  WHERE (AAC001 = ? AND AAE140= ? AND AAE792 IN (?,?) AND NOT EXISTS (SELECT ? FROM AC08 B WHERE AC08.AAZ686 = B.AAZ686 AND AC08.AAC001=B.AAC001 AND B.AAE792 IN (?)))

因为人员缴费ID(AAZ686) 一般是与人员编号(AAC001) 一一对应的
加上AC08.AAC001=B.AAC001过滤条件之后，就能避免OB跨数据节点访问了
更改后的执行计划如下：

=====================================================================
|ID|OPERATOR               |NAME                     |EST. ROWS|COST|
---------------------------------------------------------------------
|0 |EXCHANGE IN REMOTE     |                         |1        |337 |
|1 | EXCHANGE OUT REMOTE   |                         |1        |330 |
|2 |  NESTED-LOOP ANTI JOIN|                         |1        |330 |
|3 |   TABLE SCAN          |AC08(IDX_AC08EES_AAC001) |1        |294 |
|4 |   SUBPLAN SCAN        |VIEW1                    |1        |46  |
|5 |    TABLE SCAN         |B(IDX_AC08_AAZ686_AAE792)|1        |46  |
=====================================================================Outputs & filters: 
-------------------------------------0 - output([AC08.AAZ648], [AC08.AAZ223], [AC08.AAZ686], [AC08.AAC001], [AC08.AAZ159], [AC08.AAZ061], [AC08.AAB001], [AC08.AAB365], [AC08.AAE140], [AC08.AAE002], [AC08.AAE003], [AC08.AAA115], [AC08.AAE793], [AC08.AAE794], [AC08.AAA093], [AC08.AAC066], [AC08.AAC313], [AC08.AAC314], [AC08.AAE737], [AC08.AAB019], [AC08.AAB033], [AC08.AAE745], [AC08.AAC028], [AC08.AAC040], [AC08.AAB121], [AC08.AAE180], [AC08.AAE795], [AC08.AAE871], [AC08.AAE020], [AC08.AAE021], [AC08.AAE022], [AC08.AAE023], [AC08.AAE784], [AC08.AAE785], [AC08.AAE786], [AC08.AAE787], [AC08.AAE836], [AC08.AAE837], [AC08.AAE838], [AC08.AAE839], [AC08.AAE056], [AC08.AAE887], [AC08.AAE026], [AC08.AAE828], [AC08.AIC380], [AC08.AAE834], [AC08.AAE845], [AC08.AAE844], [AC08.AAB191], [AC08.AAE080], [AC08.AAE081], [AC08.AAE082], [AC08.AAE083], [AC08.AAE788], [AC08.AAE789], [AC08.AAE790], [AC08.AAE791], [AC08.AAE840], [AC08.AAE841], [AC08.AAE842], [AC08.AAE843], [AC08.AAE057], [AC08.AAE888], [AC08.AAE086], [AC08.AAE741], [AC08.AAE202], [AC08.AAA042], [AC08.AAA043], [AC08.AAA041], [AC08.AAA045], [AC08.AAZ616], [AC08.AAZ615], [AC08.AAE061], [AC08.AAE108], [AC08.AAB301], [AC08.AAB299], [AC08.AAE729], [AC08.AAE819], [AC08.AAE820], [AC08.AAE796], [AC08.AAE797], [AC08.AAE798], [AC08.AAE799], [AC08.AAE800], [AC08.AAZ625], [AC08.AAE792], [AC08.AAE150], [AC08.AAE066], [AC08.AAE748], [AC08.AAE822], [AC08.AAZ631], [AC08.AAZ650], [AC08.AAC323], [AC08.AAE013], [AC08.AAZ649], [AC08.AAE860], [AC08.AAE859], [AC08.AAE011], [AC08.AAZ692], [AC08.AAE036], [AC08.AAB034], [AC08.AAB360], [AC08.AAB359], [AC08.AAF018], [AC08.AAA431], [AC08.AAZ673], [AC08.AAA027], [AC08.AAA508], [AC08.AAA350]), filter(nil)1 - output([AC08.AAZ648], [AC08.AAZ223], [AC08.AAZ686], [AC08.AAC001], [AC08.AAZ159], [AC08.AAZ061], [AC08.AAB001], [AC08.AAB365], [AC08.AAE140], [AC08.AAE002], [AC08.AAE003], [AC08.AAA115], [AC08.AAE793], [AC08.AAE794], [AC08.AAA093], [AC08.AAC066], [AC08.AAC313], [AC08.AAC314], [AC08.AAE737], [AC08.AAB019], [AC08.AAB033], [AC08.AAE745], [AC08.AAC028], [AC08.AAC040], [AC08.AAB121], [AC08.AAE180], [AC08.AAE795], [AC08.AAE871], [AC08.AAE020], [AC08.AAE021], [AC08.AAE022], [AC08.AAE023], [AC08.AAE784], [AC08.AAE785], [AC08.AAE786], [AC08.AAE787], [AC08.AAE836], [AC08.AAE837], [AC08.AAE838], [AC08.AAE839], [AC08.AAE056], [AC08.AAE887], [AC08.AAE026], [AC08.AAE828], [AC08.AIC380], [AC08.AAE834], [AC08.AAE845], [AC08.AAE844], [AC08.AAB191], [AC08.AAE080], [AC08.AAE081], [AC08.AAE082], [AC08.AAE083], [AC08.AAE788], [AC08.AAE789], [AC08.AAE790], [AC08.AAE791], [AC08.AAE840], [AC08.AAE841], [AC08.AAE842], [AC08.AAE843], [AC08.AAE057], [AC08.AAE888], [AC08.AAE086], [AC08.AAE741], [AC08.AAE202], [AC08.AAA042], [AC08.AAA043], [AC08.AAA041], [AC08.AAA045], [AC08.AAZ616], [AC08.AAZ615], [AC08.AAE061], [AC08.AAE108], [AC08.AAB301], [AC08.AAB299], [AC08.AAE729], [AC08.AAE819], [AC08.AAE820], [AC08.AAE796], [AC08.AAE797], [AC08.AAE798], [AC08.AAE799], [AC08.AAE800], [AC08.AAZ625], [AC08.AAE792], [AC08.AAE150], [AC08.AAE066], [AC08.AAE748], [AC08.AAE822], [AC08.AAZ631], [AC08.AAZ650], [AC08.AAC323], [AC08.AAE013], [AC08.AAZ649], [AC08.AAE860], [AC08.AAE859], [AC08.AAE011], [AC08.AAZ692], [AC08.AAE036], [AC08.AAB034], [AC08.AAB360], [AC08.AAB359], [AC08.AAF018], [AC08.AAA431], [AC08.AAZ673], [AC08.AAA027], [AC08.AAA508], [AC08.AAA350]), filter(nil)2 - output([AC08.AAZ648], [AC08.AAZ223], [AC08.AAZ686], [AC08.AAC001], [AC08.AAZ159], [AC08.AAZ061], [AC08.AAB001], [AC08.AAB365], [AC08.AAE140], [AC08.AAE002], [AC08.AAE003], [AC08.AAA115], [AC08.AAE793], [AC08.AAE794], [AC08.AAA093], [AC08.AAC066], [AC08.AAC313], [AC08.AAC314], [AC08.AAE737], [AC08.AAB019], [AC08.AAB033], [AC08.AAE745], [AC08.AAC028], [AC08.AAC040], [AC08.AAB121], [AC08.AAE180], [AC08.AAE795], [AC08.AAE871], [AC08.AAE020], [AC08.AAE021], [AC08.AAE022], [AC08.AAE023], [AC08.AAE784], [AC08.AAE785], [AC08.AAE786], [AC08.AAE787], [AC08.AAE836], [AC08.AAE837], [AC08.AAE838], [AC08.AAE839], [AC08.AAE056], [AC08.AAE887], [AC08.AAE026], [AC08.AAE828], [AC08.AIC380], [AC08.AAE834], [AC08.AAE845], [AC08.AAE844], [AC08.AAB191], [AC08.AAE080], [AC08.AAE081], [AC08.AAE082], [AC08.AAE083], [AC08.AAE788], [AC08.AAE789], [AC08.AAE790], [AC08.AAE791], [AC08.AAE840], [AC08.AAE841], [AC08.AAE842], [AC08.AAE843], [AC08.AAE057], [AC08.AAE888], [AC08.AAE086], [AC08.AAE741], [AC08.AAE202], [AC08.AAA042], [AC08.AAA043], [AC08.AAA041], [AC08.AAA045], [AC08.AAZ616], [AC08.AAZ615], [AC08.AAE061], [AC08.AAE108], [AC08.AAB301], [AC08.AAB299], [AC08.AAE729], [AC08.AAE819], [AC08.AAE820], [AC08.AAE796], [AC08.AAE797], [AC08.AAE798], [AC08.AAE799], [AC08.AAE800], [AC08.AAZ625], [AC08.AAE792], [AC08.AAE150], [AC08.AAE066], [AC08.AAE748], [AC08.AAE822], [AC08.AAZ631], [AC08.AAZ650], [AC08.AAC323], [AC08.AAE013], [AC08.AAZ649], [AC08.AAE860], [AC08.AAE859], [AC08.AAE011], [AC08.AAZ692], [AC08.AAE036], [AC08.AAB034], [AC08.AAB360], [AC08.AAB359], [AC08.AAF018], [AC08.AAA431], [AC08.AAZ673], [AC08.AAA027], [AC08.AAA508], [AC08.AAA350]), filter(nil), conds(nil), nl_params_([AC08.AAZ686])3 - output([AC08.AAC001], [AC08.AAE140], [AC08.AAE792], [AC08.AAZ686], [AC08.AAZ648], [AC08.AAZ223], [AC08.AAZ159], [AC08.AAZ061], [AC08.AAB001], [AC08.AAB365], [AC08.AAE002], [AC08.AAE003], [AC08.AAA115], [AC08.AAE793], [AC08.AAE794], [AC08.AAA093], [AC08.AAC066], [AC08.AAC313], [AC08.AAC314], [AC08.AAE737], [AC08.AAB019], [AC08.AAB033], [AC08.AAE745], [AC08.AAC028], [AC08.AAC040], [AC08.AAB121], [AC08.AAE180], [AC08.AAE795], [AC08.AAE871], [AC08.AAE020], [AC08.AAE021], [AC08.AAE022], [AC08.AAE023], [AC08.AAE784], [AC08.AAE785], [AC08.AAE786], [AC08.AAE787], [AC08.AAE836], [AC08.AAE837], [AC08.AAE838], [AC08.AAE839], [AC08.AAE056], [AC08.AAE887], [AC08.AAE026], [AC08.AAE828], [AC08.AIC380], [AC08.AAE834], [AC08.AAE845], [AC08.AAE844], [AC08.AAB191], [AC08.AAE080], [AC08.AAE081], [AC08.AAE082], [AC08.AAE083], [AC08.AAE788], [AC08.AAE789], [AC08.AAE790], [AC08.AAE791], [AC08.AAE840], [AC08.AAE841], [AC08.AAE842], [AC08.AAE843], [AC08.AAE057], [AC08.AAE888], [AC08.AAE086], [AC08.AAE741], [AC08.AAE202], [AC08.AAA042], [AC08.AAA043], [AC08.AAA041], [AC08.AAA045], [AC08.AAZ616], [AC08.AAZ615], [AC08.AAE061], [AC08.AAE108], [AC08.AAB301], [AC08.AAB299], [AC08.AAE729], [AC08.AAE819], [AC08.AAE820], [AC08.AAE796], [AC08.AAE797], [AC08.AAE798], [AC08.AAE799], [AC08.AAE800], [AC08.AAZ625], [AC08.AAE150], [AC08.AAE066], [AC08.AAE748], [AC08.AAE822], [AC08.AAZ631], [AC08.AAZ650], [AC08.AAC323], [AC08.AAE013], [AC08.AAZ649], [AC08.AAE860], [AC08.AAE859], [AC08.AAE011], [AC08.AAZ692], [AC08.AAE036], [AC08.AAB034], [AC08.AAB360], [AC08.AAB359], [AC08.AAF018], [AC08.AAA431], [AC08.AAZ673], [AC08.AAA027], [AC08.AAA508], [AC08.AAA350]), filter([cast(cast(AC08.AAE140, VARCHAR2(3 BYTE)), NUMBER(-1, -85)) = 120], [AC08.AAE792 IN (?, ?)]), access([AC08.AAC001], [AC08.AAE140], [AC08.AAE792], [AC08.AAZ686], [AC08.AAZ648], [AC08.AAZ223], [AC08.AAZ159], [AC08.AAZ061], [AC08.AAB001], [AC08.AAB365], [AC08.AAE002], [AC08.AAE003], [AC08.AAA115], [AC08.AAE793], [AC08.AAE794], [AC08.AAA093], [AC08.AAC066], [AC08.AAC313], [AC08.AAC314], [AC08.AAE737], [AC08.AAB019], [AC08.AAB033], [AC08.AAE745], [AC08.AAC028], [AC08.AAC040], [AC08.AAB121], [AC08.AAE180], [AC08.AAE795], [AC08.AAE871], [AC08.AAE020], [AC08.AAE021], [AC08.AAE022], [AC08.AAE023], [AC08.AAE784], [AC08.AAE785], [AC08.AAE786], [AC08.AAE787], [AC08.AAE836], [AC08.AAE837], [AC08.AAE838], [AC08.AAE839], [AC08.AAE056], [AC08.AAE887], [AC08.AAE026], [AC08.AAE828], [AC08.AIC380], [AC08.AAE834], [AC08.AAE845], [AC08.AAE844], [AC08.AAB191], [AC08.AAE080], [AC08.AAE081], [AC08.AAE082], [AC08.AAE083], [AC08.AAE788], [AC08.AAE789], [AC08.AAE790], [AC08.AAE791], [AC08.AAE840], [AC08.AAE841], [AC08.AAE842], [AC08.AAE843], [AC08.AAE057], [AC08.AAE888], [AC08.AAE086], [AC08.AAE741], [AC08.AAE202], [AC08.AAA042], [AC08.AAA043], [AC08.AAA041], [AC08.AAA045], [AC08.AAZ616], [AC08.AAZ615], [AC08.AAE061], [AC08.AAE108], [AC08.AAB301], [AC08.AAB299], [AC08.AAE729], [AC08.AAE819], [AC08.AAE820], [AC08.AAE796], [AC08.AAE797], [AC08.AAE798], [AC08.AAE799], [AC08.AAE800], [AC08.AAZ625], [AC08.AAE150], [AC08.AAE066], [AC08.AAE748], [AC08.AAE822], [AC08.AAZ631], [AC08.AAZ650], [AC08.AAC323], [AC08.AAE013], [AC08.AAZ649], [AC08.AAE860], [AC08.AAE859], [AC08.AAE011], [AC08.AAZ692], [AC08.AAE036], [AC08.AAB034], [AC08.AAB360], [AC08.AAB359], [AC08.AAF018], [AC08.AAA431], [AC08.AAZ673], [AC08.AAA027], [AC08.AAA508], [AC08.AAA350]), partitions(p0)4 - output([1]), filter(nil), access([VIEW1.B.AAZ686])5 - output([B.AAZ686]), filter([B.AAC001 = 1026005878]), access([B.AAC001], [B.AAZ686]), partitions(p0)

执行计划中的PX消失了(你可以理解为跨界点访问)，说明数据访问，关联都在一个节点完成
最终SQL由每次0.5秒-2秒优化到每次0.05秒，性能提升了10倍
算上每天跑20w次，平均耗费20%的CPU资源，那么这一个点的优化就可以将CPU资源从20%降低到2%

最后，有些小伙伴会有疑问，你咋知道SQL的NOT EXISTS应该加AC08.AAC001=B.AAC001过滤条件？原因有2个

1. 业务逻辑反推(得有开发&业务思维，纯运维DBA可能没这个思维)
2. AAC001是分区key

还有些小伙伴可能还有疑问，如果没有开发&业务思维怎么办？ 也可以写个SQL检查AAC001与AAZ686的对应关系

select  *from (select AAZ686, count(*) cntfrom (select AAZ686, AAC001 from ac08 group by AAZ686, AAC001)group by AAZ686order by 2 desc)where rownum <= 10;

如果CNT都是1就证明了我们的逻辑反推，如果CNT>1，得和业务确认，看看表中数据是否有问题

讲到这里还遇到点插曲，最开始查询的时候确实是1，过了2周我准备写博客了，再去查询发现CNT变成2了，把我吓了一跳
最终找到开发商，和业务确认，数据有错误。搞优化的同时帮开发商发现了数据质量问题，这尼玛...
 

相关文章：

利用业务逻辑+OB分布式特性优化SQL

最近某人社局核心数据库上了OB&#xff0c;经常出现性能问题 某人社与我司合作多年&#xff0c;非常信任我司在数据库的专业能力&#xff0c;邀请我司过去看看能否提供帮助 与OB驻场工程师合作&#xff0c;抓取了一天的TOP SQL&#xff0c;跑得慢的SQL有几十条(注意只是某一天的…...

编程日记 2023/4/15 12:17:23

哈希表

文章目录什么是哈希问题引入哈希函数直接定址法除留余数法 &#xff08;常用、重点&#xff09;哈希冲突哈希冲突的解决方法闭散列开散列unordered_map && unordered_set 封装实现哈希的应用位图布隆过滤器哈希经典面试题哈希切分位图应用布隆过滤器什么是哈希 在上一…...

编程日记 2023/4/15 12:17:15

基于Halcon的MLP（多层感知神经网络）分类器分类操作实例

一、介绍 人工神经网络（Artificial Neural Network，ANN）简称神经网络（Neural Network，NN）或类神经网络，是一种模仿生物神经网络的结构和功能的数学模型或计算模型，用于对函数进行估计或近似。 MLP神经网络是一种基于神经网络、动态的分类器。MLP分类器使用神经…...

编程日记 2023/4/12 3:04:26

VR全景博物馆，打造7*24小时的线上参访体验

导语&#xff1a;博物馆作为人们了解历史、文化和艺术的重要场所&#xff0c;现在可以通过VR全景技术来进行展览&#xff0c;让参观者身临其境地感受历史文化的魅力。本文将介绍博物馆VR全景的特点、优势&#xff0c;以及如何使用VR全景技术来丰富博物馆的展览和教育活动。什么…...

编程日记 2023/4/15 12:17:05

Go 数据类型

基础数据类型 类型长度&#xff08;字节&#xff09;默认值说明bool1falsebyte10uint8&#xff0c;取值范围[0,255]rune40Unicode Code Point&#xff0c;int32int,uint4或者8032位或64位操作系统int8,uint810-128~127&#xff0c;0-255int16,uint1620-32768~32767&#xff0c…...

编程日记 2023/4/15 12:17:00

Mybatis-Plus学透？一篇足够（持续更新中）

01、Mybatis-Plus入门 一、简介 MyBatis-Plus&#xff08;简称 MP&#xff09;是一个 MyBatis 的增强工具&#xff0c;在 MyBatis 的基础上只做增强不做改变&#xff0c;为简化开发、提高效率而生。如果你想对自己的项目进行技术升级&#xff0c;不妨尝试将mybatis换成Mybati…...

编程日记 2023/4/13 5:37:44

船用燃料油市场调研报告-主要企业、市场规模、份额及发展趋势

船用燃料油市场报告主要研究&#xff1a;市场规模&#xff1a; 产能、产量、销售、产值、价格、成本、利润等行业分析&#xff1a;原材料、市场应用、产品种类、市场需求、市场供给&#xff0c;下游市场分析、供应链分析等竞争分析&#xff1a;主要企业情况、市场份额、并购、扩…...

编程日记 2023/4/15 12:16:51

python趣味编程-奥赛罗游戏

在上一期我们用Python实现了一个高速公路汽车游戏的游戏&#xff0c;这一期我们继续使用Python实现一个简单的奥赛罗游戏&#xff0c;让我们开始今天的旅程吧~ 在Python中使用Turtle实现的奥赛罗游戏 在Python中使用Turtle的简单奥赛罗游戏 是一个以 Python 为程序设计语言的项…...

编程日记 2023/4/11 22:47:46

经典卷积模型回顾13—ResNetXt实现图像分类（matlab）

ResNetXt是ResNet的变种&#xff0c;在ResNet基础上引入了"split-transform-merge"的思想&#xff0c;旨在进一步提升模型的性能和准确率。ResNetXt模型的核心思想是通过对输入进行分组&#xff0c;然后对每个分组进行不同的变换&#xff0c;最后再将变换后的结果合并…...

编程日记 2023/4/11 12:17:42

Spring学习——Maven进阶

分模块开发与设计 创建模块 书写模块代码 通过maven指令安装模块到本地仓库(install指令) 在pom.xml中导入坐标执行maven的install命令将模块安装到本地maven仓库 团队内部开发可以发布模块功能到团队内部可共享的仓库中&#xff08;私服) 依赖管理 依赖指当前项目运行所需…...

编程日记 2023/4/15 12:16:46

第23篇：基础知识-Java Switch Case

switch case 语句判断一个变量与一系列值中某个值是否相等，每个值称为一个分支。 switch case 语句语法格式如下： switch(expression){ case value : //语句 break; //可选 case value : //语句 break; //可选 //你可以有任意数量的…...

编程日记 2023/4/15 12:16:37

Go 实现多态和 参数的动态个数及动态类型

引子 go语言作为静态(编译期类型检测)强类型(手写代码进行类型转换)语言, 要想实现 动态语言的鸭子类型的调用方法,做到 一个入参是不同类型,还是有些麻烦的; 需求 希望写代码时像python一样的鸭子类型,不用管参数类型,都可以调用同一个方法;希望 入参像python一样 能够在 个…...

编程日记 2023/4/15 12:16:28

vue 指令

Vue 提供了很多指令&#xff0c;如&#xff1a;v-model, v-show&#xff0c;v-if等等&#xff0c;有利于应付开发时出现的各种情况。Vue 也提供了自定义指令&#xff0c;有利于开发者将某些通用性功能封装成一个指令&#xff0c;进行全局或局部注册。如&#xff1a;复制指令&am…...

编程日记 2023/4/13 5:37:38

APP违法违规收集使用个人信息合规评流程和范围

近期&#xff0c;工信部通报2023年第1批《侵害用户权益行为的APP通报》&#xff08;总第27批&#xff09;&#xff0c;共通报46款APP&#xff08;SDK&#xff09;&#xff0c;这些被责令限期整改的APP&#xff08;SDK&#xff09;&#xff0c;涉及的问题主要包括3个方面&#x…...

编程日记 2023/4/13 5:37:30

【力扣2379】 得到 K 个黑块的最少涂色次数（c++100%）

给你一个长度为 n 下标从 0 开始的字符串 blocks &#xff0c;blocks[i] 要么是 W 要么是 B &#xff0c;表示第 i 块的颜色。字符 W 和 B 分别表示白色和黑色。给你一个整数 k &#xff0c;表示想要 连续 黑色块的数目。每一次操作中&#xff0c;你可以选择一个白色块将它 涂成…...

编程日记 2023/4/13 5:37:21

[2.2.2]进程调度的时机、方式、切换与过程

文章目录第二章 进程管理进程调度的时机、方式、切换与过程&#xff08;一&#xff09;进程调度的时机&#xff08;二&#xff09;进程调度的方式&#xff08;三&#xff09;进程的切换与过程小结第二章 进程管理 进程调度的时机、方式、切换与过程 时机 什么时候需要进程调度…...

编程日记 2023/4/13 5:37:10

第24篇：Java包装类知识深度分析

目录 1、包装类背景 2、包装类的优点 3、包装类与基本类型关系 4、代码示例...

编程日记 2023/4/13 5:37:05

常见问题整理1

目录 偏差和方差 欠拟合underfitting 过拟合overfitting 梯度消失和梯度爆炸 归一化 偏差和方差 偏差&#xff1a;算法期望预测和真实预测之间的偏差程度。反应的是模型本身的拟合能力。 方差&#xff1a;度量了同等大小的训练集的变动导致学习性能的变化&#xff0c;刻画…...

编程日记 2023/3/9 21:26:46

体验Linux 块设备驱动实验（模拟块）

目录 一、块设备 二、块设备驱动框架 1、块设备的注册和注销 2、gendisk 结构体 3、block_device_operations 结构体 4、块设备 I/O 请求过程 ①、请求队列 request_queue ②、bio 结构 三、编写驱动之请求队列 1、修改makefile 2、基本的驱动框架​编辑 3、添加头文…...

编程日记 2023/3/9 21:25:44

一文搞懂Linux时区设置、自定义时区文件

概念介绍 常说的 Linux 系统时钟有两个 一个是硬件时钟&#xff08;RTC&#xff09;&#xff0c;即BIOS时间&#xff0c;一般保存的是 GMT0 时间&#xff0c;没时区、夏令时的概念 一个是当地时钟&#xff08;LTC&#xff09;&#xff0c;即我们日常经常看到的时间&#xff0…...

编程日记 2023/3/9 21:24:42

Java实例实验项目大全源码企业通讯打印系统计划酒店图书学生管理进销存商城门户网站五子棋

wx供重浩&#xff1a;创享日记 对话框发送&#xff1a;java实例 获取完整源码源文件视频讲解文档资料等 文章目录1、企业通讯2、快递打印系统3、开发计划管理系统4、酒店管理系统5、图书馆管理系统6、学生成绩管理系统7、进销存管理系统8、神奇Book——图书商城9、企业门户网站…...

编程日记 2023/3/9 21:23:36

基于nvidia xavier智能车辆自动驾驶域控制器设计与实现-百度Apollo架构(二）

智能车辆操作系统 智能车辆操作系统是智能车辆系统的重要组成部分。现代汽车软件组件通常首 先由不同的供应商开发&#xff0c;然后在有限的资源下由制造商进行集成[42]。智能车辆操作 系统需要采用模块化和分层化设计思想来兼容传感器、分布式通信和自动驾驶通用 框架等模块&a…...

编程日记 2023/3/9 21:22:35

考研408 王道计算机考研 (初试/复试) 网课笔记总结

计算机初试、复试笔记总结&#xff08;导航栏&#xff09;&#x1f4dd; 一、初试 408 408 - 1. 数据结构与算法 数据结构与算法 笔记导航&#x1f6a5;&#x1f6a5;&#x1f6a5; &#x1f96c; 第一章 绪论(无)&#x1f955; 第二章 线性表&#x1f96a; 第三章 栈和队列&…...

编程日记 2023/3/9 21:21:31

[Java·算法·中等]LeetCode34. 在排序数组中查找元素的第一个和最后一个位置

每天一题&#xff0c;防止痴呆题目示例分析思路1题解1&#x1f449;️ 力扣原文 题目 给你一个按照非递减顺序排列的整数数组 nums&#xff0c;和一个目标值 target。请你找出给定目标值在数组中的开始位置和结束位置。 如果数组中不存在目标值 target&#xff0c;返回 [-1,…...

编程日记 2023/3/9 21:20:26

SAP BTEs的简介及实现

一、认识BTE BTE&#xff08;Business Transaction Event&#xff09;也称之为“业务交易事件”,一般的增强(Tcode:SMOD|CMOD)依旧使用ABAP进行二次开发,然而BTE则提供了RFC调用其它产品的可能(Tcode:FIBF)。BTE的设计思路更加简单&#xff0c;和BADI有点类似。在标准程序中留有…...

编程日记 2023/3/9 21:19:24

如何利用海外主机服务提高网站速度？

网站速度是任何在线业务成功的关键。快速的网站速度可以让用户更快地访问您的网站&#xff0c;增加页面浏览量。对于拥有全球用户的网站而言&#xff0c;选择一个海外主机服务商是提高网站速度的有效方法之一。下面是一些利用海外主机服务(如美国主机、香港主机)提高网站速度的…...

编程日记 2023/4/13 5:37:00

【SpringMVC】 一文掌握 》》》 @RequestMapping注解

个人简介&#xff1a;Java领域新星创作者&#xff1b;阿里云技术博主、星级博主、专家博主&#xff1b;正在Java学习的路上摸爬滚打&#xff0c;记录学习的过程~ 个人主页&#xff1a;.29.的博客 学习社区&#xff1a;进去逛一逛~ RequestMapping注解一、SpringMVC环境准备1.相…...

编程日记 2023/3/9 21:17:15

高三应该怎么复习

高三是学生们备战高考的重要一年&#xff0c;正确有序的复习可以有效地提高复习效率&#xff0c;下面是一些高效复习的方法和建议&#xff1a;1. 制定合理的学习计划和目标高三的学生要制定合理的学习计划和目标&#xff0c;适当的计划和目标可以使学习更有针对性和效率。建议根…...

编程日记 2023/3/9 21:16:13

如何通过C++ 将数据写入 Excel 工作表

直观的界面、出色的计算功能和图表工具&#xff0c;使Excel成为了最流行的个人计算机数据处理软件。在独立的数据包含的信息量太少&#xff0c;而过多的数据又难以理清头绪时&#xff0c;制作成表格是数据管理的最有效手段之一。这样不仅可以方便整理数据&#xff0c;还可以方便…...

编程日记 2023/3/9 21:15:10

Kalman Filter in SLAM (6) ——Error-state Kalman Filter (EsKF, 误差状态卡尔曼滤波)

文章目录0.前言1. IMU的误差状态空间方程2. 误差状态观测方程3. 误差状态卡尔曼滤波4. 误差状态卡尔曼滤波方程细节问题0.前言 这里先说一句&#xff1a;什么误差状态卡尔曼&#xff1f;完全就是在扯淡&#xff01; 回想上面我们推导的IMU的误差状态空间方程&#xff0c;其实…...

编程日记 2023/3/9 21:14:05