Mysql index and Optimization

Mysql index and Optimization

Mysql index and optimization usage summary:

In relational databases, the use of indexes is very important, and all relational databases support the indexing mechanism, because with indexes, the retrieval speed is very fast for large data volumes and the performance consumption is very low; of course, there are some advantages and disadvantages in everything. Adding indexes will also increase the overhead of the database system. We often need to compromise the design between performance and retrieval, it is also important to use indexes correctly and maintain and optimize indexes!

· Index type?

· Indexing principle?

· When to create an index?

· How to Use indexes?

· Index optimization?

I. Index types

In Mysql, indexes can be divided into common indexes, unique indexes, primary key indexes, foreign key indexes, and composite indexes. They are the same in the index creation location, create and use one or more fields in the table:

1. Common Index

The only task of a common index is to speed up data retrieval. It should be used in a single column that often appears after WHERE or order by, and the index column can be repeated, the INDEX defined by the KEY or INDEX keyword.

2. Unique Index

In addition to accelerating data retrieval, the main task of unique indexes also means that the creation of a single column or the use of unique indexes must be unique, that is, the uniqueness of data is guaranteed, it mainly applies to the index defined by the keyword UNIQUE in terms of retrieval speed and data uniqueness.

3. Primary Key Index

The PRIMARY key index is the default index generated by MYSQL for the PRIMARY key field of the data table. The only difference between it and the UNIQUE index is their definition statement. Here we use PRIMARY instead of UNIQUE.

4. Foreign Key Index

If a foreign KEY is defined for a foreign KEY field, MYSQL generates an index to accelerate the use of foreign KEY constraints. The foreign KEY data can be duplicated.

5. combined index

The difference between a composite index and a common index is that it is defined on multiple columns. The same as a unique Index, a composite index must be unique, in addition, MYSQL can select an appropriate combination of different index fields to combine the query indexes (suitable for the combination of data columns listed in the Front ).

6. Full-text index

A common index on a text field can only accelerate the retrieval of strings (characters starting with the field content) at the top of the field content. If a field contains a large text segment consisting of several or even multiple words, the normal index will be useless. This kind of search usually appears in the form of LIKE % word %, which is very complicated for MySQL. If the amount of data to be processed is large, the response time will be very long.

This type of scenario is where full-text indexes can be used. When an index of this type is generated, MySQL creates a list of all the words that appear in the text, and searches for relevant data records based on the list. The full-text index can be created along with the data table, or you can use the following command to add a full-text index if necessary in the future:

Alter table tablename add fulltext (column1, column2)

With full-text indexes, you can use the SELECT query command to retrieve data records containing one or more given words. The basic syntax for such query commands is as follows:

SELECT * FROM tablename

