mysql8.0.17分區(qū)特性測試

一、下面所有的測試基于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

創(chuàng)新互聯(lián)堅持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：成都做網(wǎng)站、成都網(wǎng)站制作、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時代的柯城網(wǎng)站設(shè)計、移動媒體設(shè)計的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！

編譯安裝腳本

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
設(shè)置配置文件
mkdir -p /opt/mysql/etc
cat >/opt/mysql/etc/my.cnf <<EOF 
[client]
port            = 3306
socket          = /data/mysqldata/mysql.sock
[mysqld]
port            = 3306
socket          = /data/mysqldata/mysql.sock
datadir=/data/mysqldata/
basedir=/opt/mysql
mysqlx=0
#innodb
innodb_data_home_dir = /data/mysqldata
innodb_data_file_path = ibdata1:128M:autoextend
innodb_buffer_pool_size = 1000M
innodb_buffer_pool_instances=1
innodb_file_per_table=on
#innodb log 
innodb_log_group_home_dir = /data/mysqldata
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M
innodb_log_files_in_group=4
#innodb zero data lost variables
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite=on
sync_binlog=1
master-info-repository=table
relay-log-info-repository=table
#tx  commit action is  heavy action
autocommit=on
transaction_isolation=READ-COMMITTED
lower_case_table_names=1  
bind-address = 0.0.0.0
#character
init_connect = 'SET NAMES utf8mb4'
character_set_server=utf8mb4
#collation_server=utf8mb4_general_ci
open_files_limit = 65535
#gtid
server_id=791
gtid_mode=on
enforce_gtid_consistency=on
master_info_repository=table
relay_log_info_repository=table
#connect
max_connections         = 2000
max_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
#mysql log
binlog_format=row
log_bin                 = binlog
log_timestamps=system
log_output='file,table'
log_error=/log/mysql/error.log
general_log=off
general_log_file=/log/mysql/general_.log
relay_log               = /log/mysql/mysql-relay-bin.log
relay_log_purge     =off
max_binlog_size         = 256M
log_slave_updates=on
binlog_expire_logs_seconds = 604800
slow_query_log_file=/log/mysql/slow.log
slow_query_log=on
long_query_time=1
skip_name_resolve=on
#slave
#skip_slave_start
slave-skip-errors=1062
replicate_ignore_db=mysql
replicate_wild_ignore_table=mysql.%
#resource
max_allowed_packet = 128M
innodb_lock_wait_timeout = 50
#use audit
binlog_rows_query_log_events=on
EOF
修改目錄權(quán)限和初始化mysql
chown -R mysql.mysql /opt/mysql
chown -R mysql.mysql /data/mysqldata
chown -R mysql.mysql /log/mysql
 /opt/mysql/bin/mysqld  --defaults-file=/opt/mysql/etc/my.cnf  --initialize  --user=mysql
將mysql設(shè)置為開機(jī)自動啟動
cp /opt/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
systemctl enable mysqld.service
#chkconfig --add mysqld
重啟mysql
systemctl restart mysqld.service

二、總體感受：

使用了mysql的分區(qū)，覺得最不爽的是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

意思就是：用于分區(qū)的column 必須是主鍵列，或者主鍵的其中幾個列，或者是唯一鍵列。無論創(chuàng)建何種類型的分區(qū)，如果表中存在主鍵或唯一索引時，分區(qū)列必須是唯一索引的一個組成部分。也不清楚作者這樣設(shè)計的初衷是什么。比如以下的寫法就是有語法錯誤：

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分區(qū)使用范圍。

三、mysql 分區(qū)的種類

  1.range 分區(qū)，頻繁使用。 基于屬于一個給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。

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 分區(qū)，比較少使用。類似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個離散值集合中的某個值來進(jìn)行選擇。

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分區(qū)，頻繁使用：基于用戶定義的表達(dá)式的返回值來進(jìn)行選擇的分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計算。這個函數(shù)可以包含MySQL 中有效的、產(chǎn)生非負(fù)整數(shù)值的任何表達(dá)式。

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分區(qū)：類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計算一列或多列，且MySQL服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。

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;

