mysql分区

一、基本理论

在mysql大数据处理上，水平分表技术是很常用的，但是分表技术带来了PHP处理的很大开销，为了解决这个弊端Mysql5.1以后以插件的方式增加了一个新的技术--分区技术。

分区技术是在逻辑层进行的水平分表，对于应用程序而言还是一张表，Mysql5.1有4种分区类型

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

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

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

4、KEY ：类似于HASH，区别在于KEY 分区只支持计算一列或多列，且MySQL提供其自身的哈希函数。

二、不同分区技术对比

类型	优点	缺点	共性
RANGE	适合日期类型，支持复合分区	有限的分区	一般只针对某一列
LIST	适合有固定取值的列，支持复合分区	有限的分区，插入记录这一列的值不再list中，数据会丢失	一般只针对某一列
HASH	线性Hash使增加、删除和合并分区更快捷	线性Hash数据分布不均匀，而一般的Hash数据分布比较均匀	一般只针对某一列
KEY	列可以是字符等其他非int型	函数比较复杂，效率比较低	一般只针对某一列

一、查看当前MySQL是否支持分区技术

二、使用HASH实现一个简单测试案例

1、建立一个分区表

2、查看数据库文件初始状态

3、写一个存储过程生成100万条记录

4、查看存储后的数据文件，可以看到数据均匀分布

三、实现RANGE分区，并和普通表实现对比

1、创建普通表no_part_tab

2、创建分区表part_tab

3、写一个存储过程，给part_tab写入800万条记录

4、同样数据写入no_part_tab

5、对比一下数据文件

6、测试SQL性能

可以看到查询时间上的差异

7、创建索引后的对比

可以看出添加索引后，查询时间基本一致。

8、查询字段有未添加索引的字段对比

可以看到时间上又出现了差异

四、关于innodb表的分区注意事项

1、共享表空间：

某一个数据库的所有表数据，索引文件全部放在一个文件中，默认的存放路径是在data目录下。默认的文件名为ibdata1。

2、独占表空间

每个表都会生成独立的的文件来进行存储，每个表都有一个frm表描述文件，还有一个ibd文件。

3、在共享模式下的存储文件

存储文件只有2个，其他数据在ibdata1中

4、修改独立存储模式，修改mysql的my.conf

1	innodb_file_per_table = 1

5、看修改后的存储文件

6、实现innodb分区

MySQL 5.5 分区性能测试之索引使用情况

1.创建一个测试表

[sql]  view plain copy

1. CREATE TABLE test (   
2.   id VARCHAR(20) NOT NULL,  
3.   name VARCHAR(20) NOT NULL,  
4.   submit_time DATETIME NOT NULL,  
5.   index time_index (submit_time),  
6.   index id_index (id)  
7. )ENGINE=MyISAM  
8. PARTITION BY RANGE COLUMNS(submit_time)  
9. (  
10. PARTITION p1 VALUES LESS THAN ('2010-02-01'),  
11. PARTITION p2 VALUES LESS THAN ('2010-03-01'),  
12. PARTITION p3 VALUES LESS THAN ('2010-04-01'),  
13. PARTITION p4 VALUES LESS THAN ('2010-05-01'),  
14. PARTITION p5 VALUES LESS THAN ('2010-06-01'),  
15. PARTITION p6 VALUES LESS THAN ('2010-07-01'),  
16. PARTITION p7 VALUES LESS THAN ('2010-08-01'),  
17. PARTITION p8 VALUES LESS THAN ('2010-09-01'),  
18. PARTITION p9 VALUES LESS THAN ('2010-10-01'),  
19. PARTITION p10 VALUES LESS THAN ('2010-11-01'),  
20. PARTITION p11 VALUES LESS THAN ('2010-12-01')    
21. );  

2.写一个存储过程,插入数据

[sql]  view plain copy

1. delimiter //    
2. CREATE PROCEDURE mark_test()  
3. begin   
4.     declare v int default 0;   
5.     while v < 8000   
6.     do   
7.        insert into test values (v,'testing partitions',adddate('2010-01-01', INTERVAL v hour));  
8.        set v = v + 1;  
9.     end while;  
10. end //  
11. delimiter ;  

3.实验开始

 上面可以看到,这个是查某一个分区里面的某一些内容,所以完全可以用到index.效果很好..

 

 上面可以看到,跨分区查询,效果也非常不错.

 

 上面可以到看,跨分区查询是,如果某个分区没有用到索引(p4就是全表扫描),整个也没有用到index.但好的是,只扫描需要的分区

 

 

  上面可以看到,如果你不用分区的字段查询,是很杯具的,因为MySQL不知道你分区的index是分别存放到哪个分区上,所以要全index扫描, 

 

3.顺便看看表结构

a. 图中test3 是innodb的存储引擎, 

    test3.frm是表结构.

    test3.par是分区表的信息.

    数据和索引都是存放在表空间里面在

b.图中test是myisam的存储引擎, 

   test.frm是表结构,

   test.par是分区表的信息.

   test#P#p10.MYD是数据文件之一,

   test#P#p10.MYI是索引文件之一