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小时售前售后，随时帮您解答问题的。