mysql 8.0.17 分区特性测试

一、下面所有的测试基于mysql 8.0.17版本。

elcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 34Server version: 8.0.17 Source distributionCopyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Server version: 8.0.17 Source distribution

编译安装脚本

yum -y  install gcc gcc-c++ ncurses-devel  libtirpc-devel  libaio-devel openssl openssl-devel增加mysql用户groupadd -g 1101 mysql  ; useradd -u 1101 -g mysql mysql ;mkdir -p /opt/mysqlmkdir -p /data/mysqldatamkdir -p /log/mysqlchown -R mysql.mysql /opt/mysqlchown -R mysql.mysql /data/mysqldatachown -R mysql.mysql /log/mysql下载mysql和rpcsvccd /tmpwget https://github.com/thkukuk/rpcsvc-proto/releases/download/v1.4/rpcsvc-proto-1.4.tar.gztar zxvf rpcsvc-proto-1.4.tar.gz cd rpcsvc-proto-1.4 ./configure make make installcd /tmpwget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-boost-8.0.17.tar.gz安装mysqltar zxvf mysql-boost-8.0.17.tar.gzcd mysql-8.0.17cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql \-DINSTALL_PLUGINDIR=/opt/mysql/lib/plugin \-DMYSQL_DATADIR=/data/mysqldata \-DWITH_MYISAM_STORAGE_ENGINE=1 \-DWITH_INNOBASE_STORAGE_ENGINE=1 \-DDEFAULT_CHARSET=utf8mb4 \-DDEFAULT_COLLATION=utf8mb4_general_ci \-DBUILD_CONFIG=mysql_release \-DWITH_SSL=system \-DWITH_ZLIB=system \-DCMAKE_BUILD_TYPE=RelWithDebInfo \-DWITH_BOOST=/tmp/mysql-8.0.17/boost/boost_1_69_0 \-DFORCE_INSOURCE_BUILD=1make -j 4make install设置配置文件mkdir -p /opt/mysql/etccat >/opt/mysql/etc/my.cnf <
二、总体感受：
使用了mysql的分区，觉得最不爽的是mysql partition 限制：
A UNIQUE INDEX must include all columns in the table's partitioning function
A PRIMARY KEY must include all columns in the table's partitioning function
意思就是：用于分区的column 必须是主键列，或者主键的其中几个列，或者是唯一键列。无论创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。也不清楚作者这样设计的初衷是什么。比如以下的写法就是有语法错误：
CREATE TABLE t1 (col1 INT NOT NULL,col2 DATE NOT NULL,col3 INT NOT NULL,col4 INT NOT NULL,UNIQUE KEY (col1, col2))PARTITION BY HASH(col3)PARTITIONS 4;CREATE TABLE t2 (col1 INT NOT NULL,col2 DATE NOT NULL,col3 INT NOT NULL,col4 INT NOT NULL,UNIQUE KEY (col1),UNIQUE KEY (col3))PARTITION BY HASH(col1 + col3)PARTITIONS 4;
这样会大大限制mysql分区使用范围。
三、mysql 分区的种类
1.range 分区，频繁使用。 基于属于一个给定连续区间的列值，把多行分配给分区。
CREATE TABLE employees (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 NOT NULL,store_id INT NOT NULL)PARTITION BY RANGE (store_id) (PARTITION p0 VALUES LESS THAN (6),PARTITION p1 VALUES LESS THAN (11),PARTITION p2 VALUES LESS THAN (16),PARTITION p3 VALUES LESS THAN (21));
2. list 分区，比较少使用。类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
CREATE TABLE employees (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 LIST(store_id) (PARTITION pNorth VALUES IN (3,5,6,9,17),PARTITION pEast VALUES IN (1,2,10,11,19,20),PARTITION pWest VALUES IN (4,12,13,14,18),PARTITION pCentral VALUES IN (7,8,15,16));
注意看上面，都是没有主键，没有唯一键的。
3.HASH分区，频繁使用：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
CREATE TABLE employees (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 HASH( YEAR(hired) )PARTITIONS 4;
4.KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
CREATE TABLE k1 (id INT NOT NULL PRIMARY KEY,name VARCHAR(20))PARTITION BY KEY()PARTITIONS 2;CREATE TABLE k1 (id INT NOT NULL,name VARCHAR(20),UNIQUE KEY (id))PARTITION BY KEY()PARTITIONS 2;
上面两个例子你会觉得很奇怪，都没有指定分区列。mysql默认就使用了唯一键来做了分区。
四、表分区的优点
1）当数据量很大(过T)时，肯定不能把数据再如到内存中，这样查询一个或一定范围的item是很耗时。另外一般这情况下，历史数据或不常访问的数据占很大部分，最新或热点数据占的比例不是很大。这时可以根据有些条件进行表分区。
2）分区表的更易管理，比如删除过去某一时间的历史数据，直接执行truncate，或者狠点drop整个分区，这比detele删除效率更高
3）当数据量很大，或者将来很大的，但单块磁盘的容量不够，或者想提升IO效率的时候，可以把没分区中的子分区挂载到不同的磁盘上。
4）使用分区表可避免某些特殊的瓶颈，例如Innodb的单个索引的互斥访问..
5）单个分区表的备份很恢复会更有效率，在某些场景下
6）涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。
这种查询的一个简单例子如
"SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；"。
通过"并行"，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。
五、表分区的查询优化
表分区了，查询where必须带上分区键，否则使用不到分区的好处了。我们来看下例子：
CREATE TABLE part_tab(c1 int default NULL, c2 varchar(30) default NULL, c3 date not null) PARTITION BY RANGE(year(c3))(PARTITION p0 VALUES LESS THAN (1995),PARTITION p1 VALUES LESS THAN (1996) ,PARTITION p2 VALUES LESS THAN (1997) ,PARTITION p3 VALUES LESS THAN (1998) ,PARTITION p4 VALUES LESS THAN (1999) ,PARTITION p5 VALUES LESS THAN (2000) ,PARTITION p6 VALUES LESS THAN (2001) ,PARTITION p7 VALUES LESS THAN (2002) ,PARTITION p8 VALUES LESS THAN (2003) ,PARTITION p9 VALUES LESS THAN (2004) ,PARTITION p10 VALUES LESS THAN (2010),PARTITION p11 VALUES LESS THAN (MAXVALUE) );CREATE TABLE no_part_tab(c1 int default NULL, c2 varchar(30) default NULL, c3 date not null);drop procedure  load_part_tab;delimiter $$CREATE  PROCEDURE load_part_tab()  begin  declare v int default 0;  while v < 8000000  do  insert into part_tab  values (v,'testingpartitions',adddate('1995-01-01',(rand(v)*36520)mod 3652));  set v = v + 1;  end while;end;$$delimiter ;call load_part_tab();//从 part_tab 导入数据到 no_part_tabinsert into no_part_tab select * from part_tab;
创建了2个表，数据都是800万。
一个表no_part_tab的大小是428M。在这里另外
mysql> explain select count(*) from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+|  1 | SIMPLE      | part_tab | p1         | ALL  | NULL          | NULL | NULL    | NULL | 796215 |    11.11 | Using where |+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+1 row in set, 1 warning (0.00 sec)mysql> explain select count(*) from no_part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';+----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+-------------+| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |+----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+-------------+|  1 | SIMPLE      | no_part_tab | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 7773613 |    11.11 | Using where |+----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+-------------+1 row in set, 1 warning (0.01 sec)mysql> select count(*) from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';+----------+| count(*) |+----------+|   795181 |+----------+1 row in set (0.39 sec)mysql> select count(*) from no_part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';+----------+| count(*) |+----------+|   795181 |+----------+1 row in set (2.94 sec)
查询时间和扫描的行数，高下可判。
六、mysql分区操作，在mysql8测试通过
序号
常见操作
举例
备注
1
删除分区
1) aher table emp drop partition p1;
2) 一次性删除各个区：alter table emp drop partition p1,p2;
3) 删除表的所有分区:Alter table emp remove partitioning;
1)不可以删除hash或者kev分区。
2)删除分区会删除数据，但是删除表的所有分区--不会丢失数据（验证ok)
2
增加分区
alter table emp add partition (partition p1 values less than (24));
alter table emp add partition partition p3 values in (40));
1)增加分区的值只能增加，不能比现在所拥有的分区值低
3
分解分区
alter table emp reorganize partition p2 into
(partition p1 values less than (6),
partition p2 values less than (16));
reorganize partition关键字可以对表的部分分区或全部分区进行修
改，并且不会丢失数据。分解前后分区的整体范围应该一致。
4
合并分区
alter table emp reorganize partition p1,p3 into (partition p1 values less than (1000));
不会丢失数据
5
重新定义分区
重新定义Hash分区：Alter table emp partition by hash(salary) partitions 7;
重新定义Range分区：
Alter table emp partition by range(id) (partition p1 values less than (2000), partition p2 values less than (4000));
相当于删除重建。