数据库

Redis 与 Pika scan 性能对比

Redis 是后端常用的键值数据库。Pika 是 360 出品的一款与 Redis 协议几乎兼容的数据库。与
Redis 不同的是,Pika 基于硬盘,使用 RocksDB 作为引擎,从容量上来说,比基于内存的 Redis
大了不少,而且在性能上也能满足一般需求。

我们知道,在 Redis 中,`keys *` 这个操作仅限于在本地调试使用,千万不能用于线上,因为这会
遍历整个数据库,可能引起数据库长时间无响应,甚至崩溃。在线上服务器,如果想要查找某个模式
的键,可以使用 scan 命令。比如说要查找 `user:` 前缀的所有键,可以使用 `scan 0 user:*` 命
令。然而如果服务器上的键非常多的话,虽然不会卡死服务器了,但是这个过程依然会很漫长。

Redis 是使用 hash table 实现的,所以 scan 命令其实也是遍历所有键,拿到每个键再做过滤,而
不能直接读取符合对应 pattern 的键。我们使用下面的代码来验证一下 redis scan 的性能。

“`
from redis import Redis
from uuid import uuid4
import time

def gen(r):
for i in range(10000000):
r.set(str(uuid4()), 1)
r.set(“user:1”, “bar”)

def scan(r):
start = time.time()
for key in r.scan_iter(“user:*”):
print(“user=%s” % r.get(key).decode())
duration = time.time() – start
print(“duration for finding user is %.3f” % duration)
duration = time.time() – start
print(“duration for full scan is %.3f” % duration)

if __name__ == “__main__”:
import sys
port = int(sys.argv[1])
r = Redis(port=port)
gen(r)
scan(r)
“`

首先插入一千万个随机数据,然后从中查找我们的目标数据。结果如下:

“`
-> % python3 rb.py 6379
user=bar
duration for finding user is 80.145
duration for full scan is 180.936
“`

和我们的预期基本是相符的, 也就是说 Redis 是首先遍历然后再做过滤的。

接下来我们对 Pika 做相同的实验,Pika 默认使用 9221 端口,我们只需要把端口换一下就好了:

“`
-> % python3 rb.py 9221
user=bar
duration for finding user is 0.002
duration for full scan is 0.003
“`

结果是令人震惊的!Pika 几乎在瞬间就完成了遍历。原因在于 Pika 使用了 RocksDB,而 RocksDB
支持 Range 操作。RocksDB 中的数据都是有序的,所以查找起来就不需要 O(n) 了,只需要二分查
找,也就是 O(logN) 即可。

MySQL 性能小技巧和在 Django 中的应用

对于 delete update insert 等语句一定要使用 limit 子句限制影响的行数,避免一次更改特别多的行,造成数据库假死

while (1) {
    //每次只做1000条
    mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000");
    if (mysql_affected_rows() == 0) {
        // 没得可删了,退出!
        break;
    }
    // 每次都要休息一会儿
    usleep(50000);
}

2. 垂直分割

把不会用作索引的,或者是过长的字段可以考虑使用其他存储引擎,比如 rocksdb 等。

3. IPv4 地址可以存为 uint32

使用 uint32 存储 IP 地址不光可以节省空间,而且可以按区间查询。

4. 避免 select *

从数据库里读出越多的数据,那么查询就会变得越慢。并且,如果你的数据库服务器和应用服务器是两台独立的服务器的话,这还会增加网络传输的负载。

所以,你应该养成一个需要什么就取什么的好的习惯。

不要使用:

SELECT * FROM user WHERE user_id = 1

使用:

SELECT username FROM user WHERE user_id = 1

在 django 中,可以使用 only

books = Book.objects.filter(author="Jim").only('book_name')

5. 当只要一行数据时使用 LIMIT 1

当你查询表的有些时候,你已经知道结果只会有一条结果,但因为你可能需要去fetch游标,或是你也许会去检查返回的记录数。

在这种情况下,加上 LIMIT 1 可以增加性能。这样一样,MySQL数据库引擎会在找到一条数据后停止搜索,而不是继续往后查少下一条符合记录的数据。

下面的示例,只是为了找一下是否有“中国”的用户,很明显,后面的会比前面的更有效率。(请注意,第一条中是Select *,第二条是Select 1)

SELECT * FROM user WHERE country = 'China'
SELECT 1 FROM user WHERE country = 'China' LIMIT 1

