Some Optimization Methods and precautions for using indexes in mysql

This article collects a large number of questions from the Internet about how to use mysql indexes to optimize your own databases. It also introduces some situations that cannot be found in index columns.

Below are some information found on the Internet. Keep it for use.

1. Create an index
Indexes are especially important for applications where queries account for the majority. In many cases, the performance problem is very simple because we forget to add an index, or we have not added a more effective index. If no index is added, a full table scan is performed to search for any specific data. If the data volume of a table is large and the results meet the requirements are few, if you do not add an index, it will cause fatal performance degradation. However, it is not always necessary to create an index. For example, there may be only two values for gender. Creating an index not only has no advantages, but also affects the update speed. This is called excessive index.

2. Composite Index
For example, a statement is as follows:

The Code is as follows:	Copy code
Select * from users where area = 'beijing' and age = 22;

If we create a single index on the area and age respectively, mysql queries can only use one index at a time. Therefore, although full table scan improves the efficiency compared with no index, however, creating a composite index on the "area" and "age" columns will increase the efficiency. If we create a composite index (area, age, salary), it is equivalent to creating (area, age, salary), (area, age), (area) three indexes, which are called the best left prefix feature. Therefore, when creating a composite index, we should place the columns that are most commonly used as restrictions on the leftmost and decrease in turn.

3. The index does not contain columns with NULL values.
As long as a column contains a NULL value, it will not be included in the index. If a column in the composite index contains a NULL value, this column is invalid for this composite index. Therefore, do not set the default value of a field to NULL during database design.

4. Use short Indexes
Index a string or column. If possible, specify a prefix length. For example, if a CHAR (255) Column exists and multiple values are unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only increase query speed, but also save disk space and I/O operations.

5. Sorting index problems
Mysql queries only use one index. Therefore, if an index is already used in the where clause, columns in order by will not use the index. Therefore, do not use the sorting operation when the database's default sorting can meet the requirements. Try not to include the sorting of multiple columns. It is best to create a composite index for these columns if necessary.

6. like statement operation
Generally, like operations are not encouraged. If they are not usable, how to use them is also a problem. Like "% aaa %" does not use indexes, but like "aaa %" can use indexes.

7. Do not perform operations on columns

The Code is as follows:	Copy code
Select * from users where YEAR (adddate) <2007;

The operation will be performed on each row, which will cause index failure and scan the entire table, so we can change it

The Code is as follows:	Copy code
Select * from users where adddate <'2014-01-01 ';

8. Do NOT use the not in and <> operations.

Not in and <> operations do NOT use indexes to scan the entire table. Not in can be replaced by not exists, and id <> 3 can be replaced by id> 3 or id <3.