Several solutions to improve MySQL database performance

The following article mainly introduces several practical operation solutions for improving MySQL database performance, including selecting the most suitable field attribute and correctly using JOIN) subquery (Sub-Queries.

1. select the most suitable field attribute

MySQL can support access to large data volumes, but generally, the smaller the table in the database, the faster the query will be executed on it. Therefore, when creating a table, we can set the field width as small as possible to achieve better MySQL database performance. For example, if you set it to CHAR (255) when defining the zip code field, it is obvious that unnecessary space is added to the database, and even the VARCHAR type is redundant, because CHAR (6) can well complete the task.

Similarly, if possible, we should use MEDIUMINT instead of BIGIN to define integer fields.

Another way to improve efficiency is to set the field to not null whenever possible, so that the database does NOT need to compare NULL values during future queries.

Some text fields, such as "Province" or "gender", can be defined as ENUM. In MySQL, The ENUM type is processed as the numeric data, and the numeric data is processed much faster than the text type. In this way, we can improve the performance of the MySQL database.

2. Use JOIN to replace subqueries (Sub-Queries)

MySQL supports SQL subqueries from 4.1. This technique can use the SELECT statement to create a single column query result, and then use this result as a filter condition in another query. For example, if you want to delete a customer who has no orders in the basic customer information table, you can use the subquery to retrieve the customer IDs of all orders from the sales information table, then pass the result to the primary query, as shown below:

 
 

  
  
DELETE FROM customerinfo   
  
  
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )  
 
 

Subqueries can be used to complete SQL operations that require multiple logical steps at a time. At the same time, transactions or tables can be prevented from being locked and can be easily written. However, in some cases, subqueries can be joined more efficiently. For example, if we want to retrieve all users without order records, we can use the following query:

 
 

  
  
SELECT * FROM customerinfo   
  
  
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )  
 
 

If you use JOIN)... to complete this query, the speed will be much faster. Especially when the salesinfo table has an index on CustomerID, MySQL database performance will be better. The query is as follows:

 
 

  
  
SELECT * FROM customerinfo   
  
  
LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.   
  
  
CustomerID   
  
  
WHERE salesinfo.CustomerID IS NULL  
 
 

JOIN)... it is more efficient because MySQL does not need to create a temporary table in the memory to perform the query in two logical steps.

3. Use UNION instead of creating a temporary table manually

MySQL 4.0 and later versions support UNION queries. It can merge two or more SELECT queries in a temporary table.

When the query Session on the client ends, the temporary table is automatically deleted to ensure the database is neat and efficient. When using UNION to create a query, we only need to use UNION as the keyword to connect multiple SELECT statements. Note that the number of fields in all SELECT statements must be the same. The following example demonstrates a query using UNION.

 
 

  
  
SELECT Name, Phone FROM client   
  
  
UNION   
  
  
SELECT Name, BirthDate FROM author   
  
  
UNION   
  
  
SELECT Name, Supplier FROM product  
 
 

4. Transactions

Although we can use subqueries Sub-Queries), JOIN joins, and UNION) to create various Queries, however, not all database operations can be completed with only one or a few SQL statements. In more cases, a series of statements are needed to complete some work. However, in this case, when a statement in the statement block fails to run, the operation of the entire statement block becomes uncertain.

Imagine that you want to insert data into two associated tables at the same time. This may happen: after the first table is successfully updated, the database suddenly experiences an unexpected situation, as a result, the operations in the second table are incomplete, which may damage the data in the database. To avoid this situation, you should use a transaction. Its function is to either operate successfully or fail each statement in the statement block.

In other words, data consistency and integrity can be maintained in the database. The transaction starts with the BEGIN keyword and ends with the COMMIT keyword. If an SQL operation fails, the ROLLBACK command can restore the database to the State before the start of in.

 
 

  
  
BEGIN;   
  
  
INSERT INTO salesinfo SET CustomerID=14;   
  
  
UPDATE inventory SET Quantity=11   
  
  
WHERE item='book';   
  
  
COMMIT;  
 
 

Another important role of transactions is that when multiple users use the same data source at the same time, they can use the database locking method to provide users with a safe access method, this ensures that user operations are not affected by other users.

The above content is an introduction to improving the performance of the MySQL database. I hope you will have some gains.