Mysql表分区

一：什么是Mysql分区、原理是什么、做什么用、应用场景是什么？

【1.1】分区介绍：对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成，Mysql在创建表的时候会使用Partition by 子句定义每个分区存放的数据，执行查询的时候优化器会根据分区定义过滤掉那些没有我们需要数据的分区。这样查询就无需扫描所有的分区，只去扫描我们需要数据所在的分区就可以了

【1.2】分区目的：一个主要的目的是把数据按照一个较粗的粒度存放在不同的表中，这样做可以将相关的数据放在一次，同时如果想要一次批量的删除整个分区的数据也会比较方便！

【1.3】应用场景：

1.3.1：表很大，没有办法全部放在内存里面、或者表的数据只有部分是热点数据、其他都是历史数据，访问频率有较大差异

1.3.2：分区表的数据更容易维护

1.3.3：分区表的数据可以放在不同的物理设备上

1.3.4：使用分区表来避免某些特殊的瓶颈，例如InnoDB的单个索引互斥访问、ext3文件系统的inode锁竞争机制

1.3.5：备份和恢复独立的分区，在非常大的数据的场景下有很好的效果

【1.4】使用限制

1.4.1：一个表最多有1024个分区

1.4.2：在Mysql5.1中分区表达式必须是整数、或者是返回整数的表达式，在Mysql5.5的某些场景下，可以直接使用列来进行分区。

1.4.3：如果分区字段中有主键或者唯一索引，那么所有的主键和唯一索引列都必须包含进来

1.4.4：分区无法使用外键约束

【1.5】使用原理

主要的原理是通过一个入口来根据条件过滤分区，并对剩下的分区进行相关的操作，不论增删改差都要“先打开并锁住所有的底层表”其中增加（insert)、删除（delete）、查询（select）都是先锁住所有的表然后对相应的分区操作，说明下 update 更新操作要删除原来的数据、然后分析插入到对应的分区相当于两次操作delete insert 。

二：怎么创建一个分区表？分区表的创建、修改、删除

我们来创建一个以id为1000000条为一个分区的表

CREATE TABLE part 
( 
id int NOT NULL AUTO_INCREMENT, 
age int NOT NULL, PRIMARY KEY(id) 
) 
ENGINE=InnoDB PARTITION BY HASH(id DIV 1000000);

然后我们写一个过程来创建1E 条数据

mysql> CREATE PROCEDURE moredata()
    -> BEGIN 
    -> DECLARE i int;
    -> SET i=0;
    -> WHILE i<26 DO
    ->     INSERT INTO part (`age`) SELECT age FROM part;
    ->     SET i=i+1;
    -> END WHILE;
    -> END;//

这一种写法发现报错，ERROR 1206 (HY000): The total number of locks exceeds the lock table size

只插入1000w条数据

第二种写法：

mysql> CREATE PROCEDURE moredata2()
    -> BEGIN
    -> DECLARE i int;
    -> SET i=0;
    -> WHILE i<10000000 DO
    ->     INSERT INTO part (`age`) VALUES (current_timestamp());
    ->     SET i=i+1;
    -> END WHILE;
    -> END;//

因为在虚拟机里面 ，性能不是很好、跑到1000w 测试够用就行

第二种创建分区的写法

mysql> CREATE TABLE part2
    -> (
    -> id  int not null auto_increment,
    -> age int not null,
    -> primary key(`id`)
    -> ) ENGINE=InnoDB PARTITION BY RANGE(id)(
    -> PARTITION p_1 VALUES LESS THAN (1000000),
    -> PARTITION p_2 VALUES LESS THAN (2000000),
    -> PARTITION p_3 VALUES LESS THAN (3000000),
    -> PARTITION p_4 VALUES LESS THAN (4000000),
    -> PARTITION p_0 VALUES LESS THAN MAXVALUE );

我们同样的创建1000w调数据

 

三：使用分区表的注意事项

为了保证大数据量的可扩展性，一般要注意两点

1：全量扫描数据，不要任何索引。

当数据量很大的时候，索引能起到的作用并不是很有用，反而不如全表扫描，只要能把扫描的物理子表锁定在尽可能少的分区，就是说where 可以过滤掉大部分的分区。

2：索引数据，并分离热点

如果数据有明显的热点，就可以把这些数据单独放在一个分区，缓存起来，使用索引，更高效。

会遇到问题的场景：

Null值会使得分区过滤无效，

分区列和索引列不匹配

选择分区的成本可能很高

打开并锁住所有底层表的成本可能很高

维护分区的成本可能很高。

注意事项：

所有的分区必须使用相同的存储引擎

分区函数中使用的函数和表达式也有一些限制

某些存储引擎不支持分区

对于MyISAM分区表，不能再使用LOAD INDEX INTO CACHE 操作

对于MyISAM分区表，使用分区表时候需要打开更多的文件描述

四：分区查询分析：

什么条件都不加的情况下

mysql> EXPLAIN PARTITIONS SELECT * FROM part G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: part
   partitions: p0
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 11065776
        Extra: NULL
1 row in set (0.00 sec)

我们看到扫描所有的分区，下面我们在where 加一个条件

mysql> EXPLAIN PARTITIONS SELECT * FROM part2  WHERE id > 2000000G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: part2
   partitions: p_3,p_4,p_0
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 310568
        Extra: Using where
1 row in set (0.00 sec)

mysql> EXPLAIN PARTITIONS SELECT * FROM part  WHERE id > 2000000G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: part
   partitions: p0
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 5532888
        Extra: Using where
1 row in set (0.04 sec)

我们可以看到扫描的分区进行了过滤，不在是全表扫描

有一个注意事项，在Where条件中 Mysql只能在使用分区函数的列本身进行比较的时候才能过滤分区，而不能根据表达式的值去过滤分区例如：

mysql> EXPLAIN PARTITIONS SELECT * FROM part  WHERE (id-1) > 2000000G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: part
   partitions: p0
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 11065776
        Extra: Using where
1 row in set (0.00 sec)

这里就要全表扫描了

我们来创建一个没有分区的一样的表比较下效率，把主键删除掉

CREATE TABLE `part3` (
  `id` int(10) NOT NULL DEFAULT '0',
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

创建1000w条数据

mysql> alter table part change id id int(10);
Query OK, 12043448 rows affected (1 min 2.69 sec)
Records: 12043448  Duplicates: 0  Warnings: 0

mysql> alter table part drop primary key;
Query OK, 12043448 rows affected (1 min 12.56 sec)
Records: 12043448  Duplicates: 0  Warnings: 0

mysql> alter table part3 change id id int(10);
Query OK, 11685760 rows affected (57.55 sec)
Records: 11685760  Duplicates: 0  Warnings: 0

mysql> alter table part3 drop primary key;
Query OK, 11685760 rows affected (1 min 4.74 sec)
Records: 11685760  Duplicates: 0  Warnings: 0

可以看到，修改字段方面分区表性能更高，这应该跟分区以后索引的创建是分区对应的有关，

测试结果发现效率类似，应该还是要索引才可以否则都是全表扫描

mysql> explain partitions select * from part where id = 17645875G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: part
   partitions: p0
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 11717145
        Extra: Using where
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> explain partitions select * from part3 where id = 17645875G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: part3
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 11182168
        Extra: Using where
1 row in set (0.00 sec)

这应征了上面分区列和索引列不匹配会出问题

 

个人小结：总的来说分区在优化大表的时候还是有一定的用处，在上面文中描述的应用场景中能起到不俗的作用，但是本身使用需要考虑的点还是很多。