Principles to follow for mysql database optimization
- 2020-05-14 05:14:25
- OfStack
This is a good mysql database optimization article I saw on the Internet, the content is very full!
1. Use indexes to traverse tables faster
The index that is created by default is a non-clustered index, but sometimes it is not optimal. In a non-clustered index, data is physically randomly stored on a data page. Reasonable index design is based on the analysis and prediction of various queries. 1 generally speaking:
a. Has a large number of duplicate values and often has range queries ( > , < . > =, < =) and order by, group by columns, can be considered to establish a cluster index;
b. Often multiple columns are accessed at the same time, and each column contains duplicate values.
c. Composite indexes are designed to maximize index coverage of key queries, leading column 1 to be the most frequently used column. Indexes can improve performance, but not more indexes. On the contrary, too many indexes can lead to system inefficiency. Every time a user adds an index to a table, the maintenance of the index set must be updated accordingly.
2. Minimize format conversion when making massive queries
3. ORDER BY and GROPU BY: using the ORDER BY and GROUP BY phrases, any index can contribute to the performance of SELECT.
4. Any operation on a column will result in a table scan, which includes database functions, evaluated expressions, and so on. When querying, move the operation as far to the right of the equal sign as possible.
5. The IN, OR clauses often use worksheets to invalidate indexes. If you don't generate a lot of duplicate values, consider splitting the clause. The detached clause should contain an index.
6. Whenever possible, use a smaller data type that meets your needs: MEDIUMINT instead of INT, for example
7. Try to set all the columns to NOT NULL. If you want to save NULL, set it manually instead of setting it to the default.
8. Use VARCHAR, TEXT and BLOB types as little as possible
9. If you have only a handful of data that you know about. It is best to use the ENUM type
10. Index, as graymice says.
The following is an experiment I conducted, I can find that indexes can greatly improve the efficiency of the query:
I have a membership information form users, in which there are 37365 user records:
Query without index:
A sql statements:
Code:
Eight times in the Mysql - Front query length: 1.40, 0.54, 0.54, 0.54, 0.53, 0.55, 0.54, found 960 records
B sql statements:
Code:
Eight times in the Mysql - Front query length: 0.53, 0.53, 0.53, 0.54, 0.53, 0.53, 0.54, 0.54, found 836 records
C sql statements:
Code:
Eight times in the Mysql - Front query length: 0.51, 0.51, 0.52, 0.52, 0.51, 0.51, 0.52, 0.51 find seven records
Add an index to the username column:
Code:
Query again:
A sql statements:
Code:
Eight times in the Mysql - Front query length: 0.35, 0.34, 0.34, 0.35, 0.34, 0.34, 0.35, 0.34, found 960 records
B sql statements:
Code:
Eight times in the Mysql - Front query length: 0.06, 0.07, 0.07, 0.07, 0.07, 0.07, 0.06, 0.06, found 836 records
C sql statements:
Code:
Eight times in the Mysql - Front query length: 0.32, 0.31, 0.31, 0.32, 0.31, 0.32, 0.31, 0.31 find seven records
In the experimental process, I did not open any other procedures, the above data shows that in the single table query, the establishment of the index can greatly improve the query speed.
It is also important to note that for queries of type like 'xu %', the speed increase is most noticeable if the index is established. Therefore, we try to use this approach when writing sql statements.
Our optimization principle for multi-table query is:
Try to build the index at: left join on/right join on... + condition, the condition statement refers to the field.
Multi-table queries can better reflect the advantages of indexes than single-table queries.
11. Principles of index establishment:
If the data in column 1 has few duplicate prefix values, it is best to index only that prefix. Mysql supports this index. The indexing method I used above is to index the six leftmost characters of username. The shorter the index, the less disk space it takes and the less time it takes to retrieve. This method indexes up to 255 characters to the left.
In many cases, we can index multiple columns of data.
The index should be based on the fields in the query criteria to compare, not on the fields we want to find and display
12, 1 deep questions :IN, OR clause often use worksheets, invalidating the index. If you don't generate a lot of duplicate values, consider splitting the clause. The detached clause should contain an index.
Can you give me an example
Examples are as follows:
If both indexes are built on fields1 and fields2, fields1 is the primary index
The following sql will use the index
Code:
The following sql does not use the index
Code:
select * from tablename1 where fields1='value1' or fields2='value2'
[/code]
13. Indexes bring great speed improvements on queries, but indexes also take up extra hard disk space (of course, 1 hard disk space is not a problem now), and indexes need to be updated as new records are inserted into the table, which also takes a certain amount of time.
Some tables do not need to be indexed if they have insert more often than select less often. This depends on the situation, and usually the index is required.
14. When I have doubts about the query efficiency, I usually use Mysql's Explain directly to track the query.
You use Mysql-Front to compare by length, and I think it would be more accurate to compare by the number of times the field was scanned at query time.
1. Use indexes to traverse tables faster
The index that is created by default is a non-clustered index, but sometimes it is not optimal. In a non-clustered index, data is physically randomly stored on a data page. Reasonable index design is based on the analysis and prediction of various queries. 1 generally speaking:
a. Has a large number of duplicate values and often has range queries ( > , < . > =, < =) and order by, group by columns, can be considered to establish a cluster index;
b. Often multiple columns are accessed at the same time, and each column contains duplicate values.
c. Composite indexes are designed to maximize index coverage of key queries, leading column 1 to be the most frequently used column. Indexes can improve performance, but not more indexes. On the contrary, too many indexes can lead to system inefficiency. Every time a user adds an index to a table, the maintenance of the index set must be updated accordingly.
2. Minimize format conversion when making massive queries
3. ORDER BY and GROPU BY: using the ORDER BY and GROUP BY phrases, any index can contribute to the performance of SELECT.
4. Any operation on a column will result in a table scan, which includes database functions, evaluated expressions, and so on. When querying, move the operation as far to the right of the equal sign as possible.
5. The IN, OR clauses often use worksheets to invalidate indexes. If you don't generate a lot of duplicate values, consider splitting the clause. The detached clause should contain an index.
6. Whenever possible, use a smaller data type that meets your needs: MEDIUMINT instead of INT, for example
7. Try to set all the columns to NOT NULL. If you want to save NULL, set it manually instead of setting it to the default.
8. Use VARCHAR, TEXT and BLOB types as little as possible
9. If you have only a handful of data that you know about. It is best to use the ENUM type
10. Index, as graymice says.
The following is an experiment I conducted, I can find that indexes can greatly improve the efficiency of the query:
I have a membership information form users, in which there are 37365 user records:
Query without index:
A sql statements:
Code:
select * from users where username like '% xu %';
Eight times in the Mysql - Front query length: 1.40, 0.54, 0.54, 0.54, 0.53, 0.55, 0.54, found 960 records
B sql statements:
Code:
select * from users where username like ' xu %';
Eight times in the Mysql - Front query length: 0.53, 0.53, 0.53, 0.54, 0.53, 0.53, 0.54, 0.54, found 836 records
C sql statements:
Code:
select * from users where username like '% xu ';
Eight times in the Mysql - Front query length: 0.51, 0.51, 0.52, 0.52, 0.51, 0.51, 0.52, 0.51 find seven records
Add an index to the username column:
Code:
create index usernameindex on users(username(6));
Query again:
A sql statements:
Code:
select * from users where username like '% xu %';
Eight times in the Mysql - Front query length: 0.35, 0.34, 0.34, 0.35, 0.34, 0.34, 0.35, 0.34, found 960 records
B sql statements:
Code:
select * from users where username like ' xu %';
Eight times in the Mysql - Front query length: 0.06, 0.07, 0.07, 0.07, 0.07, 0.07, 0.06, 0.06, found 836 records
C sql statements:
Code:
select * from users where username like '% xu ';
Eight times in the Mysql - Front query length: 0.32, 0.31, 0.31, 0.32, 0.31, 0.32, 0.31, 0.31 find seven records
In the experimental process, I did not open any other procedures, the above data shows that in the single table query, the establishment of the index can greatly improve the query speed.
It is also important to note that for queries of type like 'xu %', the speed increase is most noticeable if the index is established. Therefore, we try to use this approach when writing sql statements.
Our optimization principle for multi-table query is:
Try to build the index at: left join on/right join on... + condition, the condition statement refers to the field.
Multi-table queries can better reflect the advantages of indexes than single-table queries.
11. Principles of index establishment:
If the data in column 1 has few duplicate prefix values, it is best to index only that prefix. Mysql supports this index. The indexing method I used above is to index the six leftmost characters of username. The shorter the index, the less disk space it takes and the less time it takes to retrieve. This method indexes up to 255 characters to the left.
In many cases, we can index multiple columns of data.
The index should be based on the fields in the query criteria to compare, not on the fields we want to find and display
12, 1 deep questions :IN, OR clause often use worksheets, invalidating the index. If you don't generate a lot of duplicate values, consider splitting the clause. The detached clause should contain an index.
Can you give me an example
Examples are as follows:
If both indexes are built on fields1 and fields2, fields1 is the primary index
The following sql will use the index
Code:
select * from tablename1 where fields1='value1' and fields2='value2'
The following sql does not use the index
Code:
select * from tablename1 where fields1='value1' or fields2='value2'
[/code]
13. Indexes bring great speed improvements on queries, but indexes also take up extra hard disk space (of course, 1 hard disk space is not a problem now), and indexes need to be updated as new records are inserted into the table, which also takes a certain amount of time.
Some tables do not need to be indexed if they have insert more often than select less often. This depends on the situation, and usually the index is required.
14. When I have doubts about the query efficiency, I usually use Mysql's Explain directly to track the query.
You use Mysql-Front to compare by length, and I think it would be more accurate to compare by the number of times the field was scanned at query time.