Some optimization methods and precautions for indexing in MySQL

Here are some of the information found on the Internet, save it for backup.

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 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 can cause fatal performance degradation. 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:

The code is as follows	Copy Code
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 can only use one index at a time, the full table scan is a lot more efficient when it is relatively non indexed, but creating a composite index on the area, the age two column can be more efficient. If we create a composite index (area, age, salary), it's actually equivalent to creating (Area,age,salary), (Area,age), (area) Three indexes, which is called the best left prefix feature. 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

The code is as follows	Copy Code
SELECT * from users where year (adddate) <2007;

will be performed on each row, which will cause the index to fail with a full table scan, so we can change it to

The code is as follows	Copy Code
SELECT * from users where adddate< ' 2007-01-01 ';

8, do not use not in and <> operations

None in and <> operations do not use the index to perform a full table scan. Not in can be substituted by not exists, id<>3 can be replaced with id>3 or id<3.