大家好,又见面了,我是你们的朋友全栈君。
索引的选择性:
是指不重复的索引值(也称基数)和数据表的记录总数(#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账号...