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吧！说它成本低，却又是一项费时费力的活，需要在技术与业务都熟悉的情况下，用心去优化才能做到最优，优化后的效果也是立竿见影的！

千万级大表如何优化，这是一个很有技术含量的问题，通常我们的直觉思维都会跳转到拆分或者数据分区，在此我想做一些补充和梳理，想和大家做一些这方面的经验总结，也欢迎大家提出建议。

从一开始脑海里开始也是火光四现，到不断的自我批评，后来也参考了一些团队的经验，我整理了下面的大纲内容。

既然要吃透这个问题，我们势必要回到本源，我把这个问题分为三部分:

“千万级”，“大表”，“优化”，

也分别对应我们在图中标识的

“数据量”，“对象”和“目标”。

我来逐步展开说明一下，从而给出一系列的解决方案。

1.数据量：千万级

千万级其实只是一个感官的数字，就是我们印象中的数据量大。 这里我们需要把这个概念细化，因为随着业务和时间的变化，数据量也会有变化，我们应该是带着一种动态思维来审视这个指标，从而对于不同的场景我们应该有不同的处理策略。

1) 数据量为千万级，可能达到亿级或者更高

通常是一些数据流水，日志记录的业务，里面的数据随着时间的增长会逐步增多，超过千万门槛是很容易的一件事情。

2) 数据量为千万级，是一个相对稳定的数据量

如果数据量相对稳定，通常是在一些偏向于状态的数据，比如有1000万用户，那么这些用户的信息在表中都有相应的一行数据记录，随着业务的增长，这个量级相对是比较稳定的。

3) 数据量为千万级，不应该有这么多的数据

这种情况是我们被动发现的居多，通常发现的时候已经晚了，比如你看到一个配置表，数据量上千万;或者说一些表里的数据已经存储了很久，99%的数据都属于过期数据或者垃圾数据。

数据量是一个整体的认识，我们需要对数据做更近一层的理解，这就可以引出第二个部分的内容。

2.对象：数据表

数据操作的过程就好比数据库中存在着多条管道，这些管道中都流淌着要处理的数据，这些数据的用处和归属是不一样的。

一般根据业务类型把数据分为三种：

（1）流水型数据

流水型数据是无状态的，多笔业务之间没有关联，每次业务过来的时候都会产生新的单据，比如交易流水、支付流水，只要能插入新单据就能完成业务，特点是后面的数据不依赖前面的数据，所有的数据按时间流水进入数据库。

（2）状态型数据

状态型数据是有状态的，多笔业务之间依赖于有状态的数据，而且要保证该数据的准确性，比如充值时必须要拿到原来的余额，才能支付成功。

（3）配置型数据

此类型数据数据量较小，而且结构简单，一般为静态数据，变化频率很低。

至此，我们可以对整体的背景有一个认识了，如果要做优化，其实要面对的是这样的3*3的矩阵，如果要考虑表的读写比例（读多写少，读少写多...），那么就会是3*3*4=24种，显然做穷举是不显示的，而且也完全没有必要，可以针对不同的数据存储特性和业务特点来指定不同的业务策略。

对此我们采取抓住重点的方式，把常见的一些优化思路梳理出来，尤其是里面的核心思想，也是我们整个优化设计的一把尺子，而难度决定了我们做这件事情的动力和风险。

而对于优化方案，我想采用面向业务的维度来进行阐述。

3.目标：优化

在这个阶段，我们要说优化的方案了，总结的有点多，相对来说是比较全了。

整体分为五个部分：

其实我们通常所说的分库分表等方案只是其中的一小部分，如果展开之后就比较丰富了。

其实不难理解，我们要支撑的表数据量是千万级别，相对来说是比较大了，DBA要维护的表肯定不止一张，如何能够更好的管理，同时在业务发展中能够支撑扩展，同时保证性能，这是摆在我们面前的几座大山。

想要处理面前的这几座大山，就需要不断学习mysql，下方的课程，数据库实战32讲，带您深度剖析MySQL！

我们分别来说一下这五类改进方案：

优化设计方案1.规范设计

在此我们先提到的是规范设计，而不是其他高大上的设计方案。

黑格尔说：秩序是自由的第一条件。在分工协作的工作场景中尤其重要，否则团队之间互相牵制太多，问题多多。

规范设计我想提到如下的几个规范，其实只是属于开发规范的一部分内容，可以作为参考。

规范的本质不是解决问题，而是有效杜绝一些潜在问题，对于千万级大表要遵守的规范，我梳理了如下的一些细则，基本可以涵盖我们常见的一些设计和使用问题，比如表的字段设计不管三七二十一，都是varchar(500),其实是很不规范的一种实现方式，我们来展开说一下这几个规范。

1）配置规范

（1）MySQL数据库默认使用InnoDB存储引擎。

（2）保证字符集设置统一，MySQL数据库相关系统、数据库、表的字符集使都用UTF8，应用程序连接、展示等可以设置字符集的地方也都统一设置为UTF8字符集。

注：UTF8格式是存储不了表情类数据，需要使用UTF8MB4，可在MySQL字符集里面设置。在8.0中已经默认为UTF8MB4，可以根据公司的业务情况进行统一或者定制化设置。

（3）MySQL数据库的事务隔离级别默认为RR（Repeatable-Read），建议初始化时统一设置为RC（Read-Committed），对于OLTP业务更适合。

（4）数据库中的表要合理规划，控制单表数据量，对于MySQL数据库来说，建议单表记录数控制在2000W以内。

（5）MySQL实例下，数据库、表数量尽可能少；数据库一般不超过50个，每个数据库下，数据表数量一般不超过500个（包括分区表）。

2）建表规范

（1）InnoDB禁止使用外键约束，可以通过程序层面保证。

（2）存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE。

（3）整型定义中无需定义显示宽度，比如：使用INT，而不是INT(4)。

（4）不建议使用ENUM类型，可使用TINYINT来代替。

（5）尽可能不使用TEXT、BLOB类型，如果必须使用，建议将过大字段或是不常用的描述型较大字段拆分到其他表中；另外，禁止用数据库存储图片或文件。

（6）存储年时使用YEAR(4)，不使用YEAR(2)。

（7）建议字段定义为NOT NULL。

（8）建议DBA提供SQL审核工具，建表规范性需要通过审核工具审核后

3）命名规范

（1）库、表、字段全部采用小写。

（2）库名、表名、字段名、索引名称均使用小写字母，并以“_”分割。

（3）库名、表名、字段名建议不超过12个字符。（库名、表名、字段名支持最多64个字符，但为了统一规范、易于辨识以及减少传输量，统一不超过12字符）

（4）库名、表名、字段名见名知意，不需要添加注释。

对于对象命名规范的一个简要总结如下表4-1所示，供参考。

4）索引规范

（1）索引建议命名规则：idx_col1_col2[_colN]、uniq_col1_col2[_colN]（如果字段过长建议采用缩写）。

（2）索引中的字段数建议不超过5个。

（3）单张表的索引个数控制在5个以内。

（4）InnoDB表一般都建议有主键列，尤其在高可用集群方案中是作为必须项的。

（5）建立复合索引时，优先将选择性高的字段放在前面。

（6）UPDATE、DELETE语句需要根据WHERE条件添加索引。

（7）不建议使用%前缀模糊查询，例如LIKE “%weibo”，无法用到索引，会导致全表扫描。

（8）合理利用覆盖索引，例如：

（9）SELECT email,uid FROM user_email WHERE uid=xx，如果uid不是主键，可以创建覆盖索引idx_uid_email(uid,email)来提高查询效率。

（10）避免在索引字段上使用函数，否则会导致查询时索引失效。

（11）确认索引是否需要变更时要联系DBA。

5）应用规范

（1）避免使用存储过程、触发器、自定义函数等，容易将业务逻辑和DB耦合在一起，后期做分布式方案时会成为瓶颈。

（2）考虑使用UNION ALL，减少使用UNION，因为UNION ALL不去重，而少了排序操作，速度相对比UNION要快，如果没有去重的需求，优先使用UNION ALL。

（3）考虑使用limit N，少用limit M，N，特别是大表或M比较大的时候。

（4）减少或避免排序，如：group by语句中如果不需要排序，可以增加order by null。

（5）统计表中记录数时使用COUNT(*)，而不是COUNT(primary_key)和COUNT(1)；InnoDB表避免使用COUNT(*)操作，计数统计实时要求较强可以使用Memcache或者Redis，非实时统计可以使用单独统计表，定时更新。

（6）做字段变更操作（modify column/change column）的时候必须加上原有的注释属性，否则修改后，注释会丢失。

（7）使用prepared statement可以提高性能并且避免SQL注入。

（8）SQL语句中IN包含的值不应过多。

（9）UPDATE、DELETE语句一定要有明确的WHERE条件。

（10）WHERE条件中的字段值需要符合该字段的数据类型，避免MySQL进行隐式类型转化。

（11）SELECT、INSERT语句必须显式的指明字段名称，禁止使用SELECT * 或是INSERT INTO table_name values()。

（12）INSERT语句使用batch提交（INSERT INTO table_name VALUES(),(),()……），values的个数不应过多。

优化设计方案2：业务层优化

业务层优化应该是收益最高的优化方式了，而且对于业务层完全可见，主要有业务拆分，数据拆分和两类常见的优化场景（读多写少，读少写多）

1）业务拆分

