面试官：什么是前缀索引、为什么要用前缀使用、用在什么场景下？
SQL数据库开发
共 2433字，需浏览 5分钟
·
2021-03-20 12:16
点击关注上方“SQL数据库开发”，
设为“置顶或星标”，第一时间送达干货
# 什么是前缀索引？
# 为什么要用前缀索引？
# 什么情况下适合使用前缀索引？
可以只索引列开始的部分字符串 节约索引空间 从而提高索引效率
例如现在有一个地区表
发现 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 ( 首次执行无缓存状态下 )
最后给大家分享我写的SQL两件套：《SQL基础知识第二版》和《SQL高级知识第二版》的PDF电子版。里面有各个语法的解释、大量的实例讲解和批注等等，非常通俗易懂，方便大家跟着一起来实操。
有需要的读者可以下载学习，在下面的公众号「数据前线」(非本号)后台回复关键字：SQL，就行
数据前线
后台回复关键字：1024，获取一份精心整理的技术干货
后台回复关键字：进群，带你进入高手如云的交流群
记得帮忙点「赞」和「在看」↓
谢谢啦
评论