mysql8.0.17分区特性测试

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

elcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 34
Server version: 8.0.17 Source distribution
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
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/mysql
mkdir -p /data/mysqldata
mkdir -p /log/mysql
chown -R mysql.mysql /opt/mysql
chown -R mysql.mysql /data/mysqldata
chown -R mysql.mysql /log/mysql
下载mysql和rpcsvc
cd /tmp
wget https://github.com/thkukuk/rpcsvc-proto/releases/download/v1.4/rpcsvc-proto-1.4.tar.gz
tar zxvf rpcsvc-proto-1.4.tar.gz 
cd rpcsvc-proto-1.4
 ./configure 
make 
make install
cd /tmp
wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-boost-8.0.17.tar.gz
安装mysql
tar zxvf mysql-boost-8.0.17.tar.gz
cd mysql-8.0.17
cmake -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=1
make -j 4
make install
设置配置文件
mkdir -p /opt/mysql/etc
cat >/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_tab
insert 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));
相当于删除重建。
            
            
                        

            文章标题：mysql8.0.17分区特性测试            

            文章链接：http://cqcxhl.com/article/pssjhg.html