MySQL table design optimization and indexing

The design of MySQL index can make your database fly, greatly improve the efficiency of the database. There are a few things to note when designing a MySQL index:

　1. Create an index

Indexing is especially important for queries as a major application. Most of the time the performance problem is simply because we forgot to add the index, or we didn't add a more efficient index. If you do not add

Index, then look for any even just a specific data will be a full table scan, if a table of large amounts of data and meet the conditions of the result is very small, then no index will cause fatal performance

Drop. But it is not always necessary to build an index, such as the gender may only have two values, the index not only has no advantage, but also affect the update speed, which is called an excessive index.

　2. Composite Index

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

If we were to create a single index on area and age, because the MySQL query could only use one index at a time, the full table scan was a lot more effective when it was relatively non indexed

Rate, but creating a composite index on an area, an age two column will result in greater efficiency. If we create an area, an age,

Salary), it is actually equivalent to creating (Area,age,salary), (Area,age), (area) Three indexes, which is called the best left prefix

Characteristics. So when we create a composite index, we should place the columns that are most commonly used as constraints to the left, decreasing in descending order.

　　3. The index does not contain columns with null values

This column is not valid for this composite index as long as the column contains null values that will not be included in the index, as long as one column in the composite index contains null values. So we don't want the default value of the field to be null when designing the database.

　　4. Using Short Index

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

　　5. Indexing issues for sorting

The MySQL query uses only one index, so the columns in the order by are not indexed if the index is already used in the WHERE clause. Therefore, do not use sort operations when the database default sort meets the requirements, and try not to include sorting of multiple columns, preferably if you need to create a composite index for these columns.

　6.like statement operation

It is generally discouraged to use like operations, and how to use them is also a problem if not used. Like "%aaa%" does not use indexes and like "aaa%" can use indexes.

　7. Do not perform operations on columns

SELECT * from Users where

Year (adddate)

　　8. Do not use not in and operation

None in and operations do not use the index to perform a full table scan. Not in can be substituted by not exists, ID3 can use id>3 or ID