文档章节

MySQL 大数据操作注意事项

netkiller-
 netkiller-
发布于 2014/05/16 10:40
字数 937
阅读 2579
收藏 98

MySQL 大数据操作注意事项

MrNeo Chen (netkiller)陈景峰(BG7NYT)


中国广东省深圳市龙华新区民治街道溪山美地
518131
+86 13113668890
+86 755 29812080
<netkiller@msn.com>

版权 © 2011, 2012, 2013, 2014 http://netkiller.github.io

版权声明

转载请与作者联系,转载时请务必标明文章原始出处和作者信息及本声明。

文档出处:
http://netkiller.github.io
http://netkiller.sourceforge.net

2014-05-16

摘要

1. 关于 delete

delete from mytable 必死无疑,你需要分批删除,尽量缩小每个批次删除的记录数,delete 是可以并行执行的,你可以同时运行多个删除操作

mysql> show processlist;
+--------+-----------------+---------------------+-----------+---------+-------+-----------------------------+--------------------------------------------------------+
| Id     | User            | Host                | db        | Command | Time  | State                       | Info                                                   |
+--------+-----------------+---------------------+-----------+---------+-------+-----------------------------+--------------------------------------------------------+
|      1 | event_scheduler | localhost           | NULL      | Daemon  |    52 | Waiting for next activation | NULL                                                   |
| 115986 | dba             | localhost           | example   | Query   |     0 | NULL                        | show processlist                                       |
| 117446 | dba             | localhost           | example   | Query   |    20 | updating                    | delete from mytable where OPEN_TIME like '2011.11.28%' |
| 117525 | dba             | localhost           | example   | Query   |     2 | updating                    | delete from mytable where OPEN_TIME like '2011.12.02%' |
| 117526 | dba             | localhost           | example   | Query   |    49 | updating                    | delete from mytable where OPEN_TIME like '2011.12.12%' |
| 117527 | dba             | localhost           | example   | Query   |     6 | updating                    | delete from mytable where OPEN_TIME like '2011.12.21%' |
| 117528 | dba             | localhost           | example   | Query   |    64 | updating                    | delete from mytable where OPEN_TIME like '2011.12.30%' |
| 117546 | dba             | localhost           | example   | Query   |    33 | updating                    | delete from mytable where OPEN_TIME like '2011.11.10%' |
+--------+-----------------+---------------------+-----------+---------+-------+-----------------------------+--------------------------------------------------------+
23 rows in set (0.00 sec)

2. 关于 update

在电商领域常常遇到一个问题“调价”,经常需要调整一批商品的价格, 程序猿一条预警搞定有没有?

update goods set price=price+10 where category_id = xxx

在开发,测试环境是可以通过测试的,一旦部署到生产环境,必死无疑

3. 关于创建索引

大表创建索引需要很久的时间,通常要经历 manage keys 与 copy to tmp table 的过程

mysql> show processlist;
+--------+-----------------+---------------------+----------+---------+-------+-----------------------------+------------------------------------------------------------------+
| Id     | User            | Host                | db       | Command | Time  | State                       | Info                                                             |
+--------+-----------------+---------------------+----------+---------+-------+-----------------------------+------------------------------------------------------------------+
|      1 | event_scheduler | localhost           | NULL     | Daemon  |    47 | Waiting for next activation | NULL                                                             |
| 115986 | dba             | localhost           | example  | Query   |     0 | NULL                        | show processlist                                                 |
| 118814 | dba             | 192.168.6.20:50459  | example  | Query   |     8 | copy to tmp table           | ALTER TABLE `mytable` ADD INDEX `modifiy_time` (`MODIFY_TIME`)   |
+--------+-----------------+---------------------+----------+---------+-------+-----------------------------+------------------------------------------------------------------+
17 rows in set (0.00 sec)

删除索引,也需要经理 copy to tmp table 过程,漫长的等待

