标准SQL下分区函数Partition By结合row_number()的用法,以及排序rank()的用法详解(转)

 数据库  标准SQL下分区函数Partition By结合row_number()的用法,以及排序rank()的用法详解(转)已关闭评论
10月 222020
 
网上看到的一篇文章: 描述分区函数Partition By结合row_number()的用法,以及排序rank()的用法
,比如获取分组(分区)中前几条记录。

partition by关键字是分析性函数的一部分,它和聚合函数不同的地方在于它能返回一个分组中的多条记录,而聚合函数一般只有一条反映统计值的记录,partition by用于给结果集分组,如果没有指定那么它把整个结果集作为一个分组,分区函数一般与排名函数一起使用。

准备测试数据:

复制代码
create table Student  --学生成绩表
(
 id int,  --主键
 Grade int, --班级
 Score int --分数
)
go

insert into Student values(1,1,88)
insert into Student values(2,1,66)
insert into Student values(3,1,75)
insert into Student values(4,2,30)
insert into Student values(5,2,70)
insert into Student values(6,2,80)
insert into Student values(7,2,60)
insert into Student values(8,3,90)
insert into Student values(9,3,70)
insert into Student values(10,3,80)
insert into Student values(11,3,80)
复制代码

一、分区函数Partition By的与row_number()的用法

1、不分班按学生成绩排名

select *,row_number() over(order by Score desc) as Sequence from Student

执行结果:

2、分班后按学生成绩排名

select *,row_number() over(partition by Grade order by Score desc) as Sequence from Student

执行结果:

3、获取每个班的前1(几)名

select * from
(
select *,row_number() over(partition by Grade order by Score desc) as Sequence from Student
)T where T.Sequence<=1

执行结果:

 

二、分区函数Partition By与排序rank()的用法

1、分班后按学生成绩排名 该语句是对分数相同的记录进行了同一排名,例如:两个80分的并列第2名,第4名就没有了

select *,rank() over(partition by Grade order by Score desc) as Sequence from Student

执行结果:

2、获取每个班的前2(几)名 该语句是对分数相同的记录进行了同一排名,例如:两个80分的并列第2名,第4名就没有了

select * from
(
select *,rank() over(partition by Grade order by Score desc) as Sequence from Student
)T where T.Sequence<=2

执行结果:

9个提升逼格的redis命令(keys,scan,slowlog,rename-command,bigkeys,monitor,info,config,set)

 redis  9个提升逼格的redis命令(keys,scan,slowlog,rename-command,bigkeys,monitor,info,config,set)已关闭评论
9月 082020
 

9个提升逼格的redis命令

非常好的文章,特别是使用bigkeys查找占大内存的key真是方便,推荐https://www.jianshu.com/p/4df5f2356de9

keys

我把这个命令放在第一位,是因为笔者曾经做过的项目,以及一些朋友的项目,都因为使用keys这个命令,导致出现性能毛刺。这个命令的时间复杂度是O(N),而且redis又是单线程执行,在执行keys时即使是时间复杂度只有O(1)例如SET或者GET这种简单命令也会堵塞,从而导致这个时间点性能抖动,甚至可能出现timeout。

强烈建议生产环境屏蔽keys命令(后面会介绍如何屏蔽)。

scan

既然keys命令不允许使用,那么有什么代替方案呢?有!那就是scan命令。如果把keys命令比作类似select * from users where username like '%afei%'这种SQL,那么scan应该是select * from users where id>? limit 10这种命令。

官方文档用法如下:

SCAN cursor [MATCH pattern] [COUNT count]

初始执行scan命令例如scan 0。SCAN命令是一个基于游标的迭代器。这意味着命令每次被调用都需要使用上一次这个调用返回的游标作为该次调用的游标参数,以此来延续之前的迭代过程。当SCAN命令的游标参数被设置为0时,服务器将开始一次新的迭代,而当redis服务器向用户返回值为0的游标时,表示迭代已结束,这是唯一迭代结束的判定方式,而不能通过返回结果集是否为空判断迭代结束。

使用方式:

127.0.0.1:6380> scan 0
1) "22"
2)  1) "23"
    2) "20"
    3) "14"
    4) "2"
    5) "19"
    6) "9"
    7) "3"
    8) "21"
    9) "12"
   10) "25"
   11) "7"

返回结果分为两个部分:第一部分即1)就是下一次迭代游标,第二部分即2)就是本次迭代结果集。

slowlog

上面提到不能使用keys命令,如果就有开发这么做了呢,我们如何得知?与其他任意存储系统例如mysql,mongodb可以查看慢日志一样,redis也可以,即通过命令slowlog。用法如下:

SLOWLOG subcommand [argument]

subcommand主要有:

  • get,用法:slowlog get [argument],获取argument参数指定数量的慢日志。
  • len,用法:slowlog len,总慢日志数量。
  • reset,用法:slowlog reset,清空慢日志。

执行结果如下:

127.0.0.1:6380> slowlog get 5
1) 1) (integer) 2
   2) (integer) 1532656201
   3) (integer) 2033
   4) 1) "flushddbb"
2) 1) (integer) 1  ----  慢日志编码,一般不用care
   2) (integer) 1532646897  ----  导致慢日志的命令执行的时间点,如果api有timeout,可以通过对比这个时间,判断可能是慢日志命令执行导致的
   3) (integer) 26424  ----  导致慢日志执行的redis命令,通过4)可知,执行config rewrite导致慢日志,总耗时26ms+
   4) 1) "config"
      2) "rewrite"

命令耗时超过多少才会保存到slowlog中,可以通过命令config set slowlog-log-slower-than 2000配置并且不需要重启redis。注意:单位是微妙,2000微妙即2毫秒。

rename-command

为了防止把问题带到生产环境,我们可以通过配置文件重命名一些危险命令,例如keys等一些高危命令。操作非常简单,只需要在conf配置文件增加如下所示配置即可:

rename-command flushdb flushddbb
rename-command flushall flushallall
rename-command keys keysys

bigkeys

随着项目越做越大,缓存使用越来越不规范。我们如何检查生产环境上一些有问题的数据。bigkeys就派上用场了,用法如下:

redis-cli -p 6380 --bigkeys

执行结果如下:

... ...
-------- summary -------

Sampled 526 keys in the keyspace!
Total key length in bytes is 1524 (avg len 2.90)

Biggest string found 'test' has 10005 bytes
Biggest   list found 'commentlist' has 13 items

