Interview questions: how to optimize MYSQL database queries

: This article mainly introduces interview questions: I will talk about how to optimize MYSQL database queries. if you are interested in PHP tutorials, please refer to it. 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.

By the Internet: http://soft.chinabyte.com/database/254/11335754.shtml

Reading

Mysql optimization (how to query SQL statements with low execution efficiency in mysql)

How can I perform joint queries on multiple tables with the same structure in a mysql database of tens of millions? How to optimize or set to improve the query speed?

Query optimization and paging test of tens of millions of data in the mysql database

Mysql learning experience

Eight methods to optimize the Mysql database

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