目录
- 一:背景
- 二:排查过程
- 2.1: SQL慢查询定位
- 2.2: Python层面分析将String翻译成Int类型的原因
- 2.3: Python进行SQL执行时间检测出现的问题
- 三:总结
一:背景
- 新的业务上线后, 合作部门发现我们引擎执行完, 回调他们的时间明显增大了, 让我们排查一下原因。
二:排查过程
2.1: SQL慢查询定位
- 首先追查日志, 发现有一个SQL的执行时间大概3分钟, 因此判断是慢查询的原因导致的。
- 执行SQL如下:
select overdue_total from nc_cases where status=0 and uid = 12345678990;
- 检查SQL执行使用的索引, 发现没有用到 uid索引, 而是其使用了其他索引, 且扫描行数为500多万行。
- 查看uid的类型, 发现uid类型为varchar类型, 而自己使用的是数字, 因此导致索引失效。
- 将sql改成字符串类型, 再次查看是否使用索引, 以及扫描行数, 发现使用到了uid索引 且扫描行数为7行。
- 因此, 结果应该是自己写的sql中的uid类型不对, 导致的慢查询。
2.2: Python层面分析将String翻译成Int类型的原因
- 当前编写代码的方式, 发现即使自己%后面用的是str类型, 传进去也会变成int类型。
sql = "select overdue_total from nc_cases where status=0 and uid = %s" % str(uid)
rows = db.collection.select(sql)
- 调整编写代码的方式, 给%s 外层再套一层单引号, 测试后发现, 运行速度大大提升了。
sql = "select overdue_total from nc_cases where status=0 and uid = '%s'" % str(uid)
rows = db.collection.select(sql)
- 由于我使用的是DButils工具, 因此查询了一下官方写法
- 官方写法中, select语句不仅仅可以传递sql, 还可以传递一个values, 这个values中传递的是什么类型, 就会自动的将什么类型写入到SQL中。
- 例如下面的SQL, 列表中如果是字符串, 就会将这个字符串自动解析到sql语句中。这样类型不会发生改变。
sql = "select overdue_total from nc_cases where status=0 and uid = %s"
rows = db.collection.select(sql, ["12345678990"])
2.3: Python进行SQL执行时间检测出现的问题
- 分析完问题后, 我们需要用python语句验证SQL执行的时间, 因子我写了一个脚本测试, 两个SQL的执行效率。
sql_3 = "select overdue_total from nc_cases where status=0 and xiaoying_uid = %s " % str(12345678990)
sql_4 = "select overdue_total from nc_cases where status=0 and xiaoying_uid = '%s' " % str(12345678990)
start_time3 = time.time()
rows_3 = db.collection.select(sql_3)
end_time3 = time.time()
print(sql_3 + " use time is {}".format(end_time3 - start_time3))
start_time4 = time.time()
rows_4 = db.collection.select(sql_4)
end_time4 = time.time()
print(sql_4 + " use time is {}".format(end_time4 - start_time4))
- 当我运行发现, 执行的时间差别并不大, 这是为什么呢?
- 追查SQL工具的底层代码, 发现select()语句返回的是一个生成器对象, 在不遍历结果的条件下, 根本不会触发查询。
- 因此测试代码增加遍历语句
start_time3 = time.time()
rows_3 = db.collection.select(sql_3)
for row in rows_3:
overdue_total = row[0]
print(overdue_total)
end_time3 = time.time()
print(sql_3 + " use time is {}".format(end_time3 - start_time3))
start_time4 = time.time()
rows_4 = db.collection.select(sql_4)
for row in rows_4:
overdue_total = row[0]
print(overdue_total)
end_time4 = time.time()
print(sql_4 + " use time is {}".format(end_time4 - start_time4))
- 最终发现时间差别很大。
三:总结
- SQL慢查询问题, 首先使用explain定位使用的索引和扫描行数。
- python操作SQL语句的时候, 推荐使用官方标准格式。
- pyhton调用select语句的时候, 未必SQL就一定执行了, 如果select底层封装的是返回生成器对象, 则遍历的时候真正去数据库查询数据。