Detailed explanation of eight ways of MySQL database optimization of classic must see

Introduction:

About database optimization, there are many data and methods on the Internet, but many of them are uneven in quality, some of them are not in place, and the contents are jumbled.

Occasionally found this article, summed up very classic, 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 attribute

MySQL is good for accessing large amounts of data, but generally speaking, the smaller the table in the database, the faster the queries will be executed on it. Therefore, when creating a table, for better performance, we can set the width of the fields in the table as small as possible.

For example, when defining the zip code field, if it is set to CHAR (255), it obviously adds unnecessary space to the database, and even using VARCHAR is redundant, because CHAR (6) can complete the task well. Similarly, 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, 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 join (JOIN) instead of subquery (Sub-Queries)

MySQL supports SQL subqueries from 4.1 onwards. This technique can use the SELECT statement to create a single-column query result, and then use this result as a filter condition in another query. For example, if we want to delete customers without any orders from the customer base information table, we can use the subquery to first fetch all customers ID who placed orders from the sales information table, and then pass the results to the main query, as follows:

DELETEFROMcustomerinfo
WHERECustomerIDNOTin(SELECTCustomerIDFROMsalesinfo)

Using subqueries, many SQL operations that logically require multiple steps can be completed at one time, and transaction or table locking can be avoided, and it is easy to write. However, in some cases, subqueries can be replaced by more efficient joins (JOIN).. For example, suppose we want to take out all users without order records, which can be done with the following query:

SELECT*FROMcustomerinfo
WHERECustomerIDNOTin(SELECTCustomerIDFROMsalesinfo)

If you use join (JOIN).. to complete this query, it will be much faster. Especially if CustomerID is indexed in the salesinfo table, the performance will be better. The query is as follows:

SELECT*FROMcustomerinfo
LEFTJOINsalesinfoONcustomerinfo.CustomerID=salesinfo.CustomerID
WHEREsalesinfo.CustomerIDISNULL

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

3. Use union (UNION) 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 one query. At the end of the client's query session, the temporary table will be automatically deleted, thus ensuring that the database is neat and efficient. When using union to create a query, we only need to use UNION as a keyword to concatenate multiple select statements. It is important to note that the number of fields in all select statements should be the same. The following example demonstrates a query using UNION.

SELECTName,PhoneFROMclientUNION
SELECTName,BirthDateFROMauthorUNION
SELECTName,SupplierFROMproduct

4. Transactions

Although we can use subqueries (Sub-Queries), joins (JOIN), and unions (UNION) to create a 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 1 series of statements to complete some kind of work. But in this case, when one of the statements in this block runs in error, the operation of the whole block becomes uncertain. Imagine 1, if you want to insert some data into two associated tables at the same time, you may have such a situation: After the successful update in the first table, the database suddenly appears unexpected conditions, resulting in the operation in the second table not being completed, thus causing incomplete data and even destroying the data in the database. To avoid this, you should use transactions, which either succeed or fail every statement in the statement block. In other words, it is possible to maintain the uniformity and integrity of the data in the database. Things start with the BEGIN keyword and end with the COMMIT keyword. If one SQL operation fails in between, the ROLLBACK command can restore the database to the state before BEGIN started.

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

Another important function of transaction is that when multiple users use the same data source at the same time, it can provide users with a safe access mode by locking the database, which can ensure that users' operations are not disturbed by other users.

5. Lock table

Although transaction is a very good way to maintain database integrity, it sometimes affects database performance because of its exclusivity, especially in large application systems. Because the database will be locked during transaction execution, 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 become a big problem; However, if thousands of users visit a database system at the same time, such as visiting an e-commerce website, there will be serious response delay.

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

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

Here, we use an select statement to fetch the initial data, and through a few calculations, we use an update statement to update the new values to the table. The LOCKTABLE statement containing the WRITE keyword ensures that no other access can be made to insert, update, or delete inventory until the UNLOCKTABLES command is executed.

6. Use foreign keys

The method of locking tables can maintain the integrity of data, but it cannot guarantee the relevance of data. At this time, we can use foreign keys.

For example, a foreign key can ensure that every 1 sales record points to a 1 existing customer. Here, the foreign key can map CustomerID in the customerinfo table to CustomerID in the salesinfo table, and any record without legal CustomerID will not be updated or inserted into salesinfo.

CREATETABLEcustomerinfo( CustomerIDINTNOTNULL,PRIMARYKEY(CustomerID))TYPE=INNODB;
CREATETABLEsalesinfo( SalesIDINTNOTNULL,CustomerIDINTNOTNULL,
PRIMARYKEY(CustomerID,SalesID),
FOREIGNKEY(CustomerID)REFERENCEScustomerinfo(CustomerID)ONDELETECASCADE)TYPE=INNODB; 　 

Note the parameter "ONDELETECASCADE" in the example. This parameter ensures that when one 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, 1 remember to define the table type as the transaction-safe table InnoDB type when you create the table. This type is not the default type for MySQL tables. This is defined by adding TYPE = INNODB to the CREATETABLE statement. As shown in the example.

7. Use indexes

Indexing is a common way to improve database performance, which enables 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.

Which fields should be indexed?

1 In general, the index should be built on the fields that will be used for JOIN, WHERE judgment, and ORDERBY sorting. Try not to index a field in the database that contains a large number of duplicate values. For a field of type ENUM, a large number of duplicate values are very likely

For example, the "province".. field in customerinfo, on which indexing would not be helpful; On the contrary, it may degrade the performance of the database. We can create the appropriate index at the same time as we create the table, or we can use ALTERTABLE or CREATEINDEX to create the index later. In addition, MySQL supports full-text indexing and search from version 3.23. 23. Full-text indexes are an FULLTEXT type index in MySQL, but can only be used for tables of MyISAM type. For a large database, loading data into a table without an FULLTEXT index and then creating an index using ALTERTABLE or CREATEINDEX is very fast. However, if you load the data into a table that already has an FULLTEXT index, the execution process will be very slow.

8. Optimized query statements

In most cases, using indexes can speed up queries, but if the SQL statement is used inappropriately, indexes will not work as they should.

Here are a few aspects that should be paid attention to.

a, first of all, it is best to compare fields of the same type

Before MySQL version 3.23, this was even a necessary condition. For example, an indexed INT field cannot be compared to an BIGINT field; However, as a special case, CHAR-type fields and VARCHAR-type fields can be compared when their field sizes are the same.

b, second, try not to use functions on fields with indexes

For example, using the YEAE () function on a field of type DATE prevents the index from working as it should. Therefore, although the following two queries return the same result, the latter is much faster than the former.

C 3. When searching for character fields, we sometimes use the LIKE keyword and wildcard characters, which is simple, but at the expense of system performance

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

SELECT*FROMbooks
WHEREnamelike"MySQL%"

However, if you switch to the following query, the result will be 1, but the speed will be much faster:

SELECT*FROMbooks
WHEREname > ="MySQL"andname < "MySQM"

Finally, care should be taken to avoid having MySQL perform automatic type conversion in the query, because the conversion process will also render the index ineffective.