在 Django 中可以使用 [:1] 来添加 limit 1

6. EXPLAIN 你的 SELECT 查询

使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。

7. 尽量让查询能 fit 进内存中

参考:

  1. https://coolshell.cn/articles/1846.html

MySQL 中的 wait_timeout 是做什么的?

Mysql 中默认的 wait_timeout 和 interactive_timeout 的值是八小时,也就是一个连接(交互式和非交互式的)只有在 8 小时没有活动之后才会被关闭掉。对于互联网公司来说,这个值实在太大了,一个库可能被很多脚本和服务访问,可能只是一个简短的查询就不需要数据库了,如果每个查询都占据了8小时的时间,那么 mysql 很快连接数就会满了,报出 too many connections 错误。

mysql 默认的连接数可以修改 max_connections 参数,但是一个服务器能支撑的连接数显然是由硬件决定的。

设置 wait_timeout 过短可能会造成一些问题,如果在 django 中两次查询的之间时间大于 wait_timeout,就会报 (2006, ‘MySQL server has gone away’)。django 官方给的建议是:

  1. 当你的脚本不需要使用数据库的时候,主动关闭连接,比如在 django 中使用 from django.db import connection; connection.close()
  2. 增大 wait_timeout 的值

不过django默认 CONN_MAX_AGE 是 0,也就是在查询数据库之后会立即关闭链接,理论上应该不会报这个错误。但是这样不能复用链接,会造成对数据压力很大。

CONN_MAX_AGE 应该小于数据库本身的最大连接时间wait_timeout,否则应用程序可能会获取到连接超时的数据库连接,这时会出现MySQL server has gone away的报错。

可以在 settings.py 中动态地获取并填充这个值,然后写到 CONN_MAX_AGE 中

理论上这样就不会再报错了,但是难免数据库重启或者什么时候会报错,总是使用 close_old_connections 还是很烦。

有一种思路是在检测到和数据库链接断开的时候,自动重连,但是这样会破坏 django.db.atomic,但是可以实现一种不同的backend。可以参考这两个:

  1. https://github.com/django/django/pull/2740/commits/38f58aa4d751ad83f1dc76d5b945a1036239584f

  2. https://github.com/django/django/pull/2454/commits/36b8bf870cab183b7ad63c0d8e7e8c02e314a053#diff-f8a587a973ef4c3a94d7550a5b85342c

还有一种解决思路是使用 connection pooling,我们可以使用 sqlalchemy 的 连接池作为django连接数据库的工具。参考这里:http://menendez.com/blog/mysql-connection-pooling-django-and-sqlalchemy/, 不过这种方法比较 hack。

参考

  1. https://code.djangoproject.com/ticket/21597#no2
  2. https://github.com/django/django/commit/2ee21d9f0d9eaed0494f3b9cd4b5bc9beffffae5
  3. https://stackoverflow.com/questions/1125504/django-persistent-database-connection
  4. django 优化
  5. https://docs.djangoproject.com/en/2.1/ref/databases/#persistent-connections
  6. 如何设置 max_age

redis 常见问题

主要参考这篇文章:https://mp.weixin.qq.com/s/vS8IMgBIrfGpZYNUwtXrPQ

# 1. 集合操作避免范围过大

使用 sortedset、set、list、hash等集合类的O(N)操作时要评估当前元素个数的规模以及将来的增长规模,对于短期就可能变为大集合的key,要预估O(N)操作的元素数量,避免全量操作,可以使用HSCAN、SSCAN、ZSCAN进行渐进操作。集合元素数量过大在使用过程中会影响redis的实际性能,元素个数建议尽量不要超过5000,元素数量过大可考虑拆分成多个key进行处理。

# 2. 合理使用过期时间

如果key没有设置超时时间,会导致一直占用内存。对于可以预估使用生命周期的key应当设置合理的过期时间或在最后一次操作时进行清理,避免垃圾数据残留redis。redis 不是垃圾桶。

# 3. 利用批量操作命令

假设要给一个集合导入 5000 个元素:

方案1:直接使用redis的HSET逐个设置

“`
for _ in 0..5000
HSET hash, k,v
“`
结果:失败。redis ops飙升,同时接口响应超时

方案2:改用redis的 HMSET一次将所有元素设置到hash中

“`
map = 50000个元素
HMSET hash map
“`

结果:失败。出现redis慢日志

