欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL前缀索引和索引选择性

程序员文章站 2022-06-21 23:38:41
mysql前缀索引和索引选择性,有时候需要索引很长的字符列,这会让索引变得大且慢。通常可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率。但这样也会降低索引的选择性...

mysql前缀索引和索引选择性,有时候需要索引很长的字符列,这会让索引变得大且慢。通常可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指不重复的索引值(也称为基数,cardinality)和数据表的记录总数的比值,范围从1/#t到1之间。索引的选择性越高则查询效率越高,因为选择性高的索引可以让mysql在查找时过滤掉更多的行。唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。

一般情况下某个前缀的选择性也是足够高的,足以满足查询性能。对于blob,text,或者很长的varchar类型的列,必须使用前缀索引,因为mysql不允许索引这些列的完整长度。

诀窍在于要选择足够长的前缀以保证较高的选择性,同时又不能太长(以便节约空间)。前缀应该足够长,以使得前缀索引的选择性接近于索引的整个列。换句话说,前缀的”基数“应该接近于完整的列的”基数“。

为了决定前缀的合适长度,需要找到最常见的值的列表,然后和最常见的前缀列表进行比较。下面的示例是mysql官方提供的示例

下载地址如下:

https://downloads.mysql.com/docs/sakila-db.zip

在示例数据库sakila中并没有合适的例子,所以从表city中生成一个示例表,这样就有足够数据进行演示:

mysql> select database();                                                           
+------------+
| database() |
+------------+
| sakila     |
+------------+
1 row in set (0.00 sec)

mysql> create table city_demo (city varchar(50) not null);                          
query ok, 0 rows affected (0.02 sec)

mysql> insert into city_demo (city) select city from city;                          
query ok, 600 rows affected (0.08 sec)
records: 600  duplicates: 0  warnings: 0

mysql> insert into city_demo (city) select city from city_demo;
query ok, 600 rows affected (0.07 sec)
records: 600  duplicates: 0  warnings: 0

mysql> update city_demo set city = ( select city from city order by rand() limit 1);
query ok, 1199 rows affected (0.95 sec)
rows matched: 1200  changed: 1199  warnings: 0

mysql> 

因为这里使用了rand()函数,所以你的数据会与我的不同,当然那不影响聪明的你。

首先找到最常见的城市列表:

mysql> select count(*) as cnt, city from city_demo group by city order by cnt desc limit 10;               
+-----+--------------+
| cnt | city         |
+-----+--------------+
|   8 | garden grove |
|   7 | escobar      |
|   7 | emeishan     |
|   6 | amroha       |
|   6 | tegal        |
|   6 | lancaster    |
|   6 | jelets       |
|   6 | ambattur     |
|   6 | yingkou      |
|   6 | monclova     |
+-----+--------------+
10 rows in set (0.01 sec)

mysql> 

注意到查询结果,上面每个值都出现了6-8次。现在查找到频繁出现的城市前缀。先从3个前缀字母开始,然后4个,5个,6个:

mysql> select count(*) as cnt,left(city,3) as pref from city_demo group by pref order by cnt desc limit 10;
+-----+------+
| cnt | pref |
+-----+------+
|  25 | san  |
|  15 | cha  |
|  12 | bat  |
|  12 | tan  |
|  11 | al-  |
|  11 | gar  |
|  11 | yin  |
|  10 | kan  |
|  10 | sou  |
|  10 | bra  |
+-----+------+
10 rows in set (0.00 sec)

mysql> select count(*) as cnt,left(city,4) as pref from city_demo group by pref order by cnt desc limit 10; 
+-----+------+
| cnt | pref |
+-----+------+
|  12 | san  |
|  10 | sout |
|   8 | chan |
|   8 | sant |
|   8 | gard |
|   7 | emei |
|   7 | esco |
|   6 | ying |
|   6 | amro |
|   6 | lanc |
+-----+------+
10 rows in set (0.01 sec)

mysql> select count(*) as cnt,left(city,5) as pref from city_demo group by pref order by cnt desc limit 10; 
+-----+-------+
| cnt | pref  |
+-----+-------+
|  10 | south |
|   8 | garde |
|   7 | emeis |
|   7 | escob |
|   6 | amroh |
|   6 | yingk |
|   6 | moncl |
|   6 | lanca |
|   6 | jelet |
|   6 | tegal |
+-----+-------+
10 rows in set (0.01 sec)
mysql> select count(*) as cnt,left(city,6) as pref from city_demo group by pref order by cnt desc limit 10; 
+-----+--------+
| cnt | pref   |
+-----+--------+
|   8 | garden |
|   7 | emeish |
|   7 | escoba |
|   6 | amroha |
|   6 | yingko |
|   6 | lancas |
|   6 | jelets |
|   6 | tegal  |
|   6 | monclo |
|   6 | ambatt |
+-----+--------+
10 rows in set (0.00 sec)

mysql> 

通过上面改变不同前缀长度发现,当前缀长度为6时,这个前缀的选择性就接近完整咧的选择性了。甚至是一样的。

当然还有另外更方便的方法,那就是计算完整列的选择性,并使其前缀的选择性接近于完整列的选择性。下面显示如何计算完整列的选择性:

mysql> select count(distinct city) / count(*) from city_demo;
+---------------------------------+
| count(distinct city) / count(*) |
+---------------------------------+
|                          0.4283 |
+---------------------------------+
1 row in set (0.05 sec)

mysql> 

可以在一个查询中针对不同前缀长度的选择性进行计算,这对于大表非常有用,下面给出如何在同一个查询中计算不同前缀长度的选择性:

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 
    -> from city_demo;
+--------+--------+--------+--------+
| sel3   | sel4   | sel5   | sel6   |
+--------+--------+--------+--------+
| 0.3367 | 0.4075 | 0.4208 | 0.4267 |
+--------+--------+--------+--------+
1 row in set (0.01 sec)

mysql> 

可以看见当索引前缀为6时的基数是0.4267,已经接近完整列选择性0.4283。

在上面的示例中,已经找到了合适的前缀长度,下面创建前缀索引:

mysql> alter table city_demo add key (city(6));
query ok, 0 rows affected (0.19 sec)
records: 0  duplicates: 0  warnings: 0

mysql> 
mysql> explain select * from city_demo where city like 'jinch%';
+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table     | type  | possible_keys | key  | key_len | ref  | rows | extra       |
+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
|  1 | simple      | city_demo | range | city          | city | 20      | null |    2 | using where |
+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> 

可以看见正确使用刚创建的索引。

前缀索引是一种能使索引更小,更快的有效办法,但另一方面也有其缺点:

mysql无法使用其前缀索引做order by和group by,也无法使用前缀索引做覆盖扫描。