欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MySQL创建前缀索引和索引选择性

程序员文章站 2024-03-16 18:59:04
...

“天忽作晴山捲慢,云犹含态石披衣”
什么是前缀索引: 一般情况下,如果需要作为索引的那一列长度很长.就需要使用前缀索引来节约索引控件.即只需要为前面几个字符作为索引.
什么是索引选择性: 索引选择性是指,不重复的索引值(也称为基数)和数据表的记录总数(#T)的比值,范围从1/#T到1之间.索引选择性越高则查询效率越高,因为选择性高的索引可以让MySQL在查找时过滤掉更多的行.唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的.
举个例子说明:这里有一张测试表(只截图其中一列,该列待会要用到),如下:
MySQL创建前缀索引和索引选择性
写个简单的查询:用时0.111秒(这是没有加前缀索引的结果)
MySQL创建前缀索引和索引选择性
下面开始给表加前缀索引:
计算索引前缀的诀窍在于要选择足够长的前缀以保证较好的选择性,同时又不能太长(以便节约空间),前缀应该足够长,以使得前缀索引的选择性接近于索引整个列.话句话说,前缀的”基数”应该接近于完整列的”基数”.
计算完整列的基数:

select COUNT(DISTINCT id_address)/COUNT(*) from tabelName;

MySQL创建前缀索引和索引选择性
我这里的结果为0.7864
接下来计算最接近完整列的基数:

select COUNT(DISTINCT LEFT(id_address,3))/COUNT(*) as sel3,
COUNT(DISTINCT LEFT(id_address,4))/COUNT(*) as sel4,
COUNT(DISTINCT LEFT(id_address,5))/COUNT(*) as sel5,
COUNT(DISTINCT LEFT(id_address,6))/COUNT(*) as sel6,
COUNT(DISTINCT LEFT(id_address,7))/COUNT(*) as sel7,
COUNT(DISTINCT LEFT(id_address,8))/COUNT(*) as sel8,
COUNT(DISTINCT LEFT(id_address,22))/COUNT(*) as sel22,
COUNT(DISTINCT LEFT(id_address,23))/COUNT(*) as sel23,
COUNT(DISTINCT LEFT(id_address,24))/COUNT(*) as sel24,
COUNT(DISTINCT LEFT(id_address,25))/COUNT(*) as sel25
 from tableName;

MySQL创建前缀索引和索引选择性
这里算出当前缀为25时,该基数等于完整列的基数.
接下来建立长度为25的前缀索引.

alter table tableName add key (id_address(25));

建立完成之后,再来执行刚才耗时0.111秒的语句,看耗时多少.
MySQL创建前缀索引和索引选择性
这次只耗时0.037秒..可以看出前缀索引的效果.

刚才的前缀索引是通过算出来的最佳值(长度为25).这次我们来试一下随机值.设置前缀索引长度为15;

alter table tableName add key (id_address(15));

再来看下查询耗时多少.
MySQL创建前缀索引和索引选择性
这次用时0.044秒.由此可见索引县选择性的重要性.