MySql index detailed introduction and correct use method

MySql index detailed introduction and correct use method

1. Introduction:

Indexes have a crucial impact on query speed, and understanding indexes is the starting point for database performance tuning.

An index is a data structure used by the storage engine to quickly find records. The reasonable use of database indexes can greatly improve the access performance of the system. The following mainly introduces the index types in the MySQL database and how to create more reasonable and efficient indexing techniques.

Note: This mainly targets the B+Tree index data structure of the InnoDB storage engine.

2. Advantages of Index

1. Greatly reduces the amount of data that the server needs to scan, thereby increasing the speed of data retrieval

2. Help the server avoid sorting and temporary tables

3. Random I/O can be converted into sequential I/O

3. Index creation

3.1. Primary key index

ALTER TABLE 'table_name' ADD PRIMARY KEY 'index_name' ('column');

3.2 Unique Index

ALTER TABLE 'table_name' ADD UNIQUE 'index_name' ('column');

3.3. Common Index

ALTER TABLE 'table_name' ADD INDEX 'index_name' ('column');

3.4 Full-text index

ALTER TABLE 'table_name' ADD FULLTEXT 'index_name' ('column');

3.5. Combined Index

ALTER TABLE 'table_name' ADD INDEX 'index_name' ('column1', 'column2', ...);

4. B+Tree index rules

Create a test user table