ü 将混合业务拆分为独立业务

ü 将状态和历史数据分离

业务拆分其实是把一个混合的业务剥离成为更加清晰的独立业务，这样业务1，业务2。。。独立的业务使得业务总量依旧很大，但是每个部分都是相对独立的，可靠性依然有保证。

对于状态和历史数据分离，我可以举一个例子来说明。

例如：我们有一张表Account，假设用户余额为100。

我们需要在发生数据变更后，能够追溯数据变更的历史信息，如果对账户更新状态数据，增加100的余额，这样余额为200。

这个过程可能对应一条update语句，一条insert语句。

对此我们可以改造为两个不同的数据源，account和account_hist

在account_hist中就会是两条insert记录，如下:

而在account中则是一条update语句，如下：

这也是一种很基础的冷热分离，可以大大减少维护的复杂度，提高业务响应效率。

2）数据拆分

2.1 按照日期拆分，这种使用方式比较普遍，尤其是按照日期维度的拆分，其实在程序层面的改动很小，但是扩展性方面的收益很大。

• 数据按照日期维度拆分，如test_20191021
• 数据按照周月为维度拆分,如test_201910
• 数据按照季度，年维度拆分,如test_2019

2.2 采用分区模式，分区模式也是常见的使用方式，采用hash,range等方式会多一些，在MySQL中我是不大建议使用分区表的使用方式，因为随着存储容量的增长，数据虽然做了垂直拆分，但是归根结底，数据其实难以实现水平扩展，在MySQL中是有更好的扩展方式。

2.3 读多写少优化场景

采用缓存，采用Redis技术，将读请求打在缓存层面，这样可以大大降低MySQL层面的热点数据查询压力。

2.4 读少写多优化场景，可以采用三步走：

1) 采用异步提交模式，异步对于应用层来说最直观的就是性能的提升，产生最少的同步等待。

2) 使用队列技术，大量的写请求可以通过队列的方式来进行扩展，实现批量的数据写入。

3) 降低写入频率，这个比较难理解，我举个例子

对于业务数据，比如积分类，相比于金额来说业务优先级略低的场景，如果数据的更新过于频繁，可以适度调整数据更新的范围（比如从原来的每分钟调整为10分钟）来减少更新的频率。

例如：更新状态数据，积分为200，如下图所示

可以改造为，如下图所示。

如果业务数据在短时间内更新过于频繁，比如1分钟更新100次，积分从100到10000，则可以根据时间频率批量提交。

例如：更新状态数据，积分为100，如下图所示。

无需生成100个事务（200条SQL语句）可以改造为2条SQL语句，如下图所示。

对于业务指标，比如更新频率细节信息，可以根据具体业务场景来讨论决定。

优化设计方案3：架构层优化

架构层优化其实就是我们认为的那种技术含量很高的工作，我们需要根据业务场景在架构层面引入一些新的花样来。

3.1.系统水平扩展场景

3.1.1采用中间件技术，可以实现数据路由，水平扩展，常见的中间件有MyCAT，ShardingSphere,ProxySQL等

3.1.2 采用读写分离技术，这是针对读需求的扩展，更侧重于状态表，在允许一定延迟的情况下，可以采用多副本的模式实现读需求的水平扩展，也可以采用中间件来实现，如MyCAT,ProxySQL,MaxScale,MySQL Router等

3.1.3 采用负载均衡技术，常见的有LVS技术或者基于域名服务的Consul技术等

3.2.兼顾OLTP+OLAP的业务场景，可以采用NewSQL，优先兼容MySQL协议的HTAP技术栈，如TiDB

3.3.离线统计的业务场景，有几类方案可供选择。

3.3.1 采用NoSQL体系，主要有两类，一类是适合兼容MySQL协议的数据仓库体系，常见的有Infobright或者ColumnStore，另外一类是基于列式存储，属于异构方向，如HBase技术

3.3.2 采用数仓体系，基于MPP架构,如使用Greenplum统计，如T+1统计

优化设计方案4：数据库优化

数据库优化，其实可打的牌也不少，但是相对来说空间没有那么大了，我们来逐个说一下。

4.1 事务优化

根据业务场景选择事务模型，是否是强事务依赖

对于事务降维策略，我们来举出几个小例子来。

4.1.1 降维策略1：存储过程调用转换为透明的SQL调用

对于新业务而言，使用存储过程显然不是一个好主意，MySQL的存储过程和其他商业数据库相比，功能和性能都有待验证，而且在目前轻量化的业务处理中，存储过程的处理方式太“重”了。

有些应用架构看起来是按照分布式部署的，但在数据库层的调用方式是基于存储过程，因为存储过程封装了大量的逻辑，难以调试，而且移植性不高，这样业务逻辑和性能压力都在数据库层面了，使得数据库层很容易成为瓶颈，而且难以实现真正的分布式。

所以有一个明确的改进方向就是对于存储过程的改造，把它改造为SQL调用的方式，可以极大地提高业务的处理效率，在数据库的接口调用上足够简单而且清晰可控。

4.1.2 降维策略2：DDL操作转换为DML操作

有些业务经常会有一种紧急需求，总是需要给一个表添加字段，搞得DBA和业务同学都挺累，可以想象一个表有上百个字段，而且基本都是name1，name2……name100，这种设计本身就是有问题的，更不用考虑性能了。究其原因，是因为业务的需求动态变化，比如一个游戏装备有20个属性，可能过了一个月之后就增加到了40个属性，这样一来，所有的装备都有40个属性，不管用没用到，而且这种方式也存在诸多的冗余。

我们在设计规范里面也提到了一些设计的基本要素，在这些基础上需要补充的是，保持有限的字段，如果要实现这些功能的扩展，其实完全可以通过配置化的方式来实现，比如把一些动态添加的字段转换为一些配置信息。配置信息可以通过DML的方式进行修改和补充，对于数据入口也可以更加动态、易扩展。

4.1.3 降维策略3：Delete操作转换为高效操作

有些业务需要定期来清理一些周期性数据，比如表里的数据只保留一个月，那么超出时间范围的数据就要清理掉了，而如果表的量级比较大的情况下，这种Delete操作的代价实在太高，我们可以有两类解决方案来把Delete操作转换为更为高效的方式。

第一种是根据业务建立周期表，比如按照月表、周表、日表等维度来设计，这样数据的清理就是一个相对可控而且高效的方式了。

第二种方案是使用MySQL rename的操作方式，比如一张2千万的大表要清理99%的数据，那么需要保留的1%的数据我们可以很快根据条件过滤补录，实现“移形换位”。

4.2 SQL优化

其实相对来说需要的极简的设计，很多点都在规范设计里面了，如果遵守规范，八九不离十的问题都会杜绝掉，在此补充几点：

4.2.1 SQL语句简化，简化是SQL优化的一大利器，因为简单，所以优越。

4.2.2 尽可能避免或者杜绝多表复杂关联，大表关联是大表处理的噩梦，一旦打开了这个口子，越来越多的需求需要关联，性能优化就没有回头路了，更何况大表关联是MySQL的弱项，尽管Hash Join才推出，不要像掌握了绝对大杀器一样，在商业数据库中早就存在，问题照样层出不穷。

4.2.3 SQL中尽可能避免反连接，避免半连接，这是优化器做得薄弱的一方面，什么是反连接，半连接？其实比较好理解，举个例子，not in ,not exists就是反连接，in,exists就是半连接，在千万级大表中出现这种问题，性能是几个数量级的差异。

4.3 索引优化

应该是大表优化中需要把握的一个度。

4.3.1 首先必须有主键，规范设计中第一条就是，此处不接收反驳。

4.3.2 其次，SQL查询基于索引或者唯一性索引，使得查询模型尽可能简单。

4.3.3 最后，尽可能杜绝范围数据的查询，范围扫描在千万级大表情况下还是尽可能减少。

​

优化设计方案4：管理优化

这部分应该是在所有的解决方案中最容易被忽视的部分了，我放在最后，在此也向运维同事致敬，总是为很多认为本应该正常的问题尽职尽责（背锅）。

千万级大表的数据清理一般来说是比较耗时的，在此建议在设计中需要完善冷热数据分离的策略，可能听起来比较拗口，我来举一个例子，把大表的Drop 操作转换为可逆的DDL操作。

Drop操作是默认提交的，而且是不可逆的，在数据库操作中都是跑路的代名词，MySQL层面目前没有相应的Drop操作恢复功能，除非通过备份来恢复，但是我们可以考虑将Drop操作转换为一种可逆的DDL操作。

MySQL中默认每个表有一个对应的ibd文件，其实可以把Drop操作转换为一个rename操作，即把文件从testdb迁移到testdb_arch下面；从权限上来说，testdb_arch是业务不可见的，rename操作可以平滑的实现这个删除功能，如果在一定时间后确认可以清理，则数据清理对于已有的业务流程是不可见的，如下图所示。

此外，还有两个额外建议，一个是对于大表变更，尽可能考虑低峰时段的在线变更，比如使用pt-osc工具或者是维护时段的变更，就不再赘述了。

最后总结一下，其实就是一句话：

千万级大表的优化是根据业务场景，以成本为代价进行优化的，绝对不是孤立的一个层面的优化。

想要完全吃透优化，就需要不断的学习MySQL，为大家推荐下面的课程，带大家一步步了解MySQL

个人新书 《MySQL DBA工作笔记》

