MySQL大数据优化以及分解（下篇）

bestu

　　http://xiaorenwutest.blog.51cto.com
　　
　　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,                 ========>>　　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>>

　　可以看出修改之后的分区来自商店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的冰山一角，另外有哪里不对的希望大家指点。
　　下一次为大家带来MySQL的还原和备份