spring-helloworld

2016-10-13

1.创建一个java项目

最好是maven项目，可以自己下载包，比较方便。

2.添加spring库

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>4.3.3.RELEASE</version>
</dependency>

3.创建源文件

HelloWord.java
一个普通的class

package com.tutorialspoint;
/**
 * Created by gqdw on 13/10/2016.
 */
public class HelloWorld {
    private String message;
    public void setMessage(String m){
        message = m;
    }
    public void getMessage(){
        System.out.println("your Message : " + message);
    }
}

MainApp.java
MainApp通过spring bean来获取对接，然后调用他的方法，bean通过xml文件来配置:

package com.tutorialspoint;
/**
 * Created by gqdw on 13/10/2016.
 */
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class MainApp {
    public static void main(String[] args) {
        ApplicationContext context =
                new ClassPathXmlApplicationContext("Beans.xml");
        HelloWorld obj = (HelloWorld) context.getBean("HelloWorld");
        obj.getMessage();
    }
}

ClassPathXmlApplicationContext来获取context，然后用context的getBean方法来获取HelloWorld对象。

Beans.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">
<bean id="HelloWorld" class="com.tutorialspoint.HelloWorld">
    <property name="message" value="Hello World! aca"/>
</bean>
</beans>

Bean文件配置了一个id，class，相当于new了一个HelloWorld对象。value给message，

4.运行程序

程序正常运行就能打印出

1	Your Message : Hello World! aca

简单的helloworld程序，演示了spring bean的装配。

使用celery做异步任务

2016-10-09

1.选择broker

可以选择的broker很多，一般选择rabbitmq。本身就是消息中间件。rabbitmq的安装略。

2.安装celery

1	$ pip install celery

3.创建tasks

创建tasks.py

from celery import Celery
app = Celery('tasks', broker='amqp://guest@localhost//')
@app.task
def add(x, y):
    return x + y

4.运行celery worker

1	$ celery -A tasks worker --loglevel=info

5.调用tasks

很简单，import进来，然后调用delay函数即可。

1	add.delay(1,2)

celery worker进程会收到这个消息并执行他

1 2	Received task: proj.tasks.add[9e9ee3e8-1350-4858-9fbb-0fd62779c3e1] Task proj.tasks.add[9e9ee3e8-1350-4858-9fbb-0fd62779c3e1] succeeded in 0.00637572002597s: 3

注意，这里并没有记录结果。

6.保存结果

celery里面做法也很简单，初始化app的时候加个backend就可以了。这里已redis为例:

1	app = Celery('tasks', backend='redis://localhost', broker='amqp://')

1 2	result = add.delay(4, 4) ret = result.get(timeout=1)

java生成mysql分区语句

2016-10-05

1.分区

之前介绍了分区，
http://qinyueming.cc/2016/09/26/mysql-partitioning/，可以很容易的写出range分区语句出来,但是最好别手写，手写容易出错。

2.程序

最新学习java，顺便用java写了一个自动生成zabbix分区的程序出来。

关键点在于用了StringBuilder. 参看 java编程思想里面的UsingStringbuilder.java:

import java.util.Random;
public class UsingStringBuilder{
	public static Random rand = new Random(47);
	public String toString(){
		StringBuilder result = new StringBuilder("[");
		for(int i = 0; i < 25; i++){
			result.append(rand.nextInt(100));
			result.append(", ");
		}
		result.delete(result.length()-2, result.length());
		result.append("]");
		return result.toString();
	}
	public static void main(String[] args){
		UsingStringBuilder usb = new UsingStringBuilder();
		System.out.println(usb);
	}
}

另一个关键点是日期转化为unix timestamp。用到了SimpleDateFormat,SimpleDateFormat的parse方法可以直接解析返回一个Date对象，Date对象的getTime()返回的是unix timestamp的毫秒数，除以1000就是mysql里面的unix timestamp了。用起来还是非常爽的。总觉得比python的类库要好用。下面是一个小例子:

/**
 * Created by aca on 10/4/16.
 */
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
public class DateTest {
    public static void main(String[] args) {
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        String s1 = "2016-10-04";
        String s2 = String.format("2016-%02d-01", 11);
        System.out.println(s2);
        try{
            Date d = df.parse(s2);
            System.out.println(d.getTime() / 1000);
        }catch(ParseException pe){
            System.out.println("parse error.");
        }
    }
}

3.完整程序

见https://gist.github.com/gqdw/97ca758d3d0866baa039eb4fd02bbda8

