[mysql]数据库分区

 

mysql的分区技术理论

 

[1] 大数据存储

当mysql中一个表的总记录数超过了1000万，会出现性能的大幅度下降吗？答案是肯定的。但性能下降的比率由系统的架构、应用程序、数据库索引、服务器硬件等多种因素而定。数据库多大上亿的数据量，分表之后的单个表也已经突破千万，那么单个表的更新等均影响着系统的运行效率。甚至是一条简单的SQL都有可能压垮整个数据库，如整个表对某个字段的排序等。

 

[2] 解决方案

目前，针对海量数据的优化主要有2种方法：大表拆分小表的方式、SQL语句的优化

 

[3] SQL语句的优化：可以通过增加索引等来调整，但是数据量的增大将会导致索引的维护代价增大

 

[4] 大表拆小表

a.垂直分表 

 

table a(id,colspan1, colspan2, colspan3, colspan4, colspan5) => table a(id,colspan1, colspan2, colspan3) and table a( colspan4, colspan5)

 b.水平分表

 

 

table a(id,colspan1, colspan2, colspan3, colspan4, colspan5) id={1,1000}
table a(id,colspan1, colspan2, colspan3, colspan4, colspan5) id={2,1000}
...

 

 

 

[5] 水平分区技术将一个表拆分成多个表，比较常用的方式是将表中的记录按照某种Hash算法进行拆分，简单的拆分方法如取模方式。同样，这种分区方法也必须对前端的应用程序中的SQL进行修改方可使用。而且对于一个SQL，它可能会修改两个表，那么你必须得写成2个SQL语句从而可以完成一个逻辑的事务，使得程序的判断逻辑越来越复杂，这样也会导致程序的维护代价高，也就失去了采用数据库的优势。因此，分区技术可以有力地避免如上的弊端，成为解决海量数据存储的有力方法。

 

[6] MYSQL分区介绍 version>=5.1

MYSQL的分区技术不同于之前的分表技术，它与水平分表有点类似，但是它是在逻辑层进行的水平分表，对于应用程序而言它还是一张表，MYSQL5.1有5种分区类型：

 

(1) RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区

 

(2) LIST分区： 类似于按RANGE分区，区别在于LIST是基于列值匹配一个离散值集合的某个值来进行选择

 

(3) HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算，这个函数可以包含MYSQL中有效的，产生非负整数值的任何表达式。

 

(4)KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MYSQL服务器提供其自身的哈希函数。

 

[7] range分区示例

 

mysql>create table employees( 
	id int not null, 
	fname varchar(30), 
	lname varchar(30), 
	hire date not null default '1970-01-01', 
	separate date not null default '1999-01-01', 
	job_code int not null, 
	store_id int not null
)
partition by RANGE(store_id) (
	partition p0 values less than(6),
	partition p1 values less than(11),
	partition p2 values less than(16),
	partition p3 values less than(21)
);
//或
partition by RANGE(year(separate)) (
	partition p0 values less than (1991),
	partition p1 values less than (1996),
	partition p2 values less than (2000),
	partition p3 values less than MAXVALUE
);

 

 

[8] LIST分区示例

 

mysql>create table employees( 
	id int not null, 
	fname varchar(30), 
	lname varchar(30), 
	hire date not null default '1970-01-01', 
	separate date not null default '1999-01-01', 
	job_code int not null, 
	store_id int not null
)
partition by LIST(store_id) (
	partition pNorth values in(3,5,6,9,17),
	partition pEast values in (1,2,10,11,19,20),
	partition pWest values in (4,12,13,14,18),
	partition pCentral values in (7,8,15,16)
);

 

 

[9]HASH分区示例

 

mysql>create table employees( 
	id int not null, 
	fname varchar(30), 
	lname varchar(30), 
	hire date not null default '1970-01-01', 
	separate date not null default '1999-01-01', 
	job_code int not null, 
	store_id int not null
)
partition by HASH(year(hired))
partitions 4

 [10]分区类型、优点、缺点、共性

 

 

Range   优点：（适合与日期类型，支持复合分区） 
             缺点：（有限的分区）
	     共性：（一般针对某一列） 

List	 优点：（适合与有固定取值的列，支持复合分区）
         缺点：（有限的分区，插入记录在这一列的值，不在值List中，则数据丢失）
         共性：（一般针对某一列）      

Hash 优点：（线性Hash使得增加、删除、合并分区更快捷）
         缺点：（线性Hash的数据分布不均匀，而一般Hash的数据分布较均匀）
         共性：（一般针对某一列）  

Key	 优点：（列可以为字符型等其它非int类型）
	 缺点：（效率较之前的低，因为函数为复杂的函数(如.MD5或SHA函数)）
	 共性：（一般针对某一列）

  

 

[11] mysql一些命令

 

a.\s 查看字符集 b. show engines; c. show plugins;

d. \db // 改变定界符 

 

 

[12] linux一些命令

 

a. watch -n1 ls -lh 隔一秒钟刷新一次结果 b.ctrl+F1\F2 切换终端

 

 

[13] 存储过程

mysql>create procedure p3()
	->begin
	->set @i=1;
	->while @i<10000 do
	->insert into t3 values(@i);
	->set @i=@i+1;
	->end while;
	->end //

mysql>show precedure status;

 

[14] innodb表数据结构

 

a.对于innodb的数据结构，首先要解决两个概念性的问题：共享表空间以及独占表空间

 

b.共享表空间：某一个数据库的所有的表数据，索引文件全部放在一个文件中，默认这个共享表空间的文件路径在data目录下。默认的文件名为：ibdata1初始化为10M。

 

c.独占表空间：每个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有一个.ibd文件。其中这个文件包括了单独一个表的数据内容以及索引内容，默认情况下它的存储位置也是在表的位置中

 

d.

innodb_data_home_dir 		= "C:\mysql\data\"
innodb_log_group_home_dir 	= "C:\mysql\data\"
innodb_data_file_path 		= ibdata1:10M:autoextend
innodb_file_per_table	 	= 1
//以上的几个参数必须在一起加入