mysql 创建分区索引吗_mysql创建分区索引

该楼层疑似违规已被系统折叠 隐藏此楼查看此楼

mysql创建分区索引

(一)分区表:

把所有的数据放在一个表中,但是物理存储数据会根据一定规则存放到不同的文件中

(二)什么时候使用分区表?

数据比较大时候,数以亿记或者数以TB记的数据,如果使用索引在空间和维护消耗巨大,甚至索引没有效果了.

例子:

查看是否支持分区

show variables like '%partition%';

创建一个普通表

create table no_part_tab(

id int not null,

username varchar(50) not null,

d_date date not null

)

创建存储过程插入数据

DELIMITER #

CREATE PROCEDURE no_part_tab_pro()

BEGIN

DECLARE i INT;

SET i=1;

WHILE i<=10000 DO

INSERT INTO no_part_tab VALUES(i, CONCAT('no_part',i), ADDDATE('2007‐01‐01', (RAND(i)*36520)MOD 3652));

SET i=i+1;

END WHILE;

END#

CALL no_part_tab_pro();

创建一个分区表

CREATE TABLE part_tab(

id INT NOT NULL,

username VARCHAR(50) NOT NULL,

d_date DATE NOT NULL)

PARTITION BY RANGE(YEAR(d_date))(

PARTITION p0 VALUES LESS THAN(2007),

PARTITION p1 VALUES LESS THAN(2008),

PARTITION p2 VALUES LESS THAN(2009),

PARTITION p3 VALUES LESS THAN(2010),

PARTITION p4 VALUES LESS THAN(2011),

PARTITION p5 VALUES LESS THAN(2012),

PARTITION p6 VALUES LESS THAN(2013),

PARTITION p7 VALUES LESS THAN(2014),

PARTITION p8 VALUES LESS THAN(2015),

PARTITION p9 VALUES LESS THAN(2016),

PARTITION p10 VALUES LESS THAN maxvalue

PARTITION p10 VALUES LESS THAN maxvalue

);

创建存储过程插入数据

DELIMITER #

CREATE PROCEDURE part_tab_pro()

BEGIN

DECLARE i INT;

SET i=1;

WHILE i<=10000 DO

INSERT INTO part_tab VALUES(i, CONCAT('no_part',i), ADDDATE('2007‐01‐01', (RAND(i)*36520) MOD 3652));

SET i=i+1;

END WHILE;

END#

CALL part_tab_pro();

分别查询两个表中数据,使用explain partitions

explain partitions select * from no_part_tab where d_date>DATE '2007‐01‐01' AND d_date

EXPLAIN PARTITIONS SELECT * FROM part_tab WHERE d_date>DATE '2007‐01‐01' AND d_date

(三)常见的分区:

1.range分区

2.list分区:

CREATE TABLE list_tab(

id INT NOT NULL,

username VARCHAR(20) NOT NULL

)

PARTITION BY LIST(id)(

PARTITION p0 VALUES IN(1,2,3), #区间值不能重复

PARTITION p1 VALUES IN(4,5,6)

)

3.hash分区

CREATE TABLE hash_tab(

id INT NOT NULL,

username VARCHAR(20) NOT NULL

)

PARTITION BY HASH(id)

PARTITIONS 4;

4.key分区

CREATE TABLE key_tab(

id INT NOT NULL,

username VARCHAR(20) NOT NULL

)

PARTITION BY KEY(id)

PARTITIONS 3;