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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30317998/viewspace-2654308/，如需轉載，請註明出處，否則將追究法律責任。