怎么理解并掌握mysql索引之前綴索引

本篇內容主要講解“怎么理解并掌握mysql索引之前綴索引”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“怎么理解并掌握mysql索引之前綴索引”吧!

有時候需要很長的索引字符串,這樣會使得索引變的很大而且很慢.通常可以索引開始的部分字符,這樣可以大大節省空間提升索引效率,但這樣也會降低索引的選擇性.索引的選擇性是指,不重復的索引值和數據表的記錄總數的比值,范圍從1#T到1之間.索引的選擇性越高則查詢效率越高,因為選擇性高的索引可以讓mysql在查找時過濾掉更多的行,唯一索引的選擇性是1,這是最好的索引選擇性,性能也是最好的.

    一般情況下某個列前綴的選擇性也是足夠高的,足以滿足查詢性能.對于BLOB,TEXT或者很長的varchar類型的列,必須使用前綴索引,因為mysql不允許索引這些列的完整長度.

    訣竅在于要選擇足夠長的前綴以保證較高的選擇性,同時又不能太長.前綴長的選擇性接近于索引整個列.換句話說,前綴的基數應該接近于完整列的基數.

    為了決定前綴合適長度,需要找到最常見值的列表,然后和最常見的前綴列表進行比較.

如下構建一張表:

mysql> use sakila;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table city_demo(city varchar(50) not null);
Query OK, 0 rows affected (0.15 sec)
mysql> insert into city_demo(city) select city from city;
Query OK, 600 rows affected (0.11 sec)
Records: 600  Duplicates: 0  Warnings: 0
mysql> insert into city_demo(city) select city from city_demo;
Query OK, 600 rows affected (0.09 sec)
Records: 600  Duplicates: 0  Warnings: 0
mysql> update city_demo set city=(select city from city order by rand() limit 1);
Query OK, 1196 rows affected (0.85 sec)
Rows matched: 1200  Changed: 1196  Warnings: 0

有了數據集,數據分布不是真實分布,僅為演示.首先找到最常見的城市列表:

mysql> select count(*) as cnt,city from city_demo group by city order by cnt desc limit 10;
+-----+-------------------------+
| cnt | city                    |
+-----+-------------------------+
|   7 | Oshawa                  |
|   7 | Uijongbu                |
|   7 | Ktahya                  |
|   6 | Haiphong                |
|   6 | Berhampore (Baharampur) |
|   6 | Urawa                   |
|   6 | Mysore                  |
|   6 | Witten                  |
|   6 | Sunnyvale               |
|   6 | Esfahan                 |
+-----+-------------------------+
10 rows in set (0.01 sec)

如上每個值都出現了6-7次,現在找出最頻繁出現城市的前綴,先從前綴字母開始:

mysql> select count(*) as cnt,left(city,3) as pref from city_demo group by pref order by cnt desc limit 10;
+-----+------+
| cnt | pref |
+-----+------+
|  28 | San  |
|  16 | Cha  |
|  14 | Hal  |
|  12 | al-  |
|  11 | Bat  |
|  11 | Shi  |
|  10 | Val  |
|  10 | Ben  |
|  10 | Bra  |
|   9 | Tar  |
+-----+------+
10 rows in set (0.00 sec)

每個前綴出現的都比原來城市次數多,因此唯一前綴比唯一城市要少得多,然后增加前綴長度,直到這個前綴的選擇性接近完整列的選著性,計算合適前綴長度的一個辦法計算完整列的選擇性,并使前綴的選擇性趨于完整列的選擇性.如下計算完整列的選擇性:

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

計算前綴選擇性趨于或接近0.43這個值:

mysql> select count(distinct left(city,3))/count(*) from city_demo;
+---------------------------------------+
| count(distinct left(city,3))/count(*) |
+---------------------------------------+
|                                0.3350 |
+---------------------------------------+
1 row in set (0.01 sec)
mysql> select count(distinct left(city,4))/count(*) from city_demo;
+---------------------------------------+
| count(distinct left(city,4))/count(*) |
+---------------------------------------+
|                                0.4058 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select count(distinct left(city,5))/count(*) from city_demo;
+---------------------------------------+
| count(distinct left(city,5))/count(*) |
+---------------------------------------+
|                                0.4208 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select count(distinct left(city,6))/count(*) from city_demo;
+---------------------------------------+
| count(distinct left(city,6))/count(*) |
+---------------------------------------+
|                                0.4267 |
+---------------------------------------+

查詢顯示當前綴長度達到5的時候,再增加長度,選擇性提升幅度已經不大.

只看平均選擇性是不夠的,也有列外情況,需要考慮最壞情況下的選擇性,平均選擇性會讓你認為前綴長度為3或4的索引已經足夠,但是如果數據分布很不均勻就會有陷阱.

上面示例如果找到合適前綴長度,下面示例如何創建前綴索引:

mysql> alter table city_demo add key(city(5));
Query OK, 0 rows affected (0.34 sec)
Records: 0  Duplicates: 0  Warnings: 0

到此，相信大家對“怎么理解并掌握mysql索引之前綴索引”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！