mysql千万数据加索引

Bertha 。 2023-09-25 09:44 37阅读 0赞

## mysql批量更新多表数据 ##

mysql批量更新多表数据

update库2.tb,库1.tbset库2.tb.body=库1.tb.bodywhere库2.tb.id=库1.tb.id

![mysql千万数据加索引\_mysql批量更新千万数据][mysql_mysql]

## 千万数据量的表, 执行根据ID去update一条数据不会很慢吧 ##

mysql批量更新如果一条条去更新效率是相当的慢,循环一条一条的更新记录,一条记录update一次，这样性能很差，也很容易造成阻塞。

mysql批量更新共有以下四种办法

1、.replaceinto批量更新

replaceintotest\_tbl(id,dr)values(1,'2'),(2,'3'),...(x,'y');

2、insertinto...onduplicatekeyupdate批量更新

insertintotest\_tbl(id,dr)values(1,'2'),(2,'3'),...(x,'y')onduplicatekeyupdatedr=values(dr);

3.创建临时表，先更新临时表，然后从临时表中update

createtemporarytabletmp(idint(4)primarykey,drvarchar(50));

insertintotmpvalues(0,'gone'),(1,'xx'),...(m,'yy');

updatetest\_tbl,tmpsettest\_tbl.dr=tmp.drwheretest\_tbl.id=tmp.id;

注意：这种方法需要用户有temporary表的create权限。

4、使用mysql自带的语句构建批量更新

mysql实现批量可以用点小技巧来实现:

UPDATEyoiurtable

SETdingdan=CASEid

WHEN1THEN3

WHEN2THEN4

WHEN3THEN5

WHEREidIN(1,2,3)

这句sql的意思是，更新dingdan字段，如果id=1则dingdan的值为3，如果id=2则dingdan的值为4……

where部分不影响代码的执行，但是会提高sql执行的效率。确保sql语句仅执行需要修改的行数，这里只有3条数据进行更新，而where子句确保只有3行数据执行。

如果更新多个值的话，只需要稍加修改：

UPDATEcategories

SETdingdan=CASEid

WHEN1THEN3

WHEN2THEN4

WHEN3THEN5

title=CASEid

WHEN1THEN'NewTitle1'

WHEN2THEN'NewTitle2'

WHEN3THEN'NewTitle3'

WHEREidIN(1,2,3)

到这里，已经完成一条mysql语句更新多条记录了。

php中用数组形式赋值批量更新的代码：

