mysql 索引长度和区分度

最新推荐文章于 2025-01-14 11:11:42 发布
转载 最新推荐文章于 2025-01-14 11:11:42 发布 · 1.7k 阅读 · 1

该博客围绕MySQL索引展开，重点探讨索引长度和区分度相关内容。索引的长度和区分度对数据库性能有重要影响，合理设置能提升查询效率等。
10步轻松掌握MySQL索引长度设置，你也能成为索引大师
java专栏
02-22 902
通过本文的详细讲解，你应该已经掌握了如何设置MySQL索引长度的方法。从分析字段的区分度、测试不同长度的索引、创建合适的索引，到验证索引效果和注意事项，每一步都详细解析，希望能帮助你轻松成为索引大师。如果你有任何问题或建议，欢迎在评论区留言交流，我们一起学习，共同进步！💬🌟😘。
【MySQL 系列】MySQL 索引篇
栗筝i的博客
03-14 3964
在 MySQL 中，索引是一种帮助存储引擎快速获取数据的数据结构，形象的说就是索引是数据的目录。它一般是以包含索引键值和一个指向索引键值对应数据记录物理地址的指针的节点的集合的清单的形式存在。
MySQL索引长度限制
u011250186的博客
10-19 2897
索引 TextField是不支持建立索引的 MySQL对索引字段长度有限制 innodb引擎的每个索引列长度限制为767字节（bytes），所有组成索引列的长度和不能大于3072字节 myisam引擎的每个索引列长度限制为1000字节，所有组成索引列的长度和不能大于1000字节 varchar的最大长度是指字符长度，若数据库字符集为utf-8，则一个字符占3个bytes。因此在utf-8字符集下，innodb引擎创建的单列索引长度不能超过255个字符 mysql版...
mysql索引长度
zhaojianyin
09-04 605
计算title中，从左边数第10个不重复的汉字的行数与总行数的比 对于一般的系统应用: 区别度能达到0.1,索引的性能就可以接受. select count(distinct left(title,10))/count(*) from item; 索引长度越低，索引在内存中占的长度越小，排序越快，然而区分度就越低。这样不利于查找。 索引长度越长，区分度就高，虽然利于查找了，但是索引在内存中占得空...
MySQL 索引长度和区分度
weixin_30270889的博客
02-12 257
首先 索引长度和区分度是相互矛盾的， 索引长度太短，那么区分度就很低，吧索引长度加长，区分度就高，但是索引也是要占内存的，所以我们需要找到一个平衡点； 那么这个平衡点怎么来定？ 比如用户表有个字段 username ，要给他加索引，问题是索引长度多少合适？ 其实我们知道 百家姓里面有百多个姓 ，但是大多数人的姓 集中在前十多个；如果我设置索引索引长度为1，对染占内存少，但是区分度低...
oracle11g 新特性之不可见索引
08-29 347
不可见索引是11g引入的新特性，所谓的索引不可见，是语句生成的计划不会使用该索引，但是在物理上时可见的，也存在的，DML语句发生时候照常会进行维护，也就是说处于INVISIBLE状态下的索引并不失效，只是优化器不选择它而已...
Mysql索引长度的计算
熙熙小学姐
02-29 1191
MySQL Innodb 对于索引长度的限制为 767 字节，并且UTF8mb4字符集是4字节字符集，则 767字节 / 4字节每字符 = 191字符(默认索引最大长度)，所以在varchar(255)或char(255) 类型字段上创建索引会失败，提示最大索引长度为767字节。
MySQL 数据库 ：区分度(散列性/选择性) 的计算公式、索引规约、利用延迟关联或者子查询优化超多分页场景、 基于键集分页方法，处理时间分页数据重复的问题、高效率地导出大量数据的流水数据报表
专注于计算机研发知识和资讯分享
01-14 599
索引物理文件全扫描，速度非常慢，这个 index 级别比较 range 还低，与全表扫描是小巫见大巫。至少要达到 range 级别，要求是 ref 级别，如果可以是 const 最好。说明：存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。那么即使c 的区分度更高，也必须把 d 放在索引的最前列，即建立组合索引。防止因字段类型不同造成的隐式转换，导致索引失效。，a 列的几乎接近于唯一值，那么只需要单建。反例： explain 表的结果，
MySQL——varchar上的索引必须要指定索引长度
庄小焱
03-27 2080
虽然在阿里的Java开发手册中，是强制要求varchar字段上建立索引必须要指定索引长度，但通过我们的分析也能看出，并不是索引的字段都适用的，比如区分度不高的、或者可以大量运用覆盖索引的实现的，我认为，开发手册强调的普适场景，具体如何运用应该是我们通过了解原理之后，自行笃定。看过阿里Java开发手册的同学，应该有注意到在MySQL索引规约中，有这么一条强制性规范：”在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度“。一、指定索引长度的原理。
MySQL索引
一直走的很慢的鸭子
10-30 1367
二级索引：idx_name_age 包含了 name 与 age两个字段查询语句：select name,age from table where name = 'xxx'像上述这种情况，所需要查询的字段正是二级索引中所包含的字段，二级索引可以直接满足查询需求，所以这种情况下就无需再回表查询了。
mysql 一个表中存多少个字段儿合理_详解MySQL索引长度和区分度之间的平衡，值得收藏...
weixin_39683978的博客
12-10 764
概述前面我们讲了怎么去计算索引所占用的长度？那么换个方式想？索引又应该设置多少长度比较合理呢？区分度与索引长度的权衡首先索引长度和区分度是相互矛盾的，索引长度太短，那么区分度就很低，吧索引长度加长，区分度就高，但是索引也是要占内存的，所以我们需要找到一个平衡点；那么这个平衡点怎么来定？比如用户表有个字段 username ，要给他加索引，问题是索引长度多少合适？其实我们知道 百家姓里面有百多个姓 ...
Mysql索引区分度、索引长度
m0_46700284的博客
03-29 1278
索引长度越长，索引区分度越高。索引长度越短，索引区分度越低。区分度越高，意味着排序越快，或者说本身就是按照大概率正确的顺序排好的。但索引长度越长，也意味着相同空间内能容纳的数目越少，所以需要找到一个平衡点。
MYSQL-索引长度与区分度
八角塘塘主的博客
07-09 500
背景 索引长度越长，不利于查询，因此索引尽量长度短一些。 区分度越低，不利于查询，因此索引尽量区分度高一些。 但是两者不能兼得。那么怎么办呢？ 公式如下： 键 的 distinct 数量 / 总量=趋近于1，则更好。 select distinct( left(key,n) ) / count(*) from table; ...
mysql计算字段文本区分度
硅脂味咖啡的博客
06-05 1989
有时候需要为字段创建索引时，但是字段太长，为整个字段创建索引的话，太浪费存储空间了，所以需要计算出字段区分度，选择合适的索引长度。 计算字段文本区分度的公式： select COUNT(DISTINCT left(column_name,length))/COUNT(*) from table_name 其中column_name是需要建立索引的字段，而length则是选择这个字段用来建立索引的长度。 公式的作用就是，选择出字段长度，去重求总，这时候就可以知道使用这个长度来建立索引，大概会有多少值，然后
Mysql 唯一索引长度_关于mysql索引长度的相关内容总结
weixin_42123456的博客
02-17 1076
MySQL优化之-索引具体代码分析：索引是在存储引擎中实现的，因此每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。大多数存储引擎有更高的限制。MYSQL中索引的存储类型有两种：BTREE和HASH，具体和表的存储引擎相关；MYISAM和InnoDB存储引...
MySQL 索引的区分度之性能分析
weixin_42678790的博客
07-23 6874
对于选择性不高的列甚至可以不创建索引。如果选择性不高，极端性情况下可能会扫描全部或者大多数索引，然后再回表，这个过程可能不如直接走主键索引性能高。 索引列的选择往往需要根据具体的业务场景来选择； 但是需要注意的是索引的区分度越高则价值就越高，意味着对于检索的性价比就高。 索引的区分度等于count(distinct 具体的列) / count(*)，表示字段不重复的比例。 唯一键的区分度是1，而对于一些状态值，性别等字段区分度往往比较低，在数据量比较大的情况下，甚至有无限接近0。 ...
mysql 索引长度限制详解
热门推荐
无风的雨
03-27 2万+
mysql 每个单表中对索引长度是有限制的，且根据不同的数据引擎会有所不同。 下面以mysql 5.5.47 版本为例做相应的说明。 MYISAM表 1、创建一个字符编码为utf8的表 test create table test(id int,name1 varchar(300),name2 varchar(300),name3 varchar(500)) CHARSET=utf8 e...
MySQL原理（四）索引（3）索引失效与索引区分度
w_t_y_y的博客
01-31 1033
（1）组合索引失效：如果索引了多列，要遵守最左前缀法则，Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但必须包含最左侧的一个。例如索引是index (a,b,c)，可以支持a | a,b| a,b,c |a,c这几种组合进行查找，但不支持 b|b,c|c进行查找。（5）对索引列进行计算、函数、(自动or手动)类型转换会导致索引失效，如where substr(a, 1, 3) = ‘hhh’、where a = a+1、DATE_FORMAT函数等；当in的条件多于1个时，
mysql查看索引的大小
weixin_30488085的博客
09-24 747
1 ，show table status fromyour_database_name 这个不准； 2，这个比较详细 select database_name, table_name, index_name, round((stat_value*@@innodb_page_size)/1024/1024, 2) SizeMB, ...
索引的区分度具体要求
最新发布
06-20
<think>嗯，用户这次聚焦在索引区分度这个具体技术上，看来ta正在深入优化数据库性能。之前讨论过索引的基本要求和限制，现在ta显然想解决更核心的效率问题——如何量化索引的有效性。用户引用的资料很有价值，尤其是那个区分度计算公式：$\text{区分度}=\frac{\text{count(distinctleft(字段,长度))}}{\text{count(1)}}$。这个公式把抽象概念量化了，但ta可能更需要知道具体操作阈值。让我结合不同数据库场景说明：首先区分度低于0.1的索引基本无效这个结论需要验证。在Oracle的位图索引场景中，低区分度反而有用；但用户问题明确是SQL索引，应聚焦B+树结构。根据MySQL手册，建议值确实是0.1，但要注意：-0.1是经验值，实际要考虑数据分布。比如医疗系统中的“疾病类型”字段，虽然只有10个枚举值（理论区分度0.01），但90%查询都按该字段过滤，这时仍需索引-联合索引中次要列可以放宽要求。比如订单表索引(user_id,status)，status只有3个状态，但user_id先过滤后数据量小，status的低区分度不影响效率[^3]用户提供的引用[2]提到索引长度与区分度的平衡，这点在实践中有个经典案例：对varchar(255)的email字段，完整索引区分度0.98但占用空间大。实际用left(email,20)计算发现区分度达0.95，这时前缀索引就能节省40%空间。特别要注意的是引用[4]的案例，9秒查询很可能是索引区分度陷阱。o_id字段如果包含大量重复值（如1,2,3占全表90%），即使有索引也会全表扫描。这时应该：1.检查区分度：selectcount(distincto_id)/count(1)fromxx2.若低于0.15，考虑强制指定索引或优化IN条件最后补充个容易忽略的点：NULL值处理。当索引列含大量NULL时：-区分度计算要排除NULL：count(distinctcol)/count(col)-SQLServer的筛选索引能针对性解决：CREATEINDEX...WHEREcolISNOTNULL</think>### SQL索引区分度要求详解 #### 一、核心概念 1. **索引区分度定义** 表示索引列中唯一值占总行数的比例，计算公式： $$ \text{区分度} = \frac{\text{count(distinct 索引列)}}{\text{count(1)}} $$ - **理想值**：接近1（如主键索引区分度=1）[^1] - **最低要求**：**>0.1**（低于此值索引通常无效）[^1][^2] 2. **区分度与性能关系** | 区分度范围 | 效果 | 建议 | |------------|-------------------------------|-------------------| | >0.3 | 高效索引 | 强烈推荐创建 | | 0.1~0.3 | 中等效果 | 需结合查询频率评估| | <0.1 | 基本无效，可能降低性能[^1] | 避免创建 | #### 二、具体实践要求 1. **单列索引要求** - **必须满足**：$ \text{区分度} \geq 0.1 $ - **优化方法**：对长字段（如varchar）使用**前缀索引**： ```sql -- 测试不同前缀长度的区分度 SELECT COUNT(DISTINCT LEFT(email, 5))/COUNT(1) AS prefix5, -- 0.82 COUNT(DISTINCT LEFT(email, 10))/COUNT(1) AS prefix10 -- 0.98 FROM users; ``` - 选择最短且区分度>0.9的前缀创建索引[^1][^2] 2. **联合索引设计原则** - **列顺序策略**： ```sql /* 正确顺序：高区分度列在前 */ CREATE INDEX idx_brand_cat ON goods(brand_id, cat_id); -- brand_id区分度0.4 > cat_id 0.2[^3] ``` - **低区分度列处理**： - 若某列区分度<0.1，需评估是否加入联合索引 - 高频查询的低区分度列可放在索引末尾[^3] #### 三、区分度不足的风险 1. **性能陷阱** - **额外IO消耗**：低区分度索引需多次"回表查询"，反而比全表扫描更慢[^1] - **优化器弃用**：如区分度0.05的`delete`字段索引被忽略，导致200万行查询耗时9秒[^4] ```sql /* 低效案例 */ SELECT COUNT(*) FROM orders WHERE delete_flag = 0 -- 区分度仅0.02（0/1两种值） AND o_id IN (1,2,3); -- 实际走全表扫描[^4] ``` 2. **空间浪费** - 低效索引占用存储空间，增加写入时的维护开销[^1] #### 四、优化策略 1. **强制使用索引**（谨慎使用） ```sql SELECT COUNT(*) FROM orders USE INDEX(idx_delete) WHERE delete_flag = 0 AND o_id IN (1,2,3); ``` 2. **覆盖索引优化** 扩展索引包含查询所需所有列，避免回表： ```sql CREATE INDEX idx_covering ON orders(delete_flag, o_id) INCLUDE (total_amount); ``` 3. **数据归档** 对历史数据（如`delete_flag=1`）定期归档，提升有效数据区分度[^4]。 --- ### 总结要求 | **场景** | **区分度要求** | **解决方案** | |------------------------|----------------------|----------------------------------| | 单列索引 | ≥0.1 | 低于阈值时不创建索引 | | 联合索引首列 | ≥0.3 | 优先选最高区分度列为首列[^3] | | 长文本字段索引 | 前缀区分度≥0.9 | 使用`LEFT(字段,N)`前缀索引[^1][^2] | | 低区分度高频查询列 | 可放宽至0.05~0.1 | 作为联合索引末尾列 | > **关键结论**：索引区分度本质是**空间与时间的权衡**。高区分度加速排序但增加空间占用，需通过`COUNT(DISTINCT LEFT(列,N))`找到最佳平衡点[^1][^2]。
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值