DROP TABLE IF EXISTS user_test;
CREATE TABLE user_test(
 id int AUTO_INCREMENT PRIMARY KEY,
 user_name varchar(30) NOT NULL,
 sex bit(1) NOT NULL DEFAULT b'1',
 city ​​varchar(50) NOT NULL,
 age int NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create a composite index: ALTER TABLE user_test ADD INDEX idx_user(user_name , city , age);

4.1. Index-Effective Queries

4.1.1、Full value matching

Full value matching refers to matching all columns in the index. For example, taking the index created above as an example, after the where condition, you can query data with (user_name, city, age) as the condition at the same time.

Note: It has nothing to do with the order of the query conditions after where. This is a place where many students tend to misunderstand

SELECT * FROM user_test WHERE user_name = 'feinik' AND age = 26 AND city = 'Guangzhou';

4.1.2. Match the leftmost prefix

Matching the leftmost prefix means matching the leftmost index column first. For example, the index created above can be used for query conditions: (user_name), (user_name, city), (user_name, city, age)

Note: The order that satisfies the leftmost prefix query condition has nothing to do with the order of the index columns, such as: (city, user_name), (age, city, user_name)

4.1.3. Matching column prefixes

Refers to the beginning of the matching column value, such as: query all users whose username begins with feinik

SELECT * FROM user_test WHERE user_name LIKE 'feinik%';

4.1.4, Matching range value

For example, to query all users whose usernames begin with feinik, the first column of the index is used here.

SELECT * FROM user_test WHERE user_name LIKE 'feinik%';

4.2 Index Restrictions

1. If the where query condition does not contain the leftmost index column in the index column, the index query cannot be used, such as:

SELECT * FROM user_test WHERE city = 'Guangzhou';

or

SELECT * FROM user_test WHERE age= 26;

or

SELECT * FROM user_test WHERE city = 'Guangzhou' AND age = '26';

2. Even if the query condition of where is the leftmost index column, the index cannot be used to query users whose username ends with feinik

SELECT * FROM user_test WHERE user_name like '%feinik';

3. If there is a range query of a column in the where query condition, all columns to the right of it cannot use index optimization query, such as:

SELECT * FROM user_test WHERE user_name = 'feinik' AND city LIKE 'Guangzhou%' AND age = 26;

5. Efficient indexing strategy

5.1. Index columns cannot be part of an expression or used as function parameters. Otherwise, index queries cannot be used.

SELECT * FROM user_test WHERE user_name = concat(user_name, 'fei');

5.2 Prefix Index

Sometimes you need to index a very long character column, which will increase the index storage space and reduce the index efficiency. One strategy is to use a hash index, and another is to use a prefix index. The prefix index selects the first n characters of the character column as the index, which can greatly save index space and thus improve index efficiency.

5.2.1. Selectivity of prefix index

The prefix index should be long enough to ensure high selectivity, but not too long. We can calculate the appropriate prefix index selection length value in the following way:

(1)

SELECT COUNT(DISTINCT index_column)/COUNT(*) FROM table_name; -- index_column represents the column to which the prefix index is to be added

Note: The selectivity ratio of the prefix index is calculated in the above way. The higher the ratio, the more efficient the index is.

(2)

SELECT

COUNT(DISTINCT LEFT(index_column,1))/COUNT(*),

COUNT(DISTINCT LEFT(index_column,2))/COUNT(*),

COUNT(DISTINCT LEFT(index_column,3))/COUNT(*)

...

FROM table_name;

Note: By using the above statements, we can gradually find the selectivity ratio that is closest to the prefix index in (1), and then we can use the corresponding character truncation length to make the prefix index.

5.2.2. Creation of prefix index

ALTER TABLE table_name ADD INDEX index_name (index_column(length));

5.2.3. Notes on using prefix index

Prefix index is an effective way to make the index smaller and faster, but MySQL cannot use prefix index for ORDER BY and GROUP BY or use prefix index for covering scan.

5.3. Choose the appropriate index column order

The order of index columns is very important when creating a composite index. The correct index order depends on the query method using the index. The index order of composite indexes can be determined by the rule of thumb: put the column with the highest selectivity at the front of the index. This rule is consistent with the selectivity method of prefix indexes. However, it does not mean that the order of all composite indexes can be determined by using this rule. The specific index order also needs to be determined according to the specific query scenario.

5.4 Clustered Index and Nonclustered Index

1. Clustered Index

The clustered index determines the physical order of data on the physical disk. A table can have only one clustered index. If a primary key is defined, InnoDB will cluster data by the primary key. If no primary key is defined, InnoDB will select a unique non-empty index instead. If there is no unique non-empty index, InnoDB will implicitly define a primary key as the clustered index.

Clustered indexes can greatly improve access speed, because clustered indexes store indexes and row data in the same B-Tree, so if the index is found, the corresponding row data will be found accordingly. However, when using clustered indexes, care should be taken to avoid random clustered indexes (generally referring to discontinuous primary key values ​​and uneven distribution range). For example, if UUID is used as a clustered index, the performance will be very poor, because the discontinuity of UUID values ​​will lead to a lot of index fragmentation and random I/O, which will eventually lead to a sharp drop in query performance.

2. Non-clustered index

Unlike the clustered index, the non-clustered index does not determine the physical order of the data on the disk, and contains the index but not the row data in the B-Tree. The row data is only pointed to by the pointer corresponding to the index stored in the B-Tree. For example, the index created on (user_name, city, age) above is a non-clustered index.

5.5 Covering Index

If an index (such as a composite index) contains the values ​​of all fields to be queried, it is called a covering index, such as:

SELECT user_name, city, age FROM user_test WHERE user_name = 'feinik' AND age > 25;
Because the fields to be queried (user_name, city, age) are all included in the index columns of the composite index, a covering index query is used. To check whether a covering index is used, you can check whether the value of Extra in the execution plan is Using index, which proves that a covering index is used. A covering index can greatly improve access performance.

5.6 How to use indexes to sort

If you can use the index to sort in the sorting operation, the sorting speed can be greatly improved. To use the index to sort, the following two points need to be met.

1. The order of columns after the ORDER BY clause must be consistent with the order of columns in the composite index, and the sorting direction (ascending/descending) of all sort columns must be consistent

2. The queried field value needs to be included in the index column and satisfy the covering index

Analyze with examples

Create a composite index on the user_test table

ALTER TABLE user_test ADD INDEX index_user(user_name , city , age);

Examples of where index sorting can be used

1. SELECT user_name, city, age FROM user_test ORDER BY user_name;

2. SELECT user_name, city, age FROM user_test ORDER BY user_name, city;

3. SELECT user_name, city, age FROM user_test ORDER BY user_name DESC, city DESC;

4. SELECT user_name, city, age FROM user_test WHERE user_name = 'feinik' ORDER BY city;

Note: Point 4 is a bit special. If the where query condition is the first column of the index column and is a constant condition, the index can also be used.

Cases where index sorting cannot be used

1. Sex is not in the index column

SELECT user_name, city, age FROM user_test ORDER BY user_name, sex;

2. The direction of the sorting column is inconsistent

SELECT user_name, city, age FROM user_test ORDER BY user_name ASC, city DESC;

3. The field column sex to be queried is not included in the index column

SELECT user_name, city, age, sex FROM user_test ORDER BY user_name;

4. The user_name after the where query condition is a range query, so other columns of the index cannot be used

SELECT user_name, city, age FROM user_test WHERE user_name LIKE 'feinik%' ORDER BY city;

5. When querying multiple tables, index sorting can only be used when the sorting fields after ORDER BY are all index columns in the first table (the two rules of index sorting mentioned above must be met). For example, create another user extension table user_test_ext and establish an index for uid.

DROP TABLE IF EXISTS user_test_ext;

CREATE TABLE user_test_ext(

  id int AUTO_INCREMENT PRIMARY KEY,

  uid int NOT NULL,

  u_password VARCHAR(64) NOT NULL

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE user_test_ext ADD INDEX index_user_ext(uid);

Sort by index

SELECT user_name, city, age FROM user_test u LEFT JOIN user_test_ext ue ON u.id = ue.uid ORDER BY u.user_name;

No index sorting

SELECT user_name, city, age FROM user_test u LEFT JOIN user_test_ext ue ON u.id = ue.uid ORDER BY ue.uid;

6. Summary

This article mainly talks about the indexing rules of the B+Tree tree structure, the creation of different indexes, and how to correctly create efficient indexing techniques to maximize query speed. Of course, there are more tips on how to use indexes. You need to accumulate relevant experience to learn more about indexing.

Thank you for reading, I hope it can help you, thank you for your support of this site!