`
Tzhennan
  • 浏览: 58132 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

mysql的分区和分表

 
阅读更多

分区

分区就是把一个数据表的文件和索引分散存储在不同的物理文件中。

 

mysql支持的分区类型包括Range、List、Hash、Key,其中Range比较常用:

RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区。

LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

 

案例:

建立一个user 表 以id进行分区 id 小于10的在user_1分区id小于20的在user_2分区

create table user(

    id int not null auto_increment,

    username varchar(10),

    primary key(id)

)engine = innodb charset=utf8

partition by range (id)(

partition user_1 values less than (10),

partition user_2 values less than (20)

);

建立后添加分区:

maxvalue 表示最大值   这样大于等于20的id 都出存储在user_3分区

alter table user add partition(

    partition user_3 values less than maxvalue

);

 

删除分区:

alter table user drop partition user_3;

 

现在打开mysql的数据目录

可以看见多了user#P#user_1.ibd 和user#P#user_2.ibd  这两个文件

 

如果表使用的存储引擎是MyISAM类型,就是:

user#P#user_1.MYD,user#P#user_1.MYI和user#P#user_2.MYD,user#P#user_2.MYI

 

分区模式详解:

 

* Range(范围) – 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区,80年代(1980's)的数据,90年代(1990's)的数据以及任何在2000年(包括2000年)后的数据。

CREATE TABLE users (  

       id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  

       usersname VARCHAR(30) NOT NULL DEFAULT '',  

       email VARCHAR(30) NOT NULL DEFAULT ''  

)  

PARTITION BY RANGE (id) (  

       PARTITION p0 VALUES LESS THAN (3000000),  

      

       PARTITION p1 VALUES LESS THAN (6000000), 

     

       PARTITION p2 VALUES LESS THAN (9000000),  

     

       PARTITION p3 VALUES LESS THAN MAXVALUE     

);  

在这里,将用户表分成4个分区,以每300万条记录为界限,每个分区都有自己独立的数据、索引文件的存放目录。

 

还可以将这些分区所在的物理磁盘分开完全独立,可以提高磁盘IO吞吐量。

CREATE TABLE users (  

       id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  

       usersname VARCHAR(30) NOT NULL DEFAULT '',  

       email VARCHAR(30) NOT NULL DEFAULT ''  

)  

PARTITION BY RANGE (id) (  

       PARTITION p0 VALUES LESS THAN (3000000)  

       DATA DIRECTORY = '/data0/data'  

       INDEX DIRECTORY = '/data0/index',  

  

       PARTITION p1 VALUES LESS THAN (6000000)  

       DATA DIRECTORY = '/data1/data'  

       INDEX DIRECTORY = '/data1/index',  

  

       PARTITION p2 VALUES LESS THAN (9000000)  

       DATA DIRECTORY = '/data2/data'  

       INDEX DIRECTORY = '/data2/index',  

  

       PARTITION p3 VALUES LESS THAN MAXVALUE     

       DATA DIRECTORY = '/data3/data'   

       INDEX DIRECTORY = '/data3/index'  

);  

 

 

* List(预定义列表) – 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如:DBA根据用户的类型进行分区。 

CREATE TABLE user (  

     id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  

     name VARCHAR(30) NOT NULL DEFAULT '' ,

     user_type   int not null

)  

PARTITION BY LIST (user_type ) (  

     PARTITION p0 VALUES IN (0,4,8,12) , 

     PARTITION p1 VALUES IN (1,5,9,13) ,  

     PARTITION p2 VALUES IN (2,6,10,14),  

     PARTITION p3 VALUES IN (3,7,11,15)   

);

分成4个区,同样可以将分区设置的独立的磁盘中。

 

* Key(键值)

CREATE TABLE user (  

     id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  

     name VARCHAR(30) NOT NULL DEFAULT '',  

     email VARCHAR(30) NOT NULL DEFAULT ''  

)  

PARTITION BY KEY (id) PARTITIONS 4 (  

     PARTITION p0,  

     PARTITION p1,  

     PARTITION p2,  

     PARTITION p3

);

 

* Hash(哈希)

CREATE TABLE user (  

     id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  

     username VARCHAR(30) NOT NULL DEFAULT '',  

     email VARCHAR(30) NOT NULL DEFAULT ''  

)  

PARTITION BY HASH (id) PARTITIONS 4 (  

     PARTITION p0 ,  

     PARTITION p1,  

     PARTITION p2,

     PARTITION p3  

);

 

 

分表

分表和分区类似,区别是,分区是把一个逻辑表文件分成几个物理文件后进行存储,而分表则是把原先的一个表分成几个表。进行分表查询时可以通过union或者视图。

分表又分垂直分割和水平分割,其中水平分分割最为常用。水平分割通常是指切分到另外一个数据库或表中。例如对于一个会员表,按对3的模进行分割:

 

table = id%3

如果id%3 = 0 则将用户数据放入到user_0表中,如id%3=1就放入user_1表中,依次类推。

在这里有个问题,这个uid应该是所有会员按序增长的,可他是怎么得到的呢?使用auto_increment是不行的,这样就用到序列了。

对于一些流量统计系统,其数据量比较大,并且对过往数据的关注度不高,这时按年、月、日进行分表,将每日统计信息放到一个以日期命名的表中;或者按照增量进行分表,如每个表100万数据,超过100万就放入第二个表。还可以按Hash进行分表,但是按日期和取模余数分表最为常见,也容易扩展。

 

分表后可能会遇到新的问题,那就是查询,分页和统计。通用的方法是在程序中进行处理,辅助视图。

分享到:
评论

相关推荐

    MySQL分区和分表技术总结.docx

    MySQL 分表和分区的关系 1. 都能提高 MySQL 的性能,在高并发状态下都有一个良好的表现。 2. 分表和分区不矛盾,可以相互配合的,对于那些大访问量,并且表数据比较多的表,我们可以采取分表和分区结合的方式。 3. ...

    MySQL 分区和分表的干货总结.doc

    MySQL 分区和分表的干货总结.doc

    MySQL分区分表方案实践手册

    总之,《MySQL分区分表方案实践手册》将引导读者全面了解MySQL分区的理论与实践,通过实例解析和最佳实践分享,帮助读者在面对大数据挑战时,能够有效利用分区技术提升数据库性能,实现高效的数据管理。

    mysql分库分表分区1

    MySQL 数据库在处理大数据量时,为了提高查询性能和管理效率,通常会采用分库分表的技术。这里的“mysql分库分表分区1”主要讨论的是 MySQL 中的表分区技术,这是一种将大表物理分割为多个更小、更易管理的部分的...

    MySQL分表和分区最佳攻略word

    MySQL分表和分区最佳攻略 word版本,

    mysql分区

    “mysql分区与分表.docx”可能包含了更深入的MySQL分区和分表技术的细节,包括如何创建和管理分区,以及各种分区策略的案例分析。建议查阅该文档以获取更全面的信息。 综上所述,MySQL分区是一种强大的数据库管理...

    一个MySQL分库分表php类

    当一个表数据记录过大时就会出现性能瓶颈,而一般对应的解决办法是要么做分区表,要么分表,分区表就不说了,分表又分为垂直分割和水平分割,具体区 别请自行搜索。一般而言,分库分表属于水平分割,按照一定的规则...

    MySQL分表和分区最佳攻略

    常开发中我们经常会遇到大表的情况,所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大,导致...分表和表分区的目的就是减少数据库的负担,提高数据库的效率,通常点来讲就是提高表的增删改查效率。

    MySQL分区分表的设计及实现-收藏备用.pdf

    ### MySQL分区分表的设计及实现 #### 一、引言 随着信息技术的飞速发展,数据量呈现爆炸式增长,海量数据的管理和处理成为了一项挑战。尤其在关系型数据库中,如MySQL这样的系统,当单个表的数据量达到一定规模后...

    MySQL 分库分表的实现原理及演示案例

    MySQL分库分表是一种数据库架构优化技术,其目的是为了提高大型数据库系统的性能和可扩展性。在面对大规模数据和高并发访问时,单库单表往往难以满足需求,分库分表就成为了必要之选。分库分表技术可以将数据分散...

    什么是分表和分区 MySql数据库分区和分表方法

    MySQL数据库的分表和分区是两种常见的优化策略,旨在应对大数据量带来的性能问题。当一个表包含数百万甚至上千万的记录时,查询和插入操作可能会变得非常缓慢,导致整个数据库系统的性能下降。分表和分区的目标是...

    mysql分表创建

    本文将详细讲解如何在MySQL中进行分表创建,以及相关的源码和工具应用。 首先,分表(Sharding)是数据库水平扩展的一种解决方案,通过将一个大表的数据分散到多个物理表上,从而降低单表的数据量,提高查询效率。...

    互联网公司为啥不使用mysql分区表

    在互联网公司中,mysql分区表是一个比较少用的技术,这是因为分区表存在一些缺陷和限制,使得互联网公司更多地选择自己分库分表来水平扩展数据库。 首先,分区表的设计需要考虑到分区键的设计,不同的分区键设计会...

    MySQL分库分表无限扩容后的瓶颈及解决方案.docx

    本文将讨论 MySQL 分库分表无限扩容后的瓶颈及解决方案,旨在帮助开发者和架构师更好地掌握 MySQL 的核心技术,并提高数据库的性能和容量。 知识点: * MySQL 分库分表无限扩容后的瓶颈 * 解决方案:横向扩展、...

    一个MySQL分库分表php类.zip

    当一个表数据记录过大时就会出现性能瓶颈,而一般对应的解决办法是要么做分区表,要么分表,分区表就不说了,分表又分为垂直分割和水平分割,具体区 别请自行搜索。一般而言,分库分表属于水平分割,按照一定的规则...

    zabbix 5.0 mysql 数据库分表操作详细流程

    其中有每个命令的使用说明与注释。并增加了清理数据的方式。以及导入输入的注意事项

    mysql分表和分区的区别浅析

    MySQL中的分表和分区是两种不同的数据库优化策略,它们都在一定程度上解决了大数据量下的查询效率问题,但原理和实现方式有所不同。 **一、分表** 分表,顾名思义,是将一张大表分解为多张小表,每个小表都是独立...

Global site tag (gtag.js) - Google Analytics