MySQL（14）
个人觉得这个话题有点范，就只好简单说下该如何做，对于一个存储设计，必须考虑业务特点，收集的信息如下：
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&
更多信息参见：
MySQL数据库服务器端核心参数详解和推荐配置
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：3578次
排名：千里之外
转载：53篇
(1)(4)(2)(1)(2)(4)(1)(18)(7)(4)(14)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月 总版技术专家分月排行榜第二
本帖子已过去太久远了，不再提供回复功能。作者：哈哈
链接：/question//answer/
来源：知乎
著作权归作者所有，转载请联系作者获得授权。
很多人第一反应是各种切分；我给的顺序是:
第一优化你的sql和索引；
第二加缓存，memcached,redis；
第三以上都做了后，还是慢，就做主从复制或主主复制，读写分离，可以在应用层做，效率高，也可以用三方工具，第三方工具推荐360的atlas,其它的要么效率不高，要么没人维护；
第四如果以上都做了还是慢，不要想着去做切分，mysql自带分区表，先试试这个，对你的应用是透明的，无需更改代码,但是sql语句是需要针对分区表做优化的，sql条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区，另外分区表还有一些坑，在这里就不多说了；
第五如果以上都做了，那就先做垂直拆分，其实就是根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；
第六才是水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key,为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；
mysql数据库一般都是按照这个步骤去演化的，成本也是由低到高；
有人也许要说第一步优化sql和索引这还用说吗？的确，大家都知道，但是很多情况下，这一步做的并不到位，甚至有的只做了根据sql去建索引，根本没对sql优化（中枪了没？），除了最简单的增删改查外，想实现一个查询，可以写出很多种查询语句，不同的语句，根据你选择的引擎、表中数据的分布情况、索引情况、数据库优化策略、查询中的锁策略等因素，最终查询的效率相差很大；优化要从整体去考虑，有时你优化一条语句后，其它查询反而效率被降低了，所以要取一个平衡点；即使精通mysql的话，除了纯技术面优化，还要根据业务面去优化sql语句，这样才能达到最优效果；你敢说你的sql和索引已经是最优了吗?
再说一下不同引擎的优化，myisam读的效果好，写的效率差，这和它数据存储格式，索引的指针和锁的策略有关的，它的数据是顺序存储的（innodb数据存储方式是聚簇索引），他的索引btree上的节点是一个指向数据物理位置的指针，所以查找起来很快，（innodb索引节点存的则是数据的主键，所以需要根据主键二次查找）；myisam锁是表锁，只有读读之间是并发的，写写之间和读写之间（读和插入之间是可以并发的，去设置concurrent_insert参数，定期执行表优化操作，更新操作就没有办法了）是串行的，所以写起来慢，并且默认的写优先级比读优先级高，高到写操作来了后，可以马上插入到读操作前面去，如果批量写，会导致读请求饿死，所以要设置读写优先级或设置多少写操作后执行读操作的策略;myisam不要使用查询时间太长的sql，如果策略使用不当，也会导致写饿死，所以尽量去拆分查询效率低的sql,
innodb一般都是行锁，这个一般指的是sql用到索引的时候，行锁是加在索引上的，不是加在数据记录上的，如果sql没有用到索引，仍然会锁定表,mysql的读写之间是可以并发的，普通的select是不需要锁的，当查询的记录遇到锁时，用的是一致性的非锁定快照读，也就是根据数据库隔离级别策略，会去读被锁定行的快照，其它更新或加锁读语句用的是当前读，读取原始行；因为普通读与写不冲突，所以innodb不会出现读写饿死的情况，又因为在使用索引的时候用的是行锁，锁的粒度小，竞争相同锁的情况就少，就增加了并发处理，所以并发读写的效率还是很优秀的，问题在于索引查询后的根据主键的二次查找导致效率低；
ps:很奇怪，为什innodb的索引叶子节点存的是主键而不是像mysism一样存数据的物理地址指针吗？如果存的是物理地址指针不就不需要二次查找了吗，这也是我开始的疑惑，根据mysism和innodb数据存储方式的差异去想，你就会明白了，我就不费口舌了！
所以innodb为了避免二次查找可以使用索引覆盖技术，无法使用索引覆盖的，再延伸一下就是基于索引覆盖实现延迟关联；不知道什么是索引覆盖的，建议你无论如何都要弄清楚它是怎么回事！
尽你所能去优化你的sql吧！说它成本低，却又是一项费时费力的活，需要在技术与业务都熟悉的情况下，用心去优化才能做到最优，优化后的效果也是立竿见影的！
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：5111次
排名：千里之外
原创：33篇
(1)(14)(9)(3)(3)(4)(2)1812人阅读
MYSQL（40）
一 &大的优化方向：&数据结构优化，慢查询优化，索引优化，mysql参数设置优化
数据结构优化：先读写分离、再垂直拆分、再水平拆分！
1. 设计合适的索引,基于主键的查找,上亿数据也是很快的;
2. 反范式化设计,以空间换时间,避免join,有些join操作可以在用代码实现,没必要用数据库来实现;
3. buffer,尽量让内存大于数据.
至于优化若是指创建好的表，不能变动表结构的话，那建议InnoDB引擎，多利用点内存，减轻磁盘IO负载，因为IO往往是数据库服务器的瓶颈&
我的问题：两个数据库，database A 和 B（be） &;每个都有两个表event和id
id表增长到不会超过1亿，然后会有频繁的更新.更新需要查找id值。这个亦可以分表。按照gid的不同，在程序中处理插入不同哦功能的表
每天生成一个新表，然后整体再插入总表？？
event表一直累加，会超过1亿，需要自动切表
问题：1 &数据库是否是瓶颈？ &再开几个进程看数据库插入更新会不会有加速现象.多开程序的作用已经不大，很微小。所以瓶颈在数据库上了
& & & & & & &2 瓶颈在哪里? &insert时间花费0--100毫秒 &，update 100--1000ms
具体做法：
原来查看速度： &
& & & & &&
& & & & & & Com_insert & & & & & & & & & &100 & & & & & & & & & &&
& & & & & &Com_update & & & & & & & & & &30
以下测试再关闭程序debug日志的情况下进行
使用gprof分析程序得到 parsesql占23.5%，parseupdatesql15.5%，jsontomap占23.5，推测数据库执行占40%？
<span style="font-family:Helvetica Neue,Helvetica,Arial,sans- color:# & & 程序是瓶颈，开了6个进程
原来速度： &&
。10：00 & & & &开了6个进程来跑。速度
& & & & & & & & & & & & & & & & & & & & & & & & & & &第一次测试 & &第二次测试&
& & & & & &Com_insert & & & & & & &&& & &489 & & & & & & & & & & 342
& & & & &&Com_update& & & & & & & & & &204 & & & & & & & & & & 182
& & & & & & & &&
措施:加内寸，拆表
得到结论： 1 &只写入event表，看会不会堵塞。不需要做这个实验，DBA自动切表就行，这样就很小。
<span style="font-family:Helvetica Neue,Helvetica,Arial,sans- color:# & & &数据库表很大的时候，主要考虑IO速度慢的问题，因为表太大，不能全部放进内存，所以需要硬盘IO，引起速度慢。
& & & & & & & & & & 1 查看表大小，event表占35G，gid表占3G。
& & & & & &数据库设定的内存大小是10G,event是在太大了，导致频繁的磁盘IO。
&TABLE_NAME &| DATA_LENGTH | DATA_LENGTH&#43;INDEX_LENGTH | TABLE_ROWS |
&#43;-------------&#43;-------------&#43;--------------------------&#43;------------&#43;
| Mapping_gid | & | & & & & & & &
| & &5162861 |
&#43;-------------&#43;-------------&#43;--------------------------&#43;-----------
&#43;------------------------&#43;-------------&#43;--------------------------&#43;------------&#43;
| TABLE_NAME & & & & & & | DATA_LENGTH | DATA_LENGTH&#43;INDEX_LENGTH | TABLE_ROWS |
&#43;------------------------&#43;-------------&#43;--------------------------&#43;------------&#43;
| Mapping_event_ |
| & & & & & & & | &
&#43;------------------------&#43;-------------&#43;--------------------------&#43;------------&#43;
&#43;-------------&#43;-------------&#43;--------------------------&#43;------------&#43;
| TABLE_NAME &| DATA_LENGTH | DATA_LENGTH&#43;INDEX_LENGTH | TABLE_ROWS |
&#43;-------------&#43;-------------&#43;--------------------------&#43;------------&#43;
| Mapping_gid | & | & & & & & & &
| & &4216890 |
&#43;-------------&#43;-------------&#43;--------------------------&#43;------------&#43;
------------------------&#43;-------------&#43;--------------------------&#43;------------&#43;
| TABLE_NAME & & & & & & | DATA_LENGTH | DATA_LENGTH&#43;INDEX_LENGTH | TABLE_ROWS |
&#43;------------------------&#43;-------------&#43;--------------------------&#43;------------&#43;
| Mapping_event_ | & | & & & & & & &
对策：把event按每天备份 ； 这样每天的event表就很小。
&& & & & & & & & & & & & & & & & & & & & &第一次测试 &&
& & & & & &Com_insert & & & & & & &&& &&&1158&
& & & & & & & &
& & & & &&Com_update & & & & & & & & & &552 & & & & & & &&
<span style="font-family:Helvetica Neue,Helvetica,Arial,sans- color:# &修改程序，原来程序insert不成功然后再update，改成insert into &duplicate
可以解决的问题：1 多个进程写数据，id会跳跃性自增 & &2 &两条变一条
& & & & & & & & & & & & &&
各位，请教一个mysql问题。
说明：数据库id是主键，gid是唯一索引。insert into duplicate使用gid作为条件。
问题如下：
昨天我使用insert into duplicate 试验了很久、
我开了两个进程。都执行insert into duplicate语句。
进程A事实上只执行update，进程B 事实上只执行insert
单独开A时，id不变，单独开B，id是顺序增长同时开A，B，id跳跃性增长的。
可有什么解决方案推荐，使得id是顺序&#43;1增长的？
解释：&只能说&insert
into duplicate 也使得id 自增了.但是如果后来执行的是update操作，撤销了.
可设置 innodb_autoinc_lock_mode = 0 使的获取自增id的锁方式为表锁，
但是此参数是全局的（即影响所有表，且需重启数据库生效），对于高并发写入的数据，会影响插入性能，不建议
如果使用insert into duplicate 只能解决两条变一条，不能解决id问题。
我具体做法：（1）开了多份程序，提高了数据库写入和更新的速度
& & & & & & & & & & & & & （2） 加大内存&
未填加内存前速度 -loginfo：
& & & & & & & & & & &Com_insert & & & & & &Com_update
& & & & & & & & & & & & & 400 & & & & & & & & & & & & & & &160
& & & & & & & & & & & & & & &（3）修改字段
& & & & & & & & & & & & & & &（4）拆表
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：901345次
积分：10236
积分：10236
排名：第1168名
原创：150篇
转载：309篇
评论：49条
(4)(2)(9)(1)(5)(5)(7)(8)(10)(10)(14)(11)(9)(8)(10)(18)(20)(23)(14)(18)(12)(28)(18)(1)(2)(4)(11)(2)(18)(18)(30)(39)(18)(6)(5)(2)(7)(5)(8)(6)(1)(12)查看: 4057|回复: 10
如何设计或优化千万级别的大表提问的统一解答
论坛徽章:52
知呼或ITPUB论坛上的提问：如何设计或优化千万级别的大表？此外无其他信息，个人觉得这个话题有点范，就只好简单说下该如何做，对于一个存储设计，必须考虑业务特点，收集的信息如下：
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
论坛徽章:10
呵呵 上面的几个问题都是DBA们最在乎的问题~
论坛徽章:4
大表的话 应该分表的 也减少些压力
论坛徽章:4
原帖由 leadhoo 于
02:46 发表
大表的话 应该分表的 也减少些压力
同意，我们现在上了千万级数量级别的表都是md5分成16张字表。
论坛徽章:11
当然要水平切分表了，根据我的实际经验，单表不要超过500W记录，这个不是MySQL能不能承担千万级的大表（肯定能，之前我就有个业务表
达到了4000W+的数据，被我水平切分成了16个表分散到分库中）
而是有没有必要，肯定要切分的
否则你为了提高查询性能要付出其他成本
论坛徽章:8
水平切分16个表，什么意思，就是原来表假如有16个字段，就是建16个表，每个表一个段？
如果不是这样，那是怎么水平切分?
论坛徽章:4
原帖由 magscott 于
20:21 发表
水平切分16个表，什么意思，就是原来表假如
有16个字段，就是建16个表，每个表一个段？
如果不是这样，那是怎么水平切分?
比如表 msg_log，主键字段id int类型。
是md5（id）得出的字符串，取第一个字母，总共有16个首字母（0，1,2,3....a，b，c...f），扯分成msg_log_0、msg_log_1......msg_log_c...，msg_log_f等16张表，然后数据就按照pkid的md5（id）的首字母决定，数据存放入哪一张表里面去。
认证徽章论坛徽章:27
回复 #1 jinguanding 的帖子
楼主实际生产环境中用过msyql的分区特性么？
论坛徽章:52
回复 #8 mchdba 的帖子
有用的.........用于SNS的场景，以及一个内部监控系统
MySQL版本分别为：5.1.34，5.1.40
认证徽章论坛徽章:27
回复 #9 jinguanding 的帖子
用了多长时间了，发现隐患了没有？出过啥莫名其妙的bug没有？
itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有　　　　
　北京市公安局海淀分局网监中心备案编号：　广播电视节目制作经营许可证：编号（京）字第1149号}