Mysql database index and mysql Index

Mysql database index and mysql Index

Today, let's talk about some of the indexes of the Mysql database. I think everyone knows what indexes can do? It is inevitable that data tables are quickly searched, especially those databases with millions of rows without indexing. Do you want to test your patience? Mysql has a variety of storage engines, which are bound to be different. For example, MyISAM is a B + Tree non-clustered index (secondary index) while InnoDB is a B + Tree non-clustered index (clustered index), (here there are knowledge points: clustered index and clustered index are the same concept, but different translations in Chinese ). Next, I plan to remove the database index from the following perspectives:

Directory

1. Why should I create an index?

2. What are the types of indexes and how to create them?

3. Use of Indexes

4. Under what circumstances does the query statement not use indexes at all?

5. How can I determine whether an index is used?

6. sort by index

 

Create the table to be used in this article:

Mysql> drop table if exists user_test;

Mysql> create table user_test (id int not null 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;

Mysql> explain user_test;

 

Insert data:

Mysql> insert into user_test (user_name, sex, city, age) values ('zhang san', B '1', 'guangzhou ', 26), ('wang wu ', B '0', 'yunnan ', 28 );

Mysql> SELECT * FROM user_test;

 

1. Why index creation?

• This greatly accelerates data retrieval, which is also the main reason for creating an index.
• By creating a unique index, You can ensure the uniqueness of each row of data in the database table.
• When using group by and order by clauses to retrieve data, it can also significantly reduce the time for grouping and sorting in queries.
• By using indexes, you can use the optimizer during the query process to improve system performance.
• It can accelerate the connection between tables, especially for Data Reference integrity.

Is it too theoretical? No problem. I will explain it to you one by one.

 

2. What are the types of indexes and how to create them?

How to create an index 3:

How can I delete an index?

Drop index index_name ON table_name;

 

2.1 primary key index

• Alter table table_name add primary key index_name ('column ');
• Create prinary key index index_name ON table_name (column (length ));
• Prinary key index_name (column (length ));

 

2.2 unique index

• Alter table table_name add unique index_name ('column ');
• Create unique index index_name ON table_name (column (length ));
• UNIQUE index_name (column (length ));

2.3 normal index

• Alter table table_name add index index_name ('column ');
• Create index index_name ON table_name (column (length ));
• INDEX index_name (column (length ));

2.4 full-text index

• Alter table table_name add fulltest index_name ('column ');
• Create fulltext index index_name ON table_name (column (length ));
• FULLTEXT (column );

2.5 Composite Index

Alter table table_name add index index_name ('column1 ', 'column2 ',...);

Here are some knowledge points: the primary key, that is, the id in the user_test table. The table is decorated with the primary key, and the entire table is a primary key. The primary key value is definitely not repeated and cannot be blank.

Uniqueness: You can use the unique keyword to set that a column in the table is unique and unique. All values in this column are unique and must not be repeated, and cannot be empty.

Therefore, the primary key is unique, but it is not necessarily the primary key.

 

3. Use of Indexes

We have discussed how to create an index. How can we use it after the index is created?

For example, we create a joint index:Alter table user_test add index idx_user (user_name, city, age );

Here are some knowledge points: the establishment of an index requires a basis, rather than creating an index at will. After all, it takes time to create and maintain an index, and the index also takes up physical memory. The following will explain in detail. It is best to create an index when you frequently need to query a certain column or several columns. For example, if the WHERE column is followed by the keyword you need to search, you need to create an index.

Since the index is created, how can we use it when searching? Do you have to write this statement like this every time: SELECT * FROM user_test WHERE username = 'zhang san' AND city = 'guangzhou 'AND age = 26; this is too full. Can you reduce the number of points, or is there a way to simplify it? Of course, are there any rules for it?

 

The above index is used as an example. What kind of query is effective?

Valid query:

(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.

Here are some knowledge points: they are irrelevant to the order of the query conditions after the where clause. This is a misunderstanding of many people.

SQL statement:

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

Or SELECT * FROM user_test WHERE user_name = 'zhang san' AND age = 26 AND city = 'guangzhou ';

Or SELECT * FROM user_test WHERE age = 26 AND city = 'guangzhou AND user_name = 'zhang san '';

The results of these three types of queries are the same.

 

(2) leftmost prefix matching

If a joint query contains multiple columns, do you have to write all the column conditions during the query? Can't I write a few less lines? The answer is yes, but you should be careful with the column you are missing.

Matching the leftmost prefix indicates that the leftmost index column is matched first, that is, the first column you create the Union Index. For example, the first column of the index above is user_name. So you can write:

SQL statement:

SELECT * FROM user_test WHERE user_name = 'zhang san' AND city = 'guangzhou ';

Or SELECT * FROM user_test WHERE user_name = 'zhang san' AND age = 26;

Or SELECT * FROM user_test WHERE city = 'guangzhou 'AND user_name = 'zhang san ';

 

However, the index cannot be used for writing query conditions in this way, but only full-text search is supported. You cannot write the query conditions in this way:

SQL statement:

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

Or SELECT * FROM user_test WHERE age = 26;

Or SELECT * FROM user_test WHERE city = 'guangzhou ';

 

The reason is that the index you created is(User_name, city, age ),Therefore, the query condition must be the leftmost column containing user_name.

Re-apply: 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 ).

 

(3) range value matching

For example, to query all users whose usernames start with "Zhang", the first column of the index is used here,

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

But you cannot write it like this:

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

That is to say, the index must contain the leftmost column, and the leftmost column prefix must be a definite query.

 

(4) prefix index matching

Sometimes the value in the column to which the index is created is long, which increases the storage space of the index and reduces the efficiency of the index. A policy is to use the hash index (here there are knowledge points: the hash index is a fixed value index and cannot be used for Range Query. You can also use the prefix index, which selects the first n characters of the character column as the index, this greatly saves the index space and improves the index efficiency.

How to determine the length of the index column you created?

First, you must select a long-enough prefix to ensure a high matching degree, and the index cannot be too long. We can calculate the length of a proper prefix index in the following ways:

Select count (DISTINCT index_column)/COUNT (*) FROM table_name; -- index_column indicates the column to which you want to add an index.

The above method is used to calculate the prefix index's selective ratio. The higher the ratio, the more efficient the index is. This is the overall calculation of a column. If you want to calculate the matching degree of the first few characters in a column, then:

Select count (distinct left (index_column, 1)/COUNT (*) FROM table_name; -- indicates the matching degree of the first character in the value of index_column,

Select count (distinct left (index_column, 2)/COUNT (*) FROM table_name; -- indicates the matching degree of the second character in the value of index_column,

By using the preceding statements, we can find the ratio that is closest to calculating the prefix index of the entire column. Then, we can use the corresponding character truncation length as the prefix index.

Now we can create a prefix index:

Alter table table_name add index index_name (index_column (length ));

Note: 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.

 

4. Under what circumstances does the query statement not use indexes at all?

Since we have already created an index, what SQL statements are valid during the query and what SQL statements are invalid?

In addition to the full-value index, the leftmost prefix index, and the valid and invalid queries mentioned by the prefix index, the following situations may cause invalid indexes:

• An index Column cannot be a part of an expression or a function parameter. Otherwise, an index query cannot be used.

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

Or SELECT * FROM user_test WHERE age + = 1;

Such statements cannot use indexes.

• 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 = 'zhang san' AND city = 'guang % 'AND age = 26;

The age column is ignored.

• ! = Or <> (not equal to), leading to non-indexing.

Therefore, do not judge null in the query statement, which will lead to a full table query with extremely low efficiency.

 

5. How can I determine whether an index is used?

You thought you used the index you created for the query, but you didn't actually ,.

So how can we see the index created in the table? Two methods:

Show index from table_name;

Or show keys from table_name;

 

How can I check whether the index takes effect?

EXPLAIN + Your SQL statement;

Table: displays the data of this row about which table

Type: this is an important column that shows the type used by the connection. The connection types from the best to the worst are const, eq_reg, ref, range, indexhe, and ALL.

Possible_keys: displays the indexes that may be applied to this table. If it is null, there is no possible index. You can select an appropriate statement from the WHERE statement for the relevant domain.

Key: actually used index. If it is NULL, no index is used. In rare cases, mysql selects an optimized index. In this case, you can use index (indexname) in the SELECT statement to force an INDEX or use ignore index (indexname) to force MYSQL to IGNORE the INDEX.

Key_len: the length of the index used. The shorter the length, the better.

Ref: indicates which column of the index is used. If possible, it is a constant.

Rows: the number of rows that MYSQL deems necessary to check to return the requested data

Extra: Extra information about how MYSQL parses the query. We will discuss it in table 4.3, but here we can see that the bad examples are Using temporary and Using filesort, which means MYSQL cannot use indexes at all, and the result is that the retrieval will be slow.

From the key and ref of this figure, we can see that the three-column index takes effect.

Indicates that the index is used, but only one column is used.

Indicates that no index is used.

 

6. sort by index

 

6.1 first, I want to explain what overwrite indexes are. This concept is used below:

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 city = 'guangzhou 'AND age> 26;

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 EXPLAIN statement is Using index, the overwriting index is used, which greatly improves the access performance.

Back to topic,

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

• The column ORDER after the order by clause must be consistent with the column ORDER of the composite index, and the ORDER Direction (forward/reverse) of all sort columns must be consistent.
• 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:

SELECT user_name, city, age FROM user_test order by user_name;

SELECT user_name, city, age FROM user_test order by user_name, city;

SELECT user_name, city, age FROM user_test order by user_name DESC, city DESC;

SELECT user_name, city, age FROM user_test order by user_name = 'zhang san' order by city;

4th. If the where query condition is the first column of the index column and is a constant condition, order by can also use the index.

 

You cannot sort indexes:

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 'sheet % '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.

Mysql> drop table if exists user_test_ext;

Mysql> create table user_test_ext (id int not null AUTO_INCREMENT primary key,
-> User_name varchar (30) not null,
-> 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;

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;

 

References:

How to understand and correctly use MySQL Indexes

Data Structure and algorithm principles behind MySQL Indexes