MySQL Data optimization method

1. Select the most applicable field properties

MySQL is good at supporting large data volumes, but in general, the smaller the tables in the database, the faster queries are executed on it. Therefore, in order to achieve better performance when creating a table, we can set the width of the fields in the table as small as possible.

For example, when you define a postal code field, if you set it to char (255), you obviously add unnecessary space to the database, and even this type of varchar is redundant, because char (6) can do a good job. 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 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 connection (join) instead of subquery (sub-queries)

MySQL supports SQL subquery starting from 4.1. This technique can use the SELECT statement to create a single-column query result, and then use the result as a filter for another query. For example, if we want to delete a customer who does not have any orders in the customer's basic information table, we can take advantage of the subquery to remove all the customer IDs from the Sales Information table and then pass the results to the main query, as follows:

DELETEFROMcustomerinfo

WHERECustomerIDNOTin(SELECTCustomerIDFROMsalesinfo)

The use of subqueries allows you to do a lot of SQL operations that logically require multiple steps to complete, as well as to avoid transactions or table locks and write them easily. However, in some cases, subqueries can be more efficiently connected (join). Alternative. For example, suppose we want to take out all the users without an order record, which can be done with the following query:

SELECT*FROMcustomerinfo

WHERECustomerIDNOTin(SELECTCustomerIDFROMsalesinfo)

If you use Connection (join): To complete this query work, the speed will be much faster . Especially if the Salesinfo table in the CustomerID index, the performance will be better, query as follows:

SELECT*FROMcustomerinfo

LEFTJOINsalesinfoONcustomerinfo.CustomerID=salesinfo.CustomerID

WHEREsalesinfo.CustomerIDISNULL

Connect (Join): It is more efficient because MySQL does not need to create a temporary table in memory to complete this logical two-step query effort.

3. Use Union (Union) instead of manually created temporary table

MySQL supports union queries starting with version 4.0, which can combine two or more select queries that need to use temporary tables in a single query. At the end of the client's query session, the temporary table is automatically deleted, ensuring that the database is neat and efficient. When using union to create a query, we only need to concatenate multiple SELECT statements with union as a keyword, and be aware that the number of fields in all SELECT statements is the same. The following example shows a query that uses union.

SELECTName,PhoneFROMclientUNION

SELECTName,BirthDateFROMauthorUNION

SELECTName,SupplierFROMproduct

4. Business

Although we can use subqueries (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 it is necessary to use a series of statements to accomplish some kind of work. But in this case, when one of the statements in this block of statements runs out of error, the operation of the entire statement block becomes indeterminate. Imagine that, to insert a data into two associated tables at the same time, it may be that the first table in the successful update, the database suddenly unexpected situation, resulting in the operation of the second table is not completed, which will result in incomplete data, and even destroy the data in the database. To avoid this situation, you should use a transaction that either succeeds or fails for each statement in the statement block. In other words, the consistency and integrity of the data in the database can be maintained. Things begin with the BEGIN keyword, and the commit keyword ends. When a SQL operation fails, the rollback command can restore the database to the state it was in before begin.

BEGIN; INSERTINTOsalesinfoSETCustomerID=14;UPDATEinventorySETQuantity=11WHEREitem=‘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 secure way to access, so as to ensure that the user's operation is not disturbed by other users.

5. Lock the table

Although a transaction is a good way to maintain database integrity, it can sometimes affect the performance of a 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 be temporarily waited until the transaction ends. If a database system has only a few users to use, the impact of the transaction will not be a big problem, but assuming that there are thousands of users accessing a database system at the same time, such as visiting an e-commerce site, there will be a more severe response delay.

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

LOCKTABLEinventoryWRITESELECTQuantityFROMinventoryWHEREItem=‘book‘;

...

UPDATEinventorySETQuantity=11WHEREItem=‘book‘;UNLOCKTABLES

Here, we use a SELECT statement to remove the initial data and, with some calculations, update the new value to the table with the UPDATE statement. The locktable statement with the Write keyword guarantees that there will be no additional access to insert, update, or delete inventory until the Unlocktables command is executed.

6. Using foreign keys

The method of locking the table can maintain the integrity of the data, but it does not guarantee the data association. At this point we can use foreign keys.

For example, a foreign key can guarantee that each sales record points to an existing customer. In this case, the foreign key can map the CustomerID in the CustomerInfo table to the CustomerID in the Salesinfo table, and any record with no 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 guarantees that when a customer record in the CustomerInfo table is deleted, all records related to that customer in the Salesinfo table will be automatically deleted. If you want to use a foreign key in MySQL, be sure to remember to define the table's type as the Transaction safe 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 CreateTable statement. As shown in the example.

7. Use Index

Indexes are a common method of improving database performance, which allows the database server to retrieve specific rows at a much faster rate than without indexes, especially when the query contains Max (), MIN (), and the order of these commands.

Which fields should be indexed?

In general, the index should be based on those fields that will be used for join,where judgment and sort by order. Try not to index a field in the database that contains a large number of duplicate values. For a field of an enum type, there is a high likelihood of a large number of duplicate values

For example, "Province" in CustomerInfo. field, indexing on such a field will not help, and conversely, it may reduce the performance of the database. We can create the appropriate index at the same time when 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 starting from version 3.23.23. Full-text indexing is a fulltext type index in MySQL, but can only be used for tables of type MyISAM. For a large database, it is very fast to load the data into a table that does not have a fulltext index and then use altertable or CreateIndex to create the index. However, if you load the 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 an index can improve the speed of the query, but if the SQL statement is used inappropriately, the index will not function as it should.

Here are a few things to be aware of.

• First, it's best to compare between fields of the same type.

• Second, try not to use functions on fields that are indexed.

For example, when you use the Yeae () function on a field of a date type, the index will not function as it should. As a result, the following two queries return the same results, but the latter is much faster than the former.

• Third, 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 each record in the table.

Select*frombookswherenamelike "mysql%"

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

select*frombookswherename>= "MySQL" andname< "mysqm"

Finally, it is important to avoid having MySQL perform automatic type conversions in queries, because the conversion process also makes the index ineffective.

This article is from the Data optimization blog, so be sure to keep this source http://9988952.blog.51cto.com/9978952/1951981

MySQL Data optimization method