mysql的分区：使用hash分区

一.何为hash分区：

hash分区的目的是将数据按照某列进行hash计算后更加均匀的分散到各个分区。

二.案例

1.新建表：

CREATE TABLE `user_hash_tb` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(50) NOT NULL,
  `age` int(10) NOT NULL,
  `create_time` datetime NOT NULL,
  `modify_time` datetime NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8
 PARTITION BY HASH (user_id)
PARTITIONS 6 ；

2.添加数据：

insert into user_hash_tb(user_id,user_name,age,create_time,modify_time) values(1,'bb',34,now(),now());

insert into user_hash_tb(user_id,user_name,age,create_time,modify_time) values(2,'bb',34,now(),now());

insert into user_hash_tb(user_id,user_name,age,create_time,modify_time) values(3,'bb',34,now(),now());
insert into user_hash_tb(user_id,user_name,age,create_time,modify_time) values(4,'bb',34,now(),now());
insert into user_hash_tb(user_id,user_name,age,create_time,modify_time) values(5,'bb',34,now(),now());
insert into user_hash_tb(user_id,user_name,age,create_time,modify_time) values(6,'bb',34,now(),now());
insert into user_hash_tb(user_id,user_name,age,create_time,modify_time) values(7,'bb',34,now(),now());
insert into user_hash_tb(user_id,user_name,age,create_time,modify_time) values(8,'bb',34,now(),now());
insert into user_hash_tb(user_id,user_name,age,create_time,modify_time) values(9,'bb',34,now(),now());
insert into user_hash_tb(user_id,user_name,age,create_time,modify_time) values(10,'bb',34,now(),now());
insert into user_hash_tb(user_id,user_name,age,create_time,modify_time) values(11,'bb',34,now(),now());
insert into user_hash_tb(user_id,user_name,age,create_time,modify_time) values(12,'bb',34,now(),now());

insert into user_hash_tb(user_id,user_name,age,create_time,modify_time) values(13,'bb',34,now(),now());

3.查询数据：
 select * from user_hash_tb order by user_id 

 

4.查询分区使用情况：

SELECT
    PARTITION_NAME,
    TABLE_ROWS
FROM
    INFORMATION_SCHEMA. PARTITIONS
WHERE
    TABLE_NAME = 'user_hash_tb';

 

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION 
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='user_hash_tb';