Mysql database is the most popular relational database management system, but all databases inevitably exist when the use of low performance, that for mysql database, how to optimize it? Here we’ve rounded up 8 optimization tips to check out.

1. Create an index

Indexes are especially important in applications where queries dominate. Many times performance problems are simply caused by forgetting to add an index, or not adding a more efficient index. Without indexing, any search for even a specific piece of data will result in a full table scan, and if a table has a large amount of data and few qualifying results, not indexing can cause fatal performance degradation. However, not all cases need to be indexed. For example, there may be only two values for gender. Indexing not only has no advantage, but also can affect the update speed, which is called over-indexing.

2. Composite indexes

Select * from users where area=’ Beijing ‘and age=22; Mysql > create index (area, age); mysql > create index (age, area, age); mysql > create index (age, area, age); If we create a composite index of (area,age,salary), then we actually create three indexes of (area,age,salary), (area,age), and (area), which is called the optimal left prefix feature. Therefore, when creating a composite index, we should place the columns most commonly used as constraints at the far left, descending in order.

Index does not contain columns with NULL values

Any column that contains a NULL value will not be included in the index, and any column in the composite index that contains a NULL value is invalid for the composite index. So we don’t want the default value of the field to be NULL when we design the database.

4. Use short indexes

Index the string, specifying a prefix length if possible. For example, if you have a CHAR(255) column, do not index the entire column if multiple 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.

Mysql queries use only one index, so if the WHERE clause already uses an index, the order by column does not use an index. Therefore, the database default sort can meet the requirements of the situation do not use the sort operation; Try not to include more than one column sort, and create composite indexes for those columns if necessary.

6, like statement operation

The use of the like operation is generally discouraged, and it is also a question of how to use it if you must. Like “%aaa%” does not use indexes whereas like “aaa%” can use indexes.

7. Do not perform operations on columns

select * from users where YEAR(adddate)<2007; Select * from users where adddate< ‘2007-01-01’; select * from users where adddate<‘ 2007-01-01’;

8, do NOT use NOT IN and <> operations

Neither the NOT IN nor <> operations use indexes and will perform a full table scan. NOT IN can be replaced by NOT EXISTS. Id <>3 can be replaced by id>3 or ID <3.

There are 8 ways to optimize a Mysql database: create indexes, compound indexes, indexes that do NOT contain columns with NULL values, use short indexes, sorting index problems, like operations, do NOT perform operations on columns, and do NOT use NOT IN and <> operations.