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  @Override34  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  @Override59  public UUID getNullableResult(ResultSet rs, int columnIndex) throws SQLException {60   // 略61   return null;62  }63 64  @Override65  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_uuid10  where id = #{id}11  </select>12 13 14  <select id="selectAll" resultType="leo.domain.User">15   select id, name16   from user_uuid17  </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.);10   SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);11   session = sqlSessionFactory.openSession();12   mapper = session.getMapper(UserMapper.class);13  }14 15 16  @Test17  public void test1Insert() {18   // 插入用户119   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   // 插入用户227   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  @Test37  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  @Test44  public void test3SelectAll() {45   List<User> list = mapper.selectAll();46   System.out.println("list = " + list);47 48  }49 }

 

mybatis+mysql环境下 uuid存储优化zozotown、 欧舒丹、 haofang、 Amazon前后台获取关键词的方式、 今日中国邮政平邮运费涨价，Wish强势进行运费补贴？、 2019旺季过后如何清库存的七大绝招？、 珠海拓展公司有哪些、 珠海拓展机构、 珠海拓展机构、