Interview question: Talk about how to optimize MYSQL database queries

1. Optimize data types

There are many data types in MySQL. If you are a DBA, you are strictly checking the data types according to the principles of optimization, but developers may choose the one they think is the simplest. options to speed up coding, or to choose the most obvious choice, so you may not be faced with the best choice, and if possible, you should try to use general guidelines to change these decisions.

　　(1) Avoid using NULL

　NULL requires special handling for most databases, and MySQL is no exception. It requires more code, more checks and special indexing logic. Some developers completely Didn't realize that NULL is the default value when creating a table, but most of the time you should use NOT NULL, or use a special value like 0, -1 as the default value.

　　　(2) It is only possible to use smaller fields

　 MySQL reads the data from the disk and stores it in memory, and then uses cpu cycles and disk I/O to read it, which means the smaller The smaller the data type takes up, the more efficient it is to read from disk or pack it into memory. However, don’t be too obsessed with reducing the data type. If the application changes in the future, there will be no space. Modifying the table will require reconstruction, which may indirectly cause code changes. This is a headache, so a balance needs to be found.

2. Be careful with character set conversion

The character set used by the client or application may be different from the character set of the table itself. This requires MySQL to convert implicitly during the running process. In addition, make sure Character sets like UTF-8 support multi-byte characters, so they require more storage space.

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

If you use a MyISAM table, using count(*) without a where clause is very fast because the number of rows is counted It is very accurate, so MySQL will not search row by row to get the number of rows. If the my_col column does not have a null value, then the situation will be the same as mentioned before, that is, count(my_col) will be very fast.

If you use count() when there is a where clause, basically no more optimization can be done. The obvious index columns are exceeded in the where clause. For complex where clauses, only covering indexes are useful. .

In addition to the suggestions above, you can also use summary tables. They allow you to keep the contents of the table updated. You can use triggers, or application logic to keep the summary table always up to date, or run one periodically. Batch jobs keep populating the latest data information. If you do the latter, your information will be very close, but not exact, depending on how often the batch job runs. This needs to be weighed against the application's need for accurate information, and The system overhead of keeping data updated requires a balance between the two.

4. Optimize subqueries

When encountering subqueries, the MySQL query optimization engine is not always the most effective. This is why subqueries are often converted into join queries. The optimizer can already The connection query is processed correctly. Of course, one thing to note is to ensure that the connection column of the connection table (the second table) is indexed. On the first table, MySQL usually performs a query subset relative to the second table. A full table scan, which is part of the nested loop algorithm.

　5. Optimizing UNION

Using UNION across multiple different databases is an interesting optimization method. UNION returns data from two unrelated tables, which means there will be no duplication. At the same time, the data must be sorted. We know that sorting is very resource-intensive, especially sorting large tables.

UNION ALL can greatly speed up the process. If you already know that your data will not include duplicate rows, or you don’t care whether duplicate rows will appear, using UNION ALL is more suitable in both cases. In addition, some methods can be used in the application logic to avoid duplicate rows, so that the results returned by UNION ALL and UNION are the same, but UNION ALL will not be sorted.

Original text from [Bit Network]: http://soft.chinabyte.com/database/254/11335754.shtml

Related reading

Optimization of mysql (how to query sql statements with low execution efficiency in mysql)

mysql How to perform joint queries on multiple tables with the same structure in a tens-of-million-level database? How to optimize or set up to improve query speed?

Query optimization and paging testing of tens of millions of data in mysql database

mysql learning experience

8 ways to optimize Mysql database

The above introduces the interview questions: talk about how to optimize MYSQL database queries, including aspects of the content, I hope it will be helpful to friends who are interested in PHP tutorials.