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

Designing the index of MySql can make the ni database fly and greatly improve the database effectiveness. Here are a few things to note when designing an MySql index:

1. Create an index

For the important utilization of query, the index is particularly important. In many cases, performance problems are simply caused by wo forgetting to add indexes, or not adding more efficient claims. If it is not indexed, then 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 a fatal performance degradation. But it is not always necessary to build an index, for example, gender may only have two values, 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 wo creates a single index separately on area and age, because mysql queries can only use one index at a time, although this has improved the effectiveness of full table scanning compared to not indexing, it will be more effective if a composite index is created on area and age columns. If the wo created a composite index of (area,age,salary), then it is 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, wo should put the column that is most commonly used as a constraint on the far left, decreasing in turn.

3, the index does not include columns with null values

As long as a null value is included in the column, it will not be included in the index, and as long as one column in the composite index contains a null value, then this column is invalid for the composite index. So wo should 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 query speed, but also save disk space and Icano 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, the database default sort can meet the request situation, do not use the sort operation; try not to include multiple column sorting, if necessary, it is best to create a composite index for these columns.

6 operation of "like" sentence

In general, the use of like operation is not encouraged, if it must be used, how to use it is also a problem, video website. 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.