Eight "best hands" to optimize MySQL database performance

This paper discusses the way to improve the performance of MySQL database, and gives the concrete solution from 8 aspects.

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:

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:

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:

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.

SELECT Name, Phone FROM client
UNION
SELECT Name, BirthDate FROM author
UNION
SELECT Name, Supplier FROM product