MySQL大数据如何优化及分解
本文主要给大家介绍 MySQL大数据如何优化及分解，其所涉及的东西，从理论知识来获悉，有很多书籍、文献可供大家参考，从现实意义角度出发，累计多年的实践经验可分享给大家。
公司中的数据过大或者访问量过多都会导致数据库的性能降低，过多的损耗磁盘i/o和其他云服务器的性能，严重会导致宕机。根据这种情况我们给出了解决方法，那么接下来我们继续：
上次说到了分表和分区:首先让我们回顾下分表和分区的区别：
分表：
将一个大表分解成若干个小表，每个小表都有独立的文件.MYD/.MYI/.frm三个文件
分区：
将存放数据的数据块变多了，表还是一张大表，在后面会有一个总结。
讲到分区了上次，这次主要还是分区的内容；分区主要包括五个分区类型：
1）：range分区：
把连续区间的列值分配给分区，而且这些区间不能相互重叠，
那么我们就举个例子来验证下，range分区和未分区的不同之处：{性能比拼}
首先创建一个未分区的库和表
MySQL>create database test；
mysql> create table test.tab1(c1 int,c2 varchar(30),c3 date);
接下来创建一个分区的表，按照年份进行拆分
mysql> use test
mysql> CREATE TABLE tab2 ( c1 int, c2 varchar(30) , c3 date )
PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995),
PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) ,
PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) ,
PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) ,
PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) ,
PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010),
PARTITION p11 VALUES LESS THAN MAXVALUE );
最后一行表示有可能在插入年份数据比2005大的数据可以插入，如果没有将会出错
刚才创建的两个表tab1和tab2没有数据只有一个空表；接下来为它们插入数据;
多一点，不然看不出效果；1000000条数据
创建存储过程，需要使用界定符
mysql> delimiter &&//指定存储过程结束符
mysql>CREATE PROCEDURE load_part_tab()
begin
declare x int default 0;
while x < 1000000
do
insert into test.tab1
values (x,'testing partitions',adddate('1995-01-01',(rand(x)*36520) mod 3652));
set x = x + 1;
end while;
end
&&
rand（）函数在0和1之间的随机数，如果rand（n）中的n被指定，它将作为一个值，再次不做过多的叙述；大家可以查看相关的资料
将load_part_tab向test.tab1表中插入1000000条数据
查看一下是否插入成功
接下来向tab2表中也插入1000000条的数据
mysql> insert into test.tab2 select * from test.tab1;
注：这条sql语句属于嵌套式，将后面执行的结果交给前面的执行
查看一下是否成功;
接下来让我们拭目以待；测试sql性能：
tab1表的查询：
mysql> select count(*) from test.tab1 where c3 > '1995-01-01' and c3 < '1995-12-31';
tab2表的查询：
mysql>select count(*) from test.tab2 where c3 > '1995-01-01' and c3 < '1995-12-31';
通过explain语句来分析下它们的执行情况：
结果表明分区的效果要比为分区的效果好太多了。既然这样我们就现在这个前题之下为tab1
表和tab2表创建索引，看看它们的效果如何：
刷新下表的缓存，进行查询：
tab1表查询如下
tab2表的查询如下；
由此结果可见索引创建之后还是分区后的速度快，但是结果相差不大，但是如果数据结构复杂，数据量庞大的情况之下，结果会越发的显著
不知道大家看明白了没有，如果没有我们在举个例子，主要因为range分区在工作中使用较多，所以再次在举个例子：
mysql>create database test2
mysql> CREATE TABLE employees ( ==========>创建一个employees表
id INT NOT NULL, ========> ID号为×××不能为空
fname VARCHAR(30), ===========>姓氏
lname VARCHAR(30), ===========>名字
hired DATE NOT NULL DEFAULT '1970-01-01', ==========> 雇佣的日期，不能为空
separated DATE NOT NULL DEFAULT '9999-12-31', ==========>离开的日期，不能为空
job_code INT NOT NULL, =============> 员工职务的工号，不能为空
store_id INT NOT NULL ===========> 商店ID号，不能为空
)
partition BY RANGE (store_id) ( ============> 分区范围以商店ID为准
partition p0 VALUES LESS THAN (6), =========> p0包括小于6的id号
partition p1 VALUES LESS THAN (11), ===========>p1包括小于11ID号
partition p2 VALUES LESS THAN (16), ============>p2包括小于16ID号
partition p3 VALUES LESS THAN (21) ===============>p3包括小于21的ID号
);
上面的分区方式说明了商店1-5号；工作的员工都被保存在了p0区域当中，6-11的商店员工被保存在了p1区域中，其他的一次类推；
但是需要注意的是，如果从其他地方区域调来了一个员工如（81，'lll'，'xxff'，'1998-06-25'，'2001-26-25'，25，13）是否可以加入呢？ 当然可以而且还是p2区域，这要查看他的商店id号为13，所以可以。那么我们来验证一下OK
插入成功
那如果又有一个商店ID号为25的员工是否能加入呢？
插入失败，由于没有规则把store_id大于20的商店包含在内，服务器将不知道把该行保存在何处，将会导致错误。要避免这种错误，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在的分区中。
我们可以通过sql语句修改次分区：
mysql> alter table employees add partition (partition p4 values less than maxvalue);
可以看出修改之后的分区来自商店ID为25的员工可以加入。
介绍了range分区，那么我们进行个总结对range：
基于属于一个给定连续区间的列值，把多行分配给分区。这些区间要连续且不能相互重叠
而且在最后的区域中设置maxvalue防止大于区域的内容无法插入。
2）list分区;
比较类似range分区，区别在于range的值是连续的，而list是散值集合在一个行中，或许大家听的不太明白，一会给大家举个例子看下。
LIST分区通过使用"PARTITION BY LIST(expr)"来实现，其中"expr" 是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过"VALUES IN (value_list)"的方式来定义每个分区，其中"value_list"是一个通过逗号分隔的整数列表。
我们还以商店这个例子为大家进行演示，正好和range进行对比：
首先创建库
mysql> create database tty;
这样在表中增加或者删除制定区域的员工记录变得容易起来，假如说pNorth商店倒闭了现在需要将员工记录全部删掉可以使用"ALTER TABLE employees DROP PARTITION pWest；"来进行删除，它与具有同样作用的DELETE （删除）查询"DELETE query DELETE FROM employees WHERE store_id IN (4,12,13,14,18)；"比起来，要有效得多。
接下来为大家介绍下另外的三种分区方式：作为了解
3）HASH分区;
这种模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。
hash分区的目的是将数据均匀的分布到预先定义的各个分区中，保证各分区的数据量大致一致。分区中； MYSQL自动完成这些工作，用户所要定一个列值或者表达式，以及指定被分区的表将要被分割成的分区数量。
接下来举例说明：
mysql> create table t_hash( a int(11), b datetime) partition by hash(year(b)) partitions 4;
接下来插入点数据，让我们来验证下：
那么看一下MySQL自动会将这条数据插入到那个分区呢？
我们还可以通过系统数据库information_schema查看下ll库中的t_hash表的树形结构：
前期应为创建了4个分区所以这里会显示4个
这里只有p2表当中有数据，其他的三个都没有数据
4）key分区：
key分区和hash分区相似，不同在于hash分区是用户自定义函数进行分区，key分区使用mysql数据库提供的函数进行分区，对于其他存储引擎mysql使用内部的hash函数。
创建的sql语句为：
mysql> create table t_key( a int(11), b datetime) partition by key(b) partitions 4;
为其插入一条sql语句：
我们看一下
上面的RANGE、LIST、HASH、KEY四种分区中，分区的条件必须是×××，如果不是×××需要通过函数将其转换为×××。
5）columns分区
mysql-5.5开始支持COLUMNS分区，可视为RANGE和LIST分区的进化，COLUMNS分区可以直接使用非×××数据进行分区。COLUMNS分区支持以下数据类型：
所有×××，如INT SMALLINT TINYINT BIGINT。FLOAT和DECIMAL则不支持。
日期类型，如DATE和DATETIME。其余日期类型不支持。
字符串类型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT类型不支持。
COLUMNS可以使用多个列进行分区。
最后为大家做一个完整的总结；从不同方面介绍分表和分区的不同之处
mysql分表和分区有什么区别呢
1、实现方式上
a） mysql的分表是真正的分表，一张表分成很多表后，每一个小表都是完整的一张表，都对应三个文件，一个.MYD数据文件，.MYI索引文件，.frm表结构文件。
b） 分区不一样，一张大表进行分区后，他还是一张表，不会变成二张表，但是他存放数据的区块变多了
2、数据处理上
a）分表后，数据都是存放在分表里，总表只是一个外壳，存取数据发生在一个一个的分表里面。
b）分区呢，不存在分表的概念，分区只不过把存放数据的文件分成了许多小块，分区后的表呢，还是一张表，数据处理还是由自己来完成。
3、提高性能上
a）分表后，单表的并发能力提高了，磁盘I/O性能也提高了。并发能力为什么提高了呢，因为查寻一次所花的时间变短了，如果出现高并发的话，总表可以根据不同的查询，将并发压力分到不同的小表里面。
b）mysql提出了分区的概念，主要是想突破磁盘I/O瓶颈，想提高磁盘的读写能力，来增加mysql性能。
在这一点上，分区和分表的测重点不同，分表重点是存取数据时，如何提高mysql并发能力上；而分区呢，如何突破磁盘的读写能力，从而达到提高mysql性能的目的。
4、实现的难易度上
a）分表的方法有很多，用merge来分表，是最简单的一种方式。这种方式跟分区难易度差不多，并且对程序代码来说可以做到透明的。如果是用其他分表方式就比分区麻烦了。
b）分区实现是比较简单的，建立分区表，根建平常的表没什么区别，并且对开代码端来说是透明的。
mysql分表和分区有什么联系？
1．都能提高mysql的性高，在高并发状态下都有一个良好的表现。
2．分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表，我们可以采取分表和分区结合的方式，访问量不大，但是表数据很多的表，我们可以采取分区的方式等。
3.分表技术是比较麻烦的，需要手动去创建子表，app服务端读写时候需要计算子表名。采用merge好一些，但也要创建子表和配置子表间的union关系。
4.表分区相对于分表，操作方便，不需要创建子表。
看了以上 MySQL大数据如何优化及分解介绍，希望能给大家在实际运用中带来一定的帮助。本文由于篇幅有限，难免会有不足和需要补充的地方，大家可以继续关注行业资讯板块，会定期给大家更新行业新闻和知识，如有需要更加专业的解答，可在官网联系我们的24小时售前售后，随时帮您解答问题的。