概述
字符串列具有不定长得特点,可能长度为0也可能长度为1024,当我们频繁查询这个列的时候,我们可以为这个字符串列建立一个索引,但是这个索引不应该是不定长的, 我们应该来选择一个合适的前缀长度来建立索引.下面介绍找到最佳长度的过程.
找到最佳前缀索引长度
创建表
create table city (
id int(11) not null auto_increment,
cname varchar(255),
primary key(id)
);
# 插入数据
insert into city(cname) values('New York');
insert into city(cname) values('Beijing');
insert into city(cname) values('Guangzhou');
insert into city(cname) values('Shanghai');
insert into city(cname) values('Shenzhen');
insert into city(cname) values('Chongqing');
insert into city(cname) values('London');
insert into city(cname) values('Tokyo');
insert into city(cname) values('Soul');
insert into city(cname) values('Garden Grove');
insert into city(cname) values('Escobar');
insert into city(cname) values('Amroha');
insert into city(cname) values('Tegal');
insert into city(cname) values('Lancaster');
insert into city(cname) values('Jelets');
insert into city(cname) values('Ambattur');
insert into city(cname) values('Yingkou');
insert into city(cname) values('Monclova');
insert into city(cname) values('Dazhou');
insert into city(cname) values('Guangan');
# 复制数据(执行多次)
insert into city(cname) select cname from city;
# 随机化数据(每次执行随机删除1000条)
DELETE FROM city ORDER BY RAND() LIMIT 1000;
数据计算
- 查看最常出现的10个城市.
select count(*) as cnt, cname from city group by cname order by cnt desc limit 10;
返回结果
+-----+-----------+
| cnt | cname |
+-----+-----------+
| 654 | Guangan |
| 652 | Lancaster |
| 645 | Shanghai |
| 633 | Amroha |
| 607 | Chongqing |
| 578 | Beijing |
| 574 | Soul |
| 574 | Ambattur |
| 552 | Hangzhou |
| 549 | New York |
+-----+-----------+
- 通过计算
完整列的选择性
, 然后使得前缀的选择性接近于完整列的选择性.
SELECT count(distinct cname)/count(*) FROM city;
返回结果
+--------------------------------+
| COUNT(distinct cname)/count(*) |
+--------------------------------+
| 0.0018 |
+--------------------------------+
- 查询多个列的选择性进行比较
# 如果数据比较平均, 则这个结果也会比较平均.
SELECT count(distinct LEFT(cname, 3))/count(*) as factor3,
count(distinct LEFT(cname, 4))/count(*) as factor4,
count(distinct LEFT(cname, 5))/count(*) as factor5,
count(distinct LEFT(cname, 6))/count(*) as factor6,
count(distinct LEFT(cname, 7))/count(*) as factor7,
count(distinct LEFT(cname, 8))/count(*) as factor8,
count(distinct LEFT(cname, 9))/count(*) as factor9
FROM city;
返回结果
+---------+---------+---------+---------+---------+---------+---------+
| factor3 | factor4 | factor5 | factor6 | factor7 | factor8 | factor9 |
+---------+---------+---------+---------+---------+---------+---------+
| 0.0017 | 0.0017 | 0.0017 | 0.0018 | 0.0018 | 0.0018 | 0.0018 |
+---------+---------+---------+---------+---------+---------+---------+
- 查看长度为4的前缀的最多的前面10个.
SELECT COUNT(1) cnt, LEFT(cname,4) AS pref FROM city GROUP BY pref ORDER BY cnt DESC LIMIT 10;
返回结果
+------+------+
| cnt | pref |
+------+------+
| 1188 | Guan |
| 652 | Lanc |
| 645 | Shan |
| 633 | Amro |
| 607 | Chon |
| 578 | Beij |
| 574 | Soul |
| 574 | Amba |
| 552 | Hang |
| 549 | New |
+------+------+
结果选择
: 在我的表中, 当我选择6时候分布最为均匀,是一个好的结果.
在cname列上创建索引
ALTER TABLE city add key (cname(6));
总结
前缀索引能够使得查找更加快, 索引大小更小的有效办法. 但是缺点是无法使用前缀索引做group by或者order by, 也无法使用前缀索引做覆盖扫描.