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

Editor to share with you an example of mysql performance optimization, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

An article with a long history. I don't remember where it was recorded, but it's still useful.

When we design the database table structure, we all need to pay attention to the performance of the data operation when operating the database (especially the SQL statement when looking up the table). Here, we won't talk too much about the optimization of SQL statements, but only for MySQL, the database with the most Web applications. I hope the following optimization techniques are useful to you. (recommended course: MySQL tutorial)

1. Optimize your query for query caching

Most MySQL servers have query caching turned on. This is one of the most effective ways to improve performance, and it is handled by MySQL's database engine. When many of the same queries are executed multiple times, the query results are placed in a cache so that subsequent identical queries access the cached results directly without manipulating the table.

The main problem here is that it is easy for programmers to ignore. Because, some of our query statements will cause MySQL not to use caching. Take a look at the following example:

The code is as follows:

/ / do not enable query cache $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 above two SQL statements is CURDATE (), and MySQL's query cache has no effect on this function. Therefore, SQL functions like NOW () and RAND () or other such functions do not turn on query caching because the returns of these functions are variable. So, all you need is to replace the MySQL function with a variable to turn on the cache.

2. EXPLAIN your SELECT query

Use the EXPLAIN keyword to let you know how MySQL handles your SQL statements. This can help you analyze the performance bottlenecks of your query or table structure.

EXPLAIN's query results will also tell you how your index primary key is used and how your data tables are searched and sorted. Wait, wait.

Pick one of your SELECT statements (the most complex one with multiple table joins is recommended) and add the keyword EXPLAIN to the front. You can use phpmyadmin to do this. Then you will see a form. In the following example, we forgot to add the group_id index and have a table join:

When we index the group_id field:

We can see that the former result shows that 7883 rows are searched, while the latter only searches the 9 and 16 rows of the two tables. Looking at the rows column allows us to find potential performance problems.

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

Sometimes when you look up the table, you already know that the result will have only one result, but because you may need to go to the fetch cursor, or you may check the number of records returned.

In this case, adding LIMIT 1 can increase performance. In the same way, the MySQL database engine stops searching after finding a piece of data, rather than continuing to look back for the next piece of data that matches the record.

The following example is just to find out if there are any users of "China". Obviously, the latter one will be more efficient than the previous one. (please note that the first one is Select * and the second is Select 1)

The code is as follows:

/ / 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. Index the search field

An index is not necessarily a primary key or a unique field. If there is a field in your table that you will always use to search, please index it.

From the picture above, you can see the search string "last_name LIKE'a%". One is indexed, the other is no index, and the performance is about 4 times worse.

In addition, you should also need to know what kind of search can not use the normal index. For example, when you need to search for a word in a large article, such as "WHERE post_content LIKE'% apple%'", the index may be meaningless. You may need to use a MySQL full-text index or create your own index (for example, search keywords or Tag)

5. Use a fairly type of example in the Join table and index it

If your application has a lot of JOIN queries, you should make sure that the Join fields in both tables are indexed. In this way, MySQL will start the mechanism to optimize the SQL statement of Join for you.

Moreover, these fields used for Join should be of the same type. For example, if you want to Join the DECIMAL field with an INT field, MySQL will not be able to use their indexes. For those STRING types, you also need to have the same character set. (the character sets of the two tables may not be the same)

The code is as follows:

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

6. Never ORDER BY RAND ()

Want to disrupt the returned rows of data? Pick a data at random? I don't know who invented this usage, but many beginners like it very much. But you really don't understand the terrible performance problems of doing so.

If you really want to mess up the returned rows of data, there are N ways to do this. Using this only degrades the performance of your database exponentially. The problem here is that MySQL will have to execute the RAND () function (which takes a lot of CPU time), and this is for each row to be recorded and then sorted. Even if you use Limit 1, it won't help (because you have to sort)

The following example is to pick a record at random

The code is as follows:

/ never do this: $r = mysql_query ("SELECT username FROM user ORDER BY RAND () LIMIT 1"); / / it would be better: $r = mysql_query ("SELECT count (*) FROM user"); $d = mysql_fetch_row ($r); $rand = mt_rand (0rand [0]-1); $r = mysql_query ("SELECT username FROM user LIMIT $rand, 1")

7. Avoid SELECT *

