SQL优化之first_value

好久没写blog了,最近又忙着学java,然后就更没有时间了。刚好今天同事有个sql需要优化,我就看了一下,然后做了一个记录。

原sql如下:

SELECT M.TORD_ID,
       '101',
       M.BIZ_UNT_CD,
       DECODE(M.BIZ_UNT_CD, '1001', '2001', '5001', '52001') L_chan_id,
       (SELECT PRD.DLR_ID
          FROM shunzi.PRD_PRD_M PRD, shunzi.PRD_UNT_PRD_D UNT
         WHERE UNT.PRD_ID = PRD.PRD_ID
           AND UNT.UNT_PRD_ID = M.UNIT_SEQ) DLR_ID,
       (SELECT UNT.PRD_ID
          FROM shunzi.PRD_UNT_PRD_D UNT
         WHERE 1 = 1
           AND UNT.UNT_PRD_ID = M.UNIT_SEQ) PRD_ID,
       M.UNIT_SEQ,
       M.BARCODE UNIT_CODE,
       NVL((SELECT RGN.STKO_WH_NO
             FROM shunzi.CMM_ZIP_NO_RGN_WH_D RGN
            WHERE RGN.WH_PTR_CD = '10'
              AND RGN.MDA_CD = '10'
              AND RGN.BIZ_UNT_CD = M.BIZ_UNT_CD
              AND RGN.LRGN_ID = M.ACC_PRV_ID
              AND RGN.MRGN_ID = M.ACC_CITY_ID
              AND RGN.SRGN_ID = M.ACC_CONTR_ID
              AND ROWNUM = 1),
           'C01') WH_CD,
       DECODE(NVL((SELECT RGN.STKO_WH_NO
                    FROM shunzi.CMM_ZIP_NO_RGN_WH_D RGN
                   WHERE RGN.WH_PTR_CD = '10'
                     AND RGN.MDA_CD = '10'
                     AND RGN.BIZ_UNT_CD = M.BIZ_UNT_CD
                     AND RGN.LRGN_ID = M.ACC_PRV_ID
                     AND RGN.MRGN_ID = M.ACC_CITY_ID
                     AND RGN.SRGN_ID = M.ACC_CONTR_ID
                     AND ROWNUM = 1),
                  'C01'),
              'C01',
              1,
              'C03',
              8,
              'C04',
              3,
              'C06',
              55,
              'C07',
              60,
              'C05',
              48,
              1) DLV_ID,
       0,
       PC.SL_PC sl_pc,
       PC.SL_PC acc_sls_amt,
       PC.PRS_PC prs_pc,
       1,
       20161124,
       M.FD_DATE,
       M.CUSER_ID,
       M.FD_DATE,
       M.CUSER_ID,
       M.FD_DATE
  FROM shunzi.PRD_TICK_ORD_M M,
       (SELECT A.PRD_ID,
               A.UNT_SEQ,
               (SELECT D.BARCODE
                  FROM shunzi.PRD_UNT_PRD_D D
                 WHERE D.PRD_ID = A.PRD_ID
                   AND D.UNT_SEQ = A.UNT_SEQ) UNIT_CODE,
               (SELECT D.UNT_PRD_ID
                  FROM shunzi.PRD_UNT_PRD_D D
                 WHERE D.PRD_ID = A.PRD_ID
                   AND D.UNT_SEQ = A.UNT_SEQ) UNT_PRD_ID,
               A.SL_PC SL_PC,
               A.PRS_PC PRS_PC
          FROM shunzi.PRD_UNT_PRD_PC_CHG_D A
         WHERE (PRD_ID, UNT_SEQ, PC_SEQ) IN
               (SELECT A.PRD_ID, A.UNT_SEQ, MAX(PC_SEQ)
                  FROM shunzi.PRD_UNT_PRD_PC_CHG_D A,
                       shunzi.PRD_PRD_M            B,
                       shunzi.PRD_UNT_PRD_D        C
                 WHERE B.PRD_ID = A.PRD_ID
                   AND B.PRD_ID = C.PRD_ID
                   AND A.UNT_SEQ = C.UNT_SEQ
                   AND C.UNT_PRD_SL_STS_CD != '10'
                   AND ((B.PRD_APP_STS_CD = '210' AND A.PC_APP_STS_CD = '250') OR
                       (B.PRD_APP_STS_CD != '210'))
                   AND SYSDATE BETWEEN A.PC_BGN_DATE AND A.PC_CL_DATE
                --AND B.PRD_PTR_CD = '50' 
                 GROUP BY A.PRD_ID, A.UNT_SEQ)) PC
 WHERE 1 = 1
   AND M.UNIT_SEQ = PC.UNT_PRD_ID
   AND M.TICKET_TYPE = '30'
   AND M.FD_DATE BETWEEN TO_DATE('2016-11-01', 'yyyy-mm-dd hh24:mi:ss') AND
       TO_DATE('2016-11-25 00:00:00', 'yyyy-mm-dd hh24:mi:ss') - 0.001
   AND NVL((SELECT 1
             FROM shunzi.ORD_ORD_DTL_D G
            WHERE G.ORD_ID = M.ORD_ID
              AND G.ORD_SEQ = M.ORD_SEQ
              AND G.UNT_PRD_ID = M.UNIT_SEQ
              and (((G.ord_sts_cd = '70' and G.ord_supp_sts_cd is null) or
                  G.ord_sts_cd = '96') and G.rjt_rtn_yn = 'N')),
           '0') = '0'

