MySQL优化_合并表和分区表教程
1、合并表和分区表
1.1、合并表：把多个结果相同的表合并成为一个容器。
表类型：Myisam 存储引擎：merge
合并：union
Create table packtable (
Id int not null primary key
) engine=merge union=(table1,table2)
存在的问题：有重复的行
临时表（内存临时表、磁盘临时表）：
使用场景：
有大量的数据表的关联查询；
能够在数据量大的表中筛选记录；
处理数据只需要某一些符合条件的数据；
数据会被很快销毁的场景。
使用临时表：
在 select查询中指定sql\_small\_result
Order by 使用了dinstinct
使用Join查询的时候，如果group by的列不是第一个表的列
直接使用磁盘临时表：
Text类型、blob类型；
Groupby、ditinct子句中大于512byte的列
Union、union all,select子句大于512byte的列
临时表的配置：
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: 如何合并两张表?
Union 自动去重 select * from test1 union select * from test2;
union all 不会去重，字段数量、类型必须相同
问题2：a表和b表合并（使用临时表）?
CREATE TEMPORARY TABLE test1 {
Id int(10) not null,
Username varchar(50)
}
Insert into test1 select * from test1 union select * from test2;
=========================================
CREATE TEMPORARY TABLE test2 SELECT * from test1;
业务处理
问题3：a表和b表合并到c表，数据量大的情况
A表、 B表数据插入临时表；
在临时表/中间表中创建联合索引；
Create index union\_a on test1 ( c1,c2,c3);
1.2、分区表(partition)：创建分区表的每个分区都是有索引的独立表
目的：物理数据库设计技术、让某些特定的查询减少响应时间
分区类型：水平分区、垂直分区
水平分区：
RANGE分区：连续的空间 range values less than
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
);
Insert into test1 values(1,50);
Select count(*) from test1 where score <60;
LIST分区：定义和选择是基于某一列的值是否属于某一个集合。
Mysql>=5.5 集合的值支持非整型数据
Create table test1 (
Id int(10) not null,
Score int(3),
) partition by list(dept\_no)(
partition p1 values in(3,8),
partition p2 values in(7),
);
HASH分区(只支持数值类型)：
Create table test1 (
Id int(10) not null,
Score int(3),
Birthday date
) partition by hash(month(Birthday))
Partitions 12;
Mode(6,12) //6 哈希函数的取余
线性hash分区: //2的幂运算
Create table test1 (
Id int(10) not null,
Score int(3),
Birthday date
) partition by linear hash( month(Birthday) )
V=POW(2,ceiling(LOG(2,num))); 计算N
Partitions 12;
线性HASH分区的好处：当数据量大于1000G的时候，对分区的增加、合并、拆分、删除会变得更加快捷。
线性HASH分区的缺点：数据分布不均匀。
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;
复合分区：