mysql的limit优化查询

small山炮

已于 2024-05-16 18:22:19 修改

阅读量158

点赞数

CC 4.0 BY-SA版权

文章标签： python mysql

于 2020-10-22 17:17:34 首次发布

本文链接：https://blog.youkuaiyun.com/jikechong/article/details/109226357

该博客讨论了如何优化MySQL中的LIMIT查询，特别是在结合ORDER BY子句时。通过一个Python函数展示了如何处理查询结果，更新数据并进行分页，以提高效率。同时，文中涉及了全局变量的使用和条件判断，确保数据处理的正确性。

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

mysql的limit优化查询：(python)

select uid,id from user_play_histories where id>%s order by id limit %s

id使用查出的id 例子：

def user_play_histories_insert_line(conn_mv, conn, medal_id):
    print "========q_user_play_histories begin=========="
    cur_mv = conn_mv.cursor()
    search_cnt = 2000
    global g_uids_all
    dt = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    t_sql = "select uid,id from user_play_histories where id>%s order by id limit %s"
    maxId = 0
    while True:
        cur_mv.execute(t_sql, (
            maxId,
            search_cnt,
        ))
        numrows = int(cur_mv.rowcount)
        if numrows <= 0:
            break
        else:
            uids_insert = dict()
            for i in range(numrows):
                row = cur_mv.fetchone()
                if g_uids_all.has_key(row[0]):
                    if g_uids_all[row[0]] & 0b00000010 == 0:
                        g_uids_all[row[0]] |= 0b00000010
                        uids_insert[row[0]] = dt
                else:
                    g_uids_all[row[0]] = 0b00000000 | 0b00000010
                    uids_insert[row[0]] = dt
                if maxId < row[1]:
                    maxId = row[1]
            insert_user_medals_line(conn, uids_insert, medal_id)  # 插入
            if numrows < search_cnt:
                break

    cur_mv.close()
    print "========q_user_play_histories end=========="