【243期】面试官——什么是前缀索引、为什么要用前缀使用、用在什么场景下?

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 【243期】面试官——什么是前缀索引、为什么要用前缀使用、用在什么场景下?

【243期】面试官:什么是前缀索引、为什么要用前缀使用、用在什么场景下?

什么是前缀索引?

前缀索引也叫局部索引,比如给身份证的前 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。推荐:

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**

cnblogs.com/beyond-succeed/p/12574647.html

END

十期推荐


与其在网上拼命找题?** 不如马上关注我们~**

【243期】面试官:什么是前缀索引、为什么要用前缀使用、用在什么场景下?

原文始发于微信公众号(Java面试题精选):

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 【243期】面试官——什么是前缀索引、为什么要用前缀使用、用在什么场景下?


 上一篇
【242期】面试官——Spring AOP有哪些通知类型,它们的执行顺序是怎样的? 【242期】面试官——Spring AOP有哪些通知类型,它们的执行顺序是怎样的?
前言这篇比较简单,但是群友反馈面试被问到了,关键还没答出来,而且还是项目中用的比较多的技术点。还是要在平时开发中有一丢丢好奇心,多点进去看看代码啊! 通知类型在基于Spring AOP编程的过程中,基于AspectJ框架标准,sprin
2021-04-05
下一篇 
【245期】面试官——MySQL发生死锁有哪些原因,怎么避免? 【245期】面试官——MySQL发生死锁有哪些原因,怎么避免?
一、Mysql 锁类型和加锁分析锁类型介绍:MySQL有三种锁的级别:页级、表级、行级。 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生
2021-04-05