mysql分区分表(二)

测试未分区表和分区表性能

重新创建新的测试数据库及未分区表back1

wKiom1lOYNbBVbA_AAASfPzOktw373.png 

创建分区表back2,按照年月区分

wKioL1lOYOGjZ_n0AAAZyCm7K3I786.png 

maxvalue把对于2005的值全放在p11区里

创建大点的数据(方便测试的时候区分明显分区和未分区的区别)

wKioL1lOYOqjRqICAABU9VbV6cA660.png 

rand()函数在01之间产生一个随机数,如果一个整数参数N指定,它被用作种子值。。每个种子产生的随机数序列是不同的

执行存储过程load_part_tabback2中插入数据

wKiom1lOYPWx7_PDAAAHUSx5yyc003.png 

back1中插入数据

wKiom1lOYP7Q8DKEAAAMB8D5uy4236.png 

测试未分区和分区的性能

wKioL1lOYQeQ8oFsAAAwJh5Hx2c730.png 

可以通过explain语句分析执行情况

wKioL1lOYRHhujSPAABZiGJ0XT4381.png 

创建索引测试效果

wKiom1lOYRqApzP-AAAixy2NRK8784.png 

重启mysqld服务 可以看出时间相差不大,如果数据大会明显

wKiom1lOYSWhnqUeAAAgxaEOzgo661.png 

mysql分区类型

1.range分区:基于属于一个给定连续区间的列值,把多行给分区,区间连续不能相互重叠;使用values less than定义

创建新的表表名为benet1

wKioL1lOYTDS-o4vAABJgl2k5p4354.png 

2.list分区:类似于range分区,区别在于基本列值是离散值集合

list分区通过使用partition by list来实现

wKioL1lOYTqBjDQOAAAeorfeRoQ668.png 

这个可以在表中添加或删除指定地方的记录容易

3.hash分区:允许dba通过对表的一个或多个列的hash key进行计算,最后通过hash码不同数值对应的数据区域进行分区

使用hash算法分了四个区

wKiom1lOYUOCapoVAAAKwY2RLUQ434.png 

创建数据并且查看数据放在那个分区里

wKiom1lOYUzD1iyZAAAIMaYFnuQ858.png 

wKioL1lOYVWwH94PAAAMqGELAOo780.png 

查看分区的详细信息

mysql> select * from information_schema.partitions where table_schema='test1' and table_name='benet3'\G;

*************************** 1. row ***************************

                TABLE_CATALOG: def

                 TABLE_SCHEMA: test1

                   TABLE_NAME: benet3

               PARTITION_NAME: p0

            SUBPARTITION_NAME: NULL

   PARTITION_ORDINAL_POSITION: 1

SUBPARTITION_ORDINAL_POSITION: NULL

             PARTITION_METHOD: HASH

          SUBPARTITION_METHOD: NULL

         PARTITION_EXPRESSION: year(b)

      SUBPARTITION_EXPRESSION: NULL

        PARTITION_DESCRIPTION: NULL

                   TABLE_ROWS: 0

               AVG_ROW_LENGTH: 0

                  DATA_LENGTH: 16384

              MAX_DATA_LENGTH: NULL

                 INDEX_LENGTH: 0

                    DATA_FREE: 0

                  CREATE_TIME: 2017-06-22 18:01:56

                  UPDATE_TIME: NULL

                   CHECK_TIME: NULL

                     CHECKSUM: NULL

            PARTITION_COMMENT:

                    NODEGROUP: default

              TABLESPACE_NAME: NULL

*************************** 2. row ***************************

                TABLE_CATALOG: def

                 TABLE_SCHEMA: test1

                   TABLE_NAME: benet3

               PARTITION_NAME: p1

            SUBPARTITION_NAME: NULL

   PARTITION_ORDINAL_POSITION: 2