524 strings with 15181 bytes (99.62% of keys, avg size 28.97)
2 lists with 19 items (00.38% of keys, avg size 9.50)
0 sets with 0 members (00.00% of keys, avg size 0.00)
0 hashs with 0 fields (00.00% of keys, avg size 0.00)
0 zsets with 0 members (00.00% of keys, avg size 0.00)

最后5行可知,没有set,hash,zset几种数据结构的数据。string类型有524个,list类型有两个;通过Biggest ... ...可知,最大string结构的key是test,最大list结构的key是commentlist

需要注意的是,这个bigkeys得到的最大,不一定是最大。说明原因前,首先说明bigkeys的原理,非常简单,通过scan命令遍历,各种不同数据结构的key,分别通过不同的命令得到最大的key:

  • 如果是string结构,通过strlen判断;
  • 如果是list结构,通过llen判断;
  • 如果是hash结构,通过hlen判断;
  • 如果是set结构,通过scard判断;
  • 如果是sorted set结构,通过zcard判断。

正因为这样的判断方式,虽然string结构肯定可以正确的筛选出最占用缓存,也可以说最大的key。但是list不一定,例如,现在有两个list类型的key,分别是:numberlist–[0,1,2],stringlist–[“123456789123456789”],由于通过llen判断,所以numberlist要大于stringlist。而事实上stringlist更占用内存。其他三种数据结构hash,set,sorted set都会存在这个问题。使用bigkeys一定要注意这一点。

monitor

假设生产环境没有屏蔽keys等一些高危命令,并且slowlog中还不断有新的keys导致慢日志。那我们如何揪出这些命令是由谁执行的呢?这就是monitor的用处,用法如下:

redis-cli -p 6380 monitor

如果当前redis环境OPS比较高,那么建议结合linux管道命令优化,只输出keys命令的执行情况:

[[email protected] ~]# redis-cli -p 6380 monitor | grep keys 
1532645266.656525 [0 10.0.0.1:43544] "keyss" "*"
1532645287.257657 [0 10.0.0.1:43544] "keyss" "44*"

执行结果中很清楚的看到keys命名执行来源。通过输出的IP和端口信息,就能在目标服务器上找到执行这条命令的进程,揪出元凶,勒令整改。

info

如果说哪个命令能最全面反映当前redis运行情况,那么非info莫属。用法如下:

INFO [section]

section可选值有:

  • Server:运行的redis实例一些信息,包括:redis版本,操作系统信息,端口,GCC版本,配置文件路径等;
  • Clients:redis客户端信息,包括:已连接客户端数量,阻塞客户端数量等;
  • Memory:使用内存,峰值内存,内存碎片率,内存分配方式。这几个参数都非常重要;
  • Persistence:AOF和RDB持久化信息;
  • Stats:一些统计信息,最重要三个参数:OPS(instantaneous_ops_per_sec),keyspace_hitskeyspace_misses两个参数反应缓存命中率;
  • Replication:redis集群信息;
  • CPU:CPU相关信息;
  • Keyspace:redis中各个DB里key的信息;

config

config是一个非常有价值的命令,主要体现在对redis的运维。因为生产环境一般是不允许随意重启的,不能因为需要调优一些参数就修改conf配置文件并重启。redis作者早就想到了这一点,通过config命令能热修改一些配置,不需要重启redis实例,可以通过如下命令查看哪些参数可以热修改:

config get *

热修改就比较容易了,执行如下命令即可:

config set 

例如:config set slowlog-max-len 100config set maxclients 1024

这样修改的话,如果以后由于某些原因redis实例故障需要重启,那通过config热修改的参数就会被配置文件中的参数覆盖,所以我们需要通过一个命令将config热修改的参数刷到redis配置文件中持久化,通过执行如下命令即可:

config rewrite

执行该命令后,我们能在config文件中看到类似这种信息:

# 如果conf中本来就有这个参数,通过执行config set,那么redis直接原地修改配置文件
maxclients 1024
# 如果conf中没有这个参数,通过执行config set,那么redis会追加在Generated by CONFIG REWRITE字样后面
# Generated by CONFIG REWRITE
save 600 60
slowlog-max-len 100

set

set命令也能提升逼格?是的,我本不打算写这个命令,但是我见过太多人没有完全掌握这个命令,官方文档介绍的用法如下:

SET key value [EX seconds] [PX milliseconds] [NX|XX]

你可能用的比较多的就是set key value,或者SETEX key seconds value,所以很多同学用redis实现分布式锁分为两步:首先执行SETNX key value,然后执行EXPIRE key seconds。很明显,这种实现有很严重的问题,因为两步执行不具备原子性,如果执行第一个命令后出现某些未知异常导致无法执行EXPIRE key seconds,那么分布式锁就会一直无法得到释放。

通过SET命令实现分布式锁的正式姿势应该是SET key value EX seconds NX(EX和PX任选,取决于对过期时间精度要求)。另外,value也有要求,最好是一个类似UUID这种具备唯一性的字符串。当然如果问你redis是否还有其他实现分布式锁的方案。你能说出redlock,那对方一定眼前一亮,心里对你竖起大拇指,但嘴上不会说。

redis数据迁移的3个方法

 redis  redis数据迁移的3个方法已关闭评论
1月 172020
 

1. rdb数据备份恢复方法
redis 127.0.0.1:6379> SAVE
OK
或者
redis-cli -h 127.0.0.1 -p 6379 -a pwd bgsave
该命令将在 redis 安装目录中创建dump.rdb文件。

查找dump.rdb文件位置
redis 127.0.0.1:6379> CONFIG GET dir
1) dir
2) /usr/local/redis/bin
以上命令 CONFIG GET dir 输出的 redis 安装目录为 /usr/local/redis/bin
bgsave
创建 redis 备份文件也可以使用命令 BGSAVE,该命令在后台执行。
127.0.0.1:6379> BGSAVE
Background saving started

2. AOF数据备份恢复方法
另一种持久化方式AOF,在配置文件中打开[appendonly yes]。
AOF刷新日志到disk的规则:
appendfsync always #always 表示每次有写操作都进行同步,非常慢,非常安全。
appendfsync everysec #everysec表示对写操作进行累积,每秒同步一次
官方的建议的everysec,安全,就是速度不够快,如果是机器出现问题可能会丢失1秒的数据。

也可以手动执行bgrewriteaof进行AOF备份:
redis-cli -h 127.0.0.1 -p 6379 -a pwd bgrewriteaof

