mysql 超大数据/表管理技艺

2019-05-23 17:00栏目:IT操作系统

        CREATE TABLE `tmp_sampledata` (
        `id` bigint(20) unsigned NOT NULL,
        `username` varchar(32) NOT NULL DEFAULT ”,
        `passwd` varchar(32) DEFAULT NULL,
        `email` varchar(64) NOT NULL DEFAULT ”,
        `nickname` varchar(32) DEFAULT NULL,
        `siteid` varchar(32) DEFAULT NULL,
        `src` smallint(6) NOT NULL DEFAULT '0′
        ) ENGINE=MyISAM DEFAULT CHARSET=gbk
        PARTITION BY LIST COLUMNS(src)
        SUBPARTITION BY KEY (username,email)
        SUBPARTITIONS 10
        (PARTITION pose VALUES IN (1) ENGINE = MyISAM,
        PARTITION p2736 VALUES IN (2) ENGINE = MyISAM,
        PARTITION p736736 VALUES IN (3) ENGINE = MyISAM,
        PARTITION p3838648 VALUES IN (4) ENGINE = MyISAM,
        PARTITION p842692 VALUES IN (5) ENGINE = MyISAM,
        PARTITION p7575 VALUES IN (6) ENGINE = MyISAM,
        PARTITION p386386 VALUES IN (7) ENGINE = MyISAM,
        PARTITION p62678 VALUES IN (8) ENGINE = MyISAM)?;

  `modifytime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  

前些日子写过1篇有关mysql分表的的稿子,下边包车型大巴话一下什么是数据库分区,以mysql为例。mysql数据库中的数据是以文件的地势存在磁盘上的,暗中认可放在/mysql/data上边(能够由此my.cnf中的datadir来查阅),一张表首要对应着多少个文本,二个是frm存放表结构的,多个是myd存放表数据的,多个是myi存表索引的。要是一张表的数据量太大的话,那么myd,myi就能够变的相当的大,查找数据就能变的异常的慢,那一年我们得以应用mysql的分区功用,在情理司令员这一张表对应的多个文件,分割成许三个小块,这样啊,大家探究一条数据时,就无须任何物色了,只要精通那条数据在哪一块,然后在那壹块找就行了。假若表的多少太大,恐怕二个磁盘放不下,这一年,大家得以把数量分配到不一致的磁盘里面去。
分区的三种艺术
1,横向分区
哪些是横向分区呢?正是横着来分区了,比方来讲澳优(Ausnutria Hyproca)下,要是有100W条数据,分成10份,前10W条数据放到第二个分区,第壹个10W条数据放到首个分区,由此及彼。也正是把表分成了非常,根用merge来分表,有一点点像哦。抽取一条数据的时候,那条数据包括了表结构中的全体字段,也正是说横向分区,并未变动表的组织。
二,纵向分区
什么样是纵向分区呢?就是竖来分区了,比释尊阐明,在规划用户表的时候,开始的时候从不思念好,而把个人的装有音讯都放到了一张表里面去,那样那么些表里面就能够有相当的大的字段,如个人简要介绍,而那么些简单介绍呢,只怕不会有相当多人去看,所以等到有人要看的时候,在去寻找,分表的时候,可以把如此的大字段,分开来。
感到数据库的分区好像是切苹果,到底是横着切呢,如故竖着切,依据个人喜好了,mysql提供的分区属于第叁种,横向分区,并且细分成很两种方法。上面将举个例子表达壹(Wissu)下。

        mysql> explain select id from tsampledata where username='abcdef' and src in (2)G
        *************************** 1. row ***************************
        id: 1
        select_type: SIMPLE
        table: tsampledata
        type: ref
        possible_keys: idx_sampledata_username
        key: idx_sampledata_username
        key_len: 66
        ref: const
ca888会员登录,        rows: 10
        Extra: Using where
        1 row in set (0.00 sec)

----------  

 代码如下

        mysql> explain select * from tmp_sampledata where username = ‘yourusername'G
        *************************** 1. row ***************************
        id: 1
        select_type: SIMPLE
        table: tmp_sampledata
        type: ALL
        possible_keys: NULL
        key: NULL
        key_len: NULL
        ref: NULL
        rows: 74352359
        Extra: Using where
        1 row in set (0.00 sec)

前提:每一个表类型希图了200万条一样的数额。

复制代码

    So,全数的主题材料,又回去了二.上
    测试查询中的模糊找寻
    对于开创好索引的大数据表,一般般的针对性的查询,应该能够满意急需。可是多少查询也许还是不可能经过索引来发挥功能,譬喻查询以 1六三.com 结尾的信箱:

 PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;  

mysql> CREATE TABLE IF NOT EXISTS `user` (  
 ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',  
 ->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',  
 ->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男,1为女',  
 ->   PRIMARY KEY (`id`)  
 -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1  
 -> PARTITION BY RANGE (id) (  
 ->     PARTITION p0 VALUES LESS THAN (3),  
 ->     PARTITION p1 VALUES LESS THAN (6),  
 ->     PARTITION p2 VALUES LESS THAN (9),  
 ->     PARTITION p3 VALUES LESS THAN (12),  
 ->     PARTITION p4 VALUES LESS THAN MAXVALUE  
 -> );  
Query OK, 0 rows affected (0.13 sec)  
  
//插入一些数额  
mysql> INSERT INTO `test`.`user` (`name` ,`sex`)VALUES ('tank', '0')  
 -> ,('zhang',1),('ying',1),('张',1),('映',0),('test1',1),('tank2',1)  
 -> ,('tank1',1),('test2',1),('test3',1),('test4',1),('test5',1),('tank3',1)  
 -> ,('tank4',1),('tank5',1),('tank6',1),('tank7',1),('tank8',1),('tank9',1)  
 -> ,('tank10',1),('tank11',1),('tank12',1),('tank13',1),('tank21',1),('tank42',1);  
Query OK, 25 rows affected (0.05 sec)  
Records: 25  Duplicates: 0  Warnings: 0  
  
//到存放数据库表文件的地点看一下,my.cnf里面有陈设,datadir前边正是 

    下面是骨干的数据结构定义:

Sql代码    www.2cto.com  

mysql> CREATE TABLE IF NOT EXISTS `sub_part` (  
 ->   `news_id` int(11) NOT NULL  COMMENT '新闻ID',  
 ->   `content` varchar(1000) NOT NULL DEFAULT '' COMMENT '音讯内容',  
 ->   `u_id`  int(11) NOT NULL DEFAULT 0s COMMENT '来源IP',  
 ->   `create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '时间'  
 -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8  
 -> PARTITION BY RANGE(YEAR(create_time))  
 -> SUBPARTITION BY HASH(TO_DAYS(create_time))(  
 -> PARTITION p0 VALUES LESS THAN (1990)(SUBPARTITION s0,SUBPARTITION s1,SUBPARTITION s2),  
 -> PARTITION p1 VALUES LESS THAN (2000)(SUBPARTITION s3,SUBPARTITION s4,SUBPARTITION good),  
 -> PARTITION p2 VALUES LESS THAN MAXVALUE(SUBPARTITION tank0,SUBPARTITION tank1,SUBPARTITION tank3)  
 -> );  
