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

面对亿级数据,MySQL硬的起来么? 很多粉丝问我:MySQL到底能支撑多少数据,是不是500万以上就不行了,查询就非常慢了?这个问题问得好。到底行不行呢?我觉得还是得通过实验来见证一下,mysql面对百万、千万、亿级别的数据时,查询到底行不行???test1表,结构比较简单,2个字段,都有索引。 插入1亿数据 无条件查询(耗时24秒) 耗时20多秒,如果给用户用,这个肯定是无法接受的。 走索引查询(耗时1毫秒) order_num 字段上面有索引,我们插入的时候,order_num 这个字段的值没有重复的。用这个字段查询一下看看效果,耗时1 阅读详情

提示:最近有个新需求,需要在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

二、亿级数据插入完毕后,按需添加索引

1.按需增加索引

1.添加primary key(主键索引)
alter table k_user add primary key(id);

2.添加unique(唯一索引)
alter table k_user add unique(name);

3.添加index(普通索引)
alter table k_user add index (index_name) (nick_name);

4.添加fulltext(全文索引)
alter table k_user add fulltext (create_time);

5.添加多列索引
alter table 表名 add index 索引名(index_name) (列名1,列名2…);

2.秒级返回

在这里插入图片描述

总结

以上就是今天要讲的内容,本文简单介绍了亿级数据秒级返回的实现,希望能对大家有所帮助。

本文参考的原文链接:https://blog.youkuaiyun.com/weixin_44984864/article/details/107141534

原文建表加了索引,insert非常慢,此文已经改良,能较快实现数据库中insert亿级数据。

mysql如何实现单表数亿级数据查询不卡? 首先,你要看你的数据表查询用的多还是增删改用的多。查询用的多就选myisam数据库引擎,查询性能据说是innoDB数据库引擎的6、7倍,我经过测试,300多万的单表数据查询,myisam花了大概0.00几秒,而innoDB则是花了1、2秒2、3秒的样子!这里有个地方,就是添加多个索引的时候会选择独立索引还是复合索引,我们选择独立索引,在单表数亿级的时候独立索引的是复合索引的数倍,我这里单表3亿,没有使用索引的时候大概查询需要好几个小时,使用复合索引需要半小时左右,使用独立索引则仅需0.0几秒! 阅读详情

相关推荐

场景应用:从数据库中查询1亿条数据供后端进行条件判断处理时,如何提高查询效率?

过综合考虑这些数据库层和代码层的优化策略,我们可以高效地查询和处理1亿条数据,并在后端进行条件判断处理。请根据具体需求和应用程序的架构选择合适的策略,并根据性能测试和监控结果进行调整和优化。这将确保系统在高负载条件下保持稳定性和性能。数据库层优化策略:为查询字段建立索引,以提高查询速度。如果数据库支持,根据日期或其他标准进行表分区,以减少查询的数据量。编写高效的SQL查询,只检索需要的数据字段,避免全表扫描。配置数据库服务器的参数,如缓冲池大小、查询缓存和连接池,以优化查询性能。

流楚丶格念的博客 6337

BAT公司万亿海量数据分页秒级查询落地方案实现

在这个互联网高速发展的时代,数据呈指数级增长,像国内BAT一样的大企业数据量积累已经达到万亿级别,对于这么大的数据量,该怎么做到分页的秒级甚至毫秒级的响应时效呢?我们该怎么存储设计以及查询设计呢?    本课程将讲解万亿海量级数据存储方案以及秒级查询方案,并且落地实现。该课程将采用循序渐进方式一步一步带大家实现该系统,中间将穿插一些技术知识点讲解,让大家实现系统的同时,更深入理解其中的技术点。该课程系统最终是一个可用的分页秒级查询落地实现项目,包含解决方案以及实现,商业价值极高。大家可以根据自己企业的特定需求,稍加改造就可以用到自己企业的项目中去。 开发环境概述 开发工具:IDEA本课程用到技术:Spring  Boot  2.1.0.RELEASESpring  Cloud Greenwich.SR5Mybatis、Redis、QuartzAOP、自定义注解、反射技术Openfeign、EurekaThreadLocalThymeleafjQuery、AjaxMaven等企业一线架构师讲授,代码在老师的指导下企业可以复用,提供企业解决方案。  版权归作者所有,盗版将进行法律维权。 

