mysql数据库直接拷贝-steam游戏直接拷贝

从：

移动互联网时代，每天都会产生海量的用户数据。 基于用户使用数据等的分析，需要依赖数据统计和分析。 当数据量不大的时候，数据库的优化不是很重要。 一旦数据量增大，该值过大，系统响应就会变慢，TPS就会直线下降，直到服务不可用。

可能有人会问，为什么不用Oracle？ 确实，很多开发者在写代码的时候并不关心SQL的问题，所有的性能问题都交给DBA去负责SQL的优化。 然而，并不是每个项目都会有 DBA，也不是所有项目都会使用 Oracle 数据库。 而且，Oracle在数据库中数据量很大的情况下，要解决性能问题也不是一件容易的事。

那么，MySQL能否支持亿级的数据量呢？ 我的回答是肯定的。 大多数互联网公司都使用 MySQL 作为主要的数据存储解决方案。 国企、银行以Oracle为主，有专职DBA为您服务。

本文将以一个实际的项目应用为例，分析如何对数据库进行层层优化。 项目后台为企业级统一消息处理平台。 客户数据5000万+，消息流量1000万/分钟，日消息流量1亿左右。

虽然单张MySQL表可以存储10亿级别的数据，但是此时性能很差。 既然一张表处理不了，那就想办法把数据放到多个地方解决问题。 于是，数据库分库分表的方案应运而生。 目前常见的有分区、分库分表、NoSQL/NewSQL三种方案。

在实际项目中，往往是结合这三种方案来解决问题。 目前大部分系统的核心数据主要存储在RDBMS中，NoSQL/NewSQL存储为辅。

分割

分区方案

分区表是由多个相关的底层表实现的，这些底层表也是用句柄对象来表示的，所以我们也可以直接访问各个分区。 存储引擎对分区的底层表的管理方式与管理普通表的方式相同（所有底层表必须使用相同的存储引擎），分区表的索引只是为每个底层表添加相同的索引。 从存储引擎的角度来看，底层表与普通表没有区别，存储引擎不需要知道这是一张普通表也是分区表的一部分。 这个解决方案也不错。 它使用户免受分片细节的影响。 即使查询条件没有分片列，也能正常工作（但此时性能一般）。 但是它的缺点也很明显：很多资源都受限于单机，比如连接数、网络吞吐量等等。 如何分区是实际应用中的关键要素之一。 在我们的项目中，以客户信息为例，客户数据量在5000万以上。 项目后台需要保存客户银行卡绑定关系、客户证件绑定关系、客户绑定业务信息。 在这种业务背景下，如何设计数据库。 在项目的第一阶段，我们建立了一个客户业务绑定关系表，其中包含了每个客户绑定的冗余业务信息。 基本结构大致如下：

查询时以银行卡为索引，以商号为索引，以证件号为索引。 随着需求的增加，这个表的索引会达到10多个。而且，如果客户取消合同，再签订合同，里面会保存两条数据，只是绑定状态不同。 假设我们有5000万客户，5种业务类型，每个客户平均有2张卡，那么这张表的数据量将达到惊人的5亿。 其实当我们系统的用户数还没有超过一百万的时候就没有了。 MySQL数据库中的数据以文件的形式存储在磁盘上。 默认放在/mysql/data下（可以通过my.cnf中的datadir查看）。 一张表主要对应三个文件，一个是存放frm的表结构，一个是myd存放表数据，一个是myi存放表索引。 这三个文件都非常庞大，尤其是.myd文件，差不多有5G，下面进行第一次分区优化。 MySQL支持四种分区方式：

在我们的项目中，range分区和list分区没有使用场景。 如果范围或列表分区是基于绑定号，则绑定号没有实际业务意义，无法通过绑定号查询。 因此，我们只剩下HASH分区和KEY分区，而HASH分区只支持int类型列的分区，而且是列之一。 查看我们的库表结构，发现所有列都不是int类型的，如何分区？ 可以加一个列，绑定时间列，设置这个列为int类型，然后按照绑定时间分区，把每天绑定的用户划分到同一个区域。 这样优化之后，我们的插入速度快了很多，但是查询还是很慢。 为什么呢，因为我们在做查询的时候，只根据银行卡或者身份证号查询，没有根据时间查询，相当于每次查询，MySQL都会查询所有的分区表。