Query OK, 0 rows affected (0.07 sec)  
官网说不相同分区内的子分区能够有雷同的名字,不过mysql五.一.50却不行会提醒以下错误
ERROR 1517 (HY000): Duplicate partition name s1

    从深入分析中看出,当依据src(分区表的分区字段)进行询问范围时,被影响的数额(rows)在发生着调换。rows:80意味着着索要对八个分区进行查找。
    革新数据存款和储蓄:另一种分区格式
    既然在总结应用中,最多用的是经过username, email实行数据查询,那么在表存款和储蓄时,应该思考选择username,email实行分区,而不是通过id。由此再度创建分区表,导入数据:

  

翻看一下变量,如若帮衬的话,会有上边的唤醒的。
2,range分区
遵守RANGE分区的表是通过如下一种方法开始展览分区的,每一种分区包蕴那多少个分区表明式的值位于叁个加以的连日区间内的行

复制代码 代码如下:

  `birth` date DEFAULT NULL,  

6,子分区

目录:

表二 InnoDB & PARTITION BY RANGE (year) 

 代码如下

        mysql> set myisam_sort_buffer_size=1048576000;
        Query OK, 0 rows affected (0.00 sec)

表一 InnoDB & PARTITION BY RANGE (id) 

复制代码

复制代码 代码如下:

----------  

