一次大表SQL查询优化历程

本文主要介绍了一次查询SQL查询性能优化的整个过程。

1 背景

有一张存储了订单数据的单表,为了防止表过大,需要删除历史的已达到终态的订单,只保留近期的数据以及历史的未达到终态的订单数据。这张表trade_order的大致结构是这样的(内容有缩减):

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE "trade_order" (
"id" int(10) unsigned NOT NULL AUTO_INCREMENT,
"order_no" varchar(30) NOT NULL DEFAULT '',
"update_time" datetime DEFAULT NULL COMMENT '订单更新时间',
"book_time" datetime DEFAULT NULL COMMENT '预订时间',
"gmt_create_time" datetime NOT NULL COMMENT 'DB数据创建时间',
"gmt_modify_time" datetime NOT NULL COMMENT 'DB数据更新时间',
"version" tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '乐观锁',
PRIMARY KEY ("id"),
UNIQUE KEY "order_no" ("order_no"),
KEY "update_time_state" ("update_time","state"),
KEY "idx_stbyway_bktime" ("state","buy_way","book_time","id")
)

Read More

Hystrix Dashborad数据介绍

从Hystrix DashboardData看ReativeX的使用

最近在将系统中服务降级框架Hystrix的运行时的一些指标数据接入到监控平台,需要获取Hystrix的Dashboard数据。发现用到了ReactiveX的知识,因此总结下。

注:本文基于的Hystrix的版本是:

1
2
3
4
5
6
<hystrix.version>1.5.7</hystrix.version>
<dependency>
<groupId>com.netflix.hystrix</groupId>
<artifactId>hystrix-core</artifactId>
<version>${hystrix.version}</version>
</dependency>

1 如何获取Hystrix的Dashboard数据

获取Hystrix的Dashboard数据比较简单,实现一个观察者即可接收Dashboard数据,如下代码:

1
2
3
4
5
6
7
8
9
10
Observable<HystrixDashboardStream.DashboardData>
dashboardDataObservable = HystrixDashboardStream.getInstance().observe();
dashboardDataObservable.subscribe(new Action1<HystrixDashboardStream.DashboardData>() {
@Override
public void call(HystrixDashboardStream.DashboardData dashboardData) {
//do something
}
});

2 HystrixDashboard设计

Read More

InfluxDB写入失败-partial write: max-values-per-tag limit exceeded

最近线上向InfluxDB写入数据突然报错,大概意思是tag的值超限,详细错误信息:

1
2
3
4
5
6
7
8
9
10
11
2016-12-02 20:47:11.485 [] [] [] ERROR c.y.t.p.m.m.i.OrderCreateMonitorManageImpl - InfluxDB写入异常!influxDO=InfluxDO(measurement=com.XXX.trade.create.request.result, timeStamp=1480682831481, tagKeyList=[cluster, host, count_time, count, count_type], tagValList=[DEFAULT_CLUSTER, xxx-inc.com, 1480682831481, 3, bill_new], fieldKeyList=[result, cost], fieldValList=[true, 112]), exception={}
java.lang.RuntimeException: {"error":"partial write: max-values-per-tag limit exceeded (100000/100000): measurement=\"com.XXX.trade.create.request.result\" tag=\"count_time\" value=\"count_time\" dropped=1"}
at org.influxdb.impl.InfluxDBImpl.execute(InfluxDBImpl.java:266) ~[influxdb-java-2.4.jar:na]
at org.influxdb.impl.InfluxDBImpl.write(InfluxDBImpl.java:167) ~[influxdb-java-2.4.jar:na]
at org.influxdb.impl.InfluxDBImpl.write(InfluxDBImpl.java:157) ~[influxdb-java-2.4.jar:na]
at com.XXX.trade.process.monitor.dal.impl.InfluxDBDAOImpl.insert(InfluxDBDAOImpl.java:104) ~[trade-process-service-1.0.0-SNAPSHOT.jar:na]
at com.XXX.trade.process.monitor.manage.impl.OrderCreateMonitorManageImpl.save(OrderCreateMonitorManageImpl.java:40) ~[trade-process-service-1.0.0-SNAPSHOT.jar:na]
at com.XXX.trade.process.nsq.processor.impl.MonitorProcessor.process(MonitorProcessor.java:69) [trade-process-service-1.0.0-SNAPSHOT.jar:na]
at com.XXX.trade.process.nsq.NSQSubscriber.lambda$init$0(NSQSubscriber.java:71) [trade-process-service-1.0.0-SNAPSHOT.jar:na]
at com.XXX.trade.process.nsq.NSQSubscriber$$Lambda$8/1829287142.process(Unknown Source) [trade-process-service-1.0.0-SNAPSHOT.jar:na]

