Common methods for MySQL optimization

1. Select the most applicable field attribute
Set the width of the fields in the table as small as possible: the upper limit of char is 255 bytes (fixed occupancy space), the upper limit of varchar is 65535 bytes (actually occupied space), and the upper limit of text is 65535 bytes.
Try to set the field to NOT NULL. When executing the query, the database does not need to compare NULL values.

2. Use joins (JOIN) instead of sub-queries (Sub-Queries)
The reason why joins (JOIN) are more efficient is because MySQL does not need to be in memory Creating a temporary table to complete this logical query requires two steps (joint query conditions plus indexes are faster).

3. Use union (UNION) instead of manually created temporary tables
Combine two or more SELECT queries that need to use temporary tables into one query.
SELECT Name, Phone FROM client UNION SELECT Name, BirthDate FROM author UNION SELECT Name, Supplier FROM product;

4. Transaction
Although we can use subquery (Sub -Queries), connections (JOIN) and unions (UNION) to create a variety of queries, but not all database operations can be completed with only one or a few SQL statements. More often, a series of statements are needed to complete a certain kind of work.
The effect is: Either every statement in the statement block operates successfully, or all of them fail. 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.

5. Locking tables
Although transactions are a very good way to maintain the integrity of the database, because of its exclusivity, it sometimes affects the performance of the database, especially in large application systems. Since the database will be locked during the execution of the transaction, other user requests can only wait until the transaction ends.
LOCK TABLE inventory WRITE
SELECT Quantity FROM inventory
WHEREItem='book';
...
UPDATE inventory SET Quantity=11
WHEREItem='book';
UNLOCK TABLES
Here, we use a SELECT statement to retrieve the initial data, and through some calculations, use an UPDATE statement to update the new values ​​into the table. The LOCK TABLE statement containing the WRITE keyword ensures that there will be no other access to the inventory to insert, update, or delete before the UNLOCK TABLES command is executed.

6. Using foreign keys
The method of locking the table can maintain the integrity of the data, but it cannot guarantee the relevance of the data. At this time we can use foreign keys. For example, a foreign key can ensure that each sales record points to an existing customer. Here, the foreign key can map the CustomerID in the customerinfo table to the CustomerID in the salesinfo table. Any record without a valid CustomerID will not be updated or inserted into salesinfo.

CREATE TABLE customerinfo 
( 
CustomerID INT NOT NULL , 
PRIMARY KEY ( CustomerID ) 
) TYPE = INNODB; 
CREATE TABLE salesinfo 
( 
SalesID INT NOT NULL, 
CustomerID INT NOT NULL, 
PRIMARY KEY(CustomerID, SalesID), 
FOREIGN KEY (CustomerID) REFERENCES customerinfo 
(CustomerID) ON DELETECASCADE 
) TYPE = INNODB;

Note the parameter "ON DELETE CASCADE" in the example. This parameter ensures that when a customer record in the customerinfo table is deleted, all records related to the customer in the salesinfo table will also be automatically deleted. If you want to use foreign keys in MySQL, be sure to remember to define the table type as a transaction-safe InnoDB type when creating the table. This type is not the default type for MySQL tables. It is defined by adding TYPE=INNODB to the CREATE TABLE statement.

7. Using indexes
When the query statement contains commands such as MAX (), MIN () and ORDERBY, the performance improvement is more obvious.
Indexes should be built on fields that will be used for JOIN, WHERE judgment and ORDER BY sorting. Try not to index a field in the database that contains a large number of duplicate values. For an ENUM type field, it is very possible to have a large number of duplicate values, such as the "province".. field in customerinfo. Building an index on such a field will not be helpful; on the contrary, it is possible Reduce database performance.

8. Optimized query statement
SELECT FROM order WHERE YEAR(OrderDate)<2001;
SELECT FROM order WHERE OrderDate<"2001-01-01";

SELECT FROM inventory WHERE Amount/7<24;
SELECT FROM inventory WHERE Amount<24*7;
Avoid letting MySQL perform automatic type conversion in the query, because the conversion process will also make the index become ineffective.

For more technical articles related to SQL, please visit the SQL Tutorial column to learn!

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