个人公众号：jianrong-notes

修改回答，老有人说我是发广告，好吧，知乎不适合我这种玩

• 第一我不是阿里，腾讯员工，而且我也没必要帮他们发广告
• 第二，阿里云国内第一，世界第三，不需要我这种小白发广告，本身人家就是牛逼
• 第三，我只是记录我解决问题的过程，并不是回答如何优化！我的标准是，最低成本！不要花企业的钱
• 第四，评论你们一个表多少亿数据的，请公布你的硬件配置，或者你的优化建议。
• 第五，这次维护的表是六年前的，毕业生项目，硬件配置8核2G云服务

以下是原来答案

问题概述

使用阿里云rds for MySQL数据库（就是MySQL5.6版本），有个用户上网记录表6个月的数据量近2000万，保留最近一年的数据量达到4000万，查询速度极慢，日常卡死。严重影响业务。

问题前提：老系统，当时设计系统的人大概是大学没毕业，表设计和sql语句写的不仅仅是垃圾，简直无法直视。原开发人员都已离职，到我来维护，这就是传说中的维护不了就跑路，然后我就是掉坑的那个！！！

我尝试解决该问题，so，有个这个日志。

方案概述

• 方案一：优化现有mysql数据库。优点：不影响现有业务，源程序不需要修改代码，成本最低。缺点：有优化瓶颈，数据量过亿就玩完了。
• 方案二：升级数据库类型，换一种100%兼容mysql的数据库。优点：不影响现有业务，源程序不需要修改代码，你几乎不需要做任何操作就能提升数据库性能，缺点：多花钱
• 方案三：一步到位，大数据解决方案，更换newsql/nosql数据库。优点：没有数据容量瓶颈，缺点：需要修改源程序代码，影响业务，总成本最高。

以上三种方案，按顺序使用即可，数据量在亿级别一下的没必要换nosql，开发成本太高。三种方案我都试了一遍，而且都形成了落地解决方案。该过程心中慰问跑路的那几个开发者一万遍 :)

方案一详细说明：优化现有mysql数据库

跟阿里云数据库大佬电话沟通 and Google解决方案 and 问群里大佬，总结如下（都是精华）：

• 1.数据库设计和表创建时就要考虑性能
• 2.sql的编写需要注意优化
• 3.分区
• 4.分表
• 5.分库

1.数据库设计和表创建时就要考虑性能

mysql数据库本身高度灵活，造成性能不足，严重依赖开发人员能力。也就是说开发人员能力高，则mysql性能高。这也是很多关系型数据库的通病，所以公司的dba通常工资巨高。

设计表时要注意：

• 表字段避免null值出现，null值很难查询优化且占用额外的索引空间，推荐默认数字0代替null。
• 尽量使用INT而非BIGINT，如果非负则加上UNSIGNED（这样数值容量会扩大一倍），当然能使用TINYINT、SMALLINT、MEDIUM_INT更好。
• 使用枚举或整数代替字符串类型
• 尽量使用TIMESTAMP而非DATETIME
• 单表不要有太多字段，建议在20以内
• 用整型来存IP

索引

• 索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描
• 应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描
• 值分布很稀少的字段不适合建索引，例如"性别"这种只有两三个值的字段
• 字符字段只建前缀索引
• 字符字段最好不要做主键
• 不用外键，由程序保证约束
• 尽量不用UNIQUE，由程序保证约束
• 使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引

简言之就是使用合适的数据类型，选择合适的索引

1.选择合适的数据类型

• （1）使用可存下数据的最小的数据类型，整型 < date,time < char,varchar < blob
• （2）使用简单的数据类型，整型比字符处理开销更小，因为字符串的比较更复杂。如，int类型存储时间类型，bigint类型转ip函数
• （3）使用合理的字段属性长度，固定长度的表会更快。使用enum、char而不是varchar
• （4）尽可能使用not null定义字段
• （5）尽量少用text，非用不可最好分表

2.选择合适的索引列

• （1）查询频繁的列，在where，group by，order by，on从句中出现的列
• （2）where条件中<，<=，=，>，>=，between，in，以及like 字符串+通配符（%）出现的列
• （3）长度小的列，索引字段越小越好，因为数据库的存储单位是页，一页中能存下的数据越多越好
• （4）离散度大（不同的值多）的列，放在联合索引前面。查看离散度，通过统计不同的列值来实现，count越大，离散程度越高：

原开发人员已经跑路，该表早已建立，我无法修改，故：该措辞无法执行，放弃！

2.sql的编写需要注意优化

• 使用limit对查询结果的记录进行限定
• 避免select *，将需要查找的字段列出来
• 使用连接（join）来代替子查询
• 拆分大的delete或insert语句
• 可通过开启慢查询日志来找出较慢的SQL
• 不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边
• sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库
• OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内
• 不用函数和触发器，在应用程序实现
• 避免%xxx式查询
• 少用JOIN
• 使用同类型进行比较，比如用'123'和'123'比，123和123比
• 尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
• 对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5
• 列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大

原开发人员已经跑路，程序已经完成上线，我无法修改sql，故：该措辞无法执行，放弃！

引擎

引擎

目前广泛使用的是MyISAM和InnoDB两种引擎：

1. MyISAM

MyISAM引擎是MySQL 5.1及之前版本的默认引擎，它的特点是：

• 不支持行锁，读取时对需要读到的所有表加锁，写入时则对表加排它锁
• 不支持事务
• 不支持外键
• 不支持崩溃后的安全恢复
• 在表有读取查询的同时，支持往表中插入新纪录
• 支持BLOB和TEXT的前500个字符索引，支持全文索引
• 支持延迟更新索引，极大提升写入性能
• 对于不会进行修改的表，支持压缩表，极大减少磁盘空间占用

2. InnoDB

InnoDB在MySQL 5.5后成为默认索引，它的特点是：

• 支持行锁，采用MVCC来支持高并发
• 支持事务
• 支持外键
• 支持崩溃后的安全恢复
• 不支持全文索引

总体来讲，MyISAM适合SELECT密集型的表，而InnoDB适合INSERT和UPDATE密集型的表

MyISAM速度可能超快，占用存储空间也小，但是程序要求事务支持，故InnoDB是必须的，故该方案无法执行，放弃！

3.分区

MySQL在5.1版引入的分区是一种简单的水平拆分，用户需要在建表的时候加上分区参数，对应用是透明的无需修改代码

对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层表的对象封装，但对SQL层来说是一个完全封装底层的黑盒子。MySQL实现分区的方式也意味着索引也是按照分区的子表定义，没有全局索引

用户的SQL语句是需要针对分区表做优化，SQL条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区，可以通过EXPLAIN PARTITIONS来查看某条SQL语句会落在那些分区上，从而进行SQL优化，我测试，查询时不带分区条件的列，也会提高速度，故该措施值得一试。

分区的好处是：

• 可以让单表存储更多的数据
• 分区表的数据更容易维护，可以通过清楚整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作
• 部分查询能够从查询条件确定只落在少数分区上，速度会很快
• 分区表的数据还可以分布在不同的物理设备上，从而搞笑利用多个硬件设备
• 可以使用分区表赖避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争
• 可以备份和恢复单个分区

分区的限制和缺点：

• 一个表最多只能有1024个分区
• 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来
• 分区表无法使用外键约束
• NULL值会使分区过滤无效
• 所有分区必须使用相同的存储引擎

分区的类型：

• RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区
• LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择
• HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式
• KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值

具体关于mysql分区的概念请自行google或查询官方文档，我这里只是抛砖引玉了。

我首先根据月份把上网记录表RANGE分区了12份，查询效率提高6倍左右，效果不明显，故：换id为HASH分区，分了64个分区，查询速度提升显著。问题解决！
结果如下：PARTITION BY HASH (id)PARTITIONS 64

select count(*) from readroom_website; --11901336行记录

/* 受影响行数: 0 已找到记录: 1 警告: 0 持续时间 1 查询: 5.734 sec. */

select * from readroom_website where month(accesstime) =11 limit 10;

/* 受影响行数: 0 已找到记录: 10 警告: 0 持续时间 1 查询: 0.719 sec. */

4.分表

分表就是把一张大表，按照如上过程都优化了，还是查询卡死，那就把这个表分成多张表，把一次查询分成多次查询，然后把结果组合返回给用户。

分表分为垂直拆分和水平拆分，通常以某个字段做拆分项。比如以id字段拆分为100张表： 表名为 tableName_id%100

但：分表需要修改源程序代码，会给开发带来大量工作，极大的增加了开发成本，故：只适合在开发初期就考虑到了大量数据存在，做好了分表处理，不适合应用上线了再做修改，成本太高！！！而且选择这个方案，都不如选择我提供的第二第三个方案的成本低！故不建议采用。

5.分库

把一个数据库分成多个，建议做个读写分离就行了，真正的做分库也会带来大量的开发成本，得不偿失！不推荐使用。

方案二详细说明：升级数据库，换一个100%兼容mysql的数据库

mysql性能不行，那就换个。为保证源程序代码不修改，保证现有业务平稳迁移，故需要换一个100%兼容mysql的数据库。

1. 开源选择

• tiDB pingcap/tidb
• Cubrid Open Source Database With Enterprise Features

开源数据库会带来大量的运维成本且其工业品质和MySQL尚有差距，有很多坑要踩，如果你公司要求必须自建数据库，那么选择该类型产品。

