mysql大表更新sql的优化策略
问题sql背景：项目有6个表的要根据pid字段要写入对应的brand_id字段。但是这个其中有两个表是千万级别的。我的worker运行之后，线上的mysql主从同步立刻延迟了！运行了一个多小时之后，居然延迟到了40分钟，而且只更新了十几万行数据。问题sql如下：
&!-- 根据商品id更新品牌id --&
&update id=&updateBrandIdByPid& parameterClass=&com.jd.chat.worker.domain.param.UpdateBrandIdParam&&
UPDATE $tableName$
SET brand_id = #newBrandId#
WHERE pid = #pid#
AND brand_id = 0
项目组的mysql专家帮我分析了下，因为pid字段没有索引，mysql引擎要逐行扫描出与传入的pid值相等的列，然后更新数据，也就是要扫描完1000W+行磁盘数据才能执行完这个sql。更严重的是，这个千万级的表里面有多少个不同的pid，我就要执行多少个这样的sql。
同事给我的建议的根据id字段进行sql代码层次的纵向分表。每次更新1000行的数据，这样mysql引擎就不用每次在扫全表了，数据库压力是之前的万分之一。而且id作为主键，是有索引的有索引，有索引能大大优化查询性能，优化后的sql如下：
&!-- 根据商品id更新品牌id --&
&update id=&updateBrandIdByPid& parameterClass=&com.jd.chat.worker.domain.param.UpdateBrandIdParam&&
UPDATE $tableName$
SET brand_id = #newBrandId#
WHERE pid = #pid#
AND brand_id = 0
AND id BETWEEN #startNum# AND #endNum#
仅仅用了id限区间的语句，将一个千万级的大表代码层次上进行纵向切割。重新上线worker后，mysql主从没有任何延迟！而且经过监视，短短10分钟就更新了十几万数据，效率是之前的6倍！更重要的是数据库负载均衡，应用健康运行。
(window.slotbydup=window.slotbydup || []).push({
id: '2467140',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467141',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467142',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467143',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467148',
container: s,
size: '1000,90',
display: 'inlay-fix'mysql对于大表(千万级)该如何优化?
来源：博客园
如何设计或优化千万级别的大表？此外无其他信息，个人觉得这个话题有点范，就只好简单说下该如何做，对于一个存储设计，必须考虑业务特点，收集的信息如下： 1.数据的容量：1-3年内会大概多少条数据，每条数据大概多少字节； 2.数据项：是否有大字段，那些字段的值是否经常被更新； 3.数据查询SQL条件：哪些数据项的列名称经常出现在WHERE、GROUP BY、ORDER BY子句中等； 4.数据更新类SQL条件：有多少列经常出现UPDATE或DELETE 的WHERE子句中； 5.SQL量的统计比，如：SELECT：UPDATE+DELETE：INSERT=多少？ 6.预计大表及相关联的SQL，每天总的执行量在何数量级？ 7.表中的数据：更新为主的业务 还是 查询为主的业务 8.打算采用什么数据库物理服务器，以及数据库服务器架构？ 9.并发如何？ 10.存储引擎选择InnoDB还是MyISAM？ 大致明白以上10个问题，至于如何设计此类的大表，应该什么都清楚了！ 至于优化若是指创建好的表，不能变动表结构的话，那建议InnoDB引擎，多利用点内存，减轻磁盘IO负载，因为IO往往是数据库服务器的瓶颈 另外对优化索引结构去解决性能问题的话，建议优先考虑修改类SQL语句，使他们更快些，不得已只靠索引组织结构的方式，当然此话前提是， 索引已经创建的非常好，若是读为主，可以考虑打开query_cache， 以及调整一些参数值：sort_buffer_size,read_buffer_size,read_rnd_buffer_size,join_buffer_size 其他人建议： 1. 索引, 避免扫描,基于主键的查找,上亿数据也是很快的; 2. 反范式化设计,以空间换时间,避免join,有些join操作可以在用代码实现,没必要用数据库来实现;
免责声明：本站部分内容、图片、文字、视频等来自于互联网，仅供大家学习与交流。相关内容如涉嫌侵犯您的知识产权或其他合法权益，请向本站发送有效通知，我们会及时处理。反馈邮箱&&&&。
学生服务号
在线咨询，奖学金返现，名师点评，等你来互动Mysql 千万级数据量插入和查询应该怎么优化_百度知道mysql&千万级数据库如何进行多张结构相同的表联合查询？如何优化或设置提高查询速度？
1. 现有近亿条记录数据，分成N个表进行存储；当然这N个表结构都一样，如何才能提高这N表的联合查询？表都建了索引；
我在一个上百万的表上进行单表查询时间几乎为0，但几个同样的表进行联合查询，结果处于假死状态，请问有什么机制可以
提高这N个表的联合查询速度？
2.如何请提高其它表 结合 这N几个表进行查询？我觉得同结构表结合查询就很慢，再结合其它不同结构表 会更麻烦些，请高
手们支招！
为便于理解我建了一个表结构出来：
CREATE TABLE `test_bj` (
`id` int(10) NOT NULL,
`username` char(12) default NULL,
`userpwd` char(16) default NULL,
`sex` int(1) default NULL,
`Position` char(10) default NULL,
`Remarks` char(150) default NULL,
`addtime` timestamp NULL default NULL on update CURRENT_TIMESTAMP,
PRIMARY KEY
KEY `s_id_atm` (`id`,`addtime`),
KEY `s_all` (`id`,`username`,`userpwd`,`sex`,`Position`,`Remarks`,`addtime`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312;
字段分别为：id , username , usrepwd , sex , position, remarks, addtime (字段可根据优化需要再建或修改)
表名：test_bj,test_sh ....
test_gz... test_n (假设每张有1千万记录)
每个表名 很想以地区单独分开(不想建分区表),这样方便查看和单独查询
还说明一点
select id,username,sex,addtime from
select id,username,sex,addtime from test_bj where username like '%张%' and sex=1
select id,username,sex,addtime from test_sh where username like '%张%' and sex=1
select id,username,sex,addtime from test_gz where username like '%张%' and sex=1
) order by addtime desc limit0,20
这种多表联合查询基本处于死机状态 , 这查询代码该如何写?
朋友们以上问题给我支个招吧！大家一起总结下，对后来者都是一个帮助！在此拜谢了！
不要拷贝、复制,最好给出实例代码和配置步骤
首先要说的是创建索引会提高搜索速度
再就是 like 不会使用索引，结果就是你创建了索引但是找不到结果，这个和union没有关系
即使你单独一条查询也是遍历整个数据库，不会在索引中查询
对于这种情况一般都是通过分词创建文件索引的方式进行文字查询 如 lucene
现在的数据量，想要通过sql解决文字的like查询，通过数据库已经不够用的了~
多谢你的回答，我以上提的问题，Mysql有没有其它解决办法？表可多个，要联合查询，或者只能用分区表来做吗？还是像一个朋友说的 运用hash算法对同样结构的表进行计算分类后存入新表？
不论你怎么分表 只要使用like 索引就不会起作用 最后的结果还是所有的数据都要遍历一遍你可以看看 Mysql+sphinx 做的搜索，对于大数据量的查询使用比较多单独使用数据库很难，我找过通过创建mysql分词索引的方式搜索数据，结果会丢失很多数据，结果也不准确，毕竟查询还是以准确为第一位的你可以看看京东或者当当之类的，你输入了一个字他会自动匹配出结果数和数据量，这个就是分词索引的结果
已投稿到：
以上网友发言只代表其个人观点，不代表新浪网的观点或立场。Mysql 千万级数据量应该怎么优化
[问题点数：40分，结帖人wuqiang1123]
Mysql 千万级数据量应该怎么优化
[问题点数：40分，结帖人wuqiang1123]
不显示删除回复
显示所有回复
显示星级回复
显示得分回复
只显示楼主
2013年5月 总版技术专家分月排行榜第一
2016年7月 总版技术专家分月排行榜第二2016年3月 总版技术专家分月排行榜第二2015年12月 总版技术专家分月排行榜第二2014年8月 总版技术专家分月排行榜第二2014年7月 总版技术专家分月排行榜第二2013年6月 总版技术专家分月排行榜第二
2013年5月 总版技术专家分月排行榜第一
2016年7月 总版技术专家分月排行榜第二2016年3月 总版技术专家分月排行榜第二2015年12月 总版技术专家分月排行榜第二2014年8月 总版技术专家分月排行榜第二2014年7月 总版技术专家分月排行榜第二2013年6月 总版技术专家分月排行榜第二
2013年5月 总版技术专家分月排行榜第一
2016年7月 总版技术专家分月排行榜第二2016年3月 总版技术专家分月排行榜第二2015年12月 总版技术专家分月排行榜第二2014年8月 总版技术专家分月排行榜第二2014年7月 总版技术专家分月排行榜第二2013年6月 总版技术专家分月排行榜第二
本帖子已过去太久远了，不再提供回复功能。