Shulou(Shulou.com)06/01 Report--

1. All the tests below are based on mysql version 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

Compile the installation script

Yum-y install gcc gcc-c++ ncurses-devel libtirpc-devel libaio-devel openssl openssl-devel adds mysql users 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 download mysql and 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 installs 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 Settings profile mkdir-p / opt/mysql/etccat > / opt/mysql/etc/my.cnf 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 row in set + | count (*) | +-+ | 795181 | +-+ 1 sec)

The query time and the number of rows scanned can be judged.

6. Mysql partition operation. Pass serial number in mysql8 test. Note 1: delete partition 1) aher table emp drop partition p1

2) delete each zone at once: alter table emp drop partition p1p2

3) delete all partitions of the table: Alter table emp remove partitioning;1) you cannot delete hash or kev partitions.

2) deleting a partition deletes the data, but deletes all partitions of the table-no data is lost (verify ok) 2 add partition alter table emp add partition (partition p1 values less than (24))

Alter table emp add partition partition p3 values in (40); 1) increasing the partition value can only increase the partition value, not lower than the partition value you now have. 3 decompose the partition alter table emp reorganize partition p2 into

(partition p1 values less than (6)

Partition p2 values less than (16); the reorganize partition keyword can modify some or all partitions of the table

Change it without losing data. The overall scope of the partition should be consistent before and after decomposition. 4 merge partition alter table emp reorganize partition p1 and p3 into (partition p1 values less than (1000)); do not lose data 5 redefine partition redefine Hash partition: Alter table emp partition by hash (salary) partitions 7

Redefine the Range partition:

Alter table emp partition by range (id) (partition p1 values less than (2000), partition p2 values less than (4000)); equivalent to deletion and reconstruction.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.