MySQL分区表
来源:互联网 发布:淘宝一心店铺出售 编辑:程序博客网 时间:2024/04/30 01:20
分区表是一种粗粒度,简易的索引策略,适用于大数据的过滤场景.最适合的场景是,没有合适的索引时,对其中几个分区表进行全表扫描.或者只有一个分区表和索引是热点,而且这个分区和索引能够全部存储在内存中.限制单表分区数不要超过150个,并且注意某些导致无法做分区过滤的细节,分区表对于单条记录的查询没有优势,需要注意这类查询的性能.
分区表语法
分区表分为RANGE,LIST,HASH,KEY四种类型,并且分区表的索引是可以局部针对分区表建立的
创建分区表
CREATE TABLE sales ( id INT AUTO_INCREMENT, amount DOUBLE NOT NULL, order_day DATETIME NOT NULL, PRIMARY KEY(id, order_day)) ENGINE=Innodb PARTITION BY RANGE(YEAR(order_day)) ( PARTITION p_2010 VALUES LESS THAN (2010), PARTITION p_2011 VALUES LESS THAN (2011), PARTITION p_2012 VALUES LESS THAN (2012), PARTITION p_catchall VALUES LESS THAN MAXVALUE);
这段语句表示将表内数据按照order_dy的年份范围进行分区,2010年一个区,2011一个,2012一个,剩下的一个.
要注意如果这么做,则order_day必须包含在主键中,且会产生一个问题,就是当年份超过阈值,到了2013,2014时,需要手动创建这些分区
替代方法就是使用HASH
CREATE TABLE sales ( id INT PRIMARY KEY AUTO_INCREMENT, amount DOUBLE NOT NULL, order_day DATETIME NOT NULL) ENGINE=Innodb PARTITION BY HASH(id DIV 1000000);
这种分区表示每100W条数据建立一个分区,且没有阈值范围的影响
对于大数据而言
对于大数据(如10TB)而言,索引起到的作用相对小,因为索引的空间与维护成本很高,另外如果不是索引覆盖查询,将导致回表,造成大量磁盘IO.那么对于这种情况的解决策略是:
1.全量扫描数据,不要任何索引
通过分区表表达式将数据定位在少量的分区表,然后正常访问这些分区表的数据
2.分离热点,索引数据
将热点数据分离出来在一个小的分区,并对分区建立索引,对热点数据的查询提高效率.
分区表的问题
1.NULL值使分区过滤无效
假设按照RANGE YEAR(order_date)分区,那么如果这个表达式计算出来的时NULL值,记录就会被存放到第一个分区.所以在查询时加入查询条件有可能出现NULL值,那么就会去检查第一个分区.解决的方法可以是将第一个分区建立为NULL分区 PARTITION p_nulls VALUES LESS THAN (0),或者在MySQL5.5以后,直接使用COLUMN建立分区 PARTITION BY RANGE COLUMNS(order_date)
2. 选择分区的成本
每插入一行数据都需要按照表达式筛选插入的分区地址
3. 分区列和索引列不匹配
如果索引列和分区列不匹配,且查询中没有包含过滤分区的条件,会导致无法进行分区过滤,那么将会导致查询所有分区.
4. 打开并锁住所有底层表
分区表的的查询策略是在分区过滤之前,打开并锁住所有底层表,这会造成额外的开销,解决问题的方法是尽量使用批量操作,例如LOAD DATA INFILE,或者一次删除多行数据.
过滤分区表的要点
过滤分区表的WHERE条件必须是切分分区表的列,而不能带有函数,例如只能是order_day,而不能是YEAR(order_day)
- mysql分区表
- Mysql 分区表
- MySQL分区表
- MySQL分区表
- MySQL分区表
- [MySQL] 分区表
- MySQL分区表
- Mysql 分区表
- MySQL分区表
- MySQL分区表
- MySQL 分区表
- mysql分区表
- MySQL 分区表
- MySQL分区表
- MySQL分区表
- MySQL 分区表
- Mysql分区表
- MySQL 分区表
- 介绍const
- 毛刺修剪
- Java线程详解
- 巴什博弈
- 【Android Native Code开发系列】六 一个Native Service的完整示例
- MySQL分区表
- Web前端性能优化(五)网站样式和脚本
- 【Talk is cheap. Show me the code! - Linus Torvalds】
- Linux驱动开发之 五 (那些必须要了解的硬件知识 之 I2C)
- 对照Java学习Swift--构造过程(Initialization)
- Windows驱动开发(7) - DEVICE_OBJECT结构体
- 最长子序列问题(时间复杂度O(nlog(n))
- Linux下用户组、文件权限详解
- Class Methods & Variables