-- 前缀索引和索引选择性
-- 构建demo表数据
drop TABLE city_demo;
create table city_demo(city varchar(50) not null) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into city_demo(city) select name from hz_china_area;
select count() from city_demo;
-- 找到最常见的城市列表
select count() as cnt, city from city_demo group by city order by cnt desc limit 11;
-- 找到最频繁出现的前缀
select count(*) as cnt, left(city, 3) as pref from city_demo group by pref order by cnt desc limit 10;
-- 计算完整列的选择性
select count(distinct city)/count(*) from city_demo;
select count(distinct left(city,3))/count() as sel3,
count(distinct left(city,4))/count() as sel4,
count(distinct left(city,5))/count() as sel5,
count(distinct left(city,6))/count() as sel6,
count(distinct left(city,7))/count(*) as sel7
from city_demo;
-- 创建前缀索引. 前缀索引不能做order by和group by, 也不能做覆盖扫描
-- 可以在衣柜很长的16进制字符串上创建索引. 比如长度8,可以显著的提升性能.
alter table city_demo add key (city(7));
