前缀索引与单列&联合索引的选择

       📝个人主页：五敷有你      

 🔥系列专栏：面经

⛺️稳中求进，晒太阳

前缀索引

        当字段类型为字符串(varchar,text等) 时，有时候需要索引很长的字符串，这会让索引变得很大。查询的时候浪费大量的磁盘IO,影响查询效率，此时可以只将字符串的一部分前缀建立索引，这样可以大大节约索引的空间，从而提高索引的效率

语法：

create index idx_xxx on table_name(column(n))    

很多情况下，其实只需要一个字符串的前缀就可以很好的区分选择出这一行的数据。

前缀长度

        可以根据索引的选择性来决定，而选择性是指不重复的索引值(基数)和数据表记录的总数的比值，索引选择性越高效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

如下：对email进行前缀索引长度的判断：

使用substring函数可以一次次试探多长才符合我们的要求。

explain SELECT  count(distinct substring(email,1,10))/count(*) from tb_user

索引创建后看Sub_part就是5

前缀索引的查询流程

截取前缀一部分后去找索引进行匹配，之后找到主键id, 之后去聚集索引进行回表查询，拿到这一行的数据。

此时不是立刻返回，因为匹配都是匹配的前缀，拿到后应该去用email的值去与条件的email比较，是：返回，

不是：找下一个辅助索引看是不是符合的前缀：

        是：接着比较，

        不是就返回。

单列索引与联合索引的选择问题

• 单列索引：即一个索引只包含单个列。
• 联合索引：即一个索引包含了多个列。

我们先来看看 tb_user 表中目前的索引情况：

查询出来的索引既有单列索引，也有联合索引。

接下来执行一条SQL语句

通过上述执行计划，我们可以看出来在name字段是有两个索引的一个单列索引，一个联合索引。

但是mysql只会选择一个索引，也就是说，走一个字段的索引。此时是会回表查询的。

        在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引， 而非单列索引。