mysql前缀索引和索引选择性

索引选择性：不重复的索引值和记录总数的比值
索引选择性越高，查询效率越高，因为可以过滤掉更多的行
如何确定前缀索引的长度？
方法一：
步骤一、查询来自同一城市的学生最多的10个城市的学生

select count(1) as num ,cityname from student group by cityname order by num desc limit 10;

步骤二、按照城市前几个字符，分别算来自这些城市最多的10个城市学生

select count(1) as num ,left(cityname,1) as simcityname from student group by simcityname  order by num desc limit 10;
select count(1) as num ,left(cityname,3) as simcityname from student group by simcityname  order by num desc limit 10;

步骤三、当步骤一和步骤二的数值接近的时候，就是该查询的合适的索引长度

方法二：前缀的选择性接近完整列的选择性
步骤一、计算完整列的选择性

select count(distinct cityname)/count(1) from student;

步骤二、计算前缀的选择性

select 
count(distinct left(cityname,1))/count(1) as col1,
count(distinct left(cityname,2))/count(1) as col2,
count(distinct left(cityname,3))/count(1) as col3
from student;

mysql无法使用前缀索引做order by和group by操作