mysql前缀索引及其选择

2024-07-24 12:59:49

字体：大中小

来源：转载

供稿：网友

索引的选择性：是指不重复的索引值（也称基数）和数据表的记录总数（#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 sel7mysql> FROM `city_demo`;

显示结果：

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

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