mysql 确定前缀索引长度方法

应用场景：

数据库里有个地址（address）字段，类型为varchar(100)，业务决定了要经常根据address来进行查询。

确定选择性：

[sql] view plaincopy

1. SELECT count(DISTINCT(address))/count(*) AS Selectivity FROM info;  
2. +-------------+  
3. | Selectivity |  
4. +-------------+  
5. |      0.8745 |  
6. +-------------+  

<address>选择性很好，但是长度为100，对整个字段建立索引显然不合适，可以考虑建立前缀索引，例如<left(address,5)>，看看其选择性：

[sql] view plaincopy

1. SELECT count(DISTINCT(left(address,5)))/count(*) AS Selectivity FROM info;  
2. +-------------+  
3. | Selectivity |  
4. +-------------+  
5. |      0.5981 |  
6. +-------------+  

 

选择性还不错，但和0.8745相比还是太低，所以我们可以将前缀长度增加到10，再看看选择性：

[sql] view plaincopy

1. SELECT count(DISTINCT(left(address,10)))/count(*) AS Selectivity FROM info;  
2. +-------------+  
3. | Selectivity |  
4. +-------------+  
5. |      0.8239 |  
6. +-------------+  

和0.8745已经很接近了，但是索引长度只有10，所以就可以决定建立前缀索引了。

前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。