使用MySQL缓存开发环境为Django==2.1.7, djangorestframework==3.9.1, mysqlclient==1.4.2.post1 其他依赖包不赘述
原因
个人开发Django后端程序, 开启了SQL日志, 正巧现在在深入学习MySQL相关知识, 顺便进行了下性能测试. 不测试不知道, 一测试下一跳, 以下的是我遇到的问题和解决问题的过程.
# django查询sql日志, 包括耗时和查询语句 (0.040) SELECT COUNT(*) AS `__count` FROM `t_topic` WHERE (`t_topic`.`is_delete` = 0 AND `t_topic`.`is_active` = 1); args=(False, True)
(0.046) SELECT `t_topic`.`id`, `t_topic`.`name`, `t_topic`.`owner_id`, `t_topic`.`cover`, `t_topic`.`background`, `t_topic`.`describe`, `t_topic`.`create_time`, `t_topic`.`update_time` FROM `t_topic` WHERE (`t_topic`.`is_delete` = 0 AND `t_topic`.`is_active` = 1) ORDER BY `t_topic`.`create_time` DESC LIMIT 100 OFFSET 1000; args=(False, True)
我得吐槽下Django的ORM, 方便的地方特别方便, 不方便的地方又特别难搞, 比如我遇到的问题:
插入一千万条测试数据, 每次查询list分页前都会进行一次 SELECT COUNT(*) 操作, 这个可以理解, 但是哥哥, 一千万条数据未加索引情况下这个操作得耗时15秒左右.
# 未加索引情况 mysql> select count(*) from t_topic where is_delete = 0 and is_active =1;
+----------+
| count(*) |
+----------+
| 9999998 |
+----------+
1 row in set (15.10 sec)
# 给where后条件字段加上加索引, 这个情况好多了 mysql> select count(*) from t_topic where is_delete = 0 and is_active=1;
+----------+
| count(*) |
+----------+
| 9999998 |
+----------+
1 row in set (2.25 sec)
加过索引后好多了, 大概耗时2秒左右, 但是真的, 你叫我哥吧, 你个烂Django(其实也不是Django的锅), 每次我翻页是得等俩秒等你查询下COUNT() ?, 是不是太蠢了.
一开始我还以为是我把那个分页的生命周期搞错了, 我有重写了APIVIEW, 把那个rest_framework.pagination.PageNumberPagination 对象初始化在类变量里, 天真的我以为他能在项目启动时初始化, 在第一次请求过来时把基本的COUNT() 信息缓存下来, 后来发现不是, 之后我又深入地看了下源码, 调用了django.core.paginator.Paginator 来进行分页, paginate_queryset时都会进行COUNT() 查询, 后来我想想不做缓存也有其合理之处哈, 如果某个记录发生更改, 添加了记录, 删除了记录这样缓存也就不准确了, 但是我还是不甘心啊.
想着这个COUNT() 如果能被数据库缓存掉, 数据库有个机制是缓存查询, 能够把常用的查询情况缓存下来, 数据库缓存有条件限制:
以下是配置解释:my.cnf配置文件
# 是否开启 query_cache_type = 1
# 最大查询限制 query_cache_limit = 1K
# 最小查询限制, 注意这个地方最大值必须大于最小值, 否侧缓存无法生效 query_cache_min_res_unit= 0
# 缓存总大小 query_cache_size = 16M
修改后重启MySQL后连接进行查询:
# 用来查询当前缓存状态, 表示此时的缓存已经被开启 mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| have_query_cache | YES |
| query_cache_limit | 1024 |
| query_cache_min_res_unit | 0 |
| query_cache_size | 16777216 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
6 rows in set (0.00 sec)
# 查询缓存的信息 mysql> show status like '%Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 16760152 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+----------+
8 rows in set (0.00 sec)
# 第一次查询COUNT(*) mysql> select count(*) from t_topic where is_delete = 0 and is_active = 1;
+----------+
| count(*) |
+----------+
| 9999998 |
+----------+
1 row in set (2.27 sec)
# 再次查询缓存的使用情况, Qcache_inserts已经为1了, 说明对查询进行了缓存 mysql> show status like '%Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 16758616 |
| Qcache_hits | 0 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8 rows in set (0.00 sec)
# 经过第一次查询后第二次进行查询, 基本不耗时了, 相当快 mysql> select count(*) from t_topic where is_delete = 0 and is_active = 1;
+----------+
| count(*) |
+----------+
| 9999998 |
+----------+
1 row in set (0.00 sec)
# 进行数据修改测试, 验证缓存是否会有效(修改其他表不会出现缓存失效情况) mysql> update t_topic set is_active = 0 where id ='00011adc-64ca-11e9-95e5-00163e06f891';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 缓存失效, 查询时间又变为了2秒左右, 当然数据也发生了变化 mysql> select count(*) from t_topic where is_delete = 0 and is_active = 1;
+----------+
| count(*) |
+----------+
| 9999997 |
+----------+
1 row in set (2.24 sec)
# 再次查询, 上次结果缓存住了 mysql> select count(*) from t_topic where is_delete = 0 and is_active = 1;
+----------+
| count(*) |
+----------+
| 9999997 |
+----------+
1 row in set (0.00 sec)
Django分页查询:
# 第一次 (2.276) SELECT COUNT(*) AS `__count` FROM `t_topic` WHERE (`t_topic`.`is_delete` = 0 AND `t_topic`.`is_active` = 1); args=(False, True)
(0.038) SELECT `t_topic`.`id`, `t_topic`.`name`, `t_topic`.`owner_id`, `t_topic`.`cover`, `t_topic`.`background`, `t_topic`.`describe`, `t_topic`.`create_time`, `t_topic`.`update_time` FROM `t_topic` WHERE (`t_topic`.`is_delete` = 0 AND `t_topic`.`is_active` = 1) ORDER BY `t_topic`.`create_time` DESC LIMIT 100 OFFSET 1000; args=(False, True)
# 第二次, 明显快多了 (0.030) SELECT COUNT(*) AS `__count` FROM `t_topic` WHERE (`t_topic`.`is_delete` = 0 AND `t_topic`.`is_active` = 1); args=(False, True)
(0.038) SELECT `t_topic`.`id`, `t_topic`.`name`, `t_topic`.`owner_id`, `t_topic`.`cover`, `t_topic`.`background`, `t_topic`.`describe`, `t_topic`.`create_time`, `t_topic`.`update_time` FROM `t_topic` WHERE (`t_topic`.`is_delete` = 0 AND `t_topic`.`is_active` = 1) ORDER BY `t_topic`.`create_time` DESC LIMIT 100 OFFSET 1200; args=(False, True)
# 再次修改数据 mysql> update t_topic set is_active = 0 where id ='000122d9-64ca-11e9-95e5-00163e06f891';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 更改数据后 (2.287) SELECT COUNT(*) AS `__count` FROM `t_topic` WHERE (`t_topic`.`is_delete` = 0 AND `t_topic`.`is_active` = 1); args=(False, True)
(0.051) SELECT `t_topic`.`id`, `t_topic`.`name`, `t_topic`.`owner_id`, `t_topic`.`cover`, `t_topic`.`background`, `t_topic`.`describe`, `t_topic`.`create_time`, `t_topic`.`update_time` FROM `t_topic` WHERE (`t_topic`.`is_delete` = 0 AND `t_topic`.`is_active` = 1) ORDER BY `t_topic`.`create_time` DESC LIMIT 100 OFFSET 1200; args=(False, True)
# 再次查询 (0.033) SELECT COUNT(*) AS `__count` FROM `t_topic` WHERE (`t_topic`.`is_delete` = 0 AND `t_topic`.`is_active` = 1); args=(False, True)
(0.041) SELECT `t_topic`.`id`, `t_topic`.`name`, `t_topic`.`owner_id`, `t_topic`.`cover`, `t_topic`.`background`, `t_topic`.`describe`, `t_topic`.`create_time`, `t_topic`.`update_time` FROM `t_topic` WHERE (`t_topic`.`is_delete` = 0 AND `t_topic`.`is_active` = 1) ORDER BY `t_topic`.`create_time` DESC LIMIT 100 OFFSET 1000; args=(False, True)
说明缓存还是蛮有用的吗, 起码在分页上, 非常棒 ! 但是把那个缓存大小设置成最小缓存了, 但一些其他的基础查询可能还是要被缓存到, 但这是我能想到最好的解决办法了, 后期不知道能否重写下分页的类, 自己做下本地缓存, 在类发生变更的情况下重新查询 COUNT.
我开发时还使用了JWT的登录验证包, 有个比较完蛋的问题是, 每次登录用户请求一次结果都需要把用户信息查询一次, 也没个缓存啥的, 下篇文章介绍下使用redis来缓存登录用户的基本信息, 对了说下, Django里的request.user这个还是相当好用的