SUBPARTITION_ORDINAL_POSITION: NULL

             PARTITION_METHOD: HASH

          SUBPARTITION_METHOD: NULL

         PARTITION_EXPRESSION: year(b)

      SUBPARTITION_EXPRESSION: NULL

        PARTITION_DESCRIPTION: NULL

                   TABLE_ROWS: 0

               AVG_ROW_LENGTH: 0

                  DATA_LENGTH: 16384

              MAX_DATA_LENGTH: NULL

                 INDEX_LENGTH: 0

                    DATA_FREE: 0

                  CREATE_TIME: 2017-06-22 18:01:56

                  UPDATE_TIME: NULL

                   CHECK_TIME: NULL

                     CHECKSUM: NULL

            PARTITION_COMMENT:

                    NODEGROUP: default

              TABLESPACE_NAME: NULL

*************************** 3. row ***************************

                TABLE_CATALOG: def

                 TABLE_SCHEMA: test1

                   TABLE_NAME: benet3

               PARTITION_NAME: p2

            SUBPARTITION_NAME: NULL

   PARTITION_ORDINAL_POSITION: 3

SUBPARTITION_ORDINAL_POSITION: NULL

             PARTITION_METHOD: HASH

          SUBPARTITION_METHOD: NULL

         PARTITION_EXPRESSION: year(b)

      SUBPARTITION_EXPRESSION: NULL

        PARTITION_DESCRIPTION: NULL

                   TABLE_ROWS: 1

               AVG_ROW_LENGTH: 16384

                  DATA_LENGTH: 16384

              MAX_DATA_LENGTH: NULL

                 INDEX_LENGTH: 0

                    DATA_FREE: 0

                  CREATE_TIME: 2017-06-22 18:01:56

                  UPDATE_TIME: 2017-06-22 18:02:57

                   CHECK_TIME: NULL

                     CHECKSUM: NULL

            PARTITION_COMMENT:

                    NODEGROUP: default

              TABLESPACE_NAME: NULL

*************************** 4. row ***************************

                TABLE_CATALOG: def

                 TABLE_SCHEMA: test1

                   TABLE_NAME: benet3

               PARTITION_NAME: p3

            SUBPARTITION_NAME: NULL

   PARTITION_ORDINAL_POSITION: 4

SUBPARTITION_ORDINAL_POSITION: NULL

             PARTITION_METHOD: HASH

          SUBPARTITION_METHOD: NULL

         PARTITION_EXPRESSION: year(b)

      SUBPARTITION_EXPRESSION: NULL

        PARTITION_DESCRIPTION: NULL

                   TABLE_ROWS: 0

               AVG_ROW_LENGTH: 0

                  DATA_LENGTH: 16384

              MAX_DATA_LENGTH: NULL

                 INDEX_LENGTH: 0

                    DATA_FREE: 0

                  CREATE_TIME: 2017-06-22 18:01:56

                  UPDATE_TIME: 2017-06-22 18:02:57

                   CHECK_TIME: NULL

                     CHECKSUM: NULL

            PARTITION_COMMENT:

                    NODEGROUP: default

              TABLESPACE_NAME: NULL

4 rows in set (0.00 sec)

 

ERROR:

No query specified

可以看到第三个分区p2中有一个数据记录

4.key分区:key分区使用mysql数据库提供的函数进行分区,ndb cluster使用md5函数分区,对于其他存储引擎mysql内部的hash函数

创建key分区、表、数据 查看数据会放到那个区里

wKiom1lOYWHBuDO5AAAgJXRE6Ug109.png 

rangelisthashkey四种分区,分区条件必须整形,不是整形需要通过函数将其转换

5.columns分区:从5.5开始支持columns分区,可以说是rangelist的进化分区,可以直接使用非整形数据进行分区支持的数据:

    所有整形,如INT SMALLINT TINYINT BIGINTFLOATDECIMAL则不支持。
  日期类型,如DATEDATETIME。其余日期类型不支持。
  字符串类型,如CHARVARCHARBINARYVARBINARYBLOBTEXT类型不支持。
COLUMNS可以使用多个列进行分区。