数据库中亿级数据要求检索秒级返回

Hsinglover

已于 2023-01-03 16:05:53 修改

阅读量3.8k

点赞数

文章标签：数据库 mysql python 大数据

于 2023-01-03 15:35:59 首次发布

本文链接：https://blog.csdn.net/Hsinglover_java/article/details/128533109

版权

提示：最近有个新需求，需要在mysql数据库中插入亿级数据，同时在查询时要求秒级返回。我的想法是第一步要在数据库中插入亿级数据，第二步给字段加索引以达到查询秒级返回的效果。

文章目录

前言
一、在mysql数据库中建表，并插入亿级数据
- 1.建表语句
- 2.插入亿级数据
二、亿级数据插入完毕后，按需添加索引
- 1.按需增加索引
- 2.秒级返回
总结

前言

亿级数据的秒级返回，比较困难的是快速在数据库中插入亿级数据，所以这一步花了很多时间来处理。下面分两步来进行，第一步插入亿级数据，第二步给字段增加索引。注意，建表时不要加索引，这样会让insert执行时慢很多。

提示：以下是本篇文章正文内容，下面案例可供参考

一、在mysql数据库中建表，并插入亿级数据

假设插入1000W条数据，如果没有索引，可能只需要生成1G的空间，如果有了索引，那么可能额外再生成0.5G的索引数据，同时mysql服务器还要计算这些索引，因此，如果表存在索引，插入数据时，受CPU计算索引数据及硬盘写入索引数据的影响，会更慢。所以先建表。

1.建表语句

代码如下（示例）：

DROP TABLE IF EXISTS `k_user`;
CREATE TABLE `k_user`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '账号',
  `pass_word` varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '密码',
  `nick_name` varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '昵称',
  `create_time` datetime(0) DEFAULT NULL COMMENT '创建时间',
  `phone` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '手机号码',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 356 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

2.插入亿级数据

首先尝试过用java插入数据，结果非常慢，代码就不贴了；后面选择的用python来造数据，前提要有python环境。
代码如下（示例）：

# 向mysql的数据库表中插入1亿条数据
import MySQLdb
import MySQLdb.cursors
import random
import time

# 批量插的次数
loop_count = 10000
# 每次批量查的数据量
batch_size = 10000
success_count = 0
fails_count = 0
# 数据库的连接
# 使用 SSCursor (流式游标)，避免客户端占用大量内存。(这个 cursor 实际上没有缓存下来任何数据，它不会读取所有所有到内存中，它的做法是从储存块中读取记录，并且一条一条返回给你。)
conn = MySQLdb.connect(host="{IP}",
                       user="{帐号}",
                       passwd="{密码}",
                       db="{数据库名}",
                       port="{端口}",
                       cursorclass=MySQLdb.cursors.SSCursor)
chars = 'AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTtUuVvWwXxYyZz'
digits = '0123456789'


def random_generate_string(length):
    return ''.join(random.sample(chars, length))


def random_generate_number(length):
    if length > len(digits):
        digit_list = random.sample(digits, len(digits))
        digit_list.append(random.choice(digits))
        return ''.join(digit_list)
    return ''.join(random.sample(digits, length))


def random_generate_data(num):
    c = [num]
    phone_num = 13100000000

    def _random_generate_data():
        c[0] += 1
        return (c[0], "name_" + str(random.randrange(100000)),
                random_generate_string(20),
                "nick_name_" + str(random.randrange(100000)), 
                time.strftime("%Y-%m-%d %H:%M:%S"),
                phone_num + c[0],)

    return _random_generate_data


def execute_many(insert_sql, batch_data):
    global success_count, fails_count
    cursor = conn.cursor()
    try:
        cursor.executemany(insert_sql, batch_data)
    except Exception as e:
        conn.rollback()
        fails_count = fails_count + len(batch_data)
        print(e)
        raise
    else:
        conn.commit()
        success_count = success_count + len(batch_data)
        print(str(success_count) + " commit")
    finally:
        cursor.close()


try:
    # user表列的数量
    column_count = 6

    # 插入的SQL
    insert_sql = "replace into k_user(id, name, pass_word, nick_name, create_time, phone) values (" + ",".join(
        ["%s" for x in range(column_count)]) + ")"
    batch_count = 0
    begin_time = time.time()
    for x in range(loop_count):
        batch_count = x * batch_size + 1
        # print(batch_count)
        gen_fun = random_generate_data(batch_count)
        batch_data = [gen_fun() for x in range(batch_size)]
        execute_many(insert_sql, batch_data)
    end_time = time.time()
    total_sec = end_time - begin_time
    qps = success_count / total_sec
    print("总共生成数据： " + str(success_count))
    print("总共耗时(s): " + str(total_sec))
    print("QPS: " + str(qps))
except Exception as e:
    print(e)
    raise
else:
    pass
finally:
    pass