mysql-合并表、临时表、分区表

合并表

把多个结果相同的表合并为一个容器。

**表类型：**Myisam

**存储引擎：**merg

**合并关键字：**union

Create table packtable (
Id int not null primary key
) engine=merge union=(table1,table2)

实践

临时表

分类

​ 内存临时表：memory引擎。数据量比较小的时候。

​ 磁盘临时表：myIsam引擎。数据量大的时候。

临时表参数条件

​ Text类型、blob类型；

​ Groupby、distinct子句中大于512byte的列；

​ Union、union all,select子句大于512byte的列；

使用临时表

​ 在 select查询中指定sql_small_result

​ Order by 使用了dinstinct

​ 使用Join查询的时候，如果group by的列不是第一个表的列

使用场景

• 有大量的数据表的关联查询；

• 能够在数据量大的表中筛选记录；

• 处理数据、只需要某一些符合条件的数据；

• 数据会被很快销毁的场景；（临时购物车）

临时表配置

​ Tmp_table-size ：系统创建 ，临时表最大大小

​ max_heap_table_size：用户创建的，临时表最大大小 怎么避免临时表

​ 创建索引：在group by、order by的列创建索引；

​ 拆分列、text字段一般单独放在另一张表或者不放在查询条件里；

创建/删除临时表

-- 创建临时表
create temporary table test1{
	id int(10) not null,
	username varchar(50)
}
-- 注意：PHP脚本中创建临时表，临时表只对当前链接可见，脚本执行完毕，临时表自动销毁

-- 手动删除临时表
drop table test1;

问题1：如何合并2张表？

union 自动去重 select * from test1 union select * from test2;

union all 不会去重，字段数量，类型必须相同；

问题2：a表和b表合并（使用临时表）

-- 方法1
create temporary table test1{
	id int(10) null null,
	username varchar(50)
};

insert into test1 select * from test1 union select * from test2;

-- 方法2
create temporary table test2 select * from test1;

问题3：a表和b表合并到c表，数据量大的情况

-- a表 b表数据插入临时表
-- 在临时表中创建临时索引
create index union_a on test1(c1,c2,c3);

分区表

​ 创建分区表的每个分区都是有索引独立表

​ 目的：

​ 物理数据库涉及技术、让某些特定的查询减少响应时间。

​ 关键字：

​ partition

​ 分区类型：

​ 水平分区：把数据行分到不同的表中

​ 垂直分区：把数据字段分离到不同的表中

​ 水平分区

• RANGE分区：连续的空间

  -- 分为3个区 p1 p2 p3
  -- less是小于的意思
  create table test1(
  	id int(10) not null,
      score int(3)
  ) partition by range(score)(
  	partition p1 values less than(60),
      partition p2 values less than(80),
      partition p3 values less than maxvalue
      -- maxvalue 表示其他的都在p3表中 这个特性mysql5.1+才有
  )
  
  -- 插入到p1分区
  insert into test1 values(1,50);
  
  -- 查询 和正常一样
  select count(*) from test1 where score<60;
  

• LIST分区：定义和选择是基于某一列的值是否属于某个集合。

  -- 分为2个区 p1 p2
  -- dept_no的值为3或8分到p1，dept_no的值为7分到p2
  create table test1(
  	id int(10) not null,
      dept_no int(3)
  ) partition by list(dept_no)(
      partition p1 values in(3,8),
      partition p2 values in(7)
      -- mysql>=5.5 value in(集合)的值支持非整型数据
  );
  

• HASH分区：只支持数值类型,使用的哈希函数的模

  -- 根据生日的月份分区
  -- hash(mysql系统支持的表达式)
  create table test1(
  	id int(10) not null,
      score int(3),
      birthday date
  ) partition by hash(month(birthday))
  Partitions 12;
  -- Partitions 12 表示根据12去取模，分为12个分区
  

• 线性hash分区：使用2的幂运算法则

  线性hash分区的好处：当数据量大于1000G时，对分区的增加、合并、拆分、删除会变得更加快捷。

  线性Hash分区的缺点：数据分布不均匀。

  -- 根据生日的月份分区
  create table test1(
  	id int(10) not null,
      score int(3),
      birthday date
  ) partition by linear hash(month(birthday))
  partitions 12;
  -- linear hash 线性哈希写法
  -- 详情说明https://blog.csdn.net/qq_36877577/article/details/64920946
  

• KEY分区（只计算一列或者多列）：支持除了text和blob(二进制)以外的其他类型。

  create table test1(
  	id int(10) not null,
      score int(3),
      birthday date,
      course varchar(25)
  ) partition by key(course)
  partitions 5;
   
  

• 复合分区：

   -- 对原先的表进行直接修改，多个分区规则加上。前面的表的修改和创建语法基本相同
   alter table news_main PARTITION by list(news_class) SUBPARTITION by hash(year(news_adddate)) SUBPARTITIONS 3
    (
      PARTITION class_java values in(1) , 
      PARTITION class_php values in(2),
      PARTITION class_python values in(3)
    )