- 阅读剩余部分 -

Reading table information for completion of table and column names

今天群里一同学问了一个问题,为什么在执行use db_name的时候会有如下提示:

mysql> use db_name;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
我也很好奇,于是就google了一下,得到下面一段解释,老外的:

- 阅读剩余部分 -

MySQL之log_timestamps

今天有同学在群里问MySQL的error log里记录的时间怎么和当前系统时间不一致,日志里的时间要比当前晚了好些。我以前也遇到过这类问题,是在crontab这个地方遇到的,一直也没搞明白怎么回事,最后重装系统,重新ntp同步后,发现时间又正常了。最初查询google还以为是rsyslog程序的问题。

后来经群里另一个同学提示,发现MySQL 5.7.2以后的版本新增了一个log_timestamps参数,原来问题就出在这里。在MySQL官方文档里有下面一段话:

Error Log Message Format

As of MySQL 5.7.2, the log_timestamps system variable controls the timestamp time zone of messages written to the error log (as well as to general query log and slow query log files). Permitted values are UTC (the default) and SYSTEM (local system time zone). Before MySQL 5.7.2, messages use the local system time zone.

- 阅读剩余部分 -

记一次标量子查询的优化

今天一个同事扔来一条SQL,反映说11s才出结果,前台有个10s的超时设置,问能不能优化下,降到10s以内。SQL Code如下:

