1. 全栈程序员必看首页

mysql前缀索引及其选择「建议收藏」

全栈程序员-用户IM • • 未分类

mysql前缀索引及其选择「建议收藏」有时候需要索引很长的字符列，比如BLOB、TEXT或者很长的VARCHAR类型的列，通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。

大家好，又见面了，我是你们的朋友全栈君。 

索引的选择性：
    是指不重复的索引值（也称基数）和数据表的记录总数（#T）的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

为了保证前缀索引有较高的选择性，同时又不能太长可以使用计算完整列的选择性，并使前缀的索引性接近于完整列的选择性，方法如下：

第一步：计算完整列的选择性：

表名：city_demo, city是城市名称字段

mysql>SELECT COUNT(DISTINCT `city`)/COUNT(*) FROM `city_demo`;

显示结果：

+---------------------------------+
| COUNT(DISTINCT `city`)/COUNT(*) | +-----------------+----------+-----
| 0.0312| +---------------------------------+

也就是说前缀索引的选择性能够接近0.031,那么该长度的索引就可以用了。

第二步:计算不同前缀长度的选择性

mysql> SELECT COUNT(DISTINCT LEFT(`city`,3))/COUNT(*) AS sel3, mysql> COUNT(DISTINCT LEFT(`city`,4))/COUNT(*) AS sel4,
mysql> COUNT(DISTINCT LEFT(`city`,5))/COUNT(*) AS sel5, mysql> COUNT(DISTINCT LEFT(`city`,6))/COUNT(*) AS sel6,
mysql> COUNT(DISTINCT LEFT(`city`,7))/COUNT(*) AS sel7 mysql> FROM `city_demo`;

显示结果：

+-------+-------+-------+-------+-------+
| sel3 | sel4 | sel5 | sel6 | sel7 | +-------+-------+-------+-------+-------+
| 0.239 |0.0293 |0.0305 |0.0309 |0.0310 | +-------+-------+-------+-------+-------+

查询结果显示当前缀长度达7的时候，前缀长度的选择性接近完整列的选择性，再增加前缀长度，选择性提升的幅度已经很小了。

第三步:创建前缀索引

mysql> ALTER TABLE `city_demo` ADD KEY(city(7));

其他

前缀索引缺点

MySQL 无法使用前缀索引做ORDER BY 和GROUP BY，也无法使用前缀索引做覆盖扫描

前缀索引扩展

有时候后缀索引也有用途（例如，找到某个域名的所有电子邮件地址）。MySQL原生并不支持反向索引，但是可以把字符串反转存储，并基于此建立前缀索引。可以通过触发器来维护这种索引。

以上内容总结自《高性能MYSQL》5.3.2

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

发布者：全栈程序员-用户IM，转载请注明出处：https://javaforall.cn/142396.html原文链接：https://javaforall.cn

【正版授权，激活自己账号】： Jetbrains全家桶Ide使用，1年售后保障，每天仅需1毛

【官方授权 正版激活】： 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

赞 (0)
全栈程序员-用户IM全栈程序员-用户IM
0 0


相关推荐

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号