Mysql前缀索引和最左原则

前缀索引(https://blog.csdn.net/ma2595162349/article/details/79449493)

1. 是指索引的值比较大时, 取值的前一部分数据作为索引.

2.问题: 前一部分的值取多长?

可以通过该字段的重复率来判断, 取重复率接近整个字段的值的重复率的截取长度.

select 1.0*count(distinct left(name))/count(*) from test;  结果为1

select 1.0*count(distinct left(name,3))/count(*) from test; 假如结果为0.75

select 1.0*count(distinct left(name,4))/count(*) from test; 假如结果为1

则取name的前4个字长作为索引

alter table test add key(name(4));

 

最左原则(https://www.cnblogs.com/lanqi/p/10282279.html)

最左原则是建立在联合索引的基础上

索引的匹配规则是从左边第一个字段开始匹配, 当左边第一个字段匹配到时, 然后再匹配左边第二个字段, 当遇到范围查询时, 范围后的字段不会走索引

b = 2 如果建立(a,b)顺序的索引，是匹配不到(a,b)索引的；但是如果查询条件是a = 1 and b = 2或者a=1(又或者是b = 2 and a = 1)就可以，因为优化器会自动调整a,b的顺序。再比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，因为c字段是一个范围查询，它之后的字段会停止匹配。

原理

我们都知道索引的底层是一颗B+树，那么联合索引当然还是一颗B+树，只不过联合索引的健值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。
例子：假如创建一个（a,b)的联合索引，那么它的索引树是这样的

可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。所以b = 2这种查询条件没有办法利用索引，因为联合索引首先是按a排序的，b是无序的。

同时我们还可以发现在a值相等的情况下，b值又是按顺序排列的，但是这种顺序是相对的。所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。例如a = 1 and b = 2 a,b字段都可以使用索引，因为在a值确定的情况下b是相对有序的，而a>1and b=2，a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的。