SELECT /* system-mng-mapper | selectPtnDispatchbillInsert  */
            STKI_CMD_ID         --采购单号
            ,WH_NO              --仓库号
            ,WH_NM              --仓库名
            ,STKI_CMD_PGS_STS_NM    --执行状态
            ,PRD_ID             --商品号
            ,PRD_NM             --商品名
            ,MD_ID              --MD号
            ,MD_NM              --MD名
            ,STKI_CMD_PTR_NM    --采购类型
            ,PRS_SP_NM          --合作方式
            ,PRD_PTR_NM         --商品类型
            ,STKI_CMD_DATE      --采购指示日
            ,STKI_DDL_DATE      --采购到期日
            ,ORG_STKI_CMD_ID    --原采购单号
            ,STKI_CMD_REQ_ID    --采购申请号
            ,STOCK_MDA_NM       --库存渠道
            ,WH_PTR_NM          --入库属性
            ,STKI_CMD_QTY                                                          AS STKI_CMD_QTY         --采购数量
            ,(A.STKI_CMD_SO_ARR_QTY - A.STKI_CMD_CNFM_QTY - A.STKI_CMD_QA_FLR_QTY) AS STKI_SO_PGS_QTY      --发货在途数量
            ,STKI_CMD_CNFM_QTY                                                     AS STKI_CNFM_QTY        --入库数量            
            ,(A.STKI_CMD_QTY - (A.STKI_CMD_SO_ARR_QTY - A.STKI_CMD_QA_FLR_QTY))    AS STKI_RMN_QTY         --剩余数量   
        FROM
        (SELECT
             PG_FUL_UTIL.FN_FUL_CMD_MASK(FSCM.STKI_CMD_ID) AS STKI_CMD_ID
            ,FSCM.WH_NO
            ,(SELECT WH_NM FROM FUL_WH_M WHERE FUL_WH_M.WH_NO = FSCM.WH_NO) WH_NM
            ,(SELECT CODE_NM FROM CMM_CODE_D WHERE CODE_GRP_ID = 'FUL005' AND CMM_CODE_D.CODE = FSCM.STKI_CMD_PGS_STS_CD) STKI_CMD_PGS_STS_NM
            ,FSCM.PRD_ID
            ,(SELECT PRD_NM FROM PRD_PRD_M WHERE PRD_PRD_M.PRD_ID = FSCM.PRD_ID) AS PRD_NM
            ,FSCM.MD_ID
            ,(SELECT MD_NM FROM PRD_MD_M WHERE PRD_MD_M.MD_ID = FSCM.MD_ID) AS MD_NM
            ,(SELECT CODE_NM FROM CMM_CODE_D WHERE CODE_GRP_ID = 'FUL006' AND CMM_CODE_D.CODE = FSCM.STKI_CMD_PTR_CD) STKI_CMD_PTR_NM
            ,(SELECT CODE_NM FROM CMM_CODE_D WHERE CODE_GRP_ID = 'PRD024' AND CMM_CODE_D.CODE = PPM.PRS_SP_CD) PRS_SP_NM
            ,(SELECT Z.CODE_NM FROM CMM_CODE_D Z WHERE Z.CODE_GRP_ID = 'PRD010' AND Z.CODE = PPM.PRD_PTR_CD) AS PRD_PTR_NM
            ,FSCM.STKI_CMD_DATE
            ,FSCM.STKI_DDL_DATE
            ,FSCM.ORG_STKI_CMD_ID
            ,FSCM.STKI_CMD_REQ_ID
            ,(SELECT CODE_NM FROM CMM_CODE_D WHERE CODE_GRP_ID = 'PRD103' AND CMM_CODE_D.CODE = FSCM.STOCK_MDA_CD) AS STOCK_MDA_NM
            ,(SELECT Z.CODE_NM FROM CMM_CODE_D Z WHERE Z.CODE_GRP_ID = 'PRD014' AND Z.CODE = PPM.WH_PTR_CD) AS WH_PTR_NM
            ,NVL((SELECT SUM(STKI_CMD_QTY) STKI_CMD_QTY FROM FUL_STKI_CMD_D WHERE STKI_CMD_ID = FSCM.STKI_CMD_ID AND FSCM.WH_NO = WH_NO),0) AS STKI_CMD_QTY
            ,NVL((SELECT SUM(FSND1.STKI_CNFM_QTY)
                  FROM FUL_STKI_SO_D   FSSD1
                      ,FUL_STKI_CNFM_D FSND1
                  WHERE FSSD1.WH_NO           = FSND1.WH_NO
                    AND FSSD1.STKI_SO_ID      = FSND1.STKI_SO_ID           
                    AND FSSD1.STKI_SO_DTL_SEQ = FSND1.STKI_SO_DTL_SEQ
                    AND FSSD1.WH_NO           = FSCM.WH_NO
                    AND FSSD1.STKI_CMD_ID     = FSCM.STKI_CMD_ID),0) AS STKI_CMD_CNFM_QTY
            ,NVL((SELECT SUM(
                           CASE WHEN FSSD1.STKI_SO_PGS_STS_CD = '91' THEN 0
                                 WHEN FSSD1.STKI_SO_PGS_STS_CD = '92' THEN FSSD1.STKI_SO_ARRIV_QTY
                                ELSE FSSD1.STKI_SO_ARR_QTY  END )
                  FROM FUL_STKI_SO_D FSSD1
                 WHERE FSSD1.WH_NO         = FSCM.WH_NO
                    AND FSSD1.STKI_CMD_ID   = FSCM.STKI_CMD_ID ),0)  AS STKI_CMD_SO_ARR_QTY
            ,NVL((SELECT SUM(FSQD1.QA_FLR_QTY)
                  FROM FUL_STKI_SO_D   FSSD1
                      ,FUL_STKI_QA_D   FSQD1
                  WHERE FSSD1.WH_NO           = FSQD1.WH_NO
                    AND FSSD1.STKI_SO_ID      = FSQD1.STKI_SO_ID
                    AND FSSD1.STKI_SO_DTL_SEQ = FSQD1.STKI_SO_DTL_SEQ                  
                    AND FSSD1.WH_NO           = FSCM.WH_NO
                    AND FSSD1.STKI_CMD_ID     = FSCM.STKI_CMD_ID),0) AS STKI_CMD_QA_FLR_QTY                                       
        FROM FUL_STKI_CMD_M FSCM
            ,PRD_PRD_M PPM
       WHERE FSCM.PRD_ID = PPM.PRD_ID
         AND FSCM.DLR_ID = '803'
         AND FSCM.STKI_CMD_PGS_STS_CD IN ('16', '20', '30')                                                                            
         AND FSCM.STKI_CMD_PGS_STS_CD NOT IN ('90','95')                                                                                         
         ) A                                                                                                                                       
        ORDER BY A.STKI_CMD_DATE DESC