2. 云数据选择

• 阿里云POLARDB

云数据库POLARDB_高吞吐在线事务处理_关系型云数据库_价格_购买 - 阿里云

官方介绍语：POLARDB 是阿里云自研的下一代关系型分布式云原生数据库，100%兼容MySQL，存储容量最高可达 100T，性能最高提升至 MySQL 的 6 倍。POLARDB 既融合了商业数据库稳定、可靠、高性能的特征，又具有开源数据库简单、可扩展、持续迭代的优势，而成本只需商用数据库的 1/10。

我开通测试了一下，支持免费mysql的数据迁移，无操作成本，性能提升在10倍左右，价格跟rds相差不多，是个很好的备选解决方案！

• 阿里云OcenanBase

淘宝使用的，扛得住双十一，性能卓著，但是在公测中，我无法尝试，但值得期待

• 阿里云HybridDB for MySQL (原PetaData)

云数据库HybridDB for MySQL_产品详情_阿里云

官方介绍：云数据库HybridDB for MySQL （原名PetaData）是同时支持海量数据在线事务（OLTP）和在线分析（OLAP）的HTAP（Hybrid Transaction/Analytical Processing）关系型数据库。

我也测试了一下，是一个olap和oltp兼容的解决方案，但是价格太高，每小时高达10块钱，用来做存储太浪费了，适合存储和分析一起用的业务。

• 腾讯云DCDB

分布式数据库 - 腾讯云

官方介绍：DCDB又名TDSQL，一种兼容MySQL协议和语法，支持自动水平拆分的高性能分布式数据库——即业务显示为完整的逻辑表，数据却均匀的拆分到多个分片中；每个分片默认采用主备架构，提供灾备、恢复、监控、不停机扩容等全套解决方案，适用于TB或PB级的海量数据场景。

腾讯的我不喜欢用，不多说。原因是出了问题找不到人，线上问题无法解决头疼！但是他价格便宜，适合超小公司，玩玩。

方案三详细说明：去掉mysql，换大数据引擎处理数据

数据量过亿了，没得选了，只能上大数据了。

1. 开源解决方案

hadoop家族。hbase/hive怼上就是了。但是有很高的运维成本，一般公司是玩不起的，没十万投入是不会有很好的产出的！

2.云解决方案

这个就比较多了，也是一种未来趋势，大数据由专业的公司提供专业的服务，小公司或个人购买服务，大数据就像水/电等公共设施一样，存在于社会的方方面面。

国内做的最好的当属阿里云。

我选择了阿里云的MaxCompute配合DataWorks，使用超级舒服，按量付费，成本极低。

MaxCompute可以理解为开源的Hive，提供sql/mapreduce/ai算法/python脚本/shell脚本等方式操作数据，数据以表格的形式展现，以分布式方式存储，采用定时任务和批处理的方式处理数据。DataWorks提供了一种工作流的方式管理你的数据处理任务和调度监控。

当然你也可以选择阿里云hbase等其他产品，我这里主要是离线处理，故选择MaxCompute，基本都是图形界面操作，大概写了300行sql，费用不超过100块钱就解决了数据处理问题。

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

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数据库服务器端核心参数详解和推荐配置

http://www.mysqlops.com/2011/10/26/mysql-variables-one.html

说3点

1. 设计合适的索引,基于主键的查找,上亿数据也是很快的;

2. 反范式化设计,以空间换时间,避免join,有些join操作可以在用代码实现,没必要用数据库来实现;

3. buffer,尽量让内存大于数据.

分享：MySQL数据库设计总结

作者针对MySQL数据库设计总结了19条规则，其中对于大表优化，他在规则7中提到：

大表可以考虑水平拆分。大表影响查询效率，根据业务特性有很多拆分方式，像根据时间递增的数据，可以根据时间来分；以id划分的数据，可以根据id%数据库个数的方式来拆分。

19条规则摘要如下：

规则1：一般情况可以选择MyISAM存储引擎，如果需要事务支持必须使用InnoDB存储引擎。

规则2：命名规则。

规则3：数据库字段类型定义

1. 经常需要计算和排序等消耗CPU的字段,应该尽量选择更为迅速的字段，如用TIMESTAMP(4个字节，最小值1970-01-01 00:00:00)代替Datetime（8个字节，最小值1001-01-01 00:00:00）,通过整型替代浮点型和字符型
2. 变长字段使用varchar，不要使用char
3. 对于二进制多媒体数据，流水队列数据(如日志)，超大文本数据不要放在数据库字段中

规则4：业务逻辑执行过程必须读到的表中必须要有初始的值。避免业务读出为负或无穷大的值导致程序失败

规则5：并不需要一定遵守范式理论，适度的冗余，让Query尽量减少Join

规则6：访问频率较低的大字段拆分出数据表。有些大字段占用空间多，访问频率较其他字段明显要少很多，这种情况进行拆分，频繁的查询中就不需要读取大字段，造成IO资源的浪费。

规则7：大表可以考虑水平拆分。大表影响查询效率，根据业务特性有很多拆分方式，像根据时间递增的数据，可以根据时间来分。以id划分的数据，可根据id%数据库个数的方式来拆分。

一.数据库索引

规则8：业务需要的相关索引是根据实际的设计所构造sql语句的where条件来确定的，业务不需要的不要建索引，不允许在联合索引（或主键）中存在多于的字段。特别是该字段根本不会在条件语句中出现。

规则9：唯一确定一条记录的一个字段或多个字段要建立主键或者唯一索引，不能唯一确定一条记录，为了提高查询效率建普通索引

规则10：业务使用的表，有些记录数很少，甚至只有一条记录，为了约束的需要，也要建立索引或者设置主键。

规则11：对于取值不能重复，经常作为查询条件的字段，应该建唯一索引(主键默认唯一索引)，并且将查询条件中该字段的条件置于第一个位置。没有必要再建立与该字段有关的联合索引。

规则12：对于经常查询的字段，其值不唯一，也应该考虑建立普通索引，查询语句中该字段条件置于第一个位置，对联合索引处理的方法同样。

规则13：业务通过不唯一索引访问数据时，需要考虑通过该索引值返回的记录稠密度，原则上可能的稠密度最大不能高于0.2，如果稠密度太大，则不合适建立索引了。

规则14：需要联合索引(或联合主键)的数据库要注意索引的顺序。SQL语句中的匹配条件也要跟索引的顺序保持一致。

注意：索引的顺势不正确也可能导致严重的后果。

规则15：表中的多个字段查询作为查询条件，不含有其他索引，并且字段联合值不重复，可以在这多个字段上建唯一的联合索引，假设索引字段为 (a1,a2,...an),则查询条件(a1 op val1,a2 op val2,...am op valm)m<=n,可以用到索引，查询条件中字段的位置与索引中的字段位置是一致的。

规则16：联合索引的建立原则(以下均假设在数据库表的字段a,b,c上建立联合索引(a,b,c))

规则17：重要业务访问数据表时。但不能通过索引访问数据时，应该确保顺序访问的记录数目是有限的，原则上不得多于10.

二.Query语句与应用系统优化

规则18：合理构造Query语句

规则19：应用系统的优化

各规则详细解读，欢迎阅读 MySQL数据库设计总结

实用预警：回答将以实际项目经验为例，为你解读优化的奥义！

首先采用Mysql存储千亿级的数据，确实是一项非常大的挑战。Mysql单表确实可以存储10亿级的数据，只是这个时候性能非常差，项目中大量的实验证明，Mysql单表容量在500万左右，性能处于最佳状态。

针对大表的优化，主要是通过数据库分库分表来解决，目前比较普遍的方案有三个：分区，分库分表，NoSql/NewSql。实际项目中，这三种方案是结合的，目前绝大部分系统的核心数据都是以RDBMS存储为主，NoSql/NewSql存储为辅。

分区

首先来了解一下分区方案。分区表是由多个相关的底层表实现的。这些底层表也是由句柄对象表示，所以我们也可以直接访问各个分区，存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引只是在各个底层表上各自加上一个相同的索引。这个方案对用户屏蔽了sharding的细节，即使查询条件没有sharding column，它也能正常工作（只是这时候性能一般）。不过它的缺点很明显：很多的资源都受到单机的限制，例如连接数，网络吞吐等。如何进行分区，在实际应用中是一个非常关键的要素之一。

下面开始举例：以客户信息为例，客户数据量5000万加，项目背景要求保存客户的银行卡绑定关系，客户的证件绑定关系，以及客户绑定的业务信息。此业务背景下，该如何设计数据库呢。项目一期的时候，我们建立了一张客户业务绑定关系表，里面冗余了每一位客户绑定的业务信息。基本结构大致如下：

查询时，对银行卡做索引，业务编号做索引，证件号做索引。随着需求大增多，这张表的索引会达到10个以上。而且客户解约再签约，里面会保存两条数据，只是绑定的状态不同。假设我们有5千万的客户，5个业务类型，每位客户平均2张卡，那么这张表的数据量将会达到惊人的5亿，事实上我们系统用户量还没有过百万时就已经不行了。这样的设计绝对是不行的，无论是插入，还是查询，都会让系统崩溃。

mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的datadir来查看）， 一张表主要对应着三个文件，一个是frm存放表结构的，一个是myd存放表数据的，一个是myi存表索引的。这三个文件都非常的庞大，尤其是.myd文件，快5个G了。下面进行第一次分区优化，Mysql支持的分区方式有四种：

