MySQL學習mysql分區

測試資料準備800萬條資料，這裡通過存儲過程導入資料

do
        insert into part_tab
        values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652));
         set v = v + 1;
    end while;
    end
	
 delimiter ;
 call load_part_tab();

#導入末分區表中資料

 insert into no_part_tab select * from part_tab;

#查詢資料庫的數量

  SELECT count(*) FROM part_tab;   //8000000

#測試sql性能

#分區之後的資料時間

   select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';

花費時間 0.138s

#未分區的資料

select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';

花費時間1.166s

通過explain關鍵字進行分析

分區表的資料分析

EXPLAIN  select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';

MySQL學習mysql分區

未分區表的資料分析

 explain select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';

結果如下：

MySQL學習mysql分區

對沒有分區的資料進行加索引進行測試

create index idx_of_c3 on no_part_tab (c3)

#建立索引執行時間24.008s

建立索引執行

select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';

花費時間：0.198s

為了驗證對未使用是以你的額兩個查詢再次進行校驗：

 select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date

'1996-12-31' and c2='hello';

#0.291s

select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1996-12-31' and c2='hello';

#查詢時間1.216s

= 初步結論 =

* 分區和未分區占用檔案空間大緻相同（資料和索引檔案）

* 如果查詢語句中有未建立索引字段，分區時間遠遠優于未分區時間

* 如果查詢語句中字段建立了索引，分區和未分區的差别縮小，分區略優于未分區。

= 最終結論 =

* 對于大資料量，建議使用分區功能。

* 去除不必要的字段

* 根據手冊，增加myisam_max_sort_file_size 會增加分區性能

[分區指令詳解]

= 分區例子 =

* RANGE 類型

CREATE TABLE users (

uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(30) NOT NULL DEFAULT '',

email VARCHAR(30) NOT NULL DEFAULT ''

)

PARTITION BY RANGE (uid) (

PARTITION p0 VALUES LESS THAN (3000000)

DATA DIRECTORY = '/data0/data'

INDEX DIRECTORY = '/data1/idx',

PARTITION p1 VALUES LESS THAN (6000000)

DATA DIRECTORY = '/data2/data'

INDEX DIRECTORY = '/data3/idx',

PARTITION p2 VALUES LESS THAN (9000000)

DATA DIRECTORY = '/data4/data'

INDEX DIRECTORY = '/data5/idx',

PARTITION p3 VALUES LESS THAN MAXVALUE DATA DIRECTORY = '/data6/data'

INDEX DIRECTORY = '/data7/idx'

);

在這裡，将使用者表分成4個分區，以每300萬條記錄為界限，每個分區都有自己獨立的資料、索引檔案的存放目錄，與此同時，這些目錄所在的實體磁盤分區可能也都是完全獨立的，可以提高磁盤IO吞吐量。

* LIST 類型

CREATE TABLE category (

cid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(30) NOT NULL DEFAULT ''

)

PARTITION BY LIST (cid) (

PARTITION p0 VALUES IN (0,4,8,12)

DATA DIRECTORY = '/data0/data'

INDEX DIRECTORY = '/data1/idx',

PARTITION p1 VALUES IN (1,5,9,13)

DATA DIRECTORY = '/data2/data'

INDEX DIRECTORY = '/data3/idx',

PARTITION p2 VALUES IN (2,6,10,14)

DATA DIRECTORY = '/data4/data'

INDEX DIRECTORY = '/data5/idx',

PARTITION p3 VALUES IN (3,7,11,15)

DATA DIRECTORY = '/data6/data'

INDEX DIRECTORY = '/data7/idx'

);

分成4個區，資料檔案和索引檔案單獨存放。

* HASH 類型

CREATE TABLE users (

uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(30) NOT NULL DEFAULT '',

email VARCHAR(30) NOT NULL DEFAULT ''

)

PARTITION BY HASH (uid) PARTITIONS 4 (

PARTITION p0

DATA DIRECTORY = '/data0/data'

INDEX DIRECTORY = '/data1/idx',

PARTITION p1

DATA DIRECTORY = '/data2/data'

INDEX DIRECTORY = '/data3/idx',

PARTITION p2

DATA DIRECTORY = '/data4/data'

INDEX DIRECTORY = '/data5/idx',

PARTITION p3

DATA DIRECTORY = '/data6/data'

INDEX DIRECTORY = '/data7/idx'

);

分成4個區，資料檔案和索引檔案單獨存放。

例子：

CREATE TABLE ti2 (id INT, amount DECIMAL(7,2), tr_date DATE)

ENGINE=myisam

PARTITION BY HASH( MONTH(tr_date) )

PARTITIONS 6;

CREATE PROCEDURE load_ti2()

begin

declare v int default 0;

while v < 80000

insert into ti2

values (v,'3.14',adddate('1995-01-01',(rand(v)*3652) mod 365));

set v = v + 1;

end while;

end

* KEY 類型

CREATE TABLE users (

uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(30) NOT NULL DEFAULT '',

email VARCHAR(30) NOT NULL DEFAULT ''

)

