About database optimization, there are a lot of information and methods on the Internet, but many quality is uneven, some summary is not in place, the content is jumbled.

Occasionally FOUND this article, summarized very classic, the article flow is also very large, so get their own summary collection, accumulate high-quality articles, improve personal ability, I hope to help everyone in the future development

1. Select the most applicable field attributes

MySQL supports large data volumes well, but in general, the smaller the tables in the database, the faster the queries executed on them. Therefore, when creating a table, we can make the width of the fields in the table as small as possible for better performance.

For example, when defining the zip code field, setting it to CHAR(255) would obviously add unnecessary space to the database, and even using the type VARCHAR would be unnecessary, since CHAR(6) would do just fine. Also, 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 NULL 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 types. Because in MySQL, ENUM types are 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 joins instead of sub-queries.

MySQL has supported SQL subqueries since 4.1. The technique uses a SELECT statement to create a single column of query results, which can then be used as a filter in another query. For example, if we wanted to delete a customer without any orders from the customer base table, we could use a subquery to first extract all the customer ids that placed orders from the sales table and then pass the results to the main query, as follows:

Using subqueries allows you to do many SQL operations at once that would logically require multiple steps, avoid transactions or table locks, and make it easy to write. However, in some cases, subqueries can be joined more efficiently. Alternative. For example, suppose we wanted to extract all users with no order records, we could do this with the following query:

If you use JOIN.. To complete the query, it will be much faster. Especially if the salesInfo table has an index on CustomerID, the performance will be better.

JOIN… It is more efficient because MySQL does not need to create temporary tables in memory to perform this logical two-step query.

\

3. Use unions instead of manually created temporary tables

MySQL has supported union queries since version 4.0, which can combine two or more SELECT queries that need to use temporary tables into a single query. At the end of the query session on the client, temporary tables are automatically deleted to ensure a clean and efficient database. When using union to create a query, we simply use union as the keyword to concatenate multiple SELECT statements. Note that all select statements have the same number of fields. The following example demonstrates a query using UNION.

4, transaction

Although we can use sub-queries, joins, and unions to create a wide variety of Queries, not all database operations can be done with just one or a few SQL statements. More often than not, you need to use a series of statements to get something done. In this case, however, when one of the statements in the block fails, the operation of the whole block becomes uncertain. Imagine trying to insert some data into two related tables at the same time. After a successful update in the first table, an unexpected condition occurs in the database and the operation in the second table is not completed. This will result in incomplete data or even corrupt data in the database. To avoid this, use transactions, which either succeed or fail for each statement in the block. 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 one of these SQL operations fails, the ROLLBACK command can restore the database to the state it was in before BEGIN.

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 secure access method by locking the database, so that users can not be interfered by other users.

Lock the table

Although transactions are an excellent way to maintain database integrity, they can sometimes affect database performance because of their exclusivity, especially in large applications. Because the database is locked during the transaction, other user requests can only wait until the transaction ends. If a database system is used by only a few users, the impact of transactions is not much of a problem; However, if thousands of users access a database system at the same time, such as an e-commerce site, there will be serious response delays.

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

Here, we fetch the initial data with a SELECT statement, and update the new values to the table with an UPDATE statement after some calculation. A LOCKTABLE statement with the WRITE keyword ensures that no other access to inventory will insert, update, or delete inventory until the UNLOCKTABLES command is executed.

6. Use foreign keys

Locking tables preserves data integrity, but it does not guarantee data relevancy. That’s when we can use foreign keys.

For example, foreign keys can ensure that every sales record points to an existing customer. In this case, the foreign key maps the CustomerID in the CustomerInfo table to the CustomerID in the SalesInfo table, and any record that does not have a valid CustomerID will not be updated or inserted into salesInfo.

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 that customerin the SalesInfo table are automatically deleted. If you want to use foreign keys in MySQL, remember to define the table type as InnoDB when creating the table. This type is not the default type for MySQL tables. The method is defined by adding engine=INNODB to the CREATE TABLE statement. As shown in the example.

7. Use indexes

Indexes are a common way to improve database performance by allowing the database server to retrieve specific rows much faster than without indexes, especially if the query contains commands such as MAX(),MIN(), and ORDERBY.

Which fields should you index?

In general, indexes should be built on fields that will be used for JOIN,WHERE judgment, and Order Derby sorting. Try not to index a field in the database that contains a large number of duplicate values. It is quite possible for a field of type ENUM to have a large number of duplicate values

For example, “province” in customerInfo.. Fields on which indexing is not going to help; Conversely, it may degrade the performance of the database. We can create tables with appropriate indexes, or we can use ALTERTABLE or CREATEINDEX to create indexes later. In addition, MySQL supports full-text indexing and searching as of version 3.23.23. A FULLTEXT index is a FULLTEXT index in MySQL, but can only be used for MyISAM tables. For a large database, loading data into a table without a FULLTEXT index and then creating an index using ALTERTABLE or CREATEINDEX can be very fast. However, if you load the data into a table that already has a FULLTEXT index, the execution will be very slow.

8. Optimized query statements

In most cases, using indexes can speed up queries, but if SQL statements are not used properly, indexes can’t do their job.

Here are a few things to watch out for.

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

Prior to MySQL3.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, you can compare a CHAR field and a VARCHAR field if they have the same size.

B. Second, try not to use functions on fields that have indexes

For example, using the YEAE() function on a field of type DATE will disable the index. So, the following two queries return the same results, but the latter is much faster than the former.

C. Third, when searching for character fields, we sometimes use the LIKE keyword and wildcards, which is simple but also at the expense of systematicness

For example, the following query will compare each record in the table

But the following query returns the same result, but much faster:

Finally, you should be careful to avoid having MySQL perform automatic type conversions in queries, because the conversion process can also render indexes useless. That’s the end of this article. If you want to learn more about Java, you can watch the video. Welcome to comment on the private letter interaction, learn and progress together ~