mysql 中遍历数据库数据, 变个写法速度提升真多.

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

› MySQL 5.5 Community Server

› MySQL 5.6 Community Server

› Percona Configuration Wizard

› XtraBackup 搭建主从复制

Great Sites on MySQL

› Percona

› MySQL Performance Blog

› Severalnines

推荐管理工具

› Sequel Pro

› phpMyAdmin

推荐书目

› MySQL Cookbook

MySQL 相关项目

› MariaDB

› Drizzle

参考文档

› http://mysql-python.sourceforge.net/MySQLdb.html

这是一个创建于 2691 天前的主题，其中的信息可能已经有所发展或是发生改变。

自己写了个存储过程, 每天定时处理日志表, 需要一条一条去取. 主键 id 是 int 自增.
以前是这样的, 统计总数再用 limit 取

SELECT count(1) into count_sum from t_day_log_bak_1;

SELECT 
x,x,x,x
INTO 
@x,@x,@x,@x
FROM 
t_day_log_bak_1 
LIMIT ?,1;

改了之后是这样的, 获取最大的 id, 再去用 id 遍历.

SELECT max(id) into count_sum from t_day_log_bak_1;

SELECT 
x,x,x,x
INTO 
@x,@x,@x,@x
FROM 
t_day_log_bak_1 
where id = ?;

本地测试 10000 条数据, 时间从 35.352s 缩短到 11.212s.
然后我试了一天的数据, 总时间直接快了五倍...

21 条回复 • 2017-06-28 19:07:58 +08:00

husky

2017-06-28 14:45:13 +08:00

这意思是第一个没用索引，改了以后用了？

johnny23

2017-06-28 14:50:22 +08:00 via iPhone

还是效率低

liprais

2017-06-28 14:50:40 +08:00

需要一条一条去遍历数据处理的需求一般都有更好的方法

snail00

2017-06-28 14:52:57 +08:00

@husky #1 差不多这个意思, 速度是上来了, 应该也不锁表了.

snail00

2017-06-28 14:53:16 +08:00

@johnny23 #2 还有什么优化的方法?

snail00

2017-06-28 14:53:59 +08:00

@liprais #3 还有一个任务我扔 nosql 上去处理了. 还有其他更好的办法吗?

liprais

2017-06-28 15:03:31 +08:00

@snail00 我没看到你遍历这些数据到底是要干啥

plusium

2017-06-28 15:28:18 +08:00

用游标会更快。搜索“ MySQL 游标”。

artandlol

2017-06-28 15:30:00 +08:00

类似 sql 查询缓慢出现率最高的一条。
如：
select xx from xx limit 5000,1000
这个 SQL 每次都是全表扫描，建议添加 1 个自增 id 做索引，将 SQL 改为如下，可以提高处理速度
select xx from xx where id>5000 and id<6000;

johnny23

2017-06-28 15:30:54 +08:00 via iPhone

@snail00 top 加 order 如何

snail00

2017-06-28 15:34:45 +08:00

@liprais #7 相当与把每天的日志统计处理再分下表, 后边都是业务代码, 查询更新之类的, 太乱, 就没贴.

snail00

2017-06-28 15:35:59 +08:00

@artandlol #9 我就是这个问题, 查任务的时候发现这个任务锁了几十万行, 改了之后就不锁了, cpu 占用也下来了.

snail00

2017-06-28 15:36:55 +08:00

@johnny23 #10 排序没必要吧, 这个会不会也全表扫描.

snail00

2017-06-28 16:25:05 +08:00

@plusium #8 真快了点, 我本地用 id 直接获取是, 354.378s, 用游标是 316.605s.

johnny23

2017-06-28 16:43:26 +08:00 via iPhone

@snail00 id 是自增 order by id 🈶️加成

cxbig

2017-06-28 17:04:00 +08:00

@snail00 从你#11 描述看这个表是日志？？？日志放数据库哪来的效率。。。赶快换 ELK 吧。

changwei

2017-06-28 17:55:38 +08:00 via Android

@artandlol 这种情况如果中间有删除怎么办呢？只能把删除改成软删除吗？

changwei

2017-06-28 17:56:44 +08:00 via Android

@artandlol 不对，软删除貌似也不行

snail00

2017-06-28 18:16:24 +08:00

@cxbig #16 设备有限, 现在就一台 EC2 一台 RDS, 我只能充分发掘 RDS 的潜力了..

0x8C

2017-06-28 18:19:21 +08:00

试试 tidb

chenqh

2017-06-28 19:07:58 +08:00 via iPhone

感觉是 max 和 count 导致的