作者：哈哈
链接：https://www.zhihu.com/question/19719997/answer/81930332
来源：知乎
著作权归作者所有，转载请联系作者获得授权。

很多人第一反应是各种切分；我给的顺序是:
第一优化你的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吧！说它成本低，却又是一项费时费力的活，需要在技术与业务都熟悉的情况下，用心去优化才能做到最优，优化后的效果也是立竿见影的！
编辑于 2016-01-19 46 条评论 感谢 分享 收藏 • 没有帮助 • 举报 • 作者保留权利

回答一

提问：如何设计或优化千万级别的大表？此外无其他信息，个人觉得这个话题有点范，就只好简单说下该如何做，对于一个存储设计，必须考虑业务特点，收集的信息如下：
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

松哥

27
赞同反对，不会显示你的姓名
Gary Chen 从事数据库领域10多年。
27 人赞同
说3点
1. 设计合适的索引,基于主键的查找,上亿数据也是很快的;
2. 反范式化设计,以空间换时间,避免join,有些join操作可以在用代码实现,没必要用数据库来实现;
3. buffer,尽量让内存大于数据.
编辑于 2012-02-01 5 条评论 感谢 分享 收藏 • 没有帮助 • 举报 • 作者保留权利

沈涛

喜欢在夜深人静的时候听着久石让的音乐写…
1 人赞同
水平拆分+读写分离
发布于 2011-08-02 添加评论 感谢 分享 收藏 • 没有帮助 • 举报 • 作者保留权利

陈满砚

程序员里吉他弹得最好的, 吉他手里程序写…
39 人赞同
不纸上谈兵，说一下我的思路以及我的解决，抛砖引玉了
我最近正在解决这个问题
我现在的公司有三张表，是5亿的数据，每天张表每天的增量是100w
每张表大概在10个columns左右
下面是我做的测试和对比
1.首先看engine,在大数据量情况下，在没有做分区的情况下
mysiam比innodb在只读的情况下，效率要高13％左右
2.在做了partition之后，你可以去读一下mysql的官方文档，其实对于partition，专门是对myisam做的优化，对于innodb，所有的数据是存在ibdata里面的，所以即使你可以看到schema变了，其实没有本质的变化
在分区出于同一个physical disk下面的情况下，提升大概只有1％
在分区在不同的physical disk下，我分到了三个不同的disks下，提升大概在3％，其实所谓的吞吐量，由很多因素决定的，比如你的explain parition时候可以看到，record在那一个分区，如果每个分区都有，其实本质上没有解决读的问题，这样只会提升写的效率。
另外一个问题在于，分区，你怎么分，如果一张表，有三个column都是经常被用于做查询条件的，其实是一件很悲惨的事情，因为你没有办法对所有的sql做针对性的分区，如果你只是如mysql官方文档上说的，只对时间做一个分区，而且你也只用时间查询的话，恭喜你
3.表主要用来读还是写，其实这个问题是不充分的，应该这样问，你在写入的时候，同时并发的查询多么？我的问题还比较简单，因为mongodb的shredding支持不能，在crush之后，还是回到mysql，所以在通常情况下，9am－9pm，写入的情况很多，这个时候我会做一个view，view是基于最近被插入或者经常被查询的，通过做view来分离读取，就是说写是在table上的，读在进行逻辑判断前是在view上操作的
4做一些archive table，比如先对这些大表做很多已有的统计分析，然后通过已有的分析＋增量来解决
5如果你用mysiam，还有一个问题你要注意，如果你的.configure的时候，加了一个max index length参数的时候，当你的record数大于制定长度的时候，这个index会被disable
6
7
8
太多了，洗澡去先－ －
当然，我还是纸上谈兵了，lz把上下文给足，我可以帮你看一下
发布于 2011-12-19 13 条评论 感谢 分享 收藏 • 没有帮助 • 举报 • 作者保留权利

manatee

都千万数据了，居然没有具体细节，提问者肯定是新手。
发布于 2014-08-29 2 条评论 感谢 分享 收藏 • 没有帮助 • 举报 • 作者保留权利

樱木花 DBA，

1. 千万只是小case而已，千万对程序员来说好大啊， 人家一天几千万的，那DBA不是不要活了？
现在5T,10T 的单个数据库都很多。程序猿能兼职干了吗？？
2. 你需要一个专业的DBA，专业的人干专业的事，DBA手里有自己的武器

3.程序员来操DBA的心，不是傻，就是楞，在不就是逗比。
我想强调的是“专业性”，数据库架构、管理、优化，在IT的始祖，美国是一个专门的行业和要求很严格的岗位，在我们这里是什么情况，一个编代码能力还不到3-5年的初中级程序猿总要试图，找到解决海量数据问题的捷径，还总一种企图找到“魔术师式的秘诀” 的心态，很显然，并不存在，一个合格的DBA，是经历各种问题处理、碰壁、深思的成长过程，经历早已覆盖了这些初中级程序猿能碰到的你们认为比较难的问题，对他们来说，这些都是很基本的问题， IT本身并没有捷径。 数据库的运行本质和一切程序代码都是一个原理，DBA不过对于数据库运行的各个环节更加清晰，更有量化，碰到问题的广度和深度都比写代码的程序猿多一些。相反要DBA去调试java代码可能同样吃力、难受，还做不出效果。
编辑于 2016-11-16 4 条评论 感谢 分享 收藏 • 没有帮助 • 举报 • 作者保留权利