上面兩個例子你會覺得很奇怪，都沒有指定分區(qū)列。mysql默認(rèn)就使用了唯一鍵來做了分區(qū)。

四、表分區(qū)的優(yōu)點(diǎn)

1）當(dāng)數(shù)據(jù)量很大(過T)時，肯定不能把數(shù)據(jù)再如到內(nèi)存中，這樣查詢一個或一定范圍的item是很耗時。另外一般這情況下，歷史數(shù)據(jù)或不常訪問的數(shù)據(jù)占很大部分，最新或熱點(diǎn)數(shù)據(jù)占的比例不是很大。這時可以根據(jù)有些條件進(jìn)行表分區(qū)。

2）分區(qū)表的更易管理，比如刪除過去某一時間的歷史數(shù)據(jù)，直接執(zhí)行truncate，或者狠點(diǎn)drop整個分區(qū)，這比detele刪除效率更高

3）當(dāng)數(shù)據(jù)量很大，或者將來很大的，但單塊磁盤的容量不夠，或者想提升IO效率的時候，可以把沒分區(qū)中的子分區(qū)掛載到不同的磁盤上。

4）使用分區(qū)表可避免某些特殊的瓶頸，例如Innodb的單個索引的互斥訪問..

5）單個分區(qū)表的備份很恢復(fù)會更有效率，在某些場景下

6）涉及到例如SUM()和COUNT()這樣聚合函數(shù)的查詢，可以很容易地進(jìn)行并行處理。

這種查詢的一個簡單例子如

“SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。

通過“并行”，這意味著該查詢可以在每個分區(qū)上同時進(jìn)行，最終結(jié)果只需通過總計所有分區(qū)得到的結(jié)果。

五、表分區(qū)的查詢優(yōu)化

 表分區(qū)了，查詢where必須帶上分區(qū)鍵，否則使用不到分區(qū)的好處了。我們來看下例子：

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 導(dǎo)入數(shù)據(jù)到 no_part_tab
insert into no_part_tab select * from part_tab;

創(chuàng)建了2個表，數(shù)據(jù)都是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)

查詢時間和掃描的行數(shù)，高下可判。

六、mysql分區(qū)操作，在mysql8測試通過

序號	常見操作	舉例	備注
1	刪除分區(qū)	1) aher table emp drop partition   p1;     2) 一次性刪除各個區(qū)：alter table emp drop partition p1,p2;     3) 刪除表的所有分區(qū):Alter table emp remove partitioning;	1)不可以刪除hash或者kev分區(qū)。     2)刪除分區(qū)會刪除數(shù)據(jù)，但是刪除表的所有分區(qū)--不會丟失數(shù)據(jù)（驗證ok)
2	增加分區(qū)	alter   table emp add partition (partition p1   values less than (24));      alter table emp  add partition partition p3 values in (40));	1)增加分區(qū)的值只能增加，不能比現(xiàn)在所擁有的分區(qū)值低
3	分解分區(qū)	alter table emp reorganize   partition p2 into     (partition p1 values less than (6),     partition p2 values less than (16));	reorganize   partition關(guān)鍵字可以對表的部分分區(qū)或全部分區(qū)進(jìn)行修     改，并且不會丟失數(shù)據(jù)。分解前后分區(qū)的整體范圍應(yīng)該一致。
4	合并分區(qū)	alter table emp reorganize   partition p1,p3 into (partition p1 values less than (1000));	不會丟失數(shù)據(jù)
5	重新定義分區(qū)	重新定義Hash分區(qū)：Alter table emp   partition by hash(salary)  partitions   7;     重新定義Range分區(qū)：     Alter table emp partition by range(id) (partition p1 values less than   (2000), partition p2 values less than (4000));	相當(dāng)于刪除重建。

分享標(biāo)題：mysql8.0.17分區(qū)特性測試
本文URL：http://bm7419.com/article46/pssjhg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供商城網(wǎng)站、外貿(mào)建站、關(guān)鍵詞優(yōu)化、虛擬主機(jī)、定制網(wǎng)站、服務(wù)器托管

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)