然后进行第二次方案优化。 由于hash分区和key分区要求其中一列必须是int类型，那么是否可以创建一个int类型的列表来进行分区呢？ 分析发现银行卡上的这串数字有秘密。 银行卡一般都是一串16到19位的数字。 我们把其中一个拿出来做表分区可行吗？ 通过分析，我们发现在这串数字中，其中一个确实是一个从0到9的随机数生成的，不同的卡串长度，这个位是不一样的，肯定不是最后一位，一般都是最后一位一个校验位，它不是随机的。 我们新设计的方案是根据银行卡号+随机数进行KEY分区。 在每次查询中，通过计算截取随机数字，将卡号与查询结合起来，达到分区查询的目的。 需要说明的是，分区后创建的索引也必须是分区列，否则MySQL还是会查询所有分区表中的数据。 那么通过银行卡号查询绑定关系的问题就解决了，那么身份证号呢，如何通过身份证号查询绑定关系。 前面说过，必须对partition key进行索引，否则会造成全表扫描。

我们新建一张表来保存客户的身份证号绑定关系。 每个客户的 ID 号都是唯一的。 新的身份证号绑定关系表中，以身份证号为主键，那么分区键如何计算呢？ 那么，客户的证件信息比较复杂，包括身份证号码、港澳台护照、机动车驾驶证等，如何在乱七八糟的证件号码中找到partition key。

为了解决这个问题，我们将证件号绑定关系表分为两张，一张专门存放身份证类型的证件号，另一张表用于存放其他证件类型的证件号。 在证件绑定关系表中，我们将身份证号中的月份数作为分区键进行拆分，将同一地区同月出生客户的身份证号进行存储，分为12个区域。 其他类型证件编号，数据量不超过10万条，无需分区。 这样，每次查询时，先通过证书类型确定要查询哪个表，然后再计算分区键进行查询。

分区设计后，在保存2000万用户数据时，将银行卡表的数据存储文件分成10个小文件，证书表的数据存储文件分为12个小文件，解决了两个查询问题，剩下的问题就是，商号呢？

分库分表

分库分表，目前网上有很多版本，比较知名的几种方案：

但是这么多的分库分表中间件解决方案可以归结为两大类：客户端模式和代理模式。

客户端模式

代理模式

不管是client模式还是proxy模式，几个核心步骤都是一样的：SQL解析、重写、路由、执行、结果合并。 个人更倾向于采用client模式，结构简单，性能损耗相对较小，运维成本低。 如果在项目中引入mycat或者cobar，它们的单机模式无法保证可靠性，一旦宕机，服务将不可用，必须引入HAProxy来实现其高可用的集群部署方案。 为了解决Usable高的问题，还需要使用Keepalived来实现。

我们在项目中放弃了这种方案，采用了shardingjdbc的方式。 回到刚才的业务问题，业务类型如何划分为数据库和表。 分库分表的第一步，也是最重要的一步，就是分库列的选择。 分库列的选择将直接决定整个分库方案的成败。

sharding column的选择与业务强相关。 在我们的项目场景中，sharding column的最佳选择无疑是业务号。 通过业务编号，将客户不同的绑定、签约业务存储在不同的表中。 查询时根据业务号路由到对应的表进行查询，从而达到进一步优化SQL的目的。

前面我们讲了基于客户合约绑定业务场景的数据库优化，接下来讲一下海量数据的存储方案。

垂直分库

对于每分钟近千万条流和每天近亿条流的处理，如何高效的编写和查询是一个比较大的挑战。 还是老办法，分库分表分区，读写分离，不过这次是先分表，再分库，最后分区。

