Mysql前缀索引

GreenLife
39 阅读4分钟

问题1：为什么会有前缀索引？

我们知道Innodb B+树的索引节点大小是一个页(页是mysql管理磁盘的最小单位)，大小为16K，索引越大，

索引节点上存放的索引就越少，索引树的高度就越大，索引文件越大，必然影响索引效率(极端情况，一个索引节点上就一个索引记录，想象下树的高度！)

前缀索引正是为了避免这种情况，提高索引效率而存在的。 

问题2：怎么使用前缀索引？

为长字符串添加索引时，我们经常是像普通加索引一样，add index idx_url(url)这样。然而我们回头去看表结构的时候，会发现我们刚刚创建的索引变成类似idx_url(url(191))这样子。

原因：在Innodb里(在其它存储引擎里就不是这样)

 1.单索引长度不能超过767Bytes(为啥是767，不知道，规定不能超过，规定可以改吗？可以，修改innodb_large_prefix为ON,单索引可以到3072Bytes)

 2.组合索引不能超过3072Bytes(体现在最后一列截断)

InnoDB一个索引节点的默认大小是 16 k，要求叶子节点上一个page至少包含两条记录，所以一个记录最多不能超过 8 k。又由于InnoDB的聚簇索引结构，一个二级索引要包含主键索引，
因此每个单个索引不能超过 4 k（极端情况，pk和某个二级索引都达到这个限制）。由于需要预留和辅助空间，扣掉后不能超过 3500 ，取个“整数”就是(1024*3=3072)。

问题3： 为什么 是 191 ？:

因为191 * 4 =764 （192个就超767，宁少勿超！）

为什么是4？因为编码是utf8mb4！

gbk(1 character = 2byte)：767/2=383，所以gbk单列索引最大不能超过383个字符

latin1(1 character = 1byte): 767/1=767，所以latin1单列索引最大不能超过767个字符

utf8(1 character = 3byte): 767/3=255，所以latin1单列索引最大不能超过255个字符

（参考资料：www.cnblogs.com/zhiqian-ali…

虽然Mysql为我们做了兜底方案，避免我们误用过大的索引，但是我们在为char、varchar、text等字符串设计索引时，还是要让索引长度尽可能的小，减少索引文件大小，提高索引效率。

问题4：如何让前缀索引尽可能小？ （ 索引选择性）

select count(distinct 索引列)/count(*) from A;

结果越接近1，选择性越好。

计算选择性的公式：

select count(distinct left(
page_url
,3))/count(*) as sel3,count(distinct left(
page_url
,4))/count(*) as sel4,count(distinct left(
page_url
,5))/count(*) as sel5, count(distinct left(
page_url
,6))/count(*) as sel6 from A;

注：下面情况是无法使用前缀索引的

  1. 索引覆盖扫描（因为索引是被截断的！）
  1. 通过索引的排序（order by, group b y） 结论：不要对长字符串做排序或分组！

问题5：前缀索引到了最大长度了选择性还是很低，怎么办？

    1.innodb_large_prefix设置为NO，单索引长度加到最大3072（不建议）

    2.压缩：做哈希。优点是减少索引文件大小，缺点是只能进行精确查询.哈希算法CRC32(page_url):返回整数，但数据表大的话冲突会多；MD5(page_url)、SHA1等。

    select * from A where crc_url=CRC32('baidu.com') and page_url='baidu.com';//解决冲突

问题6：有没有可以对长字符串索引又可以支持模糊查询的数据库解决方案？

   没有很好的解决方法。

   大家可能会想到全文检索。

   全文检索的实现：

        倒排索引： Map<word,List>

        正排索引： Map<docId,List>

倒排索引例子：

文档1：student go to school

文档2：student has a pen

文档3：school has student

文档4:  student and teacher

TermDocument ID
student1,2,3,4
go1
to1
school1,3
teacher4

select * from A where name like '%stude%'; 返回空

需要注意的是全文检索的对象是一个单词，也就是说如果你在"baidu.com,google.cn"中检索"google"，全文检索检 索不出来，但是如果你检索google.cn，那么可以使用全文检索。

注：可以通过调整分词策略，使全文索引支持like查询—— 按一个字符分为一个词!

avatar
文章
阅读
粉丝