[root@BlackGhost mysql-5.1.50]# ./configure --help |grep -A 3 Partition  
 === Partition Support ===  
 Plugin Name:      partition  
 Description:      MySQL Partitioning Support  
 Supports build:   static  
 Configurations:   max, max-no-ndb  

        mysql> explain select * from tmp_sampledata2 where username = ‘yourusername'G
        *************************** 1. row ***************************
        id: 1
        select_type: SIMPLE
        table: tmp_sampledata2
        type: ref
        possible_keys: idx_username
        key: idx_username
        key_len: 66
        ref: const
        rows: 80
        Extra: Using where
        1 row in set (0.00 sec)

 PARTITION p13 VALUES LESS THAN (2009) ENGINE = MyISAM,  

mysql> show variables like "%part%";  
------------------- -------   
| Variable_name     | Value |  
------------------- -------   
| have_partitioning | YES   |  
------------------- -------   
1 row in set (0.00 sec)  

        where username='abcdef'                                                    rows: 80
        where username='abcdef' and src in (2,3,4,5)            rows: 40
        where username='abcdef' and src in (2)                        rows: 10
        where username='abcdef' and src in (2,3)                    rows: 20

| count(*) |  

测试请参谋range分区的操作

        mysql> explain select id from tsampledata where username='abcdef' and src in (2,3,4,5)G
        *************************** 1. row ***************************
        id: 1
        select_type: SIMPLE
        table: tsampledata
        type: ref
        possible_keys: idx_sampledata_username
        key: idx_sampledata_username
        key_len: 66
        ref: const
        rows: 40
        Extra: Using where
        1 row in set (0.01 sec)

