8 ways to optimize MySQL performance

1, select the most applicable field properties

MySQL can support a large amount of data access, but generally speaking, the smaller the table in the database, the faster the query 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 this field for a postal code, setting it to char (255) obviously adds unnecessary space to the database, and even the use of varchar is redundant because char (6) completes the task well. Similarly, if possible, we should use Mediumint instead of bigin to define an integral field.

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 the query in the future.

For some text fields, such as "provinces" or "gender," we can define them as enum types. Because in MySQL, the enum type is treated as numeric data, and numeric data is processed much faster than text type. In this way, we can improve the performance of the database.

2, use the connection (join) to replace the subquery (sub-queries)

MySQL supports SQL subquery starting with 4.1. This technique can use a SELECT statement to create a single column of query results, and then use the result as a filter condition in another query. For example, to delete a customer with no orders in the Customer profile table, you can use a subquery to remove all the customer IDs from the Sales Information table and then pass the results to the main query, as follows:

The following are the referenced contents: DELETE from CustomerInfo WHERE CustomerID not in (SELECT CustomerID from Salesinfo)

You can use subqueries to perform a lot of SQL operations that logically require multiple steps to complete, while avoiding transactions or table locks and writing easily. In some cases, however, subqueries can be more efficiently connected (join) ... Alternative. For example, suppose we want to take out all the users who have no order records, and we can do this with the following query:

The following are the referenced contents: SELECT * from CustomerInfo WHERE CustomerID not in (SELECT CustomerID from Salesinfo)

If you use a connection (join) ... To complete this query work, the speed will be much faster. Especially when the Salesinfo table has indexed the CustomerID, the performance will be better, the query is as follows:

The following are the referenced contents: SELECT * from CustomerInfo Left JOIN Salesinfoon CustomerInfo. Customerid=salesinfo. CustomerID WHERE Salesinfo. CustomerID is NULL

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

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

MySQL supports the UNION query from version 4.0, which can combine a query that requires two or more SELECT queries that require the use of temporary tables. At the end of the client's query session, temporary tables are automatically deleted, ensuring that the database is neat and efficient. When we use union to create a query, we just need to use union as a keyword to connect multiple SELECT statements, and note that the number of fields in all SELECT statements is the same. The following example shows a query that uses union.

The following are the referenced contents: SELECT Name, Phone from client UNION SELECT Name, birthdate from author UNION SELECT Name, Supplier from product

4. Business

Although we can use subqueries (sub-queries), joins (join), and Union (union) to create a wide variety of queries, not all database operations can be done with just one or a few SQL statements. More often, you need to use a series of statements to accomplish a certain kind of work. In this case, however, when one of the statements in the statement block runs in error, the entire statement block becomes indeterminate. Imagine inserting a data into two linked tables at the same time. This can happen when a successful update in the first table surprises the database, causing the operation in the second table to be incomplete, which can result in incomplete data and even damage to the data in the database. To avoid this, 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. A SQL operation between this fails, then the rollback command can restore the database to the state before the start of begin.

The following are the referenced contents: BEGIN; INSERT into Salesinfo SET customerid=14; UPDATE Inventory SET quantity=11 WHERE item= ' 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 a secure way for users to access, thus ensuring that the user's actions are not interfered by other users.

Total 2 page: previous 1 [2] Next page