在我们的项目中，range分区和list分区没有使用场景，如果基于绑定编号做range或者list分区，绑定编号没有实际的业务含义，无法通过它进行查询，因此，我们就剩下 HASH 分区和 KEY 分区了，HASH分区仅支持int类型列的分区，且是其中的一列。 KEY 分区倒是可以支持多列，但也要求其中的一列必须是int类型；看我们的库表结构，发现没有哪一列是int类型的，如何做分区呢？增加一列，绑定时间列，将此列设置为int类型，然后按照绑定时间进行分区，将每一天绑定的用户分到同一个区里面去。

这次优化之后，我们的插入快了许多，但是查询依然很慢，为什么？

因为在做查询的时候，我们也只是根据银行卡或者证件号进行查询，并没有根据时间查询，相当于每次查询，mysql都会将所有的分区表查询一遍。进行第二次方案优化，既然 HASH 分区和 KEY分区要求其中的一列必须是int类型的，那么创造出一个int类型的列出来分区是否可以？分析发现，银行卡的那串数字有秘密。银行卡一般是16位到19位不等的数字串，我们取其中的某一位拿出来作为表分区是否可行呢，通过分析发现，在这串数字中，其中确实有一位是0到9随机生成的，我们基于银行卡号+随机位进行KEY分区，每次查询的时候，通过计算截取出这位随机位数字，再加上卡号，联合查询，达到了分区查询的目的，需要说明的是，分区后，建立的索引，也必须是分区列，否则Mysql还是会在所有的分区表中查询数据。

通过银行卡号查询绑定关系的问题解决了，那么证件号呢，如何通过证件号来查询绑定关系。前面已经讲过，做索引一定是要在分区健上进行，否则会引起全表扫描。我们再创建了一张新表，保存客户的证件号绑定关系，每位客户的证件号都是唯一的，新的证件号绑定关系表里，证件号作为了主键，那么如何来计算这个分区健呢，客户的证件信息比较庞杂，有身份证号，港澳台通行证，机动车驾驶证等等，如何在无序的证件号里找到分区健。为了解决这个问题，我们将证件号绑定关系表一分为二，其中的一张表专用于保存身份证类型的证件号，另一张表则保存其他证件类型的证件号，在身份证类型的证件绑定关系表中，我们将身份证号中的月数拆分出来作为了分区健，将同一个月出生的客户证件号保存在同一个区，这样分成了12个区，其他证件类型的证件号，数据量不超过10万，就没有必要进行分区了。这样每次查询时，首先通过证件类型确定要去查询哪张表，再计算分区健进行查询。作了分区设计之后，保存2000万用户数据时银行卡表的数据保存文件就分成了10个小文件，证件表的数据保存文件分成了12个小文件，解决了这两个查询的问题，还剩下一个问题：业务编号怎么办？一个客户有多个签约业务，如何进行保存？这时候，采用分区的方案就不太合适了，它需要用到分表的方案。

分表

我们前面有提到过对于mysql，其数据文件是以文件形式存储在磁盘上的。当一个数据文件过大时，操作系统对大文件的操作就会比较麻烦耗时，且有的操作系统就不支持大文件，这个时候就必须分表了。另外对于mysql常用的存储引擎是Innodb，它的底层数据结构是B+树。当其数据文件过大的时候，查询一个节点可能会查询很多层次，而这必定会导致多次IO操作进行装载进内存，肯定会耗时的。除此之外还有Innodb对于B+树的锁机制。对每个节点进行加锁，那么当更改表结构的时候，这时候就会树进行加锁，当表文件大的时候，这可以认为是不可实现的。所以综上我们就必须进行分表与分库的操作。

如何进行分库分表，目前互联网上有许多的版本，比较知名的一些方案：阿里的TDDL，DRDS和cobar，京东金融的sharding-jdbc；民间组织的MyCAT；360的Atlas；美团的zebra；其他比如网易，58，京东等公司都有自研的中间件。

这么多的分库分表中间件方案归总起来，就两类：client模式和proxy模式。

无论是client模式，还是proxy模式。几个核心的步骤是一样的：SQL解析，重写，路由，执行，结果归并。个人比较倾向于采用client模式，它架构简单，性能损耗也比较小，运维成本低。

如何对业务类型进行分库分表。分库分表最重要的一步，即sharding column的选取，sharding column选择的好坏将直接决定整个分库分表方案最终是否成功。而sharding column的选取跟业务强相关。在我们的项目场景中，sharding column无疑最好的选择是业务编号。通过业务编号，将客户不同的绑定签约业务保存到不同的表里面去，根据业务编号路由到相应的表中进行查询，达到进一步优化sql的目的。

先读写分离、再垂直拆分、再水平拆分！

不纸上谈兵，说一下我的思路以及我的解决，抛砖引玉了

我最近正在解决这个问题

我现在的公司有三张表，是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把上下文给足，我可以帮你看一下

读：对MySQL最好的优化，就是不读MySQL。

不要笑。

是去读cache。

在cache为王的时代，如果所有请求都落到后端DB，什么架构都扛不住。

去读redis和mc吧。

写：别一个劲什么东西都直接扔给DB，扔到队列里，控制好速度去消费，就写不死。

备份：都千万级别了，如果被注入或者出现人为失误丢数据了，没有备份，估计就完蛋了。

亲身经历过，某大型app，单表366G，被注入，恢复了一天。如果那天没有备份，估计就得辞职白白了。

这个问题，我看很多回答都已经从很多方面概况性的论述了，整体看，说来说去其实基本也就这么些东西。但是如果想做好这里面的每一步则是不容易的，大家只是谈了个方案概念。我先简单谈一下自己的整体认识：

抛开具体的业务不谈，在纯技术的角度通常的解决方案：

第一：成本最低也是最实用的方式：索引优化、sql优化。

第二：上缓存，查询也不一定完全就是数据量大影响的，高访问量请求数据库密集时，影响也很大，用缓存挡在mysql前面，进行流量削锋。

第三：分库，把一个数据库，根据业务的耦合度、功能的划分，拆成若干个小的库，每个库只保留共同需要的数据就行了，比如用户表，这通常在每个库中都会有相同的一份。

第四：分表，尤其是水平切分。不过分表之后的弊端也是要考虑的，会导致有些业务对该数据的操作需求实现不了或者很麻烦。实际的做法是，分表的同时，仍然保留原表的数据，两份数据。一份是原表数据或者原表一部分的数据，尤其是主键，这个根据你们的业务需求来定。另一份是对原表进行切分的副本，用这个分开的表来满足某部分业务的查询需求即可。至于按什么样的方式分，看业务特征，比如说我做过一款手机游戏的app，在统计用户的月活跃情况时，我会按月份分。

第五：mysql读写分离，这是在一个库中又从读和写的层面上进行分流，一库，两台服务器，一台只负责读，另一台只负责写。其实本质也是一种负载均衡的实现方式

第六：分布式，把同一份数据分到不同服务器上，这个成本就大了，一般的公司用不到，较难解决的问题是在数据的一致性上。可以使用一些现成的中间件。

等等，不管使用什么技术，一定要考虑好这个技术可能带来的后果尤其弊端是什么。

回到最上面说的 ，要想做好这里面每一步则又是一个比较大的问题，比如索引、sql语句的优化，什么时候能用上索引，什么时候用不上，多列索引又是什么情况，一个多列索引很多时候并不是能完全用上所有列的索引，有时候只会用上第一列，那什么情况下能用上，什么情况下用不上，都需要对这些有详细的了解。

关于这一块，我在知乎上也答过一些问题，因为有教学从业经验，所以内容又能很容易看懂，获得了一些人的好评和认可。

mysql索引最左匹配原则的理解?608 赞同 · 63 评论回答

MySQL 查询 select * from table where id in (几百或几千个 id) 如何提高效率？160 赞同 · 31 评论回答

现在互联网应用的数据量越来越大，企业对数据库优化看的越来越重，同样也是面试必问项，答好了更是加分项。

所以最后，我就又写了一些东西，整理成了笔记，有需要的，可以按以下方式免费获取。

前言

在高并发系统当中，针对大表的分库分表是必不可少的技术手段之一，同时也是BAT等大厂面试时，经常考的热门考题。

你知道我们为什么要做分库分表吗？

这个问题要从两条线说起：垂直方向 和 水平方向。

1 垂直方向

垂直方向主要针对的是业务，下面聊聊业务的发展跟分库分表有什么关系。

1.1 单库

在系统初期，业务功能相对来说比较简单，系统模块较少。

为了快速满足迭代需求，减少一些不必要的依赖。更重要的是减少系统的复杂度，保证开发速度，我们通常会使用单库来保存数据。

系统初期的数据库架构如下：

此时，使用的数据库方案是：一个数据库包含多张业务表。用户读数据请求和写数据请求，都是操作的同一个数据库。

1.2 分表

系统上线之后，随着业务的发展，不断的添加新功能。导致单表中的字段越来越多，开始变得有点不太好维护了。

一个用户表就包含了几十甚至上百个字段，管理起来有点混乱。

这时候该怎么办呢？

答：分表。

将用户表拆分为：用户基本信息表 和 用户扩展表。

用户基本信息表中存的是用户最主要的信息，比如：用户名、密码、别名、手机号、邮箱、年龄、性别等核心数据。

这些信息跟用户息息相关，查询的频次非常高。

