mybatis oracle自增长和UUID两种主键生成策略

oracle数据库自增长没有mysql数据库简单，mysql可以在建表的时候设置auto_increment就好了，但oracle这样不行，oracle数据库必须要建立序列，然后在mybatis插入id时使用这个序列后，才能实现oracle id的自增长。

下面是序列的创建语句：

create sequence TBAL_OA_HOLIDAY_TYPE //序列名称  
increment by 1  //以1倍的速度增长，你也可以设置其他数字  
start with 1    //从id=1开始增长  
 maxvalue 9999  //最大值为9999，根据情况  
 minvalue 1     //最小值1  
 nocycle        //不循环，也就是一直增长         
 cache 20       //设置缓存cache个序列，如果系统down掉了或者其它情况将会导致序列不连续，也可以设置为---------NOCACHE  
noorder;

建立了oracle序列之后，我们就可以在mybatis中插入这样使用，来插入id了：

insert into TBL_OA_HOLIDAY_TYPE (ID, TYPE_NAME, REMARK  
  )  
values (TBAL_OA_HOLIDAY_TYPE.NEXTVAL, #{typeName,jdbcType=VARCHAR}, #{remark,jdbcType=VARCHAR}  
  )

我们在插入mybatis插入id的位置用(序列名称+ .NEXTVAL)的形式代替这个id，而不是#{xxx}这样从页面取值了，就是这么简单，自己试试吧，当然了，id必须得是number类型的才行。

如果是UUID作为主键，那么主键用varchar2类型，这样才能用字符串uuid，以下是mybatis oracle的uuid使用方法，比oracle自增长容易很多：

使用uuid作为主键，oracle中有个sys_guid函数可以产生uuid。

<insert id="insert" parameterType="com.xxx.SystemDepartment">  
    <selectKey keyProperty="id" resultType="String" order="BEFORE">  
        select sys_guid() from dual  
    </selectKey>  
     insert into TBL_OA_HOLIDAY_TYPE (ID, TYPE_NAME, REMARK  
      )  
    values (#{id,jdbcType=VARCHAR}, #{typeName,jdbcType=VARCHAR}, #{remark,jdbcType=VARCHAR}  
      )  
</insert>

SelectKey属性作用如下：

属性描述

keyProperty    selectKey 语句结果应该被设置的目标属性。如果希望得到多个生成的列，也可以是逗号分隔的属性名称列表。
keyColumn    匹配属性的返回结果集中的列名称。如果希望得到多个生成的列，也可以是逗号分隔的属性名称列表。
resultType    结果的类型。MyBatis 通常可以推算出来，但是为了更加确定写上也不会有什么问题。MyBatis 允许任何简单类型用作主键的类型，包括字符串。如果希望作用于多个生成的列，则可以使用一个包含期望属性的 Object 或一个 Map。
order    这可以被设置为 BEFORE 或 AFTER。如果设置为 BEFORE，那么它会首先选择主键，设置 keyProperty 然后执行插入语句。如果设置为 AFTER，那么先执行插入语句，然后是 selectKey 元素 - 这和像 Oracle 的数据库相似，在插入语句内部可能有嵌入索引调用。
statementType    与前面相同，MyBatis 支持 STATEMENT，PREPARED 和 CALLABLE 语句的映射类型，分别代表 PreparedStatement 和 CallableStatement 类型。

mybatis中 insert、update、delete返回的都是int，就是操作成功的记录数，SelectKey的作用是将查询值插入对象属性。

比如insert的参数是一个Person对象，SelectKey的keyProperty值是Person的id属性，执行后会将参数对象Person的id设置成SelectKey查询值，

可以在参数对象中获取

Mysql主键UUID和自增主键的区别及优劣分析

mybatis+mysql自动生成uuid主键

