Shulou(Shulou.com)06/01 Report--

How to optimize MySQL? In order to solve this problem, the editor summarizes this article on MySQL optimization today, hoping to help more students who want to learn MySQL optimization to find a more simple and easy way.

1. Select the most applicable field properties

The width of the fields in the table is set as small as possible: the upper limit of char is 255bytes (fixed footprint), the upper limit of varchar is 65535 bytes (actual footprint), and the upper limit of text is 65535. Char is more efficient than varchar.

Try to set the field to NOT NULL so that the database does not have to compare NULL values when executing the query.

two。 Use joins (JOIN) instead of subqueries (Sub-Queries)

JOIN is more efficient because MySQL does not need to create temporary tables in memory to complete this logically two-step query (conditional plus indexing for federated queries is faster).

3. Use UNION instead of manually created temporary tables

Merge 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. Business

Although we can use subqueries (Sub-Queries), JOIN (join), and UNION (UNION) to create a variety of queries, not all database operations can be done with one or a few SQL statements. More often, you need to use a series of statements to accomplish some kind of work.

The effect is that either every statement in the statement block succeeds or all fails. In other words, you can maintain the consistency and integrity of the data in the database. Things start with the BEGIN keyword and end with the COMMIT keyword. If a SQL operation fails in between, the ROLLBACK command can restore the database to the state it was before BEGIN started.

5. Lock table

Although transaction is a very good way to maintain database integrity, it sometimes affects the performance of database because of its exclusivity, especially in large application systems. Because the database will be locked during the execution of the transaction, other user requests can only wait temporarily 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 take the initial data, and through some calculations, use the UPDATE statement to update the new value to the table. A LOCK TABLE statement that contains the WRITE keyword ensures that there will be no other access to insert, update, or delete the inventory until the UNLOCK TABLES command is executed.

6. Use 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 point, 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, and any record without a legal CustomerID will not be updated or inserted into the 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

Notice 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 that 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 type of the table as the transaction safety table InnoDB type when you create the table. This type is not the default type for MySQL tables. The method defined is to add TYPE=INNODB to the CREATE TABLE statement.

7. Use index

The performance improvement is even more pronounced when the query contains commands such as MAX (), MIN (), and ORDERBY.

The index should be based on the 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 a field of type ENUM, it is possible to have a large number of duplicate values, such as "province" in customerinfo. Field, indexing on such a field will not help; on the contrary, it may degrade the performance of the database.

The only task of a normal index (an index defined by the keyword KEY or INDEX) is to speed up access to data. Therefore, indexes should be created only for those data columns that appear most frequently in query criteria (WHEREcolumn=) or sort conditions (ORDERBYcolumn).

The benefits of a unique index: first, it simplifies the management of the index by MySQL, so the index becomes more efficient; second, when a new record is inserted into the data table, MySQL automatically checks whether the value of this field of the new record has already appeared in this field of a record; if so, MySQL will refuse to insert the new record. In other words, the unique index can guarantee the uniqueness of the data record. In many cases, the purpose of creating a unique index is not to improve access speed, but only to avoid data duplication.

8. Optimized query statement

SELECT FROM order WHERE YEAR (OrderDate)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.