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）根據自己設定的條件對兩個表格查詢資料，並進行比較。