mybatis+mysql环境下 uuid存储优化

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

 1 public class UuidHandler extends BaseTypeHandler<UUID> {
 2 
 4     @Override
 5     public void setNonNullParameter(PreparedStatement ps,
 6                                     int columnIndex, UUID uuid,
 7                                     JdbcType jdbcType) throws SQLException {
 8 
 9         long mostBit = uuid.getMostSignificantBits();
10         byte[] bytes = new byte[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         //
20         long 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
34     public UUID getNullableResult(ResultSet rs, String columnName) throws SQLException {
35         byte[] bytes = rs.getBytes(columnName);
36 
37         long 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 
46         long 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)));
54         return new UUID(mostBit, leastBit);
55     }
56 
57 
58     @Override
59     public UUID getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
60         // 略
61         return null;
62     }
63 
64     @Override
65     public UUID getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
66         // 略
67         return null;
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 实体

public class User {

    private UUID id;

    private String name;

   // 略
}

4.3 mapper

1 public interface UserMapper {
2 
3     void 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)
 2 public class UserMapperTest {
 3 
 4     SqlSession session;
 5     UserMapper mapper;
 6 
 7     @Before
 8     public void 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
17     public void 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
37     public void 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
44     public void test3SelectAll() {
45         List<User> list = mapper.selectAll();
46         System.out.println("list = " + list);
47 
48     }
49 }