E6 数据库分区技术

E6 数据库分区技术

• 根据需求实施分区技术。分区表是一个单独的逻辑表，但是底层由多个物理子表组成。分区表是使用分区技术的表，分区技术将以前作为整体存储的表划分为更小、更易于管理的子表，这些子表可以单独存放在磁盘上。MySQL数据库支持的分区类型是水平的，而不是垂直的。水平分区是指将同一表中不同行的记录分配给不同的物理文件。

• 注意事项:
  1、 如果分区表具有惟一索引或主键，则分区列必须是惟一索引中包含的列。
  2.、外键约束不能用于分区表。

• Problem 1：检查数据库是否支持分区技术
  在5.6版本下面使用以下语句:
  show variables like '% partition % \ g
  以上5.6 +版本使用此语句
  show plugins \ g

• Problem 2 设计一个RANGE分区：
  RANGE分区是基于给定范围的连续体，将数据分布到不同的分区，是最常用的分区类型之一,分区是连续的，而不是重叠的。在实际应用场景中，范围分区主要用于对日期列进行分区，例如：对于sales类表，可以根据年份对销售记录进行分区。

  create table sales( money int unsigned not null, date datetime )
  partition by range(year(date))( partition p2008 values less than
  (2009), partition p2009 values less than (2010), partition p2010
  values less than (2011) );
  //分为三个区，2009年以前为P2008分区，2010年以前为P2009分区，2011年以前为P2010分区 insert into
  sales select 100,‘2008-01-01’; insert into sales select
  100,‘2008-02-01’; insert into sales select 100,‘2008-01-02’; insert
  into sales select 100,‘2009-03-01’; insert into sales select
  100,‘2010-03-01’; //向表格中插入数据

  查看分区信息：

  select table_name,partition_name,table_rows from
  information_schema.PARTITIONS where table_schema=database() AND
  table_name=‘sales’;

  通过分区特性优化查询:

  explain select * from sales where date>=‘2008-01-01’ AND
  date<=‘2008-12-31’;

  

• Problem 3如果您想为一年中的每个月创建一个分区表：

  create table sales_b( money int unsigned not null, date datetime )
  partition by range(to_days(date))( //设置以日期中的月为分区 partition p201001
  values less than (to_days(‘2010-02-01’)), partition p201002 values
  less than (to_days(‘2010-03-01’)), partition p201003 values less than
  (to_days(‘2010-04-01’)) ); insert into sales_b select
  100,‘2010-01-01’; insert into sales_b select 100,‘2010-01-21’; insert
  into sales_b select 100,‘2010-02-01’; insert into sales_b select
  100,‘2010-03-01’;

  查看分区信息：

  select table_name,partition_name,table_rows from
  information_schema.PARTITIONS where table_schema=database() AND
  table_name=‘sales_b’;

  通过分区特性优化查询:

  explain select * from sales_b where date>=‘2010-02-01’ AND
  date<=‘2010-02-26’;

  

• Problem 4
  列表分区与范围分区非常相似，不同之处在于列表分区列是离散值，而范围分区列是连续值。与范围分区中定义的值小于语句不同，列表分区使用值IN。因为每个分区的值都是离散的，所以只能定义值。
  //创建一个LIST分区表

  create table l1( a int, b int ) partition by
  list(b)(//设置列表分区，当值为1,3,5,7,9其中之一时，属于p0分区；当值为0,2,4,6,8时属于p2分区。
  partition p0 values in (1,3,5,7,9), partition p1 values in
  (0,2,4,6,8,10) ); select * from l1 where b=1;

  //在表中插入数据

  insert into l1 select 1,1; insert into l1 select 1,2; insert into l1
  select 1,3; insert into l1 select 1,4;

  //查看分区信息

  select table_name,partition_name,table_rows from
  information_schema.PARTITIONS where table_schema=database() AND
  table_name=‘l1’;

  

• Problem 5:
  哈希分区在预定义分区之间尽可能均匀地分布数据，确保每个分区拥有大致相同的数据量。HSAH分区的分区列可以是列值或返回整数的表达式。键和哈希分区的区别在于，哈希分区可以使用自定义函数进行分区，而键分区只能使用数据库提供的内部哈希函数进行分区。哈希分区只支持整数分区，而键分区支持使用BLOB或文本以外的任何类型的列作为分区列。

  create table hash_a( a int, b datetime ) partition by hash(year(b))
  partitions 4;

  //插入数据

  insert into hash_a select 1,‘2010-04-01’; insert into hash_a select
  1,‘2015-05-01’; insert into hash_a select 1,‘2013-05-01’; insert into
  hash_a select 1,‘2013-05-01’; insert into hash_a select
  1,‘2013-05-01’; insert into hash_a select 1,‘2013-05-01’; insert into
  hash_a select 1,‘2013-05-01’; insert into hash_a select
  1,‘2013-05-01’; insert into hash_a select 1,‘2012-05-01’; insert into
  hash_a select 1,‘2013-05-01’; insert into hash_a select
  1,‘2013-05-01’; insert into hash_a select 1,‘2012-05-01’; insert into
  hash_a select 1,‘2013-05-01’;

  //查看插入数据保存在哪个分区

  select table_name,partition_name,table_rows from
  information_schema.PARTITIONS where table_schema=database() AND
  table_name=‘hash_a’;
  create table key_a( a int, b datetime ) partition by key(b) partitions 4;

  
  