方案3:依然使用 HMSET,只是每次设置500个,循环100次

“`
map_chunk = 500个元素
for i in 0..100
HMSET hash map_chunk[i]
“`

结果:成功

MSET/HMSET等都支持一次输入多个key,LPUSH/RPUSH/SADD等命令都支持一次输入多个value,也要注意每次操作数量不要过多,建议控制在500个以内

# 4. 合理设置值的大小

String类型尽量控制在10KB以内。虽然redis对单个key可以缓存的对象长度能够支持的很大,但是实际使用场合一定要合理拆分过大的缓存项,1k 基本是redis性能的一个拐点。当缓存项超过10k、100k、1m性能下降会特别明显。关于吞吐量与数据大小的关系可见下面官方网站提供的示意图。

在局域网环境下只要传输的包不超过一个 MTU(以太网下大约 1500 bytes),那么对于 10、100、1000 bytes不同包大小的处理吞吐能力实际结果差不多。

# 5. 禁用一些命令

keys、monitor、flushall、flushdb应当通过redis的rename机制禁掉命令,若没有禁用,开发人员要谨慎使用。其中flushall、flushdb会清空redis数据;keys命令可能会引起慢日志;monitor命令在开启的情况下会降低redis的吞吐量,根据压测结果大概会降低redis50%的吞吐量,越多客户端开启该命令,吞吐量下降会越多。

keys和monitor在一些必要的情况下还是有助于排查线上问题的,建议可在重命名后在必要情况下由redis相关负责人员在redis备机使用,monitor命令可借助redis-faina等脚本工具进行辅助分析,能更快排查线上ops飙升等问题。

# 6. 避免大量 key 同时过期

如果大量的 key 过期时间设置得过于集中,到过期的时间点,redis 可能会出现短暂的卡顿现象。一般需要在时间上加一个随机值,使得过期时间分散一些。

# 7. Redis 如何做持久化

bgsave 做镜像全量持久化,aof 做增量持久化。因为 bgsave 会耗费较长时间,不够实时,在停机的时候回导致大量丢失数据,所以需要 aof 来配合使用。在 redis 实例重启时,优先使用 aof 来回复内存状态,如果没有 aof 日志,就会使用 rdb 来恢复。

如果 aof 文件过大会导致恢复时间过长,不过 redis 会定期做 aof 重写,压缩 aof 文件日志大小。在 redis 4.0 之后还有了混合持久化的功能,将 bgsave 的全量和 aof 的增量做了融合处理,这样既保证了回复的效率有兼容了数据的安全性。

为了避免断电时后丢失数据,还可以设置 aof 日志的 sync 属性,极端情况下,可以每次写入都执行,不过会对性能有影响,一般每秒一次就可以。

# 8. 保存失败

redis 报错:Can’t save in background: fork: Cannot allocate memory。

原因是 redis 在后台保存的时候会直接 fork 一下,然后保存。由于数据库过大,就会 fork 失败,但是实际上由于 copy-on-write 机制的存在,并不会产生问题。所以可以直接更改系统的配置,允许 fork。

把 `/etc/sysctl.conf` 文件修改如下:

“`
vm.overcommit_memory=1
“`

然后重新加载:

“`
sysctl -p /etc/sysctl.conf
“`

参考资料:

1. https://stackoverflow.com/questions/11752544/redis-bgsave-failed-because-fork-cannot-allocate-memory

redis 中如何给集合中的元素设置 TTL

我们知道在 redis 中可以给每个 key 设置过期时间(TTL),但是没法为每个集合中的每一个元素设置过期时间,可以使用zset来实现这个效果。

直接上代码吧,Python 版的。

“`
class RedisSet:

def __init__(self, key):
self.client = redis.StrictRedis()
self.key = key

def add(self, val, ttl=60):
now = time.time()
# 把过期时间作为 score 添加到 zset 中
self.client.zadd(self.key, now + ttl, val)
# 删除已经过期的元素
self.client.zremrangebyscore(self, now, ‘+inf’)

def getall(self):
# 只读取还没有过期的元素
return self.client.zrangebyscore(self.key, ‘-inf’, time.time())
“`

参考:https://github.com/antirez/redis/issues/135

mysql 基础知识(8) – 主从复制

mysql 有三种主从复制方式

