改善MySQL上16进制标识符性能的5种方法_MySQL

bitsCN.com

这里讲得是如何在使用16进制大数据的情况下保持好的性能，主要讲的是MySQL数据库，对其他数据库应该也起作用。

一、小心你的字符编码

看一下下面这个SQL语句：

mysql> explain select * from t where id = ’0cc175b9c0f1b6a831c399e269772661′G
***************************              1. row

 ***************************
id: 1
select_type: SIMPLE
table: t
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 98
ref: const
rows: 1
Extra: Using index

为什么索引是98byte？简单，因为我们用的是UTF-8：

CREATE TABLE `t` (
`id` varchar(32) NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

没有必要用UTF-8存储16进制数据，采用UTF-8存储16进制数据不会增加磁盘空间的占用，但是当你使用排序(order by)、统计(group by)、隐式临时表（MySQL查询时自建的临时表）等的时候，需要耗费多达3倍的内存和硬盘空间，至少在MySQL上是这样的。

二、使用固定长度，不要有空值

可以看到上面那个表采用的是varchar字段，我们都知道varchar是一个变长字段，如果你确认所有的数据都一样长（比如像md5()出来的，都是32个字节），最好使用char()定长字段，另外就是如果字段中不可能有空值，最好指定为not null

三、使用二进制数据存储

实际上，你并不需要存储字符串，16进制字符串不过是数字的另一种表现形式，直接保存数字。比如：00000000000000000000000000002E2A是什么呢？这正是16进制数字11818，使用一个4字节(或者更少)的整型代替一个32字节的字符存储更好。

问题是MySQL没有合适的类型来存储这么大的数字，它们比BIGINT还要大很多，不过MySQL允许我们存储到BINARY字段，数据更紧凑比较起来更快速，可以使用HEX()和UNHEX()来转换格式，或者16进制操作符’x’

mysql> select x’7861707262′;
+―――――+
| x’7861707262′ |
+―――――+
| xaprb         |
+―――――+

用BINARY(16)代替varchar(32)之后：

explain select * from t where id = x’0cc175b9c0f1b6a831c399e269772661′G
***************************           1. row

***************************
id: 1
select_type: SIMPLE
table: t
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 16
ref: const
rows: 1
Extra: Using index

索引长度变成16字节了（对比原来98字节），减小了不少，如果你使用的是UUID()，存入之前先用replace()把”-”题换掉。

四、使用前缀索引

很多时候，我们不需要索引全部字段，索引字段的前8～10个字符就可以了，如果你当前存储的是字符串，这很有用，不用转换成BINARY，只是改变索引策略而已。

你可以通过类似下面的SQL语句判断合适的前缀索引个数：

mysql> select count(distinct id), count(distinct left(id, 8)), count(distinct left(id, 9)) from tG
*************************** 1. row ***************************
count(distinct id): 2
count(distinct left(id, 8)): 2
count(distinct left(id, 9)): 2

找一个差不多行就可以，不一定要索引“唯一”。

五、创建hash索引

直接上代码，不用多余的解释：

mysql> alter table t add crc int unsigned not null, add key(crc);
mysql> update t set crc=crc32(id);
mysql> explain select * from t use index(crc) where id = ’0cc175b9c0f1b6a831c399e269772661′ and crc=crc32(’0cc175b9c0f1b6a831c399e269772661′)G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: ref
possible_keys: crc
key: crc
key_len: 4
ref: const
rows: 1
Extra: Using where

使用crc32()获取到字符串的校验值，一般这样的碰撞概率不会太大，索引数字比索引字符不知道要快多少，极力推荐，不仅仅适用16进制字符，任意字符也适合：

mysql> select crc32(‘good good study, and day day up!’);
+――――――――――――――-+
| crc32(‘good good study, and day day up!’) |
+――――――――――――――-+
|                                2265998365 |
+――――――――――――――-+
1 row in set (0.00 sec)

总结：

16进制标识符让表和索引的变大，降低比较和查找的速度，建议非不得已不要使用，如果非要使用，希望上面的五条建议对你有用。

bitsCN.com