我们将消息流按照不同的业务类型分表，同一业务的消息流进入同一张表。 分表之后，再分库。 我们将与管道相关的数据单独存储在一个库中。 这些数据对写入的要求高，对查询和更新的要求低，以区别于那些经常更新的数据。 数据库分好后，再分区。

这是基于业务垂直度的分库操作。 垂直分库是将关联度低的不同表按照业务耦合度存放在不同的数据库中，以达到系统资源的饱和利用。 这样的分库方案结合应用微服务治理，每个微服务系统使用独立的数据库。 不同模块的数据存储在分库中，不能进行模块间查询。 如果有，则必须通过数据冗余或通过应用代码进行二次处理来解决。

如果无法消除跨库关联查询，则应将小表的数据冗余转移到数据量大的大库中。 如果，流表中的查询需要关联获取频道信息，而频道信息在基础管理库中，那么要么在查询时，代码查询两次基础管理库中的频道信息表，要么渠道信息表冗余到大流水表中。

分离出每天过亿的流量数据后，流量库中单表的数据量还是太大了。 我们继续对单流表进行分区，按照一定的业务规则（通常是查询索引列）对单表进行分区，一张表编程N张表，当然这些变化是应用层无法感知的。

分区表的设置一般以查询索引列为分区。 比如流表A，查询需要根据手机号和批号进行查询，所以我们在创建分区的时候，选择使用手机号和批号进行分区，这样设置之后， query会去索引，每次查询mysql都会根据查询条件进行计算，数据会落在那个分区，可以直接在对应的分区表中检索，避免了全表扫描。

每天过亿的数据，当然要做历史表的数据迁移工作。 客户要求流量数据需要保存半年，部分关键流量数据需要保存一年。 删除数据是不可能的，我也跑不掉，虽然我有删除数据跑掉的冲动。 事实上，立即删除数据是不可能的。 delete性能差的会先淘汰，truncate也快不了多少。 我们采用了更巧妙的方法。 具体步骤如下：

创建一个和原表一模一样的临时表 1 create table test_a_serial_1 like test_a_serial;

将原表命名为临时表 2 alter table test_a_serial rename test_a_serial_{date};

将临时表1更改为原表 alter table able test_a_serial_1 rename able test_a_serial; 此时，当天的流量表是一个新的空表，继续保存当天的流量，而临时表2保存昨天的数据和部分今天的数据，临时表2中的日期时间到name是计算得到昨天的日期； 每天都会生成一张昨天日期的临时表2，每张表的数据大概是1000万左右。

将当前表中的历史数据迁移到昨天的流表中的操作由定时任务处理。 定时任务一般在凌晨12点以后触发。 这个操作在几秒内完成，可能会有几条数据落入当天表中。 因此，我们最终需要将当天表中的历史流量数据插入到昨天的表中； 插入 test_a_serial_{date}(cloumn1,cloumn2….) select(cloumn1,cloumn2….) from test_a_serial where LEFT(create_time,8) > CONCAT(date); 犯罪;

这样就完成了流量数据的迁移；

根据业务需要，部分业务数据需要保存半年，半年后删除。 删除时，可以根据表名中的_{date}过滤掉半年以上的流量，直接删除表；

半年下来，一张业务流水表有180多张表，每张表有20张分区表，怎么查询呢？ 由于我们的项目对管道的实时查询要求不是特别高，所以在做查询的时候，我们按照查询的时间间隔进行路由查询的方法。

一般情况下，根据客户选择的时间间隔，带上查询条件，在时间间隔内的各个表中进行查询，将查询结果保存在一个临时表中，再对临时表进行查询，得到最终的查询结果。

以上就是我们面对大数据量场景时在数据库层面的相应优化。 一个每天1亿的表拆分后，每个表分区的数据大概是500万。 这样设计之后，我们还面临一些其他的问题mysql数据库直接拷贝，比如流水的统计问题。 如此庞大的数据量，项目中有100多个统计维度。 就算一天数一百次mysql数据库直接拷贝，也是极其困难的。 我们使用实时计算统计的方法来解决这个问题。 相关技术涉及实时计算、消息队列、缓存中间件等，敬请期待！

长按二维码▲