Five practical tips for MySQL Query Optimization,

Five practical tips for MySQL Query Optimization,

This article summarizes and analyzes the MySQL query optimization techniques. We will share this with you for your reference. The details are as follows:

Anyone familiar with SQL statements knows that if you want to perform operations on a task, SQL statements can be written in many ways. However, the query performance of different statements may be quite different.

This article lists five MySQL query optimization methods. Of course, there are many optimization methods.

1. Optimize Data Types

There are multiple data types in MySQL. If you are a DBA, you are strictly checking the data type according to the optimization principle. However, developers may choose the simplest solution they think, to speed up encoding or select the most obvious choice. Therefore, you may not be faced with the best choice. If possible, you should try to change these decisions using general principles.

(1) Avoid NULL

NULL requires special processing for most databases, and MySQL is no exception. It requires more code, more checks, and special index logic. Some developers do not realize that, when creating a table, NULL is the default value, but not null should be used in most cases, or a special value, such as 0 and-1, should be used as the default value.

(2) Only smaller fields may be used.

After MySQL reads data from the disk, it is stored in the memory, and then reads the data using the cpu cycle and disk I/O. This means that the smaller the data type, the smaller the space occupied, the efficiency from disk reading or packaging to memory is better, but you should not persistently reduce the data type. If the application changes in the future, there will be no space. Modifying a table requires reconstruction, which may indirectly lead to code changes. This is a headache, so we need to find a balance point.

2. Careful Character Set Conversion

The character set used by the client or application may be different from the character set of the table itself, which requires implicit conversion during MySQL running, and to determine whether the character set, such as the UTF-8, supports multi-byte characters, therefore, they need more storage space.

3. Optimize count (my_col) and count (*)

If you use a MyISAM table, it is very fast to use count (*) without the where clause, because the statistics on the number of rows are very accurate, therefore, MySQL does not search for the number of rows in one row, and then obtains the number of rows. For example, if the my_col column does not have a null value, it will be the same as the preceding situation, that is, the count (my_col) speed will be very fast.

If count () is used when a where clause exists, more optimization is basically impossible. The where clause contains more than the obvious index column. For complex where clauses, only overwriting indexes are useful.

In addition to the preceding suggestions, you can also use a summary table to update the table content. You can use a trigger or keep the summary table updated by application logic, or run a batch job regularly to keep the latest data filled. If you use the latter, your information will be very close, but not accurate, depending on how often a batch job runs, you need to weigh the Needs of the Application for precise information and maintain the system overhead of data updates. You need to find a balance between the two.

4. Optimize subqueries

In the case of subqueries, the MySQL Query Optimization engine is not always the most effective, which is why subqueries are frequently converted to connection queries, the optimizer can correctly process connection queries. Of course, you must note that the connection columns of the connected table (the second table) are indexed, in the first table, MySQL usually performs a full table scan relative to the query subset of the second table, which is part of the nested loop algorithm.

5. Optimize UNION

Using UNION across multiple different databases is an interesting optimization method. UNION returns data from two unrelated tables, which means no duplicate rows will appear, data must also be sorted. We know that sorting is resource-consuming, especially for large tables.

Union all can greatly speed up. If you already know that your data does not include duplicate rows, or you don't care whether duplicate rows will appear, in both cases, union all is more suitable. In addition, some methods can be used in the application logic to avoid repeated rows. In this way, the results returned by union all and union all are the same, but union all is not sorted.

Summary

Although the optimization methods listed in this article are not complete, whenever possible, try different solutions, view the query plan, test on a large dataset, and locate the result benchmark, observe the actual situation in the application. At the same time, check your slow query log in advance to find the query to be adjusted as early as possible.

I hope this article will help you design MySQL database programs.

Articles you may be interested in:

• Simple optimization tutorial for table connection query in MySQL
• MySQL slow Query Optimization-slow query log analysis instance tutorial
• MySQL subquery and optimization learning tutorial
• Amazing MySQL query performance optimization
• Simple rewriting and optimization of MySQL subqueries
• Optimization Techniques for subqueries in MySQL
• SQL optimization solution for querying duplicate data in mysql
• Simple Example of php + mysql Query Optimization
• Mysql optimizes the five limit query statements
• 30 common methods for optimizing SQL statement queries in MySQL