PARTITION BY KEY (uid) PARTITIONS 4 (

PARTITION p0

DATA DIRECTORY = '/data0/data'

INDEX DIRECTORY = '/data1/idx',

PARTITION p1

DATA DIRECTORY = '/data2/data'

INDEX DIRECTORY = '/data3/idx',

PARTITION p2

DATA DIRECTORY = '/data4/data'

INDEX DIRECTORY = '/data5/idx',

PARTITION p3

DATA DIRECTORY = '/data6/data'

INDEX DIRECTORY = '/data7/idx'

);

分成4個區，資料檔案和索引檔案單獨存放。

* 子分區

子分區是針對 RANGE/LIST 類型的分區表中每個分區的再次分割。再次分割可以是 HASH/KEY 等類型。例如：

CREATE TABLE users (

uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(30) NOT NULL DEFAULT '',

email VARCHAR(30) NOT NULL DEFAULT ''

)

PARTITION BY RANGE (uid) SUBPARTITION BY HASH (uid % 4) SUBPARTITIONS 2(

PARTITION p0 VALUES LESS THAN (3000000)

DATA DIRECTORY = '/data0/data'

INDEX DIRECTORY = '/data1/idx',

PARTITION p1 VALUES LESS THAN (6000000)

DATA DIRECTORY = '/data2/data'

INDEX DIRECTORY = '/data3/idx'

);

對 RANGE 分區再次進行子分區劃分，子分區采用 HASH 類型。

或者

CREATE TABLE users (

uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(30) NOT NULL DEFAULT '',

email VARCHAR(30) NOT NULL DEFAULT ''

)

PARTITION BY RANGE (uid) SUBPARTITION BY KEY(uid) SUBPARTITIONS 2(

PARTITION p0 VALUES LESS THAN (3000000)

DATA DIRECTORY = '/data0/data'

INDEX DIRECTORY = '/data1/idx',

PARTITION p1 VALUES LESS THAN (6000000)

DATA DIRECTORY = '/data2/data'

INDEX DIRECTORY = '/data3/idx'

);

對 RANGE 分區再次進行子分區劃分，子分區采用 KEY 類型。

= 分區管理 =

* 删除分區

ALERT TABLE users DROP PARTITION p0;

删除分區 p0。

* 重建分區

o RANGE 分區重建

ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));

将原來的 p0,p1 分區合并起來，放到新的 p0 分區中。

o LIST 分區重建

ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));

将原來的 p0,p1 分區合并起來，放到新的 p0 分區中。

o HASH/KEY 分區重建

ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;

用 REORGANIZE 方式重建分區的數量變成2，在這裡數量隻能減少不能增加。想要增加可以用 ADD PARTITION 方法。

* 新增分區

o 新增 RANGE 分區

ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19)

DATA DIRECTORY = '/data8/data'

INDEX DIRECTORY = '/data9/idx');

新增一個RANGE分區。

o 新增 HASH/KEY 分區

ALTER TABLE users ADD PARTITION PARTITIONS 8;

将分區總數擴充到8個。

[ 給已有的表加上分區 ]

alter table results partition by RANGE (month(ttime))

(PARTITION p0 VALUES LESS THAN (1),

PARTITION p1 VALUES LESS THAN (2) , PARTITION p2 VALUES LESS THAN (3) ,

PARTITION p3 VALUES LESS THAN (4) , PARTITION p4 VALUES LESS THAN (5) ,

PARTITION p5 VALUES LESS THAN (6) , PARTITION p6 VALUES LESS THAN (7) ,

PARTITION p7 VALUES LESS THAN (8) , PARTITION p8 VALUES LESS THAN (9) ,

PARTITION p9 VALUES LESS THAN (10) , PARTITION p10 VALUES LESS THAN (11),

PARTITION p11 VALUES LESS THAN (12),

PARTITION P12 VALUES LESS THAN (13) );

預設分區限制分區字段必須是主鍵（PRIMARY KEY)的一部分，為了去除此

限制：

[方法1] 使用ID

mysql> ALTER TABLE np_pk

-> PARTITION BY HASH( TO_DAYS(added) )

-> PARTITIONS 4;

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

However, this statement using the id column for the partitioning column is valid, as shown here:

mysql> ALTER TABLE np_pk

-> PARTITION BY HASH(id)

-> PARTITIONS 4;

Query OK, 0 rows affected (0.11 sec)

Records: 0 Duplicates: 0 Warnings: 0

[方法2] 将原有PK去掉生成新PK

mysql> alter table results drop PRIMARY KEY;

Query OK, 5374850 rows affected (7 min 4.05 sec)

Records: 5374850 Duplicates: 0 Warnings: 0

mysql> alter table results add PRIMARY KEY(id, ttime);

Query OK, 5374850 rows affected (6 min 14.86 sec)

Records: 5374850 Duplicates: 0 Warnings: 0

轉自https://blog.csdn.net/tjcyjd/article/details/11194489

MySQL學習mysql分區

繼續閱讀

2022秋招面試總結（cpp+java+測開）百度測開一面位元組後端一面蝦皮後端一面蝦皮後端二面

資料庫之DDL操作資料庫DDL操作資料庫DDL操作資料表

資料庫之DQL操作資料庫

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案