运维利器之mysql的子分区

何为子分区:子分区就是在分区表中每个分区的再次进行分割
优点就是可以利用特别大的表,在多个磁盘间分配数据和索引

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| test2              |
+--------------------+
5 rows in set (0.01 sec)

mysql> use test2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| t1              |
| t2              |
| t3              |
| t4              |
| t5              |
| t6              |
+-----------------+
6 rows in set (0.00 sec)

mysql> create tables t11 (id int,udate date)         ###注意,字段名不能用update,具体原因就不描述了
    -> partition by range(year(udate))
    -> subpartition by hash(to_days(udate))
    -> subpartitions 2
    -> (
    -> partition p0 values less than (1990),
    -> partition p1 values less than (2000),
    -> partition p2 values less than maxvalue
    -> );

[root@lnmp mysql]# cd test2
[root@lnmp test2]# ll t11*
-rw-rw---- 1 mysql mysql  8588 11月 16 10:56 t11.frm
-rw-rw---- 1 mysql mysql    96 11月 16 10:56 t11.par
-rw-rw---- 1 mysql mysql 98304 11月 16 10:56 t11#P#p0#SP#p0sp0.ibd     ###可以看到子分区
-rw-rw---- 1 mysql mysql 98304 11月 16 10:56 t11#P#p0#SP#p0sp1.ibd     ###可以看到子分区
-rw-rw---- 1 mysql mysql 98304 11月 16 10:56 t11#P#p1#SP#p1sp0.ibd     ###可以看到子分区
-rw-rw---- 1 mysql mysql 98304 11月 16 10:56 t11#P#p1#SP#p1sp1.ibd     ###可以看到子分区
-rw-rw---- 1 mysql mysql 98304 11月 16 10:56 t11#P#p2#SP#p2sp0.ibd     ###可以看到子分区
-rw-rw---- 1 mysql mysql 98304 11月 16 10:56 t11#P#p2#SP#p2sp1.ibd     ###可以看到子分区

将每个子分区保存在不同的存储之上,优化I/O性能

##先创建两个不同的目录,可以挂在在不同的磁盘之上,这里也就不演示如何挂在磁盘了.
[root@lnmp test2]# mkdir /{disk0,disk1}/{data,idx} -p
 
 [root@lnmp test2]# ll /{disk0,disk1}
/disk0:
总用量 0
drwxr-xr-x 2 root root 6 11月 16 11:05 data
drwxr-xr-x 2 root root 6 11月 16 11:05 idx

/disk1:
总用量 0
drwxr-xr-x 2 root root 6 11月 16 11:05 data
drwxr-xr-x 2 root root 6 11月 16 11:05 idx
###修改目录的权限,设置成mysql,这样的话才有操作的权限

[root@lnmp test2]# chown -R mysql. /disk0 /disk1/ -R
[root@lnmp test2]# ll /{disk0,disk1}
/disk0:
总用量 0
drwxr-xr-x 2 mysql mysql 6 11月 16 11:05 data
drwxr-xr-x 2 mysql mysql 6 11月 16 11:05 idx

/disk1:
总用量 0
drwxr-xr-x 2 mysql mysql 6 11月 16 11:05 data
drwxr-xr-x 2 mysql mysql 6 11月 16 11:05 idx
[root@lnmp test2]# 

mysql> create  table t12 (id int,udate date)
    -> partition by range(year(udate))
    -> subpartition by hash (to_days(udate))
    -> (
    -> partition p01 values less than (1999)
    -> (
    -> subpartition s0
    -> data directory='/disk0/data'
    -> index  directory='/disk0/idx',
    -> subpartition s1
    -> data directory='/disk1/data'
    -> index directory='/disk1/idx'
    -> ),
    -> partition p02 values less than (2000)
    -> (
    -> subpartition s2
    -> data directory='/disk0/data'
    -> index directory='/disk0/idx',
    -> subpartition s3
    -> data directory='/disk1/data'
    -> index directory='/disk1/idx'
    -> )
    -> );
Query OK, 0 rows affected, 4 warnings (0.13 sec)
    
[root@lnmp test2]# ll /{disk0,disk1}/{data,idx}/test2
ls: 无法访问/disk0/idx/test2: 没有那个文件或目录      #由于现在表中没有数据,暂时没有索引文件
ls: 无法访问/disk1/idx/test2: 没有那个文件或目录      #由于现在表中没有数据,暂时没有索引文件
/disk0/data/test2:
总用量 384
-rw-rw---- 1 mysql mysql 98304 11月 16 11:50 t12#P#p01#SP#s0.ibd
-rw-rw---- 1 mysql mysql 98304 11月 16 11:50 t12#P#p02#SP#s2.ibd

/disk1/data/test2:
总用量 384
-rw-rw---- 1 mysql mysql 98304 11月 16 11:50 t12#P#p01#SP#s1.ibd
-rw-rw---- 1 mysql mysql 98304 11月 16 11:50 t12#P#p02#SP#s3.ibd

其实他也是和普通的文件是一样的,为什么会放到别的饿磁盘中呢,是因为在数据库存储数据的目录里面他是有一定的逻辑来找到我们指定的存储路径的,这里以上面t12表做例子

##进入到数据库存储目录
[root@lnmp mysql]# cd /data/mysql/test2/
[root@lnmp test2]# ll t12*
-rw-rw---- 1 mysql mysql 8588 11月 16 11:50 t12.frm
-rw-rw---- 1 mysql mysql   60 11月 16 11:50 t12.par
-rw-rw---- 1 mysql mysql   37 11月 16 11:50 t12#P#p01#SP#s0.isl
-rw-rw---- 1 mysql mysql   37 11月 16 11:50 t12#P#p01#SP#s1.isl
-rw-rw---- 1 mysql mysql   37 11月 16 11:50 t12#P#p02#SP#s2.isl
-rw-rw---- 1 mysql mysql   37 11月 16 11:50 t12#P#p02#SP#s3.isl

[root@lnmp test2]# cat t12#P#p01#SP#s0.isl					##通过cat命令可以看到响应和文件的存储路径,当然也可以通过更改这个路径,来改变子分区的存储位置
/disk0/data/test2/t12#P#p01#SP#s0.ibd
[root@lnmp test2]# 

• 第一步,查询时,数据库会通过.frm找到相应的表的吧结构的定义
• 第二步,通过.par来找到分区的定义
• 第三步,通过相应的.isl找到存储文件所在目录

每个分区必须有相同数量的子分区
如果在一个分区表上面的任何分区上面使用subpartition来明确的定义任何子分区,那么就的定义所有的子分区名称
子分区名称无论是不是在一个分区里面,子分区的名称是不能重复的