Some experience in MySQL performance optimization

Optimize your queries for queries

Most MySQL servers have query caching enabled. This is one of the most effective ways to improve performance, and it's handled by the MySQL database engine. When many of the same queries are executed multiple times, these query results will be placed in a cache, so that subsequent identical queries do not need to operate the table but directly access the cached results.

The main problem here is that for programmers, this matter is easily overlooked. Because some of our query statements will cause MySQL not to use the cache. Please look at the following example:

// 查询缓存不开启
$r = mysql_query("SELECT username FROM user WHERE     signup_date >= CURDATE()");
// 开启查询缓存
$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(). MySQL's query cache does not work on this function. Therefore, SQL functions like NOW() and RAND() or other similar functions will not enable query caching, because the returns of these functions are volatile. So, all you need is to replace the MySQL function with a variable to enable caching.

Learn to use EXPLAIN

Using the EXPLAIN keyword can let you know how MySQL processes your SQL statement.

select id, title, cate from news where cate = 1

If you find that the query is slow, then add an index on the cate field, it will speed up the query

Use LIMIT 1 when there is only one row of data

Sometimes you only need one piece of data when querying the table, so please use limit 1.

Use indexes correctly

Indexes are not necessarily for primary keys or unique fields. If there is a field in your table that you will always use for searches, photos, and conditions, then please create an index for it.

Don’t ORDER BY RAND()

A very inefficient random query.

Avoid SELECT *

The more data is read from the database, the slower the query will become. Moreover, if your database server and WEB server are two independent servers, this will also increase the load of network transmission. You must develop a good habit of taking whatever you need.

Use ENUM instead of VARCHAR

The ENUM type is very fast and compact. In fact, it holds a TINYINT, but it appears as a string. In this way, it becomes quite perfect to use this field to make some choice lists.

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

Using NOT NULL

Unless you have a very specific reason to use NULL values, you should always keep your fields NOT NULL. This may seem a bit controversial, please read on.

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

Don’t think that NULL does not require space, it requires additional space, and when you compare, your The procedure will be more complex. Of course, this does not mean that you cannot use NULL. The reality is very complicated, and there will still be situations where you need to use NULL values.

The following is excerpted 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.”

IP address is stored as UNSIGNED INT

Many programmers will create a VARCHAR(15) field to store the IP in the form of a string instead of an integer IP. If you use an integer to store it, it only takes 4 bytes, and you can have fixed-length fields. Moreover, this will bring you advantages in querying, especially when you need to use WHERE conditions like this: IP between ip1 and ip2.

We must use UNSIGNED INT, because the IP address will use the entire 32-bit unsigned integer

A fixed-length table will be faster

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

Fixed length tables will improve performance because MySQL will search faster. Because these fixed lengths make it easy to calculate the offset of the next data, reading will naturally be faster. . And if the field is not of fixed length, then every time you want to find the next one, the program needs 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 require so much space regardless of whether you use them or not.

vertical split

"Vertical splitting" is a method of turning the tables in the database into several tables by columns, which can reduce the complexity of the table and the number of fields, thereby achieving optimization purposes. It should be noted that you will not join the tables formed by these separated fields frequently. Otherwise, the performance will be worse than when not divided, and it will be an extreme drop. .

Split a large DELETE or INSERT statement

If you execute a large DELETE or INSERT query on an online website, you need to be very careful to avoid The operation causes your entire website to stop responding. Because these two operations will lock the table, once the table is locked, no other operations can enter.

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. This takes up a lot of server resources, especially memory.

If you lock your table for a period of time, such as 30 seconds, then for a site with a high number of visits, the access processes/threads accumulated in these 30 seconds, database links, and open The number of files may not only cause your WEB service to crash, but may also cause your entire server to hang up immediately.

Smaller columns will be faster

For most database engines, hard disk operations may be the most significant bottleneck. So, making your data compact can be very helpful in this situation because it reduces access to the hard drive.

Choose the right storage engine

There are two storage engines in MySQL, MyISAM and InnoDB. Each engine has advantages and disadvantages.

MyISAM is suitable for applications that require a large number of queries, but it is not very good for a large number of write operations. Even if you just need to update a field, the entire table will be locked, and other processes, even the reading process, cannot operate until the reading operation is completed. In addition, MyISAM is extremely fast for calculations such as SELECT COUNT(*).

The trend of InnoDB will be a very complex storage engine, and for some small applications, it will be slower than MyISAM. The other reason is that it supports "row locking", so it will be better when there are more write operations. Moreover, it also supports more advanced applications, such as transactions.

The above is the detailed content of Some experience in MySQL performance optimization. For more information, please follow other related articles on the PHP Chinese website!