mysql 分区指定分区表数据存储路径，可以支持跨磁盘

INNODB引擎只支持DATA DIRECTORY，不支持INDEX DIRECTORY, 另外 目录要空目录权限归属mysql 组mysql 用户，命令

mkdir /data

chown mysql:mysql /data

主键必须包含分区字段，PRIMARY KEY (id,store_id)

CREATE TABLE orders_list2 (

  id INT AUTO_INCREMENT,

  customer_surname VARCHAR(30),

  store_id INT,

  salesperson_id INT,

  order_date DATE,

  note VARCHAR(500),

  PRIMARY KEY (id,store_id),

  INDEX idx (id)

) ENGINE = INNODB

  PARTITION BY LIST(store_id) (

  PARTITION p1

  VALUES IN (1, 3, 4, 17)

  DATA DIRECTORY = '/data/p1',

  PARTITION p2

  VALUES IN (2, 12, 14)

  DATA DIRECTORY = '/data/p2',

  PARTITION p3

  VALUES IN (6, 8, 20)

  DATA DIRECTORY = '/data/p3',

  PARTITION p4

  VALUES IN (5, 7, 9, 11, 16)

  DATA DIRECTORY = '/data/p4',

  PARTITION p5

  VALUES IN (10, 13, 15, 18)

  DATA DIRECTORY = '/data/p5'

)

分区表扩充

ALTER TABLE orders_list2  ADD PARTITION (PARTITION p6 VALUES IN  (21,22,23)

DATA DIRECTORY = '/var/lib/mysql/monitor/orders/p6' 

子分区

CREATE TABLE tb_sub_dir (id INT, purchased DATE)

ENGINE='MYISAM'

    PARTITION BY RANGE( YEAR(purchased) )

    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (

        PARTITION p0 VALUES LESS THAN (1990) 

        (

            SUBPARTITION s0

                DATA DIRECTORY = '/data/data_sub1'

                INDEX DIRECTORY = '/data/idx_sub1',

            SUBPARTITION s1

                DATA DIRECTORY = '/data/data_sub1'

                INDEX DIRECTORY = '/data/idx_sub1'

        ),

        PARTITION p1 VALUES LESS THAN (2000) 

        (

            SUBPARTITION s2

                DATA DIRECTORY = '/data/data_sub2'

                INDEX DIRECTORY = '/data/idx_sub2',

            SUBPARTITION s3

                DATA DIRECTORY = '/data/data_sub2'

                INDEX DIRECTORY = '/data/idx_sub2'

        ),

        PARTITION p2 VALUES LESS THAN MAXVALUE 

        (

            SUBPARTITION s4

                DATA DIRECTORY = '/data/data_sub3'

                INDEX DIRECTORY = '/data/idx_sub3',

            SUBPARTITION s5

                DATA DIRECTORY = '/data/data_sub3'

                INDEX DIRECTORY = '/data/idx_sub3'

        )

    )

日期字段区分

CREATE TABLE employees1 (

    id INT NOT NULL,

    fname VARCHAR(30),

    lname VARCHAR(30),

    hired DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT '9999-12-31',

    job_code INT,

    store_id INT

)

PARTITION BY RANGE ( YEAR(separated) ) (

    PARTITION p0 VALUES LESS THAN (1991),

    PARTITION p1 VALUES LESS THAN (1996),

    PARTITION p2 VALUES LESS THAN (2001),

    PARTITION p3 VALUES LESS THAN MAXVALUE

)

MySQL大家应该用的都是innodb存储引擎的表，而innodb是索引组织表。查询删除修改都是同索引去查找数据的。对于一个业务访问比较频繁的数据库来说，增删改查的 *** 作很频繁，这就很容易导致发生分裂、合并、数据空洞等现象。包括一些事物的 *** 作可能会导致回滚段回滚后数据文件并没有回收等现象。

这些原因就导致了主从库占用磁盘空间大小有些差别现象。

---