数据库中亿级数据要求检索秒级返回-Toy模板网

这篇具有很好参考价值的文章主要介绍了数据库中亿级数据要求检索秒级返回。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

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

前言

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