文章详情

短信预约信息系统项目管理师 报名、考试、查分时间动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL 前缀索引

2019-05-10 00:18

关注

MySQL 前缀索引

使用 字符串列的索引规范中的语法,您可以创建仅使用列首字符的索引 。以这种方式仅索引列值的前缀可以使索引文件小得多。为a 或 column 编制索引时 , 必须为索引指定前缀长度。例如: col_name(N)NBLOBTEXT

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

前缀最长可以为1000个字节(InnoDB表中为767个字节 ,除非已 innodb_large_prefix设置)。

注意 前缀限制以字节为单位,而在前缀长度CREATE TABLE, ALTER TABLE和 CREATE INDEX语句被解释为非二进制串类型的字符数(CHAR, VARCHAR, TEXT对于二进制串类型),并且字节数(BINARY, VARBINARY, BLOB)。为使用多字节字符集的非二进制字符串列指定前缀长度时,请考虑到这一点。
如果搜索词超过索引前缀长度,则使用索引排除不匹配的行,然后检查其余行是否可能匹配。


当要索引的列字符很多时 索引则会很大且变慢
( 可以只索引列开始的部分字符串 节约索引空间 从而提高索引效率 )

原则: 降低重复的索引值

例如现在有一个地区表

area gdp code
chinaShanghai 100 aaa
chinaDalian 200 bbb
usaNewYork 300 ccc
chinaFuxin 400 ddd
chinaBeijing 500 eee

发现 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;

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

SELECT * FROM x_test WHERE x_name = "1892008.205824857823401.800099203178258.8904820949682635656.62526521254";

查询时间:2.253s

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

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万条的数据都是相同的索引值

重新建立前缀索引 这次以前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秒

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 ( 首次执行无缓存状态下 )

参考:
https://dev.mysql.com/doc/refman/5.7/en/column-indexes.html
https://www.jianshu.com/p/fc80445044cc

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