MySql index details and correct use methods, mysql index details

MySql index details and correct use methods, mysql index details

MySql index details and correct usage

1. Preface:

Indexes have a crucial impact on the query speed. Understanding indexes is also the starting point for optimizing database performance.

Index is a data structure used by the storage engine to quickly search for records. By using database indexes properly, the system access performance can be greatly improved. Next we will introduce the index types in the MySql database, and how to create more reasonable and efficient indexing skills.

Note: The data structure of the B + Tree index of the InnoDB Storage engine is mainly used here.

2. Advantages of Indexes

1. This greatly reduces the amount of data that the server needs to scan and improves the data retrieval speed.

2. Helps the server avoid sorting and temporary tables

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

3. Create an index

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 normal 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 Composite Index

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

4. B + Tree index rules

Create a user table for testing

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 effective index Query

4.1.1. Full value matching

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

Note: It has nothing to do with the order of the query conditions after the where clause. This is a misunderstanding of many people.

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

4.1.2. matching the leftmost prefix

The leftmost prefix indicates that the leftmost index column is matched 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 meets the leftmost prefix query conditions is irrelevant to the order of index columns, such as: (city, user_name), (age, city, user_name)

4.1.3 match the column prefix

Matches the beginning of a column value, for example, querying all users whose usernames start 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 start with feinik, the first column of the index is used.

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, you cannot use the index query, for example:

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 where query condition is the leftmost index column, you cannot use the index query user name to end with feinik.

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

3. If the where query condition contains a range query for a column, all columns on the right of the Column cannot use the index to optimize the query, for example:

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

5. Efficient index policies

5.1 index Columns cannot be part of expressions or be used as function parameters. Otherwise, index query cannot be used.

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

5.2. prefix Index

Sometimes a long character column needs to be indexed, which will increase the storage space of the index and reduce the efficiency of the index. One policy is to use the hash index and the other is to use the prefix index, prefix index selects the first n characters of a character column as the index, which greatly saves the index space and improves the index efficiency.

5.2.1 prefix index Selectivity

Prefix indexes must have a long enough prefix to ensure high selectivity without being too long. We can use the following method to calculate the Selected length value of a proper prefix index:

(1)

Select count (DISTINCT index_column)/COUNT (*) FROM table_name; -- index_column indicates the column whose prefix index is to be added.

Note: The above method is used to calculate the prefix index's selective ratio. The higher the ratio, the more efficient the index is.

(2)

SELECTCOUNT(DISTINCT LEFT(index_column,1))/COUNT(*),COUNT(DISTINCT LEFT(index_column,2))/COUNT(*),COUNT(DISTINCT LEFT(index_column,3))/COUNT(*)...FROM table_name;

Note: You can use the preceding statements to find the preferred prefix index ratio that is closest to (1). Then, you can use the corresponding string truncation length to create a prefix index.

5.2.2 create a prefix Index

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

5.2.3 note on prefix Index

Prefix index is an effective method that can make the index smaller and faster. However, MySql cannot use the prefix index for order by and group by, and use the prefix index for overwriting scanning.

5.3 select an appropriate index column Sequence

In the creation of a composite index, the order of index columns is very important. The correct index order depends on the query method using this index, we can use empirical rules to complete the index order of a composite index: place the most selective columns in the forefront of the index. This rule is consistent with the prefix index selection method, however, it does not mean that the order of all composite indexes can be determined by using this rule. You also need to determine the specific index order based on the specific query scenario.

5.4 clustered index and non-clustered Index

1. Clustered Index

Clustered index determines the physical sorting of data on the physical disk. A table can only have one clustered index. If a primary key is defined, InnoDB aggregates data through the primary key. If no primary key is defined, innoDB selects a unique non-null index instead. If there is no unique non-null index, InnoDB implicitly defines a primary key as a clustered index.

Clustered indexes can greatly increase the access speed, because clustered indexes store indexes and row data in the same B-Tree, therefore, when an index is found, the corresponding row data is also found. However, when using clustered indexes, you must avoid random clustered indexes (generally, the primary key value is not continuous, and the distribution range is uneven). If UUID is used as the clustered index, the performance will be poor, because the UUID value is not continuous, resulting in a lot of index fragmentation and random I/O, eventually, the query performance decreases sharply.

2. Non-clustered Index

Different from clustered indexes, non-clustered indexes do not determine the physical sorting of data on disks, and B-Tree contains indexes but does not contain row data, the row data is directed to the row data by the pointer corresponding to the index stored in the B-Tree. For example, the index created above (user_name, city, age) is a non-clustered index.

5.5 covering Indexes

If an index (for example, a composite index) contains the values of all fields to be queried, it is called overwrite index, for example:

SELECT user_name, city, age FROM user_test WHERE user_name = 'feiniik 'AND age> 25;
Because the fields (user_name, city, age) to be queried are included in the index column of the composite index, overwrite the index query, you can check whether the overwriting index is used. If the value of Extra in the execution plan is Using index, the overwriting index is used, which greatly improves the access performance.

5.6. How to sort by index

If indexes can be used for sorting, the sorting speed can be greatly improved. To sort indexes, the following two points must be met.

1. The column ORDER after the order by clause must be the same as that of the composite index column, and the sorting direction (forward/reverse) of all sort columns must be the same

2. The queried field value must be included in the index column and must cover the index.

Detailed analysis 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

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: The 4th point is special. If the where query condition is the first column of the index column and is a constant condition, you can also use the index.

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 sex field column 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. During Multi-table join queries, you can use index sorting only when the sorting fields after order by are the index columns in the first table (the two rules for sorting the above indexes must be met. For example, create another user extension table user_test_ext and create the uid index.

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 Indexing

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 focuses on the index rules of the B + Tree structure, the creation of different indexes, and how to correctly create efficient indexes to improve the query speed as much as possible, of course, this is not the only way to use indexes. More indexing skills require constant experience.

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!