mysql> show processlist;
+--------+-----------------+---------------------+--------------+---------+-------+-----------------------------+-------------------------------------------------+
| Id     | User            | Host                | db           | Command | Time  | State                       | Info                                            |
+--------+-----------------+---------------------+--------------+---------+-------+-----------------------------+-------------------------------------------------+
|      1 | event_scheduler | localhost           | NULL         | Daemon  |    11 | Waiting for next activation | NULL                                            |
| 115986 | dba             | localhost           | example      | Query   |     0 | NULL                        | show processlist                                |
| 118814 | dba             | 192.168.6.20:50459  | example      | Query   |     4 | copy to tmp table           | ALTER TABLE `mytable`	DROP INDEX `modifiy_time` |
+--------+-----------------+---------------------+--------------+---------+-------+-----------------------------+-------------------------------------------------+
17 rows in set (0.00 sec)

所以数据设计要深思熟虑,做到提前未雨绸缪,不要亡羊补牢

4. 关于 OPTIMIZE

OPTIMIZE 的操作是将当前表复制到临时表操作后再删除当前表,最后将临时表改名

mysql> show processlist;
+--------+-----------------+---------------------+---------------------------+---------+-------+-----------------------------+--------------------------+
| Id     | User            | Host                | db                        | Command | Time  | State                       | Info                     |
+--------+-----------------+---------------------+---------------------------+---------+-------+-----------------------------+--------------------------+
|      1 | event_scheduler | localhost           | NULL                      | Daemon  |    14 | Waiting for next activation | NULL                     |
| 115835 | dba             | 192.168.6.20:49664  | example                   | Query   |     9 | copy to tmp table           | OPTIMIZE TABLE `mytable` |
| 115986 | dba             | localhost           | example                   | Query   |     0 | NULL                        | show processlist         |
+--------+-----------------+---------------------+---------------------------+---------+-------+-----------------------------+--------------------------+
17 rows in set (0.00 sec)

5. 确保SELECT不被受阻

使用各种手段保证select操作不被受阻,只要select一直可以查询网站前端就能提供80%的功能,一旦select受阻一切都是浮云。

保证 select 操作优先于其他操作

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name  
SET col_name1=expr1 [, col_name2=expr2 ...]  
[WHERE where_definition]  
[ORDER BY ...]  
[LIMIT row_count]

update的时候增加 LOW_PRIORITY 参数,可以降低更新语句的优先级。

my.cnf

[mysqld]		
low_priority_updates=1

或者启动是添加--low-priority-updates参数

全局开启

SET @@global.low_priority_updates = 1;

适用于本次会话连接

SET @@session.low_priority_updates = 1;

© 著作权归作者所有

netkiller-

netkiller-

粉丝 706
博文 274
码字总数 383156
作品 10
深圳
部门经理
私信 提问
加载中

评论(11)

netkiller-
netkiller- 博主

引用来自“wharf_zhang”的评论

mysql应该,也可以改进一下,使得在这样的场景下,无论实际数据量有多大,都无需等待,毫秒搞定。如此就不存在什么影响了。
应该有很多人在做,一些大公司都会对mysql改进,只不过是不会公布代码而已。
netkiller-
netkiller- 博主

引用来自“WangInuze”的评论

MySQL 多少数据量,叫大数据呢
要从不同角度看,不同时代,不同技术
wharf_zhang
wharf_zhang
mysql应该,也可以改进一下,使得在这样的场景下,无论实际数据量有多大,都无需等待,毫秒搞定。如此就不存在什么影响了。
WangInuze
WangInuze
MySQL 多少数据量,叫大数据呢
letwang
letwang
分批update
netkiller-
netkiller- 博主

引用来自“CodingKu”的评论

引用来自“neo-chen”的评论

引用来自“心有灵犀”的评论

因为category_id是类型id,对应的goods可想而知有多少了。所以,一旦执行update,呵呵,那就慢慢等吧。
是的,我们不能允许任何SQL执行超过30秒,这种执行后果是我们不知道要多长时间。有时我们kill了该语句发现一只处于 Killed 状态,等也不是,重启也不是。

那应该怎么解决呢
缩小结果集
木川瓦兹
木川瓦兹

引用来自“neo-chen”的评论

引用来自“心有灵犀”的评论

因为category_id是类型id,对应的goods可想而知有多少了。所以,一旦执行update,呵呵,那就慢慢等吧。
是的,我们不能允许任何SQL执行超过30秒,这种执行后果是我们不知道要多长时间。有时我们kill了该语句发现一只处于 Killed 状态,等也不是,重启也不是。

