Shulou(Shulou.com)06/01 Report--

This article mainly introduces the method of optimizing mysql database, which has certain reference value and can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

1. Create an index

For the main application of query, the index is particularly important. In many cases, performance problems are simply caused by forgetting to add indexes, or not adding more efficient indexes. If it is not indexed, finding even a specific piece of data will cause a full table scan, and if a table has a large amount of data and few qualified results, then non-indexing can cause fatal performance degradation. However, indexing is not necessary in all cases. For example, there may be only two values for gender. Indexing not only has no advantage, but also affects the speed of update, which is called excessive 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 queries can only use one index at a time, although this has improved the efficiency of full table scanning compared to not indexing, it will be more efficient if we create composite indexes on area and age columns. If we create a composite index of (area,age,salary), it is actually equivalent to creating (area,age,salary), (area,age), and (area) three indexes, which is called the best left prefix feature. Therefore, when creating a composite index, we should place the column that is most commonly used as a constraint on the far left, decreasing in turn.

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

As long as the column contains a null value, it will not be included in the index, and as long as one column in the composite index contains a null value, the column is invalid for the composite index. So let's not let the default value of the field be NULL when designing the database.

4. Use short indexes

Index the string column and specify a prefix length if possible. For example, if you have a column of CHAR, and if most of the values are unique within the first 10 or 20 characters, do not index the entire column. Short indexes can not only improve the query speed, but also save disk space and Imax O operations.

5. The index problem of sorting

The mysql query uses only one index, so if the index is already used in the where clause, the columns in the order by will not use the index. Therefore, do not use the sort operation when the database default sorting can meet the requirements; try not to include multiple column sorting, and it is best to create a composite index on these columns if necessary.

6. Like statement operation

In general, the use of like operations is discouraged, and if you have to, how to use it is also a problem. Like "% aaa%" does not use an index while like "aaa%" can use an index.

7. Do not perform operations on columns

Select * from users where YEAR (adddate)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.