/*!50100 PARTITION BY RANGE (id)  

自个儿觉着啊,mysql的分区只有壹种情势,只但是运用不相同的算法,?则将数据分配到差异的区块中而已。

    实际利用中,此表恐怕不必要那样多索引的,都创设二回,只是为着显得一下创建的快慢而已。
    实际应用中的效果
    存款和储蓄的标题一时半刻减轻到此处了,接下去经过了壹密密麻麻的服务器参数调节以及询问的优化,笔者只能成功在那个超越九千万多少中的查询响应速度调整在一到20皮秒。听起来是个不利的实际业绩。不过当我们的系统有当先几百私家同不常候使用时,如故显的一筹莫展。也许将来还恐怕有机会能更优化那几个蕴藏与查询。让小编渐渐期待啊。

Sql代码  

壹,分区能够分在多少个磁盘,存款和储蓄越来越大学一年级点
二,依据查找条件,相当于where前边的尺度,查找只查找相应的分区不用任何物色了
三,进行大数据检索时得以实行并行管理。
肆,跨八个磁盘来分散数据查询,来得到更加大的查询吞吐量

复制代码 代码如下:

| count(*) |  

复制代码

    因为成立索引要花很久时间,此处做了个小小的调解,提升myisam索引的排序空间为1G(默许是八m):

----------  

 代码如下

        mysql> create index idx_src_username_email on tmp_sampledata(src,username,email);
        Query OK, 74352359 rows affected (16 min 5.35 sec)
        Records: 74352359 Duplicates: 0 Warnings: 0

 PARTITION p1 VALUES LESS THAN (1997) ENGINE = InnoDB,  

//range重新分区  
mysql> ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE);  
Query OK, 11 rows affected (0.08 sec)  
Records: 11  Duplicates: 0  Warnings: 0  
  
//list重新分区  
mysql> ALTER TABLE list_part REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5));  
Query OK, 0 rows affected (0.28 sec)  
Records: 0  Duplicates: 0  Warnings: 0  
  
//hash和key分区不能用REO宝马X5GANIZE,官方网站说的很掌握  
mysql> ALTER TABLE key_part REORGANIZE PARTITION COALESCE PARTITION 9;  
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARTITION 9' at line 1 

    和预期的同样,那么些查询利用了目录,查询速度在可接受范围内。
    可是那带来了其余一个题材:创建索引须求而外的半空中!!当大家对username和email都创建索引时,空间的接纳小幅度的进步!那同一不是大家希望看到的(无奈的挑3拣四?)。

) ENGINE=MyISAM DEFAULT CHARSET=utf8  

子分区是分区表中每一个分区的重新分割,子分区既能够动用HASH希分区,也能够动用KEY分区。那也被堪称复合分区(composite partitioning)。
一,倘若一个分区中开创了子分区,别的分区也要有子分区
二,假如成立了了分区,各个分区中的子分区数必有同等
三,同壹分区内的子分区,名字不雷同,区别分区内的子分区名子能够一如在此之前(5.一.50不适用)

复制代码 代码如下:

CREATE TABLE `customer_myisam_id` (  

mysql> CREATE TABLE IF NOT EXISTS `hash_part` (  
 ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '评论ID',  
 ->   `comment` varchar(1000) NOT NULL DEFAULT '' COMMENT '评论',  
 ->   `ip` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP',  
 ->   PRIMARY KEY (`id`)  
 -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1  
 -> PARTITION BY HASH(id)  
 -> PARTITIONS 3;  
Query OK, 0 rows affected (0.06 sec)  

复制代码 代码如下:

  

四,分区优点

    除了运用索引,并保证其在查询中能使用到此索引外,分区的要害字段是3个很要紧的优化要素,譬喻下边包车型地铁这些事例:

 PARTITION p17 VALUES LESS THAN (2013) ENGINE = InnoDB,  

//这种方法失利 

复制代码 代码如下:

00:00' and regtime < '1996-01-01 00:00:00';  

复制代码

    对于有所分区及子分区的数据表,分区条件(包涵子分区条件)中利用的数据列,都应该定义在primary key 或许 unique key中。详细的分区定义格式,能够参照MySQL的文书档案。下边包车型大巴结构是第1稿的仓库储存方式(后文还将开始展览改变)。选用load data infile的形式加载,用时贰拾4分钟加载八千万笔录。以为照旧挺快的(bulk_insert_buffer_size=8m)。
    基本查询优化
    数据装载完结后,我们测试了3个询问:

CREATE TABLE `customer_innodb_year` (  

5,key分区

    那是不必置疑的,通过id举办查询是运用了主键,查询速度会异常的快。可是如此的做法大概平昔不意思。因为对于极端用户来说,不容许明白任何的质地的id的。借使须要依据username来拓展查询的话:

1 row in set (0.19 sec)  

二,mysql的分区

    这些创制索引的岁月很久,就像是超越了多少装载时间,可是好歹建好了。

Sql代码  

三,分区管制

        mysql> explain select * from tmp_sampledata where id=9562468G
        *************************** 1. row ***************************
        id: 1
        select_type: SIMPLE
        table: tmp_sampledata
        type: ref
        possible_keys: PRIMARY
        key: PRIMARY
        key_len: 8
        ref: const
        rows: 8
        Extra:
        1 row in set (0.00 sec)

|   449999 |  

设置安装的时候,大家就足以查看一下

    这一个概念没什么难点,根据预期,它将依据primary key来进展多少表分区。可是那有2个非凡可怜沉痛的脾气难点:数据在load data infile的时候,同临时候对数据开始展览索引创立。那大大延伸了数据装载时间,一样是不行忍受的景况。上面那些例子,若是建表时启用了 primary key 或许 unique key, 在自身的测试系统上,load data infile试行了超过1二钟头。而下边这么些:

  `sex` int(1) DEFAULT NULL,  

地方的这几个创造list分区时,借使有主?的话,分区时主键必须在里边,不然就能报错。假如本身不用主键,分区就创办成功了,一般意况下,一个张表鲜明会有3个主键,那算是叁个分区的局限性吧。
假如对数据开始展览测试,请参谋range分区的测试来操作
4,hash分区
HASH分区主要用来确认保证数量在预先鲜明数目的分区中平均布满,你所要做的只是依据将在被哈希的列值内定叁个列值或表达式,以 及钦点被分区的表就要被剪切成的分区数量。

        mysql> explain select id from tsampledata where username='abcdef' and src in (2,3)G
        *************************** 1. row ***************************
        id: 1
        select_type: SIMPLE
        table: tsampledata
        type: ref
        possible_keys: idx_sampledata_username
        key: idx_sampledata_username
        key_len: 66
        ref: const
        rows: 20
        Extra: Using where
        1 row in set (0.00 sec)

Sql代码  

[root@BlackGhost test]# ls |grep user |xargs du -sh  
4.0K    user#P#p0.MYD  
4.0K    user#P#p0.MYI  
4.0K    user#P#p1.MYD  
4.0K    user#P#p1.MYI  
4.0K    user#P#p2.MYD  
4.0K    user#P#p2.MYI  
4.0K    user#P#p3.MYD  
4.0K    user#P#p3.MYI  
4.0K    user#P#p4.MYD  
4.0K    user#P#p4.MYI  
12K    user.frm  
4.0K    user.par  
  
//抽取数据 

    纵然数据针对 email 创建有目录,上边的查询是用不到那多少个索引的。假诺大家应用的是 oracle,那么还是能建设构造1个反向索引,不过mysql不帮助反向索引。所以倘使发生类似的查询,唯有三种方案可以:
        通过数量冗余,把要求的字段反转二回别的保存,并创立2个索引
        那样地点的十二分查询能够由此 where email like ‘moc.3六壹%' 来产生,但是那一个资本(存款和储蓄、更新)太高昂了
        通过全文字笔迹查证索fulltext来促成。但是mysql同样在分区表上不援救fulltext(恐怕等待以后的版本吧。)
        自个儿做分词fulltext
    没有最终方案

 PARTITION p8 VALUES LESS THAN (2004) ENGINE = MyISAM,  

查看一下,假使发掘有上面这些事物,表达她是支撑分区的,暗许是张开的。假使您已经安装过了mysql的话

    分区存款和储蓄
    对于超大的数量以来,分区存款和储蓄是1个不错的挑3拣四,可能说那是三个必选项。对于本例来讲,数据记录来源分裂,首先能够依赖来自来划分那么些数据。可是只是那样还相当不足,因为各类来自的分区的多寡都或者超越千万。那对数码的蕴藏和查询依旧太大了。MySQL伍.x今后一度比较好的支撑了数额分区以及子分区。由此数据就动用分区 子分区来存款和储蓄。

1 row in set (0.59 sec)  

[root@BlackGhost test]# ls |grep user |xargs du -sh  
4.0K    user#P#p0.MYD  
4.0K    user#P#p0.MYI  
4.0K    user#P#p1.MYD  
4.0K    user#P#p1.MYI  
4.0K    user#P#p2.MYD  
4.0K    user#P#p2.MYI  
4.0K    user#P#p3.MYD  
4.0K    user#P#p3.MYI  
12K    user.frm  
4.0K    user.par  
  
/*能够对现存表进行分区,并且会按?则自动的将表中的数据分配相应的分区 
中,那样就比较好了,能够省去很多业务,看上边包车型客车操作*/  
mysql> alter table aa partition by RANGE(id)  
 -> (PARTITION p1 VALUES less than (1),  
 -> PARTITION p2 VALUES less than (5),  
 -> PARTITION p3 VALUES less than MAXVALUE);  
