5 Practical tips for Mysql query optimization _mysql

This paper summarizes and analyzes the techniques of MySQL query optimization. Share to everyone for your reference, specific as follows:

People who are familiar with SQL statements know that if you want to operate on a task, the SQL statement can have a lot of relevant writing, but different ways of writing query performance may be a great difference.

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

1, optimize the data type

There are many types of data in MySQL, if you are a DBA, you are rigorously checking the data types according to the principles of optimization, but developers may choose the easiest option they think to speed up coding, or choose the most obvious choice, so you may not be facing the best choice, if possible , you should try to change these decisions with general guidelines.

(1) Avoid using null

Null for most databases require special processing, MySQL is no exception, it requires more code, more checks and special indexing logic, some developers are completely unaware that the creation of a table null is the default value, but most of the time should use NOT NULL, or use a special value, such as 0,-1 as the default value.

(2) Only smaller fields may be used

MySQL is stored in memory after reading data from disk, and then reads it using CPU cycles and disk I/O, which means that the smaller the data type takes up less space, the more efficient it is to read from disk or package to memory, but not too persistent to reduce the data type, There will be no room for any future changes in the application. Modifying a table will require refactoring, which can indirectly cause code changes, which is a headache, so you need to find a balance.

2, careful character set conversion

The character set used by clients or applications may not be the same as the character set of the table itself, which requires the implicit conversion of MySQL during its run, and the need to determine whether the character set, such as UTF-8, supports multibyte characters, so they require more storage space.

3. Optimize count (My_col) and COUNT (*)

If you use the MyISAM table, using the COUNT (*) speed without a WHERE clause is fast because the number of rows is very accurate, so MySQL does not look in line to find the number of rows, such as the My_col column has no null value, then, as in the case above, That is, COUNT (My_col) will also be fast.

If you use COUNT () when you have a WHERE clause, you will basically not be able to do more optimizations, which are beyond the obvious indexed columns in the WHERE clause, and only use the overlay index for complex where clauses.

In addition to the above suggestions, you can also use the summary table, they allow you to keep an update on the contents of the table, you can use triggers, or the application logic keeps the summary table up to date, or if you use the latter, your information will be very close. But not accurate, depending on how often a batch job runs, it requires balancing the application's need for precise information, and maintaining the system overhead of data updates, to find a balance between the two.

4, optimize the subquery

When a subquery is encountered, the MySQL query optimization engine is not always the most effective, which is why the subquery is often converted to a connection query, and the optimizer has been able to handle the connection query correctly, and of course it is important to ensure that the connection column (second table) of the connection table is indexed, On the first table MySQL usually scans the entire table relative to the query subset of the second table, which is part of the nested loop algorithm.

5. Optimizing Union

Using union when crossing multiple different databases is an interesting optimization method, the union returns data from two unrelated tables, which means that no duplicate rows are present and the data must be sorted, and we know that sorting is very resource-intensive, especially the sort of large tables.

UNION all can greatly speed up, if you already know your data will not include duplicate rows, or you do not care whether there will be duplicate rows, in both cases using union all is more appropriate. In addition, you can use some method in the application logic to avoid duplicate rows so that union all and union return the same result, but union all does not sort.

Summary

Although the optimization methods listed in this article are not complete, whenever possible, try different solutions whenever possible, view the query plan, test on a large dataset, and position the result datum points to see what happens in the actual application. Also, check your slow query log in advance to find the query you need to adjust as early as possible.

I hope this article will help you with MySQL database program design.