基础设施建设网站,03340 网站建设与管理,网站开发工程师绩效考核,免费中介系统房产软件SQL GUID和自增列做主键的优缺点
公司的数据库全部是使用GUID做主键的#xff0c;很多人习惯使用int做主键。所以呢#xff0c;这里总结一下#xff0c;将两种数据类型做主键进行一个比较。
主键自增为什么比随机和自定义快#xff1f;
1、如果表使用自增主键#xff0…SQL GUID和自增列做主键的优缺点
公司的数据库全部是使用GUID做主键的很多人习惯使用int做主键。所以呢这里总结一下将两种数据类型做主键进行一个比较。
主键自增为什么比随机和自定义快
1、如果表使用自增主键那么每次插入新的记录记录就会顺序添加到当前索引节点的后续位置当一页写满就会自动开辟一个新的页 2、如果使用非自增主键如果身份证号或学号等由于每次插入主键的值近似于随机因此每次新纪录都要被插到现有索引页得中间某个位置此时MySQL不得不为了将新记录插到合适位置而移动数据甚至目标页面可能已经被回写到磁盘上而从缓存中清掉此时又要从磁盘上读回来这增加了很多开销同时频繁的移动、分页操作造成了大量的碎片得到了不够紧凑的索引结构后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。
使用INT做主键的优点
1、需要很小的数据存储空间仅仅需要4 byte 。 2、insert和update操作时使用INT的性能比GUID好所以使用int将会提高应用程序的性能。 3、index和Join 操作int的性能最好。 4、容易记忆。 5、支持通过函数获取最新的值如Scope_Indentity() 。
使用INT做主键的缺点
1、如果经常有合并表的操作就可能会出现主键重复的情况。 2、使用INT数据范围有限制。如果存在大量的数据可能会超出INT的取值范围。 Int 4B存储-231~231-1范围的整数2147483647 / 1000 / 365 5883.516841095890410958904109589 差不多可以用 6000年就算10000条记录也可以用 500多年 3、很难处理分布式存储的数据表。
为什么要使用GUID做主键
其实在innodb存储引擎下自增长的id做主键性能已经达到了最佳。不论是存储和读取速度都是最快的而且占的存储空间也是最小。 但是在我们实际到项目中会碰到问题历史数据表的主键id会与数据表的id重复两张自增id做主键的表合并时id一定会有冲突但如果各自的id还关联了其他表这就很不好操作。 如果使用GUID生成的ID不仅是表独立的而且是库独立的。对以后的数据操作很有好处可以说一劳永逸。
使用GUID做主键的优点
1、它是独一无二的。 2、出现重复的机会少。 3、适合大量数据中的插入和更新操作。 4、跨服务器数据合并非常方便。
使用GUID做主键的缺点
1、存储空间大16 byte因此它将会占用更多的磁盘大小。 如果你建的索引越多 影响越严重。 2、很难记忆。join操作性能比int要低。 3、没有内置的函数获取最新产生的guid主键。 4、GUID做主键将会添加到表上的所以其他索引中因此会降低性能影响插入速度。 5、GUID之间比较大小相对数字慢不少 影响查询速度
最优方案
(1).InnoDB引擎表是基于B树的索引组织表。 (2).B树B树是为磁盘或其他直接存取辅助设备而设计的一种平衡查找树在B树中所有记录节点都是按键值的大小顺序存放在同一层的叶节点中各叶节点指针进行连接。 (3).InnoDB主索引叶节点包含了完整的数据记录。这种索引叫做聚集索引。InnoDB 的索引能提供一种非常快速的主键查找性能。不过它的辅助索引也会包含主键列所以如果主键定义的比较大其他索引也将很大。如果想在表上定义 、很多索引则争取尽量把主键定义得小一些。InnoDB 不会压缩索引 (4).聚集索引这种实现方式使得按主键的搜索十分高效但是辅助索引搜索需要检索两遍索引首先检索辅助索引获得主键然后用主键到主索引中检索获得记录。
总结
(1).如果InnoDB表的数据写入顺序能和B树索引的叶子节点顺序一致的话这时候存取效率是最高的。为了存储和查询性能应该使用自增长id做主键。
(2).对于InnoDB的主索引数据会按照主键进行排序由于Guid的无序性InnoDB会产生巨大的IO压力此时不适合使用Guid做物理主键可以把它作为逻辑主键物理主键依然使用自增ID。为了全局的唯一性应该用Guid做索引关联其他表或做外键。
如果非要使用Guid做主键下面是小建议
如果是主从即M-S模式最好是不使用自带函数Guid来生成唯一主键因为主表生成的Guid要再关联从表时需要再去数据库查出这个Guid需要多进行一次数据库交互而且在这个时间差里面主表很有可能还有数据生成这样就很容易导致关联的Guid出错。如果真要使用Guid可以在Java中生成后直接存储到DB里这时主从的Guid就是一样的了
