MySQL分区分表

　　
　　

博文大纲:

　　
    　　<李> 1,为什么要分表?李 　　<李> 2,MySQL分表 　　<李> 3,利用合并存储引擎实现分表 　　<李> 4,MySQL分区李 　　
　　 　　

1,为什么要分表?

　　

数据库数据越来越大,随之而来的是单个表中数据太多。以至于查询速度变慢,而且由于表的锁机制导致应用操作也搜到严重影响,出现了数据库性能瓶颈。
mysql中有一种机制是表锁定和行锁定,是为了保证数据的完整性。表锁定表示你们都不能对这张表进行操作,必须等我对表操作完才行。行锁定也一样,别的sql必须等我对这条数据操作完了,才能对这条数据进行操作。当出现这种情况时,我们可以考虑分表或分区。

　　

2, MySQL分表

　　

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表,每个表都对应三个文件,MYD数据文件,.MYI索引文件,.frm表结构文件。这些表可以分布在同一块磁盘上,也可以在不同的机器上.app读写的时候根据事先定义好的规则得到对应的表名,然后去操作它。
将单个数据库表进行拆分,拆分成多个数据表,然后用户访问的时候,根据一定的算法(如用哈希的方式,也可以用求余(取模)的方式),让用户访问不同的表,这样数据分散到多个数据表中,减少了单个数据表的访问压力。提升了数据库访问性能。分表的目的就在于此,减小数据库的负担,缩短查询时间。

　　

<强> Mysql分表分为垂直切分和水平切分,具体区别如下:
<强>垂直切分是指数据表列的拆分,把一张列比较多的表拆分为多张表通常我们按以下原则进行垂直拆分:把不常用的字段单独放在一张表;把文本、blob(二进制大对象,二进制大对象)等大字段拆分出来放在附表中,
经常组合查询的列放在一张表中;垂直拆分更多时候就应该在数据表设计之初就执行的步骤,然后查询的时候用加入关键起来即可。

　　

<强>水平拆分是指数据表行的拆分,把一张的表的数据拆成多张表来存放。水平拆分原则,通常情况下,我们使用散列,取模等方式来进行表的拆分比如一张有400 w的用户表用户,为提高其查询效率我们把其分成4张表users1, users2, users3, users4通过用身份证取模的方法把数据分散到四张表内ID % 4=[0, 1, 2, 3]然后查询,更新,删除也是通过取模的方法来查询部分业务逻辑也可以通过地区,年份等字段来进行归档拆分;进行拆分后的表,这时我们就要约束用户查询行为。比如我们是按年来进行拆分的,这个时候在页面设计上就约束用户必须要先选择年,然后才能进行查询。

　　

3,利用合并存储引擎实现分表

　　

<强>注:只有myisam引擎的原表才可以利用合并存储引擎实现分表。

　　

合并分表,分为主表和子表,主表类似于一个壳子,逻辑上封装了子表,实际上数据都是存储在子表中的。我们可以通过主表插入和查询数据,如果清楚分表规律,也可以直接操作子表。

　　

<强>举个栗子:

　　
1)创建一个完整表
　　
 <代码> mysql>创建数据库测试;
　　mysql>使用测试;
　　mysql>创建表(
　　→id bigint auto_increment主键,
　　→名字varchar (20),
　　→性非常小的整数not null默认' 0 '
　　→)=myisam引擎默认字符集=utf8 auto_increment=1;
　　& lt; !——插入数据——比;
　　mysql>插入成员(姓名,性别)值(' tom1 ', 1);
　　mysql>插入成员(姓名,性别)选择名字,从成员性;
　　& lt; !——将上面第二条插入语句多执行几次,即可插入大量的数据——比;
　　mysql>从成员选择count (*);& lt; !——我这里插入了4096条数据——比;
　　+----------+
　　| | count (*)
　　+----------+
　　| 4096 |
　　+----------+
　　1行集(0.00秒)
　　
　　
2)对上面完整的表进行分表
　　
　　

分表注意事项:

　　
    　　<李>子表和主表的字段定义需要一致,包括数据类型,数据长度等,李 　　<李>当分表完成后,所有的操作(增删改查)需要对主表进行,虽然主表并不存放实际的数据。 　　
　　 　　
 <代码> & lt; !——创建两个分表,表结构必须和上面完整的表结构一致——比;
　　mysql>创建表tb_member1像成员;
　　mysql>创建表tb_member2像成员;
　　& lt; !——创建合并引擎的表作为主表,并关联上面的两个分表——比;
　　mysql>创建表tb_member (
　　→id bigint auto_increment主键,
　　→名字varchar (20),
　　→性非常小的整数not null默认' 0 '
　　→)引擎=合并联盟去年charset=(tb_member1, tb_member2) insert_method==use utf8;
　　
MySQL分区分表