Query OK, 一伍 rows affected (0.贰一 sec)   //对1五数量进行分区  
Records: 15  Duplicates: 0  Warnings: 0  
  
//总共有15条  
mysql> select count(*) from aa;  
----------   
| count(*) |  
----------   
|       15 |  
----------   
1 row in set (0.00 sec)  
  
//删除2个分区  
mysql> alter table aa drop partition p2;  
Query OK, 0 rows affected (0.30 sec)  
Records: 0  Duplicates: 0  Warnings: 0  
  
//只有1一条了,表明对现成的表分区成功了  
mysql> select count(*) from aa;  
----------   
| count(*) |  
----------   
|       11 |  
----------   
1 row in set (0.00 sec) 

假诺您对大块文章没有意思味,也足以一向看看结果,或者你对结果感兴趣。在实际应用中经过存款和储蓄、优化能够达成在超过玖千万数码中的查询响应速度调节在一到20阿秒。看上去是个不错的大成,但是优化那条路未有极限,当大家的类别有超过常规几百人、上千人同一时间接选举择时,照旧会显的不能够。

1、对于根据时间间隔来询问的,提出利用根据时间来分区,收缩查询范围。

 

            成立3个不含任何索引、键的分区表;
            导入数据;
            创设索引;

----------  

//创建range分区表 

正文:

  `avatar` blob,  

