臧富祥 ^回到顶部

您的当前位置:首页 > 最新IT资讯 > MySQL > mysql优化-分库分表-分区

mysql优化-分库分表-分区

所属分类: MySQL   2020-03-18 16:21:04  编辑:admin  浏览次数 747 次

垂直拆封

拆分原则:当遇到一个表中某些列不常用,某些列常用,则进行垂直拆分或者:遇到大数据量的字段,比如文章表的内容text放在一个表,或者不常用的放在一起,这样会避免在查询的时候该表其他结构时,进行临时表操作。

image.png

image.png

image.png

image.png

缺点:全关联,需要连表查询join

优点:查询常用的字段,不需要创建临时表


水平拆分

场景:


image.png

image.png

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;

image.png

限制:一个表最多只能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脚本中创建临时表,临时表只对当前连接可见,脚本执行完毕,临时表自动销毁;

随笔文章检索

随笔文章目录