理想

曾几何时,我们还是否记得自己的理想?
就像歌词里写的 :又一个年代在变化,我已不是无悔的那个青年,青春被时光抛弃,已是当父亲的年纪。

一个人住在这城市
为了填饱肚子就已精疲力尽
还谈什么理想
那是我们的美梦

梦醒后 还是依然奔波在风雨的街头
有时候想哭就把泪 咽进一腔热血的胸口

公车上我睡过了车站
一路上我望着霓虹的北京
我的理想把我丢在这个拥挤的人潮
车窗外已经是一片白雪茫茫

又一个四季在轮回
而我一无所获的坐在街头
只有理想在支撑着那些麻木的血肉

理想今年你几岁
你总是诱惑着年轻的朋友
你总是谢了又开 给我惊喜
又让我沉入失望的生活里

公车上我睡过了车站
一路上我望着霓虹的北京
我的理想把我丢在这个拥挤的人潮
车窗外已经是一片白雪茫茫

又一个四季在轮回
而我一无所获的坐在街头
只有理想在支撑着那些麻木的血肉

理想今年你几岁
你总是诱惑着年轻的朋友
你总是谢了又开 给我惊喜
又让我沉入失望的生活里

又一个年代在变换
我已不是无悔的那个青年
青春被时光抛弃
已是当父亲的年纪

理想永远都年轻
你让我倔强地反抗着命运
你让我变得苍白
却依然天真的相信花儿会再次的盛开

阳光之中 到处可见奔忙的人们
被拥挤着 被一晃而飞的光阴忽略过

域名变更

域名shunzi.me突然被GFW墙了.无解...
突然有几天想放弃了
既然我不怎么写blog
又何必折腾来,折腾去呢
缓了好久,想了好久
然后重新注册域名mydba.me
blog从wordpress迁移到typecho
web跟db分开.
然后继续折腾.
还是那句话:生命不息,折腾不止!

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'

拿到sql大概看了一下,发现PC那段可以改写,然后做了如下修改:

SELECT A.PRD_ID,
       A.UNT_SEQ,
       D.BARCODE UNIT_CODE,
       D.UNT_PRD_ID UNT_PRD_ID,
       first_value(A.SL_PC) over(partition by A.PRD_ID, A.UNT_SEQ, D.BARCODE, D.UNT_PRD_ID order by a.PC_SEQ desc) SL_PC,
       first_value(A.PRS_PC) over(partition by A.PRD_ID, A.UNT_SEQ, D.BARCODE, D.UNT_PRD_ID order by a.PC_SEQ desc) PRS_PC
  FROM CHGSHS.PRD_UNT_PRD_PC_CHG_D A,
       chgshs.prd_unt_prd_d        d,
       CHGSHS.PRD_PRD_M            B,
       CHGSHS.PRD_UNT_PRD_D        C
 where d.prd_id = a.prd_id
   and d.unt_seq = a.unt_seq
   and 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

然后让同事拿去验证下数据是否一致,如果没问题就继续改写;不一会同事就说没问题了,原先4分多没出结果,现在几乎秒出结果,不用往下改了。

这种根据自己表的某一列取最大值相对应的数据时,就可以改成first_value() over()来处理,极大的加快了数据处理的时间,oracle在分析函数方面还是非常不错的。

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了一下,得到下面一段解释,老外的:

On the first "use" command after login, MySQL scans database, tables and columns name for auto completion. If you have many db, tables it could take a while.

To avoid that, launch your client with the -A option (or --no-auto-rehash)

mysql -uroot -p -A

You could also add the disable_auto_rehash variable in your my.cnf (in the [mysql] section) if you want to disable it completely. This change does not require a reboot (it is a client, not server, variable).

意思就是说,当你登录数据库第一次使用use命令的时候,mysql要去扫描相关的数据库、表、列名的信息,然后就会出现上面那段提示,如果不想显示,可以在登录的时候加上-A or --no-auto-rehash命令,另外一种是在my.cnf里的cliect里加上no-auto-rehash参数即可。

这个扫描动作也可以理解为收集统计信息,当数据库的数据量比较大的时候,你会发现执行use db_name时会卡住一段时间,不用紧张,转身去倒杯水,回来就好啦。

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.

在MySQL 5.7.2版本以后用log_timestamps参数来控制写入错误日志消息的时间戳的时间区(以及通用查询日志和慢查询日志文件),时区参数分为UTC(系统默认)和SYSTEM(本地系统时区)。

mysql  Ver 14.14 Distrib 5.7.13, for linux-glibc2.5 (x86_64) using  EditLine wrapper

Connection id: 117
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.13-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /data/mysql3306/tmp/mysql3306.sock
Uptime: 2 days 11 hours 49 min 28 sec

Threads: 3 Questions: 20 Slow queries: 0 Opens: 110 Flush tables: 1 Open tables: 103 Queries per second avg: 0.000

mysql> exit
Bye
[root@node2 ~]# mysql -uroot -p12345678 -S /data/mysql3306/tmp/mysql3306.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@node2 ~]# mysql -uroot -p12345678 -S /data/mysql3306/tmp/mysql3306.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@node2 ~]# cat /data/mysql3306/data/error.log |grep "using password"
2016-09-03T02:19:26.048001Z 118 [Note] Access denied for user 'root'@'localhost' (using password: YES)
2016-09-03T02:19:29.125050Z 119 [Note] Access denied for user 'root'@'localhost' (using password: YES)
[root@node2 ~]# date
Sat Sep 3 10:21:04 CST 2016

mysql> show variables like 'log_timestamps'
-> ;
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| log_timestamps | UTC |
+----------------+-------+
1 row in set (0.01 sec)

mysql> SET GLOBAL log_timestamps = SYSTEM;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'log_timestamps';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| log_timestamps | SYSTEM |
+----------------+--------+
1 row in set (0.01 sec)

mysql> exit
Bye
[root@node2 ~]# mysql -uroot -p12345678 -S /data/mysql3306/tmp/mysql3306.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@node2 ~]# cat /data/mysql3306/data/error.log |grep "using password"
2016-09-03T02:19:26.048001Z 118 [Note] Access denied for user 'root'@'localhost' (using password: YES)
2016-09-03T02:19:29.125050Z 119 [Note] Access denied for user 'root'@'localhost' (using password: YES)
2016-09-03T10:30:48.548075+08:00 121 [Note] Access denied for user 'root'@'localhost' (using password: YES)
[root@node2 ~]# date
Sat Sep 3 10:30:55 CST 20


测试发现,就是这个问题,改完log_timestamps参数为SYSTEM后,日志记录时间戳就和本机系统时间一致了,在这里mark一下。

Title - Artist
0:00