-- 前綴索引和索引選擇性

-- 構(gòu)建demo表數(shù)據(jù)
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;
-- 找到最頻繁出現(xiàn)的前綴
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;

-- 創(chuàng)建前綴索引. 前綴索引不能做order by和group by, 也不能做覆蓋掃描
-- 可以在衣柜很長的16進制字符串上創(chuàng)建索引. 比如長度8,可以顯著的提升性能.
alter table city_demo add key (city(7));