而用户扩展表中存的是用户的扩展信息，比如：所属单位、户口所在地、所在城市等等，非核心数据。

这些信息只有在特定的业务场景才需要查询，而绝大数业务场景是不需要的。

所以通过分表把核心数据和非核心数据分开，让表的结构更清晰，职责更单一，更便于维护。

除了按实际业务分表之外，我们还有一个常用的分表原则是：把调用频次高的放在一张表，调用频次低的放在另一张表。

有个非常经典的例子就是：订单表和订单详情表。

最近无意间获得一份BAT大厂大佬写的刷题笔记，一下子打通了我的任督二脉，越来越觉得算法没有想象中那么难了。

[BAT大佬写的刷题笔记，让我offer拿到手软](这位BAT大佬写的Leetcode刷题笔记，让我offer拿到手软)

1.3 分库

不知不觉，系统已经上线了一年多的时间了。经历了N个迭代的需求开发，功能已经非常完善。

系统功能完善，意味着系统各种关联关系，错综复杂。

此时，如果不赶快梳理业务逻辑，后面会带来很多隐藏问题，会把自己坑死。

这就需要按业务功能，划分不同领域了。把相同领域的表放到同一个数据库，不同领域的表，放在另外的数据库。

具体拆分过程如下：

将用户、产品、物流、订单相关的表，从原来一个数据库中，拆分成单独的用户库、产品库、物流库和订单库，一共四个数据库。

在这里为了看起来更直观，每个库我只画了一张表，实际场景可能有多张表。

这样按领域拆分之后，每个领域只用关注自己相关的表，职责更单一了，一下子变得更好维护了。

1.4 分库分表

有时候按业务，只分库，或者只分表是不够的。比如：有些财务系统，需要按月份和年份汇总，所有用户的资金。

这就需要做：分库分表了。

每年都有个单独的数据库，每个数据库中，都有12张表，每张表存储一个月的用户资金数据。

这样分库分表之后，就能非常高效的查询出某个用户每个月，或者每年的资金了。

此外，还有些比较特殊的需求，比如需要按照地域分库，比如：华中、华北、华南等区，每个区都有一个单独的数据库。

甚至有些游戏平台，按接入的游戏厂商来做分库分表。

2 水平方向

水分方向主要针对的是数据，下面聊聊数据跟分库分表又有什么关系。

2.1 单库

在系统初期，由于用户非常少，所以系统并发量很小。并且存在表中的数据量也非常少。

这时的数据库架构如下：

此时，使用的数据库方案同样是：一个master数据库包含多张业务表。

用户读数据请求和写数据请求，都是操作的同一个数据库，该方案比较适合于并发量很低的业务场景。

2.2 主从读写分离

系统上线一段时间后，用户数量增加了。

此时，你会发现用户的请求当中，读数据的请求占据了大部分，真正写数据的请求占比很少。

众所周知，数据库连接是有限的，它是非常宝贵的资源。而每次数据库的读或写请求，都需要占用至少一个数据库连接。

如果写数据请求需要的数据库连接，被读数据请求占用完了，不就写不了数据了？

这样问题就严重了。

为了解决该问题，我们需要把读库和写库分开。

于是，就出现了主从读写分离架构：

考虑刚开始用户量还没那么大，选择的是一主一从的架构，也就是常说的一个master一个slave。

所有的写数据请求，都指向主库。一旦主库写完数据之后，立马异步同步给从库。这样所有的读数据请求，就能及时从从库中获取到数据了（除非网络有延迟）。

读写分离方案可以解决上面提到的单节点问题，相对于单库的方案，能够更好的保证系统的稳定性。

因为如果主库挂了，可以升级从库为主库，将所有读写请求都指向新主库，系统又能正常运行了。

读写分离方案其实也是分库的一种，它相对于为数据做了备份，它已经成为了系统初期的首先方案。

但这里有个问题就是：如果用户量确实有些大，如果master挂了，升级slave为master，将所有读写请求都指向新master。

但此时，如果这个新master根本扛不住所有的读写请求，该怎么办？

这就需要一主多从的架构了：

上图中我列的是一主两从，如果master挂了，可以选择从库1或从库2中的一个，升级为新master。假如我们在这里升级从库1为新master，则原来的从库2就变成了新master的的slave了。

调整之后的架构图如下：

这样就能解决上面的问题了。

除此之外，如果查询请求量再增大，我们还可以将架构升级为一主三从、一主四从...一主N从等。

2.3 分库

上面的读写分离方案确实可以解决读请求大于写请求时，导致master节点扛不住的问题。但如果某个领域，比如：用户库。如果注册用户的请求量非常大，即写请求本身的请求量就很大，一个master库根本无法承受住这么大的压力。

这时该怎么办呢？

答：建立多个用户库。

用户库的拆分过程如下：

在这里我将用户库拆分成了三个库（真实场景不一定是这样的），每个库的表结构是一模一样的，只有存储的数据不一样。

2.4 分表

用户请求量上来了，带来的势必是数据量的成本上升。即使做了分库，但有可能单个库，比如：用户库，出现了5000万的数据。

根据经验值，单表的数据量应该尽量控制在1000万以内，性能是最佳的。如果有几千万级的数据量，用单表来存，性能会变得很差。

如果数据量太大了，需要建立的索引也会很大，从小到大检索一次数据，会非常耗时，而且非常消耗cpu资源。

这时该怎么办呢？

答：分表，这样可以控制每张表的数据量，和索引大小。

表拆分过程如下：

我在这里将用户库中的用户表，拆分成了四张表（真实场景不一定是这样的），每张表的表结构是一模一样的，只是存储的数据不一样。

如果以后用户数据量越来越大，只需再多分几张用户表即可。

2.5 分库分表

当系统发展到一定的阶段，用户并发量大，而且需要存储的数据量也很多。这时该怎么办呢？

答：需要做分库分表。

如下图所示：

图中将用户库拆分成了三个库，每个库都包含了四张用户表。

如果有用户请求过来的时候，先根据用户id路由到其中一个用户库，然后再定位到某张表。

路由的算法挺多的：

• 根据id取模，比如：id=7，有4张表，则7%4=3，模为3，路由到用户表3。
• 给id指定一个区间范围，比如：id的值是0-10万，则数据存在用户表0，id的值是10-20万，则数据存在用户表1。
• 一致性hash算法

这篇文章就不过多介绍了，后面会有文章专门介绍这些路由算法的。

3 真实案例

接下来，废话不多说，给大家分享三个我参与过的分库分表项目经历，给有需要的朋友一个参考。

3.1 分库

我之前待过一家公司，我们团队是做游戏运营的，我们公司提供平台，游戏厂商接入我们平台，推广他们的游戏。

游戏玩家通过我们平台登录，成功之后跳转到游戏厂商的指定游戏页面，该玩家就能正常玩游戏了，还可以充值游戏币。

这就需要建立我们的账号体系和游戏厂商的账号的映射关系，游戏玩家通过登录我们平台的游戏账号，成功之后转换成游戏厂商自己平台的账号。

这里有两个问题：

1. 每个游戏厂商的接入方式可能都不一样，账号体系映射关系也有差异。
2. 用户都从我们平台登录，成功之后跳转到游戏厂商的游戏页面。当时有N个游戏厂商接入了，活跃的游戏玩家比较多，登录接口的并发量不容小觑。

为了解决这两个问题，我们当时采用的方案是：分库。即针对每一个游戏都单独建一个数据库，数据库中的表结构允许存在差异。

我们当时没有进一步分表，是因为当时考虑每种游戏的用户量，还没到大到离谱的地步。不像王者荣耀这种现象级的游戏，有上亿的玩家。

其中有个比较关键的地方是：登录接口中需要传入游戏id字段，通过该字段，系统就知道要操作哪个库，因为库名中就包含了游戏id的信息。

3.2 分表

还是在那家游戏平台公司，我们还有另外一个业务就是：金钻会员。

说白了就是打造了一套跟游戏相关的会员体系，为了保持用户的活跃度，开通会员有很多福利，比如：送游戏币、充值有折扣、积分兑换、抽奖、专属客服等等。

在这套会员体系当中，有个非常重要的功能就是：积分。

用户有很多种途径可以获取积分，比如：签到、充值、玩游戏、抽奖、推广、参加活动等等。

积分用什么用途呢？

1. 退换实物礼物
2. 兑换游戏币
3. 抽奖

说了这么多，其实就是想说，一个用户一天当中，获取积分或消费积分都可能有很多次，那么，一个用户一天就可能会产生几十条记录。

如果用户多了的话，积分相关的数据量其实挺惊人的。

我们当时考虑了，水平方向的数据量可能会很大，但是用户并发量并不大，不像登录接口那样。

所以采用的方案是：分表。

当时使用一个积分数据库就够了，但是分了128张表。然后根据用户id，进行hash除以128取模。

最近无意间获得一份BAT大厂大佬写的刷题笔记，一下子打通了我的任督二脉，越来越觉得算法没有想象中那么难了。

[BAT大佬写的刷题笔记，让我offer拿到手软](这位BAT大佬写的Leetcode刷题笔记，让我offer拿到手软)

需要特别注意的是，分表的数量最好是2的幂次方，方便以后扩容。

3.3 分库分表

后来我去了一家从事餐饮软件开发的公司。这个公司有个特点是在每天的中午和晚上的就餐高峰期，用户的并发量很大。

