What is a prefix index in mysql

In mysql, the prefix index is a special index type that indexes the first few characters of the text; this index type can reduce the size of the index to a certain extent and can process some data more efficiently. specific query operations. Under normal circumstances, the index length should be as short as possible, because short indexes can reduce the size of the index. However, if the index length is too short, index failure may occur, resulting in slower query efficiency; therefore, when using prefix indexes When doing this, you need to choose the appropriate index length according to the specific situation.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

1. What is a prefix index

MySQL is a widely used relational database management system, which has high efficiency and efficiency in data storage and retrieval. flexibility. In MySQL, an index is a data structure used to speed up query operations, which can greatly improve the speed and efficiency of queries. The prefix index is a special index type in MySQL, which can handle some specific query operations more efficiently.

The so-called prefix index, to put it bluntly, is to index the first few characters of the text (specifically, how many characters are specified when creating the index), such as the first 10 digits of the product name To build an index, the index created in this way will be smaller and the query efficiency will be faster!

Prefix index refers to indexing only the prefix part of the string, not the entire string. This index type can reduce the size of the index to a certain extent, for example, if you have a table containing ten million strings, each string is 100 characters long, if you use the complete string as the index, then the index The size will be very large and the query efficiency will be very low. But if you only index the first 10 characters of the string, the index size will be greatly reduced, and the query efficiency will be significantly improved.

It is somewhat similar to using the Left function on fields in Oracle to create a function index, except that MySQL's prefix index automatically completes the matching internally when querying, and does not need to use the Left function.

In MySQL, using a prefix index requires specifying the length of the index. Normally, the index length should be as short as possible, because short indexes can reduce the size of the index. However, if the index length is too short, index failure may occur, resulting in slower query efficiency. Therefore, when using prefix indexes, you need to choose the appropriate index length according to the specific situation.

2. Why use prefix index

Some students may ask, why not index the entire field?

Generally speaking, when the amount of data in a certain field is too large and the query is very frequent, using a prefix index can effectively reduce the size of the index file, allowing each index page to save more index value, thus improving the speed of index query.

For example, some customer store names are very long and some are very short. If the index is built based on full coverage, the storage space of the index may be very large. If some tables are indexed A lot of them are created, and even the storage space of the index is much larger than the storage space of the data table. Therefore, for such fields with long text, we can intercept the first few characters to build the index. To a certain extent, It can not only meet the query efficiency requirements of data, but also save index storage space.

But on the other hand, the prefix index also has its shortcomings. The prefix index cannot be used for ORDER BY and GROUP BY in MySQL, nor can it be used for coverage scanning. When the string itself may be relatively long, and the first few characters are exactly the same. At this time, the advantage of the prefix index is no longer obvious, and there is no need to create a prefix index.

So this goes back to a concept, which is about the selectivity of

indexes!

Regarding the selectivity of database table indexes, I will explain it in a separate article. You only need to remember one thing:

The higher the selectivity of the index, the higher the query efficiency , because the selectivity is high The index allows MySQL to filter out more rows when searching, and the data query speed is faster!

When the first few digits of a certain field content are highly differentiated, using a prefix index at this time can achieve a very high cost performance in terms of query performance and space storage.

Then the question is, how to create a prefix index?

3. How to create a prefix index

The way to create a prefix index is very simple. You can create it in the following way!

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

prefix_lengthThis parameter means the prefix length. It is usually confirmed by the following method. The steps are as follows:

The first step, First calculate the discrimination of all columns of a field.

SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

The second step is to calculate the prefix length that is most similar to the discrimination of the entire column

SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

最后，不断地调整prefix_length的值，直到和全列计算出区分度相近，最相近的那个值，就是我们想要的值。

下面以某个测试表为例，数据体量在 100 万以上，表结构如下！

CREATE TABLE `tb_test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

测试一下正常的带name条件查询，效率如下：

select * from tb_test where name like '1805.59281427%'

我们以name字段为例，创建前缀索引，找出最合适的prefix_length值。

首先，我们大致计算一下name字段全列的区分度。

可以看到，结果为 0.9945，也就是说全局不相同的数据率在99.45%这个比例。

下面我们一起来看看，不同的prefix_length值下，对应的数据不重复比例。

当prefix_length为5，区分度为0.2237

当prefix_length为10，区分度为0.9944

当prefix_length为11，区分度为0.9945

通过对比，我们发现当prefix_length为11，最接近全局区分度，因此可以为name创建一个长度为11的前缀索引，创建索引语句如下：

alter table tb_test add key(name(11));

下面，我们再试试上面那个语句查询！

创建前缀索引之后，查询效率倍增！

四、使用前缀索引需要注意的事项

是不是所有的字段，都适合用前缀索引呢？

答案显然不是，在上文我们也说到了，当某个索引的字符串列很大时，创建的索引也就变得很大，为了减小索引体积，提高索引的扫描速度，使用索引的前部分字符串作为索引值，这样索引占用的空间就会大大减少，并且索引的选择性也不会降低很多，这时前缀索引显现的作用就会非常明显，前缀索引本质是索引查询性能和存储空间的一种平衡。

对于 BLOB 和 TEXT 列进行索引，或者非常长的 VARCHAR 列，就必须使用前缀索引，因为 MySQL 不允许索引它们的全部长度。

但是如果某个字段内容，比如前缀部分相似度很高，此时的前缀索引显现效果就不会很明显，采用覆盖索引效果会更好！

【相关推荐：mysql视频教程】

The above is the detailed content of What is a prefix index in mysql. For more information, please follow other related articles on the PHP Chinese website!