# 与 SQL 对比
下面我将以 Prometheus server 收集的 `http_requests_total` 时序数据为例子展开对比。
## MySQL 数据准备
```
mysql>
# 创建数据库
create database prometheus_practice;
use prometheus_practice;
# 创建 http_requests_total 表
CREATE TABLE http_requests_total (
code VARCHAR(256),
handler VARCHAR(256),
instance VARCHAR(256),
job VARCHAR(256),
method VARCHAR(256),
created_at DOUBLE NOT NULL,
value DOUBLE NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE http_requests_total ADD INDEX created_at_index (created_at);
# 初始化数据
# time at 2017/5/22 14:45:27
INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("200", "query_range", "localhost:9090", "prometheus", "get", 1495435527, 3);
INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("400", "query_range", "localhost:9090", "prometheus", "get", 1495435527, 5);
INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("200", "prometheus", "localhost:9090", "prometheus", "get", 1495435527, 6418);
INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("200", "static", "localhost:9090", "prometheus", "get", 1495435527, 9);
INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("304", "static", "localhost:9090", "prometheus", "get", 1495435527, 19);
INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("200", "query", "localhost:9090", "prometheus", "get", 1495435527, 87);
INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("400", "query", "localhost:9090", "prometheus", "get", 1495435527, 26);
INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("200", "graph", "localhost:9090", "prometheus", "get", 1495435527, 7);
INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("200", "label_values", "localhost:9090", "prometheus", "get", 1495435527, 7);
# time at 2017/5/22 14:48:27
INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("200", "query_range", "localhost:9090", "prometheus", "get", 1495435707, 3);
INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("400", "query_range", "localhost:9090", "prometheus", "get", 1495435707, 5);
INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("200", "prometheus", "localhost:9090", "prometheus", "get", 1495435707, 6418);
INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("200", "static", "localhost:9090", "prometheus", "get", 1495435707, 9);
INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("304", "static", "localhost:9090", "prometheus", "get", 1495435707, 19);
INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("200", "query", "localhost:9090", "prometheus", "get", 1495435707, 87);
INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("400", "query", "localhost:9090", "prometheus", "get", 1495435707, 26);
INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("200", "graph", "localhost:9090", "prometheus", "get", 1495435707, 7);
INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("200", "label_values", "localhost:9090", "prometheus", "get", 1495435707, 7);
```
数据初始完成后，通过查询可以看到如下数据：
```
mysql>
mysql> select * from http_requests_total;
+------+--------------+----------------+------------+--------+------------+-------+
| code | handler | instance | job | method | created_at | value |
+------+--------------+----------------+------------+--------+------------+-------+
| 200 | query_range | localhost:9090 | prometheus | get | 1495435527 | 3 |
| 400 | query_range | localhost:9090 | prometheus | get | 1495435527 | 5 |
| 200 | prometheus | localhost:9090 | prometheus | get | 1495435527 | 6418 |
| 200 | static | localhost:9090 | prometheus | get | 1495435527 | 9 |
| 304 | static | localhost:9090 | prometheus | get | 1495435527 | 19 |
| 200 | query | localhost:9090 | prometheus | get | 1495435527 | 87 |
| 400 | query | localhost:9090 | prometheus | get | 1495435527 | 26 |
| 200 | graph | localhost:9090 | prometheus | get | 1495435527 | 7 |
| 200 | label_values | localhost:9090 | prometheus | get | 1495435527 | 7 |
| 200 | query_range | localhost:9090 | prometheus | get | 1495435707 | 3 |
| 400 | query_range | localhost:9090 | prometheus | get | 1495435707 | 5 |
| 200 | prometheus | localhost:9090 | prometheus | get | 1495435707 | 6418 |
| 200 | static | localhost:9090 | prometheus | get | 1495435707 | 9 |
| 304 | static | localhost:9090 | prometheus | get | 1495435707 | 19 |
| 200 | query | localhost:9090 | prometheus | get | 1495435707 | 87 |
| 400 | query | localhost:9090 | prometheus | get | 1495435707 | 26 |
| 200 | graph | localhost:9090 | prometheus | get | 1495435707 | 7 |
| 200 | label_values | localhost:9090 | prometheus | get | 1495435707 | 7 |
+------+--------------+----------------+------------+--------+------------+-------+
18 rows in set (0.00 sec)
```
## 基本查询对比
假设当前时间为 2017/5/22 14:48:30
* 查询当前所有数据
```
// PromQL
http_requests_total
// MySQL
SELECT * from http_requests_total WHERE created_at BETWEEN 1495435700 AND 1495435710;
```
我们查询 MySQL 数据的时候，需要将当前时间向前推一定间隔，比如这里的 10s (Prometheus 数据抓取间隔)，这样才能确保查询到数据，而 PromQL 自动帮我们实现了这个逻辑。
* 条件查询
```
// PromQL
http_requests_total{code="200", handler="query"}
// MySQL
SELECT * from http_requests_total WHERE code="200" AND handler="query" AND created_at BETWEEN 1495435700 AND 1495435710;
```
* 模糊查询: code 为 2xx 的数据
```
// PromQL
http_requests_total{code~="2xx"}
// MySQL
SELECT * from http_requests_total WHERE code LIKE "%2%" AND created_at BETWEEN 1495435700 AND 1495435710;
```
* 比较查询: value 大于 100 的数据
```
// PromQL
http_requests_total > 100
// MySQL
SELECT * from http_requests_total WHERE value > 100 AND created_at BETWEEN 1495435700 AND 1495435710;
```
* 范围区间查询: 过去 5 分钟数据
```
// PromQL
http_requests_total[5m]
// MySQL
SELECT * from http_requests_total WHERE created_at BETWEEN 1495435410 AND 1495435710;
```
## 聚合, 统计高级查询
* count 查询: 统计当前记录总数
```
// PromQL
count(http_requests_total)
// MySQL
SELECT COUNT(*) from http_requests_total WHERE created_at BETWEEN 1495435700 AND 1495435710;
```
* sum 查询: 统计当前数据总值
```
// PromQL
sum(http_requests_total)
// MySQL
SELECT SUM(value) from http_requests_total WHERE created_at BETWEEN 1495435700 AND 1495435710;
```
* avg 查询: 统计当前数据平均值
```
// PromQL
avg(http_requests_total)
// MySQL
SELECT AVG(value) from http_requests_total WHERE created_at BETWEEN 1495435700 AND 1495435710;
```
* top 查询: 查询最靠前的 3 个值
```
// PromQL
topk(3, http_requests_total)
// MySQL
SELECT * from http_requests_total WHERE created_at BETWEEN 1495435700 AND 1495435710 ORDER BY value DESC LIMIT 3;
```
* irate 查询，过去 5 分钟平均每秒数值
```
// PromQL
irate(http_requests_total[5m])
// MySQL
SELECT code, handler, instance, job, method, SUM(value)/300 AS value from http_requests_total WHERE created_at BETWEEN 1495435700 AND 1495435710 GROUP BY code, handler, instance, job, method;
```
## 总结
通过以上一些示例可以看出，在常用查询和统计方面，PromQL 比 MySQL 简单和丰富很多，而且查询性能也高不少。
- 前言
- 修订记录
- 如何贡献
- Prometheus 简介
- Prometheus 是什么？
- 为什么选择 Prometheus？
- Prometheus 安装
- 二进制包安装
- Docker 安装
- 基础概念
- 数据模型
- Metric types
- 作业与实例
- PromQL
- PromQL 基本使用
- 与 SQL 对比
- 数据可视化
- Web Console
- Grafana
- Prometheus 配置
- 全局配置
- 告警配置
- 规则配置
- 数据拉取配置
- 远程可写存储
- 远程可读存储
- 服务发现
- 配置样例
- Exporter
- 文本格式
- Golang Sample Exporter
- Python Sample Exporter
- Node Exporter 安装使用
- Node Exporter 常用查询
- 其他 Exporter 介绍
- Pushgateway
- Pushgateway 是什么？
- 如何使用 Pushgateway?
- 数据存储
- Memory Store
- Local Store
- Remote Store
- Rule
- 如何配置
- Rule 触发逻辑
- Aleretmanager
- Aleretmanager 是什么？
- 如何实现告警分组和去噪
- 通过 Email 接收告警
- 通过 OneAlert 管理告警
- 通过 Webhooks 接收告警
- 其他告警接收方案
- 使用 Prometheus 实现主机运行状态监控的完整演示
- Target 配置
- Rule 配置
- Alertmanager 配置
- 演示功能
- Prometheus Tool
- Promu 介绍和使用
- Client SDK
- Prometheus 性能调优
- 通过 Metrics 查看 Prometheus 运行状态
- 通过日志分析 Prometheus 运行状态
- 通过调整启动参数优化性能
- Prometheus 与 JVM 监控
- JVM Exporter 安装
- JVM 数据查询
- Prometheus 与容器监控
- Docker 监控
- Rocket 监控
- Prometheus 与容器编排
- Kubernetes
- Docker Swarm
- Prometheus 与 DevOps
- 如何从 0 开发一个 exporter
- 使用 Webhooks 开发一个 alert receiver
- 产品化
- 高可用方案探讨
- 集群方案
- 主从方案
- v2.0 功能洞见
- 新功能
- 新存储架构
- 常见问题收录
- 如何热加载新配置？
- 为什么重启 Prometheus 过后，数据无法查询？
- 如何删除 Pushgateway 的数据？
- 为什么内存使用这么高？
- 为什么有数据丢失？
- Prometheus 如何通过认证后拉取数据？