雁过留声

a tiger in me sniffs roses

0%

mysql建立索引时为什么要求区分度高？

发表于 更新于 分类于

因为InnoDB支持事务，空间利用率高（使用聚簇索引），行级锁等特点，是mysql的默认存储引擎。在建立索引时，通常说索引列区分度高效果更好，其原理是什么？
MySQL B+树索引是MySQL中最常用的索引类型，它是一种基于B+树数据结构的索引方式。B+树索引的优点在于它可以快速定位到需要查找的数据，同时还可以支持范围查询和排序等操作。B+树是在B树基础上的一种优化，非叶子节点上仅存储键值，不存储数据；而所有数据记录均存储在叶子节点上，并且数据是按照顺序排列的。

  • 如果一列数据为性别，只有1-2个值时，查询时会不会走索引？索引树是什么样？

  • 为什么not in也通常会使用索引失效？

区分度对索引有什么影响？

有如下一张表：
alt

在sex字段上建立了一个索引，当使用

1
2
SELECT * FROM `people` WHERE sex = 1;
# 查询时间1.837s

explain 显示
alt
确实走了索引扫描，但是速度比不使用索引慢

1
2
SELECT * FROM `people` ignore index(idx_sex) WHERE sex = 1 ;
# 查询时间 0.615s

不走索引更快
对于仅有1、2的b+tree树如下，相同的结点会分布在不同的分支上，
alt
查询时会走过不同的分支而不是单单一边的分支

对于区分度高的age字段，值范围在[0,100] 之前, 使用索引可以显著减少扫描的行数：
alt

not in 索引失效

1
2
3
4
5
6
7
8
9
10
11
12
EXPLAIN SELECT * FROM `people` WHERE age NOT IN (83);
/* 1	SIMPLE	people	ALL	idex_age				995605	84.01	Using where */

EXPLAIN SELECT age FROM `people` WHERE age NOT IN (83);

/* 1	SIMPLE	people		range	idex_age	idex_age	5		836434	100.00	Using where; Using index */

EXPLAIN SELECT age FROM `people` WHERE age NOT IN (83, 37)

/* 
1	SIMPLE	people		index	idex_age	idex_age	5		995605	100.00	Using where; Using index
*/

not in 并不是总是不能使用索引，当查询的字段得包含索引字段，可以使用到索引，type为range 或 index，并不高效，当in中只有一个值时，会被查询优化器优化