Google搜索了下,这是InfluxDB v1.1.0 [2016-11-14]版本新加的一个特性(CHANGELOG.md):

Read More

RocketMQ源码学习之四-PushConsumer

本文主要讲解RocketMQ的push消息消费者的实现原理。

1 RocketMQ的两种消息消费者

RocketMQ支持两种形式的消息消费者:

  • Push Consumer
    通常是应用向Consumer对象注册一个Listener,一旦收到消息,Consumer对象立即回调Listener接口方法。底层采用的是Pull长轮询拉取消息的方式。
  • Pull Consumer
    应用通常主动调用Consumer的拉消息方法从Broker拉消息,主动权由应用控制。

2 Push Consumer的一个demo

Read More

RocketMQ源码学习之三-异步消息发送

本文主要介绍RocketMQ异步消息发送的原理。

1 异步消息发送Demo

首先看一段RocketMQ异步消息发送的Demo代码:
消息生产者:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
import com.alibaba.rocketmq.client.exception.MQClientException;
import com.alibaba.rocketmq.client.producer.DefaultMQProducer;
import com.alibaba.rocketmq.common.message.Message;
import com.SpringTest.rocketmq.callback.MySendCallback;
public class Producer3 {
public static void main(String[] args) throws MQClientException, InterruptedException {
DefaultMQProducer producer = new DefaultMQProducer("ProducerGroupName");
producer.setNamesrvAddr("192.168.2.104:9876");
producer.setInstanceName("Producer3");
producer.start();
for (int i = 0; i < 1; i++)
try {
{
Message msg = new Message("TopicTest",// topic
"TagA",// tag
"OrderID188",// key
("Hello MetaQ").getBytes());// body
producer.send(msg, new MySendCallback());
}
}
catch (Exception e) {
e.printStackTrace();
}
producer.shutdown();
}
}

Read More

RocketMQ源码学习之二-同步消息发送

本文讲述RocketMQ同步发送消息的原理。

1 消息生产者demo

先看一段同步发送消息的demo代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
import com.alibaba.rocketmq.client.exception.MQClientException;
import com.alibaba.rocketmq.client.producer.DefaultMQProducer;
import com.alibaba.rocketmq.client.producer.SendResult;
import com.alibaba.rocketmq.common.message.Message;
public class Producer1 {
public static void main(String[] args) throws MQClientException, InterruptedException {
DefaultMQProducer producer = new DefaultMQProducer("ProducerGroupName");
producer.setNamesrvAddr("192.168.2.101:9876");
producer.setInstanceName("Producer1");
producer.start();
for (int i = 0; i < 1; i++)
try {
{
Message msg = new Message("TopicTest",// topic
"TagA",// tag
"OrderID188",// key
("Hello MetaQ").getBytes());// body
SendResult sendResult = producer.send(msg);
System.out.println(sendResult);
}
}
catch (Exception e) {
e.printStackTrace();
}
producer.shutdown();
}
}

Read More

RocketMQ源码学习之一-MAC单机环境搭建

本文讲述如何在单机(MAC)上搭建RocketMQ环境,实现正常收发消息。

1 安装JDK、maven等工具

忽略。

2 部署RocketMQ服务端

https://github.com/alibaba/RocketMQ下载压缩包,并解压。

Read More

MySQL的日期时间类型关于秒以下精度的处理

1 MySQL中日期和时间的表示

MySQL中有多种数据类型可以用于日期和时间的表示:

Read More

技术分享-MySQL InnoDB locks and deadlocks

本文是在组内技术分享的ppt:《MySQL InnoDB locks and deadlocks》。与大家分享,如有问题,欢迎来信交流。

Read More