MySQL传统的高可用解决方案是通过binlog复制来搭建主从或一主多从的数据库集群。主从之间的复制模式支持异步模式(async replication)和半同步模式(semi-sync replication)。无论哪种模式下,都是主库master提供读写事务的能力,而slave只能提供只读事务的能力。在master上执行的更新事务通过binlog复制的方式传送给slave,slave收到后将事务先写入relay log,然后重放事务,即在slave上重新执行一次事务,从而达到主从机事务一致的效果。 

其他文档:

http://mysql.taobao.org/monthly/2017/08/01/
http://blog.csdn.net/d6619309/article/details/53691352
http://blog.51cto.com/wangwei007/1893703
https://www.digitalocean.com/community/tutorials/how-to-configure-mysql-group-replication-on-ubuntu-16-04
http://blog.csdn.net/d6619309/article/details/53691352
http://mysql.taobao.org/monthly/2017/08/01/

mysql 基础知识(7) – JSON 字段

在前公司的时候,大家习惯在每个表加一个 extra 字段来表示一些额外的字段,然后在 ORM 中使用的时候再解析出来,方便了扩展字段,但是缺点也很明显,extra 字段只能读取而无法进行查询。MySQL 5.7 终于支持了 json 字段,相当于加入了一些 NoSQL 的特性,这样就可以很方便得查询了。

json 字段的使用

建表:

CREATE TABLE `book` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `tags` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

直接使用 json 类型就可以了。注意 json 字段不可以作为主键,不可以作为外键,不过既然是 json 字段了,谁会这么做呢。。

插入:

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

使用一个 json 字符串作为值插入即可。或者你也可以使用 json 相关的函数来表示json。

json 相关函数

json path

-- returns "SitePoint":
SELECT JSON_EXTRACT(
  '{"id": 1, "website": "SitePoint"}', 
  '$.website'
);

json path 的语法,用 $ 开头,然后跟着下面的选择器:

  • . 点后面跟着跟着一个字典里的名字, 比如 $.website
  • [N] 表示数组里的第 N 个元素
  • .[*] 表示选择字典里的所有元素
  • [*] 表示选择数组里的所有元素
  • prefix**suffix 表示以 prefix 开头,suffix 结尾的所有路径

举个栗子

{
  "a": 1,
  "b": 2,
  "c": [3, 4],
  "d": {
    "e": 5,
    "f": 6
  }
}
the following paths:

$.a returns 1
$.c returns [3, 4]
$.c[1] returns 4
$.d.e returns 5
$**.e returns [5]

构造、修改 json 的函数

函数都比较简单,看注释就明白了。

-- returns [1, 2, "abc"]:
SELECT JSON_ARRAY(1, 2, 'abc');

-- returns {"a": 1, "b": 2}:
SELECT JSON_OBJECT('a', 1, 'b', 2);

-- returns ["a", 1, {"key": "value"}]:
SELECT JSON_MERGE('["a", 1]', '{"key": "value"}');

-- returns ARRAY:
SELECT JSON_TYPE('[1, 2, "abc"]');

-- returns OBJECT:
SELECT JSON_TYPE('{"a": 1, "b": 2}');

-- returns an error:
SELECT JSON_TYPE('{"a": 1, "b": 2');

-- returns 1:
SELECT JSON_VALID('[1, 2, "abc"]');

还有其他一些函数,可以查看文档:

  • JSON_SET(doc, path, val[, path, val]…) —
    inserts or updates data in the document
  • JSON_INSERT(doc, path, val[, path, val]…) —
    inserts data into the document
  • JSON_REPLACE(doc, path, val[, path, val]…) —
    replaces data in the document
  • JSON_MERGE(doc, doc[, doc]…) —
    merges two or more documents
  • JSON_ARRAY_APPEND(doc, path, val[, path, val]…) —
    appends values to the end of an array
  • JSON_ARRAY_INSERT(doc, path, val[, path, val]…) —
    inserts an array within the document
  • JSON_REMOVE(doc, path[, path]…) —
    removes data from the document.

查询 json 函数

用于 where 子句中的函数

json_contains 用于选取数组中包含某个元素的行

-- all books with the 'JavaScript' tag:
SELECT * FROM `book` 
WHERE JSON_CONTAINS(tags, '["JavaScript"]');

json_search 用于选取字典中包含某个值的行

-- all books with tags starting 'Java':
SELECT * FROM `book` 
WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;

用于 select 子句中的 json 函数

可以使用 json path 语法从得到的 json 文档中抽取出某个值。