/**
 * Created by aca on 10/4/16.
 */
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
public class Genpartition {
    // alter table xxx partition by range(id)
    // (partition p0 values less than (xx) ,
    // partition p1 values less than (yy));
    static String Gensql(String tablename, String column){
        StringBuilder result = new StringBuilder("alter table ");
//        String res = "alter table " + tablename + " partition by range(" + column + ")" ;
        result.append(tablename);
        result.append(" partition by range(");
        result.append(column);
        result.append(")(\n");http://qinyueming.cc/2016/09/26/mysql-partitioning/
        for (int i = 1; i <= 12; i++) {
            SimpleDateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd");
            String txtdate = String.format("2016-%02d-01", i);
            //= "2016-%d-01".format(i);
//            System.out.println(txtdate);
            Date date = new Date();
            try {
                date = dateformat.parse(txtdate);
            }catch(ParseException pe){
                System.out.println("parse error.");
            }
            result.append("partition p" );
            result.append(String.format("2016%02d", i));
            result.append(" values less than (");
            result.append(date.getTime() / 1000);
            result.append("),\n");
        }
        result.delete(result.length()-2, result.length());
        result.append(");\n");
        return result.toString();
    }
    public static void main(String[] args) {
        String[] tables = {"history_uint", "trends_uint"};
        for(String t:tables)
            System.out.println(Gensql(t, "clock"));
    }
}

4.生成结果

alter table history_uint partition by range(clock)(
partition p201601 values less than (1451577600),
partition p201602 values less than (1454256000),
partition p201603 values less than (1456761600),
partition p201604 values less than (1459440000),
partition p201605 values less than (1462032000),
partition p201606 values less than (1464710400),
partition p201607 values less than (1467302400),
partition p201608 values less than (1469980800),
partition p201609 values less than (1472659200),
partition p201610 values less than (1475251200),
partition p201611 values less than (1477929600),
partition p201612 values less than (1480521600));
alter table trends_uint partition by range(clock)(
partition p201601 values less than (1451577600),
partition p201602 values less than (1454256000),
partition p201603 values less than (1456761600),
partition p201604 values less than (1459440000),
partition p201605 values less than (1462032000),
partition p201606 values less than (1464710400),
partition p201607 values less than (1467302400),
partition p201608 values less than (1469980800),
partition p201609 values less than (1472659200),
partition p201610 values less than (1475251200),
partition p201611 values less than (1477929600),
partition p201612 values less than (1480521600));

可以直接在mysql里面跑。

java-mysql操作

2016-09-28

1.基本用法

官网文档在这里了:
https://dev.mysql.com/doc/connector-j/5.1/en/

DriverManager
Connection
Statement
ResultSet

四个主要的对象。由DriverManager的getConnection方法获得一个连接，然后再用Connection的createStatement来创建一个Statement对象，之后用Statement对象的executeQuery来执行相关的SQL语句，返回一个ResultSet。套路基本跟PYTHON的差不多。

需要注意的点是项目最好用maven来编译:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.39</version>
</dependency>

2.code

我照着写了个例子:

/**
 * Created by gqdw on 9/28/16.
 */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