用户吃饭前需要通过我们系统点餐，然后下单，然后结账。当时点餐和下单的并发量挺大的。

餐厅可能会有很多人，每个人都可能下多个订单。这样就会导致用户的并发量高，并且数据量也很大。

所以，综合考虑了一下，当时我们采用的技术方案是：分库分表。

经过调研之后，觉得使用了当当网开源的基于jdbc的中间件框架：sharding-jdbc。

当时分了4个库，每个库有32张表。

4 总结

上面主要从：垂直和水平，两个方向介绍了我们的系统为什么要分库分表。

说实话垂直方向（即业务方向）更简单。

在水平方向（即数据方向）上，分库和分表的作用，其实是有区别的，不能混为一谈。

• 分库：是为了解决数据库连接资源不足问题，和磁盘IO的性能瓶颈问题。
• 分表：是为了解决单表数据量太大，sql语句查询数据时，即使走了索引也非常耗时问题。此外还可以解决消耗cpu资源问题。
• 分库分表：可以解决 数据库连接资源不足、磁盘IO的性能瓶颈、检索数据耗时 和 消耗cpu资源等问题。

如果在有些业务场景中，用户并发量很大，但是需要保存的数据量很少，这时可以只分库，不分表。

如果在有些业务场景中，用户并发量不大，但是需要保存的数量很多，这时可以只分表，不分库。

如果在有些业务场景中，用户并发量大，并且需要保存的数量也很多时，可以分库分表。

好了，今天的内容就先到这里。

是不是有点意犹未尽？

没关系，其实分库分表相关内容挺多的，本文作为分库分表系列的第一弹，作为一个开胃小菜吧，分享给大家。

在文章末尾顺便提几个问题：

1. 分库分表的具体实现方案有哪些？
2. 分库分表后如何平滑扩容？
3. 分库分表后带来了哪些问题？
4. 如何在项目中实现分库分表功能？

欢迎关注，敬请期待我的下一篇文章。

最近无意间获得一份BAT大厂大佬写的刷题笔记，一下子打通了我的任督二脉，越来越觉得算法没有想象中那么难了。

[BAT大佬写的刷题笔记，让我offer拿到手软](这位BAT大佬写的Leetcode刷题笔记，让我offer拿到手软)

最后说一句(求关注，别白嫖我)

如果这篇文章对您有所帮助，或者有所启发的话，帮忙扫描下发二维码关注一下，您的支持是我坚持写作最大的动力。

求一键三连：点赞、转发、在看。

最近无意间获得一份阿里大佬写的刷题笔记，一下子打通了我的任督二脉，进大厂原来没那么难。

链接：https://pan.baidu.com/s/1UECE5yuaoTTRpJfi5LU5TQ 密码：bhbe

不会有人刷到这里还想白嫖吧？点赞对我真的非常重要！在线求赞。加个关注我会非常感激！

1. 今天看到这个问题，看到了优秀的回答已经不少了，我再补充几句， 对于千万级别的大表，如果是mysql数据库 ，确实表数量已经算大了，mysql 的合理上限不应该超过500万。达到千万级，起码要进行历史归档，否则会严重拖累数据库性能。虽然有很多answers说千万级是小case,但是如果这样的表再不进行优化，结果会不堪设想。
2. 再来说一下 我的优化方案，依我看任何偏离业务场景的优化都是耍流氓，如果是订单表，主要通过订单id来查询订单信息，则可以对这样的表 进行垂直分库，每个库表容量500万条，按订单号维度 给拆分到多个库，而在查询的时候，使用订单号查询，通过某个业务规则，直接定位到要查询的目标库。或者通过用户ID 、日期维度 进行分库，但是千万要注意，查询时携带 分库的条件。 如果是CRM系统 ，不直接使用订单号直接查询，而是一个范围查询，返回一个列表集合，而你还继续执着于分库分表就能解决你的性能问题，这样你要对各个库的查询结果集进行union，数据库的性能非但不能提高反而会适得其反！
3. 表设计，看字段设计是否合理，是否符合数据库的设计规范，这个我就不细说了。
4. SQL优化，慢SQL监控，检查是否有大量的的子查询和关联查询 嵌套查询等，尽量避免使用这些查询。可以结合redis，memcache等缓存服务，把这些复杂的sql进行拆分，充分利用二级缓存，减少数据库IO操作。对数据库连接池，mybatis，hiberante二级缓存充分利用上。尽量使用顺序IO代替随机IO。合理使用索引，尽量避免全表扫描。
5. 索引优化，减少无效索引，提高索引的使用效率，众所周知的是，索引的维护成本通常是表维护成本的几倍，所以一定要避免建立无效的索引。建立的索引就要充分利用上。
6. 有人说 水平分表，这个我还是建议 三思，搞不好非但不能提升性能反而多了很多的join和磁盘IO，开发起来也麻烦，有很多的业务就是要求一次查询大部分的字段 看你业务场景了。

抛砖引玉，有问题大家一起讨论吧

前段时间刚入职一家公司，就遇上这事！

背景

XX实例（一主一从）xxx告警中每天凌晨在报SLA报警，该报警的意思是存在一定的主从延迟（若在此时发生主从切换，需要长时间才可以完成切换，要追延迟来保证主从数据的一致性）

XX实例的慢查询数量最多（执行时间超过1s的sql会被记录），XX应用那方每天晚上在做删除一个月前数据的任务

分析

使用pt-query-digest工具分析最近一周的mysql-slow.log

pt-query-digest --since=148h mysql-slow.log | less

结果第一部分

就不一一复制上来了，更多优化过程请查看：

千万级，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的高效运行才是最正确的选择。

背景

目前做电商后台开发，经历了数据从零到百万到百亿级别的增长。在这个增长的过程中，我们做了很多的优化。
这里分享一下我们的优化经验，仅供参考。　

优化策略：
不要过度优化，优化的过程一定是跟随着业务量的增长来慢慢调整优化策略的，比如说，没人就在一开始数据为0的时候就开始畅想10亿的数据的优化方案。
所以一般是业务量的增长在某个方向达到了性能瓶颈或者将要达到性能瓶颈，我们针对特殊的点做优化。
同时，不同的业务场景，优化的方案也不尽相同，比如读频繁的场景、写频繁的场景、分页查询场景、热点数据场景。

优化

零、建表

最开始我们针对一个单一业务只有一张表，比如用户表，存了用户的所有信息。一开始数据量少，并没有什么性能压力。

一、优化SQL语句和索引

比如是否有回表，索引是否可以优化来减少回表，查询条件是否可以修改来命中索引。

二、主从复制/读写分离

其实很多时候主从复制在一开始设计的时候就应该支持了。毕竟很多的ORM库本身也是支持的，并没有很多难度。

三、缓存

当访问量（QPS）增大，MySQL已经有了性能的压力，就该考虑加缓存了。现行的比较常见的缓存策略就是Cache Aside Pattern，也就是如果有更新，让缓存失效（删除）。
为何不更新缓存而是删除缓存呢，主要是为了防止并发的情况下可能缓存不一致的问题。所以现在大多数缓存方案都是删除的方案。它能保证数据的最终一致性。
我们的做法是监听MySQL的binlog，某一行数据有修改，删除对应的缓存，比较常用的就是Memcached，我们也有地方会用到Redis，比如需要分页查询的时候，使用Redis的ZSET方便支持。

四、垂直切分

加了缓存之后发现缓存命中率低，为啥呢，因为我们的表里面有几列数据是常更新的数据。比如：登入时间，登出时间。当他们变化的时候会删除缓存，导致重新读DB。
这个时候需要一定的策略来增大缓存的命中率，我们使用垂直切分，把易变的数据和不易变的数据且分开来。这样大多数数据不易变的，就能充分利用缓冲的优势，易变的数据可以单独优化。

五、水平切分，分库分表

关键业务就水平切分，做sharding，分库。
非关键业务直接上TiDB吧。数据量增加到亿级别之后，我们很多业务直接切换到TiDB了。TiDB 是一个兼容MySQL 协议和语法的分布式数据库，支撑百亿数据没有问题，只是在生产环境还不太稳定，有时候会出现无法命中索引需要加force index，还有由于它的二段提交机制，经常会有一些write conflict之类的。
而对于一些关键业务，我们不放心使用TiDB，开始对MySQL进行水平切分。

六、热点数据本地缓存

取决于你的数据是否有热点数据，比如秒杀活动，双十一大促的活动，很多热点店铺，热点商品等等。这种热点数据，即时使用了Memcached/Redis缓存，还是可能导致缓存的单节点性能瓶颈，这个时候就需要使用内存本地缓存，我们使用的LRU缓存。

七、水平切分（写）

对于易变的数据，QPS增大很容易触发性能瓶颈，这个时候需要水平切分来降低单个DB/表的写入QPS，来降低MySQL的资源占用。

八、消息队列（写）

异步写入，进行流量削峰，降低峰值的压力。同时降低线上服务的压力。

九、批量更新（写）

我们的更新QPS一直在增涨，即使分库分表之后，每个库的update QPS也非常高，最后我们使用批量更新的方式，把数据先更新到缓存，然后批量取固定量的数据一起更新DB。

关于批量更新：
我们由于更新的QPS过大，导致了MySQL的CPU/IO/Network都非常大。我们就是用了一种批量更新的方式，也就是MySQL的UPDATE SET CASE WHEN语法。