select 语句

要想在select语句中使用 json path 抽取元素可以使用下面的语法,也就是 column->path

SELECT
  name,
  tags->"$[0]" AS `tag1`
FROM `book`;

一个更复杂一点的例子:

id name profile
1 Craig {“twitter”: “@craigbuckler”,“facebook”: “craigbuckler”,“googleplus”: “craigbuckler”}
2 SitePoint {“twitter”: “@sitepointdotcom”}
SELECT
  name, profile->"$.twitter" AS `twitter`
FROM `user`;
SELECT
  name, profile->"$.twitter" AS `twitter`
FROM `user`
WHERE
  profile->"$.twitter" IS NOT NULL;

REF:

  1. https://www.sitepoint.com/use-json-data-fields-mysql-databases/

mysql 基础知识(6) – Join

看到网上有篇文章用韦恩图来讲解了一下 SQL 的 join 操作,但是感觉举的例子似乎不太实际,遂自己写了一篇,图是从那篇文章里面盗的(逃

假设我们有下面两张表,上边的是表 user,下边的是 package,表示每个用户对应的包裹

id name
1 Luke
2 Leia
3 Anakin
4 Padem
id content user_id
1 droid 3
2 lightsaber 2
3 blaster 1
4 R2D2 5

创建这两个表的语句分别是:

create table user (id integer, name string);
create table package (id integer, content string, user_id integer);
insert into user (id, name) values (1, 'Luke');
insert into user (id, name) values (2, 'Leia');
insert into user (id, name) values (3, 'Anakin');
insert into user (id, name) values (4, 'Padme');
insert into package (id, content, user_id) values (1, 'droid', 3);
insert into package (id, content, user_id) values (2, 'lightsaber', 2);
insert into package (id, content, user_id) values (3, 'blaster', 1);
insert into package (id, content, user_id) values (4, 'R2D2', 5);

Veen diagram(韦恩图)是一种表示集合的图形语言。SQL 的 join 本质上也是从集合论里面来的,可以从集合论的角度来学习和记忆 Join 的语法。

Inner Join

如果我们要选出每个有包裹的人,以及对应的包裹,可以使用 inner join。内连接(inner join)计算的是两个表的交集,也就是 A ∩ B

select
user.id, user.name, package.id, package.content 
from
user inner join package
on user.id == package.user_id;

结果一共有3列,每个表中的第四列都因为在对方表中没有而没有出现在结果里。

id          name        id          content
----------  ----------  ----------  ----------
1           Luke        3           blaster
2           Leia        2           lightsaber
3           Anakin      1           droid

Full Outer Join

如果我们想要选出所有的任何包裹的对应关系,哪怕是对应得人或者包裹不存在的话,可以使用 full outer join。全连接计算的是两个表的并集,也就是 A ∪ B

select
user.id, user.name, package.id, package.content
from
user full outer join package
on user.id == package.user_id;

结果一共有6列,注意其中缺字段的地方被补上了 null。另外 SQLite 不支持 full outer join。感觉这个 Join 似乎用的不是太多,因为实际情况中,往往 package.user_id 是 user.id 的外键,所以不会出现 user_id 不存在的情况。

// 结果省略

Left Outer Join

如果我们要取出每个人的包裹情况,没有包裹的也写上 null,那么这用情况下应该使用 left outer join。

select
user.id, user.name, package.id, package.content
from
user left outer join package
on user.id == package.user_id;

id          name        id          content
----------  ----------  ----------  ----------
1           Luke        3           blaster
2           Leia        2           lightsaber
3           Anakin      1           droid
4           Padme       NULL        NULL

Cross Join

要获得A表和B表左右可能的交叉组合的话,可以使用 cross join,也就是笛卡尔乘积。

select
user.id, user.name, package.id, package.content
from
user cross join package;

结果如下

id          name        id          content
----------  ----------  ----------  ----------
1           Luke        1           droid
1           Luke        2           lightsaber
1           Luke        3           blaster
1           Luke        4           R2D2
2           Leia        1           droid
2           Leia        2           lightsaber
2           Leia        3           blaster
2           Leia        4           R2D2
3           Anakin      1           droid
3           Anakin      2           lightsaber
3           Anakin      3           blaster
3           Anakin      4           R2D2
4           Padme       1           droid
4           Padme       2           lightsaber
4           Padme       3           blaster
4           Padme       4           R2D2