Abstract: MySQL performance optimization means to make MySQL run faster and save resources by reasonably arranging resources and adjusting system parameters. MySQL performance optimization includes query speed optimization, update speed optimization, MySQL server optimization and so on.
preface
MySQL performance optimization can make MySQL run faster and save resources by reasonably arranging resources and adjusting system parameters. MySQL performance optimization includes query speed optimization, update speed optimization, MySQL server optimization and so on. Here, the following optimizations are introduced, including: performance optimization, query optimization, database structure optimization, MySQL server optimization.
MySQL optimization, on the one hand, is to find out the bottlenecks of the system and improve the overall performance of MySQL database; on the other hand, reasonable structural design and parameter adjustment are needed to improve the speed of user operation response. At the same time, the system resources should be saved as much as possible so that the system can provide a larger load of services. MySQL database optimization is multi-faceted, the principle is to reduce the bottleneck of the system, reduce the occupation of resources, increase the speed of system response.
1. 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 is handled by MySQL’s database engine. When many identical queries are executed multiple times, the results of these queries are placed in a cache so that subsequent identical queries can access the cached results without manipulating the table.
The main problem here is that it’s very easy for programmers to overlook this. Because some of our queries will make MySQL not use the cache. Take a 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(), which MySQL’s query cache does not use. Therefore, SQL functions such as NOW() and RAND(), or other such functions, do not enable query caching, because the return of these functions is variable. So, all you need to do is replace the MySQL function with a variable to enable caching.
2, Explain your SELECT query
Using the EXPLAIN keyword lets you see how MySQL processes your SQL statements.
For queries that have table associations, such as:
select username, group_namefrom users ujoins groups g on (u.group_id = g.id)
Finding the query slow and then adding an index to the GROUP_ID field speeds up the query
3. Use LIMIT 1 when only one row is needed
Sometimes when you query a table, you already know that the result will only have one result, simply because you may need to fetch the cursor, or you may check the number of records returned. In such cases, adding LIMIT 1 can increase performance. In this way, the MySQL database engine stops searching when it finds one piece of data, rather than moving on to find the next piece of data that matches the record. The following example, just to see if there is a “Chinese” user, is obviously more efficient than the previous one. (Note that the first is SELECT *, the second is SELECT 1)
$r = MySQL_query(“SELECT * FROM user WHERE country = ‘China'”); if (MySQL_num_rows($r) > 0) { // … $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 does not have to be a primary key or a unique field. If there’s a field in your table that you’re going to be searching for a lot, index it.
Join a table with columns of the same type and index them
If your application has many JOIN queries, you should make sure that the JOIN fields in both tables are indexed. In this way, MySQL internally initiates the mechanism to optimize the SQL statement of the JOIN for you. Also, the fields to be joined should be of the same type. For example, if you were to JOIN a DECIMAL field with an INT field, MySQL would not be able to use their indexes. For those strings, the character set is the same. (It is possible that the character set of two tables is different.)
6, Do not ORDER BY RAND()
7. Avoid SELECT *
The more data is read out of the database, the slower the query will become. Also, if your database server and Web server are two separate servers, this can add to the network traffic load.
Therefore, you should get into the good habit of taking what you need.
$r = MySQL_query(“SELECT * FROM user WHERE user_id = 1”); $d = MySQL_fetch_assoc($r); echo “Welcome {$d[‘username’]}”; $r = MySQL_query(“SELECT username FROM user WHERE user_id = 1”); $d = MySQL_fetch_assoc($r); echo “Welcome {$d[‘username’]}”;
Always set an ID for both tables
Each table in the database should have an ID as its primary key, preferably an INT (UNSIGNED is recommended), and an auto-increment flag on it. Even if your users table has a field with a primary key called "email", you don't want it to be a primary key. Using the VARCHAR type as a primary key will degrade performance. In addition, in your program, you should use the table ID to construct your data structure.In addition, MySQL data engine, there are some operations that need to use the primary key, in these cases, the primary key performance and Settings become very important, such as clustering, partitioning…
9. Use ENUM instead of VARCHAR?
Enum types are very fast and compact. In reality, it holds TinyInt, but it appears as a string. As a result, this field is pretty perfect for making lists of options.
If you have a field such as “gender”, “country”, “ethnicity”, “status” or “department” that you know is finite and fixed, then you should use ENUM instead of VARCHAR.
Take advice from Procedure Analyse ().
Procedure Analyse () will let MySQL analyze your fields and actual data for you and give you some advice. These suggestions will only be useful if there is actual data in the table, which is the basis for making big decisions.
For example, if you’ve created an INT field as your primary key and you don’t have a lot of data in it, Procedure Analyse () will advise you to change the field’s type to MediumInt. Or if you’re using a VARCHAR field, and you don’t have much data, you might get a suggestion to change it to ENUM. These suggestions are probably because there is not enough data, so the decision making is not accurate enough.
11. Use NOT NULL whenever possible
Unless you have a very specific reason to use NULL values, you should always leave your fields NOT NULL. This may seem a little controversial, but read on.
First, ask yourself how different is “Empty” from “NULL” (if it’s an INT, then it’s 0 and NULL)? If you think there is no difference between them, then you should not use NULL. You know what? In Oracle, NULL and Empty strings are the same!
Don’t assume that NULL doesn’t require space, it does require 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 complicated, there are still some cases where you need to use NULL.
The following excerpts are 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.”
Save IP address as an UNSIGNED INT
Many programmers create a VARCHAR(15) field to hold the string IP instead of the integer IP. If you store it with an int, it only needs 4 bytes, and you can have fixed-length fields. Furthermore, this gives you an advantage in queries, especially if you need to use WHERE conditions such as IP between ip1 and ip2.
We must use UNSIGNED INT because the IP address will use the entire 32-bit UNSIGNED INT
13. Fixed length watches 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 in the table 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”, so the MySQL engine will handle it another way.
Fixed length tables will improve performance because MySQL will search faster, and since these fixed lengths are easy to calculate the next data offset, the read will naturally be faster. If the field is not of fixed length, then the program needs to find the primary key each time it looks for the next field.
Also, fixed-length tables are easier to cache and rebuild. The only side effect, however, is that a fixed-length field wastes some space, because it allocates that much space whether you use it or not.
14. Vertical segmentation
Vertical splitting is a method of breaking a database table into several tables by column, which reduces the complexity of the table and the number of fields for optimization purposes. (Before, I did a project in the bank, and I saw a table with more than 100 fields. It was horrible.)
Example 1: There is a field in the Users table that is the home address. This field is optional, and you don’t need to read or override this field very often, except for personal information, when you are working with the database. Well, why not put him on another list? So that will give you a better performance for your table, and if you think about it, a lot of the time, for my user table, only the user ID, the user name, the password, the user role and so on are going to be used a lot. Smaller tables always have better performance.
Example 2: You have a field called “last_login” that will be updated every time the user logs in. However, each update causes the query cache for the table to be cleared. So, you can put this field in another table, so that you don’t have to read the user ID, user name, and user role all the time, because query caching can help you a lot.
In addition, you need to be aware that you do not often Join the table from which the partitioned fields are formed, or the performance will be even worse than it would have been without partitioning, and there will be an extremely high level of degradation.
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 cause your entire site to stop doing so. Because these two operations will lock the table, once the table is locked, other operations can not enter.
Apache can have many child processes or threads. As a result, it works fairly efficiently, and our server doesn’t want to have too many child processes, threads, and database links, which can take up a lot of server resources, especially memory.
If you lock your table for an extended period of time, say 30 seconds, the cumulative number of access processes/threads, database links, and open files on a highly trafficsite may not only cause a Web service Crash, but also cause your entire server to Crash immediately.
So, if you have a big process, you must break it up, and using the LIMIT condition is a good way to do it. Here’s an example:
While (1) {MySQL_query(“DELETE FROM logs WHERE log_date <= ‘2009-11-01’ LIMIT 1000”); If (MySQL_affected_rows() == 0) { break; }// Always rest for a while usleep(50000); }
16. Smaller columns are faster
For most database engines, disk operation is probably the most significant bottleneck. So, making your data compact can be very helpful in this case, because it reduces the need for hard disk access.
Refer to MySQL’s Storage Requirements document for all data types.
If a table can only have a few columns (such as dictionary tables, configuration tables), then there is no reason to use INT as the primary key. It would be more economical to use mediumInt, SmallInt, or smaller TinyInt. If you don’t need to record time, use DATE rather than DATETIME.
Of course, you also need to leave enough room to expand, otherwise you will end up looking ugly if you do this later. See the example of Slashdot (November 06, 2009), where a simple ALTER TABLE statement took more than 3 hours because it had 16 million entries.
17. Choose the right storage engine
There are two storage engines in MySQL, MyISAM and InnoDB, each of which has advantages and disadvantages. The previous article “MySQL: InnoDB or MyISAM?” Discuss and discuss this matter.
MyISAM is good for applications that require a lot of queries, but it’s not so good for a lot of writes. Even if you only need to update a single field, the entire table will be locked and no other process, even the reader process, will be able to do anything until the read is complete. Also, MyISAM is super fast for computations like SELECT COUNT(*).
InnoDB tends to be a very complex storage engine, slower than MyISAM for small applications. It supports “row locking”, so it is better when there are more write operations. It also supports more advanced applications such as transactions.
18. Beware of “permanent links”
The purpose of “permanent links” is to reduce the number of times MySQL links have to be recreated. When a link is created, it stays connected, even after the database operation has ended. Also, since our Apache started reusing its child processes — that is, the next HTTP request will reuse the Apache child processes and reuse the same MySQL link.
PHP manual: MySQL_pconnect() This sounds pretty good in theory. But speaking from personal experience (and most people’s), this feature creates more trouble. Because, you only have a limited number of links, memory problems, file handles, etc.
Also, Apache runs in an extremely parallel environment, creating many, many processes. This is why the “permanent link” mechanism doesn’t work well. Before you decide to use permalink, you need to take a long look at the architecture of your entire system.
reference
19, When the query is slow, you can use JOIN to rewrite the query to optimize
MySQL> select sql_no_cache from guang_deal_outs where deal_id in (select id from guang_deals where id = 100017151) ; Empty set (18.87 SEC) MySQL> select sql_no_cache a. From guang_deal_outs a inner join guang_deals b on a.deal_id = b.id where b.id = 100017151; MySQL> desc select sql_no_cache from guang_deal_outs where deal_id in (select id from guang_deals where id = 100017151) ; +—-+——————–+—————–+——-+—————+———+———+——-+———-+———— -+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+—-+——————–+—————–+——-+—————+——— +———+——-+———-+————-+| 1 | PRIMARY | guang_deal_outs | ALL | NULL | NULL | NULL | NULL | 18633779 | Using where || 2 | DEPENDENT SUBQUERY | guang_deals | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |+—-+——————–+—————–+——-+—————+——— + + — — — — — — — — — — — — — — — — + — — — — — — — — — – + — — — — — — — — — — — — — + 2 rows in the set (0.04 SEC) MySQL > desc select sql_no_cache a. the from guang_deal_outs a inner join guang_deals b on a.deal_id = b.id where b.id = 100017151; +—-+————-+——-+——-+———————- +———————-+———+——-+——+————-+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+—-+————-+——-+——-+———————- +———————-+———+——-+——+————-+| 1 | SIMPLE | b | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index || 1 | SIMPLE | a | ref | idx_guang_dlout_dlid | idx_guang_dlout_dlid | 4 | const | 1 | |+—-+————-+——-+——-+———————- + — — — — — — — — — — — — — — — — — — — — — — + + — — — — — — — — — — — — — — — — + + — — — — — — — — — — — — — — — — — — + 2 rows in the set (0.05 SEC)
In fact, guang_deal_outs are indexed on deal_id as well.
I actually want to set the subquery to
select * from guang_deal_outs where deal_id in (select id from guang_deals where id = 100017151);
It’s going to look like this
select * from guang_deal_outs where deal_id in (100017151);
Unfortunately, the opposite is true. MySQL tries to make it associated with the outside table to “help” optimize the query, and it considers the EXISTS form below to be more efficient
select from guang_deal_outs where exists (select from guang_deals where id = 100017151 and
This form of IN subquery is inefficient for external tables (such as the guang_deals above) with large volumes of data (it does not have a significant effect on smaller tables).
This article is taken from the SegmentFault community:
Java siege divisionThe article is published with the author’s authorization. Please contact the author if you need to reprint. _
Click on the attention, the first time to understand Huawei cloud fresh technology ~