Shulou(Shulou.com)05/31 Report--

This article mainly introduces the points for attention in the use of the mysql index, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

The optimization of mysql can be carried out from the aspects of hardware equipment selection, operating system, database structure design, SQL query and application program. Here, it is optimized only from the aspects of database design and query statements.

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 there is no indexing, then finding even a specific piece of data will do a full table scan, 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 that without indexing, it will be more efficient if we create a composite index 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 will 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 index

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 operation of "like" sentence

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 operate on the column

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.