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

Today, the editor to share with you is a detailed introduction of the index in the MySQL database, many people do not understand, today, in order to let you know more about the index in the MySQL database, so give you a summary of the following content, let's look down. I'm sure you'll get something.

1. Overview

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

The types of indexes in MySql databases and how to create more reasonable and efficient index techniques.

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

2. The advantages of index

It greatly reduces the amount of data that the server needs to scan, thus improving the speed of data retrieval.

Help the server avoid sorting and temporary tables

You can change a random Imax O to a sequential Imax 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. Composite index

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

4. Index rules of B+Tree

Create a user table for the 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 bust 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. Query with valid index

4.1.1, full value matching

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

Data for the condition.

Note: it has nothing to do with the order of query conditions after where. This is a place that many students are easy to 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 means to match the leftmost index column first. For example, the index created above can be used to query the following conditions: (user_name), (user_name, city), (user_name, city, age).

Note: the order in which the leftmost prefix query condition is satisfied is independent of the order of the index columns, such as (city, user_name), (age, city, user_name).

4.1.3, match column prefix

Refers to the beginning of a matching column value, such as querying all users whose user names begin with feinik

SELECT * FROM user_test WHERE user_name LIKE 'feinik%'

4.1.4, matching range valu

For example, query all users whose user names begin with feinik, where the first column of the index is used

SELECT * FROM user_test WHERE user_name LIKE 'feinik%'

4.2. Restrictions on the index

1. If the where query condition does not contain the leftmost index column in the index column, you cannot use the index query, 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 user names end 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 condition, none of the columns on the right can optimize the query using the index, such as:

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

5. Efficient indexing strategy

5.1. An index column cannot be part of an expression or an argument to a function, otherwise an index query cannot be used.

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

5.2, prefix index

Sometimes long character columns are required, which increases the storage space of the index and reduces the efficiency of the index. one strategy is to use a hash index, and the other is to make

Using the prefix index, the prefix index 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, selectivity of prefix index

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

(1)

SELECT COUNT (DISTINCT index_column) / COUNT (*) FROM table_name;-- index_column represents the column to which you want to add a prefix index

Note: the selective ratio of the prefix index is calculated in the above way. The higher the ratio is, 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 statement to gradually find the selective 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, creation of prefix index

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

5.2.3. Points for attention in using prefix indexes

The prefix index is an effective way to make the index smaller and faster, but MySql cannot use the prefix index for ORDER BY and GROUP BY and overwrite with the prefix index.

Scan.

5.3. Select the appropriate index column order

The order of index columns is very important in the creation of composite indexes. the correct index order depends on the query method of using the index.

The rule to help us do this: put the most selective columns in the front column of the index, which is consistent with the selective method of prefix indexes, but not the order of all combined indexes.

All of them 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

A clustered index determines the physical sort of data on a physical disk. A table can only have one clustered index. If a primary key is defined, then InnoDB aggregates the data through the primary key, as shown in

If no primary key is defined, InnoDB will choose a unique non-empty index instead. If there is no unique non-empty index, InnoDB will implicitly define a primary key as the clustering index.

Quote.

The clustered index can greatly improve the access speed, because the clustered index saves the index and row data in the same B-Tree, so if the index is found, it will be found accordingly.

Corresponding row data, but when using a clustered index, you should be careful to avoid random clustered indexes (which generally refer to discontinuous primary key values and uneven distribution), such as using UUID to do

Performance for clustered indexes can be poor, because discontiguous UUID values can lead to a lot of index fragmentation and random Imax O, resulting in a sharp decline in query performance.

2. Nonclustered index

Unlike a clustered index, a nonclustered index does not determine the physical sort of data on disk, and the B-Tree contains the index but no row data, which is simply stored in the

The index in B-Tree corresponds to a pointer to row data, for example, the index established on (user_name,city, age) above is a nonclustered index.

5.5, override 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 contained in the index column of the composite index, an overlay index query is used to see if the override index can be used.

Taking the value in the Extra in the execution plan as Using index proves that the overlay index is used, and the override index can greatly improve access performance.

5.6. How to use an index to sort

In the sorting operation, if you can use the index to sort, then you can greatly improve the speed of sorting, to use the index to sort need to meet the following two points.

1. The column order after the ORDER BY clause should be the same as that of the composite index, and the sorting direction of all sort sequences (positive / reverse) should be the same.

2. The field values queried need to be included in the index column and satisfy the overlay 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 in which 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 special. If the where query condition is the first column of the index column and is a constant condition, then the index can also be used.

Cases in which 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 arrangement is not consistent.

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 table joins, indexes can be used only if the sort fields after ORDER BY are index columns in the first table (which need to meet the two rules of index sorting above).

Quoting and sorting. Such as: create a 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

Do not go index sort

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 index rules of B+Tree tree structure, the creation of different indexes, and how to correctly create efficient index skills to improve query speed as much as possible, of course.

About the use of the index skills are not only these, more skills about the index also need to accumulate relevant experience at ordinary times.

The above is a brief introduction to the detailed introduction of the index in the MySQL database. Of course, the differences in the detailed use of the above have to be understood by everyone. If you want to know more, welcome to 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.