Principles for mysql database Optimization

This is a good mysql database optimization article I have seen on the Internet. The content is full!
1. Use indexes to traverse tables faster
The index created by default is a non-clustered index, but sometimes it is not optimal. In a non-clustered index, data is physically stored on the data page randomly. Reasonable index design should be based on the analysis and prediction of various queries. Generally speaking:
A. You can create a cluster index for columns with a large number of duplicate values and frequent range queries (>,<,>=, <=) and order by and group;
B. Multiple columns are frequently accessed at the same time, and each column contains duplicate values. You can consider creating a composite index;
C. The composite index should try its best to make the key Query Form an index overwrite. Its leading column must be the most frequently used column. Although indexes can improve performance, the more indexes, the better. On the contrary, too many indexes will lead to low system efficiency. Each time you add an index to a table, you must update the index set.
2. Use as few formats as possible for massive queries
3. order by and gropu by: Using order by and group by phrases, any index can improve SELECT performance.
4. Any operation on a column will cause a table scan, including database functions and calculation expressions. During query, try to move the operation to the right of the equal sign.
5. IN And OR clauses usually use worksheets to invalidate indexes. If a large number of duplicate values are not generated, consider splitting the clause. The split clause should contain the index.
6. Use a smaller data type as much as possible to meet your needs: for example, use MEDIUMINT instead of INT.
7. Try to set all columns as not null. If you want to save NULL, set it manually instead of setting it as the default value.
8. Use VARCHAR, TEXT, and BLOB types as little as possible
9. If your data is only a small amount of data you know. It is best to use the ENUM type
10. Create an index as described in graymice.
The following is an experiment I conducted to find that indexes can greatly improve the query efficiency:
I have a member information table, users, which contains 37365 user records:
When no index is added, perform the following query:
SQL statement:
Code: Copy codeThe Code is as follows: select * from users where username like '% ';

The length of 8 queries in Mysql-Front is 1.40, 0.54, 0.54, 0.54, 0.53, 0.55, 0.54, and 960. A total of records are found.
SQL statement B:
Code:Copy codeThe Code is as follows: select * from users where username like '% ';

The maximum query time in Mysql-Front is 0.53, 0.53, 0.53, 0.54, 0.53, 0.53, 0.54, 0.54, and 836. A total of records are found.
SQL statement C:
Code:Copy codeThe Code is as follows: select * from users where username like '% Xu ';

The length of 8 queries in Mysql-Front is 0.51, 0.51, 0.52, 0.52, 0.51, 0.51, 0.52, 0.51, And. A total of 7 records are found.
Add an index for the username column:
Code:Copy codeThe Code is as follows: create index usernameindex on users (username (6 ));

Query again:
SQL statement:
Code:Copy codeThe Code is as follows: select * from users where username like '% ';

The maximum query time in Mysql-Front is 0.35, 0.34, 0.34, 0.35, 0.34, 0.34, 0.35, 0.34, and 960. A total of records are found.
SQL statement B:
Code:Copy codeThe Code is as follows: select * from users where username like '% ';

The maximum query time in Mysql-Front is 0.06, 0.07, 0.07, 0.07, 0.07, 0.07, 0.06, 0.06, and 836. A total of records are found.
SQL statement C:
Code:Copy codeThe Code is as follows: select * from users where username like '% Xu ';

The length of 8 queries in Mysql-Front is 0.32, 0.31, 0.31, 0.32, 0.31, 0.32, 0.31, 0.31, And. A total of 7 records are found.
During the experiment, I didn't open any other program. The above data shows that index creation can greatly improve the query speed in single-table queries.
In addition, if an index is created, the speed increase is the most obvious for queries of the like '%' type. Therefore, we try to use this method for queries when writing SQL statements.
Our Optimization Principle for multi-table queries is::
Try to establish the index on the fields involved in the Condition Statement: left join on/right join on... +.
Multi-table queries are more advantageous than single-table queries.
11. indexing principles:
If there are few duplicate values in the Data prefix in a column, we 'd better index this prefix. Mysql supports this index. The indexing method I used above is to index the leftmost six characters of username. The shorter the index, the less disk space it occupies and the less time it takes to search. This method can index up to 255 characters on the left.
In many cases, we can index multiple columns of data.

The index should be based on the fields to be compared in the query condition, instead of the fields to be found and displayed.
12. Questions from the past: IN and OR clauses often use worksheets to invalidate indexes. If a large number of duplicate values are not generated, consider splitting the clause. The split clause should contain the index.
Here is an example of how to solve this problem.
Example::
If both fields1 and fields2 are indexed, fields1 is the primary index.
The following SQL statements use Indexes
Code:Copy codeThe Code is as follows: select * from tablename1 where fields1 = 'value1' and fields2 = 'value2'

The following SQL statements do not use Indexes
Code:
Select * from tablename1 where fields1 = 'value1' or fields2 = 'value2'
[/Code]
13. indexes greatly increase the query speed, but indexes also occupy extra hard disk space (of course, hard disk space is not a problem now ), it also takes some time to insert a new record to the table as the index is updated.
If some tables are often inserted with less select statements, no index is required. otherwise, it will take time to rewrite the index every time data is written. This depends on the actual situation. Generally, the index is required.

14. When I have doubts about query efficiency, I usually use Mysql's Explain to track query conditions directly.
You use Mysql-Front to compare the time length. I think it is more accurate to the number of fields scanned during the query.