Efficiency Comparison between common indexes and unique indexes in mysql _ MySQL

Comparison between the efficiency of normal indexes and unique indexes in mysql bitsCN.com

Today, I deployed an environment in my virtual machine. the test screenshot is as follows:

These operations are performed for the first time. after a few clicks, the average value is obtained and the efficiency is roughly the same. if a unique index and a common index are created simultaneously on a column, mysql automatically selects a unique index.

Google:

The structure used by the unique index and the common index is B-tree, and the execution time complexity is O (log n ).

Additional concepts:

1. common index

The only task of a common INDEX (INDEX defined by the KEY or INDEX keyword) is to speed up data access. Therefore, you should create an index only for the data columns that most frequently appear in the query condition (WHEREcolumn =) or sort condition (ORDERBYcolumn. If possible, you should select the most neat and compact data column (such as an integer data column) to create an index.

2. unique index

Normal indexes allow indexed data columns to contain duplicate values. For example, because a person may have the same name, the same name may appear twice or more times in the same "employee profile" data table.

If you can determine that a data column will only contain different values, you should use the keyword UNIQUE to define it as a UNIQUE index when creating an index for this data column. The advantage of doing so: first, it simplifies MySQL's management of this index, and this index becomes more efficient. second, MySQL inserts a data table with a new record, automatically checks whether the value of this field of the new record has already exists in this field of a record; if yes, MySQL rejects the insert of that new record. That is to say, the unique index can ensure the uniqueness of data records. In fact, in many cases, the purpose of creating a unique index is not to speed up access, but to avoid data duplication.

Note:

Finally, I found out why my colleague said that the execution efficiency was different. he created a prefix index on a common index, which only took the first 16 bytes and the full bytes used for the unique index :)

BitsCN.com