数据库优化(Django Mysql)--示例

最新推荐文章于 2024-11-20 19:02:06 发布

DefaultTest

最新推荐文章于 2024-11-20 19:02:06 发布

阅读量693

点赞数 2

分类专栏： python web 文章标签： mysql django

本文链接：https://blog.youkuaiyun.com/DefaultTest/article/details/107447717

版权

python web 专栏收录该内容

9 篇文章

订阅专栏

本文介绍了针对Django应用中MySQL数据库的优化方法，包括使用django的shell和调试工具查看SQL，添加索引，选择合适字段类型，利用缓存，理解并优化QuerySet的使用，减少不必要的查询，以及批量操作。还提到了实际案例中的索引冲突问题及解决办法，以及如何配置MySQL日志。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

数据库优化–示例

1.背景

通过监控软件可以明确的定位某个HTTP协议的API的响应时间在2-3秒(一般HTTP协议的API要控制在200ms以内)，显然需要优化。

服务是一个djnago后台服务，采用原生ORM框架。

另外，该API内部业务逻辑并不复杂，主要是做了MySQL数据库I/O的全量查询。且查询中有很多级联查询。

2.说明

一个HTTP响应的优化可以从很多层面去做，这里单纯从业务代码和SQL语句的角度去优化。

3.分析

实际工作场景，监控到API响应慢一般是由运维等其他人员提出，作为开发人员需要用开发场景下的工具来进行复测和验证。
django中数据库性能分析工具很多，常见如(下列同一个组织出品)：
- django-debug-toolbar：github
  - 场景：开发测试阶段
  - 限制：Debug=True 才加载该模块和URL地址。
- silk： github
  - 场景：开发测试阶段；服务交互和用户使用后分析；封装性更好一点。
  - 限制：需要使用数据库，数据库迁移
采用silk
可以直接通过上述工具，清晰查看request/response 和 SQL 耗时情况，以及详细的函数调用堆栈和数据库查询语句。

4.调优思路

减少外部I/O
减少冗余调用
优化耗时逻辑

5.数据库优化的方式 – django ORM

参考官网

0. 性能调优工具

1.django的shell (Debug=True), 查看django执行的原生SQL语句

>>> from django.db import connection
>>> connection.queries

2.`explain()`查看某个语句执行情况

>>> Blog.objects.filter(title='My Blog').explain()

3.借助三方工具

django-debug-toolbar
silk

1.标准的数据库优化技术

1.1 添加必要的索引

1.数据库优化，首先可选索引；使用 `Meta.indexes` 或 `Field.db_index` 不同级别的设置

2.在频繁进行下列查询方式的操作上添加

filter
exclude
order_by 等

1.2 使用合适的字段类型

1.3 缓存技术

下面是针对django的ORM来谈

2.理解QuerySet

2.1 理解 `QuerySet` 赋值

1.惰性查询

创建、生成QuerySet 对象时，不会发生数据库查询；
只有需要 “输出”结果的时候才会发生数据库查询；

2.何时真正赋值(下面是展开的几个例子)

QuerySet是迭代对象，发生迭代的那一刻就会查询数据库。
- 如果要做判断「某个记录是否存在」或「模型类对应的表是否为空」的查询用exists()，在转换成SQL语句的时候会加上limit的限制。
- 不要用for e in Entry.objects.all(): + if xxx == xx 这样的操作，会查询处该表的所有记录。
- 不要用bool(qs) 或 if qs: 这样的判断方式，也会查询所有符合条件的再做判断。
QuerySet可以被切片，语法类似python的array-slicing。切片时不会触发数据库查询，等价于SQL的 LIMIT + OFFSET 操作，返回值仍然是一个QuerySet对象。(注意：在django shell中操作时，如果没有对切片返回的结果进行赋值，那么默认会进行output，这就会发生数据库查询)
- 注意：不支持负数索引和切片
- 注意，如果切片中加step参数，那么返回的是lsit对象而不再是QuerySet；如果是索引查询，返回的结果ModelObject，等价于[0:1].get()，不存在时肯能会报错。
- 由于切片操作存在一定的“模棱两可”的可能性，所以禁止对切片进一步filter or order_by。
repr() 在交互式终端中执行，会触发QuerySet 执行
len() 会触发QuerySet 执行，返回list长度
bool() 或 if <statement> 判断 QuerySet 是否有期望的结果

3 缓存(QuerySet级别)

每一个QuerySet 包含一个最小的数据库访问缓存。

查询集使用缓存

新创建一个QuerySet 对象，它的缓存为空；当发生一次针对该QuerySet查询之后，会将这个结果缓存起来，下次发生同样的查询时，Django不再查询数据库而是直接利用这次的缓存，合理利用可以提高性能。
- 利：合理利用减少数据库查询，提高性能；
- 弊：当数据库发生update or delete 操作时，若要获取最新信息可能会误用缓存，造成业务逻辑错误；
查询集不使用缓存

当仅仅计算查询集部分结果时，会去检查缓存，如果这部分没有被集成那么子序列查询返回的部分就没有被缓存。具体来说，使用切片和索引将不会被集成。

说明：上面这句话是翻译的，自己对这块儿理解的不够透彻。我通过比较官方文档和我自己实践验证来看，我重新理解如下(可能有失偏颇)：

