MySQL对于千万级的⼤表要怎么优化？
千万级，MySQL实际上确实不是什么压⼒，InnoDB的存储引擎，使⽤的是B+树存储结构，千万级的数据量，基本也就是三到四层的搜索，如果有合适的索引，性能基本也不是问题。
sql语句优化方式
但经常出现的情况是，业务上⾯的增长，导致数据量还会继续增长，为了应对这⽅⾯的问题⽽必须要做扩展了此时可能⾸先需要考虑的就是分表策略了。
当然分表，可能还有其它⼏个原因，⽐如表变⼤了，千万级的数据库，为了减少运维成本，降低风险，就想到了通过分表来解决问题，这都是⽐较合适的。
分表，还有另⼀个⽅⾯的意思，就是在数据量更⼤的情况下，为了分担业务压⼒，将数据表分到不同的实例中去，这样有两⽅⾯的好处：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: 垂直切分⼀般都要做，只不过业务粒度⼤⼩⽽已。
分库有是经常⽤的，就算当前压⼒⼩，也尽量分出⼏个逻辑库出来。等规模上去了，很⽅便就迁移扩展。
⽔平拆分有⼀定难度，但如果将来⼀定会到这个规模，⼜可能⽤到，建议越早做越好。因为对应⽤的改动较⼤，⽽且迁移成本⾼。
综上，数据库设计要⾯向现代化，⾯向世界，⾯向未来。。。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。