Simple method for MySQL database optimization

The performance of the database is very important. I believe everyone knows this. Let’s optimize the mysql database from four aspects!
1. Select the most applicable field Attributes
MySQL can well support the access of large amounts of data, but generally speaking, the smaller the table in the database, the better the execution on it. Query will be faster. Therefore, when creating a table, in order to obtain better performance, we can set the width of the fields in the table as small as possible. For example, when defining the postal code field, if you set it to CHAR(255), it will obviously add unnecessary space to the database. Even using the VARCHAR type is redundant, because CHAR(6) is fine. Mission accomplished. Likewise, 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 when possible NULL, so that the database does not need to compare NULL values ​​when executing queries in the future.
For some text fields, such as "province" or "gender", we can define them as ENUM type. Because in MySQL, the ENUM type is treated as numeric data, and numeric data is processed much faster than text types. In this way, we can improve the performance of the database.
2. Use connections (JOIN) instead of sub-queries (Sub-Queries)
MySQL supports SQL sub-queries starting from 4.1. This technique can use a SELECT statement to create a single column of query results, and then use this result as a filter condition in another query. For example, if we want to delete customers who do not have any orders in the basic customer information table, we can use a subquery to first retrieve the IDs of all customers who have placed orders from the sales information table, and then pass the results to The main query is as follows:
DELETE FROM customerinfoWHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)


Using subquery can be done all at once It can complete many SQL operations that logically require multiple steps to complete. It can also avoid transaction or table locks, and it is also easy to write. However, in some cases, subqueries can be replaced by more efficient joins (JOIN).. For example, suppose we want to retrieve all users who do not have order records, we can use the following query: SELECT * FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)

If using Connection (JOIN).. to complete this query will be much faster. Especially if there is an index on CustomerID in the salesinfo table, the performance will be better. The query is as follows:
SELECT * FROM customerinfo
LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.

CustomerID

WHERE salesinfo.CustomerID IS NULL

Connection (JOIN).. The reason why it is more efficient is that MySQL does not need to create a temporary table in memory to complete this logical step. query work.
3. Use union (
UNION) to replace manually created temporary tables MySQL supports UNION query starting from version 4.0, which can combine two or more data that need to use temporary tables. SELECT queries are combined into one query. When the client's query session ends, the temporary table will be automatically deleted to ensure that the database is tidy and efficient. When using UNION to create a query, we only need to use UNION as the keyword to connect multiple SELECT statements. It should be noted 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, Birth
Date FROM author
UNION

SELECT Name, Supplier FROM product

4、Transaction
Although we can use sub-queries (Sub-Queries), connections (JOIN) and unions (UNION) to create a variety of queries, not all database operations can be done with only one or a few It can be completed with just one SQL statement. More often, a series of statements are needed to complete a certain kind of work. But in this case, when a certain statement in this statement block runs incorrectly, the operation of the entire statement block will become uncertain. Imagine that you want to insert certain data into two related tables at the same time. This may happen: after the first table is successfully updated, an unexpected situation occurs in the database, causing the operation in the second table to not be completed. , In this way, the data will be incomplete and even the data in the database will be destroyed. To avoid this situation, you should use transactions. Its function is: either every statement in the statement block succeeds or fails. In other words, the consistency and integrity of the data in the database can be maintained. Things start with the BEGIN keyword and end with the COMMIT keyword. If a SQL operation fails during this period, the ROLLBACK command can restore the database to the state before BEGIN started.
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, it can use the method of locking the database to provide users with a safe access method, which can ensure that the user's operations are not blocked. Interference by other users.

The above is the detailed content of Simple method for MySQL database optimization. For more information, please follow other related articles on the PHP Chinese website!