This is what I saw on the internet a good MySQL database optimization article, the content is very full!
1, use the index to traverse the table faster
The index established by default is not a clustered index, but sometimes it is not optimal. Under a non-clustered index, data is physically stored randomly on a data page. The reasonable index design should be based on the analysis and prediction of various queries. Generally speaking:
A. You can consider establishing a clustered index if you have a large number of duplicate values and often have range queries (>,<,> =,< =) and columns that occur with order by and group by.
B. Multiple columns are frequently accessed at the same time, and each column contains duplicate values to consider establishing a composite index;
C. Composite indexes to maximize the indexing of critical queries, the leading columns must be the most frequently used columns. Indexes can help improve performance but not the more indexes the better, just the opposite. Too many indexes cause the system to be inefficient. When the user adds an index to the table, maintaining the collection of indexes will update the work accordingly.
2, in the mass query as little as possible with format conversion
3. Order BY and Gropu by: Any index contributes to the performance improvement of a select by using a form by and a group by phrase.
4, any action on the column will result in table scan, which includes database functions, evaluation expressions, and so on, when the query to move the operation to the right of the equal sign as much as possible.
5, IN, or clauses often use a worksheet to invalidate the index. If you do not produce a large number of duplicate values, you can consider the sentence to be opened. The open clause should contain an index.
6, as long as you can meet your needs, should use a smaller data type as far as possible: for example, using mediumint instead of int
7, try to set all columns to NOT NULL, if you want to save NULL, manually set it, instead of setting it as the default value.
8, as little as possible with varchar, TEXT, BLOB type
9, if your data is only a few of the few you know. It is best to use the enum type
10, as Graymice said, to build an index.
Here's an experiment I did to find that indexes can greatly improve the efficiency of queries:
I have a membership information table users, there are 37,365 user records:
To query without indexing:
SQL statement A:
Code:
Copy Code code as follows:
SELECT * from the 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 Total found 960 records
SQL Statement B:
Code:
Copy Code code as follows:
SELECT * from the users where username like ' Xu% ';
The length of 8 queries in Mysql-front is: 0.53,0.53,0.53,0.54,0.53,0.53,0.54,0.54 Total found 836 records
SQL statement C:
Code:
Copy Code code as follows:
SELECT * from the 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 Total found 7 records
To add an index to the username column:
Code:
Copy Code code as follows:
Create index Usernameindex on users (username (6));
Query again:
SQL statement A:
Code:
Copy Code code as follows:
SELECT * from the users where username like '%% ';
The length of 8 queries in Mysql-front is: 0.35,0.34,0.34,0.35,0.34,0.34,0.35,0.34 Total found 960 records
SQL Statement B:
Code:
Copy Code code as follows:
SELECT * from the users where username like ' Xu% ';
The length of 8 queries in Mysql-front is: 0.06,0.07,0.07,0.07,0.07,0.07,0.06,0.06 Total found 836 records
SQL statement C:
Code:
Copy Code code as follows:
SELECT * from the 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 Total found 7 records
In the course of the experiment, I did not open any other programs, the above data shows that in a single table query, indexing can greatly improve the speed of the query.
The other thing to say is that if you set up an index, the speed increase is most obvious to the type of like ' Xu% ' queries. As a result, we try to query in this way when we write SQL statements.
for multiple table queries our optimization principle is：
Try to build the index on the fields that are involved in the conditional statement of the LEFT join On/right join on ... + condition.
Multiple table queries can better reflect the advantages of indexes than a single table query.
11, the establishment of the principle of indexing：
If the prefix value of the data in a column is very small, we'd better index the prefix only. MySQL supports this index. The index method I used above is to index the leftmost 6 characters of the username. The shorter the index, the less disk space it takes, and the less time it takes to retrieve it. This method can index up to 255 characters up to the left.
On many occasions, we can index the creation of multiple columns of data.
The index should be based on the fields that are compared in the query criteria, not on the fields we want to find out and display
12, Madly asked the question: in, or clauses often use worksheets to invalidate the index. If you do not produce a large number of duplicate values, you can consider the sentence to be opened. The open clause should contain an index.
How to solve this sentence, please give an example
examples are as follows:
If an index is established on both FIELDS1 and Fields2, FIELDS1 is the primary index
The following SQL will use the index
Code:
Copy Code code as follows:
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. The index brings a significant increase in the speed of the query, but the index also takes up additional hard disk space (of course, there is no problem with normal hard disk space), and the index also needs to be updated with the update when inserting new records into the table.
Some tables do not have to be indexed if they are often inserts and fewer select. Otherwise rewrite the index every time you write the data; This depends on the actual situation, and usually the index is required.
14. I have doubts about the query efficiency, the general is directly with the MySQL explain to track the situation.
You use Mysql-front to compare the length of the time, I think if the number of scans from the query to more accurate.
