MySQL: How are bin() columns indexed?-CodePudding

Low-level MySQL question for you: we are storing UUIDs as bin(16) in the DB and are using them as a primary key. We are in the process of changing the UUID stored to time-based. We want to optimize clustered index insertions to be append-only, but that will only work if we are sure of how MySQL adds the values to the b-tree. Does anyone know if MySQL b-tree for bin types uses the least or most significant bit first and then goes right-to-left or left-to-right respectively?

CodePudding user response：

Binary string types are like other string types, except they have no character set. That is, the bytes are treated as literal byte values, with no encoding. Otherwise, they sort just like strings: left to right.

Try an experiment to test this:

create table mytable (id int primary key, b binary(16), key(b));
    
insert into mytable values 
(1, unhex('BBBBBBBBBBBBBBBB7777777777777777')), 
(2, unhex('7777777777777777BBBBBBBBBBBBBBBB'))
    
mysql> select id, hex(b) from mytable order by b;
 ---- ---------------------------------- 
| id | hex(b)                           |
 ---- ---------------------------------- 
|  2 | 7777777777777777BBBBBBBBBBBBBBBB |
|  1 | BBBBBBBBBBBBBBBB7777777777777777 |
 ---- ----------------------------------

If the string were sorted by the least significant bit, the order would be opposite.

I used EXPLAIN to prove that this query uses the index on column b:

explain select id, hex(b) from mytable order by b;
 ---- ------------- --------- ------------ ------- --------------- ------ --------- ------ ------ ---------- ------------- 
| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
 ---- ------------- --------- ------------ ------- --------------- ------ --------- ------ ------ ---------- ------------- 
|  1 | SIMPLE      | mytable | NULL       | index | NULL          | b    | 17      | NULL |    2 |   100.00 | Using index |
 ---- ------------- --------- ------------ ------- --------------- ------ --------- ------ ------ ---------- -------------

CodePudding user response：

Yes, it is possible to make Type 1 UUIDs into BINARY(16) for 'trivial` indexing in virtually chronological order.

All ports of MySQL have identical ordering of the bits and bytes on disk. So this discussion is OS-independent.

Pre-8.0, see my UUID blog: UUIDs

8.0: See the uuid functions; they do essentially the same as mentioned in my blog

MariaDB 10.7 has a UUID datatype; no need for adding function calls. But they rearrange the bits differently.