MySQL下使用Sequence功能分析和总结.docxVIP

MySQL 下使用 Sequence 功能 Oracle 提供了 sequence 功能，但是 MySQL 不知道为什么到现在都还没有这个简单的功能，所以有很多人就想方设法在 MySQL 上扩展，进而支持 Sequence， 但是一些好的解决方案都没有考虑到性能和并发连接数据库时 Sequence 的唯一性，下面就针对这些做一些简要的分析，进而提出完美的解决方案。 来自互联网的最好的解决方案是建立一个表来存储 sequence，这个思路很好，但是如果选择数据库的引擎不当就会造成性能下降或者 sequence 重复，导致失去 sequence 的意义。 首先说性能，往往 sequence 要求的是全局变量，这样所有的系统都从一个地方获取 sequence，所以就要求这个引擎有很高的性能，而采用数据表存储的sequence 要求反复的读写数据库，如果采用 InnoDB，会导致 update 速度非常缓慢，而不能满足大量请求的快速响应，从而可能造成系统瓶颈，分析原因，是因为 InnoDB 是采用行锁定的方式，这样就会造成对并发请求的排队响应，进而严重影响速度，而 MyISAM 引擎采用表锁定方式，就大大缩短了请求的排队时间， MEMORY 是基于内存的引擎，所有的数据操作都在内存中进行，速度更快，所以数据库引擎应该采取 MyISAM 或者 MEMORY。 进过进一步的分析，发现如果采用常用的普通字段存储 sequence 值，每次采用 update 方式更新，在 MyISAM 和 MEMORY 引擎时，如果有多个连接并发请求就会导致 sequence 重复，原因是这两种引擎都是表锁定的，也就是并发时可能有多次同时 update 语句执行，而要规避这个情况出现，只有采用记录锁定的引擎，也就是 InnodB，但是效率又不能满足要求，如何解决这个矛盾呢？我又一次陷入了深深地思索之中...... 这个时候一个常用的字段类型闪过了我的脑海“ auto_increment”对，这个本身就是自动增涨的，和我们的 sequence 非常类似，为什么不在它身上打主意呢！ 我立即修改了网上常用方法的表结构，直接设置一个主键，该主键是一个 auto_increment 类型的 int 字段，当然可以根据需求设置初始值和增长步进值。经 过这么改造以后，无论采用什么引擎，在多联接并发的情况下再也不会出现sequence 值重复的情形了。但是，MEMORY 是基于内存的，数据并不存在物理磁盘上，一旦数据库重启，数据就会丢失，所以不能采用。 经过反复的对比分析，最终选择的一个完美的解决方案：数据库引擎采用 MyISAM，sequence 存储在 auto_increment 的字段内。 下面是建表ＳＱＬ及内置函数： DROP TABLE IF EXISTS `SequenceTest`.`sequence`; CREATE TABLE `SequenceTest`.`sequence` ( `id` int(11) unsigned NOT NULL auto_increment, `name` varchar(45) NOT NULL, PRIMARY KEY (`id`), KEY `Index_2` (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1; 获取当前 sequence 值的函数 DELIMITER $$ DROP FUNCTION IF EXISTS `currval` $$ CREATE FUNCTION `currval`(seq_name VARCHAR(50)) RETURNS int(11) DETERMINISTIC BEGIN DECLARE value INTEGER; SET value = 0; SELECT id INTO value FROM sequence WHERE name=seq_name; RETURN value; END $$ DELIMITER ; 获取下一个新 sequence 的函数 DELIMITER $$ DROP FUNCTION IF EXISTS `nextval` $$ CREATE FUNCTION `nextval`(seq_name VARCHAR(50)) RETURNS int(11) DETERMINISTIC BEGIN DELETE FROM sequence WHERE name=seq_name; INSERT INTO sequence(name) VALUES(seq_name); RETURN currval(seq_name); END $$ DELIMITER ; 测试 SQL：