<insert id="insert" parameterType="com.jo.model.Functions" >
    <selectKey keyProperty="pkGlobalId" resultType="String" order="BEFORE">
        select  replace(uuid(),'-','')   from dual
    </selectKey> 
    insert into tbl_function (pk_global_id, name, parent_id, 
      sort, url)
    values (#{pkGlobalId,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR}, #{parentId,jdbcType=VARCHAR}, 
      #{sort,jdbcType=INTEGER}, #{url,jdbcType=VARCHAR})
  </insert>

在mysql5.0之前，如果是多个master复制的环境，无法用自增主键，因为可能重复。在5.0以及之后的版本通过配置自增偏移量解决了整个问题。

什么情况下我们希望用uuid

1. 避免重复，便于scale，这就是我们做cloud service的时候选择uuid的主要原因

2. 入库之前可以知道id

3.相对安全，不能简单的从uuid获取信息，但是如果自增，则容易暴露信息，如果一个客户id是123456，很容易猜到有客户id是123456.

UUID有什么问题

1.uuid有16个字节，比int（4 byte）和bigint（8 byte）占用更多存储空间

2.由于size和无序性，可能引起性能问题

Mysql的uuid原理

mysql的innodb存储引擎处理storage的方式是靠聚集索引。

聚集索引是指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。一个表只能有一个聚集索引，因为一个表的物理顺序只有一种情况

1.为什么要使用uuid做主键

(1).其实在innodb存储引擎下，自增长的id做主键性能已经达到了最佳。不论是存储和读取速度都是最快的，而且占的存储空间也是最小。

(2).但是在我们实际到项目中会碰到问题，历史数据表的主键id会与数据表的id重复，两张自增id做主键的表合并时，id一定会有冲突，但如果各自的id还关联了其他表，这就很不好操作。

(3).如果使用UUID，生成的ID不仅是表独立的，而且是库独立的。对以后的数据操作很有好处，可以说一劳永逸。

2.UUID优缺点

缺点： 1. 影响插入速度，并且造成硬盘使用率低

2. uuid之间比较大小相对数字慢不少，影响查询速度。

3. uuid占空间大，如果你建的索引越多，影响越严重

优点：出现数据拆分、合并存储的时候，能达到全局的唯一性

3.最优方案

(1).InnoDB引擎表是基于B+树的索引组织表。

(2).B+树：B+树是为磁盘或其他直接存取辅助设备而设计的一种平衡查找树，在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶节点中，各叶节点指针进行连接。

(3).InnoDB主索引：叶节点包含了完整的数据记录。这种索引叫做聚集索引。InnoDB 的索引能提供一种非常快速的主键查找性能。不过，它的辅助索引也会包含主键列，所以，如果主键定义的比较大，其他索引也将很大。如果想在表上定义、很多索引，则争取尽量把主键定义得小一些。InnoDB 不会压缩索引

(4).聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

综合上述可得：

(1).如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话，这时候存取效率是最高的。为了存储和查询性能应该使用自增长id做主键。

(2).对于InnoDB的主索引，数据会按照主键进行排序，由于UUID的无序性，InnoDB会产生巨大的IO压力，此时不适合使用UUID做物理主键，可以把它作为逻辑主键，物理主键依然使用自增ID。为了全局的唯一性，应该用uuid做索引关联其他表或做外键。

4.如果非要使用uuid做主键，下面是小建议：

如果是主从即M-S模式，最好是不使用mysql自带函数uuid来生成唯一主键，因为主表生成的uuid要再关联从表时，需要再去数据库查出这个uuid，需要多进行一次数据库交互，而且在这个时间差里面主表很有可能还有数据生成，这样就很容易导致关联的uuid出错。如果真要使用uuid，可以在Java中生成后，直接存储到DB里，这时主从的uuid就是一样的了！

补充：mysql的uuid()主键重复

1、mysql的uuid()主键重复

mysql使用了navicat客户端，某次执行了如下sql

select replace(uuid(), '-', '') as id, u.user_id from t_user u;

结果发现，生成的uuid重复了，

经过排查，发现是navicat的问题，需要将该sql语句做如下调整:

select replace(convert(uuid() using utf8mb4), '-', ''), u.user_id from t_user u;

结果如下：

2、使用其他方案：

将uuid再进行一次md5：

select md5(uuid()) as id, u.user_id from t_user u;

建议无论那个数据库使用uuid还是在Java中生产uuid

posted @ 2021-08-13 10:40 一心二念阅读(2263) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部