Principles of MySQL statement optimization

1, use the index to traverse the table more quickly.

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 help improve performance but not indexes
The more the better, the opposite of too many indexes can 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 the format conversion.

3. The order BY and Gropu by using a by and group by phrase, any index contributes to the performance improvement of the Select.

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 with 37,365 user records inside)

to query without indexing:
SQL statement A:
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:
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:
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:
CREATE index Usernameindex on users (username (6));
Query again:

SQL statement A:
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:
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:
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. So, we're writing the SQL statement
As far as possible to use this way to query.

Our optimization principles for multiple table queries are:
Try to establish the index in: Left JOIN On/right join on ... + condition, in which the conditional statement is involved in the field. Multi-table query than single table check
Polls better reflect the advantages of indexing.

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 rope I used on the top
The primer method is to index the leftmost 6 characters of the username. The shorter the index, the less disk space it takes to spend during the retrieval
Less room. 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
　　The IN, or clauses often use worksheets to invalidate indexes. 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
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
SELECT * from tablename1 where fields1= ' value1 ' and fields2= ' value2 '

The following SQL does not use the index
SELECT * from tablename1 where fields1= ' value1 ' or fields2= ' value2 '

13. The index brings a significant increase in the speed of the query, but the index also takes up extra hard disk space (of course, now general hard disk space is not a problem),
And when you insert new records into a table, the index also needs a certain amount of time to update.
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 view
In practice, indexing is usually 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.

1, create an index
Indexing is especially important for queries as a major application. Most of the time the performance problem is simply because we forgot to add the index, or we didn't add a more efficient index. If you do not add
Index, then look for any even just a specific data will be a full table scan, if a table of large amounts of data and meet the conditions of the result is very small, then no index will cause fatal performance
Drop. But it is not always necessary to build an index, such as the gender may only have two values, the index not only has no advantage, but also affect the update speed, which is called an excessive index.
2, composite Index
For example, there is a statement like this: SELECT * from users where area= ' Beijing ' and age=22;
If we were to create a single index on area and age, because the MySQL query could only use one index at a time, the full table scan was a lot more effective when it was relatively non indexed
Rate, but creating a composite index on an area, an age two column will result in greater efficiency. If we create an area, an age,
Salary), it is actually equivalent to creating (Area,age,salary), (Area,age), (area) Three indexes, which is called the best left prefix
Characteristics. So when we create a composite index, we should place the columns that are most commonly used as constraints to the left, decreasing in descending order.
3, the index does not contain columns with null values
This column is not valid for this composite index as long as the column contains null values that will not be included in the index, as long as one column in the composite index contains null values. So we don't want the default value of the field to be null when designing the database.
4, using short index
Index A string column, if possible, to specify a prefix length. For example, if you have a column with char (255), if most values are unique within the first 10 or 20 characters, do not index the entire column. Short indexing can not only improve query speed but also save disk space and I/O operations.
5, sort of indexing problem
The MySQL query uses only one index, so the columns in the order by are not indexed if the index is already used in the WHERE clause. Therefore, do not use sort operations when the database default sort meets the requirements, and try not to include sorting of multiple columns, preferably if you need to create a composite index for these columns.
6,like Statement Actions
It is generally discouraged to use like operations, and how to use them is also a problem if not used. Like "%aaa%" does not use indexes and like "aaa%" can use indexes.
7, do not perform operations on the column
SELECT * from Users where
Year (adddate)
8, no in and operation not used