• Problem 6: 不分区与分区的比较
  1.建表，插入数据
  #不分区的表

  CREATE TABLE no_part_tab (id INT DEFAULT NULL, remark VARCHAR(50)
  DEFAULT NULL, d_date DATE DEFAULT NULL )ENGINE=MYISAM;

  #分区的表

  CREATE TABLE part_tab (id INT DEFAULT NULL, remark VARCHAR(50) DEFAULT
  NULL, d_date DATE DEFAULT NULL ) PARTITION BY RANGE(YEAR(d_date))(
  PARTITION p0 VALUES LESS THAN(1995), PARTITION p1 VALUES LESS
  THAN(1996), PARTITION p2 VALUES LESS THAN(1997), PARTITION p3 VALUES
  LESS THAN(1998), PARTITION p4 VALUES LESS THAN(1999), PARTITION p5
  VALUES LESS THAN(2000), PARTITION p6 VALUES LESS THAN(2001), PARTITION
  p7 VALUES LESS THAN(2002), PARTITION p8 VALUES LESS THAN(2003),
  PARTITION p9 VALUES LESS THAN(2004), PARTITION p10 VALUES LESS THAN
  maxvalue);

  #插入未分区表记录

  DROP PROCEDURE IF EXISTS no_load_part; DELIMITER // CREATE PROCEDURE
  no_load_part() BEGIN
  DECLARE i INT;
  SET i =1;
  WHILE i<100000
  DO
  INSERT INTO no_part_tab VALUES(i,‘no’,ADDDATE(‘1995-01-01’,(RAND(i)*36520) MOD 3652));
  SET i=i+1;
  END WHILE; END// DELIMITER ; CALL no_load_part;

  #插入分区表记录

  DROP PROCEDURE IF EXISTS load_part DELIMITER // CREATE PROCEDURE
  load_part() BEGIN
  DECLARE i INT;
  SET i=1;
  WHILE i<100000
  DO
  INSERT INTO part_tab VALUES(i,‘partition’,ADDDATE(‘1995-01-01’,(RAND(i)*36520) MOD 3652));
  SET i=i+1;
  END WHILE; END// DELIMITER ; CALL load_part;

  
  
  2.测试SQL

  SELECT COUNT() FROM no_part_tab WHERE d_date > DATE ‘1995-01-01’ AND
  d_date< DATE ‘1995-12-31’; SELECT COUNT() FROM part_tab WHERE d_date
  DATE ‘1995-01-01’ AND d_date< DATE ‘1995-12-31’;

  

• Problem 7: 综合实验
  一个需要存放100车辆的GPS信息的表格，包括车牌号、驾驶员编号、驾驶员姓名、时间（精确到秒）、经度、维度、速度，每台车每天需要保存的记录数量大约为350条，应用需要保存和查询近一个月的每台车的轨迹。（约1万条）
  (1) 请分别设计存储数据无分区和具有分区的表格，并在Mysql上实现；
  (2) 请分别编写一个存储过程为50台车存储1个月合理的轨迹数据；（insert）
  (3) 请设计存储过程，根据车辆牌照和起止时间查询轨迹数据，并给出实际的查询时间并分析。（select）
  （1）建表
  //无分区

  create database wheel_log; use wheel_log; create table
  no_part_cartb(id int(11) DEFAULT NULL,driverId int DEFAULT NULL,name
  varchar(30) DEFAULT NULL, d_date date DEFAULT NULL,longitude double
  DEFAULT NULL,latitude double DEFAULT NULL,velocity int DEFAULT NULL,
  remark varchar(50) DEFAULT NULL) ENGINE=myisam;

  //有分区

  use wheel_log; create table part_cartb(id int(11) DEFAULT
  NULL,driverId int DEFAULT NULL,name varchar(30) DEFAULT NULL, d_date
  date DEFAULT NULL,longitude double DEFAULT NULL,latitude double
  DEFAULT NULL,velocity int DEFAULT NULL, remark varchar(50) DEFAULT
  NULL) partition by range(to_days(d_date))( partition p1 values less
  than (to_days(‘2018-02-01’)), partition p2 values less than
  (to_days(‘2018-03-01’)), partition p3 values less than
  (to_days(‘2018-04-01’)), partition p4 values less than
  (to_days(‘2018-05-01’)), partition p5 values less than
  (to_days(‘2018-06-01’)), partition p6 values less than
  (to_days(‘2018-07-01’)), PARTITION p7 VALUES LESS THAN maxvalue);

  
  
  （2）插入数据：构造两个存储过程分别动态产生50台车的50万条数据，插入到以上两个表格中。
  
  
  （3）根据自己设定的条件对两个表格查询数据，并进行比较。