# MySQL在UUID建立索引导致的磁盘占用高
对于MySQL数据库,建立BTREE索引时,如果选择在uuid类型的字段上建立索引时,会引起磁盘占用暴增
# 问题复现
- 创建一个新的MySQL服务器
新建MySQL服务的方法有多种,根据自己的技术储备、机器资源,选择Docker镜像或者原生安装方式均可,本次为了简便,使用docker镜像方式启动,并将数据文件夹挂载到宿主机
启动一次镜像(这一步是为了下一步能从镜像中拉取到默认配置文件):
docker run -d -e MYSQL_RANDOM_ROOT_PASSWORD=12345t --name=mysqltest mysql
将容器的文件拷贝出来,这一步目的是将来启动后可以方便的从宿主机修改配置:
docker cp mysqltest:/etc/mysql config
正式启动,文件如下
# Use root/example as user/password credentials version: '3.1' services: db: image: mysql command: --default-authentication-plugin=mysql_native_password restart: always environment: MYSQL_ROOT_PASSWORD: example volumes: - ./data:/var/lib/mysql ports: - 3306:3306
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
- 新建库和表
create database test;
create table index_push_down_test
(
id bigint auto_increment primary key,
uuid varchar(64) not null,
time datetime default CURRENT_TIMESTAMP null,
some_data int null
);
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
- 充填数据
-- 填充数据
delimiter $$
drop procedure if exists fillTest $$
create procedure fillTest(in num int)
begin
set @currNum = 0;
-- 这里设置随机的字符串
set @chars = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
while @currNum < num do
-- 这里设置sql后面拼接
set @insertSql = concat("insert into index_push_down_test (uuid,time,some_data) values ( ");
set @columnNum = 1;
-- uuid
set @uuid := '';
set @counter = 0;
while @counter < 63 do
set @uuid = concat(@uuid,substr(@chars,ceil(rand()*(length(@chars)-1)),1));
set @counter = @counter + 1;
end while;
set @uuid = concat("'" , @uuid , "'");
-- time
set @rndDate = FROM_UNIXTIME(UNIX_TIMESTAMP('2021-10-22 14:53:27') + FLOOR(0 + (RAND() * 83072000)));
set @rndDate = concat("'" , @rndDate , "'");
-- some_data
set @someData := '';
set @counter = 0;
while @counter < 63 do
set @someData = concat(@someData,substr(@chars,ceil(rand()*(length(@chars)-1)),1));
set @counter = @counter + 1;
end while;
set @someData = concat("'" , @someData , "'");
set @insertSql = concat(@insertSql , @uuid , ' , ',@rndDate,' , ',@someData,')');
-- 执行
prepare stmt from @insertSql;
execute stmt;
deallocate prepare stmt;
set @currNum = @currNum + 1;
end while;
end $$
delimiter ;
-- 调用存储过程
call fillTest(220000);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
-- 创建索引
create index idx_test_uuid_time on index_push_down_test(uuid,time);
create index idx_test_uuid on index_push_down_test(uuid);
create index idx_test_time on index_push_down_test(time);
-- 重新优化索引等
ANALYZE TABLE index_push_down_test ;
-- 查看表索引空间和数据空间
show table status like 'index_push_down_test';
-- 索引下推示例,以后用
SELECT * from index_push_down_test ipdt where id = 37823;
explain SELECT some_data from index_push_down_test ipdt where uuid like 'NqHgekYTCn4bKcI47rOOCDnWDlO4RacuRSQCwKeTNljyQAodSNoxzchPk%' and time <='2023-03-30 03:21:13';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 磁盘占用数据统计
场景 | 索引 | 数据表空间 | 索引表空间 | 备注 | 对比 |
---|---|---|---|---|---|
1 | uuid_time,time,uuid | 38354944 | 63848448 | 先建立索引后插入数据 | 索引空间 >> 数据空间 |
2 | uuid_time,uuid | 39403520 | 57016320 | 先建立索引后插入数据 | 索引空间 >> 数据空间 |
3 | uuid | 39403520 | 30048256 | 先建立索引后插入数据 | 索引空间 ≈ 数据空间 |
4 | uuid | 39403520 | 20561920 | 先有数据后建立索引 | 索引空间 ≈ 数据空间 |
5 | time | 39403520 | 5783552 | 先有数据后建立索引 | |
6 | uuid_time | 39403520 | 21626880 | 先有数据后建立索引 | 索引空间 ≈ 数据空间 |
7 | uuid_time,uuid | 39403520 | 42188800 | 先有数据后建立索引 | 索引空间 > 数据空间 |
# 结论与避坑
- UUID类型的字段上建立索引,需要考虑索引空间大于数据空间后,磁盘占用情况