存在没有缓存的情况，就是QuerySet 对象没有被整体“计算查询”过，就会发生没有被集成，就不会发生缓存。
还是看官网的几个例子比较好理解一点。
切片和索引不总是不会产生缓存。如果发生对查询集被完整遍历情况，那么再对该查询集索引的时候，也不会产生新的数据库查询。

在这里插入图片描述

2.2 理解缓存的属性

除了缓存整个QuerySet外，还可以缓存ORM对象的属性的结果，通常不能被调用的属性将被缓存，可以被调用的属性不会被缓存。

# 下面示例从官网摘录。说明：Blog是Entry的外键，Authors和Entry是多对多关系。
>>> entry = Entry.objects.get(id=1)
>>> entry.blog   # Blog object is retrieved at this point
>>> entry.blog   # cached version, no DB access


>>> entry = Entry.objects.get(id=1)
>>> entry.authors.all()   # query performed
>>> entry.authors.all()   # query performed again

Django 模板中使用，可调用方法也不加()，不要弄混。
自定义属性时，可以使用django的cached_property 装饰器

2.3 模板中使用`with`

2.4 使用`iterator()`

当要查询的结果包含大量的对象，使用QuerySet 的cache 会占用大量的系统内存时，且这些结果只使用一次的时候，可以考虑使用.iterator()。
注意：如果要重复使用这些结果，不推荐使用。

2.5 使用`explain()` 来查看数据库查询的细节

但，我简单试了一下，好像不太明显，这地方我需求不是特别大，暂时就不细致研究了。

3.数据库做数据库的事情，而不是交给python (重点)

3.1 使用`filter` & `exclude` 进行数据库层面过滤

3.2 使用 `F` 查询同一个model的不同字段比较

3.3 用好聚合查询

3.4 使用原生SQL

4.使用unique或indexed约束的列(字段)来查询单个对象

5.一次查询所有需要的数据而不是分多次部分查询

5.1 使用 `QuerySet.select_related()` and `QuerySet.prefetch_related()`

1.使用原生managers 或自定义 managers

2.使用 `select_related` & `prefetch_related`

6.不要查询不需要的数据

1.使用 `QuerySet.values()` and `QuerySet.values_list()`

2.使用 `QuerySet.defer()` and `QuerySet.only()`

3.使用 `count()` 代替 `len(qs)`

4.使用 `exists()`，如果只是判断是否对应的实例

5.不要过度使用 `count()` 或 `exists()`

6.使用 `update()` and `delete()`

7.直接使用外键的值，代替先查出外键对象再通过`.id`取值

8.如果不关心结果的顺序，不要排序

6.使用批量的方法

6.1 批量创建：`bulk_create`

6.2 批量更新: `bulk_update`

6.3 批量插入：`add` 多对多字段

6.4 批量删除：`remove`多对多字段

6.结合实际，具体方案

6.1 添加索引

对order_by()操作的时间戳部分添加索引；

6.2 级联部分，采用`prefetch_related` 和 `select_related`

多对多查询和一对多查询，使用prefetch_related
外键查询(多对一，一对一)，使用select_related
另外，一定要确保上述的操作是生效的。复杂的级联查询很容易让上面的操作失效，这一点需要注意，避免出现loop查询。

增补：处理过程中的小插曲

1.调整过所以，中途`migrate`报错：

MySQLdb._exceptions.OperationalError: (1061, "Duplicate key name 'competition_stage_start_time_d773f483'")

1.1 明确问题：索引(key)重复，要migrate的值已经存在

查询mysql中对应的表的索引情况：show indexes from <table_name>，结果示例：

在这里插入图片描述

看到有一个字段名：Key_name , 里面有一个值就是我们报错信息中的值。

1.2 解决问题：

删除：drop index <index name> on <table name>;
重新migrate, 成功。

1.3 参考：[Django database migration error: duplicate key]

2.mysql on mac 配置日志

2.0 说明：mysql是使用`brew install mysql`安装，存在一些弊端。

2.1 查看配置文件：`mysqld --verbose --help | grep -A 1 "Default options"`

配置文件过于简陋，常见的配置都没有。

# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
mysqlx-bind-address = 127.0.0.1

需要我们自己添件我们的配置去覆盖掉默认配置。
这里采用了全局配置：/etc/my.cnf, 该文件不存在，手动创建了一个，去网上找了一个模板。取消下面两行的注释：

general_log_file        = /var/log/mysql/mysql.log
general_log             = 1

修改配置文件后，要重启mysql服务brew services restart mysql
查看日志：tail -f /var/log/mysql/mysql.log

2.2 默认安装的mysql直接可以`mysql -uroot`登陆，显然不安全，建议处理。

参考：Install MySQL on Mac OSX using Homebrew
- 另外，其中一个指令被移除。mysql_install_db
参考：Your password does not satisfy the current policy requirements
执行：mysql_secure_installation
提示我们配置密码，如果不想配置的密码过于复杂，可以手动修改密码验证的规则和水平.

# 默认情况
mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)
# 手动修改
mysql> SET GLOBAL validate_password.length = 6;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL validate_password.number_count = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL validate_password.policy=LOW;
Query OK, 0 rows affected (0.00 sec)

# 调整之后：
mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password.check_user_name    | ON    |
| validate_password.dictionary_file    |       |
| validate_password.length             | 6     |
| validate_password.mixed_case_count   | 1     |
| validate_password.number_count       | 0     |
| validate_password.policy             | LOW   |
| validate_password.special_char_count | 1     |
+--------------------------------------+-------+
7 rows in set (0.01 sec)