后端攻城狮

@哈哈 答主答得非常好了，但是不太赞同答主给出的顺序。
一个合格的技术人，应该能做出适用于未来的产品。引入分布式会带来很多麻烦，但它会让你走得更远。这不正是技术人的价值所在吗？
看到有人回复说有钱就上Oracle了。Facebook，阿里没钱吗？钱不是最重要的考虑。再说他们的技术实力没有甲骨文强吗？万万不要迷信IOE。
首先，任何优化，都需要你了解你的业务，了解你的数据。
QPS要到多少？- 带宽及存储够的情况下，单机几千QPS妥妥的。
读写比例如何？- 读多写少和写多读少，优化方法是有很大差别的。设置于只读场景，果断压缩。
数据是否快速增长？- 基本就是QPS的要求。
数据及服务的SLA要到多少？- 数据需不需要强一致？HA做到什么程度？
诸如此类。
不同的场景有不同的侧重，解决方案是不同的。而对于一些典型的场景可能会有成熟的解决方案。
题主已注明“千万级”，因此以下假设题主为最常见的场景：大量数据，QPS要求高，读多写少，数据快速增长，SLA要求高。
其次，说优化的方法。
主要从三个维度说：Why, How, When。
0. sql vs nosql
有些跑题，但也是很重要的一方面。
Why: nosql天生分布，而且大多针对某种类型的数据、某种使用场景做过优化。
比如大批量的监控数据，用mysql存费时费力，可以选择mongo，甚至时间序列数据库，存取会有量级提升。
How: 找对应解决方案。
When: 有足够诱惑 - 针对使用场景，有成熟解决方案，效率获得大量提升。
1. 优化shema、sql语句+索引
Why: 再好的MySQL架构也扛不住一个频繁的垃圾查询。不合理的schema设计也会导致数据存取慢。索引的作用不必多说，但如innodb下，错的索引带来的可能不只是查询变慢而已。
How: 设计阶段就需要预计QPS及数据规模，参考业务场景对数据的要求，合理设计表结构（参考mysql在线DDL问题），甚至违反设计范式做到适当冗余。生产环境分析慢日志，优化语句。索引的设计需要知道索引是怎么用的，比如innodb的加锁机制。
When: 这个不仅仅是第一个要考虑的，而应该是需要持续去优化的。特别是要参考业务。但实际环境中如果是这个的问题，那一般比较幸运了，因为一般已经优化过很多了。实际中遇到的一般是更深的问题。
2. 缓存
缓存没有那么简单。
缓存对于应用不是完全透明的，除非你用Django这种成熟框架，而且缓存粒度很大，但实际。。。像python，最少也得加几个装饰器。
如何保证缓存里面的数据是始终正确的？写数据前失效缓存还是写数据后？
缓存挂了或者过冷，流量压到后端mysql了怎么办？
缓存也不是万能的。写多读少，命中率会很低。
How: memcache用做缓存，redis用于需要持久化的场景。（redis能不能完全取代memcache？呵呵。。）
还可以使用mysql自带的query cache，对应用基本完全透明。但会受限于本机。而且只缓存查询结果，mc和redis可以缓存一些加工后的数据。
而且数据量大、QPS大的情况下，也需要考虑分片及HA的问题。如果有一个数据过热，把一个节点压垮了怎么办？
When: 基本上大多数读多写少的场景都能用，写多的情况下可能需要考虑考虑。
3. 复制及读写分离
Why: 这个其实是大多数场景下都必须的。因为复制可以实现备份、高可用、负载均衡。就算嫌麻烦不做负载均衡，那备份下总是要的吧？既然已经备份了，何不加个LVS+HAProxy做下HA？顺便稍微修改下应用，读写分离也就成了。
How: 节点少的情况下，主备。前面加Keepalived+HAProxy等组件，失效自动切换。读写分离可能需要修改下应用。
节点多的情况下，一是考虑多级备份，减轻主的压力。其次可以引入第三方组件，接管主节点的备份工作。
主主不是很推荐。一是需要考虑数据冲突的情况，比如错开id，同时操作数据后冲突解决。其次如果强一致会导致延迟增加，如果有节点挂了，需要等到超时才返回。
When: 主备几乎大多数场景。甚至不论数据大小。高可用对应用透明，为啥不用?主主麻烦，建议先用切分。
4. 切分
包括垂直切分和水平切分，实现方式上又包括分库、分表。
虽然有些难度，但还是推荐常用的。
Why: 垂直切分保证业务的独立性，防止不同业务争抢资源，毕竟业务是有优先级的。
水平切分主要用于突破单机瓶颈。除了主主外，只有切分能真正做到将负载分配下去。
切分后也可对不同片数据进行不同优化。如按时间切分，超过一定时间数据不允许修改，就可以引入压缩了，数据传输及读取减少很多。
How: 根据业务垂直切分。业务内部分库、分表。一般都需要修改应用。除分表外，其余实现不是很复杂。有第三方组件可用，但通用高效又灵活的方式，还是自己写client。
When: 垂直切分一般都要做，只不过业务粒度大小而已。
分库有是经常用的，就算当前压力小，也尽量分出几个逻辑库出来。等规模上去了，很方便就迁移扩展。
水平拆分有一定难度，但如果将来一定会到这个规模，又可能用到，建议越早做越好。因为对应用的改动较大，而且迁移成本高。

