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

This article mainly explains "what are the advantages and disadvantages of MySQL index". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the advantages and disadvantages of MySQL index".

In SQL optimization, index is a very important part, which can bring qualitative leap to query efficiency, but index is not omnipotent, unreasonable index design will even slow down query efficiency.

Index definition

Index is a kind of data structure specially designed to help SQL obtain data efficiently. A common example is that index is similar to a book catalog, which can quickly locate and find specific values, thus greatly accelerating the efficiency of data query. In fact, an index is also a table that holds the primary key and index fields and points to records (similar pointers) of the entity table.

Advantages and disadvantages of index

The index greatly reduces the amount of data that the server needs to scan

Indexes can help servers avoid sorting and temporary tables

Indexes can turn random IO into sequential IO

Indexes are very important for InnoDB (row-level locks are supported for indexes). InnoDB locks only the tuples that need to be accessed, while indexes can reduce the number of tuples accessed by InnoDB. If the query cannot use an index, MySQL performs a full table scan and locks each tuple, regardless of whether it is really needed or not.

Shortcoming

Although the index greatly improves the query speed, it slows down the speed of updating the table. Because when updating a table, MySQL saves not only the data, but also the index file. Therefore, it is generally not recommended to use indexes for fields that update very frequently.

Creating an index takes up disk space.

If a data column contains many duplicates, indexing it is not effective. This property is called index selectivity: the ratio of non-repeating index values to the total number of records in the data table. The higher the selectivity of the index, the higher the query efficiency. For example, if the gender field is indexed and there are 1 million items of data, there are only two possibilities for men and women, and the index selectivity is 1/500000, so the index effect is very poor.

For very small tables, the index does not make much sense, and in most cases a simple full table scan is more efficient.

Therefore, you should index only the most frequently queried and sorted data columns. The total number of indexes in the same data table in MySQL is limited to 16.

Index types are divided according to functional logic

According to the functional logic, the index is mainly divided into general index, unique index, primary key index and full-text index.

General index

The most basic index, it has no restrictions. The only task of a normal index (an index defined by the keyword KEY or INDEX) is to speed up access to data. Therefore, it should only be for those that appear most frequently in the query condition (WHERE column =...) Or the data column in the sort condition (ORDER BY column) creates an index.

There are three ways to create a general index.

# create index CREATE INDEX idx_username ON user_tbl (username) # for string fields, you can specify the length manually, such as user_tbl (username (5)), which means that only the first five characters are used for indexing, which can further speed up the query efficiency. The index length should be less than the field length # modify the table structure ALTER TABLE user_tbl ADD INDEX idx_username (username) # specify directly when creating the table Such as CREATE TABLE user_tbl (ID INT NOT NULL, username VARCHAR (16) NOT NULL, INDEX idx_username (username))

Delete index

DROP INDEX idx_username ON user_tbl

View Index

SHOW INDEX FROM user_tbl; unique index

It is similar to the previous normal index, except that the normal index allows the indexed data column to contain duplicate values. The value of the unique index column must be unique, but null values are allowed. If it is a combined index, the combination of column values must be unique.

Creating a unique index is similar to a normal index:

# create index CREATE UNIQUE INDEX idx_username ON user_tbl (username); # modify table structure ALTER TABLE user_tbl ADD UNIQUE idx_username (username) # specify CREATE TABLE user_tbl (ID INT NOT NULL, username VARCHAR (16) NOT NULL, UNIQUE idx_username (username)) directly when creating a table; primary key index

It is a special unique index and no null values are allowed. A table can have only one primary key, which is usually created at the same time as the table is created.

CREATE TABLE user_tbl (ID INT NOT NULL, username VARCHAR (16) NOT NULL, PRIMARY KEY (ID))

Similarly, foreign key indexes, where a foreign key constraint is defined for a foreign key field, MySQL defines an internal index to help it manage and use foreign key constraints in the most efficient way.

Full-text index

In the previous article MySQL basic syntax, we said that if we use the beginning of LIKE +%, the index will fail, so when we need fuzzy search requirements (such as LIKE'% hello%'), we need to use full-text indexing. It is important to note that Innodb only supports full-text indexing after version 5.6.

Creation and deletion of full-text indexes:

# two created methods CREATE FULLTEXT INDEX idx_name ON tbl_name (field_name); ALTER TABLE tbl_name ADD FULLTEXT INDEX idx_name (field_name); # two deleted methods DROP INDEX idx_name ON tbl_name;ALTER TABLE tbl_name DROP INDEX idx_name

The syntax for full-fuzzy matching using full-text indexing is:

SELECT XXX FROM tbl_name WHERE match (field_name) against ('xxx'); # for example, add a full-text index to the user_name field of user_tbl # query results are equivalent to SELECT user_name, user_id FROM user_tbl WHERE user_name LIKE'% hello%';SELECT user_name, user_id FROM user_tbl WHERE match (user_name) against ('hello')

Using the explain check, you can find that the fulltext index is in effect.

Divided by physical implementation

According to the physical implementation, it can be divided into clustered index and nonclustered index.

Clustered index (clustered index)