- 阅读剩余部分 -

MySQLSRV起停脚本

分享一个自己写mysql起停脚本,方便测试环境多实例其他操作

[root@node2 ~]# cat /sbin/mysqlsrv 
#!/bin/bash

mysql_dir=/usr/local/mysql/
mysql_dat=/data
if [ "$2" = "" ] 
then
        port=3306
else
        port="$2"
fi

function mysql_process_cnt()
(ps -ef|grep -v "grep"|grep  mysql$port|wc -l)

function mysql_start()
(/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my$port.cnf &)

function mysql_kill()
(
#ps -ef|grep mysql|grep $port|awk -F' ' '{print $2}'|xargs kill -9
mysqladmin  -uroot -pshunzi -S /data/mysql$port/tmp/mysql$port.sock shutdown
)

function mysql_stat()
(mysqladmin  -uroot -pshunzi -S /data/mysql$port/tmp/mysql$port.sock  -r -i 1 ext |\
awk -F"|" \
"BEGIN{ count=0; }"\
'{ if($2 ~ /Variable_name/ && ((++count)%20 == 1)){\
    print "----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --";\
    print "---Time---|---QPS---|select insert update delete|  read inserted updated deleted|   logical    physical";\
}\
else if ($2 ~ /Queries/){queries=$3;}\
else if ($2 ~ /Com_select /){com_select=$3;}\
else if ($2 ~ /Com_insert /){com_insert=$3;}\
else if ($2 ~ /Com_update /){com_update=$3;}\
else if ($2 ~ /Com_delete /){com_delete=$3;}\
else if ($2 ~ /Innodb_rows_read/){innodb_rows_read=$3;}\
else if ($2 ~ /Innodb_rows_deleted/){innodb_rows_deleted=$3;}\
else if ($2 ~ /Innodb_rows_inserted/){innodb_rows_inserted=$3;}\
else if ($2 ~ /Innodb_rows_updated/){innodb_rows_updated=$3;}\
else if ($2 ~ /Innodb_buffer_pool_read_requests/){innodb_lor=$3;}\
else if ($2 ~ /Innodb_buffer_pool_reads/){innodb_phr=$3;}\
else if ($2 ~ /Uptime / && count >= 2){\
  printf(" %s |%9d",strftime("%H:%M:%S"),queries);\
  printf("|%6d %6d %6d %6d",com_select,com_insert,com_update,com_delete);\
  printf("|%6d %8d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);\
  printf("|%10d %11d\n",innodb_lor,innodb_phr);\
}}')

case $1 in 
        start)
        if [ `mysql_process_cnt` -gt  0 ]
                then
                        echo 'Mysql Server is running!'
        else
                        mysql_start
                        echo 'Begin startup mysql...'
                        sleep 5;
                        if [ `mysql_process_cnt` -gt  0 ]
                        then
                                echo 'Mysql Server is startup!'
                        else
                                echo 'Mysql Server is startup error!'
                        fi
        fi
;;
        stop)
        if [ `mysql_process_cnt` -gt 0 ]
                then 
                        mysql_kill
                        echo 'Begin stop mysql...'
                        sleep 5;
                        if [ `mysql_process_cnt` -gt 0 ]
                        then
                                echo 'Mysql Server is stop error!'
                        else
                                echo 'Mysql Server is stop ok!'
                        fi
        else
                        echo 'Mysql Server is not run!'
        fi
;;
        cnt)
        echo `mysql_process_cnt` "$2" mysql$port
;;
        mysql)
        mysql -uroot -pshunzi -S /data/mysql$port/tmp/mysql$port.sock
;;
        stat)
        mysql_stat
;;
        *)
        echo "###  Usage:  ### default port 3306"
        echo "==================================================="
        echo "mysqlsrv start port #start the port's mysql server!"
        echo "mysqlsrv stop port  #stop  the port's mysql server!"
        echo "mysqlsrv cnt port   #view  the port's process info!"
        echo "mysqlsrv stat port  #view  the port's server status!"
        echo "==================================================="
;;
esac
``
Title - Artist
0:00