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

elcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis34Serverversion:8.0.17SourcedistributionCopyright(c)2000,2019,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.

Server version: 8.0.17 Source distribution

编译安装脚本

yum-yinstallgccgcc-c++ncurses-devellibtirpc-devellibaio-developensslopenssl-devel增加mysql用户groupadd-g1101mysql;useradd-u1101-gmysqlmysql;mkdir-p/opt/mysqlmkdir-p/data/mysqldatamkdir-p/log/mysqlchown-Rmysql.mysql/opt/mysqlchown-Rmysql.mysql/data/mysqldatachown-Rmysql.mysql/log/mysql下载mysql和rpcsvccd/tmpwgethttps://github.com/thkukuk/rpcsvc-proto/releases/download/v1.4/rpcsvc-proto-1.4.tar.gztarzxvfrpcsvc-proto-1.4.tar.gzcdrpcsvc-proto-1.4./configuremakemakeinstallcd/tmpwgethttps://cdn.mysql.com//Downloads/MySQL-8.0/mysql-boost-8.0.17.tar.gz安装mysqltarzxvfmysql-boost-8.0.17.tar.gzcdmysql-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-j4makeinstall设置配置文件mkdir-p/opt/mysql/etccat>/opt/mysql/etc/my.cnf<<EOF[client]port=3306socket=/data/mysqldata/mysql.sock[mysqld]port=3306socket=/data/mysqldata/mysql.sockdatadir=/data/mysqldata/basedir=/opt/mysqlmysqlx=0#innodbinnodb_data_home_dir=/data/mysqldatainnodb_data_file_path=ibdata1:128M:autoextendinnodb_buffer_pool_size=1000Minnodb_buffer_pool_instances=1innodb_file_per_table=on#innodbloginnodb_log_group_home_dir=/data/mysqldatainnodb_log_file_size=256Minnodb_log_buffer_size=64Minnodb_log_files_in_group=4#innodbzerodatalostvariablesinnodb_flush_log_at_trx_commit=1innodb_doublewrite=onsync_binlog=1master-info-repository=tablerelay-log-info-repository=table#txcommitactionisheavyactionautocommit=ontransaction_isolation=READ-COMMITTEDlower_case_table_names=1bind-address=0.0.0.0#characterinit_connect='SETNAMESutf8mb4'character_set_server=utf8mb4#collation_server=utf8mb4_general_ciopen_files_limit=65535#gtidserver_id=791gtid_mode=onenforce_gtid_consistency=onmaster_info_repository=tablerelay_log_info_repository=table#connectmax_connections=2000max_connect_errors=9999999#sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'#memory#query_cache_size=0M#query_cache_type=0#mysqllogbinlog_format=rowlog_bin=binloglog_timestamps=systemlog_output='file,table'log_error=/log/mysql/error.loggeneral_log=offgeneral_log_file=/log/mysql/general_.logrelay_log=/log/mysql/mysql-relay-bin.logrelay_log_purge=offmax_binlog_size=256Mlog_slave_updates=onbinlog_expire_logs_seconds=604800slow_query_log_file=/log/mysql/slow.logslow_query_log=onlong_query_time=1skip_name_resolve=on#slave#skip_slave_startslave-skip-errors=1062replicate_ignore_db=mysqlreplicate_wild_ignore_table=mysql.%#resourcemax_allowed_packet=128Minnodb_lock_wait_timeout=50#useauditbinlog_rows_query_log_events=onEOF修改目录权限和初始化mysqlchown-Rmysql.mysql/opt/mysqlchown-Rmysql.mysql/data/mysqldatachown-Rmysql.mysql/log/mysql/opt/mysql/bin/mysqld--defaults-file=/opt/mysql/etc/my.cnf--initialize--user=mysql将mysql设置为开机自动启动cp/opt/mysql/support-files/mysql.server/etc/init.d/mysqldchmod+x/etc/init.d/mysqldsystemctlenablemysqld.service#chkconfig--addmysqld重启mysqlsystemctlrestartmysqld.service

二、总体感受：

使用了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 必须是主键列，或者主键的其中几个列，或者是唯一键列。无论创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。也不清楚作者这样设计的初衷是什么。比如以下的写法就是有语法错误：

CREATETABLEt1(col1INTNOTNULL,col2DATENOTNULL,col3INTNOTNULL,col4INTNOTNULL,UNIQUEKEY(col1,col2))PARTITIONBYHASH(col3)PARTITIONS4;CREATETABLEt2(col1INTNOTNULL,col2DATENOTNULL,col3INTNOTNULL,col4INTNOTNULL,UNIQUEKEY(col1),UNIQUEKEY(col3))PARTITIONBYHASH(col1+col3)PARTITIONS4;

这样会大大限制mysql分区使用范围。

三、mysql 分区的种类

1.range 分区，频繁使用。基于属于一个给定连续区间的列值，把多行分配给分区。

