mysql range hash分库+分表+分区

一、方法

<?php

/**
*@Description 该系列函数，用于库表分离下，根据主键id获取库和表
*@param $id int 主键id 必须
*@param $tbName string 必须
*@param $dbName string 非必须，默认db开头
*@return array db名称 表名称
*/

$dbNum=2;
$tbNum=5;
$perNum=1000;
// 数据库名称类似：db1,db2...表名字类似 user1,user2...

// range获取库和表
function range_insert($id, $tbName ,$dbName='db'){
	$tb = $id % ($dbNum * $tbNum) + 1;
	$db = ceil($tb / $tbNum);
	return [$dbName.$db, $dbName.$db.'_'.$tbName.'_'.$tb];
}

// 哈希获取库和表
function hash_insert($id, $tbName, $dbName='db'){
   $str = decbin($id) >> 2;
   $str = bindec($str);
   $str = sprintf('%08d',$str);
   $num = crc32($str2);
   $tb = $num % ($dbNum * $tbNum) + 1;
   $db = ceil($tb / $tbNum);
   
   return [$dbName.$db, $dbName.'_'.$tbName.'_'.$tb];
}

//线性哈希获取库和表
function linearHash_insert($id, $tbName, $dbName='db'){
   $v = pow(2,ceil(log( $dbNum * $tbNum,2)));
   $p = $id & ($v - 1);
   
   if($p >= $dbNum * $tbNum){
      $p = $p & (ceil($dbNum * $tbNum / 2) - 1);
   }
   
   $tb = $p % ($dbNum * $tbNum) + 1;
   $db = ceil($tb / $tbNum);
   
   return [$dbName.$db, $dbName.$db.'_'.$tbName.'_'.$tb];
}

//获取字符串
function getStr($num){
	$string = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
	$str = '';
	for($i=0;$i<$num;$i++){
		
		$str .= substr($string,mt_rand(0,61),1);
	}
	
	return $str;

}?>

二、调用

<?php
include 'partition_db_func.php';

$con = mysqli_connect("localhost","root","admin123987+..");

if(!con){
	die('数据库连接失败');
}

$con = mysqli_set_charset($con, 'utf8');
// 3种方式插入数据10万条数据


// 方式1 range方式
for($i=1;$i<=100000;$i++){
	$arr = range_insert($i, 'user');
	$con = mysqli_select_db($con, $arr[0]);
	$str = getStr(8);
	$result = mysqli_query($con,"insert into $arr[1] values($i,$str));
}

mysqli_close($con);

//方式二 hash方式
for($i=1;$i<=100000;$i++){
	$arr = hash_insert($i, 'user2');
	$con = mysqli_select_db($con, $arr[0]);
	$str = getStr(8);
	$result = mysqli_query($con,"insert into $arr[1] values($i,$str));
}

//方式三 linear_hash方式 （没什么用，了解思路就行）
for($i=1;$i<=100000;$i++){
	$arr = linear_hash_insert($i, 'user3');
	$con = mysqli_select_db($con, $arr[0]);
	$str = getStr(8);
	$result = mysqli_query($con,"insert into $arr[1] values($i,$str));
}
?>

三、分区

range分区

// 如果表存在主键或唯一索引，则分区字段必须是唯一索引的一部分

CREATE TABLE `part_order` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL DEFAULT '',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `year` int(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (year)
(PARTITION p0 VALUES LESS THAN (2021) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2022) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2023) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB)  // MAXVALUE的存在会造成不允许增加新的分区

测试：

explain partitions select count(*) from part_order where id = 10 and year > 2021

结果：

hash分区

// 根据主键的hash值进行分区，适合equal查询 不适合区间查询

CREATE TABLE `hash_order` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL DEFAULT '',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `year` int(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY HASH (id)
(PARTITION p0 ENGINE = InnoDB,
 PARTITION p1 ENGINE = InnoDB,
 PARTITION p2 ENGINE = InnoDB,
 PARTITION p3 ENGINE = InnoDB) 

测试

执行 explain partitions select count(*) from hash_order where id =1

结果：

list分区

// 必须是主键的一部分，类似range，不过数据必须是list的值，否则报错，适用与类型固定的，不如range范围广

CREATE TABLE `list_order` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL DEFAULT '',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `year` int(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY list (year)
(PARTITION p0 values in(2019),
 PARTITION p1 values in(2020),
 PARTITION p2 values in(2021),
 PARTITION p3 values in(2022))

测试：

explain partitions select count(*) from list_order where id = 10 and year > 2019

结果：

key分区

// key分区跟hash分区类似，区别时hash只支持数字类型字段分区，而key分区特别提供了对非数字类型字段进行分区，以下只做简要介绍

CREATE TABLE `key_order` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL DEFAULT '',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `year` int(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY key (id)
(PARTITION p0 ENGINE = InnoDB,
 PARTITION p1 ENGINE = InnoDB,
 PARTITION p2 ENGINE = InnoDB,
 PARTITION p3 ENGINE = InnoDB)

分区性能测试

• 300万条数据 ，range分区

没有分区：

有分区：

结论：性能差距非常小，所以分区的作用并不大

• 800万条数据，hash分区

不适用分区：

使用分区:

结论：性能差距非常小，所以分区的作用并不大

分区修改

1、range分区（经过测试）

增加：

alter table part_order add partition (partition p3 values less than (MAXVALUE))

重建：

alter table part_order reorganize partition p0,p1 into (partition p0 values less than (2022))

删除

alter table part_order drop partition p3

2、hash/key分区（未经测试）

增加：
ALTER TABLE users ADD PARTITION PARTITIONS 8;

重构：
ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2; #用 REORGANIZE 方式重建分区的数量变成2，在这里数量只能减少不能增加。想要增加可以用 ADD PARTITION 方法。

删除同range

3、list分区（未测试）

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

重构：
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));#将原来的 p0,p1 分区合并起来，放到新的 p0 分区中。

删除同range

结论：分区对sql性能提升并不明显