MySQL 的长字段如何优化？

在我们的实际项目数据库中，可能需要对很长的字段进行索引，其长度可能在几百甚至上千。

我们如果直接在如此长的字段上直接索引，那么光索引这部分就占用了很大的空间，这显然是我们程序开发人员和老板都不想看到的，另外，索引很长的列，在查询的时候，效率也大打折扣。那么这种情况下该如何优化这种索引呢？下面介绍 2 种优化方式。

比如说我们有这样一张表结构

对于 employees 这张表里的 introduce 字段，它非常长，所以这里单独的给它加一个 hash值的字段存进去

不过要注意的是，hash 值不能太长，太长也就失去意义了。

当插入一条数据的时候，就是这样写了，这里对introduce_hash的hash 值使用 MySQL CRC32() 函数，这个函数 的hash 结果是比较短的，并且 hash碰撞的概率比较低，像 MD5 等计算出来的hash 值就非常长了，不适合存在这里。

insert into employees(introduce,introduce_hash) value (
    'baladsfasdafdsfdsaferdesfddfsgsdfsdf',CRC32('baladsfasdafdsfdsaferdesfddfsgsdfsdf')
    )

经过 hash 之后，SQL 的查询就变成这样了，我们只需要在 introduce_hash 上加上索引就可以了。

select *
from employees t where introduce = 'sdfdsafsd' and introduce_hash = CRC32('sdfdsafsd') 

上面的 伪hash 优化的方式，适用于条件是等值的优化，它不适合模糊的查询。不过 MySQL支持 前缀索引，前缀所以的也是是，我们只索引字段开始的部分字符串，这样在节省了空间的同时，也能完成对字段整体的索引查询效果。

对于前缀索引，这里就要提到一个索引的选择性，索引的选择性指的是不重复的索引值和数据表中记录数的比值，这个值越大查询效率会越高(这个值在 1/总记录数 - 1 之间 )，前缀索引会降低索引的选择性，所以我们在创建前缀索引的时候，要计算好应该索引几个字符串比较合适，我们按照索引的选择性公式来计算一下下面这条语句

select *
FROM employees
WHERE first_name = '12312dsafds';

查看一下 first_name的索引选择性值

select count(distinct first_name) / count(*)
from employees;
# 执行一下可以看到，first_name 这个完整字段的索引选择性是 0.0042 

然后看一下first_name 的前几个字符串 的索引选择性

select count(distinct left(employees.first_name, 5)) / count(*)
from employees;
# 5 0.0037;6:0.0041;7:0.0042;8:0.0042

经过测试， first_name 的前 5 ，6，7, 8 个字符串的索引选择性分别是 0.0037,0.0041，0.0042, 0.0042。这说明当对first_name 的前 7 个字段进行索引时和对其整个字段进行索引时，索引值是一样的，那么对这个字段的前缀索引就定在前7个字符串，具体创建索引的 SQL如下

alter table employees add key (first_name(7));

需要注意的是，前置索引也有其局限性，使用了 前置索引的话，order by，group by ,覆盖索引就无法使用了。

有人可能会问，MySQL有前缀索引，那有没有后缀索引？

答案是：没有！

你可能要说了，没有后置索引，但是有时候我们的需求很奇葩，就是需要查询尾部的字符串来匹配一下，那怎么办呢？

MySQL 虽然没有后置索引，但是通过它这个前缀索引的创建方式，我们可以自己模拟一个后缀索引出来，我们还拿上面的 first_name举个栗子，要实现后缀索引，不能直接在 first_name 直接创建，我们需要另外创建一个字段 first_name_reverse 字段，这个字段存什么呢，直接存储 first_name 这个值的翻转内容mysql字段，让后对first_name_reverse 创建前缀索引，这样我们就实现了一个 first_name 的后缀索引。

参考资料：

《MySQL 高性能书籍》

测试数据库为 github.com/datacharmer/test_db

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!