MySQL分区技术 （一），mysql分区技术

MySQL分区技术 （一），mysql分区技术

4：MySQL 分区技术（是mysql 5.1以版本后开始用->是甲骨文mysql技术团队维护人员以插件形式插入到mysql里面的技术）


	目前，针对海量数据的优化主要有2中方法：
		1：大表拆成小表的方式（物理上）
			一：垂直分表->一张垂直切成几张
			二：水平分表（一般重点）->横切，意思就是一张表有100个数据横切10张表，一张表存10条（字段一致）
		2：SQL语句的优化（可以通过增加索引等来调整，但是数据量大的增大会导致索引的维护代价增大）
		
			水平分区技术将一个表拆成多个表，比较常用的方式是将表中的记录按照某种hash算法进行拆分，简单的拆分方法如取摸
		方式。同样，这种分区方法也必须对前端的应用程序中的SQL进行修改方可以使用。而且对于一个SQL，它可能会修改两个
		表，那么你必须地写出2个SQL语句从而可以完成一个逻辑事务，使得程序的判断逻辑越来越复杂，这样也导致程序的维护代价
		高，也就失去了采用数据库的优势。
		
		*因此：分区技术可以有力地避免如上的弊端，成为解决海量数据存储的有力方法。
	
	
	
	分区技术：
	->>有效解决了：物理上拆分多个表，逻辑上操作一个表表明不变
	
	->>MySQL分区技术介绍（*主要用的是range 和 list 分区*）：
		-----分区在逻辑上是一张表，在硬件/物理上是多张表,就是拆分表索引和数据-----


		MySQL的分区技术不同与之前的分表技术，它与水平分表有点类似，但是它在逻辑层进行的水平分表，
		对与应用程序而言它还是一张表，


	    MySQL5.1版本后有4中分区类型：
		一：RANGE分区（用的最多）：基于属于一个给定连续区间的列值（字段），把多行分配给分区  -->基于女字段为参考点来进行分区
			--将一个表拆分成：索引文件，数据文件分片存储


		二：LIST分区：类似于按range分区，区别在于list分区是基于列值匹配一个离散值集合中的某个值来进行选择（列里面的值是固定值时候来进行分区，而且是枚举类型的值适合用list分区  -->比如说 性别：男，女）
		
		三：HASH分区：基于用户定义的表达式的返回值来进行选择的分区，改表达式使用将要插入到表中的这些行的
			      列值计算，这个函数可以包含MySQL中有效的、产生负整数值的任何表达式
			      --->把每次插入的数据随机的平均的分配到多个分区里面，最终多个分区里面的数据时平均分配的，但是每个分区里面的数值肯能不太一样，因为是随机分配的（一般可以用来做MySQL分区的测试来使用）
		
		四：KEY分区：类似于按hash分区，区别在于key分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数
		
		测试一下（测试用hash类型的）：->myisam增删改查的速度快
			create table t2（id int）engine=myisam
			partition by hash(id)
			partitions 5;                 ->能后当你插入数据的时候就会随机分配插入个个分区中
			
			
			建立一个存储
			\d //         ->修改结束符号  之前是；号改成 //
			create procedure p5()
			begin
			set @i=1;
			while @i<100000 do
			insert into t4 values(@i);
			set @i=@i+1;
			end while;
			end //
			
			执行刚才建立的存储
			call p3()     ->表p3就插入了9999条数据
			
			


		innodb的数据结构：
		
		分为：共享表空间及其独占表空间


			一：innodb表结构共享表空间不能做成分区表：
				所有文件的数据和索引都在ibddata1（比如你建了2个表会对应生成frm文件，但是2个表的所有数据和索引全部在这个文件里面共用，所有不能对表做正真的分区，初始值是10M）
				原因：数据和索引全都是放在一个文件里面 .ibddata1文件
			
			二：innodb表结构要想做出分区表必须是“独占表空间”
				原因：数据和索引全都是独立的一个文件
			
			    开启独占空间：（*必须配置文件中开启文件才能做出独占表空间，才能做成分区表*）
				innodb_data_home_dir = C:\mysql\data\
				innodb_data_file_path = ibdata1:10M:autoextend
				innodb_log_group_home_dir = C:\mysql\data\


				innodb_file_per_table=1   ->配置文件中innodb下方加上
				重启：MySQL   -->pkill mysqld 关闭进程  重新启动MySQL-bin/mysqld_safe --user=mysql &


			    测试：
				create table t4(id int)engine=innodb
				partition by RANGE(id)(
				partition p0 values less than(10000),
				partition p1 values less than(20000),
				PARTITION p2 VALUES less than MAXVALUE);
			
			能后你在创建innodb类型的数据表后，你会发现建一个x表就有x.frm x.ibd文件，就不会和其他表放到一起从而做表分区
			
			*重点总结：只有把innodb设置成独立的表空间后，才能创建innodb表引擎的表分区
		
			


		相关命令：
			/s；   查看详细信息版本啊编码啊 什么的。。。
			show engines; 查看默认表引擎
			show plugins; 查看当前MySQL的所有插件，可以查看是否支持分区partition
			show index from from tabName;  查看索引
			show procedure status; 查看简历的存储

第一阶段：
1，一定要正确设计索引
2，一定要避免SQL语句全表扫描，所以SQL一定要走索引（如：一切的 > < != 等等之类的写法都会导致全表扫描）
3，一定要避免 limit 10000000,20 这样的查询
4，一定要避免 LEFT JOIN 之类的查询，不把这样的逻辑处理交给数据库
5，每个表索引不要建太多，大数据时会增加数据库的写入压力

第二阶段：

1，采用分表技术（大表分小表）

a)垂直分表：将部分字段分离出来，设计成分表，根据主表的主键关联
b)水平分表：将相同字段表中的记录按照某种Hash算法进行拆分多个分表

2，采用mysql分区技术（必须5.1版以上，此技术完全能够对抗Oracle），与水平分表有点类似，但是它是在逻辑层进行的水平分表

第三阶段（服务器方面）：

1,采用memcached之类的内存对象缓存系统，减少数据库读取操作
2,采用主从数据库设计，分离数据库的读写压力
3,采用Squid之类的代理服务器和Web缓存服务器技术

PS:由于篇幅问题，我只简单说一些基本概念，其实里面每个知识点关系到的内容都很多。特别是第一阶段，很多工作几年的程序员，都不能完全理解。我觉得要真正理解索引，最好的办法就是在1000W-亿级以上的数据，进行测试SQL语句，再结合 explain 命令进行查看SQL语句索引情况。
 

我认为已经成熟了，原因：分区特性出现在5.1中，而且sun被oracle收购之后，大力改写mysql很多东西都已被完善，和oracle分区表的性能无法相比，不过可以满足基本的要求。
不过我想说的是，能不分就不分，分区之后虽然每个分区内的数据较少，但是如果分区索引（就是分区字段）效率会比不走好一些，如果不走索引，效率甚至会更差些。