How to optimize mysql database?

Methods to optimize Mysql database: 1. Create an index; 2. Compound index; 3. Do not include columns with NULL values ​​in the index; 4. Use short indexes; 5. Try not to include multiple column sorting; 6. Avoid using like operations; 7. Do not perform operations on columns, etc.

This article optimizes the Mysql database through 8 methods: creating indexes, compound indexes, indexes that do not contain columns with NULL values, using short indexes, and sorted indexes Questions, like statement operations, do not perform operations on columns, do not use NOT IN and <> operations, and introduce why you do this to provide reference materials.

Recommended courses: MySQL Tutorial.

1. Create an index

For applications where queries dominate, indexes are particularly important. Many times performance problems are simply caused by us forgetting to add an index, or not adding a more effective index. If there is no index, then a full table scan will be performed to find even a specific piece of data. If the amount of data in a table is large and there are few qualified results, then not adding an index will cause fatal performance degradation. . But it is not necessary to build an index in every situation. For example, gender may only have two values. Building an index not only has no advantage, but also affects the update speed. This is called over-indexing.

2. Composite index

For example, there is a statement like this: select * from users where area='beijing' and age=22;

If we create a single index on area and age respectively, since mysql query can only use one index at a time, although this has improved the efficiency of full table scan compared with no index, but if we create a single index on area and age, Creating a composite index on the column will bring higher efficiency. If we create a composite index of (area, age, salary), it is actually equivalent to creating three indexes (area, age, salary), (area, age), (area), which is called the best left prefix characteristic. Therefore, when we create a composite index, we should put the columns most commonly used as constraints on the left, in descending order.

3. The index should not contain columns with NULL values.

As long as the columns contain NULL values, they will not be included in the index. As long as there is a column in the composite index, Column contains NULL value, then this column is invalid for this composite index. Therefore, when designing the database, we should not let the default value of the field be NULL.

4. Use short index

to index the string. If possible, you should specify a prefix length. For example, if you have a CHAR(255) column, do not index the entire column if most values ​​are unique within the first 10 or 20 characters. Short indexes not only improve query speed but also save disk space and I/O operations.

5. Sorting index problem

mysql query only uses one index, so if the index has been used in the where clause, then the columns in order by are Indexes will not be used. Therefore, do not use sorting operations when the default sorting of the database can meet the requirements; try not to include sorting of multiple columns. If necessary, it is best to create composite indexes for these columns.

6. Like statement operation

Generally, the use of like operation is not encouraged. If it must be used, how to use it is also a problem. Like “�a%” will not use the index but like “aaa%” will.

7. Do not perform operations on columns

select * from users where YEAR(adddate)<2007;

will be in each row This will cause the index to fail and perform a full table scan, so we can change it to

select * from users where adddate<'2007-01-01';

8. Do not use NOT IN and <> operations

NOT IN and <> operations will not use indexes and will perform a full table scan. NOT IN can be replaced by NOT EXISTS, and id<>3 can be replaced by id>3 or id<3.

The above is the detailed content of How to optimize mysql database?. For more information, please follow other related articles on the PHP Chinese website!