Optimization summary of mysql database

Using the mysql database in PHP development has become a habit of every PHP programmer. If we want PHP to operate mysql faster and more conveniently, we have to optimize the mysql database. , MySQL optimization is also frequently asked in interviews, so let’s take a look together!

1. Optimize your MySQL query cache

When querying on the MySQL server, you can enable high-speed query cache. Letting the database engine handle things quietly in the background is one of the most effective ways to improve performance. When the same query is executed multiple times, it is quite fast if the results are pulled from the cache.
But the main problem is that it is so easily hidden that most of us programmers ignore it. In some processing tasks, we can actually prevent the query cache from working.

// query cache does NOT work
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
 // query cache works!
 $today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
 // query cache does NOT work
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
 // query cache works!
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

2. Use EXPLAIN to make your SELECT query clearer

Using the EXPLAIN keyword is another MySQL optimization tip that allows you to understand what kind of query operations MySQL is doing. This can help you find bottlenecks and show where the query or table structure is going wrong.
The results of the EXPLAIN query can tell you which indexes are being referenced, how the table is scanned and sorted, etc.
Implement a SELECT query (preferably a more complex one with joins) and add your keyword explanation in it. Here we can use phpMyAdmin, which will tell you the table results in. For example, if I forget to add a column to an index when performing joins, EXPLAIN can help me find the problem.

After adding the index to the group_id field

3. Use LIMIT 1 to obtain the unique row

Sometimes, when you want to query a table, you know you only need to look at one row. You might be looking for a very unique record, or you might just be checking the number of records that exist that satisfy your WHERE clause.
In this case, adding a LIMIT 1 will make your query more efficient. In this way, the database engine will stop scanning after finding only 1, instead of scanning the entire table or index.

 // do I have any users from Alabama?   
 // what NOT to do:   
 $r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'");   
 if (mysql_num_rows($r) > 0) {   
     // ...   
 }     
 // much better:   
 $r = mysql_query("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1");   
 if (mysql_num_rows($r) > 0) {   
     // ...   
 }

4. Search fields in the index

An index is not just a primary key or a unique key. If you want to search any column in the table, you should always point to the index.

5. Ensure that the connected indexes are of the same type

