mybatis+mysql环境下uuid存储优化

Z时代
2024-01-10
分类：综合

database

1问题

uuid采用char(32)或char(36)存储的话，需要占用32或36个字节。为节省存储空间，改为binary(16)，占用16字节。对于500W行的表，可节省7.4G的空间。

mybatis中没有默认的type handler来完成uuid类型<->binary类型的相互转换，需要自定义一个type handler。下面就详细地介绍如何实现。

2 环境

mybatis 3.5.3

mysql 5.7.21

java 8

3 自定义TypeHandler

 1publicclass UuidHandler extends BaseTypeHandler<UUID> {
 2
 4    @Override
 5publicvoid setNonNullParameter(PreparedStatement ps,
 6int columnIndex, UUID uuid,
 7                                     JdbcType jdbcType) throws SQLException {
 8
 9long mostBit = uuid.getMostSignificantBits();
10byte[] bytes = newbyte[16];
11         bytes[0] = (byte) ((mostBit >> 56) & 0xFF);
12         bytes[1] = (byte) ((mostBit >> 48) & 0xFF);
13         bytes[2] = (byte) ((mostBit >> 40) & 0xFF);
14         bytes[3] = (byte) ((mostBit >> 32) & 0xFF);
15         bytes[4] = (byte) ((mostBit >> 24) & 0xFF);
16         bytes[5] = (byte) ((mostBit >> 16) & 0xFF);
17         bytes[6] = (byte) ((mostBit >> 8) & 0xFF);
18         bytes[7] = (byte) (mostBit & 0xFF);
19//
20long leastBit = uuid.getLeastSignificantBits();
21         bytes[8] = (byte) ((leastBit >> 56) & 0xFF);
22         bytes[9] = (byte) ((leastBit >> 48) & 0xFF);
23         bytes[10] = (byte) ((leastBit >> 40) & 0xFF);
24         bytes[11] = (byte) ((leastBit >> 32) & 0xFF);
25         bytes[12] = (byte) ((leastBit >> 24) & 0xFF);
26         bytes[13] = (byte) ((leastBit >> 16) & 0xFF);
27         bytes[14] = (byte) ((leastBit >> 8) & 0xFF);
28         bytes[15] = (byte) (leastBit & 0xFF);
29//
30        ps.setBytes(columnIndex, bytes);
31    }
32
33    @Override
34public UUID getNullableResult(ResultSet rs, String columnName) throws SQLException {
35byte[] bytes = rs.getBytes(columnName);
36
37long mostBit = ((((long) bytes[0] & 0xFF) << 56)
38                 | (((long) bytes[1] & 0xFF) << 48)
39                 | (((long) bytes[2] & 0xFF) << 40)
40                 | (((long) bytes[3] & 0xFF) << 32)
41                 | (((long) bytes[4] & 0xFF) << 24)
42                 | (((long) bytes[5] & 0xFF) << 16)
43                 | (((long) bytes[6] & 0xFF) << 8)
44                 | (((long) bytes[7] & 0xFF)));
45
46long leastBit = ((((long) bytes[8] & 0xFF) << 56)
47                 | (((long) bytes[9] & 0xFF) << 48)
48                 | (((long) bytes[10] & 0xFF) << 40)
49                 | (((long) bytes[11] & 0xFF) << 32)
50                 | (((long) bytes[12] & 0xFF) << 24)
51                 | (((long) bytes[13] & 0xFF) << 16)
52                 | (((long) bytes[14] & 0xFF) << 8)
53                 | (((long) bytes[15] & 0xFF)));
54returnnew UUID(mostBit, leastBit);
55    }
56
57
58    @Override
59public UUID getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
60// 略
61returnnull;
62    }
63
64    @Override
65public UUID getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
66// 略
67returnnull;
68    }
69 }

以上代码有2个关键点：

1、在setNonNullParameter()方法中，将UUID转换成byte[]。UUID使用128位（16字节）存储，其中，uuid.getMostSignificantBits()返回高64位，uuid.getLeastSignificantBits()返回低64位。

2、在getNullableResult()方法中 , 将byte[]转换成UUID。

4 测试

4.1 表

  1 CREATE TABLE `user_uuid` (
  2   `id` binary(16) NOT NULL,
  3   `name` varchar(10) DEFAULT NULL,
  4   PRIMARY KEY (`id`)
  5 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

4.2 实体

publicclass User {
private UUID id;
private String name;
   // 略
}

4.3 mapper

1publicinterface UserMapper {
2
3void insert(User record);
4
5    User selectByPrimaryKey(UUID id);
6
7     List<User> selectAll();
8
9 }

 1<insert id="insert" parameterType="leo.domain.User">
 2    insert into user_uuid (id, name)
 3    values (#{id}, #{name})
 4</insert>
 5
 6<select id="selectByPrimaryKey" resultType="leo.domain.User"
 7            parameterType="java.util.UUID">
 8    select id,  name
 9    from user_uuid
10    where id = #{id}
11</select>
12
13
14<select id="selectAll" resultType="leo.domain.User">
15      select id,  name
16      from user_uuid
17</select>

4.4 配置

  1 <typeHandlers>
  2     <typeHandler handler="leo.handler.UuidHandler"/>
  3 </typeHandlers>

在typeHandlers节点中注册UuidHandler

4.5 测试用例

 1@FixMethodOrder(MethodSorters.NAME_ASCENDING)
 2publicclass UserMapperTest {
 3
 4    SqlSession session;
 5    UserMapper mapper;
 6
 7    @Before
 8publicvoid init() {
 9         InputStream inputStream = App3_uuid.class.getResourceAsStream("/mybatis-config.xml");
10         SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
11         session = sqlSessionFactory.openSession();
12         mapper = session.getMapper(UserMapper.class);
13    }
14
15
16    @Test
17publicvoid test1Insert() {
18// 插入用户1
19         UUID uuid1 = UUID.fromString("0eaa9bf6-0c70-4678-8658-8737d3a5eba1");
20         User user1 = new User();
21        user1.setId(uuid1);
22         user1.setName("用户1");
23        mapper.insert(user1);
24        session.commit();
25
26// 插入用户2
27         UUID uuid2 = UUID.fromString("0eaa9bf6-0c70-4678-8658-8737d3a5eba2");
28         User user2 = new User();
29        user2.setId(uuid2);
30         user2.setName("用户2");
31        mapper.insert(user2);
32        session.commit();
33
34    }
35
36    @Test
37publicvoid test2SelectByPrimaryKey() {
38         UUID uuid1 = UUID.fromString("0eaa9bf6-0c70-4678-8658-8737d3a5eba1");
39         User user1 = mapper.selectByPrimaryKey(uuid1);
40         System.out.println("user = " + user1);
41    }
42
43    @Test
44publicvoid test3SelectAll() {
45         List<User> list = mapper.selectAll();
46         System.out.println("list = " + list);
47
48    }
49 }