记一次标量子查询的优化

今天一个同事扔来一条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
``

MySQL 5.7之主从复制搭建

MySQL5.7版本发版已经好久了,相对也比较稳定了,刚好今天搭好虚拟机环境,下面就来搭一套基于MySQL5.7版本的主从复制吧。

复制过程

mysql-replication.png

  1. master将变化的binary log events记录到binary log
  2. slavemasterbinary log events拷贝到它的relay log中
  3. slave把relay log events重新应用到数据库。

准备环境

- 阅读剩余部分 -

Linux审计脚本

有时候遇到主机上的数据莫名被rm的情况,而且还没人承认,
那么就需要在系统上加个审计记录操作日志,方便追溯。
这样锅就不用你来背了,脚本如下:

cat /etc/profile
PS1="`whoami`@`hostname`:"'[$PWD]'
history
USER_IP=`who -u am i 2>/dev/null| awk '{print $NF}'|sed -e 's/[()]//g'`
if [ "$USER_IP" = "" ]
then
USER_IP=`hostname`
fi
if [ ! -d /tmp/history ]
then
mkdir /tmp/history
chmod 777 /tmp/history
fi
if [ ! -d /tmp/history/${LOGNAME} ]
then
mkdir /tmp/history/${LOGNAME}
chmod 300 /tmp/history/${LOGNAME}
fi
export HISTSIZE=4096
DT=`date +"%Y%m%d_%H%M%S"`
export HISTFILE="/tmp/history/${LOGNAME}/${USER_IP} history.$DT"
chmod 600 /tmp/history/${LOGNAME}/*history* 2>/dev/null

你不是真的快乐

【你不是真正的快乐】
关于周星驰一生作品的回忆,我看哭了。。星爷陪伴我们走过了那么多年,这是一部唯一回忆他过往的。 。 分享给为他笑过的你~~~
(如遇到不能播放,请把https改成http访问!)

Title - Artist
0:00