CREATETABLEemployees(idINTNOTNULL,fnameVARCHAR(30),lnameVARCHAR(30),hiredDATENOTNULLDEFAULT'1970-01-01',separatedDATENOTNULLDEFAULT'9999-12-31',job_codeINTNOTNULL,store_idINTNOTNULL)PARTITIONBYRANGE(store_id)(PARTITIONp0VALUESLESSTHAN(6),PARTITIONp1VALUESLESSTHAN(11),PARTITIONp2VALUESLESSTHAN(16),PARTITIONp3VALUESLESSTHAN(21));

2. list 分区，比较少使用。类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

CREATETABLEemployees(idINTNOTNULL,fnameVARCHAR(30),lnameVARCHAR(30),hiredDATENOTNULLDEFAULT'1970-01-01',separatedDATENOTNULLDEFAULT'9999-12-31',job_codeINT,store_idINT)PARTITIONBYLIST(store_id)(PARTITIONpNorthVALUESIN(3,5,6,9,17),PARTITIONpEastVALUESIN(1,2,10,11,19,20),PARTITIONpWestVALUESIN(4,12,13,14,18),PARTITIONpCentralVALUESIN(7,8,15,16));

注意看上面，都是没有主键，没有唯一键的。

3.HASH分区，频繁使用：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

CREATETABLEemployees(idINTNOTNULL,fnameVARCHAR(30),lnameVARCHAR(30),hiredDATENOTNULLDEFAULT'1970-01-01',separatedDATENOTNULLDEFAULT'9999-12-31',job_codeINT,store_idINT)PARTITIONBYHASH(YEAR(hired))PARTITIONS4;

4.KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

CREATETABLEk1(idINTNOTNULLPRIMARYKEY,nameVARCHAR(20))PARTITIONBYKEY()PARTITIONS2;CREATETABLEk1(idINTNOTNULL,nameVARCHAR(20),UNIQUEKEY(id))PARTITIONBYKEY()PARTITIONS2;

上面两个例子你会觉得很奇怪，都没有指定分区列。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必须带上分区键，否则使用不到分区的好处了。我们来看下例子：

CREATETABLEpart_tab(c1intdefaultNULL,c2varchar(30)defaultNULL,c3datenotnull)PARTITIONBYRANGE(year(c3))(PARTITIONp0VALUESLESSTHAN(1995),PARTITIONp1VALUESLESSTHAN(1996),PARTITIONp2VALUESLESSTHAN(1997),PARTITIONp3VALUESLESSTHAN(1998),PARTITIONp4VALUESLESSTHAN(1999),PARTITIONp5VALUESLESSTHAN(2000),PARTITIONp6VALUESLESSTHAN(2001),PARTITIONp7VALUESLESSTHAN(2002),PARTITIONp8VALUESLESSTHAN(2003),PARTITIONp9VALUESLESSTHAN(2004),PARTITIONp10VALUESLESSTHAN(2010),PARTITIONp11VALUESLESSTHAN(MAXVALUE));CREATETABLEno_part_tab(c1intdefaultNULL,c2varchar(30)defaultNULL,c3datenotnull);dropprocedureload_part_tab;delimiter$$CREATEPROCEDUREload_part_tab()begindeclarevintdefault0;whilev<8000000doinsertintopart_tabvalues(v,'testingpartitions',adddate('1995-01-01',(rand(v)*36520)mod3652));setv=v+1;endwhile;end;$$delimiter;callload_part_tab();//从part_tab导入数据到no_part_tabinsertintono_part_tabselect*frompart_tab;

创建了2个表，数据都是800万。

一个表no_part_tab的大小是428M。在这里另外

mysql>explainselectcount(*)frompart_tabwherec3>date'1995-01-01'andc3<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|Usingwhere|+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+1rowinset,1warning(0.00sec)mysql>explainselectcount(*)fromno_part_tabwherec3>date'1995-01-01'andc3<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|Usingwhere|+----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+-------------+1rowinset,1warning(0.01sec)mysql>selectcount(*)frompart_tabwherec3>date'1995-01-01'andc3<date'1995-12-31';+----------+|count(*)|+----------+|795181|+----------+1rowinset(0.39sec)mysql>selectcount(*)fromno_part_tabwherec3>date'1995-01-01'andc3<date'1995-12-31';+----------+|count(*)|+----------+|795181|+----------+1rowinset(2.94sec)

查询时间和扫描的行数，高下可判。

六、mysql分区操作，在mysql8测试通过序号常见操作举例备注1删除分区1) aher table emp drop partitionp1;
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 p1values less than (24));
alter table emp add partition partition p3 values in (40));1)增加分区的值只能增加，不能比现在所拥有的分区值低3分解分区alter table emp reorganizepartition p2 into
(partition p1 values less than (6),
partition p2 values less than (16));reorganizepartition关键字可以对表的部分分区或全部分区进行修
改，并且不会丢失数据。分解前后分区的整体范围应该一致。4合并分区alter table emp reorganizepartition p1,p3 into (partition p1 values less than (1000));不会丢失数据5重新定义分区重新定义Hash分区：Alter table emppartition by hash(salary) partitions7;
重新定义Range分区：
Alter table emp partition by range(id) (partition p1 values less than(2000), partition p2 values less than (4000));相当于删除重建。