MySQL 分区分库分表简介

1.Mysql分区：

    (1) 是什么: 如一张表的数据量太大，那么myd,myi就会变的很大，查找数据就会变的很慢，这个时候我们可以利用mysql的分区功能，在物理上将这一张表对应的三个文件，分割成许多个小块，这样呢，我们查找一条数据时，就不用全部查找了，只要知道这条数据在哪一块，然后在那一块找就行了，如果表的数据太大，可能一个磁盘放不下，这个时候，就可以把数据分配到不同的磁盘里面去。

   (2) 能干嘛：

          ①逻辑数据分割；

          ②提高单一的写和读应用速度；

          ③提高分区范围读查询的速度；

          ④分割数据能够有多个不同的物理文件路径；

          ⑤高效的保存历史数据；

   (3)怎么玩：查看当前数据库是否支持分区：

          ①SHOW VARIABLES LIKE '%partition%';       //Yes

          ②show plugins;                               //ACTIVE

          说明：以上是两种不同的表达形式，第一种Yes表示支持分区，第二种ACTIVE表示支持分区，在一般情况下，我们的MySQL数据库都支持分区。

   (4)分区类型及操作

①RANGE分区(范围分区，一般按月份分12个区即可，逻辑分区):

     原理：mysql将会根据指定的拆分策略，把数据放在不同的表文件上，相当于在文件上,被拆成了小块.但是,对外给客户的感觉还是一张表，透明的。

     建表

CREATE TABLE tbl_new(

 id INT NOT NULL PRIMARY KEY,

 title VARCHAR(20) NOT NULL DEFAULT  ''

 )ENGINE MYISAM CHARSET utf8

 PARTITION BY RANGE(id)(

 PARTITION t0 VALUES LESS THAN(10),

 PARTITION t1 VALUES LESS THAN(20),

 PARTITION t2 VALUES LESS THAN(MAXVALUE)

 );

INSERT INTO tbl_new VALUES(1,'z3');

INSERT INTO tbl_new VALUES(2,'z4');

INSERT INTO tbl_new VALUES(3,'z5');

INSERT INTO tbl_new VALUES(4,'z6');

     查看分区

     查看分区 2 次

②List分区：

     原理：MySQL中的LIST分区在很多方面类似于RANGE分区。和按照RANGE分区一样，每个分区必须明确定义。 它们的主要区别在于，LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值。而RANGE分区是从属于一个连续区间值的集合。

     案例SQL(建表);

create table area(

id INT NOT NULL PRIMARY KEY,

region varchar(20)

)engine myisam charset utf8;

insert into area values(1,'bj');

insert into area values(2,'sh');

insert into area values(3,'gz');

insert into area values(4,'sz');

create table user (

uid int not null,

userName varchar(20),

area_id int

)engine myisam charset utf8

partition by list(area_id) (

partition bj values in (1),

partition sh values in (2),

partition gz values in (3),

partition sz values in (4)

);

insert into user(uid,userName,area_id) values(1,'z3',1);

insert into user(uid,userName,area_id) values(2,'z4',2);

insert into user(uid,userName,area_id) values(3,'z5',3);

   案例SQL( 案例SQL):

③其它： Hash 分区， Key 分区， 子分区。

④对NULL值的处理：

     MySQL中的分区在禁止空值NULL上没有进行处理，无论它是一个列值还是一个用户定义表达式的值，

     一般而言，在这种情况下MySQL把NULL当做零。如果你不希望出现类似情况，建议在设计表时声明该列“NOT NULL”。

2.Mysql分库：

     (1)是什么: 一个库里表太多了，导致了海量数据，系统性能下降，把原本存储于一个库的表拆分存储到多个库上，通常是将表按照功能模块、关系密切程度划分出来，部署到不同库上。

     (2)为什么要分库

     数据库集群环境后都是多台slave,基本满足了读取操作;但是写入或者说大数据、频繁的写入操作对master(主人：这里指Mysql数据库)性能影响就比较大，这个时候，单库并不能解决大规模并发写入的问题。

     优点: ①减少增量数据写入时的锁对查询的影响。

             ②由于单表数量下降，常见的查询操作由于减少了需要扫描的记录，使得单表单次查询所需的检索行数变少，减少了磁盘IO，时延变短。

     缺点：无法解决单表数据量太大的问题。

3.Mysql分表：

     (1)是什么:

①垂直拆分:user_main,user_job,user_contact.

  通常是按照业务功能的使用频次，把主要的、热门的字段放在一起做为主要表；

         然后把不常用的，按照各自的业务属性进行聚集，拆分到不同的次要表中；主要表和次要表的关系一般都是一对一的。

②水平拆分(数据分片):mysql单表的容量不超过300-500W，否则不建议水平拆分

     (2)能干嘛:

     (3)怎么玩:

①切分策略

②导航路由

③是否有一些开源方案:MySQL Fabric,Atlas,TDDL,MySQL proxy

4.小总结：

     (1)分库分表演变过程:

        单库多表--->读写分离主从复制--->垂直分库，每个库又可以带着salve--->继续垂直分库，极端情况单库单表--->分区(变相的水平拆分表，只不过是单库的)--->水平分表后，再放入多个数据库里，进行分布式部署，终极method。 

     (2)分库分表后的难题:

      ①分布式事务的问题，数据的完整性和一致性问题。

      ②数据操作维度问题：用户、交易、订单各个不同的维度，用户查询维度、产品数据分析维度的不同对比分析角度。

      ③跨库联合查询的问题，可能需要两次查询

      ④跨节点的count、order by、group by以及聚合函数问题，可能需要分别在各个节点上得到结果后在应用程序端进行合并

      ⑤额外的数据管理负担，如：访问数据表的导航定位

      ⑥额外的数据运算压力，如：需要在多个节点执行，然后再合并计算

      ⑦程序编码开发难度提升，没有太好的框架解决，更多依赖业务看如何分，如何合，是个难题。

     (3)不到最后一步，轻易不用进行水平分表.

5.我的理解：

     分区：:如一张表的数据量太大，查找数据就会变的很慢，这个时候我们可以利用mysql的分区功能，在物理上将这一张表对应的三个文件(.MYD数据文件，.MYI索引文件，.frm表结构文件 )，分割成许多个小块，这样呢，我们查找一条数据时，就不用全部查找了，只要知道这条数据在哪一块，然后在那一块找就行了，如果表的数据太大，可能一个磁盘放不下，这个时候，就可以把数据分配到不同的磁盘里面去。再一般情况下我们分为12个区。

     分库： 一个库里表太多了，导致了海量数据，系统性能下降，把原本存储于一个库的表拆分存储到多个库上，通常是将表按照功能模块、关系密切程度划分出来，部署到不同库上。     

     分表：一个表中的数据太大，可以分为水平拆分 和 垂直拆分: