1, an overview of the

Index is a data structure used by storage engine to find records quickly. Proper use of database index can greatly improve system access performance. Next, it mainly introduces index types in MySql database and how to create more reasonable and efficient index skills.

Note: This is mainly for InnoDB storage engine’s B+Tree index data structure

2. Advantages of indexes

1, greatly reduce the server need to scan the amount of data, thus improving the speed of data retrieval

2. Help the server avoid sorting and temporary tables

3. You can change random I/ OS into sequential I/ OS

3. Index creation

3.1 primary key index

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

3.2. Unique index

ALTER TABLE 'table_name' ADD UNIQUE 'index_name' ('column');Copy the code

3.3. Common Indexes

ALTER TABLE 'table_name' ADD INDEX 'index_name' ('column');Copy the code

3.4. Full-text index

ALTER TABLE 'table_name' ADD FULLTEXT 'index_name' ('column');Copy the code

3.5. Composite indexes

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

4, B+Tree index rule

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;Copy the code

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

4.1 index valid query

4.1.1. Full Value Matching

Full value matching refers to the matching of all columns in the index. For example, if the index is created in the preceding example, the data with the condition (user_name, city, age) can be queried at the same time.

Note: not related to the order of the query conditions after where, here is a lot of students easy to misunderstand a place

SELECT * FROM user_test WHERE user_name = 'feinik' AND age = 26 AND city = 'gz ';Copy the code

4.1.2 match the leftmost prefix

The left-most prefix matches the left-most index column first. For example, the indexes created above can be queried under the following conditions: (user_name), (user_name, city), (user_name, city, age)

Note: the order of the left-most prefix query is independent of the order of the index column, e.g. : (city, user_name), (age, city, user_name)

4.1.3 Match the column prefix

Indicates the beginning of a matching column value. For example, query all users whose user names start with feinik

SELECT * FROM user_test WHERE user_name LIKE 'feinik%';Copy the code

4.1.4 Matching range value

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

SELECT * FROM user_test WHERE user_name LIKE 'feinik%';Copy the code

4.2. Index limitations

Select * from ‘where’ where (select * from ‘where’);

SELECT * FROM user_test WHERE city = 'guangzhou ';Copy the code

or

SELECT * FROM user_test WHERE age= 26;Copy the code

or

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

Select * from feinik; select * from feinik; select * from feinik

SELECT * FROM user_test WHERE user_name like '%feinik';Copy the code

If there is a range query for a column, then all columns to the right of the column cannot be indexed.

SELECT * FROM user_test WHERE user_name = 'feinik' AND city LIKE 'gz %' AND age = 26;Copy the code

5. Efficient indexing strategies

5.1. Indexed columns cannot be part of an expression or arguments to a function, otherwise indexed queries cannot be used.

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

5.2. Prefix index

Sometimes it takes long character indexed columns, this will increase the index of storage space and reduce the efficiency of the index, a strategy can be use hash index, another is to use the prefix index, prefix index is to choose the first n characters of character column as the index, so can greatly save the index space, so as to improve the efficiency of the index.

5.2.1 Selectivity of prefix indexes

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 the prefixed index is to be addedCopy the code

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

(2)

SELECT

COUNT(DISTINCT LEFT(index_column,1))/COUNT(*),

COUNT(DISTINCT LEFT(index_column,2))/COUNT(*),

COUNT(DISTINCT LEFT(index_column,3))/COUNT(*)

...

FROM table_name;Copy the code

Note: step by step find the selective ratio of the prefix index in (1), then you can use the corresponding character truncation length to make the prefix index

5.2.2 Creating prefix indexes

ALTER TABLE table_name ADD INDEX index_name (index_column(length));Copy the code

5.2.3. Precautions for using prefix indexes

Prefixed indexes are a useful way to make indexes smaller and faster, but MySql cannot use prefixed indexes for ORDER BY and GROUP BY and for overwrite scans.

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 using the index. The index order of composite indexes can be accomplished by the rule of thumb: This rule is consistent with the selective method 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 Indexes and non-clustered Indexes

1. Aggregate indexes

A table can only have one clustered index. If a primary key is defined, InnoDB aggregates data by primary key. If no primary key is defined, InnoDB uses a unique non-empty index instead. InnoDB implicitly defines a primary key as a clustered index.

Clustered index can greatly improve the access speed, because the clustered index will index and rows of data stored in the same b-tree, so find the index also corresponding to find the corresponding row data, but in the use of the clustered index need to pay attention to avoid random clustered index (generally refers to the primary key of discontinuity, and the distribution range is uneven). Using UUID as a clustered index results in poor performance because discontinuous UUID values can lead to increased index fragmentation and random I/O, resulting in a sharp decline in query performance.

2. Non-clustered indexes

Different from a clustered index, a non-clustered index does not determine the physical ordering of data on disk, and a B-tree contains indexes but does not contain row data. Row data only refers to row data by the pointer corresponding to the index stored in a B-tree, for example: The index created above on (user_name, city, age) is a non-clustered index.

5.5. Overwrite indexes

If an index (such as a composite index) contains the values of all the fields to be queried, it is called a covered index, as in:

SELECT user_name, city, age FROM user_test WHERE user_name = 'feinik' AND age > 25;Copy the code

Select * from Extra (user_name, city, age); select * from Extra (user_name, city, age); select * from Extra (user_name); Overwriting indexes can greatly improve access performance.

5.6. How do I use indexes to sort

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

  • 1, The ORDER of the columns after the ORDER BY clause must be the same as that of the combined index, and all the columns must be ordered in the same direction (forward/backward)
  • 2. The value of the queried field must be included in the index column and meet the requirement of overwriting the index

Specific analysis through examples

Create a composite index on the USER_test table

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

Can be used to index sort cases

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 WHERE user_name = 'feinik' ORDER BY city;Copy the code

Note: The fourth point is special, if the where query condition is the first column of the index column, and the constant condition, then the index can also be used

A case where index sort cannot be used

1, sex is not in the index

SELECT user_name, city, age FROM user_test ORDER BY user_name, sex;Copy the code

2, the direction of the sequence is inconsistent

SELECT user_name, city, age FROM user_test ORDER BY user_name ASC, city DESC;Copy the code

Select * from sex where sex = ‘sex’

SELECT user_name, city, age, sex FROM user_test ORDER BY user_name;Copy the code

SQL > select user_name from user_name where user_name from user_name where user_name from user_name

SELECT user_name, city, age FROM user_test WHERE user_name LIKE 'feinik%' ORDER BY city;Copy the code

SQL > alter table join (select * from ORDER BY, select * from ORDER BY); SQL > alter table join (select * from ORDER BY, select * from ORDER BY); For example, create a user extension table user_test_ext and index 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);Copy the code

Indexed sort

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;Copy the code

Don’t 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 ue.uid;Copy the code

6, summary

This article mainly describes 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, about the use of index skills are not only these, more skills about the index also need to constantly accumulate relevant experience.