mysql分表最佳实践-MySQL分区表的最佳实践指南.pdfVIP

mysql分表最佳实践_MySQL分区表的最佳实践指南 前⾔： 分区是⼀种表的设计模式，通俗地讲表分区是将⼀⼤表，根据条件分割成若⼲个⼩表。但是对于应⽤程序来讲，分区的表和没有分区的表是 ⼀样的。换句话来讲，分区对于应⽤是透明的，只是数据库对于数据的重新整理。本篇⽂章给⼤家带来的内容是关于MySQL中分区表的介 绍及使⽤场景，有需要的朋友可以参考⼀下，希望对你有所帮助。 1.分区的⽬的及分区类型 MySQL在创建表的时候可以通过使⽤PARTITION BY⼦句定义每个分区存放的数据。在执⾏查询的时候，优化器根据分区定义过滤那些没 有我们需要的数据的分区，这样查询就可以⽆需扫描所有分区，只需要查找包含需要数据的分区即可。 分区的另⼀个⽬的是将数据按照⼀个较粗的粒度分别存放在不同的表中。这样做可以将相关的数据存放在⼀起，另外，当我们想要⼀次批量 删除整个分区的数据也会变得很⽅便。 下⾯简单介绍下四种常见的分区类型： RANGE分区：最为常⽤，基于属于⼀个给定连续区间的列值，把多⾏分配给分区。最常见的是基于时间字段。 LIST分区：LIST分区和RANGE分区类似，区别在于LIST是枚举值列表的集合，RANGE是连续的区间值的集合。 HASH分区：基于⽤户定义的表达式的返回值来进⾏选择的分区，该表达式使⽤将要插⼊到表中的这些⾏的列值进⾏计算。这个函数可以包 含MySQL中有效的、产⽣⾮负整数值的任何表达式。 KEY分区：类似于按HASH分区，区别在于KEY分区只⽀持计算⼀列或多列，且MySQL服务器提供其⾃⾝的哈希函数。必须有⼀列或多列 包含整数值。 上述四种分区类型中，RANGE分区即范围分区是最常⽤的。RANGE分区的特点是多个分区的范围要连续，但是不能重叠，默认情况下使⽤ VALUES LESS THAN属性，即每个分区不包括指定的那个值。 2.分区操作⽰例 本节内容以RANGE分区为例，介绍下分区表相关的操作。 # 创建分区表 mysql CREATE TABLE `tr` ( - `id` INT, - `name` VARCHAR(50), - `purchased` DATE - ) ENGINE=InnoDB DEFAULT CHARSET=utf8 - PARTITION BY RANGE( YEAR(purchased) ) ( - PARTITION p0 VALUES LESS THAN (1990), - PARTITION p1 VALUES LESS THAN (1995), - PARTITION p2 VALUES LESS THAN (2000), - PARTITION p3 VALUES LESS THAN (2005), - PARTITION p4 VALUES LESS THAN (2010), - PARTITION p5 VALUES LESS THAN (2015) - ); Query OK, 0 rows affected (0.28 sec) # 插⼊数据 mysql INSERT INTO `tr` VALUES - (1, desk organiser, 2003-10-15), - (2, alarm clock, 1997-11-05), - (3, chair, 2009-03-10), - (4, bookcase, 1989-01-10), - (5, exercise bike, 2014-05-09), - (6, sofa, 1987-06-05), - (7, espresso maker, 2011-11-22), - (8, aquarium, 1992-08-04), - (9, study desk, 2006-09-16), - (10, lava lamp, 1998-12-25); Query OK, 10 rows affected (0.03 sec) Records: 10 Duplicates: 0 Warnings: 0 创建后可以看到，每个分区都会对应1个ibd⽂件。上⾯创建语句还是很好理解的，在此分区表中，通过YEAR函数取出DATE⽇期中的年份 并转化为整型，年份⼩于1990的存储在分区p0中，⼩于1995的存储在分区p1中，以此类推。请注意，每个分区的定义顺序是从最低到最 ⾼。为了防⽌插⼊的数据因找不到相应分区⽽报错，我们应该及时创建新的分区。下⾯继续展⽰关于分区维护的其他操作。 # 查看某个分区的数据 mysql SELECT * FROM tr PARTITION (p2); ++++ |