Schema的优化和索引 - 高性能的索引策略 - 前缀索引和索引的选择性
2009-09-02 00:00:00 来源:WEB开发网如果前缀的选择性接近与0.031。那么这个前缀长度就是合适的。下面就是找到合适前缀的语句。
mysql> 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 sakila.city_demo;
+--------+--------+--------+--------+--------+
| sel3 | sel4 | sel5 | sel6 | sel7 |
+--------+--------+--------+--------+--------+
| 0.0239 | 0.0293 | 0.0305 | 0.0309 | 0.0310 |
+--------+--------+--------+--------+--------+
这个查询显示出,7字符的前缀长度最佳。
看平均的选择性也不是太好的主意。你也需要考虑最坏情况的选择性的时候。平均的选择性可能使你看到4,5长度的前缀也是不错的。但是如果你的数据是不均匀的。这可能就出现问题了。如果你查看最常见城市的前缀为4的出现数目,你就会清楚的发现不均匀的数据。
mysql> SELECT COUNT(*) AS cnt, LEFT(city, 4) AS pref
-> FROM sakila.city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 5;
+-----+------+
| cnt | pref |
+-----+------+
| 205 | San |
| 200 | Sant |
| 135 | Sout |
| 104 | Chan |
| 91 | Toul |
+-----+------+
4个字符,出现前缀的频率比全部值的频率要高。因此选择性就要比平均选择性要低很多。如果你有真实的数据,你可能看到这种影响非常大。比如,你在真实的城市名称中创建4个字符前缀的索引,会带来非常差的选择性。
对于我们的示例数据,我们已经发现合适的值了。来看看怎样在这个列创建索引:
mysql> ALTER TABLE sakila.city_demo ADD KEY (city(7));
前缀的索引可以使索引更小并且更快,但是它也有明显的缺点。MySQL不能在ORDER BY或GROUP BY语句中使用前缀索引,也不能把它们作为覆盖索引进行使用。
更多精彩
赞助商链接