综上，数据库设计要面向现代化，面向世界，面向未来。。。
编辑于 2016-04-06 2 条评论 感谢 分享 收藏 • 没有帮助 • 举报 • 作者保留权利

1
知乎用户 程序员

如果数据很冷或者都是简单主键查询，不优化也罢。
发布于 2014-08-09 2 条评论 感谢 分享 收藏 • 没有帮助 • 举报 • 作者保留权利

babyyellow

单表 7亿笔记录, 80G 大小, 无分区, 每秒可以支持400次增删查改.
还有一个 2.8亿记录, 大约40Gb 同一个库 的两个大表.

出现问题了,多半是你设计出问题了, 跟数据库关系不大.
编辑于 2015-08-31 6 条评论 感谢 分享 收藏 • 没有帮助 • 举报 • 作者保留权利

朱明豪 mysql/oracel DBA。

mysql只做简单的事情，千万级的表，不论如何优化，同样的SQL都没有十万级的表访问快。
如果设计千万级的大表，要问自己几个问题：
1.数据是否存在明显的冷热（考虑旧数据归档）
2.是否可以按照时间，区域拆分表
3.如果字段过多是否可以字段的关联性进行拆分
说白了就是：归档，垂直拆分，水平拆分
周松，http://ChinaDBA.com
先读写分离、再垂直拆分、再水平拆分！
发布于 2014-11-25 添加评论 感谢 分享 收藏 • 没有帮助 • 举报 • 作者保留权利
推荐一本书，《MySQL高性能优化》

谭景峰 always beta

提到优化，先要确定出现的问题，是存储引擎选择问题，还是sql语句使用问题（如：索引）亦或者是单一存储服务器对于千万级别的数据力不从心。
解决方法：
1、根据不同业务选用不同存储引擎，虽然一般情况下都优先选择InnoDB。
2、分析sql语句的影响结果集。查慢查询日志，定位慢查询的sql语句，查看是否有锁定的sql操作。
3、分库，分表，分区（慎用分区，往往OLTP操作不适用分区，分区反倒会拖慢原有查询）
4、利用缓存或NoSql代替现有一些热点查询操作，减轻Mysql压力。
分库：
可以基于业务逻辑拆分，不同业务分布在不同服务器中，减少单一服务器压力
分表：
垂直分表和水平分表两种方式
垂直分表：将表中的一些频繁更新和非频繁更新的字段分开存放
水平分表，三种分表方式：
均等分表，哈希方式分表，优点是负载平均分布，缺点是当容量持续增加时扩荣不方便，需要重新分表，主键就很不好处理。
递增分表，比如每一千万数据开一个新表，优点是自适应强，缺点是数据负载不均衡，需要代码层额外处理。
时间分表，根据不同的创建时间分表，适用于OLAP应用。
发布于 2015-11-22 添加评论 感谢 分享 收藏 • 没有帮助 • 举报 • 禁止转载

3
赞同反对，不会显示你的姓名
华天清 网络爬虫 http://www.GooSeeker.com 创始人，…
3 人赞同
没看错吧，千万级也叫大表？这个规模的，只要没把索引用错，几乎不用考虑优化。

具体遇到什么问题了？可以列出来，大家一起诊断一下。

关系数据库本来是一套严密的理论，现在越来越少的人愿意去花时间捉摸集合运算，很多时候是没有用对集合运算导致性能急剧下降。这其实不属于优化范围
编辑于 2016-06-05 添加评论 感谢 分享 收藏 • 没有帮助 • 举报 • 作者保留权利

2
赞同反对，不会显示你的姓名
简朝阳 Oracle ACE Director, Data Architect
2 人赞同
优化本身需要有一个非常明确具体的目标，以及应用类型环境。

如果非要抛开这两个因素，也有看上去可以解决一切问题但不具备实际操作范例的答案，那就是：
1. 优化SQL
2. 优化结构
3. 优化存储
编辑于 2016-05-04 添加评论 感谢 分享 收藏 • 没有帮助 • 举报 • 作者保留权利

1
赞同反对，不会显示你的姓名
drdr xp 要了几天饱饭就不记得西北风啥味了
1 人赞同
千万也算大表ರ_ರ
发布于 2016-01-18 添加评论 感谢 分享 收藏 • 没有帮助 • 举报 • 作者保留权利

1
赞同反对，不会显示你的姓名
苏钰文 转点
1 人赞同
千万级不算大，优化一下，可以在几十到几百毫秒几查询。
30多条mysql数据库优化方法,千万级数据库记录查询轻松解决