If the application With multiple join queries, you need to ensure that the columns you join are indexed on both tables. This affects how MySQL optimizes inner join operations.
In addition, the columns added must be of the same type. For example, if you join a DECIMAL column and at the same time join an int column in another table, MySQL will not be able to use at least one of the indicators. Even the character encoding must be the same as the string type.

 // looking for companies in my state   
 $r = mysql_query("SELECT company_name FROM users  
     LEFT JOIN companies ON (users.state = companies.state)  
     WHERE users.id = $user_id");   

 // both state columns should be indexed   
 // and they both should be the same type and character encoding   
 // or MySQL might do full table scans

6. Do not use the BY RAND() command

这是一个令很多新手程序员会掉进去的陷阱。你可能不知不觉中制造了一个可怕的平静。这个陷阱在你是用BY RAND（）命令时就开始创建了。
如果您真的需要随机显示你的结果，有很多更好的途径去实现。诚然这需要写更多的代码，但是能避免性能瓶颈的出现。问题在于，MySQL可能会为表中每一个独立的行执行BY RAND()命令（这会消耗处理器的处理能力），然后给你仅仅返回一行。

 // what NOT to do:   
 $r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");   
 // much better:   
 $r = mysql_query("SELECT count(*) FROM user");   
 $d = mysql_fetch_row($r);   
 $rand = mt_rand(0,$d[0] - 1);   
 $r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

7. 尽量避免SELECT *命令

从表中读取越多的数据，查询会变得更慢。他增加了磁盘需要操作的时间，还是在数据库服务器与WEB服务器是独立分开的情况下。你将会经历非常漫长的网络延迟，仅仅是因为数据不必要的在服务器之间传输。
始终指定你需要的列，这是一个非常良好的习惯。

 // not preferred   
 $r = mysql_query("SELECT * FROM user WHERE user_id = 1");   
 $d = mysql_fetch_assoc($r);   
 echo "Welcome {$d['username']}";   
 // better:   
 $r = mysql_query("SELECT username FROM user WHERE user_id = 1");   
 $d = mysql_fetch_assoc($r);   
 echo "Welcome {$d['username']}";    
 // the differences are more significant with bigger result sets

8. 从PROCEDURE ANALYSE()中获得建议

PROCEDURE ANALYSE()可让MySQL的柱结构分析和表中的实际数据来给你一些建议。如果你的表中已经存在实际数据了，能为你的重大决策服务。

9. 准备好的语句

准备好的语句，可以从性能优化和安全两方面对大家有所帮助。
准备好的语句在过滤已经绑定的变量默认情况下，能给应用程序以有效的保护，防止SQL注入攻击。当然你也可以手动过滤，不过由于大多数程序员健忘的性格，很难达到效果。

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

10. 将IP地址存储为无符号整型

许多程序员在创建一个VARCHAR（15）时并没有意识到他们可以将IP地址以整数形式来存储。当你有一个INT类型时，你只占用4个字节的空间，这是一个固定大小的领域。
你必须确定你所操作的列是一个UNSIGNED INT类型的,因为IP地址将使用32位unsigned integer。

$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";

11.永远为每张表设置一个ID

我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的(推荐使用UNSIGNED)，并设置上自动增加的AUTO_INCREMENT标志。
就算是你users表有一个主键叫“email”的字段，你也别让它成为主键。使用VARCHAR类型来当主键会使用得性能下降。另外，在你的程序中，你应该使用表的ID来构造你的数据结构。
而且，在MySQL数据引擎下，还有一些操作需要使用主键，在这些情况下，主键的性能和设置变得非常重要，比如，集群，分区……
在这里，只有一个情况是例外，那就是“关联表”的“外键”，也就是说，这个表的主键，通过若干个别的表的主键构成。我们把这个情况叫做“外键”。比如：有一个“学生表”有学生的ID，有一个“课程表”有课程ID，那么，“成绩表”就是“关联表”了，其关联了学生表和课程表，在成绩表中，学生ID和课程ID叫“外键”其共同组成主键。

12. Use ENUM instead of VARCHAR

The ENUM type is very fast and compact. In fact, it saves 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 Not VARCHAR.
MySQL also has a "suggestion" (see item 10) to tell you how to reorganize your table structure. When you have a VARCHAR field, this advice will tell you to change it to an ENUM type. Using PROCEDURE ANALYSE() you can get relevant suggestions.

13. Get advice from PROCEDURE ANALYSE() p Programmer Station

PROCEDURE ANALYSE() will let MySQL help you Analyzes your fields and their actual data and gives you some useful suggestions. These suggestions will only become useful if there is actual data in the table, because making some big decisions requires data as a basis.
For example, if you create an INT field as your primary key, but there is not much data, then PROCEDURE ANALYSE() will recommend that you change the type of this field to MEDIUMINT. Or if you are using a VARCHAR field, because there is not much data, you may get a suggestion to change it to ENUM. These suggestions are all possible because there is not enough data, so the decision-making is not accurate enough.
In phpmyadmin, you can click "Propose table structure" to view these suggestions when viewing the table

Be sure to note that these are only suggestions, only when there is more and more data in your table , these recommendations will become accurate. Be sure to remember that you are the one who makes the final decision

14. Use NOT NULL php programmer station as much as possible

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 that 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 perform comparisons, your program 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:

15. Prepared Statements

Prepared Statements are very similar to stored procedures , is a collection of SQL statements that run in the background. We can get many benefits from using prepared statements, whether it is performance issues or security issues.
Prepared Statements can check some variables that you have bound, which can protect your program from "SQL injection" attacks. Of course, you can also manually check your variables. However, manual checks are prone to problems and are often forgotten by programmers. When we use some framework or ORM, this problem will be better.
In terms of performance, this will bring you considerable performance advantages when the same query is used multiple times. You can define some parameters for these Prepared Statements, and MySQL will only parse them once.
Although the latest version of MySQL uses binary format when transmitting Prepared Statements, this will make network transmission very efficient.
Of course, there are some cases where we need to avoid using Prepared Statements because they do not support query caching. But it is said that it is supported after version 5.1. To use prepared statements in PHP, you can check its manual: mysqli extension or use a database abstraction layer, such as: PDO.

16. Unbuffered query

Under normal circumstances, when you execute a SQL statement in your script, your program will stop there until no more SQL statements are returned, and then your program will continue Continue execution below. You can use unbuffered queries to change this behavior.
Regarding this matter, there is a very good explanation in the PHP documentation: mysql_unbuffered_query() function:
The translation of the above sentence means that mysql_unbuffered_query() sends a SQL statement to MySQL does not automatically fethch and cache results like mysql_query(). This will save a lot of considerable memory, especially for queries that generate a large number of results, and you don't need to wait until all the results are returned. You only need to return the first row of data and you can start working immediately. The query results are up.
However, this will have some limitations. Because you either have to read all the rows, or you have to call mysql_free_result() to clear the results before the next query. Also, mysql_num_rows() or mysql_data_seek() will not work. Therefore, you need to consider carefully whether to use unbuffered queries.

17. Save the IP address as UNSIGNED INT

Many programmers will create a VARCHAR(15) field to store characters IP in string form instead of shaped 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 uses the entire 32-bit unsigned integer.
As for your query, you can use INET_ATON() to convert a string IP into an integer, and use INET_NTOA() to convert an integer into a string IP. In PHP, there are also such functions ip2long() and long2ip().

18. Fixed length tables will be faster

If all fields in the table are "fixed length" ", the entire table will be 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. PHP Programmer Station
Using the "vertical split" technology (see next item), you can split your table into two, one with a fixed length and one with a variable length.

19. Vertical split

"Vertical split" is a way to turn the tables in the database into several tables by columns method, which can reduce the complexity of the table and the number of fields, thereby achieving the purpose of optimization. (I used to do projects in a bank and saw a table with more than 100 fields, which was scary)
Example 1: There is a field in the Users table that is the home address. This field is optional. Compared with , and when you operate database, except for personal information, you do not need to read or rewrite this field frequently. So, why not put it in another table? This will make your table better

Related recommendations:

MySQL optimization summary-total number of queries

The above is the detailed content of Optimization summary of mysql database. For more information, please follow other related articles on the PHP Chinese website!