MySQL optimization I (Introduction to literacy and indexing) (Advanced chapter)

Time:2021-5-3

Previously, we learned about the locks of MyISAM and inoodb engines. Now let’s talk about the differences between the two engines. What are they suitable for?

What MyISAM engine is suitable for:
(it doesn’t support transactions or foreign keys, especially for fast access speed, no requirement for transaction integrity, or application based on select and insert can basically use this engine to create tables.)
Explanation of other data:
1. Select intensive tables. MyISAM storage engine is very fast in filtering large amounts of data, which is its most prominent advantage.
2. Insert intensive tables. The concurrent insert feature of MyISAM allows you to select and insert data at the same time. For example, MyISAM storage engine is very suitable for managing mail or web server log data.
What inoodb engine is suitable for:
InnoDB storage engine provides transaction security with commit, rollback and crash recovery capabilities. However, compared with the storage engine of MyISAM, InnoDB is less efficient and takes up more disk space to retain data and index.
Interpretation of other information:
1. Update intensive tables. InnoDB storage engine is especially suitable for handling multiple concurrent update requests.
2. Business. InnoDB storage engine is a standard MySQL storage engine supporting transactions.
3. Automatic disaster recovery. Unlike other storage engines, InnoDB tables can automatically recover from disasters.
4. Foreign key constraint. The only storage engine supporting foreign keys in MySQL is InnoDB.
5. Support auto adding columns automatically_ Attribute increment

Why does MySQL performance decline?

Fundamental: slow SQL execution is either due to CPU complexity or frequent IO overhead
1. First of all, enter Linux and hit top to see if the memory is not enough, CPU is full, disk is full or other hardware reasons! These ruled out the reason to find SQL again!
2. If we really exclude the above reasons, then we will find the reason for SQL
General reasons for slow performance degradation:
Long execution time or waiting time
The reasons for long execution time or query time are as follows:
a. The query statements are poorly written (explanation: the seed queries, various join functions, etc. result in index invalidation or slow running without establishing an index at all)
b. Index invalidation(
Explanation: established but not used
Index is divided into single value index and composite index
Single value index:
Suppose there are 1 million pieces of data in the table, and we can query one of them. We can create an index for the name field, which means that the query will be very fast;
select * from users where name = ‘’;
In this case, we can create an index for the name field
//Index means index
//idx_ users_ Name index name is usually defined like this: IDX prefix indicates which table of index users name which field of this table. Of course, we can also name it by other names, but this is more standardized
//On users (name) indicates which field in the table is being indexed
create index idx_ users_ name on users(name);// Key words: create index
Composite index:
Suppose we’re going to search for two fields
select * from users where name=’’ and password=’’;
There are often combined fields that need to be queried, so now we can create a composite index
create index idx_ users_ namepassword on users(name,password);// Key words: create index
This creates a composite index, which will be used when name and password are checked at the same time

c. Too many joins for associated queries(
Explanation: design defects or unavoidable requirements
Too many table join associations and nested subqueries will cause slow queries!
)
d. Server tuning and parameter configuration (number of buffering threads, etc.)

SQL execution order: SQL execution order:

The order of user’s handwritten SQL is as follows:
MySQL optimization I (Introduction to literacy and indexing) (Advanced chapter)
The internal execution order of SQL is as follows:
MySQL optimization I (Introduction to literacy and indexing) (Advanced chapter)MySQL optimization I (Introduction to literacy and indexing) (Advanced chapter)

Indexes:

What is an index?
MySQL official definition index: index is a data structure to help MySQL get data efficiently
Essence: index is a data structure
The purpose of index is to improve the efficiency of query
The quick search data structure in good order is index!
Index has two functions
Search and sort, that is to say, it will affect whether the conditional search after where can be used for quick search, and it will also affect order by sort
Advantages and disadvantages of index
MySQL optimization I (Introduction to literacy and indexing) (Advanced chapter)
The index is a process of deleting, building and deleting. We have to update the index according to the user’s search habits! It’s not a constant layer
Index classification and creation:
It is generally recommended to create a composite index. Of course, there are different situations. If a field is searched many times and frequently, we can also create a single value index for it;
(we created a composite index, but we only used one of the indexes when searching. It’s also possible and effective!)
The number of indexes in a table should not exceed 5 at most, which is a standard practice!
MySQL optimization I (Introduction to literacy and indexing) (Advanced chapter)
MySQL optimization I (Introduction to literacy and indexing) (Advanced chapter)
When is it appropriate to build an index and when is it not
MySQL optimization I (Introduction to literacy and indexing) (Advanced chapter)
be careful:
If there are few records in the table, don’t index them, because the amount of data in MySQL database is still roaring!
If there are too many duplicate values in a field, don’t index it, because it’s useless!

This work adoptsCC agreementReprint must indicate the author and the link of this article

Hu Jun

Recommended Today

[Q & A share the second bullet] MySQL search engine, after watching the tyrannical interviewer!

Hello, I’m younger brother. A few days ago, I shared the second interview question, the interview site of search engine in MySQL. This question is the interview at normal temperature. After reading it, I’m sure you will gain something in terms of database engine If you haven’t read my first share, you can refer to […]