1. MQ或者API写入缓存，并把主键写入到Redis ZSET，主要是为了批量的取数据。我们之前已经水平切分到了1000个table，所以这里有1000个Redis ZSET。
2. 设计一个定时器的服务，定时从ZSET里面POP 20条数据，根据主键从实际的缓存里面拿具体的value数据。
3. 组装MySQL批量更新语法一次性更新。

Note：我们定时器的服务可以支持分布式任务调度，我们利用了Kafka的partition机制，每个服务都消费同一个kafka的同一个group，这样kafka会给每个服务分配互斥的partition列表。我们通过实现partition->任务分片的映射算法，最终增加或者减少实例数量的时候，通过监听的kafka的partition的变化，来决定处理那些任务分片，从而实现了无中心服务器的任务调度。

我们这种方式，使MySQL的IO/Network/CPU都降低了80%以上。这也是我们更新的终极方案了。

总结

上文只是我们优化经历的一些阶段，不同数据不同分析，比如你的查询主要是多行查询而非单行查询，那优化方式就不一样了。
像我们有服务是多行数据查询的，我们就使用Redis的List来实现了缓存，由于是更新缓存，担心不一致，我们同时监听了MySQL的binlog来校验缓存和数据库的一致性。

同时读频繁场景和写频繁场景，优化方案也不一样。

所以说，MySQL的优化，不是一个通用的方案，只能具体业务具体分析。但是大概方案就是我上面列出来的那些。希望对你的优化有启发。

<全文完>

欢迎关注我的微信公众号：码农在新加坡，有更多好的技术分享。

答主答得非常好了，但是不太赞同答主给出的顺序。

一个合格的技术人，应该能做出适用于未来的产品。引入分布式会带来很多麻烦，但它会让你走得更远。这不正是技术人的价值所在吗？

看到有人回复说有钱就上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: 垂直切分一般都要做，只不过业务粒度大小而已。

分库有是经常用的，就算当前压力小，也尽量分出几个逻辑库出来。等规模上去了，很方便就迁移扩展。

水平拆分有一定难度，但如果将来一定会到这个规模，又可能用到，建议越早做越好。因为对应用的改动较大，而且迁移成本高。

综上，数据库设计要面向现代化，面向世界，面向未来。。。

1. 千万只是小case而已，千万对程序员来说好大啊， 人家一天几千万的，那DBA不是不要活了？

现在5T,10T 的单个数据库都很多。程序猿能兼职干了吗？？

2. 你需要一个专业的DBA，专业的人干专业的事，DBA手里有自己的武器

3.程序员来操DBA的心，不是傻，就是楞，在不就是逗比。

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

都千万数据了，居然没有具体细节，提问者肯定是新手。

不BB，直接上干货！！！跟数据打交道的第五年，目前也是大厂的数据负责人，遇过各种关于数据的问题，也因此阅读了不少相关书籍。

这些书单我都有收集了电子版本，并且整理到网盘上。上面也有我自己总结的读书笔记和思维导图，大家可以点击下方链接进行下载。

计算机必备书籍(持续更新，附下载地址)​mp.weixin.qq.com/s?__biz=Mzg3NjI4OTg1Mw==&mid=100016617&idx=1&sn=1113864c265dcfc9ab496682c0d4cdd5&chksm=4f360bbb784182ada8c0099d89165166548ae8754af5c47ab3006b19f497a8b9a1d3c480e566#rd​mp.weixin.qq.com/s?__biz=Mzg3NjI4OTg1Mw==&mid=100016617&idx=1&sn=1113864c265dcfc9ab496682c0d4cdd5&chksm=4f360bbb784182ada8c0099d89165166548ae8754af5c47ab3006b19f497a8b9a1d3c480e566#rd

一、数据库瓶颈

不管是IO瓶颈，还是CPU瓶颈，最终都会导致数据库的活跃连接数增加，进而逼近甚至达到数据库可承载活跃连接数的阈值。在业务Service来看就是，可用数据库连接少甚至无连接可用。接下来就可以想象了吧（并发量、吞吐量、崩溃）。

1、IO瓶颈

第一种：磁盘读IO瓶颈，热点数据太多，数据库缓存放不下，每次查询时会产生大量的IO，降低查询速度 -> 分库和垂直分表。第二种：网络IO瓶颈，请求的数据太多，网络带宽不够 -> 分库。

2、CPU瓶颈

第一种：SQL问题，如SQL中包含join，group by，order by，非索引字段条件查询等，增加CPU运算的操作 -> SQL优化，建立合适的索引，在业务Service层进行业务计算。第二种：单表数据量太大，查询时扫描的行太多，SQL效率低，CPU率先出现瓶颈 -> 水平分表。

二、分库分表

1、水平分库

概念：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。结果：

• 每个库的结构都一样；
• 每个库的数据都不一样，没有交集；
• 所有库的并集是全量数据；

场景：系统绝对并发量上来了，分表难以根本上解决问题，并且还没有明显的业务归属来垂直分库。分析：库多了，io和cpu的压力自然可以成倍缓解。

2、水平分表

概念：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。

结果：

• 每个表的结构都一样；
• 每个表的数据都不一样，没有交集；
• 所有表的并集是全量数据；

场景：系统绝对并发量并没有上来，只是单表的数据量太多，影响了SQL效率，加重了CPU负担，以至于成为瓶颈。推荐：一次SQL查询优化原理分析分析：表的数据量少了，单次SQL执行效率高，自然减轻了CPU的负担。

3、垂直分库

概念：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。结果：

• 每个库的结构都不一样；
• 每个库的数据也不一样，没有交集；
• 所有库的并集是全量数据；

场景：系统绝对并发量上来了，并且可以抽象出单独的业务模块。分析：到这一步，基本上就可以服务化了。例如，随着业务的发展一些公用的配置表、字典表等越来越多，这时可以将这些表拆到单独的库中，甚至可以服务化。再有，随着业务的发展孵化出了一套业务模式，这时可以将相关的表拆到单独的库中，甚至可以服务化。

4、垂直分表

概念：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。

结果：

• 每个表的结构都不一样；
• 每个表的数据也不一样，一般来说，每个表的字段至少有一列交集，一般是主键，用于关联数据；
• 所有表的并集是全量数据；

场景：系统绝对并发量并没有上来，表的记录并不多，但是字段多，并且热点数据和非热点数据在一起，单行数据所需的存储空间较大。以至于数据库缓存的数据行减少，查询时会去读磁盘数据产生大量的随机读IO，产生IO瓶颈。分析：可以用列表页和详情页来帮助理解。垂直分表的拆分原则是将热点数据（可能会冗余经常一起查询的数据）放在一起作为主表，非热点数据放在一起作为扩展表。这样更多的热点数据就能被缓存下来，进而减少了随机读IO。拆了之后，要想获得全部数据就需要关联两个表来取数据。但记住，千万别用join，因为join不仅会增加CPU负担并且会讲两个表耦合在一起（必须在一个数据库实例上）。关联数据，应该在业务Service层做文章，分别获取主表和扩展表数据然后用关联字段关联得到全部数据。

三、分库分表工具

• sharding-sphere：jar，前身是sharding-jdbc；
• TDDL：jar，Taobao Distribute Data Layer；
• Mycat：中间件。

注：工具的利弊，请自行调研，官网和社区优先。

四、分库分表步骤

根据容量（当前容量和增长量）评估分库或分表个数 -> 选key（均匀）-> 分表规则（hash或range等）-> 执行（一般双写）-> 扩容问题（尽量减少数据的移动）。五、分库分表问题

1、非partition key的查询问题

基于水平分库分表，拆分策略为常用的hash法。端上除了partition key只有一个非partition key作为条件查询映射法

基因法

注：写入时，基因法生成user_id，如图。关于xbit基因，例如要分8张表，23=8，故x取3，即3bit基因。根据user_id查询时可直接取模路由到对应的分库或分表。
根据user_name查询时，先通过user_name_code生成函数生成user_name_code再对其取模路由到对应的分库或分表。id生成常用snowflake算法。

端上除了partition key不止一个非partition key作为条件查询映射法

冗余法

注：按照order_id或buyer_id查询时路由到db_o_buyer库中，按照seller_id查询时路由到db_o_seller库中。感觉有点本末倒置！有其他好的办法吗？改变技术栈呢？

后台除了partition key还有各种非partition key组合条件查询NoSQL法

冗余法

2、非partition key跨库跨表分页查询问题

基于水平分库分表，拆分策略为常用的hash法。

注：用NoSQL法解决（ES等）。

3、扩容问题

基于水平分库分表，拆分策略为常用的hash法。水平扩容库（升级从库法）

注：扩容是成倍的。

水平扩容表（双写迁移法）

• 第一步：（同步双写）修改应用配置和代码，加上双写，部署；
• 第二步：（同步双写）将老库中的老数据复制到新库中；
• 第三步：（同步双写）以老库为准校对新库中的老数据；
• 第四步：（同步双写）修改应用配置和代码，去掉双写，部署；

注：双写是通用方案。

六、分库分表总结

• 分库分表，首先得知道瓶颈在哪里，然后才能合理地拆分（分库还是分表？水平还是垂直？分几个？）。且不可为了分库分表而拆分。
• 选key很重要，既要考虑到拆分均匀，也要考虑到非partition key的查询。
• 只要能满足需求，拆分规则越简单越好。