在业务场景中, 经常会有把一个产品与多个标签进行关联的情况. 这里我要简单的研究一下这个表到底该怎么建
模拟数据生成
表的定义:
USE `test`;
DROP TABLE IF EXISTS `test_co_ix`;
CREATE TABLE `test_co_ix`(
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`aid` INT UNSIGNED NOT NULL,
`bid` INT UNSIGNED NOT NULL,
PRIMARY KEY(`id`),
KEY `ix_aid_bid`(`aid`, `bid`)
) ENGINE = InnoDB COMMENT '联合索引';
DROP TABLE IF EXISTS `test_co_uk`;
CREATE TABLE `test_co_uk`(
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`aid` INT UNSIGNED NOT NULL,
`bid` INT UNSIGNED NOT NULL,
PRIMARY KEY(`id`),
UNIQUE KEY `uk_aid_bid`(`aid`, `bid`)
) ENGINE = InnoDB COMMENT '联合(唯一)索引';
DROP TABLE IF EXISTS `test_co_pk`;
CREATE TABLE `test_co_pk`(
`aid` INT UNSIGNED NOT NULL,
`bid` INT UNSIGNED NOT NULL,
PRIMARY KEY(`aid`, `bid`)
) ENGINE = InnoDB COMMENT '联合主键';
使用到的笛卡尔乘积函数, 用于参数组合:
/**
* 笛卡尔乘积
* @return array|mixed
*/
function cartesianProduct(){
$t = func_get_args();// 获取传入的参数
if (func_num_args() == 1) {// 判断参数个数是否为1
// 回调当前函数，并把第一个数组作为参数传入
return call_user_func_array(__FUNCTION__, $t[0]);
}
// 将 $t 中的第一个元素移动到 $a 中，$t 中索引值重新排序
$a = array_shift($t);
if (!is_array($a)) {
$a = [$a];
}
// 分割数组 $a ，为每个单元1个元素的新数组
$a = array_chunk($a, 1);
do {
$r = [];
$b = array_shift($t);
if (!is_array($b)) {
$b = [$b];
}
foreach ($a as $p) {
foreach (array_chunk($b, 1) as $q) {
$r[] = array_merge($p, $q);
}
}
$a = $r;
} while ($t);
return $r;
}
我们用下面的代码, 来生成需要的三个sql文件:
- test_co_ix.sql: 联合索引
- test_co_uk.sql: 唯一(联合)索引
- test_co_pk.sql: 联合主键
/**
* 生成参数文件
*
* @param int $aid_max
* @param int $bid_max
* @param bool $auto_commit 是否使用自动提交. 默认开启, 每一个sql都是一个事务(会导致插入数据很慢)
* @param bool $batch_insert 是否使用批量插入. 开启后会合并insert的values
* @param bool $ordered_data 数据是否是有序的. 无序是比较接近现实的业务需求.
*/
function genData($aid_max, $bid_max, $auto_commit=true, $batch_insert=false, $ordered_data=false) {
// 1. 100 * 5000, 连续的
$arr_aid = range(1, $aid_max);
$arr_bid = range(1, $bid_max);
// 如果php的memory_limit设置太小, 请调大一些
$arr_aid_bid = cartesianProduct($arr_aid, $arr_bid);//aid和bid组合后的数据
if(!$ordered_data){
shuffle($arr_aid_bid);//打乱后, 就是模拟现实的业务需求.
}
$arr_table_name = [
'test_co_ix', //联合索引
'test_co_uk', //唯一(联合)索引
'test_co_pk', //联合主键
];
$n_arr_aid_bid = count($arr_aid_bid);
// region 数据插入优化设置
$step = 500;
$batch_insert_size = 50;
// endregion
foreach ($arr_table_name AS $table_name) {
//数据太长, 容易超出内存限制, 故分批次写入
$file_name = $table_name
. ('__'.(string)$aid_max .'x'. (string)$bid_max)
. ($auto_commit ? '_autoCommit1':'_autoCommit0')
. ($batch_insert ? '_batch':'_single')
. ($ordered_data ? '_ordered':'_disordered')
. '.sql';
file_put_contents($file_name, 'USE `test`;' . PHP_EOL);//重置文件
for ($i = 0, $n = ceil($n_arr_aid_bid / $step); $i < $n; $i++) {
$lot_data = array_slice($arr_aid_bid, $step * $i, $step);//分批数据
$arr_sql = [];
if (!$auto_commit) {
$arr_sql[] = 'SET autocommit = 0;';
}
if ($batch_insert) {
$arr_v = [];
foreach ($lot_data AS $row) {
$aid = $row[0];
$bid = $row[1];
$arr_v[] = '(' . $aid . ', ' . $bid . ')';
}
//这里再分成50个一份
for ($j = 0, $n2 = ceil(count($arr_v) / $batch_insert_size); $j < $n2; $j++) {
$arr_v_sliced = array_slice($arr_v, $batch_insert_size * $j, $batch_insert_size);
$arr_sql[] = 'INSERT INTO `' . $table_name . '`(`aid`,`bid`) VALUES ' . implode(',', $arr_v_sliced) . ';';
}
} else {
foreach ($lot_data AS $row) {
$aid = $row[0];
$bid = $row[1];
$arr_sql[] = 'INSERT INTO `' . $table_name . '`(`aid`,`bid`) VALUES (' . $aid . ', ' . $bid . ');';
}
}
if (!$auto_commit) {
$arr_sql[] = 'commit;';
}
$sql_all = implode(PHP_EOL, $arr_sql) . ($i < $n - 1 ? PHP_EOL : '');
file_put_contents($file_name, $sql_all, FILE_APPEND);
}
}
}
数据写入测试
笔电SSD硬盘windows下实测结果. (机械硬盘windows下的centos虚机写入速度会比windows下快很多.)
- 500笔无序数据单笔插入:
genData(10, 50, true, false, false);
- test_co_ix.sql: 4秒, test_co_uk.sql: 5秒, test_co_pk.sql: 4秒
- 50w笔无序数据批量插入:
genData(100, 5000, false, true, false);
- test_co_ix.sql: 19秒, test_co_uk.sql: 16秒, test_co_pk.sql: 16秒
在插入数据时, 发现了一个神奇的现象: 插入有序和无序的500笔数据到联合索引表test_co_ix, 然后查询, 默认的数据排序是不一样的.
这个问题在
5.7.34和8.0.24下测试, 显示的结果与截图一致. 把数据插入表test_co_uk结果也一样.
我们各插入50w笔无序数据到3个table中, 看下空间大小:
SELECT `TABLE_NAME`, `ROW_FORMAT`, `TABLE_ROWS`, `AVG_ROW_LENGTH`
, CONCAT(ROUND(`DATA_LENGTH`/1024/1024, 2), ' MB') AS `DATA_LENGTH`
, CONCAT(ROUND(`INDEX_LENGTH`/1024/1024, 2), ' MB') AS `INDEX_LENGTH`
, CONCAT(ROUND(`DATA_FREE`/1024/1024, 2), ' MB') AS `DATA_FREE`
, `TABLE_COMMENT`
FROM `information_schema`.`tables`
WHERE table_schema='test' AND table_name IN('test_co_ix', 'test_co_uk', 'test_co_pk');
centos + MySQL 5.7:
windows + MySQL 8.0:
查看SELECT * FROM test_co_ix/uk/pk WHERE aid=89 AND bid=198 LIMIT 1:
mysql> EXPLAIN SELECT * FROM `test_co_ix` WHERE aid=89 AND bid=198 LIMIT 1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_co_ix
partitions: NULL
type: ref
possible_keys: ix_aid_bid
key: ix_aid_bid
key_len: 8
ref: const,const
rows: 1
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM `test_co_uk` WHERE aid=89 AND bid=198 LIMIT 1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_co_uk
partitions: NULL
type: const
possible_keys: uk_aid_bid
key: uk_aid_bid
key_len: 8
ref: const,const
rows: 1
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM `test_co_pk` WHERE aid=89 AND bid=198 LIMIT 1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_co_pk
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const,const
rows: 1
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.01 sec)
因为这个三个表都没有额外需要显示的栏位, 所以都不需要回表查询, 直接走索引或主键即可. 查询性能基本一致.
那我们现在重建一下表. 由于这里无法执行 OPTIMIZE TABLE table_name, 只能用 recreate + analyze操作代替.
OPTIMIZE = recreate + analyze
ALTER TABLE `test_co_ix` engine = InnoDB;
ANALYZE TABLE `test_co_ix`;
ALTER TABLE `test_co_uk` engine = InnoDB;
ANALYZE TABLE `test_co_uk`;
ALTER TABLE `test_co_pk` engine = InnoDB;
ANALYZE TABLE `test_co_pk`;
centos + MySQL 5.7:
windows + MySQL 8.0:
未完待续...
