仿真场景1:
若要
调整数据库中表的结构,请添加几个字段,然后刷新以前的数据。清爽的内容是匹配一个现有的域的URL,然后更新新添加的字段
类型和
运行。
后来,我写了一个shell
脚本来刷数据,在我无知之后运行shell脚本,怎么这么慢。
场景再现
复制代码代码如下所示:
创建表(` fuckspeed `
` UIN ` bigint(20)符号的非空值0,
id int(11)未签名的非空
默认值0,
` URL ` varchar(255)不为空的默认,
类型int(11)未签名的非空默认值0,
` typeid ` varchar(64)不为空的默认,
......
关键` uin_id `(` UIN `,` ID `)
InnoDB引擎=默认的字符集utf8);
The table structure is probably the above (a lot of fields omitted), and there is only one joint index uin_id in the table, and the following idea is when I update it:
首先,从id范围获得一定量的数据。
复制代码代码如下所示:
选择ID,URL funkspeed其中ID > = 101和ID <= 200;
遍历所有数据并更新每个数据
#第一数据
处理,获得型和typeid匹配
复制代码代码如下所示:
更新fuckspeed集类型= {类型} = { },typeid typeid id是{id}
然后按照上述思路进行,特别是更新缓慢,平均3 ~每秒5左右,我无言,我想看到更新的数据,共32w +,此更新下24h左右+,这是1天,量~ ~哭,想关于其中的问题肯定是。
发现问题
我首先考虑的是,是否只有一个
进程正在更新,导致它很慢,于是我
启动了5个进程,并对ID进行了分段,如下所示
复制代码代码如下所示:
update_url.sh / 010000。
update_url.sh / 1000020001。
update_url.sh / 2000130001。
update_url.sh / 3000240002。
update_url.sh / 4000350003。
运行之后,发现
速度仍然没有增加,或者每秒更新3~5次。想想也。插入数据之前不能花费时间。匹配SQL语句,当插入时应该是个问题。
再看一下我的SQL语句
复制代码代码如下所示:
选择ID,URL funkspeed其中ID > = 101和ID <= 200;
在这里,尝试在
命令行
执行它,结果如下
复制代码代码如下所示:
MySQL >选择ID,URL funkspeed其中ID > = 0和ID <= 200;
空集(0.18秒)
用了0.18秒。这时,我突然意识到关节指数并没有被我使用。联合索引生效的
条件是左边必须有一个字段,这是通过解释得到验证的。
复制代码代码如下所示:
MySQL >解释身份,URL funkspeed其中ID > = 0和ID <= 200;
+ + + + ------------- ------ --------------- ------ + --------- + + + + ------ -------- -------------
桌上型possible_keys | | | |关键key_len
参考行| | | |额外|
+ + + + ------------- ------ --------------- ------ + --------- + + + + ------ -------- -------------
funkspeed所有空| | | |空|空|空| 324746 |使用|
+ + + + ------------- ------ --------------- ------ + --------- + + + + ------ -------- -------------
1行集(0秒)
然后使用联合索引:
复制代码代码如下所示:
MySQL >选择UIN,ID,在funkspeed ID = 162 = 10023;
------------ ---------- + + +
| UIN | ID |
------------ ---------- + + +
| 10023 | 162 |
------------ ---------- + + +
1行集(0秒)
MySQL >解释选择在哪里,ID从funkspeed UIN = 10023和ID = 162;
+ + + + ------------- ------ --------------- ---------- + --------- + + + + ------------- ------ -------------
桌上型possible_keys | | | |关键key_len参考行| | | |额外|
+ + + + ------------- ------ --------------- ---------- + --------- + + + + ------------- ------ -------------
funkspeed REF uin_id | | | | uin_id | | 12 const,const 4使用指数| | |
+ + + + ------------- ------ --------------- ---------- + --------- + + + + ------------- ------ -------------
1行集(0秒)
您可以看到几乎第二次的外观,这次您可以基本上得出结论,问题是在索引中。
当我被选中时,有一小部分时间。每两个选择之间的ID差为10000,所以在这里可以忽略,除非我们将索引添加到ID中,否则无法
优化它。
问题的发生
复制代码代码如下所示:
更新fuckspeed集类型= {类型} = { },typeid typeid id是{id}
这也用于更新时
查询。我的MySQL版本是5.5,不解释更新。否则,它肯定能证实我说的话。在这里,我们需要更新32w +数据,所有的数据将被更新,并且每个数据约为0.2s,这太可怕了。
解决问题
当问题被发现时,它将更容易解决。
选择添加一个字段,UIN,以下
复制代码代码如下所示:
选择的UIN,ID,URL funkspeed其中ID > = 101和ID <= 200;
然后在更新时使用它
复制代码代码如下所示:
更新fuckspeed集类型= {型},{ },typeid typeid = u = {在} ID = {id}
因此使用索引。
三,五,和两个改变了代码,试图启动一个进程,看看效果是什么。事实上,效果并没有改善。平均时间是30倍/秒,所以它可以在大约3小时内完成所有更新。
模拟场景2:
6台PID字段要求写入相应的brand_id场
问题
背景:SQL项目6表写入相应brand_id现场根据PID领域。但这些表是一千万。我的
工作运行后,线路上的MySQL主从立即延迟!经过一个多小时,它被延迟到40分钟,只有超过一万行的数据更新:
复制代码代码如下所示:
更新的是美元美元
集brand_id = # newbrandid #
在PID = # PID #
和brand_id = 0
项目组的MySQL专家帮我分析它,因为PID字段没有索引,MySQL引擎扫描行行、同一列的PID值,然后更新数据,这是1000w +行扫描
磁盘数据执行SQL。更严重的是,有多少种不同的PID有在这10百万级的表,我要进行多少这些SQL。
我的同事给我的基于数据的ID field.1000线的SQL代码的垂直分表水平建议更新一次使MySQL引擎不需要扫描整个表,每一次,和数据库的压力是1 / 10000之前。我是主要的关键,指数一个索引,索引可以大大提高查询
性能,优化的SQL如下:
复制代码代码如下所示:
更新的是美元美元
集brand_id = # newbrandid #
在PID = # PID #
和brand_id = 0
我# startnum #和# endnum #之间
只有使用ID限制间隔,110百万字节的大表代码才能被纵向切割!而经过监控,仅10分钟就更新了一万多个数据,效率是以前的6倍多!更重要的是数据库负载均衡,健康运行的应用。
以上两个问题模拟场景再现分析mysql更新优化
策略,希望能对数据库中的人们有所帮助。