Interview question: Talk about how to optimize MySQL database query

1. Optimize data type

There are several types of data in MySQL, and if you are a DBA, you are rigorously checking the data types according to the principles of optimization, but developers may choose what they think is the simplest scenario to speed up the encoding or choose the most obvious choice, so you may not face the best choice, if possible , you should try to change these decisions with common criteria.

(1) Avoid using null

Null for most databases requires special handling, MySQL is no exception, it requires more code, more checks and special indexing logic, some developers do not realize that when creating 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

When MySQL reads data from disk, it is stored in memory and then read with CPU cycles and disk I/O, which means 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 is no room for any future changes to the application. Modifying the table will require refactoring, which can indirectly lead to changes in the code, which is a headache, so you need to find a balance point.

2. Be careful about character set conversion

The character set used by the client or application may not be the same as the character set of the table itself, which requires MySQL to be implicitly converted during operation, and 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 very fast, because the statistics of the number of rows is very precise, so MySQL does not go one line at a time to find the number of rows, such as the My_col column does not have null values, then the same as the previous said, That is, the count (My_col) speed will also be fast.

If you have a WHERE clause that uses count (), it is basically impossible to do more optimizations, beyond the obvious index columns in the WHERE clause, and for complex where clauses it is only useful to use an overlay index.

In addition to the suggestions above, you can also use the summary table, which allows you to keep the contents of the table updated, you can use triggers, or the application logic keeps the summary table always up-to-date, or periodically runs a batch job to keep up with the latest data information, and if you use the latter, your information will be very close, But not exactly, depending on how often a batch job runs, it is necessary to weigh the application's need for precise information and to maintain the overhead of data updates to find a balance between the two.

4, optimize sub-query

The MySQL query optimization engine is not always the most efficient when it comes to subqueries, which is why the subquery is often converted to a connection query, and the optimizer is already able to handle the connection query correctly, but one thing to be aware of is that the connection columns of the Join table (the second table) are indexed. On the first table, MySQL usually does a full table scan relative to the second table's subset of queries, which is part of the nested loop algorithm.

5. Optimize Union

Using union across multiple different databases is an interesting optimization method, and union returns data from two unrelated tables, which means that no duplicate rows will occur and the data must be sorted, and we know that sorting is very resource-intensive, especially for large tables.

Union ALL can be much faster if you already know that your data does not include duplicate rows, or if you don't care if duplicate rows appear, in both cases it is more appropriate to use UNION all. In addition, you can use some methods in your application logic to avoid duplicate rows, so that the Union all and union return the same results, but the union all is not sorted.

Original from "Bit net": http://soft.chinabyte.com/database/254/11335754.shtml

Related reading

MySQL optimization (how to query MySQL for inefficient execution of SQL statements)

How does the MySQL tens database perform the same table union query with multiple structures? How can I optimize or set up query speed?

Query optimization and paging testing of MySQL database tens data

MySQL Learning experience

8 ways to optimize a MySQL database

Interview question: Talk about how to optimize MySQL database query