Explain how to completely optimize MySQL database performance from eight aspects.

1. Select the most applicable field attribute

MySQL can support the access of large amounts of data very well, but generally speaking, the smaller the table in the database, the faster the queries executed on it will be. 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 fields to NOT NULL when possible, 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 types. 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 join (JOIN) instead of sub-query (Sub-Queries)

MySQL supports SQL subqueries starting from 4.1. This technique allows you to 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 issued orders from the sales information table, and then pass the results to the main query, as shown below :

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

Using subqueries can complete many SQL operations that logically require multiple steps to complete at one time. 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, assuming we want to retrieve all users who have no order records, we can use the following query:

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

If you use connection (JOIN).. to complete this query, the speed 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 two-step query.

3. Use UNION to replace manually created temporary tables

MySQL supports UNION queries starting from version 4.0, which can combine two or more SELECT queries that require the use of temporary tables 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 a 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, BirthDate FROM author
UNION
SELECT Name, Supplier FROM product

4. Affairs

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 completed with only one or a few SQL statements. of. 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 interfered by other users. .

5. Lock table

Although transactions are a very good way to maintain database integrity, because of their exclusivity, they sometimes affect database performance, 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. If a database system has only a few users

, the impact of transactions will not be a big problem; but if thousands of users access a database system at the same time, such as accessing an e-commerce website, there will be a serious response delay.

In fact, in some cases we can obtain better performance by locking the table. The following example uses the lock table method to complete the transaction function in the previous example.

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 insert, update, or delete the inventory before 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 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, you must remember to define the table type as a transaction-safe table InnoDB type when creating the table. This type is not the default type for MySQL tables. The way to define it is to add TYPE=INNODB to the CREATE TABLE statement. As shown in the example.

7. Use index

Indexing is a common method to improve database performance. It allows the database server to retrieve specific rows much faster than without an index, especially when the query statement contains commands such as MAX(), MIN() and ORDERBY. The performance improvement is more obvious. So which fields should be indexed? Generally speaking, 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. We can create appropriate indexes at the same time when creating the table, or we can use ALTER TABLE or CREATE INDEX to create indexes later. Additionally, MySQL

Full-text indexing and search are supported starting from version 3.23.23. The full-text index is a FULLTEXT type index in MySQL, but it can only be used for MyISAM type tables. For a large database, it is very fast to load the data into a table without a FULLTEXT index and then use ALTER TABLE or CREATE INDEX to create the index. But if you load data into a table that already has a FULLTEXT index, the execution process will be very slow.

8. Optimized query statements

In most cases, using indexes can improve query speed, but if SQL statements are not used appropriately, the index will not be able to play its due role. The following are several aspects that should be paid attention to. First, it is best to perform comparison operations between fields of the same type. Before MySQL version 3.23, this was even a required condition. For example, an indexed INT field cannot be compared with a BIGINT field; but as a special case, when a CHAR type field and a VARCHAR type field have the same size, they can be compared. Secondly, try not to use functions to operate on indexed fields.

For example, when using the YEAE() function on a DATE type field, the index will not function as it should. Therefore, although the following two queries return the same results, the latter is much faster than the former.

SELECT * FROM order WHERE YEAR(OrderDate)<2001;
SELECT * FROM order WHERE OrderDate<"2001-01-01";

The same situation will also occur when calculating numeric fields:

SELECT * FROM inventory WHERE Amount/7<24;
SELECT * FROM inventory WHERE Amount<24*7;

The above two queries also return the same results, but the latter query will be much faster than the previous one. Third, when searching for character fields, we sometimes use LIKE keywords and wildcards. Although this approach is simple, it also comes at the expense of system performance. For example, the following query will compare every record in the table.

SELECT * FROM books
WHERE name like "MySQL%"

But if you use the following query instead, the results returned will be the same, but the speed will be much faster:

SELECT * FROM books
WHERE name>="MySQL"and name<"MySQM"

Finally, care should be taken to avoid letting MySQL perform automatic type conversions in queries, because the conversion process will also render the index useless.

Explain how to completely optimize MySQL database performance from eight aspects. _PHP Tutorial