MySQL不允许索引BLOB, TEXT又长VARCHAR列,因为它们包含的数据可能很大,而隐式DB索引将很大,这意味着没有从索引中获益。
MySQL要求您首先定义要索引的N个字符,诀窍是选择一个长度足够长以提供良好选择性的数字N,但它足够短以节省空间。前缀应该足够长,使索引几乎与索引整个列时一样有用。
在我们进一步讨论之前,让我们先定义一些重要的术语。指数选择性是.的比率完全不同的索引值和行总数。..下面是测试表的一个示例:
+-----+-----------+
| id | value |
+-----+-----------+
| 1 | abc |
| 2 | abd |
| 3 | adg |
+-----+-----------+
如果我们只索引第一个字符(N=1),那么索引表将如下表所示:
+---------------+-----------+
| indexedValue | rows |
+---------------+-----------+
| a | 1,2,3 |
+---------------+-----------+
在这种情况下,索引选择性等于is=1/3=0.33。
现在让我们看看如果我们将索引字符的数量增加到两个(N=2)会发生什么。
+---------------+-----------+
| indexedValue | rows |
+---------------+-----------+
| ab | 1,2 |
| ad | 3 |
+---------------+-----------+
在这个场景中,is=2/3=0.66,这意味着我们增加了索引的选择性,但是我们也增加了索引的大小。技巧是找出最小数N,它将导致极大。指数选择性.
有两种方法可以对数据库表进行计算。我会在这个数据库转储.
假设我们要添加列姓表中员工,我们想要定义最小的数字。N这将产生最好的指数选择性。
首先,让我们确定最常见的姓氏:
select count(*) as cnt, last_name
from employees
group by employees.last_name
order by cnt
+-----+-------------+
| cnt | last_name |
+-----+-------------+
| 226 | Baba |
| 223 | Coorg |
| 223 | Gelosh |
| 222 | Farris |
| 222 | Sudbeck |
| 221 | Adachi |
| 220 | Osgood |
| 218 | Neiman |
| 218 | Mandell |
| 218 | Masada |
| 217 | Boudaillier |
| 217 | Wendorf |
| 216 | Pettis |
| 216 | Solares |
| 216 | Mahnke |
+-----+-------------+
15 rows in set (0.64 sec)
如你所见,姓巴巴是最常见的。现在我们会发现最常见的姓前缀,以五个字母开头。
+-----+--------+
| cnt | prefix |
+-----+--------+
| 794 | Schaa |
| 758 | Mande |
| 711 | Schwa |
| 562 | Angel |
| 561 | Gecse |
| 555 | Delgr |
| 550 | Berna |
| 547 | Peter |
| 543 | Cappe |
| 539 | Stran |
| 534 | Canna |
| 485 | Georg |
| 417 | Neima |
| 398 | Petti |
| 398 | Duclo |
+-----+--------+
15 rows in set (0.55 sec)
每个前缀都有更多的出现,这意味着我们必须增加N,直到其值与前面的示例几乎相同为止。
以下是N=9的结果
select count(*) as cnt, left(last_name,9) as prefix
from employees
group by prefix
order by cnt desc
limit 0,15;
+-----+-----------+
| cnt | prefix |
+-----+-----------+
| 336 | Schwartzb |
| 226 | Baba |
| 223 | Coorg |
| 223 | Gelosh |
| 222 | Sudbeck |
| 222 | Farris |
| 221 | Adachi |
| 220 | Osgood |
| 218 | Mandell |
| 218 | Neiman |
| 218 | Masada |
| 217 | Wendorf |
| 217 | Boudailli |
| 216 | Cummings |
| 216 | Pettis |
+-----+-----------+
以下是N=10的结果。
+-----+------------+
| cnt | prefix |
+-----+------------+
| 226 | Baba |
| 223 | Coorg |
| 223 | Gelosh |
| 222 | Sudbeck |
| 222 | Farris |
| 221 | Adachi |
| 220 | Osgood |
| 218 | Mandell |
| 218 | Neiman |
| 218 | Masada |
| 217 | Wendorf |
| 217 | Boudaillie |
| 216 | Cummings |
| 216 | Pettis |
| 216 | Solares |
+-----+------------+
15 rows in set (0.56 sec)
这是非常好的结果。这意味着我们可以在列上索引。last_name只索引前10个字符。在表定义列中last_name定义为VARCHAR(16),这意味着我们已经在每个条目中保存了6个字节(如果姓氏中有UTF 8字符,则保存更多字节)。在这个表中有1637个不同的值乘以6个字节大约是9KB,想象一下如果我们的表包含数百万行,这个数字将如何增长。
您可以阅读其他计算N在我的岗位上MySQL中的前缀索引.