public class MyTest {
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://xxx.27.199.225/myzb";
    static final String USER = "";
    static final String PASS = "";
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        try{
            Class.forName("com.mysql.jdbc.Driver1");
            System.out.println("connecting to database...");
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
            stmt = conn.createStatement();
            String sql0;
            sql0 = "select eventid,clock from events limit 10";
            ResultSet rs = stmt.executeQuery(sql0);
            while(rs.next()){
                Integer eventid = rs.getInt("eventid");
                Integer clock = rs.getInt("clock");
                System.out.println("eventid: " + eventid + "\t" + "clock:" + clock);
            }
            rs.close();
            stmt.close();
            conn.close();
        }catch(ClassNotFoundException ce){
            System.out.println("Class not found.");
            ce.printStackTrace();
            System.exit(1);
        }
        catch(SQLException se){
            se.printStackTrace();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
}

上传到gist了:
https://gist.github.com/gqdw/3fa9acf7f7fdb38b0fb1f04c6dfe0f96

mysql分区

2016-09-26

1.确认已安装

很多人动不动就想到要用分布式数据库，其实单库的性能随着MYSQL版本的提升越来越高，单表1000w数据应用优化的好的话用单库是完全没问题的。一个更优的做法是做表分区。也就是mysql partition。本文大量参考官网和。

这里以mysql5.7.15为例。确认有partition插件。

1 2	mysql> SHOW PLUGINS; partition \| ACTIVE \| STORAGE ENGINE \| NULL \| GPL

有partition就ok。
另外5.7有所不同的是mysql_install_db以后，会生成一个随便密码，在~/.mysql_secret,之后用

1
2
3

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
或者:
ALTER USER user IDENTIFIED BY 'new_password';

重置密码，然后就可以开始使用了。
5.7给我的感觉很不错。repo源很快，mysql被oracle买了以后运作的很规范，文档也是全的不行。

2.分区的类型

介绍常用的4中分区（当然不止这4个）:

RANGE分区
LIST分区
HASH分区
KEY分区

3.RANGE分区

很好理解，就是安装区域来分。

这里先看不用分区，

1
2
3

create table t1(
id INT
)engine=innodb

默认生成一个ibd文件。

1	t1.frm t1.ibd

create table t2(
id INT
)engine=innodb
partition by RANGE(id)(
partition p0 values less than (10),
partition p1 values less than (20));

t2用了分区，可以看到p0,p1分区信息的ibd文件。

1	t2.frm t2#P#p0.ibd t2#P#p1.ibd

再插入几条数据试试

1
2
3

insert into t2 select 9;
insert into t2 select 10;
insert into t2 select 15;

可以通过information_schema.partitions中的有相关分区信息:

mysql> select * from information_schema.partitions where table_name='t2'\G
*************************** 1. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: t2
               PARTITION_NAME: p0
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: id
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 10
                   TABLE_ROWS: 1
               AVG_ROW_LENGTH: 16384
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2016-09-26 15:34:03
                  UPDATE_TIME: 2016-09-26 15:34:52
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT:
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 2. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: t2
               PARTITION_NAME: p1
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 2
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: id
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 20
                   TABLE_ROWS: 2
               AVG_ROW_LENGTH: 8192
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2016-09-26 15:34:03
                  UPDATE_TIME: 2016-09-26 15:34:58
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT:
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
2 rows in set (0.01 sec)

但是这存在一个问题

1 2	mysql> insert into t2 select 30; ERROR 1526 (HY000): Table has no partition for value 30

插入30时即会报错，分区range没有涵盖所以条件，当插入30这个值的时候MYSQL不知道插入到哪。我们可以增加一个小于MAXVALUE的分区来涵盖所以的区域:

alter table t2
add partition (partition p2 values less than MAXVALUE);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> insert into t2 select 30;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> insert into t2 select 40;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

再插入遍不会报错了。

range分区主要用于日期列的分区。
mysql里面data type 格式:

Data Type	“Zero” Value
DATE	'0000-00-00'
TIME	'00:00:00'
DATETIME	'0000-00-00 00:00:00'
TIMESTAMP	'0000-00-00 00:00:00'
YEAR	0000

官网举了个例子

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

按joined日期来的YEAR函数来分区，

把上面的表格简化一下，变成t3:

CREATE TABLE t3 (
    firstname VARCHAR(25) NOT NULL,
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);
mysql> select * from t3;
+-----------+------------+
| firstname | joined     |
+-----------+------------+
| aaa       | 1955-01-01 |
| bbb       | 1965-01-01 |
| ccc       | 1966-02-02 |
| ccc       | 1967-03-03 |
| ccc       | 1977-03-03 |
| ccc       | 1987-03-03 |
+-----------+------------+

然后做select查询

mysql> select * from t3 where joined<'1969-12-30';
+-----------+------------+
| firstname | joined     |
+-----------+------------+
| aaa       | 1955-01-01 |
| bbb       | 1965-01-01 |
| ccc       | 1966-02-02 |
| ccc       | 1967-03-03 |
+-----------+------------+
4 rows in set (0.00 sec)
mysql> explain partitions select * from t3 where joined<'1969-12-30';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t3    | p0,p1      | ALL  | NULL          | NULL | NULL    | NULL |    4 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

year(‘1955-01-01’)返回1955，表格根据year做了分区，可以看到where子句只扫了p0,p1两个分区，提升了查询效率。这个叫partition pruning,官网地址 http://dev.mysql.com/doc/refman/5.7/en/partitioning-pruning.html

但并不是对所以的函数都能进行partition pruning,只是几个时间的函数，YEAR(),TO_DAYS(),TO_SECONDS(),UNIX_TIMESTAMP()这几个有效。

4.LIST分区

LIST分区跟RANGE特别类似，只是RANGE是连续的值，用less than 表达，LIST分区用VALUES IN(…)来表达，是离散的值。

create table t4(
a INT,
b INT)engine=innodb
partition by LIST(b)(
partition p0 values in (1,3,5,7,9),
partition p1 values in (2,4,6,8,0));

这时候你select * from t4 where b=1;那么只会扫p1分区。

5.HASH分区

hash目的将数据均匀分布到预设的分区中，保证各分区数据量大致一样。HASH分区也和之前的其他分区一样，需要制定分区的函数，以及HASH分区的个数。如下:

create table t_hash(
a INT,
b DATETIME) engine=innodb
partition by HASH (YEAR(b))
partitions 4;

上面根据YEAR的结果进行hash分区，不是很科学，最好根据自增字段进行分区，如下:

create table t_hash2(
a INT AUTO_INCREMENT,
b DATETIME,
PRIMARY KEY (a)) engine=innodb
partition by HASH(a)
partitions 4;

6.KEY分区

KEY分区跟HASH分区类似，不过HASH分区根据用户提供的函数进行分区，而KEY分区则使用MYSQL内部的散列函数进行分区。

create table t_key(
a INT,
b DATETIME) engine=innodb
partition by KEY(b)
partitions 4;

秦悦明的运维笔记