Shulou(Shulou.com)06/03 Report--

This article to share with you is about how to understand MySQL database index, Xiaobian think quite practical, so share to everyone to learn, I hope you can read this article after some harvest, not much to say, follow Xiaobian to see it.

1. Overview

Index is a kind of data structure used by storage engine to quickly find records. Through reasonable use of database index, the access performance of the system can be greatly improved.

MySql database index types, and how to create a more reasonable and efficient index skills.

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

2. Advantages of indexing

Greatly reduces the amount of data that the server needs to scan, thus improving the retrieval speed of data

Help servers avoid sorting and temporary tables

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

3. Creation of 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 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, combined index

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

4. Indexing rules of B+Tree

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;

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

4.1 index valid queries

4.1.1, all-value matching

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

conditional data.

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 Match leftmost prefix

Matching the leftmost prefix refers to preferentially matching the leftmost index column. For example, the index 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 condition is satisfied has nothing to do with the order of index columns, such as: (city, user_name),(age, city, user_name)

4.1.3 Match column prefix

The first part of the matching column value, e.g. query all users whose username starts with feinik

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

4.1.4 Match Range Value

For example: query all users whose username starts with feinik, here the first column of the index is used

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

4.2 Limitations of the index

1. If the 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, you cannot use the index to query users whose username ends with feinik.

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

3. If there is a range query for a column in the where query criteria, all columns on the right cannot be optimized using indexes, 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 arguments to a function, 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 long character columns, which increases the storage space of the index and reduces the efficiency of the index. One strategy is to use hash indexes, and another is to make

Prefix index is used to select the first n characters of the character column as the index, which can greatly save index space and improve index efficiency.

5.2.1 Selectivity of prefix index

Prefix index To choose a prefix long enough to ensure high selectivity, but not too long, we can calculate the appropriate prefix index selection length value by the following way:

（1）

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

Note: The selectivity ratio of prefix index is calculated in the above way. 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: Through the above sentences, gradually find the selectivity ratio closest to the prefix index in (1), then you can use the corresponding character truncation length to make the prefix index.

5.2.2 Prefix index creation

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

5.2.3 Attention points for using prefix index

Prefix indexing is an efficient way to make indexes smaller and faster, but MySql cannot use prefix indexing for ORDER BY and GROUP BY and prefix indexing for overwriting.

Scan.

5.3 Select the appropriate index column order

The order of index columns is very important in the creation of a composite index. The correct index order depends on the query method using the index. The index order of a composite index can be determined by experience.

The rule to help us do this is to put the most selective column at the top of the index. This rule is consistent with the selective method of prefix index, but it does not mean the order of all combined indexes.

All 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. Cluster index

A clustered index determines the physical ordering of data on physical disks. A table can only have one clustered index. If a primary key is defined, InnoDB will aggregate data by primary key, such as

If no primary key is defined, InnoDB selects a unique non-null index instead. If no unique non-null index exists, InnoDB implicitly defines a primary key as the aggregate index.

cited.

Clustered indexes can greatly improve access speed, because clustered indexes store index and row data in the same B-Tree, so index is found accordingly.

Corresponding row data, but when using clustered indexes, pay attention to avoid random clustered indexes (generally refers to discontinuous primary key values and uneven distribution range), such as using UUID as

Unaggregated indexes perform poorly because discontinuities in UUID values cause a lot of index fragmentation and random I/O, resulting in a sharp drop in query performance.

2. Non-clustered indexes

Unlike clustered indexes, nonclustered indexes do not determine the physical ordering of data on disk, and in B-Trees, indexes are included but no row data is included, which is simply stored in the

The pointer corresponding to the index in the B-Tree points to the row data. For example, the index established above on (user_name, city, age) is a non-clustered index.

5.5 Overlay index

If an index (e.g., composite index) contains the values of all the fields to be queried, it is called an overlay 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 contained in the index columns of the composite index, an overlay index query is used. To see if an overlay index is used, you can

Using the value of Using index in Extra in the execution plan proves that an overlay index is used, which can greatly improve access performance.

5.6 How to use index to sort

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

1. The column order after ORDER BY clause should be consistent with that of composite index, and the sorting direction (positive order/reverse order) of all sorted columns should be consistent.

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

Through examples to specific analysis

Create a composite index on the user_test table

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

Cases that can be sorted using index

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 special. If the where query condition is the first column of the index column and it is a constant condition, then the index can also be used.

Cases that cannot be sorted using index

Sex is not in the index column.

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

2. The direction of sorting columns 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 multi-table join query, only when the sorting fields after ORDER BY are index columns in the first table (the two rules of index sorting above need to be satisfied), you can use the query.

Sort by citation. For example, create a user extension table user_test_ext, and establish 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);

index sorting

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

Mainly talked about the B+Tree tree structure index rules, the creation of different indexes, and how to correctly create efficient index techniques to improve the query speed as much as possible, of course

These are not the only skills on the use of the index, and more skills on the index require constant accumulation of relevant experience.

The above is how to understand MySQL database index, Xiaobian believes that some knowledge points may be our daily work will see or use. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.