mysql数据库分表

一般下载的源码都带了MySQL数据库的，做个真正意义上的网站没数据库肯定不行。 数据库主要存放用户信息(注册用户名密码，分组，等级等)，配置信息(管理权限配置，模板配置等)，内容链接(html ,图片，声音，视频等等的路径)。那mysql数据库为什么要分表和分区？

 

为什么要分表和分区?

　　我们的数据库数据越来越大，随之而来的是单个表中数据太多。以至于查询书读变慢，而且

　　由于表的锁机制导致应用操作也搜到严重影响，出现了数据库性能瓶颈。

　　mysql 中有一种机制是表锁定和行锁定，是为了保证数据的完整性。表锁定表示你们都不能

　　对这张表进行操作，必须等我对表操作完才行。行锁定也一样，别的 sql 必须等我对这条数

　　据操作完了，才能对这条数据进行操作。当出现这种情况时，我们可以考虑分表或分区。

　　1、分表

　　什么是分表?

　　分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，每个表都对应三

　　个文件，MYD 数据文件，.MYI 索引文件，.frm 表结构文件。这些表可以分布在同一块磁盘

　　上，也可以在不同的机器上。app 读写的时候根据事先定义好的规则得到对应的表名，然后

　　去操作它。

　　将单个数据库表进行拆分，拆分成多个数据表，然后用户访问的时候，根据一定的算法(如

　　用 hash 的方式，也可以用求余(取模)的方式)，让用户访问不同的表，这样数据分散到多

　　个数据表中，减少了单个数据表的访问压力。提升了数据库访问性能。分表的目的就在于此，

　　减小数据库的负担，缩短查询时间。

　　Mysql 分表分为垂直切分和水平切分

　　垂直切分是指数据表列的拆分，把一张列比较多的表拆分为多张表

　　通常我们按以下原则进行垂直拆分:

　　把不常用的字段单独放在一张表;

　　把 text，blob(binary large object，二进制大对象)等大字段拆分出来放在附表中;

　　经常组合查询的列放在一张表中;

　　垂直拆分更多时候就应该在数据表设计之初就执行的步骤，然后查询的时候用 jion 关键起来

　　即可。

　　水平拆分是指数据表行的拆分，把一张的表的数据拆成多张表来存放。

　　水平拆分原则

　　通常情况下，我们使用 hash、取模等方式来进行表的拆分

　　比如一张有 400W 的用户表 users，为提高其查询效率我们把其分成 4 张表 users1，users2，

　　users3，users4

　　通过用 ID 取模的方法把数据分散到四张表内 Id%4= [0,1,2,3]

　　然后查询,更新,删除也是通过取模的方法来查询

　　部分业务逻辑也可以通过地区，年份等字段来进行归档拆分;

　　进行拆分后的表，这时我们就要约束用户查询行为。比如我们是按年来进行拆分的,这个时

　　候在页面设计上就约束用户必须要先选择年,然后才能进行查询。

　　分表的几种方式：

　　1)mysql 集群

　　它并不是分表，但起到了和分表相同的作用。集群可分担数据库的操作次数，将任务分担到

　　多台数据库上。集群可以读写分离，减少读写压力。从而提升数据库性能。

　　2)预先估计会出现大数据量并且访问频繁的表，将其分为若干个表

　　根据一定的算法(如用 hash 的方式，也可以用求余(取模)的方式)让用户访问不同的表。

　　例如论坛里面发表帖子的表，时间长了这张表肯定很大，几十万，几百万都有可能。聊天室里面信息表，几十个人在一起一聊一个晚上，时间长了，这张表的数据肯定很大。像这样的

　　情况很多。所以这种能预估出来的大数据量表，我们就事先分出个 N 个表，这个 N 是多少，

　　根据实际情况而定。以聊天信息表为例：我们事先建 100 个这样的表，

　　message_00,message_01,message_02..........message_98,message_99.然后根据用户的 ID 来判

　　断这个用户的聊天信息放到哪张表里面，可以用 hash 的方式来获得，也可以用求余的方式

　　来获得，方法很多。

　　或者可以设计每张表容纳的数据量是 N 条，那么如何判断某张表的数据是否容量已满呢?

　　可以在程序段对于要新增数据的表，在插入前先做统计表记录数量的操作，当

　　就直接插入，当已经到达阀值，可以在程序段新创建数据库表(或者已经事先创建好)，再

　　执行插入操作)。

　　3)利用 merge 存储引擎来实现分表

　　如果要把已有的大数据量表分开比较痛苦，最痛苦的事就是改代码，因为程序里面的 sql 语

　　句已经写好了，用 merge 存储引擎来实现分表, 这种方法比较适合。

　　merge 分表，分为主表和子表，主表类似于一个壳子，逻辑上封装了子表，实际上数据都是

　　存储在子表中的。

　　我们可以通过主表插入和查询数据，如果清楚分表规律，也可以直接操作子表。

　　下面我们来实现一个简单的利用 merge 存储引擎来实现分表的演示：

　　创建一个完整表存储着所有的成员信息(表名为 member)

 

　　加入点数据：

 

　　第二条语句多执行几次就有了很多数据：

 

　　下面我们进行分表，这里我们把 member 分两个表 tb_member1,tb_member2

 

　　创建主表 tb_member

 

　　注：INSERT_METHOD,此参数 INSERT_METHOD = NO 表示该表不能做任何写入操作只作为查

　　询使用,INSERT_METHOD = LAST 表示插入到最后的一张表里面。INSERT_METHOD = first 表示

　　插入到第一张表里面。

　　查看一下 tb_member 表的结构:

 

　　注：查看子表与主表的字段定义要一致

　　接下来，我们把数据分到两个分表中去：

 

　　查看两个子表的数据：

 

　　查看一下主表的数据：

 

　　注意：总表只是一个外壳，存取数据发生在一个一个的子表里面。

　　注意：每个子表都有自已独立的相关表文件，而主表只是一个壳，并没有完整的相关表文件。