MySQL 对于千万级的大表要怎么优化？
https://www.zhihu.com/question/19719997
千万级，MySQL实际上确实不是什么压力，InnoDB的存储引擎，使用的是B+树存储结构，千万级的数据量，基本也就是三到四层的搜索，如果有合适的索引，性能基本也不是问题。
但经常出现的情况是，业务上面的增长，导致数据量还会继续增长，为了应对这方面的问题而必须要做扩展了此时可能首先需要考虑的就是分表策略了。
当然分表，可能还有其它几个原因，比如表变大了，千万级的数据库，为了减少运维成本，降低风险，就想到了通过分表来解决问题，这都是比较合适的。
分表，还有另一个方面的意思，就是在数据量更大的情况下，为了分担业务压力，将数据表分到不同的实例中去，这样有两方面的好处：1. 降低业务风险，如果一套数据库集群出问题了，那至少还有其它的可以服务，这样被影响的业务可能只是一部分。2. 降低运维成本，如果数据库想要做迁移，或者正常维护等操作了，那涉及到的数据量小，下线时间短，操作快，从而对业务影响也就小了。这种方式，我们称之为“分实例”。
分表的话，还是要根据具体的业务逻辑等方面来做，这方面有更精彩的回答，我这里贴一下：
========================================
分库分表是MySQL永远的话题，一般情况下认为MySQL是个简单的数据库，在数据量大到一定程度之后处理查询的效率降低，如果需要继续保持高性能运转的话，必须分库或者分表了。关于数据量达到多少大是个极限这个事儿，本文先不讨论，研究源码的同学已经证实MySQL或者Innodb内部的锁粒度太大的问题大大限制了MySQL提供QPS的能力或者处理大规模数据的能力。在这点上，一般的使用者只好坐等官方不断推出的优化版本了。
在一般运维的角度来看，我们什么情况下需要考虑分库分表？
首先说明，这里所说的分库分表是指把数据库数据的物理拆分到多个实例或者多台机器上去，而不是类似分区表的原地切分。
原则零：能不分就不分。
是的，MySQL 是关系数据库，数据库表之间的关系从一定的角度上映射了业务逻辑。任何分库分表的行为都会在某种程度上提升业务逻辑的复杂度，数据库除了承载数据的存储和访问外，协助业务更好的实现需求和逻辑也是其重要工作之一。分库分表会带来数据的合并，查询或者更新条件的分离，事务的分离等等多种后果，业务实现的复杂程度往往会翻倍或者指数级上升。所以，在分库分表之前，不要为分而分，去做其他力所能及的事情吧，例如升级硬件，升级，升级网络，升级数据库版本，读写分离，负载均衡等等。所有分库分表的前提是，这些你已经尽力了。
原则一：数据量太大，正常的运维影响正常业务访问。
这里说的运维，例如：
（1）对数据库的备份。如果单表或者单个实例太大，在做备份的时候需要大量的磁盘IO或者网络IO资源。例如1T的数据，网络传输占用50MB的时候，需要20000秒才能传输完毕，在此整个过程中的维护风险都是高于平时的。我们在Qunar的做法是给所有的数据库机器添加第二块网卡，用来做备份，或者SST，Group Communication等等各种内部的数据传输。1T的数据的备份，也会占用大量的磁盘IO，如果是SSD还好，当然这里忽略某些厂商的产品在集中IO的时候会出一些BUG的问题。如果是普通的物理磁盘，则在不限流的情况下去执行xtrabackup，该实例基本不可用。
（2）对数据表的修改。如果某个表过大，对此表做DDL的时候，MySQL会锁住全表，这个时间可能很长，在这段时间业务不能访问此表，影响甚大。解决的办法有类似腾讯游戏DBA自己改造的可以在线秒改表，不过他们目前也只是能添加字段而已，对别的DDL还是无效；或者使用pt-online-schema-change，当然在使用过程中，它需要建立触发器和影子表，同时也需要很长很长的时间，在此操作过程中的所有时间，都可以看做是风险时间。把数据表切分，总量减小，有助于改善这种风险。
（3）整个表热点，数据访问和更新频繁，经常有锁等待，你又没有能力去修改源码，降低锁的粒度，那么只会把其中的数据物理拆开，用空间换时间，变相降低访问压力。
原则二：表设计不合理，需要对某些字段垂直拆分
这里举一个例子，如果你有一个用户表，在最初设计的时候可能是这样：
table :users
id bigint 用户的ID
name varchar 用户的名字
last_login_time datetime 最近登录时间
personal_info text 私人信息
xxxxx 其他信息字段。
一般的users表会有很多字段，我就不列举了。如上所示，在一个简单的应用中，这种设计是很常见的。但是：
设想情况一：你的业务中彩了，用户数从100w飙升到10个亿。你为了统计活跃用户，在每个人登录的时候都会记录一下他的最近登录时间。并且的用户活跃得很，不断的去更新这个login_time，搞的你的这个表不断的被update，压力非常大。那么，在这个时候，只要考虑对它进行拆分，站在业务的角度，最好的办法是先把last_login_time拆分出去，我们叫它 user_time。这样做，业务的代码只有在用到这个字段的时候修改一下就行了。如果你不这么做，直接把users表水平切分了，那么，所有访问users表的地方，都要修改。或许你会说，我有proxy，能够动态merge数据。到目前为止我还从没看到谁家的proxy不影响性能的。
设想情况二：personal_info这个字段本来没啥用，你就是让用户注册的时候填一些个人爱好而已，基本不查询。一开始的时候有它没它无所谓。但是到后来发现两个问题，一，这个字段占用了大量的空间，因为是text嘛，有很多人喜欢长篇大论地介绍自己。更糟糕的是二，不知道哪天哪个产品经理心血来潮，说允许个人信息公开吧，以方便让大家更好的相互了解。那么在所有人猎奇窥私心理的影响下，对此字段的访问大幅度增加。数据库压力瞬间抗不住了，这个时候，只好考虑对这个表的垂直拆分了。
原则三：某些数据表出现了无穷增长
例子很好举，各种的评论，消息，日志记录。这个增长不是跟人口成比例的，而是不可控的，例如微博的feed的广播，我发一条消息，会扩散给很多很多人。虽然主体可能只存一份，但不排除一些索引或者路由有这种存储需求。这个时候，增加存储，提升机器配置已经苍白无力了，水平切分是最佳实践。拆分的标准很多，按用户的，按时间的，按用途的，不在一一举例。
原则四：安全性和可用性的考虑
这个很容易理解，鸡蛋不要放在一个篮子里，我不希望我的数据库出问题，但我希望在出问题的时候不要影响到100%的用户，这个影响的比例越少越好，那么，水平切分可以解决这个问题，把用户，库存，订单等等本来同统一的资源切分掉，每个小的数据库实例承担一小部分业务，这样整体的可用性就会提升。这对Qunar这样的业务还是比较合适的，人与人之间，某些库存与库存之间，关联不太大，可以做一些这样的切分。
原则五：业务耦合性考虑
这个跟上面有点类似，主要是站在业务的层面上，我们的火车票业务和烤羊腿业务是完全无关的业务，虽然每个业务的数据量可能不太大，放在一个MySQL实例中完全没问题，但是很可能烤羊腿业务的DBA 或者开发人员水平很差，动不动给你出一些幺蛾子，直接把数据库搞挂。这个时候，火车票业务的人员虽然技术很优秀，工作也很努力，照样被老板打屁股。解决的办法很简单:惹不起，躲得起。
《三国演义》第一回：“话说天下大势，分久必合，合久必分。”其实在实践中，有时候可能你原本要分，后来又发现分了还得合，分分合合，完全是现实的需求，随需而变才是王道，而DBA的价值也能在此体现。或分或合的情况太多，不能穷举，欢迎继续交流这个话题，如果以上有错误之后，也请批评指正。
给生活加点料。
================================
文章摘自微信公众号formysql。
如何分表的方案，其实这个不能一概而论，与业务逻辑有关系，与数据性质有关系，比如订单类型的，那就非常容易了，通过时间这个特性，可以通过一个路由表，把数据分散到多个实例上面，或者多个表上面，扩展性非常强，但是如果是用户关系等类似的表，他的唯一可以做HASH的值就是用户ID，做HASH时，涉及到不均匀、可扩展能力，迁移麻烦等问题，所以还是不太容易的，所以只能是具体问题具体分析了。
上面说的是分表的优化方案，当然还有其它方案，那就是要尽可能的写好SQL语句，不要留坑，MySQL就是适合那种快进快出的语句，尽可能的别把业务逻辑放到MySQL中去处理，要保持MySQL的高效运行才是最正确的选择。
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
优化顺序：优化sql和索引 > 加缓存 memcached，redis > 主从复制或主主复制，读写分离 > mysql分区表 > 垂直分区 > 水平分区
很多人第一反应是各种切分；我给的顺序是:
第一优化你的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吧！说它成本低，却又是一项费时费力的活，需要在技术与业务都熟悉的情况下，用心去优化才能做到最优，优化后的效果也是立竿见影的！++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
一个合格的技术人，应该能做出适用于未来的产品。引入分布式会带来很多麻烦，但它会让你走得更远。这不正是技术人的价值所在吗？
看到有人回复说有钱就上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: 垂直切分一般都要做，只不过业务粒度大小而已。
分库有是经常用的，就算当前压力小，也尽量分出几个逻辑库出来。等规模上去了，很方便就迁移扩展。
水平拆分有一定难度，但如果将来一定会到这个规模，又可能用到，建议越早做越好。因为对应用的改动较大，而且迁移成本高。