那应该怎么解决呢
netkiller-
netkiller- 博主

引用来自“心有灵犀”的评论

因为category_id是类型id,对应的goods可想而知有多少了。所以,一旦执行update,呵呵,那就慢慢等吧。
是的,我们不能允许任何SQL执行超过30秒,这种执行后果是我们不知道要多长时间。有时我们kill了该语句发现一只处于 Killed 状态,等也不是,重启也不是。
心有灵犀
心有灵犀
因为category_id是类型id,对应的goods可想而知有多少了。所以,一旦执行update,呵呵,那就慢慢等吧。
Outshine
Outshine

引用来自“daoee”的评论

update goods set price=price+10 where category_id = xxx
这条语句在生产环境为何必死

同问,感觉很平常的一句sql啊?
MySQL 大数据操作注意事项

MySQL 大数据操作注意事项 http://netkiller.github.io/journal/mysql.parallel.html Mr. Neo Chen (netkiller), 陈景峰(BG7NYT) 中国广东省深圳市龙华新区民治街道溪山美地 518131 +86 131...

netkiller-
2014/05/16
1K
11
MySQL索引类型一览 让MySQL高效运行起来

本文介绍了七种MySQL索引类型。在数据库表中,对字段建立索引可以大大提高查询速度。通过善用这些索引,可以令MySQL的查询和运行更加高效。 索引是快速搜索的关键。MySQL索引的建立对于MySQL...

5W1H-
2013/06/13
104
0
企业主流MySQL高可用集群架构三部曲之PXC

前段时间,老张给大家介绍了企业中主流MySQL高可用集群架构三部曲中的前两部,有不了解的同学可以去访问我之前的博客内容。 第一部曲直通车>> 企业中MySQL主流高可用架构实战三部曲之MHA 第二...

superZS
2017/08/14
0
0
MySQL在线修改表结构pt-osc

MySQL在线修改表结构pt-osc 重所周知 MySQL的DDL操作操作是相比比较昂贵的。因为MySQL在修改表期间会阻塞任何读写操作。 基本上业务处于瘫痪。如果数据量较大可能需要好几个小时才能完成,无...

786376668
2017/06/07
0
0
MySQL索引类型一览 让MySQL高效运行起来

索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。下面介绍几种常见的MySQL索引类型。 在数据库表中,对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytabl...

anziguoer
2014/02/20
176
0

没有更多内容

加载失败,请刷新页面

加载更多

golang-字符串-地址分析

demo package mainimport "fmt"func main() {str := "map.baidu.com"fmt.Println(&str, str)str = str[0:5]fmt.Println(&str, str)str = "abc"fmt.Println(&s......

李琼涛
今天
4
0
Spring Boot WebFlux 增删改查完整实战 demo

03:WebFlux Web CRUD 实践 前言 上一篇基于功能性端点去创建一个简单服务,实现了 Hello 。这一篇用 Spring Boot WebFlux 的注解控制层技术创建一个 CRUD WebFlux 应用,让开发更方便。这里...

泥瓦匠BYSocket
今天
6
0
从0开始学FreeRTOS-(列表与列表项)-3

FreeRTOS列表&列表项的源码解读 第一次看列表与列表项的时候,感觉很像是链表,虽然我自己的链表也不太会,但是就是感觉很像。 在FreeRTOS中,列表与列表项使用得非常多,是FreeRTOS的一个数...

杰杰1号
今天
8
0
Java反射

Java 反射 反射是框架设计的灵魂(使用的前提条件:必须先得到代表的字节码的 Class,Class 类 用于表示.class 文件(字节码)) 一、反射的概述 定义:JAVA 反射机制是在运行状态中,对于任...

zzz1122334
今天
6
0
聊聊nacos的LocalConfigInfoProcessor

序 本文主要研究一下nacos的LocalConfigInfoProcessor LocalConfigInfoProcessor nacos-1.1.3/client/src/main/java/com/alibaba/nacos/client/config/impl/LocalConfigInfoProcessor.java p......

go4it
昨天
9
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部