The more data is read from the database, the slower the query becomes. And, if your database server and WEB server are two separate servers, it will also increase the load of network traffic.

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

The code is as follows:

/ / 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 as its primary key for each table in the database, and preferably an int type (UNSIGNED is recommended), and set the auto-added AUTO_INCREMENT flag.

Even if your users table has a field with a primary key called "email", don't let it be the primary key. Using the VARCHAR type as the primary key degrades performance. In addition, in your program, you should use the ID of the table to construct your data structure.

Moreover, under the MySQL data engine, there are some operations that require the use of primary keys, in which case, 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 "associated table", that is, the primary key of this table is formed by the primary key of several individual tables. We call this situation "foreign keys". For example, if there is a "student table" with a student's ID, and a "curriculum schedule" with a course ID, then the "score sheet" is a "related table", which is related to the student table and the curriculum schedule. In the grade table, the student ID and the course ID are called "foreign keys" which together form the primary key.

9. Use ENUM instead of VARCHAR

The ENUM type is very fast and compact. In fact, it holds TINYINT, but it appears as a string on the outside. In this way, it is perfect to use this field to make a list of options.

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

MySQL also has a "suggestion" (see Article 10) on how to reorganize your table structure. When you have a VARCHAR field, this proposal will tell you to change it to ENUM. Using PROCEDURE ANALYSE (), you can get relevant suggestions.

10. Get advice from PROCEDURE ANALYSE ()

PROCEDURE ANALYSE () will ask MySQL to help you analyze your fields and their actual data, and will give you some useful advice. These suggestions become useful only if there is actual data in the table, because data is needed to make some big decisions.

For example, if you create an INT field as your primary key, but there is not much data, PROCEDURE ANALYSE () will advise you to change the type of this field to MEDIUMINT. Or if you use a VARCHAR field, because there is not much data, you may get a suggestion to change it to ENUM. These suggestions may not be accurate enough because there is not enough data.

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

It is important to note that these are just suggestions, and they will become accurate only when you have more and more data in your table. It is important to remember that you are the one who makes the final decision.

11. Use NOT NULL whenever possible

Unless you have a very special reason to use NULL values, you should always keep your fields NOT NULL. This seems to be a little controversial. Please read on.

First of all, ask yourself what's the difference between "Empty" and "NULL" (if it's INT, it's 0 and NULL). If you think there is no difference between them, then you should not use NULL. Do you know? In Oracle, the strings of NULL and Empty are the same!)

Don't assume that NULL doesn't need space, it needs extra space, and your program will be more complex when you make comparisons. Of course, this is not to say that you can't use NULL, the reality is very complicated, there will still be some cases, you need to use null values.

The following is an excerpt from MySQL's own documentation:

"NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte."

12. Prepared Statements

Prepared Statements, much like a stored procedure, is a collection of SQL statements that run in the background, and we can get a lot of benefits from using prepared statements, whether it's performance or security issues.

Prepared Statements can check some of the variables you bind to protect your program from "SQL injection" attacks. Of course, you can also check your variables manually, however, manual checks are prone to problems and are often forgotten by programmers. This problem is better when we use some framework or ORM.

In terms of performance, this will give you a considerable performance advantage when the same query is used multiple times. You can define some parameters for these Prepared Statements, and MySQL will only parse it once.

Although the latest version of MySQL uses binary to transmit Prepared Statements, this makes network transmission very efficient.

Of course, there are cases where we need to avoid using Prepared Statements because it does not support query caching. But it is said that version 5.1 is supported.

To use prepared statements in PHP, you can check its manual: mysqli extension or database abstraction layer, such as PDO.

The code is as follows:

/ / create prepared statementif ($stmt = $mysqli- > prepare ("SELECT username FROM user WHERE state=?")) {/ / bind parameters $stmt- > bind_param ("s", $state); / / execute $stmt- > execute (); / / bind result $stmt- > bind_result ($username); / / Mobile cursor $stmt- > fetch (); printf ("% s is from% s\ n", $username, $state); $stmt- > close ();}

13. Unbuffered query

Normally, when you execute a SQL statement in your script, your program will stop there until no SQL statement returns, and then your program will continue to execute. You can use unbuffered queries to change this behavior.

There is a very good note about this in the PHP documentation: the mysql_unbuffered_query () function:

Mysql_unbuffered_query () sends the SQL query query to MySQL without automatically fetching and buffering the result rows as mysql_query () does. This saves a considerable amount of memory with SQL queries that produce large result sets, and you can start working on the result set immediately after the first row has been retrieved as you don't have to wait until the complete SQL query has been performed.

The above sentence translates that mysql_unbuffered_query () sends a SQL statement to MySQL instead of automatically fethch and caching the results as mysql_query () does. This can save a lot of memory, especially those queries that produce a lot of results, and you don't have to wait for all the results to be returned, just the first row of data. you can start working on the query results right away.

However, there will be some limitations. Because you either read all the lines, or you need to call mysql_free_result () to clear the results before making the next query. Also, mysql_num_rows () or mysql_data_seek () will not be available. Therefore, you need to think carefully about whether to use unbuffered queries.

14. Save the IP address as UNSIGNED INT

Many programmers create a VARCHAR (15) field to hold the string IP instead of the shaping IP. If you use shaping to store, it only takes 4 bytes, and you can have fixed-length fields. Moreover, it will give you an advantage in query, especially if you need to use the WHERE condition: IP between ip1 and ip2.

We have to use UNSIGNED INT because the IP address uses the entire 32-bit unsigned shaping.

For your query, you can use INET_ATON () to convert a string IP into an integer and INET_NTOA () to convert an integer into a string IP. In PHP, there are also functions like ip2long () and long2ip ().

1$ r = "UPDATE users SET ip = INET_ATON ('{$_ SERVER ['REMOTE_ADDR']}') WHERE user_id = $user_id"

15. Fixed length watches will be faster.

If all the fields in the table are "fixed length", the entire table is considered "static" or "fixed-length". For example, there are no fields of the following type in the table: VARCHAR,TEXT,BLOB. As long as you include one of these fields, the table is not a "fixed-length static table", so the MySQL engine will handle it in a different way.

Fixed-length tables improve performance because MySQL searches faster, and because these fixed lengths are easy to calculate the offset of the next data, they are naturally read quickly. If the field is not fixed in length, then every time you want to find the next one, you need the program to find the primary key.

Also, fixed-length tables are easier to cache and rebuild. However, the only side effect is that fixed-length fields waste some space, because fixed-length fields allocate so much space whether you use them or not.

Using the "vertical split" technique (see next), you can split your watch into two, one with fixed length and the other with variable length.

16. Vertical segmentation

"Vertical splitting" is a method of turning tables in a database into several tables according to columns, which can reduce the complexity of tables and the number of fields, thus achieving the purpose of optimization. (in the past, I worked on a project in a bank and saw a table with more than 100 fields, which was terrifying.)

Example 1: there is a field in the Users table that is a home address. This field is optional, compared to, and you do not need to read or rewrite this field frequently except for your personal information when you are operating in the database. So why not put him on another table? This will make your table have better performance, let's think about it, a large number of times, I for the user table, only the user ID, user name, password, user role and so on will be often used. Smaller watches always have good performance.

Example 2: you have a field called "last_login" that is updated every time the user logs in. However, each update causes the query cache for the table to be emptied. So, you can put this field in another table so that it doesn't affect your constant reading of the user's ID, user name, and user role, because the query cache will add a lot of performance to you.

In addition, you need to note that the tables formed by these separated fields, you will not Join them regularly, otherwise, the performance will be even worse than when it is not split, and it will be extremely degraded.

17. Split large DELETE or INSERT statements

If you need to perform a large DELETE or INSERT query on an online site, you need to be very careful not to stop your entire site from responding. Because these two operations will lock the table, once the table is locked, other operations can not come in.

Apache will have many child processes or threads. Therefore, it works quite efficiently, and our server does not want to have too many child processes, threads and database links, which takes up a lot of server resources, especially memory.

If you lock your table for a period of time, say 30 seconds, then for a site with high traffic, the accumulated access processes / threads, database links, and the number of files opened in those 30 seconds may not only allow you to park the WEB service Crash, but also cause your entire server to crash immediately.

So, if you have a big deal, you must split it, using the LIMIT condition is a good way. Here is an example:

The code is as follows:

While (1) {/ / only do 1000 mysql_query at a time ("DELETE FROM logs WHERE log_date

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.