mysql分區(qū)及分表（二）

                            mysql分區(qū)分表（二）

測(cè)試未分區(qū)表和分區(qū)表性能

重新創(chuàng)建新的測(cè)試數(shù)據(jù)庫(kù)及未分區(qū)表back1

創(chuàng)建分區(qū)表back2，按照年月區(qū)分

maxvalue把對(duì)于2005的值全放在p11區(qū)里

創(chuàng)建大點(diǎn)的數(shù)據(jù)（方便測(cè)試的時(shí)候區(qū)分明顯分區(qū)和未分區(qū)的區(qū)別）

rand()函數(shù)在0和1之間產(chǎn)生一個(gè)隨機(jī)數(shù)，如果一個(gè)整數(shù)參數(shù)N指定，它被用作種子值。。每個(gè)種子產(chǎn)生的隨機(jī)數(shù)序列是不同的

執(zhí)行存儲(chǔ)過(guò)程load_part_tab向back2中插入數(shù)據(jù)

向back1中插入數(shù)據(jù)

測(cè)試未分區(qū)和分區(qū)的性能

可以通過(guò)explain語(yǔ)句分析執(zhí)行情況

創(chuàng)建索引測(cè)試效果

重啟mysqld服務(wù) 可以看出時(shí)間相差不大，如果數(shù)據(jù)大會(huì)明顯

mysql分區(qū)類型

1.range分區(qū)：基于屬于一個(gè)給定連續(xù)區(qū)間的列值，把多行給分區(qū)，區(qū)間連續(xù)不能相互重疊；使用values less than定義

創(chuàng)建新的表表名為benet1

2.list分區(qū)：類似于range分區(qū)，區(qū)別在于基本列值是離散值集合

list分區(qū)通過(guò)使用partition by list來(lái)實(shí)現(xiàn)

這個(gè)可以在表中添加或刪除指定地方的記錄容易

3.hash分區(qū)：允許dba通過(guò)對(duì)表的一個(gè)或多個(gè)列的hash key進(jìn)行計(jì)算，最后通過(guò)hash碼不同數(shù)值對(duì)應(yīng)的數(shù)據(jù)區(qū)域進(jìn)行分區(qū)

使用hash算法分了四個(gè)區(qū)

創(chuàng)建數(shù)據(jù)并且查看數(shù)據(jù)放在那個(gè)分區(qū)里

查看分區(qū)的詳細(xì)信息

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

可以看到第三個(gè)分區(qū)p2中有一個(gè)數(shù)據(jù)記錄

4.key分區(qū)：key分區(qū)使用mysql數(shù)據(jù)庫(kù)提供的函數(shù)進(jìn)行分區(qū)，ndb cluster使用md5函數(shù)分區(qū)，對(duì)于其他存儲(chǔ)引擎mysql內(nèi)部的hash函數(shù)

創(chuàng)建key分區(qū)、表、數(shù)據(jù) 查看數(shù)據(jù)會(huì)放到那個(gè)區(qū)里

range、list、hash、key四種分區(qū)，分區(qū)條件必須×××，不是×××需要通過(guò)函數(shù)將其轉(zhuǎn)換

5.columns分區(qū)：從5.5開(kāi)始支持columns分區(qū)，可以說(shuō)是range和list的進(jìn)化分區(qū)，可以直接使用非×××數(shù)據(jù)進(jìn)行分區(qū)支持的數(shù)據(jù)：

    所有×××，如INT SMALLINT TINYINT BIGINT。FLOAT和DECIMAL則不支持。
　　日期類型，如DATE和DATETIME。其余日期類型不支持。
　　字符串類型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT類型不支持。
COLUMNS可以使用多個(gè)列進(jìn)行分區(qū)。