How can I speed up my MySQL UUID v4 stored function?
I'm attempting to write a MySQL stored function to generate v4 UUIDs as described in RFC 4122's section 4.4 ( http://www.ietf.org/rfc/rfc4122.txt ). My initial naive effort after a few tweaks is the following:
CREATE FUNCTION UUID_V4()
RETURNS BINARY(16)
READS SQL DATA
BEGIN
SET @uuid = CONCAT(
LPAD( HEX( FLOOR( RAND() * 4294967296 ) ), 8, '0' ),
LPAD( HEX( FLOOR( RAND() * 4294967296 ) ), 8, '0' ),
LPAD( HEX( FLOOR( RAND() * 4294967296 ) ), 8, '0' ),
LPAD( HEX( FLOOR( RAND() * 4294967296 ) ), 8, '0' )
);
SET @uuid = CONCAT(
SUBSTR( @uuid FROM 1 FOR 12 ),
'4',
SUBSTR( @uuid FROM 14 FOR 3 ),
SUBSTR( 'ab89' FROM FLOOR( 1 + RAND() * 4 ) FOR 1 )开发者_开发知识库,
SUBSTR( @uuid FROM 18 )
);
RETURN UNHEX(@uuid);
END
The above function is quite slow: almost 100 times slower than the built-in UUID()
, according to MySQL's BENCHMARK()
feature. Short of writing a UDF using MySQL's C API, are there any improvements I can make here to, say, shave off an order of magnitude from its runtime?
If there is an already existing, well-regarded UUID UDF or stored procedure, I'd be happy to hear about that, too.
I didn't test this for correctness or for performance. It is just the idea of doing one only concatenation in instead of two.
create function uuid_v4()
returns binary(16)
begin
set @h1 = lpad(hex(floor(rand() * 4294967296)), 8, '0');
set @h2 = lpad(hex(floor(rand() * 4294967296)), 8, '0');
set @h3 = lpad(hex(floor(rand() * 4294967296)), 8, '0');
set @h4 = lpad(hex(floor(rand() * 4294967296)), 8, '0');
set @uuid = concat(
@h1,
substr(@h2 from 1 for 4),
'4',
substr(@h2 from 6),
substr('ab89' from floor(1 + rand() * 4) for 1 ),
substr(@h3 from 2),
@h4
);
return unhex(@uuid);
end
;
Also why do you use READS SQL DATA
in your function?
精彩评论