3,list分区

        CREATE TABLE `tmp_sampledata` (
        `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        `username` varchar(32) DEFAULT NULL,
        `passwd` varchar(32) DEFAULT NULL,
        `email` varchar(64) DEFAULT NULL,
        `nickname` varchar(32) DEFAULT NULL,
        `siteid` varchar(32) DEFAULT NULL,
        `src` smallint(6) NOT NULL DEFAULT '0′,
        PRIMARY KEY (`id`,`src`)
        ) ENGINE=MyISAM AUTO_INCREMENT=95660181 DEFAULT CHARSET=gbk
        /*!50500 PARTITION BY LIST COLUMNS(src)
        SUBPARTITION BY HASH (id)
        SUBPARTITIONS 5
        (PARTITION pose VALUES IN (1) ENGINE = MyISAM,
        PARTITION p2736 VALUES IN (2) ENGINE = MyISAM,
        PARTITION p736736 VALUES IN (3) ENGINE = MyISAM,
        PARTITION p3838648 VALUES IN (4) ENGINE = MyISAM,
        PARTITION p842692 VALUES IN (5) ENGINE = MyISAM,
        PARTITION p7575 VALUES IN (6) ENGINE = MyISAM,
        PARTITION p386386 VALUES IN (7) ENGINE = MyISAM,
        PARTITION p62678 VALUES IN (8) ENGINE = MyISAM) */

  `avatar` blob,  

唯有11条数据,不过总计出来的文件大小都是四.0K,从此时我们能够看出分区的 
最小区块是4K 
*/  
mysql> select count(id) as count from user;  
-------   
| count |  
-------   
|    11 |  
-------   
1 row in set (0.00 sec)  
  
//第多个区块已删除 

        mysql> create index idx_username_src on tmp_sampledata (username,src);
        Query OK, 74352359 rows affected (7 min 13.11 sec)
        Records: 74352359 Duplicates: 0 Warnings: 0

----------  

1,mysql5.1及以上协助分区作用

        mysql> create index idx_email_src on tmp_sampledata (email,src);
        Query OK, 74352359 rows affected (10 min 48.30 sec)
        Records: 74352359 Duplicates: 0 Warnings: 0

 PARTITION p18 VALUES LESS THAN (2014) ENGINE = MyISAM,  

 代码如下

        mysql> explain select * from tmp_sampledata where src between 1 and 7 and username = ‘yourusername'G
        *************************** 1. row ***************************
        id: 1
        select_type: SIMPLE
        table: tmp_sampledata
        type: ALL
        possible_keys: NULL
        key: NULL
        key_len: NULL
        ref: NULL
        rows: 74352359
        Extra: Using where
        1 row in set (0.00 sec)

----------  

 代码如下

    数据装载仅仅用了陆分钟:
    mysql> load data infile ‘cvsfile.txt' into table tmp_sampledata fields terminated by ‘t' escaped by ”;
    Query OK, 74352359 rows affected, 65535 warnings (5 min 23.67 sec)
    Records: 74352359 Deleted: 0 Skipped: 0 Warnings: 51267046

 PARTITION p3 VALUES LESS THAN (1500000) ENGINE = InnoDB,  

//range增加新分区  
mysql> alter table user add partition(partition p4 values less than MAXVALUE);  
Query OK, 0 rows affected (0.06 sec)  
Records: 0  Duplicates: 0  Warnings: 0  
  
//list增多新分区  
mysql> alter table list_part add partition(partition p4 values in (25,26,28));  
Query OK, 0 rows affected (0.01 sec)  
Records: 0  Duplicates: 0  Warnings: 0  
  
//hash重新分区  
mysql> alter table hash_part add partition partitions 4;  
Query OK, 0 rows affected (0.12 sec)  
Records: 0  Duplicates: 0  Warnings: 0  
  
//key重新分区  
mysql> alter table key_part add partition partitions 4;  
Query OK, 一 row affected (0.0陆 sec)    //有数量也会被重新分配  
Records: 1  Duplicates: 0  Warnings: 0  
  
//子分区增添新分区,纵然本身从不内定子分区,可是系统会给子分区命名的  
mysql> alter table sub1_part add partition(partition p3 values less than MAXVALUE);  
Query OK, 0 rows affected (0.02 sec)  
Records: 0  Duplicates: 0  Warnings: 0  
  
mysql> show create table sub1_partG;  
*************************** 1. row ***************************  
 Table: sub1_part  
Create Table: CREATE TABLE `sub1_part` (  
 `news_id` int(11) NOT NULL COMMENT '新闻ID',  
 `content` varchar(一千) NOT NULL DEFAULT '' COMMENT '新闻内容',  
 `u_id` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP',  
 `create_time` date NOT NULL DEFAULT '0000-00-00' COMMENT '时间'  
) ENGINE=InnoDB DEFAULT CHARSET=utf8  
!50100 PARTITION BY RANGE (YEAR(create_time))  
SUBPARTITION BY HASH (TO_DAYS(create_time))  
(PARTITION p0 VALUES LESS THAN (1990)  
 (SUBPARTITION s0 ENGINE = InnoDB,  
 SUBPARTITION s1 ENGINE = InnoDB,  
 SUBPARTITION s2 ENGINE = InnoDB),  
 PARTITION p1 VALUES LESS THAN (2000)  
 (SUBPARTITION s3 ENGINE = InnoDB,  
 SUBPARTITION s4 ENGINE = InnoDB,  
 SUBPARTITION good ENGINE = InnoDB),  
 PARTITION p2 VALUES LESS THAN (3000)  
 (SUBPARTITION tank0 ENGINE = InnoDB,  
 SUBPARTITION tank1 ENGINE = InnoDB,  
 SUBPARTITION tank3 ENGINE = InnoDB),  
 PARTITION p3 VALUES LESS THAN MAXVALUE  
 (SUBPARTITION p叁sp0 ENGINE = InnoDB,    //子分区的名子是自动生成的  
 SUBPARTITION p3sp1 ENGINE = InnoDB,  
 SUBPARTITION p3sp2 ENGINE = InnoDB))  
1 row in set (0.00 sec)  
三,重新分区

        mysql> explain select id from tsampledata where username='abcdef'G
        *************************** 1. row ***************************
        id: 1
        select_type: SIMPLE
        table: tsampledata
        type: ref
        possible_keys: idx_sampledata_username
        key: idx_sampledata_username
        key_len: 66
        ref: const
        rows: 80
        Extra: Using where
        1 row in set (0.00 sec)

表四 MyISAM & PARTITION BY RANGE (year) 

LIST分区中各种分区的定义和采纳是依赖某列的值从属于七个值列表聚焦的一个值,而RANGE分 区是从属于多少个总是区间值的汇聚。

        CREATE TABLE `tmp_sampledata` (
        `id` bigint(20) unsigned NOT NULL,
        `username` varchar(32) NOT NULL DEFAULT ”,
        `passwd` varchar(32) DEFAULT NULL,
        `email` varchar(64) NOT NULL DEFAULT ”,
        `nickname` varchar(32) DEFAULT NULL,
        `siteid` varchar(32) DEFAULT NULL,
        `src` smallint(6) NOT NULL DEFAULT '0′,
        primary KEY (`src`,`username`,`email`, `id`)
        ) ENGINE=MyISAM DEFAULT CHARSET=gbk
        PARTITION BY LIST COLUMNS(src)
        SUBPARTITION BY KEY (username,email)
        SUBPARTITIONS 10
        (PARTITION pose VALUES IN (1) ENGINE = MyISAM,
        PARTITION p2736 VALUES IN (2) ENGINE = MyISAM,
        PARTITION p736736 VALUES IN (3) ENGINE = MyISAM,
        PARTITION p3838648 VALUES IN (4) ENGINE = MyISAM,
        PARTITION p842692 VALUES IN (5) ENGINE = MyISAM,
        PARTITION p7575 VALUES IN (6) ENGINE = MyISAM,
        PARTITION p386386 VALUES IN (7) ENGINE = MyISAM,
        PARTITION p62678 VALUES IN (8) ENGINE = MyISAM)?;

  `sex` int(1) DEFAULT NULL,  

 代码如下

    分区存款和储蓄
    优化查询
    立异分区
    模糊搜索
    持续革新的方案

 PARTITION p2 VALUES LESS THAN (1998) ENGINE = InnoDB,  

测试请参照他事他说加以调查range分区的操作

        select … from … where email like ‘3.com'

  `password` varchar(32) NOT NULL,  

一,删除分区
mysql> alter table user drop partition p4;  
2,新增添分区

复制代码 代码如下:

----------  

复制代码

    同二个查询语句在凭借是还是不是针对分区限定做询问时,查询资金互不相同:

 

mysql> alter table user drop partition p4;  
Query OK, 0 rows affected (0.11 sec)  
Records: 0  Duplicates: 0  Warnings: 0  
  
/**存放在分区里面的数额丢失了,第多个分区里面有14条数据,剩下的二个分区

        create index idx_username on tmp_sampledata(username);

  PRIMARY KEY (`id`,`regtime`)  

复制代码

    那那一个查询就没办法用了。根本就没人能等待2个上亿表的全表找寻!那是大家就思虑是或不是给username创制八个索引,那样自然会压实查询速度:

 结果汇总  www.2cto.com  

复制代码

  `sex` int(1) DEFAULT NULL,  

分区听上去怎么以为是硬盘呀,对没有错除了硬盘能够分区数据库未来也支撑分区了,分区能够缓慢解决大数据量的管理难题,下面一齐来看1个mysql数据库分区成效及实例详解

----------  

复制代码

  `name` varchar(32) NOT NULL,  

mysql> select count(id) as count from user;  
-------   
| count |  
-------   
|    25 |  
-------   
1 row in set (0.00 sec)  
  
//删除第陆个分区 

1 row in set (2.08 sec)  

 代码如下

 

mysql> CREATE TABLE IF NOT EXISTS `list_part` (  
 ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',  
 ->   `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省',  
 ->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',  
 ->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男,1为女',  
 ->   PRIMARY KEY (`id`)  
 -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1  
 -> PARTITION BY LIST (province_id) (  
 ->     PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),  
 ->     PARTITION p1 VALUES IN (9,10,11,12,16,21),  
 ->     PARTITION p2 VALUES IN (13,14,15,19),  
 ->     PARTITION p3 VALUES IN (17,18,20,22,23,24)  
 -> );  
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function 
 
