详解MySQL索引长度和区分度之间的平衡,值得收藏

数据库 MySQL 新闻
前面我们讲了怎么去计算索引所占用的长度?那么换个方式想?索引又应该设置多少长度比较合理呢?

[[271557]]

概述

前面我们讲了怎么去计算索引所占用的长度?那么换个方式想?索引又应该设置多少长度比较合理呢?

详解MySQL索引长度和区分度之间的平衡,值得收藏

区分度与索引长度的权衡

首先索引长度和区分度是相互矛盾的,

索引长度太短,那么区分度就很低,吧索引长度加长,区分度就高,但是索引也是要占内存的,所以我们需要找到一个平衡点;

那么这个平衡点怎么来定?

比如用户表有个字段 username ,要给他加索引,问题是索引长度多少合适?

其实我们知道 百家姓里面有百多个姓 ,但是大多数人的姓 集中在前十多个;如果我设置索引索引长度为1,占内存少,但是区分度低,区分度低索引的效率越低。太长则占内存;

首先 mysql的索引都是排好序的。如果区分度高排序越快,区分度越低,排序慢;

举个例子: (张,张三,张三哥),如果索引长度取1的话,那么每一行的索引都是 张 这个字,完全没有区分度,你让他怎么排序?结果这样三行完全是随机排的,因为索引都一样;如果长度取2,那么排序的时候至少前两个是排对了的,如果取3,区分度达到100%,排序完全正确;

那是不是索引越长越好? 答案肯定是错的,比如 (张,李,王) 和 (张三啦啦啦,张三呵呵呵,张三呼呼呼);前者在内存中排序占得空间少,排序也快,后者明显更慢更占内存。

总之:

索引长度越低,索引在内存中占的长度越小,排序越快,然而区分度就越低。这样不利于查找。

索引长度越长,区分度就高,虽然利于查找了,但是索引在内存中占得空间就多了。

mysql创建索引的时候指定索引长度

大部分的索引前面一部分的长度就能够有很好的区分度了。

通过减小索引长度,这样能够减小索引文件的大小,能够加快数据的insert。

语法:

 

  1. CREATE INDEX index_name ON table_name (column_name(length), clolumn_name(length)…); 

如何确认当前字段设置一个合适的长度呢?

索引长度与区分度要做一个取舍;这个取舍不是没有一个固定的量;需要根据数据库里面的数据来判断;比较常规的公式是:

 

  1. select count(distinct left(password, 5))/count(*) from user 

其中password是要加索引的字段,5是索引长度,求出一个浮点数,这个浮点数是逐渐趋向1的,上面这个比值,也算是区分度,也可以算作索引长度测试值,多测试几组,找出最合适的来,一般的区分值在0.1左右就差不多了。

网上找了个图片来分析下;

详解MySQL索引长度和区分度之间的平衡,值得收藏

这个地方观察到,当索引长度达到4的时候就已经趋向1了,所以长度设为4是最佳的,在大点增加的索引效果已经很小了,这个地方不是说必须接近1才行;总之要找一个平衡点;

还有一些特殊的字段常规方法用起不太顺畅,比如有一个url字段,绝大部分的url都是 http://www. 开头的,这种情况下索引长度取取到11都是无效的,需要更长的索引,那么有没有优雅的方式来解决呢;

  • 第一种方法: 可以将数据倒序存入数据库;
  • 第二种方法:对字符串进行crc32哈希处理;

两种方法都不错,当然要配合客户端程序完成;

简单测试:

详解MySQL索引长度和区分度之间的平衡,值得收藏

这个方法可能是优化最后才考虑的点了,不建议太过深究,了解到这就行了。后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注一下~

责任编辑:华轩 来源: 今日头条
相关推荐

2019-09-12 08:03:56

MySQL索引数据库

2023-11-15 16:35:31

SQL数据库

2011-03-31 13:51:54

MySQL索引

2019-09-02 14:53:53

JVM内存布局GC

2017-02-28 19:30:31

Windows 10Windows关闭索引

2018-09-21 11:31:59

2019-09-26 08:59:39

DockerGoogle软件

2019-10-12 00:03:07

MyCat数据库分库分表

2021-01-05 10:41:07

远程工作CIO首席信息官

2022-07-22 09:55:02

Python练手题

2019-07-29 17:15:35

MySQL操作系统数据库

2022-10-10 19:05:01

DSL宽带SDSL

2019-10-22 18:00:00

MySQL基础入门数据库

2019-09-04 08:13:53

MySQLInnodb事务系统

2023-06-29 00:17:58

DevSecOps软件开发

2019-04-22 15:00:05

CSS前端开发

2010-05-12 11:14:25

MySQL SQL优化

2019-01-17 08:16:05

MySQL规范数据库

2018-11-20 15:05:12

MySQLMyISAM数据库

2020-06-15 14:39:27

智慧城市物联网5G
点赞
收藏

51CTO技术栈公众号