mysql优化-分库分表-分区
2020-03-18 16:21:04 来源：admin 点击：990
垂直拆封
拆分原则：当遇到一个表中某些列不常用，某些列常用，则进行垂直拆分或者:遇到大数据量的字段，比如文章表的内容text放在一个表，或者不常用的放在一起，这样会避免在查询的时候该表其他结构时，进行临时表操作。
缺点：全关联，需要连表查询join
优点：查询常用的字段，不需要创建临时表
水平拆分
场景：
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;
限制：一个表最多只能1024个分区，分区字段如果有主键和唯一字段，必须包含进来，分区表种无法使用外键约束、所有分区都必须是相同的存储引擎、
查询所有数据的时候得用union操作
合并表
把多个结果相同的表合并成为一个容器。
表类型：Myisam 存储引擎：merge
合并：union
Create table packtable (
Id int not null primary key
) engine=merge union=(table1,table2)
存在的问题：有重复的行
Union 自动去重 select * from test1 union select * from test2;
union all 不会去重，字段数量、类型必须相同
临时表（内存临时表、磁盘临时表）：
有大量的数据表的关联查询；
能够在数据量大的表中筛选记录；
处理数据只需要某一些符合条件的数据；
数据会被很快销毁的场景。
使用临时表：
在 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脚本中创建临时表，临时表只对当前连接可见，脚本执行完毕，临时表自动销毁；