//这种格局成功 
mysql> CREATE TABLE IF NOT EXISTS `list_part` ( 
 ->   `id` int(11) NOT NULL  COMMENT '用户ID', 
 ->   `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省', 
 ->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称', 
 ->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男,1为女'  
 -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8  
 -> PARTITION BY LIST (province_id) (  
 ->     PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),  
 ->     PARTITION p1 VALUES IN (9,10,11,12,16,21),  
 ->     PARTITION p2 VALUES IN (13,14,15,19),  
 ->     PARTITION p3 VALUES IN (17,18,20,22,23,24)  
 -> );  
Query OK, 0 rows affected (0.33 sec) 

  

mysql> CREATE TABLE IF NOT EXISTS `key_part` (  
 ->   `news_id` int(11) NOT NULL  COMMENT '新闻ID',  
 ->   `content` varchar(一千) NOT NULL DEFAULT '' COMMENT '音讯内容',  
 ->   `u_id` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP',  
 ->   `create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '时间'  
 -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8  
 -> PARTITION BY LINEAR HASH(YEAR(create_time))  
 -> PARTITIONS 3;  
Query OK, 0 rows affected (0.07 sec) 

 PARTITION p19 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;  

一,什么是数据库分区

| count(*) |  

 代码如下

mysql> select count(*) from customer_myisam_year where regtime > '1995-01-01 00:  

遵照KEY进行分区类似于根据HASH分区,除了HASH分区动用的用 户定义的表达式,而KEY分区的 哈希函数是由MySQL 服务器提供。

----------  

----------  

  `email` varchar(64) NOT NULL,  

|   449999 |  

  `id` int(11) NOT NULL,  

----------  

2 InnoDB id regtime 4.74 5.28

/*!50100 PARTITION BY RANGE (id)  

 PARTITION p13 VALUES LESS THAN (2009) ENGINE = InnoDB,  

mysql> select count(*) from customer_myisam_year where id > 50000 and id < 50000  

0;  

二、MyISAM质量完全占优,可是不援救事务管理、外键约束等。

mysql> select count(*) from customer_innodb_year where regtime > '1995-01-01 00:  

 

 PARTITION p2 VALUES LESS THAN (1000000) ENGINE = InnoDB,  

 PARTITION p3 VALUES LESS THAN (1999) ENGINE = MyISAM,  

3 InnoDB year id 5.31 0.31

mysql> select count(*) from customer_innodb_year where regtime > '1995-01-01 00:  

  `password` varchar(32) NOT NULL,  

:00' and regtime < '1996-01-01 00:00:00';  

----------  

 PARTITION p12 VALUES LESS THAN (2008) ENGINE = InnoDB,  

(PARTITION p0 VALUES LESS THAN (1996) ENGINE = MyISAM,  

  `birth` date DEFAULT NULL,  

mysql> select count(*) from customer_innodb_year where id > 50000 and id < 50000  

 PARTITION p11 VALUES LESS THAN (2007) ENGINE = MyISAM,  

  

  `birth` date DEFAULT NULL,  

mysql> select count(*) from customer_myisam_id where regtime > '1995-01-01 00:00  

Sql代码  

 

----------  

查询结果:

00:00' and regtime < '1996-01-01 00:00:00';  

  `address` varchar(64) DEFAULT NULL,  

mysql> select count(*) from customer_myisam_year where regtime > '1995-01-01 00:  

mysql> select count(*) from customer_myisam_id where id > 50000 and id < 500000;  

----------  

  

(PARTITION p0 VALUES LESS THAN (100000) ENGINE = InnoDB,  

 PARTITION p14 VALUES LESS THAN (2010) ENGINE = MyISAM,  

  

1 row in set (0.56 sec)  

----------  

 PARTITION p5 VALUES LESS THAN (2001) ENGINE = MyISAM,  

版权声明:本文由ca888发布于IT操作系统,转载请注明出处:mysql 超大数据/表管理技艺