The stored content is sorted according to the clustered index, the order of the clustered index is the same as the order of row records, and there can be only one clustered index per table. The leaf node of the clustered index directly stores the content pointed to by the clustered index, so the query only needs to be done once.

The clustered index is automatically generated when the primary key is created, if there is no primary key, based on the first unique index that is not empty, or an implicit clustered index is automatically generated if it does not already exist.

It should be noted that when performing query operations, clustered indexes are more efficient because there is one less lookup, but when modifying operations, they are less efficient than nonclustered indexes because the data content is directly modified. the data pages are reordered in order that the order of the standard data content is the same as that of the clustered index.

Nonclustered index (non-clustered index)

Although the nonclustered index items are stored sequentially, the corresponding contents of the index items are stored randomly, and the system maintains a separate index table to store the index.

The leaf node of the nonclustered index stores the address of the data. when querying the nonclustered index, the system will search twice, first looking for the index, and then looking for the data in the corresponding position of the index. So a nonclustered index is also called a secondary index or a secondary index.

Divided by the number of fields

According to the number of fields, the index can be divided into a single index and a joint index.

Single index

When the index field has only one column, it is a single index, and all of the above indexes are single indexes.

Joint index

An index created by combining multiple fields is called a federated index. As follows:

ALTER TABLE user_tbl ADD INDEX idx_name_city_age (username,city,age); leftmost matching principle

The establishment of such a federated index is actually equivalent to the following three sets of federated indexes:

Usernname,city,ageusernname,cityusernname

Why is there no federated index like city,age? This is because the leftmost matching principle of the MySQL federated index only matches the index in the order of the leftmost priority, that is to say, (xrecery yjournal z) and (zrecery ymeme x) are different indexes, and the federated index may fail even if the field query in the federated index is used.

The federated index will take effect only if the following query conditions are used for (xquotiy _ z):

WHERE x = 1WHERE x = 1 AND y = 1WHERE x = 1 AND y = 1 AND z = 1

For other cases, such as WHERE y = 1, WHERE y = 1 AND z = 1, the federated index will not match and the index will fail. Note that for WHERE x = 1 AND z = 1, the federated index will take effect for x, but not for z.

It can be extended to understand that, in theory, the index in the leftmost matching principle is also sensitive to the order of where subclauses, but because MySQL's query optimizer automatically adjusts the conditional order of where clauses to use the appropriate index, in fact, the order of where clauses does not affect the effect of the index.

It is important to note that if there is a range query during the federated index query, the matching will be stopped, for example, in the following statement, the z field cannot use the index:

WHERE x = 1 AND y > 2 AND z = 3

By the way, you can use the explain command to check whether the index is valid in a query statement. For specific usage, please refer to the official website documentation.

If we set up a single-column index on x, y, z and let the table have three single-column indexes, the index efficiency will be very different. When the federated index is in effect, the efficiency of the single index is much lower than that of the federated index. This is determined by the execution order of the MySQL query optimizer, and when executing a query sql, the selection of the index is roughly as follows:

According to the search criteria, the MySQL optimizer finds all possible indexes.

Calculate the cost of a full table scan

Calculate the cost of executing a query using different indexes

Compare the costs of various implementation schemes and find out which one has the lowest cost

Therefore, although there are multiple single-column indexes, MySQL can only use which system seems to be the most efficient, and the others will fail.

Divided by index structure

Different mysql data engines support indexes with different structures. According to the structure, the commonly used indexes are B+ tree index, Hash index, FULLTEXT index and so on, which will be introduced in the next article MySQL index structure.

Usage summary

Next, let's briefly summarize the scenarios in which indexes are recommended.

Recommended use

Fields in the WHERE, GROUP BY, ORDER BY clause

Multiple single-column indexes only have one optimal index in a multi-conditional query, so it is best to create a federated index in a multi-conditional query.

The leftmost matching principle must be met when federated indexing, and it is best to consider the execution order of sql statements, such as WHERE a = 1 GROUP BY b ORDER BY c, then the federated index should be designed as (mysql bline c), because in the previous article, we introduced the execution order of mysql query statements: WHERE > GROUP BY > ORDER BY.

Create an index on the table join field when there are multiple tables JOIN.

When there are fields in the SELECT that are not in the index, the primary key values that meet the conditions will be queried through the index, and then all the fields in the SELECT will be queried through the primary key back to the table, which affects the query efficiency. So if there is little content in the SELECT, to avoid returning to the table, you can add all the fields in the SELECT to the federated index, which is the concept of a wide index. However, it is important to note that if there are too many index fields, the cost of storing and maintaining the index will also increase.

Not recommended or index invalidation

A table with a small amount of data

Fields with a lot of duplicate data

Fields that are updated frequently

If a function or expression evaluation is used on the index field, the index is invalid

The innodb OR condition does not create an index on all conditions, and the index is invalid

Greater than less than the condition, whether the index is effective depends on the proportion of hits. If the number of hits is large, the index takes effect, the number of hits is very small, and the index is invalid.

Is not equal to the condition! =, the index is invalid

The LIKE value starts with% and the index is invalid

Thank you for reading, the above is the content of "what are the advantages and disadvantages of MySQL index". After the study of this article, I believe you have a deeper understanding of what the advantages and disadvantages of MySQL index are, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.