Shulou(Shulou.com)06/01 Report--

1. Select the most applicable field attribute

MySQL can well support access to a large amount of data, but in general, the smaller the table in the database, the faster the query will be executed on it. Therefore, in order to achieve better performance when creating the table, we can set the width of the fields in the table as small as possible.

For example, when defining the zip code field, if you set it to CHAR, it obviously adds unnecessary space to the database, and even the use of the VARCHAR type is redundant, because CHAR (6) can do the job 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 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 JOIN to replace subquery (Sub-Queries)

MySQL has supported SQL's subqueries since 4. 1. This technique can use the select statement to create a single-column query result, which can then be used as a filter condition in another query. For example, if we want to delete a customer without any order in the customer basic information table, we can use a subquery to extract all the customer ID that placed the order from the sales information table, and then pass the result to the main query, as shown below:

DELETEFROMcustomerinfo

WHERECustomerIDNOTin (SELECTCustomerIDFROMsalesinfo)

Using subqueries can accomplish many SQL operations that logically require multiple steps to complete at one time, avoid transaction or table locking, and are easy to write. However, in some cases, subqueries can be JOIN more efficiently. Replace. For example, suppose we want to take out all the users who do not have an order record, we can complete it with the following query:

SELECT*FROMcustomerinfo

WHERECustomerIDNOTin (SELECTCustomerIDFROMsalesinfo)

If you use connection (JOIN).. To complete this query, it will be much faster. Especially when there is an index on CustomerID in the salesinfo table, the performance will be better, as shown in the query:

SELECT*FROMcustomerinfo

LEFTJOINsalesinfoONcustomerinfo.CustomerID=salesinfo.CustomerID

WHEREsalesinfo.CustomerIDISNULL

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

3. Use UNION instead of manually created temporary tables

MySQL has supported union queries since version 4.0, which can merge two or more select queries that need to use temporary tables in a single query. At the end of the client query session, the temporary table is automatically deleted to ensure that the database is neat and efficient. When using union to create a query, we only need to concatenate multiple select statements using UNION as the keyword, and note that the number of fields in all select statements should be the same. The following example demonstrates a query that uses UNION.

SELECTName,PhoneFROMclientUNION

SELECTName,BirthDateFROMauthorUNION

SELECTName,SupplierFROMproduct

4. Transaction

Although we can use subqueries (Sub-Queries), JOIN (join), and UNION (UNION) to create a variety of queries, not all database operations can be done with one or a few SQL statements. More often, you need to use a series of statements to accomplish some kind of work. But in this case, when one of the statements in this block goes wrong, the operation of the whole block becomes uncertain. Imagine that if you want to insert some data into two associated tables at the same time, it may happen that after a successful update in the first table, there is a sudden unexpected condition in the database, resulting in the operation in the second table not being completed, thus resulting in incomplete data and even destroying the data in the database. To avoid this, you should use transactions, which either succeed or fail for every statement in the block. In other words, you can maintain the consistency and integrity of the data in the database. Things start with the BEGIN keyword and end with the COMMIT keyword. If a SQL operation fails in between, the ROLLBACK command can restore the database to the state it was before BEGIN started.

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 of access. this ensures that the user's operation will not be disturbed by other users.

5. Lock the table

Although transaction is a very good way to maintain database integrity, it sometimes affects the performance of 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 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 suppose there are thousands of users accessing a database system at the same time, such as 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 take the initial data, and through some calculations, update the new value to the table. A LOCKTABLE statement that contains the WRITE keyword ensures that there will be no other access to insert, update, or delete the inventory until the UNLOCKTABLES 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 point, 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, and any record without a legal CustomerID will not be updated or inserted into the salesinfo.

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

Notice 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, be sure to remember to define the type of the table 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 the index

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 contains commands such as MAX (), MIN () and ORDERBY.

Which fields should be indexed?

In general, the index should be based 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, it is possible to have a large number of duplicate values

For example, "province" in customerinfo.. Field, indexing on such a field will not help; 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 since version 3.23.23. A full-text index is an index of type FULLTEXT in MySQL, but can only be used for tables of type MyISAM. For a large database, it would be very fast to load the data into a table without an FULLTEXT index, and then create an index using ALTERTABLE or CREATEINDEX. 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 statement

In most cases, using an index can improve the speed of the query, 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 paid attention to.

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

Before the MySQL3.23 version, this was even a necessary condition. For example, you cannot compare an indexed INT field to a BIGINT field; but as a special case, you can compare fields of type CHAR and field of type VARCHAR when they are the same size.

Second, try not to use functions on indexed fields.

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

Third, we sometimes use LIKE keywords and wildcards when searching for character fields, 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%"

But if you switch to the following query, the result is the same, but much faster:

SELECT*FROMbooksWHEREname > = "MySQL" andname < "MySQM"

Finally, care should be taken to avoid letting MySQL do automatic type casting in the query, because the conversion process can also render the index ineffective.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.