Shulou(Shulou.com)05/31 Report--

This article mainly explains how to understand and master the prefix index of mysql index. interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to understand and master the prefix index of mysql index.

Sometimes a long index string is required, which makes the index large and slow. It is usually possible to index some characters at the beginning, which can greatly save space and improve the efficiency of the index, but it will also reduce the selectivity of the index. The selectivity of the index refers to the ratio of the non-repeating index value to the total number of records in the data table, ranging from 1 to 1. The higher the selectivity of the index, the higher the query efficiency, because the highly selective index allows mysql to filter out more rows when searching, and the selectivity of the unique index is 1, which is the best index selectivity and the best performance.

In general, the selectivity of a column prefix is high enough to satisfy the query performance. For BLOB,TEXT or very long varchar type columns, you must use a prefix index because mysql does not allow you to index the full length of these columns.

The trick is to choose a long enough prefix to ensure high selectivity, but not too long. The selectivity of the prefix length is close to indexing the entire column. In other words, the cardinality of the prefix should be close to the cardinality of the complete column.

To determine the appropriate prefix length, you need to find the list of the most common values and compare it with the most common prefix list.

Build a table as follows:

Mysql > use sakila;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > 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: 0mysql > insert into city_demo (city) select city from city_demo Query OK, 600rows affected (sec) Records: 600Duplicates: 0 Warnings: 0mysql > update city_demo set city= (select city from city order by rand () limit 1); Query OK, 1196 rows affected (0.85sec) Rows matched: 1200 Changed: 1196 Warnings: 0

With the dataset, the data distribution is not a real distribution, only for demonstration. First find the list of the most common cities:

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) |

As each of the above values appears 6-7 times, now find out the prefix of the city that occurs most frequently, starting with the prefix letter:

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)

Each prefix appears more times than the original city, so the unique prefix is much less than the unique city, and then increase the prefix length until the selectivity of the prefix is close to the selectivity of the complete column. A method for calculating the appropriate prefix length calculates the selectivity of the complete column and makes the selectivity of the prefix tend to the selectivity of the complete column. The selectivity of the complete column is calculated as follows:

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

The calculated prefix selectivity tends to or close to 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 (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 | +-+

Query shows that when the current suffix length reaches 5, and then increase the length, the selective improvement has been small.

It is not enough to look at the average selectivity, there are also exceptional cases, you need to consider the worst-case selectivity, the average selectivity will make you think that an index with a prefix length of 3 or 4 is enough, but there will be traps if the data is unevenly distributed.

If the above example finds the appropriate prefix length, the following example shows how to create a prefix index:

Mysql > alter table city_demo add key (city (5)); Query OK, 0 rows affected (0.34 sec) Records: 0 Duplicates: 0 Warnings: 0 so far, I believe you have a deeper understanding of "how to understand and master the mysql index prefix index". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.