Detailed explanation of eight ways to optimize MySQL database (classic must-read)

introduction:

There are a lot of information and methods on the Internet about database optimization, but the quality of many of them is uneven, some of the summaries are not thorough and the content is redundant.

I found this article occasionally. It is a classic summary with a large amount of traffic. So I took it to my own summary collection, accumulated high-quality articles, and improved my personal abilities. I hope it will also be helpful to everyone in future development.

1. Select the most suitable field attributes

MySQL can well support the storage and access of large amounts of data, 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) can complete the task well. 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 NOTNULL whenever possible, so that the database does not have 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 data. In this way, we can improve the performance of the database again.

2. Use JOIN instead of sub-queries

MySQL supports SQL subqueries starting from version 4.1. This technique uses a SELECT statement to create a single-column query result, and then uses 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 customer basic information table, we can use a subquery to first retrieve the customer IDs of all customers who have placed orders from the sales information table, and then pass the result to the main query, as shown below:

DELETE FROM customer info
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, while also avoiding transaction or table locks, and is also easy to write. However, in some cases, subqueries can be replaced by more efficient joins.. For example, suppose we want to retrieve all users who have no order records, we can use the following query to complete:

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

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

SELECT * FROM customerinfo
LEFTJOIN salesinfo ON customerinfo.CustomerID = salesinfo.CustomerID
WHERE salesinfo.CustomerIDISNULL

JOIN.. is more efficient because MySQL does not need to create a temporary table in memory to complete this logical two-step query.

3. Use UNION instead of 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 neat 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,BirthDate FROM author UNION
SELECT Name, Supplier FROM product

4. Affairs

Although we can use sub-queries, joins, and unions to create a variety of queries, not all database operations can be completed with only one or a few SQL statements. More often than not, a series of statements are needed to complete a task. However, in this case, when a statement in this block fails to run, the operation of the entire block becomes uncertain. Imagine that you want to insert a certain data into two related tables at the same time. The following situation may occur: after the first table is successfully updated, an unexpected situation suddenly occurs in the database, causing the operation in the second table to not be completed. This will cause incomplete data or even damage the data in the database. To avoid this situation, you should use transactions, which have the following functions: either each statement in the statement block succeeds or fails. In other words, the consistency and integrity of the data in the database can be maintained. Transactions begin with the BEGIN keyword and end with the COMMIT keyword. If an SQL operation fails during this period, the ROLLBACK command can restore the database to the state before BEGIN started.

BEGIN;
 INSERTINTOsalesinfoSETCustomerID=14;
 UPDATEinventorySETQuantity=11WHEREitem='book';
COMMIT;

Another important function of transactions is that when multiple users use the same data source at the same time, it can provide users with a safe access method by locking the database, thus ensuring that the user's operation is not interfered with by other users.

5. Lock table

Although transactions are a very good way to maintain database integrity, their exclusivity sometimes affects 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 temporarily until the transaction ends. If a database system is used by only a few users, the impact of transactions will not be a big problem; but if there are thousands of users accessing a database system at the same time, such as accessing an e-commerce website, there will be serious response delays.

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

LOCKTABLEinventoryWRITESELECTQuantityFROMinventoryWHEREItem='book';
...
UPDATEinventorySETQuantity=11WHEREItem='book';UNLOCKTABLES

Here, we use a select statement to retrieve the initial data, perform some calculations, and use an update statement to update the new values ​​into the table. The LOCKTABLE statement containing the WRITE keyword ensures that no other access to the inventory can insert, update, or delete data until the UNLOCKTABLES command is executed.

6. Use foreign keys

The locking table method 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.

CREATETABLE customerinfo( CustomerID INTNOTNULL, PRIMARYKEY(CustomerID)) TYPE=INNODB;
CREATETABLE salesinfo( SalesIDINTNOTNULL, CustomerIDINTNOTNULL,
PRIMARYKEY(CustomerID,SalesID),
FOREIGNKEY(CustomerID)REFERENCEScustomerinfo(CustomerID)ONDELETECASCADE)TYPE=INNODB;

Note the parameter "ONDELETECASCADE" 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, 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 definition method is to add TYPE=INNODB in the CREATE TABLE statement. As shown in the example.

7. Use indexes

Indexing is a common method to improve database performance. It allows the database server to retrieve specific rows much faster than without an index. This is especially true when the query statement contains commands such as MAX(), MIN(), and ORDER BY.

So which fields should be indexed?

Generally speaking, indexes should be created on fields that will be used for JOIN, WHERE judgments, and ORDER BY sorting. Try not to create an index on a field in your database that contains a large number of repeated values. For an ENUM type field, it is very likely that a large number of duplicate values ​​will appear.

For example, the "province" field in customerinfo. Creating an index on such a field will not be helpful; on the contrary, it may even reduce the performance of the database. We can create appropriate indexes at the same time when we create the table, or we can use ALTER TABLE or CREATE INDEX to create indexes later. In addition, MySQL supports full-text indexing and searching starting from version 3.23.23. The full-text index in MySQL is a FULLTEXT type index, but it can only be used for MyISAM type tables. For a large database, loading the data into a table without a FULLTEXT index and then creating the index using ALTER TABLE or CREATE INDEX will be very fast. However, 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 the SQL statement is not used properly, the index will not play its due role.

Here are a few aspects that should be noted.

a. First, it is best to compare fields of the same type.

Prior to MySQL 3.23, this was even a requirement. For example, you cannot compare an indexed INT field with a BIGINT field; however, as a special case, you can compare a CHAR type field and a VARCHAR type field when they have the same field size.

b. Secondly, try not to use functions to operate on indexed fields.

For example, using the YEAE() function on a DATE type field will prevent the index from functioning properly. So, although the following two queries return the same results, the latter is much faster than the former.

c. Third, when searching character fields, we sometimes use the LIKE keyword and wildcards. Although this approach is simple, it also sacrifices system performance.

For example, the following query will compare every record in the table.

SELECT * FROM books
WHERE namelike "MySQL%"

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

SELECT * FROM books
WHERE name＞="MySQL" and name＜"MySQM"

Finally, you should be careful to avoid letting MySQL perform automatic type conversions in your queries, because the conversion process can also render indexes ineffective.

The above are the eight ways to optimize MySQL database that I introduced to you (classic must-read). I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!