大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说MySQL 前缀索引[通俗易懂],希望您对编程的造诣更进一步.
索引前缀
使用 字符串列的索引规范中的语法，您可以创建仅使用列首字符的索引 。以这种方式仅索引列值的前缀可以使索引文件小得多。为a 或 column 编制索引时 ， 必须为索引指定前缀长度。例如： col_name(N)NBLOBTEXT
CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
代码100分
前缀最长可以为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位字符做前缀索引就会出现大量索引值重复的情况
索引值重复性越低 查询效率也就越高
前缀索引测试
代码100分// 创建一个测试表
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;
1.在无任何索引的情况下随便查询一条
SELECT * FROM x_test WHERE x_name = "1892008.205824857823401.800099203178258.8904820949682635656.62526521254";
查询时间:2.253s
2.添加前缀索引 ( 以第一位字符创建前缀索引 )
代码100分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 ( 首次执行无缓存状态下 )
参考：
https://dev.mysql.com/doc/refman/5.7/en/column-indexes.html
https://www.jianshu.com/p/fc80445044cc
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/7716.html