$display\_order=array(

1=>4,

2=>1,

3=>2,

4=>3,

5=>9,

6=>5,

7=>8,

$ids=implode(',',array\_keys($display\_order));

$sql="UPDATEcategoriesSETdisplay\_order=CASEid";

foreach($display\_orderas$id=>$ordinal)\{

$sql.=sprintf("WHEN%dTHEN%d",$id,$ordinal);

$sql.="ENDWHEREidIN($ids)";

echo$sql;

这个例子，有8条记录进行更新。代码也很容易理解，你学会了吗

更新100000条数据的性能就测试结果来看，测试当时使用replaceinto性能较好。

replaceinto和insertintoonduplicatekeyupdate的不同在于：

replaceinto操作本质是对重复的记录先delete后insert，如果更新的字段不全会将缺失的字段置为缺省值，用这个要悠着点否则不小心清空大量数据可不是闹着玩的。

insertinto则是只update重复记录，不会改变其它字段。

## mysql 批量更新10000+的数据，有什么效率比较高的方法 ##

首先， 插入上万条数据，对于数据库来说并不是“很大”的工作量，一般配置的笔记本电脑都可以在1分钟内完成。 所以最简单、最灵活的办法还是写SQL语句。

如果不希望DB编译器每次执行都编译SQL的话，可以使用存储过程，直接调用，性能上会好很多。也比较简单。

(几万条数据怎么地也得要时间去处理，所以不可能特别快的。)

如果由于各种原因，导致这个插入还是很慢， 而且你的MYSQL又是5.0以上版本的话，可以使用BulkCopy来进行批量操作。

BulkCopy的原理就是Client直接把一个数组(DataTable)传给DB，然后传入表名，所有的编译、操作都由DB自己完成，效率很高。

引用MySql.Data.dll ， 调用MysqlBulkCopy函数即可。

这个函数在处理海量数据插入的时候效率尤为明显， 小量数据反而没什么优势，而且由于传入的DataTable格式必须和表的字段一模一样(空的列也要传进去)，导致C\#要写很多代码来构造这个数组，所以要你自己权衡用还是不用。

我在自己的电脑上批量插入一亿条数据，Insert写法大概需要1小时,BulkCopy大概只需要5分钟。同问。。。

![mysql千万数据加索引\_mysql批量更新千万数据\_02][mysql_mysql_02]

## mysql数据批量更新，谢谢 ##

mysql数据批量更新

update a set b=1 where id in ($ids) and id !=$\_GET\['id'\];

update a set b=0 where id not in ($ids) and id !=$\_GET\['id'\];update a set b=1 where id in ($ids) and id !=$\_get\['id'\]; update a set b=0 where id not in ($ids) and id !=$\_get\['id'\]; 这样应该可以解决你问题，怎么把两条语句合并，我就不清楚了，你查查

[mysql_mysql]: https://img1.baidu.com/it/u=3321170212,4268842933&fm=26&fmt=auto
[mysql_mysql_02]: https://img2.baidu.com/it/u=2488188528,1079400589&fm=15&fmt=auto

发表评论取消回复

表情：

评论列表（有 0 条评论，37人围观）

还没有评论，来说两句吧...

相关阅读

相关 MySQL 千万级数据处理

目录一、优化二、案例 -------------------- 一、优化单表优化除非单表数据未来会一直不断上涨，否则不要一开始就考虑拆分，

太过爱你忘了你带给我的痛/ 2024年04月01日 13:28/ 0 赞/ 87 阅读

相关 MySQL 千万级数据迁移

背景介绍项目中表数据存在超过50%的逻辑删除数据，影响查询性能，本次计划将逻辑有效的数据迁移到新表，使得表数据量减少，索引文件减少，提高查询效率。大体思路通过存储

今天药忘吃喽~/ 2024年03月30日 14:34/ 0 赞/ 75 阅读

相关 MySQL 添加千万条数据

前言平时学习过程中经常会看到千万级数据库索引优化，千万级数据查询优化等，但是我们平时很少会有千万级数据的场景，这篇文章可以用来生成千万级数据用作学习。后续将数据表连接到

忘是亡心i/ 2023年10月14日 15:59/ 0 赞/ 6 阅读

相关 MySQL 百万级/千万级建立索引

问题：今天接到公司项目经理提出针对\\政府项目管理平台的合同管理功能模块提出性能优化要求。原因：由于\\政府项目管理平台数据存储由oracle 10g 迁移至开源MySQL

拼搏现实的明天。/ 2023年10月10日 16:24/ 0 赞/ 44 阅读

相关 MySql 索引数据结构，千万级大表，关于性别及年龄字段是否需要加索引？

阅读目录 InnoDB下索引的数据结构，索引如何起作用的，如何更好的利用索引提高效率？ 1 什么是索引 MySql 千万级大表，关于性别及年龄字段

迈不过友情╰/ 2023年10月07日 22:21/ 0 赞/ 34 阅读

相关 mysql千万数据加索引

mysql批量更新多表数据 mysql批量更新多表数据 update库2.tb,库1.tbset库2.tb.body=库1.tb.bodywhere库2.tb.id=库

Bertha 。/ 2023年09月25日 09:44/ 0 赞/ 38 阅读

相关 mysql模糊查询千万数据

mysql模糊查询怎么查多个字段下面两种情况： 1.返回值：由全体出入参数合并在一起而得到的字符串。只要输入的参数中有NULL值，就返回NULL。CONCAT允许只有

ゞ浴缸里的玫瑰/ 2023年09月25日 09:30/ 0 赞/ 51 阅读

相关 MySQL 快速批量创建千万条数据千万级数据

MySQL 快速批量创建千万条数据千万级数据一、实现思路 1、创建一张users表，数据库引擎调整为： MyISAM 2、使用存储过程实现，用循环语句，批

痛定思痛。/ 2023年01月18日 14:30/ 0 赞/ 35 阅读

相关 mysql千万级数据量根据索引优化查询速度

（一）索引的作用索引通俗来讲就相当于书的目录，当我们根据条件查询的时候，没有索引，便需要全表扫描，数据量少还可以，一旦数据量超过百万甚至千万，一条查询sql执行往往需要几十

灰太狼/ 2022年09月25日 04:20/ 0 赞/ 198 阅读

相关 Mysql千万级别数据优化方案

目录目录... 1 一、目的与意义... 2 1)说明... 2 二、解决思路与根据（本测试表中数据在千万级别）... 2 1)建立索引... 2 2)数据体现（

深藏阁楼爱情的钟/ 2022年08月09日 18:45/ 0 赞/ 219 阅读