MySQL performance optimization scheme

2. $stmt, execute();
6. //Bind result
8. $stmt-Bind_result($username);
12. //Move cursor
14. Fetch ($stmt);
18. printf("%s is from%s\n", $username, $state);
22. $stmt-close();
24. }

Copy Code

13. Non-buffered queries

Normally, when you execute an SQL statement in your script, your program will stop there until the SQL statement is returned, and your program continues to execute. You can use unbuffered queries to change this behavior.

In this case, there is a very good description in the PHP Documentation: Mysql_unbuffered_query () function:

1. “Mysql_unbuffered_query()Sends the SQL query query toMysqlWithout automatically fetchingandBuffering the result rowsAsMysql_query()Does. This saves a considerable amount of the memory with the SQL queries that produce large result sets, C8>and can start working on the result set immediately after the first row have been retrieved As you do have a to -wait until the complete SQL query with been performed. "

Copy Code

The above sentence translates to say that mysql_unbuffered_query () sends an SQL statement to MySQL instead of automatically fethch and caches the results like mysql_query (). This can save a lot of considerable memory, especially those that produce a lot of results, and you don't have to wait until all the results are returned, and you can start working on the query results as soon as the first row of data is returned.

However, there are some limitations. Because you either read all the lines, or you want to call Mysql_free_result () to clear the results before making the next query. Also, mysql_num_rows () or Mysql_data_seek () will not work. So, you need to think carefully about whether to use unbuffered queries.

14. Save IP Address as UNSIGNED INT

Many programmers create a VARCHAR (15) field to hold IP in the form of a string rather than a shaped IP. If you use plastic to store it, you only need 4 bytes, and you can have a fixed-length field. And, this will bring you the advantage of querying, especially when you need to use such a where condition: IP between Ip1 and IP2.

We need to use unsigned INT, because the IP address uses an entire 32-bit unsigned shaping.

and your query, you can use Inet_aton () to turn a string IP into an integer and use Inet_ntoa () to turn a shape into a string IP. In PHP, there are also functions such as Ip2long () and Long2ip ().

1. $r = "UPDATE users SET IP = Inet_aton (' {$_server[' remote_addr ']} ') WHERE user_id = $user _id";

Copy Code

15. Fixed-length tables are 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 can improve performance because MySQL searches faster because these fixed lengths are easy to calculate the offset of the next data, so the nature of reading will be fast. And if the field is not fixed, then every time you want to find the next one, you need the program to find the primary key.

Also, fixed-length tables are more likely to be cached and rebuilt. However, the only side effect is that a fixed-length field wastes some space, because the field is set to allocate so much space whether you use it or not.

Using the "vertical split" technique (see the next one), you can split your table into two that are fixed-length and one that is indefinite.

16. Vertical segmentation
"Vertical Segmentation" is a method of turning a table in a database into several tables, which reduces the complexity of the table and the number of fields for optimization purposes. (Previously, in a bank project, saw a table with more than 100 fields, very scary)

Example one: One of the fields in the Users table is the home address, which is an optional field, and you do not need to read or rewrite this field frequently in addition to your personal information when working in a database. So, why not put him in another table? This will make your table better performance, we think is not, a lot of time, I for the user table, only the user ID, user name, password, user role, etc. will be used frequently. A smaller table will always have good performance.

Example two: You have a field called "Last_login" that will be updated every time the user logs in. However, each update causes the table's query cache to be emptied. So, you can put this field in another table, so that you do not affect the user ID, user name, user role of the constant read, because the query cache will help you to add a lot of performance.

In addition, you need to note that these separated fields form the table, you do not regularly join them, otherwise, this performance will be worse than not split, and, it will be a drop of magnitude.

17. Splitting a large DELETE or INSERT statement
If you need to perform a large DELETE or INSERT query on an online website, you need to be very careful to avoid your actions to keep your entire site from stopping accordingly. Because these two operations will lock the table, the table is locked, the other operations are not in.

Apache will have a lot of child processes or threads. So, it works quite efficiently, and our servers don't want to have too many child processes, threads and database links, which is a huge amount of server resources, especially memory.

If you lock your watch for a period of time, say 30 seconds, for a site with a high level of access, the 30-second cumulative number of access processes/threads, database links, and open files may not only allow you to park the Web service crash, but may also leave your entire server hanging up.

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

2. While (1) {
4. //Do only 1000 strips at a time
6. Mysql_query("DELETE from logs WHERE log_date <= ' 2009-11-01 ' LIMIT");
8. if (mysql_affected_rows() = = 0) {
10. //don't have to delete, quit!
12. break;
14. }
16. //Take a break every time
18. Usleep(50000);
20. }

Copy Code

18. The smaller the column the faster

For most database engines, hard disk operations can be the most significant bottleneck. So it's very helpful to have your data compact, because it reduces access to the hard drive.

See MySQL documentation Storage Requirements View all data types.

If a table has only a few columns (for example, a dictionary table, a configuration table), then we have no reason to use INT to master the keys, using Mediumint, SMALLINT or smaller TINYINT will be more economical. If you don't need to record time, using date is much better than DATETIME.

Of course, you also need to leave enough space for expansion, otherwise, you do this later, you will die very difficult to see, see Slashdot example (November 06, 2009), a simple ALTER TABLE statement took 3 hours, because there are 16 million data.

19. Choose the right storage engine

There are two storage engines MyISAM and InnoDB in MySQL, each with a few pros and cons. Cool Shell before the article "Mysql:innodb or MyISAM?" Discussion and this matter.

MyISAM is suitable for applications that require a large number of queries, but it is not very good for a lot of write operations. Even if you just need to update a field, the entire table will be locked and other processes will be unable to manipulate the read process until the read operation is complete. In addition, MyISAM's calculations for SELECT COUNT (*) are extremely fast.

The InnoDB trend will be a very complex storage engine, and for some small applications it will be slower than MyISAM. He is it supports "row lock", so in the writing operation more time, will be more excellent. Also, he supports more advanced applications, such as: transactions.

Here's the MySQL manual.
target= "_blank" MyISAM Storage Engine
InnoDB Storage Engine
20. Using an Object-relational mapper (relational Mapper)

With ORM (Object relational Mapper), you can gain reliable performance gains. All the things an ORM can do, can be written manually. However, this requires a senior expert.

The most important thing about ORM is "Lazy Loading", that is to say, only when the need to take the value of the time to really do. But you also need to be careful about the side-effects of this mechanism, because this is likely to degrade performance by creating many, many small queries.

ORM can also package your SQL statements into a single transaction, which is much faster than executing them alone.

Currently, the personal favorite of PHP's ORM is: Doctrine.

21. Be careful with "permalink"

The purpose of the permanent link is to reduce the number of times the MySQL link is recreated. When a link is created, it will always be in a connected state, even if the database operation is finished. And since our Apache has started reusing its child processes-that is, the next HTTP request will reuse Apache's subprocess and reuse the same MySQL link.
PHP Manual: Mysql_pconnect ()

In theory, this sounds very good. But from personal experience (and most people), this function creates more trouble. Because, you only have limited number of links, memory problems, file handles, and so on.

And, Apache runs in an extremely parallel environment, creating a lot of processes. This is why this "permanent link" mechanism is not working well. Before you decide to use permanent link, you need to think about the architecture of your entire system.
Reprinted from: Cool Shell net

MySQL performance optimization scheme