21 best practices for MySQL performance optimization (1)

Today, database operations are increasingly becoming the performance bottleneck of the entire application, especially for Web applications. Concerning the database performance, this is not just something that DBAs need to worry about, but it is something that our programmers need to pay attention. When designing the database table structure and operating the database (especially the SQL statements used in table queries), we need to pay attention to the performance of data operations. Here, we will not talk about the optimization of many SQL statements, but only for the database with the most Web application MySQL. I hope the following optimization techniques will be useful to you.

1. Optimize Your query for the query Cache

Query cache is enabled on most MySQL servers. This is one of the most effective ways to improve performance, and it is processed by the MySQL database engine. When many identical queries are executed multiple times, these query results are stored in a cache, the cache results are directly accessed for the same query in the future without having to operate the table.

The main problem here is that this is easy for programmers to ignore. Because some of our query statements will make MySQL not use cache. See the following example:

The difference between the preceding two SQL statements is CURDATE (). The query cache of MySQL does not work for this function. Therefore, SQL functions such as NOW (), RAND (), and other such functions do not enable the query cache, because the returned results of these functions are variable. Therefore, all you need is to use a variable to replace the MySQL function and enable the cache.

2. EXPLAIN your SELECT query

The EXPLAIN keyword helps you know how MySQL processes your SQL statements. This helps you analyze the performance bottleneck of your query statement or table structure.

The EXPLAIN query results also show you how your index primary key is used and how your data tables are searched and sorted ...... And so on.

SELECT one of your SELECT statements (we recommend that you SELECT the most complex one with multi-table join) and add the keyword "EXPLAIN" to the front. You can use phpmyadmin to do this. Then, you will see a table. In the following example, we forget to add the group_id index and have table join:

After adding an index to the group_id field:

We can see that the previous result shows that 7883 rows are searched, and the last one only searches 9 and 16 rows of two tables. Viewing the rows column allows us to find potential performance problems.

3. Use LIMIT 1 when only one row of data is required

When you query a table, you know that only one result is returned, but you may need to fetch the cursor or check the number of returned records.

In this case, adding LIMIT 1 can increase performance. In this way, the MySQL database engine will stop searching after finding a piece of data, rather than continuing to query the next piece of data that matches the record.

The example below is just to find out if there are "China" users. Obviously, the latter will be more efficient than the former one. (Note that Select * is the first and Select 1 is the second)

4. Create an index for a search field

An index is not necessarily a primary key or a unique field. If a field in your table is always used for search, create an index for it.

You can see the search string "last_name LIKE 'a % '". One is an index, the other is no index, and the performance is about 4 times worse.

In addition, you should also know what kind of search cannot use normal indexes. For example, if you need to search for a word in a large article, for example, "WHERE post_content LIKE '% apple %'", the index may be meaningless. You may need to use MySQL full-text indexes or make an index yourself (for example, search for keywords or tags)