Schema的优化和索引 - 高性能的索引策略 - 前缀索引和索引的选择性

有的时候你可能需要索引字符数非常多的列。这会让你的索引非常大并且很慢。一个策略就是模拟hash索引。前一部分已经说过了。但是有的时候这种方法也不是很好。将要怎么做。

 

一般可以用索引前一部分的一些字符的方法来节省空间并且可以获得好的性能。这样可以使你的索引使用更少的空间，但是这样会降低选择性。索引的选择性（Index selectivity）是索引值的数量和表中行的数量（#T）的一个比率。范围是1/#T到1.索引的选择性越高越好，因为这样当匹配的时候，MySQL就会过滤掉更多的行。一个唯一索引的选择性1，这是最好的了。

 

一个前缀的索引对于好的性能来说，它的选择性已经足够了。如果你对BLOB和TEXT列进行索引，或者非常长的VARCHAR列，你必须定义前缀索引，因为MySQL不允许索引它们的全部长度。

 

技巧就是选择一个列的前缀，并且这个前缀的长度能有个好的选择性，但是又可能节约更多的空间。索引前缀所带来的索引效果应该尽可能的接近索引全部长度所带来的效果。

 

要知道一个好的前缀的长度，找到最常见的值并且把它们和最常见的前缀进行比较。看下如下例子

 

 

CREATE TABLE sakila.city_demo(city VARCHAR(50) NOT NULL);

INSERT INTO sakila.city_demo(city) SELECT city FROM sakila.city;

-- Repeat the next statement five times:

INSERT INTO sakila.city_demo(city) SELECT city FROM sakila.city_demo;

-- Now randomize the distribution (inefficiently but conveniently):

UPDATE sakila.city_demo

   SET city = (SELECT city FROM  sakila.city ORDER BY RAND( ) LIMIT 1);

 

现在我们有一个示例数据集。结果并不是真是分布的，并且我们使用了RAND()，因此结果是变化的，但是对于此练习并不会有什么影响。首先我们找到出现频率最高的cities.

 

 

mysql> SELECT COUNT(*) AS cnt, city

    -> FROM sakila.city_demo GROUP BY city ORDER BY cnt DESC LIMIT 10;

 

发现每个城市出现的次数范围为45-65.现在我们在查找频率出现最高的前缀。从3个字母的前缀开始。

 

+-----+------+

| cnt | pref |

+-----+------+

| 483 | San  |

| 195 | Cha  |

| 177 | Tan  |

| 167 | Sou  |

| 163 | al-  |

| 163 | Sal  |

| 146 | Shi  |

| 136 | Hal  |

| 130 | Val  |

| 129 | Bat  |

+-----+------+

 

每个前缀都出现了很多次。因此有太多的唯一前缀要多于唯一全长度的city names.接下来的方法就是提高前缀的长度直到这个前缀的选择性接近于全长度的列。通过实验发现前缀长度为7最为合适。

 

 

mysql> SELECT COUNT(*) AS cnt, LEFT(city, 7) AS pref

    -> FROM sakila.city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 10;

+-----+---------+

| cnt | pref    |

+-----+---------+

|  70 | Santiag |

|  68 | San Fel |

|  65 | London  |

|  61 | Valle d |

|  49 | Hiroshi |

|  48 | Teboksa |

|  48 | Pak Kre |

|  48 | Yaound  |

|  47 | Tel Avi |

|  47 | Shimoga |

+-----+---------+

 

另一种计算前缀的方法就是，计算整个列的选择性并且使前缀的选择性 接近于这个值。下面就是计算整列的选择性

 

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

+-------------------------------+

| COUNT(DISTINCT city)/COUNT(*) |

+-------------------------------+

|                        0.0312 |

+-------------------------------+

 

如果前缀的选择性接近与0.031。那么这个前缀长度就是合适的。下面就是找到合适前缀的语句。

 

mysql> SELECT COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel3,

    -> COUNT(DISTINCT LEFT(city, 4))/COUNT(*) AS sel4,

    -> COUNT(DISTINCT LEFT(city, 5))/COUNT(*) AS sel5,

    -> COUNT(DISTINCT LEFT(city, 6))/COUNT(*) AS sel6,

    -> COUNT(DISTINCT LEFT(city, 7))/COUNT(*) AS sel7

    -> FROM sakila.city_demo;

+--------+--------+--------+--------+--------+

| sel3   | sel4   | sel5   | sel6   | sel7   |

+--------+--------+--------+--------+--------+

| 0.0239 | 0.0293 | 0.0305 | 0.0309 | 0.0310 |

+--------+--------+--------+--------+--------+

 

这个查询显示出，7字符的前缀长度最佳。

 

看平均的选择性也不是太好的主意。你也需要考虑最坏情况的选择性的时候。平均的选择性可能使你看到4，5长度的前缀也是不错的。但是如果你的数据是不均匀的。这可能就出现问题了。如果你查看最常见城市的前缀为4的出现数目，你就会清楚的发现不均匀的数据。

 

mysql> SELECT COUNT(*) AS cnt, LEFT(city, 4) AS pref

-> FROM sakila.city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 5;

+-----+------+

| cnt | pref |

+-----+------+

| 205 | San |

| 200 | Sant |

| 135 | Sout |

| 104 | Chan |

| 91 | Toul |

+-----+------+

 

4个字符，出现前缀的频率比全部值的频率要高。因此选择性就要比平均选择性要低很多。如果你有真实的数据，你可能看到这种影响非常大。比如，你在真实的城市名称中创建4个字符前缀的索引，会带来非常差的选择性。

 

对于我们的示例数据，我们已经发现合适的值了。来看看怎样在这个列创建索引：

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

 

前缀的索引可以使索引更小并且更快，但是它也有明显的缺点。MySQL不能在ORDER BY或GROUP BY语句中使用前缀索引，也不能把它们作为覆盖索引进行使用。