一种可衡量的确定MySQL前缀索引长度方法

前缀索引，是指对于varchar/text/blob类型的字段建立索引时一般都会选择前N个字符作为索引。索引很长的字符列，会让索引变得大且慢。索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率，但这样也会降低索引的选择性。索引的选择性是指不重复的索引值（也称为基数，cardinality)和数据表的记录总数的比值。
计算公式：

SELECT COUNT(DISTINCT(a))/COUNT(*) FROM table1;

比值约接近于1，说明选择性越好，说明不重复的值的行数约接近总数。

---

那怎么选择一个varchar/text/blob的字段应该选择前N个字符呢？如何确定N的大小呢？有没有科学衡量的方法？
答案是有的。
计算方法如下：

mysql> SELECT COUNT(DISTINCT(a))/COUNT(*) e,
    -> COUNT(DISTINCT(LEFT(a,1)))/COUNT(*) e1,COUNT(DISTINCT(LEFT(a,2)))/COUNT(*) e2,COUNT(DISTINCT(LEFT(a,3)))/COUNT(*) e3,
    -> COUNT(DISTINCT(LEFT(a,4)))/COUNT(*) e4,COUNT(DISTINCT(LEFT(a,5)))/COUNT(*) e5,COUNT(DISTINCT(LEFT(a,6)))/COUNT(*) e6,
    -> COUNT(DISTINCT(LEFT(a,7)))/COUNT(*) e7,COUNT(DISTINCT(LEFT(a,8)))/COUNT(*) e8 FROM text_blob;
+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| e      | e1     | e2     | e3     | e4     | e5     | e6     | e7     | e8     |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| 1.0000 | 0.0225 | 0.4663 | 0.8618 | 0.9734 | 0.9958 | 0.9992 | 0.9992 | 1.0000 |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+
1 row in set (0.02 sec)

left按照不同的长度计算比值，找到一个和完整列比例接近的值，比如上面e5前5个长度比例达到0.9958，已经很接近1了，综合考虑比较合适。
最后，开开心心把索引建上:)

ALTER TABLE text_blob ADD KEY (a(5));