Where match (column1, column2) AGAINST ('word1 ', 'word2', 'word3 ′)

The preceding command will query all the data records of word1, word2, and word3 in column1 and column2 fields.

Note:

Some blog posts that full-text indexing is not supported by the database engine InnoDB. Otherwise, full-text indexing has been introduced in MYSQL 5.6.

Ii. indexing principles

The indexing principle is clear. When you create or use an index, the index fields are sorted according to certain rules by default. When you retrieve data, the system first retrieves the sorted index content, if the Matching content is found, the system returns immediately without full-text retrieval, which greatly improves the search speed. The following index analysis shows how the index works, continue reading.

Iii. When to create an index

1. Preparations

Given that creating an index requires additional disk space, you need to maintain the index fragmentation and increase the performance of the table's CUD operation in the future. Therefore, you need to be careful when creating an index.

2. When to create

Since the index was born to solve the efficiency of data retrieval, it is obvious that you need to create and maintain indexes on some fields of the frequently-queried table. In view of the considerations in preparation, if indexes must be added to table fields that frequently query and frequently query CUD, we recommend that you use an appropriate algorithm to use indexes, such as binary search (which is not described here ).

Iv. Use of Indexes

Syntax structure:

CREATE [NORMAL | UNIQUE | FULLTEXT] INDEX index_name

ON table_name (col_name [(length) [ASC | DESC], […])

NOTE:

Index_name: Index name;

Table_name: The data table that creates an index;

Col_name: The table field in which the index is created. length indicates the number of digits before the content character and adds the Index algorithm;

1. Common Index

First, I have prepared a data table t_user_info. I have added 5 million pieces of data to this table. Next, let's test and analyze the differences between indexes used and indexes not used. The same condition is retrieved:

SELECT account, nickname, email, address FROM t_user_infoWHERE account = "cwteam4000000 ";

If no index is used, EXPLAIN is used to analyze the SQL statement. The analysis result is as follows:

From, we can see that the complex field of this search is select_type, that is, simple query; the search type is type full table search; the index key (NULL) is not used; the query condition Extra is where; the number of entries to be retrieved is 4889736. In addition, the retrieval duration is about 4 seconds. (delete the EXPLAIN statement and view the results ).

Index used: Use EXPLAIN to analyze the SQL statement. The analysis result is as follows:

A. Create an index

Drop index if exists idx_user_info ON t_user_info;

Create index idx_user_info ON t_user_info (account );

B. Analysis and Retrieval

Here, we only compare the differences. After the index is used, the search type is ref, that is, the Index algorithm retrieval; the key index is used, and the index name is idx_user_info; the number of items to be retrieved is 1, the query condition Extra is Using index condition, and the retrieval duration is about 0.01 seconds, which basically improves the speed by 4 times.

Conclusion:

In the retrieval of a large amount of data, indexes are not used for comparison, which basically increases by four levels. Therefore, indexes are often used for index creation of frequently-retrieved table fields.

2. Unique Index

A. Create an index

Alter table t_user_info drop index idx_user_info;

Alter table t_user_info add unique (account );

B. analysis index

From the analysis, in order to demonstrate accuracy, we will delete the Common Index created above and create a new unique index. The main results of the analysis are the same when using the same search SQL statement as the normal index. In addition, the time obtained is the same. The above only analyzes the speed of the unique index. Next let's take a look at the unique index's features in data uniqueness. Here we choose to insert an existing data. The normal system should return an error message: the same unique index field cannot be inserted repeatedly.

Insert statement:

Insert into t_user_info (account, password, nickname, email, address) VALUES (

"Cwteam11 ",

"E10adc3949ba59abbe56e057f20f883e ",

"Cwteam11 ",

"Abc@yeah.net ",

"China shss"

);

Test results:

Conclusion:

The creation and use of a unique index does not affect its query advantages, but also ensures data uniqueness.

3. combined index

A. Create an index

Alter table t_user_info drop index account;

Alter table t_user_info add index idx_user_info (account, password );

B. analysis index

As you can see, the use of composite indexes is the same as that of common indexes, except that the number of fields in the index is different. In addition, the query time is the same.

Conclusion:

The principle of composite indexes is the same as that of common indexes. The difference is that the number of index fields does not affect the retrieval speed.

NOTE:

In general, composite indexes are used to ensure that multiple columns are UNIQUE, that is, they must be combined with UNIQUE indexes for practical significance. The above only demonstrates the differences in indexes.

4. Full-text index

Full-text indexing is supported by the engine MyISAM by default. However, the database engine InnoDB is also supported since MySQL. Because my MYSQL version is earlier than 5.6 and has not been upgraded (subsequent upgrades), the engine of the data table is changed to MyISAM for the purpose of introducing full-text search.

In 5 million of the data, we compare LIKE and MATCH (...) AGAINST (...) When the same function is achieved:

Differences between the two:

A. Use LIKE to search

Explain select account, nickname, email, address FROM t_user_info WHEREaccount LIKE "cwteam400000 ";

Analysis result:

We can see that when using LIKE search, we use full table scan instead of index scan, and the duration is about 1.16 seconds. This is actually acceptable for 5 million pieces of data, however, as the data increases, this duration will also increase. Let's take a look at the MATCH... AGAINST features.

B. Use full-text index

Explain select account, nickname, email, address FROM t_user_info WHEREMATCH (account, password) AGAINST ('cwteam400000 ');

Analysis result:

It is obvious that full-text search FULLTEXT is used for retrieval, and its duration is about 0.01 seconds. Therefore, the efficiency of full-text indexing is quite high. Of course, the retrieval duration will also increase with the increase of data volume, increasing ).

Conclusion:

If you have created a large number of index columns, we recommend that you use full-text search. In large data search, you can use MATCH-AGAINST to make FULLTEXT take effect, instead of LIKE full table search.

5. INDEX OPTIMIZATION

Based on the index introduction, we can summarize the common index optimization suggestions:

1. Create an index for frequently queried table fields

Specific indexes can be created based on requirements. If there are no special requirements, such as whether repeated indexes are allowed, a common index can be created. Otherwise, a unique epitome can be created; if multiple index columns are required to be unique, create a unique composite index.

2. Try to use the FULL-TEXT index instead of LIKE in large data search

If you are using the InnoDB engine, you need to upgrade the MYSQL version to 5.6 or use the MyISAM engine.

3. Maintain and optimize index fragmentation

When a data table with indexes is created, the index mark on the corresponding record is not deleted every time the record data is deleted. This will produce data garbage, also called fragments, and will not be processed for a long time, will affect the efficiency of data retrieval.

Better solution: re-create the index.

4. Avoid using aggregate Functions

In data retrieval with indexes, try not to use aggregate functions after the search conditions, which may invalidate the index and affect the data retrieval speed.

NOTE:

Most of the time, the index optimization is to ensure that the index can play its normal functions. Therefore, many indexes are optimized to avoid the index being persecuted, the rest is the optimization of the search SQL.