mysql 表分区技术

，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。

表分区有什么好处： a.分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。
b.和单个磁盘或者文件系统相比，可以存储更多数据
c.优化查询。在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效率；涉及sum和count语句时，也可以在多个分区上并行处理，最后汇总结果。
d.分区表更容易维护。例如：想批量删除大量数据可以清除整个分区。
e.可以使用分区表来避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问，ext3问价你系统的inode锁竞争等。

   今天，我通过查阅相关资料与动手操作，学习了一下数据库表分区的技术。个人理解，其实就是当有大数据的数据表时，将数据表中的数据按照一定的规则，分门别类存储到规定的区域空间，
   如果要对表进行“增删改查”的操作时，执行操作的区域不会是整张表，而是该表中的某个区域，实际就是“以空间换时间”，无疑会提高执行效率。

  分区表的限制因素

  a.一个表最多只能有1024个分区

  b.MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。

  c.如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。

  d.分区表中无法使用外键约束

  e.MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。

  如何判断当前MySQL是否支持分区

  命令：show variables like ‘%partition%‘

    

   其中 Variable_name 的Value = 1,我测试过了，表示可以正常分区的。我数据库的版本是：

  

    MySQL支持的分区类型有：RANGE分区，LIST分区，HASH分区，KEY分区。其中RANGE,LIST,HASH分区等一般使用Int类型，KEY分区使用BLOB，TEXT类型等。

  接下来创建数据表
 
  紧接着：
  
   以上的报错，说明partition by 不能够单独的使用（stand-alone）.此处要注意！
   然后采取第二种用法，如图所示：  
  

     
    分区成功了，呵呵呵！   

 有几点注意：
 a. 对于分区s1,表示 1 <= id < 10;对于分区s2,表示 10<= id < 20;对于分区s3，表示 20<= id < 30；对于分区s4,表示 id >= 30,无上限                                  
 b. 如果将less than(10) 和less than (20)的顺序颠倒过来，那么将报错,如： VALUES LESS THAN value must be strictly increasing for each partition，
    所以也用注意顺序问题                                                                                                       
 c. 一个表最大分区为：1024.(上面已经提到过)，在有限的表分区内，最后加上 ：partition xxx values less than maxvalue,是很有必要的。 
 d. 不管哪种分区类型，分区键必须是主键或唯一键，除非两者都没有，否者将会报如下错误。

如果是将注册日期作为分区键，则须要使用日期处理函数转换为整型，例如year(regDate),to_days(regDate),to_seconds(regDate),且只支持这三个函数。

或者使用RANGE COLUMNS分区，则不需要转换日期，如下所示

create table users_par(
    id int not null,
    usrName varchar(50) not null,
    usrEmail varchar(50) not null,
    age int not null,
    regDate date not null
    partition by range columns(regDate)(
    partition p0 values less than(‘2005-05-05‘),
    partition p1 values less than(‘2009-09-09‘),
    partition p2 values less than(‘2015-05-05‘),
);

RANGE分区特别适用于删除过期数据或者某范围数据，只需要alter table tbl_name truncate partition partition_name即可，
比delete语句效率要高很多，还有就是经常使用分区键的查询，可以提高查询性能，因为只需扫描某些分区就OK

Error Code: 1503. A PRIMARY KEY must include all columns in the table‘s partitioning function

 接下来，我们查看，我们建立的分区，相关的语法：EXPLAIN PARTITIONS SELECT * FROM `demo`     当然,我们需要做测试，“实践是检验真理的唯一标准”嘛。        语法： explain partitions sql语句，如

• < 上一篇

  MongoDB ObjectId

• 下一篇 >

  SQL Server2012 T-SQL基础教程--读书笔记