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

The following is mainly to bring you to optimize MySQL performance details, I hope these words can bring you practical use, this is also the main purpose of my editing this article. Okay, no more nonsense, let's just look at the following. Most MySQL Cloud Virtual Machine have query caching enabled. This is one of the most effective ways to improve performance and 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 queries of the same query can access the cached results without manipulating the table.

The main problem here is that it's easy for programmers to ignore this. Because, some of our query statements make MySQL not use caching. Consider the following example:

//query cache not turned on $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");//Open 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(), MySQL's query cache does not work for this function. Therefore, SQL functions like NOW() and RAND(), or others like them, do not turn on query caching because their returns are variable. So, all you need is to replace MySQL's function with a variable to turn on caching.

Learn to use EXPLAIN

Use the EXPLAIN keyword to let you know how MySQL handles your SQL statements.

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

Finding the query slow and then adding an index to the cate field speeds up the query

LIMIT 1 is used when only one row of data is needed

Use limit 1 when you query tables and sometimes only need one item.

Proper use of indexes

Indexes are not necessarily given to primary keys or unique fields. If there's a field in your table that you're going to use frequently to search, snap, or condition, index it.

DO NOT ORDER BY RAND()

An inefficient random query.

Avoid SELECT *

The more data is read from the database, the slower the query becomes. Also, if your database server and WEB server are two separate servers, this will increase the network traffic load. A good habit of taking what is needed must be formed.

Use ENUM instead of VARCHAR

ENUM types are very fast and compact. In reality, it holds TINYINT, but it appears as a string. This makes it perfect to use this field to make lists of options.

If you have a field such as Gender, Country, Ethnicity, Status, or Department, and you know that these fields have finite and fixed values, you should use ENUM instead of VARCHAR.

use NOT NULL

Unless you have a very specific reason to use NULL values, you should always leave your fields NOT NULL. This may seem controversial, but look down.

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

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

The following 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. "

IP address saved as UNSIGNED INT

Many programmers will create a VARCHAR(15) field to store IP as a string rather than a plastic IP. If you use an integer, it only takes 4 bytes, and you can have fixed-length fields. Also, this gives you a query advantage, especially if you need to use WHERE conditions such as IP between ip1 and ip2.

We have to use UNSIGNED INT because IP addresses use the entire 32-bit unsigned shape.

Fixed length tables are faster

If all fields in a table are "fixed-length," the entire table is considered "static" or "fixed-length." For example, the table does not have fields of the following types: VARCHAR, TEXT, BLOB. As long as you include one of these fields, the table is not a "fixed-length static table," and the MySQL engine handles it differently.

Fixed-length tables improve performance because MySQL searches faster, and because these fixed-length tables are easy to calculate the offset of the next data, they read quickly. If the fields are not fixed-length, the program needs to find the primary key each time it wants to find the next one.

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

vertical split

Vertical partitioning is a method of dividing a table into several tables in a database by column, thus reducing the complexity of the table and the number of fields for optimization purposes. Note that the tables formed by these separated fields are not often joined, otherwise the performance will be worse than that without separation, and it will be extremely degraded.

Split large Delete or INSERT statements

If you perform a large Delete or INSERT query on an online site, you need to be very careful not to cause your entire site to stop responding. Because these two operations will lock the table, the table is locked, and other operations cannot enter.

Apache has many child processes or threads. So, it works quite efficiently, and our server doesn't want too many child processes, threads, and database links, which is a huge drain on 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 number of access processes/threads, database links, and open files in those 30 seconds may not only cause you to crash the WEB service, but may also cause your entire server to hang immediately.

The smaller the column, the faster.

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

Choosing the right storage engine

There are two storage engines in MySQL, MyISAM and InnoDB, each with its pros and cons.

MyISAM is good for applications that require a lot of queries, but it is not good for writing a lot. Even if you need to update just one field, the entire table will be locked, and other processes, even the read process, will not be able to operate until the read operation is completed. MyISAM is also super fast for calculations like SELECT COUNT(*).

InnoDB tends to be a very complex storage engine, slower than MyISAM for small applications. He is it supports "row lock", so when writing more operations, it will be better. It also supports more advanced applications such as transactions.

For the above details on optimizing MySQL performance, do you think it is very helpful? If you need to know more, please continue to pay attention to our industry information, I believe you will like these contents.

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.