Detailed introduction and correct use of MySql index

Detailed introduction and correct use of MySql index

1. Preface:

Indexes have a critical impact on query speed, and understanding them is also a starting point for database performance tuning.

Index is a kind of data structure used by storage engine to find records quickly. By using database index reasonably, the access performance of the system can be greatly improved. Next, it mainly introduces the index types in MySql database and how to create more reasonable and efficient index skills.

Note: This is mainly for the B+Tree index data structure of the InnoDB storage engine

2. Advantages of indexing

1. It greatly reduces the amount of data needed to be scanned by the server, thus improving the retrieval speed of data

2. Help the server avoid sorting and temporary tables

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

3. Index Creation

3.1. Primary Key Index

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

3.2, only 1 index

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

3.3. General Index

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

3.4. Full-text indexing

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

3.5. Composite Index

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

4. Index rules of B+Tree

Create a user table for 1 test

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 Valid 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, the data conditional on user_name, city, age can be queried at the same time after where condition.

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

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

4.1. 2, matching the leftmost prefix

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

Note: The order in which the leftmost prefix query criteria are satisfied is independent of 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 querying all users whose user name begins with feinik

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

4.1. 4, matching range value

For example, query all users whose username begins with feinik, where the first column of the index is used

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

4.2. Restrictions on Indexes

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

SELECT * FROM user_test WHERE city = ' Guangzhou ';

Or

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

0

Or

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

2. Even if the query condition of where is the leftmost index column, you cannot use the index to query users whose user names end in feinik

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

3. If there is a range query for a column in the where query condition, all the columns on the right side of the query cannot be optimized by index, such as:

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

3

5. Efficient indexing strategy

5.1. Indexed columns cannot be part 1 of an expression, nor can they be used as parameters of a function, otherwise index queries cannot be used.

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

4

5.2, Prefix Index

Sometimes it is necessary to index very long character columns, which will increase the storage space of the index and reduce the efficiency of the index. One strategy is to use hash index, and the other is to use prefix index, which selects the first n characters of the character column as the index, which can greatly save the index space and improve the index efficiency.

5.2. 1. Prefix index selectivity

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

(1)

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

5

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

(2)

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

6

Note: Step by step find the selectivity ratio closest to the prefix index in (1), then you can use the corresponding character truncation length to do the prefix index

5.2. 2, prefix index creation

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

5.2. 3. Points to note when using prefix indexes

Prefix index is an effective way to make the index smaller and faster, but MySql can't do ORDER BY and GROUP BY with prefix index and overwrite scan with prefix index.

5.3. Choose the appropriate order of index columns

The order of index columns is very important in the creation of composite indexes, The correct index order depends on the way the index is queried, The index order of composite indexes can be helped by the rule of thumb: Putting the column with the highest selectivity at the top of the index is similar to the selectivity method 1 of prefix index, but it does not mean that the order of all combined indexes can be determined by using this rule, and the specific index order needs to be determined according to the specific query scenario.

5.4 Clustered and Nonclustered Indexes

1. Clustered index

Clustered index determines the physical sorting of data on physical disk. A table can only have one clustered index. If a primary key is defined, InnoDB will aggregate data through the primary key. If no primary key is defined, InnoDB will choose a 1-only non-null index instead. If there is no 1-only non-null index, InnoDB will implicitly define a primary key as a clustered index.

Clustered index can greatly improve the access speed, Because clustered indexes store index and row data in the same B-Tree, Therefore, if you find the index, you will find the corresponding row data accordingly. However, when using clustered index, we should pay attention to avoid random clustered index (1 generally refers to discontinuous primary key values and uneven distribution range). If UUID is used as clustered index, its performance will be poor, because the discontinuity of UUID values will lead to a lot of index fragments and random I/O, which will eventually lead to a sharp decline in query performance.

2. Nonclustered index

Unlike clustered indexes, non-clustered indexes do not determine the physical ordering of data on disk, and in B-Tree, indexes are contained but not row data, which is only pointed to row data by pointers corresponding to indexes stored in B-Tree, such as the indexes established above (user_name, city, age) are non-clustered indexes.

5.5. Override the index

If an index (such as a composite index) contains the values of all the fields to be queried, it is called an override 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 contained in the index column of the composite index, an override index query is used to see if the override index is used. The value in Extra in the execution plan is Using index, which proves that the override index is used, and the override index can greatly improve the access performance.

5.6. How to Sort Using Indexes

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

1. The column order after the ORDER BY clause should be 1 corresponding to the column order of the combined index, and the sorting direction (positive order/reverse order) of all row sequences should be 1 corresponding

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

Analyze concretely through examples

Create a composite index on the user_test table

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

Cases where index sorting can be used

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

9

Note: Point 4 is 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 index column

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

2. The direction of the arrangement sequence is not 1

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. user_name after 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. For multi-table join queries, index sorting can only be used if the sorting fields after ORDER BY are all index columns in the first table (the above two rules of index sorting need to be met). For example, create another user's extended table user_test_ext, and establish the index of 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;

Sort without index

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 paper mainly talks about the index rules of B+Tree tree structure, the creation of different indexes, and how to correctly create efficient index skills to improve the query speed as much as possible. Of course, the use skills of indexes are not only these, but also more skills about indexes need to accumulate relevant experience constantly.

Thank you for reading, hope to help everyone, thank you for your support to this site!