MySQL更新优化记录

介绍
今天（8月5日，2015 5:34 PM）对数据库表结构进行调整，增加了多个领域，在现有数据刷新，刷新的内容是：与现有域的URL，然后更新型和typeid的新领域。后来，我写了一个脚本刷数据我是无知的，后运行脚本，怎么这么慢。

场景再现

创建表（` fuckspeed `
` UIN ` bigint（20）符号的非空值0，
id int（11）未签名的非空默认值0，
` URL ` varchar（255）不为空的默认，
类型int（11）未签名的非空默认值0，
` typeid ` varchar（64）不为空的默认，
......
关键` uin_id `（` UIN `，` ID `）
InnoDB引擎=默认的字符集utf8）；
表结构可能是以上（很多领域略），只有一个共同的指标uin_id表中，和下面的想法是当我更新：

首先，从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中，否则无法优化它。

The problems occurred in the update fuckSpeed set type={type}, typeid={typeid} where id={id}, here at the time of updating is used in queries, my MySQL version is 5.5, not explain update, otherwise I can verify what I said here, to update 32w+ data, each data will update each data about 0.2S, this too scary.

解决问题
当问题被发现时，它将更容易解决。

选择下列选择UIN，UIN，ID添加一个字段时，URL funkspeed其中ID > = 101和ID <= 200，然后更新；当使用更新fuckspeed集类型= {类型} { }，typeid typeid = u = {在} ID = {id}，使指数是了。

三，五，和两个改变了代码，试图启动一个进程，看看效果是什么。事实上，效果并没有改善。平均时间是30倍/秒，所以它可以在大约3小时内完成所有更新。

总结MySQL语句级优化：

1、读取性能检查表，数行在InnoDB数量，建议另做一个统计表，使用MyISAM做统计规律。一般来说，统计数据不会被应用得太精确。
2。尽量不要在数据库中执行操作。
三.避免负面查询和%前缀模糊查询。
4。不要在索引列中执行操作或使用函数。
5。不要在生产环境程序中以SELECT *的形式查询数据。只查询需要使用的列。
6。查询尽可能减少限制返回的行数，减少数据传输时间和带宽浪费。
7。WHERE子句尽可能地为查询列使用函数，因为查询列的函数的使用没有索引。
8。避免隐式类型转换，如字符类型必须使用''，数字类型不能使用''。
9。所有的SQL关键字都要大写，养成良好的习惯，避免重复编译SQL语句，造成系统资源的浪费。
当10。表是查询，记得把小的结果放在它前面，遵循小结果集驱动大结果集的原则。
11。打开慢速查询，并定期使用解释来优化SQL语句中的慢速查询。