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(*) fromtest.tab1 where c3 > '1995-01-01' and c3 < '1995-12-31';
tab2表的查询:
mysql>select count(*) fromtest.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 tableemployees 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 bykey(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小时售前售后,随时帮您解答问题的。
网页题目:MySQL大数据如何优化及分解
网页链接:http://pwwzsj.com/article/pggssc.html