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是否支持分区技术

205440790.png


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

1、建立一个分区表

210620475.png

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

210806481.png

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

211714206.png

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

211829778.png

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

1、创建普通表no_part_tab

212918301.png


2、创建分区表part_tab

213708644.png

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

215529124.png

4、同样数据写入no_part_tab

215824398.png

5、对比一下数据文件

220205733.png

6、测试SQL性能

220518367.png

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

7、创建索引后的对比

221233856.png

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

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

221630181.png

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


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

1、共享表空间:

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

2、独占表空间

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

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

222755466.png

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

222909874.png

223005574.png

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

1
innodb_file_per_table = 1

224109151.png

5、看修改后的存储文件

224156422.png

6、实现innodb分区

224629814.png

224659577.png



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是索引文件之一 


### MySQL 分区表的概念与实现方法 #### 什么是分区? 在 MySQL 中,分区是一种将大表拆分为更小、更容易管理的部分的技术。通过分区,可以显著提高查询效率并简化数据维护工作。当 `have_partition_engine` 的值为 `YES`[^1],表示当前 MySQL 版本支持分区功能。 --- #### 分区的类型 MySQL 支持多种类型的分区方式,每种适用于不同的场景: 1. **Range 分区** 将数据按照某个字段范围分布到不同分区中。例如,可以根据日期或数值区间来划分。 ```sql CREATE TABLE sales ( id INT NOT NULL, sale_date DATE NOT NULL, amount DECIMAL(10,2) ) PARTITION BY RANGE (YEAR(sale_date)) ( PARTITION p0 VALUES LESS THAN (2020), PARTITION p1 VALUES LESS THAN (2021), PARTITION p2 VALUES LESS THAN MAXVALUE ); ``` 2. **List 分区** 类似于 Range 分区,但用于离散值集合而非连续范围。 ```sql CREATE TABLE employees ( emp_id INT NOT NULL, region VARCHAR(10) NOT NULL ) PARTITION BY LIST(region) ( PARTITION north VALUES IN ('North'), PARTITION south VALUES IN ('South'), PARTITION east VALUES IN ('East'), PARTITION west VALUES IN ('West') ); ``` 3. **Hash 分区** 使用哈希函数自动分配记录到指定数量的分区中。 ```sql CREATE TABLE logs ( log_id INT NOT NULL, message TEXT ) PARTITION BY HASH(log_id) PARTITIONS 4; ``` 4. **Key 分区** 类似 Hash 分区,但基于内置哈希算法。 ```sql CREATE TABLE users ( user_id INT NOT NULL, username VARCHAR(50) ) PARTITION BY KEY(user_id) PARTITIONS 8; ``` 5. **Composite 分区** 结合两种分区策略(如 Range 和 Hash),形成复合分区。 ```sql CREATE TABLE transactions ( trans_id INT NOT NULL, trans_date DATE NOT NULL ) PARTITION BY RANGE(YEAR(trans_date)) SUBPARTITION BY HASH(MONTH(trans_date)) SUBPARTITIONS 12 ( PARTITION p0 VALUES LESS THAN (2020), PARTITION p1 VALUES LESS THAN (2021), PARTITION p2 VALUES LESS THAN MAXVALUE ); ``` --- #### 如何管理分区? ##### 创建分区表 可以通过 `CREATE TABLE ... PARTITION BY` 语句定义分区逻辑。具体语法取决于所选的分区类型[^3]。 ##### 修改现有表的分区 如果已有表未启用分区,可通过 `ALTER TABLE` 添加分区: ```sql ALTER TABLE existing_table PARTITION BY RANGE (column_name) ( PARTITION p0 VALUES LESS THAN (value1), PARTITION p1 VALUES LESS THAN (value2), PARTITION pn VALUES LESS THAN MAXVALUE ); ``` ##### 删除分区 删除特定分区时,需注意其行为可能会影响数据重分配。例如: ```sql ALTER TABLE student DROP PARTITION student_2020; -- 此命令会将被删除分区的数据重新分配至其他分区[^2] ``` ##### 合并分区 对于相邻的 Range 或 List 分区,可执行合并操作以减少碎片化: ```sql ALTER TABLE sales REORGANIZE PARTITION p0, p1 INTO ( PARTITION p_new VALUES LESS THAN (2021) ); ``` ##### 查看分区信息 使用以下 SQL 查询获取有关分区的元数据: ```sql SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='your_table'; ``` --- #### 性能优化建议 1. 避免频繁更改分区结构,因为这可能导致锁表或其他性能开销。 2. 对于大数据量表,优先考虑按时间戳或业务属性进行分区。 3. 定期清理过期分区中的无用数据,释放存储空间。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值