迁移数据恢复
迁移到另外一台恢复数据,需先检查配置文件,将按照以下优先级恢复数据到内存:
如果只配置AOF,重启时加载AOF文件恢复数据;
如果同时 配置了RBDAOF,启动是只加载AOF文件恢复数据;
如果只配置RBD,启动是讲加载dump文件恢复数据;

dump.rdb或者AOF文件迁移到另外一台恢复数据
恢复数据,只需将备份文件 (dump.rdb或者AOF文件) 移动到 redis 安装目录并启动服务即可。

3. redis从库复制数据方法
Redis提供了复制(replication)功能可以自动实现同步的过程。
配置方法
通过配置文件 从数据库的配置文件中加入slaveof master-ip master-port,主数据库无需配置
通过命令行参数 启动redis-server的时候,使用命令行参数–slaveof master-ip master port
redis-server –port 6380 –slaveof 127.0.0.1 6379
通过命令SLAVEOF master-ip master-port
redis>SLAVEOF 127.0.0.1 6379
SLAVEOF NO ONE可以是当前数据库停止接收其他数据库的同步,转成主Redis数据库,程序连接地址都改为新的redisIP地址和端口。

转自:https://my.oschina.net/ppabvc/blog/819448

mac 下 redis 安装、redis.conf位置、redis-server后台运行

 redis  mac 下 redis 安装、redis.conf位置、redis-server后台运行已关闭评论
11月 122019
 

mac下安装redis命令:

brew install redis

 

如果没有安装homebrew,可以打开一个终端,输入下面命令安装:

/usr/bin/ruby -e “$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)”

 

启动:

redis-server 命令直接启动会运行在一个终端里,关闭终端服务就停止了,也没有运行参数可以后台运行,但可以通过修改配置文件参数实现。

mac下brew安装的redis的配置文件默认所在位置: /usr/local/etc/redis.conf

修改: 将 daemonize no 修改为 daemonize yes

 

再启动

redis-server /usr/local/etc/redis.conf

redis就会后台运行了

 

如果要开机启动,使用下面命令:

ln -sfv /usr/local/opt/redis/*.plist ~/Library/LaunchAgents

launchctl load ~/Library/LaunchAgents/homebrew.mxcl.redis.plist

brew services start redis

MYSQL中 INSERT…. ON DUPLICATE KEY UPDATE重复插入时更新及REPLACE讲解

 mysql  MYSQL中 INSERT…. ON DUPLICATE KEY UPDATE重复插入时更新及REPLACE讲解已关闭评论
9月 052019
 

个人总结:

INSERT…. ON DUPLICATE KEY UPDATE 相当于 INSERT + UPDATE的结合体

REPLACE 相当于 DELETE + INSERT的结合体

 

mysql当插入重复时更新的方法:

第一种方法:

 

示例一:插入多条记录

假设有一个主键为 client_id 的 clients 表,可以使用下面的语句:

 

Sql代码

INSERT INTO clients

(client_id,client_name,client_type)

SELECT supplier_id,supplier_name,‘advertising’

FROM suppliers

WHERE not exists(select * from clients where clients.client_id=suppliers.supplier_id);

 

示例一:插入单条记录

 

Sql代码

INSERT INTO clients

(client_id,client_name,client_type)

SELECT 10345,‘IBM’,‘advertising’

FROM dual

WHERE not exists (select * from clients where clients.client_id=10345);

 

使用 dual 做表名可以让你在 select 语句后面直接跟上要插入字段的值,即使这些值还不存在当前表中。

第二种方法:

 

INSERT 中ON DUPLICATE KEY UPDATE的使用(本文重点)

如果您指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE。例如,如果列a被定义为UNIQUE,并且包含值1,则以下两个语句具有相同的效果:

 

Sql代码

mysql>INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

mysql>UPDATE table SET c=c+1 WHERE a=1;

 

如果行作为新记录被插入,则受影响行的值为1;如果原有的记录被更新,则受影响行的值为2。

注释:如果列b也是唯一列,则INSERT与此UPDATE语句相当:

 

Sql代码

mysql>UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

 

如果a=1 OR b=2与多个行向匹配,则只有一个行被更新。通常,您应该尽量避免对带有多个唯一关键字的表使用ON DUPLICATE KEY子句。

您可以在UPDATE子句中使用VALUES(col_name)函数从INSERT…UPDATE语句的INSERT部分引用列值。换句话说,如果没有发生重复关键字冲突,则UPDATE子句中的VALUES(col_name)可以引用被插入的col_name的值。本函数特别适用于多行插入。VALUES()函数只在INSERT…UPDATE语句中有意义,其它时候会返回NULL。

示例:

 

Sql代码

mysql>INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)

ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

 

本语句与以下两个语句作用相同:

 

Sql代码

mysql>INSERT INTO table (a,b,c) VALUES (1,2,3)

ON DUPLICATE KEY UPDATE c=3;

mysql>INSERT INTO table (a,b,c) VALUES (4,5,6)

ON DUPLICATE KEY UPDATE c=9;

 

当您使用ON DUPLICATE KEY UPDATE时,DELAYED选项被忽略。

第三种方法:

 

REPLACE语句

我们在使用数据库时可能会经常遇到这种情况。如果一个表在一个字段上建立了唯一索引,当我们再向这个表中使用已经存在的键值插入一条记录,那将会抛出一个主键冲突的错误。当然,我们可能想用新记录的值来覆盖原来的记录值。如果使用传统的做法,必须先使用DELETE语句删除原先的记录,然后再使用INSERT插入新的记录。而在MySQL中为我们提供了一种新的解决方案,这就是REPLACE语句。使用REPLACE插入一条记录时,如果不重复,REPLACE就和INSERT的功能一样,如果有重复记录,REPLACE就使用新记录的值来替换原来的记录值。

使用REPLACE的最大好处就是可以将DELETE和INSERT合二为一,形成一个原子操作。这样就可以不必考虑在同时使用DELETE和INSERT时添加事务等复杂操作了。

在使用REPLACE时,表中必须有唯一索引,而且这个索引所在的字段不能允许空值,否则REPLACE就和INSERT完全一样的。

在执行REPLACE后,系统返回了所影响的行数,如果返回1,说明在表中并没有重复的记录,如果返回2,说明有一条重复记录,系统自动先调用了DELETE删除这条记录,然后再记录用INSERT来插入这条记录。如果返回的值大于2,那说明有多个唯一索引,有多条记录被删除和插入。

REPLACE的语法和INSERT非常的相似,如下面的REPLACE语句是插入或更新一条记录。

REPLACE INTO users (id,name,age) VALUES(123, ‘赵本山’, 50);

 

 

插入多条记录:

REPLACE INTO users(id, name, age)

Sql代码

VALUES(123, ‘赵本山’, 50), (134,‘Mary’,15);

REPLACE也可以使用SET语句

REPLACE INTO users SET id = 123, name = ‘赵本山’, age = 50;

 

上面曾提到REPLACE可能影响3条以上的记录,这是因为在表中有超过一个的唯一索引。在这种情况下,REPLACE将考虑每一个唯一索引,并对每一个索引对应的重复记录都删除,然后插入这条新记录。假设有一个table1表,有3个字段a, b, c。它们都有一个唯一索引。

CREATE TABLE table1(a INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE);

 

假设table1中已经有了3条记录

a b c

1 1 1

2 2 2

3 3 3

下面我们使用REPLACE语句向table1中插入一条记录。

REPLACE INTO table1(a, b, c) VALUES(1,2,3);

 

返回的结果如下

Query OK, 4 rows affected (0.00 sec)

在table1中的记录如下

a b c

1 2 3

 

转自: https://www.iteye.com/blog/lobert-1604122

CentOS 7 下安装 MySQL 5.7

 centos, mysql  CentOS 7 下安装 MySQL 5.7已关闭评论
8月 212019
 

原文链接:https://blog.csdn.net/u011886447/article/details/79796802

从 CentOS 7 系统开始,MariaDB 成为 yum 源中默认的数据库安装包。在 CentOS 7 及以上的系统中使用 yum 安装 MySQL 包将无法使用 MySQL。您可以选择使用完全兼容的 MariaDB,或依照本文介绍配置来继续使用 MySQL。本文以在 CentOS 7 下安装 MySQL 5.7.21 为例。

 

  1. 检查 MariaDB 是否安装

yum list installed | grep mariadb

 

 

 

  1. 卸载全部MariaDB 相关

yum -y remove mariadb*

 

  1. 下载 MySQL 的 YUM 源

进入到要下载到的路径:cd /usr/local/src

 

下载:wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

 

  1. 安装MySQL 的 YUM 源

rpm -ivh mysql57-community-release-el7-11.noarch.rpm

 

  1. 检查MySQL 的 YUM 源是否安装成功

yum repolist enabled | grep “mysql.*-community.*”

 

 

 

如图所示则安装成功。

 

  1. 查看 MySQL 版本

yum repolist all | grep mysql

 

 

 

  1. 安装 MySQL

yum install mysql-community-server

 

一直输 y 就可以了。

 

  1. 启动 MySQL 服务

systemctl start mysqld

 

  1. 测试连接 MySQL 服务

mysql -u root 或者 mysql

 

——————————————————————————–

 

提示:

 

刚安装的 MySQL 是没有密码的,这时如果出现:

 

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO),解决如下:

 

① 停止 MySQL 服务:systemctl stop mysqld

 

② 以不检查权限的方式启动 MySQL: mysqld –user=root –skip-grant-tables &

 

③ 再次输入 mysql -u root 或者 mysql,这次就可以进来了。

 

④ 更新密码:

 

MySQL 5.7 以下版本:UPDATE mysql.user SET Password=PASSWORD(‘123456′) where USER=’root’;

 

MySQL 5.7 版本:UPDATE mysql.user SET authentication_string=PASSWORD(‘123456′) where USER=’root’;

 

⑤ 刷新:flush privileges;

 

⑥ 退出:exit;

 

设置完之后,输入 mysql -u root -p,这时输入刚设置的密码,就可以登进数据库了。

 

——————————————————————————–

 

  1. 防火墙设置

远程访问 MySQL,需要开放 3306 端口:

 

firewall-cmd –permanent –zone=public –add-port=3306/tcp

 

firewall-cmd –permanent –zone=public –add-port=3306/udp

 

firewall-cmd –reload

 

如果是 CentOS 7,需要将 MySQL 服务加入防火墙,然后重启防火墙:

 

firewall-cmd –zone=public –permanent –add-service=mysql

 

systemctl restart firewalld

 

——————————————————————————–

 

提示:

 

在输入 firewall-cmd –permanent –zone=public –add-port=3306/tcp 时可能会报 ‘FirewallD is not running’,是说防火墙本身就没有打开,解决方法:

 

① 查看防火墙状态:systemctl status firewalld,会发现状态是 dead,即防火墙未开启。

 

② 打开防火墙:systemctl start firewalld

 

③ 再次查看防火墙状态:systemctl status firewalld,这时会发现状态变为 running,即防火墙开启成功。

 

 

 

 

 

 

这时再输入开放 3306 端口的命令就没有问题了。

 

——————————————————————————–

 

  1. 设置允许远程访问

默认情况下 MySQL 是不允许远程连接的,所以在 Java 项目或者 MySQLWorkbench 等数据库连接工具连接服务器上的 MySQL 服务的时候会报 “Host ‘x.x.x.x’ is not allowed to connect to this MySQL server”。可以通过下面的设置解决。详细可以参考之前写的一篇文章 XXX is not allowed to connect to this MySQL server。

 

① grant all privileges on *.* to [email protected]”%” identified by ‘0’;

 

② flush privileges;

 

——————————————————————————–

 

提示:

 

在执行第一条命令的时候,可能会报:

 

‘ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.’ 需要让我们重置密码。原因是因为我刚刚的命令中设置的数据库密码是0,这个密码过于简单,不符合 MySQL 的安全要求。只要重新设置一个复杂点的密码就可以了:

 

mysql> SET PASSWORD = PASSWORD(‘xxx’);   //xxx 是重置的新的复杂的密码

 

——————————————————————————–

 

思考:

 

之前设置简单密码是没有问题的,可能原因:

 

① 可能目前环境是 CentOS 7 + MySQL 5.7.21,安全性有所提升。

 

② 也有可能是之前的数据库设置过

 

mysql> set global validate_password_policy=0;

 

mysql> set global validate_password_length=1;

 

允许设置简单密码。

 

  1. 相关命令

MySQL 相关:

 

systemctl start mysqld    #启动mysql

 

systemctl stop mysqld    #停止mysqld

 

systemctl restart mysqld    #重启mysqld

 

systemctl enable mysqld    #设置开机启动

 

systemctl status mysqld    #查看 MySQL Server 状态

 

防火墙相关:

 

systemctl status firewalld    #查看防火墙状态

 

systemctl start firewalld    #打开防火墙

 

systemctl stop firewalld    #关闭防火墙

 

systemctl restart firewalld    #重启防火墙

 

 

MYSQL 中 ON DUPLICATE KEY UPDATE / REPLACE 单语句处理重复插入时更新

 mysql  MYSQL 中 ON DUPLICATE KEY UPDATE / REPLACE 单语句处理重复插入时更新已关闭评论
7月 252019
 

来自:https://lobert.iteye.com/blog/1604122 , 讲的很清楚,记录下

 

mysql当插入重复时更新的方法:

第一种方法:

 示例一:插入多条记录

假设有一个主键为 client_id clients 表,可以使用下面的语句:

 

Sql代码  

  1. INSERT INTO clients  
  2. (client_id,client_name,client_type)  
  3. SELECT supplier_id,supplier_name,‘advertising’  
  4. FROM suppliers  
  5. WHERE not exists(select * from clients where clients.client_id=suppliers.supplier_id);  

 

示例一:插入单条记录

Sql代码  

  1. INSERT INTO clients  
  2. (client_id,client_name,client_type)  
  3. SELECT 10345,‘IBM’,‘advertising’  
  4. FROM dual  
  5. WHERE not exists (select * from clients where clients.client_id=10345);  

 

使用 dual 做表名可以让你在 select 语句后面直接跟上要插入字段的值,即使这些值还不存在当前表中。

第二种方法: 

INSERT ON DUPLICATE KEY UPDATE的使用(本文重点)

如果您指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE。例如,如果列a被定义为UNIQUE,并且包含值1,则以下两个语句具有相同的效果:

 

Sql代码  

  1. mysql>INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;  
  2. mysql>UPDATE table SET c=c+1 WHERE a=1;  

 

如果行作为新记录被插入,则受影响行的值为1;如果原有的记录被更新,则受影响行的值为2

注释:如果列b也是唯一列,则INSERT与此UPDATE语句相当:

 

Sql代码  

  1. mysql>UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;  

 

如果a=1 OR b=2与多个行向匹配,则只有一个行被更新。通常,您应该尽量避免对带有多个唯一关键字的表使用ON DUPLICATE KEY子句。

您可以在UPDATE子句中使用VALUES(col_name)函数从INSERT…UPDATE语句的INSERT部分引用列值。换句话说,如果没有发生重复关键字冲突,则UPDATE子句中的VALUES(col_name)可以引用被插入的col_name的值。本函数特别适用于多行插入。VALUES()函数只在INSERT…UPDATE语句中有意义,其它时候会返回NULL

示例:

 

Sql代码  

  1. mysql>INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)  
  2.           ->ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);  

 

本语句与以下两个语句作用相同:

 

Sql代码  

  1. mysql>INSERT INTO table (a,b,c) VALUES (1,2,3)  
  2.           ->ON DUPLICATE KEY UPDATE c=3;  
  3. mysql>INSERT INTO table (a,b,c) VALUES (4,5,6)  
  4.           ->ON DUPLICATE KEY UPDATE c=9;  

 

当您使用ON DUPLICATE KEY UPDATE时,DELAYED选项被忽略。

第三种方法:

 

REPLACE语句

  我们在使用数据库时可能会经常遇到这种情况。如果一个表在一个字段上建立了唯一索引,当我们再向这个表中使用已经存在的键值插入一条记录,那将会抛出一个主键冲突的错误。当然,我们可能想用新记录的值来覆盖原来的记录值。如果使用传统的做法,必须先使用DELETE语句删除原先的记录,然后再使用INSERT插入新的记录。而在MySQL中为我们提供了一种新的解决方案,这就是REPLACE语句。使用REPLACE插入一条记录时,如果不重复,REPLACE就和INSERT的功能一样,如果有重复记录,REPLACE就使用新记录的值来替换原来的记录值。

  使用REPLACE的最大好处就是可以将DELETEINSERT合二为一,形成一个原子操作。这样就可以不必考虑在同时使用DELETEINSERT时添加事务等复杂操作了。

  在使用REPLACE时,表中必须有唯一索引,而且这个索引所在的字段不能允许空值,否则REPLACE就和INSERT完全一样的。

  在执行REPLACE后,系统返回了所影响的行数,如果返回1,说明在表中并没有重复的记录,如果返回2,说明有一条重复记录,系统自动先调用了DELETE删除这条记录,然后再记录用INSERT来插入这条记录。如果返回的值大于2,那说明有多个唯一索引,有多条记录被删除和插入。

  REPLACE的语法和INSERT非常的相似,如下面的REPLACE语句是插入或更新一条记录。

  REPLACE INTO users (id,name,age) VALUES(123, ‘赵本山‘, 50);

  

 

  插入多条记录:

  REPLACE INTO users(id, name, age)

Sql代码  

  1. VALUES(123, 赵本山, 50), (134,‘Mary’,15);  
  2.   
  3. REPLACE也可以使用SET语句  
  4.   
  5. REPLACE INTO users SET id = 123, name = 赵本山, age = 50;  

 

  上面曾提到REPLACE可能影响3条以上的记录,这是因为在表中有超过一个的唯一索引。在这种情况下,REPLACE将考虑每一个唯一索引,并对每一个索引对应的重复记录都删除,然后插入这条新记录。假设有一个table1表,有3个字段a, b, c。它们都有一个唯一索引。

  CREATE TABLE table1(a INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE);

 

  假设table1中已经有了3条记录

  a b c

  1 1 1

  2 2 2

  3 3 3

  下面我们使用REPLACE语句向table1中插入一条记录。

  REPLACE INTO table1(a, b, c) VALUES(1,2,3);

 

  返回的结果如下

  Query OK, 4 rows affected (0.00 sec)

  在table1中的记录如下

  a b c

  1 2 3

 

使用MYSQL命令查看MYSQL中数据库或表占用空间的大小

 mysql  使用MYSQL命令查看MYSQL中数据库或表占用空间的大小已关闭评论
3月 062019
 

知道每个数据库的大小,使用下面步骤:

1、进入information_schema 数据库

use information_schema;

 

2、根据表名汇总查询各表数据的大小:

select TABLE_SCHEMA as tablename, concat(round(sum(data_length/1024/1024/1024),3),’GB’) as data from tables group by TABLE_SCHEMA;

 

2、查看指定数据库的大小:

比如查看数据库mydb的大小

select concat(round(sum(data_length/1024/1024/1024),3),’GB’) as data from tables where table_schema=’mydb’;

 

4、查看指定数据库的某个表的大小

比如查看数据库mydb中 mytable 表的大小

select concat(round(sum(data_length/1024/1024),3),’MB’) as data from tables where table_schema=’mydb’ and table_name=’mytable’;

navcat 连接本地mysql,出现“client does not support authentication”

 mysql  navcat 连接本地mysql,出现“client does not support authentication”已关闭评论
12月 242018
 

navcat 使用root连接本地mysql,出现“client does not support authentication”

先使用命令行mysql -uroot -p连接mysql, 输入密码后进入

mysql>

然后运行以下命令如下:

1、mysql> use mysql;

2、mysql> alter user ‘root’@’localhost’ identified with mysql_native_password by ‘********’;

3、mysql> flush privileges;

MySQL的if、case语句使用总结

 mysql  MySQL的if、case语句使用总结已关闭评论
11月 212018
 

总结的不错,https://www.cnblogs.com/raobenjun/p/7998467.html

Mysql的if既可以作为表达式用,也可在存储过程中作为流程控制语句使用,如下是做为表达式使用:

IF表达式

IF(expr1,expr2,expr3)

如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL),则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值,具体情况视其所在语境而定。

select *,if(sva=1,"男","女") as ssva from taname where sva != ""

作为表达式的if也可以用CASE when来实现:

select CASE sva WHEN 1 THEN '男' ELSE '女' END as ssva from taname where sva != ''

在第一个方案的返回结果中, value=compare-value。而第二个方案的返回结果是第一种情况的真实结果。如果没有匹配的结果值,则返回结果为ELSE后的结果,如果没有ELSE 部分,则返回值为 NULL。

例如:

SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END as testCol

将输出one

IFNULL(expr1,expr2)

假如expr1 不为 NULL,则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。IFNULL()的返回值是数字或是字符串,具体情况取决于其所使用的语境。

mysql> SELECT IFNULL(1,0); -> 1 mysql> SELECT IFNULL(NULL,10); -> 10 mysql> SELECT IFNULL(1/0,10); -> 10 mysql> SELECT IFNULL(1/0,'yes'); -> 'yes'

IFNULL(expr1,expr2) 的默认结果值为两个表达式中更加“通用”的一个,顺序为STRING、 REAL或 INTEGER。

IF ELSE 做为流程控制语句使用

if实现条件判断,满足不同条件执行不同的操作,这个我们只要学编程的都知道if的作用了,下面我们来看看mysql 存储过程中的if是如何使用的吧。

IF search_condition THEN 
    statement_list [ELSEIF search_condition THEN] statement_list ... [ELSE 
    statement_list] END IF 

与PHP中的IF语句类似,当IF中条件search_condition成立时,执行THEN后的statement_list语句,否则判断ELSEIF中的条件,成立则执行其后的statement_list语句,否则继续判断其他分支。当所有分支的条件均不成立时,执行ELSE分支。search_condition是一个条件表达式,可以由“=、<、<=、>、>=、!=”等条件运算符组成,并且可以使用AND、OR、NOT对多个表达式进行组合。

例如,建立一个存储过程,该存储过程通过学生学号(student_no)和课程编号(course_no)查询其成绩(grade),返回成绩和成绩的等级,成绩大于90分的为A级,小于90分大于等于80分的为B级,小于80分大于等于70分的为C级,依次到E级。那么,创建存储过程的代码如下:

create procedure dbname.proc_getGrade (stu_no varchar(20),cour_no varchar(10)) BEGIN declare stu_grade float; select grade into stu_grade from grade where student_no=stu_no and course_no=cour_no; if stu_grade>=90 then select stu_grade,'A'; elseif stu_grade<90 and stu_grade>=80 then select stu_grade,'B'; elseif stu_grade<80 and stu_grade>=70 then select stu_grade,'C'; elseif stu_grade70 and stu_grade>=60 then select stu_grade,'D'; else select stu_grade,'E'; end if; END

注意:IF作为一条语句,在END IF后需要加上分号“;”以表示语句结束,其他语句如CASE、LOOP等也是相同的。

mysql取出每个分组中最新的记录

 mysql  mysql取出每个分组中最新的记录已关闭评论
2月 112018
 

原来group还能这样写。

mysql的gruopby分组功能没有排序功能,所以我们如果想取出某个分组下的最新记录是不太容易的,下面介绍两种方法,一种是通过子查询,一种是通过group_concat函数来实现。

一、表结构及数据插入
#表的结构 `test3`
CREATE TABLE IF NOT EXISTS `test3` (
`id` int(11) NOT NULL auto_increment,
`bid` int(11) NOT NULL,
`cid` int(11) NOT NULL,
`dtime` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
#转存表中的数据 `test3`
INSERT INTO `test3` (`id`, `bid`, `cid`, `dtime`) VALUES
(1, 1, 3, ’2014-03-18 16:00:00′),
(2, 1, 10, ’2014-03-18 17:00:00′),
(3, 2, 5, ’2014-03-18 18:00:00′),
(4, 2, 6, ’2014-03-18 19:00:00′),
(5, 1, 7, ’2014-03-18 20:00:00′);
二、通过子查询实现(此方法在mysql5.7.21上已不支持, 可以使用第2个方法group_concat)
1、sql语句
select * from(select * from test3 ORDER BY dtime DESC) as tempGROUP BY  bid ORDER BY dtime DESC;
三、通过group_concat函数
1、完整的语法
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator’分隔符’])
作用:将要连接的字段按照排序字段的顺序用分隔符连起来显示,默认分隔符是”,”。
2、sql语句
select substring_index(group_concat(id order by `dtime`desc),’,’,1) as id, substring_index(group_concat(bid order by`dtime` desc),’,’,1) as bid,substring_index(group_concat(cid orderby `dtime` desc),’,’,1) as cid,substring_index(group_concat(dtimeorder by `dtime` desc),’,’,1) as dtime from `test3` group bybid;
两上结果是一致的,虽然mysql自身的group by没有排序功能,但是通过自已的思考还是有办法的,写此作为备忘吧。
select * from (SELECT * FROM jcacard.v_inoroutrecord order by RecordDateTime desc) as temp 
group by EmployName
having InOrOut = 1
order by RecordDateTime

转自:http://blog.csdn.net/swazer_z/article/details/70054315

深入mysql “ON DUPLICATE KEY UPDATE” 语法的分析

 mysql  深入mysql “ON DUPLICATE KEY UPDATE” 语法的分析已关闭评论
9月 042017
 

本篇文章是对mysql “ON DUPLICATE KEY UPDATE”语法进行了详细的分析介绍,来自:http://www.jb51.net/article/39255.htm

mysql “ON DUPLICATE KEY UPDATE” 语法
如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则在出现重复值的行执行UPDATE;如果不会导致唯一值列重复的问题,则插入新行。 
例如,如果列 a 为 主键 或 拥有UNIQUE索引,并且包含值1,则以下两个语句具有相同的效果:

复制代码代码如下:

INSERT INTO TABLE (a,c) VALUES (1,3) ON DUPLICATE KEY UPDATE c=c+1;
UPDATE TABLE SET c=c+1 WHERE a=1;

如果行作为新记录被插入,则受影响行的值显示1;如果原有的记录被更新,则受影响行的值显示2。 
这个语法还可以这样用: 
如果INSERT多行记录(假设 a 为主键或 a 是一个 UNIQUE索引列):

复制代码代码如下:

INSERT INTO TABLE (a,c) VALUES (1,3),(1,7) ON DUPLICATE KEY UPDATE c=c+1;

执行后, c 的值会变为 4 (第二条与第一条重复, c 在原值上+1).

复制代码代码如下:

INSERT INTO TABLE (a,c) VALUES (1,3),(1,7) ON DUPLICATE KEY UPDATE c=VALUES(c);

执行后, c 的值会变为 7 (第二条与第一条重复, c 在直接取重复的值7). 
注意:ON DUPLICATE KEY UPDATE只是MySQL的特有语法,并不是SQL标准语法! 
这个语法和适合用在需要 判断记录是否存在,不存在则插入存在则更新的场景.

INSERT INTO .. ON DUPLICATE KEY更新多行记录
如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE;如果不会导致唯一值列重复的问题,则插入新行。例如,如果列a被定义为UNIQUE,并且包含值1,则以下两个语句具有相同的效果:

复制代码代码如下:

INSERT INTO TABLE (a,b,c) 
VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
UPDATE TABLE SET c=c+1 WHERE a=1;

如果行作为新记录被插入,则受影响行的值为1;如果原有的记录被更新,则受影响行的值为2。
如果你想了解更多关于INSERT INTO .. ON DUPLICATE KEY的功能说明,详见MySQL参考文档:13.2.4. INSERT语法

现在问题来了,如果INSERT多行记录, ON DUPLICATE KEY UPDATE后面字段的值怎么指定?要知道一条INSERT语句中只能有一个ON DUPLICATE KEY UPDATE,到底他会更新一行记录,还是更新所有需要更新的行。这个问题困扰了我很久了,其实使用VALUES()函数一切问题都解决了。

举个例子,字段a被定义为UNIQUE,并且原数据库表table中已存在记录(2,2,9)和(3,2,1),如果插入记录的a值与原有记录重复,则更新原有记录,否则插入新行:

复制代码代码如下:

INSERT INTO TABLE (a,b,c) VALUES 
(1,2,3),
(2,5,7),
(3,3,6),
(4,8,2)
ON DUPLICATE KEY UPDATE b=VALUES(b);

以上SQL语句的执行,发现(2,5,7)中的a与原有记录(2,2,9)发生唯一值冲突,则执行ON DUPLICATE KEY UPDATE,将原有记录(2,2,9)更新成(2,5,9),将(3,2,1)更新成(3,3,1),插入新记录(1,2,3)和(4,8,2)
注意:ON DUPLICATE KEY UPDATE只是MySQL的特有语法,并不是SQL标准语法!

influxdb基本操作

 数据库  influxdb基本操作已关闭评论
12月 162016
 

名词解释

在具体的讲解influxdb的相关操作之前先说说influxdb的一些专有名词,这些名词代表什么。

influxDB名词

  • database:数据库;
  • measurement:数据库中的表;
  • points:表里面的一行数据。

influxDB中独有的一些概念

Point由时间戳(time)、数据(field)和标签(tags)组成。
  • time:每条数据记录的时间,也是数据库自动生成的主索引;
  • fields:各种记录的值;
  • tags:各种有索引的属性。

还有一个重要的名词:series

所有在数据库中的数据,都需要通过图表来表示,series表示这个表里面的所有的数据可以在图标上画成几条线(注:线条的个数由tags排列组合计算出来)
举个简单的小例子:
有以下数据:
它的series为:

influxDB基本操作

数据库与表的操作

可以直接在web管理页面做操作,当然也可以命令行。
[sql] view plain copy

 在CODE上查看代码片派生到我的代码片

  1. #创建数据库  
  2. create database “db_name”  
  3.   
  4. #显示所有的数据库  
  5. show databases  
  6.   
  7. #删除数据库  
  8. drop database “db_name”  
  9.   
  10. #使用数据库  
  11. use db_name  
  12.   
  13. #显示该数据库中所有的表  
  14. show measurements  
  15.   
  16. #创建表,直接在插入数据的时候指定表名  
  17. insert test,host=127.0.0.1,monitor_name=test count=1  
  18.   
  19. #删除表  
  20. drop measurement “measurement_name”  


向数据库中插入数据。
  • 通过命令行
[sql] view plain copy

 在CODE上查看代码片派生到我的代码片

  1. > use metrics  
  2. Using database metrics  
  3. insert test,host=127.0.0.1,monitor_name=test count=1  

这样,数据库插入数据成功。

  • 通过http接口
[plain] view plain copy

 在CODE上查看代码片派生到我的代码片

  1. curl -i -XPOST ‘http://127.0.0.1:8086/write?db=metrics’ –data-binary ‘test,host=127.0.0.1,monitor_name=test count=1’  

读者看到这里可能会观察到插入的数据的格式貌似比较奇怪,这是因为influxDB存储数据采用的是Line Protocol格式。那么何谓Line Protoco格式?

Line Protocol格式:写入数据库的Point的固定格式。
在上面的两种插入数据的方法中都有这样的一部分:

[sql] view plain copy

 在CODE上查看代码片派生到我的代码片

  1. test,host=127.0.0.1,monitor_name=test count=1  
其中:
  1. test:表名;
  2. host=127.0.0.1,monitor_name=test:tag;
  3. count=1:field

相对此格式有详细的了解参见官方文档


查询数据库中的数据。
  • 通过命令行
[sql] view plain copy

 在CODE上查看代码片派生到我的代码片

  1. > use metrics  
  2. Using database metrics  
  3. select * from test order by time desc  

  • 通过http接口
[plain] view plain copy

 在CODE上查看代码片派生到我的代码片

  1. curl -G ‘http://localhost:8086/query?pretty=true’ –data-urlencode “db=metrics” –data-urlencode “q=select * from test order by time desc”  

influxDB是支持类sql语句的,具体的查询语法都差不多,这里就不再做详细的赘述了。

数据保存策略(Retention Policies)

influxDB是没有提供直接删除数据记录的方法,但是提供数据保存策略,主要用于指定数据保留时间,超过指定时间,就删除这部分数据。
  • 查看当前数据库Retention Policies
[sql] view plain copy

 在CODE上查看代码片派生到我的代码片

  1. show retention policies on “db_name”  

  • 创建新的Retention Policies
[sql] view plain copy

 在CODE上查看代码片派生到我的代码片

  1. create retention policy “rp_name” on “db_name” duration 3w replication 1 default  
  • rp_name:策略名
  • db_name:具体的数据库名
  • 3w:保存3周,3周之前的数据将被删除,influxdb具有各种事件参数,比如:h(小时),d(天),w(星期)
  • replication 1:副本个数,一般为1就可以了
  • default:设置为默认策略
  • 修改Retention Policies
[sql] view plain copy

 在CODE上查看代码片派生到我的代码片

  1. alter retention policy “rp_name” on “db_name” duration 30d default  

  • 删除Retention Policies
[sql] view plain copy

 在CODE上查看代码片派生到我的代码片

  1. drop retention policy “rp_name”  

连续查询(Continous Queries)

当数据超过保存策略里指定的时间之后就会被删除,但是这时候可能并不想数据被完全删掉,怎么办?
influxdb提供了联系查询,可以做数据统计采样。
  • 查看数据库的Continous Queries
[sql] view plain copy

 在CODE上查看代码片派生到我的代码片

  1. show continuous queries  

  • 创建新的Continous Queries
[sql] view plain copy

 在CODE上查看代码片派生到我的代码片

  1. create continous query cq_name on db_name begin select sum(countinto new_table_name from table_name group by time(30m) end  
  • cq_name:连续查询名字
  • db_name:数据库名字
  • sum(count):计算总和
  • table_name:当前表名
  • new_table_name:存新的数据的表名
  • 30m:时间间隔为30分钟
  • 删除Continous Queries
[plain] view plain copy

 在CODE上查看代码片派生到我的代码片

  1. drop continous query cp_name on db_name  

用户管理

可以直接在web管理页面做操作,也可以命令行。

  1. #显示用户  
  2. show users  
  3.   
  4. #创建用户  
  5. create user “username” with password ‘password’  
  6.   
  7. #创建管理员权限用户  
  8. create user “username” with password ‘password’ with all privileges  
  9.   
  10. #删除用户  
  11. drop user “username”  

转自:

  1. #显示用户  
  2. show users  
  3.   
  4. #创建用户  
  5. create user “username” with password ‘password’  
  6.   
  7. #创建管理员权限用户  
  8. create user “username” with password ‘password’ with all privileges  
  9.   
  10. #删除用户  
  11. drop user “username”  

分布式时序数据库InfluxDB介绍

 数据库  分布式时序数据库InfluxDB介绍已关闭评论
12月 162016
 

记录下influxdb的一些资料,免得到时找不到

InfluxDB 是一个开源分布式时序、事件和指标数据库。使用 Go 语言编写,无需外部依赖。其设计目标是实现分布式和水平伸缩扩展。

它有三大特性:

1. Time Series (时间序列):你可以使用与时间有关的相关函数(如最大,最小,求和等)
2. Metrics(度量):你可以实时对大量数据进行计算
3. Eevents(事件):它支持任意的事件数据

InfluxDB

特点

  • schemaless(无结构),可以是任意数量的列
  • Scalable
  • min, max, sum, count, mean, median 一系列函数,方便统计
  • Native HTTP API, 内置http支持,使用http读写
  • Powerful Query Language 类似sql
  • Built-in Explorer 自带管理工具

管理界面:

InfluxDB

API

InfluxDB 支持两种api方式

  • HTTP API
  • Protobuf API

Protobuf 还未开发完成, 官网文档都没有

如何使用 http api 进行操作?

比如对于foo_production这个数据库,插入一系列数据,可以发现POST 请求到 /db/foo_production/series?u=some_user&p=some_password, 数据放到body里。

数据看起来是这样的:

下面的”name”: “events”, 其中”events”就是一个series,类似关系型数据库的表table

格式是json,可以在一个POST请求发送多个 series, 每个 series 里的 points 可以是多个,但索引要和columns对应。

上面的数据里没有包含time 列,InfluxDB会自己加上,不过也可以指定time,比如:

time 在InfluxDB里是很重要的,毕竟InfluxDB是time series database
在InfluxDB里还有个sequence_number字段是数据库维护的,类似于mysql的 主键概念

InfluxDB 增删更查都是用http api来完成,甚至支持使用正则表达式删除数据,还有计划任务。

比如:

发送POST请求到 /db/:name/scheduled_deletes, body如下,

这个查询会删除大于14天的数据,并且任何以stats开头的数据,并且每天3:00 AM运行。

更加详细查看官方文档: http://influxdb.org/docs/api/http.html

查询语言

InfluxDB 提供了类似sql的查询语言

看起来是这样的:

非常容易上手, 还支持Group By, Merging Series, Joining Series, 并内置常用统计函数,比如max, min, mean 等

文档: http://influxdb.org/docs/query_language/


常用语言的库都有,因为api简单,也很容易自己封装。

InfluxdDB作为很多监控软件的后端,这样监控数据就可以直接存储在InfluxDB
StatsD, CollectD, FluentD

还有其它的可视化工具支持InfluxDB, 这样就可以基于InfluxDB很方便的搭建监控平台

InfluxDB 数据可视化工具

InfluxDB 用于存储基于时间的数据,比如监控数据,因为InfluxDB本身提供了Http API,所以可以使用InfluxDB很方便的搭建了个监控数据存储中心。

对于InfluxDB中的数据展示,官方admin有非常简单的图表, 看起来是这样的

InfluxDB

除了自己写程序展示数据还可以选择:

tasseo

tasseo,为Graphite写的Live dashboard,现在也支持InfluxDB,tasseo 比较简单, 可以配置的选项很少。

InfluxDB

Grafana

Grafana是一个纯粹的html/js应用,访问InfluxDB时不会有跨域访问的限制。只要配置好数据源为InfluxDB之后就可以,剩下的工作就是配置图表。Grafana 功能非常强大。使用ElasticsSearch保存DashBoard的定义文件,也可以Export出JSON文件(Save ->Advanced->Export Schema),然后上传回它的/app/dashboards目录。

配置数据源:

InfluxDB

转自:http://www.ttlsa.com/monitor-safe/monitor/distributed-time-series-database-influxdb/