Best 20 experiences in MySQL database performance optimization

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 query cache does not enable $ r = mysql_query ("SELECT username FROM user WHERE signup_date> = CURDATE ()"); // enable query cache $ today = date ("Y-m-d"); $ r = mysql_query ("SELECT username FROM user WHERE signup_date> = '$ today '");

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)

// Inefficient: $ r = mysql_query ("SELECT * FROM user WHERE country = 'China'"); if (mysql_num_rows ($ r)> 0) {//...} // efficient: $ r = mysql_query ("SELECT 1 FROM user WHERE country = 'China' LIMIT 1"); if (mysql_num_rows ($ r)> 0) {//...}

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)

5. Use an equivalent type of example when joining a table and index it

If your application has many JOIN queries, you should confirm that the Join fields in the two tables are indexed. In this way, MySQL will launch a mechanism to optimize the Join SQL statement for you.

In addition, these fields used for Join should be of the same type. For example, if you want to Join a DECIMAL field with an INT field, MySQL cannot use their indexes. For those STRING types, the same character set is required. (The character sets of the two tables may be different)

// Find company $ r = mysql_query ("SELECT company_name FROM usersLEFT JOIN companies ON (users. state = companies. state) WHERE users. id = $ user_id "); // the two state fields should be indexed and of the same type and character set.

6. Never order by rand ()

Want to disrupt the returned data rows? Pick a random data? I really don't know who invented this method, but many new users like it. But you do not know how terrible the performance is.

If you really want to disrupt the returned data rows, you have N methods to achieve this purpose. This only causes an exponential decline in the performance of your database. The problem here is that MySQL will have to execute the RAND () function (which consumes CPU time), and this is to record rows for each row of records, and then sort them. Even if you use Limit 1, it will not help (because you want to sort)

The following example selects a random record.

// Do not do this: $ r = mysql_query ("SELECT username FROM user order by rand () LIMIT 1"); // This would be better: $ r = mysql_query ("SELECT count (*) FROM user"); $ d = mysql_fetch_row ($ r); $ rand = mt_rand (0, $ d [0]-1); $ r = mysql_query ("SELECT username FROM user LIMIT $ rand, 1 ");

7. Avoid SELECT *

The more data you read from the database, the slower the query. In addition, if your database server and WEB server are two independent servers, this will increase the network transmission load.

Therefore, you should develop a good habit of taking what you need.

// Not recommended $ r = mysql_query ("SELECT * FROM user WHERE user_id = 1"); $ d = mysql_fetch_assoc ($ r ); echo "Welcome {$ d ['username']}"; // recommended $ r = mysql_query ("SELECT username FROM user WHERE user_id = 1 "); $ d = mysql_fetch_assoc ($ r); echo "Welcome {$ d ['username']}";

8. Always set an ID for each table

We should set an ID for each table in the database as its primary key, and the best is an INT type (UNSIGNED is recommended), and set the AUTO_INCREMENT flag automatically added.

Even if your users table has a primary key field "email", you should not make it a primary key. When the VARCHAR type is used, the primary key performance decreases. In addition, in your program, you should use the table ID to construct your data structure.

In addition, some operations in the MySQL Data Engine require primary keys. In these cases, the performance and settings of primary keys become very important, such as clusters, partitions ......

Here, there is only one exception, that is, the "foreign key" of the "joined table". That is to say, the primary key of the table is composed by the primary keys of several individual tables. We call this a "foreign key ". For example, if a "student table" has a student ID and a "Course table" has a course ID, "Student table" is "Join table", which is associated with the student table and Course table, in the student ID table, the student ID and the course ID are both called "Foreign keys" to form a primary key.

9. Use ENUM instead of VARCHAR

The ENUM type is extremely fast and compact. In fact, it stores TINYINT, but its appearance is displayed as a string. In this way, it is quite perfect to use this field for some option lists.

If you have a field, such as "gender", "country", "nationality", "status", or "department", you know that the values of these fields are limited and fixed, then, you should use ENUM instead of VARCHAR.

MySQL also has a "suggestion" (see article 10) to tell you how to reorganize your table structure. When you have a VARCHAR field, we recommend that you change it to The ENUM type. You can get related suggestions using procedure analyse.

10. get advice from procedure analyse ()

Procedure analyse () will allow MySQL to help you analyze your fields and actual data, and give you some useful suggestions. Only when the table has actual data can these suggestions become useful, because to make some big decisions, we need data as the basis.

For example, if you create an INT field as your primary key, but there is not much data, procedure analyse () suggests that you change the field type to MEDIUMINT. Or you use a VARCHAR field. Because there is not much data, you may get a suggestion that you change it to ENUM. These suggestions may be due to insufficient data, so decision-making is not accurate enough.

In phpmyadmin, you can click "Propose table structure" when viewing the table to view these suggestions.

Be sure to note that these are just suggestions. These suggestions will become accurate only when there is more and more data in your table. Remember that you are the final decision maker.

• Three pages in total:
• Previous Page
• 1
• 2
• 3
• Next Page