深度学习---双层神经网络中矩阵的运算及其含义记录

深度学习---双层神经网络中矩阵的运算及其含义记录神经网络的正向传播与反向传播正向传播第一层的权重矩阵第一层的输入矩阵Z^1^ = W^1^X^1^ + b^1^A^1^ = σ(Z^1^)第二层的权重矩阵第二层的输入矩阵Z^2^ = W^2^A^1^ + b^2^反向传播dz^2^ = A^2^ - Ydw^2^ = 1/n dz^2^A^1T^db^2^ = 1/n np.sum(dz^2...

qq_43228441的博客 3749

MySQL 单表亿级数据查询优化,达到秒级响应

最近应单用户画像业务需求,需要满足6个月单用户的天粒度收视情况,呈现近半年的收视趋势图。每天数据量大概在150w条左右,一个月大概4500w,6个月大概2.7亿条。要满足大数据量的秒级响应。由于系统采用的为mysql+mongo+oracle的多数据源的模式,多数据源管理起来复杂难度较大。系统也在逐渐往mysql数据库迁移。在此篇文章里就只聊mysql的十亿条数据的查询效率优化,说到优化不得不聊聊mysql数据库索引,以及数据库表结构设计。 mysql数据库索引:https://blog.youkuaiyun.com

weixin_37702260的博客 1万+

【STM32CubeIDE入门】(一)工程创建&工程配置

​写在前面:如果是刚接触stm32cubeide的同学推荐与这一篇文章共同阅读,能够更加快速认识上手stm32cubeide。 1、打开stm32cubeide,选择好工作目录进入主界面后,点击创建工程。

涛涛子的博客 1万+

用C++手把手教你连接SimNow期货模拟盘:从CTP API封装到第一个订单(附完整代码)

本文详细介绍了如何使用C++连接SimNow期货模拟盘,从CTP API的基础架构解析到实现第一个订单的完整流程。内容涵盖CTP API的封装、SimNow环境配置、交易通道实现、行情接收与处理等核心环节,并提供完整代码示例,帮助开发者快速掌握量化交易中的期货模拟盘连接技术。

weixin_27215337的博客 333

亿级数据多条件组合查询——秒级响应解决方案

概述 组合查询为多条件组合查询,在很多场景下都有使用。购物网站中通过勾选类别、价格、销售量范围等属性来对所有的商品进行筛选,筛选出满足客户需要的商品,这是一种典型的组合查询。在小数据量的情况下,后台通过简单的sql语句便能够快速过滤出需要的数据,但随着数据量的增加,继续使用sql语句,查询效率会直线下降。当数据量达到一定的量级,服务器将会不堪重负甚至面临挂掉的危险,并且大数据量的存储也成为了一个问...

编码强迫症患者 2万+

Autosar CAN 网络管理标准文档

Autosar CAN 网络管理标准文档

蚂蚁小兵 926

ES亿级数据检索优化,三秒返回突破性能瓶颈

「读者福利!2 TB 各类技术资源免费赠送」一、前言数据平台已迭代了三个版本,从刚开始遇到很多常见的难题,到终于有片段时间整理一些已完善的文档,在此分享以供需要的朋友参考,少走些弯路...

民工哥的博客 2134

分布式优化 (Distributed Optimization) 算法详解及案例分析

分布式优化是一种将优化问题分解为多个子问题并在多个计算节点上并行求解的方法。其目标是通过分布式计算资源高效地求解大规模优化问题。

闲人编程的博客 2714

Nova — GPU/vGPU 虚拟机

目录 文章目录目录云主机使用显卡的实现方式虚拟显卡物理显卡直通物理显卡虚拟化 云主机使用显卡的实现方式 采用虚拟显卡 采用物理显卡直通 采用物理显卡虚拟化 虚拟显卡 虚拟显卡技术,主要用于虚拟机 Remote Console 领域,如:VNC(Virtual Network Computing,虚拟网络计算机),它能将完整的窗口界面通过网络,传输到另一台计算机的屏幕上。 VNC 主要由 VNC server 和 VNC viewer 两部分组成。用户需先将 VNC server 安装在被远程操控的计算

烟云的计算 2431

elasticsearch 亿级数据检索案例与原理

    版权说明: 本文章版权归本人及博客园共同所有,转载请标明原文出处( https://www.cnblogs.com/mikevictor07/p/10006553.html ),以下内容为个人理解,仅供参考。 一、前言     数据平台已迭代三个版本,从头开始遇到很多常见的难题,终于有片段时间整理一些已完善的文档,在此分享以供所需朋友的 实现参考,少走些弯路,在此篇幅中偏重于ES的优化,关...

ahilll的博客 5941

Anaconda数据报错:Fatal error in launcher: Unable to create process using ‘“E:\Anaconda\python.exe“ “D:\

里面的pip.exe程序出现了问题,我这里直接删除,重新安装解决,上面已经手动删除pip.exe删除pip.exe,重新安装pip.exe用下面命令: 测试是否成功成功解决

Deng872347348的博客 1270

es对几十亿数据能达到秒级响应吗_分库代价高的情况下,如何优化ES解决亿级数据量检索...

数据平台已迭代三个版本,从一开始遇到很多常见的难题,到现在终于有片段时间整理一些已完善的文档,在此分享以供所需朋友的实现参考,但愿能帮助大家少走些弯路,在此篇幅中偏重于ElasticSearch的优化。一、需求说明项目背景:在一业务系统中,部分表每天的数据量过亿,已按天分表,但业务上受限于按天查询,并且DB中只能保留3个月的数据(硬件高配),分库代价较高。改进版本目标:数据能跨月查询,并且支持1年...

weixin_39926016的博客 300

arcgis计算栅格平均值

打开FC栅格数据和非水体矢量数据 利用矢量数据提取对应栅格的非水体部分 2.1打开空间分析工具à提取分析à按掩膜提取 输入对应栅格数据和矢量数据 2.2提取结果如下 进行计算FC平均值 3.1打开空间分析工具à多元分析à波段集统计 输入对应栅格和输出路径 3.2统计结果如下 ...

Z_W_H_的博客 5371

数学建模模型总结

这是本人在两年内准备参加数学建模竞赛搜集到的模型资料,已经经过了本人的筛选和整理,希望能够对准备参加数学建模竞赛的学弟学妹们提供一些帮助!

ElasticSearch 与向量数据库的结合实践:突破亿级大表查询瓶颈20241204

本篇博客深入解析了 ElasticSearch 与向量数据库结合处理亿级大表查询的优化方案,涵盖核心架构设计、实践案例及关键技术点,通过引入语义检索和混合查询技术,提供高效灵活的解决路径,并结合插图直观展示全貌,助您快速掌握这一解决方案。

Narutolxy的博客 2469

VB摄像头监控

该VB模块可对摄像头捕获的画面进行录像等,可在创建的窗口进行控制

es对几十亿数据能达到秒级响应吗_elasticsearch 亿级数据检索案例与原理

来源:mikevictor来源:https://www.cnblogs.com/mikevictor07/p/10006553.html一、前言数据平台已迭代三个版本,从头开始遇到很多常见的难题,终于有片段时间整理一些已完善的文档,在此分享以供所需朋友的实现参考,少走些弯路,在此篇幅中偏重于ES的优化,关于HBase,Hadoop的设计优化估计有很多文章可以参考,不再赘述。二、需求说明项目背景:在...

weixin_39752087的博客 346

pscad IEEE标准系统搭建说明

pscad IEEE标准系统搭建说明,包含典型的IEEE 9节点、14节点、30节点、39节点和118节点系统。内含标准测试系统的原始数据和潮流结果。

es建立索引后的排序返回结果_Elasticsearch 亿级数据检索优化,三秒返回突破性能瓶颈...

一、前言数据平台已迭代了三个版本,从刚开始遇到很多常见的难题,到终于有片段时间整理一些已完善的文档,在此分享以供需要的朋友参考,少走些弯路。此文篇幅会偏重于Elasticsearch的优化,关于HBase、Hadoop的设计优化估计有很多文章可以参考,不再赘述。二、需求说明项目背景:在一业务系统中,部分表每天的数据量过亿,已按天分表,但业务上受限于按天查询,并且DB中只能保留3个月的数据...

weixin_39792686的博客 281

elasticsearch transportclient count 很慢_ElasticSearch 亿级数据检索深度优化

一、前言数据平台已迭代三个版本,从头开始遇到很多常见的难题,终于有片段时间整理一些已完善的文档,在此分享以供所需朋友的实现参考,少走些弯路,在此篇幅中偏重于ES的优化,关于HBase,Hadoop的设计优化估计有很多文章可以参考,不再赘述。二、需求说明项目背景:在一业务系统中,部分表每天的数据量过亿,已按天分表,但业务上受限于按天查询,并且DB中只能保留3个月的数据(硬件高配),分库代价较高。改进...

weixin_31155097的博客 861

elasticsearch 查询_elasticsearch如何做到亿级查询3秒内返回?

作者丨mikevictor来源丨www.cnblogs.com/mikevictor07/p/10006553.html一、前言数据平台已迭代了三个版本,从刚开始遇到很多常见的难题,到终于有片段时间整理一些已完善的文档,在此分享以供需要的朋友参考,少走些弯路。此文篇幅会偏重于Elasticsearch的优化,关于HBase、Hadoop的设计优化估计有很多文章可以参考,不再赘述。二、需求...

weixin_39588252的博客 212

es查询size大 短时间数据查不出_elasticsearch 亿级数据检索案例与原理

一、前言数据平台已迭代三个版本,从头开始遇到很多常见的难题,终于有片段时间整理一些已完善的文档,在此分享以供所需朋友的实现参考,少走些弯路,在此篇幅中偏重于ES的优化,关于HBase,Hadoop的设计优化估计有很多文章可以参考,不再赘述。二、需求说明项目背景: 在一业务系统中,部分表每天的数据量过亿,已按天分表,但业务上受限于按天查询,并且DB中只能保留3个月的数据(硬件高配)...

weixin_42513296的博客 1627

能设值多个rowkey吗_Elasticsearch 亿级数据检索优化,三秒返回突破性能瓶颈

一、前言数据平台已迭代了三个版本,从刚开始遇到很多常见的难题,到终于有片段时间整理一些已完善的文档,在此分享以供需要的朋友参考,少走些弯路。此文篇幅会偏重于Elasticsearch的优化,关于HBase、Hadoop的设计优化估计有很多文章可以参考,不再赘述。二、需求说明项目背景:在一业务系统中,部分表每天的数据量过亿,已按天分表,但业务上受限于按天查询,并且DB中只能保留3个月的数据...

weixin_39930671的博客 227

向量数据库深度实践:从索引原理到亿级数据检索调优

摘要: 向量数据库通过高效存储与检索高维向量,实现快速相似性搜索,核心原理包括向量距离度量(如欧氏距离、余弦相似度)、索引结构(如IVF、HNSW)和分层检索流程。主流数据库如Milvus(开源大场景)、Pinecone(云原生高并发)、Weaviate(关联查询)各有优势,选型需结合业务需求。性能调优涵盖索引构建(参数如nlist、ef)、批量写入和查询精度平衡。典型应用包括大模型知识库(RAG)、图像检索和实时推荐系统,显著提升AI落地的效率与精度。

没事学AI的博客 1548
上一篇: 非maven项目在有外部jar包的情况下打jar包
Hsinglover
博客等级 码龄8年 2粉丝 4原创
评论 1
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值