想一想千万级大表如何设计或优化？除此之外，没有其他信息。我个人觉得这个题目有点玄乎，所以我要简单说明一下怎么做。对于存储设计，必须考虑业务特征。收集到的信息如下：1.数据容量：1-3年大概有多少条数据，大概每条数据多少字节；2、数据项：是否有大字段，那些字段的值是否经常更新；3、数据查询SQL条件：哪些数据项列名经常出现在WHERE、GROUPBY、ORDERBY子句中；4、数据更新类SQL条件：UPDATE或DELETE的WHERE子句中经常出现多少列；5、统计SQL量的比例，如：SELECT:UPDATE+DELETE:INSERT=Howmany？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调整一些参数值：sort_buffer_size,read_buffer_size,read_rnd_buffer_size,join_buffer_size案例1我现在公司三张表5亿条数据，并且每张表每天的增量大概是每张100w以下是我做的10列左右的测试对比。1、先看发动机。在数据量很大的情况下，mysiam在只读不分区的情况下，效率比innodb高13%左右。2、分区后可以看mysql的官方文档。事实上，对于一部分ition专门针对myisam进行了优化。对于innodb来说，所有的数据都存储在ibdata中，所以即使看到schema变了，也没有本质的变化。当分区在同一个物理磁盘下时，提升只有1%左右。分区在不同的物理磁盘下。我把它分成了三个不同的磁盘，大约有3%的提升。事实上，所谓的吞吐量是由很多因素决定的。比如你在explainparition中可以看到记录在哪个分区，如果每个分区都有，本质上并不能解决读的问题，只会提高写的效率。另一个问题是如何划分分区。如果一个表有三列，它们通常被用作查询条件。其实这是一件很悲惨的事情，因为你没有办法对所有的sql做针对性的分区。如果像mysql官方文档说的Partitioning那样只对时间做一个，如果只用时间查询，恭喜你3.表主要用于读还是写？事实上，这个问题是不够的。应该这样问。你写的时候，同时有多少个并发查询？我的问题比较简单，因为不支持mongodb的shredding支持，而且crush之后还是回到mysql，所以一般情况下，9am-9pm之间写入比较多。这个时候我会做一个视图，这个视图是根据最近插入或者经常查询的，读通过做一个视图来分离，也就是写在表上，读在之前的视图上操作做出逻辑判断。4.做一些归档表，比如先对这些大表做大量已有统计分析，然后通过已有分析+增量解决。5.如果你使用mysiam，还有一个问题你要注意。如果你在你的.configure中添加一个maxindexlength参数，当你的记录数大于指定的长度时，索引将被禁用。案例2任何偏离业务场景的优化都是流氓。如果是订单表，主要通过orderid查询订单信息，并将此类Tables垂直分库，每个库容量500万，按照订单号维度拆分成多个库。查询时使用订单号进行查询，通过某个业务规则目标库直接定位到要查询的查询。或者使用用户ID和日期维度来分库，但是查询的时候一定要注意分库的条件。如果是CRM系统，不是直接用订单号直接查询，而是范围查询，返回一个列表集合，你继续执着于分库分表来解决你的性能问题，所以你需要查询每个库如果结果集是union的，那么数据库的性能不但没有提升反而适得其反！解决方案首先，任何优化都需要您了解您的业务和数据。QPS是多少？-在带宽和存储足够的情况下，单机几千QPS比较合适。读和写的比例是多少？-多读少写和多写少读的优化方式大不相同。设置成只读场景，果断压缩。数据是否快速增长？-基本上是QPS的要求。数据和服务的SLA是多少？-数据是否需要高度一致？HA的范围是什么？等等。不同场景侧重点不同，解决方案也不同。对于一些典型场景，可能已经有成熟的解决方案。题目已经标明“千万级别”，所以下面假设题目是最常见的场景：数据量大，QPS要求高，读多写少，数据增长快，SLA要求高。其次，说一下优化方法。主要从三个维度：Why、How、When。0.sqlvsnosql有点跑题，但也是很重要的一个方面。Why：nosql天生就是分布式的，而且大部分都针对某类数据和某类使用场景进行了优化。比如大量的监控数据，用mysql存储起来费时费力。可以选择mongo，甚至是时序数据库，访问量会增加一个数量级。How：找到对应的解决方案。When：诱惑够多——有针对使用场景的成熟方案，效率大幅提升。1、优化shema、sql语句+索引原因：MySQL架构再好，也无法处理频繁的垃圾查询。不合理的模式设计也会导致数据访问缓慢。索引的作用不用多说，但是在InnoDB下，错误的索引可能不仅会拖慢查询速度。How：设计阶段需要预估QPS和数据量，参考业务场景的数据需求，合理设计表结构（参考mysql在线DDL问题），甚至违反设计范式实现适当的冗余。生产环境分析慢日志，优化语句。索引的设计需要知道索引是如何使用的，比如innodb的锁机制。When：这不仅是首先要考虑的事情，还应该不断优化。尤其是在业务方面。但如果这是实际环境中的问题，一般就比较幸运了，因为一般都优化了很多。实践中遇到的一般都是更深层次的问题。2.缓存缓存不是那么简单。缓存对应用程序不是完全透明的，除非你使用Django等成熟的框架，而且缓存粒度非常大，但是实用。..和python一样，至少要加几个装饰器。如何保证缓存中的数据始终正确？在写入数据之前或写入数据之后使缓存无效？缓存宕机或过冷，流量过载到后端mysql怎么办？缓存不是一切。如果写多读少，命中率就会很低。How：memcache用于缓存，redis用于需要持久化的场景。（redis能不能完全替代memcache？呵呵。。。）也可以使用mysql自带的querycache，对应用程序基本是完全透明的。但它会仅限于本机。而且只缓存查询结果，mc和redis可以缓存一些处理过的数据。而且，当数据量大，QPS大的时候，还需要考虑分片和HA的问题。如果数据过热并且节点不堪重负怎么办？when：基本上大部分读多写少的场景都可以使用，但是在写多的情况下可能需要考虑。3、复制和读写分离（主从复制或者主从复制，读写分离，可以在应用层做，效率高，或者使用第三方工具，第三方工具推荐360的图集，其他的要么效率低High，要么没人维护）Why：这个其实在大多数场景下是必须的。因为复制可以实现备份、高可用、负载均衡。就算你觉得做负载均衡不麻烦，那备份也总有必要吧？既然已经备份好了，为什么不加个LVS+HAProxy做HA呢？对了，稍微修改一下应用，读写分离就变成了。How：节点少的时候，master和backup。前面加上Keepalived+HAProxy等组件，失效??自动切换。读写分离可能需要修改应用程序。在节点多的情况下，一是考虑多级备份，减轻master的压力。其次，可以引入第三方组件来接管主节点的备份工作。师傅不是很推荐。一是需要考虑数据冲突的情况，比如错开id，同时操作数据后解决冲突。其次，如果有强一致性，延迟会增加。如果一个节点挂掉了，需要等到超时再返回。何时：几乎大多数场景的主用/备用。即使不考虑数据大小。高可用性对应用程序是透明的，为什么不用呢？对高手来说比较麻烦，建议先用分段。4.Sharding包括垂直分片和水平分片，实现方式也包括分库分表。虽然有点难度，但还是推荐大家常用。理由：垂直细分保证了业务的独立性，避免了不同业务之间的资源竞争。毕竟，企业有优先权。水平切分主要用于突破单机瓶颈。除了主master，只有分裂才能真正分散负载。也可以对拆分后的不同分片数据进行不同的优化。如果按时间划分，数据在一定时间后不允许修改，可以引入压缩，可以大大减少数据的传输和读取。How：根据业务垂直细分。分区业务中的数据库和表。通常，需要修改应用程序。除了分表，其余的实现都不是很复杂。有第三方组件可用，但最有效和灵活的方法是自己编写客户端。When：一般需要纵向切分，但只是业务粒度的大小。分库经常用到，即使现在压力不大，也尽量单独出几个逻辑库。规模上去的时候，迁移和扩展非常方便。横向拆分比较难，但是如果以后肯定会达到这个规模，而且可能会用到，建议还是尽快做。因为对应用的改动比较大，迁移成本高。综上所述，数据库设计应面向现代化、面向世界、面向未来。..从一般运维的角度，什么时候需要考虑分库分表？首先，这里所说的分库分表，是指将数据库数据物理拆分到多个实例或多台机器上，而不是类似于分区表的就地拆分。原则零：分不清就别分。是的，MySQL是一个关系型数据库，数据库表之间的关系从一定的角度映射业务逻辑。任何分库分表的行为都会在一定程度上增加业务逻辑的复杂度。数据库除了承载数据的存储和访问，帮助业务更好地实现需求和逻辑也是其重要的工作之一。分库分表会带来数据合并、查询或更新条件分离、事务分离等多种后果。业务实施的复杂度往往会成倍或呈指数增长。所以，在分库分表之前，不要为了分而分，做其他力所能及的事情，比如升级硬件，升级，升级网络，升级数据库版本，读写分离，负载均衡等很快。所有分库分表的前提是你已经尽力了。原则一：数据量过大，正常运维会影响正常业务访问。这里所说的运维，例如：（1）数据库的备份。如果单表或单实例过大，则需要大量的磁盘IO或网络IO资源进行备份。比如1T的数据，当网络传输占用50MB时，需要20000秒才能完成传输，整个过程的维护风险比平时要高。我们在去哪儿做的是给所有的数据库机器加第二块网卡做备份，或者SST、GroupCommunication等各种内部数据传输。1T数据的备份也会占用大量的磁盘IO。如果是SSD就没问题。当然有些厂商的产品在IO集中的时候会出现一些bug。如果是普通物理盘，不限流执行xtrabackup的话，实例基本不可用。(2)数据表的修改。如果某个表太大，MySQL在对这个表做DDL时会锁住整个表。这个时间可能会很长，这段时间业务不能访问这张表，影响会很大。方案类似于腾讯游戏DBA自己改造，可以在线秒改表，但是他们目前只能添加字段，对其他DDL还是无效的；或者使用pt-online-schema-change，当然在使用过程中，需要建立触发器和影子表，同样需要很长很长的时间。在此操作过程中的所有时间都可以视为风险时间。划分数据表并减少总量可以帮助改善这种风险。（3）整表热，数据访问更新频繁，经常有锁等待，你没有能力修改源码降低锁的粒度，那你就只能物理拆解里面的数据，以空间换时间，变相减少访问压力。原则二：表格设计不合理，部分字段需要垂直拆分。这是一个例子。如果你有一个user表，在最初设计的时候可能是这样的：table:usersidbigint:用户IDnamevarchar:用户名last_login_timedatetime:最近登录时间personal_infotext:私人信息xxxxx:其他信息字段。一般users表会有很多字段，就不一一列举了。在一个简单的应用程序中，如上所示，这种设计是相当普遍的。但是：想象一下情况1：您的企业中了彩票，用户数量从100万猛增到10亿。为了统计活跃用户，你会在所有人登录的时候记录他最近的登录时间。而且用户很活跃，不断更新login_time，你的表也在不断更新，压力很大。嗯，这个时候，就考虑拆分吧。从业务的角度来说，最好的办法是先拆分last_login_time，我们称之为user_time。这样，只需要在使用该字段时修改业务代码即可。如果不这样做，水平拆分users表，那么必须修改所有对users表的访问权限。也许你会说，我有一个可以动态合并数据的代理。到目前为止，我从未见过任何人的代理不影响性能。场景2：personal_info字段没有用。你注册的时候就让用户填一些个人爱好，基本不查询。一开始，它在不在并不重要。但是后来我发现了两个问题。第一，这个字段比较占篇幅，因为是文字，很多人喜欢长篇大论地自我介绍。更惨的是两个，不知道哪天哪位产品经理突发奇想，说允许公开个人信息，让大家更好的了解彼此。然后在大家的好奇心和窥淫癖心理的影响下，这个领域的访问量明显增加了。数据库的压力一时也顶不住。这时候，我们就不得不考虑对这张表进行垂直拆分了。原则三：一些数据表中无限增长的一些例子非常好，比如各种评论，留言，日志记录。这种增长与人口不成正比，但无法控制。比如在微博的传播中，我发一条消息，就会传播给很多很多人。虽然可能只存储一份主题，但不排除某些索引或路由有这样的存储需求。这个时候增加存储和提高机器配置是没有用的，横向切分才是最好的做法。拆分的标准有很多，比如按用户、按时间、按用途，我就不一一举例了。原则4：安全性和可用性方面的考虑这很容易理解。不要把所有的鸡蛋都放在一个篮子里。我不希望我的数据库出问题，但我希望出问题时100%的用户不会受到影响。这个影响比值越小越好。那么，水平切分可以解决这个问题，将用户、库存、订单等从同一个统一的资源中切分出来。每个小型数据库实例承担一小部分业务，这样整体的可用性可以降低也会提高。这更适合像去哪儿这样的企业。人和某些库存之间没有太大的关联，可以做一些这样的细分。原则5：业务耦合的考虑这个和上面有些类似，主要是在业务层面，我们的火车票业务和烤羊腿业务是完全不相关的业务，虽然每个业务的数据量可能不会太大，把在MySQL实例中是完全没有问题的，但是很可能是烤羊腿业务的DBA或者开发人员很穷，动不动就给你一些飞蛾扑火直接挂数据库。这时，火车票业务人员虽然技术非常好，工作也很努力，但还是被老板打了屁股。解决办法很简单：惹不起，但躲得起。20条规则总结如下：规则一：一般情况下可以选择MyISAM存储引擎，如果需要事务支持则必须使用InnoDB存储引擎。规则2：命名规则。规则三：数据库字段类型定义经常需要对占用CPU的字段进行计算和排序，应尽量选择较快的字段，如使用TIMESTAMP（4字节，最小值1970-01-0100:00:00）代替Datetime（8字节，最小值1001-01-0100:00:00），使用varchar代替浮点数，字符变长字段由整数代替char对于二进制多媒体数据，管道数据（如日志）、超大文本数据不应放在数据库字段中规则四：业务逻辑执行过程中必须读取的表必须有初始值。避免导致程序失败的业务读数为负值或无限值。规则五：不必遵守范式理论，适度冗余，让Query尽量减少join。法则六：对访问频率低的大字段拆分数据表。一些大字段占用空间大，访问频率明显低于其他字段。在这种情况下，拆分字段不需要在频繁查询时读取大字段，造成IO资源的浪费。规则7：水平分表，这个我还是建议三思而后行，可能不但不能提高性能反而会增加join数和磁盘IO，开发起来也麻烦。有很多业务需要一次查询大部分字段才能看到你的业务场景。大表可以水平拆分。大表影响查询效率。根据业务特点有多种拆分方式。比如按时间增加的数据，可以按时间划分。按id划分的数据可以按照id%数据库个数进行拆分。规则八：业务需要的相关索引根据实际设计中构造的SQL语句的where条件确定。如果业务不需要，则不建索引，联合索引（或主键）中不允许有多个字段。特别是该字段根本不会出现在条件语句中。规则9：要唯一确定一条记录的一个或多个字段，必须建立主键或唯一索引。一条记录不能唯一确定。为了提高查询效率，建立了一个公共索引。规则10：对于业务使用的表，有些记录很少，甚至只有一条记录。为了满足约束的需要，还必须设置索引或主键。规则11：对于取值不能重复且经常作为查询条件的字段，应建立唯一索引（主键默认唯一索引），并在查询条件中放置该字段的条件在第一个位置。无需创建与该字段相关的联合索引。规则十二：对于经常查询的字段，其值不唯一的，也应该考虑建立一个公共索引。将字段条件放在查询语句的第一个位置，联合索引处理方法相同。规则十三：业务通过非唯一索引访问数据时，需要考虑索引值返回的记录密度。原则上，最大可能的密度不能高于0.2。如果密度太大，不适合建索引。规则十四：需要联合索引（或联合主键）的数据库要注意索引的顺序。SQL语句中的匹配条件也要和索引的顺序一致。注意：索引的不正确对齐也会导致严重的后果。规则十五：以表中多个字段查询作为查询条件，不包含其他索引，字段的联合值不重复。可以对这多个字段建立唯一联合索引，假设索引字段为(a1,a2,...an)，查询条件(a1opval1,a2opval2,...amopvalm)m<=n，可以使用索引，查询条件中字段的位置与索引中的字段位置一致。规则十六：建立联合索引的原则（以下假设在数据库表的a、b、c字段上建立联合索引（a，b，c））。规则17：重要业务访问数据表时。但当无法通过索引访问数据时，应保证顺序访问的记录数有限，原则上不超过10条。法则18：合理构造Query语句，监控慢SQL，检查是否有大量的子查询和相关查询嵌套查询等，尽量避免使用这些查询，使用连接（JOIN）代替子查询（Sub-Queries），使用联合（UNION）代替手动创建的临时表。第十九条：优化应用系统。Rule20：可以结合redis、memcache等缓存服务，将这些复杂的SQL进行拆分，充分利用二级缓存，减少数据库IO操作。充分利用数据库连接池、mybatis、hiberante二级缓存。尝试使用顺序IO而不是随机IO。合理使用索引，尽量避免全表扫描。
