面试官：什么是前缀索引、为什么要用前缀使用、用在什么场景下？

阅读 1

2021-03-14

什么是前缀索引？

前缀索引也叫局部索引，比如给身份证的前 10 位添加索引，类似这种给某列部分信息添加索引的方式叫做前缀索引。

为什么要用前缀索引？

前缀索引能有效减小索引文件的大小，让每个索引页可以保存更多的索引值，从而提高了索引查询的速度。但前缀索引也有它的缺点，不能在 order by 或者 group by 中触发前缀索引，也不能把它们用于覆盖索引。

什么情况下适合使用前缀索引？

当字符串本身可能比较长，而且前几个字符就开始不相同，适合使用前缀索引；相反情况下不适合使用前缀索引，比如，整个字段的长度为 20，索引选择性为 0.9，而我们对前 10 个字符建立前缀索引其选择性也只有 0.5，那么我们需要继续加大前缀字符的长度，但是这个时候前缀索引的优势已经不明显，就没有创建前缀索引的必要了。

举例说明：

当要索引的列字符很多时索引则会很大且变慢

可以只索引列开始的部分字符串节约索引空间从而提高索引效率

原则：降低重复的索引值

例如现在有一个地区表

发现 area 字段很多都是以 china 开头的

那么如果以前1-5位字符做前缀索引就会出现大量索引值重复的情况

索引值重复性越低查询效率也就越高

前缀索引测试

// 创建一个测试表
CREATE TABLE `x_test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `x_name` varchar(255) NOT NULL,
  `x_time` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4145025 DEFAULT CHARSET=utf8mb4

// 添加200万条测试数据
INSERT INTO x_test(x_name,x_time) SELECT CONCAT(rand()*3300102,x_name),x_time FROM x_test WHERE id < 30000;

200万测试数据

1.在无任何索引的情况下随便查询一条

SELECT * FROM x_test WHERE x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';

查询时间:2.253s。推荐：Java面试练题宝典

2.添加前缀索引 ( 以第一位字符创建前缀索引 )

alter table x_test add index(x_name(1))

再次查询相同sql语句

SELECT * FROM x_test WHERE x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';

查询时间:3.291s

当使用第一位字符创建前缀索引后貌似查询的时间更长了，因为只第一位字符而言索引值的重读性太大了。200万条数据全以数字开头那么平均20万条的数据都是相同的索引值。

3.重新建立前缀索引这次以前4位字符来创建

alter table x_test add index(x_name(4));

再次查询相同sql语句

SELECT * FROM x_test WHERE x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';

查询时间:0.703s

这次以前4位创建索引大大减少了索引值的重复性查询速度从3秒提升到0.7秒

4.200万条数据都以数字开头而0-9排列组合7位则可达到千万种组合

也就是以前7位来做索引则不会出现重复索引值的情况了

alter table x_test add index(x_name(7));

再次查询相同sql语句

SELECT * FROM x_test WHERE x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';

查询时间:0.014s ( 首次执行无缓存状态下 )

作者：秋天的风，夏天的雨
cnblogs.com/beyond-succeed/p/12574647.html

 
  END
 


 十期推荐
 【231期】面试官：Java中 serialVersionUID 的作用是什么？

 【232期】面试官：Spring的 IOC 容器比New对象究竟好在哪?

 【233期】面试官：什么是耦合？解耦合的方法有哪几种？

 【234期】30个 Java 集合面试必备的问题和答案

 【235期】面试官：Redis的数据是存在内存里吗？谈谈Redis各种数据类型的使用场景？

 【236期】面试官：线程池中多余的线程是如何回收的？

 【237期】面试官：如何发现 Redis 热点 Key ，解决方案有哪些？

 【238期】面试官：Redis新版本开始引入多线程，谈谈你的看法？

 【239期】面试官：如何使用Redis实现电商系统的库存扣减？

 【240期】面试官：你了解JVM的内存溢出吗？
 

？ ~

以上数据来源于网络，如有侵权，请联系删除。

上一篇：面试官：Spring AOP有哪些通知类型，它们的执行顺序是怎样的？

下一篇：万字+图解 Redis，面试不用愁了！

类别

什么是前缀索引？

为什么要用前缀索引？

什么情况下适合使用前缀索引？

1.在无任何索引的情况下随便查询一条

2.添加前缀索引 ( 以第一位字符创建前缀索引 )

3.重新建立前缀索引 这次以前4位字符来创建

4.200万条数据都以数字开头 而0-9排列组合7位则可达到千万种组合

3.重新建立前缀索引这次以前4位字符来创建

4.200万条数据都以数字开头而0-9排列组合7位则可达到千万种组合